The pinboard.in database schema.

-- MySQL dump 10.13
--
-- Pinboard database schema
-- Feel free to use this however you like. Send questions to support@pinboard.in.
-- For best results, use the Percona version of MySQL! http://www.percona.com/software/percona-server/


CREATE TABLE `bookmarks` (
    `id` int(11) NOT NULL,
    `url` mediumtext,        -- verbatim URL (may differ from actual URL referenced by url_id)
    `title` varchar(255),
    `description` mediumtext,
    `user_id` int(11) NOT NULL,
    `toread` tinyint(1) DEFAULT '0',
    `private` binary(1) DEFAULT '0',
    `url_id` int(11),
    `slug` char(20),         -- opaque token for use in URLs
    `snapshot_id` int(11),
    `code` char(3),          -- http response code (if crawled)
    `source` smallint(6),    -- numeric
    `added_at` datetime,     -- date added to Pinboard
    `created_at` datetime,   -- stated creation date
    `updated_at` datetime,

    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`),
    UNIQUE KEY `bookmark` (`user_id`,`url`(255)),
    KEY `created` (`created_at`),
    KEY `user` (`user_id`),
    KEY `private` (`private`),
    KEY `url` (`url_id`),
    KEY `toread` (`toread`),
    KEY `updated` (`updated_at`),
    KEY `snapshot` (`snapshot_id`),
    KEY `code` (`code`),
    KEY `multi` (`user_id`,`private`,`toread`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Table structure for table `btags` (short for 'bookmark tags')


CREATE TABLE `btags` (
    `id` int(11) NOT NULL,
    `user_id` int(11) NOT NULL,
    `bookmark_id` int(11) NOT NULL,
    `url_id` int(11),
    `tag` varchar(255),
    `created_at` datetime,
    `private` tinyint(1),
    `seq` tinyint(4),         -- preserve display order
    PRIMARY KEY (`id`),
    UNIQUE KEY `btag` (`user_id`,`bookmark_id`,`tag`),
    KEY `user` (`user_id`),
    KEY `tag` (`tag`),
    KEY `bookmark` (`bookmark_id`),
    KEY `url` (`url_id`),
    KEY `private` (`private`),
    KEY `usertag` (`user_id`,`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




-- Table structure for table `snapshots`
-- (snapshots are directories created by wget)

CREATE TABLE `snapshots` (
    `id` int(11) NOT NULL,
    `url_id` int(11) NOT NULL,
    `crawled_at` datetime,
    `slug` varchar(30),
    `content_type` varchar(200),
    `etag` varchar(255),                  -- from Etag header
    `last_modified` varchar(255),         -- from Last-Modified header
    `code` char(3),                       -- http status code
    `content_length` varchar(20),         -- from Content-Length header
    `num_files` smallint(6),
    `size` int(11),                       -- size in bytes of snapshot files
    `filename` varchar(255),              -- which file in this snapshot to serve
    `updated_at` datetime,
    `user_id` int(11),
    `remote_backup` datetime,
    `flagged` binary(1) DEFAULT '0',
    `actual_url_id` int(11),              -- where we ended up after redirects
    `server` varchar(200),                -- where this snapshot is stored
    `charset` varchar(20),                -- detected charset (don't trust servers!)
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_url` (`url_id`,`user_id`),
    KEY `size` (`size`),
    KEY `content_length` (`content_length`),
    KEY `content_type` (`content_type`),
    KEY `slug` (`slug`),
    KEY `user_id` (`user_id`),
    KEY `code` (`code`),
    KEY `crawled` (`crawled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




--
-- Table structure for table `urls`
--

CREATE TABLE `urls` (
    `id` int(11) NOT NULL,
    `url` mediumtext CHARACTER SET latin1,  -- latin1 reduces storage requirement
    `created_at` datetime,
    `count` int(11),
    `slug` varchar(40),
    `alias_of` int(11),              -- mark duplicates
    `last_checked` datetime,
    `last_status` smallint(6),       -- most recent HTTP status code
    `content_hash` varchar(255),     -- content hash of most recent snapshot
    `etag` varchar(255),             -- from http headers
    `last_modified` varchar(255),    -- from http headers
    `domain` varchar(255),
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`),
    KEY `count` (`count`),
    KEY `url` (`url`(767))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



--
-- Table structure for table `user_tags` (summary table generated from btags)
-- tags are stored here twice - once a public set and a private set

CREATE TABLE `user_tags` (
    `tag` varchar(255),
    `user_id` int(11) NOT NULL,
    `count` int(11),
    `include_private` tinyint(1), -- is this tag in the public set or private set?
    UNIQUE KEY `usertag` (`user_id`,`tag`,`include_private`),
    KEY `count` (`count`),
    KEY `tag` (`tag`),
    KEY `user` (`user_id`),
    KEY `multi` (`user_id`,`include_private`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
    `id` int(11) NOT NULL,
    `login` varchar(40),
    `password` varchar(60),         -- bcrypt
    `created_at` datetime,
    `last_login` datetime,
    `email` varchar(255),
    `name` varchar(255),
    `enabled` tinyint(1) DEFAULT '0',
    `signup_paid` tinyint(1) DEFAULT '0',
    `email_confirmed` tinyint(1) DEFAULT '0',
    `reset_code` char(20),
    `email_slug` char(6),           -- secret token for bookmarking by email
    `last_active` datetime,         -- most recent add/edit/delete
    `bytes_used` int(11) DEFAULT '0',
    `disk_used` int(11) DEFAULT '0',
    `public_count` int(11),
    `private_count` int(11),
    `cached_count` int(11),         -- all crawled bookmarks (includes errors)
    `snapshot_count` int(11),       -- actual snapshots stored
    `unread_count` int(11),
    `rss_slug` char(20),            -- secret token for private RSS feeds
    `language` char(2),
    `tag_count` smallint(6),
    `snapshot_error_count` int(11),
    `cached_size` bigint(20),       -- total archive size in bytes (need bigint!)
    `cached_disk_size` bigint(20),  -- actual disk used
    `oversize_count` int(11),
    `is_premium` binary(1) DEFAULT '0',
    `fee` smallint(6),
    `ftext_indexed_at` datetime,    -- date last fulltext index completed
    `ftext_count` int(11),          -- number of bookmarks with parsed text
    `ftext_size` bigint(20),        -- size of extracted text

    PRIMARY KEY (`id`),
    UNIQUE KEY `login` (`login`),
    KEY `updated` (`created_at`),
    KEY `premium` (`is_premium`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;