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