terom@178: """
terom@178:     SQLAlchemy Database tables model for pvl.verkko
terom@178: """
terom@178: 
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@17:     Column('id',            Integer,    primary_key=True),
terom@0: 
terom@0:     # unique
terom@14:     Column('ip',            String,     nullable=False),
terom@14:     Column('mac',           String,     nullable=False),
terom@14:     Column('gw',            String,     nullable=False),
terom@0:     
terom@0:     # updated
terom@14:     Column('first_seen',    DateTime,   nullable=False),
terom@14:     Column('last_seen',     DateTime,   nullable=False),
terom@14:     
terom@14:     # scalar; updated
terom@14:     Column('name',          String,     nullable=True),
terom@16:     Column('state',         String,     nullable=True),
terom@16:     Column('error',         String,     nullable=True),
terom@16:     
terom@16:     # counters
terom@16:     Column('count',         Integer,    default=1),
terom@14: 
terom@14:     UniqueConstraint('ip', 'mac', 'gw'),
terom@0: )
terom@0: 
terom@17: dhcp_leases = Table('dhcp_leases', metadata,
terom@17:     Column('id',            Integer,    primary_key=True),
terom@17: 
terom@17:     Column('ip',            String,     nullable=False),
terom@17:     Column('mac',           String,     nullable=False),
terom@17:     Column('hostname',      String,     nullable=True),
terom@17: 
terom@17:     Column('starts',        DateTime,   nullable=False),
terom@17:     Column('ends',          DateTime,   nullable=True),  # never
terom@184:     # TODO: renewed
terom@17: 
terom@17:     Column('state',         String,     nullable=True),
terom@17:     Column('next',          String,     nullable=True),
terom@17: )
terom@17: 
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@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: 
terom@0:     # ORM
terom@16:     def session (self) :
terom@40:         """
terom@40:             Return a new ORM session bound to our engine.
terom@40: 
terom@40:             XXX: session lifetimes? Explicit close?
terom@40:         """
terom@40: 
terom@16:         return Session(bind=self.engine)
terom@16:    
terom@0:     # SQL
terom@40:     def connect (self) :
terom@40:         return self.engine.connect()
terom@40: 
terom@17:     def execute (self, query) :
terom@17:         return self.engine.execute(query)
terom@17: 
terom@0:     def select (self, query) :
terom@14:         return self.engine.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()
terom@17: 
terom@17:     def insert (self, insert) :
terom@17:         """
terom@17:             Execute given INSERT query, returning the inserted primary key.
terom@17:         """
terom@17: 
terom@17:         result = self.engine.execute(insert)
terom@17: 
terom@17:         id, = result.inserted_primary_key
terom@17:         
terom@17:         return id
terom@17: 
terom@17:     def update (self, update) :
terom@17:         """
terom@17:             Execute given UPDATE query, returning the number of matched rows.
terom@17:         """
terom@17: 
terom@17:         result = self.engine.execute(update)
terom@17:         
terom@17:         return result.rowcount
terom@17: