SQL
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.
Contents
Common MediaWiki MySQL queries
Insert into interwiki
To delete entry;
Backup and compress DB & FS
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.
Notethatthepasswordisanestedconcatenationofboththemd5oftheuser_idcombinedwiththemd5password. Nowupdatethatuserspassword
AnalternativewayisifyouknowtheusersemailaddressthenyoucanidentifytheuserandenabletheiremailaddresssothatthepageSpecial:UserLogincanbeusedtoEmail-password
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
- Inthisexampletheprefixissetto1120_,setitinTemplate:SQLprefix.
- InthisexamplethecategoryissettoSelenium,setitinTemplate:SQLcat.
- 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)
- Tables
- Drop
- Info
- Select User
- CL
- CUR
- OLD
- Delete
- Update
- IW
- IW-Update
- IW-Insert
- RC
- UR
- makeBot
- Reset Password
- Owner-Old
- Owner-Cur
- Sys:User articles
- xwAdmins
Documentation
- SQL Tutorial - A nice tutorial on SQL
- Official MySQL Documentation
- W3C SQL Tutorial (QuickRef)
MySQL vs MSSQL
MySQL News & Information
- Falcon Overview - the new MySQL 6 Falcon storage engine
- MySQL Stored procedures & triggers
RODBC
Using RODBC to interact with R requires the user to configure the system as described in the file README after installing RODBC.
- See
- http://www.unixODBC.org
- http://www.easysoft.com/developer/interfaces/odbc/linux.html (tutorial using unixODBC)