-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
On Tue, 25 May 2004, Puneet Kishor wrote:
Do want to elaborate more? Why is the code not correct even with the correction? We will all learn from your input. Btw, I do start with the disclaimer that the code is untested. It is merely meant as a guidance for Wade [who, since we haven't heard back from, may already know what I set out to tell him ;-) ]. Let us (me) know what the correct code would be.
Yeah - that was a pointer toward what I needed, but didn't accomplish what I required (which was a mimic of the .dump command).
I do understand that. That is why the code was merely a pointer in the right direction. Unfortunately, while SQLite has a COPY command (now on the endangered list), it does not have a DUMP command. So, one would have to use the .dump utility from SQLite shell. The objective was to mimic it with Perl. Also, afaik, there doesn't seem to be any "easy" facility to reconstruct the schema (I had asked a question on this a while back on this list). What I ended up doing was taking the response from querying the sqlite_master table and extracting the column names and types, etc. In any case, with enough fiddling around, the same concept as in my code could be applied to indexes, constraints, et al.
Here's what I ended up with - it's not incredibly robust or portable, but does what I need:
# Get the table schema information
my $sth = $dbh->table_info();
while ( my $row = $sth->fetchrow_hashref )
{
if ( ( $row->{TABLE_NAME} eq "categories" )
|| ( $row->{TABLE_NAME} eq "mrvoice" ) )
{
print DUMPFILE "DROP TABLE $row->{TABLE_NAME};\n";
my $schema = $row->{sqlite_sql};
$schema =~ s/\n//g;
print DUMPFILE "$schema;\n";
}
}
# Dump the contents of the categories table
my $query = "SELECT * FROM categories";
$sth = $dbh->prepare($query);
$sth->execute();
while ( my @row = $sth->fetchrow_array )
{
my @quoted;
print DUMPFILE "INSERT INTO categories VALUES (";
foreach my $item (@row)
{
push( @quoted, $dbh->quote($item) );
} print DUMPFILE join( ",", @quoted ) . ");\n";
}contents of the mrvoice table
$query = "SELECT * FROM mrvoice";
$sth = $dbh->prepare($query);
$sth->execute();
while ( my @row = $sth->fetchrow_array )
{
my @quoted;
print DUMPFILE "INSERT INTO mrvoice VALUES (";
foreach my $item (@row)
{
push( @quoted, $dbh->quote($item) );
} print DUMPFILE join( ",", @quoted ) . ");\n";
}-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (FreeBSD)
iD8DBQFAs2B/o4DwsyRGDscRAtuLAKC+rluE50HWmbk5JU0bK8BI0eykXgCeK5wc XNAyw1TCrCW9pyEOWeskElQ= =tEl2 -----END PGP SIGNATURE-----
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
