def remove_m2m(self): """Elimina a relação entre as duas tabelas e no caso de ter varias relações entre as mesmas tabelas, elimina apenas uma""" import objs from utils import get_many2many_name, get_model_fields #print('{var1}'.format(var1=str(self.kargs))) for f in get_model_fields(self): #print('{var1}'.format(var1=str(f))) if f[0] == self.kargs['parent_name']: field = f[1] try: parent_model = eval('objs.' + field.model_name + '()') except: return 'Verifique o model_name na definição do campo M2M!' table = get_many2many_name(self.__name__, parent_model.__name__) where = """WHERE {field1}='{value1}' AND {field2}='{value2}'""".format(field1=self.__name__, field2=parent_model.__name__, value1=self.kargs[self.__name__], value2=self.kargs[parent_model.__name__]) sql = "SELECT id from {table} {where}".format(table=table, where=where) #print(sql) with getcursor() as cursor: cursor.execute(sql) result = cursor.fetchone()['id'] sql = "DELETE FROM {table} WHERE id='{result}'".format(table=table, result=result) #print(sql) with getcursor() as cursor: cursor.execute(sql) return 'ok'
def get_m2m_ids(self, field_name, record): """Processa os many2many e devolve uma lista com os ids da tabela ascendente""" #print('estou no orm em get_m2m_ids') import objs from utils import get_many2many_name, get_condition, get_model_fields for f in get_model_fields(self): if f[0] == field_name: field = f[1] parent_model = eval('objs.' + field.model_name + '()') table = get_many2many_name(self.__name__, parent_model.__name__) where = get_condition(field.condition, record=record) #print('o meu where é', where) sql = "SELECT * FROM {table} WHERE {where}".format(table=table, where=where) #print(sql) result = [] try: with getcursor() as cursor: cursor.execute(sql) res = cursor.fetchall() for r in res: result.append(r[parent_model.__name__]) except Exception as err: if 'does not exist' in str(err): syncModel(self) else: etype = sys.exc_info()[0] try: ename = etype.__name__ except AttributeError: ename = etype return str(ename) + ":", sys.exc_info()[1] #print('o resultado do m2m é ', result) return result
def put_m2m(self): """Grava a relação entre duas tabelas na relação many2many """ import objs from utils import get_many2many_name, get_model_fields #print('orm put_m2m kargs {var1} {var2}'.format(var1=str(self.kargs), var2=str(dir(self)))) for f in get_model_fields(self): if hasattr(f[1],'model_name') and f[1].model_name.split('.')[0] == self.kargs['parent_name']: field = f[1] parent_model = eval('objs.' + field.model_name + '()') table = get_many2many_name(self.__name__, parent_model.__name__) #print('table to write to! {var1}'.format(var1=table)) fields = """{field1},{field2},id""".format(field1=self.__name__, field2=parent_model.__name__) values = """'{value1}', '{value2}','{new_id}'""".format(value1=self.kargs[self.__name__], value2=self.kargs[parent_model.__name__],new_id=get_identity(table=table, user=str(self.kargs['user']))) sql = "INSERT INTO {table} ({fields}) VALUES ({values})".format(table=table, fields=fields, values=values) #print(sql) with getcursor() as cursor: cursor.execute(sql) return 'ok'# aqui talvez um rowid qualquer relativo á relação ou algo assim... dificil no entanto não me parece grave dado que a maioria das vezes a relação terá apenas uma ocorrencia
def syncModel(model): import utils table = model.__name__ fields = utils.get_model_fields(model) model_fields = '' model_fields_list = [('active','boolean'), ('id','varchar(50)'), ('date_create','date'), ('user_create','date'), ('date_change','date'), ('user_change','date')] db_types = {'active':'boolean', 'id':'varchar(50)', 'date_create':'date', 'user_create':'date', 'date_change':'date', 'user_change':'date'} import objs constrains = [] for f in fields: #aqui depois colocar um if f not in [lista com nomes reservados] #print('{var1}'.format(var1=f[0])) if f[1].__class__.__name__ not in ['list_field', 'many2many','link','separator','label','newline','hrline']: field = eval(f[1].__class__.__name__ + '()') db_types[f[0]] = field.dbtype if f[1].__class__.__name__ == 'parent_field': #print('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) parent = eval('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) constrain = field.constrains%{'name':f[0],'parent':parent,'table':table} constrain_name = 'fk_%(table)s_%(parent)s'%{'parent':parent,'table':table} constrains.append((constrain, constrain_name))#aqui on caso das combo tambem deveria ter constrain se for parent=true dbtype = field.dbtype if f[1].__class__.__name__ == 'combo_field': if f[1].parent: dbtype = 'VARCHAR(50)' model_fields += f[0] + ' ' + dbtype + ',' model_fields_list.append((f[0], dbtype)) elif f[1].__class__.__name__ == 'many2many': table1 = table #print('table1 {var1}'.format(var1=table1)) #print('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) table2 = eval('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) #print('table2 {var1}'.format(var1=table2)) new_table = utils.get_many2many_name(table1, table2) #print('new_table {var1}'.format(var1=new_table)) sql = 'CREATE TABLE IF NOT EXISTS {table}(id VARCHAR(50) PRIMARY KEY, {field1} VARCHAR(50), {field2} VARCHAR(50));'.format(table=new_table, field1=table1, field2=table2)#aqui tambem teremos que introduzir as foreign keys with getcursor() as cursor: cursor.execute(sql) #print('1') # depois deveremos utilizar herança para estes campos base e outros que eventualmente o justifiquem, teremos que ter tambem estrat´egia de partiç~ao de tabelas model_fields += 'id VARCHAR(50) PRIMARY KEY, user_create VARCHAR(50), date_create DATE, user_change VARCHAR(50), date_change DATE, active BOOLEAN' sql = """CREATE TABLE IF NOT EXISTS %(table)s(%(cols)s);"""%{'table':table, 'cols':model_fields} #print(sql) with getcursor() as cursor: cursor.execute(sql) #vou utilizar para ir buscar os nomes das colunas sql = """SELECT * FROM {table}""".format(table=table) #print(sql) with getcursor() as cursor: cursor.execute(sql) col_name_list = [desc[0] for desc in cursor.description] #print('{var1} {var2}'.format(var1=str(col_name_list), var2=str(model_fields_list))) if col_name_list: for f in model_fields_list: field_name = f[0] #print('{var1}'.format(var1=field_name)) if field_name not in col_name_list: #print('{var1} {var2}'.format(var1=table, var2=str(db_types[f[0]]))) sql = """ALTER TABLE {table} ADD COLUMN {column} {field_type}""".format(table=table, column=field_name, field_type=db_types[f[0]]) #print(sql) with getcursor() as cursor: cursor.execute(sql) if f[1].__class__.__name__ == 'parent_field': sql = """ALTER TABLE {table} ADD CONSTRAINT fk_{name}_{parent} FOREIGN KEY ({name}) REFERENCES {parent}(id) ON DELETE RESTRICT;""".format(table=table, parent=f[1].parent, name=f[0]) #print(sql) with getcursor() as cursor: cursor.execute(sql) #print('contrains {var1}'.format(var1=str(constrains))) for constrain in constrains: constrain_name = constrain[1] constrain = constrain[0] sql = """ ALTER TABLE {table} DROP CONSTRAINT IF EXISTS {constrain_name}; ALTER TABLE {table} ADD CONSTRAINT {constrain};""".format(table=table, constrain=constrain, constrain_name=constrain_name) #print(sql) with getcursor() as cursor: cursor.execute(sql)
def syncModel(model): #print('estou no syncModel') import utils table = model.__name__ fields = utils.get_model_fields(model) model_fields = '' model_fields_list = [('active','boolean'), ('id','varchar(50)'), ('date_create','date'), ('user_create','date'), ('date_change','date'), ('user_change','date')] db_types = {'active':'boolean', 'id':'varchar(50)', 'date_create':'date', 'user_create':'date', 'date_change':'date', 'user_change':'date'} import objs constrains = [] for f in fields: #aqui depois colocar um if f not in [lista com nomes reservados] #print('{var1}'.format(var1=f[0])) if f[1].__class__.__name__ not in ['list_field', 'many2many']: field = eval(f[1].__class__.__name__ + '()') db_types[f[0]] = field.dbtype if f[1].__class__.__name__ == 'parent_field': #print('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) parent = eval('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) constrain = field.constrains%{'name':f[0],'parent':parent,'table':table} constrain_name = 'fk_%(table)s_%(parent)s'%{'parent':parent,'table':table} constrains.append((constrain, constrain_name))#aqui on caso das combo tambem deveria ter constrain se for parent=true dbtype = field.dbtype if f[1].__class__.__name__ == 'combo_field': if f[1].parent: dbtype = 'VARCHAR(50)' model_fields += f[0] + ' ' + dbtype + ',' model_fields_list.append((f[0], dbtype)) elif f[1].__class__.__name__ == 'many2many': table1 = table #print('table1 {var1}'.format(var1=table1)) #print('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) table2 = eval('objs.{model_name}().__name__'.format(model_name=f[1].model_name)) #print('table2 {var1}'.format(var1=table2)) new_table = utils.get_many2many_name(table1, table2) #print('new_table {var1}'.format(var1=new_table)) sql = 'CREATE TABLE IF NOT EXISTS {table}(id VARCHAR(50) PRIMARY KEY, {field1} VARCHAR(50), {field2} VARCHAR(50));'.format(table=new_table, field1=table1, field2=table2)#aqui tambem teremos que introduzir as foreign keys with getcursor() as cursor: cursor.execute(sql) #print('1') # depois deveremos utilizar herança para estes campos base e outros que eventualmente o justifiquem, teremos que ter tambem estrat´egia de partiç~ao de tabelas model_fields += 'id VARCHAR(50) PRIMARY KEY, user_create VARCHAR(50), date_create DATE, user_change VARCHAR(50), date_change DATE, active BOOLEAN' sql = """CREATE TABLE IF NOT EXISTS %(table)s(%(cols)s);"""%{'table':table, 'cols':model_fields} #print(sql) with getcursor() as cursor: cursor.execute(sql) #vou utilizar para ir buscar os nomes das colunas sql = """SELECT * FROM {table}""".format(table=table) #print(sql) with getcursor() as cursor: cursor.execute(sql) col_name_list = [desc[0] for desc in cursor.description] #print('{var1} {var2}'.format(var1=str(col_name_list), var2=str(model_fields_list))) if col_name_list: for f in model_fields_list: field_name = f[0] #print('{var1}'.format(var1=field_name)) if field_name not in col_name_list: #print('{var1} {var2}'.format(var1=table, var2=str(db_types[f[0]]))) sql = """ALTER TABLE {table} ADD COLUMN {column} {field_type}""".format(table=table, column=field_name, field_type=db_types[f[0]]) #print(sql) with getcursor() as cursor: cursor.execute(sql) if f[1].__class__.__name__ == 'parent_field': sql = """ALTER TABLE {table} ADD CONSTRAINT fk_{name}_{parent} FOREIGN KEY ({name}) REFERENCES {parent}(id) ON DELETE RESTRICT;""".format(table=table, parent=f[1].parent, name=f[0]) #print(sql) with getcursor() as cursor: cursor.execute(sql) #print('contrains {var1}'.format(var1=str(constrains))) for constrain in constrains: constrain_name = constrain[1] constrain = constrain[0] sql = """ ALTER TABLE {table} DROP CONSTRAINT IF EXISTS {constrain_name}; ALTER TABLE {table} ADD CONSTRAINT {constrain};""".format(table=table, constrain=constrain, constrain_name=constrain_name) #print(sql) with getcursor() as cursor: cursor.execute(sql)