gozerbot.database.db

database interface

class gozerbot.database.db.Db(doconnect=True, dbtype=None, ddir=None, configin=None)

Bases: object

this class implements a database connection. it connects to the database on initialisation.

close()

close database.

commit()

do a commit on the datase.

connect(dbname=None, dbhost=None, dbuser=None, dbpasswd=None, timeout=15, oldstyle=False)

connect to the database.

cursor()

return cursor to the database.

execute(*args, **kwargs)

the locked function.

executescript(*args, **kwargs)

the locked function.

ping()

do a ping.

reconnect()

reconnect to the database server.

gozerbot.database.db.init_tables()

CODE

# gozerbot/databse/db.py
#
#

""" database interface """

__status__ = "seen"

gozerbot imports

from gozerbot.config import config
from gozerbot.utils.log import rlog
from gozerbot.utils.locking import lockdec
from gozerbot.utils.generic import tolatin1
from gozerbot.utils.exception import handle_exception
from gozerbot.datadir import datadir

basic imports

import thread
import os
import time
import types

locks

dblock = thread.allocate_lock()
dblocked = lockdec(dblock)

Db class

class Db(object):

    """
        this class implements a database connection. it connects to the
        database on initialisation.
    """

    def __init__(self, doconnect=True, dbtype=None, ddir=None,configin=None):
        self.datadir = ddir or datadir
        if not os.path.isdir(self.datadir + os.sep + 'db/'): os.mkdir(self.datadir + os.sep + 'db/')
        self.config = configin or config
        self.dbname = self.config['dbname'] or ""
        self.dbhost = self.config['dbhost'] or ""
        self.dbuser = self.config['dbuser'] or ""
        self.dbpasswd = self.config['dbpasswd'] or ""
        self.connection = None
        self.timeout = 15
        self.oldstyle = ""
        self.dbtype = dbtype or self.config['dbtype'] or 'sqlite'
        if doconnect: self.connect()

    def connect(self, dbname=None, dbhost=None, dbuser=None, dbpasswd=None, timeout=15, oldstyle=False):
        """ connect to the database. """
        self.dbname = dbname or self.config['dbname']
        self.dbhost = dbhost or self.config['dbhost']
        self.dbuser = dbuser or self.config['dbuser']
        self.dbpasswd = dbpasswd or self.config['dbpasswd']
        self.timeout = timeout
        self.oldstyle = oldstyle or self.config['dboldstyle']
        if self.dbtype == 'mysql':
            import MySQLdb
            self.connection = MySQLdb.connect(db=self.dbname, host=self.dbhost, user=self.dbuser, passwd=self.dbpasswd, connect_timeout=self.timeout, charset='utf8')
        elif 'sqlite' in self.dbtype:
            try:
                import sqlite
                self.connection = sqlite.connect(self.datadir + os.sep + self.dbname)
            except ImportError:
                import sqlite3
                self.connection = sqlite3.connect(self.datadir + os.sep + self.dbname, check_same_thread=False)
        elif self.dbtype == 'postgres':
            import psycopg2
            rlog(1000, 'db', 'NOTE THAT POSTGRES IS NOT FULLY SUPPORTED')
            self.connection = psycopg2.connect(database=self.dbname, host=self.dbhost, user=self.dbuser, password=self.dbpasswd)
        else:
            rlog(100, 'db', 'unknown database type %s' % self.dbtype)
            return 0
        rlog(10, 'db', "%s database ok" % self.dbname)
        return 1

    def reconnect(self):
        """ reconnect to the database server. """
        return self.connect()

    @dblocked
    def executescript(self, txt):
        """ run executescript on the cursor. """
        cursor = self.cursor()
        cursor.executescript(txt)
        cursor.close()
        self.commit()

    @dblocked
    def execute(self, execstr, args=None):
        """ execute string on database. """
        time.sleep(0.001)
        result = None
        execstr = execstr.strip()
        if not self.connection: self.connect()
        if self.dbtype == 'sqlite': execstr = execstr.replace('%s', '?')
        if self.dbtype == 'mysql':
            try: self.ping()
            except AttributeError: self.reconnect()
            except Exception, ex:
                rlog(10, 'db', "can't ping database: %s" % str(ex))
                rlog(10, 'db', 'reconnecting')
                try: self.reconnect()
                except Exception, ex:
                    rlog(10, 'db', 'failed reconnect: %s' % str(ex))
                    return
        if args and self.oldstyle:
            nargs = []
            for i in args: nargs.append(tolatin1(i))
            args = nargs
        rlog(-2, 'db', 'exec %s %s' % (execstr, args))
        cursor = self.cursor()
        nr = 0
        try:
            if args:
                if type(args) == tuple or type(args) == list: nr = cursor.execute(execstr, args)
                else: nr = cursor.execute(execstr, (args, ))
            else: nr = cursor.execute(execstr)
        except:
            if self.dbtype == 'postgres': cursor.execute(""" ROLLBACK """)
            if 'sqlite' in self.dbtype: cursor.close()
            raise
        got = False
        if execstr.startswith('INSERT'): nr = cursor.lastrowid or nr ; got = True
        elif execstr.startswith('UPDATE'): nr = cursor.rowcount ; got = True
        elif execstr.startswith('DELETE'): nr = cursor.rowcount ; got = True
        if got: self.commit()
        if self.dbtype == 'sqlite' and not got and type(nr) != types.IntType:
            nr = cursor.rowcount or cursor.lastrowid
            if nr == -1: nr = 0
        result = None
        try:
            result = cursor.fetchall()
            if not result: result = nr
        except Exception, ex:
            if 'no results to fetch' in str(ex): pass
            else: handle_exception()
            result = nr
        cursor.close()
        return result

    def cursor(self):
        """ return cursor to the database. """
        return self.connection.cursor()

    def commit(self):
        """ do a commit on the datase. """
        self.connection.commit()

    def ping(self):
        """ do a ping. """
        return self.connection.ping()

    def close(self):
        """ close database. """
        if 'sqlite' in self.dbtype: self.commit()
        self.connection.close()

