#!/usr/bin/env python

import sys
import sqlite3

SQL = '''
--- User ----------------------------------------------------------------------
Create Table User (
    Name            Not Null,
    DisplayName     Not Null,
    Password        Not Null,
    Created         Not Null Default Current_Timestamp
);

Create Unique Index UserName        On User (Name);
Create Index        UserDisplayName On User (DisplayName);

--- Session -------------------------------------------------------------------
Create Table Session (
    Token           Not Null,
    User_R          Not Null, --> User
    Created         Not Null Default Current_Timestamp,
    LastSeen        Not Null Default Current_Timestamp,

    Foreign Key (User_R) References User (Oid) On Delete Restrict
);

Create Unique Index SessionToken    On Session (Token);
Create Index        SessionUser_R   On Session (User_R);
'''

def main(argv):
    conn = sqlite3.connect(':memory:')
    conn.executescript(SQL)
    conn.execute("insert into user (name, password, displayname) values ('user1', 'pass1', 'User 1')")
    conn.execute("insert into user (name, password, displayname) values ('user2', 'pass2', 'User 2')")
    conn.commit()
    conn.execute("insert into session (token, user_r) values ('SESSION1', 1)")
    conn.commit()
    conn.execute('update session set lastseen = 1')
    r = conn.execute('select name, displayname from user')
    conn.commit()
    lr = list(r)
    print lr
    assert lr == [(u'user1', u'User 1'), (u'user2', u'User 2')], "Expected [(u'user1', u'User 1'), (u'user2', u'User 2')]"

if __name__ == '__main__':
    main(sys.argv)