|Portada|Blog|Space|

[ Go back to WG-S20 index page. ]

====== SQLite database ======

Inside the backup tar "enotes.bkup" we can fin the metadata database, called
enotes.db3, with the following schema (thank you Sharp for making it public on
the backups):

CREATE TABLE "notes" (
  "id" integer NOT NULL PRIMARY KEY,
  "form_id" integer DEFAULT 1 REFERENCES "forms" ("id"),
  "create_date" integer NOT NULL,
  "modify_date" integer NOT NULL,
  "create_date_page" integer NOT NULL,
  "imgname" TEXT,
  "thumbnail" BLOB,
  "page_id" integer DEFAULT 0 REFERENCES "pages" ("id"));
CREATE INDEX "notes_page_id" ON "notes" ("page_id");
CREATE INDEX "notes_create_date_page" ON "notes" ("create_date_page");
CREATE INDEX "notes_modify_date" ON "notes" ("modify_date");
CREATE INDEX "notes_create_date" ON "notes" ("create_date");
CREATE INDEX "notes_form_id" ON "notes" ("form_id");

CREATE TABLE "tags" (
  "id" integer NOT NULL PRIMARY KEY,
  "image_sys" TEXT,
  "image_user" TEXT);

CREATE TABLE "forms" (
  "id" integer NOT NULL PRIMARY KEY,
  "imgname" TEXT);

CREATE TABLE "pages" (
  "id" integer NOT NULL PRIMARY KEY,
  "note_id" integer NOT NULL REFERENCES "notes" ("id"),
  "tag_id" integer DEFAULT 0 REFERENCES "tags" ("id"),
  "form_id" integer DEFAULT 1 REFERENCES "forms" ("id"),
  "imgname" TEXT,
  "create_date" integer NOT NULL,"modify_date" integer NOT NULL);
CREATE INDEX "pages_modify_date" ON "pages" ("modify_date");
CREATE INDEX "pages_create_date" ON "pages" ("create_date");
CREATE INDEX "pages_form_id" ON "pages" ("form_id");
CREATE INDEX "pages_tag_id" ON "pages" ("tag_id");
CREATE INDEX "pages_note_id" ON "pages" ("note_id");

CREATE TABLE "info" (
  "version" integer NOT NULL,
  "date_time" integer NOT NULL);
CREATE INDEX "info_date_time" ON "info" ("date_time");

CREATE TABLE "uforms" (
  "id" integer NOT NULL PRIMARY KEY,
  "form_id" integer DEFAULT 1 REFERENCES "forms" ("id"),
  "delflag" integer DEFAULT 0);
CREATE INDEX "uforms_form_id" ON "uforms" ("form_id");

CREATE TABLE "schedules" (
  "id" integer NOT NULL PRIMARY KEY,
  "form_group" integer NOT NULL,
  "start_date" integer NOT NULL,
  "end_date" integer NOT NULL,
  "imgname" TEXT,
  "create_date" integer NOT NULL,
  "modify_date" integer NOT NULL);
CREATE INDEX "schedules_modify_date" ON "schedules" ("modify_date");
CREATE INDEX "schedules_create_date" ON "schedules" ("create_date");
CREATE INDEX "schedules_start_date_end_date" ON "schedules" (start_date,end_date);

CREATE TABLE "schedule_pages" (
  "id" integer NOT NULL PRIMARY KEY,
  "schedule_id" integer NOT NULL REFERENCES "notes" ("id"),
  "form_id" integer DEFAULT 1 REFERENCES "forms" ("id"),
  "imgname" TEXT,
  "modify" integer DEFAULT 0,
  "start_date" integer NOT NULL,
  "end_date" integer NOT NULL);
CREATE INDEX "schedule_pages_form_id" ON "schedule_pages" ("form_id");
CREATE INDEX "schedule_pages_schedule_id" ON "schedule_pages" ("schedule_id");
CREATE INDEX "schedule_pages_modify_start_date_end_date" ON "schedule_pages" (modify,start_date,end_date);

CREATE TABLE "schedule_form" (
  "id" integer NOT NULL PRIMARY KEY,
  "imgname" TEXT);

CREATE TABLE "manual" (
  "id" integer NOT NULL PRIMARY KEY,
  "note_id" integer NOT NULL REFERENCES "notes" ("id"),
  "version" integer NOT NULL);
CREATE INDEX "manual_note_id" ON "manual" ("note_id");

CREATE TRIGGER INSERT_TRIGER AFTER INSERT ON pages BEGIN
  update notes set create_date_page=new.modify_date where id == new.note_id;
END;
CREATE TRIGGER UPDATE_TRIGER AFTER UPDATE ON pages BEGIN
  update notes set modify_date=new.modify_date where id == old.note_id and modify_date<new.modify_date;
END;