defines1

db = Db()

init_tables function

def init_tables():
    if True:
        if db.dbtype in ["sqlite", "sqlite3"]:
            import sqlite3
            try:
                rlog(10, 'db', 'trying to create tables')
                db.executescript(sqlite3_newtxt)
                rlog(10, 'db', 'tables created')
            except sqlite3.OperationalError, ex:
                if 'already exists' in str(ex): rlog(10, 'db', 'tables are already created')
            except Exception, ex: handle_exception()
        else: rlog(10, 'db', "dbtype is %s .. NOT initialising tables. see the files dir for mysql or postgress schema's" % db.dbtype)

defines2

sqlite3_oldtxt =  """

CREATE TABLE userhosts (
    userhost VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE perms (
    name VARCHAR(255) NOT NULL,
    perm VARCHAR(255) NOT NULL
);

CREATE TABLE email (
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE TABLE permits (
    name VARCHAR(255) NOT NULL,
    permit VARCHAR(255) NOT NULL
);

CREATE TABLE statuses (
    name VARCHAR(255)  NOT NULL,
    status VARCHAR(255) NOT NULL
);

CREATE TABLE passwords (
    name VARCHAR(255) NOT NULL,
    passwd VARCHAR(255) NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE infoitems (
    indx INTEGER PRIMARY KEY,
    item VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    userhost VARCHAR(255) NOT NULL,
    time INT(20) NOT NULL
);

CREATE TABLE karma (
    item VARCHAR(255) NOT NULL,
    value INT(20) NOT NULL,
    PRIMARY KEY (item)
);

CREATE TABLE whykarma (
    item VARCHAR(255) NOT NULL,
    updown VARCHAR(10) NOT NULL,
    why TEXT NOT NULL
);

CREATE TABLE whokarma (
    item VARCHAR(255) NOT NULL,
    nick VARCHAR(255) NOT NULL,
    updown VARCHAR(10) NOT NULL
);

CREATE TABLE quotes (
  indx INTEGER PRIMARY KEY,
  quote text NOT NULL,
  userhost VARCHAR(255) NOT NULL,
  createtime INT(20) NOT NULL,
  nick VARCHAR(255) NOT NULL
);

CREATE TABLE todo (
  indx INTEGER PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  time INT(20) DEFAULT NULL,
  duration INT(20) DEFAULT NULL,
  warnsec INT(20) DEFAULT NULL,
  descr TEXT NOT NULL,
  priority INT(20) DEFAULT NULL
);

CREATE TABLE birthday (
    name VARCHAR(255) NOT NULL,
    birthday varchar(255) NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE list (
    indx INTEGER PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    listname VARCHAR(255) NOT NULL,
    item TEXT NOT NULL
);
"""

