--
-- This is the required schema for sqlite.
--
--     sqlite3 jabberd2.db <  db-setup.sqlite
--

--
-- c2s authentication/registration table
--
CREATE TABLE 'authreg' (
    'username' TEXT,
    'realm' TEXT,
    'password' TEXT,
    'token' VARCHAR(10),
    'sequence' INTEGER,
    'hash' VARCHAR(40) );

--
-- Session manager tables 
--

--
-- Active (seen) users
-- Used by: core
--
CREATE TABLE 'active' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'time' INT );

--
-- Logout times
-- Used by: mod_iq_last
--
CREATE TABLE 'logout' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'time' INT );

--
-- Roster items
-- Used by: mod_roster
--
CREATE TABLE 'roster-items' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'jid' TEXT,
    'name' TEXT,
    'to' BOOL,
    'from' BOOL,
    'ask' INTEGER );

--
-- Roster groups
-- Used by: mod_roster
--
CREATE TABLE 'roster-groups' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'jid' TEXT,
    'group' TEXT );

--
-- vCard (user profile information)
-- Used by: mod_iq_vcard
--
CREATE TABLE 'vcard' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'fn' TEXT,
    'nickname' TEXT,
    'url' TEXT,
    'tel' TEXT,
    'email' TEXT,
    'title' TEXT,
    'role' TEXT,
    'bday' TEXT,
    'desc' TEXT,
    'n-given' TEXT,
    'n-family' TEXT,
    'adr-street' TEXT,
    'adr-extadd' TEXT,
    'adr-locality' TEXT,
    'adr-region' TEXT,
    'adr-pcode' TEXT,
    'adr-country' TEXT,
    'org-orgname' TEXT,
    'org-orgunit' TEXT,
    'photo-type' TEXT,
    'photo-binval' BLOB );

--
-- Offline message queue
-- Used by: mod_offline
--
CREATE TABLE 'queue' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'xml' TEXT );

--
-- Private XML storage
-- Used by: mod_iq_private
--
CREATE TABLE 'private' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'ns' TEXT,
    'xml' TEXT );

--
-- Message Of The Day (MOTD) messages (announcements)
-- Used by: mod_announce
--
CREATE TABLE 'motd-message' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'xml' TEXT );

--
-- Times of last MOTD message for each user
-- Used by: mod_announce
--
CREATE TABLE 'motd-times' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'time' INTEGER );

--
-- User-published discovery items
-- Used by: mod_disco_publish
--
CREATE TABLE 'disco-items' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'jid' TEXT,
    'name' TEXT,
    'node' TEXT );

--
-- Default privacy list
-- Used by: mod_privacy
--
CREATE TABLE 'privacy-default' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'default' TEXT );

--
-- Privacy lists
-- Used by: mod_privacy
--
CREATE TABLE 'privacy-items' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'list' TEXT,
    'type' TEXT,
    'value' TEXT,
    'deny' BOOL,
    'order' INTEGER,
    'block' INTEGER );

--
-- Vacation settings
-- Used by: mod_vacation
--
CREATE TABLE 'vacation-settings' (
    'collection-owner' TEXT NOT NULL,
    'object-sequence' INTEGER PRIMARY KEY,
    'start' INTEGER,
    'end' INTEGER,
    'message' TEXT );

---
--- Index to speed up jabber_autobuddy operations
---
CREATE INDEX "roster-items-owner-jid-index" on "roster-items" ("collection-owner", "jid");