154 lines
3.8 KiB
PL/PgSQL
154 lines
3.8 KiB
PL/PgSQL
BEGIN;
|
|
|
|
ALTER TABLE chats RENAME TO chats_old;
|
|
|
|
CREATE TABLE IF NOT EXISTS chats (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account TEXT NOT NULL COLLATE NOCASE,
|
|
jid TEXT NOT NULL COLLATE NOCASE,
|
|
type INTEGER NOT NULL,
|
|
timestamp INTEGER,
|
|
thread_id TEXT,
|
|
resource TEXT,
|
|
nickname TEXT,
|
|
password TEXT,
|
|
room_state INTEGER
|
|
);
|
|
|
|
INSERT INTO chats (
|
|
account, jid, type, timestamp, thread_id, resource, nickname, password, room_state
|
|
)
|
|
SELECT account, jid, type, timestamp, thread_id, resource, nickname, password, room_state
|
|
FROM chats_old;
|
|
|
|
DROP TABLE chats_old;
|
|
|
|
CREATE INDEX IF NOT EXISTS chat_jid_idx on chats (
|
|
jid, account
|
|
);
|
|
|
|
ALTER TABLE chat_history RENAME TO chat_history_old;
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account TEXT NOT NULL COLLATE NOCASE,
|
|
jid TEXT NOT NULL COLLATE NOCASE,
|
|
author_jid TEXT COLLATE NOCASE,
|
|
author_nickname TEXT,
|
|
timestamp INTEGER,
|
|
item_type INTEGER,
|
|
data TEXT,
|
|
stanza_id TEXT,
|
|
state INTEGER,
|
|
preview TEXT,
|
|
error TEXT
|
|
);
|
|
|
|
INSERT INTO chat_history (
|
|
account, jid, author_jid, author_nickname, timestamp, item_type, data, stanza_id, state, preview
|
|
)
|
|
SELECT account, jid, author_jid, author_nickname, timestamp, item_type, data, stanza_id, state, preview
|
|
FROM chat_history_old;
|
|
|
|
DROP TABLE chat_history_old;
|
|
|
|
CREATE INDEX IF NOT EXISTS chat_history_account_jid_timestamp_idx on chat_history (
|
|
account, jid, timestamp
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS chat_history_account_jid_state_idx on chat_history (
|
|
account, jid, state
|
|
);
|
|
|
|
ALTER TABLE roster_items RENAME TO roster_items_old;
|
|
ALTER TABLE roster_items_groups RENAME TO roster_items_groups_old;
|
|
|
|
CREATE TABLE IF NOT EXISTS roster_items (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account TEXT NOT NULL COLLATE NOCASE,
|
|
jid TEXT NOT NULL COLLATE NOCASE,
|
|
name TEXT,
|
|
subscription TEXT,
|
|
timestamp INTEGER,
|
|
ask INTEGER
|
|
);
|
|
|
|
INSERT INTO roster_items (
|
|
account, jid, name, subscription, timestamp, ask
|
|
)
|
|
SELECT account, jid, name, subscription, timestamp, ask
|
|
FROM roster_items_old;
|
|
|
|
CREATE TABLE IF NOT EXISTS roster_items_groups (
|
|
item_id INTEGER NOT NULL,
|
|
group_id INTEGER NOT NULL,
|
|
FOREIGN KEY(item_id) REFERENCES roster_items(id),
|
|
FOREIGN KEY(group_id) REFERENCES roster_groups(id)
|
|
);
|
|
|
|
INSERT INTO roster_items_groups (
|
|
item_id, group_id
|
|
)
|
|
SELECT i.id, go.group_id
|
|
FROM
|
|
roster_items_groups_old go
|
|
INNER JOIN roster_items_old io on io.id = go.item_id
|
|
INNER JOIN roster_items i on i.jid = io.jid;
|
|
|
|
DROP TABLE roster_items_groups_old;
|
|
DROP TABLE roster_items_old;
|
|
|
|
CREATE INDEX IF NOT EXISTS roster_item_jid_idx on roster_items (
|
|
jid, account
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS roster_item_groups_item_id_idx ON roster_items_groups (item_id);
|
|
CREATE INDEX IF NOT EXISTS roster_item_groups_group_id_idx ON roster_items_groups (group_id);
|
|
|
|
ALTER TABLE vcards_cache RENAME TO vcards_cache_old;
|
|
|
|
CREATE TABLE IF NOT EXISTS vcards_cache (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
jid TEXT NOT NULL COLLATE NOCASE,
|
|
data TEXT,
|
|
timestamp INTEGER
|
|
);
|
|
|
|
INSERT INTO vcards_cache (
|
|
jid, data, timestamp
|
|
)
|
|
SELECT jid, data, timestamp
|
|
FROM vcards_cache_old;
|
|
|
|
DROP TABLE vcards_cache_old;
|
|
|
|
CREATE INDEX IF NOT EXISTS vcards_cache_jid_idx on vcards_cache (
|
|
jid
|
|
);
|
|
|
|
ALTER TABLE avatars_cache RENAME TO avatars_cache_old;
|
|
|
|
CREATE TABLE IF NOT EXISTS avatars_cache (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
jid TEXT NOT NULL COLLATE NOCASE,
|
|
account TEXT NOT NULL COLLATE NOCASE,
|
|
hash TEXT NOT NULL,
|
|
type TEXT NOT NULL
|
|
);
|
|
|
|
INSERT INTO avatars_cache (
|
|
jid, account, hash, type
|
|
)
|
|
SELECT jid, account, hash, type
|
|
FROM avatars_cache_old;
|
|
|
|
DROP TABLE avatars_cache_old;
|
|
|
|
CREATE INDEX IF NOT EXISTS avatars_cache_jid_idx on avatars_cache (
|
|
jid
|
|
);
|
|
|
|
COMMIT;
|
|
|
|
PRAGMA user_version = 2;
|