User:OrenBochman/The Database

This is an assignment for Simone's adoption program. You are welcome to edit this page if you notice any errors or have any additional information to add, but as a courtesy, please notify OrenBochman if you make any major changes to avoid any possible confusion between him and his adoptee(s). Thanks!

This unit is a quick review of the Database. Remember:

1. It explains the key contents of the database
2. What is in it and what is not.

thumb|right|200px|this is the database schema

The current MySQL diagram for any MediaWiki version—with extensive comments—can be found in the maintenance/tables.sql file.

See the latest version from SVN: maintenance/tables.sql.

The most important tables are probably page, revision, pagelinks and text.

The most important tables are:

• page
• revision
• pagelinks
• text

the page contains information on a page in cluding a pointer to the current revision page

the revision

Use the api to access data , do not just excecute sql via <>tomysql_query() or pg_send_query()

MediaWiki provides a database abstraction layer. Unless you are working on the abstraction layer, you should never directly call PHP's database functions (such as mysql_query() or pg_send_query().)

The abstraction layer is accessed by using the wfGetDB() function. For more detailed documentation on wfGetDB(), see the entry on wfGetDB() in the GlobalFunctions.php file reference.

Typically, wfGetDB() is called with a single parameter, which can be the DB_SLAVE (for read queries) or DB_MASTER (for write queries and read queries that need to have absolutely newest information) constant. The distinction between master and slave is important in a multi-database environment, such as Wikimedia. This function will return you a Database object that you can use to access the database. See the #Wrapper functions section below for what you can do with this Database object.

To make a read query, something like this usually suffices:

$dbr = wfGetDB( DB_SLAVE );
$res = $dbr->select( /* ...see docs... */ );
foreach( $res as $row ) {
    ...
}

For a write query, use something like:

$dbw = wfGetDB( DB_MASTER );
$dbw->insert( /* ...see docs... */ );

We use the convention $dbr for read and $dbw for write to help you keep track of whether the database object is a slave (read-only) or a master (read/write). If you write to a slave, the world will explode. Or to be precise, a subsequent write query which succeeded on the master may fail when replicated to the slave due to a unique key collision. Replication on the slave will stop and it may take hours to repair the database and get it back online. Setting read_only in my.cnf on the slave will avoid this scenario, but given the dire consequences, we prefer to have as many checks as possible.

We provide a query() function for raw SQL, but the wrapper functions like select() and insert() are usually more convenient. They can take care of things like table prefixes and escaping for you under some circumstances. If you really need to make your own SQL, please read the documentation for tableName() and addQuotes(). You will need both of them.

Another important reason to use the high level methods rather than constructing your own queries is to ensure that your code will run properly regardless of the database type. Currently there is MySQL and reasonable support for SQLite and PostgreSQL, also somewhat limited for Oracle and DB2, but there could be other databases in the future such as MSSQL or Firebird.

In the following, the available wrapper functions are listed. For a detailed description of the parameters of the wrapper functions, please refer to class DatabaseBase's docs. Particularly see DatabaseBase::select for an explanation of the $table, $vars, $conds, $fname, $options, and $join_conds parameters that are used by many of the other wrapper functions.

function select( $table, $vars, $conds = '', $fname = 'Database::select', $options = array() );
function selectRow( $table, $vars, $conds = '', $fname = 'Database::select', $options = array() );
function insert( $table, $a, $fname = 'Database::insert', $options = array() );
function insertSelect( $destTable, $srcTable, $varMap, $conds, $fname = 'Database::insertSelect', $insertOptions = array(), $selectOptions = array() );
function update( $table, $values, $conds, $fname = 'Database::update', $options = array() );
function delete( $table, $conds, $fname = 'Database::delete' );
function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = 'Database::deleteJoin' );
function buildLike(/*...*/);

The select() function provides the MediaWiki interface for a SELECT statement. The components of the SELECT statement are coded as parameters of the select() function. An example is

$dbr = wfGetDB( DB_SLAVE );
$res = $dbr->select(
    'category',                                   // $table
    array( 'cat_title', 'cat_pages' ),            // $vars (columns of the table)
    'cat_pages > 0',                              // $conds
    __METHOD__,                                   // $fname = 'Database::select',
    array( 'ORDER BY' => 'cat_title ASC' )        // $options = array()
);

This example corresponds to the query

SELECT cat_title, cat_pages FROM category WHERE cat_pages > 0 ORDER BY cat_title ASC

Arguments are either single values (such as 'category' and 'cat_pages > 0') or arrays, if more than one value is passed for an argument position (such as array('cat_pages > 0', $myNextCond)). If you pass in strings, you must manually use DatabaseBase::addQuotes() on your values as you construct the string, as the wrapper will not do this for you. The array construction for $conds is somewhat limited; it can only do equality relationships (i.e. WHERE key = 'value').

Any questions or would you like to take the test?