daily pastebin goal
59%
SHARE
TWEET

Untitled

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