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