class OOQuery(object): def __init__(self, table, foreign_key=None): self.fields = None self.table = Table(table) self._select = self.table.select() self.parser = Parser(self.table, foreign_key) @property def select_on(self): if self.parser.joins: return self.parser.joins[-1] else: return self.table def select(self, fields=None): if fields: self.fields = [getattr(self.table, arg) for arg in fields] self._select = self.table.select(*self.fields) return self def where(self, domain): where = self.parser.parse(domain) self._select = self.select_on.select(*self.fields) self._select.where = where return self._select
def test_update_subselect(self): t1 = Table('t1') t2 = Table('t2') query_list = t1.update([t1.c], [t2.select(t2.c, where=t2.i == t1.i)]) query_nolist = t1.update([t1.c], t2.select(t2.c, where=t2.i == t1.i)) for query in [query_list, query_nolist]: self.assertEqual(str(query), 'UPDATE "t1" SET "c" = (' 'SELECT "b"."c" FROM "t2" AS "b" WHERE ("b"."i" = "t1"."i"))') self.assertEqual(query.params, ())
def commit(cls, transaction): table = Table(cls._table) reset = cls._reset.setdefault(transaction, set()) if not reset: return database = transaction.database dbname = database.name if not _clear_timeout and transaction.database.has_channel(): with transaction.connection.cursor() as cursor: # The count computed as # 8000 (max notify size) / 64 (max name data len) for sub_reset in grouped_slice(reset, 125): cursor.execute( 'NOTIFY "%s", %%s' % cls._channel, (json.dumps(list(sub_reset), separators=(',', ':')), )) else: connection = database.get_connection(readonly=False, autocommit=True) try: with connection.cursor() as cursor: for name in reset: cursor.execute(*table.select(table.name, table.id, table.timestamp, where=table.name == name, limit=1)) if cursor.fetchone(): # It would be better to insert only cursor.execute(*table.update( [table.timestamp], [CurrentTimestamp()], where=table.name == name)) else: cursor.execute( *table.insert([table.timestamp, table.name], [[CurrentTimestamp(), name]])) cursor.execute(*table.select(Max(table.timestamp), where=table.name == name)) timestamp, = cursor.fetchone() cursor.execute( *table.select(_cast(Max(table.timestamp)), where=table.name == name)) timestamp, = cursor.fetchone() inst = cls._instances[name] inst._clear(dbname, timestamp) connection.commit() finally: database.put_connection(connection) cls._clean_last = datetime.now() reset.clear()
def commit(cls, transaction): table = Table(cls._table) reset = cls._reset.setdefault(transaction, set()) if not reset: return database = transaction.database dbname = database.name if not _clear_timeout and transaction.database.has_channel(): with transaction.connection.cursor() as cursor: # JCA: Fix for https://bugs.tryton.org/issue8781 resets = list(reset) for i in range(0, len(resets), 10): cursor.execute('NOTIFY "%s", %%s' % cls._channel, (json.dumps(resets[i:i + 10], separators=(',', ':')), )) else: connection = database.get_connection(readonly=False, autocommit=True) try: with connection.cursor() as cursor: for name in reset: cursor.execute(*table.select(table.name, table.id, table.timestamp, where=table.name == name, limit=1)) if cursor.fetchone(): # It would be better to insert only cursor.execute(*table.update( [table.timestamp], [CurrentTimestamp()], where=table.name == name)) else: cursor.execute( *table.insert([table.timestamp, table.name], [[CurrentTimestamp(), name]])) cursor.execute(*table.select(Max(table.timestamp), where=table.name == name)) timestamp, = cursor.fetchone() cursor.execute( *table.select(_cast(Max(table.timestamp)), where=table.name == name)) timestamp, = cursor.fetchone() inst = cls._instances[name] inst._clear(dbname, timestamp) connection.commit() finally: database.put_connection(connection) reset.clear()
def test_insert_subselect(self): t1 = Table("t1") t2 = Table("t2") subquery = t2.select(t2.c1, t2.c2) query = t1.insert([t1.c1, t1.c2], subquery) self.assertEqual(str(query), 'INSERT INTO "t1" ("c1", "c2") ' 'SELECT "a"."c1", "a"."c2" FROM "t2" AS "a"') self.assertEqual(query.params, ())
def _get_modules(cursor): ir_module = Table('ir_module') cursor.execute(*ir_module.select( ir_module.name, where=ir_module.state.in_( ['activated', 'to upgrade', 'to remove']))) return {m for m, in cursor}
def __register__(cls, module_name): cursor = Transaction().connection.cursor() model_data = Table('ir_model_data') # Migration from 3.0: new tax rates if module_name == 'account_fr': for old_id, new_id in ( ('tva_vente_19_6', 'tva_vente_taux_normal'), ('tva_vente_7', 'tva_vente_taux_intermediaire'), ('tva_vente_intracommunautaire_19_6', 'tva_vente_intracommunautaire_taux_normal'), ('tva_vente_intracommunautaire_7', 'tva_vente_intracommunautaire_taux_intermediaire'), ('tva_achat_19_6', 'tva_achat_taux_normal'), ('tva_achat_7', 'tva_achat_taux_intermediaire'), ('tva_achat_intracommunautaire_19_6', 'tva_achat_intracommunautaire_taux_normal'), ): cursor.execute( *model_data.select(model_data.id, where=(model_data.fs_id == new_id) & (model_data.module == module_name))) if cursor.fetchone(): continue cursor.execute( *model_data.update(columns=[model_data.fs_id], values=[new_id], where=(model_data.fs_id == old_id) & (model_data.module == module_name))) super(TaxTemplate, cls).__register__(module_name)
def document_db_table_columns(self): with connection().cursor() as cursor: documents = Table(self.document_db_table) select = documents.select(Column(documents, '*'), limit=1) query, args = tuple(select) cursor.execute(query, args) return [col[0] for col in cursor.description]
def test_0050_query_pagination(self): """ Test pagination via `nereid.contrib.QueryPagination.` """ self.setup_defaults() # Create a 100 addresses for id in xrange(0, 100): self.address_obj.create([{ 'party': self.guest_party, 'name': 'User %s' % id, }]) table = Table('party_address') select_query = table.select() pagination = QueryPagination(self.address_obj, select_query, table, page=1, per_page=10) self.assertEqual(pagination.count, 100) self.assertEqual(pagination.pages, 10) self.assertEqual(pagination.begin_count, 1) self.assertEqual(pagination.end_count, 10)
def sync(cls, transaction): database = transaction.database dbname = database.name if not _clear_timeout and database.has_channel(): pid = os.getpid() with cls._listener_lock[pid]: if (pid, dbname) not in cls._listener: cls._listener[pid, dbname] = listener = threading.Thread( target=cls._listen, args=(dbname,), daemon=True) listener.start() return if (datetime.now() - cls._clean_last).total_seconds() < _clear_timeout: return connection = database.get_connection(readonly=True, autocommit=True) try: with connection.cursor() as cursor: table = Table(cls._table) cursor.execute(*table.select( _cast(table.timestamp), table.name)) timestamps = {} for timestamp, name in cursor.fetchall(): timestamps[name] = timestamp finally: database.put_connection(connection) for name, timestamp in timestamps.items(): try: inst = cls._instances[name] except KeyError: continue inst_timestamp = inst._timestamp.get(dbname) if not inst_timestamp or timestamp > inst_timestamp: inst._clear(dbname, timestamp) cls._clean_last = datetime.now()
def test_in(self): in_ = In(self.table.c1, [self.table.c2, 1, None]) self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))') self.assertEqual(in_.params, (1, None)) t2 = Table('t2') in_ = In(self.table.c1, t2.select(t2.c2)) self.assertEqual(str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))') self.assertEqual(in_.params, ()) in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3)) self.assertEqual(str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" ' 'UNION SELECT "a"."c3" FROM "t2" AS "a"))') self.assertEqual(in_.params, ())
def _check_update_needed(db_name, options): # Get current main module version main_module = config.get('version', 'module', default='coog_core') current_main_module_version = get_module_info(main_module)['version'] # Do the upgrade anyway if -u is activated if options.update: return True, current_main_module_version # Get main module version which stocked in the database version_control_table = Table('upgrade_version_control') cursor = Transaction().connection.cursor() cursor.execute( *version_control_table.select(version_control_table.current_version)) db_main_module_version = cursor.fetchone()[0] if (options.check_update and current_main_module_version != db_main_module_version): logger.warning( f'Current code version ({current_main_module_version}) is ' 'different from the last update version ' '({db_main_module_version}), updating') return True, current_main_module_version logger.warning(f'Current code version ({current_main_module_version}) ' 'matches last update version, nothing to do') return False, current_main_module_version
def __register__(cls, module_name): TableHandler = backend.get('TableHandler') cursor = Transaction().cursor super(ProductMedia, cls).__register__(module_name) media_table = cls.__table__() if TableHandler.table_exist(cursor, 'product_product_imageset'): # Migrate data from ProductImageSet table to ProductMedia table imageset_table = Table('product_product_imageset') cursor.execute(*media_table.insert( columns=[ media_table.sequence, media_table.product, media_table.template, media_table.static_file, ], values=imageset_table.select( Literal(10), imageset_table.product, imageset_table.template, imageset_table.image ) )) TableHandler.drop_table( cursor, 'product.product.imageset', 'product_product_imageset', cascade=True )
def test_0050_query_pagination(self): """ Test pagination via `nereid.contrib.QueryPagination.` """ with Transaction().start(DB_NAME, USER, CONTEXT): self.setup_defaults() # Create a 100 addresses for id in xrange(0, 100): self.address_obj.create([{ 'party': self.guest_party, 'name': 'User %s' % id, }]) table = Table('party_address') select_query = table.select() pagination = QueryPagination( self.address_obj, select_query, table, page=1, per_page=10 ) self.assertEqual(pagination.count, 100) self.assertEqual(pagination.pages, 10) self.assertEqual(pagination.begin_count, 1) self.assertEqual(pagination.end_count, 10)
def __register__(cls, module_name): cursor = Transaction().connection.cursor() model_data = Table('ir_model_data') # Migration from 5.6: Rename main tax ids if module_name == 'account_de_skr03': for old_id, new_id in ( ('tax_ust_19', 'tax_ust_standard_rate'), ('tax_ust_7', 'tax_ust_reduced_rate'), ('tax_vst_19', 'tax_vst_standard_rate'), ('tax_vst_7', 'tax_vst_reduced_rate'), ('tax_eu_19_purchase', 'tax_purchase_eu_standard_rate'), ('tax_eu_7_purchase', 'tax_purchase_eu_reduced_rate'), ('tax_import_19', 'tax_import_standard_rate'), ('tax_import_7', 'tax_import_reduced_rate'), ): cursor.execute( *model_data.select(model_data.id, where=(model_data.fs_id == new_id) & (model_data.module == module_name))) if cursor.fetchone(): continue cursor.execute( *model_data.update(columns=[model_data.fs_id], values=[new_id], where=(model_data.fs_id == old_id) & (model_data.module == module_name))) super().__register__(module_name)
def __register__(cls, module_name): cursor = Transaction().cursor model_data = Table('ir_model_data') # Migration from 3.0: new tax rates if module_name == 'account_nl': for old_id, new_id in ( ('tva_vente_19_6', 'tva_vente_taux_normal'), ('tva_vente_7', 'tva_vente_taux_intermediaire'), ('tva_vente_intracommunautaire_19_6', 'tva_vente_intracommunautaire_taux_normal'), ('tva_vente_intracommunautaire_7', 'tva_vente_intracommunautaire_taux_intermediaire'), ('tva_achat_19_6', 'tva_achat_taux_normal'), ('tva_achat_7', 'tva_achat_taux_intermediaire'), ('tva_achat_intracommunautaire_19_6', 'tva_achat_intracommunautaire_taux_normal'), ): cursor.execute(*model_data.select(model_data.id, where=(model_data.fs_id == new_id) & (model_data.module == module_name))) if cursor.fetchone(): continue cursor.execute(*model_data.update( columns=[model_data.fs_id], values=[new_id], where=(model_data.fs_id == old_id) & (model_data.module == module_name))) super(TaxTemplate, cls).__register__(module_name)
def login_from_id(user_id): with Transaction().new_transaction(readonly=True) as t: cursor = t.connection.cursor() table = Table('res_user') cursor.execute(*table.select(table.login, where=table.id == user_id)) return cursor.fetchone()[0]
def __register__(cls, module_name): TableHandler = backend.get('TableHandler') cursor = Transaction().cursor super(ProductMedia, cls).__register__(module_name) media_table = cls.__table__() if TableHandler.table_exist(cursor, 'product_product_imageset'): # Migrate data from ProductImageSet table to ProductMedia table imageset_table = Table('product_product_imageset') cursor.execute(*media_table.insert( columns=[ media_table.sequence, media_table.product, media_table.template, media_table.static_file, ], values=imageset_table.select(Literal( 10), imageset_table.product, imageset_table.template, imageset_table.image))) TableHandler.drop_table(cursor, 'product.product.imageset', 'product_product_imageset', cascade=True)
def test_delete3(self): t1 = Table('t1') t2 = Table('t2') query = t1.delete(where=(t1.c.in_(t2.select(t2.c)))) self.assertEqual(str(query), 'DELETE FROM "t1" WHERE ("c" IN (' 'SELECT "a"."c" FROM "t2" AS "a"))') self.assertEqual(query.params, ())
def test_delete3(self): t1 = Table('t1') t2 = Table('t2') query = t1.delete(where=(t1.c.in_(t2.select(t2.c)))) self.assertEqual( str(query), 'DELETE FROM "t1" WHERE ("c" IN (' 'SELECT "a"."c" FROM "t2" AS "a"))') self.assertEqual(query.params, ())
def test_order_query(self): table = Table('t') column = Column(table, 'c') query = table.select(column) self.assertEqual(str(Asc(query)), '(SELECT "a"."c" FROM "t" AS "a") ASC') self.assertEqual(str(Desc(query)), '(SELECT "a"."c" FROM "t" AS "a") DESC')
def registry_sign(self): cases = Table('vw_case_for_advocates') select = cases.select(cases.registry_sign) select.where = cases.id_case == self.info['case_id'] query, args = tuple(select) with connection().cursor() as cursor: cursor.execute(query, args) return cursor.fetchone()[0]
def test_update_subselect(self): t1 = Table('t1') t2 = Table('t2') query = t1.update([t1.c], [t2.select(t2.c, where=t2.i == t1.i)]) self.assertEqual( str(query), 'UPDATE "t1" SET "c" = (' 'SELECT "b"."c" FROM "t2" AS "b" WHERE ("b"."i" = "t1"."i"))') self.assertEqual(query.params, ())
def test_insert_subselect(self): t1 = Table('t1') t2 = Table('t2') subquery = t2.select(t2.c1, t2.c2) query = t1.insert([t1.c1, t1.c2], subquery) self.assertEqual( str(query), 'INSERT INTO "t1" ("c1", "c2") ' 'SELECT "a"."c1", "a"."c2" FROM "t2" AS "a"') self.assertEqual(query.params, ())
def clean(dbname): with Transaction().new_transaction(_nocache=True) as transaction,\ transaction.connection.cursor() as cursor: table = Table('ir_cache') cursor.execute(*table.select(table.timestamp, table.name)) timestamps = {} for timestamp, name in cursor.fetchall(): timestamps[name] = timestamp for inst in BaseCache._cache_instance: inst.clean_inst(dbname, timestamps)
def test_lateral_select(self): t1 = Table('t1') t2 = Table('t2') lateral = Lateral(t2.select(where=t2.id == t1.t2)) query = From([t1, lateral]).select() self.assertEqual( str(query), 'SELECT * FROM "t1" AS "a", LATERAL ' '(SELECT * FROM "t2" AS "c" WHERE ("c"."id" = "a"."t2")) AS "b"') self.assertEqual(query.params, ())
def test_lateral_select(self): t1 = Table('t1') t2 = Table('t2') lateral = Lateral(t2.select(where=t2.id == t1.t2)) query = From([t1, lateral]).select() self.assertEqual(str(query), 'SELECT * FROM "t1" AS "a", LATERAL ' '(SELECT * FROM "t2" AS "c" WHERE ("c"."id" = "a"."t2")) AS "b"') self.assertEqual(query.params, ())
def test_schema_subselect(self): t1 = Table('t1', 'default') t2 = Table('t2', 'default') query = t1.update([t1.c1], t2.select(t2.c, where=t2.i == t1.i)) self.assertEqual( str(query), 'UPDATE "default"."t1" SET "c1" = (' 'SELECT "b"."c" FROM "default"."t2" AS "b" ' 'WHERE ("b"."i" = "default"."t1"."i"))') self.assertEqual(query.params, ())
def test_with(self): t1 = Table('t1') w = With(query=t1.select(t1.c1)) query = self.table.delete(with_=[w], where=self.table.c2.in_(w.select(w.c3))) self.assertEqual(str(query), 'WITH a AS (SELECT "b"."c1" FROM "t1" AS "b") ' 'DELETE FROM "t" WHERE ("c2" IN (SELECT "a"."c3" FROM a AS "a"))') self.assertEqual(query.params, ())
def test_join_subselect(self): t1 = Table('t1') t2 = Table('t2') select = t2.select() join = Join(t1, select) join.condition = t1.c == select.c with AliasManager(): self.assertEqual(str(join), '"t1" AS "a" INNER JOIN (SELECT * FROM "t2" AS "c") AS "b" ' 'ON ("a"."c" = "b"."c")') self.assertEqual(join.params, ())
def test_insert_returning_select(self): t1 = Table('t1') t2 = Table('t2') query = t1.insert([t1.c], [['foo']], returning=[ t2.select(t2.c, where=(t2.c1 == t1.c) & (t2.c2 == 'bar'))]) self.assertEqual(str(query), 'INSERT INTO "t1" ("c") VALUES (%s) ' 'RETURNING (SELECT "b"."c" FROM "t2" AS "b" ' 'WHERE (("b"."c1" = "t1"."c") AND ("b"."c2" = %s)))') self.assertEqual(query.params, ('foo', 'bar'))
def test_with(self): t1 = Table('t1') w = With(query=t1.select()) query = self.table.insert([self.table.c1], with_=[w], values=w.select()) self.assertEqual( str(query), 'WITH "a" AS (SELECT * FROM "t1" AS "b") ' 'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"') self.assertEqual(query.params, ())
def test_with(self): t1 = Table('t1') w = With(query=t1.select(t1.c1)) query = self.table.delete(with_=[w], where=self.table.c2.in_(w.select(w.c3))) self.assertEqual(str(query), 'WITH "a" AS (SELECT "b"."c1" FROM "t1" AS "b") ' 'DELETE FROM "t" WHERE ' '("c2" IN (SELECT "a"."c3" FROM "a" AS "a"))') self.assertEqual(query.params, ())
def get_parent_language(code): if code not in _parents: # Use SQL because it is used by load_module_graph cursor = Transaction().connection.cursor() lang = Table('ir_lang') cursor.execute(*lang.select(lang.code, lang.parent)) _parents.update(cursor.fetchall()) if _parents.get(code): return _parents[code] for sep in ['@', '_']: if sep in code: return code.rsplit(sep, 1)[0]
def test_in(self): in_ = In(self.table.c1, [self.table.c2, 1, None]) self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))') self.assertEqual(in_.params, (1, None)) t2 = Table('t2') in_ = In(self.table.c1, t2.select(t2.c2)) self.assertEqual(str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))') self.assertEqual(in_.params, ()) in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3)) self.assertEqual( str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" ' 'UNION SELECT "a"."c3" FROM "t2" AS "a"))') self.assertEqual(in_.params, ()) in_ = In(self.table.c1, array('l', range(10))) self.assertEqual(str(in_), '("c1" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s))') self.assertEqual(in_.params, tuple(range(10)))
def test_with(self): t1 = Table('t1') w = With(query=t1.select()) query = self.table.insert( [self.table.c1], with_=[w], values=w.select()) self.assertEqual(str(query), 'WITH "a" AS (SELECT * FROM "t1" AS "b") ' 'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"') self.assertEqual(query.params, ())
def test_with(self): t1 = Table('t1') w = With(query=t1.select(t1.c1)) query = self.table.update([self.table.c2], with_=[w], values=[w.select(w.c3, where=w.c4 == 2)]) self.assertEqual( str(query), 'WITH "b" AS (SELECT "c"."c1" FROM "t1" AS "c") ' 'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM "b" AS "b" ' 'WHERE ("b"."c4" = %s))') self.assertEqual(query.params, (2, ))
def test_with(self): t1 = Table('t1') w = With(query=t1.select(t1.c1)) query = self.table.update( [self.table.c2], with_=[w], values=[w.select(w.c3, where=w.c4 == 2)]) self.assertEqual(str(query), 'WITH b AS (SELECT "c"."c1" FROM "t1" AS "c") ' 'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM b AS "b" ' 'WHERE ("b"."c4" = %s))') self.assertEqual(query.params, (2,))
def test_in(self): for in_ in [In(self.table.c1, [self.table.c2, 1, Null]), ~NotIn(self.table.c1, [self.table.c2, 1, Null]), ~~In(self.table.c1, [self.table.c2, 1, Null])]: self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))') self.assertEqual(in_.params, (1, None)) t2 = Table('t2') in_ = In(self.table.c1, t2.select(t2.c2)) self.assertEqual(str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))') self.assertEqual(in_.params, ()) in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3)) self.assertEqual(str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" ' 'UNION SELECT "a"."c3" FROM "t2" AS "a"))') self.assertEqual(in_.params, ()) in_ = In(self.table.c1, array('l', range(10))) self.assertEqual(str(in_), '("c1" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s))') self.assertEqual(in_.params, tuple(range(10)))
def test_update_returning_select(self): t1 = Table('t1') t2 = Table('t2') query = t1.update([t1.c], ['foo'], returning=[ t2.select(t2.c, where=(t2.c1 == t1.c) & (t2.c2 == 'bar')) ]) self.assertEqual( str(query), 'UPDATE "t1" SET "c" = %s ' 'RETURNING (SELECT "b"."c" FROM "t2" AS "b" ' 'WHERE (("b"."c1" = "t1"."c") AND ("b"."c2" = %s)))') self.assertEqual(query.params, ('foo', 'bar'))
def clean(cls, dbname): with Transaction().new_transaction() as transaction,\ transaction.connection.cursor() as cursor: table = Table('ir_cache') cursor.execute(*table.select(table.timestamp, table.name)) timestamps = {} for timestamp, name in cursor.fetchall(): timestamps[name] = timestamp for inst in cls._cache_instance: if inst._name in timestamps: with inst._lock: if (not inst._timestamp or timestamps[inst._name] > inst._timestamp): inst._timestamp = timestamps[inst._name] inst._empty(dbname)
def clean(dbname): with Transaction().new_transaction(_nocache=True) as transaction,\ transaction.connection.cursor() as cursor: table = Table('ir_cache') cursor.execute(*table.select(table.timestamp, table.name)) timestamps = {} for timestamp, name in cursor.fetchall(): timestamps[name] = timestamp for inst in Cache._cache_instance: if inst._name in timestamps: with inst._lock: if (not inst._timestamp or timestamps[inst._name] > inst._timestamp): inst._timestamp = timestamps[inst._name] inst._cache[dbname] = LRUDict(inst.size_limit)
def _search_full_text_language(self, language): languages = self._search_full_text_languages[self.name] if language not in languages: lang = Table('ir_lang') connection = self.get_connection() try: cursor = connection.cursor() cursor.execute(*lang.select( Coalesce(lang.pg_text_search, 'simple'), where=lang.code == language, limit=1)) config_name, = cursor.fetchone() finally: self.put_connection(connection) languages[language] = config_name else: config_name = languages[language] return config_name
def _ref(self, ref): if '.' in ref: module, xml_id = ref.split('.') else: xml_id = ref module = self.module t = Table('ir_model_data') select = t.select(t.res_id) select.where = (t.module == module) & (t.name == xml_id) self.cursor.execute(*select) res = self.cursor.fetchone() if not res: raise KeyError('Reference: {}.{} not found'.format( module, xml_id )) return res[0]
def clean(cls, dbname): if (datetime.now() - cls._clean_last).total_seconds() < _clear_timeout: return with Transaction().new_transaction(_nocache=True) as transaction,\ transaction.connection.cursor() as cursor: table = Table('ir_cache') cursor.execute(*table.select(table.timestamp, table.name)) timestamps = {} for timestamp, name in cursor.fetchall(): timestamps[name] = timestamp for inst in cls._cache_instance: if inst._name in timestamps: with inst._lock: if (not inst._timestamp or timestamps[inst._name] > inst._timestamp): inst._timestamp = timestamps[inst._name] inst._cache[dbname] = LRUDict(inst.size_limit) cls._clean_last = datetime.now()
def resets(dbname): table = Table('ir_cache') with Transaction().new_transaction(_nocache=True) as transaction,\ transaction.connection.cursor() as cursor,\ Cache._resets_lock: Cache._resets.setdefault(dbname, set()) for name in Cache._resets[dbname]: cursor.execute(*table.select( table.name, where=table.name == name, limit=1)) if cursor.fetchone(): # It would be better to insert only cursor.execute( *table.update([table.timestamp], [CurrentTimestamp()], where=table.name == name)) else: cursor.execute(*table.insert([table.timestamp, table.name], [[CurrentTimestamp(), name]])) Cache._resets[dbname].clear()
def __register__(cls, module_name): TableHandler = backend.get('TableHandler') cursor = Transaction().connection.cursor() table = TableHandler(cls, module_name) sql_table = cls.__table__() journal_account = Table('account_journal_account') created_account = not table.column_exist('account') super(Journal, cls).__register__(module_name) # Migration from 4.8: new account field if created_account and table.table_exist('account_journal_account'): value = journal_account.select(journal_account.credit_account, where=((journal_account.journal == sql_table.journal) & (journal_account.credit_account == journal_account.debit_account))) # Don't use UPDATE FROM because SQLite does not support it. cursor.execute(*sql_table.update([sql_table.account], [value]))
def resets(cls, dbname): table = Table('ir_cache') with Transaction().new_transaction() as transaction,\ transaction.connection.cursor() as cursor,\ cls._resets_lock: cls._resets.setdefault(dbname, set()) for name in cls._resets[dbname]: cursor.execute(*table.select(table.name, where=table.name == name)) if cursor.fetchone(): # It would be better to insert only cursor.execute(*table.update([table.timestamp], [CurrentTimestamp()], where=table.name == name)) else: cursor.execute(*table.insert( [table.timestamp, table.name], [[CurrentTimestamp(), name]])) cls._resets[dbname].clear()
def clean_properties_from_4_2(cls): from sql import Null, Table, Cast from sql.operators import Like, Concat TableHandler = backend.get('TableHandler') if not TableHandler.table_exist('ir_property'): return property = Table('ir_property') cursor = Transaction().connection.cursor() cursor.execute(*property.select(property.res, where=property.res != Null)) res_model_names = list(set([x[0].split(',')[0] for x in cursor.fetchall()])) to_delete = {} for res_model_name in res_model_names: table_name = res_model_name.replace('.', '_') res_model = Table(table_name) query_table = property.join(res_model, 'LEFT OUTER', condition=( property.res == Concat(res_model_name + ',', Cast(res_model.id, 'VARCHAR')) )) cursor.execute(*query_table.select(property.id, where=Like(property.res, res_model_name + ',%') & (res_model.id == Null))) property_ids = [x[0] for x in cursor.fetchall()] if property_ids: to_delete[res_model_name] = property_ids if to_delete: cursor.execute( *property.delete(where=property.id.in_( sum([p for p in list(to_delete.values())], [])))) for res_model_name, property_ids in list(to_delete.items()): if property_ids: print('[%s] - %s Inconsistent record(s) removed' % ( res_model_name, len(property_ids))) else: print('Nothing to do - Exisiting property records are clean')
def __register__(cls, module_name): TableHandler = backend.get('TableHandler') cursor = Transaction().cursor sql_table = cls.__table__() super(InvoiceLine, cls).__register__(module_name) # Migration from 2.6: remove purchase_lines rel_table_name = 'purchase_line_invoice_lines_rel' if TableHandler.table_exist(cursor, rel_table_name): rel_table = Table(rel_table_name) cursor.execute(*rel_table.select( rel_table.purchase_line, rel_table.invoice_line)) for purchase_line, invoice_line in cursor.fetchall(): cursor.execute(*sql_table.update( columns=[sql_table.origin], values=['purchase.line,%s' % purchase_line], where=sql_table.id == invoice_line)) TableHandler.drop_table(cursor, 'purchase.line-account.invoice.line', rel_table_name)
def test(self): sqlite_master = Table('sqlite_master') select = sqlite_master.select(sqlite_master.name) select.where = sqlite_master.type == 'table' select.where &= sqlite_master.name.in_([ 'ir_model', 'ir_model_field', 'ir_ui_view', 'ir_ui_menu', 'res_user', 'res_group', 'ir_module', 'ir_module_dependency', 'ir_translation', 'ir_lang', ]) try: self.cursor.execute(*select) except Exception: return False return len(self.cursor.fetchall()) != 0
def run(options): Database = backend.get('Database') init = {} for db_name in options.database_names: init[db_name] = False with Transaction().start(db_name, 0): database = Database(db_name) database.connect() if options.update: if not database.test(): logger.info("init db") database.init() init[db_name] = True elif not database.test(): raise Exception("'%s' is not a Tryton database!" % db_name) for db_name in options.database_names: if options.update: with Transaction().start(db_name, 0) as transaction,\ transaction.connection.cursor() as cursor: database = Database(db_name) database.connect() if not database.test(): raise Exception("'%s' is not a Tryton database!" % db_name) lang = Table('ir_lang') cursor.execute(*lang.select(lang.code, where=lang.translatable == True)) lang = [x[0] for x in cursor.fetchall()] else: lang = None Pool(db_name).init(update=options.update, lang=lang) for db_name in options.database_names: if init[db_name]: # try to read password from environment variable # TRYTONPASSFILE, empty TRYTONPASSFILE ignored passpath = os.getenv('TRYTONPASSFILE') password = '' if passpath: try: with open(passpath) as passfile: password = passfile.readline()[:-1] except Exception, err: sys.stderr.write('Can not read password ' 'from "%s": "%s"\n' % (passpath, err)) if not password: while True: password = getpass('Admin Password for %s: ' % db_name) password2 = getpass('Admin Password Confirmation: ') if password != password2: sys.stderr.write('Admin Password Confirmation ' 'doesn\'t match Admin Password!\n') continue if not password: sys.stderr.write('Admin Password is required!\n') continue break with Transaction().start(db_name, 0) as transaction: pool = Pool() User = pool.get('res.user') admin, = User.search([('login', '=', 'admin')]) User.write([admin], { 'password': password, })
with description('The OOQuery object'): with description('when creating an ooquery object'): with it('should get the table to query'): q = OOQuery('table') expect(q.table).to(be_a(Table)) with it('should have a select method which returns table.attr'): q = OOQuery('table') sel = q.select(['field1', 'field2']) sel2 = q.table.select(q.table.field1, q.table.field2) expect(str(sel._select)).to(equal(str(sel2))) with it('should have a where method to pass the domain'): q = OOQuery('table') sql = q.select(['field1', 'field2']).where([('field3', '=', 4)]) t = Table('table') sel = t.select(t.field1, t.field2) sel.where = And((t.field3 == 4,)) expect(tuple(sql)).to(equal(tuple(sel))) with it('must support joins'): def dummy_fk(table): return { 'table_2': { 'constraint_name': 'fk_contraint_name', 'table_name': 'table', 'column_name': 'table_2', 'foreign_table_name': 'table2', 'foreign_column_name': 'id' } }
def run(self): "Run the server and never return" init = {} signal.signal(signal.SIGINT, lambda *a: self.stop()) signal.signal(signal.SIGTERM, lambda *a: self.stop()) if hasattr(signal, 'SIGQUIT'): signal.signal(signal.SIGQUIT, lambda *a: self.stop()) if hasattr(signal, 'SIGUSR1'): signal.signal(signal.SIGUSR1, lambda *a: self.restart()) if self.options.pidfile: with open(self.options.pidfile, 'w') as fd_pid: fd_pid.write("%d" % (os.getpid())) if not self.options.update: self.start_servers() for db_name in self.options.database_names: init[db_name] = False try: with Transaction().start(db_name, 0) as transaction: cursor = transaction.cursor if self.options.update: if not cursor.test(): self.logger.info("init db") backend.get('Database').init(cursor) init[db_name] = True cursor.commit() elif not cursor.test(): raise Exception("'%s' is not a Tryton database!" % db_name) except Exception: self.stop(False) raise for db_name in self.options.database_names: if self.options.update: with Transaction().start(db_name, 0) as transaction: cursor = transaction.cursor if not cursor.test(): raise Exception("'%s' is not a Tryton database!" % db_name) lang = Table('ir_lang') cursor.execute(*lang.select(lang.code, where=lang.translatable == True)) lang = [x[0] for x in cursor.fetchall()] else: lang = None Pool(db_name).init(update=self.options.update, lang=lang) for db_name in self.options.database_names: if init[db_name]: # try to read password from environment variable # TRYTONPASSFILE, empty TRYTONPASSFILE ignored passpath = os.getenv('TRYTONPASSFILE') password = '' if passpath: try: with open(passpath) as passfile: password = passfile.readline()[:-1] except Exception, err: sys.stderr.write('Can not read password ' 'from "%s": "%s"\n' % (passpath, err)) if not password: while True: password = getpass('Admin Password for %s: ' % db_name) password2 = getpass('Admin Password Confirmation: ') if password != password2: sys.stderr.write('Admin Password Confirmation ' 'doesn\'t match Admin Password!\n') continue if not password: sys.stderr.write('Admin Password is required!\n') continue break with Transaction().start(db_name, 0) as transaction: pool = Pool() User = pool.get('res.user') admin, = User.search([('login', '=', 'admin')]) User.write([admin], { 'password': password, }) transaction.cursor.commit()
def test_select_subselect(self): t1 = Table('t1') select = t1.select() self.assertEqual(str(select.select()), 'SELECT * FROM (SELECT * FROM "t1" AS "b") AS "a"') self.assertEqual(select.params, ())