Das deutsche Python-Forum

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""

   2005-12-18: v0.0.1a    - Created by Tabellar

   PYPERCOM: PYthon PERsistence COMponent
   =======================================

   Access and modify component for database objects.
   Object access only available over the Pool and
   Query Manager Interface.

                        o 
                        |
                     PYPERCOM
                        |
                        o
                       /  \ 
                      /    \
                     /      \
                    o        o
                    |        |  
        OBJMGR--o--POOL    QUERY
                    |  \  /  |
                    |   \/   |
                    |   /\   |
                    |  /  \  |
                    | /    \ |
                    o        o
                    |        |
                 CONMGR   SQLMGR
                    |
                    o
                    |
                  DBAPI     


   Pool Manager
   ----------------------
   Creates and shares object pools. Object pools are
   responsible for object modifications and the DB
   persistence synchronisation.

   Query Manager
   ----------------------
   Provides only SELECT queries for list requests. 

   Connection Manager
   ----------------------
   Creates and shares CURSORCONNECTIONS, a special
   PYPER DB-API cursor connection object.

   Object Manager
   ----------------------
   Provides database objects

   SQL Manager
   ----------------------
   Administrate all SQL statements.    

"""



# DB-Engine settings. Maybe later stored in a config file.
dbEngines={
           'postgres': {'database':'dbwf', 
                        'host'    :'127.0.0.1', 
                        'user'    :'postgres', 
                        'password':'xxxxxxx'},
           'sqlite'  : {'database':'pypercom.sqlite'}
           }


class PyPerCom(object):
  def __init__(self):
      self.conMgr  = ConnectionMgr()
      self.sqlMgr  = SqlMgr()
      self.objMgr  = ObjectMgr()
      self.poolMgr = PoolMgr(self.conMgr,self.objMgr,self.sqlMgr)
      self.queryMgr= QueryMgr(self.conMgr,self.sqlMgr)

        

class PoolMgr(object):
  def __init__(self,conMgr,objMgr,sqlMgr):
      """ Pool Manager """
      self.pools=[]
      self.conMgr=conMgr
      self.objMgr=objMgr
      self.sqlMgr=sqlMgr

  def makePool(self):
      pool=Pool(self.conMgr.getCursorConnection(),self.objMgr,self.sqlMgr)
      self.pools.append(pool)
      return pool

class Pool(object):
  def __init__(self,curcon,objMgr,sqlMgr):
      """ Objekt Pool """
      self.objects=[]
      self.curcon=curcon
      self.objMgr=objMgr
      self.sqlMgr=sqlMgr
      
  def makeObject(self,type):
      object=self.objMgr.makeObject(type)
      object._dirty=1
      self.objects.append(object)
      return object

  def getObject(self,type,id):
      object=self.objMgr.makeObject(type)
      object.props['id']=id
      self.objects.append(object)
      sqlStat=self.sqlMgr.getSqlPropertyStatement(object)
      self.curcon.execute(sqlStat[0],sqlStat[1])
      results=self.curcon.fetchall()
      object.props={}
      resultindex=0
      for i in sqlStat[2]:
          object.props[i]=results[0][resultindex]
          resultindex+=1
      return object

  def insertObject(self,object):
      self.objects.append(object)

  def commit(self):
      for i in self.objects:
            if i._dirty!=0:
               sqlStat=self.sqlMgr.getSqlModifyStatement(i)
               self.curcon.execute(sqlStat[0],sqlStat[1])
               i._dirty=0
      self.curcon.commit()

  def clear(self):    
      self.objects=[]

  def close(self):
      self.curcon.ready()
      self=None



class QueryMgr(object):
    def __init__(self,conMgr,sqlMgr):
        """ SELECT Query Manager """
        self.conMgr=conMgr
        self.sqlMgr=sqlMgr
        
    def getPersons(self):
        curcon=self.conMgr.getCursorConnection()
        sql=self.sqlMgr.getSqlQuery('getPersons')
        curcon.execute(sql,())
        results=curcon.fetchall()
        curcon.ready()
        return results


class ConnectionMgr(object):
    def __init__(self):
        self.maxsize=4
        self.curcons=[]
        
    def newCursorConnection(self):
        if len(self.curcons) <= self.maxsize:
           curcon=CursorConnection()
           self.curcons.append(curcon)
           return curcon
        else:
           raise NotImplementedError
        
    def getCursorConnection(self):
        notusedcons=0
        for i in self.curcons:
           if i.inUse()==0:
              notusedcons += 1
              i.used()
              return i
              break
        if notusedcons == 0:
           curcon=self.newCursorConnection()
           curcon.used()
           return curcon
           
    def closeCursorConnections(self):
        for i in self.curcons:
            i.close()

class CursorConnection(object):
    def __init__(self):
        self.inuse=0
        self.connect()
    def connect(self,dbengine='sqlite'):
        if dbengine=='postgres':
            import pyPgSQL.PgSQL as dbAPI
            args=dbEngines['postgres']
            self.con=dbAPI.connect(**args)
            self.enginetype='postgres'
        elif dbengine=='sqlite':
            from pysqlite2 import dbapi2 as dbAPI
            args=dbEngines['sqlite']
            self.con=dbAPI.connect(args['database'])
            self.enginetype='sqlite'
        self.cursor=self.con.cursor()
    def inUse(self):
        return self.inuse
    def used(self):
        self.inuse=1
    def ready(self):
        self.inuse=0
    def cursor(self):
        return self.con.cursor()
    def execute(self,sql,args):
        self.cursor.execute(sql,args)
    def fetchall(self):
        return self.cursor.fetchall()
    def commit(self):
        self.con.commit()
    def close(self):
        self.con.close()

class ObjectMgr(object):
  def __init__(self):
      """ object Mgr """
  def makeObject(self,type):
      if type=='person':
         return Person()


class Person(object):
  def __init__(self):
      """ Example Object """
      self.type='person'
      self._dirty=0
      self.props={}

  def init(self,vorname,nachname,nickname,birthday,status):
      self.props['vorname'] = vorname
      self.props['nachname']= nachname
      self.props['nickname']= nickname
      self.props['birthday']= birthday
      self.props['status']  = status
      self._dirty=1

  def initByProps(self,props):
      self.props=props
      self._dirty=5

  def updateProps(self,propname,propvalue):
      self.props[propname]=propvalue      
      self._dirty=2 #update

  def delete(self):
      self._dirty=3 #delete


class SqlMgr(object):
  def __init__(self):
      """ Statement Mgr """ 

  def getSqlModifyStatement(self,obj):
      sql=sqlObject[obj.type][obj._dirty]['sql']
      args=[]
      for i in sqlObject[obj.type][obj._dirty]['args']:
          args.append(obj.props[i])
      return (sql,args)

  def getSqlPropertyStatement(self,obj):
      sql=sqlObject[obj.type][9]['sql']
      args=[]
      for i in sqlObject[obj.type][9]['args']:
          args.append(obj.props[i])
      propDef=sqlObject[obj.type][9]['prop']    
      return (sql,args,propDef)

  def getSqlQuery(self,key):
      return sqlQuery[key]





#---------------------------------------------------------------------------------   
#   Dictionary for SQL Object modifications -> Pool
#---------------------------------------------------------------------------------   
"""
     CursorConnection._dirty Flags:
     ------------------------------------
     1:INSERT
     2:UPDATE
     3:DELETE
     9:PROPERTIES
