odb.py

在 linux平台上的网页编程的模板

        if not join is None:
            for a_table,retrieve_foreign_cols in join:
                try:
                    my_col,foreign_col = self.__relations_by_table[a_table]
                    for a_col in retrieve_foreign_cols:
                        full_col_name = "%s.%s" % (my_col,a_col)
                        joined_cols_hash[full_col_name] = 1
                        joined_cols.append(full_col_name)
                        sql_columns.append( full_col_name )

                    join_clauses.append(" left join %s as %s on %s=%s " % (a_table,my_col,my_col,foreign_col))
                        
                except KeyError:
                    eInvalidJoinSpec, "can't find table %s in defined relations for %s" % (a_table,self.__table_name)
                    
        # start buildling SQL
        sql = "select %s from %s" % (string.join(sql_columns,","),
                                     self.__table_name)

        # add join clause
        if join_clauses:
            sql = sql + string.join(join_clauses," ")
        
        # add where clause elements
        sql_where_list = self.__buildWhereClause (col_match_spec,where)
        if sql_where_list:
            sql = sql + " where %s" % (string.join(sql_where_list," and "))

        # add order by clause
        if order_by:
            sql = sql + " order by %s " % string.join(order_by,",")

        # add limit
        if not limit_to is None:
            if not skip_to is None:
#                log("limit,skip = %s,%s" % (limit_to,skip_to))
                if self.db.db.__module__ == "sqlite.main":
                    sql = sql + " limit %s offset %s " % (limit_to,skip_to)
                else:
                    sql = sql + " limit %s, %s" % (skip_to,limit_to)
            else:
                sql = sql + " limit %s" % limit_to
        else:
            if not skip_to is None:
                raise eInvalidData, "can't specify skip_to without limit_to in MySQL"

        dlog(DEV_SELECT,sql)
        cursor.execute(sql)

        # create defaultRowListClass instance...
        return_rows = self.__defaultRowListClass()
            
        # should do fetchmany!
        all_rows = cursor.fetchall()
        for a_row in all_rows:
            data_dict = {}

            col_num = 0
            
            #            for a_col in cursor.description:
            #                (name,type_code,display_size,internal_size,precision,scale,null_ok) = a_col
            for name in sql_columns:
                if self.__col_def_hash.has_key(name) or joined_cols_hash.has_key(name):
                    # only include declared columns!
                    if self.__col_def_hash.has_key(name):
                        c_name,c_type,c_options = self.__col_def_hash[name]
                        if c_type == kBigString and c_options.get("compress_ok",0) and a_row[col_num]:
                            try:
                                a_col_data = zlib.decompress(a_row[col_num])
                            except zlib.error:
                                a_col_data = a_row[col_num]

                            data_dict[name] = a_col_data
                        elif c_type == kInteger or c_type == kIncInteger:
                            value = a_row[col_num]
                            if not value is None:
                                data_dict[name] = int(value)
                            else:
                                data_dict[name] = None
                        else:
                            data_dict[name] = a_row[col_num]

                    else:
                        data_dict[name] = a_row[col_num]
                        
                    col_num = col_num + 1

	    newrowobj = self.__defaultRowClass(self,data_dict,joined_cols = joined_cols)
	    return_rows.append(newrowobj)
	      

            
        return return_rows

    def __deleteRow(self,a_row,cursor = None):
        if cursor is None:
            cursor = self.db.defaultCursor()

        # build the where clause!
        match_spec = a_row.getPKMatchSpec()
        sql_where_list = self.__buildWhereClause (match_spec)

        sql = "delete from %s where %s" % (self.__table_name,
                                           string.join(sql_where_list," and "))
        dlog(DEV_UPDATE,sql)
        cursor.execute(sql)
       

    def __updateRowList(self,a_row_list,cursor = None):
        if cursor is None:
            cursor = self.db.defaultCursor()

        for a_row in a_row_list:
            update_list = a_row.changedList()

            # build the set list!
            sql_set_list = []
            for a_change in update_list:
                col_name,col_val,col_inc_val = a_change
                c_name,c_type,c_options = self.__col_def_hash[col_name]

                if c_type != kIncInteger and col_val is None:
                    sql_set_list.append("%s = NULL" % c_name)
                elif c_type == kIncInteger and col_inc_val is None:
                    sql_set_list.append("%s = 0" % c_name)
                else:
                    if c_type == kInteger:
                        sql_set_list.append("%s = %d" % (c_name, long(col_val)))
                    elif c_type == kIncInteger:
                        sql_set_list.append("%s = %s + %d" % (c_name,c_name,long(col_inc_val)))
                    elif c_type == kBigString and c_options.get("compress_ok",0) and self.db.compression_enabled:
                        compressed_data = zlib.compress(col_val,9)
                        if len(compressed_data) < len(col_val):
                            sql_set_list.append("%s = '%s'" % (c_name, self.db.escape(compressed_data)))
                        else:
                            sql_set_list.append("%s = '%s'" % (c_name, self.db.escape(col_val)))
                    elif c_type == kReal:
                        sql_set_list.append("%s = %s" % (c_name,float(col_val)))

                    else:
                        sql_set_list.append("%s = '%s'" % (c_name, self.db.escape(col_val)))

            # build the where clause!
            match_spec = a_row.getPKMatchSpec()
            sql_where_list = self.__buildWhereClause (match_spec)

            if sql_set_list:
                sql = "update %s set %s where %s" % (self.__table_name,
                                                 string.join(sql_set_list,","),
                                                 string.join(sql_where_list," and "))

                dlog(DEV_UPDATE,sql)
                try:
                    cursor.execute(sql)
                except Exception, reason:
                    if string.find(str(reason), "Duplicate entry") != -1:
                        raise eDuplicateKey, reason
                    raise Exception, reason
                a_row.markClean()

    def __insertRow(self,a_row_obj,cursor = None,replace=0):
        if cursor is None:
            cursor = self.db.defaultCursor()

        sql_col_list = []
        sql_data_list = []
        auto_increment_column_name = None

        for a_col in self.__column_list:
            name,type,options = a_col

            try:
                data = a_row_obj[name]

                sql_col_list.append(name)
                if data is None:
                    sql_data_list.append("NULL")
                else:
                    if type == kInteger or type == kIncInteger:
                        sql_data_list.append("%d" % data)
                    elif type == kBigString and options.get("compress_ok",0) and self.db.compression_enabled:
                        compressed_data = zlib.compress(data,9)
                        if len(compressed_data) < len(data):
                            sql_data_list.append("'%s'" % self.db.escape(compressed_data))
                        else:
                            sql_data_list.append("'%s'" % self.db.escape(data))
                    elif type == kReal:
                        sql_data_list.append("%s" % data)
                    else:
                        sql_data_list.append("'%s'" % self.db.escape(data))

            except KeyError:
                if options.has_key("autoincrement"):
                    if auto_increment_column_name:
                        raise eInternalError, "two autoincrement columns (%s,%s) in table (%s)" % (auto_increment_column_name, name,self.__table_name)
                    else:
                        auto_increment_column_name = name

        if replace:
            sql = "replace into %s (%s) values (%s)" % (self.__table_name,
                                                   string.join(sql_col_list,","),
                                                   string.join(sql_data_list,","))
        else:
            sql = "insert into %s (%s) values (%s)" % (self.__table_name,
                                                   string.join(sql_col_list,","),
                                                   string.join(sql_data_list,","))

        dlog(DEV_UPDATE,sql)
        try:
          cursor.execute(sql)
        except Exception, reason:
          # sys.stderr.write("errror in statement: " + sql + "\n")
          log("error in statement: " + sql + "\n")
          if string.find(str(reason), "Duplicate entry") != -1:
            raise eDuplicateKey, reason
          raise Exception, reason
            
        if auto_increment_column_name:
            if cursor.__module__ == "sqlite.main":
                a_row_obj[auto_increment_column_name] = cursor.lastrowid
            elif cursor.__module__ == "MySQLdb.cursors":
                a_row_obj[auto_increment_column_name] = cursor.insert_id()
            else:
                # fallback to acting like mysql
                a_row_obj[auto_increment_column_name] = cursor.insert_id()

    # ----------------------------------------------------
    #   Helper methods for Rows...
    # ----------------------------------------------------


        
    #####################
    # r_deleteRow(a_row_obj,cursor = None)
    #
    # normally this is called from within the Row "delete()" method
    # but you can call it yourself if you want
    #

    def r_deleteRow(self,a_row_obj, cursor = None):
        curs = cursor
        self.__deleteRow(a_row_obj, cursor = curs)


    #####################
    # r_updateRow(a_row_obj,cursor = None)
    #
    # normally this is called from within the Row "save()" method
    # but you can call it yourself if you want
    #

    def r_updateRow(self,a_row_obj, cursor = None):
        curs = cursor
        self.__updateRowList([a_row_obj], cursor = curs)

    #####################
    # InsertRow(a_row_obj,cursor = None)
    #
    # normally this is called from within the Row "save()" method
    # but you can call it yourself if you want
    #

    def r_insertRow(self,a_row_obj, cursor = None,replace=0):
        curs = cursor
        self.__insertRow(a_row_obj, cursor = curs,replace=replace)


    # ----------------------------------------------------
    #   Public Methods
    # ----------------------------------------------------


        
    #####################
    # deleteRow(col_match_spec)
    #
    # The col_match_spec paramaters must include all primary key columns.
    #
    # Ex:
    #    a_row = tbl.fetchRow( ("order_id", 1) )
    #    a_row = tbl.fetchRow( [ ("order_id", 1), ("enterTime", now) ] )


    def deleteRow(self,col_match_spec, where=None):
        n_match_spec = self._fixColMatchSpec(col_match_spec)
        cursor = self.db.defaultCursor()

        # build sql where clause elements
        sql_where_list = self.__buildWhereClause (n_match_spec,where)
        if not sql_where_list:
            return

        sql = "delete from %s where %s" % (self.__table_name, string.join(sql_where_list," and "))

        dlog(DEV_UPDATE,sql)
        cursor.execute(sql)
        
    #####################
    # fetchRow(col_match_spec)
    #
    # The col_match_spec paramaters must include all primary key columns.
    #
    # Ex:
    #    a_row = tbl.fetchRow( ("order_id", 1) )
    #    a_row = tbl.fetchRow( [ ("order_id", 1), ("enterTime", now) ] )


    def fetchRow(self, col_match_spec, cursor = None):
        n_match_spec = self._fixColMatchSpec(col_match_spec, should_match_unique_row = 1)

        rows = self.__fetchRows(n_match_spec, cursor = cursor)
        if len(rows) == 0:
            raise eNoMatchingRows, "no row matches %s" % repr(n_match_spec)

        if len(rows) > 1:
            raise eInternalError, "unique where clause shouldn't return > 1 row"

        return rows[0]
            

    #####################
    # fetchRows(col_match_spec)
    #
    # Ex:
    #    a_row_list = tbl.fetchRows( ("order_id", 1) )
    #    a_row_list = tbl.fetchRows( [ ("order_id", 1), ("enterTime", now) ] )


    def fetchRows(self, col_match_spec = None, cursor = None, 
		  where = None, order_by = None, limit_to = None, 
		  skip_to = None, join = None):
        n_match_spec = self._fixColMatchSpec(col_match_spec)

        return self.__fetchRows(n_match_spec,
                                cursor = cursor,
                                where = where,
                                order_by = order_by,
                                limit_to = limit_to,
                                skip_to = skip_to,
                                join = join)

    def fetchRowCount (self, col_match_spec = None, 
		       cursor = None, where = None):
        n_match_spec = self._fixColMatchSpec(col_match_spec)
        sql_where_list = self.__buildWhereClause (n_match_spec,where)
	sql = "select count(*) from %s" % self.__table_name
        if sql_where_list:
            sql = "%s where %s" % (sql,string.join(sql_where_list," and "))
        if cursor is None:
          cursor = self.db.defaultCursor()
        dlog(DEV_SELECT,sql)
        cursor.execute(sql)
        try:
            count, = cursor.fetchone()
        except TypeError:
            count = 0
        return count


    #####################
    # fetchAllRows()
    #
    # Ex:
    #    a_row_list = tbl.fetchRows( ("order_id", 1) )
    #    a_row_list = tbl.fetchRows( [ ("order_id", 1), ("enterTime", now) ] )

    def fetchAllRows(self):
        try:
            return self.__fetchRows([])
        except eNoMatchingRows:
            # else return empty list...
            return self.__defaultRowListClass()

    def newRow(self,replace=0):