def delete(self, values):
query = QueryBuilder('user_has_roles')\
.delete()\
.where('user_id')\
.where('role_id')\
.sql()
db.execute_sql(query, (values['user_id'], values['role_id']))
def delete(self, pk):
query = QueryBuilder(self.model.table)\
.delete()\
.where(self.model.primary_key)\
.sql()
db.execute_sql(query, (pk,))
def findByUsername(self, username):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('username')\
.limit(1)\
.sql()
cursor = db.execute_sql(query, (username,))
return self.as_object(cursor)
def update(self, pk, values):
if isinstance(values, self.model):
values = dict(values)
values['updated_at'] = datetime.datetime.now()
query = QueryBuilder(self.model.table, self.model.columns[1:])\
.update()\
.where(self.model.primary_key)\
.sql()
db.execute_sql(
query,
(*[values.get(c, None) for c in self.model.columns[1:]], pk)
)
return self.find(pk, True)
def allByPerson(self, person_id):
query = QueryBuilder(models.Person.table, models.Person.columns)\
.join(self.model.table)\
.on('person_id')\
.on('associated_id', '=', 'id')\
.sql()
cursor = db.execute_sql(query, (person_id,))
return self.as_iterator(cursor, models.Person)
def allByFederativeUnit(self, federative_unit_id, order_by=[]):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('federative_unit_id')
for o in order_by:
query = query.order_by(*o)
cursor = db.execute_sql(query.sql(), (federative_unit_id,))
return self.as_iterator(cursor)
def allByUser(self, user_id):
query = QueryBuilder(self.model.table, self.model.columns)\
.join('user_has_roles')\
.on('role_id', '=', 'id')\
.on('user_id')\
.sql()
cursor = db.execute_sql(query, (user_id,))
return self.as_iterator(cursor)
def findByName(self, name, with_trash=False):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('name')\
.limit(1)
query = query.sql()
cursor = db.execute_sql(query, (name,))
return self.as_object(cursor)
def create(self, values):
query = QueryBuilder('user_has_roles', ['user_id', 'role_id'])\
.insert()\
.returning(['user_id', 'role_id'])\
.sql()
cursor = db.execute_sql(query, (values['user_id'], values['role_id']))
return models.UserHasRole(**dict(
zip(models.UserHasRole.columns, cursor.fetchone())
))
def findByAssociated(self, person_id):
query = QueryBuilder(models.Person.table, models.Person.columns)\
.join(self.model.table)\
.on('person_id', '=', 'id')\
.on('associated_id')\
.limit(1)\
.sql()
cursor = db.execute_sql(query, (person_id,))
return self.as_object(cursor, models.Person)
def all(self, with_trash=False, order_by=[('id',)]):
query = QueryBuilder(self.model.table, self.model.columns)
for o in order_by:
query = query.order_by(*o)
if self.use_soft_delete and not with_trash:
query = query.where('deleted_at', 'IS', 'NULL')
query = query.sql()
cursor = db.execute_sql(query)
return self.as_iterator(cursor)
def find(self, pk, with_trash=False):
query = QueryBuilder(self.model.table, self.model.columns)\
.where(self.model.primary_key)\
.limit(1)
if self.use_soft_delete and not with_trash:
query = query.where('deleted_at', 'IS', 'NULL')
query = query.sql()
cursor = db.execute_sql(query, (pk,))
return self.as_object(cursor)
def findByIdentifier(self, identifier, with_trash=False):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('identifier')\
.limit(1)
if not with_trash:
query = query.where('deleted_at', 'IS', 'NULL')
query = query.sql()
cursor = db.execute_sql(query, (identifier,))
return self.as_object(cursor)
def create(self, values):
if isinstance(values, self.model):
values = dict(values)
values['created_at'] = datetime.datetime.now()
query = QueryBuilder(self.model.table, self.model.columns[1:])\
.insert()\
.returning([self.model.primary_key])\
.sql()
cursor = db.execute_sql(
query,
[values.get(c, None) for c in self.model.columns[1:]]
)
return self.find(cursor.fetchone()[0])
def deleteByPerson(self, person_id):
query = QueryBuilder(self.model.table).delete().where(
'person_id').sql()
db.execute_sql(query, (person_id,))
def deleteByModuleType(self, module_type_id):
query = QueryBuilder(self.model.table, self.model.columns)\
.delete()\
.where('module_type_id')\
.sql()
db.execute_sql(query, (module_type_id,))
def allByModuleType(self, module_type_id):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('module_type_id')\
.sql()
cursor = db.execute_sql(query, (module_type_id,))
return self.as_iterator(cursor)
def allRegistered(self):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('person_id','IS NOT', 'NULL')\
.sql()
cursor = db.execute_sql(query)
return self.as_iterator(cursor)
def allByPerson(self, person_id):
query = QueryBuilder(self.model.table, self.model.columns)\
.where('person_id')\
.sql()
return self.as_iterator(db.execute_sql(query, (person_id,)))
def deleteAssociation(self, person_id, associated_id):
query = QueryBuilder(self.model.table).delete().where(
'person_id').where('associated_id').sql()
db.execute_sql(query, (person_id, associated_id))
