#!/usr/local/bin/python
import pg
import sys
import _mysql
from time import strftime
import sqlite3
import random

# INSERT TEST START HERE
# PosgreSQL test
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("PosgreSQL start time " + time + "\n")
con2 = pg.connect(dbname='test', host='localhost', user='username')

for i in range(1, 1000000):
	con2.query("INSERT INTO perf2 (name) VALUES ('ttest" + str(i) + "')");

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("PosgreSQL end time " + time + "\n")

# MySQL test
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("MySQL start time " + time + "\n")
conn = _mysql.connect('localhost', 'root', '', 'test')
for i in range(1, 1000000):
	conn.query("INSERT INTO perf (name) VALUES ('ttest" + str(i) + "')");
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("MySQL end time " + time + "\n")

# SQLite test
con = sqlite3.connect("test.db")
cursor = con.cursor()

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("SQLite start time " + time + "\n")
for i in range(1, 1000000):
	cursor.execute("INSERT INTO perf (name) VALUES ('ttest" + str(i) + "')");
con.commit()
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("SQLite end time " + time + "\n")

# INSERT TEST END

# UPDATE TEST STARTS HERE
# PosgreSQL test
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("PosgreSQL start time " + time + "\n")
con2 = pg.connect(dbname='test', host='localhost', user='username')

# 647787 is a start offset because it not start with 1 id
for i in range(1, 1000000):
	con2.query("UPDATE perf2 SET name = 'a_" + str(i) + "' WHERE id = " + str(647787+i));

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("PosgreSQL end time " + time + "\n")

# MySQL test
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("MySQL start time " + time + "\n")
conn = _mysql.connect('localhost', 'root', '', 'test')
for i in range(1, 1000000):
	conn.query("UPDATE perf SET name = 'a_" + str(i) + "' WHERE id = " + str(i));
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("MySQL end time " + time + "\n")

# SQLite test
con = sqlite3.connect("test.db")
cursor = con.cursor()

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("SQLite start time " + time + "\n")
for i in range(1, 1000000):
	cursor.execute("UPDATE perf SET name = 'a_" + str(i) + "' WHERE id = " + str(i));
con.commit()
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("SQLite end time " + time + "\n")
# UPDATE TEST ENDS HERE

# SELECT TEST STARTS HERE 
# PosgreSQL test
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("PosgreSQL start time " + time + "\n")
con2 = pg.connect(dbname='test', host='localhost', user='username')

for i in range(1, 300):
	start = random.randint(1, 999999)
	end   = random.randint(start, 999999)
	con2.query("SELECT * FROM perf2 WHERE id > " + str(start) + " AND id < " + str(end));

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("PosgreSQL end time " + time + "\n")

# MySQL test
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("MySQL start time " + time + "\n")
conn = _mysql.connect('localhost', 'root', '', 'test')
for i in range(1, 300):
	start = random.randint(1, 999999)
	end   = random.randint(start, 999999)
	conn.query("SELECT * FROM perf WHERE id > " + str(start) + " AND id < " + str(end) + ";");
	r=conn.store_result()

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("MySQL end time " + time + "\n")

# SQLite test
con = sqlite3.connect("test.db")
cursor = con.cursor()

time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("SQLite start time " + time + "\n")
for i in range(1, 100000):
	start = random.randint(1, 999999)
	end   = random.randint(start, 999999)
	cursor.execute("SELECT * FROM perf WHERE id > " + str(start) + " AND id < " + str(end));
	con.commit()
time = strftime("%Y-%m-%d %H:%M:%S")
sys.stderr.write("SQLite end time " + time + "\n")