+
+sub table_name {
+ return '"' . $arch . $schema_suffix . '".packages';
+}
+
+sub user_table_name {
+ return '"' . $arch . $schema_suffix . '".users';
+}
+
+sub transactions_table_name {
+ return '"' . $arch . $schema_suffix . '".transactions';
+}
+
+sub pkg_history_table_name {
+ return '"' . $arch . $schema_suffix . '".pkg_history';
+}
+
+sub get_readonly_source_info {
+ my $name = shift;
+ # SELECT FLOOR(EXTRACT('epoch' FROM age(localtimestamp, '2010-01-22 23:45')) / 86400) -- change to that?
+ my $q = "SELECT rel, priority, state_change, permbuildpri, section, buildpri, failed, state, binary_nmu_changelog, bd_problem, version, package, distribution, installed_version, notes, failed_category, builder, old_failed, previous_state, binary_nmu_version, depends, extract(days from date_trunc('days', now() - state_change)) as state_days"
+ . ", (SELECT max(build_time) FROM ".pkg_history_table_name()." WHERE pkg_history.package = packages.package AND pkg_history.distribution = packages.distribution AND result = 'successful') AS successtime"
+ . ", (SELECT max(build_time) FROM ".pkg_history_table_name()." WHERE pkg_history.package = packages.package AND pkg_history.distribution = packages.distribution ) AS anytime"
+ . " FROM " . table_name()
+ . ' WHERE package = ? AND distribution = ?';
+ my $pkg = $dbh->selectrow_hashref( $q,
+ undef, $name, $distribution);
+ return $pkg;
+}
+
+sub get_source_info {
+ my $name = shift;
+ my $pkg = $dbh->selectrow_hashref('SELECT *, extract(days from date_trunc(\'days\', now() - state_change)) as state_days FROM ' .
+ table_name() . ' WHERE package = ? AND distribution = ?' .
+ ' FOR UPDATE',
+ undef, $name, $distribution);
+ return $pkg;
+}
+
+sub get_all_source_info {
+ my %options = @_;
+
+ my $q = "SELECT rel, priority, state_change, permbuildpri, section, buildpri, failed, state, binary_nmu_changelog, bd_problem, version, package, distribution, installed_version, notes, failed_category, builder, old_failed, previous_state, binary_nmu_version, depends, extract(days from date_trunc('days', now() - state_change)) as state_days"
+# . ", (SELECT max(build_time) FROM ".pkg_history_table_name()." WHERE pkg_history.package = packages.package AND pkg_history.distribution = packages.distribution AND result = 'successful') AS successtime"
+# . ", (SELECT max(build_time) FROM ".pkg_history_table_name()." WHERE pkg_history.package = packages.package AND pkg_history.distribution = packages.distribution ) AS anytime"
+ . " FROM " . table_name()
+ . " left join ( "
+ . "select distinct on (package, distribution) build_time, package, distribution from ".pkg_history_table_name()." where result = 'successful' order by package, distribution, timestamp "
+ . " ) as successtime using (package, distribution) "
+ . " left join ( "
+ . "select distinct on (package, distribution) build_time, package, distribution from ".pkg_history_table_name()." order by package, distribution, timestamp desc"
+ . " ) as anytime using (package, distribution) "
+ . " WHERE TRUE ";
+ my @args = ();
+ if ($distribution) {
+ my @dists = split(/[, ]+/, $distribution);
+ $q .= ' AND ( distribution = ? '.(' OR distribution = ? ' x $#dists).' )';
+ foreach my $d ( @dists ) {
+ push @args, ($d);
+ }
+ }
+ if ($options{state} && uc($options{state}) ne "ALL") {
+ $q .= ' AND upper(state) = ? ';
+ push @args, uc($options{state});
+ }
+
+ if ($options{user}) {
+ #this basically means "this user, or no user at all":
+ $q .= ' AND (builder = ? OR upper(state) = ?)';
+ push @args, $options{user};
+ push @args, "NEEDS-BUILD";
+ }
+
+ if ($options{category}) {
+ $q .= ' AND failed_category <> ? AND upper(state) = ? ';
+ push @args, $options{category};
+ push @args, "FAILED";
+ }
+
+ if ($options{list_min_age} > 0) {
+ $q .= ' AND age(state_change) > ? ';
+ push @args, $options{list_min_age} . " days";
+ }
+
+ if ($options{list_min_age} < 0) {
+ $q .= ' AND age(state_change) < ? ';
+ push @args, -$options{list_min_age} . " days";
+ }
+
+ my $db = $dbh->selectall_hashref($q, 'package', undef, @args);
+ return $db;
+}
+
+sub update_source_info {
+ my $pkg = shift;
+
+ my $pkg2 = get_source_info($pkg->{'package'});
+ if (! defined $pkg2)
+ {
+ add_source_info($pkg);
+ }
+
+ $dbh->do('UPDATE ' . table_name() . ' SET ' .
+ 'version = ?, ' .
+ 'state = ?, ' .
+ 'section = ?, ' .
+ 'priority = ?, ' .
+ 'installed_version = ?, ' .
+ 'previous_state = ?, ' .
+ (($pkg->{'do_state_change'}) ? "state_change = now()," : "").
+ 'notes = ?, ' .
+ 'builder = ?, ' .
+ 'failed = ?, ' .
+ 'old_failed = ?, ' .
+ 'binary_nmu_version = ?, ' .
+ 'binary_nmu_changelog = ?, ' .
+ 'failed_category = ?, ' .
+ 'permbuildpri = ?, ' .
+ 'buildpri = ?, ' .
+ 'depends = ?, ' .
+ 'rel = ?, ' .
+ 'bd_problem = ? ' .
+ 'WHERE package = ? AND distribution = ?',
+ undef,
+ $pkg->{'version'},
+ $pkg->{'state'},
+ $pkg->{'section'},
+ $pkg->{'priority'},
+ $pkg->{'installed_version'},
+ $pkg->{'previous_state'},
+ $pkg->{'notes'},
+ $pkg->{'builder'},
+ $pkg->{'failed'},
+ $pkg->{'old_failed'},
+ $pkg->{'binary_nmu_version'},
+ $pkg->{'binary_nmu_changelog'},
+ $pkg->{'failed_category'},
+ $pkg->{'permbuildpri'},
+ $pkg->{'buildpri'},
+ $pkg->{'depends'},
+ $pkg->{'rel'},
+ $pkg->{'bd_problem'},
+ $pkg->{'package'},
+ $distribution) or die $dbh->errstr;
+}
+
+sub add_source_info {
+ my $pkg = shift;
+ $dbh->do('INSERT INTO ' . table_name() .
+ ' (package, distribution) values (?, ?)',
+ undef, $pkg->{'package'}, $distribution) or die $dbh->errstr;
+}
+
+sub del_source_info {
+ my $name = shift;
+ $dbh->do('DELETE FROM ' . table_name() .
+ ' WHERE package = ? AND distribution = ?',
+ undef, $name, $distribution) or die $dbh->errstr;
+}
+
+sub get_user_info {
+ my $name = shift;
+ my $user = $dbh->selectrow_hashref('SELECT * FROM ' .
+ user_table_name() . ' WHERE username = ? AND distribution = ?',
+ undef, $name, $distribution);
+ return $user;
+}
+
+sub update_user_info {
+ my $user = shift;
+ $dbh->do('UPDATE ' . user_table_name() .
+ ' SET last_seen = now() WHERE username = ?' .
+ ' AND distribution = ?',
+ undef, $user, $distribution)
+ or die $dbh->errstr;
+}
+
+
+sub add_user_info {
+ my $user = shift;
+ $dbh->do('INSERT INTO ' . user_table_name() .
+ ' (username, distribution, last_seen)' .
+ ' values (?, ?, now())',
+ undef, $user, $distribution)
+ or die $dbh->errstr;
+}
+
+sub lock_table()
+{
+ $dbh->do('LOCK TABLE ' . table_name() .
+ ' IN EXCLUSIVE MODE', undef) or die $dbh->errstr;
+}
+