Archive

$ duckdb -ui

can have multiple databases including in memory database attached. Can explore the databases using Table summary, the notebooks are useful in organizing work into notebooks. SQL is the language of the notebooks like [[python]] is for [[jupyter]] notebooks. The notebook cells support syntax highlighting and autocompletion.

The results can be visualized using the column explorer, which shows the summary of the results. It is "type aware", and thus can visulize things like percentiles, median etc.

Interestingly, the the UI extension is all written in [[cpp]] - https://github.com/duckdb/duckdb-ui

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