#test-p.py
from pysqlite2 import dbapi2 as sqlite
import os,time

VALUE = 'ABCDEFGHI'

try:
    os.remove('ptest.sqlite')
except:
    pass
con = sqlite.connect('ptest.sqlite')
cur = con.cursor()
cur.execute('create table file_audio (fileid integer primary key,artist text,album text,track text,title,genre text)')
cur.execute('create table file_audio_attr (fileid integer,attrid integer,value text)')
cur.execute('create table file_attr (fileid integer,attrid integer,value text)')
cur.execute('create index ix_file_attr on file_attr (fileid)')
cur.execute('create index ix_file_audio_attr on file_audio_attr (fileid)')
for i in xrange(1000):
    #6 attributes, 5 in main table 1 extra
    cur.execute("insert into file_audio values(null,?,?,?,?,?)",5*[VALUE])
    cur.execute("insert into file_audio_attr values(?,?,?)",(i,0,VALUE))
    #6 attributes,all in 1 table
    for attrid in xrange(6):
        cur.execute("insert into file_audio_attr values(?,?,?)",(i,attrid,VALUE))
con.commit()

one = 0.0
two = 0.0
for i in xrange(20):
    start = time.time()
    for i in xrange(50,60):
        cur.execute('select artist,album,track,title,genre from file_audio where fileid=?',(i,))
        cur.fetchall()
        cur.execute('select attrid,value from file_audio_attr where fileid=?',(i,))
        cur.fetchall()
    two+= time.time() - start

    start = time.time()
    for i in xrange(50,60):
        cur.execute('select attrid,value from file_attr where fileid=?',(i,))
        cur.fetchall()
    one += time.time() - start

print 'two-select on 5,1',two
print 'one-select on 6',one