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;