Well, I've refactored quite a bit of code to allow for more compact
databases...
I'm keeping the old one for now, and creating a new sqlports-compact
version, that's more suitable for automated handling, as the sql queries
are ways more fun than before..
The refactoring means that a lot of more fun stuff becomes possible, or
at least easier than it used to.
Basically, the new code handles most of its database interaction through
an inserter, which can keep full rows for ports, and that handles most
table creations and keyword handling by itself.
Inserter has two specializations, one which creates the old style table,
and the new one, which creates keyword tables, and does not copy columns
that exist in their independent table as basic ports columns.
This is work in progress, it's obvious there's still room for improvement...
Index: Makefile
===================================================================
RCS file: /cvs/ports/databases/sqlports/Makefile,v
retrieving revision 1.10
diff -u -p -r1.10 Makefile
--- Makefile 15 Sep 2007 22:27:39 -0000 1.10
+++ Makefile 17 Aug 2008 13:17:23 -0000
@@ -1,18 +1,18 @@
# $OpenBSD: Makefile,v 1.10 2007/09/15 22:27:39 simon Exp $
-CATEGORIES= databases
-DISTNAME= sqlports-0.5
-PKGNAME= ${DISTNAME}p0
-DISTFILES=
-COMMENT= sqlite database of ports
-MAINTAINER= Marc Espie <[EMAIL PROTECTED]>
+CATEGORIES = databases
+DISTNAME = sqlports-0.5
+PKGNAME = ${DISTNAME}p1
+DISTFILES =
+COMMENT = sqlite database of ports
+MAINTAINER = Marc Espie <[EMAIL PROTECTED]>
-PERMIT_PACKAGE_CDROM= Yes
-PERMIT_PACKAGE_FTP= Yes
-PERMIT_DISTFILES_CDROM= Yes
-PERMIT_DISTFILES_FTP= Yes
+PERMIT_PACKAGE_CDROM = Yes
+PERMIT_PACKAGE_FTP = Yes
+PERMIT_DISTFILES_CDROM =Yes
+PERMIT_DISTFILES_FTP = Yes
-DBNAME= ${WRKBUILD}/sqlports
+DBNAME = ${WRKBUILD}/sqlports
do-build:
cd ${PORTSDIR} && ${MAKE} dump-vars| \
@@ -21,8 +21,8 @@ do-build:
do-install:
${INSTALL_DATA} ${DBNAME} ${PREFIX}/share
-BUILD_DEPENDS= ::databases/p5-DBD-SQLite
-NO_REGRESS= Yes
-NO_CHECKSUM= Yes
+BUILD_DEPENDS = ::databases/p5-DBD-SQLite
+NO_REGRESS = Yes
+NO_CHECKSUM = Yes
.include <bsd.port.mk>
Index: files/mksqlitedb
===================================================================
RCS file: /cvs/ports/databases/sqlports/files/mksqlitedb,v
retrieving revision 1.4
diff -u -p -r1.4 mksqlitedb
--- files/mksqlitedb 27 Dec 2006 11:16:10 -0000 1.4
+++ files/mksqlitedb 17 Aug 2008 13:17:23 -0000
@@ -31,6 +31,209 @@ sub words($)
return split(/\s+/, $v);
}
+package AbstractInserter;
+# this is the object to use to put stuff into the db...
+sub new
+{
+ my ($class, $db, $i) = @_;
+ bless {db => $db, transaction => 0, threshold => $i, vars => {}, done
=> {} }, $class;
+}
+
+sub set
+{
+ my ($self, $ref) = @_;
+ $self->{ref} = $ref;
+}
+
+sub db
+{
+ return shift->{db};
+}
+
+sub last_id
+{
+ return shift->db->func('last_insert_rowid');
+}
+
+sub insert_done
+{
+ my $self = shift;
+ if ($self->{transaction}++ % $self->{threshold} == 0) {
+ $self->db->commit;
+ }
+}
+
+sub new_table
+{
+ my ($self, $name, @cols) = @_;
+
+ return if defined $self->{done}->{$name};
+
+ $self->db->do("DROP TABLE IF EXISTS $name");
+ $self->db->do("CREATE TABLE $name (".join(',', @cols).")");
+ $self->{done}->{$name} = 1;
+}
+
+sub prepare
+{
+ my ($self, $s) = @_;
+ return $self->db->prepare($s);
+}
+
+sub finish_port
+{
+ my $self = shift;
+ my @values = ($self->ref);
+ for my $i (@{$self->{varlist}}) {
+ push(@values, $self->{vars}->{$i});
+ }
+ $self->{insert_ports}->execute(@values);
+}
+
+sub addports
+{
+ my ($self, $var, $value) = @_;
+ $self->{vars}->{$var} = $value;
+}
+
+sub create_tables
+{
+ my ($self, $vars) = @_;
+
+ $self->db->commit;
+ $self->new_table("Ports", $self->pathref." PRIMARY KEY", map {$_."
".$vars->{$_}->sqltype } sort @{$self->{varlist}});
+ $self->{insert_ports} = $self->prepare("INSERT OR REPLACE INTO Ports (".
+ join(", ", "FULLPKGPATH", @{$self->{varlist}}).") VALUES (".
+ join(", ", "?", map {'?'} @{$self->{varlist}}).")");
+ $self->{insert_pathkey} = $self->prepare("INSERT OR REPLACE INTO Paths
(FULLPKGPATH, PKGPATH) VALUES (?, ?)");
+}
+
+sub ref
+{
+ return shift->{ref};
+}
+
+package CompactInserter;
+our @ISA=(qw(AbstractInserter));
+
+
+sub pathref
+{
+ return "FULLPKGPATH INTEGER NOT NULL";
+}
+
+sub create_tables
+{
+ my ($self, $vars) = @_;
+ # create the various tables, dropping old versions
+
+ my @keys;
+
+ while (my ($name, $class) = each %$vars) {
+ if (!defined $class->table) {
+ push(@keys, $name." ".$class->sqltype);
+ push(@{$self->{varlist}}, $name);
+ }
+ $class->create_table($self);
+ }
+
+ $self->new_table("Paths", "ID INTEGER PRIMARY KEY", "FULLPKGPATH TEXT
NOT NULL UNIQUE", "PKGPATH TEXT NOT NULL");
+ $self->SUPER::create_tables($vars);
+ $self->{find_pathkey} = $self->prepare("SELECT ID From Paths WHERE
FULLPKGPATH=(?)");
+ $self->SUPER::create_tables($vars);
+}
+
+sub set_newkey
+{
+ my ($self, $key) = @_;
+
+ # get pathkey for existing value
+ $self->{find_pathkey}->execute($key);
+ my $z = $self->{find_pathkey}->fetchall_arrayref;
+ if (@$z == 0) {
+ # if none, we create one
+ my $path = $key;
+ $path =~ s/\,.*//;
+ $self->{insert_pathkey}->execute($key, $path);
+ $self->set($self->last_id);
+ $self->insert_done;
+ } else {
+ $self->set($z->[0]->[0]);
+ }
+}
+
+sub find_keyword_id
+{
+ my ($self, $key, $t) = @_;
+ $self->{$t}->{find_key1}->execute($key);
+ my $a = $self->{$t}->{find_key1}->fetchrow_arrayref;
+ if (!defined $a) {
+ $self->{$t}->{find_key2}->execute($key);
+ $self->insert_done;
+ return $self->last_id;
+ } else {
+ return $a->[0];
+ }
+}
+
+sub create_keyword_table
+{
+ my ($self, $t) = @_;
+ $self->new_table($t,
+ "KEYREF INTEGER PRIMARY KEY AUTOINCREMENT",
+ "VALUE TEXT NOT NULL UNIQUE");
+ $self->{$t}->{find_key1} = $self->prepare("SELECT KEYREF FROM $t WHERE
VALUE=(?)");
+ $self->{$t}->{find_key2} = $self->prepare("INSERT INTO $t (VALUE)
VALUES (?)");
+}
+
+package NormalInserter;
+our @ISA=(qw(AbstractInserter));
+sub create_tables
+{
+ my ($self, $vars) = @_;
+ # create the various tables, dropping old versions
+
+ my @keys;
+
+ while (my ($name, $class) = each %$vars) {
+ push(@keys, $name." ".$class->sqltype);
+ push(@{$self->{varlist}}, $name);
+ $class->create_table($self);
+ }
+
+ $self->new_table("Paths", "FULLPKGPATH TEXT NOT NULL PRIMARY KEY",
"PKGPATH TEXT NOT NULL");
+ $self->SUPER::create_tables($vars);
+
+}
+
+sub pathref
+{
+ return "FULLPKGPATH TEXT NOT NULL";
+}
+
+sub set_newkey
+{
+ my ($self, $key) = @_;
+
+ my $path = $key;
+ $path =~ s/\,.*//;
+ $self->{insert_pathkey}->execute($key, $path);
+ $self->set($key);
+ $self->insert_done;
+}
+
+# no keyword for this dude
+sub find_keyword_id
+{
+ my ($self, $key, $t) = @_;
+ return $key;
+}
+
+sub create_keyword_table
+{
+ my ($self, $t) = @_;
+}
+
# use a Template Method approach to store the variable values.
# rule: we store each value in the main table, after converting YesNo
@@ -40,9 +243,47 @@ sub words($)
package AnyVar;
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- my $stmt=$db->prepare("UPDATE Ports SET $var=(?) WHERE RowID=(?)");
- $stmt->execute($value, $rowid);
+ my ($class, $ins, $var, $value) = @_;
+ $ins->addports($var, $value);
+}
+
+sub sqltype()
+{
+ return "TEXT";
+}
+
+sub table()
+{
+ return undef;
+}
+
+# by default, there's no separate table
+sub create_table
+{
+}
+
+sub keyword_table()
+{
+ return "Keywords";
+}
+
+package KeyVar;
+our @ISA=(qw(AnyVar));
+sub add
+{
+ my ($class, $ins, $var, $value) = @_;
+ $class->SUPER::add($ins, $var, $ins->find_keyword_id($value,
$class->keyword_table));
+}
+
+sub sqltype()
+{
+ return "INTEGER NOT NULL";
+}
+
+sub create_table
+{
+ my ($self, $inserter) = @_;
+ $inserter->create_keyword_table($self->keyword_table);
}
package YesNoVar;
@@ -50,8 +291,13 @@ our @ISA=(qw(AnyVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- $class->SUPER::add($pkgpath, $db, $var, $value =~ m/^Yes/i ? 1 : undef,
$rowid);
+ my ($class, $ins, $var, $value) = @_;
+ $class->SUPER::add($ins, $var, $value =~ m/^Yes/i ? 1 : undef);
+}
+
+sub sqltype()
+{
+ return "INTEGER";
}
# variable is always defined, but we don't need to store empty values.
@@ -60,9 +306,9 @@ our @ISA=(qw(AnyVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
+ my ($class, $ins, $var, $value) = @_;
return if $value eq '';
- $class->SUPER::add($pkgpath, $db, $var, $value, $rowid);
+ $class->SUPER::add($ins, $var, $value);
}
@@ -72,32 +318,49 @@ sub add
package DependsVar;
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- AnyVar->add($pkgpath, $db, $var, $value, $rowid);
+ my ($class, $ins, $var, $value) = @_;
+ AnyVar::add($class, $ins, $var, $value);
for my $depends (main::words $value) {
my ($libs, $pkgspec, $pkgpath2, $rest) = split(/\:/, $depends);
- my $stmt = $db->prepare("INSERT INTO Depends (FULLPKGPATH,
FULLDEPENDS, DEPENDSPATH, TYPE) VALUES (?, ?, ?, ?)");
- $stmt->execute($pkgpath, $depends, $pkgpath2, $class->type());
+ my $stmt = $ins->prepare("INSERT OR REPLACE INTO Depends
(FULLPKGPATH, FULLDEPENDS, DEPENDSPATH, TYPE) VALUES (?, ?, ?, ?)");
+ $stmt->execute($ins->ref, $depends, $pkgpath2, $class->type);
+ $ins->insert_done;
if ($libs ne '') {
for my $lib (split(/\,/, $libs)) {
- $class->addlib($pkgpath, $db, $lib);
+ $class->addlib($ins, $lib);
}
}
}
}
+sub create_table
+{
+ my ($self, $inserter) = @_;
+ $inserter->new_table("Depends", $inserter->pathref, "FULLDEPENDS TEXT
NOT NULL", "DEPENDSPATH TEXT NOT NULL", "TYPE TEXT NOT NULL");
+}
+
sub addlib
{
}
+sub table()
+{
+ return undef;
+}
+
+sub sqltype()
+{
+ return "TEXT";
+}
+
package LibDependsVar;
our @ISA=(qw(DependsVar));
sub type() { 'L' }
sub addlib
{
- my ($class, $pkgpath, $db, $lib) = @_;
- WantlibVar->addvalue($pkgpath, $db, $lib);
+ my ($class, $ins, $lib) = @_;
+ WantlibVar->addvalue($ins, $lib);
}
package RunDependsVar;
@@ -112,14 +375,35 @@ package RegressDependsVar;
our @ISA=(qw(DependsVar));
sub type() { 'Regress' }
-# Stuff that gets stored in another table as well
+# Stuff that gets stored in another table
package SecondaryVar;
sub addvalue
{
- my ($class, $pkgpath, $db, $value) = @_;
- my $stmt = $db->prepare("INSERT OR REPLACE INTO ".$class->table."
(FULLPKGPATH, VALUE) VALUES (?, ?)");
- $stmt->execute($pkgpath, $value);
+ my ($class, $ins, $value) = @_;
+ my $stmt = $ins->prepare("INSERT OR REPLACE INTO ".$class->table."
(FULLPKGPATH, VALUE) VALUES (?, ?)");
+ $stmt->execute($ins->ref, $value);
+ $ins->insert_done;
+}
+
+sub addkeyword
+{
+ my ($class, $ins, $value) = @_;
+ $class->addvalue($ins, $ins->find_keyword_id($value,
$class->keyword_table));
+}
+
+sub create_table
+{
+ my ($self, $inserter) = @_;
+ $inserter->new_table($self->table, $inserter->pathref,
+ "VALUE TEXT NOT NULL", "UNIQUE(FULLPKGPATH, VALUE)");
+ KeyVar::create_table($self, $inserter);
+}
+
+sub keyword_table()
+{
+ return "Keywords";
}
+sub sqltype() { "TEXT" }
# Generic handling for any blank-separated list
package ListVar;
@@ -127,10 +411,22 @@ our @ISA=(qw(SecondaryVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- AnyVar->add($pkgpath, $db, $var, $value, $rowid);
+ my ($class, $ins, $var, $value) = @_;
+ AnyVar::add($class, $ins, $var, $value);
for my $d (main::words $value) {
- $class->addvalue($pkgpath, $db, $d) if $d ne '';
+ $class->addvalue($ins, $d) if $d ne '';
+ }
+}
+
+package ListKeyVar;
+our @ISA=(qw(SecondaryVar));
+
+sub add
+{
+ my ($class, $ins, $var, $value) = @_;
+ AnyVar::add($class, $ins, $var, $value);
+ for my $d (main::words $value) {
+ $class->addkeyword($ins, $d) if $d ne '';
}
}
@@ -139,8 +435,8 @@ our @ISA=(qw(SecondaryVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- AnyVar->add($pkgpath, $db, $var, $value, $rowid);
+ my ($class, $ins, $var, $value) = @_;
+ AnyVar::add($class, $ins, $var, $value);
my @l = (main::words $value);
while (my $v = shift @l) {
while ($v =~ m/^[^']*\'[^']*$/ || $v =~m/^[^"]*\"[^"]*$/) {
@@ -152,38 +448,41 @@ sub add
if ($v =~ m/^\'(.*)\'$/) {
$v = $1;
}
- $class->addvalue($pkgpath, $db, $v) if $v ne '';
+ $class->addvalue($ins, $v) if $v ne '';
}
}
-package DefinedListVar;
-our @ISA=(qw(ListVar));
+package DefinedListKeyVar;
+our @ISA=(qw(ListKeyVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
+ my ($class, $ins, $var, $value) = @_;
return if $value eq '';
- $class->SUPER::add($pkgpath, $db, $var, $value, $rowid);
+ $class->SUPER::add($ins, $var, $value);
}
package FlavorsVar;
-our @ISA=(qw(DefinedListVar));
+our @ISA=(qw(DefinedListKeyVar));
sub table() { 'Flavors' }
package CategoriesVar;
-our @ISA=(qw(ListVar));
+our @ISA=(qw(ListKeyVar));
sub table() { 'Categories' }
+sub keyword_table() { 'CategoryKeys' }
package MultiVar;
our @ISA=(qw(ListVar));
sub table() { 'Multi' }
package ModulesVar;
-our @ISA=(qw(ListVar));
+our @ISA=(qw(ListKeyVar));
sub table() { 'Modules' }
+sub keyword_table() { 'ModulesKeys' }
package ConfigureVar;
-our @ISA=(qw(DefinedListVar));
+our @ISA=(qw(DefinedListKeyVar));
sub table() { 'Configure' }
+sub keyword_table() { 'ConfigureKeys' }
package ConfigureArgsVar;
our @ISA=(qw(QuotedListVar));
@@ -194,25 +493,30 @@ our @ISA=(qw(ListVar));
sub table() { 'Wantlib' }
sub addvalue
{
- my ($class, $pkgpath, $db, $value) = @_;
- $class->SUPER::addvalue($pkgpath, $db, $value);
+ my ($class, $ins, $value) = @_;
+ $class->SUPER::addvalue($ins, $value);
if ($value =~ m/\.(?:\>?\=)?\d+\.\d+$/) {
- $class->SUPER::addvalue($pkgpath, $db, $`);
+ $class->SUPER::addvalue($ins, $`);
} elsif ($value =~ m/\.(?:\>?\=)?\d+$/) {
- $class->SUPER::addvalue($pkgpath, $db, $`);
+ $class->SUPER::addvalue($ins, $`);
}
}
+package OnlyForArchVar;
+our @ISA=(qw(QuotedListKeyVar));
+sub table() { 'OnlyForArch' }
+sub keyword_table() { 'Arches' }
+
package FileVar;
our @ISA=(qw(SecondaryVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- AnyVar->add($pkgpath, $db, $var, $value, $rowid);
+ my ($class, $ins, $var, $value) = @_;
+ AnyVar::add($class, $ins, $var, $value);
open my $file, '<', $value or return;
local $/ = undef;
- $class->addvalue($pkgpath, $db, <$file>);
+ $class->addvalue($ins, <$file>);
}
sub table() { 'Descr' }
@@ -222,15 +526,37 @@ our @ISA=(qw(AnyVar));
sub add
{
- my ($class, $pkgpath, $db, $var, $value, $rowid) = @_;
- $class->SUPER::add($pkgpath, $db, $var, $value, $rowid);
- my $stmt = $db->prepare("INSERT INTO Shared_Libs (FULLPKGPATH, LIBNAME,
VERSION) VALUES (?, ?, ?)");
+ my ($class, $ins, $var, $value) = @_;
+ $class->SUPER::add($ins, $var, $value);
+ my $stmt = $ins->prepare("INSERT OR REPLACE INTO Shared_Libs
(FULLPKGPATH, LIBNAME, VERSION) VALUES (?, ?, ?)");
my %t = main::words($value);
while (my ($k, $v) = each %t) {
- $stmt->execute($pkgpath, $k, $v);
+ $stmt->execute($ins->ref, $k, $v);
+ $ins->insert_done;
}
}
+sub create_table
+{
+ my ($self, $inserter) = @_;
+ $inserter->new_table("Shared_Libs", $inserter->pathref,
+ "LIBNAME TEXT NOT NULL", "VERSION TEXT NOT NULL",
+ "UNIQUE (FULLPKGPATH, LIBNAME)");
+}
+
+package EmailVar;
+our @ISA=(qw(KeyVar));
+sub keyword_table()
+{
+ return "Email";
+}
+
+package YesKeyVar;
+our @ISA=(qw(KeyVar));
+sub keyword_table()
+{
+ return "Keywords2";
+}
package main;
@@ -242,8 +568,14 @@ if (@ARGV > 0) {
} else {
$dbname = 'sqlports';
}
+my @inserters;
my $db =DBI->connect("dbi:SQLite:dbname=$dbname", '', '', {AutoCommit => 0});
+my $db2 =DBI->connect("dbi:SQLite:dbname=$dbname-compact", '', '', {AutoCommit
=> 0});
+push(@inserters, NormalInserter->new($db, 1000));
+push(@inserters, CompactInserter->new($db2, 1000));
+
+
my $vars = {
AUTOCONF_VERSION => 'AnyVar',
AUTOMAKE_VERSION => 'AnyVar',
@@ -263,7 +595,7 @@ my $vars = {
HOMEPAGE => 'AnyVar',
IS_INTERACTIVE => 'AnyVar',
LIB_DEPENDS => 'LibDependsVar',
- MAINTAINER=> 'AnyVar',
+ MAINTAINER=> 'EmailVar',
MASTER_SITES => 'AnyVar',
MASTER_SITES0 => 'AnyVar',
MASTER_SITES1 => 'AnyVar',
@@ -281,17 +613,17 @@ my $vars = {
NO_REGRESS => 'YesNoVar',
ONLY_FOR_ARCHS => 'AnyVar',
PACKAGES => 'AnyVar',
- PERMIT_DISTFILES_CDROM => 'AnyVar',
- PERMIT_DISTFILES_FTP=> 'AnyVar',
- PERMIT_PACKAGE_CDROM => 'AnyVar',
- PERMIT_PACKAGE_FTP=> 'AnyVar',
+ PERMIT_DISTFILES_CDROM => 'YesKeyVar',
+ PERMIT_DISTFILES_FTP=> 'YesKeyVar',
+ PERMIT_PACKAGE_CDROM => 'YesKeyVar',
+ PERMIT_PACKAGE_FTP=> 'YesKeyVar',
PKGNAME => 'AnyVar',
- PKG_ARCH => 'AnyVar',
+ PKG_ARCH => 'KeyVar',
PSEUDO_FLAVORS => 'DefinedVar',
REGRESS_DEPENDS => 'RegressDependsVar',
REGRESS_IS_INTERACTIVE => 'AnyVar',
RUN_DEPENDS => 'RunDependsVar',
- SEPARATE_BUILD => 'AnyVar',
+ SEPARATE_BUILD => 'YesKeyVar',
SHARED_LIBS => 'SharedLibsVar',
SHARED_ONLY => 'YesNoVar',
SUBPACKAGE => 'DefinedVar',
@@ -303,37 +635,16 @@ my $vars = {
WANTLIB => 'WantlibVar',
};
-# create the various tables, dropping old versions
-
-for my $t (qw(Categories Flavors Multi Modules Configure ConfigureArgs
Wantlib)) {
- $db->do("DROP TABLE IF EXISTS $t");
- $db->do("CREATE TABLE $t (FULLPKGPATH TEXT NOT NULL, VALUE TEXT NOT NULL,
UNIQUE(FULLPKGPATH, VALUE))");
-}
-$db->do("DROP TABLE IF EXISTS Depends");
-$db->do("CREATE TABLE Depends (FULLPKGPATH TEXT NOT NULL, FULLDEPENDS TEXT NOT
NULL, DEPENDSPATH TEXT NOT NULL, TYPE TEXT NOT NULL)");
-$db->do("DROP TABLE IF EXISTS Shared_Libs");
-$db->do("CREATE TABLE Shared_Libs (FULLPKGPATH TEXT NOT NULL, LIBNAME TEXT NOT
NULL, VERSION TEXT NOT NULL, UNIQUE (FULLPKGPATH, LIBNAME))");
-$db->do("DROP TABLE IF EXISTS Ports");
-$db->do("CREATE TABLE Ports (FULLPKGPATH TEXT NOT NULL PRIMARY KEY, ".
- join(',', (map {$_." TEXT"} (keys %$vars))).")");
-$db->do("DROP TABLE IF EXISTS Paths");
-$db->do("CREATE TABLE Paths (FULLPKGPATH TEXT NOT NULL PRIMARY KEY, PKGPATH
TEXT NOT NULL)");
-$db->do("DROP TABLE IF EXISTS Descr");
-$db->do("CREATE TABLE Descr (FULLPKGPATH TEXT NOT NULL PRIMARY KEY, VALUE TEXT
NOT NULL)");
-$db->commit();
-
-my $stmt = $db->prepare("SELECT RowID FROM Ports WHERE FULLPKGPATH=(?)");
-my $stmt2= $db->prepare("INSERT INTO Ports (FULLPKGPATH) VALUES (?)");
-my $stmt3= $db->prepare("INSERT INTO Paths (FULLPKGPATH, PKGPATH) VALUES (?,
?)");
+for my $inserter (@inserters) {
+ $inserter->create_tables($vars);
+}
-my $i = 0;
-my $rowid;
my $lastkey;
while (<STDIN>) {
chomp;
# kill noise
if (m/^\=\=\=/) {
- print $_, "\n";
+ print "---", $_, "\n";
next;
}
next unless m/^(.*?)\.([A-Z][A-Z_0-9]*)\=(.*)$/;
@@ -345,28 +656,22 @@ while (<STDIN>) {
}
if (!(defined $lastkey) || $key ne $lastkey) {
- # get rowid for existing value
- $stmt->execute($key);
- my $z = $stmt->fetchall_arrayref;
- if (@$z == 0) {
- # if none, we create one
- $stmt2->execute($key);
- my $path = $key;
- $path =~ s/\,.*//;
- $stmt3->execute($key, $path);
- $stmt->execute($key);
- $z = $stmt->fetchall_arrayref;
+ if (defined $lastkey) {
+ for my $inserter (@inserters) {
+ $inserter->finish_port;
+ }
+ }
+ for my $inserter (@inserters) {
+ $inserter->set_newkey($key);
}
- $rowid = $z->[0]->[0];
$lastkey = $key;
}
- $vars->{$var}->add($key, $db, $var, $value, $rowid);
-
- # and we commit just once every 1000 transactions, for efficiency
- $i++;
- if ($i % 1000 == 0) {
- $db->commit();
+ for my $inserter (@inserters) {
+ $vars->{$var}->add($inserter, $var, $value);
}
}
-$db->commit();
+for my $inserter (@inserters) {
+ $inserter->finish_port;
+ $inserter->db->commit;
+}
