Python sqlite3.PARSE_DECLTYPES Examples

def remind_always(channelid, channelname, title):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND type=?', (channelid, channelname, title, "REMIND ALWAYS")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, type) VALUES(?, ?, ?, ?)",
        [channelid, channelname, title, "REMIND ALWAYS"])

    conn.commit()
    conn.close()

    service() 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def __open(self):
        if not self.createIfNotExists and not os.path.exists(self.fileName):
            return False

        self.connection = sqlite3.connect(self.fileName, detect_types=sqlite3.PARSE_DECLTYPES)
        if(self.connection):
            self.connection.row_factory = sqlite3.Row
            self.connection.text_factory = str

            self.cursor = self.connection.cursor()
            self.cursor.execute("PRAGMA foreign_keys=1")

            self.versionTable = RMVersionTable(self)

            return True
        return False 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def init_db(self):
        """Connect to the database, and create tables if necessary."""
        if not self.enabled:
            self.db = DummyDB()
            return
        
        # use detect_types so that timestamps return datetime objects
        kwargs = dict(detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
        kwargs.update(self.connection_options)
        self.db = sqlite3.connect(self.hist_file, **kwargs)
        self.db.execute("""CREATE TABLE IF NOT EXISTS sessions (session integer
                        primary key autoincrement, start timestamp,
                        end timestamp, num_cmds integer, remark text)""")
        self.db.execute("""CREATE TABLE IF NOT EXISTS history
                (session integer, line integer, source text, source_raw text,
                PRIMARY KEY (session, line))""")
        # Output history is optional, but ensure the table's there so it can be
        # enabled later.
        self.db.execute("""CREATE TABLE IF NOT EXISTS output_history
                        (session integer, line integer, output text,
                        PRIMARY KEY (session, line))""")
        self.db.commit() 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def cleanup_database(days=1):
    """
    remove any PDFs which have been here for more than
    1 day, then compact the database
    """
    log.info('Cleaning up database')
    conn = sqlite3.connect(robotreviewer.get_data('uploaded_pdfs/uploaded_pdfs.sqlite'),
                           detect_types=sqlite3.PARSE_DECLTYPES)

    d = datetime.now() - timedelta(days=days)
    c = conn.cursor()
    c.execute("DELETE FROM article WHERE timestamp < datetime(?) AND dont_delete=0", [d])
    conn.commit()
    conn.execute("VACUUM") # make the database smaller again
    conn.commit()
    conn.close() 

def __init__(self, bot):
        self.bot = bot
        self.db = sqlite3.connect(SQLDB, detect_types=sqlite3.PARSE_DECLTYPES)
        self.db.row_factory = sqlite3.Row

        with self.db as con:
            con.executescript(INIT_SQL)

            if check_fts4():
                self.has_fts = True
                con.executescript(FTS_SQL)
                con.create_function('bm25', -1, bm25)
            else:
                self.has_fts = False

        self.bot.loop.create_task(self._populate_userinfo())
        self.bot.loop.create_task(self._upgrade_210())
        self._upgrade_211()
        self._upgrade_230()

        try:
            self.analytics = CogAnalytics(self)
        except Exception as e:
            self.bot.logger.exception(e)
            self.analytics = None 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def __init__(self, filename, debug = False, logLevel = 'INFO'):
        if filename == ':memory:':
            self.dbname = ""
        else:
            if not filename.lower().endswith(DB_EXTENSION):
               self.dbname = "{}.{}".format(filename, DB_EXTENSION)
            else:
               self.dbname = filename
        self._engine = create_engine("sqlite:///{}".format(self.dbname), echo = debug,
            connect_args={'detect_types': sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES},
        native_datetime = True)

        self._session = orm.Session(self._engine, autoflush = False, autocommit = False)
        self._metadata = Base.metadata
        #loadInitialData(Node)
        Base.metadata.create_all(self.engine)
        meta = MetaData(schema_version = CURRENT_SCHEMA_VERSION)
        self.session.add(meta)
        self.session.flush()
        self.session.commit()
        self._closed = False 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def __init__(self, db_path, writeable=False, translate_ncbi_namespace=False, check_same_thread=True):
        self._db_path = db_path
        self._db = None
        self._writeable = writeable
        self.translate_ncbi_namespace = translate_ncbi_namespace

        if self._writeable:
            self._upgrade_db()
        self._db = sqlite3.connect(self._db_path,
                                   check_same_thread=check_same_thread,
                                   detect_types=sqlite3.PARSE_DECLTYPES)
        self._db.row_factory = sqlite3.Row
        schema_version = self.schema_version()
        # if we're not at the expected schema version for this code, bail
        if schema_version != expected_schema_version:    # pragma: no cover
            raise RuntimeError("Upgrade required: Database schema"
                               "version is {} and code expects {}".format(schema_version, expected_schema_version))

    # ############################################################################
    # Special methods 

def record_daily(channelid, channelname, title, start, stop):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    start = timestamp2datetime(float(start))
    stop = timestamp2datetime(float(stop))

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    #TODO problem with PRIMARY KEYS, UNIQUE and TIMESTAMP
    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND start=? AND stop =? AND type=?', (channelid, channelname, title, start, stop, "DAILY")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, start, stop, type) VALUES(?, ?, ?, ?, ?, ?)",
        [channelid, channelname, title, start, stop, "DAILY"])

    conn.commit()
    conn.close()

    service() 

