the mysql database schema that powers pinboard.in. useful if you are trying to create your own pinboard like service.
-- 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
`create