Author: ymartel Date: 2012-05-24 18:05:32 +0200 (Thu, 24 May 2012) New Revision: 544 Url: http://nuiton.org/repositories/revision/sandbox/544 Log: Add folder for scripts around DataBase SQL generation and perf test Added: scripts/generateSQL4Perfdb/ scripts/generateSQL4Perfdb/dbTableGenerator.py Removed: scripts/dbTableGenerator.py Deleted: scripts/dbTableGenerator.py =================================================================== --- scripts/dbTableGenerator.py 2012-05-24 15:26:37 UTC (rev 543) +++ scripts/dbTableGenerator.py 2012-05-24 16:05:32 UTC (rev 544) @@ -1,103 +0,0 @@ -# Generate a SQL file with 1.000.000 entries for a (not yet) customized table -# This program should ask user to have number of String/Test Column, Date Column -# or Numeric column -#!/usr/bin/python -# -*- coding: utf-8 -*- -from datetime import date, datetime -import random, string, sys, time - -#maybe manage String as a table of max lenght ? stringColumns = [50, 10, 23] ? -stringColumns = 13 -textColumns = 0 -#think about bigint ? -integerColumns = 5 -floatColumns = 1 -dateColumns = 2 -#have you seen blob ? - -#prepare the SQL file -filename = "perfotest1M.sql" -file = open(filename, "w") - -#Create the table -database_name = "PerfoTest" -createStatement = "create table " + database_name + " (\n" -createStatement += "\tid\t\tvarchar(255)" -create_index = "\nCREATE INDEX id_idx ON " + database_name + " (id);" - -for n in range (stringColumns) : - column_name = "stringcol" + str(n + 1) - createStatement += ",\n\t" + column_name + "\t\tvarchar(255)" - create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ - + " (" + column_name + ");" - -for n in range (textColumns) : - column_name = "textcol" + str(n + 1) - createStatement += ",\n\t" + column_name + "\t\ttext" - create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ - + " (" + column_name + ");" - -for n in range (integerColumns) : - column_name = "integercol" + str(n + 1) - createStatement += ",\n\t" + column_name + "\t\tinteger" - create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ - + " (" + column_name + ");" - -for n in range (floatColumns) : - column_name = "numericcol" + str(n + 1) - createStatement += ",\n\t" + column_name + "\t\tnumeric" - create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ - + " (" + column_name + ");" - -for n in range (dateColumns) : - column_name = "datecol" + str(n + 1) - createStatement += ",\n\t" + column_name + "\t\ttimestamp" - create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ - + " (" + column_name + ");" - -createStatement += "\n);" - -#print createStatement -file.writelines("START TRANSACTION;\n") -file.writelines(createStatement) -file.writelines(create_index) - -#Add entries now! Groovy baby -print "c'est parti mon kiki! " + str(time.strftime("%H:%M:%S")) -query = "\n\n" -for row in range(1000000): - #ready ? - query_row = "INSERT INTO PerfoTest VALUES (" + "'monsuperIdOfTheDead" + str(row) +"'" - - for n in range (stringColumns) : - query_row += ", '" + ''.join(random.choice(string.digits + \ - string.letters) for i in xrange(random.randint(1, 255))) + "'" - - for n in range (textColumns) : - query_row += ", '" + ''.join(random.choice(string.letters) for i in \ - xrange(random.randint(1, 700))) + "'" - - for n in range (integerColumns) : - query_row += ", " + str(random.randint(1, 2147483647)) - - for n in range (floatColumns) : - query_row += ", " + str(random.uniform(1, 10) * random.randint(1, 10000)) - - for n in range (dateColumns) : - start_date = date.today().replace(day=1, month=1).toordinal() - end_date = date.today().toordinal() - random_day = date.fromordinal(random.randint(start_date, end_date)) - query_row += ", '" + str(datetime(*(random_day.timetuple())[:6])) + "'" - - query += query_row + ");\n" - - #A little commit! - if (row % 100000 == 0) : - query += "COMMIT;\nSTART TRANSACTION;\n" - -#print query -file.writelines(query) -file.writelines("COMMIT;\n") - -file.close() -print "Generation effectuee, finie a " + str(time.strftime("%H:%M:%S")) Copied: scripts/generateSQL4Perfdb/dbTableGenerator.py (from rev 543, scripts/dbTableGenerator.py) =================================================================== --- scripts/generateSQL4Perfdb/dbTableGenerator.py (rev 0) +++ scripts/generateSQL4Perfdb/dbTableGenerator.py 2012-05-24 16:05:32 UTC (rev 544) @@ -0,0 +1,103 @@ +# Generate a SQL file with 1.000.000 entries for a (not yet) customized table +# This program should ask user to have number of String/Test Column, Date Column +# or Numeric column +#!/usr/bin/python +# -*- coding: utf-8 -*- +from datetime import date, datetime +import random, string, sys, time + +#maybe manage String as a table of max lenght ? stringColumns = [50, 10, 23] ? +stringColumns = 13 +textColumns = 0 +#think about bigint ? +integerColumns = 5 +floatColumns = 1 +dateColumns = 2 +#have you seen blob ? + +#prepare the SQL file +filename = "perfotest1M.sql" +file = open(filename, "w") + +#Create the table +database_name = "PerfoTest" +createStatement = "create table " + database_name + " (\n" +createStatement += "\tid\t\tvarchar(255)" +create_index = "\nCREATE INDEX id_idx ON " + database_name + " (id);" + +for n in range (stringColumns) : + column_name = "stringcol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\tvarchar(255)" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (textColumns) : + column_name = "textcol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\ttext" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (integerColumns) : + column_name = "integercol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\tinteger" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (floatColumns) : + column_name = "numericcol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\tnumeric" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +for n in range (dateColumns) : + column_name = "datecol" + str(n + 1) + createStatement += ",\n\t" + column_name + "\t\ttimestamp" + create_index += "\nCREATE INDEX " + column_name + "_idx ON " + database_name \ + + " (" + column_name + ");" + +createStatement += "\n);" + +#print createStatement +file.writelines("START TRANSACTION;\n") +file.writelines(createStatement) +file.writelines(create_index) + +#Add entries now! Groovy baby +print "c'est parti mon kiki! " + str(time.strftime("%H:%M:%S")) +query = "\n\n" +for row in range(1000000): + #ready ? + query_row = "INSERT INTO PerfoTest VALUES (" + "'monsuperIdOfTheDead" + str(row) +"'" + + for n in range (stringColumns) : + query_row += ", '" + ''.join(random.choice(string.digits + \ + string.letters) for i in xrange(random.randint(1, 255))) + "'" + + for n in range (textColumns) : + query_row += ", '" + ''.join(random.choice(string.letters) for i in \ + xrange(random.randint(1, 700))) + "'" + + for n in range (integerColumns) : + query_row += ", " + str(random.randint(1, 2147483647)) + + for n in range (floatColumns) : + query_row += ", " + str(random.uniform(1, 10) * random.randint(1, 10000)) + + for n in range (dateColumns) : + start_date = date.today().replace(day=1, month=1).toordinal() + end_date = date.today().toordinal() + random_day = date.fromordinal(random.randint(start_date, end_date)) + query_row += ", '" + str(datetime(*(random_day.timetuple())[:6])) + "'" + + query += query_row + ");\n" + + #A little commit! + if (row % 100000 == 0) : + query += "COMMIT;\nSTART TRANSACTION;\n" + +#print query +file.writelines(query) +file.writelines("COMMIT;\n") + +file.close() +print "Generation effectuee, finie a " + str(time.strftime("%H:%M:%S"))