From 288a7eb7f18ebc1f98f6ad65ede01c9ea39d9e9b Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Thu, 14 Dec 2006 13:22:52 +0000 Subject: [PATCH] Going ahead and adding this field while other DB updates are pending. Interfaces to use it can be added shortly. * Add user_editcount field to provide data for heuristics on account use. Incremented on edit, with lazy initialization from past revision data. Can batch-initialize with maintenance/initEditCount.php (not yet friendly to replication environments, this will do all accounts in one query). * Allow raw SQL subsections in Database::update() SET portion as well as for WHERE portion. Handy for increments and such. --- RELEASE-NOTES | 6 +++ includes/Article.php | 2 + includes/Database.php | 2 + includes/User.php | 42 +++++++++++++++++++ maintenance/archives/patch-user_editcount.sql | 5 +++ maintenance/initEditCount.php | 40 ++++++++++++++++++ maintenance/mysql5/tables-binary.sql | 12 ++++++ maintenance/mysql5/tables.sql | 12 ++++++ maintenance/postgres/tables.sql | 3 +- maintenance/tables.sql | 12 ++++++ maintenance/updaters.inc | 2 + 11 files changed, 137 insertions(+), 1 deletion(-) create mode 100644 maintenance/archives/patch-user_editcount.sql create mode 100644 maintenance/initEditCount.php diff --git a/RELEASE-NOTES b/RELEASE-NOTES index e02ed75848..4485307950 100644 --- a/RELEASE-NOTES +++ b/RELEASE-NOTES @@ -310,6 +310,12 @@ it from source control: http://www.mediawiki.org/wiki/Download_from_SVN creation, so people don't immediately go off to request a second one. * Add a warning on Special:Confirmemail if a code was already sent and has not yet expired. +* Add user_editcount field to provide data for heuristics on account use. + Incremented on edit, with lazy initialization from past revision data. + Can batch-initialize with maintenance/initEditCount.php (not yet friendly + to replication environments, this will do all accounts in one query). +* Allow raw SQL subsections in Database::update() SET portion as well as + for WHERE portion. Handy for increments and such. == Languages updated == diff --git a/includes/Article.php b/includes/Article.php index edf7aea80d..e1242d3155 100644 --- a/includes/Article.php +++ b/includes/Article.php @@ -1339,6 +1339,7 @@ class Article { RecentChange::markPatrolled( $rcid ); } } + $wgUser->incEditCount(); $dbw->commit(); } } else { @@ -1399,6 +1400,7 @@ class Article { RecentChange::markPatrolled( $rcid ); } } + $wgUser->incEditCount(); $dbw->commit(); # Update links, etc. diff --git a/includes/Database.php b/includes/Database.php index f35aaaa1e4..f2acd11021 100644 --- a/includes/Database.php +++ b/includes/Database.php @@ -1332,6 +1332,8 @@ class Database { } 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) ) { $list .= $field." IN (".$this->makeList($value).") "; } else { diff --git a/includes/User.php b/includes/User.php index 53b14ef4cc..89b1dd0d0d 100644 --- a/includes/User.php +++ b/includes/User.php @@ -2451,6 +2451,48 @@ class User { return $text; } } + + /** + * Increment the user's edit-count field. + * Will have no effect for anonymous users. + */ + function incEditCount() { + if( !$this->isAnon() ) { + $dbw = wfGetDB( DB_MASTER ); + $dbw->update( 'user', + array( 'user_editcount=user_editcount+1' ), + array( 'user_id' => $this->getId() ), + __METHOD__ ); + + // Lazy initialization check... + if( $dbw->affectedRows() == 0 ) { + // Pull from a slave to be less cruel to servers + // Accuracy isn't the point anyway here + $dbr = wfGetDB( DB_SLAVE ); + $count = $dbr->selectField( 'revision', + 'COUNT(rev_user)', + array( 'rev_user' => $this->getId() ), + __METHOD__ ); + + // Now here's a goddamn hack... + if( $dbr !== $dbw ) { + // If we actually have a slave server, the count is + // at least one behind because the current transaction + // has not been committed and replicated. + $count++; + } else { + // But if DB_SLAVE is selecting the master, then the + // count we just read includes the revision that was + // just added in the working transaction. + } + + $dbw->update( 'user', + array( 'user_editcount' => $count ), + array( 'user_id' => $this->getId() ), + __METHOD__ ); + } + } + } } ?> diff --git a/maintenance/archives/patch-user_editcount.sql b/maintenance/archives/patch-user_editcount.sql new file mode 100644 index 0000000000..cdde36dc33 --- /dev/null +++ b/maintenance/archives/patch-user_editcount.sql @@ -0,0 +1,5 @@ +ALTER TABLE /*$wgDBprefix*/user + ADD COLUMN user_editcount int; + +-- Don't initialize values immediately... or should we? +-- They will be lazy-evaluated, or batch-filled via maintenance/initEditCount.php diff --git a/maintenance/initEditCount.php b/maintenance/initEditCount.php new file mode 100644 index 0000000000..a975f35616 --- /dev/null +++ b/maintenance/initEditCount.php @@ -0,0 +1,40 @@ +tableName( 'user' ); +$revision = $dbw->tableName( 'revision' ); + +$dbver = $dbw->getServerVersion(); +if( ($dbw instanceof DatabaseMySql && version_compare( $dbver, '4.1' ) < 0) + || isset( $options['force-mysql4'] ) ) { + + echo "Warning: MySQL $dbver; using hacky MySQL 4.0 compatibility query...\n"; + $sql = "CREATE TEMPORARY TABLE temp_editcount ( + temp_user_id INT, + temp_user_editcount INT + )"; + $dbw->query( $sql ); + + $sql = "INSERT INTO temp_editcount + (temp_user_id, temp_user_editcount) + SELECT rev_user, COUNT(rev_user) + FROM $revision GROUP BY rev_user"; + $dbw->query( $sql ); + + $sql = "UPDATE $user + LEFT OUTER JOIN temp_editcount ON user_id=temp_user_id + SET user_editcount=IF(temp_user_editcount IS NULL,0,temp_user_editcount)"; + $dbw->query( $sql ); +} else { + // Subselect should work on modern MySQLs etc + $sql = "UPDATE $user SET user_editcount=(SELECT COUNT(*) FROM $revision WHERE rev_user=user_id)"; + $dbw->query( $sql ); +} + +echo "Done!\n"; + +?> diff --git a/maintenance/mysql5/tables-binary.sql b/maintenance/mysql5/tables-binary.sql index d9caafaf5b..2ab3654638 100644 --- a/maintenance/mysql5/tables-binary.sql +++ b/maintenance/mysql5/tables-binary.sql @@ -135,6 +135,18 @@ CREATE TABLE /*$wgDBprefix*/user ( -- Timestamp of account registration. -- Accounts predating this schema addition may contain NULL. user_registration char(14) binary, + + -- Count of edits and edit-like actions. + -- + -- *NOT* intended to be an accurate copy of COUNT(*) WHERE rev_user=user_id + -- May contain NULL for old accounts if batch-update scripts haven't been + -- run, as well as listing deleted edits and other myriad ways it could be + -- out of sync. + -- + -- Meant primarily for heuristic checks to give an impression of whether + -- the account has been used much. + -- + user_editcount int, PRIMARY KEY user_id (user_id), UNIQUE INDEX user_name (user_name), diff --git a/maintenance/mysql5/tables.sql b/maintenance/mysql5/tables.sql index cc624bafb0..356f3bbfff 100644 --- a/maintenance/mysql5/tables.sql +++ b/maintenance/mysql5/tables.sql @@ -126,6 +126,18 @@ CREATE TABLE /*$wgDBprefix*/user ( -- Timestamp of account registration. -- Accounts predating this schema addition may contain NULL. user_registration char(14) binary, + + -- Count of edits and edit-like actions. + -- + -- *NOT* intended to be an accurate copy of COUNT(*) WHERE rev_user=user_id + -- May contain NULL for old accounts if batch-update scripts haven't been + -- run, as well as listing deleted edits and other myriad ways it could be + -- out of sync. + -- + -- Meant primarily for heuristic checks to give an impression of whether + -- the account has been used much. + -- + user_editcount int, PRIMARY KEY user_id (user_id), UNIQUE INDEX user_name (user_name), diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 3c507aa3fb..bf0328b0f6 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -25,7 +25,8 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_email_authenticated TIMESTAMPTZ, user_options TEXT, user_touched TIMESTAMPTZ, - user_registration TIMESTAMPTZ + user_registration TIMESTAMPTZ, + user_editcount INTEGER ); CREATE INDEX user_email_token_idx ON mwuser (user_email_token); diff --git a/maintenance/tables.sql b/maintenance/tables.sql index e2c67373c6..da1911358c 100644 --- a/maintenance/tables.sql +++ b/maintenance/tables.sql @@ -113,6 +113,18 @@ CREATE TABLE /*$wgDBprefix*/user ( -- Timestamp of account registration. -- Accounts predating this schema addition may contain NULL. user_registration char(14) binary, + + -- Count of edits and edit-like actions. + -- + -- *NOT* intended to be an accurate copy of COUNT(*) WHERE rev_user=user_id + -- May contain NULL for old accounts if batch-update scripts haven't been + -- run, as well as listing deleted edits and other myriad ways it could be + -- out of sync. + -- + -- Meant primarily for heuristic checks to give an impression of whether + -- the account has been used much. + -- + user_editcount int, PRIMARY KEY user_id (user_id), UNIQUE INDEX user_name (user_name), diff --git a/maintenance/updaters.inc b/maintenance/updaters.inc index 81481ec240..86cfb3b0c7 100644 --- a/maintenance/updaters.inc +++ b/maintenance/updaters.inc @@ -62,6 +62,7 @@ $wgNewFields = array( array( 'site_stats', 'ss_images', 'patch-ss_images.sql' ), array( 'ipblocks', 'ipb_anon_only', 'patch-ipb_anon_only.sql' ), array( 'user', 'user_newpass_time','patch-user_newpass_time.sql' ), + array( 'user', 'user_editcount', 'patch-user_editcount.sql' ), ); function rename_table( $from, $to, $patch ) { @@ -1021,6 +1022,7 @@ ALTER TABLE revision ADD CONSTRAINT revision_rev_user_fkey -- New column for better password tracking: ALTER TABLE mwuser ADD user_newpass_time TIMESTAMPTZ; +ALTER TABLE mwuser ADD user_editcount INTEGER; -- New column for autoblocking problem users ALTER TABLE ipblocks ADD ipb_enable_autoblock CHAR NOT NULL DEFAULT '1'; -- 2.20.1