Skip to content

Instantly share code, notes, and snippets.

@njamescouk

njamescouk/createRoundTripDb.sql

Last active May 12, 2019 12:03
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save njamescouk/ae5f6bfe4ebb8af0afc00eacb7c0ae81 to your computer and use it in GitHub Desktop.
Save njamescouk/ae5f6bfe4ebb8af0afc00eacb7c0ae81 to your computer and use it in GitHub Desktop.
Download ZIP
simple minded load db - display - add row - save html + js to demo sql.js
Raw
createRoundTripDb.sql
-- do
-- sqlite3 roundTrip.db < createRoundTripDb.sql
-- then open roundTrip.db from sqlJsRoundTrip.html
-- you may need to press the load db button, or you may not
-- sql.js is available at https://github.com/kripken/sql.js/
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t
(
f1 TEXT
, f2 TEXT
, f3 TEXT
);
INSERT INTO t VALUES('1','2','3');
COMMIT;
Raw
sqlJsRoundTrip.html
<!DOCTYPE html>
<html>
<head>
<!--
BEWARE: this file has been generated by formgen and will be overwritten.
cmd: formgen sqlJsRoundTrip.fg
2019-05-11 21:17:01
-->
<meta charset="UTF-8" />
<meta name="generator" content="njames.co.uk : formgen"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>
sqlJsRoundTrip
</title>
<style>
body
{
margin-left: 5em;
background-color: #FFFFE0;
}
form, fieldset
{
max-width: 40ex;
}
form
{
padding-top: 1em;
padding-bottom: 1em;
/* border-style: solid; */
border-width: 1px;
border-color: #888;
padding-left: 3ex;
margin-bottom: 1em;
}
img
{
min-height: 100px;
min-width: 100px;
}
form.fgForm > button
{
display: block;
margin-bottom: .25em;
}
legend
{
padding-left: 1ex;
padding-right: 1ex;
}
label
{
width: 5em;
display: inline-block;
}
div.fgField
{
vertical-align: top;
margin-bottom: .5em;
}
div.fgField>span
{
background-color: #ddd;
}
div.fgField>span, input
{
border-style: inset;
border-width: 1px;
border-color: #888;
vertical-align: top;
margin-bottom: .5em;
min-height: 1.2em;
width: 10em;
display: inline-block;
}
input.fgInput
{
font-family: monospace;
font-size: medium;
}
fieldset
{
margin-bottom: 3ex;
}
/*
fieldset > label, form > label, form > button
{
display: inline-block;
}
*/
input[type=image]
{
cursor: crosshair;
}
input:focus
{
outline-width: 1px;
outline-style: dashed;
outline-color: #C8686E;
}
span.fgFormTitle
{
/* font-weight: bold; */
display: block;
padding-bottom: .5em;
}
span.fgDropdown>select
{
font-family: monospace;
}
a.fgButtonLikeLink
{
color: black;
background-color: #D4D0C8;
font-size: x-small;
border-top: 2px solid #FFFFFF;
border-right: 2px solid #404040;
border-bottom: 2px solid #404040;
border-left: 2px solid #FFFFFF;
text-decoration: none;
padding: 2px;
padding-left: 4px;
padding-right: 4px;
/*
border-width: 1px 1px 1px 1px;
display: block;
*/
}
a.fgButtonLikeLink:active
{
border-top: 2px solid #404040;
border-right: 2px solid #FFFFFF;
border-bottom: 2px solid #FFFFFF;
border-left: 2px solid #404040;
}
td
{
padding: 5px;
border-width: 1px;
border-style: dotted;
}
.fgCheckbox, .fgDropdown, .fgRadio
{
display: inline-block;
}
</style>
</head>
<body onload="init()">
<p class="fgPara" id="fgPara001">open roundTrip.db write table t to html</p>
<p class="fgPara" id="fgPara002">add line to table and save to db</p>
<!-- fgVerbatim004 --><label style="width:9em;" for="userFile">open roundTrip.db:</label><input type="file" id="userFile" name="dbFileEdit" value="" accept="application/x-sqlite3"><!-- EndVerbatim -->
<button class="fgButton" id="fgButton005" onclick="readDb()" type="submit">load db</button>
<!-- GROUP values for new line... -->
<fieldset id="fgGroup011">
<legend>values for new line...</legend>
<!-- Edit f1 -->
<span class="fgEdit">
<label for="fgEdit007">f1</label><input id="fgEdit007" name="number" value="" >
</span>
<!-- Edit f2 -->
<span class="fgEdit">
<label for="fgEdit008">f2</label><input id="fgEdit008" name="text" value="" >
</span>
<!-- Edit f3 -->
<span class="fgEdit">
<label for="fgEdit009">f3</label><input id="fgEdit009" name="number" value="" >
</span>
<button class="fgButton" id="fgButton010" onclick="doAddLine(newF1, newF2, newF3)" type="submit">add</button>
</fieldset>
<!-- Table fgTable012 -->
<table class="fgTable" id="fgTable012">
<thead>
<tr><th>f1</th><th>f2</th><th>f3</th></tr>
</thead>
<tbody id="fgTable012Body">
</tbody>
</table>
<button class="fgButton" id="fgButton013" onclick="doSave(userTableBody)" type="submit">save db</button>
<!-- fgVerbatim014 --> <a id="downloadlink">Download</a><!-- EndVerbatim -->
<!-- fgVerbatim016 --><script src="sql.js" type="text/javascript"></script><!-- EndVerbatim -->
<!--
=============
fg javascript
=============
-->
<script type="text/javascript">
var newF1;
var newF2;
var newF3;
var userTable;
fgGetRadioSelection = function ()
{
var fgRadioOptions = this.children;
for (var i = 0; i < fgRadioOptions.length; i++)
{
var fgRadioInput = fgRadioOptions[i].children[1];
if (fgRadioInput.checked)
{
return fgRadioInput.value;
}
}
return "";
}
fgGetDropdownSelection = function ()
{
var selctd = [];
var fgDropdownOptions = this.selectedOptions;
for (var i = 0; i < fgDropdownOptions.length; i++)
{
var fgDropdownInput = fgDropdownOptions[i];
selctd.push(fgDropdownInput.value);
}
var res = "";
if (selctd.length === 1)
{
res = selctd[0];
}
return res;
}
function fgUserVarsInit()
{
newF1 = document.getElementById("fgEdit007");
newF2 = document.getElementById("fgEdit008");
newF3 = document.getElementById("fgEdit009");
userTable = document.getElementById("fgTable012");
}
/*
sqlJsRoundTrip.js
*/
var userTableBody;
var roundTripDB;
var dbDownload;
var dbName = "";
var dbFileEdit;
function init()
{
fgUserVarsInit();
userTableBody = userTable.tBodies[0];
dbFileEdit = document.getElementById("userFile");
dbFileEdit.oninput = readDb;
dbDownload = document.getElementById("downloadlink");
roundTripDB = null;
}
function clearTBody(tb)
{
var tableRows = tb.rows;
const rowsToDelete = tableRows.length;
for (var i = 0; i < rowsToDelete; i++)
{
tb.deleteRow(0);
}
}
function clearTable()
{
newF1.value = '';
newF2.value = '';
newF3.value = '';
clearTBody(userTableBody);
dbDownload.hidden=true;
}
function doAddLine(newF1, newF2, newF3)
{
window.URL.revokeObjectURL(dbDownload.href);
dbDownload.hidden=true;
var newRow = [newF1.value, newF2.value, newF3.value];
addTr (userTableBody, newRow);
}
function addTr(tbody, arrValues)
{
var newRow = tbody.insertRow(-1);
if (Array.isArray(arrValues))
{
for (var i = 0; i < arrValues.length; i++)
{
var cell = newRow.insertCell(i);
cell.innerHTML = arrValues[i];
}
}
else if (typeof arrValues == 'object')
{
var thingy = Object.keys(arrValues);
for (var i = 0; i < thingy.length; i++)
{
var keyThingy = thingy[i];
var cell = newRow.insertCell(i);
cell.innerHTML = arrValues[thingy[i]];
}
}
}
function readDb()
{
clearTable();
window.URL.revokeObjectURL(dbDownload.href);
dbDownload.hidden=true;
var f = dbFileEdit.files[0];
dbName = f.name;
var r = new FileReader();
r.onload = function()
{
var Uints = new Uint8Array(r.result);
if (roundTripDB != null)
{
roundTripDB.close();
}
roundTripDB = new SQL.Database(Uints);
var res = roundTripDB.exec("SELECT * FROM t;");
var thingy = res[0].values;
for (var i = 0; i < thingy.length; i++)
{
addTr(userTableBody, thingy[i]);
}
}
r.readAsArrayBuffer(f);
};
function doSave(tbody)
{
if (roundTripDB != null)
{
var sqlStr = "BEGIN TRANSACTION;\nDELETE FROM t;\n\nINSERT OR REPLACE INTO t(f1, f2, f3) VALUES ";
var tableRows = userTableBody.rows;
for (var i = 0; i < tableRows.length; i++)
{
var rowCells = tableRows[i].cells;
var newF1 = rowCells[0];
var newF2 = rowCells[1];
var newF3 = rowCells[2];
if (i > 0)
{
sqlStr += "\n,";
}
sqlStr += '(' + newF1.textContent + ',' + newF2.textContent + ',' + newF3.textContent + ')';
};
sqlStr += ";\nCOMMIT;\n";
roundTripDB.exec(sqlStr);
var binaryArray = roundTripDB.export();
var blob = new Blob([binaryArray]);
dbDownload.href = window.URL.createObjectURL(blob);
dbDownload.download = dbName;
dbDownload.hidden=false;
}
};
/*
end sqlJsRoundTrip.js
*/
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment