新建table
CREATE TABLE MEMBER( TIMESTAMP INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT NOT NULL, LASTNAME TEXT NOT NULL, COUNTRY TEXT NOT NULL)
1) TIMESTAMP INT PRIMARY KEY NOT NULL,
2) FIRSTNAME TEXT NOT NULL,
3) LASTNAME TEXT NOT NULL,
4) COUNTRY TEXT NOT NULL)
write query function
var d = new Date();
var timestamp = d.getTime();
var newMsg = {
"payload":{
"time":timestamp,
"firstname": msg.payload.firstname,
"lastname": msg.payload.lastname,
"country" : msg.payload.country
},
"topic": "INSERT INTO MEMBER VALUES ( " + timestamp + ", '" + msg.payload.firstname + "', '" + msg.payload.lastname + "', '" + msg.payload.country + "')"
}
return newMsg;
msg function
msg.topic = "SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;";
return msg;
Template HTML樣板
<table style="width:100%">
<tr>
<th>Index</th>
<th>Timestamp</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Country</th>
</tr>
<tr ng-repeat="x in msg.payload | limitTo:20">
<td>{{$index}}</td>
<td>{{msg.payload[$index].TIMESTAMP}}</td>
<td>{{msg.payload[$index].FIRSTNAME}}</td>
<td>{{msg.payload[$index].LASTNAME}}</td>
<td>{{msg.payload[$index].COUNTRY}}</td>
</tr>
</table>
View records
SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;
Flow JSON Sourcecode
[{"id":"cf55a763.b5ff38","type":"ui_form","z":"fea04978.fe55d8","name":"","label":"","group":"31745d9a.412202","order":0,"width":0,"height":0,"options":[{"label":"First Name","value":"firstname","type":"text","required":true},{"label":"Last Name","value":"lastname","type":"text","required":true},{"label":"Country","value":"country","type":"text","required":true}],"formValue":{"firstname":"","lastname":"","country":""},"payload":"","topic":"","x":110,"y":80,"wires":[["3f5f4dda.fc2022","24308878.5823e8"]]},{"id":"3f5f4dda.fc2022","type":"http request","z":"fea04978.fe55d8","name":"","method":"POST","ret":"txt","url":"localhost:1880/mysitepost","tls":"","x":310,"y":80,"wires":[[]]},{"id":"f71bc20e.2b85b","type":"sqlite","z":"fea04978.fe55d8","mydb":"77e8c1a4.6e881","sqlquery":"msg.topic","sql":"","name":"","x":520,"y":140,"wires":[["e5d3a3d1.44fea","ebe341d5.18eec"]]},{"id":"fa5d0e64.396e4","type":"inject","z":"fea04978.fe55d8","name":"create database table","topic":"CREATE TABLE MEMBER( TIMESTAMP INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT NOT NULL, LASTNAME TEXT NOT NULL, COUNTRY TEXT NOT NULL)","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":260,"y":200,"wires":[["f71bc20e.2b85b"]]},{"id":"e5d3a3d1.44fea","type":"debug","z":"fea04978.fe55d8","name":"","active":true,"console":"false","complete":"false","x":670,"y":140,"wires":[]},{"id":"24308878.5823e8","type":"function","z":"fea04978.fe55d8","name":"write query","func":"var d = new Date();\nvar timestamp = d.getTime();\n\nvar newMsg = {\n \"payload\":{\n \"time\":timestamp,\n \"firstname\": msg.payload.firstname,\n \"lastname\": msg.payload.lastname,\n \"country\" : msg.payload.country\n },\n \"topic\": \"INSERT INTO MEMBER VALUES ( \" + timestamp + \", '\" + msg.payload.firstname + \"', '\" + msg.payload.lastname + \"', '\" + msg.payload.country + \"')\"\n}\nreturn newMsg;","outputs":1,"noerr":0,"x":310,"y":140,"wires":[["f71bc20e.2b85b"]]},{"id":"bb4ecef5.9fbc8","type":"inject","z":"fea04978.fe55d8","name":"View records","topic":"SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":270,"y":260,"wires":[["f71bc20e.2b85b"]]},{"id":"4c2d7ba7.5ab7e4","type":"inject","z":"fea04978.fe55d8","name":"delete","topic":"DELETE FROM MEMBER WHERE TIMESTAMP <= strftime('%s','now', '-1 hours')*1000","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"x":120,"y":140,"wires":[["f71bc20e.2b85b"]]},{"id":"98c12adf.ad0728","type":"ui_template","z":"fea04978.fe55d8","group":"63800e4a.d5e23","name":"","order":0,"width":"10","height":"10","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Firstname</th> \n <th>Lastname</th>\n <th>Country</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].FIRSTNAME}}</td> \n <td>{{msg.payload[$index].LASTNAME}}</td>\n <td>{{msg.payload[$index].COUNTRY}}</td>\n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":940,"y":180,"wires":[[]]},{"id":"99a0029a.fed36","type":"sqlite","z":"fea04978.fe55d8","mydb":"77e8c1a4.6e881","sqlquery":"msg.topic","sql":"","name":"","x":780,"y":180,"wires":[["98c12adf.ad0728"]]},{"id":"ebe341d5.18eec","type":"function","z":"fea04978.fe55d8","name":"msg","func":"msg.topic = \"SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;\";\nreturn msg;","outputs":1,"noerr":0,"x":650,"y":180,"wires":[["99a0029a.fed36"]]},{"id":"31745d9a.412202","type":"ui_group","z":"","name":"Default","tab":"1d173c13.e2dc64","disp":true,"width":"6"},{"id":"77e8c1a4.6e881","type":"sqlitedb","z":"","db":"sqlite.db","mode":"RWC"},{"id":"63800e4a.d5e23","type":"ui_group","z":"","name":"Database","tab":"1d173c13.e2dc64","disp":true,"width":"10"},{"id":"1d173c13.e2dc64","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]
沒有留言:
張貼留言