def record_weekly(channelid, channelname, title, start, stop):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    start = timestamp2datetime(float(start))
    stop = timestamp2datetime(float(stop))

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    #TODO problem with PRIMARY KEYS, UNIQUE and TIMESTAMP
    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND start=? AND stop =? AND type=?', (channelid, channelname, title, start, stop, "WEEKLY")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, start, stop, type) VALUES(?, ?, ?, ?, ?, ?)",
        [channelid, channelname, title, start, stop, "WEEKLY"])

    conn.commit()
    conn.close()

    service() 

def record_always_search(channelid, channelname):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")

    title = xbmcgui.Dialog().input("IPTV Recorder: " + _("Title Search (% is wildcard)?")).decode("utf8")
    if not title:
        return

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND type=?', (channelid, channelname, title, "SEARCH")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, type) VALUES(?, ?, ?, ?)",
        [channelid, channelname, title, "SEARCH"])

    conn.commit()
    conn.close()

    service() 

def record_always_search_plot(channelid, channelname):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")

    description = xbmcgui.Dialog().input("IPTV Recorder: " + _("Plot Search (% is wildcard)?")).decode("utf8")
    if not description:
        return

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND description=? AND type=?', (channelid, channelname, description, "PLOT")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, description, type) VALUES(?, ?, ?, ?)",
        [channelid, channelname, description, "PLOT"])

    conn.commit()
    conn.close()

    service() 

def watch_daily(channelid, channelname, title, start, stop):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    start = timestamp2datetime(float(start))
    stop = timestamp2datetime(float(stop))

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    #TODO problem with PRIMARY KEYS, UNIQUE and TIMESTAMP
    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND start=? AND stop =? AND type=?', (channelid, channelname, title, start, stop, "WATCH DAILY")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, start, stop, type) VALUES(?, ?, ?, ?, ?, ?)",
        [channelid, channelname, title, start, stop, "WATCH DAILY"])

    conn.commit()
    conn.close()

    service() 

def watch_weekly(channelid, channelname, title, start, stop):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    start = timestamp2datetime(float(start))
    stop = timestamp2datetime(float(stop))

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    #TODO problem with PRIMARY KEYS, UNIQUE and TIMESTAMP
    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND start=? AND stop =? AND type=?', (channelid, channelname, title, start, stop, "WATCH WEEKLY")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, start, stop, type) VALUES(?, ?, ?, ?, ?, ?)",
        [channelid, channelname, title, start, stop, "WATCH WEEKLY"])

    conn.commit()
    conn.close()

    service() 

def watch_always(channelid, channelname, title):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND type=?', (channelid, channelname, title, "WATCH ALWAYS")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, type) VALUES(?, ?, ?, ?)",
        [channelid, channelname, title, "WATCH ALWAYS"])

    conn.commit()
    conn.close()

    service() 

def migrate_db_v1_to_v2(db_path):
    old_db_path = db_path + datetime.now().strftime("%s") + ".bkp_db_v1"
    os.rename(db_path, old_db_path)

    old_db = sql.connect(old_db_path,
                         detect_types=sql.PARSE_DECLTYPES |
                         sql.PARSE_COLNAMES)
    new_db = DB(db_path)
    old_db.cursor().execute("PRAGMA foreign_keys = ON")

    epoch_list = []
    for epoch, schema in old_db.execute('SELECT epoch, schema '
                                        'FROM snapshots_schemas'):
        new_db.add_snapshot(epoch, schema)
        epoch_list.append(epoch)

    for epoch in epoch_list:
        file_list = list(old_db.execute("SELECT keyspace, tablename, file "
                                        "FROM snapshots_files WHERE epoch = ?",
                                        (epoch,)))
        new_db.add_snapshot_files(epoch, file_list) 

