Skip to content

Instantly share code, notes, and snippets.

@hoehrmann
hoehrmann / sqlite.abnf
Created Jun 9, 2019
ABNF for SQLite 3.28 SQL
View sqlite.abnf
; FIXME: The grammar has been transformed so that `w` appears after a
; token, but there is no way in ABNF to define it as token-separator
; that can optionally contain a mix of comments and white-space. Take
; `;;` as an example, for that to match `sql-stmt-list` `w` would
; have to match the empty string. But if `w` matches the empty string
; then `ISNOT` is the same as `IS NOT`.
sql-stmt-list = [ sql-stmt ] *( ";" w [ sql-stmt ] )
sql-stmt = [ "EXPLAIN" w [ "QUERY" w "PLAN" w ] ] ( alter-table-stmt / analyze-stmt / attach-stmt / begin-stmt / commit-stmt / create-index-stmt / create-table-stmt / create-trigger-stmt / create-view-stmt / create-virtual-table-stmt / delete-stmt / delete-stmt-limited / detach-stmt / drop-index-stmt / drop-table-stmt / drop-trigger-stmt / drop-view-stmt / insert-stmt / pragma-stmt / reindex-stmt / release-stmt / rollback-stmt / savepoint-stmt / select-stmt / update-stmt / update-stmt-limited / vacuum-stmt )
alter-table-stmt = "ALTER" w "TABLE" w [ schema-name w "." w ] table-na
View sqlite-eval-params.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use JSON;
use YAML::XS;
my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');
our $Arg;
View utf8.sql
WITH
bytes AS (
SELECT 0x00 AS byte
UNION ALL
SELECT byte+1 FROM bytes WHERE byte < 0xFF
),
base AS (
SELECT 0x0000 AS cp
UNION ALL
SELECT cp+1 FROM base WHERE cp < 0x10FFFF
@hoehrmann
hoehrmann / set-intspan-partition.sql
Created May 5, 2019
SQL alternative for Set::IntSpan::Partition
View set-intspan-partition.sql
WITH
args AS (
SELECT
'
[
[[1,3]],
[[3,5],[7,8]],
[[8,8]],
[[0,100]]
]
@hoehrmann
hoehrmann / vowels-vs-consonants.ts
Created Apr 19, 2019
NodeJS backpressure streams with prioritisation
View vowels-vs-consonants.ts
const through2 = require('through2');
const split2 = require('split2');
var vowels = through2({ objectMode: true });
var consonants = through2({ objectMode: true });
var muxed = through2({ objectMode: true, highWaterMark: 1 });
[vowels, consonants].forEach(x => x.pipe(muxed));
muxed.pipe(process.stderr);
View evolveProtocol.ts
export namespace api {
export interface Request<
> {
method: string;
params: any;
result: any;
}
export interface Notification<
@hoehrmann
hoehrmann / evolveProtocol.ts
Last active Apr 18, 2019
Idea for JSON-RPC 2.0 interface definitions using TypeScript
View evolveProtocol.ts
export namespace api {
export interface Request<T extends string> {
params: any;
result: any;
__name: {
[name in T]: T
}
}
@hoehrmann
hoehrmann / fdpassing.ts
Created Apr 14, 2019
node <> Perl communication over additional fds
View fdpassing.ts
import { spawn } from 'child_process';
import { Writable, Readable } from 'stream';
const child = spawn(
'perl',
['-e', `
use IO::Handle;
my $in = IO::Handle->new_from_fd($ENV{JIPE_RHS_STDIN_FD}, 'r');
my $out = IO::Handle->new_from_fd($ENV{JIPE_RHS_STDOUT_FD}, 'w');
@hoehrmann
hoehrmann / sqlite-stored-procedure.sql
Created Oct 15, 2018
Poor man's Stored Procedures with SQLite
View sqlite-stored-procedure.sql
DROP VIEW IF EXISTS stored_procedure_do;
CREATE VIEW IF NOT EXISTS stored_procedure_do
AS SELECT NULL AS json_args LIMIT 1;
DROP TABLE IF EXISTS stored_procedure_result;
CREATE TABLE IF NOT EXISTS stored_procedure_result(
json_result
);
DROP TRIGGER IF EXISTS stored_procedure_impl;
@hoehrmann
hoehrmann / sql-json-style-sheets.sql
Created Oct 6, 2018
Complex table formatting of SQL Query results using JSON formatting objects
View sql-json-style-sheets.sql
-- styles for CPAN module Text::ANSITable
SELECT
time, priority, message
,
json_object(
'priority',
json_object(
'fgcolor',
CASE
You can’t perform that action at this time.