1 e112f2ae 2023-04-14 jrmu #!/usr/bin/perl
 2 e112f2ae 2023-04-14 jrmu #
 3 e112f2ae 2023-04-14 jrmu # Written by IanJ@nastycode.com 13/04/2023
 4 e112f2ae 2023-04-14 jrmu #
 5 e112f2ae 2023-04-14 jrmu use strict;
 6 e112f2ae 2023-04-14 jrmu use DBI;
 7 e112f2ae 2023-04-14 jrmu 
 8 e112f2ae 2023-04-14 jrmu my $DBFILE = 'botnow.db';
 9 e112f2ae 2023-04-14 jrmu my $OLDDBFILE = 'botnow.old.db';
10 e112f2ae 2023-04-14 jrmu 
11 e112f2ae 2023-04-14 jrmu my $dbh = DBI->connect("dbi:SQLite:dbname=$DBFILE","");
12 e112f2ae 2023-04-14 jrmu 
13 e112f2ae 2023-04-14 jrmu foreach my $table ("mail", "shell", "bnc")
14 e112f2ae 2023-04-14 jrmu {
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");
18 e112f2ae 2023-04-14 jrmu 
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);");
32 e112f2ae 2023-04-14 jrmu 
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");
36 e112f2ae 2023-04-14 jrmu 
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");
46 e112f2ae 2023-04-14 jrmu 
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");
50 e112f2ae 2023-04-14 jrmu 
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");
55 e112f2ae 2023-04-14 jrmu }
56 e112f2ae 2023-04-14 jrmu 
57 e112f2ae 2023-04-14 jrmu print "Finished - hopefully with no errors!\n";