finish mysql locking workaround

4 files changed, 155 insertions, 68 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index e56a91b30..0539f3fac 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -65,13 +65,22 @@ assuming it is up-to-date).  See L<DBIx::DBSchema>.
 
 sub dbdef { $dbdef; }
 
-=item dbdef_dist [ OPTION => VALUE ... ]
+=item dbdef_dist [ DATASRC ]
 
 Returns the current canoical database definition as defined in this file.
 
+Optionally, pass a DBI data source to enable syntax specific to that database.
+Currently, this enables "TYPE=InnoDB" for MySQL databases.
+
 =cut
 
 sub dbdef_dist {
+  my $datasrc = @_ ? shift : '';
+  
+  my $local_options = '';
+  if ( $datasrc =~ /^dbi:mysql/i ) {
+    $local_options = 'TYPE=InnoDB';
+  }
 
   ###
   # create a dbdef object from the old data structure
@@ -79,7 +88,6 @@ sub dbdef_dist {
 
   my $tables_hashref = tables_hashref();
 
-
   #turn it into objects
   my $dbdef = new DBIx::DBSchema map {  
 
@@ -125,10 +133,11 @@ sub dbdef_dist {
                        @$index;
 
     DBIx::DBSchema::Table->new({
-      'name'        => $tablename,
-      'primary_key' => $tables_hashref->{$tablename}{'primary_key'},
-      'columns'     => \@columns,
-      'indices'     => \@indices,
+      'name'          => $tablename,
+      'primary_key'   => $tables_hashref->{$tablename}{'primary_key'},
+      'columns'       => \@columns,
+      'indices'       => \@indices,
+      'local_options' => $local_options,
     });
 
   } keys %$tables_hashref;
@@ -184,66 +193,104 @@ sub dbdef_dist {
                         keys %indices;
 
     my $h_tableobj = DBIx::DBSchema::Table->new( {
-      'name'        => "h_$table",
-      'primary_key' => 'historynum',
-      'indices'     => \%h_indices,
-      'columns'     => [
-                         DBIx::DBSchema::Column->new( {
-                           'name'    => 'historynum',
-                           'type'    => 'serial',
-                           'null'    => 'NOT NULL',
-                           'length'  => '',
-                           'default' => '',
-                           'local'   => '',
-                         } ),
-                         DBIx::DBSchema::Column->new( {
-                           'name'    => 'history_date',
-                           'type'    => 'int',
-                           'null'    => 'NULL',
-                           'length'  => '',
-                           'default' => '',
-                           'local'   => '',
-                         } ),
-                         DBIx::DBSchema::Column->new( {
-                           'name'    => 'history_user',
-                           'type'    => 'varchar',
-                           'null'    => 'NOT NULL',
-                           'length'  => '80',
-                           'default' => '',
-                           'local'   => '',
-                         } ),
-                         DBIx::DBSchema::Column->new( {
-                           'name'    => 'history_action',
-                           'type'    => 'varchar',
-                           'null'    => 'NOT NULL',
-                           'length'  => '80',
-                           'default' => '',
-                           'local'   => '',
-                         } ),
-                         map {
-                           my $column = $tableobj->column($_);
+      'name'          => "h_$table",
+      'primary_key'   => 'historynum',
+      'indices'       => \%h_indices,
+      'local_options' => $local_options,
+      'columns'       => [
+          DBIx::DBSchema::Column->new( {
+            'name'    => 'historynum',
+            'type'    => 'serial',
+            'null'    => 'NOT NULL',
+            'length'  => '',
+            'default' => '',
+            'local'   => '',
+          } ),
+          DBIx::DBSchema::Column->new( {
+            'name'    => 'history_date',
+            'type'    => 'int',
+            'null'    => 'NULL',
+            'length'  => '',
+            'default' => '',
+            'local'   => '',
+          } ),
+          DBIx::DBSchema::Column->new( {
+            'name'    => 'history_user',
+            'type'    => 'varchar',
+            'null'    => 'NOT NULL',
+            'length'  => '80',
+            'default' => '',
+            'local'   => '',
+          } ),
+          DBIx::DBSchema::Column->new( {
+            'name'    => 'history_action',
+            'type'    => 'varchar',
+            'null'    => 'NOT NULL',
+            'length'  => '80',
+            'default' => '',
+            'local'   => '',
+          } ),
+          map {
+            my $column = $tableobj->column($_);
     
-                           #clone so as to not disturb the original
-                           $column = DBIx::DBSchema::Column->new( {
-                             map { $_ => $column->$_() }
-                               qw( name type null length default local )
-                           } );
+            #clone so as to not disturb the original
+            $column = DBIx::DBSchema::Column->new( {
+              map { $_ => $column->$_() }
+                qw( name type null length default local )
+            } );
     
-                           if ( $column->type =~ /^(\w*)SERIAL$/i ) {
-                             $column->type('int');
-                             $column->null('NULL');
-                           }
-                           #$column->default('')
-                           #  if $column->default =~ /^nextval\(/i;
-                           #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
-                           #$column->local($local);
-                           $column;
-                         } $tableobj->columns
-                     ],
+            if ( $column->type =~ /^(\w*)SERIAL$/i ) {
+              $column->type('int');
+              $column->null('NULL');
+            }
+            #$column->default('')
+            #  if $column->default =~ /^nextval\(/i;
+            #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
+            #$column->local($local);
+            $column;
+          } $tableobj->columns
+      ],
     } );
     $dbdef->addtable($h_tableobj);
   }
 
+  if ( $datasrc =~ /^dbi:mysql/i ) {
+
+    my $dup_lock_table = DBIx::DBSchema::Table->new( {
+      'name'          => 'duplicate_lock',
+      'primary_key'   => 'duplocknum',
+      'local_options' => $local_options,
+      'columns'       => [
+        DBIx::DBSchema::Column->new( {
+          'name'    => 'duplocknum',
+          'type'    => 'serial',
+          'null'    => 'NOT NULL',
+          'length'  => '',
+          'default' => '',
+          'local'   => '',
+        } ),
+        DBIx::DBSchema::Column->new( {
+          'name'    => 'lockname',
+          'type'    => 'varchar',
+          'null'    => 'NOT NULL',
+          'length'  => '80',
+          'default' => '',
+          'local'   => '',
+        } ),
+      ],
+      'indices' => { 'duplicate_lock1' =>
+                       DBIx::DBSchema::Index->new({
+                         'name'    => 'duplicate_lock1',
+                         'unique'  => 1,
+                         'columns' => [ 'lockname' ],
+                       })
+                   },
+    } );
+
+    $dbdef->addtable($dup_lock_table);
+
+  }
+
   $dbdef;
 
 }
diff --git a/FS/FS/Setup.pm b/FS/FS/Setup.pm
index 17101a745..191f62bc5 100644
--- a/FS/FS/Setup.pm
+++ b/FS/FS/Setup.pm
@@ -5,7 +5,7 @@ use vars qw( @ISA @EXPORT_OK );
 use Exporter;
 #use Tie::DxHash;
 use Tie::IxHash;
-use FS::UID qw( dbh );
+use FS::UID qw( dbh driver_name );
 use FS::Record;
 
 use FS::svc_domain;
@@ -45,6 +45,8 @@ sub create_initial_data {
 
   populate_locales();
 
+  populate_duplock();
+
   #initial_data data
   populate_initial_data(%opt);
 
@@ -125,6 +127,18 @@ sub _add_locale {
   die $error if $error;
 }
 
+sub populate_duplock {
+
+  return unless driver_name =~ /^mysql/i;
+
+  my $sth = dbh->prepare(
+    "INSERT INTO duplicate_lock ( lockname ) VALUES ( 'svc_acct' )"
+  ) or die dbh->errstr;
+
+  $sth->execute or die $sth->errstr;
+
+}
+
 sub populate_initial_data {
   my %opt = @_;
 
diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm
index 10bf081d2..322fea902 100644
--- a/FS/FS/svc_acct.pm
+++ b/FS/FS/svc_acct.pm
@@ -21,7 +21,7 @@ use Fcntl qw(:flock);
 use Date::Format;
 use Crypt::PasswdMD5 1.2;
 use Data::Dumper;
-use FS::UID qw( datasrc );
+use FS::UID qw( datasrc driver_name );
 use FS::Conf;
 use FS::Record qw( qsearch qsearchs fields dbh dbdef );
 use FS::Msgcat qw(gettext);
@@ -1142,11 +1142,19 @@ sub _check_duplicate {
   my $global_unique = $conf->config('global_unique-username') || 'none';
   return '' if $global_unique eq 'disabled';
 
-  #this is Pg-specific.  what to do for mysql etc?
-  # ( mysql LOCK TABLES certainly isn't equivalent or useful here :/ )
   warn "$me locking svc_acct table for duplicate search" if $DEBUG;
-  dbh->do("LOCK TABLE svc_acct IN SHARE ROW EXCLUSIVE MODE")
-    or die dbh->errstr;
+  if ( driver_name =~ /^Pg/i ) {
+    dbh->do("LOCK TABLE svc_acct IN SHARE ROW EXCLUSIVE MODE")
+      or die dbh->errstr;
+  } elsif ( driver_name =~ /^mysql/i ) {
+    dbh->do("SELECT * FROM duplicate_lock
+               WHERE lockname = 'svc_acct'
+	       FOR UPDATE"
+	   ) or die dbh->errstr;
+  } else {
+    die "unknown database ". driver_name.
+        "; don't know how to lock for duplicate search";
+  }
   warn "$me acquired svc_acct table lock for duplicate search" if $DEBUG;
 
   my $part_svc = qsearchs('part_svc', { 'svcpart' => $self->svcpart } );
diff --git a/FS/bin/freeside-upgrade b/FS/bin/freeside-upgrade
index 792b8ba13..b05d6f0a8 100755
--- a/FS/bin/freeside-upgrade
+++ b/FS/bin/freeside-upgrade
@@ -47,7 +47,7 @@ if (dbdef->table('cust_main')->column('agent_custid')) {
 
 if ( $DRY_RUN ) {
   print
-    join(";\n", @bugfix, dbdef->sql_update_schema( dbdef_dist, $dbh ) ). ";\n";
+    join(";\n", @bugfix, dbdef->sql_update_schema( dbdef_dist(datasrc), $dbh ) ). ";\n";
   exit;
 } else {
   foreach my $statement ( @bugfix ) {
@@ -55,7 +55,7 @@ if ( $DRY_RUN ) {
       or die "Error: ". $dbh->errstr. "\n executing: $statement";
   }
 
-  dbdef->update_schema( dbdef_dist, $dbh );
+  dbdef->update_schema( dbdef_dist(datasrc), $dbh );
 }
 
 my $hashref = {};
@@ -64,6 +64,24 @@ $hashref->{debug} = 1 if $DEBUG;
 print join "\n", prune_applications($hashref);
 print "\n" if $DRY_RUN;
 
+if ( $dbh->{Driver}->{Name} =~ /^mysql/i ) {
+
+  my $sth = $dbh->prepare(
+    "SELECT COUNT(*) FROM duplicate_lock WHERE lockname = 'svc_acct'"
+  ) or die $dbh->errstr;
+
+  $sth->execute or die $sth->errstr;
+
+  unless ( $sth->fetchrow_arrayref->[0] ) {
+
+    $sth = $dbh->prepare(
+      "INSERT INTO duplicate_lock ( lockname ) VALUES ( 'svc_acct' )"
+    ) or die $dbh->errstr;
+
+    $sth->execute or die $sth->errstr;
+
+  }
+}
 
 $dbh->commit or die $dbh->errstr;