def test_where_in(self):
print("\nTest -> SELECT * FROM CORP WHERE *col* IN *(values)*")
columns = table_columns[0]
types = table_column_types[0]
string_cols = [columns[i] for i in range(len(types))
if types[i] == 1043]
string_cols *= 10
fuzz2 = []
for i in range(len(string_cols)):
x = randop.create_random_int(0, 15)
fuzz2.append((randop.create_random_string(x), randop.create_random_string(x)))
for i in range(len(string_cols)):
query = "SELECT * FROM CORP WHERE {} IN {}".format(string_cols[i], fuzz2[i])
print("\t{}".format(query))
test_obj.run_query(query)
def create_sql_in_withindex():
name = randop.create_random_string(6)
sql = "SELECT * from COMPANY where id IN (SELECT id FROM CORP where name = 'Paul')"
return sql
def create_sql_in_noanyindex():
name = randop.create_random_string(6)
sql = "SELECT * from COMPANY where age IN (SELECT age FROM CORP where name = 'Paul')"
return sql
def generate_clause(columns, col_type, rules):
# check col_type and set columns
if col_type == "VarChar":
columns = columns[0]
elif col_type == "Int":
columns = columns[1]
# check if performing a logical generation
conjunction = False
disjunction = False
"""
if rules[-1] == 'AND' and len(rules) != 1:
rules = rules[:-1]
conjunction = True
elif rules[-1] == 'OR' and len(rules) != 1:
rules = rules[:-1]
disjunction = True
"""
if col_type == "VarChar":
for x in columns:
for y in rules:
query_formatting = [x, y]
if y == 'IN':
temp_string = randop.create_random_string(
randop.create_random_int(1, 20))
query_formatting.append("(" + temp_string + ")")
elif conjunction:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
query_formatting.append('AND')
query_formatting.append(columns[randop.create_random_int(
0,
len(columns) - 1)])
second_operator = rules[randop.create_random_int(
0,
len(rules) - 1)]
if second_operator == 'IN':
temp_string = randop.create_random_string(
randop.create_random_int(1, 20))
query_formatting.append("(" + temp_string + ")")
else:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
elif disjunction:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
query_formatting.append('OR')
query_formatting.append(columns[randop.create_random_int(
0,
len(columns) - 1)])
second_operator = rules[randop.create_random_int(
0,
len(rules) - 1)]
if second_operator == 'IN':
temp_string = randop.create_random_string(
randop.create_random_int(1, 20))
query_formatting.append("(" + temp_string + ")")
else:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
else:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
# create the return string
return_string = ""
for i in range(len(query_formatting)):
if i > 0:
return_string += " " + query_formatting[i]
else:
return_string += query_formatting[i]
yield return_string
elif col_type == "Int":
for x in columns:
for y in rules:
query_formatting = [x, y]
if conjunction:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
query_formatting.append('AND')
query_formatting.append(columns[randop.create_random_int(
0,
len(columns) - 1)])
second_operator = rules[randop.create_random_int(
0,
len(rules) - 1)]
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
elif disjunction:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
query_formatting.append('OR')
query_formatting.append(columns[randop.create_random_int(
0,
len(columns) - 1)])
second_operator = rules[randop.create_random_int(
0,
len(rules) - 1)]
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
else:
query_formatting.append(
randop.create_random_string(
randop.create_random_int(1, 20)))
# create the return string
return_string = ""
for i in range(len(query_formatting)):
if i > 0:
return_string += " " + query_formatting[i]
else:
return_string += query_formatting[i]
yield return_string
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) \
VALUES (2, 'Allen', 25, 'Texas')");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) \
VALUES (3, 'Teddy', 23, 'Norway')");
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) \
VALUES (4, 'Mark', 25, 'Rich-Mond ')");
conn.commit()
print "Data inserted successfully"
except:
print "Can not INSERT data"
try:
for rowkey in range(5, 10001):
name = randop.create_random_string(6)
age = randop.create_random_int(1,80)
address = randop.create_random_string(12)
query = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS) VALUES (%s, %s, %s, %s);"
data = (rowkey, name, age, address)
print (query)
print (data)
cur.execute(query, data)
conn.commit()
print "Batch insert successfully"
except:
print "Error in batch insert"
conn.close()
def create_sql_in_withindex():
name = randop.create_random_string(6)
sql = "SELECT * from COMPANY where id IN (SELECT id FROM CORP where name = 'Paul')"
return sql
def create_sql_in_noanyindex():
name = randop.create_random_string(6)
sql = "SELECT * from COMPANY where age IN (SELECT age FROM CORP where name = 'Paul')"
return sql
def test_select_random(self):
query = "SELECT {0}{1}{2} FROM CORP WHERE {3} {4} {5}"
if debug:
print("\n{}".format(breakLine))
print("Test -> {}\n".format(query))
# this covers combinations for {0}{1}{2}
col_combinations = []
combinations_cols = []
for x in range(len(table_columns[0])):
col_combinations.append(itertools.combinations(
table_columns[0], x))
for x in range(1, len(col_combinations)):
for comb in col_combinations[x]:
if x != len(col_combinations) - 1:
comb += ('', ) * ((len(col_combinations) - 1) - x)
combinations_cols.append(comb)
# this covers options for {3}
# made global string_cols and string_ints
# this covers options for {4}
combinations_cols4 = [op for op in operators]
combinations_cols4_more = [op for op in operators_more]
# this covers options for {5}
random_strings = []
random_ints = []
random_floats = []
for i in range(100):
random_strings += [
randop.create_random_string(randop.create_random_int(1, 20))
]
random_ints += [randop.create_random_int(-100, 100)]
random_floats += [randop.create_random_float(-100, 100)]
# query loop
iterations = 10
while iterations:
iterations -= 1
query = "SELECT {0}{1}{2} FROM CORP WHERE {3} {4} {5}"
# obtain indexes
index_cols = randop.create_random_int(0,
len(combinations_cols) - 1)
index_cols3_string = randop.create_random_int(
0,
len(string_cols) - 1)
index_cols3_int = randop.create_random_int(0, len(int_cols) - 1)
index_cols4 = randop.create_random_int(0,
len(combinations_cols4) - 1)
index_cols4_more = randop.create_random_int(
0,
len(combinations_cols4_more) - 1)
index_strings = randop.create_random_int(0,
len(random_strings) - 1)
index_ints = randop.create_random_int(0, len(random_ints) - 1)
index_floats = randop.create_random_int(0, len(random_floats) - 1)
# obtain formatting values
zero = combinations_cols[index_cols][0]
one = combinations_cols[index_cols][1]
if one:
one = "," + one
two = combinations_cols[index_cols][2]
if two:
two = "," + two
three_string = string_cols[index_cols3_string]
three_int = int_cols[index_cols3_int]
four = combinations_cols4[index_cols4]
four_string = combinations_cols4_more[index_cols4_more]
string = "('" + random_strings[index_strings] + "')"
int_var = random_ints[index_ints]
float_var = random_floats[index_floats]
# string query
query_string = query.format(zero, one, two, three_string,
four_string, string)
if debug:
print("\t{}".format(query_string))
test_obj.run_query(query_string)
query_int = query.format(zero, one, two, three_int, four, int_var)
if debug:
print("\t{}".format(query_int))
test_obj.run_query(query_int)
query_float = query.format(zero, one, two, three_int, four,
float_var)
if debug:
print("\t{}\n".format(query_float))
test_obj.run_query(query_float)