"""
SQLAlchemy Database tables model for pvl.verkko
"""
import sqlalchemy
from sqlalchemy import *
import logging; log = logging.getLogger('pvl.verkko.db')
# schema
metadata = MetaData()
dhcp_hosts = Table('dhcp_hosts', metadata,
Column('id', Integer, primary_key=True),
# unique
Column('ip', String, nullable=False),
Column('mac', String, nullable=False),
Column('gw', String, nullable=False),
# updated
Column('first_seen', DateTime, nullable=False),
Column('last_seen', DateTime, nullable=False),
# scalar; updated
Column('name', String, nullable=True),
Column('state', String, nullable=True),
Column('error', String, nullable=True),
# counters
Column('count', Integer, default=1),
UniqueConstraint('ip', 'mac', 'gw'),
)
dhcp_leases = Table('dhcp_leases', metadata,
Column('id', Integer, primary_key=True),
Column('ip', String, nullable=False),
Column('mac', String, nullable=False),
Column('hostname', String, nullable=True),
Column('starts', DateTime, nullable=False),
Column('ends', DateTime, nullable=True), # never
# TODO: renewed
Column('state', String, nullable=True),
Column('next', String, nullable=True),
)
wlan_sta = Table('wlan_sta', metadata,
Column('id', Integer, primary_key=True),
Column('ap', String, nullable=False),
Column('wlan', String, nullable=False),
Column('sta', String, nullable=False),
# updated
Column('first_seen', DateTime, nullable=False),
Column('last_seen', DateTime, nullable=False),
Column('count', Integer, default=1),
Column('msg', String, nullable=True),
UniqueConstraint('ap', 'wlan', 'sta'),
)
# for ORM models
from sqlalchemy.orm import mapper, sessionmaker
Session = sessionmaker()
class Database (object) :
"""
Our underlying database.
"""
def __init__ (self, database) :
"""
database - sqlalchemy connection URI
"""
self.engine = create_engine(database,
echo = (log.isEnabledFor(logging.DEBUG)),
)
# ORM
def session (self) :
"""
Return a new ORM session bound to our engine.
XXX: session lifetimes? Explicit close?
"""
return Session(bind=self.engine)
# SQL
def connect (self) :
return self.engine.connect()
def execute (self, query) :
return self.engine.execute(query)
def select (self, query) :
return self.engine.execute(query)
def get (self, query) :
"""
Fetch a single row.
XXX: returning None if not found
"""
return self.select(query).fetchone()
def insert (self, insert) :
"""
Execute given INSERT query, returning the inserted primary key.
"""
result = self.engine.execute(insert)
id, = result.inserted_primary_key
return id
def update (self, update) :
"""
Execute given UPDATE query, returning the number of matched rows.
"""
result = self.engine.execute(update)
return result.rowcount
# command-line
import optparse
import sys
def parser (parser, table=None) :
parser.set_defaults(
create_table = table,
)
parser = optparse.OptionGroup(parser, "Verkko Database")
parser.add_option('--database', metavar='URI',
help="Connect to given database")
if table is not None :
parser.add_option('--create', action='store_true',
help="Initialize database")
return parser
def apply (options, required=True) :
# db
if not options.database and required :
log.error("No database given")
sys.exit(1)
log.info("Open up database: %s", options.database)
db = Database(options.database)
if options.create_table is not None and options.create :
log.info("Creating database tables: %s", options.create_table)
options.create_table.create(db.engine, checkfirst=True)
return db