SHARE
TWEET

Untitled

a guest Jun 12th, 2019 99 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import psycopg2
  2. import json
  3. import sys
  4. import bcrypt
  5. inFile = sys.argv[1]
  6.  
  7. git_exit = """{"status": "OK"}"""
  8. bad_exit = """{"status": "ERROR"}"""
  9.  
  10. connected = 0
  11. good=0
  12. inita = 0
  13.  
  14. def connect():
  15.     global connected
  16.     connected =1
  17.  
  18.  
  19. #Sprawdza czy x podane z wejscia jest tym samym co z hashowanym hasłem w bazie
  20. def check_password(x,y):
  21.     return bcrypt.checkpw(x,y)
  22.    
  23. #Funkcja służaca do sprawdzenia czy w tabeli allid nie znajduje się id które chcemy dodać
  24. def checker_allid(id):
  25.     cursor.execute("""SELECT * from AllId;""")
  26.     x=cursor.fetchall()
  27.     for i in x:
  28.         if id in i:
  29.             return False
  30.     return True
  31.  
  32. #Obsługuje wszystkie zapytania  
  33. def which_query(line):
  34.     global good,inita
  35.     good=1
  36.     if 'open' in line:
  37.         if line['open']['password'] == 'qwerty':
  38.             connect()
  39.             if line['open']['login'] == 'init':
  40.                 init()
  41.                 inita=1
  42.         else:
  43.             good = 0
  44.     if connected == 1 and inita ==1:
  45.         if 'leader' in line:
  46.             member(line['leader'],1)
  47.     elif connected ==1 and inita ==0:
  48.         if 'protest' in line:
  49.             action(line['protest'],"protest")
  50.         if 'support' in line:
  51.             action(line['support'],"support")
  52.         if "downvote" in line:
  53.             vote(line["downvote"],"downvote")
  54.         if 'upvote' in line:
  55.             vote(line['upvote'],'upvote')
  56.         if 'actions' in line:
  57.             actions(line['actions'])
  58.         if 'projects' in line:
  59.             projects(line["projects"])
  60.         if 'votes' in line:
  61.             votes(line['votes'])
  62.         if 'trolls' in line:
  63.             trolls(line['trolls'])    
  64.     else :
  65.         good =0
  66. #Wypisuje trolli (funkcja api)
  67. def trolls(query):
  68.  
  69.     global good
  70.     good=2
  71.     # todo trzeba dodać najpierw wszystkie votes_For
  72.     q=(""" Select * from (Select member, SUM(votes_for) as a, SUM(votes_against) as b, """+
  73.     "Case WHEN " + str(query["timestamp"]) + """ - Members.timestamp < 31536000 Then 'true' else 'false' END """+
  74.     """from Actions join Members ON(Actions.member_id=member)  Group by member) as foo  where b > a Order by b-a DESC, member ASC;""")
  75.     cursor.execute(q)
  76.     x=cursor.fetchall()
  77.     print("""{"status": "OK",
  78.     "data": """,x, "}")
  79. #Sprawdza czy member jest liderem
  80. def checker_isleader(id):
  81.     cursor.execute("Select member,is_leader from Members where member = " + str(id) +" and "
  82.     + "is_leader = 1" +";")
  83.     x=cursor.fetchall()
  84.     if x == []:
  85.         return False
  86.     return True
  87. #Wypisuje akcje (funkcja api)
  88. def actions(query):
  89.     global good
  90.     cursor.execute("""SELECT member,password,timestamp,is_leader FROM Members ;""")
  91.     x=cursor.fetchall()  
  92.     mem_leader_exist =0
  93.     for i in x :
  94.         if query['member'] in i : # czy istnieje
  95.             if checker_isleader(query["member"]):
  96.                 if check_password(query["password"],i[1]): # dobre haslo  
  97.                     if query['timestamp'] - i[2] < 31536000 : # czy zamrozona
  98.                         mem_leader_exist=1
  99.  
  100.     if mem_leader_exist ==1 :
  101.         good=2
  102.         if 'type' in query and 'project' in query and 'authority' in query:
  103.            
  104.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  105.              "ON(Actions.project_id = Projects.id) where type = " + "'"+str(query['type']) +"' and "+
  106.              " project_id = " + str(query["project"])+ " and authorityid =" +str(query["authority"])+" ORDER By Actions.Id ; ")
  107.             cursor.execute(q)
  108.             x=cursor.fetchall()
  109.             print("""{"status": "OK",
  110.     "data": """,x, "}")
  111.         elif 'project' in query and 'type' in query:
  112.            
  113.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  114.              "ON(Actions.project_id = Projects.id) where project_id = " + str(query["project"]) +
  115.              " and type = " + "'"+str(query['type']) +"'"+" ORDER By Actions.Id ;")
  116.             cursor.execute(q)
  117.             x=cursor.fetchall()
  118.             print("""{"status": "OK",
  119.     "data": """,x, "}")
  120.         elif 'project' in query and 'authority' in query:
  121.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  122.              "ON(Actions.project_id = Projects.id) where project_id = " + str(query["project"]) +
  123.              " and authority = " + str(query['authority']) +" ORDER By Actions.Id ;")
  124.             cursor.execute(q)
  125.             x=cursor.fetchall()
  126.             print("""{"status": "OK",
  127.     "data": """,x, "}")
  128.         elif 'type' in query and 'authority' in query :
  129.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  130.              "ON(Actions.project_id = Projects.id) where authority = " + str(query["authority"]) +
  131.              " and type = " + "'"+str(query['type']) +"'"+" ORDER By Actions.Id ;")
  132.             cursor.execute(q)
  133.             x=cursor.fetchall()
  134.             print("""{"status": "OK",
  135.     "data": """,x, "}")
  136.         elif 'project' in query:
  137.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  138.              "ON(Actions.project_id = Projects.id) where project_id = " + str(query["project"]) +
  139.             " ORDER By Actions.Id ;")
  140.             cursor.execute(q)
  141.             x=cursor.fetchall()
  142.             print("""{"status": "OK",
  143.     "data": """,x, "}")
  144.         elif 'type' in query:
  145.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  146.              "ON(Actions.project_id = Projects.id) where " +
  147.              " type = " + "'"+str(query['type']) +"'"+" ORDER By Actions.Id ;")
  148.             cursor.execute(q)
  149.             x=cursor.fetchall()
  150.             print("""{"status": "OK",
  151.     "data": """,x, "}")
  152.         elif 'authority' in query:
  153.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  154.              "ON(Actions.project_id = Projects.id) where authorityid =" +str(query["authority"]) + "  ORDER By Actions.Id;")
  155.             cursor.execute(q)
  156.             x=cursor.fetchall()
  157.             print("""{"status": "OK",
  158.     "data": """,x, "}")
  159.         else:
  160.             q=("SELECT Actions.id,type,project_id,authorityid,votes_for,votes_against from Actions join Projects " +
  161.              "ON(Actions.project_id = Projects.id) ORDER By Actions.Id ;")
  162.             cursor.execute(q)
  163.             x=cursor.fetchall()
  164.             print("""{"status": "OK",
  165.     "data": """,x, "}")  
  166.         update_timestamp(query)
  167.     else:
  168.         good = 0
  169. #Wypisuje projekty(funkcja api)
  170. def projects(query):
  171.     global good
  172.     cursor.execute("""SELECT member,password,timestamp,is_leader FROM Members ;""")
  173.     x=cursor.fetchall()  
  174.     mem_leader_exist =0
  175.    
  176.     for i in x :
  177.         if query['member'] in i : # czy istnieje
  178.             if checker_isleader(query["member"]): # czy to jest lider
  179.                 if check_password(query["password"],i[1]): # dobre haslo              
  180.                     if query['timestamp'] - i[2] < 31536000 : # czy zamrozony                  
  181.                         mem_leader_exist=1
  182.     if mem_leader_exist ==1 :
  183.         good =2
  184.         if 'authority' in query:
  185.             q=("SELECT * from  Projects " +
  186.                 " where authorityid =" +str(query["authority"]) + "  ORDER By id asc ;")
  187.         else :
  188.             q=("SELECT * from  Projects   ORDER By id asc;")
  189.         cursor.execute(q)
  190.         x=cursor.fetchall()
  191.         print("""{"status": "OK",
  192.     "data": """,x, "}")
  193.        
  194.         update_timestamp(query)
  195.     else :
  196.         good = 0
  197.  
  198. #Dodawanie projektu do bazy danych
  199. def project_add(query):
  200.     global good
  201.     if checker_allid(query["authority"]):
  202.         all_id(query["authority"])
  203.     if checker_allid(query["project"]):
  204.         q= (""" INSERT INTO Projects (id,authorityid) VAlues ( """ +  str(query["project"]) +", "
  205.          + str(query["authority"])+ ');')    
  206.         cursor.execute(q)
  207.         all_id(query["project"])
  208.     else:
  209.         good=0
  210. #Dodawania głosów
  211. def up_or_downvote(query,t):
  212.  
  213.     global good
  214.     cursor.execute("Select member_id,action_id from Votes WHERE member_id = "+str(query['member']) +
  215.     " and " + "action_id = " +str(query['action']) +";")
  216.     x=cursor.fetchall()
  217.     if x == []:
  218.         good =1
  219.         if t == "downvote":
  220.             q=(""" Update Actions SET votes_against = votes_against+1 """
  221.             + "Where id = " + str(query['action']) + ";")  
  222.             e=(""" Insert into votes (action_id,member_id,voted_against) Values ( """
  223.             + str(query['action']) +", "+str(query['member'])+", " + "1" + ");")    
  224.         else:
  225.             q=(""" Update Actions SET votes_for = votes_for+1 """
  226.             + "Where id = " + str(query['action']) + ";" )
  227.             e=(""" Insert into votes (action_id,member_id,voted_for) Values ( """
  228.             + str(query['action']) +", "+str(query['member'])+", " + "1" + ");")
  229.        
  230.         cursor.execute(q)
  231.         cursor.execute(e)
  232.         update_timestamp(query)  
  233.     else:
  234.         good = 0
  235.  
  236. #Wypisuje głosy funkcja (api)
  237. def votes(query):
  238.     global good
  239.     cursor.execute("""SELECT member,password,timestamp,is_leader FROM Members ;""")
  240.     x=cursor.fetchall()  
  241.     mem_leader_exist =0
  242.    
  243.     for i in x :
  244.         if query['member'] in i : # czy istnieje
  245.             if checker_isleader(query["member"]):
  246.                if check_password(query["password"],i[1]): # dobre haslo                
  247.                     if query['timestamp'] - i[2] < 31536000 : # czy zamrozona                    
  248.                         mem_leader_exist=1
  249.     if mem_leader_exist:
  250.         good=2
  251.        
  252.         if 'action' in query:
  253.            
  254.             #tutaj mam problem z zapytaniem
  255.             q=("SELECT member,SUM(coalesce(voted_for,0)) as a,SUM(coalesce(voted_against,0)) as b "
  256.             + " from Members left JOIN Votes on (Members.member=Votes.member_id) "+
  257.             "where action_id = " +str(query["action"])+" GROUP BY member Order by member;")    
  258.                
  259.             cursor.execute(q)
  260.             x=cursor.fetchall()
  261.  
  262.         elif 'project' in query:
  263.            
  264.             q=("SELECT member,SUM(coalesce(voted_for,0)) as a,SUM(coalesce(voted_against,0)) as b  "
  265.             +"from Members LEFT JOIN Votes on (Members.member=Votes.member_id) "
  266.              +" Join   Actions On(Actions.id=Votes.action_id)"+
  267.             " where project_id = " +str(query["project"])+
  268.             " GROUP BY member Order by member;")
  269.             cursor.execute(q)
  270.             x=cursor.fetchall()
  271.            
  272.         else:
  273.            
  274.             q=("SELECT member,SUM(coalesce(voted_for,0)) as a,SUM(coalesce(voted_against,0)) as b "
  275.             +"from Members LEFT JOIN Votes on (Members.member=Votes.member_id)  Group by member Order by member;")
  276.             cursor.execute(q)
  277.             x=cursor.fetchall()
  278.            
  279.         print("""{"status": "OK",
  280.         "data": """,x, "}")
  281.         update_timestamp(query)
  282.     else:
  283.         good =0
  284.  
  285. #Funkcja która wstępnie przeprowadza dodania głosu robi wszystkie checki
  286. def vote(query,t):
  287.     global good
  288.     cursor.execute("""SELECT member,password,timestamp FROM Members ;""")
  289.     x=cursor.fetchall()  
  290.    
  291.     error=0
  292.     mem_exist=0
  293.    
  294.     for i in x :
  295.         if query['member'] in i : # czy istnieje
  296.             mem_exist=1
  297.             if check_password(query["password"],i[1]): # dobre haslo  
  298.                 if query['timestamp'] - i[2] < 31536000 : # czy zamrozona
  299.                     error=0
  300.                 else:
  301.                     error=1
  302.             else:
  303.                 error=1
  304.     if error == 0:
  305.         if mem_exist == 0:  #nie istnieje osoba ktora chce zrobic akcje wiec ją tworzymy
  306.             member(query,0)  # 0 bo nie lider
  307.         cursor.execute("""SELECT id from Actions ;""")
  308.  
  309.         x=cursor.fetchall()
  310.         action_exist = 0
  311.         for i in x:
  312.             if query['action'] in i:
  313.                 up_or_downvote(query,t)
  314.                 action_exist = 1    
  315.         #jeśli nie istnieje to go tworzymy
  316.         if action_exist == 0:
  317.             good =0
  318.     else:
  319.         good =0
  320.  
  321. # Updatowanie timestampu
  322. def update_timestamp(query):
  323.     cursor.execute("""Update Members Set timestamp = """ + str(query["timestamp"]) + " WHERE member = " + str(query["member"]) +";")
  324. #Dodawanie akcji protestu lub supportu do bazy danych
  325. def add_protest_or_support(query,t):
  326.     global good
  327.     #dodajemy akcje  supportu lub protestu
  328.     if checker_allid(query["action"]):
  329.         q=("""INSERT INTO Actions (id,project_id,member_id,type,timestamp) Values
  330.             ( """ + str(query['action']) + ', '+ str(query["project"])+", "
  331.              + str(query["member"]) +", "+"'"+t+"'"+ ", " +str(query["timestamp"]) +
  332.              ");") #Insertujemy akcje po sprawdzeniu wszystkiego
  333.         cursor.execute(q)
  334.         all_id(query["action"])
  335.         update_timestamp(query)
  336.     else :
  337.         good=0
  338. #Funkcja która wstępnie przeprowadza dodania akcji robi wszystkie checki
  339. def action(query,t):
  340.     global good
  341.     cursor.execute("""SELECT member,password,timestamp FROM Members ;""")
  342.     x=cursor.fetchall()  
  343.     error=0
  344.     mem_exist=0
  345.     for i in x :
  346.         if query['member'] in i : # czy istnieje
  347.             mem_exist=1
  348.             if check_password(query["password"],i[1]): # dobre haslo  
  349.                 if query['timestamp'] - i[2] < 31536000 : # czy zamrozona    
  350.                     error=0
  351.                 else:
  352.                     error=1
  353.             else:
  354.                 error=1              
  355.     if error == 0:
  356.         if mem_exist == 0:  #nie istnieje osoba ktora chce zrobic akcje wiec ją tworzymy
  357.             member(query,0)  # 0 bo nie lider
  358.  
  359.        
  360.         cursor.execute("""SELECT * From Projects ;""")
  361.         x=cursor.fetchall()
  362.        
  363.         project_exist = 0
  364.         for i in x:
  365.             if query['project'] in i:
  366.                 add_protest_or_support(query,t)
  367.                 project_exist = 1    
  368.  
  369.         #jeśli nie istnieje to go tworzymy
  370.         if project_exist == 0:
  371.             project_add(query)
  372.             add_protest_or_support(query,t)
  373.     else:
  374.         good = 0
  375.                                                    
  376. #Dodawanie id do tabelu allid
  377. def all_id(id):
  378.     cursor.execute("""INSERT INTO AllId (id) VALUES ( """ + str (id) + """ ) ;""")
  379.  
  380. #Dodawanie membera do bazy danych
  381. def member(dict,n):
  382.     global good
  383.     x=bcrypt.hashpw(str(dict['password']),bcrypt.gensalt())
  384.     if checker_allid(dict["member"]):
  385.         query= ("""INSERT INTO Members (timestamp,password,member,is_leader) VALUES ( """ + "'" + str((dict['timestamp']))
  386.         + "'" + """, """ +  "'" + x + "'" + """, """ + str(dict['member'])
  387.         + """ , """ + str(n) + """); """)
  388.        
  389.         cursor.execute(query)
  390.         all_id(dict["member"])
  391.     else:
  392.         good =0
  393. # Zapytania SQL budujące naszę tabele
  394. def init():  
  395.     f=open("projekt.sql")
  396.     full_sql=f.read()
  397.     sql_commands = full_sql.replace('\n', '').split(';')[:-1] # this bring just [] which is  empty
  398.     for x in sql_commands:
  399.      q=str(x)+ " ;"
  400.      cursor.execute(q)
  401.    
  402. inputowa=[]
  403. #Czytanie z plików wejścia i jego obłsugif check_password(query["password"],i[1]): # dobre haslo  a
  404. with open(inFile,'r') as f:
  405.     for line in f:
  406.         inputowa.append(json.loads(line))
  407.  
  408. if 'open' in inputowa[0]:
  409.     if 'init' == inputowa[0]["open"]['login']:
  410.         connect_str = "dbname='student' user='postgres' host='localhost' " + \
  411.             "password='haslo' "
  412.  
  413.     elif 'app' == inputowa[0]["open"]['login']:
  414.         if  inputowa[0]["open"]['password'] == 'qwerty':
  415.             print("Test appa")
  416.             connect_str = "dbname='student' user='" + str(inputowa[0]['open']['login']) + "' host='localhost' " + \
  417.             "password='qwerty' "
  418.      
  419. conn=psycopg2.connect(connect_str)
  420. cursor = conn.cursor()
  421. for line in inputowa:
  422.     which_query(line)
  423.     if good == 1:
  424.         print(git_exit)
  425.     elif good == 0:
  426.         print(bad_exit)
  427.        
  428.  
  429. conn.commit()
  430. cursor.close()
  431. conn.close()
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