From faef1c92c28153400c682edccd78ab83be1d0314 Mon Sep 17 00:00:00 2001 From: Leons Petrazickis Date: Wed, 1 Sep 2010 18:14:15 +0000 Subject: [PATCH] DB2: Implemented prepared statements for INSERT and UPDATE to allow more than 32k of text data DB2: New LIST_SET_PREPARED mode for DatabaseIbm_db2::makeList() that handles ? tokens in prepared UPDATE statement DB2: General fixes for DB2 database schema DB2: Factored out foreign key definitions into foreignkeys.sql so that more can be added down the line without messing with the main schema DB2: Better limit and offset support --- includes/Defines.php | 1 + includes/GlobalFunctions.php | 2 + includes/db/DatabaseIbm_db2.php | 167 ++++++++++++++-------------- maintenance/ibm_db2/foreignkeys.sql | 107 ++++++++++++++++++ maintenance/ibm_db2/tables.sql | 76 +++---------- 5 files changed, 213 insertions(+), 140 deletions(-) create mode 100644 maintenance/ibm_db2/foreignkeys.sql diff --git a/includes/Defines.php b/includes/Defines.php index 5fa510c961..61e9559bce 100644 --- a/includes/Defines.php +++ b/includes/Defines.php @@ -192,6 +192,7 @@ define( 'LIST_AND', 1 ); define( 'LIST_SET', 2 ); define( 'LIST_NAMES', 3); define( 'LIST_OR', 4); +define( 'LIST_SET_PREPARED', 8); // List of (?, ?, ?) for DatabaseIbm_db2 /** * Unicode and normalisation related diff --git a/includes/GlobalFunctions.php b/includes/GlobalFunctions.php index b89ab2ffa1..a3b9a42c30 100644 --- a/includes/GlobalFunctions.php +++ b/includes/GlobalFunctions.php @@ -1980,6 +1980,8 @@ function wfTimestamp( $outputtype = TS_UNIX, $ts = 0 ) { # TS_POSTGRES } elseif ( preg_match( '/^(\d{4})\-(\d\d)\-(\d\d) (\d\d):(\d\d):(\d\d)\.*\d* GMT$/', $ts, $da ) ) { # TS_POSTGRES + } elseif (preg_match('/^(\d{4})\-(\d\d)\-(\d\d) (\d\d):(\d\d):(\d\d)\.\d\d\d$/',$ts,$da)) { + # TS_DB2 } elseif ( preg_match( '/^[A-Z][a-z]{2}, \d\d [A-Z][a-z]{2} \d{4} \d\d:\d\d:\d\d/', $ts ) ) { # TS_RFC2822 $uts = strtotime( $ts ); diff --git a/includes/db/DatabaseIbm_db2.php b/includes/db/DatabaseIbm_db2.php index 60cd817de9..550b6e5416 100644 --- a/includes/db/DatabaseIbm_db2.php +++ b/includes/db/DatabaseIbm_db2.php @@ -459,7 +459,7 @@ class DatabaseIbm_db2 extends DatabaseBase { * @param $string String: the relevant debug message */ private function installPrint($string) { - wfDebug("$string"); + wfDebug("$string\n"); if ($this->mMode == self::INSTALL_MODE) { print "
  • $string
    • "; flush(); @@ -634,16 +634,13 @@ class DatabaseIbm_db2 extends DatabaseBase { */ /*private*/ public function doQuery( $sql ) { - //print "
  • $sql
    • "; - // Switch into the correct namespace $this->applySchema(); $ret = db2_exec( $this->mConn, $sql, $this->mStmtOptions ); - if( !$ret ) { - print "
    ";
