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_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 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)
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_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
