コード例 #1
0
    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"))
コード例 #2
0
ファイル: sql.py プロジェクト: glensc/griffith
    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()
コード例 #3
0
ファイル: sql.py プロジェクト: BackupTheBerlios/griffith-svn
    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()