Python sqlite3.sqlite_version_info() Examples

def create(self):
        for schema_name, mapping in self.mappings.iteritems():
            self.db_cursor.execute('drop table if exists %s' % schema_name)
            slversion = sqlite3.sqlite_version_info[:3]
            if slversion > (3, 8, 0):
                self.db_cursor.execute(
                    '''CREATE VIRTUAL TABLE %s USING fts4(id, %s, prefix="3,5,7", %s);''' %
                    (schema_name,
                     ','.join(mapping['fields']),
                     ','.join(mapping['notindexed']))
                )
            elif slversion > (3, 7, 7):
                print "Warning: older version of sqlite3 detected, please upgrade to sqlite 3.8.0 or newer."
                self.db_cursor.execute(
                    '''CREATE VIRTUAL TABLE %s USING fts4(id, %s, prefix="3,5,7");''' %
                    (schema_name,
                     ','.join(mapping['fields']))
                )
            else:
                raise ImportError('Your version of sqlite3 is too old, please upgrade to sqlite 3.8.0 or newer.') 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def sqlite_check(app_configs, **kwargs):
    errors = []
    if 'sqlite' not in settings.DATABASES['default']['ENGINE']:
        # not using sqlite, so don't worry
        return errors

    import sqlite3
    if sqlite3.sqlite_version_info < (3, 12):
        errors.append(
            Warning(
                'SQLite version problem',
                hint='A bug is sqlite version 3.11.x causes a segfault in our tests. Upgrading to >=3.14 is suggested. This is only a warning because many things still work. Just not the tests.',
                id='coredata.E001',
            )
        )
    return errors 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def test_purge_deleted_rows_older(self):
        dialect = self.engine.url.get_dialect()
        if dialect == sqlite.dialect:
            # We're seeing issues with foreign key support in SQLite 3.6.20
            # SQLAlchemy doesn't support it at all with < SQLite 3.6.19
            # It works fine in SQLite 3.7.
            # Force foreign_key checking if running SQLite >= 3.7
            import sqlite3
            tup = sqlite3.sqlite_version_info
            if tup[0] > 3 or (tup[0] == 3 and tup[1] >= 7):
                self.conn.execute("PRAGMA foreign_keys = ON")
        # Purge at 10 days old now, should delete 2 more rows
        db.purge_deleted_rows(self.context, age_in_days=10)

        plans_rows = self.session.query(self.plans).count()
        resources_rows = self.session.query(self.resources).count()
        # Verify that we only have 2 rows now
        self.assertEqual(2, plans_rows)
        self.assertEqual(2, resources_rows) 

def test_purge_deleted_rows_old(self):
        dialect = self.engine.url.get_dialect()
        if dialect == sqlite.dialect:
            # We're seeing issues with foreign key support in SQLite 3.6.20
            # SQLAlchemy doesn't support it at all with < SQLite 3.6.19
            # It works fine in SQLite 3.7.
            # Force foreign_key checking if running SQLite >= 3.7
            import sqlite3
            tup = sqlite3.sqlite_version_info
            if tup[0] > 3 or (tup[0] == 3 and tup[1] >= 7):
                self.conn.execute("PRAGMA foreign_keys = ON")
        # Purge at 30 days old, should only delete 2 rows
        db.purge_deleted_rows(self.context, age_in_days=30)
        plans_rows = self.session.query(self.plans).count()
        resources_rows = self.session.query(self.resources).count()
        # Verify that we only deleted 2
        self.assertEqual(4, plans_rows)
        self.assertEqual(4, resources_rows) 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def CheckLocking(self):
        """
        This tests the improved concurrency with pysqlite 2.3.4. You needed
        to roll back con2 before you could commit con1.
        """
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")
        # NO self.con2.rollback() HERE!!!
        self.con1.commit() 

def __init__(self, isolation_level=None, native_datetime=False, **kwargs):
        default.DefaultDialect.__init__(self, **kwargs)
        self.isolation_level = isolation_level

        # this flag used by pysqlite dialect, and perhaps others in the
        # future, to indicate the driver is handling date/timestamp
        # conversions (and perhaps datetime/time as well on some hypothetical
        # driver ?)
        self.native_datetime = native_datetime

        if self.dbapi is not None:
            self.supports_right_nested_joins = (
                self.dbapi.sqlite_version_info >= (3, 7, 16))
            self._broken_dotted_colnames = (
                self.dbapi.sqlite_version_info < (3, 10, 0)
            )
            self.supports_default_values = (
                self.dbapi.sqlite_version_info >= (3, 3, 8))
            self.supports_cast = (
                self.dbapi.sqlite_version_info >= (3, 2, 3))
            self.supports_multivalues_insert = (
                # http://www.sqlite.org/releaselog/3_7_11.html
                self.dbapi.sqlite_version_info >= (3, 7, 11))
            # see http://www.sqlalchemy.org/trac/ticket/2568
            # as well as http://www.sqlite.org/src/info/600482d161
            self._broken_fk_pragma_quotes = (
                self.dbapi.sqlite_version_info < (3, 6, 14)) 

