terom@0: import sqlalchemy
terom@0: from sqlalchemy import *
terom@0: 
terom@0: import logging; log = logging.getLogger('pvl.verkko.db')
terom@0: 
terom@0: # schema
terom@0: metadata = MetaData()
terom@0: 
terom@0: dhcp_hosts = Table('dhcp_hosts', metadata,
terom@0:     Column('rowid',         Integer, primary_key=True),
terom@0: 
terom@0:     # unique
terom@0:     Column('ip',            String),
terom@0:     Column('mac',           String),
terom@0:     Column('gw',            String),
terom@0:     
terom@0:     # updated
terom@0:     Column('name',          String),
terom@0:     Column('first_seen',    DateTime),
terom@0:     Column('last_seen',     DateTime),
terom@0: )
terom@0: 
terom@0: # for ORM models
terom@0: from sqlalchemy.orm import mapper, sessionmaker
terom@0: 
terom@0: Session = sessionmaker()
terom@0: 
terom@0: class Database (object) :
terom@0:     """
terom@0:         Our underlying database.
terom@0:     """
terom@0:     
terom@0:     # XXX: alias Tables in?
terom@0:     dhcp_hosts = dhcp_hosts
terom@0: 
terom@2:     def __init__ (self, database) :
terom@2:         """
terom@2:             database        - sqlalchemy connection URI
terom@2:         """
terom@2: 
terom@0:         self.engine = create_engine(database, 
terom@0:             echo    = (log.isEnabledFor(logging.DEBUG)),
terom@0:         )
terom@0:         self.session = Session(bind=self.engine)
terom@0: 
terom@0:     # ORM
terom@0:     def query (self, cls) :
terom@0:         return self.session.query(cls)
terom@0:     
terom@0:     # SQL
terom@0:     def select (self, query) :
terom@0:         return self.engine.connect().execute(query)
terom@0: 
terom@0:     def get (self, query) :
terom@0:         """
terom@0:             Fetch a single row.
terom@0:             
terom@0:             XXX: returning None if not found
terom@0:         """
terom@0: 
terom@0:         return self.select(query).fetchone()