Commit Diff
Diff:
5863bc781b628f072ef06924f2962d35a68196f3
e112f2ae812467b89f1ff59bb00ee89bc15e25f9
Commit:
e112f2ae812467b89f1ff59bb00ee89bc15e25f9
Tree:
0d8b2c8a60b317becd99fe45e941d3c928373af4
Author:
jrmu <jrmu@ircnow.org>
Committer:
jrmu <jrmu@ircnow.org>
Date:
Fri Apr 14 00:58:04 2023 UTC
Message:
Add new file to merge old database with new one
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;
IRCNow