def CheckOnConflictRollback(self):
        if sqlite.sqlite_version_info < (3, 2, 2):
            return
        con = sqlite.connect(":memory:")
        con.execute("create table foo(x, unique(x) on conflict rollback)")
        con.execute("insert into foo(x) values (1)")
        try:
            con.execute("insert into foo(x) values (1)")
        except sqlite.DatabaseError:
            pass
        con.execute("insert into foo(x) values (2)")
        try:
            con.commit()
        except sqlite.OperationalError:
            self.fail("pysqlite knew nothing about the implicit ROLLBACK") 

def CheckRaiseTimeout(self):
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError") 

def __init__(self, isolation_level=None, native_datetime=False, **kwargs):
        default.DefaultDialect.__init__(self, **kwargs)
        self.isolation_level = isolation_level

        # this flag used by pysqlite dialect, and perhaps others in the
        # future, to indicate the driver is handling date/timestamp
        # conversions (and perhaps datetime/time as well on some hypothetical
        # driver ?)
        self.native_datetime = native_datetime

        if self.dbapi is not None:
            self.supports_right_nested_joins = (
                self.dbapi.sqlite_version_info >= (3, 7, 16))
            self._broken_dotted_colnames = (
                self.dbapi.sqlite_version_info < (3, 10, 0)
            )
            self.supports_default_values = (
                self.dbapi.sqlite_version_info >= (3, 3, 8))
            self.supports_cast = (
                self.dbapi.sqlite_version_info >= (3, 2, 3))
            self.supports_multivalues_insert = (
                # http://www.sqlite.org/releaselog/3_7_11.html
                self.dbapi.sqlite_version_info >= (3, 7, 11))
            # see http://www.sqlalchemy.org/trac/ticket/2568
            # as well as http://www.sqlite.org/src/info/600482d161
            self._broken_fk_pragma_quotes = (
                self.dbapi.sqlite_version_info < (3, 6, 14)) 

def check_runtime_requirements():
    if sqlite3.sqlite_version_info < (3, 9, 0):
        raise RuntimeError("Need sqlite version >= 3.9.0, you have: %r" % (sqlite3.sqlite_version,)) 

def _sqlite_has_fk_constraint(self):
        # SQLAlchemy doesn't support it at all with < SQLite 3.6.19
        import sqlite3
        tup = sqlite3.sqlite_version_info
        return tup[0] > 3 or (tup[0] == 3 and tup[1] >= 7) 

def CheckRaiseTimeout(self):
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError") 

def CheckSqlTimestamp(self):
        # The date functions are only available in SQLite version 3.1 or later
        if sqlite.sqlite_version_info < (3, 1):
            return

        # SQLite's current_timestamp uses UTC time, while datetime.datetime.now() uses local time.
        now = datetime.datetime.now()
        self.cur.execute("insert into test(ts) values (current_timestamp)")
        self.cur.execute("select ts from test")
        ts = self.cur.fetchone()[0]
        self.assertEqual(type(ts), datetime.datetime)
        self.assertEqual(ts.year, now.year) 

def CheckOpenUri(self):
        if sqlite.sqlite_version_info < (3, 7, 7):
            with self.assertRaises(sqlite.NotSupportedError):
                sqlite.connect(':memory:', uri=True)
            return
        self.addCleanup(unlink, TESTFN)
        with sqlite.connect(TESTFN) as cx:
            cx.execute('create table test(id integer)')
        with sqlite.connect('file:' + TESTFN, uri=True) as cx:
            cx.execute('insert into test(id) values(0)')
        with sqlite.connect('file:' + TESTFN + '?mode=ro', uri=True) as cx:
            with self.assertRaises(sqlite.OperationalError):
                cx.execute('insert into test(id) values(1)') 

