def toolbar_icon_clicked(self, widget, movie):
#
# remove unused posters
#
session = self.db.Session()
delete_posters = delete(posters_table)
delete_posters = delete_posters.where(not_(exists([movies_table.c.movie_id], and_(posters_table.c.md5sum==movies_table.c.poster_md5)).correlate(posters_table)))
log.debug(delete_posters)
session.execute(delete_posters)
session.commit()
#
# compressing sqlite databases
#
if self.app.config.get('type', 'sqlite', section='database') == 'sqlite':
databasefilename = "%s.db" % os.path.join(self.app.locations['home'], self.app.config.get('name', section='database'))
pagesize = gutils.get_filesystem_pagesize(databasefilename)
# works since sqlite 3.5.8
# python 2.5 doesn't include 3.x but perhaps in future versions
# another way is the installation of pysqlite2 with 2.5.6/2.6.0 or higher
try:
from pysqlite2 import dbapi2 as sqlite3
con = sqlite3.connect(databasefilename)
try:
con.isolation_level = None
cur = con.cursor()
cur.execute('PRAGMA page_size=' + str(pagesize))
cur.execute('VACUUM;')
finally:
con.close()
except:
log.error('fallback to default driver')
self.app.db.engine.execute('PRAGMA page_size=' + str(pagesize))
self.app.db.engine.execute('VACUUM;')
gutils.info(_("Finished"))
def __init__(self, config, griffith_dir, fallback=True):
#mapper = Session.mapper
self.config = config
self.data_dir = griffith_dir
if config.get('type', None, section='database') is None:
config.set('type', 'sqlite', section='database')
if config.get('type', 'sqlite', section='database') != 'sqlite':
if config.get('host', None, section='database') is None:
config.set('host', '127.0.0.1', section='database')
if config.get('user', None, section='database') is None:
config.set('user', 'griffith', section='database')
if config.get('passwd', None, section='database') is None:
config.set('passwd', 'gRiFiTh', section='database')
if config.get('name', None, section='database') is None:
config.set('name', 'griffith', section='database')
if config.get('port', 0, section='database') == 0:
config.set('port',
GriffithSQL.DEFAULT_PORTS[config.get(
'type', section='database')],
section='database')
conn_params = config.to_dict(section='database')
conn_params.update({
'port': int(conn_params.get('port', 0)),
'engine_kwargs': {
'echo': False,
'convert_unicode': False
}
})
# connect to database --------------------------------------{{{
dbinitializingsql = None
if config.get('type', section='database') == 'sqlite':
sqlitefile = "%s.db" % os.path.join(griffith_dir,
conn_params['name'])
if not os.path.isfile(sqlitefile):
# for new created database this is an optimization step syncing the database page size
# to the filesystem page size
dbinitializingsql = 'PRAGMA page_size=' + str(
get_filesystem_pagesize(sqlitefile))
url = "sqlite:///%s" % sqlitefile
elif config.get('type', section='database') == 'postgres':
# sqlalchemy version check because postgres dialect is renamed in sqlalchemy>=0.6 from postgres to postgresql
from sqlalchemy import __version__ as sqlalchemyversion
if map(int, sqlalchemyversion[:3].split('.')) < [0, 6]:
url = "postgres"
else:
url = "postgresql"
url = url + "://%(user)s:%(passwd)s@%(host)s:%(port)d/%(name)s" % conn_params
elif config.get('type', section='database') == 'mysql':
conn_params['engine_kwargs']['convert_unicode'] = True
conn_params['engine_kwargs']['pool_recycle'] = int(
config.get('pool_recycle', 3600, section='database'))
url = "mysql://%(user)s:%(passwd)s@%(host)s:%(port)d/%(name)s?charset=utf8&use_unicode=0" % conn_params
elif config.get('type', section='database') == 'mssql':
# use_scope_identity=0 have to be set as workaround for a sqlalchemy bug
# but it is not guaranteed that the right identity value will be selected
# because the select @@identity statement selects the very last id which
# also can be a id from a trigger-insert or another user
# sqlalchemy uses a wrong syntax. It has to select the id within the insert
# statement: insert <table> (<columns>) values (<values>) select scope_identity()
# (one statement !) After preparing and executing there should be a fetch
# If it is executed as two separate statements the scope is lost after insert.
url = "mssql://%(user)s:%(passwd)s@%(host)s:%(port)d/%(name)s?use_scope_identity=0" % conn_params
else:
config.set('type', 'sqlite', section='database')
url = "sqlite:///%s.db" % os.path.join(griffith_dir,
conn_params['name'])
# try to establish a db connection
try:
engine = create_engine(url, **conn_params['engine_kwargs'])
conn = engine.connect()
if dbinitializingsql is not None:
engine.execute(dbinitializingsql)
except Exception, e: # InvalidRequestError, ImportError
log.info("MetaData: %s", e)
if not fallback:
raise e
config.set('type', 'sqlite', section='database')
warning("%s\n\n%s" %
(_('Cannot connect to database.\nFalling back to SQLite.'),
_('Please check debug output for more informations.')))
url = "sqlite:///%s.db" % os.path.join(griffith_dir,
conn_params['name'])
engine = create_engine(url)
conn = engine.connect()
def __init__(self, config, griffith_dir, fallback=True):
#mapper = Session.mapper
self.config = config
self.data_dir = griffith_dir
if config.get('type', None, section='database') is None:
config.set('type', 'sqlite', section='database')
if config.get('type', 'sqlite', section='database') != 'sqlite':
if config.get('host', None, section='database') is None:
config.set('host', '127.0.0.1', section='database')
if config.get('user', None, section='database') is None:
config.set('user', 'griffith', section='database')
if config.get('passwd', None, section='database') is None:
config.set('passwd', 'gRiFiTh', section='database')
if config.get('name', None, section='database') is None:
config.set('name', 'griffith', section='database')
if config.get('port', 0, section='database') == 0:
config.set('port', GriffithSQL.DEFAULT_PORTS[config.get('type', section='database')], section='database')
conn_params = config.to_dict(section='database')
conn_params.update({'port': int(conn_params.get('port', 0)),
'engine_kwargs': {'echo': False, 'convert_unicode': False}})
# connect to database --------------------------------------{{{
dbinitializingsql = None
if config.get('type', section='database') == 'sqlite':
sqlitefile = "%s.db" % os.path.join(griffith_dir, conn_params['name'])
if not os.path.isfile(sqlitefile):
# for new created database this is an optimization step syncing the database page size
# to the filesystem page size
dbinitializingsql = 'PRAGMA page_size=' + str(get_filesystem_pagesize(sqlitefile))
url = "sqlite:///%s" % sqlitefile
elif config.get('type', section='database') == 'postgres':
# sqlalchemy version check because postgres dialect is renamed in sqlalchemy>=0.6 from postgres to postgresql
from sqlalchemy import __version__ as sqlalchemyversion
if map(int, sqlalchemyversion[:3].split('.')) < [0, 6]:
url = "postgres"
else:
url = "postgresql"
url = url + "://%(user)s:%(passwd)s@%(host)s:%(port)d/%(name)s" % conn_params
elif config.get('type', section='database') == 'mysql':
conn_params['engine_kwargs']['convert_unicode'] = True
conn_params['engine_kwargs']['pool_recycle'] = int(config.get('pool_recycle', 3600, section='database'))
url = "mysql://%(user)s:%(passwd)s@%(host)s:%(port)d/%(name)s?charset=utf8&use_unicode=0" % conn_params
elif config.get('type', section='database') == 'mssql':
# use_scope_identity=0 have to be set as workaround for a sqlalchemy bug
# but it is not guaranteed that the right identity value will be selected
# because the select @@identity statement selects the very last id which
# also can be a id from a trigger-insert or another user
# sqlalchemy uses a wrong syntax. It has to select the id within the insert
# statement: insert <table> (<columns>) values (<values>) select scope_identity()
# (one statement !) After preparing and executing there should be a fetch
# If it is executed as two separate statements the scope is lost after insert.
url = "mssql://%(user)s:%(passwd)s@%(host)s:%(port)d/%(name)s?use_scope_identity=0" % conn_params
else:
config.set('type', 'sqlite', section='database')
url = "sqlite:///%s.db" % os.path.join(griffith_dir, conn_params['name'])
# try to establish a db connection
try:
engine = create_engine(url, **conn_params['engine_kwargs'])
conn = engine.connect()
if dbinitializingsql is not None:
engine.execute(dbinitializingsql)
except Exception, e: # InvalidRequestError, ImportError
log.info("MetaData: %s", e)
if not fallback:
raise e
config.set('type', 'sqlite', section='database')
warning("%s\n\n%s" % (_('Cannot connect to database.\nFalling back to SQLite.'), _('Please check debug output for more informations.')))
url = "sqlite:///%s.db" % os.path.join(griffith_dir, conn_params['name'])
engine = create_engine(url)
conn = engine.connect()