def delete_table_sql(self, db_type): table_name = self.f_table_name.value db_module = db_modules.get_db_module(db_type) result = db_module.delete_table_sql(table_name) for i, s in enumerate(result): print result[i] return result
def process_request(parentPID, name, queue, db_type, db_database, db_user, db_password, db_host, db_port, db_encoding, mod_count): con = None counter = 0 db_module = db_modules.get_db_module(db_type) while True: if parentPID and hasattr(os, 'getppid') and os.getppid() != parentPID: break request = queue.get() if request: result_queue = request['queue'] command = request['command'] params = request['params'] result_set = request['result_set'] call_proc = request['call_proc'] commit = request['commit'] cur_mod_count = request['mod_count'] if cur_mod_count != mod_count or counter > 1000: if con: con.rollback() con.close() con = None mod_count = cur_mod_count counter = 0 con, result = execute_sql(db_module, db_database, db_user, db_password, db_host, db_port, db_encoding, con, command, params, result_set, call_proc, commit) counter += 1 result_queue.put(result)
def __init__(self, name, caption, template, edit_template, db_type, db_database = '', db_user = '', db_password = '', host='', port='', encoding='', con_pool_size=1): Task.__init__(self, None, None, None, None) self.items = [] self.ID = None self.item_name = name self.item_caption = caption self.template = template self.db_type = db_type self.db_database = db_database self.db_user = db_user self.db_password = db_password self.db_host = host self.db_port = port self.db_encoding = encoding self.db_module = db_modules.get_db_module(self.db_type) self.work_dir = os.getcwd() self.con_pool_size = 0 self.mod_count = 0 self.modules = [] self.processes = [] self._busy = 0 if sys.db_multiprocessing and con_pool_size: self.queue = multiprocessing.Queue() self.manager = multiprocessing.Manager() self.con_pool_size = con_pool_size self.create_connection_pool() else: self.connection = None
def process_request(name, queue, db_type, db_database, db_user, db_password, db_host, db_port, db_encoding, mod_count): con = None counter = 0 db_module = db_modules.get_db_module(db_type) while True: request = queue.get() if request: # print name, 'process id:', os.getpid() result_queue = request['queue'] command = request['command'] params = request['params'] result_set = request['result_set'] call_proc = request['call_proc'] commit = request['commit'] cur_mod_count = request['mod_count'] if cur_mod_count != mod_count or counter > 1000: if con: con.rollback() con.close() con = None mod_count = cur_mod_count counter = 0 if command == 'QUIT': if con: con.commit() con.close() result_queue.put('QUIT') break else: con, result = execute_sql(db_module, db_database, db_user, db_password, db_host, db_port, db_encoding, con, command, params, result_set, call_proc, commit) counter += 1 result_queue.put(result)
def create_index_sql(self, db_type, table_name, fields=None, new_fields=None): def new_field_name_by_id(id_value): for f in new_fields: if f['id'] == id_value: return f['field_name'] db_module = db_modules.get_db_module(db_type) index_name = self.f_index_name.value if self.f_foreign_index.value: key = self.f_foreign_field.display_text ref_id = self.task.sys_fields.field_by_id(self.f_foreign_field.value, 'f_object') ref = self.task.sys_items.field_by_id(ref_id, 'f_table_name') sql = db_module.create_foreign_index_sql(table_name, index_name, key, ref) else: index_desc = self.descending.value index_fields = self.f_fields.value desc = '' if index_desc: desc = 'DESC' fields = cPickle.loads(str(index_fields)) if new_fields: fields = [new_field_name_by_id(field[0]) for field in fields] else: fields = [self.task.sys_fields.field_by_id(field[0], 'f_field_name') for field in fields] if desc and db_type in (db_modules.SQLITE, db_modules.POSTGRESQL): fields = ['"%s" %s' % (field, desc) for field in fields] field_str = ', '.join(fields) else: field_str = '"' + '", "'.join(fields) + '"' sql = db_module.create_index_sql(index_name, table_name, field_str, desc) print db_module.set_case(sql) return db_module.set_case(sql)
def __init__(self, app, name, caption, js_filename, db_type, db_database = '', db_user = '', db_password = '', host='', port='', encoding='', con_pool_size=1, mp_pool=False, persist_con=False): AbstrTask.__init__(self, None, None, None, None) self.app = app self.consts = Consts() self.items = [] self.ID = None self.item_name = name self.item_caption = caption self.js_filename = js_filename self.db_type = db_type self.db_database = db_database self.db_user = db_user self.db_password = db_password self.db_host = host self.db_port = port self.db_encoding = encoding self.db_module = db_modules.get_db_module(self.db_type) self.on_request = None self.work_dir = os.getcwd() self.con_pool_size = 0 self.mod_count = 0 self.modules = [] self.conversion_lock = threading.Lock() self.con_pool_size = con_pool_size self.mp_pool = mp_pool self.persist_con = persist_con self.persist_con_busy = 0 if self.mp_pool: if self.persist_con: self.create_connection_pool(1) self.create_mp_connection_pool(self.con_pool_size) else: self.create_connection_pool(self.con_pool_size)
def delete_index_sql(self, db_type): db_module = db_modules.get_db_module(db_type) table_name = self.task.sys_items.field_by_id(self.owner_rec_id.value, 'f_table_name') index_name = self.f_index_name.value if self.f_foreign_index.value: sql = db_module.delete_foreign_index(table_name, index_name) else: sql = db_module.delete_index(table_name, index_name) print db_module.set_case(sql) return db_module.set_case(sql)
def delete_index_sql(self, db_type): db_module = db_modules.get_db_module(db_type) table_name = self.task.sys_items.field_by_id(self.owner_rec_id.value, 'f_table_name') index_name = self.f_index_name.value if self.f_foreign_index.value: sql = db_module.delete_foreign_index(table_name, index_name) else: sql = db_module.delete_index(table_name, index_name) print sql return sql
def copy_database_data(self, db_type, db_database=None, db_user=None, db_password=None, db_host=None, db_port=None, db_encoding=None): connection = None limit = 1024 db_module = db_modules.get_db_module(db_type) for group in self.items: for item in group.items: handlers = item.store_handlers() item.clear_handlers() try: if item.item_type != 'report': self.execute(self.db_module.set_case('DELETE FROM %s' % item.table_name)) item.open(expanded=False, open_empty=True) params = {'__fields': [], '__filters': [], '__expanded': False, '__loaded': 0, '__limit': 0} sql = item.get_record_count_query(params, db_module) connection, (result, error) = \ execute_sql(db_module, db_database, db_user, db_password, db_host, db_port, db_encoding, connection, sql, None, 'ALL') record_count = result[0][0] loaded = 0 max_id = 0 if record_count: while True: params['__loaded'] = loaded params['__limit'] = limit sql = item.get_select_statement(params, db_module) connection, (result, error) = \ execute_sql(db_module, db_database, db_user, db_password, db_host, db_port, db_encoding, connection, sql, None, 'ALL') if not error: for i, r in enumerate(result): item.append() j = 0 for field in item.fields: if not field.master_field: field.value = r[j] j += 1 if item.id.value > max_id: max_id = item.id.value item.post() item.apply() else: raise Exception, error records = len(result) loaded += records print 'coping table %s: %d%%' % (item.item_name, int(loaded * 100 / record_count)) if records == 0 or records < limit: break if self.db_module.restart_sequence_sql: sql = self.db_module.restart_sequence_sql(item.table_name, max_id + 1) self.execute(sql) finally: item.load_handlers(handlers)
def create_index_sql(self, db_type, table_name, fields=None, new_fields=None): def new_field_name_by_id(id_value): for f in new_fields: if f['id'] == id_value: return f['field_name'] db_module = db_modules.get_db_module(db_type) index_name = self.f_index_name.value if self.f_foreign_index.value: fields = self.task.sys_fields.copy() fields.set_where(id=self.f_foreign_field.value) fields.open() key = fields.f_field_name.value ref_id = fields.f_object.value items = self.task.sys_items.copy() items.set_where(id=ref_id) items.open() ref = items.f_table_name.value primary_key = items.f_primary_key.value fields.set_where(id=primary_key) fields.open() primary_key = fields.f_field_name.value sql = db_module.create_foreign_index_sql(table_name, index_name, key, ref, primary_key) else: index_fields = self.f_fields.value desc = '' if self.descending.value: desc = 'DESC' unique = '' if self.f_unique_index.value: unique = 'UNIQUE' fields = common.load_index_fields(index_fields) if new_fields: fields = [new_field_name_by_id(field[0]) for field in fields] else: fields = [ self.task.sys_fields.field_by_id(field[0], 'f_field_name') for field in fields ] if desc and db_type in (db_modules.SQLITE, db_modules.POSTGRESQL): fields = ['"%s" %s' % (field, desc) for field in fields] field_str = ', '.join(fields) else: field_str = '"' + '", "'.join(fields) + '"' sql = db_module.create_index_sql(index_name, table_name, unique, field_str, desc) print sql return sql
def change_table_sql(self, db_type, old_fields, new_fields): def recreate(comp): for key, (old_field, new_field) in comp.items(): if old_field and new_field: if old_field['field_name'] != new_field['field_name']: return True elif old_field['default_value'] != new_field[ 'default_value']: return True elif old_field and not new_field: return True db_module = db_modules.get_db_module(db_type) table_name = self.f_table_name.value result = [] comp = {} for field in old_fields: comp[field['id']] = [field, None] for field in new_fields: if comp.get(field['id']): comp[field['id']][1] = field else: if field['id']: comp[field['id']] = [None, field] else: comp[field['field_name']] = [None, field] if db_type == db_modules.SQLITE and recreate(comp): result += self.recreate_table_sql(db_type, old_fields, new_fields) else: for key, (old_field, new_field) in comp.items(): if old_field and not new_field and db_type != db_modules.SQLITE: result.append( db_module.del_field_sql(table_name, old_field)) for key, (old_field, new_field) in comp.items(): if old_field and new_field and db_type != db_modules.SQLITE: if (old_field['field_name'] != new_field['field_name']) or \ (db_module.FIELD_TYPES[old_field['data_type']] != db_module.FIELD_TYPES[new_field['data_type']]) or \ (old_field['default_value'] != new_field['default_value']) or \ (old_field['size'] != new_field['size']): sql = db_module.change_field_sql( table_name, old_field, new_field) if type(sql) in (list, tuple): result += sql else: result.append() for key, (old_field, new_field) in comp.items(): if not old_field and new_field: result.append( db_module.add_field_sql(table_name, new_field)) for i, s in enumerate(result): print result[i] return result
def create_index_sql(self, db_type, table_name, fields=None, new_fields=None, foreign_key_dict=None): def new_field_name_by_id(id_value): for f in new_fields: if f['id'] == id_value: return f['field_name'] db_module = db_modules.get_db_module(db_type) index_name = self.f_index_name.value if self.f_foreign_index.value: if foreign_key_dict: key = foreign_key_dict['key'] ref = foreign_key_dict['ref'] primary_key = foreign_key_dict['primary_key'] else: fields = self.task.sys_fields.copy() fields.set_where(id=self.f_foreign_field.value) fields.open() key = fields.f_field_name.value ref_id = fields.f_object.value items = self.task.sys_items.copy() items.set_where(id=ref_id) items.open() ref = items.f_table_name.value primary_key = items.f_primary_key.value fields.set_where(id=primary_key) fields.open() primary_key = fields.f_field_name.value sql = db_module.create_foreign_index_sql(table_name, index_name, key, ref, primary_key) else: index_fields = self.f_fields.value desc = '' if self.descending.value: desc = 'DESC' unique = '' if self.f_unique_index.value: unique = 'UNIQUE' fields = common.load_index_fields(index_fields) if new_fields: fields = [new_field_name_by_id(field[0]) for field in fields] else: fields = [self.task.sys_fields.field_by_id(field[0], 'f_field_name') for field in fields] if desc and db_type in (db_modules.SQLITE, db_modules.POSTGRESQL): fields = ['"%s" %s' % (field, desc) for field in fields] field_str = ', '.join(fields) else: field_str = '"' + '", "'.join(fields) + '"' sql = db_module.create_index_sql(index_name, table_name, unique, field_str, desc) print sql return sql
def change_table_sql(self, db_type, old_fields, new_fields): def recreate(comp): for key, (old_field, new_field) in comp.items(): if old_field and new_field: if old_field['field_name'] != new_field['field_name']: return True elif old_field['default_value'] != new_field['default_value']: return True elif old_field and not new_field: return True db_module = db_modules.get_db_module(db_type) table_name = self.f_table_name.value result = [] comp = {} for field in old_fields: comp[field['id']] = [field, None] for field in new_fields: if comp.get(field['id']): comp[field['id']][1] = field else: if field['id']: comp[field['id']] = [None, field] else: comp[field['field_name']] = [None, field] if db_type == db_modules.SQLITE and recreate(comp): result += self.recreate_table_sql(db_type, old_fields, new_fields) else: for key, (old_field, new_field) in comp.items(): if old_field and not new_field and db_type != db_modules.SQLITE: result.append(db_module.del_field_sql(table_name, old_field)) for key, (old_field, new_field) in comp.items(): if old_field and new_field and db_type != db_modules.SQLITE: if (old_field['field_name'] != new_field['field_name']) or \ (db_module.FIELD_TYPES[old_field['data_type']] != db_module.FIELD_TYPES[new_field['data_type']]) or \ (old_field['default_value'] != new_field['default_value']) or \ (old_field['size'] != new_field['size']): sql = db_module.change_field_sql(table_name, old_field, new_field) if type(sql) in (list, tuple): result += sql else: result.append() for key, (old_field, new_field) in comp.items(): if not old_field and new_field: result.append(db_module.add_field_sql(table_name, new_field)) for i, s in enumerate(result): print result[i] return result
def create_table_sql(self, db_type, table_name, fields=None, foreign_fields=None): if not fields: fields = [] for field in self.fields: if not (field.calculated or field.master_field): dic = {} dic['id'] = field.ID dic['field_name'] = field.field_name dic['data_type'] = field.data_type dic['size'] = field.field_size fields.append(dic) result = [] db_module = db_modules.get_db_module(db_type) result = db_module.create_table_sql(table_name, fields, foreign_fields=None) for i, s in enumerate(result): result[i] = db_module.set_case(s) print result[i] return result
def create_table_sql(self, db_type, table_name, fields=None, foreign_fields=None): if not fields: fields = [] for field in self.fields: if not (field.calculated or field.master_field): dic = {} dic['id'] = field.ID dic['field_name'] = field.field_name dic['data_type'] = field.data_type dic['size'] = field.field_size dic['default_value'] = field.f_default_value.value dic['primary_key'] = field.id.value == item.f_primary_key.value fields.append(dic) result = [] db_module = db_modules.get_db_module(db_type) result = db_module.create_table_sql(table_name, fields, foreign_fields) for i, s in enumerate(result): print result[i] return result
def create_index_sql(self, db_type, table_name, fields=None, new_fields=None): def new_field_name_by_id(id_value): for f in new_fields: if f['id'] == id_value: return f['field_name'] db_module = db_modules.get_db_module(db_type) index_name = self.f_index_name.value if self.f_foreign_index.value: key = self.f_foreign_field.display_text ref_id = self.task.sys_fields.field_by_id( self.f_foreign_field.value, 'f_object') ref = self.task.sys_items.field_by_id(ref_id, 'f_table_name') sql = db_module.create_foreign_index_sql(table_name, index_name, key, ref) else: index_desc = self.descending.value index_fields = self.f_fields.value desc = '' if index_desc: desc = 'DESC' fields = common.load_index_fields(index_fields) if new_fields: fields = [new_field_name_by_id(field[0]) for field in fields] else: fields = [ self.task.sys_fields.field_by_id(field[0], 'f_field_name') for field in fields ] if desc and db_type in (db_modules.SQLITE, db_modules.POSTGRESQL): fields = ['"%s" %s' % (field, desc) for field in fields] field_str = ', '.join(fields) else: field_str = '"' + '", "'.join(fields) + '"' sql = db_module.create_index_sql(index_name, table_name, field_str, desc) print db_module.set_case(sql) return db_module.set_case(sql)