Difference between revisions of "SQL"

From Organic Design wiki
(→‎Reset a password: Details)
(→‎Towards SQL for P2P environments: Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server)
Line 37: Line 37:
 
With server side access to the mysql database you first need to identify the '''user_id''' of the '''username''' you want to reset.
 
With server side access to the mysql database you first need to identify the '''user_id''' of the '''username''' you want to reset.
 
{{Code|<PHP>
 
{{Code|<PHP>
−
SELECT user_id, user_name from user
+
SELECT user_id,user_namefromuser
−
WHERE user_name="WikiSysop";
+
WHEREuser_name="WikiSysop";
 
</PHP>
 
</PHP>
 
}}
 
}}
  
−
Note that the password is a nested concatenation of both the md5 of the '''user_id''' combined with the md5 password.
+
Notethatthepasswordisanestedconcatenationofboththemd5ofthe'''user_id'''combinedwiththemd5password.
−
Now update that users password
+
Nowupdatethatuserspassword
 
{{Code|<PHP>
 
{{Code|<PHP>
−
UPDATE user
+
UPDATEuser
−
SET user_password=md5(CONCAT('1-',md5('password')))  
+
SETuser_password=md5(CONCAT('1-',md5('password')))
−
WHERE user_id=1;
+
WHEREuser_id=1;
 
</PHP>
 
</PHP>
 
}}
 
}}
  
−
An alternative way is if you know the users email address then you can identify the user and enable their email address so that the page [[Special:UserLogin]] can be used to '''Email-password'''
+
Analternativewayisifyouknowtheusersemailaddressthenyoucanidentifytheuserandenabletheiremailaddresssothatthepage[[Special:UserLogin]]canbeusedto'''Email-password'''
  
 
{{Code|<PHP>
 
{{Code|<PHP>
−
UPDATE user
+
UPDATEuser
−
SET user_email="wikiSysops@email.address"  
+
SETuser_email="wikiSysops@email.address"
−
WHERE user_id=1;
+
WHEREuser_id=1;
 
</PHP>
 
</PHP>
 
}}
 
}}
  
−
=== Reset or set a page hit counter ===
+
===Resetorsetapagehitcounter===
−
UPDATE page SET page_counter=0 WHERE page_title='Main_Page';
+
UPDATEpageSETpage_counter=0WHEREpage_title='Main_Page';
  
−
=== Adjust user groups ===
+
===Adjustusergroups===
−
INSERT INTO user_groups (ug_user,ug_group) VALUES(999,'sysop');
+
INSERTINTOuser_groups(ug_user,ug_group)VALUES(999,'sysop');
  
