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

# for ORM models

    52

from sqlalchemy.orm import mapper, sessionmaker

    53

    54

Session = sessionmaker()

    55

    56

class Database (object) :

    57

    """

    58

        Our underlying database.

    59

    """

    60

    61

    def __init__ (self, database) :

    62

        """

    63

            database        - sqlalchemy connection URI

    64

        """

    65

    66

        self.engine = create_engine(database, 

    67

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

    68

        )

    69

    70

    # ORM

    71

    def session (self) :

    72

        """

    73

            Return a new ORM session bound to our engine.

    74

    75

            XXX: session lifetimes? Explicit close?

    76

        """

    77

    78

        return Session(bind=self.engine)

    79

    80

    # SQL

    81

    def connect (self) :

    82

        return self.engine.connect()

    83

    84

    def execute (self, query) :

    85

        return self.engine.execute(query)

    86

    87

    def select (self, query) :

    88

        return self.engine.execute(query)

    89

    90

    def get (self, query) :

    91

        """

    92

            Fetch a single row.

    93

    94

            XXX: returning None if not found

    95

        """

    96

    97

        return self.select(query).fetchone()

    98

    99

    def insert (self, insert) :

   100

        """

   101

            Execute given INSERT query, returning the inserted primary key.

   102

        """

   103

   104

        result = self.engine.execute(insert)

   105

   106

        id, = result.inserted_primary_key

   107

   108

        return id

   109

   110

    def update (self, update) :

   111

        """

   112

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

   113

        """

   114

   115

        result = self.engine.execute(update)

   116

   117

        return result.rowcount

   118