From d0cda6472332abf16b01b48401d2106936df7c9d Mon Sep 17 00:00:00 2001 From: River Tarnell Date: Sun, 11 Mar 2007 04:00:43 +0000 Subject: [PATCH] new files for oracle --- includes/DatabaseOracle.php | 644 ++++++++++++++++++++++++++++++++++++ maintenance/ora/tables.sql | 427 ++++++++++++++++++++++++ 2 files changed, 1071 insertions(+) create mode 100644 includes/DatabaseOracle.php create mode 100644 maintenance/ora/tables.sql diff --git a/includes/DatabaseOracle.php b/includes/DatabaseOracle.php new file mode 100644 index 0000000000..7f3601bf3a --- /dev/null +++ b/includes/DatabaseOracle.php @@ -0,0 +1,644 @@ +mData = $data; + } + + function getData() { + return $this->mData; + } +}; + +/* + * The oci8 extension is fairly weak and doesn't support oci_num_rows, among + * other things. We use a wrapper class to handle that and other + * Oracle-specific bits, like converting column names back to lowercase. + */ +class ORAResult { + private $rows; + private $cursor; + private $stmt; + private $nrows; + private $db; + + function __construct(&$db, $stmt) { + $this->db =& $db; + if (($this->nrows = oci_fetch_all($stmt, $this->rows, 0, -1, OCI_FETCHSTATEMENT_BY_ROW | OCI_NUM)) === false) { + $e = oci_error($stmt); + $db->reportQueryError('', $e['message'], $e['code']); + return; + } + + $this->cursor = 0; + $this->stmt = $stmt; + } + + function free() { + oci_free_statement($this->stmt); + } + + function seek($row) { + $this->cursor = min($row, $this->nrows); + } + + function numRows() { + return $this->nrows; + } + + function numFields() { + return oci_num_fields($this->stmt); + } + + function fetchObject() { + if ($this->cursor >= $this->nrows) + return false; + + $row = $this->rows[$this->cursor++]; + $ret = new stdClass(); + foreach ($row as $k => $v) { + $lc = strtolower(oci_field_name($this->stmt, $k + 1)); + $ret->$lc = $v; + } + + return $ret; + } + + function fetchAssoc() { + if ($this->cursor >= $this->nrows) + return false; + + $row = $this->rows[$this->cursor++]; + $ret = array(); + foreach ($row as $k => $v) { + $lc = strtolower(oci_field_name($this->stmt, $k + 1)); + $ret[$lc] = $v; + $ret[$k] = $v; + } + return $ret; + } +}; + +class DatabaseOracle extends Database { + var $mInsertId = NULL; + var $mLastResult = NULL; + var $numeric_version = NULL; + var $lastResult = null; + var $cursor = 0; + var $mAffectedRows; + + function DatabaseOracle($server = false, $user = false, $password = false, $dbName = false, + $failFunction = false, $flags = 0 ) + { + + global $wgOut; + # Can't get a reference if it hasn't been set yet + if ( !isset( $wgOut ) ) { + $wgOut = NULL; + } + $this->mOut =& $wgOut; + $this->mFailFunction = $failFunction; + $this->mCascadingDeletes = true; + $this->mCleanupTriggers = true; + $this->mStrictIPs = true; + $this->mFlags = $flags; + $this->open( $server, $user, $password, $dbName); + + } + + function realTimestamps() { + return true; + } + + function implicitGroupby() { + return false; + } + + function searchableIPs() { + return true; + } + + static function newFromParams( $server = false, $user = false, $password = false, $dbName = false, + $failFunction = false, $flags = 0) + { + return new DatabaseOracle( $server, $user, $password, $dbName, $failFunction, $flags ); + } + + /** + * Usually aborts on failure + * If the failFunction is set to a non-zero integer, returns success + */ + function open( $server, $user, $password, $dbName ) { + # Test for Postgres support, to avoid suppressed fatal error + if ( !function_exists( 'oci_connect' ) ) { + throw new DBConnectionError( $this, "Oracle functions missing, have you compiled PHP with the --with-oci8 option?\n (Note: if you recently installed PHP, you may need to restart your webserver and database)\n" ); + } + + + $this->close(); + $this->mServer = $server; + $this->mUser = $user; + $this->mPassword = $password; + $this->mDBname = $dbName; + + if (!strlen($user)) { ## e.g. the class is being loaded + return; + } + + error_reporting( E_ALL ); + $this->mConn = oci_connect($user, $password, $dbName); + + if ($this->mConn == false) { + wfDebug("DB connection error\n"); + wfDebug("Server: $server, Database: $dbName, User: $user, Password: " . substr( $password, 0, 3 ) . "...\n"); + wfDebug($this->lastError()."\n"); + return false; + } + + $this->mOpened = true; + return $this->mConn; + } + + /** + * Closes a database connection, if it is open + * Returns success, true if already closed + */ + function close() { + $this->mOpened = false; + if ( $this->mConn ) { + return oci_close( $this->mConn ); + } else { + return true; + } + } + + function execFlags() { + return $this->mTrxLevel ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS; + } + + function doQuery($sql) { + wfDebug("SQL: [$sql]\n"); + $this->mLastResult = $stmt = oci_parse($this->mConn, $sql); + if (oci_execute($stmt, $this->execFlags()) == false) { + $e = oci_error($stmt); + $this->reportQueryError($sql, $e['message'], $e['code']); + } + if (oci_statement_type($stmt) == "SELECT") + return new ORAResult($this, $stmt); + else { + $this->mAffectedRows = oci_num_rows($stmt); + return true; + } + } + + function queryIgnore($sql, $fname = '') { + return $this->query($sql, $fname, true); + } + + function freeResult($res) { + $res->free(); + } + + function fetchObject($res) { + return $res->fetchObject(); + } + + function fetchRow($res) { + return $res->fetchAssoc(); + } + + function numRows($res) { + return $res->numRows(); + } + + function numFields($res) { + return $res->numFields(); + } + + function fieldName($stmt, $n) { + return pg_field_name($stmt, $n); + } + + /** + * This must be called after nextSequenceVal + */ + function insertId() { + return $this->mInsertId; + } + + function dataSeek($res, $row) { + $res->seek($row); + } + + function lastError() { + if ($this->mConn === false) + $e = oci_error(); + else + $e = oci_error($this->mConn); + return $e['message']; + } + + function lastErrno() { + if ($this->mConn === false) + $e = oci_error(); + else + $e = oci_error($this->mConn); + return $e['code']; + } + + function affectedRows() { + return $this->mAffectedRows; + } + + /** + * Returns information about an index + * If errors are explicitly ignored, returns NULL on failure + */ + function indexInfo( $table, $index, $fname = 'Database::indexExists' ) { + return false; + } + + function indexUnique ($table, $index, $fname = 'Database::indexUnique' ) { + return false; + } + + function insert( $table, $a, $fname = 'Database::insert', $options = array() ) { + if (!is_array($options)) + $options = array($options); + + if (in_array('IGNORE', $options)) + $oldIgnore = $this->ignoreErrors(true); + + # IGNORE is performed using single-row inserts, ignoring errors in each + # FIXME: need some way to distiguish between key collision and other types of error + //$oldIgnore = $this->ignoreErrors(true); + if (!is_array(reset($a))) { + $a = array($a); + } + foreach ($a as $row) { + $this->insertOneRow($table, $row, $fname); + } + //$this->ignoreErrors($oldIgnore); + $retVal = true; + + if (in_array('IGNORE', $options)) + $this->ignoreErrors($oldIgnore); + + return $retVal; + } + + function insertOneRow($table, $row, $fname) { + // "INSERT INTO tables (a, b, c)" + $sql = "INSERT INTO " . $this->tableName($table) . " (" . join(',', array_keys($row)) . ')'; + $sql .= " VALUES ("; + + // for each value, append ":key" + $first = true; + foreach ($row as $col => $val) { + if (is_object($val)) + $what = "EMPTY_BLOB()"; + else + $what = ":$col"; + + if ($first) + $sql .= "$what"; + else + $sql.= ", $what"; + $first = false; + } + $sql .= ")"; + + $stmt = oci_parse($this->mConn, $sql); + foreach ($row as $col => $val) { + if (!is_object($val)) { + if (oci_bind_by_name($stmt, ":$col", $row[$col]) === false) + $this->reportQueryError($this->lastErrno(), $this->lastError(), $sql, __METHOD__); + } + } + + if (oci_execute($stmt, $this->execFlags()) === false) { + $e = oci_error($stmt); + $this->reportQueryError($e['message'], $e['code'], $sql, __METHOD__); + } + oci_free_statement($stmt); + } + + function tableName( $name ) { + # Replace reserved words with better ones + switch( $name ) { + case 'user': + return 'mwuser'; + case 'text': + return 'pagecontent'; + default: + return $name; + } + } + + /** + * Return the next in a sequence, save the value for retrieval via insertId() + */ + function nextSequenceValue($seqName) { + $res = $this->query("SELECT $seqName.nextval FROM dual"); + $row = $this->fetchRow($res); + $this->mInsertId = $row[0]; + $this->freeResult($res); + return $this->mInsertId; + } + + /** + * ORacle does not have a "USE INDEX" clause, so return an empty string + */ + function useIndexClause($index) { + return ''; + } + + # REPLACE query wrapper + # Oracle simulates this with a DELETE followed by INSERT + # $row is the row to insert, an associative array + # $uniqueIndexes is an array of indexes. Each element may be either a + # field name or an array of field names + # + # It may be more efficient to leave off unique indexes which are unlikely to collide. + # However if you do this, you run the risk of encountering errors which wouldn't have + # occurred in MySQL + function replace( $table, $uniqueIndexes, $rows, $fname = 'Database::replace' ) { + $table = $this->tableName($table); + + if (count($rows)==0) { + return; + } + + # Single row case + if (!is_array(reset($rows))) { + $rows = array($rows); + } + + foreach( $rows as $row ) { + # Delete rows which collide + if ( $uniqueIndexes ) { + $sql = "DELETE FROM $table WHERE "; + $first = true; + foreach ( $uniqueIndexes as $index ) { + if ( $first ) { + $first = false; + $sql .= "("; + } else { + $sql .= ') OR ('; + } + if ( is_array( $index ) ) { + $first2 = true; + foreach ( $index as $col ) { + if ( $first2 ) { + $first2 = false; + } else { + $sql .= ' AND '; + } + $sql .= $col.'=' . $this->addQuotes( $row[$col] ); + } + } else { + $sql .= $index.'=' . $this->addQuotes( $row[$index] ); + } + } + $sql .= ')'; + $this->query( $sql, $fname ); + } + + # Now insert the row + $sql = "INSERT INTO $table (" . $this->makeList( array_keys( $row ), LIST_NAMES ) .') VALUES (' . + $this->makeList( $row, LIST_COMMA ) . ')'; + $this->query($sql, $fname); + } + } + + # DELETE where the condition is a join + function deleteJoin( $delTable, $joinTable, $delVar, $joinVar, $conds, $fname = "Database::deleteJoin" ) { + if ( !$conds ) { + throw new DBUnexpectedError($this, 'Database::deleteJoin() called with empty $conds' ); + } + + $delTable = $this->tableName( $delTable ); + $joinTable = $this->tableName( $joinTable ); + $sql = "DELETE FROM $delTable WHERE $delVar IN (SELECT $joinVar FROM $joinTable "; + if ( $conds != '*' ) { + $sql .= 'WHERE ' . $this->makeList( $conds, LIST_AND ); + } + $sql .= ')'; + + $this->query( $sql, $fname ); + } + + # Returns the size of a text field, or -1 for "unlimited" + function textFieldSize( $table, $field ) { + $table = $this->tableName( $table ); + $sql = "SELECT t.typname as ftype,a.atttypmod as size + FROM pg_class c, pg_attribute a, pg_type t + WHERE relname='$table' AND a.attrelid=c.oid AND + a.atttypid=t.oid and a.attname='$field'"; + $res =$this->query($sql); + $row=$this->fetchObject($res); + if ($row->ftype=="varchar") { + $size=$row->size-4; + } else { + $size=$row->size; + } + $this->freeResult( $res ); + return $size; + } + + function lowPriorityOption() { + return ''; + } + + function limitResult($sql, $limit, $offset) { + if ($offset === false) + $offset = 0; + return "SELECT * FROM ($sql) WHERE rownum >= (1 + $offset) AND rownum < 1 + $limit + $offset"; + } + + /** + * Returns an SQL expression for a simple conditional. + * Uses CASE on Oracle + * + * @param string $cond SQL expression which will result in a boolean value + * @param string $trueVal SQL expression to return if true + * @param string $falseVal SQL expression to return if false + * @return string SQL fragment + */ + function conditional( $cond, $trueVal, $falseVal ) { + return " (CASE WHEN $cond THEN $trueVal ELSE $falseVal END) "; + } + + function wasDeadlock() { + return $this->lastErrno() == 'OCI-00060'; + } + + function timestamp($ts = 0) { + return wfTimestamp(TS_ORACLE, $ts); + } + + /** + * Return aggregated value function call + */ + function aggregateValue ($valuedata,$valuename='value') { + return $valuedata; + } + + function reportQueryError($error, $errno, $sql, $fname, $tempIgnore = false) { + # Ignore errors during error handling to avoid infinite recursion + $ignore = $this->ignoreErrors(true); + ++$this->mErrorCount; + + if ($ignore || $tempIgnore) { + wfDebug("SQL ERROR (ignored): $error\n"); + $this->ignoreErrors( $ignore ); + } + else { + $message = "A database error has occurred\n" . + "Query: $sql\n" . + "Function: $fname\n" . + "Error: $errno $error\n"; + throw new DBUnexpectedError($this, $message); + } + } + + /** + * @return string wikitext of a link to the server software's web site + */ + function getSoftwareLink() { + return "[http://www.oracle.com/ Oracle]"; + } + + /** + * @return string Version information from the database + */ + function getServerVersion() { + return oci_server_version($this->mConn); + } + + /** + * Query whether a given table exists (in the given schema, or the default mw one if not given) + */ + function tableExists($table) { + $etable= $this->addQuotes($table); + $SQL = "SELECT 1 FROM user_tables WHERE table_name='$etable'"; + $res = $this->query($SQL); + $count = $res ? oci_num_rows($res) : 0; + if ($res) + $this->freeResult($res); + return $count; + } + + /** + * Query whether a given column exists in the mediawiki schema + */ + function fieldExists( $table, $field ) { + return true; // XXX + } + + function fieldInfo( $table, $field ) { + return false; // XXX + } + + function begin( $fname = '' ) { + $this->mTrxLevel = 1; + } + function immediateCommit( $fname = '' ) { + return true; + } + function commit( $fname = '' ) { + oci_commit($this->mConn); + $this->mTrxLevel = 0; + } + + /* Not even sure why this is used in the main codebase... */ + function limitResultForUpdate($sql, $num) { + return $sql; + } + + function strencode($s) { + return str_replace("'", "''", $s); + } + + function encodeBlob($b) { + return new ORABlob($b); + } + function decodeBlob($b) { + return $b; //return $b->load(); + } + + function addQuotes( $s ) { + return "'" . $this->strencode($s) . "'"; + } + + function quote_ident( $s ) { + return $s; + } + + /* For now, does nothing */ + function selectDB( $db ) { + return true; + } + + /** + * Returns an optional USE INDEX clause to go after the table, and a + * string to go at the end of the query + * + * @private + * + * @param array $options an associative array of options to be turned into + * an SQL query, valid keys are listed in the function. + * @return array + */ + function makeSelectOptions( $options ) { + $tailOpts = ''; + $startOpts = ''; + + $noKeyOptions = array(); + foreach ( $options as $key => $option ) { + if ( is_numeric( $key ) ) { + $noKeyOptions[$option] = true; + } + } + + if ( isset( $options['GROUP BY'] ) ) $tailOpts .= " GROUP BY {$options['GROUP BY']}"; + if ( isset( $options['ORDER BY'] ) ) $tailOpts .= " ORDER BY {$options['ORDER BY']}"; + + if (isset($options['LIMIT'])) { + // $tailOpts .= $this->limitResult('', $options['LIMIT'], + // isset($options['OFFSET']) ? $options['OFFSET'] + // : false); + } + + if ( isset( $noKeyOptions['FOR UPDATE'] ) ) $tailOpts .= ' FOR UPDATE'; + if ( isset( $noKeyOptions['LOCK IN SHARE MODE'] ) ) $tailOpts .= ' LOCK IN SHARE MODE'; + if ( isset( $noKeyOptions['DISTINCT'] ) && isset( $noKeyOptions['DISTINCTROW'] ) ) $startOpts .= 'DISTINCT'; + + if ( isset( $options['USE INDEX'] ) && ! is_array( $options['USE INDEX'] ) ) { + $useIndex = $this->useIndexClause( $options['USE INDEX'] ); + } else { + $useIndex = ''; + } + + return array( $startOpts, $useIndex, $tailOpts ); + } + + public function setTimeout( $timeout ) { + /// @fixme no-op + } + + function ping() { + wfDebug( "Function ping() not written for DatabasePostgres.php yet"); + return true; + } + + +} // end DatabaseOracle class + +?> diff --git a/maintenance/ora/tables.sql b/maintenance/ora/tables.sql new file mode 100644 index 0000000000..4cf034c313 --- /dev/null +++ b/maintenance/ora/tables.sql @@ -0,0 +1,427 @@ +-- SQL to create the initial tables for the MediaWiki database. +-- This is read and executed by the install script; you should +-- not have to run it by itself unless doing a manual install. +-- This is the Oracle version (based on PostgreSQL schema). +-- For information about each table, please see the notes in maintenance/tables.sql + +CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0; + +CREATE TABLE mwuser ( -- replace reserved word 'user' + user_id INTEGER NOT NULL PRIMARY KEY, + user_name VARCHAR(255) NOT NULL UNIQUE, + user_real_name CLOB, + user_password CLOB, + user_newpassword CLOB, + user_newpass_time TIMESTAMP WITH TIME ZONE, + user_token CHAR(32), + user_email CLOB, + user_email_token CHAR(32), + user_email_token_expires TIMESTAMP WITH TIME ZONE, + user_email_authenticated TIMESTAMP WITH TIME ZONE, + user_options CLOB, + user_touched TIMESTAMP WITH TIME ZONE, + user_registration TIMESTAMP WITH TIME ZONE, + user_editcount INTEGER +); +CREATE INDEX user_email_token_idx ON mwuser (user_email_token); + +-- Create a dummy user to satisfy fk contraints especially with revisions +INSERT INTO mwuser + VALUES (user_user_id_seq.nextval,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, '', current_timestamp, current_timestamp, 0); + +CREATE TABLE user_groups ( + ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + ug_group CHAR(16) NOT NULL +); +CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group); + +CREATE TABLE user_newtalk ( + user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + user_ip VARCHAR(40) NULL +); +CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id); +CREATE INDEX user_newtalk_ip_idx ON user_newtalk (user_ip); + +CREATE SEQUENCE page_page_id_seq; +CREATE TABLE page ( + page_id INTEGER NOT NULL PRIMARY KEY, + page_namespace SMALLINT NOT NULL, + page_title VARCHAR(255) NOT NULL, + page_restrictions CLOB, + page_counter INTEGER DEFAULT 0 NOT NULL, + page_is_redirect CHAR DEFAULT 0 NOT NULL, + page_is_new CHAR DEFAULT 0 NOT NULL, + page_random NUMERIC(15,14) NOT NULL, + page_touched TIMESTAMP WITH TIME ZONE, + page_latest INTEGER NOT NULL, -- FK? + page_len INTEGER NOT NULL +); +CREATE UNIQUE INDEX page_unique_name ON page (page_namespace, page_title); +CREATE INDEX page_random_idx ON page (page_random); +CREATE INDEX page_len_idx ON page (page_len); + +CREATE TRIGGER page_set_random BEFORE INSERT ON page + FOR EACH ROW WHEN (new.page_random IS NULL) + BEGIN + SELECT dbms_random.value INTO :new.page_random FROM dual; + END; +/ + +CREATE SEQUENCE rev_rev_id_val; +CREATE TABLE revision ( + rev_id INTEGER NOT NULL PRIMARY KEY, + rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, + rev_text_id INTEGER NULL, -- FK + rev_comment CLOB, + rev_user INTEGER NOT NULL REFERENCES mwuser(user_id), + rev_user_text VARCHAR(255) NOT NULL, + rev_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, + rev_minor_edit CHAR DEFAULT '0' NOT NULL, + rev_deleted CHAR DEFAULT '0' NOT NULL, + rev_len INTEGER NULL, + rev_parent_id INTEGER DEFAULT NULL +); +CREATE UNIQUE INDEX revision_unique ON revision (rev_page, rev_id); +CREATE INDEX rev_text_id_idx ON revision (rev_text_id); +CREATE INDEX rev_timestamp_idx ON revision (rev_timestamp); +CREATE INDEX rev_user_idx ON revision (rev_user); +CREATE INDEX rev_user_text_idx ON revision (rev_user_text); + + +CREATE SEQUENCE text_old_id_val; +CREATE TABLE pagecontent ( -- replaces reserved word 'text' + old_id INTEGER NOT NULL PRIMARY KEY, + old_text CLOB, + old_flags CLOB +); + + +CREATE SEQUENCE pr_id_val; +CREATE TABLE page_restrictions ( + pr_id INTEGER NOT NULL UNIQUE, + pr_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, + pr_type VARCHAR(255) NOT NULL, + pr_level VARCHAR(255) NOT NULL, + pr_cascade SMALLINT NOT NULL, + pr_user INTEGER NULL, + pr_expiry TIMESTAMP WITH TIME ZONE NULL +); +ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (pr_page,pr_type); + +CREATE TABLE archive ( + ar_namespace SMALLINT NOT NULL, + ar_title VARCHAR(255) NOT NULL, + ar_text CLOB, + ar_comment CLOB, + ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + ar_user_text CLOB NOT NULL, + ar_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, + ar_minor_edit CHAR DEFAULT '0' NOT NULL, + ar_flags CLOB, + ar_rev_id INTEGER, + ar_text_id INTEGER, + ar_deleted INTEGER DEFAULT '0' NOT NULL +); +CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); + +CREATE TABLE redirect ( + rd_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + rd_namespace SMALLINT NOT NULL, + rd_title VARCHAR(255) NOT NULL +); +CREATE INDEX redirect_ns_title ON redirect (rd_namespace,rd_title,rd_from); + + +CREATE TABLE pagelinks ( + pl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + pl_namespace SMALLINT NOT NULL, + pl_title VARCHAR(255) NOT NULL +); +CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title); + +CREATE TABLE templatelinks ( + tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + tl_namespace INTEGER NOT NULL, + tl_title VARCHAR(255) NOT NULL +); +CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from); + +CREATE TABLE imagelinks ( + il_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + il_to VARCHAR(255) NOT NULL +); +CREATE UNIQUE INDEX il_from ON imagelinks (il_to,il_from); + +CREATE TABLE categorylinks ( + cl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + cl_to VARCHAR(255) NOT NULL, + cl_sortkey VARCHAR(86), + cl_timestamp TIMESTAMP WITH TIME ZONE NOT NULL +); +CREATE UNIQUE INDEX cl_from ON categorylinks (cl_from, cl_to); +CREATE INDEX cl_sortkey ON categorylinks (cl_to, cl_sortkey); + +CREATE TABLE externallinks ( + el_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, + el_to CLOB NOT NULL, + el_index CLOB NOT NULL +); +-- XXX CREATE INDEX externallinks_from_to ON externallinks (el_from,el_to); +-- XXX CREATE INDEX externallinks_index ON externallinks (el_index); + +CREATE TABLE langlinks ( + ll_from INTEGER NOT NULL REFERENCES page (page_id) ON DELETE CASCADE, + ll_lang VARCHAR(10), + ll_title VARCHAR(255) +); +CREATE UNIQUE INDEX langlinks_unique ON langlinks (ll_from,ll_lang); +CREATE INDEX langlinks_lang_title ON langlinks (ll_lang,ll_title); + + +CREATE TABLE site_stats ( + ss_row_id INTEGER NOT NULL UNIQUE, + ss_total_views INTEGER DEFAULT 0, + ss_total_edits INTEGER DEFAULT 0, + ss_good_articles INTEGER DEFAULT 0, + ss_total_pages INTEGER DEFAULT -1, + ss_users INTEGER DEFAULT -1, + ss_admins INTEGER DEFAULT -1, + ss_images INTEGER DEFAULT 0 +); + +CREATE TABLE hitcounter ( + hc_id INTEGER NOT NULL +); + + +CREATE SEQUENCE ipblocks_ipb_id_val; +CREATE TABLE ipblocks ( + ipb_id INTEGER NOT NULL PRIMARY KEY, + ipb_address VARCHAR(255) NULL, + ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + ipb_reason VARCHAR(255) NOT NULL, + ipb_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, + ipb_auto CHAR DEFAULT '0' NOT NULL, + ipb_anon_only CHAR DEFAULT '0' NOT NULL, + ipb_create_account CHAR DEFAULT '1' NOT NULL, + ipb_enable_autoblock CHAR DEFAULT '1' NOT NULL, + ipb_expiry TIMESTAMP WITH TIME ZONE NOT NULL, + ipb_range_start CHAR(8), + ipb_range_end CHAR(8), + ipb_deleted INTEGER DEFAULT '0' NOT NULL +); +CREATE INDEX ipb_address ON ipblocks (ipb_address); +CREATE INDEX ipb_user ON ipblocks (ipb_user); +CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end); + + +CREATE TABLE image ( + img_name VARCHAR(255) NOT NULL PRIMARY KEY, + img_size INTEGER NOT NULL, + img_width INTEGER NOT NULL, + img_height INTEGER NOT NULL, + img_metadata CLOB, + img_bits SMALLINT, + img_media_type CLOB, + img_major_mime CLOB DEFAULT 'unknown', + img_minor_mime CLOB DEFAULT 'unknown', + img_description CLOB NOT NULL, + img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + img_user_text CLOB NOT NULL, + img_timestamp TIMESTAMP WITH TIME ZONE +); +CREATE INDEX img_size_idx ON image (img_size); +CREATE INDEX img_timestamp_idx ON image (img_timestamp); + +CREATE TABLE oldimage ( + oi_name VARCHAR(255) NOT NULL REFERENCES image(img_name), + oi_archive_name VARCHAR(255) NOT NULL, + oi_size INTEGER NOT NULL, + oi_width INTEGER NOT NULL, + oi_height INTEGER NOT NULL, + oi_bits SMALLINT NOT NULL, + oi_description CLOB, + oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + oi_user_text CLOB NOT NULL, + oi_timestamp TIMESTAMP WITH TIME ZONE NOT NULL +); +CREATE INDEX oi_name ON oldimage (oi_name); + +CREATE SEQUENCE filearchive_fa_id_seq; +CREATE TABLE filearchive ( + fa_id INTEGER NOT NULL PRIMARY KEY, + fa_name VARCHAR(255) NOT NULL, + fa_archive_name VARCHAR(255), + fa_storage_group VARCHAR(16), + fa_storage_key CHAR(64), + fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + fa_deleted_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, + fa_deleted_reason CLOB, + fa_size SMALLINT NOT NULL, + fa_width SMALLINT NOT NULL, + fa_height SMALLINT NOT NULL, + fa_metadata CLOB, + fa_bits SMALLINT, + fa_media_type CLOB, + fa_major_mime CLOB DEFAULT 'unknown', + fa_minor_mime CLOB DEFAULT 'unknown', + fa_description CLOB NOT NULL, + fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + fa_user_text CLOB NOT NULL, + fa_timestamp TIMESTAMP WITH TIME ZONE, + fa_deleted INTEGER DEFAULT '0' NOT NULL +); +CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp); +CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key); +CREATE INDEX fa_notime ON filearchive (fa_deleted_timestamp); +CREATE INDEX fa_nouser ON filearchive (fa_deleted_user); + + +CREATE SEQUENCE rc_rc_id_seq; +CREATE TABLE recentchanges ( + rc_id INTEGER NOT NULL PRIMARY KEY, + rc_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, + rc_cur_time TIMESTAMP WITH TIME ZONE NOT NULL, + rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + rc_user_text CLOB NOT NULL, + rc_namespace SMALLINT NOT NULL, + rc_title VARCHAR(255) NOT NULL, + rc_comment VARCHAR(255), + rc_minor CHAR DEFAULT '0' NOT NULL, + rc_bot CHAR DEFAULT '0' NOT NULL, + rc_new CHAR DEFAULT '0' NOT NULL, + rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL, + rc_this_oldid INTEGER NOT NULL, + rc_last_oldid INTEGER NOT NULL, + rc_type CHAR DEFAULT '0' NOT NULL, + rc_moved_to_ns SMALLINT, + rc_moved_to_title CLOB, + rc_patrolled CHAR DEFAULT '0' NOT NULL, + rc_ip VARCHAR(15), + rc_old_len INTEGER, + rc_new_len INTEGER, + rc_deleted INTEGER DEFAULT '0' NOT NULL, + rc_logid INTEGER DEFAULT '0' NOT NULL, + rc_log_type CLOB, + rc_log_action CLOB, + rc_params CLOB +); +CREATE INDEX rc_timestamp ON recentchanges (rc_timestamp); +CREATE INDEX rc_namespace_title ON recentchanges (rc_namespace, rc_title); +CREATE INDEX rc_cur_id ON recentchanges (rc_cur_id); +CREATE INDEX new_name_timestamp ON recentchanges (rc_new, rc_namespace, rc_timestamp); +CREATE INDEX rc_ip ON recentchanges (rc_ip); + + +CREATE TABLE watchlist ( + wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + wl_namespace SMALLINT DEFAULT 0 NOT NULL, + wl_title VARCHAR(255) NOT NULL, + wl_notificationtimestamp TIMESTAMP WITH TIME ZONE +); +CREATE UNIQUE INDEX wl_user_namespace_title ON watchlist (wl_namespace, wl_title, wl_user); + + +CREATE TABLE math ( + math_inputhash VARCHAR(16) NOT NULL UNIQUE, + math_outputhash VARCHAR(16) NOT NULL, + math_html_conservativeness SMALLINT NOT NULL, + math_html CLOB, + math_mathml CLOB +); + + +CREATE TABLE interwiki ( + iw_prefix CHAR(32) NOT NULL UNIQUE, + iw_url CHAR(127) NOT NULL, + iw_local CHAR NOT NULL, + iw_trans CHAR DEFAULT '0' NOT NULL +); + +CREATE TABLE querycache ( + qc_type CHAR(32) NOT NULL, + qc_value SMALLINT NOT NULL, + qc_namespace SMALLINT NOT NULL, + qc_title CHAR(255) NOT NULL +); +CREATE INDEX querycache_type_value ON querycache (qc_type, qc_value); + +CREATE TABLE querycache_info ( + qci_type VARCHAR(32) UNIQUE, + qci_timestamp TIMESTAMP WITH TIME ZONE NULL +); + +CREATE TABLE querycachetwo ( + qcc_type CHAR(32) NOT NULL, + qcc_value SMALLINT DEFAULT 0 NOT NULL, + qcc_namespace INTEGER DEFAULT 0 NOT NULL, + qcc_title CHAR(255) DEFAULT '' NOT NULL, + qcc_namespacetwo INTEGER DEFAULT 0 NOT NULL, + qcc_titletwo CHAR(255) DEFAULT '' NOT NULL +); +CREATE INDEX querycachetwo_type_value ON querycachetwo (qcc_type, qcc_value); +CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,qcc_title); +CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo); + + +CREATE TABLE objectcache ( + keyname CHAR(255) UNIQUE, + value BLOB, + exptime TIMESTAMP WITH TIME ZONE NOT NULL +); +CREATE INDEX objectcacache_exptime ON objectcache (exptime); + +CREATE TABLE transcache ( + tc_url VARCHAR(255) NOT NULL UNIQUE, + tc_contents CLOB NOT NULL, + tc_time TIMESTAMP WITH TIME ZONE NOT NULL +); + + +CREATE SEQUENCE log_log_id_seq; +CREATE TABLE logging ( + log_type VARCHAR(10) NOT NULL, + log_action VARCHAR(10) NOT NULL, + log_timestamp TIMESTAMP WITH TIME ZONE NOT NULL, + log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL, + log_namespace SMALLINT NOT NULL, + log_title VARCHAR(255) NOT NULL, + log_comment VARCHAR(255), + log_params CLOB, + log_deleted INTEGER DEFAULT '0' NOT NULL, + log_id INTEGER NOT NULL PRIMARY KEY +); +CREATE INDEX logging_type_name ON logging (log_type, log_timestamp); +CREATE INDEX logging_user_time ON logging (log_timestamp, log_user); +CREATE INDEX logging_page_time ON logging (log_namespace, log_title, log_timestamp); + +CREATE SEQUENCE trackbacks_tb_id_seq; +CREATE TABLE trackbacks ( + tb_id INTEGER NOT NULL PRIMARY KEY, + tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE, + tb_title VARCHAR(255) NOT NULL, + tb_url VARCHAR(255) NOT NULL, + tb_ex CLOB, + tb_name VARCHAR(255) +); +CREATE INDEX trackback_page ON trackbacks (tb_page); + +CREATE SEQUENCE job_job_id_seq; +CREATE TABLE job ( + job_id INTEGER NOT NULL PRIMARY KEY, + job_cmd VARCHAR(255) NOT NULL, + job_namespace SMALLINT NOT NULL, + job_title VARCHAR(255) NOT NULL, + job_params CLOB NOT NULL +); +CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title); + +-- This table is not used unless profiling is turned on +--CREATE TABLE profiling ( +-- pf_count INTEGER DEFAULT 0 NOT NULL, +-- pf_time NUMERIC(18,10) DEFAULT 0 NOT NULL, +-- pf_name CLOB NOT NULL, +-- pf_server CLOB NULL +--); +--CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); -- 2.20.1