-- 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;