From: John Runyon <redacted>
Date: Wed, 8 May 2024 09:28:35 +0000 (-0600)
Subject: add sqlite database support
X-Git-Url: https://jfr.im/git/erebus.git/commitdiff_plain/6b4ba0b651d53a40629a59d4a5df8767217c7386?hp=5b8f6176bdaccb440e3c7ed67a7cec4db25e5c5f

add sqlite database support

these are the current commands which interact with the database and
should be tested for a new database:

join part stafflist whoami glevel clevel whoami

(make sure glevel and clevel changes are reflected in the whoami; make
sure no errors are logged from the queries)
---

diff --git a/.gitignore b/.gitignore
index 620d76e..82461e4 100644
--- a/.gitignore
+++ b/.gitignore
@@ -9,3 +9,4 @@ nohup.out
 pidfile
 logfile
 oldlogs/
+erebus.db
diff --git a/README.md b/README.md
index 2ec3dc8..2e1a2d1 100644
--- a/README.md
+++ b/README.md
@@ -5,9 +5,17 @@ Getting started
 ---------------
 - `cp bot.config.example bot.config`
 - `vim bot.config`
-- Create a MySQL database, i.e. `CREATE DATABASE foo; GRANT ALL ON foo.* TO ...`
-- `mysql <dump.sql`
-- Add your auth (main NickServ nick/account name on other networks) in the database as owner: `INSERT INTO users VALUES ('YourAuth', 100);`
+- Pick a database:
+  - MySQL:
+    - Create a MySQL database, i.e. `CREATE DATABASE foo; GRANT ALL ON foo.* TO ...`
+    - `mysql <mysql.sql`
+    - dbtype=mysql, dbhost/dbuser/dbpass/dbname with connection info
+  - SQLite
+    - `sqlite3 erebus.db <sqlite.sql`
+    - dbtype=sqlite, dbhost=erebus.db (or other database path)
+- Populate at least the `bots` and `users` table:
+  - Add a bot: `INSERT INTO bots VALUES ('nick', 'user', NULL, NULL, NULL, 1, 0)`
+  - Add your auth (main NickServ nick/account name on other networks) in the database as owner: `INSERT INTO users VALUES ('YourAuth', 100);`
 - `./run`
 
 Install croncheck.sh in your crontab, if desired.  
diff --git a/erebus.py b/erebus.py
index 1a06ed3..12be844 100644
--- a/erebus.py
+++ b/erebus.py
@@ -194,17 +194,24 @@ class Erebus(object): #singleton to pass around
 			self.potype = "select"
 			self.fdlist = []
 
-	def query(self, *args, **kwargs):
-		if 'noretry' in kwargs:
-			noretry = kwargs['noretry']
-			del kwargs['noretry']
-		else:
-			noretry = False
+	def query(self, sql, parameters=[], noretry=False):
+		# Callers use %s-style (paramstyle='format') placeholders in queries.
+		# There's no provision for a literal '%s' present inside the query; stuff it in a parameter instead.
+		if db_api.paramstyle == 'format' or db_api.paramstyle == 'pyformat': # mysql, postgresql
+			# psycopg actually asks for a mapping with %(name)s style (pyformat) but it will accept %s style.
+			pass
+		elif db_api.paramstyle == 'qmark': # sqlite doesn't like %s style.
+			parameters = [str(p) for p in parameters]
+			sql = sql.replace('%s', '?') # hope that wasn't literal, oopsie
+
+		log_noretry = ''
+		if noretry:
+			log_noretry = ', noretry=True'
+		self.log("[SQL]", "?", "query(%r, %r%s)" % (sql, parameters, log_noretry))
 
-		self.log("[SQL]", "?", "query(%s, %s)" % (', '.join([repr(i) for i in args]), ', '.join([str(key)+"="+repr(kwargs[key]) for key in kwargs])))
 		try:
 			curs = self.db.cursor()
-			res = curs.execute(*args, **kwargs)
+			res = curs.execute(sql, parameters)
 			if res:
 				return curs
 			else:
@@ -216,11 +223,12 @@ class Erebus(object): #singleton to pass around
 			self.log("[SQL]", "!", "DB error! %r" % (e))
 			if not noretry:
 				dbsetup()
-				return self.query(*args, noretry=True, **kwargs)
+				return self.query(sql, parameters, noretry=True)
 			else:
 				raise e
 
 	def querycb(self, cb, *args, **kwargs):
+		# TODO this should either get thrown out with getdb()/returndb(), or else be adjusted to make use of it.
 		def run_query():
 			cb(self.query(*args, **kwargs))
 		threading.Thread(target=run_query).start()
@@ -298,7 +306,9 @@ class Erebus(object): #singleton to pass around
 		return [u for u in self.users.values() if u.auth == auth.lower()]
 
 	def getdb(self):
-		"""Get a DB object. The object must be returned to the pool after us, using returndb()."""
+		"""Get a DB object. The object must be returned to the pool after us, using returndb(). This is intended for use from child threads.
+			It should probably be treated as deprecated though. Where possible new modules should avoid using threads.
+			In the future, timers will be provided (manipulating the timeout_seconds of the poll() method), and that should mostly be used in place of threading."""
 		return self.dbs.pop()
 
 	def returndb(self, db):
