commit e112f2ae812467b89f1ff59bb00ee89bc15e25f9
from: jrmu <jrmu@ircnow.org>
date: Fri Apr 14 00:58:04 2023 UTC

Add new file to merge old database with new one

commit - 5863bc781b628f072ef06924f2962d35a68196f3
commit + e112f2ae812467b89f1ff59bb00ee89bc15e25f9
blob - /dev/null
blob + bba47dd1b68a1458706873b1e84dc9e3b00df714 (mode 644)
--- /dev/null
+++ botnow_db_fixer.pl
@@ -0,0 +1,57 @@
+#!/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";
blob - b75a4b83b6367cda0746f781ee96956bad41fa8d
blob + 663c4d7ffa12688c9b62b0d7d83cd8bc5e5980b8
--- report
+++ report
@@ -1,3 +1,4 @@
+# Original author is izzyb@planetofnix.com
 #!/usr/bin/perl
 #
 use strict;