Half way through migration away from sqlalchemy

[pwp.git] / app / DB / db.py

from enum import IntEnum, unique
import inspect
from abc import ABCMeta
from baseorm import Observer
import logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@unique
class DBDriver(IntEnum):
    PG = 1
    MySQL = 2

class Singleton(ABCMeta):
    _instances = {}
    def __call__(cls, password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None):
        if cls not in cls._instances:
            cls._instances[cls] = super(Singleton, cls).__call__(password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None)
        return cls._instances[cls]

class DB(Observer, metaclass=Singleton):
    __filename__ = None
    
    def __init__(self, password, driver = DBDriver.PG, user = 'pwp', database = 'pwp', host = 'localhost', port = None):
        self.password = password
        self.user = user
        self.database = database
        self.driver = driver
        self.host = host
        self.port = port
        self.__connect()
        if driver == DBDriver.PG:
            self.__filename__ = 'db_postgres.sql'
        elif driver == DBDriver.MySQL:
            self.__filename__ = 'db_mysql.sql'
    
    def __connect(self):
        if self.driver is DBDriver.PG:
            from postgres import Postgres 
            try:
                logger.info("Connect: database: {0} user: {1} host: {2} port: {3}".format(self.database, self.user, self.host, self.port))
                self.db = Postgres(self.database, self.user, self.password, self.host, self.port)
            except Exception as e:
                raise Exception(e)
        elif self.driver is DBDriver.MySQL:
            from mysqld import Mysql
            try:
                self.db = Mysql(self.database, self.user, self.password, self.host, self.port)
            except Exception as e:
                raise Exception(e)
        else:
            raise Exception("%s: Unknown DB driver" % self.driver)
    
    def update(self, subject):
        self.store(subject)
    
    def initDb(self):
        file = None
        try:
            file = open(self.__filename__, "r")
            sql = file.read()
            file.close()
            self.db.ddl(sql)
        except:
            raise

    def query(self, object, **kwargs):
        if kwargs:
            if object.lower() == 'user' and self.driver is DBDriver.PG:
                sql = 'select * from "{0}" where '.format(object)
            else:
                sql = 'select * from {0} where '.format(object)
            placeholder = []
            clause = None
            for name, value in kwargs.items():
                if name.lower() == 'user' and self.driver is DBDriver.PG:
                    attr = '"{0}"'.format(name)
                else:
                    attr = name
                if clause:
                    clause += ' and {0} = %s '.format(attr)
                else:
                    clause = '{0} = %s'.format(attr)
                placeholder.append(value)
            sql += clause
            logger.info("{0} -> {1}".format(sql, placeholder))
            return self.db.query(sql, placeholder)
        else:
            raise Exception("{0}: Missing at least one query parameter".format(object))

    def store(self, object):
        if inspect.isclass(object):
            raise Exception("{0}: Class not instance".format(object))

        if hasattr(object, '__tablename__'):
            table = object.__tablename__
        else:
            table = object.__class__.__name__

        v = [i for i in dir(object) if isinstance(getattr(type(object), i, None), property)]
        
        action = None
        column = []
        values = []
        sql = None
        id = None
        for p in v:
            value = getattr(object, p)
            if p == 'id':
                id = value
                if value < 0:
                    # insert
                    action = 'insert'
                else:
                    # update
                    action = 'update'
            else:
                if hasattr(value, 'id'):
                    values.append(value.id)
                else:
                    values.append(value)
                column.append(p)
        if action == 'insert':
            if table.lower() == 'user' and self.driver is DBDriver.PG:
                sql = 'insert into "' + table + '" ('
            else:
                sql = 'insert into ' + table + ' ('
            for i in range(0, len(column)):
                field = column[i]
                if field.lower() == 'user' and self.driver is DBDriver.PG:
                    field = '"{0}"'.format(field)
                if i == 0:
                    sql += field
                else:
                    sql += ', ' + field
            sql += ') values ('
            for i in range(0, len(values)):
                if i == 0:
                    sql += '%s'
                else:
                    sql += ', %s'
            sql += ') '
            object.id = self.db.insert(sql, values)
        else:
            if table.lower() == 'user' and self.driver is DBDriver.PG:
                sql = 'update "' + table + '" set '
            else:
                sql = 'update ' + table + ' set '
            for i in range(0, len(column)):
                if i == 0:
                    sql += '{0} = %s'.format(column[i])
                else:
                    sql += ', {0} = %s'.format(column[i])
            sql += ' where id = {0}'.format(id)
            self.db.update(sql, values)
        
    def __repr__(self):
        default = '<%s.%s object at %s>' % (self.__class__.__module__, self.__class__.__name__, hex(id(self)))
        return "%s <Driver: %s, User: %s, database: %s>" % (default, self.driver.name,  self.user, self.database)

    def __del__(self):
        #print("%s: deleted" % self.__class__.__name__)
        try:
            del self.db
        except AttributeError:
            pass

class Test(object):
    def __init__(self, x):
        self.x = x
    
    @property
    def x(self):
        return self.__x
        
    @x.setter
    def x(self, x):
        if x < 0:
            self.__x = 0
        elif x > 1000:
            self.__x = 1000
        else:
            self.__x = x

if __name__ == "__main__":
    import sys
    sys.path.append('../..')
    from app.models import User, Portfolio, Album, Photo, AccessRight
    #db = DB('test', DBDriver.MySQL)
    db = DB('test')
    db.initDb()

    user = User('test', 'test@test.dk', 'test', 'sha256$1HX2n73E$ac27f843b4342df7b6c12e5ac340e063ea958d52ce62c3883c124385c96b263a')
    user.addObserver(db)
    print(user)
    db.store(user)
    portfolio = Portfolio('test', user)
    print(portfolio)
    album = Album('test', portfolio)
    print(album)
    accessright = AccessRight(user)
    print(accessright)
    photo = Photo('test', album)
    print(photo)
    db.store(portfolio)
    user.name = 'MIR'
    del db