mark: A photo of Mark kneeling on top of the Taal Volcano in the Philippines. It was a long hike. (Default)
Mark Smith ([staff profile] mark) wrote in [site community profile] changelog2009-09-13 04:50 am
  • Add Memory
  • Share This Entry

[dw-free] remove legacy database tables

[commit: http://hg.dwscoalition.org/dw-free/rev/fe91fe551e64]

http://bugs.dwscoalition.org/show_bug.cgi?id=1710

Drop memorable/memkeyword tables. They were for pre-clustered memories which
have ascended.

Patch by [personal profile] kareila.

Files modified:
  • bin/deleteusers.pl
  • bin/upgrading/base-data.sql
  • bin/upgrading/update-db-general.pl
  • cgi-bin/ljmemories.pl
--------------------------------------------------------------------------------
diff -r 0f9e42f655bc -r fe91fe551e64 bin/deleteusers.pl
--- a/bin/deleteusers.pl	Sun Sep 13 04:44:38 2009 +0000
+++ b/bin/deleteusers.pl	Sun Sep 13 04:50:47 2009 +0000
@@ -68,17 +68,6 @@ foreach my $uid (@delusers)
     # but with meta-data saying who used to own it
     # ..... hm, with clusters this is a pain.  let's not.
 
-    # delete memories
-    print "  memories\n";
-    while (($ids = $dbh->selectcol_arrayref("SELECT memid FROM memorable WHERE userid=$uid LIMIT 100")) && @{$ids})
-    {
-        my $in = join(",", @$ids);
-        print "  id: $in\n";
-        $runsql->($dbh, $user, "DELETE FROM memkeyword WHERE memid IN ($in)");
-        $runsql->($dbh, $user, "DELETE FROM memorable WHERE memid IN ($in)");
-    }
-
-
     # delete userpics
     {
         print "  userpics\n";
@@ -110,7 +99,6 @@ foreach my $uid (@delusers)
     $runsql->($user, "DELETE FROM wt_edges WHERE from_userid=$uid");
     $runsql->($user, "DELETE FROM wt_edges WHERE to_userid=$uid");
     $runsql->($dbcm, $user, "DELETE FROM trust_groups WHERE userid=$uid");
-    $runsql->($user, "DELETE FROM memorable WHERE userid=$uid");
     $runsql->($dbcm, $user, "DELETE FROM memorable2 WHERE userid=$uid");
     $runsql->($dbcm, $user, "DELETE FROM userkeywords WHERE userid=$uid");
     $runsql->($dbcm, $user, "DELETE FROM memkeyword2 WHERE userid=$uid");
diff -r 0f9e42f655bc -r fe91fe551e64 bin/upgrading/base-data.sql
--- a/bin/upgrading/base-data.sql	Sun Sep 13 04:44:38 2009 +0000
+++ b/bin/upgrading/base-data.sql	Sun Sep 13 04:50:47 2009 +0000
@@ -871,7 +871,6 @@ REPLACE INTO schematables (des, public_b
 REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for SiteMessages widget (used to post announcements to users).', '0', 'off', NULL, 'site_messages');
 REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for a rotating secret value on the server, for auth support. Please see also [dbtable[challenges]].', '0', 'off', NULL, 'secrets');
 REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for mapping a userid to an external identity and its type.\n\r\nThis focuses on OpenID consumer support, and was introduced so infrastructure was in place to make it completely identity-system neutral rather than OpenID-specific.\r\n\r\nIn theory, eventually, this will allow people to log in with TypeKey, LID, etc.', '0', 'off', NULL, 'identitymap');
-REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for mapping memories to keywords. Please see also [dbtable[memkeyword2]].', '0', 'off', NULL, 'memkeyword');
 REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for mapping pollid <=> journalid.', '0', 'off', NULL, 'pollowner');
 REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for mapping userid <=> user(name).', '0', 'off', NULL, 'useridmap');
 REPLACE INTO schematables (des, public_browsable, redist_mode, redist_where, tablename) VALUES ('Global. Stores data for mapping userpic [dbtable[keywords]] to [dbtable[userpic]]s. Please see also the clustered [dbtable[userpicmap2]] table, used from data version 7.', '0', 'off', NULL, 'userpicmap');
diff -r 0f9e42f655bc -r fe91fe551e64 bin/upgrading/update-db-general.pl
--- a/bin/upgrading/update-db-general.pl	Sun Sep 13 04:44:38 2009 +0000
+++ b/bin/upgrading/update-db-general.pl	Sun Sep 13 04:50:47 2009 +0000
@@ -174,29 +174,6 @@ CREATE TABLE logproplist (
 
     PRIMARY KEY  (propid),
     UNIQUE KEY name (name)
-)
-EOC
-
-register_tablecreate("memkeyword", <<'EOC');
-CREATE TABLE memkeyword (
-    memid int(10) unsigned NOT NULL default '0',
-    kwid int(10) unsigned NOT NULL default '0',
-
-    PRIMARY KEY  (memid,kwid)
-)
-EOC
-
-register_tablecreate("memorable", <<'EOC');
-CREATE TABLE memorable (
-    memid int(10) unsigned NOT NULL auto_increment,
-    userid int(10) unsigned NOT NULL default '0',
-    itemid int(10) unsigned NOT NULL default '0',
-    des varchar(60) default NULL,
-    security enum('public','friends','private') NOT NULL default 'public',
-
-    PRIMARY KEY  (memid),
-    UNIQUE KEY userid (userid,itemid),
-    KEY (itemid)
 )
 EOC
 
@@ -911,6 +888,8 @@ register_tabledrop("portal_box_prop");
 register_tabledrop("portal_box_prop");
 register_tabledrop("portal_config");
 register_tabledrop("portal_typemap");
+register_tabledrop("memkeyword");
+register_tabledrop("memorable");
 
 register_tablecreate("infohistory", <<'EOC');
 CREATE TABLE infohistory (
@@ -3206,15 +3185,6 @@ register_alter(sub {
                  "ADD is_public ENUM('1', '0') DEFAULT '1' NOT NULL");
     }
 
-    if (column_type("memorable", "jitemid") eq "") {
-        do_alter("memorable", "ALTER TABLE memorable ".
-                 "DROP INDEX userid, DROP INDEX itemid, ".
-                 "CHANGE itemid jitemid INT UNSIGNED NOT NULL, ".
-                 "ADD journalid INT UNSIGNED NOT NULL AFTER userid, ".
-                 "ADD UNIQUE uniq (userid, journalid, jitemid), ".
-                 "ADD KEY item (journalid, jitemid)");
-    }
-
     if (column_type("user", "clusterid") eq "") {
         do_alter("user", "ALTER TABLE user ".
                  "ADD clusterid TINYINT UNSIGNED NOT NULL AFTER caps, ".
@@ -3233,13 +3203,6 @@ register_alter(sub {
 
     if (column_type("user", "allow_getpromos") ne "") {
         do_alter("user", "ALTER TABLE user DROP allow_getpromos");
-    }
-
-    # widen columns to accomodate larger Unicode names
-    if (column_type("memorable", "des") eq "varchar(60)") {
-        do_alter("memorable",
-                 "ALTER TABLE memorable ".
-                 "MODIFY des VARCHAR(150) NOT NULL");
     }
 
     #allow longer moodtheme pic URLs
diff -r 0f9e42f655bc -r fe91fe551e64 cgi-bin/ljmemories.pl
--- a/cgi-bin/ljmemories.pl	Sun Sep 13 04:44:38 2009 +0000
+++ b/cgi-bin/ljmemories.pl	Sun Sep 13 04:50:47 2009 +0000
@@ -104,18 +104,14 @@ sub delete_by_id {
     $memids = [ $memids ] if $memids && !ref $memids; # so they can just pass a single thing...
     return undef unless $u && @{$memids || []};
 
-    # setup
-    my ( $db, $table ) = ( $u, '2' );
-
+    # delete actual memory
     my $in = join ',', map { $_ + 0 } @$memids;
-
-    # delete actual memory
-    $db->do("DELETE FROM memorable$table WHERE userid = ? AND memid IN ($in)", undef, $u->{userid});
-    return undef if $db->err;
+    $u->do("DELETE FROM memorable2 WHERE userid = ? AND memid IN ($in)", undef, $u->{userid});
+    return undef if $u->err;
 
     # delete keyword associations
     my $euser = "userid = $u->{userid} AND";
-    $db->do("DELETE FROM memkeyword$table WHERE $euser memid IN ($in)");
+    $u->do("DELETE FROM memkeyword2 WHERE $euser memid IN ($in)");
 
     # delete cache of count and keyword counts
     clear_memcache($u);
--------------------------------------------------------------------------------