I'm learning node js at the mo, and using SQLite3 to create a portable project/task/workflow tool (it'll eventually end up on github).
If it's the first time a user is running the node app (and the SQLite database file does not exist), I want it to run an install SQL script to create all the tables it'll need to work.
I could do them all as separate transactions, but that looks messy and it's much easier to do it all in one call.
In Oracle I know I can wrap them all in as follows:
BEGIN CREATE TABLE1...; CREATE TABLE2...; END;
SQLite though, I don't know as much about (and don't know if I can do what I am trying to do).
The code below is only create the first table (and not the others).
Thanks.
Express Node JS app calling the SQL on startup
// Declare Express
var express = require('express');
// Instantiate Express
var app = express();
// Create a http server with express
var server = require('http').createServer(app);
// Instantiate socket on the http express server
var io = require('socket.io').listen(server);
// Declare filesystem
var fs = require('fs');
// Database File
var db_file = './sqlite/db-rhubarb.sqlite';
// Check if the dataexists
var db_exists = fs.existsSync(db_file);
// Declare sqlite3
var sqlite3 = require('sqlite3').verbose();
// Instantiate sqlite3 database
var db = new sqlite3.Database(db_file);
// Check if a database exists - create if on first run
db.serialize(function() {
//if (!fs.existsSync(db_file)) {
console.log("Can't find a SQLite database, creating one now...");
var install_sql = fs.readFileSync('./sqlite/sql/install.sql', 'utf-8');
db.run(install_sql);
//}
});
// Application port (this is what eg localhost:1227)
var port = 1127;
// Tell server listen on port
server.listen(port);
console.log("Running application on port: "+port);
Install SQL
/* Create the contacts table */
CREATE TABLE IF NOT EXISTS 'main'.'contacts' ( "contact_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"date_added" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER );
/* Create the contacts attribute table */
CREATE TABLE IF NOT EXISTS "main"."contact_attributes" ( "cont_attr_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"contact_id" INTEGER,
"date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER,
"attr_name" VARCHAR,
"attr_value" VARCHAR );
/* Create the task table */
CREATE TABLE IF NOT EXISTS "main"."tasks" ( "task_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER,
"task_value" VARCHAR );
/* Create the tag table */
CREATE TABLE IF NOT EXISTS "main"."tags" ( "tag_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER,
"tag_value" VARCHAR UNIQUE );
/* Create the tag mapping table */
CREATE TABLE IF NOT EXISTS "main"."map_tag_task" ( "map_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER,
"tag_id" INTEGER,
"task_id" INTEGER );
/* Create the log table */
CREATE TABLE IF NOT EXISTS "main"."logs" ( "log_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER,
"task_id" INTEGER,
"log_value" VARCHAR );
/* Create the task messages table */
CREATE TABLE IF NOT EXISTS "main"."messages" ( "message_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"date_created" DATETIME DEFAULT CURRENT_TIMESTAMP,
"user_id_created" INTEGER,
"task_id" INTEGER,
"message_value" VARCHAR );
/* Create the users table */
CREATE TABLE IF NOT EXISTS "main"."users" ( "user_id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
"forename" VARCHAR,
"surname" VARCHAR,
"password" VARCHAR,
"salt" VARCHAR );
Aucun commentaire:
Enregistrer un commentaire