Sqlite - Export SQL seems to skip table views creation

[jy95]

Summary

The AI export to SQLite is good for tables, but seems to skip table views creation

CREATE TABLE IF NOT EXISTS backlog (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  platform INTEGER,
  notes TEXT,
  FOREIGN KEY (platform) REFERENCES platforms (id)
);

CREATE TABLE IF NOT EXISTS games (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  videoId TEXT,
  playlistId TEXT,
  title TEXT,
  releaseDate TEXT,
  duration TEXT,
  platform INTEGER,
  FOREIGN KEY (platform) REFERENCES platforms (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_games_2 ON games (playlistId);
CREATE UNIQUE INDEX sqlite_autoindex_games_1 ON games (videoId);

CREATE TABLE IF NOT EXISTS games_dlcs (
  game INTEGER,
  dlc INTEGER,
  "order" INTEGER,
  FOREIGN KEY (dlc) REFERENCES games (id),
  FOREIGN KEY (game) REFERENCES games (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_games_dlcs_1 ON games_dlcs (game, dlc);

CREATE TABLE IF NOT EXISTS games_genres (
  game INTEGER,
  genre INTEGER,
  FOREIGN KEY (game) REFERENCES games (id),
  FOREIGN KEY (genre) REFERENCES genres (id)
);

CREATE TABLE IF NOT EXISTS games_schedules (
  id INTEGER,
  availableAt TEXT,
  endAt TEXT
);

CREATE TABLE IF NOT EXISTS genres (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE IF NOT EXISTS platforms (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE IF NOT EXISTS series (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

CREATE TABLE IF NOT EXISTS series_games (
  serie INTEGER,
  game INTEGER,
  "order" INTEGER,
  FOREIGN KEY (game) REFERENCES games (id),
  FOREIGN KEY (serie) REFERENCES series (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_series_games_1 ON series_games (serie, game);

CREATE TABLE IF NOT EXISTS sqlite_sequence (
  name TEXT,
  seq INTEGER
);

CREATE TABLE IF NOT EXISTS tests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  videoId TEXT,
  playlistId TEXT,
  title TEXT,
  releaseDate TEXT,
  platform INTEGER,
  duration TEXT,
  FOREIGN KEY (platform) REFERENCES platforms (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_tests_2 ON tests (playlistId);
CREATE UNIQUE INDEX sqlite_autoindex_tests_1 ON tests (videoId);

Steps to reproduce

• Use a SQLite database that have views
• Use DB Browser for SQLite 3.13.1, to run the query asked by ChartDB editor
• Click on "File > Export SQL > SQLite" and see results

Others

I suspect that ChartDB might not support recent versions / syntax of SQLite, to be checked ?

[johnnyfish]

@jy95 it fixed now?

[jy95]

@johnnyfish Doesn't seem, here is the sql I got with latest version :

CREATE TABLE IF NOT EXISTS backlog (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  title text NOT NULL,
  platform int,
  notes text
);

CREATE TABLE IF NOT EXISTS games (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  videoId text UNIQUE,
  playlistId text UNIQUE,
  title text NOT NULL,
  releaseDate text NOT NULL,
  duration text,
  platform int NOT NULL,
  FOREIGN KEY (platform) REFERENCES platforms (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_games_2 ON games (playlistId);
CREATE UNIQUE INDEX sqlite_autoindex_games_1 ON games (videoId);

CREATE TABLE IF NOT EXISTS games_dlcs (
  game int NOT NULL,
  dlc int NOT NULL,
  "order" int,
  FOREIGN KEY (game) REFERENCES games (id),
  FOREIGN KEY (dlc) REFERENCES games (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_games_dlcs_1 ON games_dlcs (game, dlc);

CREATE TABLE IF NOT EXISTS games_genres (
  game int NOT NULL,
  genre int NOT NULL,
  FOREIGN KEY (game) REFERENCES games (id),
  FOREIGN KEY (genre) REFERENCES genres (id)
);

CREATE TABLE IF NOT EXISTS games_schedules (
  id int,
  availableAt text,
  endAt text,
  FOREIGN KEY (id) REFERENCES games (id)
);

CREATE TABLE IF NOT EXISTS genres (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name text NOT NULL
);

CREATE TABLE IF NOT EXISTS platforms (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name text NOT NULL
);

CREATE TABLE IF NOT EXISTS series (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name text NOT NULL
);

CREATE TABLE IF NOT EXISTS series_games (
  serie int NOT NULL,
  game int NOT NULL,
  "order" int,
  FOREIGN KEY (serie) REFERENCES series (id),
  FOREIGN KEY (game) REFERENCES games (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_series_games_1 ON series_games (serie, game);

CREATE TABLE IF NOT EXISTS tests (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  videoId text UNIQUE,
  playlistId text UNIQUE,
  title text NOT NULL,
  releaseDate text NOT NULL,
  platform int NOT NULL,
  duration text,
  FOREIGN KEY (platform) REFERENCES platforms (id)
);

CREATE UNIQUE INDEX sqlite_autoindex_tests_2 ON tests (playlistId);
CREATE UNIQUE INDEX sqlite_autoindex_tests_1 ON tests (videoId);