######################################################################
# Copyright (c) 2007, Petteri Aimonen <jpa@kapsi.fi>
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are met:
#     * Redistributions of source code must retain the above copyright
#       notice and this list of conditions.
#     * Redistributions in binary form must reproduce the above copyright
#       notice and this list of conditions in the documentation and/or other
#       materials provided with the distribution.

'''Low-level database access
See dataaccess.py for high-level access functions
'''

################
# Table creation

table_definitions = {
'animals': 'animalid INTEGER, groupid INTEGER, active INTEGER, notes TEXT, PRIMARY KEY (animalid)',
'feeds': 'date TEXT, animalid INTEGER, grams INTEGER, PRIMARY KEY (animalid, date)',
'files': 'filename TEXT, PRIMARY KEY (filename)',
'groupfeed': 'date TEXT, valve INTEGER, count INTEGER, grams INTEGER, PRIMARY KEY (valve, date)',
'importlogs': 'time TEXT, log TEXT, PRIMARY KEY (time)'
}

index_definitions = {
'animals': ['groupid'],
'groupfeed': ['date']
}

def create_indexes(cursor, table):
    '''Create indexes for specified table'''
    for column in index_definitions[table]:
        indexname = "idx_%s_%s" % (table, column)
        cursor.execute('''CREATE INDEX %s ON %s (%s)''' % (indexname, table, column))

def create_table(cursor, table):
    '''Create specified table'''
    cursor.execute("CREATE TABLE %s (%s)" % (table, table_definitions[table]))

def create_tables(cursor):
    '''Create all tables and indexes'''
    for table in table_definitions.keys():
        create_table(cursor, table)
        
        if index_definitions.has_key(table):
            create_indexes(cursor, table)

################
# Main import procedure: Open database connection

import os
import atexit
import settings
import backups # Better to do backups before opening db

dbfile = settings.parser.get('DEFAULT', 'dbfile')
newfile = not os.path.isfile(dbfile) # Are we creating a new database?

if not newfile:
    backups.dobackup()
    backups.cleanbackups()

# Pysqlite2 is close enough to sqlite3 to work - plain sqlite is not
try:
    import sqlite3
except ImportError:
    import pysqlite2.dbapi2 as sqlite3

# Open db_conn that is imported to other modules
db_conn = sqlite3.connect(dbfile)

if newfile:
    cursor = db_conn.cursor()
    create_tables(cursor)
    db_conn.commit()

atexit.register(db_conn.commit)