def insert_match_to_sqlite(file_pathname: Any, start_time: datetime,
end_time: datetime, game_mode: str, map_name: str,
frags: List[Tuple[datetime, Any]]) -> int:
"""Insert Game Session Data into SQLite.
Args:
file_pathname: The path and name of the Far Cry's SQLite database.
start_time: The start of the game session.
end_time: The end of the game session.
game_mode: Multi-player mode of the game session.
map_name: Name of the map that was played.
frags: A list of tuples of the frags.
Returns: The identifier of the match that has been inserted.
"""
insert_statement = '''INSERT INTO match (start_time, end_time, game_mode,
map_name) VALUES (?,?,?,?)'''
try:
with sqlite_connect(file_pathname) as conn:
cur = conn.cursor()
cur.execute(insert_statement,
(start_time, end_time, game_mode, map_name))
insert_frags_to_sqlite(conn, cur.lastrowid, frags)
return cur.lastrowid
except sqlite_DatabaseError as e:
error(e, exc_info=True)
return 0
def get_training_set():
db_is_exist = Path(PATH_TO_PROBLEM_DATABASE).exists()
with sqlite_connect(PATH_TO_PROBLEM_DATABASE) as conn:
if not db_is_exist:
_create_database(conn)
_fill_database(conn)
return get_problems(conn)
def func():
""" func """
nonlocal connect, cursor
cursor.execute('select 1')
result = cursor.fetchone()
if result == (1, ):
return connect, cursor
else:
connect = sqlite_connect('../efence.db')
cursor = connect.cursor()
return connect, cursor
async def _create_connection(self) -> None:
# Create directories
await self.path.parent.mkdir(mode=0o700, parents=True, exist_ok=True)
# Create sqlite connection
self._conn = sqlite_connect(str(self.path), check_same_thread=False)
# The default isolation level ("") lets python manage the transaction
# so we can periodically commit the pending changes.
assert self._conn.isolation_level == ""
# The combination of WAL journal mode and NORMAL synchronous mode
# is a great combination: it allows for fast commits (~10 us compare
# to 15 ms the default mode) but still protects the database against
# corruption in the case of OS crash or power failure.
async with self._manage_operational_error():
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.execute("PRAGMA synchronous=NORMAL")
def deserialize(cls, fobj: IO[bytes]) -> Iterable[dict]:
with NamedTemporaryFile() as db_file:
copyfileobj(fobj, db_file)
db_file.seek(0)
with closing(sqlite_connect(db_file.name)) as connection:
connection.row_factory = SqliteRow
with closing(connection.cursor()) as cursor:
joined = cursor.execute('''
SELECT emails.id AS __row_order__, *
FROM emails
LEFT OUTER JOIN emails_to_attachments
ON emails.id = emails_to_attachments.email_id
LEFT OUTER JOIN attachments
ON attachments.id = emails_to_attachments.attachment_id
LEFT OUTER JOIN contents
ON attachments.content_id = contents.id
ORDER BY __row_order__
''')
for _, rows in groupby(joined,
itemgetter('__row_order__')):
rows = list(rows)
row = rows[0]
obj = {
key: row[key]
for key in ('_uid', 'read', 'sent_at', 'from',
'subject', 'body')
if row[key] is not None
}
for key in ('to', 'cc', 'bcc'):
obj[key] = cls._deserialize_list(row, key)
obj['attachments'] = []
for row in rows:
attachment = {
key: row[key]
for key in ('filename', 'content', 'cid')
if row[key] is not None
}
if attachment:
obj['attachments'].append(attachment)
obj = unbyteify_attachments(obj)
yield obj
def sqlite_execute(command, params=None):
"""
Function to execute a sql statement on the mysql database. This function is
called by the database_execute function when the sqlite backend is set in
the configuration file
@param command the sql command to execute
@param params a list of tuple of values to substitute in command
@returns a list of dictionaries representing the sql result
"""
# NOTE mostly copypasta'd from mysql_execute, may be a better way
getLogger(__name__).debug("sqlite_execute(" + command + ", " +
str(params) + ")", extra=get_sql_log_dict())
try:
parser = lox_config.ConfigSingleton(module.NAME)
filename = parser.get('database', 'filename')
init_db = not exists(filename)
connection = sqlite_connect(filename)
connection.text_factory = Binary
cursor = connection.cursor()
cursor.execute('PRAGMA foreign_keys = ON;')
connection.commit()
if cursor.fetchall() is None:
exit("cannot do foreign keys on this SQLite database, exiting")
if params:
cursor.execute(command, params)
else:
cursor.execute(command)
connection.commit()
return cursor.fetchall()
except MySQLError as mysqlerror:
getLogger(__name__).info(
"MySQL Error: %d: %s" %
(mysqlerror.args[0], mysqlerror.args[1]))
except NoSectionError:
getLogger(__name__).info("Please configure the database")
finally:
try:
if connection:
connection.close()
except UnboundLocalError:
pass
def sqlite_execute(command, params=None):
"""
Function to execute a sql statement on the mysql database. This function is
called by the database_execute function when the sqlite backend is set in
the configuration file
@param command the sql command to execute
@param params a list of tuple of values to substitute in command
@returns a list of dictionaries representing the sql result
"""
# NOTE mostly copypasta'd from mysql_execute, may be a better way
getLogger(__name__).debug("sqlite_execute(" + command + ", " +
str(params) + ")",
extra=get_sql_log_dict())
try:
parser = lox_config.ConfigSingleton(module.NAME)
filename = parser.get('database', 'filename')
init_db = not exists(filename)
connection = sqlite_connect(filename)
connection.text_factory = Binary
cursor = connection.cursor()
cursor.execute('PRAGMA foreign_keys = ON;')
connection.commit()
if cursor.fetchall() is None:
exit("cannot do foreign keys on this SQLite database, exiting")
if params:
cursor.execute(command, params)
else:
cursor.execute(command)
connection.commit()
return cursor.fetchall()
except MySQLError as mysqlerror:
getLogger(__name__).info("MySQL Error: %d: %s" %
(mysqlerror.args[0], mysqlerror.args[1]))
except NoSectionError:
getLogger(__name__).info("Please configure the database")
finally:
try:
if connection:
connection.close()
except UnboundLocalError:
pass
def __init__(self, path, others):
self.db = sqlite_connect(path)
self.cur = self.db.cursor()
self.cur.executescript('''
CREATE TABLE IF NOT EXISTS symbols (
file_id INTEGER REFERENCES files(id),
symbol TEXT NOT NULL, -- Symbol name, valid Python identifier.
scope TEXT NOT NULL, -- Scope inside a file (eg. class name).
row INTEGER NOT NULL,
col INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS symbols_symbol ON symbols(symbol);
CREATE TABLE IF NOT EXISTS files (
id INTEGER PRIMARY KEY,
path TEXT NOT NULL UNIQUE,
package TEXT NOT NULL, -- Package name (eg. "os.path").
timestamp REAL NOT NULL -- Last modification time.
);
CREATE UNIQUE INDEX IF NOT EXISTS files_path ON files(path);
''')
self.db_prefixes = ['']
for i in xrange(len(others)):
db_name = 'db{}'.format(i)
self.cur.execute('''
ATTACH DATABASE ? AS ?
''', (others[i], db_name))
self.db_prefixes.append('{}.'.format(db_name))
# Performance sucks when using views.
self.cur.execute('CREATE TEMP VIEW all_symbols AS ' +
' UNION ALL '.join(
'SELECT *, {0} AS dbid FROM {1}symbols'.format(i, prefix)
for i, prefix in enumerate(self.db_prefixes)))
self.cur.execute('CREATE TEMP VIEW all_files AS ' +
' UNION ALL '.join(
'SELECT *, {0} AS dbid FROM {1}files'.format(i, prefix)
for i, prefix in enumerate(self.db_prefixes)))
def sqlite_execute(command, params=None):
"""
Function to execute a sql statement on the mysql database. This function is
called by the database_execute function when the sqlite backend is set in
the configuration file
:param command: the sql command to execute
:param params: a list of tuple of values to substitute in command
:returns: a list of dictionaries representing the sql result
"""
# NOTE mostly copypasta'd from mysql_execute, may be a better way
try:
filename = config.get('database', 'filename')
init_db = not exists(filename)
connection = sqlite_connect(filename)
cursor = connection.cursor()
if init_db:
for sql in open('database.sql').read().split("\n"):
if sql != "" and sql is not None:
cursor.execute(sql)
connection.commit()
if params:
cursor.execute(command, params)
else:
cursor.execute(command)
connection.commit()
return cursor.fetchall()
except MySQLError as mysqlerror:
print("MySQL Error: %d: %s" %
(mysqlerror.args[0], mysqlerror.args[1]))
except NoSectionError:
print("Please configure the database")
finally:
try:
if connection:
connection.close()
except UnboundLocalError:
pass
def sqlite_execute(command, params=None):
"""
Function to execute a sql statement on the mysql database. This function is
called by the database_execute function when the sqlite backend is set in
the configuration file
:param command: the sql command to execute
:param params: a list of tuple of values to substitute in command
:returns: a list of dictionaries representing the sql result
"""
# NOTE mostly copypasta'd from mysql_execute, may be a better way
try:
filename = config.get('database', 'filename')
init_db = not exists(filename)
connection = sqlite_connect(filename)
cursor = connection.cursor()
if init_db:
for sql in open('database.sql').read().split("\n"):
if sql != "" and sql is not None:
cursor.execute(sql)
connection.commit()
if params:
cursor.execute(command, params)
else:
cursor.execute(command)
connection.commit()
return cursor.fetchall()
except MySQLError as mysqlerror:
print("MySQL Error: %d: %s" % (mysqlerror.args[0], mysqlerror.args[1]))
except NoSectionError:
print("Please configure the database")
finally:
try:
if connection:
connection.close()
except UnboundLocalError:
pass
def get_sqlite3_conx(db_name):
"""Get SQLite3 connection for communicating with the local DB"""
conn = sqlite_connect(db_name, check_same_thread=False) # Allows multithread access
return conn
def __enter__(self): self.con = sqlite_connect(self.fname) return self
for statement in [x for x in statements if x.startswith(f'database upgrade {DatabaseVersion.UPDATE3.value}.')]:
database_manager.execute(statement)
database_manager.execute('setting update', arguments=(str(DatabaseVersion.CURRENT.value), ), format_args=('version', ))
settings['version'] = DatabaseVersion.CURRENT
OnSettingsLoaded.manager.notify(settings)
# ============================================================================
# >> INITIALIZATION
# ============================================================================
# TODO: Get MySQL working
if _driver == 'mysql':
_queue.put(_Node(NodeType.CONNECT, query=lambda: mysql_connect(host=_database['host'], port=_database['port'], user=_database['user'], passwd=_database['password'], connect_timeout=_database['timeout'], charset='utf8mb4')))
database_manager.execute('toggle warnings', format_args=(0, ))
database_manager.execute('create database', format_args=(_database['database'], ))
_queue.put(_Node(NodeType.USE, query=statements['use database'].format(_database['database'])))
else:
_queue.put(_Node(NodeType.CONNECT, query=lambda: sqlite_connect(DATA_PATH.joinpath('players.sqlite'))))
for statement in ('create players', 'create races', 'create skills', 'create stats', 'create stats races', 'create stats items', 'create settings'):
database_manager.execute(statement)
if _driver == 'mysql':
database_manager.execute('toggle warnings', format_args=(1, ))
database_manager.execute('setting get', callback=_query_settings)
def serialize(cls, objs: Iterable[dict], fobj: IO[bytes]):
with NamedTemporaryFile() as db_file:
with closing(sqlite_connect(db_file.name)) as connection:
with closing(connection.cursor()) as cursor:
cursor.execute('''
CREATE TABLE emails (
_uid TEXT,
read BOOLEAN,
sent_at TEXT,
"to" TEXT,
cc TEXT,
bcc TEXT,
"from" TEXT,
subject TEXT,
body TEXT,
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
)
''')
cursor.execute('''
CREATE TABLE contents (
content BLOB UNIQUE,
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
)
''')
cursor.execute('''
CREATE TABLE attachments (
filename TEXT,
content_id INTEGER,
cid TEXT,
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
FOREIGN KEY (content_id) REFERENCES contents(id)
)
''')
cursor.execute('''
CREATE TABLE emails_to_attachments (
email_id INTEGER,
attachment_id INTEGER,
FOREIGN KEY (email_id) REFERENCES emails(id),
FOREIGN KEY (attachment_id) REFERENCES attachments(id)
)
''')
connection.commit()
for obj in objs:
obj = byteify_attachments(obj)
cursor.execute(
'''
INSERT INTO emails(
_uid,
read,
sent_at,
"to",
cc,
bcc,
"from",
subject,
body
)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
''', (
obj.get('_uid'),
obj.get('read'),
obj.get('sent_at'),
cls._serialize_list(obj, 'to'),
cls._serialize_list(obj, 'cc'),
cls._serialize_list(obj, 'bcc'),
obj.get('from'),
obj.get('subject'),
obj.get('body'),
))
email_id = cursor.lastrowid
for attachment in obj.get('attachments', []):
try:
cursor.execute(
'''
INSERT INTO contents(
content
)
VALUES (?)
''', (attachment.get('content'), ))
except IntegrityError:
rows = cursor.execute(
'''
SELECT id
FROM contents
WHERE content = ?
''', (attachment.get('content'), ))
content_id = rows.fetchone()[0]
else:
content_id = cursor.lastrowid
cursor.execute(
'''
INSERT INTO attachments (
filename,
content_id,
cid
)
VALUES (?, ?, ?)
''', (
attachment.get('filename'),
content_id,
attachment.get('cid'),
))
attachment_id = cursor.lastrowid
cursor.execute(
'''
INSERT INTO emails_to_attachments(
email_id,
attachment_id
)
VALUES (?, ?)
''', (
email_id,
attachment_id,
))
connection.commit()
db_file.seek(0)
copyfileobj(db_file, fobj)
def __init__(self):
self._connection = sqlite_connect(':memory:')
self._connection.execute(CREATE_ITEMS_SQL)
database_manager = _DatabaseManager()
# TODO: Get MySQL working
# if _database['driver'].lower() == 'mysql':
# if mysql_connect is None:
# raise NotImplementedError('MySQL is not installed.')
# with open(STRUCTURE_PATH / 'mysql.json') as inputfile:
# statements = json_load(inputfile)
# _queue.put(lambda: mysql_connect(host=_database['host'], port=_database['port'], user=_database['user'], passwd=_database['password'], connect_timeout=_database['timeout']))
# def _execute(result):
# _thread._use_database = statements['use database'].format(_database['database'])
# database_manager.execute('toggle warnings', format_args=(0, ))
# database_manager.execute('create database', format_args=(_database['database'], ))
# database_manager.execute('use database', callback=_execute, format_args=(_database['database'], ))
# else:
with open(STRUCTURE_PATH / 'sqlite.json') as inputfile:
statements = json_load(inputfile)
_queue.put(lambda: sqlite_connect(DATA_PATH.joinpath('players.sqlite')))
for statement in ('create players', 'create races', 'create skills',
'create stats races', 'create stats items'):
database_manager.execute(statement)
# if _database['driver'].lower() == 'mysql':
# database_manager.execute('toggle warnings', format_args=(1, ))
SCHEMA = """
create table Diagram (
name text primary key,
content text not null
);
"""
def db_integrity_check(conn):
c = conn.cursor()
c.execute("select sql from sqlite_master where type='table' "
"and name in ('Diagram')")
return len(c.fetchall()) == 1
conn = sqlite_connect("diagrams.db", check_same_thread=False)
conn.row_factory = sqlite_row
if not db_integrity_check(conn):
print("Integrity check failed... Recreating database.")
conn.cursor().executescript(SCHEMA)
conn.commit()
@get("/diagrams")
def get_diagrams():
diagrams = {}
response.content_type = "application/json"
c = conn.cursor()
_Node(NodeType.CONNECT,
query=lambda: mysql_connect(host=_database['host'],
port=_database['port'],
user=_database['user'],
passwd=_database['password'],
connect_timeout=_database['timeout'],
charset='utf8mb4')))
database_manager.execute('toggle warnings', format_args=(0, ))
database_manager.execute('create database',
format_args=(_database['database'], ))
_queue.put(
_Node(NodeType.USE,
query=statements['use database'].format(_database['database'])))
else:
_queue.put(
_Node(NodeType.CONNECT,
query=lambda: sqlite_connect(DATA_PATH.joinpath('players.sqlite')
)))
for statement in ('create players', 'create races', 'create skills',
'create stats races', 'create stats items',
'create settings'):
database_manager.execute(statement)
if _driver == 'mysql':
database_manager.execute('toggle warnings', format_args=(1, ))
database_manager.execute('setting get', callback=_query_settings)
def sqlite_execute(command, params=None):
"""
Function to execute a sql statement on the mysql database. This function is
called by the database_execute function when the sqlite backend is set in
the configuration file
@param command the sql command to execute
@param params a list of tuple of values to substitute in command
@returns a list of dictionaries representing the sql result
"""
# NOTE mostly copypasta'd from mysql_execute, may be a better way
try:
parser = ConfigParser()
parser.read(SYNCINI_PATH)
try:
filename = parser.get('database', 'filename')
except (NoSectionError, NoOptionError) as error:
warning_no_database_section(error)
filename = DATABASE_PATH
init_db = not exists(expandvars(filename))
# make sure the folder in which the database is saved exists
if init_db and not exists(dirname(filename)):
makedirs(dirname(filename))
connection = sqlite_connect(filename)
connection.text_factory = Binary
cursor = connection.cursor()
logo_path = "http://104.45.14.234/media/logos/penguin.jpg"
if init_db:
for sql in (
'CREATE TABLE sites (site char(255), client_id'
' char(255), client_secret char(255), user char(255), token char(255));',
'CREATE TABLE keys (site char(255), user char(255), fingerprint char(40));',
'CREATE TABLE servers (label char(255), url char(255), picture blob);',
'INSERT INTO servers (label, url, picture) VALUES ("PF-EUMAIN", "https://104.45.14.234:5001/", "{}");'
.format(logo_path)):
if sql != "" and sql is not None:
cursor.execute(sql)
connection.commit()
if params:
cursor.execute(command, params)
else:
cursor.execute(command)
connection.commit()
final_result = []
for l in cursor.fetchall():
final_result.append(
list(
map(
lambda i: i.obj.decode('utf-8')
if hasattr(i, 'decode') else str(i), l)))
return final_result
except SQLiteError as sqlerror:
getLogger(__name__).exception(sqlerror)
raise DatabaseError("SQLite Error: %s" % (sqlerror.args[0]))
except (NoSectionError, NoOptionError) as error:
getLogger(__name__).exception(error)
raise DatabaseError("Please configure the database section"
" in the ini file")
except TypeError as error:
getLogger(__name__).exception(error)
raise DatabaseError("Please configure the 'filename' parameter"
" in the [database] section in the ini file")
finally:
try:
if connection:
connection.close()
except UnboundLocalError as error:
getLogger(__name__).exception(error)