def watch_always_search_plot(channelid, channelname):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")

    description = xbmcgui.Dialog().input("IPTV watcher: " + _("Plot Search (% is wildcard)?")).decode("utf8")
    if not description:
        return

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND description=? AND type=?', (channelid, channelname, description, "WATCH PLOT")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, description, type) VALUES(?, ?, ?, ?)",
        [channelid, channelname, description, "WATCH PLOT"])

    conn.commit()
    conn.close()

    service() 

def remind_daily(channelid, channelname, title, start, stop):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    start = timestamp2datetime(float(start))
    stop = timestamp2datetime(float(stop))

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    #TODO problem with PRIMARY KEYS, UNIQUE and TIMESTAMP
    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND start=? AND stop =? AND type=?', (channelid, channelname, title, start, stop, "REMIND DAILY")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, start, stop, type) VALUES(?, ?, ?, ?, ?, ?)",
        [channelid, channelname, title, start, stop, "REMIND DAILY"])

    conn.commit()
    conn.close()

    service() 

def remind_weekly(channelid, channelname, title, start, stop):
    channelid = channelid.decode("utf8")
    channelname = channelname.decode("utf8")
    title = title.decode("utf8")
    title = xbmcgui.Dialog().input(_("% is Wildcard"), title).decode("utf8")

    start = timestamp2datetime(float(start))
    stop = timestamp2datetime(float(stop))

    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    #TODO problem with PRIMARY KEYS, UNIQUE and TIMESTAMP
    rule = cursor.execute('SELECT * FROM rules WHERE channelid=? AND channelname=? AND title=? AND start=? AND stop =? AND type=?', (channelid, channelname, title, start, stop, "REMIND WEEKLY")).fetchone()

    if not rule:
        conn.execute("INSERT OR REPLACE INTO rules(channelid, channelname, title, start, stop, type) VALUES(?, ?, ?, ?, ?, ?)",
        [channelid, channelname, title, start, stop, "REMIND WEEKLY"])

    conn.commit()
    conn.close()

    service() 

def CheckConvertTimestampMicrosecondPadding(self):
        """
        http://bugs.python.org/issue14720

        The microsecond parsing of convert_timestamp() should pad with zeros,
        since the microsecond string "456" actually represents "456000".
        """

        con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute("CREATE TABLE t (x TIMESTAMP)")

        # Microseconds should be 456000
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.456')")

        # Microseconds should be truncated to 123456
        cur.execute("INSERT INTO t (x) VALUES ('2012-04-04 15:06:00.123456789')")

        cur.execute("SELECT * FROM t")
        values = [x[0] for x in cur.fetchall()]

        self.assertEqual(values, [
            datetime.datetime(2012, 4, 4, 15, 6, 0, 456000),
            datetime.datetime(2012, 4, 4, 15, 6, 0, 123456),
        ]) 

