Created
December 9, 2016 17:24
-
-
Save gbrault/0356d54be8eb3db79a3ec466d72cdbba to your computer and use it in GitHub Desktop.
sqlite with search
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [{"id":"226b6388.c3db4c","type":"function","z":"e81c960d.042c08","name":"sql exec","func":"var statements = msg.payload.split(\";\");\nflow.set('responses',[]);\nflow.set('statements',statements);\nflow.set('count',0);\nflow.set('exec', function(){\n context.global.knex.raw(flow.get('statements')[flow.get('count')]).then(\n\t function(resp){\n\t var responses = flow.get('responses');\n\t responses.push(resp);\n\t flow.set('count',flow.get('count')+1);\n\t if(flow.get('count')>=flow.get('statements').length){\n\t msg.payload = JSON.stringify(responses);\n\t msg.headers = {\n 'Content-type' : 'application/json'\n };\n\t node.send(msg);\n\t } else {\n\t flow.get('exec')();\n\t }\n\t }\n );\n} );\nflow.get('exec')();\nreturn null;","outputs":1,"noerr":0,"x":469,"y":159,"wires":[["ce9475a6.52c3d8","c6d5f352.65145"]]},{"id":"3faa0dfa.2ea942","type":"inject","z":"e81c960d.042c08","name":"supplier","topic":"test","payload":"select rowid, * from supplier order by rowid limit 5 offset 0","payloadType":"str","repeat":"","crontab":"","once":false,"x":207,"y":159,"wires":[["226b6388.c3db4c"]]},{"id":"ce9475a6.52c3d8","type":"debug","z":"e81c960d.042c08","name":"","active":false,"console":"false","complete":"false","x":900,"y":158,"wires":[]},{"id":"2ddc2fc4.66e7c","type":"http in","z":"e81c960d.042c08","name":"sql webservice","url":"/sql","method":"get","swaggerDoc":"","x":162,"y":87,"wires":[["af57b8ac.28b538"]]},{"id":"af57b8ac.28b538","type":"function","z":"e81c960d.042c08","name":"prepare sql","func":"if(msg.payload.sql!==undefined){\n msg.topic=\"http\";\n msg.payload=msg.payload.sql;\n return msg;\n}\nreturn null;","outputs":1,"noerr":0,"x":348,"y":87,"wires":[["226b6388.c3db4c","60bb5e5.a4da4a"]]},{"id":"ab52403d.51d42","type":"http response","z":"e81c960d.042c08","name":"","x":883,"y":85,"wires":[]},{"id":"60bb5e5.a4da4a","type":"debug","z":"e81c960d.042c08","name":"","active":false,"console":"false","complete":"payload","x":530,"y":86,"wires":[]},{"id":"c6d5f352.65145","type":"function","z":"e81c960d.042c08","name":"http?","func":"if(msg.topic===\"http\"){\n return [msg,null];\n}\nelse{\n msg.search = flow.get('sql').search || \"\";\n return [null,msg];\n}","outputs":"2","noerr":0,"x":739,"y":85,"wires":[["ab52403d.51d42"],["ebe312d9.60d79"]]},{"id":"ebe312d9.60d79","type":"ui_template","z":"e81c960d.042c08","group":"1b831892.e67a07","name":"Table View","order":4,"width":"0","height":"0","format":"<table>\n<tr>\n <th ng-repeat=\"(key,value) in table[0]\">{{key}}</th>\n</tr>\n<tbody ng-repeat=\"row in table\">\n<tr ng-if=\"$even\">\n <td ng-repeat=\"(key,value) in row\" ng-bind-html=\"decorate(value)\"></td>\n</tr>\n<tr ng-if=\"$odd\">\n <td style=\"background-color:#f1f1f1\" ng-repeat=\"(key,value) in row\" ng-bind-html=\"decorate(value)\"></td>\n</tr>\n</tbody> \n</table>\n<style>\ntable, td {\n border: 1px solid grey;\n border-collapse: collapse;\n padding: 5px;\n}\n.highlighted { background: yellow }\n</style>\n<script>\n(function(scope) {\n // debugger;\n scope.table=[];\n scope.search=\"\";\n scope.$watch('msg', function (newValue, oldValue, scope) {\n scope.table=JSON.parse(scope.msg.payload)[0];\n scope.search = scope.msg.search;\n });\n scope.decorate = function(value){\n // debugger;\n var tmp=\"\";\n if ((scope.search) && (($.type(value) === \"string\")))\n tmp = value.replace( new RegExp('('+scope.search+')', 'gi'),\n '<span class=\"highlighted\">$1</span>');\n else\n tmp = value;\n if (tmp.length==0) return value; else return tmp;\n };\n})(scope); \n</script>","storeOutMessages":true,"fwdInMessages":true,"x":755,"y":207,"wires":[[]]},{"id":"379b67fa.22f238","type":"inject","z":"e81c960d.042c08","name":"customer","topic":"test","payload":"select rowid, * from customer order by rowid limit 5 offset 0","payloadType":"str","repeat":"","crontab":"","once":false,"x":215,"y":229,"wires":[["226b6388.c3db4c"]]},{"id":"c32ae8f2.ab9748","type":"ui_template","z":"e81c960d.042c08","group":"1b831892.e67a07","name":"paging controller","order":5,"width":0,"height":0,"format":"<div paging page=\"page\" page-size=\"page_size\" total=\"total\" paging-action=\"paging(page, pageSize, total)\">\n</div>\n<script>\n(function(scope) {\n scope.total=0;\n scope.page_size=0;\n scope.page=0;\n scope.$watch('msg', function (newValue, oldValue, scope) {\n // debugger;\n if(scope.msg!==undefined){\n scope.total=scope.msg.total;\n scope.page_size=scope.msg.page_size;\n scope.page=scope.msg.page;\n }\n \n });\n scope.paging=function(page,pageSize,total){\n // debugger;\n scope.msg.page=page;\n scope.msg.page_size=pageSize;\n scope.send(scope.msg);\n };\n})(scope); \n</script>","storeOutMessages":true,"fwdInMessages":true,"x":735,"y":399,"wires":[["bfa92b69.0fb078","81998703.eea268"]]},{"id":"ac46feba.89eca","type":"function","z":"e81c960d.042c08","name":"total, page_size","func":"context.global.knex.raw(flow.get('sql').qtotal).then(\n\t function(resp){\n\t // console.log(resp[0].count);\n\t msg.total=resp[0].count;\n\t flow.get('sql').total=resp[0].count;\n\t msg.page_size=flow.get('sql').page_size;\n\t msg.page=flow.get('sql').page;\n\t node.send(msg);\n\t });\nreturn null;","outputs":1,"noerr":0,"x":514,"y":399,"wires":[["c32ae8f2.ab9748","e3cf3c63.fd3cf"]]},{"id":"81998703.eea268","type":"function","z":"e81c960d.042c08","name":"sql page request","func":"flow.get('sql').page=msg.page;\nflow.get('sql').page_size=msg.page_size;\nmsg.payload=flow.get('sql').qpage;\nreturn msg;","outputs":1,"noerr":0,"x":474,"y":271,"wires":[["226b6388.c3db4c","b680954.c1ef968"]]},{"id":"aa3e785.4f40988","type":"inject","z":"e81c960d.042c08","name":"","topic":"","payload":"customer","payloadType":"str","repeat":"","crontab":"","once":false,"x":311,"y":398,"wires":[["ac46feba.89eca"]]},{"id":"bfa92b69.0fb078","type":"debug","z":"e81c960d.042c08","name":"","active":false,"console":"false","complete":"true","x":947,"y":398,"wires":[]},{"id":"2185d632.97e08a","type":"ui_dropdown","z":"e81c960d.042c08","name":"Table choice","label":"","group":"1b831892.e67a07","order":2,"width":"5","height":"1","passthru":true,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"","x":334,"y":517,"wires":[["ab5a26ee.990b28"]]},{"id":"7e015f6b.5ba1d","type":"inject","z":"e81c960d.042c08","name":"once","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":103,"y":511,"wires":[["c541ae3e.28cb9"]]},{"id":"138b457a.a7a34b","type":"debug","z":"e81c960d.042c08","name":"","active":false,"console":"false","complete":"true","x":582,"y":581,"wires":[]},{"id":"c541ae3e.28cb9","type":"function","z":"e81c960d.042c08","name":"sql functions + List of tables","func":"flow.set('sql',{\n get table() { return flow.get('table');},\n set table(t) { flow.set('table',\"'\"+t+\"'\");},\n get qtotal(){ return 'select Count(*) as count from '+flow.get('table')+ flow.get('sql').whereorall;},\n get total() { return flow.get('total');},\n set total(t) {flow.set('total',t);},\n get page_size() {return flow.get('page_size');},\n set page_size(s) {flow.set('page_size',s);}, \n get page() { return flow.get('page');},\n set page(p) {flow.set('page',p);},\n get qpage(){ return \"select rowid, * from \"+\n flow.get('table')+flow.get('sql').whereorall+\" order by rowid limit \"+\n flow.get('page_size')+\" offset \"+flow.get('page_size')*(flow.get('page')-1);},\n get search() {return flow.get('search');},\n set search(s) {flow.set('search',s);},\n set columns(c) {flow.set('columns',c);},\n get columns() { return flow.get('columns');},\n get whereorall() {\n var where = \"\";\n var search = flow.get('search');\n if((search!==undefined)&&(search!==null)&&(search.length>0)){\n var columns = flow.get('columns');\n if((columns!==undefined)&&(columns!==null)&&(columns.length>0)){\n where = \" where \";\n for(var i=0; i< columns.length; i++){\n where = where + columns[i].name+ \" like '%\"+flow.get('search')+\"%'\"; \n if (i<columns.length-1){\n where = where + \" or \";\n }\n }\n }\n }\n return where;\n }\n});\n/* set the page_size */\nflow.get('sql').page_size=5;\nflow.get('sql').page=1;\n/* get a list of table backend dependent */\ncontext.global.knex.raw('select * from sqlite_master').then(\n\t function(resp){\n\t msg.options=[];\n\t for(var i=0; i<resp.length;i++){\n\t if(resp[i].type==='table')\n\t msg.options.push(resp[i].name);\n\t }\n\t msg.options=msg.options.sort();\n\t msg.payload=msg.options[0];\n\t flow.get('sql').table=msg.options[0];\n\t node.send(msg);\n\t }\n);\nreturn null;","outputs":1,"noerr":0,"x":188,"y":584,"wires":[["2185d632.97e08a","138b457a.a7a34b"]]},{"id":"91710930.712198","type":"ui_text_input","z":"e81c960d.042c08","name":"","label":"search","group":"1b831892.e67a07","order":3,"width":"6","height":"1","passthru":true,"mode":"text","delay":300,"topic":"","x":142,"y":328,"wires":[["9ec88988.209e98"]]},{"id":"ab5a26ee.990b28","type":"function","z":"e81c960d.042c08","name":"get columns","func":"flow.get('sql').table=msg.payload;\n/* get a list of associated columns backend dependent*/\ncontext.global.knex.raw('pragma table_info('+flow.get('sql').table+')').then(\n function(resp){\n flow.get('sql').columns=resp;\n // console.log(resp);\n node.send(msg);\n }\n);\nreturn null;","outputs":1,"noerr":0,"x":428,"y":460,"wires":[["ac46feba.89eca"]]},{"id":"b680954.c1ef968","type":"debug","z":"e81c960d.042c08","name":"","active":false,"console":"false","complete":"true","x":671,"y":271,"wires":[]},{"id":"9ec88988.209e98","type":"function","z":"e81c960d.042c08","name":"save search","func":"flow.get('sql').search=msg.payload;\nflow.get('sql').page=0;\nreturn msg;","outputs":1,"noerr":0,"x":304,"y":328,"wires":[["ac46feba.89eca"]]},{"id":"e3cf3c63.fd3cf","type":"ui_template","z":"e81c960d.042c08","group":"1b831892.e67a07","name":"total","order":1,"width":"1","height":"1","format":"<small> </small>\n<small>{{msg.total}}</small>","storeOutMessages":true,"fwdInMessages":true,"x":704,"y":460,"wires":[[]]},{"id":"1b831892.e67a07","type":"ui_group","z":"","name":"Table Browser 2","tab":"4782f281.167f9c","order":1,"disp":true,"width":"27"},{"id":"4782f281.167f9c","type":"ui_tab","z":"","name":"SQL 2.0","icon":"dashboard","order":1}] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment