path: root/atoidejouer/db/story.py

# python import
import copy, logging

# get application logger
logger = logging.getLogger('atoidejouer')

# sqlite import
import sqlite3;

# atoidejouer import
from atoidejouer.tools import image, storage


WHERE_COLS = [
    'id',
    'title',
    'mime_type',
    'time',
    'timestamp'
    ]

SET_COLS = [
    'layer',
    'x',
    'y',
    'duration',
    'loop'
    ]

INSERT_COLS = [
    'title',
    'mime_type',
    'time',
    'layer',
    'timestamp',
    'x',
    'y',
    'duration',
    'loop'
    ]

NUMERIC_COLS = [
    'time',
    'layer',
    'x',
    'y',
    'duration',
    'loop'
    ]


class Key(object):

    def __init__(self, id=None, title=None, mime_type=None, timestamp=None, **kargs):
        self.id, self.title, self.mime_type, self.timestamp = id, title, mime_type, timestamp
        # ensure value if select row returns None value
        for arg in ['time', 'layer', 'x', 'y', 'duration', 'loop', 'path']:
            setattr(self, arg, kargs[arg] if arg in kargs and kargs[arg] else 0)

    def _get_obj(self):
        for _ds_obj in storage.journal_query({
            'mime_type': self.mime_type,
            'title': self.title
            }):
            return _ds_obj

    def get_preview(self):
        if self.mime_type == 'image/png':
            _ds_obj = self._get_obj()
            return storage.get_pixbuf_from_data(
                        _ds_obj.metadata['preview'],
                        size=(64, 48)
                        )
        else:
            _path = storage.get_image_path('sound', dir_='data')
            return image.get_pixbuf(_path, 64, 48)

    def get_path(self):
        if self.path == 0:
            _ds_obj   = self._get_obj()
            self.path = _ds_obj.file_path if _ds_obj else 0
        return self.path

    def set_path(self, path):
        self.path = path

    def __repr__(self):
        return "%s|title=%s|mime_type=%s|timestamp=%s|time=%s|layer=%s|"\
                "x=%s|y=%s|duration=%s|loop=%s"\
                % (self.id, self.title, self.mime_type, self.timestamp,
                        self.time, self.layer,
                        self.x, self.y,
                        self.duration, self.loop)

    def __cmp__(self, other):
        return cmp(
                (self.title, self.mime_type, self.timestamp, self.time, self.layer),
                (other.title, other.mime_type, self.timestamp, other.time, other.layer)
                )

    def create(self):
        return "create table story("\
                "id integer primary key autoincrement not null,"\
                "title text,"\
                "mime_type text,"\
                "timestamp text,"\
                "time integer,"\
                "layer integer,"\
                "x integer,"\
                "y integer,"\
                "duration integer,"\
                "loop integer"\
                ")"

    def insert(self):
        columns = list()
        values  = list()
        for c in INSERT_COLS:
            v = getattr(self, c)
            if v is not None and v != -1:
                columns.append(c)
                values.append(str(v) if c in NUMERIC_COLS else "'%s'" % v)
        return "insert into story (%s) values (%s)" % (
                ",".join(columns),
                ",".join(values)
                )

    def _params(self, crit):
        values = list()
        joiner = ' and '    if crit == 'where' else ', '
        cols   = WHERE_COLS if crit == 'where' else SET_COLS
        for c in cols:
            v = getattr(self, c)
            if v is not None and v != -1:
                v = str(v) if c in NUMERIC_COLS else "'%s'" % v
                values.append("%s=%s" % (c, v))
        return "%s %s" % (crit, joiner.join(values))

    def where(self):
        """Prepares simple where query according OO parameters.
        """
        return self._params('where')

    def set(self):
        """Prepares simple where query according OO parameters.
        """
        return self._params('set')

    def select(self):
        """Prepares simple select query according OO parameters.
        """
        return "select * from story %s" % self.where()

    def update(self):
        return "update story %s where id=%s" % (self.set(), self.id)

    def delete(self, all=False):
        """Prepares simple delete query according OO parameters.
        """
        q = "delete from story"
        if all is True:
            return q
        else:
            return "%s %s" % (q, self.where())

    def _query_duration(self, time, sign='=', order=''):
        return "select * from story where %s order by %stime" % (
                " and ".join([
                    "%s='%s'"  % ("title",       self.title),
                    "%s='%s'"  % ("mime_type",   self.mime_type),
                    "%s='%s'"  % ("timestamp",   self.timestamp),
                    "%s=%s"    % ("layer",       self.layer),
                    "%s%s%s"   % ("time", sign,  time),
                    ]),
                order)

    def update_duration(self, duration):
        # list next keys
        key_dura = duration
        start = self.time + 1
        _range = self.duration if self.duration > duration else duration
        for time in range(start, start + _range):
            key = None
            for key in DB()._all(query=self._query_duration(time)):
                break
            key_dura -= 1
            # DEBUG
            # print '\nup time: %s' % time
            # print 'up query : %s' % self._query_duration(time)
            # print 'up key : %s' % key
            # print 'up key_dura: %s' % key_dura
            # no key?
            if key is None:
                new_key = copy.copy(self)
                new_key.id = None
                new_key.time = time
                new_key.duration = key_dura
                # DEBUG
                # print 'up new_key: %s' % new_key
                DB().add(new_key)
            # remove next keys
            elif key.time > self.time + duration:
                key._del(refresh=False)
                break
            # update duration of existing key
            else:
                key.duration = key_dura
                DB().update(key)
        # update key duration after check
        self.duration = duration
        DB().update(self)
        # need some refresh
        self._duration_refresh()

    def _duration_refresh(self):
        """Ensure valid duration logic.
        """
        def _do_fresh(iter_):
            """Common method for our generator.
            """
            cur = None
            for key in iter_:
                if cur is None or key.time < cur - 1:
                    cur = 0
                    key.duration = cur
                else:
                    cur += 1
                    key.duration = cur
                # DEBUG
                # print 'fresh: %s' % key
                # and update
                DB().update(key)
        # update previous
        end = self.time
        _do_fresh(DB()._all(query=self._query_duration(end, sign='<', order='-')))
        # update next
        start = self.time + self.duration
        _do_fresh(DB()._all(query=self._query_duration(start, sign='>', order='-')))

    def _del(self, refresh=True):
        # list next keys
        key_dura = 0
        ran_time = self.time + 1
        ran_dura = self.duration
        for time in range(ran_time, ran_time + ran_dura):
            key = None
            for key in DB()._all(query=self._query_duration(time)):
                break
            # DEBUG
            # print '\ndel time: %s' % time
            # print 'del query : %s' % self._query_duration(time)
            # print 'del key : %s' % key
            # print 'del key_dura: %s' % key_dura
            if key is None:
                pass
            # finish
            elif key.time > time:
                break
            # update duration of existing key
            else:
                key.duration = key_dura
                DB()._del(key)
            # update next duration
            key_dura += 1
        # DEBUG
        # print 'del old key: %s' % self
        # and del
        DB()._del(self)
        # need some refresh
        if refresh is True:
            self._duration_refresh()

class DB(object):

    class __Singleton:

        def __init__(self, config=None, name="story", obj=Key):
            self.name, self.obj = name, obj
            db_path = storage.get_db_path('default')
            self.con = sqlite3.connect(db_path,
                    detect_types=sqlite3.PARSE_DECLTYPES)
            self.con.row_factory = sqlite3.Row
            self.__check()

        def __check(self):
            cur = self.con.cursor()
            # remove all first
            try:
                cur.execute("drop table %s" % self.name)
            except Exception, e:
                pass
            # create fresh db
            cur.execute(self.obj().create())
            # and close
            cur.close()

        def add(self, obj):
            cur = self.con.cursor()
            cur.execute(obj.insert())
            count = cur.rowcount
            cur.close()
            return count

        def _fetch(self, cur):
            row = cur.fetchone()
            while(row):
                yield self.obj(**row)
                row = cur.fetchone()

        def _all(self, query=None):
            cur = self.con.cursor()
            query = "select * from %s" % self.name\
                    if query is None\
                    else query
            cur.execute(query)
            for obj in self._fetch(cur):
                yield obj
            cur.close()

        def get(self, obj):
            cur = self.con.cursor()
            # DEBUG
            # logger.debug('[db.story] get - query: %s' % obj.select())
            # DEBUG
            cur.execute(obj.select())
            for obj in self._fetch(cur):
                yield obj
            cur.close()

        def one(self, obj):
            for one in self.get(obj):
                return one

        def get_max(self, max_):
            query = "select %(max_)s from %(name)s order by -%(max_)s"\
                    % {'name': self.name, 'max_': max_}
            for obj in self._all(query=query):
                return getattr(obj, max_)
            return 0

        def update(self, obj):
            cur = self.con.cursor()
            cur.execute(obj.update())
            rowcount = cur.rowcount
            cur.close()
            return rowcount

        def _del(self, obj=None, all=False):
            cur = self.con.cursor()
            obj = self.obj() if obj is None else obj
            cur.execute(obj.delete(all=all))
            rowcount = cur.rowcount
            cur.close()
            return rowcount

    # singleton instance
    instance = None

    def __new__(c, force=False):
        """Singleton new init.
        """
        # if doesn't already initialized
        if not DB.instance\
        or force is True:
            # create a new instance
            DB.instance = DB.__Singleton()
        # return the manager object
        return DB.instance