@@ -360,6 +370,8 @@ def dbsetup():
 	dbtype = cfg.get('erebus', 'dbtype', 'mysql')
 	if dbtype == 'mysql':
 		_dbsetup_mysql()
+	elif dbtype == 'sqlite':
+		_dbsetup_sqlite()
 	else:
 		main.log('*', '!', 'Unknown dbtype in config: %s' % (dbtype))
 
@@ -370,6 +382,14 @@ def _dbsetup_mysql():
 		main.dbs.append(db_api.connect(host=cfg.dbhost, user=cfg.dbuser, passwd=cfg.dbpass, db=cfg.dbname, cursorclass=MySQLdb.cursors.DictCursor))
 	main.db = db_api.connect(host=cfg.dbhost, user=cfg.dbuser, passwd=cfg.dbpass, db=cfg.dbname, cursorclass=MySQLdb.cursors.DictCursor)
 
+def _dbsetup_sqlite():
+	global db_api
+	import sqlite3 as db_api
+	for i in range(cfg.get('erebus', 'num_db_connections', 2)):
+		main.db = db_api.connect(cfg.dbhost)
+		main.db.row_factory = db_api.Row
+		main.db.isolation_level = None
+		main.dbs.append(main.db)
 
 def setup():
 	global cfg, main
diff --git a/dump.sql b/mysql.sql
similarity index 78%
rename from dump.sql
rename to mysql.sql
index 29cfe0c..ab17b72 100644
--- a/dump.sql
+++ b/mysql.sql
@@ -34,17 +34,6 @@ CREATE TABLE `bots` (
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
---
--- Dumping data for table `bots`
---
-
-LOCK TABLES `bots` WRITE;
-/*!40000 ALTER TABLE `bots` DISABLE KEYS */;
-INSERT INTO `bots` VALUES
-('Erebus','Erebus',NULL,'Erebus','',1,0);
-/*!40000 ALTER TABLE `bots` ENABLE KEYS */;
-UNLOCK TABLES;
-
 --
 -- Table structure for table `chans`
 --
@@ -61,17 +50,6 @@ CREATE TABLE `chans` (
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
---
--- Dumping data for table `chans`
---
-
-LOCK TABLES `chans` WRITE;
-/*!40000 ALTER TABLE `chans` DISABLE KEYS */;
-INSERT INTO `chans` VALUES
-('#','Erebus',1);
-/*!40000 ALTER TABLE `chans` ENABLE KEYS */;
-UNLOCK TABLES;
-
 --
 -- Table structure for table `chusers`
 --
@@ -87,17 +65,6 @@ CREATE TABLE `chusers` (
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
---
--- Dumping data for table `chusers`
---
-
-LOCK TABLES `chusers` WRITE;
-/*!40000 ALTER TABLE `chusers` DISABLE KEYS */;
-INSERT INTO `chusers` VALUES
-('#','dimecadmium',5);
-/*!40000 ALTER TABLE `chusers` ENABLE KEYS */;
-UNLOCK TABLES;
-
 --
 -- Table structure for table `users`
 --
@@ -112,16 +79,6 @@ CREATE TABLE `users` (
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
---
--- Dumping data for table `users`
---
-
-LOCK TABLES `users` WRITE;
-/*!40000 ALTER TABLE `users` DISABLE KEYS */;
-INSERT INTO `users` VALUES
-('dimecadmium',100);
-/*!40000 ALTER TABLE `users` ENABLE KEYS */;
-UNLOCK TABLES;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
diff --git a/sqlite.sql b/sqlite.sql
new file mode 100644
index 0000000..df3a4d4
--- /dev/null
+++ b/sqlite.sql
@@ -0,0 +1,28 @@
+CREATE TABLE `bots` (
+  `nick` varchar(30) NOT NULL,
+  `user` varchar(10) NOT NULL DEFAULT 'erebus',
+  `bind` varchar(15) DEFAULT NULL,
+  `authname` varchar(30) DEFAULT NULL,
+  `authpass` varchar(20) DEFAULT NULL,
+  `active` tinyint(1) NOT NULL DEFAULT 1,
+  `connected` tinyint(1) NOT NULL DEFAULT 0,
+  PRIMARY KEY (`nick`)
+);
+CREATE TABLE `chans` (
+  `chname` varchar(100) NOT NULL,
+  `bot` varchar(30) NOT NULL,
+  `active` tinyint(1) NOT NULL DEFAULT 1,
+  PRIMARY KEY (`chname`)
+);
+CREATE INDEX chans_bot ON chans (bot);
+CREATE TABLE `chusers` (
+  `chan` varchar(100) NOT NULL,
+  `user` varchar(30) NOT NULL,
+  `level` int(11) NOT NULL,
+  PRIMARY KEY (`chan`,`user`)
+);
+CREATE TABLE `users` (
+  `auth` varchar(30) NOT NULL,
+  `level` tinyint(3) NOT NULL,
+  PRIMARY KEY (`auth`)
+);