SHARE
TWEET

Untitled

a guest Jan 24th, 2020 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import json
  2. import uuid
  3. import hashlib
  4. from SPARQLWrapper import SPARQLWrapper, JSON, CSV
  5. from collections import Counter
  6.  
  7.  
  8. def get_preferences():
  9.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/")
  10.     query = """
  11.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  12.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  13.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  14.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  15.    PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
  16.  
  17.    SELECT ?label ?user_id ?views
  18.    WHERE {
  19.    ?db rdf:type preferences:Preference .
  20.     ?db preferences:database_id ?db_id .
  21.     ?db_id rdfs:label ?label .
  22.     ?db preferences:views_count ?views .
  23.    optional {
  24.      ?db preferences:users ?user .
  25.      ?user users:user_id ?user_id .
  26.    }
  27.     }
  28.    """
  29.  
  30.     fuseki_client.setQuery(query)
  31.     fuseki_client.method = "POST"
  32.     fuseki_client.setReturnFormat(CSV)
  33.     results = fuseki_client.query().convert()
  34.     prefs = {}
  35.  
  36.     for line in results.decode('utf-8').strip().split('\r\n')[1:]:
  37.         label, uid, views = line.split(',')
  38.         if label not in prefs:
  39.             prefs[label] = {
  40.                 'views': views,
  41.                 'user_ids': []
  42.             }
  43.         if len(uid) > 0:
  44.             prefs[label]['user_ids'].append(uid)
  45.  
  46.     return prefs
  47.  
  48.  
  49. def get_db_labels():
  50.     return [
  51.         "Oracle",
  52.         "ApacheJena",
  53.         "Cassandra",
  54.         "Cloudant",
  55.         "DB2",
  56.         "DynamoDB",
  57.         "HBase",
  58.         "Hive",
  59.         "Hypertable",
  60.         "Memchached",
  61.         "MySQL",
  62.         "Neo4j",
  63.         "MapR-DB",
  64.         "RavenDB",
  65.         "Redis",
  66.         "RiakKV",
  67.         "SQLite",
  68.         "Stardog",
  69.         "Titan",
  70.         "MongoDB"
  71.     ]
  72.  
  73.  
  74. def insert_preference(label, views_count=0):
  75.     supported_dbs = get_db_labels()
  76.     pref = get_preference(label)
  77.  
  78.     if label not in supported_dbs or pref is not None:
  79.         return False
  80.  
  81.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/update")
  82.  
  83.     query = """
  84.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  85.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  86.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  87.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  88.    PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
  89.  
  90.    INSERT {
  91.        preferences:%s rdf:type preferences:Preference ;
  92.        preferences:database_id ?x ;
  93.        preferences:views_count %d
  94.    } WHERE {
  95.        ?x rdfs:label "%s"
  96.    }
  97.    """ % (label, views_count, label)
  98.  
  99.     fuseki_client.setQuery(query)
  100.     fuseki_client.queryType = "INSERT"
  101.     fuseki_client.method = "POST"
  102.     fuseki_client.query()
  103.     return True
  104.  
  105.  
  106. def delete_preference(label):
  107.     if label not in get_db_labels():
  108.         return False
  109.  
  110.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/update")
  111.  
  112.     query = """
  113.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  114.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  115.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  116.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  117.  
  118.    DELETE WHERE {
  119.    preferences:%s a preferences:Preference ;
  120.    ?property ?value
  121.    }
  122.    """ % label
  123.  
  124.     fuseki_client.setQuery(query)
  125.     fuseki_client.queryType = "DELETE"
  126.     fuseki_client.method = "POST"
  127.     fuseki_client.query()
  128.     return True
  129.  
  130.  
  131. def get_preference(label):
  132.     if label not in get_db_labels():
  133.         return None
  134.  
  135.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/")
  136.  
  137.     query = """
  138.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  139.    PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
  140.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  141.  
  142.    SELECT ?prefid ?dbid ?ctr ?uid
  143.    WHERE {
  144.        ?prefid a preferences:Preference ;
  145.        preferences:database_id ?dbid ;
  146.        preferences:views_count ?ctr .
  147.        optional {
  148.            ?prefid preferences:users ?user .
  149.            ?user users:user_id ?uid .
  150.        }
  151.        ?dbid rdfs:label "%s" .
  152.    }
  153.    """ % label
  154.  
  155.     fuseki_client.setQuery(query)
  156.     fuseki_client.method = "POST"
  157.     fuseki_client.setReturnFormat(CSV)
  158.     results = fuseki_client.query().convert()
  159.     pref = {}
  160.  
  161.     for idx, line in enumerate(results.decode('utf-8').strip().split('\r\n')[1:]):
  162.         pref_url, db_url, ctr, uid = line.split(',')
  163.         if idx == 0:
  164.             pref['preference'] = pref_url
  165.             pref['database'] = db_url
  166.             pref['views_count'] = ctr
  167.             pref['user_ids'] = [uid] if len(uid) > 0 else []
  168.         else:
  169.             pref['user_ids'].append(uid)
  170.  
  171.     if len(pref) == 0:
  172.         return None
  173.  
  174.     return pref
  175.  
  176.  
  177. def add_user_preference(preference_label, user_id):
  178.     insert_preference(preference_label)
  179.  
  180.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/update")
  181.  
  182.     query = """
  183.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  184.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  185.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  186.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  187.    PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
  188.  
  189.    INSERT {
  190.        preferences:%s preferences:users ?s .
  191.    }
  192.    WHERE {
  193.        ?s rdf:type users:User .  
  194.        ?s users:user_id "%s" .
  195.    }
  196.    """ % (preference_label, user_id)
  197.  
  198.     fuseki_client.setQuery(query)
  199.     fuseki_client.queryType = "INSERT"
  200.     fuseki_client.method = "POST"
  201.     fuseki_client.query()
  202.     return True
  203.  
  204.  
  205. def remove_user_preference(preference_label, user_id):
  206.     if preference_label not in get_db_labels():
  207.         return False
  208.  
  209.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/update")
  210.  
  211.     query = """
  212.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  213.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  214.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  215.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  216.  
  217.    DELETE {
  218.        preferences:%s preferences:users ?x
  219.    }
  220.    WHERE {
  221.        ?x users:user_id "%s"
  222.    }
  223.    """ % (preference_label, user_id)
  224.  
  225.     fuseki_client.setQuery(query)
  226.     fuseki_client.queryType = "DELETE"
  227.     fuseki_client.method = "POST"
  228.     fuseki_client.query()
  229.  
  230.     return True
  231.  
  232.  
  233. def update_preference(label, views_count):
  234.     if label not in get_db_labels():
  235.         return False
  236.  
  237.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/v2/")
  238.  
  239.     query = """
  240.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  241.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  242.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  243.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  244.  
  245.    DELETE WHERE {
  246.        preferences:%s preferences:views_count ?x
  247.    }
  248.    """ % label
  249.  
  250.     fuseki_client.setQuery(query)
  251.     fuseki_client.queryType = "DELETE"
  252.     fuseki_client.method = "POST"
  253.     fuseki_client.query()
  254.  
  255.     query = """
  256.    PREFIX users: <http://www.semanticweb.org/ontologies/users#>
  257.    PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
  258.    PREFIX owl: <http://www.w3.org/2002/07/owl#>
  259.    PREFIX preferences: <http://www.semanticweb.org/milut/ontologies/preferences#>
  260.  
  261.    INSERT DATA {
  262.        preferences:%s preferences:views_count %d
  263.    }
  264.    """ % (label, views_count)
  265.  
  266.     fuseki_client.setQuery(query)
  267.     fuseki_client.queryType = "INSERT"
  268.     fuseki_client.method = "POST"
  269.     fuseki_client.query()
  270.  
  271.     return True
  272.  
  273.  
  274. def get_stats1(prefs):
  275.  
  276.     ctr = {}
  277.  
  278.     for key in prefs:
  279.         ctr[key] = int(prefs[key].get('views'))
  280.  
  281.     sum_all = sum(ctr.values())
  282.  
  283.     for key in ctr:
  284.         ctr[key] = int(10000 * ctr[key] / sum_all) / 100
  285.  
  286.     result = {}
  287.     for k, v in sorted(list(ctr.items()), key=lambda  x: x[1], reverse=True)[:4]:
  288.         result[k] = v
  289.  
  290.     result['Others'] = int((100 - sum(result.values())) * 100) / 100
  291.  
  292.     return result
  293.  
  294.  
  295. def get_ststs2():
  296.     all_preferences = get_preferences
  297.     labels = get_db_labels()
  298.     print(labels.keys())
  299.  
  300.     prefs = {}
  301.  
  302.     # for label in labels:
  303.     #     programming_languages = get_programming_languages(label)
  304.     #     if programming_languages is None:
  305.     #         continue
  306.     #     for language in programming_languages:
  307.     #         language_str = language[0]
  308.     #         if not language_str in prefs:
  309.     #             prefs[language_str] = 1
  310.     #         else:
  311.     #             prefs[language_str] += 1
  312.     #
  313.     #
  314.     # sum_all = sum(prefs.values())
  315.     #
  316.     # for key in prefs:
  317.     #     prefs[key] = int(10000 * prefs[key] / sum_all) / 100
  318.     #
  319.     # result = {}
  320.     # for k, v in sorted(list(prefs.items()), key=lambda  x: x[1], reverse=True)[:4]:
  321.     #     result[k] = v
  322.     #
  323.     # result['Others'] = int((100 - sum(result.values())) * 100) / 100
  324.     #
  325.     #
  326.     # return result
  327.  
  328.  
  329. def get_programming_languages(label):
  330.     fuseki_client = SPARQLWrapper("http://ec2-54-93-236-36.eu-central-1.compute.amazonaws.com:3030/dbs")
  331.  
  332.     query = """
  333.        PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
  334.        PREFIX db:<http://www.semanticweb.org/ontologies/databases/tbox/>
  335.        SELECT ?name
  336.               WHERE {
  337.                     ?x a db:DatabaseManagementSystem  .
  338.                     ?x rdfs:label "%s"@en ;                        
  339.                    db:supportsProgrammingLanguage  ?prg .
  340.                    ?prg rdfs:label ?name . }
  341.          """ %label
  342.  
  343.     fuseki_client.setQuery(query)
  344.     fuseki_client.method = "POST"
  345.     fuseki_client.setReturnFormat(CSV)
  346.     results = fuseki_client.query().convert()
  347.     pref = []
  348.  
  349.     for idx, line in enumerate(results.decode('utf-8').strip().split('\r\n')[1:]):
  350.         name = line.split(',')
  351.         pref.append(name)
  352.  
  353.     if len(pref) == 0:
  354.         return None
  355.  
  356.     return pref
  357.  
  358. # def get_stats2(prefs):
  359.  
  360. def tests():
  361.     print(get_preferences())
  362.     pass
  363.     # print(get_preferences())
  364.  
  365.     # print(delete_preference("MongoDb"))
  366.  
  367.     # print(get_preference('HBase'))
  368.  
  369.     # print(insert_preference('Redis', 8))
  370.  
  371.     # print(add_user_preference('Oracle', '38325f1f-3176-40b0-a3bf-2b653c9f3c8d'))
  372.  
  373.     # print(remove_user_preference('Oracle', '38325f1f-3176-40b0-a3bf-2b653c9f3c8d'))
  374.  
  375.     # print(update_preference('Cassandra', 25))
  376.  
  377.     # print(get_stats1())
  378.  
  379. tests()
  380. def lambda_handler(event, context):
  381.     prefs = get_preferences()
  382.     stats1 = get_stats1(prefs)
  383.     # stats2 = get_stats2(prefs)
  384.     return {
  385.         "statusCode": 200,
  386.         "headers" : {"Access-Control-Allow-Origin":"*"},
  387.         "body": json.dumps(stats1, indent = 2)}
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