1#@!#!123s

: / opt / psa / admin / plib / modules / xovi / library / Model /
Filename : init_schema.sql
back
CREATE TABLE IF NOT EXISTS `version` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `dbVersion` TEXT NOT NULL
);
INSERT INTO `version` (`dbVersion`) VALUES (date('now'));

CREATE TABLE IF NOT EXISTS `siteAuditResults` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `timestamp` INTEGER NOT NULL,
  `domainGuid` TEXT NOT NULL,
  `potential` TEXT NOT NULL,
  `kpi` TEXT NOT NULL,
  `errors` TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS `siteAuditErrors` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `timestamp` INTEGER NOT NULL,
  `domainGuid` TEXT NOT NULL,
  `issue` TEXT NOT NULL,
  `chartData` TEXT NOT NULL,
  `tableData` TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `category` TEXT NOT NULL,
  `domainGuid` TEXT NOT NULL,
  `taskId` INTEGER NOT NULL,
  `variables` TEXT NOT NULL,
  `status` INTEGER NOT NULL,
  `weight` INTEGER NOT NULL,
  `timestamp` INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS `loganalyzer` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `domainGuid` TEXT NOT NULL,
  `sengine` TEXT NOT NULL,
  `ip` TEXT NOT NULL,
  `timestamp` INTEGER NOT NULL,
  `path` TEXT NOT NULL,
  `directory` TEXT NOT NULL,
  `code` INTEGER NOT NULL,
  `size` INTEGER NOT NULL,
  `useragent` TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS `seo` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `domainGuid` TEXT NOT NULL,
  `sengine` TEXT NOT NULL,
  `date` TEXT NOT NULL,
  `metricData` TEXT NOT NULL,
  `timestamp` INTEGER NOT NULL,
  UNIQUE(domainGuid, sengine, date) ON CONFLICT REPLACE
);

CREATE TABLE IF NOT EXISTS `useractivity` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `tool` TEXT NOT NULL,
  `action` TEXT NOT NULL,
  `domainGuid` TEXT NOT NULL,
  `userGuid` TEXT NOT NULL,
  `timestamp` INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS `apierrors` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `method` TEXT NOT NULL,
  `code` TEXT NOT NULL,
  `message` TEXT NOT NULL,
  `domainGuid`,
  `userGuid` TEXT NOT NULL,
  `timestamp` INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS `apicache` (
  `hash` TEXT NOT NULL PRIMARY KEY,
  `method` TEXT NOT NULL,
  `result` TEXT NOT NULL,
  `expiration_time` INTEGER NOT NULL
);