Difference between revisions of "SQL"

From Organic Design wiki
(→‎See also)
m
Line 13: Line 13:
 
== Dealing with duplicates on import ==
 
== Dealing with duplicates on import ==
 
See [[Backup]]
 
See [[Backup]]
−
 
−
== 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.
 
−
{{Code|<PHP>
 
−
SELECT user_id,user_namefromuser
 
−
WHEREuser_name="WikiSysop";
 
−
</PHP>
 
−
}}
 
−
 
−
Note that the password is a nested concatenation of both the md5 of the '''user_id''' combined with the md5 password.
 
−
 
−
Now update that users password:
 
−
{{Code|<PHP>
 
−
UPDATEuser
 
−
SETuser_password=md5(CONCAT('1-',md5('password')))
 
−
WHEREuser_id=1;
 
−
</PHP>
 
−
}}
 
−
 
−
Analternative 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''':
 
−
{{Code|<PHP>
 
−
UPDATEuser
 
−
SETuser_email="wikiSysops@email.address"
 
−
WHEREuser_id=1;
 
−
</PHP>
 
−
}}
 
−
 
−
===Reset or set a page hit counter===
 
−
UPDATEpageSETpage_counter=0WHEREpage_title='Main_Page';
 
−
 
−
===Adjust user groups===
 
−
INSERTINTOuser_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.pngMediaWikischema] for a description of the tables. Essentially '''categorylinks''' store the category member relationships, '''page''' identifies the title and metadata information, '''revision''' identifies all 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.
 
−
 
−
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.
 
  
 
== Documentation ==
 
== Documentation ==
Line 76: Line 39:
 
*[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''
 
*[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''
 +
 +
== Database procedures ==
 +
*[[Reset MySQL root password]]
 +
*[[Renaming MySQL tables and databases]]
 +
*[[Rename a MediaWiki database]]
 +
*[[Reset a wiki user password]]
 +
*[[Set a user preference]]
 +
*[[Manually backup a wiki]]
 +
*[[Add a wiki database]]
  
 
== See also ==
 
== See also ==
−
*[[Reset MySQL root password]]
 
 
*[https://mariadb.org/en/about/ MariaDB] ''- see [[PeerPedia]] Feb 2013 update for more info''
 
*[https://mariadb.org/en/about/ MariaDB] ''- see [[PeerPedia]] Feb 2013 update for more info''

Revision as of 22:42, 8 April 2015

Broom icon.svg The content of this article requires cleaning up to meet OD's quality standards. Check the wiki best practices for guidelines on improving article and categorisation quality.

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. We're now using MariaDB for our SQL server which is a drop-in replacement for MySQL but is fully open source and more efficient too.

Backup & restore

See backup for details about this as it's doesn't outside the SQL environment so doesn't belong in this article. But don't forget to set the default character set to the same as the dump used when you import the data!

Fixing crashed tables

To fix a crashed table just use repair TABLENAME from the MySQL command line, or to automatically scan all tables and fix them run the following from the shell.

<bash>mysqlcheck --auto-repair DBNAME -u USER -p</bash>

Dealing with duplicates on import

See Backup

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

Database procedures

See also

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