Skip to content

Instantly share code, notes, and snippets.

@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
View sqlite-3.25.1-window-function-in-view-bug.txt
Hi,
Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that
Window functions in VIEWS behave differently from PostgreSQL 9.6 and
from what I expect.
DROP TABLE IF EXISTS example;
CREATE TABLE example(t INT, total INT);
INSERT INTO example VALUES(0,2);
INSERT INTO example VALUES(5,1);
@hoehrmann
hoehrmann / view_every_hour_past_six_months.sql
Last active Jul 28, 2018
view_every_hour_past_six_months.sql
View view_every_hour_past_six_months.sql
DROP VIEW IF EXISTS view_every_hour_past_six_months;
CREATE VIEW view_every_hour_past_six_months AS
WITH RECURSIVE
bounds AS (
SELECT
strftime('%s', 'now', 'start of day', '-6 month') AS lower,
strftime('%s', 'now', 'start of day', '+1 day') AS upper
),
samples AS (
SELECT lower AS sample FROM bounds
View sqlite-cte-random-path.txt
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> EXPLAIN QUERY PLAN
...> WITH RECURSIVE path(pos, vertex) AS (
...> SELECT 0, ?
...> UNION ALL
...> SELECT path.pos + 1, (SELECT Edge.dst
...> FROM Edge
...> WHERE Edge.src = path.vertex
...> ORDER BY RANDOM()
@hoehrmann
hoehrmann / binops-all.txt
Created Jan 10, 2018
Binary boolean operation truth tables, various versions
View binops-all.txt
0 !| < > & A B ==
+---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+
| 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 |
+---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+
| 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 |
+---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+
+---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+
| 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 | | 0 | 0 |
+---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+ +---+---+
@hoehrmann
hoehrmann / Xirip.pas
Created Jun 5, 2013
XiRip is a 1997 Turbo Pascal program that takes .xm music files and extracts the embedded instruments.
View Xirip.pas
uses StrngTol;
type
parameter=record
FileName:String;
ExtractPath:String;
ShowHelpScreen:Boolean; {/?}
KeepInstName:boolean; {/K}
KeepSampName:boolean; {/S}
QuitOutput:Boolean; {/Q}
end;
View lab_color_distance_mask.sh
#!/bin/bash
#####################################################################
# Copyright (c) 2016 Bjoern Hoehrmann <bjoern@hoehrmann.de>. GPLv3+.
#
# Given image + color, creates mask encoding pixel's LAB color diff.
#####################################################################
path_in="$1"
color_rgb="$2"
path_out="$3"
@hoehrmann
hoehrmann / scriptrec.sh
Created Apr 26, 2016
Automatically record bash sessions
View scriptrec.sh
#####################################################################
#
# This script starts an automatically recorded session using `script`
# storing logs in `$log_dir`. Care should be taken to secure records
# generated by this script as they may easily contain passwords and
# other sensistive information.
#
#####################################################################
# FIXME(bh): make this configurable
View CopyConsoleScreen.c
#include <stdio.h>
#include <stdlib.h>
#include <Windows.h>
int
main(int argc, char *argv[]) {
char* buffer;
BOOL bStatus;
COORD coords;
HANDLE hConsole;
You can’t perform that action at this time.