Re: [ic] DatabaseAuto and auto_increment

[Bill Carr <suppressed>]

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


_______________________________________________
interchange-users mailing list
suppressed
http://www.icdevgroup.org/mailman/listinfo/interchange-users