import sqlite3

import time



import ajc



db_one = sqlite3.connect('sep.one.sqlite3')

cu_one = db_one.cursor()

cu_one.execute("CREATE TABLE IF NOT EXISTS my_table (fid INTEGER, key TEXT, value TEXT, PRIMARY KEY (fid, key))")

db_two = sqlite3.connect('sep.two.sqlite3')

cu_two = db_two.cursor()

cu_two.execute("CREATE TABLE IF NOT EXISTS my_table (fid INTEGER, key TEXT, type TEXT, value TEXT, PRIMARY KEY (fid, key))")



N = 100000



def i1():

    ajc.util.time_start(msg = '1 column insert')

    for i in range(N): 

        cu_one.execute('INSERT OR REPLACE INTO my_table (fid, key, value) VALUES (?,?,?)', (i, 'a_key', 'i' + str(i),))     

        cu_one.execute('INSERT OR REPLACE INTO my_table (fid, key, value) VALUES (?,?,?)', (i, 'b_key', 'i' + str(4),))     

    db_one.commit()

    ajc.util.time_stop()



def i2():

    ajc.util.time_start(msg = '2 column insert')

    for i in range(N): 

        cu_two.execute('INSERT OR REPLACE INTO my_table (fid, key, type, value) VALUES (?,?,?,?)', (i, 'a_key', 'i', i,))     

        cu_two.execute('INSERT OR REPLACE INTO my_table (fid, key, type, value) VALUES (?,?,?,?)', (i, 'b_key', 'i', 4,))     

    db_two.commit()

    ajc.util.time_stop()



def e1():

    ajc.util.time_start(msg = '1 column query equals')

    for row in cu_one.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND value = 'i4'"):

        fid = row[0]

    db_one.commit()

    ajc.util.time_stop()



def e2():

    ajc.util.time_start(msg = '2 column query equals')

    for row in cu_two.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND type = 'i' AND value = '4'"):

        fid = row[0]

    db_two.commit()

    ajc.util.time_stop()



def l1():

    ajc.util.time_start(msg = '1 column query less than')

    for row in cu_one.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND substr(value,1,1) = 'i' AND 1*substr(value,2) < 75000"):

        fid = row[0]

        #print "A", fid

    db_one.commit()

    ajc.util.time_stop()



def l2():

    ajc.util.time_start(msg = '2 column query less than')

    for row in cu_two.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND type = 'i' AND 1*value < 75000"):

        fid = row[0]

        #print "B", fid

    db_two.commit()

    ajc.util.time_stop()



i1()

i2()

i2()

i2()

i1()

i1()

i2()

i1()



e2()

e1()

e2()

e2()

e1()

e1()

e2()

e1()



l2()

l1()

l2()

l2()

l1()

l1()

l2()

l1()