async def delete(self):
""" Delete all the tables and indexes """
async with self._pool.acquire() as conn:
await conn.execute(str(DropIndex(self._i_peers_phone_number)))
await conn.execute(str(DropIndex(self._i_peers_username)))
await conn.execute(str(DropTable(self._t_session)))
await conn.execute(str(DropTable(self._t_peers)))
def create_book(sqlite_file=None,
uri_conn=None,
currency="EUR",
overwrite=False,
keep_foreign_keys=False,
db_type=None,
db_user=None,
db_password=None,
db_name=None,
db_host=None,
db_port=None,
version_format="2.6",
**kwargs):
"""Create a new empty GnuCash book. If both sqlite_file and uri_conn are None, then an "in memory" sqlite book is created.
:param str sqlite_file: a path to an sqlite3 file (only used if uri_conn is None)
:param str uri_conn: a sqlalchemy connection string
:param str currency: the ISO symbol of the default currency of the book
:param bool overwrite: True if book should be deleted and recreated if it exists already
:param bool keep_foreign_keys: True if the foreign keys should be kept (may not work at all with GnuCash)
:param str db_type: type of database in ["postgres","mysql"]
:param str db_user: username of database
:param str db_password: password for the use of database
:param str db_name: name of database
:param str db_host: host of database
:param str db_port: port of database
:param str version_format: the format (2.6 or 2.7) for the schema tables to generate
:return: the document as a gnucash session
:rtype: :class:`GncSession`
:raises GnucashException: if document already exists and overwrite is False
"""
from sqlalchemy_utils.functions import database_exists, create_database, drop_database
uri_conn = build_uri(sqlite_file, uri_conn, db_type, db_user, db_password,
db_name, db_host, db_port)
# create database (if DB is not a sqlite in memory)
if uri_conn != "sqlite:///:memory:":
if database_exists(uri_conn):
if overwrite:
drop_database(uri_conn)
else:
raise GnucashException(
"'{}' db already exists".format(uri_conn))
create_database(uri_conn)
engine = create_piecash_engine(uri_conn, **kwargs)
# drop constraints if we de not want to keep them (keep_foreign_keys=False), the default
if not keep_foreign_keys:
for n, tbl in DeclarativeBase.metadata.tables.items():
# drop index constraints
for idx in tbl.indexes:
event.listen(tbl, "after_create", DropIndex(idx), once=True)
# drop FK constraints
for cstr in tbl.constraints:
if isinstance(cstr, PrimaryKeyConstraint):
continue
else:
event.listen(tbl,
"before_drop",
DropConstraint(cstr),
once=True)
#
# create all (tables, fk, ...)
DeclarativeBase.metadata.create_all(engine)
s = Session(bind=engine)
# create all rows in version table
assert version_format in version_supported, "The 'version_format'={} is not supported. " \
"Choose one of {}".format(version_format,
list(version_supported.keys()))
for table_name, table_version in version_supported[version_format].items():
s.add(Version(table_name=table_name, table_version=table_version))
# create book and merge with session
b = Book()
s.add(b)
adapt_session(s, book=b, readonly=False)
# create commodities and initial accounts
from .account import Account
b.root_account = Account(name="Root Account",
type="ROOT",
commodity=None,
book=b)
b.root_template = Account(name="Template Root",
type="ROOT",
commodity=None,
book=b)
b["default-currency"] = b.currencies(mnemonic=currency)
b.save()
return b
async def visit_index(self, index):
await self.connection.status(DropIndex(index))
def downgrade():
op.drop_table('blockchain')
op.execute(DropSequence('object_id_seq'))
op.execute(DropSequence('blockchain_index_seq'))
op.execute(DropIndex('idx_blockchain'))
def create_book(sqlite_file=None,
uri_conn=None,
currency="EUR",
overwrite=False,
keep_foreign_keys=False,
db_type=None,
db_user=None,
db_password=None,
db_name=None,
db_host=None,
db_port=None,
version_format="2.6",
**kwargs):
"""Create a new empty GnuCash book. If both sqlite_file and uri_conn are None, then an "in memory" sqlite book is created.
:param str sqlite_file: a path to an sqlite3 file (only used if uri_conn is None)
:param str uri_conn: a sqlalchemy connection string
:param str currency: the ISO symbol of the default currency of the book
:param bool overwrite: True if book should be deleted and recreated if it exists already
:param bool keep_foreign_keys: True if the foreign keys should be kept (may not work at all with GnuCash)
:param str db_type: type of database in ["postgres","mysql"]
:param str db_user: username of database
:param str db_password: password for the use of database
:param str db_name: name of database
:param str db_host: host of database
:param str db_port: port of database
:param str version_format: the format (2.6 or 2.7) for the schema tables to generate
:return: the document as a gnucash session
:rtype: :class:`GncSession`
:raises GnucashException: if document already exists and overwrite is False
"""
from sqlalchemy_utils.functions import database_exists, create_database, drop_database
uri_conn = build_uri(sqlite_file, uri_conn, db_type, db_user, db_password, db_name, db_host, db_port)
_db_created = False
# create database (if DB is not a sqlite in memory)
if uri_conn != "sqlite:///:memory:":
if database_exists(uri_conn):
if overwrite:
drop_database(uri_conn)
else:
raise GnucashException("'{}' db already exists".format(uri_conn))
create_database(uri_conn)
_db_created = True
engine = create_piecash_engine(uri_conn, **kwargs)
# Do any special setup we need to do the first time the database is created
if _db_created:
# For postgresql, GnuCash needs the standard_conforming_strings database variable set to 'on' in order to
# find the gnclock table as expected. (Probably would break some other stuff too.)
match = re.match('postgres://([^:]+):([^@]+)@([^/]+)/(.+)', uri_conn)
if match:
# TODO: figure out how to use sqlalchemy.sql.expression.literal to make this slightly SQL injection safer.
# t = text('ALTER DATABASE :db_name SET standard_conforming_string TO on')
# engine.execute(t, db_name="blah")
# produces: ALTER DATABASE 'blah' SET standard_conforming_string TO on
# we need: ALTER DATABASE blah SET standard_conforming_string TO on
t = text('ALTER DATABASE {} SET standard_conforming_strings TO on'.format(match.group(4)))
engine.execute(t)
# drop constraints if we de not want to keep them (keep_foreign_keys=False), the default
if not keep_foreign_keys:
for n, tbl in DeclarativeBase.metadata.tables.items():
# drop index constraints
for idx in tbl.indexes:
event.listen(tbl,
"after_create",
DropIndex(idx),
once=True)
# drop FK constraints
for cstr in tbl.constraints:
if isinstance(cstr, PrimaryKeyConstraint):
continue
else:
event.listen(tbl,
"before_drop",
DropConstraint(cstr),
once=True)
#
# create all (tables, fk, ...)
DeclarativeBase.metadata.create_all(engine)
s = Session(bind=engine)
# create all rows in version table
assert version_format in version_supported, "The 'version_format'={} is not supported. " \
"Choose one of {}".format(version_format,
list(version_supported.keys()))
for table_name, table_version in version_supported[version_format].items():
s.add(Version(table_name=table_name, table_version=table_version))
# create book and merge with session
b = Book()
s.add(b)
adapt_session(s, book=b, readonly=False)
# create commodities and initial accounts
from .account import Account
b.root_account = Account(name="Root Account", type="ROOT", commodity=None, book=b)
b.root_template = Account(name="Template Root", type="ROOT", commodity=None, book=b)
b["default-currency"] = b.currencies(mnemonic=currency)
b.save()
s.create_lock()
b._acquire_lock = True
return b
def create_book(
sqlite_file=None,
uri_conn=None,
currency="EUR",
overwrite=False,
keep_foreign_keys=False,
db_type=None,
db_user=None,
db_password=None,
db_name=None,
db_host=None,
db_port=None,
check_same_thread=True,
pg_template="template0",
**kwargs
):
"""Create a new empty GnuCash book. If both sqlite_file and uri_conn are None, then an "in memory" sqlite book is created.
:param str sqlite_file: a path to an sqlite3 file (only used if uri_conn is None)
:param str uri_conn: a sqlalchemy connection string
:param str currency: the ISO symbol of the default currency of the book
:param bool overwrite: True if book should be deleted and recreated if it exists already
:param bool keep_foreign_keys: True if the foreign keys should be kept (may not work at all with GnuCash)
:param str db_type: type of database in ["postgres","mysql"]
:param str db_user: username of database
:param str db_password: password for the use of database
:param str db_name: name of database
:param str db_host: host of database
:param int db_port: port of database
:param bool check_same_thread: sqlite flag that restricts connection use to the thread that created (see False for use in ipython/flask/... but read first https://docs.python.org/3/library/sqlite3.html)
:param str pg_template: the postgres template to use when creating the database. One of template1 or template0 (default template0). Irrelevant for other databases than postgres.
:return: the document as a gnucash session
:rtype: :class:`GncSession`
:raises GnucashException: if document already exists and overwrite is False
"""
from sqlalchemy_utils.functions import (
database_exists,
create_database,
drop_database,
)
VERSION_FORMAT = "3.0"
uri_conn = build_uri(
sqlite_file,
uri_conn,
db_type,
db_user,
db_password,
db_name,
db_host,
db_port,
check_same_thread,
)
# create database (if DB is not a sqlite in memory)
if uri_conn != "sqlite:///:memory:":
if database_exists(uri_conn):
if overwrite:
drop_database(uri_conn)
else:
raise GnucashException("'{}' db already exists".format(uri_conn))
create_database(uri_conn, template=pg_template)
engine = create_piecash_engine(uri_conn, **kwargs)
# drop constraints if we de not want to keep them (keep_foreign_keys=False), the default
if not keep_foreign_keys:
for n, tbl in DeclarativeBase.metadata.tables.items():
# drop index constraints
for idx in tbl.indexes:
if idx.name.startswith("ix_") or idx.name.startswith("_"):
event.listen(tbl, "after_create", DropIndex(idx), once=True)
# drop FK constraints
for cstr in tbl.constraints:
if isinstance(cstr, PrimaryKeyConstraint):
continue
else:
event.listen(tbl, "before_drop", DropConstraint(cstr), once=True)
#
# create all (tables, fk, ...)
DeclarativeBase.metadata.create_all(engine)
s = Session(bind=engine)
# create all rows in version table
assert (
VERSION_FORMAT in version_supported
), "The 'version_format'={} is not supported. " "Choose one of {}".format(
VERSION_FORMAT, list(version_supported.keys())
)
for table_name, table_version in version_supported[VERSION_FORMAT].items():
s.add(Version(table_name=table_name, table_version=table_version))
# create book and merge with session
b = Book()
s.add(b)
adapt_session(s, book=b, readonly=False)
# create commodities and initial accounts
from .account import Account
b.root_account = Account(
name="Root Account",
type="ROOT",
commodity=factories.create_currency_from_ISO(currency),
book=b,
)
b.root_template = Account(name="Template Root", type="ROOT", commodity=None, book=b)
b.save()
return b