1 files changed, 153 insertions, 0 deletions
diff --git a/plugins/guildbot/guilddb.py b/plugins/guildbot/guilddb.py
new file mode 100644
index 0000000..a720b49
--- /dev/null
+++ b/plugins/guildbot/guilddb.py
@@ -0,0 +1,153 @@
+import logging
+import sys
+import sqlite3
+
+
+class GuildDB:
+
+    def __init__(self, dbfile):
+        self._dbfile = dbfile
+        self.logger = logging.getLogger('ManaChat.Guild')
+        self.db, self.cur = self._open_sqlite_db(dbfile)
+
+        # self.cur.execute('PRAGMA foreign_keys = ON')
+        self.cur.execute('create table if not exists GUILDS(\
+                              ID       integer primary key,\
+                              NAME     text[25] not null unique,\
+                              CREATED  datetime default current_timestamp,\
+                              MOTD     text[100])')
+        self.cur.execute('create table if not exists PLAYERS(\
+                              ID       integer primary key,\
+                              NAME     text[25] not null unique,\
+                              LASTSEEN date,\
+                              GUILD_ID integer,\
+                              ACCESS   integer not null default -10,\
+                              SHOWINFO boolean not null default 0,\
+                              foreign key(GUILD_ID) references GUILDS(ID))')
+        self.db.commit()
+
+    def __del__(self):
+        try:
+            self.db.close()
+        except Exception:
+            pass
+
+    def _open_sqlite_db(self, dbfile):
+        """
+        Open sqlite db, and return tuple (connection, cursor)
+        """
+        try:
+            db = sqlite3.connect(dbfile)
+            cur = db.cursor()
+        except sqlite3.Error, e:
+            self.logger.error("sqlite3 error: %s", e.message)
+            sys.exit(1)
+        return db, cur
+
+    def guild_create(self, name):
+        self.cur.execute('insert into GUILDS(NAME) values(?)', (name,))
+        self.db.commit()
+        if self.cur.rowcount:
+            self.logger.info('Created guild "%s"', name)
+            return True
+        else:
+            self.logger.info('Error creating guild "%s"', name)
+            return False
+
+    def guild_delete(self, name):
+        self.cur.execute('select ID from GUILDS where name = ?', (name,))
+        row = self.cur.fetchone()
+        if row:
+            guild_id = row[0]
+            self.cur.execute('delete from GUILDS where name=?', (name,))
+            self.cur.execute('update PLAYERS set GUILD_ID = NULL, \
+                              ACCESS = -10, where GUILD_ID = ?', (guild_id,))
+            self.db.commit()
+            self.logger.info('Deleted guild "%s"', name)
+            return True
+        else:
+            self.logger.error('Guild "%s" not found', name)
+            return False
+
+    def guild_set_motd(self, name, motd):
+        self.cur.execute('update GUILD set MOTD = ? where NAME = ?',
+                         (motd, name))
+        self.db.commit()
+        if self.cur.rowcount:
+            self.logger.info('Guild "%s" MOTD: %s', name, motd)
+            return True
+        else:
+            self.logger.error('Error setting MOTD for guild: %s', name)
+            return False
+
+    def player_info(self, name):
+        query = '''select GUILDS.ID,GUILDS.NAME,ACCESS
+                   from PLAYERS join GUILDS
+                   on PLAYERS.GUILD_ID = GUILDS.ID
+                   where PLAYERS.NAME = ?'''
+        self.cur.execute(query, (name,))
+        return self.cur.fetchone()
+
+    def player_get_access(self, name, guild_name=''):
+        query = 'select ACCESS from PLAYERS where NAME = ?'
+        self.cur.execute(query, (name, guild_name))
+        row = self.cur.fetchone()
+        if row:
+            return row[0]
+        else:
+            # self.logger.warning('player %s not found', name)
+            return -10
+
+    def player_set_access(self, name, access_level):
+        query = '''update table PLAYERS set ACCESS = ?
+                   where name = ?'''
+        self.cur.execute(query, (name, access_level))
+        self.db.commit()
+
+    def player_join_guild(self, player, guild, access=0):
+        self.cur.execute('select ID from GUILDS where NAME = ?', (guild,))
+        guild_info = self.cur.fetchone()
+        if guild_info:
+            guild_id = guild_info[0]
+        else:
+            self.logger.error('Guild "%s" not found', guild)
+            return False
+
+        query = '''update or ignore PLAYERS
+                   set GUILD_ID = ?, ACCESS = ?
+                   where NAME = ?'''
+        self.cur.execute(query, (guild_id, access))
+
+        query = '''insert or ignore into
+                   PLAYERS(NAME, GUILD_ID, ACCESS)
+                   values(?, ?, ?)'''
+        self.cur.execute(query, (player, guild_id, access))
+
+        self.db.commit()
+
+        self.logger.info('Added player "%s" to guild "%s"',
+                         player, guild)
+        return True
+
+    def player_set_showinfo(self, player, si=True):
+        query = '''update table PLAYERS set SHOWINFO = ?
+                   where name = ?'''
+        self.cur.execute(query, (player, si))
+        self.db.commit()
+
+    def guild_remove_player(self, player_name):
+        query = '''update PLAYERS set GUILD_ID = NULL, ACCESS = -10
+                   where NAME = ?'''
+        self.cur.execute(query, (player_name,))
+        self.db.commit()
+
+    def all_players_same_guild(self, player_name):
+        query = '''select NAME from PLAYERS
+                   where GUILD_ID = (select GUILD_ID from PLAYERS
+                                     where NAME = ?)'''
+        return self.cur.fetchall(query, (player_name,))
+
+    def all_players_any_guild(self):
+        query = '''select NAME from PLAYERS
+                   where ACCESS >= 0'''
+        return self.cur.fetchall(query)