Андрей (andy) wrote in changelog,
Андрей
andy
changelog

[livejournal] r18184: LJSUP-7847 (do not use mysql auto_increm...

Committer: ailyin
LJSUP-7847 (do not use mysql auto_increment to generate propids)
U   trunk/bin/upgrading/update-db.pl
Modified: trunk/bin/upgrading/update-db.pl
===================================================================
--- trunk/bin/upgrading/update-db.pl	2011-02-02 07:42:05 UTC (rev 18183)
+++ trunk/bin/upgrading/update-db.pl	2011-02-02 08:56:41 UTC (rev 18184)
@@ -622,6 +622,16 @@
               'ratelist' => 'name',
               );
 
+    my %propid_field = (
+        'categoryproplist'  => 'propid',
+        'logproplist'       => 'propid',
+        'pollproplist2'     => 'propid',
+        'ratelist'          => 'rlid',
+        'talkproplist'      => 'tpropid',
+        'usermsgproplist'   => 'propid',
+        'userproplist'      => 'upropid',
+    );
+
     my %noscope = (
         'ratelist' => 1,
     );
@@ -632,21 +642,45 @@
     my %vals;   # hash of column -> value, including primary key
     my $insert = sub {
         return unless %vals;
+
         my $sets = join(", ", map { "$_=" . $dbh->quote($vals{$_}) } keys %vals);
 
-        my $rv = $dbh->do("INSERT IGNORE INTO $table SET $sets");
-        die $dbh->errstr if $dbh->err;
+        my $row = $dbh->selectrow_hashref(qq{
+            SELECT * FROM $table WHERE $pk = ?
+        }, undef, $pkv);
 
-        # zero-but-true:  see if row didn't exist before, so above did nothing.
-        # in that case, update it.
-        if ($rv < 1) {
-            $rv = $dbh->do("UPDATE $table SET $sets WHERE $pk=?", undef, $pkv);
+        if ( defined $row ) {
+            # the row exists, let's update it
+            $dbh->do("UPDATE $table SET $sets WHERE $pk=?", undef, $pkv);
             die $dbh->errstr if $dbh->err;
+        } else {
+            # find a propid for the row and then insert it
+
+            my $propid_field = $propid_field{$table};
+
+            my $existing_propids = $dbh->selectcol_arrayref(qq{
+                SELECT $propid_field FROM $table
+            });
+            die $dbh->errstr if $dbh->err;
+
+            my %existing_propids = map { $_ => 1 } @$existing_propids;
+
+            # find a "hole" in the set of propids, because
+            # we don't trust auto_increment enough
+            my $propid = 1;
+            $propid++ while exists $existing_propids{$propid};
+
+            # finally, insert
+            $dbh->do(qq{
+                INSERT INTO $table SET $propid_field = ?, $sets
+            }, undef, $propid);
+            die $dbh->errstr if $dbh->err;
         }
 
         $table = undef;
         %vals = ();
     };
+
     while (<$fh>) {
         next if /^\#/;
 

Tags: andy, livejournal, pl
Subscribe
  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 0 comments