def create_table(self, table):
        if not table in self.table_definitions.keys():
            return

        if self. print_data_structure:
            print 'creating table', table

        create_string = u"CREATE TABLE IF NOT EXISTS %s" % table
        psplit = u" ("
        for fld in self.get_fields(table):
            create_string = u"%s%s%s" % (create_string, psplit, self.get_column_string(table, fld))
            psplit = u", "

        pkfields = self.get_ifields(table, 'PRIMARY')
        pkstring = u")"
        if len(pkfields) > 0:
            pkstring = u", PRIMARY KEY"
            psplit = u" ("
            for fld in pkfields:
                if not fld in self.get_fields(table):
                    continue

                pkstring += u"%s`%s`"% (psplit, fld)
                psplit = u", "

            if self.get_iprop(table, 'PRIMARY', "on conflict", '').upper() in ('ROLLBACK', 'ABORT', 'FAIL', 'IGNORE', 'REPLACE'):
                pkstring += u") ON CONFLICT %s)" % self.get_iprop(table, 'PRIMARY', "on conflict", '').upper()

            else:
                pkstring += u"))"

        create_string = u"%s%s"% (create_string, pkstring)
        if self.get_tprop(table, "no rowid") and sqlite3.sqlite_version_info >= (3, 8, 2):
            create_string += u" WITHOUT ROWID"

        self.execute(create_string)
        self.check_indexes(table) 

def test_bad_target_in_transaction(self):
        bck = sqlite.connect(':memory:')
        bck.execute('CREATE TABLE bar (key INTEGER)')
        bck.executemany('INSERT INTO bar (key) VALUES (?)', [(3,), (4,)])
        with self.assertRaises(sqlite.OperationalError) as cm:
            self.cx.backup(bck)
        if sqlite.sqlite_version_info < (3, 8, 8):
            self.assertEqual(str(cm.exception), 'target is in transaction') 

def CheckOpenUri(self):
        if sqlite.sqlite_version_info < (3, 7, 7):
            with self.assertRaises(sqlite.NotSupportedError):
                sqlite.connect(':memory:', uri=True)
            return
        self.addCleanup(unlink, TESTFN)
        with sqlite.connect(TESTFN) as cx:
            cx.execute('create table test(id integer)')
        with sqlite.connect('file:' + TESTFN, uri=True) as cx:
            cx.execute('insert into test(id) values(0)')
        with sqlite.connect('file:' + TESTFN + '?mode=ro', uri=True) as cx:
            with self.assertRaises(sqlite.OperationalError):
                cx.execute('insert into test(id) values(1)') 

def schema_create(conn):
    """Create the index for storing profiles.

    This is idempotent; it can be called every time a database is opened to
    make sure it's ready to use and up-to-date.

    :param conn: A connection to an SQLite3 database.
    """
    conn.execute(
        dedent(
            """\
    CREATE TABLE IF NOT EXISTS profiles
      (id INTEGER PRIMARY KEY,
       name TEXT NOT NULL UNIQUE,
       data BLOB NOT NULL,
       selected BOOLEAN NOT NULL DEFAULT FALSE)
    """
        )
    )
    # Partial indexes are only available in >=3.8.0 and expressions in indexes
    # are only available in >=3.9.0 (https://www.sqlite.org/partialindex.html
    # & https://www.sqlite.org/expridx.html). Don't bother with any kind of
    # index before that because it would complicate upgrades.
    if sqlite3.sqlite_version_info >= (3, 9, 0):
        # This index is for data integrity -- ensuring that only one profile
        # is the default ("selected") profile -- and speed a distant second.
        conn.execute(
            dedent(
                """\
        CREATE UNIQUE INDEX IF NOT EXISTS
          only_one_profile_selected ON profiles
          (selected IS NOT NULL) WHERE selected
        """
            )
        ) 

def __init__(self, isolation_level=None, native_datetime=False, **kwargs):
        default.DefaultDialect.__init__(self, **kwargs)
        self.isolation_level = isolation_level

        # this flag used by pysqlite dialect, and perhaps others in the
        # future, to indicate the driver is handling date/timestamp
        # conversions (and perhaps datetime/time as well on some hypothetical
        # driver ?)
        self.native_datetime = native_datetime

        if self.dbapi is not None:
            self.supports_right_nested_joins = (
                self.dbapi.sqlite_version_info >= (3, 7, 16))
            self._broken_dotted_colnames = (
                self.dbapi.sqlite_version_info < (3, 10, 0)
            )
            self.supports_default_values = (
                self.dbapi.sqlite_version_info >= (3, 3, 8))
            self.supports_cast = (
                self.dbapi.sqlite_version_info >= (3, 2, 3))
            self.supports_multivalues_insert = (
                # http://www.sqlite.org/releaselog/3_7_11.html
                self.dbapi.sqlite_version_info >= (3, 7, 11))
            # see http://www.sqlalchemy.org/trac/ticket/2568
            # as well as http://www.sqlite.org/src/info/600482d161
            self._broken_fk_pragma_quotes = (
                self.dbapi.sqlite_version_info < (3, 6, 14)) 

