315 lines
12 KiB
Python
315 lines
12 KiB
Python
import random
|
|
from MonetDB import *
|
|
from PostgreSQL import *
|
|
from datetime import datetime
|
|
|
|
|
|
#Bilioteques nécessaires (pour se connecter a monetDB et postgreeSQL
|
|
#pip install psycopg2
|
|
#pip install pymonetdb
|
|
|
|
|
|
#Monet DB
|
|
requeteSelect1 = """SELECT * FROM flights WHERE "AIRLINE" = 'AA';"""
|
|
requeteSelect2 = """SELECT * FROM flights;"""
|
|
|
|
requeteSELECT3 = """SELECT * FROM flights WHERE "YEAR" = 2015 AND "MONTH" = 12 AND "DAY" = 32 AND "DAY_OF_WEEK" = 4 AND "AIRLINE" = 'B6' AND "FLIGHT_NUMBER" = 839;"""
|
|
|
|
requeteSelect05 = """SELECT * FROM flights WHERE "DAY" > 5;"""
|
|
requeteSelect10 = """SELECT * FROM flights WHERE "DAY" > 10;"""
|
|
requeteSelect15 = """SELECT * FROM flights WHERE "DAY" > 15;"""
|
|
requeteSelect20 = """SELECT * FROM flights WHERE "DAY" > 20;"""
|
|
requeteSelect25 = """SELECT * FROM flights WHERE "DAY" > 25;"""
|
|
requeteSelect30 = """SELECT * FROM flights WHERE "DAY" > 30;"""
|
|
|
|
requeteSelect05_2 = """SELECT * FROM flights WHERE "DAY" > 5 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
|
|
requeteSelect10_2 = """SELECT * FROM flights WHERE "DAY" > 10 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
|
|
requeteSelect15_2 = """SELECT * FROM flights WHERE "DAY" > 15 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
|
|
requeteSelect20_2 = """SELECT * FROM flights WHERE "DAY" > 20 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
|
|
requeteSelect25_2 = """SELECT * FROM flights WHERE "DAY" > 25 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
|
|
requeteSelect30_2 = """SELECT * FROM flights WHERE "DAY" > 30 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
|
|
|
|
requeteSelect05_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 5;"""
|
|
requeteSelect10_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 10;"""
|
|
requeteSelect15_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 15;"""
|
|
requeteSelect20_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 20;"""
|
|
requeteSelect25_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 25;"""
|
|
requeteSelect30_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 30;"""
|
|
|
|
requeteCreateTable5 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 5;"""
|
|
requeteCreateTable10 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 10;"""
|
|
requeteCreateTable15 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 15;"""
|
|
requeteCreateTable20 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 20;"""
|
|
requeteCreateTable25 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 25;"""
|
|
requeteCreateTable30 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 30;"""
|
|
|
|
requeteDelete5 = """DELETE FROM flights WHERE "DAY" > 5;"""
|
|
requeteDelete10 = """DELETE FROM flights WHERE "DAY" > 10;"""
|
|
requeteDelete15 = """DELETE FROM flights WHERE "DAY" > 15;"""
|
|
requeteDelete20 = """DELETE FROM flights WHERE "DAY" > 20;"""
|
|
requeteDelete25 = """DELETE FROM flights WHERE "DAY" > 25;"""
|
|
requeteDelete30 = """DELETE FROM flights WHERE "DAY" > 30;"""
|
|
|
|
requeteInsert = """INSERT INTO flights SELECT * FROM flights2;"""
|
|
|
|
requeteCopyTable = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS;"""
|
|
requeteReplace = """UPDATE flights2 SET "WEATHER_DELAY" = -1 WHERE "WEATHER_DELAY" IS NULL;"""
|
|
|
|
requeteDropFlight2 = """DROP TABLE flights2;"""
|
|
|
|
|
|
|
|
rapport : str = ""
|
|
|
|
def initMonetDB():
|
|
global rapport
|
|
|
|
reset_data_monetdb()
|
|
time = create_table_monetdb()
|
|
result = "[MonetDB] create table: time = " + str(time) + " s \n"
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
time = load_data_monetdb()
|
|
result = "[MonetDB] load data : time = " + str(time) + " s \n"
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
|
|
|
|
|
|
def execMonetDB(str):
|
|
return requete_monetdb(str)
|
|
|
|
def readMonetDB():
|
|
execForMonetDB(5,[requeteSelect1])
|
|
execForMonetDB(5,[requeteSelect2])
|
|
|
|
execForMonetDB(5,[requeteSELECT3])
|
|
|
|
execForMonetDB(5,[requeteSelect05])
|
|
execForMonetDB(5,[requeteSelect10])
|
|
execForMonetDB(5,[requeteSelect15])
|
|
execForMonetDB(5,[requeteSelect20])
|
|
execForMonetDB(5,[requeteSelect25])
|
|
execForMonetDB(5,[requeteSelect30])
|
|
|
|
execForMonetDB(5,[requeteSelect05_2])
|
|
execForMonetDB(5,[requeteSelect10_2])
|
|
execForMonetDB(5,[requeteSelect15_2])
|
|
execForMonetDB(5,[requeteSelect20_2])
|
|
execForMonetDB(5,[requeteSelect25_2])
|
|
execForMonetDB(5,[requeteSelect30_2])
|
|
|
|
execForMonetDB(10,[requeteSelect05_3])
|
|
execForMonetDB(10,[requeteSelect10_3])
|
|
execForMonetDB(10,[requeteSelect15_3])
|
|
execForMonetDB(10,[requeteSelect20_3])
|
|
execForMonetDB(10,[requeteSelect25_3])
|
|
execForMonetDB(10,[requeteSelect30_3])
|
|
|
|
def readAndWriteMonetDB():
|
|
execForMonetDB(4,[requeteCreateTable5,requeteDelete5,requeteInsert,requeteDropFlight2])
|
|
execForMonetDB(4,[requeteCreateTable10,requeteDelete10,requeteInsert,requeteDropFlight2])
|
|
execForMonetDB(4,[requeteCreateTable15,requeteDelete15,requeteInsert,requeteDropFlight2])
|
|
execForMonetDB(4,[requeteCreateTable20,requeteDelete20,requeteInsert,requeteDropFlight2])
|
|
execForMonetDB(4,[requeteCreateTable25,requeteDelete25,requeteInsert,requeteDropFlight2])
|
|
execForMonetDB(4,[requeteCreateTable30,requeteDelete30,requeteInsert,requeteDropFlight2])
|
|
|
|
|
|
def randomWriteMonetDB(n):
|
|
global rapport
|
|
time = 0
|
|
min = 0
|
|
max = 0
|
|
for i in range(n):
|
|
requete = """INSERT INTO flights ("YEAR","MONTH","DAY","DAY_OF_WEEK","AIRLINE","FLIGHT_NUMBER","TAIL_NUMBER","ORIGIN_AIRPORT","DESTINATION_AIRPORT","SCHEDULED_DEPARTURE","DEPARTURE_TIME") VALUES ("""
|
|
requete += str(random.randint(2015,2016)) + "," + str(random.randint(1,12)) + "," + str(random.randint(1,31)) + "," + str(random.randint(1,7)) + ",'AA'," + str(random.randint(1,1000)) + ",'N1','JFK','LAX'," + str(random.randint(0,2400)) + "," + str(random.randint(0,2400)) + ");"
|
|
time += execMonetDB(requete)[0]
|
|
|
|
if(time<min or min == 0) :
|
|
min = time
|
|
if(time>max) :
|
|
max = time
|
|
time = time/n
|
|
result = "[MonetDB] requete (moyenne de " + str(n) +") : INSERT INTO flights random value \n\ttime = " + str(time) + " s" + "\n\tmin time = " + str(min) + "\n\tmax time = " + str(max)
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
|
|
def writeMonetDB():
|
|
randomWriteMonetDB(1000)
|
|
|
|
|
|
|
|
|
|
def runMonetDB():
|
|
|
|
readMonetDB()
|
|
|
|
readAndWriteMonetDB()
|
|
|
|
writeMonetDB()
|
|
|
|
|
|
|
|
# execute n fois les n requêtes dans l'ordre
|
|
def execForMonetDB(n, tabReq):
|
|
global rapport
|
|
t = [[0] * len(tabReq) for _ in range(4)]
|
|
for i in range(n) :
|
|
print(i+1,"/",n)
|
|
for j in range(len(tabReq)) :
|
|
v = execMonetDB(tabReq[j])
|
|
t[0][j] += v[0]
|
|
t[1][j] = v[1]
|
|
if(v[0]<t[2][j] or t[2][j] == 0) :
|
|
t[2][j] = v[0]
|
|
if(v[0]>t[3][j]) :
|
|
t[3][j] = v[0]
|
|
print("calcul en cours...")
|
|
|
|
for j in range(len(tabReq)):
|
|
t[0][j] = t[0][j]/n
|
|
result = "[MonetDB] requete (moyenne de " + str(n) +") : " + str(tabReq[j]) + "\n\ttime = " + str(t[0][j]) + " s\n\tsize=" + str(t[1][j]) + "\n\tmin time = " + str(t[2][j]) + "\n\tmax time = " + str(t[3][j])
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
|
|
|
|
def initPostgreSQL():
|
|
global rapport
|
|
reset_database_postgres()
|
|
time = create_tables_postgres()
|
|
result = "[PostgreSQL] create table: time = " + str(time) + " s\n"
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
time = create_data_postgres()
|
|
result = "[PostgreSQL] load data : time = " + str(time) + " s\n"
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
def initIndex():
|
|
global rapport
|
|
time = execPostgreSQL("""CREATE INDEX idx_day ON flights ("DAY");""")
|
|
result = "[PostgreSQL] create index : time = " + str(time) + " s\n"
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
def execPostgreSQL(str):
|
|
return requete_postgres(str)
|
|
|
|
def readPostgreSQL():
|
|
execForPostgreSQL(2,[requeteSelect1])
|
|
execForPostgreSQL(2,[requeteSelect2])
|
|
|
|
execForPostgreSQL(5,[requeteSELECT3])
|
|
|
|
execForPostgreSQL(2,[requeteSelect05])
|
|
execForPostgreSQL(2,[requeteSelect10])
|
|
execForPostgreSQL(2,[requeteSelect15])
|
|
execForPostgreSQL(2,[requeteSelect20])
|
|
execForPostgreSQL(2,[requeteSelect25])
|
|
execForPostgreSQL(3,[requeteSelect30])
|
|
|
|
execForPostgreSQL(2,[requeteSelect05_2])
|
|
execForPostgreSQL(2,[requeteSelect10_2])
|
|
execForPostgreSQL(2,[requeteSelect15_2])
|
|
execForPostgreSQL(2,[requeteSelect20_2])
|
|
execForPostgreSQL(2,[requeteSelect25_2])
|
|
execForPostgreSQL(2,[requeteSelect30_2])
|
|
|
|
execForPostgreSQL(5,[requeteSelect05_3])
|
|
execForPostgreSQL(5,[requeteSelect10_3])
|
|
execForPostgreSQL(5,[requeteSelect15_3])
|
|
execForPostgreSQL(5,[requeteSelect20_3])
|
|
execForPostgreSQL(5,[requeteSelect25_3])
|
|
execForPostgreSQL(5,[requeteSelect30_3])
|
|
|
|
def readAndWritePostgreSQL():
|
|
execForPostgreSQL(4,[requeteCreateTable5,requeteDelete5,requeteInsert,requeteDropFlight2])
|
|
execForPostgreSQL(4,[requeteCreateTable10,requeteDelete10,requeteInsert,requeteDropFlight2])
|
|
execForPostgreSQL(4,[requeteCreateTable15,requeteDelete15,requeteInsert,requeteDropFlight2])
|
|
execForPostgreSQL(4,[requeteCreateTable20,requeteDelete20,requeteInsert,requeteDropFlight2])
|
|
execForPostgreSQL(4,[requeteCreateTable25,requeteDelete25,requeteInsert,requeteDropFlight2])
|
|
execForPostgreSQL(4,[requeteCreateTable30,requeteDelete30,requeteInsert,requeteDropFlight2])
|
|
|
|
|
|
def randomWritePostgreSQL(n):
|
|
global rapport
|
|
time = 0
|
|
min = 0
|
|
max = 0
|
|
for i in range(n):
|
|
requete = """INSERT INTO flights ("YEAR","MONTH","DAY","DAY_OF_WEEK","AIRLINE","FLIGHT_NUMBER","TAIL_NUMBER","ORIGIN_AIRPORT","DESTINATION_AIRPORT","SCHEDULED_DEPARTURE","DEPARTURE_TIME") VALUES ("""
|
|
requete += str(random.randint(2015,2016)) + "," + str(random.randint(1,12)) + "," + str(random.randint(1,31)) + "," + str(random.randint(1,7)) + ",'AA'," + str(random.randint(1,1000)) + ",'N1','JFK','LAX'," + str(random.randint(0,2400)) + "," + str(random.randint(0,2400)) + ");"
|
|
time += execPostgreSQL(requete)[0]
|
|
if(time<min or min == 0) :
|
|
min = time
|
|
if(time>max) :
|
|
max = time
|
|
time = time/n
|
|
result = "[PostgreSQL] requete (moyenne de " + str(n) +") : INSERT INTO flights random value \n\ttime = " + str(time) + " s" + "\n\tmin time = " + str(min) + "\n\tmax time = " + str(max)
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
def writePostgreSQL():
|
|
randomWritePostgreSQL(1000)
|
|
|
|
|
|
|
|
|
|
def runPostgreSQL():
|
|
|
|
readPostgreSQL()
|
|
|
|
readAndWritePostgreSQL()
|
|
|
|
writePostgreSQL()
|
|
|
|
|
|
# execute n fois les n requêtes dans l'ordre
|
|
def execForPostgreSQL(n, tabReq):
|
|
global rapport
|
|
t = [[0] * len(tabReq) for _ in range(4)]
|
|
# avg nbResult min max
|
|
for i in range(n):
|
|
print(i+1,"/",n)
|
|
for j in range(len(tabReq)):
|
|
v = execPostgreSQL(tabReq[j])
|
|
t[0][j] += v[0]
|
|
t[1][j] = v[1]
|
|
if(v[0]<t[2][j] or t[2][j] == 0) :
|
|
t[2][j] = v[0]
|
|
if(v[0]>t[3][j]) :
|
|
t[3][j] = v[0]
|
|
print("calcul en cours...")
|
|
|
|
for j in range(len(tabReq)):
|
|
t[0][j] = t[0][j] / n
|
|
result = "[PostgreSQL] requete (moyenne de " + str(n) +") : " + str(tabReq[j]) + "\n\ttime = " + str(t[0][j]) + " s\n\tsize=" + str(t[1][j]) + "\n\tmin time = " + str(t[2][j]) + "\n\tmax time = " + str(t[3][j])
|
|
rapport = rapport + "\n" + result
|
|
print(result)
|
|
|
|
if __name__ == '__main__':
|
|
|
|
initMonetDB()
|
|
runMonetDB()
|
|
|
|
rapport += "\n"
|
|
|
|
initPostgreSQL()
|
|
#initIndex()
|
|
runPostgreSQL()
|
|
|
|
print("/////////////////////////////////////////////////////////////")
|
|
print(rapport)
|
|
print("/////////////////////////////////////////////////////////////")
|
|
|
|
date = datetime.now()
|
|
nom_fichier = "result" + date.strftime("_%d_%m_%y_%H_%M_%S") + ".txt" #un nom de fichier unique pour ne pas écraser les anciens résultats
|
|
with open(nom_fichier, 'w') as fichier:
|
|
fichier.write(rapport)
|
|
#
|