sqlite3_newtxt = """

PRAGMA foreign_keys=OFF;
CREATE TABLE user (
        name VARCHAR(255) NOT NULL,
        PRIMARY KEY (name)
);
CREATE TABLE perms (
        name VARCHAR(255) NOT NULL,
        perm VARCHAR(255) NOT NULL,
        FOREIGN KEY(name) REFERENCES user (name)
);
INSERT INTO "perms" VALUES('owner','USER');
INSERT INTO "perms" VALUES('owner','OPER');
CREATE TABLE userhosts (
        userhost VARCHAR(255) NOT NULL,
        name VARCHAR(255) NOT NULL,
        PRIMARY KEY (userhost),
        FOREIGN KEY(name) REFERENCES user (name)
);
CREATE TABLE permits (
        name VARCHAR(255) NOT NULL,
        permit VARCHAR(255) NOT NULL,
        FOREIGN KEY(name) REFERENCES user (name)
);
CREATE TABLE passwords (
        name VARCHAR(255) NOT NULL,
        passwd VARCHAR(255) NOT NULL,
        PRIMARY KEY (name),
        FOREIGN KEY(name) REFERENCES user (name)
);
CREATE TABLE statuses (
        name VARCHAR(255) NOT NULL,
        status VARCHAR(255) NOT NULL,
        FOREIGN KEY(name) REFERENCES user (name)
);
CREATE TABLE email (
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL,
        "order" INTEGER NOT NULL,
        FOREIGN KEY(name) REFERENCES user (name)
);
CREATE TABLE whokarma (
        item VARCHAR(255) NOT NULL,
        nick VARCHAR(255) NOT NULL,
        updown VARCHAR(10) NOT NULL
);
CREATE TABLE karma (
        item VARCHAR(255) NOT NULL,
        value INTEGER NOT NULL,
        PRIMARY KEY (item)
);
CREATE TABLE whykarma (
        item VARCHAR(255) NOT NULL,
        updown VARCHAR(10) NOT NULL,
        why TEXT NOT NULL
);
CREATE TABLE quotes (
        indx INTEGER NOT NULL,
        quote TEXT NOT NULL,
        userhost VARCHAR(255) NOT NULL,
        createtime DATETIME NOT NULL,
        nick VARCHAR(255) NOT NULL,
        PRIMARY KEY (indx),
        FOREIGN KEY(userhost) REFERENCES userhosts (userhost)
);
CREATE TABLE todo (
        indx INTEGER NOT NULL,
        name VARCHAR(255) NOT NULL,
        time DATETIME,
        duration INTEGER,
        warnsec INTEGER,
        descr TEXT NOT NULL,
        priority INTEGER,
        PRIMARY KEY (indx)
);
CREATE TABLE birthday (
        name VARCHAR(255) NOT NULL,
        birthday VARCHAR(255) NOT NULL,
        PRIMARY KEY (name)
);
CREATE TABLE infoitems (
        indx INTEGER NOT NULL,
        item VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        userhost VARCHAR(255) NOT NULL,
        time DATETIME NOT NULL,
        PRIMARY KEY (indx),
        FOREIGN KEY(userhost) REFERENCES userhosts (userhost)
);
CREATE TABLE list (
        indx INTEGER NOT NULL,
        username VARCHAR(255) NOT NULL,
        listname VARCHAR(255) NOT NULL,
        item TEXT NOT NULL,
        PRIMARY KEY (indx)
);
"""