#!/usr/bin/perl # # Written by IanJ@nastycode.com 13/04/2023 # use strict; use DBI; my $DBFILE = 'botnow.db'; my $OLDDBFILE = 'botnow.old.db'; my $dbh = DBI->connect("dbi:SQLite:dbname=$DBFILE",""); foreach my $table ("mail", "shell", "bnc") { # Delete unconfirmed captcha entries print "Deleting unconfirmed captcha entries.\n"; $dbh->do("delete from $table where password is null"); # Create table with new schema (seems you can't add/modify autoindex/timestamp fields) print "Create table '".$table."_new' with new schema for auto timestamp.\n"; $dbh->do("CREATE table ".$table."_new ( id INTEGER PRIMARY KEY, hashid VARCHAR(100), ircid INTEGER, wwwid INTEGER, smtpid INTEGER, username VARCHAR(32), email VARCHAR(100), password VARCHAR(100), localtime default current_timestamp, captcha INTEGER);"); # Copy data to new table print "Copying data from '$table' to '".$table."_new' table.\n"; $dbh->do("insert into ".$table."_new select * from $table"); # Copy data from old db into new table print "Copying data from old database '$table' to '".$table."_new'.\n"; $dbh->do("attach database '$OLDDBFILE' as olddb"); # Causes column mismatch without adding hashid for shell $dbh->do("alter table olddb.shell add hashid varchar(100)") if ($table eq "shell"); $dbh->do("insert into ".$table."_new (hashid, ircid, wwwid, smtpid, username, email, password, captcha) \ select hashid, ircid, wwwid, smtpid, username, email, password, captcha from olddb.".$table." \ where password is not null"); $dbh->do("detach olddb"); # Set timestamp to null as we don't know the true date of entry print "Set timestamps to null as we don't know the true date of entries.\n"; $dbh->do("update ".$table."_new set localtime = null"); # Rename original table (don't delete it yet) print "Rename '$table' to '".$table."_backup', rename '".$table."_new' to '$table'.\n"; $dbh->do("alter table $table rename to ".$table."_backup"); $dbh->do("alter table ".$table."_new rename to $table"); } print "Finished - hopefully with no errors!\n";