def CheckCreateCollationBadUpper(self):
class BadUpperStr(str):
def upper(self):
return None
con = sqlite.connect(":memory:")
mycoll = lambda x, y: -((x > y) - (x < y))
con.create_collation(BadUpperStr("mycoll"), mycoll)
result = con.execute("""
select x from (
select 'a' as x
union
select 'b' as x
) order by x collate mycoll
""").fetchall()
self.assertEqual(result[0][0], 'b')
self.assertEqual(result[1][0], 'a')
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), bad bad)"
)
# 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["BAD"] = DeclTypesTests.BadConform
sqlite.converters["WRONG"] = lambda x: "WRONG"
sqlite.converters["NUMBER"] = float
def CheckInTransaction(self):
# Can't use db from setUp because we want to test initial state.
cx = sqlite.connect(":memory:")
cu = cx.cursor()
self.assertEqual(cx.in_transaction, False)
cu.execute(
"create table transactiontest(id integer primary key, name text)")
self.assertEqual(cx.in_transaction, False)
cu.execute("insert into transactiontest(name) values (?)", ("foo", ))
self.assertEqual(cx.in_transaction, True)
cu.execute("select name from transactiontest where name=?", ["foo"])
row = cu.fetchone()
self.assertEqual(cx.in_transaction, True)
cx.commit()
self.assertEqual(cx.in_transaction, False)
cu.execute("select name from transactiontest where name=?", ["foo"])
row = cu.fetchone()
self.assertEqual(cx.in_transaction, False)
def setUp(self):
self.con = sqlite.connect(":memory:")
self.con.create_function("returntext", 0, func_returntext)
self.con.create_function("returnunicode", 0, func_returnunicode)
self.con.create_function("returnint", 0, func_returnint)
self.con.create_function("returnfloat", 0, func_returnfloat)
self.con.create_function("returnnull", 0, func_returnnull)
self.con.create_function("returnblob", 0, func_returnblob)
self.con.create_function("returnlonglong", 0, func_returnlonglong)
self.con.create_function("raiseexception", 0, func_raiseexception)
self.con.create_function("isstring", 1, func_isstring)
self.con.create_function("isint", 1, func_isint)
self.con.create_function("isfloat", 1, func_isfloat)
self.con.create_function("isnone", 1, func_isnone)
self.con.create_function("isblob", 1, func_isblob)
self.con.create_function("islonglong", 1, func_islonglong)
self.con.create_function("spam", -1, func)
def CheckCollationReturnsLargeInteger(self):
def mycoll(x, y):
# reverse order
return -((x > y) - (x < y)) * 2**32
con = sqlite.connect(":memory:")
con.create_collation("mycoll", mycoll)
sql = """
select x from (
select 'a' as x
union
select 'b' as x
union
select 'c' as x
) order by x collate mycoll
"""
result = con.execute(sql).fetchall()
self.assertEqual(result, [('c', ), ('b', ), ('a', )],
msg="the expected order was not returned")
def CheckRecursiveCursorUse(self):
"""
http://bugs.python.org/issue10811
Recursively using a cursor, such as when reusing it from a generator led to segfaults.
Now we catch recursive cursor usage and raise a ProgrammingError.
"""
con = sqlite.connect(":memory:")
cur = con.cursor()
cur.execute("create table a (bar)")
cur.execute("create table b (baz)")
def foo():
cur.execute("insert into a (bar) values (?)", (1, ))
yield 1
with self.assertRaises(sqlite.ProgrammingError):
cur.executemany("insert into b (baz) values (?)",
((i, ) for i in foo()))
def get_messages(config, key):
logger.info('Connecting to sql/db.sqlite, reading messages...')
conn = sqlite.connect(os.path.join(config['signalDir'], 'sql/db.sqlite'))
try:
c = conn.cursor()
c.execute(f"PRAGMA key=\"x'{key}'\"")
for setting, value in config.get('sqlcipher', {}).items():
c.execute(f"PRAGMA {setting}={value}")
c.execute("select json from items where id=?", ('number_id', ))
number_id = json.loads(c.fetchone()[0])
own_number, device_id = number_id['value'].split('.')
logger.info('Own number: %s, device ID: %s', own_number, device_id)
cond = ["hasVisualMediaAttachments > 0"]
if not config.get('includeExpiringMessages', False):
cond.append("expires_at is null")
c.execute(f"""
select id, json
from messages
where { ' and '.join(cond) }
order by sent_at asc
{f'limit {config["maxMessages"]}' if config["maxMessages"] > 0 else ''}
""")
for row in c:
msg = json.loads(row[1])
if 'source' not in msg and msg['type'] == 'outgoing':
msg['source'] = own_number
yield (row[0], msg)
except sqlite.DatabaseError as err:
logger.fatal(
'DatabaseError "%s" - please check the database and the sqlcipher parameters!',
' | '.join(err.args))
finally:
conn.close()
def CheckUnicodeContent(self):
"""
Test that the statement can contain unicode literals.
"""
unicode_value = '\xf6\xe4\xfc\xd6\xc4\xdc\xdf\u20ac'
con = sqlite.connect(":memory:")
traced_statements = []
def trace(statement):
traced_statements.append(statement)
con.set_trace_callback(trace)
con.execute("create table foo(x)")
# Can't execute bound parameters as their values don't appear
# in traced statements before SQLite 3.6.21
# (cf. http://www.sqlite.org/draft/releaselog/3_6_21.html)
con.execute('insert into foo(x) values ("%s")' % unicode_value)
con.commit()
self.assertTrue(
any(unicode_value in stmt for stmt in traced_statements),
"Unicode data %s garbled in trace callback: %s" %
(ascii(unicode_value), ', '.join(map(ascii, traced_statements))))
def test_modifying_progress(self):
journal = []
def progress(status, remaining, total):
if not journal:
self.cx.execute('INSERT INTO foo (key) VALUES (?)',
(remaining + 1000, ))
self.cx.commit()
journal.append(remaining)
with sqlite.connect(':memory:') as bck:
self.cx.backup(bck, pages=1, progress=progress)
self.verify_backup(bck)
result = bck.execute("SELECT key FROM foo"
" WHERE key >= 1000"
" ORDER BY key").fetchall()
self.assertEqual(result[0][0], 1001)
self.assertEqual(len(journal), 3)
self.assertEqual(journal[0], 1)
self.assertEqual(journal[1], 1)
self.assertEqual(journal[2], 0)
def setUp(self):
self.con = sqlite.connect(":memory:")
cur = self.con.cursor()
cur.execute("""
create table test(
t text,
i integer,
f float,
n,
b blob
)
""")
cur.execute("insert into test(t, i, f, n, b) values (?, ?, ?, ?, ?)",
("foo", 5, 3.14, None, memoryview(b"blob"),))
self.con.create_aggregate("nostep", 1, AggrNoStep)
self.con.create_aggregate("nofinalize", 1, AggrNoFinalize)
self.con.create_aggregate("excInit", 1, AggrExceptionInInit)
self.con.create_aggregate("excStep", 1, AggrExceptionInStep)
self.con.create_aggregate("excFinalize", 1, AggrExceptionInFinalize)
self.con.create_aggregate("checkType", 2, AggrCheckType)
self.con.create_aggregate("checkTypes", -1, AggrCheckTypes)
self.con.create_aggregate("mysum", 1, AggrSum)
def CheckOpcodeCount(self):
"""
Test that the opcode argument is respected.
"""
con = sqlite.connect(":memory:")
progress_calls = []
def progress():
progress_calls.append(None)
return 0
con.set_progress_handler(progress, 1)
curs = con.cursor()
curs.execute("""
create table foo (a, b)
""")
first_count = len(progress_calls)
progress_calls = []
con.set_progress_handler(progress, 2)
curs.execute("""
create table bar (a, b)
""")
second_count = len(progress_calls)
self.assertGreaterEqual(first_count, second_count)
def setUp(self):
self.con = sqlite.connect(":memory:", factory=MyConnection)
def setUp(self):
self.con = sqlite.connect(":memory:")
self.con.execute("create table test (value text)")
self.con.execute("insert into test (value) values (?)", ("a\x00b", ))
def setUp(self):
self.con = sqlite.connect(":memory:",
detect_types=sqlite.PARSE_DECLTYPES)
self.cur = self.con.cursor()
self.cur.execute("create table test(d date, ts timestamp)")
def setUp(self):
self.con = sqlite.connect(":memory:",
detect_types=sqlite.PARSE_COLNAMES)
sqlite.register_converter("bin", BinaryConverterTests.convert)
def setUp(self):
self.con = sqlite.connect(":memory:")
self.cur = self.con.cursor()
self.cur.execute(
"create table test(i integer, s varchar, f number, b blob)")
def CheckFailedOpen(self):
YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
with self.assertRaises(sqlite.OperationalError):
con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
def CheckClosedCurExecute(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
con.close()
with self.assertRaises(sqlite.ProgrammingError):
cur.execute("select 4")
def CheckConnectionExecute(self):
con = sqlite.connect(":memory:")
result = con.execute("select 5").fetchone()[0]
self.assertEqual(result, 5, "Basic test of Connection.execute")
def CheckScriptErrorNormal(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
with self.assertRaises(sqlite.OperationalError):
cur.executescript(
"create table test(sadfsadfdsa); select foo from hurz;")
def CheckScriptSyntaxError(self):
con = sqlite.connect(":memory:")
cur = con.cursor()
with self.assertRaises(sqlite.OperationalError):
cur.executescript(
"create table test(x); asdf; create table test2(x)")
def setUp(self):
self.cx = sqlite.connect(":memory:")
self.cu = self.cx.cursor()
self.cu.execute("create table test(id integer primary key, name text, "
"income number, unique_test text unique)")
self.cu.execute("insert into test(name) values (?)", ("foo", ))
def CheckSameThreadErrorOnOldVersion(self):
with self.assertRaises(sqlite.NotSupportedError) as cm:
sqlite.connect(':memory:', check_same_thread=False)
self.assertEqual(str(cm.exception), 'shared connections not available')
def CheckClosedCall(self):
con = sqlite.connect(":memory:")
con.close()
with self.assertRaises(sqlite.ProgrammingError):
con()
def setUp(self):
self.con = sqlite.connect(":memory:")
def CheckConnectionExecutescript(self):
con = sqlite.connect(":memory:")
con.executescript(
"create table test(foo); insert into test(foo) values (5);")
result = con.execute("select foo from test").fetchone()[0]
self.assertEqual(result, 5, "Basic test of Connection.executescript")
logger = logging.getLogger(__name__)
if not sys.platform.startswith('win'):
import coloredlogs
coloredlogs.install(level='INFO')
args = parse_args()
dataDir = args.data_dir
if not os.access(os.path.dirname(args.db_file), os.X_OK) and not os.access(
args.db_file, os.W_OK):
print("无法创建或打开数据库文件")
exit()
elif not os.access(dataDir, os.X_OK):
print("数据目录不存在或权限不足")
exit()
conn = sqlite.connect(args.db_file)
while True:
try:
passwd = getpass.getpass("输入数据库密码：")
if passwd != '':
conn.execute("PRAGMA KEY = %s" % passwd)
conn.execute("CREATE TABLE IF NOT EXISTS msg (\
msg_id TEXT NOT NULL,\
create_time INTEGER NOT NULL,\
msg_type INTEGER NOT NULL,\
content TEXT,\
from_name TEXT,\
from_nickname TEXT,\
to_name TEXT,\
to_nickname TEXT,\
group_name TEXT\
def setUp(self):
self.con = sqlite.connect(":memory:")
self.cur = self.con.cursor()
self.cur.execute("create table test(x foo)")
def setUp(self):
self.con = sqlite.connect(":memory:")
self.cur = self.con.cursor()
self.cur.execute(
"create table test(id integer primary key, name text, bin binary, ratio number, ts timestamp)"
)
def setUp(self):
self.cx = sqlite.connect(":memory:")
self.cx.execute(
"create table test(id integer primary key, blob_col blob)")
self.cx.execute("insert into test(blob_col) values (zeroblob(100))")
