Difference between revisions of "SQL"

From Organic Design wiki
m (→‎Selecting the most recent revision of all articles)
(rv error in SQL query)
Line 33: Line 33:
 
=== Adjust user groups ===
 
=== Adjust user groups ===
 
  INSERT INTO user_groups (ug_user,ug_group) VALUES(999,'sysop');
 
  INSERT INTO user_groups (ug_user,ug_group) VALUES(999,'sysop');
−
 
−
==Selecting current articles by category==
 
−
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, and '''text''' contains the actual wikitext of articles.
 
−
 
−
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.
 
−
 
−
===Example===
 
−
*In this example the prefix is set to  '''{{SQLprefix}}''', set it in [[Template:SQLprefix]].
 
−
*In this example the category is set to '''{{SQLcat}}''', set it in [[Template:SQLcat]].
 
−
 
−
;Illustrating the relevant tables in the query
 
−
SELECT  * FROM {{SQLprefix}}categorylinks WHERE cl_to LIKE '{{SQLcat}}';
 
−
 
−
;This selects all pages (article names) from category '{{SQLcat}}'.
 
−
SELECT * FROM {{SQLprefix}}page
 
−
WHERE page_id IN (SELECT  cl_from FROM {{SQLprefix}}categorylinks WHERE cl_to LIKE '{{SQLcat}}');
 
−
 
−
;This selects all revisions of articles from category '{{SQLcat}}'.
 
−
SELECT * FROM {{SQLprefix}}revision 
 
−
WHERE rev_page IN (SELECT  cl_from FROM {{SQLprefix}}categorylinks WHERE cl_to LIKE '{{SQLcat}}');
 
−
 
−
From the output of this information it can be seen that what we want is the rev_text_id that is largest for any rev_page.
 
−
 
−
;This selects all text from articles of category '{{SQLcat}}';
 
−
Select old_text FROM {{SQLprefix}}text
 
−
WHERE old_id IN (SELECT page_latest FROM {{SQLprefix}}page WHERE page_id IN (SELECT  cl_from FROM {{SQLprefix}}categorylinks WHERE cl_to LIKE '{{SQLcat}}'));
 
−
 
−
The query is extremely slow taking up to 3 seconds due to multiple nesting of SELECT statements.
 
−
the following optimization by joining the two inner most nested SELECT statements speeds up the query by at least an order of magnitude.
 
−
SELECT old_text FROM {{SQLprefix}}text
 
−
WHERE old_id IN (SELECT page_latest FROM {{SQLprefix}}page,{{SQLprefix}}categorylinks WHERE cl_to = '{{SQLcat}}' AND cl_from = page_id);
 
−
 
−
==Selecting the most recent revision of all articles==
 
−
Select old_text FROM {{SQLprefix}}text
 
−
WHERE old_id IN (SELECT page_latest FROM {{SQLprefix}}page);
 
  
 
== Some SQL queries executable by admin (need fixing to work in new MW1.9.3 environment) ==
 
== Some SQL queries executable by admin (need fixing to work in new MW1.9.3 environment) ==
Line 92: Line 57:
  
 
== Documentation ==
 
== Documentation ==
−
{{Info|1=A nice tutorial on SQL [http://www.sql-tutorial.com/ Syntax]}}
 
 
*[http://dev.mysql.com/doc/index.html Official MySQL Documentation]
 
*[http://dev.mysql.com/doc/index.html Official MySQL Documentation]
 
*[http://www.w3schools.com/sql/sql_join.asp W3C SQL Tutorial] [http://www.w3schools.com/sql/sql_quickref.asp (QuickRef)]
 
*[http://www.w3schools.com/sql/sql_join.asp W3C SQL Tutorial] [http://www.w3schools.com/sql/sql_quickref.asp (QuickRef)]
 +
*[http://www.sql-tutorial.com/ SQL-Tutorial.com]
  
 
== MySQL vs MSSQL ==
 
== MySQL vs MSSQL ==

Revision as of 03:51, 27 May 2008

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.

Common MediaWiki MySQL queries

Insert into interwiki

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

To delete entry; 

DELETE FROM interwiki WHERE iw_prefix LIKE "example";

Backup and compress DB & FS

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

tar cf - directory


{{{1}}}

Reset a password

UPDATE user SET user_password=md5(CONCAT('184-',md5('password'))) WHERE user_id=184;

Reset or set a page hit counter

UPDATE page SET page_counter=0 WHERE page_title='Main_Page';

Adjust user groups

INSERT INTO user_groups (ug_user,ug_group) VALUES(999,'sysop');

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

Documentation

MySQL vs MSSQL

MySQL News & Information

Towards SQL for P2P environments

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