orm.py - web2pyorm - An Hibernate like ORM for Web2py

Source path: hg/ modules/ orm.py

‹4c65eb030bb0

5e04abd02e6e

"""
Created on May 10, 2010
@author: Ionel Anton <ionelanton.ca@gmail.com>

A sort of ORM for Web2py build above the DAL
It creates DTOs from tables structure.
"""

class DataType:
    """ Web2py DAL data types """
    id = 'id'
    integer = 'integer'
    float = 'float'
    boolean = 'boolean'
    string = 'string'
    text = 'text'
    double = 'double'
    decimal = 'decimal'
    date = 'date'
    time = 'time'
    password = 'password'
    
    """ null type """
    null = None
    
    """ ORM types """
    Attribute = '<Attribute>'
    DTO = '<DTO>'
    DAO = '<DAO>'

class Operator:
    """ SQL operators """
    EQUALS = 0
    NOT_EQUAL = 1
    GREATER_THAN = 2
    LESS_THAN = 3
    STARTS_WITH = 4
    ENDS_WITH = 5
    CONTAINS = 6

class Attribute(object):
    """ An attribute """

    def __init__(self, name, type, value = None, label = ''):
        """
        @param name:
        @param type:
        @param value:
        @param label:  
        """
        
        """
        @attention: the arguments are not inside the body of the function, so,
        once initialized, each call came with the first time initialized values
        """
        if value == None: value = DataType.null
        if label == '': label = ''
             
        """ Members """
        self.name = name
        self.type = type
        self.value = value
        if label == '':
            self.label = name
        else:
            self.label = label
            
    def __repr__(self):
        return DataType.Attribute
    
    def set(self, attr):
        self.name = attr.name
        self.type = attr.type
        self.value = attr.value
        self.label = attr.label

    def copy(self):
        return Attribute(self.name, self.type, self.value, self.label)
    
    def isSimilarTo(self, attr):
        return self.name == attr.name and self.type == attr.type
                
            
class DTO(object):
    """ The DTO contains a list of attributes """
    
    def __init__(self):
        """ Members """
        self.attributes = []
        
    def __repr__(self):
        return DataType.DTO

    def __getattr__(self, name):
        for attr in self.attributes:
            if attr.name == name:
                return attr
        return None
        
#    def __setattr__(self, name, value):
#        pass
        
    def __getitem__(self, name):
        for attr in self.attributes:
            if attr.name == name:
                return attr
        return None

    def __setitem__(self, name, value):
        for attr in self.attributes:
            if attr.name == name:
                attr.value = value
                break
            
    def __delitem__(self, name):
        for attr in self.attributes:
            if attr.name == name:
                self.attributes.remove(attr)
                break

    def setAttr(self, attr):
        for a in self.attributes:
            if a.name == attr.name:
                a.set(attr)
                break
    
    def getAttr(self, name):
        for attr in self.attributes:
            if attr.name == name:
                return attr
        return None
            
    def addAttr(self, attr, index = -1):
        if index == -1: index = -1
        if index >= 0 and index <= len(self.attributes):
            self.attributes.insert(index, attr)
        else:
            self.attributes.append(attr)        

    def copy(self):
        dto = DTO()
        for attr in self.attributes:
            dto.addAttr(attr.copy())
        return dto

    def empty(self):
        """ Sets all attributes values to None """
        for attr in self.attributes:
            attr.value = None
            
    def isSimilarTo(self, dto):
        for attr in self.attributes:
            if not attr.isSimilarTo(dto.getAttr(attr.name)):
                return False
        return True

    def inflate(self):
        """
        Join all attributes of nested DTOs with current attributes.
        """
        dto = DTO()
        dto.attributes = self.__inflate()
        return dto

    def __inflate(self):
        """
        A DTO can hold inside his attributes other DTOs.
        We join all attributes of nested DTOs with current attributes.
        """
        attrs = []
        for attr in self.attributes:
            if attr.type == DataType.DTO:
                attrs.extend(attr.value.__inflate())
            else:
                attrs.append(attr.copy())
        return attrs

