text:
-- This SQL code was generated by sklearn2sql (development version).
-- Copyright 2018

-- Model : CaretRegressor_svmPoly_pca
-- Dataset : boston
-- Database : sqlite

-- This SQL code can contain one or more statements, to be executed in the order they appear in this file.

-- Model deployment code

WITH "ADS_sca_1_OUT" AS
(SELECT "ADS"."KEY" AS "KEY", (CAST("ADS"."Feature_0" AS FLOAT) - 3.3936090099009895) / 8.010536728867239 AS "Feature_0", (CAST("ADS"."Feature_1" AS FLOAT) - 12.113861386138614) / 24.217647441147463 AS "Feature_1", (CAST("ADS"."Feature_2" AS FLOAT) - 10.983613861386141) / 6.826200750364282 AS "Feature_2", (CAST("ADS"."Feature_3" AS FLOAT) - 0.07178217821782178) / 0.2584469594478702 AS "Feature_3", (CAST("ADS"."Feature_4" AS FLOAT) - 0.5541153465346534) / 0.1181544189262098 AS "Feature_4", (CAST("ADS"."Feature_5" AS FLOAT) - 6.299148514851484) / 0.7069171793669617 AS "Feature_5", (CAST("ADS"."Feature_6" AS FLOAT) - 67.85049504950496) / 28.14225428392539 AS "Feature_6", (CAST("ADS"."Feature_7" AS FLOAT) - 3.8198420792079206) / 2.0959683176629325 AS "Feature_7", (CAST("ADS"."Feature_8" AS FLOAT) - 9.559405940594061) / 8.73962685484057 AS "Feature_8", (CAST("ADS"."Feature_9" AS FLOAT) - 405.8019801980198) / 169.99638120813233 AS "Feature_9", (CAST("ADS"."Feature_10" AS FLOAT) - 18.409158415841585) / 2.169479311043464 AS "Feature_10", (CAST("ADS"."Feature_11" AS FLOAT) - 358.3797277227722) / 90.75931171894922 AS "Feature_11", (CAST("ADS"."Feature_12" AS FLOAT) - 12.626584158415842) / 7.176826037708456 AS "Feature_12"
FROM boston AS "ADS"),
"ADS_pre_1_OUT" AS
(SELECT "ADS_sca_1_OUT"."KEY" AS "KEY", "ADS_sca_1_OUT"."Feature_0" * 0.25022067888868665 + "ADS_sca_1_OUT"."Feature_1" * -0.25765762216632226 + "ADS_sca_1_OUT"."Feature_2" * 0.3491428172111632 + "ADS_sca_1_OUT"."Feature_3" * 0.01094773991121527 + "ADS_sca_1_OUT"."Feature_4" * 0.34170294050212857 + "ADS_sca_1_OUT"."Feature_5" * -0.18342485606154985 + "ADS_sca_1_OUT"."Feature_6" * 0.3129084082735513 + "ADS_sca_1_OUT"."Feature_7" * -0.3220310104224693 + "ADS_sca_1_OUT"."Feature_8" * 0.3210957066290012 + "ADS_sca_1_OUT"."Feature_9" * 0.3372514741075172 + "ADS_sca_1_OUT"."Feature_10" * 0.2041002866212432 + "ADS_sca_1_OUT"."Feature_11" * -0.20483878453228935 + "ADS_sca_1_OUT"."Feature_12" * 0.3106501513197035 AS "PC1", "ADS_sca_1_OUT"."Feature_0" * -0.33389249833500073 + "ADS_sca_1_OUT"."Feature_1" * -0.3461278969103596 + "ADS_sca_1_OUT"."Feature_2" * 0.08741121935217115 + "ADS_sca_1_OUT"."Feature_3" * 0.4245155851940165 + "ADS_sca_1_OUT"."Feature_4" * 0.2112937184640132 + "ADS_sca_1_OUT"."Feature_5" * 0.0574342949321771 + "ADS_sca_1_OUT"."Feature_6" * 0.3143514152302056 + "ADS_sca_1_OUT"."Feature_7" * -0.33832121905897383 + "ADS_sca_1_OUT"."Feature_8" * -0.3093926550229444 + "ADS_sca_1_OUT"."Feature_9" * -0.27803257944665394 + "ADS_sca_1_OUT"."Feature_10" * -0.28693120813154377 + "ADS_sca_1_OUT"."Feature_11" * 0.2527117523816798 + "ADS_sca_1_OUT"."Feature_12" * -0.020410873762011308 AS "PC2", "ADS_sca_1_OUT"."Feature_0" * 0.19608688758362933 + "ADS_sca_1_OUT"."Feature_1" * 0.2469257706594379 + "ADS_sca_1_OUT"."Feature_2" * -0.02294168584923974 + "ADS_sca_1_OUT"."Feature_3" * 0.2950722382634112 + "ADS_sca_1_OUT"."Feature_4" * 0.14475391738768567 + "ADS_sca_1_OUT"."Feature_5" * 0.6390828425216293 + "ADS_sca_1_OUT"."Feature_6" * 0.04637333835457555 + "ADS_sca_1_OUT"."Feature_7" * -0.10318310122143892 + "ADS_sca_1_OUT"."Feature_8" * 0.2497932777479205 + "ADS_sca_1_OUT"."Feature_9" * 0.18494382558800715 + "ADS_sca_1_OUT"."Feature_10" * -0.3548131264402329 + "ADS_sca_1_OUT"."Feature_11" * -0.2684239315694441 + "ADS_sca_1_OUT"."Feature_12" * -0.2761542549039 AS "PC3", "ADS_sca_1_OUT"."Feature_0" * -0.07043882717757964 + "ADS_sca_1_OUT"."Feature_1" * -0.3664439770890674 + "ADS_sca_1_OUT"."Feature_2" * 0.0055058786745181285 + "ADS_sca_1_OUT"."Feature_3" * -0.3946776597336387 + "ADS_sca_1_OUT"."Feature_4" * -0.08824417045679225 + "ADS_sca_1_OUT"."Feature_5" * 0.4793823607635913 + "ADS_sca_1_OUT"."Feature_6" * 0.08998214645492984 + "ADS_sca_1_OUT"."Feature_7" * -0.18326301068911208 + "ADS_sca_1_OUT"."Feature_8" * 0.09856418200051932 + "ADS_sca_1_OUT"."Feature_9" * 0.04616348150064367 + "ADS_sca_1_OUT"."Feature_10" * 0.42777912036010746 + "ADS_sca_1_OUT"."Feature_11" * 0.3005689288221394 + "ADS_sca_1_OUT"."Feature_12" * -0.37482955126669193 AS "PC4", "ADS_sca_1_OUT"."Feature_0" * 0.04300395631166038 + "ADS_sca_1_OUT"."Feature_1" * -0.02875212611151597 + "ADS_sca_1_OUT"."Feature_2" * 0.02223188515780241 + "ADS_sca_1_OUT"."Feature_3" * 0.7435684786227807 + "ADS_sca_1_OUT"."Feature_4" * -0.1834288143499854 + "ADS_sca_1_OUT"."Feature_5" * -0.07107783038498264 + "ADS_sca_1_OUT"."Feature_6" * -0.1682770489309431 + "ADS_sca_1_OUT"."Feature_7" * 0.14991701697987567 + "ADS_sca_1_OUT"."Feature_8" * 0.19696674860202626 + "ADS_sca_1_OUT"."Feature_9" * 0.1420113329297506 + "ADS_sca_1_OUT"."Feature_10" * 0.4646548460967998 + "ADS_sca_1_OUT"."Feature_11" * 0.255520747968419 + "ADS_sca_1_OUT"."Feature_12" * -0.11952854639999652 AS "PC5", "ADS_sca_1_OUT"."Feature_0" * -0.3767232718011799 + "ADS_sca_1_OUT"."Feature_1" * -0.29207806001032416 + "ADS_sca_1_OUT"."Feature_2" * -0.01942601421821086 + "ADS_sca_1_OUT"."Feature_3" * 0.10887824454350792 + "ADS_sca_1_OUT"."Feature_4" * -0.10146416318885716 + "ADS_sca_1_OUT"."Feature_5" * 0.057934635009180835 + "ADS_sca_1_OUT"."Feature_6" * -0.10809395127298303 + "ADS_sca_1_OUT"."Feature_7" * 0.032878444909013 + "ADS_sca_1_OUT"."Feature_8" * -0.09390581030770888 + "ADS_sca_1_OUT"."Feature_9" * -0.12972376055449672 + "ADS_sca_1_OUT"."Feature_10" * 0.2572802499128157 + "ADS_sca_1_OUT"."Feature_11" * -0.7965340188449787 + "ADS_sca_1_OUT"."Feature_12" * -0.08840431527675782 AS "PC6", "ADS_sca_1_OUT"."Feature_0" * 0.7320645629415989 + "ADS_sca_1_OUT"."Feature_1" * -0.3159393337538693 + "ADS_sca_1_OUT"."Feature_2" * -0.3373999976989149 + "ADS_sca_1_OUT"."Feature_3" * 0.0931540519768708 + "ADS_sca_1_OUT"."Feature_4" * -0.2259561892558596 + "ADS_sca_1_OUT"."Feature_5" * 0.04114489763402434 + "ADS_sca_1_OUT"."Feature_6" * 0.08761501939857337 + "ADS_sca_1_OUT"."Feature_7" * -0.08894297135254832 + "ADS_sca_1_OUT"."Feature_8" * -0.17352234917240772 + "ADS_sca_1_OUT"."Feature_9" * -0.3494997544996276 + "ADS_sca_1_OUT"."Feature_10" * 0.023466306336306118 + "ADS_sca_1_OUT"."Feature_11" * -0.11741140060045532 + "ADS_sca_1_OUT"."Feature_12" * 0.08255206116222752 AS "PC7"
FROM "ADS_sca_1_OUT"),
kernel_input_with_scaling AS
(SELECT "ADS_pre_1_OUT"."KEY" AS "KEY", (CAST("ADS_pre_1_OUT"."PC1" AS FLOAT) - -2.633516342292315e-16) / 2.481593803551449 AS "PC1", (CAST("ADS_pre_1_OUT"."PC2" AS FLOAT) - 1.1222458605972728e-16) / 1.186923667445278 AS "PC2", (CAST("ADS_pre_1_OUT"."PC3" AS FLOAT) - 4.64785663202063e-16) / 1.104592456631308 AS "PC3", (CAST("ADS_pre_1_OUT"."PC4" AS FLOAT) - 4.60131108132858e-17) / 0.9424174689498424 AS "PC4", (CAST("ADS_pre_1_OUT"."PC5" AS FLOAT) - -1.6126380174849488e-16) / 0.939135215408228 AS "PC5", (CAST("ADS_pre_1_OUT"."PC6" AS FLOAT) - -1.9118456130660232e-16) / 0.8084195928325133 AS "PC6", (CAST("ADS_pre_1_OUT"."PC7" AS FLOAT) - -7.059981037626401e-17) / 0.7273813779641739 AS "PC7"
FROM "ADS_pre_1_OUT"),
"SV_data" AS
(SELECT "Values".sv_idx AS sv_idx, "Values".dual_coeff AS dual_coeff, "Values".sv_0 AS sv_0, "Values".sv_1 AS sv_1, "Values".sv_2 AS sv_2, "Values".sv_3 AS sv_3, "Values".sv_4 AS sv_4, "Values".sv_5 AS sv_5, "Values".sv_6 AS sv_6
FROM (SELECT 0 AS sv_idx, -0.5 AS dual_coeff, -0.1045439569977479 AS sv_0, 2.4956851536016607 AS sv_1, 0.9099867054273189 AS sv_2, -1.364695645309829 AS sv_3, 2.118716866663061 AS sv_4, 0.2682479114148916 AS sv_5, 0.5810918464304881 AS sv_6 UNION ALL SELECT 1 AS sv_idx, 0.5 AS dual_coeff, -0.5178582580508849 AS sv_0, 0.11525557901822615 AS sv_1, -1.5341763907634438 AS sv_2, -0.6083549574449119 AS sv_3, -0.1519679574765703 AS sv_4, 0.15911265156014867 AS sv_5, 0.7735031655871997 AS sv_6 UNION ALL SELECT 2 AS sv_idx, 0.5 AS dual_coeff, -0.4684058774198557 AS sv_0, -0.1758755424841571 AS sv_1, -1.1168953842726497 AS sv_2, 0.2866994723841566 AS sv_3, 0.33345306645099126 AS sv_4, 0.4548783470894426 AS sv_5, 0.3894139347838949 AS sv_6 UNION ALL SELECT 3 AS sv_idx, 0.5 AS dual_coeff, -0.013133601890823285 AS sv_0, 0.32319186548081696 AS sv_1, -1.259891950694293 AS sv_2, 0.659396596166996 AS sv_3, 0.16275633465645886 AS sv_4, 0.3448585204628308 AS sv_5, 0.5792197536536284 AS sv_6 UNION ALL SELECT 4 AS sv_idx, -0.018635539920633687 AS dual_coeff, -0.07868938758867668 AS sv_0, 0.40814624679307193 AS sv_1, -2.1491259450512024 AS sv_2, -1.4312383102512205 AS sv_3, -0.61773873122651 AS sv_4, -0.2485053425312692 AS sv_5, 1.1478110436845819 AS sv_6 UNION ALL SELECT 5 AS sv_idx, 0.4919553751248228 AS dual_coeff, -1.7128983755938842 AS sv_0, -1.0409203878735342 AS sv_1, 1.7886912703177755 AS sv_2, -0.5665168667879465 AS sv_3, -0.5499779186810893 AS sv_4, -0.929574024567326 AS sv_5, -0.9104330110243583 AS sv_6 UNION ALL SELECT 6 AS sv_idx, -0.4511252627900854 AS dual_coeff, 2.516487701836688 AS sv_0, -2.3608773392068887 AS sv_1, -0.1880706317999139 AS sv_2, -2.8359766153451975 AS sv_3, -0.6013613327519711 AS sv_4, -0.4817663626620448 AS sv_5, 4.53791208305893 AS sv_6 UNION ALL SELECT 7 AS sv_idx, -0.5 AS dual_coeff, -0.5928885615866929 AS sv_0, 0.8480431247230008 AS sv_1, 0.22141476925727763 AS sv_2, 1.2692147404125214 AS sv_3, -0.5651396041643564 AS sv_4, 0.2393539289599995 AS sv_5, 1.0070298846373265 AS sv_6 UNION ALL SELECT 8 AS sv_idx, -0.5 AS dual_coeff, -0.8752668195094886 AS sv_0, -0.12471019388133425 AS sv_1, -0.5191385803073354 AS sv_2, -0.483168801020619 AS sv_3, -0.281666138769796 AS sv_4, -0.11765613849572833 AS sv_5, 0.31380984689026414 AS sv_6 UNION ALL SELECT 9 AS sv_idx, 0.5 AS dual_coeff, -0.8126536947335997 AS sv_0, 0.4558447150520892 AS sv_1, 0.2611883437375125 AS sv_2, 1.2467710362407591 AS sv_3, -0.27149867201837025 AS sv_4, 0.5101960135952182 AS sv_5, 0.6865838855981896 AS sv_6 UNION ALL SELECT 10 AS sv_idx, 0.5 AS dual_coeff, 1.140717094484003 AS sv_0, -0.5364314985719622 AS sv_1, -0.1628522553984461 AS sv_2, 0.32516125009355357 AS sv_3, 0.2921712692701462 AS sv_4, -0.16799792957491244 AS sv_5, -1.0035222593730915 AS sv_6 UNION ALL SELECT 11 AS sv_idx, -0.5 AS dual_coeff, -0.773174132627298 AS sv_0, -0.6325886579435764 AS sv_1, -0.39499985171924495 AS sv_2, 0.4903676999281441 AS sv_3, 0.5034632443812628 AS sv_4, 0.16831557427878524 AS sv_5, 0.042333050418852006 AS sv_6 UNION ALL SELECT 12 AS sv_idx, -0.5 AS dual_coeff, -0.03482298691447657 AS sv_0, 0.7362886978028736 AS sv_1, -1.0581354875311793 AS sv_2, 1.099929425580185 AS sv_3, 0.061549942744863126 AS sv_4, 0.4735854847512291 AS sv_5, 0.2618763432677506 AS sv_6 UNION ALL SELECT 13 AS sv_idx, -0.5 AS dual_coeff, 1.647957856278381 AS sv_0, -0.7755405418323745 AS sv_1, 0.08707609375740005 AS sv_2, 0.07029886204025158 AS sv_3, 0.2241427485910346 AS sv_4, -2.325071371174422 AS sv_5, 1.348332536944179 AS sv_6 UNION ALL SELECT 14 AS sv_idx, -0.5 AS dual_coeff, 1.2986736947580857 AS sv_0, -0.2849068781618396 AS sv_1, 0.510380799037936 AS sv_2, 0.6261937357020433 AS sv_3, -0.016201768198369215 AS sv_4, -0.22814009129776555 AS sv_5, -1.1489549653598687 AS sv_6 UNION ALL SELECT 15 AS sv_idx, -0.5 AS dual_coeff, -0.44350750166492486 AS sv_0, 1.6051506076959954 AS sv_1, 1.6874025982593557 AS sv_2, 0.09558250825450275 AS sv_3, -2.1940713144535753 AS sv_4, -0.9512917377226988 AS sv_5, 0.3690441606804352 AS sv_6 UNION ALL SELECT 16 AS sv_idx, 0.07638749607086892 AS dual_coeff, -1.449657681744216 AS sv_0, -1.7504489778460974 AS sv_1, -0.3459679283314336 AS sv_2, -1.7474684372256533 AS sv_3, 0.10734531653113 AS sv_4, 0.042338803417303936 AS sv_5, -0.34319496904141733 AS sv_6 UNION ALL SELECT 17 AS sv_idx, 0.5 AS dual_coeff, 1.8213571207243315 AS sv_0, -0.8205501798204095 AS sv_1, -1.0594137646912984 AS sv_2, -0.9621825779041134 AS sv_3, 0.3157311047856167 AS sv_4, -2.4552645371831887 AS sv_5, 1.1721234540055965 AS sv_6 UNION ALL SELECT 18 AS sv_idx, -0.5 AS dual_coeff, -0.7486459888173832 AS sv_0, -0.8284713635660879 AS sv_1, -0.9324988414736904 AS sv_2, -0.07087077991310198 AS sv_3, 0.6247088394945368 AS sv_4, 0.16933423369599626 AS sv_5, -0.03254642118186361 AS sv_6 UNION ALL SELECT 19 AS sv_idx, -0.5 AS dual_coeff, -0.10604149256798387 AS sv_0, 0.9478317710387458 AS sv_1, -0.17530737332605795 AS sv_2, 1.9705709161711185 AS sv_3, -0.0905788489429411 AS sv_4, 0.5478299937238995 AS sv_5, 0.3671746144350921 AS sv_6 UNION ALL SELECT 20 AS sv_idx, -0.5 AS dual_coeff, -0.5122133264043053 AS sv_0, 0.10454287749760728 AS sv_1, -0.200721366952928 AS sv_2, 0.2629687834134 AS sv_3, -0.14528972780619484 AS sv_4, -0.07699587144619904 AS sv_5, -0.4241816079019721 AS sv_6 UNION ALL SELECT 21 AS sv_idx, -0.5 AS dual_coeff, 1.315424343163018 AS sv_0, -0.39735973635318605 AS sv_1, 0.5518679061371237 AS sv_2, 0.5539586824843177 AS sv_3, -0.04444639267822442 AS sv_4, -0.14395011336618985 AS sv_5, -0.8662330230280169 AS sv_6 UNION ALL SELECT 22 AS sv_idx, 0.5 AS dual_coeff, 1.5299763594185518 AS sv_0, -1.2242465531567421 AS sv_1, 1.6478540561471269 AS sv_2, -0.2962029418613772 AS sv_3, -0.8972488827989724 AS sv_4, 3.0371189745057685 AS sv_5, -0.10807782581451708 AS sv_6 UNION ALL SELECT 23 AS sv_idx, -0.5 AS dual_coeff, 1.4868780153159955 AS sv_0, -0.5958387261625682 AS sv_1, -0.34207020476728717 AS sv_2, -0.33654990587121025 AS sv_3, 0.05388423745612544 AS sv_4, -0.765926683537342 AS sv_5, -0.4981243935009554 AS sv_6 UNION ALL SELECT 24 AS sv_idx, 0.5 AS dual_coeff, -1.1260876030782847 AS sv_0, 0.5047427456528792 AS sv_1, 1.4709239845956747 AS sv_2, 0.8540026694059032 AS sv_3, -1.001202360905043 AS sv_4, -0.08155282283560983 AS sv_5, 0.686984046860761 AS sv_6 UNION ALL SELECT 25 AS sv_idx, -0.5 AS dual_coeff, -0.4833981509047305 AS sv_0, 0.4395848571219077 AS sv_1, -0.3511930077939865 AS sv_2, -0.8727598292368127 AS sv_3, -0.9031372740250047 AS sv_4, -0.6125134030739292 AS sv_5, 0.02378146332663434 AS sv_6 UNION ALL SELECT 26 AS sv_idx, -0.5 AS dual_coeff, 1.2570282000532305 AS sv_0, -0.5955937808087997 AS sv_1, 0.009727362799613058 AS sv_2, 0.20771712055861488 AS sv_3, 0.1076598853592051 AS sv_4, -0.019427893372990227 AS sv_5, -0.6165660573001855 AS sv_6 UNION ALL SELECT 27 AS sv_idx, 0.5 AS dual_coeff, -0.19983671287024474 AS sv_0, 1.0548930118990492 AS sv_1, 0.1128843400849549 AS sv_2, 0.4610858300505428 AS sv_3, -0.911491727116792 AS sv_4, -0.16446009499612665 AS sv_5, 0.05582667692121935 AS sv_6 UNION ALL SELECT 28 AS sv_idx, -0.5 AS dual_coeff, -1.2191508273819602 AS sv_0, -0.9416889022191884 AS sv_1, 0.027874353094230527 AS sv_2, -0.46226616757791 AS sv_3, 0.13221423815419434 AS sv_4, 0.21606575423590374 AS sv_5, -0.2382340656591815 AS sv_6 UNION ALL SELECT 29 AS sv_idx, -0.5 AS dual_coeff, -1.32179112547659 AS sv_0, -1.1815357395871888 AS sv_1, 0.4814705898468714 AS sv_2, 0.017538678470837506 AS sv_3, 0.36796614378883774 AS sv_4, -0.3444365605501609 AS sv_5, -0.6880384080649175 AS sv_6 UNION ALL SELECT 30 AS sv_idx, -0.5 AS dual_coeff, 1.435660471199105 AS sv_0, -0.5083010946513961 AS sv_1, 0.28804813604508706 AS sv_2, 0.6087808995295684 AS sv_3, 0.2577858188661982 AS sv_4, -1.753721869287129 AS sv_5, 0.4615778229122222 AS sv_6 UNION ALL SELECT 31 AS sv_idx, 0.5 AS dual_coeff, -0.94492759861087 AS sv_0, -0.3284506194535408 AS sv_1, -0.00038540335628581464 AS sv_2, -0.08189246907725897 AS sv_3, -0.12152955303146883 AS sv_4, -0.1730186565614871 AS sv_5, -0.10243766118123943 AS sv_6 UNION ALL SELECT 32 AS sv_idx, 0.5 AS dual_coeff, -1.7001785423928557 AS sv_0, -0.6612923482457878 AS sv_1, 2.059413111285331 AS sv_2, -0.291451186856136 AS sv_3, -0.8474876751385626 AS sv_4, -0.9245520215267252 AS sv_5, -0.4474386369428003 AS sv_6 UNION ALL SELECT 33 AS sv_idx, -0.5 AS dual_coeff, -0.15970610724920034 AS sv_0, 0.18695813767382183 AS sv_1, -0.5981708076171122 AS sv_2, 0.5165542867039081 AS sv_3, -0.03846777555584895 AS sv_4, 0.37835452563241845 AS sv_5, 0.07709160851679278 AS sv_6 UNION ALL SELECT 34 AS sv_idx, 0.5 AS dual_coeff, -0.09957122982324147 AS sv_0, -0.262941452557641 AS sv_1, -1.9913402602412529 AS sv_2, -1.4575107162532148 AS sv_3, -0.20208057079406894 AS sv_4, 0.64400147878571 AS sv_5, 0.4868228520315437 AS sv_6 UNION ALL SELECT 35 AS sv_idx, -0.5 AS dual_coeff, 0.10971318222522979 AS sv_0, -0.010011790004493816 AS sv_1, -1.4393961748875654 AS sv_2, -0.11842842983149407 AS sv_3, -0.05681756741225441 AS sv_4, 1.2601927037171228 AS sv_5, 0.7085682599113048 AS sv_6 UNION ALL SELECT 36 AS sv_idx, 0.5 AS dual_coeff, -0.34221549202518964 AS sv_0, 0.6372212625737055 AS sv_1, 0.10311216344918296 AS sv_2, 0.8498309335069156 AS sv_3, -0.4683228501635283 AS sv_4, -0.04360908001098938 AS sv_5, 0.5532026147586823 AS sv_6 UNION ALL SELECT 37 AS sv_idx, 0.5 AS dual_coeff, 1.3064614000035888 AS sv_0, -0.410568581365622 AS sv_1, 0.7590075632840488 AS sv_2, 0.8373135178624604 AS sv_3, 0.05229648378796648 AS sv_4, -0.4639334115141312 AS sv_5, -0.49980763422899 AS sv_6 UNION ALL SELECT 38 AS sv_idx, -0.5 AS dual_coeff, -0.046230422927213057 AS sv_0, 0.855207017545685 AS sv_1, -0.7244878032110394 AS sv_2, 0.40210666932700495 AS sv_3, -0.4949253644699675 AS sv_4, -0.043404677705886775 AS sv_5, 0.37794087981170554 AS sv_6 UNION ALL SELECT 39 AS sv_idx, -0.5 AS dual_coeff, 1.2104590506184556 AS sv_0, -0.10546553379978017 AS sv_1, 0.2521627721029242 AS sv_2, 0.9383459985773588 AS sv_3, 0.2331884221736034 AS sv_4, -1.033810401531539 AS sv_5, -1.1665105760535193 AS sv_6 UNION ALL SELECT 40 AS sv_idx, 0.5 AS dual_coeff, -0.6785288347040923 AS sv_0, 0.3448740761716733 AS sv_1, -0.1423449830974219 AS sv_2, 0.7677170335117338 AS sv_3, -0.3519984431991264 AS sv_4, 0.810901542861568 AS sv_5, 1.0828508005817077 AS sv_6 UNION ALL SELECT 41 AS sv_idx, -0.5 AS dual_coeff, 0.741920149455067 AS sv_0, 3.001174263479659 AS sv_1, 0.6842694584880589 AS sv_2, -3.0414586242641684 AS sv_3, 1.1990795696810597 AS sv_4, -0.1488810009269273 AS sv_5, -0.8166600591228584 AS sv_6 UNION ALL SELECT 42 AS sv_idx, -0.5 AS dual_coeff, -0.6928120557322595 AS sv_0, 1.611702483532756 AS sv_1, 2.868807531448415 AS sv_2, 1.217323849768525 AS sv_3, -2.1983052566936254 AS sv_4, -0.7777626015727086 AS sv_5, 0.36798675498443423 AS sv_6 UNION ALL SELECT 43 AS sv_idx, 0.5 AS dual_coeff, -0.6368323178812518 AS sv_0, -0.4842001924641832 AS sv_1, -1.1451966477367586 AS sv_2, 0.18971228468809437 AS sv_3, 0.5397663624525064 AS sv_4, 0.840840359524351 AS sv_5, 0.20188518250619086 AS sv_6 UNION ALL SELECT 44 AS sv_idx, -0.5 AS dual_coeff, -0.9274558960973724 AS sv_0, -0.3499945587614435 AS sv_1, 0.280675269287912 AS sv_2, -0.1617396617081555 AS sv_3, -0.3313526217100109 AS sv_4, -0.2403923796016281 AS sv_5, 0.10859995175612899 AS sv_6 UNION ALL SELECT 45 AS sv_idx, 0.5 AS dual_coeff, -0.16316719708458274 AS sv_0, 0.904833453614433 AS sv_1, -0.9290224754769678 AS sv_2, -0.4131455150235389 AS sv_3, -0.8619414092397778 AS sv_4, -0.34243312767321116 AS sv_5, 0.8335620090666473 AS sv_6 UNION ALL SELECT 46 AS sv_idx, -0.5 AS dual_coeff, 1.44887652917814 AS sv_0, -0.2301978229424859 AS sv_1, -0.5149240124430849 AS sv_2, 0.06465875099709507 AS sv_3, 0.2257780722286749 AS sv_4, -1.407512961368578 AS sv_5, -0.7483227329052132 AS sv_6 UNION ALL SELECT 47 AS sv_idx, 0.5 AS dual_coeff, -0.6257223900846298 AS sv_0, -0.12394162008555683 AS sv_1, -1.0580190010468615 AS sv_2, -0.381776031364047 AS sv_3, -0.047129690946334714 AS sv_4, 0.1775101808874416 AS sv_5, 0.4139681974610006 AS sv_6 UNION ALL SELECT 48 AS sv_idx, -0.5 AS dual_coeff, -0.4631189015577663 AS sv_0, 0.0817979187406059 AS sv_1, -1.1639097540389904 AS sv_2, 0.593888658669423 AS sv_3, 0.3034309047713783 AS sv_4, 0.556785813125175 AS sv_5, 0.6788397063780356 AS sv_6 UNION ALL SELECT 49 AS sv_idx, 0.5 AS dual_coeff, -0.4871876367145925 AS sv_0, 0.00148121990248204 AS sv_1, -1.0777240044342995 AS sv_2, 0.1015891226783655 AS sv_3, 0.16851176510104332 AS sv_4, 0.41288295236843137


text:
-- 菜单
CREATE TABLE sys_menu (
  menu_id NUMBER(20, 0) NOT NULL,
  parent_id NUMBER(20, 0) NOT NULL,
  name varchar2(50),
  url varchar2(200),
  perms varchar2(500),
  type NUMBER(2, 0),
  icon varchar2(50),
  order_num NUMBER(8, 0),
  PRIMARY KEY (menu_id)
);

-- 系统用户
CREATE TABLE sys_user (
  user_id NUMBER(20, 0) NOT NULL,
  username varchar2(50) NOT NULL,
  password varchar2(100),
  salt varchar2(20),
  email varchar2(100),
  mobile varchar2(100),
  status NUMBER(2, 0) NOT NULL,
  create_user_id NUMBER(20, 0) NOT NULL,
  create_time timestamp,
  PRIMARY KEY (user_id)
);
CREATE UNIQUE INDEX index_sys_user_username on sys_user(username);

-- 系统用户Token
CREATE TABLE sys_user_token (
  user_id NUMBER(20, 0) NOT NULL,
  token varchar2(100) NOT NULL,
  expire_time timestamp,
  update_time timestamp,
  PRIMARY KEY (user_id)
);
CREATE UNIQUE INDEX index_token on sys_user_token(token);

-- 系统验证码
CREATE TABLE sys_captcha (
  uuid varchar2(36) NOT NULL,
  code varchar2(6) NOT NULL,
  expire_time timestamp,
  PRIMARY KEY (uuid)
);

-- 角色
CREATE TABLE sys_role (
  role_id NUMBER(20, 0) NOT NULL,
  role_name varchar2(100),
  remark varchar2(100),
  create_user_id NUMBER(20, 0) NOT NULL,
  create_time timestamp,
  PRIMARY KEY (role_id)
);

-- 用户与角色对应关系
CREATE TABLE sys_user_role (
  id NUMBER(20, 0) NOT NULL,
  user_id NUMBER(20, 0) NOT NULL,
  role_id NUMBER(20, 0) NOT NULL,
  PRIMARY KEY (id)
);

-- 角色与菜单对应关系
CREATE TABLE sys_role_menu (
  id NUMBER(20, 0) NOT NULL,
  role_id NUMBER(20, 0) NOT NULL,
  menu_id NUMBER(20, 0) NOT NULL,
  PRIMARY KEY (id)
);

-- 系统配置信息
CREATE TABLE sys_config (
  id NUMBER(20, 0) NOT NULL,
  param_key varchar2(50),
  param_value varchar2(4000),
  status NUMBER(2, 0) DEFAULT 1 NOT NULL,
  remark varchar2(500),
  PRIMARY KEY (id)
);
CREATE UNIQUE INDEX index_param_key on sys_config(param_key);

-- 系统日志
CREATE TABLE sys_log (
  id NUMBER(20, 0) NOT NULL,
  username varchar2(50),
  operation varchar2(50),
  method varchar2(200),
  params clob,
  time NUMBER(20, 0) NOT NULL,
  ip varchar2(64),
  create_date timestamp,
  PRIMARY KEY (id)
);

-- 文件上传
CREATE TABLE sys_oss (
  id NUMBER(20, 0) NOT NULL,
  url varchar2(200),
  create_date timestamp,
  PRIMARY KEY (id)
);

-- 定时任务
CREATE TABLE schedule_job (
  job_id NUMBER(20, 0) NOT NULL,
  bean_name varchar2(200),
  params varchar2(2000),
  cron_expression varchar2(100),
  status NUMBER(2, 0) NOT NULL,
  remark varchar2(255),
  create_time timestamp,
  PRIMARY KEY (job_id)
);

-- 定时任务日志
CREATE TABLE schedule_job_log (
  log_id NUMBER(20, 0) NOT NULL,
  job_id NUMBER(20, 0) NOT NULL,
  bean_name varchar2(200),
  params varchar2(2000),
  status NUMBER(2, 0) NOT NULL,
  error varchar2(2000),
  times NUMBER(10, 0) NOT NULL,
  create_time timestamp,
  PRIMARY KEY (log_id)
);
CREATE INDEX index_job_id on schedule_job_log(job_id);

-- 用户表
CREATE TABLE tb_user (
  user_id NUMBER(20, 0) NOT NULL,
  username varchar2(50) NOT NULL,
  mobile varchar2(20) NOT NULL,
  password varchar2(64),
  create_time timestamp,
  PRIMARY KEY (user_id)
);
CREATE UNIQUE INDEX index_tb_user_username on tb_user(username);

INSERT INTO sys_user (user_id, username, password, salt, email, mobile, status, create_user_id, create_time) VALUES ('1', 'admin', '9ec9750e709431dad22365cabc5c625482e574c74adaebba7dd02f1129e4ce1d', 'YzcmCZNvbXocrsz9dm8e', 'root@renren.io', '13612345678', '1', '1', CURRENT_DATE);

INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (1, 0, '系统管理', NULL, NULL, 0, 'system', 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (2, 1, '管理员列表', 'sys/user', NULL, 1, 'admin', 1);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (3, 1, '角色管理', 'sys/role', NULL, 1, 'role', 2);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (4, 1, '菜单管理', 'sys/menu', NULL, 1, 'menu', 3);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (5, 1, 'SQL监控', 'http://localhost:8080/renren-fast/druid/sql.html', NULL, 1, 'sql', 4);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (6, 1, '定时任务', 'job/schedule', NULL, 1, 'job', 5);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (7, 6, '查看', NULL, 'sys:schedule:list,sys:schedule:info', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (8, 6, '新增', NULL, 'sys:schedule:save', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (9, 6, '修改', NULL, 'sys:schedule:update', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (10, 6, '删除', NULL, 'sys:schedule:delete', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (11, 6, '暂停', NULL, 'sys:schedule:pause', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (12, 6, '恢复', NULL, 'sys:schedule:resume', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (13, 6, '立即执行', NULL, 'sys:schedule:run', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (14, 6, '日志列表', NULL, 'sys:schedule:log', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (15, 2, '查看', NULL, 'sys:user:list,sys:user:info', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (16, 2, '新增', NULL, 'sys:user:save,sys:role:select', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (17, 2, '修改', NULL, 'sys:user:update,sys:role:select', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (18, 2, '删除', NULL, 'sys:user:delete', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (19, 3, '查看', NULL, 'sys:role:list,sys:role:info', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (20, 3, '新增', NULL, 'sys:role:save,sys:menu:list', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (21, 3, '修改', NULL, 'sys:role:update,sys:menu:list', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (22, 3, '删除', NULL, 'sys:role:delete', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (23, 4, '查看', NULL, 'sys:menu:list,sys:menu:info', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (24, 4, '新增', NULL, 'sys:menu:save,sys:menu:select', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (25, 4, '修改', NULL, 'sys:menu:update,sys:menu:select', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (26, 4, '删除', NULL, 'sys:menu:delete', 2, NULL, 0);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (27, 1, '参数管理', 'sys/config', 'sys:config:list,sys:config:info,sys:config:save,sys:config:update,sys:config:delete', 1, 'config', 6);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (29, 1, '系统日志', 'sys/log', 'sys:log:list', 1, 'log', 7);
INSERT INTO sys_menu(menu_id, parent_id, name, url, perms, type, icon, order_num) VALUES (30, 1, '文件上传', 'oss/oss', 'sys:oss:all', 1, 'oss', 6);

INSERT INTO sys_config (id, param_key, param_value, status, remark) VALUES (1, 'CLOUD_STORAGE_CONFIG_KEY',  '{"aliyunAccessKeyId":"","aliyunAccessKeySecret":"","aliyunBucketName":"","aliyunDomain":"","aliyunEndPoint":"","aliyunPrefix":"","qcloudBucketName":"","qcloudDomain":"","qcloudPrefix":"","qcloudSecretId":"","qcloudSecretKey":"","qiniuAccessKey":"NrgMfABZxWLo5B-YYSjoE8-AZ1EISdi1Z3ubLOeZ","qiniuBucketName":"ios-app","qiniuDomain":"http://7xlij2.com1.z0.glb.clouddn.com","qiniuPrefix":"upload","qiniuSecretKey":"uIwJHevMRWU0VLxFvgy0tAcOdGqasdtVlJkdy6vV","type":1}', '0', '云存储配置信息');

INSERT INTO schedule_job (job_id, bean_name, params, cron_expression, status, remark, create_time) VALUES (1, 'captchaCleanTask', '', '0 /10 * * * ?', '0', '验证码清理定时器，每10分钟执行一次', CURRENT_DATE);

-- 账号：13612345678  密码：admin
INSERT INTO tb_user (user_id, username, mobile, password, create_time) VALUES (1, 'mark', '13612345678', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', CURRENT_DATE);

--  quartz自带表结构
CREATE TABLE qrtz_job_details
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  JOB_NAME  VARCHAR2(200) NOT NULL,
  JOB_GROUP VARCHAR2(200) NOT NULL,
  DESCRIPTION VARCHAR2(250) NULL,
  JOB_CLASS_NAME   VARCHAR2(250) NOT NULL,
  IS_DURABLE VARCHAR2(1) NOT NULL,
  IS_NONCONCURRENT VARCHAR2(1) NOT NULL,
  IS_UPDATE_DATA VARCHAR2(1) NOT NULL,
  REQUESTS_RECOVERY VARCHAR2(1) NOT NULL,
  JOB_DATA BLOB NULL,
  CONSTRAINT QRTZ_JOB_DETAILS_PK PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
);
CREATE TABLE qrtz_triggers
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  TRIGGER_NAME VARCHAR2(200) NOT NULL,
  TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  JOB_NAME  VARCHAR2(200) NOT NULL,
  JOB_GROUP VARCHAR2(200) NOT NULL,
  DESCRIPTION VARCHAR2(250) NULL,
  NEXT_FIRE_TIME NUMBER(13) NULL,
  PREV_FIRE_TIME NUMBER(13) NULL,
  PRIORITY NUMBER(13) NULL,
  TRIGGER_STATE VARCHAR2(16) NOT NULL,
  TRIGGER_TYPE VARCHAR2(8) NOT NULL,
  START_TIME NUMBER(13) NOT NULL,
  END_TIME NUMBER(13) NULL,
  CALENDAR_NAME VARCHAR2(200) NULL,
  MISFIRE_INSTR NUMBER(2) NULL,
  JOB_DATA BLOB NULL,
  CONSTRAINT QRTZ_TRIGGERS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  CONSTRAINT QRTZ_TRIGGER_TO_JOBS_FK FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)
  REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)
);
CREATE TABLE qrtz_simple_triggers
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  TRIGGER_NAME VARCHAR2(200) NOT NULL,
  TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  REPEAT_COUNT NUMBER(7) NOT NULL,
  REPEAT_INTERVAL NUMBER(12) NOT NULL,
  TIMES_TRIGGERED NUMBER(10) NOT NULL,
  CONSTRAINT QRTZ_SIMPLE_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  CONSTRAINT QRTZ_SIMPLE_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE qrtz_cron_triggers
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  TRIGGER_NAME VARCHAR2(200) NOT NULL,
  TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  CRON_EXPRESSION VARCHAR2(120) NOT NULL,
  TIME_ZONE_ID VARCHAR2(80),
  CONSTRAINT QRTZ_CRON_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  CONSTRAINT QRTZ_CRON_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE qrtz_simprop_triggers
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  TRIGGER_NAME VARCHAR2(200) NOT NULL,
  TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  STR_PROP_1 VARCHAR2(512) NULL,
  STR_PROP_2 VARCHAR2(512) NULL,
  STR_PROP_3 VARCHAR2(512) NULL,
  INT_PROP_1 NUMBER(10) NULL,
  INT_PROP_2 NUMBER(10) NULL,
  LONG_PROP_1 NUMBER(13) NULL,
  LONG_PROP_2 NUMBER(13) NULL,
  DEC_PROP_1 NUMERIC(13,4) NULL,
  DEC_PROP_2 NUMERIC(13,4) NULL,
  BOOL_PROP_1 VARCHAR2(1) NULL,
  BOOL_PROP_2 VARCHAR2(1) NULL,
  CONSTRAINT QRTZ_SIMPROP_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  CONSTRAINT QRTZ_SIMPROP_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE qrtz_blob_triggers
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  TRIGGER_NAME VARCHAR2(200) NOT NULL,
  TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  BLOB_DATA BLOB NULL,
  CONSTRAINT QRTZ_BLOB_TRIG_PK PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
  CONSTRAINT QRTZ_BLOB_TRIG_TO_TRIG_FK FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
  REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
CREATE TABLE qrtz_calendars
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  CALENDAR_NAME  VARCHAR2(200) NOT NULL,
  CALENDAR BLOB NOT NULL,
  CONSTRAINT QRTZ_CALENDARS_PK PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)
);
CREATE TABLE qrtz_paused_trigger_grps
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  TRIGGER_GROUP  VARCHAR2(200) NOT NULL,
  CONSTRAINT QRTZ_PAUSED_TRIG_GRPS_PK PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)
);
CREATE TABLE qrtz_fired_triggers
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  ENTRY_ID VARCHAR2(95) NOT NULL,
  TRIGGER_NAME VARCHAR2(200) NOT NULL,
  TRIGGER_GROUP VARCHAR2(200) NOT NULL,
  INSTANCE_NAME VARCHAR2(200) NOT NULL,
  FIRED_TIME NUMBER(13) NOT NULL,
  SCHED_TIME NUMBER(13) NOT NULL,
  PRIORITY NUMBER(13) NOT NULL,
  STATE VARCHAR2(16) NOT NULL,
  JOB_NAME VARCHAR2(200) NULL,
  JOB_GROUP VARCHAR2(200) NULL,
  IS_NONCONCURRENT VARCHAR2(1) NULL,
  REQUESTS_RECOVERY VARCHAR2(1) NULL,
  CONSTRAINT QRTZ_FIRED_TRIGGER_PK PRIMARY KEY (SCHED_NAME,ENTRY_ID)
);
CREATE TABLE qrtz_scheduler_state
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  INSTANCE_NAME VARCHAR2(200) NOT NULL,
  LAST_CHECKIN_TIME NUMBER(13) NOT NULL,
  CHECKIN_INTERVAL NUMBER(13) NOT NULL,
  CONSTRAINT QRTZ_SCHEDULER_STATE_PK PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)
);
CREATE TABLE qrtz_locks
(
  SCHED_NAME VARCHAR2(120) NOT NULL,
  LOCK_NAME  VARCHAR2(40) NOT NULL,
  CONSTRAINT QRTZ_LOCKS_PK PRIMARY KEY (SCHED_NAME,LOCK_NAME)
);

create index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY);
create index idx_qrtz_j_grp on qrtz_job_details(SCHED_NAME,JOB_GROUP);

create index idx_qrtz_t_j on qrtz_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
create index idx_qrtz_t_jg on qrtz_triggers(SCHED_NAME,JOB_GROUP);
create index idx_qrtz_t_c on qrtz_triggers(SCHED_NAME,CALENDAR_NAME);
create index idx_qrtz_t_g on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP);
create index idx_qrtz_t_state on qrtz_triggers(SCHED_NAME,TRIGGER_STATE);
create index idx_qrtz_t_n_state on qrtz_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
create index idx_qrtz_t_n_g_state on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
create index idx_qrtz_t_next_fire_time on qrtz_triggers(SCHED_NAME,NEXT_FIRE_TIME);
create index idx_qrtz_t_nft_st on qrtz_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
create index idx_qrtz_t_nft_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
create index idx_qrtz_t_nft_st_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
create index idx_qrtz_t_nft_st_misfire_grp on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);

create index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME);
create index idx_qrtz_ft_inst_job_req_rcvry on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
create index idx_qrtz_ft_j_g on qrtz_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
create index idx_qrtz_ft_jg on qrtz_fired_triggers(SCHED_NAME,JOB_GROUP);
create index idx_qrtz_ft_t_g on qrtz_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
create index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP);

domain: sql


text:
-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 17, 2022 at 08:34 AM
-- Server version: 10.4.22-MariaDB
-- PHP Version: 8.0.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `library`
--

-- --------------------------------------------------------

--
-- Table structure for table `account`
--

CREATE TABLE `account` (
  `Account_ID` int(11) NOT NULL,
  `Username` varchar(100) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `Password` varchar(20) NOT NULL,
  `Gender` varchar(6) NOT NULL,
  `Admin` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `account`
--

INSERT INTO `account` (`Account_ID`, `Username`, `Email`, `Password`, `Gender`, `Admin`) VALUES
(0, 'Guest', 'Guest', '', '', 0),
(4, ' Abdo Ali ', 'abdoali@gmail.com', '0000', ' Male ', 1),
(5, 'Bavly Badry', 'bavlybadry@gmail.com', '1234', 'Male', 0),
(7, 'Shadow', 'shadow_company@gmail.com', 'Shadow141', 'Male', 1);

-- --------------------------------------------------------

--
-- Table structure for table `books`
--

CREATE TABLE `books` (
  `Book_id` int(11) NOT NULL,
  `Book_Name` varchar(255) NOT NULL,
  `Book_Cover` longblob NOT NULL,
  `Author` varchar(255) NOT NULL,
  `Category` varchar(25) NOT NULL,
  `book` longblob NOT NULL,
  `NoOfPages` int(4) NOT NULL,
  `Most_Read` int(11) NOT NULL,
  `Time_Added` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


text:
-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 19, 2017 at 04:12 AM
-- Server version: 10.1.28-MariaDB
-- PHP Version: 7.1.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `contatos`
--

-- --------------------------------------------------------

--
-- Table structure for table `contato`
--

CREATE TABLE `contato` (
  `id` int(20) NOT NULL,
  `nome` varchar(30) NOT NULL,
  `telefone` varchar(30) NOT NULL,
  `email` varchar(30) NOT NULL,
  `nascimento` varchar(30) NOT NULL,
  `endereco` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `contato`
--

INSERT INTO `contato` (`id`, `nome`, `telefone`, `email`, `nascimento`, `endereco`) VALUES
(27, 'Miguel Peres', '34996929374', 'miguel@ufu.br', '24/09/1997', 'Rua A, 541'),
(29, 'Samuel', '512', 'Samuel@samuel.com', '1235', 'rua do Samuel,443');

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `email` text NOT NULL,
  `pasw` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `contato`
--
ALTER TABLE `contato`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `contato`
--
ALTER TABLE `contato`
  MODIFY `id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


text:
/* Identities */

INSERT INTO identities (
  type, data
) VALUES ( /* C=CH, O=Linux strongSwan, CN=strongSwan Root CA */
  9, X'3045310B300906035504061302434831193017060355040A13104C696E7578207374726F6E675377616E311B3019060355040313127374726F6E675377616E20526F6F74204341'
 );

INSERT INTO identities (
  type, data
) VALUES ( /* subjkey of 'C=CH, O=Linux strongSwan, CN=strongSwan Root CA' */
  11, X'5da7dd700651327ee7b66db3b5e5e060ea2e4def'
 );

INSERT INTO identities (
  type, data
) VALUES ( /* keyid of 'C=CH, O=Linux strongSwan, CN=strongSwan Root CA' */
  11, X'ae096b87b44886d3b820978623dabd0eae22ebbc'
 );

INSERT INTO identities (
  type, data
) VALUES ( /* moon.strongswan.org */
  2, X'6d6f6f6e2e7374726f6e677377616e2e6f7267'
 );

INSERT INTO identities (
  type, data
) VALUES ( /* sun.strongswan.org */
  2, X'73756e2e7374726f6e677377616e2e6f7267'
 );

INSERT INTO identities (
  type, data
) VALUES ( /* subjkey of 'C=CH, O=Linux strongSwan, CN=sun.strongswan.org' */
  11, X'56d69e2fdaa8a1cd195c2353e7c5b67096e30bfb'
 );

/* Certificates */

INSERT INTO certificates (
   type, keytype, data
) VALUES ( /* C=CH, O=Linux strongSwan, CN=strongSwan Root CA */
  1, 1, X'308203b53082029da003020102020100300d06092a864886f70d01010405003045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f74204341301e170d3034303931303131303134355a170d3134303930383131303134355a3045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f7420434130820122300d06092a864886f70d01010105000382010f003082010a0282010100bff25f62ea3d566e58b3c87a49caf3ac61cfa96377734d842db3f8fd6ea023f7b0132e66265012317386729c6d7c427a8d9f167be138e8ebae2b12b95933baef36a315c3ddf224cee4bb9bd578135d0467382629621ff96b8d45f6e002e5083662dce181805c140b3f2ce93f83aee3c861cff610a39f0189cb3a3c7cb9bf7e2a09544e2170efaa18fdd4ff20fa94be176d7fecff821f68d17152041d9b46f0cfcfc1e4cf43de5d3f3a587763afe9267f53b11699b3264fc55c5189f5682871166cb98307950569641fa30ffb50de134fed2f973cef1a392827862bc4ddaa97bbb01442e293c41070d07224d4be47ae2753eb2bed4bc1da91c68ec780c4620f0f0203010001a381af3081ac300f0603551d130101ff040530030101ff300b0603551d0f040403020106301d0603551d0e041604145da7dd700651327ee7b66db3b5e5e060ea2e4def306d0603551d230466306480145da7dd700651327ee7b66db3b5e5e060ea2e4defa149a4473045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f74204341820100300d06092a864886f70d010104050003820101009ad74e3e60592dfb9b21c78628bd76b63090c1720c74bf94753cad6fddadc9c776eb39d3bfaa52136bf528840078386308fcf79503bd3d1ad6c15ac38e10c846bff7888a03cfe7fa0e644b522b2af5aedf0bbc508dc48330a180757772771095059b2be148f58dc0c753b59e9d6bfb02e9b685a928a284531b187313fd2b835bc9ea27d0020739a8d485e88bdede9a45cde6d28ed553b0e8e92dabf877bed59abf9d151f15e4f2d00b5e6e49fcb665293d2296697926c2954dae367542ef6e98053e76d2728732f6ce69f284f0b856aa6c2823a9ee29b280a66f50828f9b5cf27f84feca3c31c24897db156c7a833768ab306f51286457a51f09dd53bbb4190f'
);

INSERT INTO certificates (
   type, keytype, data
) VALUES ( /* C=CH, O=Linux strongSwan, CN=sun.strongswan.org */
  1, 1, X'3082042030820308a003020102020116300d06092a864886f70d01010b05003045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f74204341301e170d3039303832373039353930345a170d3134303832363039353930345a3045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b30190603550403131273756e2e7374726f6e677377616e2e6f726730820122300d06092a864886f70d01010105000382010f003082010a0282010100df95548a67e90e63694fff10dea9e80e5c49f51f8412b49856b695a145661fd8d21eba017aaaad0dd7f75ee1ed836c4a4ebca28c18f61f48de03b1ee135506c26bd958dd602f9bd2ae4d4e16b4f7f0399a29affe9ad88faa34c9ac31a0f8e80ecf5887b0fb29ff85f1f920934b2d9472595fae89edd36b1576e0d7106577e129e4eaf615b119f50594a51413ba176936dff8f58bb1439f437a663d2116f0b2b2821c7d261ab26c0dda9e6f46a9fbc42f4971e30add5fafd30d29668014040b2902387b475ad67b4f08440b784f37dfe34d441806b9f9342aa193dde017aabc5af401085099d570f7b141da94323714a5715bf1637345607c1208770432ba16c10203010001a38201193082011530090603551d1304023000300b0603551d0f0404030203a8301d0603551d0e0416041456d69e2fdaa8a1cd195c2353e7c5b67096e30bfb306d0603551d230466306480145da7dd700651327ee7b66db3b5e5e060ea2e4defa149a4473045310b300906035504061302434831193017060355040a13104c696e7578207374726f6e675377616e311b3019060355040313127374726f6e675377616e20526f6f74204341820100301d0603551d1104163014821273756e2e7374726f6e677377616e2e6f726730130603551d25040c300a06082b0601050507030130390603551d1f04323030302ea02ca02a8628687474703a2f2f63726c2e7374726f6e677377616e2e6f72672f7374726f6e677377616e2e63726c300d06092a864886f70d01010b05000382010100a37ecb613f40c31d0c2bf9c0159a4f26a52bd04cbe3b952472c771ee7774d12966a6241163c2a6b915c20509c312074bb2d777b254234f4a49120585d29cf1c9b9c8e9e3b360084b7e16abb7e5f58544623466d78e0826dace08e7e2b7d9b54890d12a96c62777fe85f68a3964d3b4fde4d3aee2d97e435eff372104629efd9ecc69c57095859609b32d055e0ad5de5d19c45182943d5a2bc39666ad361f7096c7034a04869371c991bc67abaf7954def351b0d6014bd65a15796c80e394047fc724fd029a88c62c95551ba57d8ac4ed4d391dbb7a1a002ba0028ba7aa89ba9f7e4f14002928ca23027485feceb98c90f1366d5474bb00e866f93ff91bf8c618'
);

INSERT INTO certificate_identity (
  certificate, identity
) VALUES (
  1, 1
);

INSERT INTO certificate_identity (
  certificate, identity
) VALUES (
  1, 2
);

INSERT INTO certificate_identity (
  certificate, identity
) VALUES (
  1, 3
);

INSERT INTO certificate_identity (
  certificate, identity
) VALUES (
  2, 5
);

INSERT INTO certificate_identity (
  certificate, identity
) VALUES (
  2, 6
);

/* Private Keys */

INSERT INTO private_keys (
   type, data
) VALUES ( /* key of 'C=CH, O=Linux strongSwan, CN=sun.strongswan.org' */
  1, X'308204a40201000282010100df95548a67e90e63694fff10dea9e80e5c49f51f8412b49856b695a145661fd8d21eba017aaaad0dd7f75ee1ed836c4a4ebca28c18f61f48de03b1ee135506c26bd958dd602f9bd2ae4d4e16b4f7f0399a29affe9ad88faa34c9ac31a0f8e80ecf5887b0fb29ff85f1f920934b2d9472595fae89edd36b1576e0d7106577e129e4eaf615b119f50594a51413ba176936dff8f58bb1439f437a663d2116f0b2b2821c7d261ab26c0dda9e6f46a9fbc42f4971e30add5fafd30d29668014040b2902387b475ad67b4f08440b784f37dfe34d441806b9f9342aa193dde017aabc5af401085099d570f7b141da94323714a5715bf1637345607c1208770432ba16c102030100010282010031f9d618cddb393d1d5825426713016cdc5227b970b321acff8cf66b42f0ede3702c30158e8ec1f9db314f031f2d0632a1e0e6507c6fdf545153f01cb0338c3c3f11291cea9819b381048494ecc492ecbd39de3e01ecb048325e75dfee045512a2643e885fcbe672d140877885105e2325390ef183b883321c0d6be51d592b79dfd0f3b522a29509f6c5d3d98c59cc4f4fe9aef978340ffc667e78fd27560eae7245d2d66fa10ad55ef2277ebcec65d1d63cc6b60cbb4d7ba77142554d8817ed174fd539fa8160fc60043f45f4f40dd5989b85874a049bf7f356f250591f6cb2de183e94d8287d712f4df0643b94325b7d4b22bd2d095eb384921c63d33168a502818100f6c92d6977e13cb7b41028dd79010eab910ab83ac2be248a08eb8057804348eb1dc3afb92d67d855bfe891ef0def494bdd109b95c8f5a4e296522d8efd2a4425d0d176e8b0da033d717b77fdb0a14931c2e13b08c7137f0dbf4e1a0ef6919e2c458e40bbecf20004180705e1156bb1d7de09939a8ad096e5346f49e5032a643702818100e7ee610601d43043bea5d304e6502249ce4f70d4de8e903a8fe3a2902878fc7157cb70bf74bc7e0acd7e7a0c3ae1cf2255a22f344a3b9fb13d2347b75ea6a73d15e1327051acffb7f4e56c58f999d7c2e2d878bce0ac09ca2f18d759775c93aa698a45c0c554b99a7147475f1b993fe46c13fed12499006094e806de6db350c702818100e26dd577d6a1579769e405caa7329c26388f3057e1c49a3bf85133d19502a74dea6258c1bbf272e0c292fe0aebab28822dd4061cd964e123712ef75421defce601819eeb8310953674000829413dcaad9894151949a70ec52b48dac9eddbcfd7e8fdcb5161e6ecb2d4e4e4b50f755f98a3c5ffa325489b9ab39084a9564d37e302818047f5087b21a42099541404a55783732fece76ebd4c9374a206b47c62377c59ee1c6c0cfe098cd59a2a695c1a61465fca6a41185e23cddddcd27818af0699b3f75acb74a7ae5f7b332ab2e76baf7d1098f162720b3fb580900f0ea8f9a3f3c008b617e54e4aaadfaed0086a5752abb84bf95036d5d281f9c0fd5203978cf77e4f028181009b29e83640d4b6aa13c81bd79aa890d13aec66c5bff4eddece05cb60320c55bca86a5bd856e364b2809e7d7f2cf94231416927dc39d7f88242b211ac15a38e7fabaca6210537ed1e2f178096f59dc27da21bc09a7b2dc1d5cc7d3bb28fc736c66e6ba19609208b254e4c782462673936445892f4a21a2cfe2066776fe255ce89'
);

INSERT INTO private_key_identity (
  private_key, identity
) VALUES (
  1, 5
);

INSERT INTO private_key_identity (
  private_key, identity
) VALUES (
  1, 6
);

/* Configurations */

INSERT INTO ike_configs (
  local, remote
) VALUES (
  'PH_IP_SUN', 'PH_IP_MOON'
);

INSERT INTO peer_configs (
  name, ike_cfg, local_id, remote_id, mobike, dpd_delay
) VALUES (
  'net-net', 1, 5, 4, 0, 0
);

INSERT INTO child_configs (
  name, updown
) VALUES (
  'net-net', 'ipsec _updown iptables'
);

INSERT INTO peer_config_child_config (
  peer_cfg, child_cfg
) VALUES (
  1, 1
);

INSERT INTO traffic_selectors (
  type, start_addr, end_addr
) VALUES (
  7, X'0a010000', X'0a01ffff'
);

INSERT INTO traffic_selectors (
  type, start_addr, end_addr
) VALUES (
  7, X'0a020000', X'0a02ffff'
);

INSERT INTO child_config_traffic_selector (
  child_cfg, traffic_selector, kind
) VALUES (
  1, 2, 0
);

INSERT INTO child_config_traffic_selector (
	child_cfg, traffic_selector, kind
) VALUES (
  1, 1, 1
);


text:
SELECT acc.location, count(*)
FROM
site as s,
so_user as u1,
question as q1,
answer as a1,
tag as t1,
tag_question as tq1,
badge as b,
account as acc
WHERE
s.site_id = q1.site_id
AND s.site_id = u1.site_id
AND s.site_id = a1.site_id
AND s.site_id = t1.site_id
AND s.site_id = tq1.site_id
AND s.site_id = b.site_id
AND q1.id = tq1.question_id
AND q1.id = a1.question_id
AND a1.owner_user_id = u1.id
AND t1.id = tq1.tag_id
AND b.user_id = u1.id
AND acc.id = u1.account_id
AND (s.site_name in ('electronics','pt','stackoverflow','unix'))
AND (t1.name in ('appium','botframework','build.gradle','continuous-integration','deserialization','elixir','markdown','parent-child','passport.js','retrofit','thymeleaf'))
AND (q1.view_count >= 0)
AND (q1.view_count <= 100)
AND (u1.reputation >= 0)
AND (u1.reputation <= 10)
AND (b.name in ('Citizen Patrol','Cleanup','Famous Question','Fanatic','Necromancer','Supporter'))
GROUP BY acc.location
ORDER BY COUNT(*)
DESC
LIMIT 100


text:
--
-- Copyright 2005-2014 The Kuali Foundation
--
-- Licensed under the Educational Community License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.opensource.org/licenses/ecl2.php
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

-- Traveler Account Type change to use KualiCodeBase
-- KULRICE-11129 Samples for I22, population of Active Indicator field with initial values
UPDATE TRV_ACCT_TYPE SET ACTV_IND='Y'
/


text:
-- add deployment.lock row to property table --
INSERT INTO ACT_GE_PROPERTY
  VALUES ('deployment.lock', '0', 1);

-- add revision column to incident table --
ALTER TABLE ACT_RU_INCIDENT
  ADD REV_ INTEGER;

-- set initial incident revision to 1 --
UPDATE
  ACT_RU_INCIDENT
SET
  REV_ = 1;

-- set incident revision column to not null --
ALTER TABLE ACT_RU_INCIDENT
  MODIFY REV_ INTEGER NOT NULL;

-- case management

ALTER TABLE ACT_RU_VARIABLE
  ADD CASE_EXECUTION_ID_ varchar(64);

ALTER TABLE ACT_RU_VARIABLE
  ADD CASE_INST_ID_ varchar(64);

ALTER TABLE ACT_RU_TASK
  ADD CASE_EXECUTION_ID_ varchar(64);

ALTER TABLE ACT_RU_TASK
  ADD CASE_INST_ID_ varchar(64);

ALTER TABLE ACT_RU_TASK
  ADD CASE_DEF_ID_ varchar(64);

ALTER TABLE ACT_RU_EXECUTION
  ADD SUPER_CASE_EXEC_ varchar(64);

ALTER TABLE ACT_RU_EXECUTION
  ADD CASE_INST_ID_ varchar(64);

ALTER TABLE ACT_HI_OP_LOG
  ADD CASE_EXECUTION_ID_ varchar(64);

ALTER TABLE ACT_HI_OP_LOG
  ADD CASE_INST_ID_ varchar(64);

ALTER TABLE ACT_HI_OP_LOG
  ADD CASE_DEF_ID_ varchar(64);

ALTER TABLE ACT_HI_OP_LOG
  ADD PROC_DEF_KEY_ varchar(255);

ALTER TABLE ACT_HI_PROCINST
  ADD CASE_INST_ID_ varchar(64);

ALTER TABLE ACT_HI_TASKINST
  ADD CASE_EXECUTION_ID_ varchar(64);

ALTER TABLE ACT_HI_TASKINST
  ADD CASE_INST_ID_ varchar(64);

ALTER TABLE ACT_HI_TASKINST
  ADD CASE_DEF_ID_ varchar(64);

-- create case definition table --
create table ACT_RE_CASE_DEF (
    ID_ varchar(64) not null,
    REV_ integer,
    CATEGORY_ varchar(255),
    NAME_ varchar(255),
    KEY_ varchar(255) not null,
    VERSION_ integer not null,
    DEPLOYMENT_ID_ varchar(64),
    RESOURCE_NAME_ varchar(4000),
    DGRM_RESOURCE_NAME_ varchar(4000),
    primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

-- create case execution table --
create table ACT_RU_CASE_EXECUTION (
    ID_ varchar(64) NOT NULL,
    REV_ integer,
    CASE_INST_ID_ varchar(64),
    SUPER_CASE_EXEC_ varchar(64),
    BUSINESS_KEY_ varchar(255),
    PARENT_ID_ varchar(64),
    CASE_DEF_ID_ varchar(64),
    ACT_ID_ varchar(255),
    PREV_STATE_ integer,
    CURRENT_STATE_ integer,
    primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

-- create case sentry part table --

create table ACT_RU_CASE_SENTRY_PART (
    ID_ varchar(64) NOT NULL,
    REV_ integer,
    CASE_INST_ID_ varchar(64),
    CASE_EXEC_ID_ varchar(64),
    SENTRY_ID_ varchar(255),
    TYPE_ varchar(255),
    SOURCE_CASE_EXEC_ID_ varchar(64),
    STANDARD_EVENT_ varchar(255),
    SATISFIED_ boolean,
    primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

-- create unique constraint on ACT_RE_CASE_DEF --
alter table ACT_RE_CASE_DEF
    add constraint ACT_UNIQ_CASE_DEF
    unique (KEY_,VERSION_);

-- create index on business key --
create index ACT_IDX_CASE_EXEC_BUSKEY on ACT_RU_CASE_EXECUTION(BUSINESS_KEY_);

-- create foreign key constraints on ACT_RU_CASE_EXECUTION --
alter table ACT_RU_CASE_EXECUTION
    add constraint ACT_FK_CASE_EXE_CASE_INST
    foreign key (CASE_INST_ID_)
    references ACT_RU_CASE_EXECUTION(ID_) on delete cascade on update cascade;

alter table ACT_RU_CASE_EXECUTION
    add constraint ACT_FK_CASE_EXE_PARENT
    foreign key (PARENT_ID_)
    references ACT_RU_CASE_EXECUTION(ID_);

alter table ACT_RU_CASE_EXECUTION
    add constraint ACT_FK_CASE_EXE_CASE_DEF
    foreign key (CASE_DEF_ID_)
    references ACT_RE_CASE_DEF(ID_);

-- create foreign key constraints on ACT_RU_VARIABLE --
alter table ACT_RU_VARIABLE
    add constraint ACT_FK_VAR_CASE_EXE
    foreign key (CASE_EXECUTION_ID_)
    references ACT_RU_CASE_EXECUTION(ID_);

alter table ACT_RU_VARIABLE
    add constraint ACT_FK_VAR_CASE_INST
    foreign key (CASE_INST_ID_)
    references ACT_RU_CASE_EXECUTION(ID_);

-- create foreign key constraints on ACT_RU_TASK --
alter table ACT_RU_TASK
    add constraint ACT_FK_TASK_CASE_EXE
    foreign key (CASE_EXECUTION_ID_)
    references ACT_RU_CASE_EXECUTION(ID_);

alter table ACT_RU_TASK
  add constraint ACT_FK_TASK_CASE_DEF
  foreign key (CASE_DEF_ID_)
  references ACT_RE_CASE_DEF(ID_);

-- create foreign key constraints on ACT_RU_CASE_SENTRY_PART --
alter table ACT_RU_CASE_SENTRY_PART
    add constraint ACT_FK_CASE_SENTRY_CASE_INST
    foreign key (CASE_INST_ID_)
    references ACT_RU_CASE_EXECUTION(ID_);

alter table ACT_RU_CASE_SENTRY_PART
    add constraint ACT_FK_CASE_SENTRY_CASE_EXEC
    foreign key (CASE_EXEC_ID_)
    references ACT_RU_CASE_EXECUTION(ID_);

-- create filter table
create table ACT_RU_FILTER (
  ID_ varchar(64) not null,
  REV_ integer not null,
  RESOURCE_TYPE_ varchar(255) not null,
  NAME_ varchar(255) not null,
  OWNER_ varchar(255),
  QUERY_ LONGTEXT not null,
  PROPERTIES_ LONGTEXT,
  primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

-- add index to improve job executor performance
create index ACT_IDX_JOB_PROCINST on ACT_RU_JOB(PROCESS_INSTANCE_ID_);

-- create historic case instance/activity table and indexes --
create table ACT_HI_CASEINST (
    ID_ varchar(64) not null,
    CASE_INST_ID_ varchar(64) not null,
    BUSINESS_KEY_ varchar(255),
    CASE_DEF_ID_ varchar(64) not null,
    CREATE_TIME_ datetime not null,
    CLOSE_TIME_ datetime,
    DURATION_ bigint,
    STATE_ integer,
    CREATE_USER_ID_ varchar(255),
    SUPER_CASE_INSTANCE_ID_ varchar(64),
    primary key (ID_),
    unique (CASE_INST_ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

create table ACT_HI_CASEACTINST (
    ID_ varchar(64) not null,
    PARENT_ACT_INST_ID_ varchar(64),
    CASE_DEF_ID_ varchar(64) not null,
    CASE_INST_ID_ varchar(64) not null,
    CASE_ACT_ID_ varchar(255) not null,
    TASK_ID_ varchar(64),
    CALL_PROC_INST_ID_ varchar(64),
    CALL_CASE_INST_ID_ varchar(64),
    CASE_ACT_NAME_ varchar(255),
    CASE_ACT_TYPE_ varchar(255),
    CREATE_TIME_ datetime not null,
    END_TIME_ datetime,
    DURATION_ bigint,
    STATE_ integer,
    primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;

create index ACT_IDX_HI_CAS_I_CLOSE on ACT_HI_CASEINST(CLOSE_TIME_);
create index ACT_IDX_HI_CAS_I_BUSKEY on ACT_HI_CASEINST(BUSINESS_KEY_);
create index ACT_IDX_HI_CAS_A_I_CREATE on ACT_HI_CASEACTINST(CREATE_TIME_);
create index ACT_IDX_HI_CAS_A_I_END on ACT_HI_CASEACTINST(END_TIME_);
create index ACT_IDX_HI_CAS_A_I_COMP on ACT_HI_CASEACTINST(CASE_ACT_ID_, END_TIME_, ID_);
create index ACT_IDX_HI_CAS_A_I_CASEINST on ACT_HI_CASEACTINST(CASE_INST_ID_, CASE_ACT_ID_);

create index ACT_IDX_TASK_ASSIGNEE on ACT_RU_TASK(ASSIGNEE_);

-- add case instance/execution to historic variable instance and detail --
alter table ACT_HI_VARINST
  add CASE_INST_ID_ varchar(64);

alter table ACT_HI_VARINST
  add CASE_EXECUTION_ID_ varchar(64);

alter table ACT_HI_DETAIL
  add CASE_INST_ID_ varchar(64);

alter table ACT_HI_DETAIL
  add CASE_EXECUTION_ID_ varchar(64);

create index ACT_IDX_HI_DETAIL_CASE_INST on ACT_HI_DETAIL(CASE_INST_ID_);
create index ACT_IDX_HI_DETAIL_CASE_EXEC on ACT_HI_DETAIL(CASE_EXECUTION_ID_);
create index ACT_IDX_HI_CASEVAR_CASE_INST on ACT_HI_VARINST(CASE_INST_ID_);


text:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Create_CtuvattuHoadon`(IN `fromd` DATE, IN `tod` DATE, IN `ctid` VARCHAR(15))
BEGIN
SELECT kt.ctid,kt.soct, kt.ngay,kt.diengiai,kt.tkno, kt.tkco,kt.sotien as sotienkt,"" as sotienvn, vt.mahang, vt.makho ,th.tenhang,th.donvi,vt.soluong,vt.sotien ,kh.tengoi,kh.diachi,COUNT('hd.ctid') as demHoadon FROM ctuktoan as kt LEFT OUTER JOIN ctuvattu as vt ON  kt.ctid = vt.ctid RIGHT OUTER JOIN tenhang as th ON  vt.mahang = th.mahang RIGHT OUTER JOIN dmtenkho as kh ON  vt.makho = kh.makho RIGHT OUTER JOIN hoadon as hd ON  kt.ctid = hd.ctid where IF(ctid, kt.ngay BETWEEN fromd AND tod AND kt.ctid = ctid ,kt.ngay BETWEEN fromd AND tod ) AND LEFT(kt.tkco,3)="511" GROUP BY kt.ctid,vt.mahang ;
END$$
DELIMITER ;


text:
# test03_insert_small.sql
#
# MySQL query for inserting content into existing database tables
#

# Insert data into database testdb03
# Data inserted into tables: arkivskaper, arkiv, serie, stykke, mappe

# Using a small (limited) dataset (number of rows in tables stykke & mappe)
# Table rows: arkivskaper 2, arkiv 2, serie, 6, stykke 9, mappe 9, objekt 0

USE testdb03;

INSERT INTO arkivskaper (
	arkivskaperID, identifikator, navn, innhold, merknad)
VALUES
	('1','K-15290.150','Skodje kommune. Formannskapet/kommunestyret',null,null)
	,('2','K-15190.703','Volda kommune. Kommuneingeniøren',null,null)
;

INSERT INTO arkiv (
	arkivID, arkivskaperID, identifikator, depinstID, navn, innhold,
	startdato, sluttdato, arkivkodefra, arkivkodetil, merknad)
VALUES
	('1','1','K-15290.150','IKAM','Skodje kommune. Formannskapet/kommunestyret',null,null,null,null,null,null)
	,('2','2','K-15190.703','IKAM','Volda kommune. Kommuneingeniøren',null,null,null,null,null,null)
;

INSERT INTO serie (
	serieID, arkivID, identifikator, navn, innhold,
	startdato, sluttdato, arkivkodefra, arkivkodetil, merknad)
VALUES
	('1','1','Ab','Møtebøker kommunestyret',null,null,null,null,null,null)
	,('2','1','Aa','Møtebøker formannskapet',null,null,null,null,null,null)
	,('3','1','Ac','Sakslister formannskapet',null,null,null,null,null,null)
	,('4','1','Ca','Postjournalar formannskapet',null,null,null,null,null,null)
	,('5','2','Dba','Saksarkiv ordna etter Norsk Herredsforbund sin arkivnøkkel 1963-1965',null,'1963','1965',null,null,null)
	,('6','2','Dbb','Saksarkiv ordna etter Norsk Herredsforbund sin arkivnøkkel 1966-1967',null,'1966','1967',null,null,null)
;

INSERT INTO stykke (
	stykkeID, serieID, identifikator, navn, innhold,
	startdato, sluttdato, arkivkodefra, arkivkodetil, merknad)
VALUES
	('1','2','L0001','Møtebok',null,'19761101','19790326',null,null,null)
	,('14','1','L0001','Møtebok',null,'19761026','19811214',null,null,null)
	,('16','3','L0001','Sakslister  Del 1',null,'1977','1977',null,null,null)
	,('100','4','L0001','Postjournal',null,'19770104','19781031',null,null,null)
	,('119','5','L0001','Arkivkode 000-046',null,'1963','1965','0','46',null)
	,('120','5','L0002','Arkivkode 210-210',null,'1963','1965','210','210',null)
	,('121','6','L0003','Arkivkode 504-505.1',null,'1966','1967','504','505.1',null)
	,('122','6','L0002','Arkivkode 210-533',null,'1966','1967','210','533',null)
	,('123','6','L0001','Arkivkode 022-221',null,'1966','1967','22','221',null)
;

INSERT INTO mappe (
	mappeID, stykkeID, identifikator, navn, innhold,
	startdato, sluttdato, arkivkodefra, arkivkodetil, merknad)
VALUES
	('1','1','0001','Rådet for trygg trafikk',null,'1963','1965','2','2',null)
	,('2','1','0002','Bygningsrådet',null,'1963','1965','21','21',null)
	,('3','1','0003','Brannstyret',null,'1963','1965','22','22',null)
	,('4','1','0004','Teknisk utval',null,'1963','1965','23','23',null)
	,('5','1','0005','Møter i vassforyningsnemnda',null,'1963','1965','24','24',null)
	,('214','14','0001','Oppføring av braanngarasje på Fyrde Austefjorden',null,'1966','1967','510','510',null)
	,('259','14','0002','Branngarasje på Straumshamn',null,'1966','1967','510','510',null)
	,('308','16','0001','Vegar - vedlikehald diverse papir',null,'1966','1967','563','563',null)
	,('328','16','0002','Nye vegar med kommunalt sommarvedlikehald',null,'1966','1967','563','563',null)
;

INSERT INTO objekt (
	objektID, mappeID, navn, innhold, merknad,
	filKatalog, filNavn, filtypeExtention, filtypeNavn)
VALUES
	('1','1','Dokument 1','Rådet for trygg trafikk','Merknad dokument 1','objekt','Vedlikehold av Noark 5.txt','TXT','TXT-dokument')
	,('2','2','Dokument 2',NULL,'Merknad dokument 2','objekt','utvalg_k2000v01_addml_7.3.XML','XML','XML-dokument')
	,('3','3','Dokument 3','Brannstyret','Merknad dokument 3','objekt','1.pdf','PDF','PDF-dokument')
	,('4','4','Dokument 4','Teknisk utval','Merknad dokument 4','objekt','2.pdf','PDF','PDF-dokument')
	,('5','5','Dokument 5','Møter i vassforyningsnemnda','Merknad dokument 5','objekt','5.pdf','PDF','PDF-dokument')
	,('6','214','Dokument ','Oppføring av braanngarasje på Fyrde Austefjorden','Merknad dokument 6','objekt','Noark 5v3.1 a5 Eks Arkivuttrekk xml.docx','DOCX','Word 2003-dokument')
	,('7','259','Dokument ','Branngarasje på Straumshamn','Merknad dokument 7','objekt','Noark-5v3.1_a2_Metadatakatalog _objektsortert.doc','DOC','Word-dokument')
	,('8','308','Dokument ','Vegar - vedlikehald diverse papir','Merknad dokument 8','objekt','Noark-5v3.1_a4_Eks_Virksomhetsspesifikke_metadata.docx','DOCX','Word-dokument')
	,('9','328','Dokument ','Nye vegar med kommunalt sommarvedlikehald','Merknad dokument 9','objekt','Samlet oversikt over krav i Noark5_egenerklæring_3_1 (rev 20.06.2013).xlsx','XLSX','Word-dokument')
;


text:
--
-- Cloud Foundry
-- Copyright (c) [2016] Pivotal Software, Inc. All Rights Reserved.
--
-- This product is licensed to you under the Apache License, Version 2.0 (the "License").
-- You may not use this product except in compliance with the License.
--
-- This product includes a number of subcomponents with
-- separate copyright notices and license terms. Your use of these
-- subcomponents is subject to the terms and conditions of the
-- subcomponent's license, as noted in the LICENSE file.
--

ALTER TABLE group_membership ALTER COLUMN authorities SET NULL;


text:
/*
   Copyright 2016  Georgia Institute of Technology

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/
CREATE OR REPLACE PROCEDURE omop.generic_create_procedures( v_loadid int )

AS

  rowcnt integer;

begin

-- procedures

-- create sequence omop.procedure_occurrence_id_seq;

etl.logm('generic_create_procedures', 'create procedure', 'START' );

INSERT INTO omop.procedure_occurrence_temp
(
	procedure_occurrence_id,
	person_id,
	procedure_concept_id,
	procedure_date,
	procedure_type_concept_id,
	modifier_concept_id,
	quantity,
	provider_id,
	visit_occurrence_id,
	procedure_source_value,
	procedure_source_concept_id,
  qualifier_source_value,
  x_srcid,
  x_srcloadid,
  x_srcfile
)
select
  omop.procedure_occurrence_id_seq.nextval as procedure_occurrence_id,
	person_id,
	procedure_concept_id,
	procedure_date,
	procedure_type_concept_id,
	modifier_concept_id,
	quantity,
	provider_id,
	visit_occurrence_id,
	procedure_source_value,
	procedure_source_concept_id,
  qualifier_source_value,
  x_srcid,
  x_srcloadid,
  x_srcfile
from
(
    select
    	p.person_id as person_id,
    	coalesce( tar.concept_id, src.concept_id, 0 )  as procedure_concept_id,
    	coalesce( s.procedure_date, v.visit_start_date )  as procedure_date,
    	s.procedure_source_type_value as procedure_type_concept_id,
		  coalesce(mod.concept_id,0) as modifier_concept_id,
      s.quantity  as quantity,
    	v.provider_id as provider_id,
    	v.visit_occurrence_id as visit_occurrence_id,
    	s.procedure_source_value as procedure_source_value,
    	coalesce( src.concept_id, 0 ) as procedure_source_concept_id,
      s.code_modifier as qualifier_source_value
      , s.id as x_srcid
  		, s.load_id as x_srcloadid
      , 'STAGE_PROCEDURE' as x_srcfile
    from etl.stage_procedure_temp s
    join omop.person p on p.person_source_value = s.person_source_value
    left join omop.visit_occurrence v on s.visit_source_value = v.visit_source_value
    left join omop.concept src on s.procedure_source_value = replace(src.concept_code, '.', '' )
        and src.domain_id like '%Proc%'
        and coalesce(s.procedure_code_source_type, src.vocabulary_id ) = src.vocabulary_id
        and src.invalid_reason is null
        and src.vocabulary_id in ( 'CPT4', 'HCPCS', 'ICD9Proc', 'ICD10PCS')
    left join omop.concept_relationship cr on src.concept_id = cr.concept_id_1
        and cr.relationship_id = 'Maps to'
        and cr.invalid_reason is null
    left join omop.concept tar on cr.concept_id_2 = tar.concept_id
        and tar.standard_concept = 'S'
        and tar.invalid_reason is null
    left join omop.concept mod on s.code_modifier = mod.concept_code
        and mod.concept_class_id like '%Modifier%'
        and mod.vocabulary_id = src.vocabulary_id
    left join omop.provider pr on s.provider_source_value = pr.provider_source_value
    where s.procedure_date is not null
and s.load_id = coalesce(v_loadid, s.load_id )

) a
;

  rowcnt := SQL%ROWCOUNT;
  etl.logm('generic_create_procedures', 'insert into procedure', rowcnt );
  etl.logm('generic_create_procedures', 'create procedure', 'FINISH' );

  etl.logm('generic_create_procedures', 'generic_create_proc_observations from procedures' , 'START' );

INSERT INTO omop.observation_temp
(
             observation_id
            , person_id
            , observation_concept_id
            , observation_date
            , observation_time
            , observation_type_concept_id
            , qualifier_concept_id
            , value_as_concept_id
            , visit_occurrence_id
            , observation_source_value
            , observation_source_concept_id
            , provider_id
            , qualifier_source_value
            , x_srcid
            , x_srcloadid
            , x_srcfile
)
        select
            omop.observation_id_seq.nextval as observation_id
            , person_id
            , observation_concept_id
            , observation_date
            , observation_time
            , observation_type_concept_id
            , qualifier_concept_id
            , value_as_concept_id
            , visit_occurrence_id
            , observation_source_value
            , observation_source_concept_id
            , provider_id
            , qualifier_source_value
            , x_srcid
            , x_srcloadid
            , x_srcfile
        from
        (
        select
            p.person_id as person_id
            , coalesce(tar.concept_id, 0 )  as observation_concept_id
            , coalesce(s.procedure_date, v.visit_start_date) as observation_date
            , extract( hour from s.procedure_date ) || ':' ||
                extract( minute from s.procedure_date ) || ':' ||
                extract( second from s.procedure_date ),
                'HH24:MI:SS' as observation_time
            , 38000280 as observation_type_concept_id  -- 'Observation recorded from EHR'  -- TODO: may need to be changed
            , coalesce(mod.concept_id,0) as qualifier_concept_id
            , val.concept_id as value_as_concept_id
            , v.visit_occurrence_id as visit_occurrence_id
            , s.procedure_source_value as observation_source_value
            , coalesce(src.concept_id, 0) as observation_source_concept_id
            , pr.provider_id
            , s.code_modifier as qualifier_source_value
            , s.id as x_srcid
            , s.load_id as x_srcloadid
            , 'STAGE_PROCEDURE' as x_srcfile
        from etl.stage_procedure_temp s
        join omop.person p on p.person_source_value = s.person_source_value
        left join omop.visit_occurrence v on s.visit_source_value = v.visit_source_value
        join omop.concept src on s.procedure_source_value = replace(src.concept_code, '.', '' )
			                  and src.domain_id like '%Obs%'
                        and coalesce(s.procedure_code_source_type, src.vocabulary_id ) = src.vocabulary_id
			                  and src.invalid_reason is null
			                  and src.vocabulary_id in ( 'CPT4', 'HCPCS', 'ICD9Proc', 'ICD10PCS')
        left join omop.concept_relationship cr on src.concept_id = cr.concept_id_1
                  			and cr.relationship_id = 'Maps to'
                  			and cr.invalid_reason is null
        left join omop.concept tar on cr.concept_id_2 = tar.concept_id
                  			and tar.standard_concept = 'S'
                  			and tar.invalid_reason is null
        left join omop.concept_relationship crv on src.concept_id = crv.concept_id_1
                  			and crv.relationship_id = 'Maps to value'
                  			and crv.invalid_reason is null
        left join omop.concept val on crv.concept_id_2 = val.concept_id
                  			and val.standard_concept = 'S'
                  			and val.invalid_reason is null
        left join omop.concept mod on s.code_modifier = mod.concept_code
                        and mod.concept_class_id like '%Modifier%'
                        and mod.vocabulary_id = src.vocabulary_id
        left join omop.provider pr on s.provider_source_value = pr.provider_source_value
        where s.procedure_date is not null
and s.load_id = coalesce(v_loadid, s.load_id )

     ) a  ;

        rowcnt := SQL%ROWCOUNT;

        etl.logm('generic_create_procedures', 'insert into observation' , rowcnt );

        etl.logm('generic_create_procedures', 'generic_create_proc_observations from procedures' , 'FINISH' );

        etl.logm('generic_create_procedures', 'generic_create_proc_measurements from procedure' , 'START' );

        insert into omop.measurement_temp
        (
            measurement_id,
            person_id,
            measurement_concept_id,
            measurement_date,
            measurement_type_concept_id,
            value_as_concept_id,
            visit_occurrence_id,
            measurement_source_value,
            measurement_source_concept_id,
            provider_id,
            x_srcid,
            x_srcloadid,
            x_srcfile
        )
        select
            omop.measurement_id_seq.nextval as measurement_id
            , person_id
            , measurement_concept_id
            , measurement_date
            , measurement_type_concept_id
            , value_as_concept_id
            , visit_occurrence_id
            , measurement_source_value
            , measurement_source_concept_id
            , provider_id
            , x_srcid
            , x_srcloadid
            , x_srcfile
        from
        (
            select distinct
                p.person_id as person_id
                , coalesce(tar.concept_id, 0 )  as measurement_concept_id
                , coalesce( s.procedure_date, v.visit_start_date ) as measurement_date
                , 44818701 as measurement_type_concept_id  -- 'From physical examination' -- TODO: may need to be changed
                , val.concept_id as value_as_concept_id
                , v.visit_occurrence_id as visit_occurrence_id
                , s.procedure_source_value as measurement_source_value
                , coalesce(tar.concept_id, 0) as measurement_source_concept_id
                , pr.provider_id
                , s.id as x_srcid
                , s.load_id as x_srcloadid
                , 'STAGE_PROCEDURE' as x_srcfile
            from etl.stage_procedure_temp s
            join omop.person p on p.person_source_value = s.person_source_value
            left join omop.visit_occurrence v on s.visit_source_value = v.visit_source_value
            join omop.concept src on s.procedure_source_value = replace(src.concept_code, '.', '' )
                            and src.domain_id like '%Meas%'
                            and coalesce(s.procedure_code_source_type, src.vocabulary_id ) = src.vocabulary_id
                            and src.invalid_reason is null
                            and src.vocabulary_id in ( 'CPT4', 'HCPCS', 'ICD9Proc', 'ICD10PCS')
            left join omop.concept_relationship cr on src.concept_id = cr.concept_id_1
                      			and cr.relationship_id = 'Maps to'
                      			and cr.invalid_reason is null
            left join omop.concept tar on cr.concept_id_2 = tar.concept_id
                      			and tar.standard_concept = 'S'
                      			and tar.invalid_reason is null
            left join omop.concept_relationship crv on src.concept_id = crv.concept_id_1
                      			and crv.relationship_id = 'Maps to value'
                      			and crv.invalid_reason is null
            left join omop.concept val on crv.concept_id_2 = val.concept_id
                      			and val.standard_concept = 'S'
                      			and val.invalid_reason is null
            left join omop.provider pr on s.provider_source_value = pr.provider_source_value
             where s.procedure_date is not null
and s.load_id = coalesce(v_loadid, s.load_id )

        ) a
        ;

        rowcnt := SQL%ROWCOUNT;

        etl.logm('generic_create_procedures', 'insert into measurements' , rowcnt );

        etl.logm('generic_create_procedures', 'generic_create_proc_measurements from procedure' , 'FINISH' );

      etl.logm('generic_create_procedures', 'generic_create_proc_drug_exposure from procedure' , 'START' );

      INSERT INTO omop.drug_exposure_temp
      (
        drug_exposure_id,
        person_id,
        drug_concept_id,
        drug_exposure_start_date,
        drug_type_concept_id,
        quantity,
        provider_id,
        visit_occurrence_id,
        drug_source_value,
        drug_source_concept_id,
        x_srcid,
        x_srcloadid,
        x_srcfile
      )
      select
        omop.drug_exposure_id_seq.nextval as 	drug_exposure_id,
        person_id,
        drug_concept_id,
        drug_exposure_start_date,
        drug_type_concept_id,
        quantity,
        provider_id,
        visit_occurrence_id,
        drug_source_value,
        drug_source_concept_id,
        x_srcid,
        x_srcloadid,
        x_srcfile
      from
      (
          select
          	p.person_id as person_id,
          	coalesce(tar.concept_id, 0 ) as drug_concept_id,
          	coalesce( s.procedure_date, v.visit_start_date )   as drug_exposure_start_date,
          	38000179 as drug_type_concept_id,   -- physician administered as procedure code
          	s.quantity as quantity,
          	coalesce( pr.provider_id, 0 )  as provider_id,
          	v.visit_occurrence_id as visit_occurrence_id,
          	s.procedure_source_value as drug_source_value,
          	coalesce( src.concept_id, 0) as drug_source_concept_id,
            s.id as x_srcid,
            s.load_id as x_srcloadid,
            'STAGE_PROCEDURE' as x_srcfile
          from etl.stage_procedure_temp s
          join omop.person p on p.person_source_value = s.person_source_value
          left join omop.visit_occurrence v on s.visit_source_value = v.visit_source_value
           join omop.concept src on s.procedure_source_value = replace(src.concept_code, '.', '' )
              and src.domain_id like '%Drug%'
              and coalesce(s.procedure_code_source_type, src.vocabulary_id ) = src.vocabulary_id
              and src.invalid_reason is null
              and src.vocabulary_id in ( 'CPT4', 'HCPCS', 'ICD9Proc', 'ICD10PCS')
          left join omop.concept_relationship cr on src.concept_id = cr.concept_id_1
              and cr.relationship_id = 'Maps to'
              and cr.invalid_reason is null
          left join omop.concept tar on cr.concept_id_2 = tar.concept_id
              and tar.standard_concept = 'S'
              and tar.invalid_reason is null
          left join omop.provider pr on s.provider_source_value = pr.provider_source_value
--          where s.procedure_date is not null
where s.load_id = coalesce(v_loadid, s.load_id )

      ) a
      ;

        rowcnt := SQL%ROWCOUNT;

        etl.logm('generic_create_procedures', 'insert into drug_exposure' , rowcnt );

        etl.logm('generic_create_procedures', 'generic_create_proc_drug_exposure from procedure' , 'FINISH' );

      etl.logm('generic_create_procedures', 'generic_create_proc_device from procedure' , 'START' );

      INSERT INTO omop.device_exposure_temp
      (
          device_exposure_id,
          person_id,
          device_concept_id,
          device_exposure_start_date,
          device_type_concept_id,
          quantity,
          provider_id,
          visit_occurrence_id,
          device_source_value,
          device_source_concept_id,
          x_srcid,
          x_srcloadid,
          x_srcfile
      )
      select
          omop.device_exposure_id_seq.nextval as device_exposure_id,
          person_id,
          device_concept_id,
          device_exposure_start_date,
          device_type_concept_id,
          quantity,
          provider_id,
          visit_occurrence_id,
          device_source_value,
          device_source_concept_id,
          x_srcid,
          x_srcloadid,
          x_srcfile
      from
      (
          select
          	p.person_id as person_id,
          	coalesce(tar.concept_id, 0 ) as device_concept_id,
          	coalesce( s.procedure_date, v.visit_start_date )   as device_exposure_start_date,
          	44818705 as device_type_concept_id,   -- inferred from procedure code
          	s.quantity as quantity,
          	coalesce( pr.provider_id, 0 )  as provider_id,
          	v.visit_occurrence_id as visit_occurrence_id,
          	s.procedure_source_value as device_source_value,
          	coalesce( src.concept_id, 0) as device_source_concept_id,
             s.id as x_srcid,
             s.load_id as x_srcloadid,
            'STAGE_PROCEDURE' as x_srcfile
          from etl.stage_procedure_temp s
          join omop.person p on p.person_source_value = s.person_source_value
          left join omop.visit_occurrence v on s.visit_source_value = v.visit_source_value
           join omop.concept src on s.procedure_source_value = replace(src.concept_code, '.', '' )
              and src.domain_id like '%Dev%'
              and coalesce(s.procedure_code_source_type, src.vocabulary_id ) = src.vocabulary_id
              and src.invalid_reason is null
              and src.vocabulary_id in ( 'CPT4', 'HCPCS', 'ICD9Proc', 'ICD10PCS')
          left join omop.concept_relationship cr on src.concept_id = cr.concept_id_1
              and cr.relationship_id = 'Maps to'
              and cr.invalid_reason is null
          left join omop.concept tar on cr.concept_id_2 = tar.concept_id
              and tar.standard_concept = 'S'
              and tar.invalid_reason is null
          left join omop.provider pr on s.provider_source_value = pr.provider_source_value
  --        where s.procedure_date is not null
where s.load_id = coalesce(v_loadid, s.load_id )

      ) a
      ;

        rowcnt := SQL%ROWCOUNT;

        etl.logm('generic_create_procedures', 'insert into device_exposure' , rowcnt );

        etl.logm('generic_create_procedures', 'generic_create_proc_device_exposure from procedure' , 'FINISH' );

      etl.logm('generic_create_procedures', 'create procedure', 'STOP' );

    end;


text:
-- MySQL dump 10.13  Distrib 5.6.23, for Linux (x86_64)
--
-- Host: localhost    Database: dance
-- ------------------------------------------------------
-- Server version	5.6.23-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=''+00:00'' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=''NO_AUTO_VALUE_ON_ZERO'' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `wz_class`
--

DROP TABLE IF EXISTS `wz_class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT ''课程名称'',
  `hard` int(11) NOT NULL COMMENT ''课程难度'',
  `age` int(11) NOT NULL COMMENT ''适用年龄段'',
  `cls` varchar(25) NOT NULL COMMENT ''分类'',
  `des` text NOT NULL COMMENT ''课程介绍'',
  `showCls` text NOT NULL COMMENT ''教学环境展示'',
  `clsTime` int(11) NOT NULL COMMENT ''课时'',
  `clsAim` text NOT NULL COMMENT ''预期效果'',
  `clsNotice` text NOT NULL COMMENT ''课程须知'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''最后修改时间'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=''课程表'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_feedback`
--

DROP TABLE IF EXISTS `wz_feedback`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) NOT NULL COMMENT ''学生id'',
  `message` text NOT NULL COMMENT ''留言内容'',
  `enable` tinyint(1) NOT NULL DEFAULT ''1'' COMMENT ''1有效 0已删除'',
  `reply_type` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''回复类型 1短信 2在线回复'',
  `reply` text COMMENT ''回复内容'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''最后修改时间'',
  `uid` int(11) DEFAULT NULL COMMENT ''管理员id'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''留言'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_good_student`
--

DROP TABLE IF EXISTS `wz_good_student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_good_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT ''学员名称'',
  `gender` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''性别 1 男 2 女'',
  `cls` varchar(25) NOT NULL COMMENT ''分类'',
  `age` int(11) NOT NULL COMMENT ''年龄'',
  `banner` varchar(255) NOT NULL COMMENT ''banner图片地址'',
  `des` text NOT NULL COMMENT ''介绍'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''最后修改时间'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''优秀学员管理'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_info`
--

DROP TABLE IF EXISTS `wz_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL COMMENT ''资讯标题'',
  `cls` varchar(20) NOT NULL COMMENT ''资讯分类'',
  `from` varchar(50) DEFAULT NULL COMMENT ''来源'',
  `banner` varchar(255) NOT NULL COMMENT ''banner图片地址'',
  `content` text NOT NULL COMMENT ''正文'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''最后修改时间'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''资讯管理'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_photo`
--

DROP TABLE IF EXISTS `wz_photo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_photo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL COMMENT ''剧照图片地址'',
  `cls` varchar(20) DEFAULT NULL COMMENT ''资讯分类'',
  `banner` varchar(255) NOT NULL COMMENT ''banner图片地址'',
  `des` text NOT NULL COMMENT ''剧照描述'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''最后修改时间'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''剧照'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_relationship`
--

DROP TABLE IF EXISTS `wz_relationship`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_relationship` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` char(70) NOT NULL COMMENT ''课程id'',
  `teacher_id` char(70) NOT NULL COMMENT ''教师ID'',
  `enable` tinyint(1) NOT NULL DEFAULT ''1'' COMMENT ''状态'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=''课程教师关系表'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_resource`
--

DROP TABLE IF EXISTS `wz_resource`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(70) NOT NULL COMMENT ''资源名称'',
  `enable` tinyint(1) NOT NULL DEFAULT ''1'' COMMENT ''1有效 0无效'',
  `type` tinyint(1) NOT NULL DEFAULT ''1'' COMMENT ''资源类型 1图片 2视频'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `uid` int(11) NOT NULL COMMENT ''创建者ID'',
  `location` text COMMENT ''文件位置'',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rolename` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=''资源信息表'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_role`
--

DROP TABLE IF EXISTS `wz_role`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(70) NOT NULL COMMENT ''角色名称'',
  `enable` tinyint(1) NOT NULL DEFAULT ''1'' COMMENT ''1有效 0无效'',
  `created_time` datetime DEFAULT NULL COMMENT ''注册时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''修改时间'',
  `config` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rolename` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=''角色信息表'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_teacher`
--

DROP TABLE IF EXISTS `wz_teacher`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT ''教师名称'',
  `gender` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''性别 1 男 2 女'',
  `cls` varchar(25) NOT NULL COMMENT ''分类'',
  `age` int(11) NOT NULL COMMENT ''年龄'',
  `des` text NOT NULL COMMENT ''介绍'',
  `phone` varchar(25) NOT NULL COMMENT ''手机号码'',
  `created_time` datetime DEFAULT NULL COMMENT ''创建时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''最后修改时间'',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=''教师信息表'';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `wz_user`
--

DROP TABLE IF EXISTS `wz_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `wz_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` char(70) NOT NULL COMMENT ''用户名'',
  `password` char(70) NOT NULL COMMENT ''密码'',
  `superman` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''1超级管理员 0 普通管理员'',
  `created_time` datetime DEFAULT NULL COMMENT ''注册时间'',
  `updated_time` datetime DEFAULT NULL COMMENT ''修改时间'',
  `status` tinyint(4) NOT NULL DEFAULT ''1'' COMMENT ''封禁状态，0禁止 1正常'',
  `login_ip` char(20) DEFAULT NULL COMMENT ''登录ip'',
  `login_time` int(11) DEFAULT NULL COMMENT ''上一次登录时间'',
  `login_count` int(10) DEFAULT ''0'' COMMENT ''登陆次数'',
  `update_password` int(10) DEFAULT ''0'' COMMENT ''修改密码次数'',
  `config` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  KEY `username` (`username`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT=''登录管理表'';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-07-26 23:59:36

CREATE TABLE `wz_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nickname` char(70) NOT NULL COMMENT '用户名',
  `email` char(70) NOT NULL COMMENT '邮箱',
  `password` char(70) NOT NULL COMMENT '密码',
  `phone` varchar(20) NULL  COMMENT '手机号',
  `avatar` varchar(255) NULL  COMMENT '头像',
  `wx_open_id` varchar(255) NULL  COMMENT '微信id',
  `created_time` datetime DEFAULT NULL COMMENT '注册时间',
  `updated_time` datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='学员信息表'   ;


text:
select date_add(max(end_time),INTERVAL 1 DAY)  into @start_time from compute_usage;
select date_add(max(end_time),INTERVAL 6 MONTH)  into @end_time from compute_usage;

insert into compute_usage (type, project, code, owner, end_time, wallclock, wallclock_hours)
-- hadoop jobs * slots * 16
select 'spark' type
      ,project
      ,code
      ,case account when 'sge' then owner else account end as owner
      ,date_format(end_time,'%Y-%m-%d') as end_time
      ,sum(ru_wallclock*slots*16) as wallclock
      ,round(sum(ru_wallclock*slots*16)/3600,2) as wallclock_hours
from compute_accounting_fy15,department
where name = group_name
  and end_time >= concat(@start_time, ' 00:00:00')
  and end_time <= concat(@end_time, ' 23:59:59')
  and qname like 'hadoop%'
group by project, code, owner, account, date_format(end_time,'%Y-%m-%d')
union
-- non-impi jobs * slots
select 'non-impi'
      ,project
      ,code
      ,case account when 'sge' then owner else account end as owner
      ,date_format(end_time,'%Y-%m-%d') as end_time
      ,sum(ru_wallclock*slots) as wallclock
      ,round(sum(ru_wallclock*slots)/3600,2) as wallclock_hours
from compute_accounting_fy15,department
where name = group_name
  and end_time >= concat(@start_time, ' 00:00:00')
  and end_time <= concat(@end_time, ' 23:59:59')
  and granted_pe not like 'impi%'
  and (category not like '%exclusive=true%' or ( category like '%exclusive=true%' and slots !=1 ))
group by project, code, owner, account, date_format(end_time,'%Y-%m-%d')
union
-- impi jobs (distinct job * slots)
select 'impi'
      ,project
      ,code
      ,case account when 'sge' then owner else account end as owner
      ,date_format(end_time,'%Y-%m-%d') as end_time
      ,sum(ru_wallclock*slots) as wallclock
      , round(sum(ru_wallclock*slots)/3600,2) as wallclock_hours
from (
      select  project, code, owner, date_format(end_time,'%Y-%m-%d') end_time ,max(ru_wallclock) ru_wallclock, max(slots) slots,jobnumber,account
      from compute_accounting_fy15,department
      where name = group_name
        and end_time >= concat(@start_time, ' 00:00:00')
        and end_time <= concat(@end_time, ' 23:59:59')
        and granted_pe like 'impi%'
      group by jobnumber) x
group by project, code, owner, account, date_format(end_time,'%Y-%m-%d');


text:
create  table "postgres".test_normalization."conflict_stream_array_conflict_stream_array__dbt_tmp"
  as (

-- Final base SQL model
select
    _airbyte_conflict_stream_array_hashid,
    conflict_stream_name,
    _airbyte_emitted_at,
    _airbyte_conflict_stream_array_2_hashid
from "postgres"._airbyte_test_normalization."conflict_stream_array_conflict_stream_array_ab3"
-- conflict_stream_array at conflict_stream_array/conflict_stream_array from "postgres".test_normalization."conflict_stream_array"
  );


text:
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `files`;
CREATE TABLE `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `organizations`;
CREATE TABLE `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `license` varchar(15) NOT NULL DEFAULT 'basic',
  `validity` datetime NOT NULL,
  `org_secret` varchar(50) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '0',
  `secret` varchar(50) NOT NULL DEFAULT '206b2dbe-ecc9-490b-b81b-83767288bc5e',
  PRIMARY KEY (`id`),
  UNIQUE KEY `org_secret` (`org_secret`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `organizations` (`id`, `name`, `email`, `license`, `validity`, `is_active`, `org_secret`, `secret`) VALUES
(1,	'Default Organization',	'superadmin@example.com', 'super',	'0000-00-00 00:00:00', 1, '206b2dbe-ecc9-490b-b81b-83767288bc5e',	'206b2dbe-ecc9-490b-b81b-83767288bc5e');

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `token` varchar(50) NOT NULL DEFAULT '00000000-00000-0000-0000-000000000000',
  `lease` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `role` varchar(50) DEFAULT 'user',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `secret` varchar(50) NOT NULL DEFAULT '206b2dbe-ecc9-490b-b81b-83767288bc5e',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `email`, `username`, `password`, `token`, `lease`, `role`, `is_active`, `secret`) VALUES
(1,	'superadmin@example.com',	'superadmin',	'17c4520f6cfd1ab53d8745e84681eb49',	'1',	'0000-00-00 00:00:00',	'superadmin', 1, '206b2dbe-ecc9-490b-b81b-83767288bc5e');

INSERT INTO `users` (`id`, `email`, `username`, `password`, `token`, `lease`, `role`, `is_active`, `secret`) VALUES
(2,	'admin@example.com',	'admin',	'21232f297a57a5a743894a0e4a801fc3',	'1',	'0000-00-00 00:00:00',	'admin', 1, '206b2dbe-ecc9-490b-b81b-83767288bc5e');

INSERT INTO `users` (`id`, `email`, `username`, `password`, `token`, `lease`, `role`, `is_active`, `secret`) VALUES
(3,	'user@example.com',	'user',	'ee11cbb19052e40b07aac0ca060c23ee',	'1',	'0000-00-00 00:00:00',	'user', 1, '206b2dbe-ecc9-490b-b81b-83767288bc5e');

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `secret` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `user_groups`;
CREATE TABLE `user_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `secret` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `group_id` (`group_id`),
  UNIQUE KEY `user_id_group_id_secret` (`user_id`,`group_id`,`secret`),
  CONSTRAINT `user_groups_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `user_groups_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `secret` varchar(50) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `tasks`;
CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `secret` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


text:
CREATE DATABASE Empresa;

CREATE TABLE Marca (
    MarcaId INT IDENTITY(1,1) PRIMARY KEY,
    Nome CHAR(80) NOT NULL UNIQUE
);

CREATE TABLE Patrimonio (
    PatrimonioId INT IDENTITY(1,1) PRIMARY KEY,
    MarcaId INT NOT NULL,
    Nome CHAR(80) NOT NULL,
    Descricao TEXT,
    NumeroTombo INT
);

ALTER TABLE Patrimonio ADD CONSTRAINT fk_patrimonio_marca
    FOREIGN KEY(MarcaId) REFERENCES Marca (MarcaId)
    ON DELETE CASCADE;

INSERT INTO Marca (Nome)
VALUES
	('Marca1'),
	('Marca2');

INSERT INTO Patrimonio (MarcaId, Nome, Descricao, NumeroTombo)
VALUES
	(1, 'PatrimonioI', 'Descricao PatrimonioI', '1001'),
	(1, 'PatrimonioII', 'Descricao PatrimonioII', '1002'),
	(2, 'PatrimonioIII', 'Descricao PatrimonioIII', '1003'),
	(2, 'PatrimonioIV', 'Descricao PatrimonioIV', '1004');