API tools faq
paste
Guest User

Untitled

a guest
Sep 25th, 2018
109
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.24 KB | None | 0 0
raw download clone embed print report
  1. import re
  2. import sys
  3. import json
  4. from flask import request, abort
  5. from werkzeug.exceptions import BadRequest
  6.  
  7. from gran.main.lib.ext_filter_sql import _validate_crit, extFilterGetSQL, prepareCriteriaSQL, extFilterByRules
  8. from gran.main.util.crossdomain import crossdomain
  9. from services import app
  10. from gran.main.db.open_connection import with_open_connection, build_query, rows_to_dict_list
  11.  
  12.  
  13. @app.route('/entities/search', methods=['GET', 'POST', 'OPTIONS'])
  14. @crossdomain(origin='*')
  15. def search_nodes():
  16. response = {}
  17. try:
  18. search_string = request.get_json()['search_string'].strip()
  19. result = re.split(r'\s', search_string)
  20. full_json = request.get_json()
  21. ext_filter = full_json.get('criteria')
  22. tags = []
  23. if ext_filter != []:
  24. tags = ext_filter['tags']
  25. tags = [int(tag) for tag in tags]
  26. data = search_result_in_db(result, tags, ext_filter)
  27. response['errorcode'] = 0
  28. response['errordesc'] = ''
  29. response['data'] = data
  30. except BadRequest:
  31. abort(422)
  32. except ValueError:
  33. abort(422)
  34. except TypeError:
  35. abort(422)
  36. except Exception as e:
  37. response['errorcode'] = 1
  38. response['errordesc'] = str(e)
  39. response['data'] = []
  40. return response, 200
  41.  
  42.  
  43. def _prepare_criteria_sql(criteria):
  44. if criteria is None or criteria == []:
  45. return '', []
  46. else:
  47. criteria_sql = ''
  48. criteria_data = []
  49. criteria_sql,criteria_data = prepareCriteriaSQL(criteria)
  50.  
  51. criteria_sql = ' JOIN ('+criteria_sql+') tblCrit ON tblCrit.id=entities.id '
  52.  
  53. return criteria_sql, criteria_data
  54.  
  55. def gen_all_ngrams(search_list):
  56. search_clear = [elem for elem in search_list if elem != '']
  57. if len(search_clear) == 0:
  58. return []
  59. if len(search_clear)>1 and sys.getsizeof(' '.join(search_clear)) < 256:
  60. ngram_range = [1, len(search_clear)]
  61. else:
  62. ngram_range = [1]
  63. return [list(zip(*[search_clear[i:] for i in range(n)])) for n in ngram_range]
  64.  
  65.  
  66. def gen_sql_var(comb_list):
  67. if len(comb_list) == 0:
  68. return [['1=1'], ['1=1'], ['1'], ['']]
  69. condSQLcontent = 'CONTAINS(CONTENT3, %s, {}) > 0'
  70. condSQLname = 'CONTAINS(name, %s, {}) > 0'
  71. # orderSQL = 'SCORE({})/COALESCE(NULLIF(SCORE({}),0),1)'
  72. orderSQL = 'SCORE({})'
  73. orderData = []
  74. indexCont = []
  75. indexName = []
  76. stringData = []
  77. for i, temp_list in enumerate(comb_list):
  78. base_comb = [' ACCUM '.join(['%{' + ' '.join(content) + '}%' for content in temp_list])]
  79. base_comb += [' ACCUM '.join(['{' + ' '.join(content) + '}'for content in temp_list])]
  80. tempOrder = []
  81. tempCont = []
  82. tempName = []
  83. for j, content in enumerate(base_comb):
  84. tempOrder += [str(10**j)+'*'+orderSQL.format(j + 1)]
  85. tempCont += [condSQLcontent.format(j + 1)]
  86. tempName += [condSQLname.format(j + 1)]
  87. indexCont += [' OR '.join(tempCont)]
  88. indexName += [' OR '.join(tempName)]
  89. orderData += [str(10 ** (i)) + '*' + '(' + ' + '.join(tempOrder) + ')']
  90. stringData += base_comb
  91.  
  92. return [indexCont, indexName, orderData, stringData]
  93.  
  94.  
  95. @with_open_connection
  96. def search_result_in_db(search_string, tags, ext_filter, cursor):
  97. rows = []
  98.  
  99. if len(search_string) == 1 and search_string[0] == '' and len(ext_filter) == 0 and len(tags) == 0:
  100. return []
  101.  
  102. sql_with = None
  103. sql_relations_table = 'relations'
  104. sql_relation_data = []
  105. tags_data = []
  106. sql = ""
  107.  
  108. if ext_filter==None:
  109. ext_filter = []
  110.  
  111. sql_with_data, sql_with, sql_relation_data, sql_relations_table_tmp = extFilterGetSQL(ext_filter)
  112. if not sql_relations_table_tmp == None:
  113. sql_relations_table = sql_relations_table_tmp
  114.  
  115. if not sql_relations_table_tmp:
  116. return []
  117.  
  118. relations_count = int(cursor.execute("SELECT COUNT(*) FROM RELATION_TYPES").fetchone()[0])
  119.  
  120. all_relation_types = '1=1'
  121. if 'relationtypes' in ext_filter and len(ext_filter['relationtypes']) != relations_count:
  122. all_relation_types = '0=1'
  123.  
  124. if len(search_string) == 1 and search_string[0] == '':
  125. sql_entities = sql_with + " SELECT tblExtFilter.id AS id" \
  126. " FROM tblExtFilter JOIN ENTITIES ON tblExtFilter.id = ENTITIES.id" \
  127. " WHERE COALESCE(ENTITIES.origin_id, 0) != -1 AND parent_id IS NULL" \
  128. " AND (" + all_relation_types + " OR EXISTS(SELECT * FROM " + sql_relations_table_tmp + "" \
  129. " WHERE tblExtFilter.id=receiver_id OR tblExtFilter.id=sender_id)) AND ROWNUM <= 1001"
  130. rows = rows_to_dict_list(cursor.execute(build_query(sql_entities), sql_with_data+sql_relation_data))
  131.  
  132. if ext_filter:
  133. rows = extFilterByRules(ext_filter, rows, cursor)
  134.  
  135. if len(rows) > 0:
  136. result = []
  137. for row in rows:
  138. result.append({'id': row['id'], 'order_num': 1})
  139. return result
  140. else:
  141. return []
  142.  
  143. add_where = ' 1=1 '
  144. if sql_with == None:
  145. add_where = ' ROWNUM <= 1001 '
  146.  
  147. where_cont, where_name, order_sql, string_data = gen_sql_var(gen_all_ngrams(search_string))
  148.  
  149. if tags is not None and tags != [] and string_data[0] == '':
  150. s = ','
  151.  
  152. tags_tpl = ["%s"] * len(tags)
  153. tags_tpl = s.join(tags_tpl)
  154. tags_data = [int(tag) for tag in tags]
  155.  
  156. sql = " SELECT DISTINCT entities.id as id, entities.name as name, entity_types.name as type, 0 as order_num" \
  157. " FROM entities JOIN entity_types ON entities.type_id = entity_types.id " \
  158. " LEFT JOIN entities_tags ON entities.id = entities_tags.entity_id " \
  159. " LEFT JOIN tags ON entities_tags.tag_id = tags.id " \
  160. " WHERE COALESCE(entities.origin_id, 0) != -1 AND parent_id IS NULL AND "+add_where+" AND ((tags.id) IN (" + tags_tpl + ")) ORDER BY entities.id "
  161.  
  162. else:
  163. sql_body_facts = 'SELECT entity_id id, {} num ' \
  164. 'FROM entity_facts ' \
  165. 'WHERE ({}) '
  166. sql_body_entities = 'SELECT id, {} num ' \
  167. 'FROM entities ' \
  168. 'WHERE ({}) AND COALESCE(origin_id, 0) != -1 AND parent_id IS NULL '
  169.  
  170. if len(where_cont) != 0:
  171. sql = "WITH t0 AS (" + sql_body_facts.format(order_sql[0], where_cont[0]) + ")"
  172.  
  173. for i, where in enumerate(where_cont[1:]):
  174. sql = sql + ", t" + str(i + 1) + " AS (" + sql_body_facts.format(order_sql[i + 1], where) + ")"
  175.  
  176. for i, where in enumerate(where_name):
  177. sql = sql + ", t" + str(len(where_cont) + i) + " AS (" + sql_body_entities.format(
  178. str(10 ** len(where_cont)) + '*' + order_sql[i], where) + ")"
  179.  
  180. sql = sql + "SELECT * FROM (SELECT id, sum(num) order_num " \
  181. "FROM (" + ' union all '.join(
  182. ["SELECT id, num FROM t" + str(i) for i in range(len(where_cont) + len(where_name))])
  183.  
  184. sql = sql + " ORDER BY num DESC) GROUP BY id ORDER BY order_num DESC) " \
  185. " WHERE " + add_where + " ORDER BY order_num DESC"
  186.  
  187. if string_data[0]!='':
  188. string_data = tags_data + string_data + string_data
  189. else:
  190. string_data = tags_data
  191. if not sql_with==None:
  192. sql = sql_with+" SELECT TT.id,TT.order_num FROM ("+sql+") TT JOIN tblExtFilter ON tblExtFilter.id=TT.id WHERE ROWNUM<=1001"
  193. string_data = sql_with_data+string_data
  194.  
  195. sql = "SELECT * FROM (" + sql + ") WHERE " + all_relation_types + " OR EXISTS(SELECT * FROM (" + sql_relations_table_tmp + ") " \
  196. " WHERE id=receiver_id OR id=sender_id)"
  197.  
  198. cursor.execute(
  199. "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'"
  200. " NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD'")
  201.  
  202. cursor.execute(build_query(sql), string_data + sql_relation_data)
  203. rows = rows + rows_to_dict_list(cursor)
  204.  
  205. if ext_filter:
  206. rows = extFilterByRules(ext_filter, rows, cursor)
  207.  
  208. if len(rows) > 0:
  209. result = []
  210. for row in rows:
  211. result.append({'id': row['id'], 'order_num': row['order_num']})
  212. return result
  213. else:
  214. return []
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!