53 lines
1.3 KiB
SQL
53 lines
1.3 KiB
SQL
BEGIN TRANSACTION;
|
|
CREATE TABLE IF NOT EXISTS "jobs" (
|
|
"_id" INTEGER NOT NULL UNIQUE,
|
|
"title" TEXT,
|
|
"site" TEXT,
|
|
"url" TEXT,
|
|
"id" TEXT UNIQUE,
|
|
"summary" TEXT,
|
|
"company" TEXT,
|
|
"location" TEXT,
|
|
"postdate" TEXT,
|
|
"salary" TEXT,
|
|
"easyapply" INTEGER,
|
|
"timestamp" INTEGER,
|
|
PRIMARY KEY("_id" AUTOINCREMENT)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS "read" (
|
|
"_id" INTEGER NOT NULL UNIQUE,
|
|
"rid" INTEGER UNIQUE,
|
|
"d" INTEGER NOT NULL,
|
|
PRIMARY KEY("_id" AUTOINCREMENT),
|
|
FOREIGN KEY("rid") REFERENCES "jobs"("_id")
|
|
);
|
|
CREATE TABLE IF NOT EXISTS "applied" (
|
|
"_id" INTEGER NOT NULL UNIQUE,
|
|
"aid" INTEGER UNIQUE,
|
|
"a" INTEGER NOT NULL,
|
|
PRIMARY KEY("_id" AUTOINCREMENT),
|
|
FOREIGN KEY("aid") REFERENCES "jobs"("_id")
|
|
);
|
|
|
|
DROP VIEW IF EXISTS "jobsList";
|
|
CREATE VIEW jobsList as
|
|
select jobs._id, jobs.title, jobs.site, jobs.company, jobs.timestamp, read.d, applied.a from jobs
|
|
left join read on read.rid = jobs._id
|
|
left join applied on applied.aid = jobs._id
|
|
|
|
order by jobs._id desc;
|
|
|
|
DROP TABLE IF EXISTS "accounts";
|
|
CREATE TABLE IF NOT EXISTS "accounts" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
|
|
"username" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL
|
|
);
|
|
INSERT INTO accounts VALUES(1,'bob','plop','bob@plop.com');
|
|
INSERT INTO accounts VALUES(2,'martin','MPReoa43','martind2000@gmail.com');
|
|
|
|
INSERT INTO sqlite_sequence VALUES('accounts',2);
|
|
|
|
COMMIT;
|