def jobs():
    conn = sqlite3.connect(xbmc.translatePath('%sxmltv.db' % plugin.addon.getAddonInfo('profile')), detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
    cursor = conn.cursor()

    jobs = cursor.execute("SELECT * FROM jobs ORDER by channelname, start").fetchall()

    items = []

    now = datetime.now()
    for uid, uuid, channelid, channelname, title, start, stop, type in jobs:
        local_stop = utc2local(stop)
        if local_stop < now:
            continue

        url = ""
        channel = cursor.execute("SELECT * FROM streams WHERE tvg_id=?", (channelid, )).fetchone()
        if channel:
            uid, name, tvg_name, tvg_id, tvg_logo, groups, url = channel
            echannelname=name.encode("utf8")

        context_items = []

        context_items.append((_("Delete Job"), 'XBMC.RunPlugin(%s)' % (plugin.url_for(delete_job, job=uuid))))
        context_items.append((_("Delete All Jobs"), 'XBMC.RunPlugin(%s)' % (plugin.url_for(delete_all_jobs))))

        if url:
            context_items.append((_("Play Channel"), 'XBMC.RunPlugin(%s)' % (plugin.url_for(play_channel, channelname=echannelname))))
            if plugin.get_setting('external.player'):
                context_items.append((_("Play Channel External"), 'XBMC.RunPlugin(%s)' % (plugin.url_for(play_channel_external, channelname=echannelname))))


        label = "%s [COLOR yellow]%s[/COLOR] %s[COLOR grey]%s - %s[/COLOR] %s" % (channelname, title, CR, utc2local(start), utc2local(stop), type)

        items.append({
            'label': label,
            'path': plugin.url_for(delete_job, job=uuid),
            'context_menu': context_items,
            'thumbnail':get_icon_path('recordings'),
        })

    return items 

def setUp(self):
        self.con = sqlite.connect(":memory:", detect_types=sqlite.PARSE_DECLTYPES)
        self.cur = self.con.cursor()
        self.cur.execute("create table test(i int, s str, f float, b bool, u unicode, foo foo, bin blob, n1 number, n2 number(5))")

        # override float, make them always return the same number
        sqlite.converters["FLOAT"] = lambda x: 47.2

        # and implement two custom ones
        sqlite.converters["BOOL"] = lambda x: bool(int(x))
        sqlite.converters["FOO"] = DeclTypesTests.Foo
        sqlite.converters["WRONG"] = lambda x: "WRONG"
        sqlite.converters["NUMBER"] = float 

def __init__(self, dbfile=None, scan_changes=True, silent=False):
        if not dbfile:
            dblocation = appdirs.user_data_dir("PythonJukebox", "Razorvine")
            os.makedirs(dblocation, mode=0o700, exist_ok=True)
            dbfile = os.path.join(dblocation, "tracks.sqlite")
        dbfile = os.path.abspath(dbfile)
        self.dbfile = dbfile
        self.dbconn = sqlite3.connect(dbfile, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
        self.dbconn.row_factory = sqlite3.Row   # make sure we can get results by column name
        self.dbconn.execute("PRAGMA foreign_keys=ON")
        try:
            self.dbconn.execute("SELECT COUNT(*) FROM tracks").fetchone()
            if not silent:
                print("Connected to database.")
                print("Database file:", dbfile)
            if scan_changes:
                self.scan_changes()
        except sqlite3.OperationalError:
            # the table does not yet exist, create the schema
            if not silent:
                print("Creating new database.")
                print("Database file:", dbfile)
            self.dbconn.execute("""CREATE TABLE tracks
                (
                    id integer PRIMARY KEY,
                    title nvarchar(260),
                    artist nvarchar(260),
                    album nvarchar(260),
                    year int,
                    genre nvarchar(100),
                    duration real NOT NULL,
                    modified timestamp NOT NULL,
                    location nvarchar(500) NOT NULL,
                    hash char(40) NOT NULL UNIQUE
                );""") 

def delete_log_entry(log_id, token):
        """
        delete a log entry (DB & file), validate token first

        :return: True on success
        """
        con = sqlite3.connect(get_db_filename(), detect_types=sqlite3.PARSE_DECLTYPES)
        cur = con.cursor()
        cur.execute('select Token from Logs where Id = ?', (log_id,))
        db_tuple = cur.fetchone()
        if db_tuple is None:
            return False
        if token != db_tuple[0]: # validate token
            return False

        # kml file
        kml_path = get_kml_filepath()
        kml_file_name = os.path.join(kml_path, log_id.replace('/', '.')+'.kml')
        if os.path.exists(kml_file_name):
            os.unlink(kml_file_name)

        #preview image
        preview_image_filename = os.path.join(get_overview_img_filepath(), log_id+'.png')
        if os.path.exists(preview_image_filename):
            os.unlink(preview_image_filename)

        log_file_name = get_log_filename(log_id)
        print('deleting log entry {} and file {}'.format(log_id, log_file_name))
        os.unlink(log_file_name)
        cur.execute("DELETE FROM LogsGenerated WHERE Id = ?", (log_id,))
        cur.execute("DELETE FROM Logs WHERE Id = ?", (log_id,))
        con.commit()
        cur.close()
        con.close()

        # need to clear the cache as well
        clear_ulog_cache()

        return True 

def connect(self):
        if self.dbObject != None:
            raise Exception("MySQL: already connected to %s" % self.filename)
        #http://stackoverflow.com/questions/1829872/read-datetime-back-from-sqlite-as-a-datetime-in-python
        self.dbObject = sqlite3.connect(self.filename,  detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)


## Database driver factory
##