-			print $sql;
-			print "

    "; + if( $ret == FALSE ) { $error = db2_stmt_errormsg(); + $this->installPrint("
    $sql
    "); + $this->installPrint($error); throw new DBUnexpectedError($this, 'SQL error: ' . htmlspecialchars( $error ) ); } $this->mLastResult = $ret; @@ -748,6 +745,12 @@ EOF; } else { print " done"; } + $res = $this->sourceFile( "../maintenance/ibm_db2/foreignkeys.sql" ); + if ($res !== true) { + print " FAILED: " . htmlspecialchars( $res ) . ""; + } else { + print "
  • Foreign keys done
    • "; + } $res = null; // TODO: populate interwiki links @@ -881,67 +884,35 @@ EOF; * LIST_OR - ORed WHERE clause (without the WHERE) * LIST_SET - comma separated with field names, like a SET clause * LIST_NAMES - comma separated field names + * LIST_SET_PREPARED - like LIST_SET, except with ? tokens as values */ - public function makeList( $a, $mode = LIST_COMMA ) { + function makeList( $a, $mode = LIST_COMMA ) { if ( !is_array( $a ) ) { - throw new DBUnexpectedError( $this, 'Database::makeList called with incorrect parameters' ); + throw new DBUnexpectedError( $this, 'DatabaseBase::makeList called with incorrect parameters' ); } - - $first = true; - $list = ''; - foreach ( $a as $field => $value ) { - if ( !$first ) { - if ( $mode == LIST_AND ) { - $list .= ' AND '; - } elseif($mode == LIST_OR) { - $list .= ' OR '; - } else { - $list .= ','; - } - } else { - $first = false; - } - if ( ($mode == LIST_AND || $mode == LIST_OR) && is_numeric( $field ) ) { - $list .= "($value)"; - } elseif ( ($mode == LIST_SET) && is_numeric( $field ) ) { - $list .= "$value"; - } elseif ( ($mode == LIST_AND || $mode == LIST_OR) && is_array($value) ) { - if( count( $value ) == 0 ) { - throw new MWException( __METHOD__.': empty input' ); - } elseif( count( $value ) == 1 ) { - // Special-case single values, as IN isn't terribly efficient - // Don't necessarily assume the single key is 0; we don't - // enforce linear numeric ordering on other arrays here. - $value = array_values( $value ); - $list .= $field." = ".$this->addQuotes( $value[0] ); - } else { - $list .= $field." IN (".$this->makeList($value).") "; - } - } elseif( is_null($value) ) { - if ( $mode == LIST_AND || $mode == LIST_OR ) { - $list .= "$field IS "; - } elseif ( $mode == LIST_SET ) { - $list .= "$field = "; - } - $list .= 'NULL'; - } else { - if ( $mode == LIST_AND || $mode == LIST_OR || $mode == LIST_SET ) { - $list .= "$field = "; - } - if ( $mode == LIST_NAMES ) { - $list .= $value; - } - // Leo: Can't insert quoted numbers into numeric columns - // (?) Might cause other problems. May have to check column type before insertion. - else if ( is_numeric($value) ) { - $list .= $value; + + // if this is for a prepared UPDATE statement + // (this should be promoted to the parent class + // once other databases use prepared statements) + if ($mode == LIST_SET_PREPARED) { + $first = true; + $list = ''; + foreach ( $a as $field => $value ) { + if (!$first) { + $list .= ", $field = ?"; } else { - $list .= $this->addQuotes( $value ); + $list .= "( $field = ?"; + $first = false; } } + $list .= ')'; + + return $list; } - return $list; + + // otherwise, call the usual function + return parent::makeList( $a, $mode ); } /** @@ -956,10 +927,14 @@ EOF; throw new DBUnexpectedError( $this, "Invalid non-numeric limit passed to limitResult()\n" ); } if( $offset ) { - $this->installPrint("Offset parameter not supported in limitResult()\n"); + //$this->installPrint("Offset parameter not supported in limitResult()\n"); + if ( stripos($sql, 'where') === false ) { + return "$sql AND (ROWNUM BETWEEN $offset AND $offset+$limit)"; + } + else { + return "$sql WHERE (ROWNUM BETWEEN $offset AND $offset+$limit)"; + } } - // TODO implement proper offset handling - // idea: get all the rows between 0 and offset, advance cursor to offset return "$sql FETCH FIRST $limit ROWS ONLY "; } @@ -1027,8 +1002,8 @@ EOF; */ private function calcInsertId($table, $primaryKey, $stmt) { if ($primaryKey) { - $id_row = $this->fetchRow($stmt); - $this->mInsertId = $id_row[0]; + $this->mInsertId = db2_last_insert_id($this->mConn); + //$this->installPrint("Last $primaryKey for $table was $this->mInsertId"); } } @@ -1052,11 +1027,12 @@ EOF; // get database-specific table name (not used) $table = $this->tableName( $table ); // format options as an array - if ( !is_array( $options ) ) $options = array( $options ); + $options = IBM_DB2Helper::makeArray($options); // format args as an array of arrays if ( !( isset( $args[0] ) && is_array( $args[0] ) ) ) { $args = array($args); } + // prevent insertion of NULL into primary key columns list($args, $primaryKeys) = $this->removeNullPrimaryKeys($table, $args); // if there's only one primary key @@ -1081,27 +1057,29 @@ EOF; } $sql = "INSERT INTO $table (" . implode( ',', $keys ) . ') VALUES '; - switch($key_count) { - //case 0 impossible - case 1: - $sql .= '(?)'; - break; - default: - $sql .= '(?' . str_repeat(',?', $key_count-1) . ')'; - } - // add logic to read back the new primary key value - if ($primaryKey) { - $sql = "SELECT $primaryKey FROM FINAL TABLE($sql)"; + if ($key_count == 1) { + $sql .= '(?)'; + } else { + $sql .= '(?' . str_repeat(',?', $key_count-1) . ')'; } + //$this->installPrint("Preparing the following SQL:"); + //$this->installPrint("$sql"); + //$this->installPrint(print_r($args, true)); $stmt = $this->prepare($sql); // start a transaction/enter transaction mode $this->begin(); if ( !$ignore ) { + //$first = true; foreach ( $args as $row ) { + //$this->installPrint("Inserting " . print_r($row, true)); // insert each row into the database $res = $res & $this->execute($stmt, $row); + if (!$res) { + $this->installPrint("Last error:"); + $this->installPrint($this->lastError()); + } // get the last inserted value into a generated column $this->calcInsertId($table, $primaryKey, $stmt); } @@ -1118,8 +1096,14 @@ EOF; foreach ( $args as $row ) { $overhead = "SAVEPOINT $ignore ON ROLLBACK RETAIN CURSORS"; db2_exec($this->mConn, $overhead, $this->mStmtOptions); + //$this->installPrint("Inserting " . print_r($row, true)); $this->execute($stmt, $row); + //$this->installPrint(wfGetAllCallers()); + if (!$res2) { + $this->installPrint("Last error:"); + $this->installPrint($this->lastError()); + } // get the last inserted value into a generated column $this->calcInsertId($table, $primaryKey, $stmt); @@ -1139,6 +1123,7 @@ EOF; } // commit either way $this->commit(); + $this->freePrepared($stmt); return $res; } @@ -1187,11 +1172,20 @@ EOF; public function update( $table, $values, $conds, $fname = 'Database::update', $options = array() ) { $table = $this->tableName( $table ); $opts = $this->makeUpdateOptions( $options ); - $sql = "UPDATE $opts $table SET " . $this->makeList( $values, LIST_SET ); + $sql = "UPDATE $opts $table SET " . $this->makeList( $values, LIST_SET_PREPARED ); if ( $conds != '*' ) { $sql .= " WHERE " . $this->makeList( $conds, LIST_AND ); } - return $this->query( $sql, $fname ); + $stmt = $this->prepare( $sql ); + $this->installPrint("UPDATE: " . print_r($values, TRUE)); + // assuming for now that an array with string keys will work + // if not, convert to simple array first + $result = $this->execute( $stmt, $values ); + $this->freePrepared( $stmt ); + //$result = $this->query( $sql, $fname ); + // commit regardless of state + //$this->commit(); + return $result; } /** @@ -1208,7 +1202,10 @@ EOF; if ( $conds != '*' ) { $sql .= ' WHERE ' . $this->makeList( $conds, LIST_AND ); } - return $this->query( $sql, $fname ); + $result = $this->query( $sql, $fname ); + // commit regardless + //$this->commit(); + return $result; } /** @@ -1643,7 +1640,7 @@ SQL; * @return mixed */ public function decodeBlob($b) { - return $b->getData(); + return "$b"; } /** @@ -1791,3 +1788,11 @@ SQL; return 'BITOR('.$fieldLeft.', '.$fieldRight.')'; } } + +class IBM_DB2Helper { + public static function makeArray($maybeArray) { + if ( !is_array( $maybeArray ) ) $maybeArray = array( $maybeArray ); + + return $maybeArray; + } +} diff --git a/maintenance/ibm_db2/foreignkeys.sql b/maintenance/ibm_db2/foreignkeys.sql new file mode 100644 index 0000000000..88fe1613e6 --- /dev/null +++ b/maintenance/ibm_db2/foreignkeys.sql @@ -0,0 +1,107 @@ +-- good +ALTER TABLE user_groups ADD CONSTRAINT USER_GROUPS_FK1 FOREIGN KEY (ug_user) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE user_newtalk ADD CONSTRAINT USER_NEWTALK_FK1 FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- referenced value not found +ALTER TABLE revision ADD CONSTRAINT REVISION_PAGE_FK FOREIGN KEY (rev_page) REFERENCES page(page_id) ON DELETE CASCADE +; +-- referenced value not found +ALTER TABLE revision ADD CONSTRAINT REVISION_USER_FK FOREIGN KEY (rev_user) REFERENCES user(user_id) ON DELETE RESTRICT +; + +-- good +ALTER TABLE page_restrictions ADD CONSTRAINT PAGE_RESTRICTIONS_PAGE_FK FOREIGN KEY (pr_page) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE page_props ADD CONSTRAINT PAGE_PROPS_PAGE_FK FOREIGN KEY (pp_page) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE archive ADD CONSTRAINT ARCHIVE_USER_FK FOREIGN KEY (ar_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- referenced value not found +ALTER TABLE redirect ADD CONSTRAINT REDIRECT_FROM_FK FOREIGN KEY (rd_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- referenced value not found +ALTER TABLE pagelinks ADD CONSTRAINT PAGELINKS_FROM_FK FOREIGN KEY (pl_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE templatelinks ADD CONSTRAINT TEMPLATELINKS_FROM_FK FOREIGN KEY (tl_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE imagelinks ADD CONSTRAINT IMAGELINKS_FROM_FK FOREIGN KEY (il_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE categorylinks ADD CONSTRAINT CATEGORYLINKS_FROM_FK FOREIGN KEY (cl_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE externallinks ADD CONSTRAINT EXTERNALLINKS_FROM_FK FOREIGN KEY (el_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- good +ALTER TABLE langlinks ADD CONSTRAINT LANGLINKS_FROM_FK FOREIGN KEY (ll_from) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_USER_FK FOREIGN KEY (ipb_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- good +ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_BY_FK FOREIGN KEY (ipb_by) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE image ADD CONSTRAINT IMAGE_USER_FK FOREIGN KEY (img_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_USER_FK FOREIGN KEY (oi_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- good +ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_NAME_FK FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_DELETED_USER_FK FOREIGN KEY (fa_deleted_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_USER_FK FOREIGN KEY (fa_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_USER_FK FOREIGN KEY (rc_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_CUR_ID_FK FOREIGN KEY (rc_cur_id) REFERENCES page(page_id) ON DELETE SET NULL +--; + +-- good +ALTER TABLE watchlist ADD CONSTRAINT WATCHLIST_USER_FK FOREIGN KEY (wl_user) REFERENCES user(user_id) ON DELETE CASCADE +; + +-- good +-- already in MySQL schema +ALTER TABLE trackbacks ADD CONSTRAINT TRACKBACKS_PAGE_FK FOREIGN KEY (tb_page) REFERENCES page(page_id) ON DELETE CASCADE +; + +-- cannot contain null values +-- ALTER TABLE protected_titles ADD CONSTRAINT PROTECTED_TITLES_USER_FK FOREIGN KEY (pt_user) REFERENCES user(user_id) ON DELETE SET NULL +--; + +-- cannot contain null values +-- ALTER TABLE logging ADD CONSTRAINT LOGGING_USER_FK FOREIGN KEY (log_user) REFERENCES user(user_id) ON DELETE SET NULL +--; \ No newline at end of file diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql index bf5a949d47..1e1e9aa776 100644 --- a/maintenance/ibm_db2/tables.sql +++ b/maintenance/ibm_db2/tables.sql @@ -8,6 +8,7 @@ CREATE TABLE user ( + -- Needs to start with 0 user_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), user_name VARCHAR(255) NOT NULL UNIQUE, user_real_name VARCHAR(255), @@ -71,7 +72,7 @@ CREATE UNIQUE INDEX user_newtalk_include_idx CREATE TABLE page ( - page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + page_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), page_namespace SMALLINT NOT NULL, page_title VARCHAR(255) NOT NULL, page_restrictions VARCHAR(1024), @@ -96,7 +97,7 @@ CREATE UNIQUE INDEX page_name_include CREATE TABLE revision ( - rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + rev_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), rev_page BIGINT NOT NULL DEFAULT 0, -- REFERENCES page (page_id) ON DELETE CASCADE, rev_text_id BIGINT, -- FK @@ -119,9 +120,7 @@ CREATE INDEX rev_user_text_idx ON revision (rev_user_text); CREATE TABLE text ( -- replaces reserved word 'text' - --old_id INTEGER NOT NULL, - old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), - --PRIMARY KEY DEFAULT nextval('text_old_id_val'), + old_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), old_text CLOB(16M) INLINE LENGTH 4096, old_flags VARCHAR(1024) ); @@ -129,8 +128,8 @@ CREATE TABLE text ( -- replaces reserved word 'text' CREATE TABLE page_restrictions ( --pr_id INTEGER NOT NULL UNIQUE, --DEFAULT nextval('pr_id_val'), - --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), - pr_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0), + --pr_id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), + pr_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), pr_page INTEGER NOT NULL DEFAULT 0, --(used to be nullable) -- REFERENCES page (page_id) ON DELETE CASCADE, @@ -184,7 +183,7 @@ CREATE INDEX archive_user_text ON archive (ar_user_text); CREATE TABLE redirect ( - rd_from BIGINT NOT NULL PRIMARY KEY, + rd_from BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --REFERENCES page(page_id) ON DELETE CASCADE, rd_namespace SMALLINT NOT NULL DEFAULT 0, rd_title VARCHAR(255) NOT NULL DEFAULT '', @@ -248,7 +247,7 @@ CREATE INDEX externallinks_index ON externallinks (el_index); -- CREATE TABLE external_user ( -- Foreign key to user_id - eu_local_id BIGINT NOT NULL PRIMARY KEY, + eu_local_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), -- Some opaque identifier provided by the external database eu_external_id VARCHAR(255) NOT NULL @@ -289,7 +288,7 @@ CREATE TABLE hitcounter ( ); CREATE TABLE ipblocks ( - ipb_id INTEGER NOT NULL PRIMARY KEY, + ipb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --DEFAULT nextval('ipblocks_ipb_id_val'), ipb_address VARCHAR(1024), ipb_user BIGINT NOT NULL DEFAULT 0, @@ -358,7 +357,6 @@ CREATE TABLE oldimage ( oi_sha1 VARCHAR(255) NOT NULL DEFAULT '' --FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE ); ---ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE; CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp); CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name); CREATE INDEX oi_sha1 ON oldimage (oi_sha1); @@ -366,7 +364,7 @@ CREATE INDEX oi_sha1 ON oldimage (oi_sha1); CREATE TABLE filearchive ( - fa_id INTEGER NOT NULL PRIMARY KEY, + fa_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'), fa_name VARCHAR(255) NOT NULL, fa_archive_name VARCHAR(255), @@ -398,7 +396,7 @@ CREATE INDEX fa_nouser ON filearchive (fa_deleted_user); CREATE TABLE recentchanges ( - rc_id INTEGER NOT NULL PRIMARY KEY, + rc_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), rc_timestamp TIMESTAMP(3) NOT NULL, rc_cur_time TIMESTAMP(3) NOT NULL, @@ -509,7 +507,7 @@ CREATE TABLE transcache ( CREATE TABLE logging ( - log_id BIGINT NOT NULL PRIMARY KEY, + log_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --PRIMARY KEY DEFAULT nextval('log_log_id_seq'), log_type VARCHAR(32) NOT NULL, log_action VARCHAR(32) NOT NULL, @@ -534,10 +532,11 @@ CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp); CREATE TABLE trackbacks ( - tb_id INTEGER NOT NULL PRIMARY KEY, + tb_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'), -- foreign key also in MySQL - tb_page INTEGER REFERENCES page(page_id) ON DELETE CASCADE, + tb_page INTEGER, + -- REFERENCES page(page_id) ON DELETE CASCADE, tb_title VARCHAR(255) NOT NULL, tb_url CLOB(64K) INLINE LENGTH 4096 NOT NULL, tb_ex CLOB(64K) INLINE LENGTH 4096, @@ -548,7 +547,7 @@ CREATE INDEX trackback_page ON trackbacks (tb_page); CREATE TABLE job ( - job_id BIGINT NOT NULL PRIMARY KEY, + job_id BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --PRIMARY KEY DEFAULT nextval('job_job_id_seq'), job_cmd VARCHAR(255) NOT NULL, job_namespace SMALLINT NOT NULL, @@ -558,47 +557,6 @@ CREATE TABLE job ( CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title); - --- Postgres' Tsearch2 dropped ---ALTER TABLE page ADD titlevector tsvector; ---CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS ---$mw$ ---BEGIN ---IF TG_OP = 'INSERT' THEN --- NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); ---ELSIF NEW.page_title != OLD.page_title THEN --- NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); ---END IF; ---RETURN NEW; ---END; ---$mw$; - ---CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page --- FOR EACH ROW EXECUTE PROCEDURE ts2_page_title(); - - ---ALTER TABLE text ADD textvector tsvector; ---CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS ---$mw$ ---BEGIN ---IF TG_OP = 'INSERT' THEN --- NEW.textvector = to_tsvector('default',NEW.old_text); ---ELSIF NEW.old_text != OLD.old_text THEN --- NEW.textvector := to_tsvector('default',NEW.old_text); ---END IF; ---RETURN NEW; ---END; ---$mw$; - ---CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text --- FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); - --- These are added by the setup script due to version compatibility issues --- If using 8.1, we switch from "gin" to "gist" - ---CREATE INDEX ts2_page_title ON page USING gin(titlevector); ---CREATE INDEX ts2_page_text ON text USING gin(textvector); - --TODO --CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS --$mw$ @@ -644,7 +602,7 @@ CREATE TABLE updatelog ( CREATE TABLE category ( - cat_id INTEGER NOT NULL PRIMARY KEY, + cat_id INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1), --PRIMARY KEY DEFAULT nextval('category_id_seq'), cat_title VARCHAR(255) NOT NULL, cat_pages INTEGER NOT NULL DEFAULT 0, -- 2.20.1