@sql-extra/setuptable v0.1.9

@sql-extra/setuptable

Generate SQL commands to setup table (create, insert, index).

const setupTable = require('@sql-extra/setuptable');
// setupTable.index(<name>, <columns>, [options]) -> sql index commands only
// setupTable(<name>, <columns>, [values], [options])
// -> sql commands

// options: {
//   pk: null,  // primary key (on conflict): none
//   index: false,   // create index: no
//   tsvector: null, // create tsvector view: no
// }


setupTable('food', {code: 'TEXT', name: 'TEXT'});
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);

setupTable('food', {code: 'TEXT', name: 'TEXT'},
  [{code: 'F1', name: 'Mango'}, {code: 'F2', name: 'Lychee'}]);
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
// INSERT INTO "food" ("code", "name") VALUES
// ('F1', 'Mango'),
// ('F2', 'Lychee');

setupTable('food', {code: 'TEXT', name: 'TEXT'},
  [{code: 'F1', name: 'Mango'}, {code: 'F2', name: 'Lychee'}],
  {index: true});
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT);
// INSERT INTO "food" ("code", "name") VALUES
// ('F1', 'Mango'),
// ('F2', 'Lychee');
// CREATE INDEX IF NOT EXISTS food_code_idx ON "food" ("code");
// CREATE INDEX IF NOT EXISTS food_name_idx ON "food" ("name");

setupTable('food', {code: 'TEXT', name: 'TEXT'},
  [{code: 'F1', name: 'Mango'}, {code: 'F2', name: 'Lychee'}],
  {pk: 'code', index: true, tsvector: {code: 'A', name: 'B'}});
// CREATE TABLE IF NOT EXISTS "food" ("code" TEXT, "name" TEXT, PRIMARY KEY("code"));
// INSERT INTO "food" ("code", "name") VALUES
// ('F1', 'Mango'),
// ('F2', 'Lychee')
// ON CONFLICT ("code") DO NOTHING;
// CREATE OR REPLACE VIEW "food_tsvector" AS SELECT *, setweight(to_tsvector('english', "code"), 'A')||setweight(to_tsvector('english', "name"), 'B') AS "tsvector" FROM "food";
// CREATE INDEX IF NOT EXISTS "food_tsvector_idx" ON "food" USING GIN ((setweight(to_tsvector('english', "code"), 'A')||setweight(to_tsvector('english', "name"), 'B')));
// CREATE INDEX IF NOT EXISTS "food_name_idx" ON "food" ("name");