Wikimedia Meta-Wiki

User:OrenBochman/The Database

< User:OrenBochman

The Database

edit

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.

Intro

edit

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

Best Practices

edit

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


Database Abstraction Layer

edit

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.

Wrapper functions

edit

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(/*...*/);

Wrapper function: select()

edit

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').

Simulated article

edit
The Sun is the star at the centre of our solar system. The Earth and other matter (including other planets, asteroids, meteoroids, comets and dust) orbit the Sun, which by itself accounts for more than 99% of the solar system’s mass. Energy from the Sun—in the form of sunlight, supports almost all life on Earth via photosynthesis, and, via heating from insolation—drives the Earth’s climate and weather. About 74% of the Sun’s mass is hydrogen, 25% is helium, and the rest is made up of trace quantities of heavier elements. The Sun is about 4.6 billion years old and is about halfway through its main-sequence evolution, during which nuclear fusion reactions in its core fuse hydrogen into helium. Each second, more than four million tonnes of matter are converted into energy within the Sun’s core, producing neutrinos and solar radiation. In about five billion years, the Sun will evolve into a red giant and then a white dwarf, creating a planetary nebula in the process. The Sun is a magnetically active star; it supports a strong, changing magnetic field that varies from year to year and reverses direction about every 11 years. The Sun’s magnetic field gives rise to many effects that are collectively called solar activity, including sunspots on the surface of the Sun, solar flares, and variations in the solar wind that carry material through the solar system. The effects of solar activity on Earth include auroras at moderate to high latitudes, and the disruption of radio communications and electric power. Solar activity is thought to have played a large role in the formation and evolution of the solar system, and strongly affects the structure of Earth’s outer atmosphere. Although it is the nearest star to Earth and has been intensively studied by scientists, many questions about the Sun remain unanswered; these include why its outer atmosphere has a temperature of over a million degrees K when its visible surface (the photosphere) has a temperature of just 6000 K. Current topics of scientific enquiry include the Sun’s regular cycle of sunspot activity, the physics and origin of solar flares and prominences, the magnetic interaction between the chromosphere and the corona, and the origin of the solar wind.

Test yourself

edit

Discussion

edit

Any questions or would you like to take the test?

Retrieved from "https://meta.wikimedia.org/w/index.php?title=User:OrenBochman/The_Database&oldid=11457108"