1 e112f2ae 2023-04-14 jrmu #!/usr/bin/perl
3 e112f2ae 2023-04-14 jrmu # Written by IanJ@nastycode.com 13/04/2023
8 e112f2ae 2023-04-14 jrmu my $DBFILE = 'botnow.db';
9 e112f2ae 2023-04-14 jrmu my $OLDDBFILE = 'botnow.old.db';
11 e112f2ae 2023-04-14 jrmu my $dbh = DBI->connect("dbi:SQLite:dbname=$DBFILE","");
13 e112f2ae 2023-04-14 jrmu foreach my $table ("mail", "shell", "bnc")
15 e112f2ae 2023-04-14 jrmu # Delete unconfirmed captcha entries
16 e112f2ae 2023-04-14 jrmu print "Deleting unconfirmed captcha entries.\n";
17 e112f2ae 2023-04-14 jrmu $dbh->do("delete from $table where password is null");
19 e112f2ae 2023-04-14 jrmu # Create table with new schema (seems you can't add/modify autoindex/timestamp fields)
20 e112f2ae 2023-04-14 jrmu print "Create table '".$table."_new' with new schema for auto timestamp.\n";
21 e112f2ae 2023-04-14 jrmu $dbh->do("CREATE table ".$table."_new (
22 e112f2ae 2023-04-14 jrmu id INTEGER PRIMARY KEY,
23 e112f2ae 2023-04-14 jrmu hashid VARCHAR(100),
24 e112f2ae 2023-04-14 jrmu ircid INTEGER,
25 e112f2ae 2023-04-14 jrmu wwwid INTEGER,
26 e112f2ae 2023-04-14 jrmu smtpid INTEGER,
27 e112f2ae 2023-04-14 jrmu username VARCHAR(32),
28 e112f2ae 2023-04-14 jrmu email VARCHAR(100),
29 e112f2ae 2023-04-14 jrmu password VARCHAR(100),
30 e112f2ae 2023-04-14 jrmu localtime default current_timestamp,
31 e112f2ae 2023-04-14 jrmu captcha INTEGER);");
33 e112f2ae 2023-04-14 jrmu # Copy data to new table
34 e112f2ae 2023-04-14 jrmu print "Copying data from '$table' to '".$table."_new' table.\n";
35 e112f2ae 2023-04-14 jrmu $dbh->do("insert into ".$table."_new select * from $table");
37 e112f2ae 2023-04-14 jrmu # Copy data from old db into new table
38 e112f2ae 2023-04-14 jrmu print "Copying data from old database '$table' to '".$table."_new'.\n";
39 e112f2ae 2023-04-14 jrmu $dbh->do("attach database '$OLDDBFILE' as olddb");
40 e112f2ae 2023-04-14 jrmu # Causes column mismatch without adding hashid for shell
41 e112f2ae 2023-04-14 jrmu $dbh->do("alter table olddb.shell add hashid varchar(100)") if ($table eq "shell");
42 e112f2ae 2023-04-14 jrmu $dbh->do("insert into ".$table."_new (hashid, ircid, wwwid, smtpid, username, email, password, captcha) \
43 e112f2ae 2023-04-14 jrmu select hashid, ircid, wwwid, smtpid, username, email, password, captcha from olddb.".$table." \
44 e112f2ae 2023-04-14 jrmu where password is not null");
45 e112f2ae 2023-04-14 jrmu $dbh->do("detach olddb");
47 e112f2ae 2023-04-14 jrmu # Set timestamp to null as we don't know the true date of entry
48 e112f2ae 2023-04-14 jrmu print "Set timestamps to null as we don't know the true date of entries.\n";
49 e112f2ae 2023-04-14 jrmu $dbh->do("update ".$table."_new set localtime = null");
51 e112f2ae 2023-04-14 jrmu # Rename original table (don't delete it yet)
52 e112f2ae 2023-04-14 jrmu print "Rename '$table' to '".$table."_backup', rename '".$table."_new' to '$table'.\n";
53 e112f2ae 2023-04-14 jrmu $dbh->do("alter table $table rename to ".$table."_backup");
54 e112f2ae 2023-04-14 jrmu $dbh->do("alter table ".$table."_new rename to $table");
57 e112f2ae 2023-04-14 jrmu print "Finished - hopefully with no errors!\n";