−
==Selecting current articles by category==
+
==Selectingcurrentarticlesbycategory==
−
See [http://upload.wikimedia.org/wikipedia/commons/4/41/Mediawiki-database-schema.png MediaWiki schema] for a description of the tables. Essentially '''categorylinks''' store the category member relationships , '''page''' identifies the title and metadata information, '''revision''' identifies al revisions of an article, and '''text''' contains the actual wikitext of articles. there is a one to many relationship between pages and revisions, and a one to many relationship between text and revisions.
+
See[http://upload.wikimedia.org/wikipedia/commons/4/41/Mediawiki-database-schema.pngMediaWikischema]foradescriptionofthetables.Essentially'''categorylinks'''storethecategorymemberrelationships,'''page'''identifiesthetitleandmetadatainformation,'''revision'''identifiesalrevisionsofanarticle,and'''text'''containstheactualwikitextofarticles.thereisaonetomanyrelationshipbetweenpagesandrevisions,andaonetomanyrelationshipbetweentextandrevisions.
  
−
The database schema used by MediaWiki allows variable article content to be stored as key => value pairs where the atomic unit for the content '''varies''' depending on the content of the article. A way around this is to use categorization to group common atomic unit structure together. Basically this is a filtering problem, however queried atomic unit structure needs to be checked downstream by any downstream processing of content within categories.  
+
ThedatabaseschemausedbyMediaWikiallowsvariablearticlecontenttobestoredaskey=>valuepairswheretheatomicunitforthecontent'''varies'''dependingonthecontentofthearticle.Awayaroundthisistousecategorizationtogroupcommonatomicunitstructuretogether.Basicallythisisafilteringproblem,howeverqueriedatomicunitstructureneedstobecheckeddownstreambyanydownstreamprocessingofcontentwithincategories.
  
 
===Example===
 
===Example===
−
*In this example the prefix is set to  '''{{SQLprefix}}''', set it in [[Template:SQLprefix]].
+
*Inthisexampletheprefixissetto'''{{SQLprefix}}''',setitin[[Template:SQLprefix]].
−
*In this example the category is set to '''{{SQLcat}}''', set it in [[Template:SQLcat]].
+
*Inthisexamplethecategoryissetto'''{{SQLcat}}''',setitin[[Template:SQLcat]].
  
−
:SELECT  old_text FROM {{SQLprefix}}text, {{SQLprefix}}revision
+
:SELECTold_text,old_id FROM {{SQLprefix}}text, {{SQLprefix}}revision
 
:WHERE old_id = rev_text_id and rev_id IN (
 
:WHERE old_id = rev_text_id and rev_id IN (
 
:SELECT page_latest FROM {{SQLprefix}}page, {{SQLprefix}}categorylinks
 
:SELECT page_latest FROM {{SQLprefix}}page, {{SQLprefix}}categorylinks
Line 126: Line 126:
 
*[[w:Prefix Hash Tree|Prefix Hash Trees]]
 
*[[w:Prefix Hash Tree|Prefix Hash Trees]]
 
*[http://www.cs.huji.ac.il/~ittaia/papers/AAY-OPODIS05.pdf Skip B-Trees]
 
*[http://www.cs.huji.ac.il/~ittaia/papers/AAY-OPODIS05.pdf Skip B-Trees]
 +
*[http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server] ''- Yoshinori Matsunobu's blog''

Revision as of 02:31, 24 December 2010

Template:Merge


SQL (Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL has been standardised by both ANSI and ISO.

SQL is commonly spoken either as the names of the letters ess-cue-el, or like the word sequel. The official pronunciation of SQL according to ANSI is ess-cue-el. However, each of the major database products (or projects) containing the letters SQL has its own convention: MySQL is officially and commonly pronounced "My Ess Cue El"; PostgreSQL is expediently pronounced postgres (being the name of the predecessor to PostgreSQL); and Microsoft SQL Server is commonly spoken as Microsoft-sequel-server. See MediaWikiLite for information about SQLite support in MediaWiki, or MSSQL for information about Microsoft SQL Server support.

Common MediaWiki MySQL queries

Insert into interwiki

<PHP>

INSERT INTO interwiki (iw_prefix,iw_url,iw_local) VALUES('example','http://www.example.org/$1',0); </PHP>

To delete entry;

<PHP>

DELETE FROM interwiki WHERE iw_prefix LIKE "example"; </PHP>

Backup and compress DB & FS

7za a -si backupfile.sql.7z # Backup

tar cf - directory


{{{1}}}

Reset a password

With server side access to the mysql database you first need to identify the user_id of the username you want to reset.

{{{1}}}

Notethatthepasswordisanestedconcatenationofboththemd5oftheuser_idcombinedwiththemd5password. Nowupdatethatuserspassword

{{{1}}}

AnalternativewayisifyouknowtheusersemailaddressthenyoucanidentifytheuserandenabletheiremailaddresssothatthepageSpecial:UserLogincanbeusedtoEmail-password


{{{1}}}

Resetorsetapagehitcounter

UPDATEpageSETpage_counter=0WHEREpage_title='Main_Page';

Adjustusergroups

INSERTINTOuser_groups(ug_user,ug_group)VALUES(999,'sysop');

Selectingcurrentarticlesbycategory

See[1]foradescriptionofthetables.Essentiallycategorylinksstorethecategorymemberrelationships,pageidentifiesthetitleandmetadatainformation,revisionidentifiesalrevisionsofanarticle,andtextcontainstheactualwikitextofarticles.thereisaonetomanyrelationshipbetweenpagesandrevisions,andaonetomanyrelationshipbetweentextandrevisions.

ThedatabaseschemausedbyMediaWikiallowsvariablearticlecontenttobestoredaskey=>valuepairswheretheatomicunitforthecontentvariesdependingonthecontentofthearticle.Awayaroundthisistousecategorizationtogroupcommonatomicunitstructuretogether.Basicallythisisafilteringproblem,howeverqueriedatomicunitstructureneedstobecheckeddownstreambyanydownstreamprocessingofcontentwithincategories.

Example

SELECTold_text,old_id FROM 1120_text, 1120_revision
WHERE old_id = rev_text_id and rev_id IN (
SELECT page_latest FROM 1120_page, 1120_categorylinks
WHERE cl_to = "Portal" and cl_from = page_id );

Some SQL queries executable by admin (need fixing to work in new MW1.9.3 environment)

Documentation

MySQL vs MSSQL

MySQL News & Information

RODBC

Using RODBC to interact with R requires the user to configure the system as described in the file README after installing RODBC.

See

Towards SQL for P2P environments

Retrieved from "https://organicdesign.nz/wiki/index.php?title=SQL&oldid=100145"