[ic] DatabaseAuto and auto_increment

[Bill Carr]

On Nov 2, 2005, at 2:40 PM, Jon Jensen wrote:

> On Wed, 2 Nov 2005, JT Justman wrote:
>
>> I've recently set up DatabaseAuto on first my testing and now  
>> production
>> servers. I neglected to notice, however, that for tables with
>> auto_incriment set, IC still tries to set an index in the UI. If I  
>> set
>> like this:
>>
>> Database table_name AUTO_SEQUENCE 1
>>
>> in catalog/db config, as I had before, it seems to work. But, the  
>> point
>> of using DatabaseAuto was to avoid having to specify such things for
>> each table, and I've got tons of custom tables.
>
> Does that really work with MySQL? Does MySQL 4.1 have sequences?  
> They are different than AUTO_INCREMENT columns. I believe you  
> should be supplying the sequence name, not a boolean '1'. But I  
> haven't tried it.
>
>> I was looking at the DatabaseDefault code and it's a tad over my  
>> head. I
>> can only imagine that there is some kind of magic going on that pulls
>> the hash of the table info from DBI. Is there an existing  
>> mechanism that
>> can help me out here?
>
> I'm not aware of any. It would be nice to have. Still, using  
> DatabaseAuto saves you a fair amount of manual configuration even  
> if you still need to specify sequences separately ...
Here's a little something I use that might help. I have hacked  
Config.pm to allow my config files to be run through the perl  
interpreter. That is where this stuff gets called. You could just as  
easily create a perl script for generating part or all of your config  
file that could use something like this. Sorry, I have not looked at  
DatabaseAuto. I set this up before I knew about it.

########################################################################
# ic_db_config - prints IC config for given database
sub ic_db_config {
   my ($db,$fullname) = @_;
   return unless $db;
   my $dsn = "dbi:mysql:$db:db1;mysql_read_default_file=/ic/.my.cnf";
   my $out = '';
   $out = qq{Variable\tSQLDSN\t$dsn\n} unless $fullname;
   my $dbh = DBI->connect($dsn);
   my @tables = $dbh->tables;
   for (@tables) {
	s/[`'"]+//g;  # end quote `
	my $name = $fullname ? qq{$db.$_} : $_;
	$out .= "Database\t$name\t$name.txt\tSQL\n";
	$out .= "Database\t$name\tDSN\t$dsn\n";
#	$out .= "Database\t$name\t$name.txt\t$dsn\n";
	$out .= "Database\t$name\tREAL_NAME\t$_\n" if $fullname;
	$out .= "Database\t$name\tAUTO_SEQUENCE\t1\n" if &auto_increment 
($dbh,$_);
	$out .= "Database\t$name\tRaiseError\t0\n";
	$out .= "Database\t$name\tPrintError\t1\n";
	$out .= "Database\t$name\tLOG_ERROR_SESSION\t0\n";
	$out .= "NoImport\t$name\n" unless $name =~ /session/;
	my @prefer_null_cols = &get_prefer_null_cols($dbh,$_);
	if ($prefer_null_cols[0]) {
	  my $prefer_null_cols = join ' ',  @prefer_null_cols;
	  $out .= qq{Database\t$name\tPREFER_NULL\t$prefer_null_cols\n};
	}
   }
   $dbh->disconnect;
   return $out;
}

########################################################################
# auto_increment - return 1 if pk is auto_increment
sub auto_increment {
   my ($dbh,$table) = @_;
   return unless $table;
   my $sth = $dbh->prepare(qq{EXPLAIN $table});
   $sth->execute();
   while(my $row = $sth->fetch) {
	return 1 if $row->[5] =~ /auto_increment/;
   }
   return 0;
}

########################################################################
# prefer_null - Return an array of column names  that prefer null
#               for the given table
sub get_prefer_null_cols {
   my ($dbh,$table) = @_;
   my $sql = qq{SHOW COLUMNS FROM $table};
   my $sth = $dbh->prepare($sql);
   $sth->execute();
   my @null_cols;
   while (my $ary_ref = $sth->fetchrow_arrayref()) {
	if ($ary_ref->[1] =~ /timestamp/i || $ary_ref->[5] =~ / 
auto_increment/i) {
	  push @null_cols, $ary_ref->[0];
	}
   }
   return @null_cols;
}

Bill Carr