Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import random
- from timeit import default_timer as timer
- from py2neo import Graph
- from neo4j.v1 import GraphDatabase, basic_auth
- from statistics import mean
- import psycopg2
- noOfLoops = 3
- threshold = 5
- sourceId = -1
- targetId = -1
- A_B_FROM_EDGES = "SELECT * from nodesH a join nodesH b on a.id_prac = b.id_szef where b.id_prac = 9"
- MIN_DISTANCE_BETWEEN2 = "WITH RECURSIVE transitive_closure(a, b, distance, path_string) " \
- "AS( SELECT a, b, 1 AS distance, a || '.' || b || '.' " \
- "AS path_string FROM edges{size} " \
- "UNION ALL " \
- "SELECT tc.a, e.b, tc.distance + 1, tc.path_string || e.b || '.' " \
- "AS path_string FROM edges{size} AS e " \
- "JOIN transitive_closure " \
- "AS tc ON e.a = tc.b " \
- "WHERE tc.path_string NOT LIKE '%' || e.b || '.%') " \
- "SELECT a, b, min(distance) AS dist FROM transitive_closure" \
- " WHERE a = {source} AND b={target} " \
- "GROUP BY a, b ORDER BY a, dist, b;"
- COMMON_FRIENDS_ON_LEVEL = "WITH RECURSIVE transitive_closure(a, b, distance, path_string) " \
- "AS( SELECT a, b, 1 AS distance, a || '.' || b || '.' " \
- "AS path_string, b AS direct_connection FROM edges{size} " \
- "WHERE a = {source} " \
- "" \
- "UNION ALL " \
- "" \
- "SELECT tc.a, e.b, tc.distance + 1, tc.path_string || e.b || '.' " \
- "AS path_string, tc.direct_connection FROM edges{size} " \
- "AS e JOIN transitive_closure AS tc ON e.a = tc.b " \
- "WHERE tc.path_string NOT LIKE '%' || e.b || '.%' AND tc.distance < {level}) " \
- "SELECT * FROM transitive_closure WHERE b={target} ORDER BY a,b,distance"
- FIND_CHIEF = "WITH RECURSIVE menu_tree(id_prac, url, level, id_szef) " \
- "AS ( SELECT id_prac, '' || id_prac, 0, id_szef " \
- "FROM nodes{size} WHERE id_szef = 0 " \
- "UNION ALL " \
- "SELECT mn.id_prac, mt.url || '/' || mn.id_prac, mt.level + 1, mt.id_prac " \
- "FROM nodes{size} mn, menu_tree mt " \
- "WHERE mn.id_szef = mt.id_prac)" \
- "" \
- " SELECT * FROM menu_tree ORDER BY level, id_szef;"
- FIND_TOO_BIG_SETS = "SELECT a.id_prac, count(*) from nodes{size} a join nodes{size} b on a.id_prac = b.id_szef " \
- "GROUP BY (a.id_prac) HAVING count(*) > {level};"
- GET_RANDOM_NODE = "SELECT id_prac FROM nodes{size} " \
- "ORDER BY RANDOM() LIMIT 1"
- MATCH_N = "MATCH (n:man)-[r:podwladny*]->(m:man) return n"
- NEO_SHORTEST_PATH = "MATCH (user1:User { id:1 }),(user2:User { id:10001 }), " \
- "p = shortestPath((user1)-[*..15]-(user2)) RETURN p"
- NEO_FIND_CHIEF = "match (n) where (n)-[:IS_FRIEND]->(n) return n"
- NEO_BIG_TEAMS = "match (x)<-[]-(n) with x, count(n) as big where big > 40 return x,big order by big desc"
- NEO_COMMON_FRIENDS = "MATCH (user1:User { id:1 }),(user2:User { id:1001 }), p = (user1)-[*..15]-(user2) " \
- "UNWIND nodes(p) as common return distinct common"
- def main():
- cursor, conn = connectToPgDb()
- driver = connectToNeoDb()
- getRandomNodes(cursor)
- start = "INSERT INTO EXPERIMENTS(id, instancesize, data, source, target) VALUES(nextval('experimentId'), %s, now(), %s, %s)"
- cursor.execute(start, (instance_size, sourceId, targetId))
- # SQLQuery(MIN_DISTANCE_BETWEEN2.format(source = sourceId, target= targetId, size = instance_size), cursor, 'FIND_PATH')
- #SQLQuery(COMMON_FRIENDS_ON_LEVEL, cursor, 'COMMON')
- #SQLQuery(FIND_CHIEF, cursor, 'FIND_CHIEF')
- NeoQuery(MATCH_N, driver, 'FIND_CHIEF', cursor)
- #SQLQuery(FIND_TOO_BIG_SETS, cursor, 'TOO_BIG')
- conn.commit()
- conn.close()
- def SQLQuery(query, cursor, title):
- print(title)
- totalTime = 0
- for i in range(noOfLoops):
- print(i + 1, '/', noOfLoops, end=' | ')
- start = timer()
- cursor.execute(query.format(source = sourceId, target= targetId, size = instance_size, level=threshold ))
- records = cursor.fetchall()
- end = timer()
- totalTime += end - start
- print('\nResult : ', len(records), ' rows')
- final_result = totalTime / noOfLoops
- cursor.execute("Select max(id) from experiments")
- maxId = cursor.fetchall()
- insertSql = "INSERT INTO EXPERIMENTINSTANCES(experimentid, exptype, duration, database_model) " \
- "VALUES(%s, %s, %s, 'SQL')"
- cursor.execute(insertSql, (maxId[0][0], title, final_result,))
- def NeoQuery(query, driver, title, cursor):
- print(title)
- totalTime = 0
- for i in range(noOfLoops):
- print(i + 1, '/', noOfLoops, end=' | ')
- start = timer()
- records = driver.evaluate(query.format(source = sourceId, target= targetId, size = instance_size, level=threshold ))
- end = timer()
- totalTime += end - start
- print('\nResult : ', len(records), ' rows')
- final_result = totalTime / noOfLoops
- cursor.execute("Select max(id) from experiments")
- maxId = cursor.fetchall()
- insertSql = "INSERT INTO EXPERIMENTINSTANCES(experimentid, exptype, duration, database_model) " \
- "VALUES(%s, %s, %s, 'SQL')"
- cursor.execute(insertSql, (maxId[0][0], title, final_result,))
- def connectToPgDb():
- conn_string = "host='localhost' dbname='postgres' user='postgres' password='asdmn11'"
- # print the connection string we will use to connect
- print("Connecting to database")
- # get a connection, if a connect cannot be made an exception will be raised here
- conn = psycopg2.connect(conn_string)
- conn.commit()
- # conn.cursor will return a cursor object, you can use this cursor to perform queries
- cursor = conn.cursor()
- return cursor, conn
- def connectToNeoDb():
- driver = GraphDatabase.driver("bolt://localhost:7687", auth=basic_auth("neo4j", "guwno"))
- return driver;
- def askForInstanceSize():
- selected_instance = input("Select size of instance:\n"
- "x 6\n" \
- "a 50\n" \
- "b 10000\n" \
- "c 100000\n" \
- "d 500000\n\n" \
- "Selected : ")
- choices = {'a': 50, 'b': 10000, 'c': 100000, 'd': 50000, 'x': 6}
- return choices.get(selected_instance, 'default')
- def getRandomNodes(cursor):
- global sourceId, targetId
- sourceId = getRandomNode(cursor)
- targetId =getRandomNode(cursor)
- while sourceId == targetId:
- targetId = getRandomNode(cursor)
- print('Your random selected nodes are : sourceId ', sourceId, ' targetId ', targetId)
- def getRandomNode(cursor):
- cursor.execute(GET_RANDOM_NODE.format(size = instance_size))
- tempResult = cursor.fetchall()
- result = tempResult[0][0]
- return result
- if __name__ == "__main__":
- instance_size = askForInstanceSize()
- main()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement