SHARE
TWEET

Untitled

a guest May 31st, 2017 75 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import random
  2. from timeit import default_timer as timer
  3. from py2neo import Graph
  4. from neo4j.v1 import GraphDatabase, basic_auth
  5. from statistics import mean
  6.  
  7. import psycopg2
  8.  
  9. noOfLoops = 3
  10. threshold = 5
  11. sourceId = -1
  12. targetId = -1
  13. ####################################################################################3
  14.  
  15. PG_SHORTEST_PATH = "SELECT * FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost " \
  16.                    "FROM edge_table',{source}, {target})"
  17.  
  18. PG_COMMON_FRIENDS_ON_LEVEL = "WITH RECURSIVE transitive_closure(source, target, distance, path_string) " \
  19.                           "AS( SELECT source, target, 1 AS distance, source || '.' || target || '.' " \
  20.                           "AS path_string, target AS direct_connection FROM edge_table " \
  21.                           "WHERE source= {source} " \
  22.                           "" \
  23.                           "UNION ALL " \
  24.                           "" \
  25.                           "SELECT tc.source, e.target, tc.distance + 1, tc.path_string || e.target || '.' " \
  26.                           "AS path_string, tc.direct_connection FROM edge_table " \
  27.                           "AS e JOIN transitive_closure AS tc ON e.source = tc.target " \
  28.                           "WHERE tc.path_string NOT LIKE '%' || e.target || '.%' AND tc.distance < {level}) " \
  29.                           "SELECT * FROM transitive_closure WHERE target={target} ORDER BY source,source,distance"
  30.  
  31.  
  32.  
  33. PG_FIND_CHIEF = "WITH RECURSIVE menu_tree(id_prac, url, level, id_szef) " \
  34.              "AS ( SELECT id_prac, '' || id_prac, 0, id_szef " \
  35.              "FROM nodesBuf  WHERE id_szef = 0 " \
  36.              "UNION ALL " \
  37.              "SELECT mn.id_prac, mt.url || '/' || mn.id_prac, mt.level + 1, mt.id_prac " \
  38.              "FROM nodesBuf mn, menu_tree mt " \
  39.              "WHERE mn.id_szef = mt.id_prac)" \
  40.              "" \
  41.              " SELECT * FROM menu_tree ORDER BY level, id_szef;"
  42. PG_BIG_TEAMS = "SELECT a.id_prac, count(*) from nodesBuf a join nodesBuf b on a.id_prac = b.id_szef  " \
  43.                     "GROUP BY (a.id_prac) HAVING count(*) > {big};"
  44.  
  45.  
  46. ####################################################################################3
  47.  
  48. NEO_SHORTEST_PATH = "MATCH (user1:User { id:1 }),(user2:User { id:1001 }), " \
  49.                     "p = shortestPath((user1)-[*..15]-(user2)) RETURN p"
  50.  
  51. NEO_FIND_CHIEF = "match (n) where (n)-[:IS_FRIEND]->(n) return n"
  52.  
  53. NEO_BIG_TEAMS = "match (x)<-[]-(n) with x, count(n) as big where big > 40 return x,big order by big desc"
  54.  
  55. NEO_COMMON_FRIENDS = "MATCH (user1:User { id:1 }),(user2:User { id:1001 }), " \
  56.                      "p = (user1)-[*..15]-(user2) " \
  57.                      "UNWIND nodes(p) as common return distinct common"
  58.  
  59. ##############################################################################3
  60.  
  61. GET_RANDOM_NODE = "SELECT id_prac FROM nodesBuf " \
  62.                   "ORDER BY RANDOM() LIMIT 1"
  63.  
  64.  
  65. ##############################################################################3
  66.  
  67. def main():
  68.     cursor, conn = connectToPgDb()
  69.     driver = connectToNeoDb()
  70.  
  71.     getRandomNodes(cursor)
  72.  
  73.     start = "INSERT INTO EXPERIMENTS(id, instancesize, data, source, target)" \
  74.             " VALUES(nextval('experimentId'), %s, now(), %s, %s)"
  75.     cursor.execute(start, (instance_size, sourceId, targetId))
  76.  
  77.     SQLQuery(PG_SHORTEST_PATH, cursor, 'SHORTEST_PATH')
  78.     SQLQuery(PG_COMMON_FRIENDS_ON_LEVEL, cursor, 'COMMON_FRIENDS_ON_LEVEL')
  79.     SQLQuery(PG_FIND_CHIEF, cursor, 'FIND_CHIEF')
  80.     SQLQuery(PG_BIG_TEAMS, cursor, 'BIG_TEAMS')
  81.  
  82.     NeoQuery(NEO_SHORTEST_PATH, driver, 'SHORTEST_PATH', cursor)
  83.     NeoQuery(NEO_COMMON_FRIENDS, driver, 'COMMON_FRIENDS_ON_LEVEL', cursor)
  84.     NeoQuery(NEO_FIND_CHIEF, driver, 'FIND_CHIEF', cursor)
  85.     NeoQuery(NEO_BIG_TEAMS, driver, 'BIG_TEAMS', cursor)
  86.  
  87.     conn.commit()
  88.     conn.close()
  89.  
  90.  
  91. def SQLQuery(query, cursor, title):
  92.     print('SQL ' + title)
  93.  
  94.     totalTime = 0
  95.     for i in range(noOfLoops):
  96.         print(i + 1, '/', noOfLoops, end=' | ')
  97.         start = timer()
  98.  
  99.         cursor.execute(query.format(source = sourceId, target= targetId, size = instance_size,
  100.                                     level=threshold, big = 40))
  101.  
  102.         records = cursor.fetchall()
  103.         end = timer()
  104.         totalTime += end - start
  105.  
  106.     print('\nResult : ', len(records), ' rows')
  107.  
  108.     final_result = totalTime / noOfLoops
  109.     cursor.execute("Select max(id) from experiments")
  110.     maxId = cursor.fetchall()
  111.  
  112.     insertSql = "INSERT INTO EXPERIMENTINSTANCES(experimentid, exptype, duration, database_model) " \
  113.                 "VALUES(%s, %s, %s, 'SQL')"
  114.     cursor.execute(insertSql, (maxId[0][0], title, final_result,))
  115.  
  116.  
  117.  
  118. def NeoQuery(query, driver, title, cursor):
  119.     print('NEO ' + title)
  120.  
  121.     totalTime = 0
  122.     values = []
  123.     for i in range(noOfLoops):
  124.         print(i + 1, '/', noOfLoops, end=' | ')
  125.         start = timer()
  126.         session = driver.session()
  127.  
  128.         #print (query.format(source = sourceId, target= targetId, size = instance_size,level=threshold, big = 40))
  129.         result = session.run(query).consume()
  130.         values.append(result.result_consumed_after)
  131.         session.close()
  132.  
  133.         end = timer()
  134.         totalTime += end - start
  135.  
  136.     print('\nResult : ', len(values), ' rows')
  137.  
  138.     final_result = totalTime / noOfLoops
  139.     cursor.execute("Select max(id) from experiments")
  140.     maxId = cursor.fetchall()
  141.  
  142.     insertSql = "INSERT INTO EXPERIMENTINSTANCES(experimentid, exptype, duration, database_model) " \
  143.                 "VALUES(%s, %s, %s, 'NEO')"
  144.     cursor.execute(insertSql, (maxId[0][0], title, final_result,))
  145.  
  146. def connectToPgDb():
  147.     conn_string = "host='localhost' dbname='postgres' user='postgres' password='password'"
  148.     # print the connection string we will use to connect
  149.     print("Connecting to database")
  150.     # get a connection, if a connect cannot be made an exception will be raised here
  151.     conn = psycopg2.connect(conn_string)
  152.     conn.commit()
  153.     # conn.cursor will return a cursor object, you can use this cursor to perform queries
  154.     cursor = conn.cursor()
  155.     return cursor, conn
  156.  
  157. def connectToNeoDb():
  158.     driver =  GraphDatabase.driver("bolt://localhost:7687", auth=basic_auth("neo4j", "guwno"))
  159.     return driver;
  160.  
  161. def askForInstanceSize():
  162.     selected_instance = input("Select size of instance:\n"
  163.                               "x 6\n" \
  164.                               "a 50\n" \
  165.                               "b 10000\n" \
  166.                               "c 100000\n" \
  167.                               "d 500000\n\n" \
  168.                               "Selected : ")
  169.     choices = {'a': 50, 'b': 10000, 'c': 100000, 'd': 50000, 'x': 6}
  170.     return choices.get(selected_instance, 'default')
  171.  
  172. def getRandomNodes(cursor):
  173.     global sourceId, targetId
  174.  
  175.     sourceId = getRandomNode(cursor)
  176.  
  177.     targetId =getRandomNode(cursor)
  178.     while sourceId == targetId:
  179.         targetId = getRandomNode(cursor)
  180.     print('Your random selected nodes are : sourceId ', sourceId, ' targetId ', targetId)
  181.  
  182. def getRandomNode(cursor):
  183.     cursor.execute(GET_RANDOM_NODE.format(size = instance_size))
  184.     tempResult = cursor.fetchall()
  185.     result = tempResult[0][0]
  186.     return result
  187.  
  188. if __name__ == "__main__":
  189.     instance_size = askForInstanceSize()
  190.  
  191.     main()
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top