[PATCH] Grys patch for captcha via email

[Ian Jones <ianj@xxxxxxxxxxxxx>]

---
 botnow_db_consolidate_tables.pl | 35 +++++++++++++++++++++++++++++++++
 table.sql                       | 29 +++------------------------
 2 files changed, 38 insertions(+), 26 deletions(-)
 create mode 100644 botnow_db_consolidate_tables.pl

diff --git a/botnow_db_consolidate_tables.pl b/botnow_db_consolidate_tables.pl
new file mode 100644
index 0000000..56e357a
--- /dev/null
+++ b/botnow_db_consolidate_tables.pl
@@ -0,0 +1,35 @@
+#!/usr/bin/perl
+#
+# Written by IanJ@xxxxxxxxxxxxx 27/04/2023
+#
+use strict;
+use DBI;
+
+my $DBFILE = 'botnow.db';
+
+my $dbh = DBI->connect("dbi:SQLite:dbname=$DBFILE","");
+
+# Create service table
+print "Create table 'service'.\n";
+$dbh->do("CREATE table service ( 
+	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,
+	type VARCHAR(10));");
+
+# Consolidate data to service table
+foreach my $table ('mail', 'bnc', 'shell')
+{
+	print "Copying '$table' data to 'service'.\n";
+	$dbh->do("insert into service (hashid, ircid, wwwid, smtpid, username, email, password, localtime, captcha, type) \
+		select hashid, ircid, wwwid, smtpid, username, email, password, localtime, captcha, '$table' from $table;");
+}
+
+print "Finished - hopefully with no errors!\n";
diff --git a/table.sql b/table.sql
index d9fb362..059d9f3 100644
--- a/table.sql
+++ b/table.sql
@@ -1,4 +1,4 @@
-CREATE TABLE bnc (
+CREATE TABLE service (
   id INTEGER PRIMARY KEY,
   hashid VARCHAR(100),
   ircid INTEGER,
@@ -8,31 +8,8 @@ CREATE TABLE bnc (
   email VARCHAR(100),
   password VARCHAR(100),
   localtime default current_timestamp,
-  captcha INTEGER
-);
-CREATE TABLE shell (
-  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
-);
-CREATE TABLE mail (
-  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
+  captcha INTEGER,
+  type VARCHAR(10)
 );
 CREATE TABLE www (
   id INTEGER PRIMARY KEY,
-- 
2.37.3