miniflux-legacy/app/schemas/sqlite.php

154 lines
4.3 KiB
PHP
Raw Permalink Normal View History

2013-02-18 03:48:21 +01:00
<?php
2016-08-25 03:17:58 +02:00
namespace Miniflux\Schema;
2013-02-18 03:48:21 +01:00
2014-10-30 02:28:23 +01:00
use PDO;
2016-08-25 03:17:58 +02:00
use Miniflux\Helper;
2014-10-30 02:28:23 +01:00
const VERSION = 5;
function version_5(PDO $pdo)
{
$pdo->exec('CREATE INDEX items_status_idx ON items(status)');
}
function version_4(PDO $pdo)
{
$pdo->exec('ALTER TABLE feeds ADD COLUMN ignore_expiration INTEGER DEFAULT 0');
}
function version_3(PDO $pdo)
{
$pdo->exec('ALTER TABLE feeds ADD COLUMN expiration INTEGER DEFAULT 0');
}
function version_2(PDO $pdo)
{
$pdo->exec('ALTER TABLE feeds ADD COLUMN parsing_error_message TEXT');
}
2015-10-20 03:21:18 +02:00
function version_1(PDO $pdo)
2013-02-18 03:48:21 +01:00
{
$pdo->exec('CREATE TABLE users (
id INTEGER PRIMARY KEY,
2016-12-26 21:07:18 +01:00
username TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
is_admin INTEGER DEFAULT 0,
last_login INTEGER,
api_token TEXT NOT NULL UNIQUE,
bookmarklet_token TEXT NOT NULL UNIQUE,
cronjob_token TEXT NOT NULL UNIQUE,
feed_token TEXT NOT NULL UNIQUE,
fever_token TEXT NOT NULL UNIQUE,
fever_api_key TEXT NOT NULL UNIQUE
)');
$pdo->exec('CREATE TABLE user_settings (
"user_id" INTEGER NOT NULL,
"key" TEXT NOT NULL,
"value" TEXT NOT NULL,
PRIMARY KEY("user_id", "key"),
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)');
$pdo->exec('CREATE TABLE feeds (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
feed_url TEXT NOT NULL,
site_url TEXT,
2016-12-26 21:07:18 +01:00
title TEXT COLLATE NOCASE NOT NULL,
last_checked INTEGER DEFAULT 0,
last_modified TEXT,
etag TEXT,
enabled INTEGER DEFAULT 1,
download_content INTEGER DEFAULT 0,
parsing_error INTEGER DEFAULT 0,
rtl INTEGER DEFAULT 0,
cloak_referrer INTEGER DEFAULT 0,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE(user_id, feed_url)
)');
$pdo->exec('CREATE TABLE items (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
feed_id INTEGER NOT NULL,
checksum TEXT NOT NULL,
2016-12-26 21:07:18 +01:00
status TEXT NOT NULL,
bookmark INTEGER DEFAULT 0,
2016-12-26 21:07:18 +01:00
url TEXT NOT NULL,
title TEXT COLLATE NOCASE NOT NULL,
author TEXT,
content TEXT,
updated INTEGER,
enclosure_url TEXT,
enclosure_type TEXT,
language TEXT,
rtl INTEGER DEFAULT 0,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY(feed_id) REFERENCES feeds(id) ON DELETE CASCADE,
UNIQUE(feed_id, checksum)
)');
$pdo->exec('CREATE TABLE "groups" (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
2016-12-26 21:07:18 +01:00
title TEXT COLLATE NOCASE NOT NULL,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE(user_id, title)
)');
$pdo->exec('CREATE TABLE "feeds_groups" (
2016-12-26 21:07:18 +01:00
feed_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
PRIMARY KEY(feed_id, group_id),
FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE,
FOREIGN KEY(feed_id) REFERENCES feeds(id) ON DELETE CASCADE
)');
$pdo->exec('CREATE TABLE favicons (
id INTEGER PRIMARY KEY,
hash TEXT UNIQUE,
type TEXT
)');
$pdo->exec('CREATE TABLE "favicons_feeds" (
feed_id INTEGER NOT NULL,
favicon_id INTEGER NOT NULL,
PRIMARY KEY(feed_id, favicon_id),
FOREIGN KEY(favicon_id) REFERENCES favicons(id) ON DELETE CASCADE,
FOREIGN KEY(feed_id) REFERENCES feeds(id) ON DELETE CASCADE
)');
$pdo->exec('CREATE TABLE remember_me (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
ip TEXT,
user_agent TEXT,
token TEXT,
sequence TEXT,
expiration INTEGER,
date_creation INTEGER,
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
)');
$fever_token = Helper\generate_token();
$rq = $pdo->prepare('
INSERT INTO users
(username, password, is_admin, api_token, bookmarklet_token, cronjob_token, feed_token, fever_token, fever_api_key)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
2013-02-18 03:48:21 +01:00
');
$rq->execute(array(
'admin',
password_hash('admin', PASSWORD_BCRYPT),
'1',
Helper\generate_token(),
Helper\generate_token(),
Helper\generate_token(),
Helper\generate_token(),
$fever_token,
md5('admin:'.$fever_token),
));
}