0
|
1 |
import sqlalchemy
|
|
2 |
from sqlalchemy import *
|
|
3 |
|
|
4 |
import logging; log = logging.getLogger('pvl.verkko.db')
|
|
5 |
|
|
6 |
DATABASE = 'sqlite:///var/verkko.db'
|
|
7 |
|
|
8 |
# schema
|
|
9 |
metadata = MetaData()
|
|
10 |
|
|
11 |
dhcp_hosts = Table('dhcp_hosts', metadata,
|
|
12 |
Column('rowid', Integer, primary_key=True),
|
|
13 |
|
|
14 |
# unique
|
|
15 |
Column('ip', String),
|
|
16 |
Column('mac', String),
|
|
17 |
Column('gw', String),
|
|
18 |
|
|
19 |
# updated
|
|
20 |
Column('name', String),
|
|
21 |
Column('first_seen', DateTime),
|
|
22 |
Column('last_seen', DateTime),
|
|
23 |
)
|
|
24 |
|
|
25 |
# for ORM models
|
|
26 |
from sqlalchemy.orm import mapper, sessionmaker
|
|
27 |
|
|
28 |
Session = sessionmaker()
|
|
29 |
|
|
30 |
|
|
31 |
class Database (object) :
|
|
32 |
"""
|
|
33 |
Our underlying database.
|
|
34 |
"""
|
|
35 |
|
|
36 |
# XXX: alias Tables in?
|
|
37 |
dhcp_hosts = dhcp_hosts
|
|
38 |
|
|
39 |
def __init__ (self, database=DATABASE) :
|
|
40 |
self.engine = create_engine(database,
|
|
41 |
echo = (log.isEnabledFor(logging.DEBUG)),
|
|
42 |
)
|
|
43 |
self.session = Session(bind=self.engine)
|
|
44 |
|
|
45 |
# ORM
|
|
46 |
def query (self, cls) :
|
|
47 |
return self.session.query(cls)
|
|
48 |
|
|
49 |
# SQL
|
|
50 |
def select (self, query) :
|
|
51 |
return self.engine.connect().execute(query)
|
|
52 |
|
|
53 |
def get (self, query) :
|
|
54 |
"""
|
|
55 |
Fetch a single row.
|
|
56 |
|
|
57 |
XXX: returning None if not found
|
|
58 |
"""
|
|
59 |
|
|
60 |
return self.select(query).fetchone()
|