table_definitions = {
'groups': 'groupid INTEGER, section INTEGER, pen INTEGER, PRIMARY KEY (groupid)',
'animals': 'animalid INTEGER, groupid INTEGER, alive INTEGER, notes TEXT, PRIMARY KEY (animalid)',
'feeds': 'feedid INTEGER, timestamp timestamp, groupid INTEGER, animalid INTEGER, amount REAL, PRIMARY KEY (feedid)',
'notices': 'noticeid INTEGER, timestamp timestamp, animalid INTEGER, data TEXT, PRIMARY KEY (noticeid)'
}

index_definitions = {
'animals': ['groupid'],
'feeds': ['timestamp', 'animalid'],
'notices': ['timestamp', 'animalid']
}

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)

def patch_sqlite():
    """Compatibility code for datetime support in older sqlite versions.
    Adapted from http://www.pollenation.net/journal/matt/97"""
    
    import datetime
    import sqlite
    import sqlite.main
    
    def adapt_datetime(val):
        return val.isoformat(" ")
    
    def convert_timestamp(val):
        datepart, timepart = val.split(" ")
        year, month, day = map(int, datepart.split("-"))
        timepart_full = timepart.split(".")
        hours, minutes, seconds = map(int, timepart_full[0].split(":"))
        if len(timepart_full) == 2:
            microseconds = int(float("0." + timepart_full[1]) * 1000000)
        else:
            microseconds = 0

        val = datetime.datetime(year, month, day, hours, minutes, seconds, microseconds)
        return val
    
    # Tell sqlite it didn't really find mx installed ;-)
    sqlite.main.have_datetime = False
    
    # Replace sqlite's _quote function to include the standard datetime type.
    def quote(oldfn):
        def _(value):
            if isinstance(value, datetime.datetime):
                return "'%s'" % adapt_datetime(value)
            return oldfn(value)
        return _
    sqlite.main._quote = quote(sqlite.main._quote)
    
    # Replace sqlite's connect to add the datetime converter
    def connect(oldfn):
        def _(*args, **kwargs):
            kwargs.setdefault('converters', {}).update({'timestamp': convert_timestamp})
            return oldfn(*args, **kwargs)
        return _
    sqlite.connect = connect(sqlite.connect)

################
# 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()

# Open db_conn that is imported to other modules

try:
    import sqlite3
    db_conn = sqlite3.connect(dbfile, detect_types = sqlite3.PARSE_DECLTYPES)
except ImportError:
    import sqlite
    patch_sqlite()
    db_conn = sqlite.connect(dbfile)

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

atexit.register(db_conn.commit)