"""

sqlObject={'person':{1:{'sql' :"INSERT INTO tperson (vorname,nachname,nickname,birthday,status) VALUES (?,?,?,?,?);",
                        'args': ('vorname','nachname','nickname','birthday','status')},
                     2:{'sql' :"UPDATE tperson set created=?,vorname=?,nachname=?,nickname=?,birthday=?,status=? where id=?;",
                        'args': ('created','vorname','nachname','nickname','birthday','status','id')},
                     3:{'sql' :"DELETE FROM tperson where id=?;",
                        'args': ('id',)},
                     9:{'sql' :"""SELECT id,created,vorname,nachname,nickname,birthday,status
                                  FROM tperson
                                  WHERE id=?;""",
                        'args': ('id',),
                        'prop': ('id','created','vorname','nachname','nickname','birthday','status')},
                    } 
        } 


#---------------------------------------------------------------------------------   
#   Dictionary for SELECT statements -> QueryManager
#---------------------------------------------------------------------------------   

sqlQuery={
          'getPersons':"select * from tperson;",
         }


#---------------------------------------------------------------------------------   
#   SQL Table example
#---------------------------------------------------------------------------------   

"""
create Table tperson        (
             id             INTEGER PRIMARY KEY,
             created        TIMESTAMP,
             vorname        VARCHAR (16),
             nachname       VARCHAR (16),
             nickname       VARCHAR (32),
             birthday       DATE,
             status         INTEGER DEFAULT 0          
);
CREATE TRIGGER insert_tperson AFTER  INSERT ON tperson
BEGIN
 UPDATE tperson SET created = DATETIME('NOW','LOCALTIME') WHERE rowid = new.rowid;
END;
"""