def test_purge_deleted_rows_integrity_failure(self):
        dialect = self.engine.url.get_dialect()
        if dialect == sqlite.dialect:
            # We're seeing issues with foreign key support in SQLite 3.6.20
            # SQLAlchemy doesn't support it at all with < SQLite 3.6.19
            # It works fine in SQLite 3.7.
            # So return early to skip this test if running SQLite < 3.7
            import sqlite3
            tup = sqlite3.sqlite_version_info
            if tup[0] < 3 or (tup[0] == 3 and tup[1] < 7):
                self.skipTest(
                    'sqlite version too old for reliable SQLA foreign_keys')
            self.conn.execute("PRAGMA foreign_keys = ON")

        # add new entry in plans and resources for
        # integrity check
        uuid_str = uuidutils.generate_uuid(dashed=False)
        ins_stmt = self.plans.insert().values(id=uuid_str)
        self.conn.execute(ins_stmt)
        ins_stmt = self.resources.insert().values(
            plan_id=uuid_str)
        self.conn.execute(ins_stmt)

        # set plans record to deleted 20 days ago
        old = timeutils.utcnow() - datetime.timedelta(days=20)
        make_old = self.plans.update().where(
            self.plans.c.id.in_([uuid_str])).values(deleted_at=old)
        self.conn.execute(make_old)

        # Verify that purge_deleted_rows fails due to Foreign Key constraint
        self.assertRaises(db_exc.DBReferenceError, db.purge_deleted_rows,
                          self.context, age_in_days=10) 

def test_bad_target_in_transaction(self):
        bck = sqlite.connect(':memory:')
        bck.execute('CREATE TABLE bar (key INTEGER)')
        bck.executemany('INSERT INTO bar (key) VALUES (?)', [(3,), (4,)])
        with self.assertRaises(sqlite.OperationalError) as cm:
            self.cx.backup(bck)
        if sqlite.sqlite_version_info < (3, 8, 8):
            self.assertEqual(str(cm.exception), 'target is in transaction') 

def CheckOpenUri(self):
        if sqlite.sqlite_version_info < (3, 7, 7):
            with self.assertRaises(sqlite.NotSupportedError):
                sqlite.connect(':memory:', uri=True)
            return
        self.addCleanup(unlink, TESTFN)
        with sqlite.connect(TESTFN) as cx:
            cx.execute('create table test(id integer)')
        with sqlite.connect('file:' + TESTFN, uri=True) as cx:
            cx.execute('insert into test(id) values(0)')
        with sqlite.connect('file:' + TESTFN + '?mode=ro', uri=True) as cx:
            with self.assertRaises(sqlite.OperationalError):
                cx.execute('insert into test(id) values(1)') 

def __init__(self, isolation_level=None, native_datetime=False, **kwargs):
        default.DefaultDialect.__init__(self, **kwargs)
        self.isolation_level = isolation_level

        # this flag used by pysqlite dialect, and perhaps others in the
        # future, to indicate the driver is handling date/timestamp
        # conversions (and perhaps datetime/time as well on some hypothetical
        # driver ?)
        self.native_datetime = native_datetime

        if self.dbapi is not None:
            self.supports_right_nested_joins = (
                self.dbapi.sqlite_version_info >= (3, 7, 16))
            self._broken_dotted_colnames = (
                self.dbapi.sqlite_version_info < (3, 10, 0)
            )
            self.supports_default_values = (
                self.dbapi.sqlite_version_info >= (3, 3, 8))
            self.supports_cast = (
                self.dbapi.sqlite_version_info >= (3, 2, 3))
            self.supports_multivalues_insert = (
                # http://www.sqlite.org/releaselog/3_7_11.html
                self.dbapi.sqlite_version_info >= (3, 7, 11))
            # see http://www.sqlalchemy.org/trac/ticket/2568
            # as well as http://www.sqlite.org/src/info/600482d161
            self._broken_fk_pragma_quotes = (
                self.dbapi.sqlite_version_info < (3, 6, 14)) 

def CheckRaiseTimeout(self):
        if sqlite.sqlite_version_info < (3, 2, 2):
            # This will fail (hang) on earlier versions of sqlite.
            # Determine exact version it was fixed. 3.2.1 hangs.
            return
        self.cur1.execute("create table test(i)")
        self.cur1.execute("insert into test(i) values (5)")
        try:
            self.cur2.execute("insert into test(i) values (5)")
            self.fail("should have raised an OperationalError")
        except sqlite.OperationalError:
            pass
        except:
            self.fail("should have raised an OperationalError")