class DAO(object):
    """
    Generate a complete ready to use DTO
    """
    
    def __init__(self, db, table_name, field_names = []):
        """
        @param db: gluon.sql.DAL
        @param table_name: name of the table
        @param field_names: list of field names used to initialize the DTO member
        """
        
        """ Argument verification """
        if field_names == [] : field_names = []
        
        """ Members """
        self.db = db
        self.table_name = table_name
        self.field_names = field_names
        self.dto = DTO()
        
        if self.field_names == [] : self.field_names = self.db[self.table_name].fields
        
        for field_name in self.field_names:
            if field_name in self.db[self.table_name].fields:
                self.dto.addAttr(Attribute(field_name,
                                           self.db[self.table_name][field_name].type,
                                           None,
                                           self.db[self.table_name][field_name].label))
            else:
                raise Exception('There is no field "' + field_name + '" in the table_name "' + self.table_name + '"')
        
    def __repr__(self):
        return DataType.DAO
    
    def getRows(self, op):
        query = self.db[self.table_name]['id'] > 0
        for field_name in self.field_names:
            if not self.dto[field_name].value is None:
                query &= self.__build_query(self.db[self.table_name][field_name], op, self.dto[field_name].value)
        print "query: " + str(query) + '\n'
        return self.db(query).select()

    def persist(self, dto):
        """
        SQL INSERT EQUIVALENT
        @return: id of the inserted record
        """
        if self.dto.isSimilarTo(dto):
            sql_args = dict()
            for attr in dto.attributes:
                if 'id' != attr.name:
                    sql_args[attr.name] = dto[attr.name].value
            id = self.db[self.table_name].insert(**sql_args)
            self.db.commit()
            return id
        else:
            raise Exception("Incompatible dto object") 
        
    def update(self, dto):
        """ SQL UPDATE EQUIVALENT """
        if self.dto.isSimilarTo(dto):
            sql_args = dict()
            for attr in dto.attributes:
                if 'id' != attr.name:
                    sql_args[attr.name] = dto[attr.name].value
            if not dto.id.value is None: 
                self.db(self.db[self.table_name].id == dto.id.value).update(**sql_args)
                self.db.commit()
            else:
                raise Exception("No id -> Cannot update!")
        else:
            raise Exception("Incompatible dto object")
    
    def removeById(self, id):
        """ SQL DELETE BY ID EQUIVALENT """
        self.db(self.db[self.table_name].id == id).delete()
        self.db.commit()
    
    def removeByExample(self, dto):
        dto_list = self.findByExample(dto)
        for each_dto in dto_list:
            self.removeById(each_dto.id.value)
    
    def removeLike(self, dto):
        dto_list = self.findLike(dto)
        for each_dto in dto_list:
            self.removeById(each_dto.id.value)
    
    def removeByComparison(self, op, dto):
        dto_list = self.findByComparison(dto)
        for each_dto in dto_list:
            self.removeById(each_dto.id.value)

    def findById(self, id):
        """
        SQL SELECT BY ID EQUIVALENT
        @note: DAL assumes 'id' as primary key
        @param id: id of the record in database
        """
        dto_copy = self.dto.copy()
        rows = self.db(self.db[self.table_name]['id'] == id).select()
        if rows:
            for row in rows:
                for field_name in self.field_names:
                    dto_copy.setAttr(Attribute(field_name,
                                               self.db[self.table_name][field_name].type,
                                               row[field_name],
                                               self.db[self.table_name][field_name].label))
                break ## only first row is needed
            return dto_copy
        else:
            return None    

    def findByIdRecursive(self, id):
        """ Select all records in the related tables """
        dto_copy = self.dto.copy()
        rows = self.db(self.db[self.table_name]['id'] == id).select()
        if rows:
            for row in rows:
                for field_name in self.field_names:
                    if self.__isForeignKey(field_name):
                        dao = DAO(self.db,
                                  self.__returnParentTable(field_name),
                                  self.__returnListOfReferencedFields(field_name, False))
                        dto_copy.setAttr(Attribute(field_name,
                                                   DataType.DTO,
                                                   dao.findByIdRecursive(row[field_name]), ## id of record in parent table
                                                   self.__returnPrimaryKey(field_name)))
                    else:
                        dto_copy.setAttr(Attribute(field_name,
                                                   self.db[self.table_name][field_name].type,
                                                   row[field_name],
                                                   self.db[self.table_name][field_name].label))    
                break ## even if one row is returned anyway
            return dto_copy
        else:
            return None
    
    def findByExample(self, dto):
        """
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            dto_list = []
            self_dto = self.dto.copy()
            self.dto = dto
            rows = self.getRows(Operator.EQUALS)
            if rows:
                for row in rows:
                    dto_list.append(self.findById(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 


    def findByExampleRecursive(self, dto):
        """
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            self_dto = self.dto.copy()
            self.dto = dto
            dto_list = []
            rows = self.getRows(Operator.EQUALS)
            if rows:
                for row in rows:
                    dto_list.append(self.findByIdRecursive(row.id))
            self.dto = self_dto
            return dto_list

        else:
            raise Exception("Incompatible dto object") 


    def findLike(self, dto):
        """
        SELECT LIKE EQUIVALENT
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            self_dto = self.dto.copy()
            self.dto = dto
            dto_list = []
            rows = self.getRows(Operator.CONTAINS)
            if rows:
                for row in rows:
                    dto_list.append(self.findById(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 


    def findLikeRecursive(self, dto):
        """
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            self_dto = self.dto.copy()
            self.dto = dto
            dto_list = []
            rows = self.getRows(Operator.CONTAINS)
            if rows:
                for row in rows:
                    dto_list.append(self.findByIdRecursive(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 

    def findByComparison(self, op, dto):
        """
        @dto: example dto
        @op: Operator
        @return: list of dto
        """
        if self.dto.isSimilarTo(dto):
            dto_list = []
            self_dto = self.dto.copy()
            self.dto = dto
            rows = self.getRows(op)
            if rows:
                for row in rows:
                    dto_list.append(self.findById(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object")


    def findByComparisonRecursive(self, op, dto):
        """
        @param op: Operator
        @param dto:
        @return: list of DTO
        """
        if self.dto.isSimilarTo(dto):
            dto_list = []
            self_dto = self.dto.copy()
            self.dto = dto
            rows = self.getRows(op)
            if rows:
                for row in rows:
                    dto_list.append(self.findByIdRecursive(row.id))
            self.dto = self_dto
            return dto_list
        else:
            raise Exception("Incompatible dto object") 

    def findAll(self):
        """
        @return: list of all dto
        """
        dto_list = []
        rows = self.db(self.db[self.table_name]['id'] > 0).select()
        if rows:
            for row in rows:
                dto_list.append(self.findById(row.id))
        return dto_list

    def findAllRecursive(self):
        dto_list = []
        rows = self.db(self.db[self.table_name]['id'] > 0).select()
        if rows:
            for row in rows:
                dto_list.append(self.findByIdRecursive(row.id))
        return dto_list
        
    def query(self, query):
        """
        @param query: a DAL query
        """
        dto_list = []
        rows = self.db(query).select()
        if rows:
            for row in rows:
                dto_list.append(self.findById(row.id))
        return dto_list
    
    def __isForeignKey(self, field_name):
        """
        Verify if a field name is a foreign key
        @param field_name: field name
        @return: True if the field is foreign key 
        """
        is_foreign_key = False
        ## if IS_IN_DB is the only validator
        if "<class 'gluon.validators.IS_IN_DB'>" == str(type(self.db[self.table_name][field_name].requires)):
            is_foreign_key = True
        ## if multiples validators 
        elif type([]) == type(self.db[self.table_name][field_name].requires):
            for validator in self.db[self.table_name][field_name].requires:
                if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                    is_foreign_key = True
                    break
        return is_foreign_key
    
    def __returnParentTable(self, field_name):
        """Get the table name referenced by field_name foreign key"""
        parent_table = ''
        if type([]) == type(self.db[self.table_name][field_name].requires):
            for validator in self.db[self.table_name][field_name].requires:
                if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                    parent_table = validator.ktable
                    break
        else:
            parent_table = self.db[self.table_name][field_name].requires.ktable
        return parent_table
    
    def __returnPrimaryKey(self, field_name):
        """Get the primary key referenced by field_name"""
        if self.__isForeignKey(field_name):
            if type([]) == type(self.db[self.table_name][field_name].requires):
                for validator in self.db[self.table_name][field_name].requires:
                    if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                        return validator.kfield
            else:
                return self.db[self.table_name][field_name].requires.kfield

    
    def __returnListOfReferencedFields(self, field_name, show_id = True):
        """
        Get the list of all field names referenced by field_name
        @param field_name: the foreign key that make reference to the field in the parent table
        @param show_id: if exists, add the referenced 'id' field name in the list.
        @return: list of referenced field names in the parent table    
        """
        if show_id == True:
            show_id = True
            
        list_of_fields = []
        if type([]) == type(self.db[self.table_name][field_name].requires):
            for validator in self.db[self.table_name][field_name].requires:
                if "<class 'gluon.validators.IS_IN_DB'>" == str(type(validator)):
                    list_of_fields = validator.ks
                    break
        else:
            list_of_fields = self.db[self.table_name][field_name].requires.ks
            
        if not show_id and 'id' in list_of_fields:
            list_of_fields.remove('id')
        return list_of_fields

    def __build_query(self, field, op, value):
        """
        @note: inspired by mr.freeze
        @copyright: http://www.web2pyslices.com/main/slices/take_slice/78
        
        @param field: table field
        @param op: Operator
        @param value: value  
        """
        if op == Operator.EQUALS:
            return field == value
        elif op == Operator.NOT_EQUAL:
            return field != value
        elif op == Operator.GREATER_THAN:
            return field > value
        elif op == Operator.LESS_THAN:
            return field < value
        elif op == Operator.ENDS_WITH:
            return field.like(value+'%')
        elif op == Operator.STARTS_WITH:
            return field.like('%'+value)
        elif op == Operator.CONTAINS:
            return field.like('%'+value+'%')

Show details Hide details

Change log

5e04abd02e6e by hg on May 29, 2010 Diff

Added more DAL data types.

Go to:

Project members, sign in to write a code review

Older revisions

4c65eb030bb0 by hg on May 28, 2010 Diff

Multiple changes.

da2e30006b22 by hg on May 15, 2010 Diff

First commit.

All revisions of this file

File info

Size: 18555 bytes, 550 lines

View raw file