1

"""

     2

    SQLAlchemy Database tables model for pvl.verkko

     3

"""

     4

     5

import sqlalchemy

     6

from sqlalchemy import *

     7

     8

import logging; log = logging.getLogger('pvl.verkko.db')

     9

    10

# schema

    11

metadata = MetaData()

    12

    13

dhcp_hosts = Table('dhcp_hosts', metadata,

    14

    Column('id',            Integer,    primary_key=True),

    15

    16

    # unique

    17

    Column('ip',            String,     nullable=False),

    18

    Column('mac',           String,     nullable=False),

    19

    Column('gw',            String,     nullable=False),

    20

    21

    # updated

    22

    Column('first_seen',    DateTime,   nullable=False),

    23

    Column('last_seen',     DateTime,   nullable=False),

    24

    25

    # scalar; updated

    26

    Column('name',          String,     nullable=True),

    27

    Column('state',         String,     nullable=True),

    28

    Column('error',         String,     nullable=True),

    29

    30

    # counters

    31

    Column('count',         Integer,    default=1),

    32

    33

    UniqueConstraint('ip', 'mac', 'gw'),

    34

)

    35

    36

dhcp_leases = Table('dhcp_leases', metadata,

    37

    Column('id',            Integer,    primary_key=True),

    38

    39

    Column('ip',            String,     nullable=False),

    40

    Column('mac',           String,     nullable=False),

    41

    Column('hostname',      String,     nullable=True),

    42

    43

    Column('starts',        DateTime,   nullable=False),

    44

    Column('ends',          DateTime,   nullable=True),  # never

    45

    # TODO: renewed

    46

    47

    Column('state',         String,     nullable=True),

    48

    Column('next',          String,     nullable=True),

    49

)

    50

    51

wlan_sta = Table('wlan_sta', metadata,

    52

    Column('id',            Integer,    primary_key=True),

    53

    54

    Column('ap',            String,     nullable=False),

    55

    Column('wlan',          String,     nullable=False),

    56

    Column('sta',           String,     nullable=False),

    57

    58

    # updated

    59

    Column('first_seen',    DateTime,   nullable=False),

    60

    Column('last_seen',     DateTime,   nullable=False),

    61

    62

    Column('count',         Integer,    default=1),

    63

    Column('msg',           String,     nullable=True),

    64

    65

    UniqueConstraint('ap', 'wlan', 'sta'),

    66

)

    67

    68

# for ORM models

    69

from sqlalchemy.orm import mapper, sessionmaker

    70

    71

Session = sessionmaker()

    72

    73

class Database (object) :

    74

    """

    75

        Our underlying database.

    76

    """

    77

    78

    def __init__ (self, database) :

    79

        """

    80

            database        - sqlalchemy connection URI

    81

        """

    82

    83

        self.engine = create_engine(database, 

    84

            echo    = (log.isEnabledFor(logging.DEBUG)),

    85

        )

    86

    87

    # ORM

    88

    def session (self) :

    89

        """

    90

            Return a new ORM session bound to our engine.

    91

    92

            XXX: session lifetimes? Explicit close?

    93

        """

    94

    95

        return Session(bind=self.engine)

    96

    97

    # SQL

    98

    def connect (self) :

    99

        return self.engine.connect()

   100

   101

    def execute (self, query) :

   102

        return self.engine.execute(query)

   103

   104

    def select (self, query) :

   105

        return self.engine.execute(query)

   106

   107

    def get (self, query) :

   108

        """

   109

            Fetch a single row.

   110

   111

            XXX: returning None if not found

   112

        """

   113

   114

        return self.select(query).fetchone()

   115

   116

    def insert (self, insert) :

   117

        """

   118

            Execute given INSERT query, returning the inserted primary key.

   119

        """

   120

   121

        result = self.engine.execute(insert)

   122

   123

        id, = result.inserted_primary_key

   124

   125

        return id

   126

   127

    def update (self, update) :

   128

        """

   129

            Execute given UPDATE query, returning the number of matched rows.

   130

        """

   131

   132

        result = self.engine.execute(update)

   133

   134

        return result.rowcount

   135

   136

# command-line

   137

import optparse

   138

import sys

   139

   140

def parser (parser, table=None) :

   141

    parser.set_defaults(

   142

            create_table    = table,

   143

    )

   144

    parser = optparse.OptionGroup(parser, "Verkko Database")

   145

    parser.add_option('--database',             metavar='URI',

   146

            help="Connect to given database")

   147

   148

    if table is not None :

   149

        parser.add_option('--create',               action='store_true',

   150

                help="Initialize database")

   151

   152

    return parser

   153

   154

def apply (options, required=True) :

   155

    # db

   156

    if not options.database and required :

   157

        log.error("No database given")

   158

        sys.exit(1)

   159

   160

    log.info("Open up database: %s", options.database)

   161

    db = Database(options.database)

   162

   163

    if options.create_table is not None and options.create :

   164

        log.info("Creating database tables: %s", options.create_table)

   165

        options.create_table.create(db.engine, checkfirst=True)

   166

   167

    return db