Skip to content

Instantly share code, notes, and snippets.

@ifraixedes

ifraixedes/CREDITS

Created October 4, 2015 23:36
Show Gist options
  • Save ifraixedes/485329b7d46531e50e23 to your computer and use it in GitHub Desktop.
Save ifraixedes/485329b7d46531e50e23 to your computer and use it in GitHub Desktop.
Download ZIP
Demo of my talk about Lovefield and sql.js at @BcnJS - http://blog.fraixed.es/post/speaking-bcnjs-intro-to-lovefield/
Raw
CREDITS
I used one of demos of Lovefield project (https://github.com/google/lovefield) as an inspiration.
Raw
index.html
<DOCTYPE html>
<html>
<head>
<script src="/node_modules/sql.js/js/sql.js"></script>
<script src="/node_modules/lovefield/dist/lovefield.js"></script>
<script src="init-dbs.js"></script>
<title>Lovefield & Sql.js demo</title>
</head>
</html>
Raw
init-dbs.js
(function() {
function sqljsDB() {
var db = new SQL.Database();
db.run(`CREATE TABLE Medal (
city text,
color text,
country text,
discipline text,
eventGender text,
event text,
firstName text,
gender text,
lastName text,
sport text,
year integer
);
CREATE INDEX IF NOT EXISTS year_idx ON Medal(year);
CREATE INDEX IF NOT EXISTS lastName_idx ON Medal(lastName);`);
rawDataPromise.then(withPreparedStm).
then(function (db) {
window.dSql = {
db: db,
print: print
};
}).
then(function () { console.log('Sql.js ended')});
function withPreparedStm(data) {
var pstm = db.prepare(
//VALUES (?,?,?,?,?,?,?,?,?,?,?)
`INSERT INTO Medal(city, color, country, discipline, eventGender, event, firstName, gender, lastName, sport, year)
VALUES (?,?,?,?,?,?,?,?,?,?,?)`);
data.forEach(function (o) {
var v =[o.city, o.color, o.country, o.discipline, o.eventGender, o.event, o.firstName, o.gender, o.lastName, o.sport, o.year];
pstm.run(v);
});
return db;
}
// IT CRASHES if all the rows are concatenated
function allInOneQuery(data) {
var stms = ['INSERT INTO Medal(city, color, country, discipline, eventGender, event, firstName, gender, lastName, sport, year) VALUES '];
data.forEach(function (o, idx) {
if (idx === 0) {
stms[0] += `('${o.city}', '${o.color}', '${o.country}', '${o.discipline}', '${o.eventGender}', '${o.event}', '${o.firstName}', '${o.gender}', '${o.lastName}', '${o.sport}', ${o.year})`;
return;
}
stms.push(`('${o.city}', '${o.color}', '${o.country}', '${o.discipline}', '${o.eventGender}', '${o.event}', '${o.firstName}', '${o.gender}', '${o.lastName}', '${o.sport}', ${o.year})`);
});
db.run(stms.join(','));
return db
}
function print(results) {
results.forEach(function (stmResult) {
console.table(stmResult.values);
});
}
}
function lovelFieldDB() {
var dLf;
function buildSchema() {
var schemaBuilder = lf.schema.create('olympia', 1);
schemaBuilder.createTable('Medal').
addColumn('city', lf.Type.STRING).
addColumn('color', lf.Type.STRING).
addColumn('country', lf.Type.STRING).
addColumn('discipline', lf.Type.STRING).
addColumn('eventGender', lf.Type.STRING).
addColumn('event', lf.Type.STRING).
addColumn('firstName', lf.Type.STRING).
addColumn('gender', lf.Type.STRING).
addColumn('lastName', lf.Type.STRING).
addColumn('sport', lf.Type.STRING).
addColumn('year', lf.Type.NUMBER).
addIndex('idx_year', ['year']).
addIndex('idx_lastName', ['lastName']).
addNullable(['firstName']);
return schemaBuilder;
}
function checkForExistingData_() {
var medal = dLf.getSchema().table('Medal');
return dLf.select().from(medal).exec().then(
function(rows) {
return rows.length > 0;
});
}
function insertData_() {
var medal = dLf.getSchema().table('Medal');
return rawDataPromise.then(function (data) {
var rows = data.map(function(obj) {
return medal.createRow(obj);
});
return dLf.insert().into(medal).values(rows).exec();
});
}
function init() {
return buildSchema().connect().then((function(database) {
dLf = database;
window.dLf = database;
return checkForExistingData_();
})).then((function(dataExist) {
return dataExist ? Promise.resolve() : insertData_();
}));
}
return init().then(function () { console.log('Lovefield ended')});
}
var rawDataPromise = new Promise(function (resolve, reject) {
var xhr = new XMLHttpRequest();
// You can find the data file that I used on the next URL
//https://raw.githubusercontent.com/google/lovefield/2a9e5d82a518d76949fd71d1cd70bcada2cd0865/demos/olympia_db/data/olympic_medalists.json
xhr.open('GET', '/demo/olympic_medalists.json');
xhr.send();
xhr.onreadystatechange = function() {
if (xhr.readyState == 4) {
try {
resolve(JSON.parse(xhr.responseText));
} catch (e) {
reject(`Error parsing raw data json: ${e.message}`);
}
}
};
});
lovelFieldDB();
sqljsDB();
})();
Raw
LICENSE
WHAT THE FUCK YOU WANT TO PUBLIC LICENSE Version 2, December 2004
(http://www.wtfpl.net/about/)
Copyright (C) 2015 Ivan Fraixedes <[email protected]> (https://ivan.fraixed.es)
Everyone is permitted to copy and distribute verbatim or modified
copies of this license document, and changing it is allowed as long
as the name is changed.
DO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
0. You just DO WHAT THE FUCK YOU WANT TO.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment