API tools faq
paste
Advertisement
asanchez75

virtuoso/procedures

asanchez75
Feb 5th, 2016
128
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.48 KB | None | 0 0
raw download clone embed print report
  1. > SELECT DB.DBA.A_GENERATE_ONTOSIDES_GROUPS('http://ontosides.fr');
  2. =============================================
  3.  
  4. DB.DBA.A_GENERATE_ONTOSIDES_GROUPS('http://ontosides.fr');
  5. =============================================
  6.  
  7. create procedure DB.DBA.A_GENERATE_ONTOSIDES_GROUPS(IN graphname VARCHAR){
  8.  
  9. DECLARE baseuri, state, msg, descs, rows1, rows2, rows3, rows4, rows5, epreuve, eval, prop, obj any;
  10. DECLARE i, j, k, group_id INTEGER;
  11.  
  12. baseuri := 'http://www.side-sante.fr/sides#';
  13.  
  14. exec(sprintf('SPARQL
  15. WITH <%s>
  16. DELETE {?s sides:Groupe1_Pour ?o}
  17. WHERE
  18. {?s sides:Groupe1_Pour ?o.}
  19. ', graphname));
  20.  
  21. exec(sprintf('SPARQL
  22. WITH <%s>
  23. DELETE {?s sides:Groupe2_Pour ?o}
  24. WHERE
  25. {?s sides:Groupe2_Pour ?o.}
  26. ', graphname));
  27.  
  28. exec(sprintf('SPARQL
  29. WITH <%s>
  30. DELETE {?s sides:Groupe3_Pour ?o}
  31. WHERE
  32. {?s sides:Groupe3_Pour ?o.}
  33. ', graphname));
  34.  
  35. exec(sprintf('SPARQL
  36. SELECT ?ep (COUNT(?et) AS ?NombreEt)
  37. WHERE{GRAPH <%s> {?ep sides:a_pour_inscrit ?et. }}
  38. GROUP BY ?ep
  39. HAVING (COUNT(?et) > 30)
  40. ORDER BY ?NombreEt
  41. ', graphname), state, msg, vector (), 0, descs, rows1);
  42.  
  43.  
  44. log_message(sprintf('total rows = %d', LENGTH(rows1)));
  45.  
  46. for(i:=0;i<LENGTH(rows1);i:=i+1){
  47. log_message(sprintf('processed row = %d', i));
  48. epreuve := replace(rows1[i][0], baseuri,'');
  49. exec(sprintf('
  50. SPARQL
  51. SELECT ?ev (COUNT (DISTINCT ?q) AS ?NombreQuestions )
  52. WHERE {GRAPH <%s> {sides:%s sides:est_constitue_de ?ev.
  53. ?q sides:fait_partie_de ?ev .
  54. ?a sides:correspond_a_question ?q.
  55. ?a sides:effectue_durant sides:%s.
  56. }} GROUP BY ?ev
  57. HAVING (COUNT (DISTINCT ?q) > 10)
  58. ', graphname, epreuve, epreuve), state, msg, vector (), 0, descs, rows2);
  59.  
  60. if (isarray(rows2) > 0 ) {
  61.  
  62. for(j:=0;j<LENGTH(rows2);j:=j+1){
  63.  
  64. eval := replace(rows2[j][0], baseuri, '');
  65.  
  66. exec(sprintf('
  67. SPARQL
  68. SELECT ?et (SUM (?n) AS ?NoteGenerale)
  69. WHERE {GRAPH <%s> { ?q sides:fait_partie_de sides:%s.
  70. ?a sides:effectue_durant sides:%s.
  71. ?a sides:correspond_a_question ?q.
  72. ?a sides:a_pour_note ?n.
  73. ?a sides:realise_par ?et .
  74. }}
  75. GROUP BY ?et
  76. ORDER BY ASC(?NoteGenerale)
  77. ', graphname, eval, epreuve), state, msg, vector (), 0, descs, rows3);
  78.  
  79. if (isarray(rows3)) {
  80.  
  81. for(k:=0;k<LENGTH(rows3);k:=k+1){
  82.  
  83. if (k < 63) {
  84. group_id := 3;
  85. }
  86. if (k>=63 AND k <=126) {
  87. group_id := 2;
  88. }
  89. if (k>126) {
  90. group_id := 1;
  91. }
  92. prop := concat(baseuri, sprintf('Groupe%d_Pour', group_id));
  93. obj := concat(baseuri, sprintf('%s_dans_%s', eval, epreuve));
  94.  
  95. DB.DBA.RDF_QUAD_URI(graphname, rows3[k][0], prop, obj);
  96.  
  97. }
  98. }
  99. }
  100. }
  101. }
  102. }
  103.  
  104.  
  105.  
  106. =====================================================================================================================
  107.  
  108. create procedure DB.DBA.A_LOAD_ONTOSIDES_DATA(){
  109.  
  110. sparql drop silent graph <http://ontosides.en>;
  111. sparql drop silent graph <http://ontosides.fr>;
  112. sparql create graph <http://ontosides.en>;
  113. sparql create graph <http://ontosides.fr>;
  114. DELETE FROM DB.DBA.LOAD_LIST WHERE ll_graph = 'http://ontosides.en';
  115. DELETE FROM DB.DBA.LOAD_LIST WHERE ll_graph = 'http://ontosides.fr';
  116. ld_dir('/Users/asanchez75/Trash/Ontosides_Fabrice/Onto', 'OntoSides_schema_VE.ttl', 'http://ontosides.en');
  117. ld_dir('/Users/asanchez75/Trash/Ontosides_Fabrice/Onto', 'OntoSides_sat_VE.ttl', 'http://ontosides.en');
  118. ld_dir('/Users/asanchez75/Trash/Ontosides_Fabrice/Onto', 'OntoSides_sat_VF.ttl', 'http://ontosides.fr');
  119. ld_dir('/Users/asanchez75/Trash/Ontosides_Fabrice/Onto', 'OntoSides_schema_VF.ttl', 'http://ontosides.fr');
  120. log_enable(2);
  121. rdf_loader_run();
  122. commit WORK;
  123. }
  124.  
  125.  
  126. =====================================================================================================================
  127.  
  128. create procedure "EXMO"."DBA"."RESULTS_CSV_VEC_INS_1" (inout arr any, in fill any)
  129. {
  130. declare "classname1_VA" any;
  131. "classname1_VA" := make_array (fill, 'any');
  132. declare "classname2_VA" any;
  133. "classname2_VA" := make_array (fill, 'any');
  134. declare "cardinality1_VA" any;
  135. "cardinality1_VA" := make_array (fill, 'any');
  136. declare "cardinality2_VA" any;
  137. "cardinality2_VA" := make_array (fill, 'any');
  138. declare "intersection_VA" any;
  139. "intersection_VA" := make_array (fill, 'any');
  140. declare "measure_VA" any;
  141. "measure_VA" := make_array (fill, 'any');
  142. declare "graphname_VA" any;
  143. "graphname_VA" := make_array (fill, 'any');
  144. for (declare i int, i := 0; i < fill; i := i + 1)
  145. {
  146. aset_1_2_zap ("classname1_VA", i, arr, i,0);
  147. aset_1_2_zap ("classname2_VA", i, arr, i,1);
  148. aset_1_2_zap ("cardinality1_VA", i, arr, i,2);
  149. aset_1_2_zap ("cardinality2_VA", i, arr, i,3);
  150. aset_1_2_zap ("intersection_VA", i, arr, i,4);
  151. aset_1_2_zap ("measure_VA", i, arr, i,5);
  152. aset_1_2_zap ("graphname_VA", i, arr, i,6);
  153. }
  154. for vectored modify (in "classname1_VI" any array := "classname1_VA", in "classname2_VI" any array := "classname2_VA", in "cardinality1_VI" any array := "cardinality1_VA", in "cardinality2_VI" any array := "cardinality2_VA", in "intersection_VI" any array := "intersection_VA", in "measure_VI" any array := "measure_VA", in "graphname_VI" any array := "graphname_VA")
  155. {
  156. INSERT INTO "EXMO"."DBA"."RESULTS" ("classname1", "classname2", "cardinality1", "cardinality2", "intersection", "measure", "graphname") values ("classname1_VI", "classname2_VI", "cardinality1_VI", "cardinality2_VI", "intersection_VI", "measure_VI", "graphname_VI");
  157. }
  158.  
  159. }
  160. ======================================================================================================================
  161.  
  162.  
  163. csv_load_file('/Users/asanchez75/Documents/workspace/Evolvement/resources',0, null, 'EXMO.DBA.RESULTS')
  164. ======================================================================================================================
  165.  
  166. exmo.DBA.csvExport('SELECT id_to_iri(classname1) as classname1,id_to_iri(classname2) as classname2,cardinality1,cardinality2,intersection,measure,id_to_iri(graphname) as graphname FROM EXMO.DBA.RESULTS WHERE graphname = iri_to_id(\'<http://linkedgeodata.org>\')','/home/asanchez75/virtuoso/data/linkedgeodata.csv');
  167.  
  168.  
  169. ======================================================================================================================
  170.  
  171. create procedure A() {
  172. for (sparql select ?s ?p ?o where {?s ?p ?o} LIMIT 10 OFFSET 0) do {
  173. RESULT_NAMES (s,p, o);
  174. RESULT (s,p,o);
  175. }
  176. END_RESULT ();
  177. }
  178. ======================================================================================================================
  179. create procedure A() {
  180. for (sparql select ?s ?o where {?s rdf:type ?o. FILTER(REGEX(?o, 'http://dbpedia.org/ontology'))} LIMIT 10 OFFSET 0) do {
  181. RESULT_NAMES (s, o);
  182. RESULT (s,o);
  183. }
  184. END_RESULT ();
  185. }
  186. ======================================================================================================================
  187. create procedure A() {
  188. for (sparql select ?s ?o where {?s owl:sameAs ?o} LIMIT 10 OFFSET 0) do {
  189. for (sparql select ?s1 ?o1 where {?s1 rdf:type ?o1. FILTER (?s1 = ?s )}) do {
  190. RESULT_NAMES (s1, o1);
  191. RESULT (s1,o1);
  192. }
  193. }
  194. END_RESULT ();
  195. }
  196.  
  197. ======================================================================================================================
  198.  
  199. create procedure A() {
  200. for (sparql select ?s ?o where {?s owl:sameAs ?o} LIMIT 10 OFFSET 0) do {
  201. for (sparql select ?s1 ?o1 where {?s1 rdf:type ?o1. FILTER (?s1 = ?s && ?o1 = <http://dbpedia.org/ontology/Country>)}) do {
  202. RESULT_NAMES (s1, o1);
  203. RESULT (s1,o1);
  204. }
  205. }
  206. END_RESULT ();
  207. }
  208.  
  209. ======================================================================================================================
  210.  
  211. create procedure
  212. CARDINALITY
  213. (
  214. in graphname VARCHAR
  215. )
  216. {
  217. DECLARE state, msg, descs, rows any;
  218.  
  219. exec(sprintf('SPARQL SELECT ?class COUNT(DISTINCT ?instance) as ?count {GRAPH %S {
  220. ?class rdf:type owl:Class.
  221. ?instance rdf:type ?class.
  222. }}
  223. GROUP BY ?class', graphname), state, msg, vector (), 0, descs, rows);
  224.  
  225. declare i INTEGER;
  226. for(i:=0;i<LENGTH(rows);i:=i+1){
  227.  
  228. exec(sprintf('INSERT INTO DB.DBA.CARDINALITIES (CLASSNAME, CARDINALITY, GRAPHNAME) VALUES (\'%s\', \'%d\', \'%s\')', rows[i][0], rows[i][1], graphname), null, null, vector (), 0, null, null);
  229.  
  230. }
  231. }
  232.  
  233. ======================================================================================================================
  234.  
  235. create procedure
  236. SAMEAS
  237. (
  238. in query VARCHAR,
  239. in graphname VARCHAR
  240. )
  241. {
  242.  
  243. DECLARE state, msg, descs, rows any;
  244. DECLARE sql any;
  245.  
  246. sql := sprintf('sparql define input:storage "" %s', query);
  247.  
  248. exec(sql, state, msg, vector (), 0, descs, rows);
  249.  
  250. declare i INTEGER;
  251. for(i:=0;i<LENGTH(rows);i:=i+1){
  252.  
  253. exec(sprintf('INSERT INTO DB.DBA.SAMEAS (INSTANCE1, CLASS1, INSTANCE2, CLASS2, GRAPHNAME) VALUES (\'%s\', \'%s\', \'%s\', \'%s\', \'%s\')', rows[i][0], rows[i][1], rows[i][2], rows[i][3], graphname), null, null, vector (), 0, null, null);
  254.  
  255. }
  256. }
  257.  
  258. ======================================================================================================================
  259.  
  260.  
  261. create procedure exmo.DBA.A() {
  262. for (sparql select ?s ?o where {?s owl:sameAs ?o} LIMIT 10 OFFSET 0) do {
  263. for (sparql select ?s1 ?o1 where {?s1 rdf:type ?o1. FILTER (?s1 = ?s && ?o1 = <http://dbpedia.org/ontology/Country>)}) do {
  264. RESULT_NAMES (s1, o1);
  265. RESULT (s1,o1);
  266. }
  267. }
  268. END_RESULT ();
  269. }
  270.  
  271.  
  272. create procedure exmo.DBA.A1(){
  273. DECLARE state, msg, descs, rows, sub any;
  274. exec('SPARQL select * {?s ?p ?o} limit 10', state, msg, vector (), 0, descs, rows);
  275.  
  276. while(exec_next(rows, state, msg, sub) = 0) {
  277. return sub[0];
  278. }
  279. exec_close (rows);
  280. }
  281.  
  282. create procedure
  283. exmo.DBA.A2
  284. (
  285. in graphname VARCHAR
  286. )
  287. {
  288.  
  289. return sprintf('INSERT INTO DB.DBA.CARDINALITIES (CLASSNAME, CARDINALITY, GRAPHNAME) VALUES (\'%s\', \'%d\', \'%s\')', 'president', 4, 'obama');
  290. }
  291.  
  292.  
  293. create procedure
  294. exmo.DBA.CARDINALITY
  295. (
  296. in graphname VARCHAR
  297. )
  298. {
  299. DECLARE state, msg, descs, rows any;
  300.  
  301. exec(sprintf('SPARQL SELECT ?class COUNT(DISTINCT ?instance) as ?count {GRAPH %S {
  302. ?class rdf:type owl:Class.
  303. ?instance rdf:type ?class.
  304. }}
  305. GROUP BY ?class', graphname), state, msg, vector (), 0, descs, rows);
  306.  
  307. declare i INTEGER;
  308. for(i:=0;i<LENGTH(rows);i:=i+1){
  309.  
  310. exec(sprintf('INSERT INTO DB.DBA.CARDINALITIES (CLASSNAME, CARDINALITY, GRAPHNAME) VALUES (\'%s\', \'%d\', \'%s\')', rows[i][0], rows[i][1], graphname), null, null, vector (), 0, null, null);
  311.  
  312.  
  313. }
  314.  
  315. }
  316.  
  317.  
  318. create procedure
  319. exmo.DBA.csvExport( IN in_query VARCHAR, IN filename VARCHAR )
  320. {
  321.  
  322. declare query varchar;
  323. query := split_and_decode(in_query,0,'%');
  324.  
  325. file_delete(filename,1);
  326.  
  327. declare meta, _dt any;
  328. declare errorCode, errorMessage varchar;
  329. declare inx integer;
  330. exec (query, errorCode, errorMessage, vector(), 0, meta, _dt);
  331.  
  332. declare linestr varchar;
  333. declare flag integer;
  334. linestr := '';
  335. if (meta <> 0) {
  336. flag := 0;
  337. FOREACH (varchar col in meta[0]) DO {
  338. if (flag = 0) flag := 1;
  339. else linestr:=concat(linestr,',');
  340. linestr := concat(linestr,'"',col[0],'"');
  341. }
  342. string_to_file(filename,linestr,-2);
  343. string_to_file(filename,'\n',-1);
  344. -- Header is now complete
  345. inx := 0;
  346. if (_dt <> 0) {
  347. while (inx < length (_dt))
  348. {
  349. linestr := '';
  350. flag := 0;
  351. FOREACH (any col in _dt[inx]) DO {
  352. if (flag = 0) flag := 1;
  353. else linestr:=concat(linestr,',');
  354. linestr := concat(linestr,'"',col,'"');
  355. }
  356. string_to_file(filename,linestr,-1);
  357. string_to_file(filename,'\n',-1);
  358. inx := inx + 1;
  359.  
  360. }
  361. }
  362. } else return 'No records or some error';
  363.  
  364. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!