diff options
| -rw-r--r-- | README.md | 40 | ||||
| -rwxr-xr-x | image.sh | 13 | ||||
| -rw-r--r-- | justfile | 14 | ||||
| -rw-r--r-- | media/wavey.gif | bin | 0 -> 2523921 bytes | |||
| -rw-r--r-- | setup.sql | 20 | ||||
| -rw-r--r-- | src/wavey.sql | 33 | ||||
| -rwxr-xr-x | video.sh | 47 |
7 files changed, 144 insertions, 23 deletions
@@ -2,18 +2,7 @@ Generating some visuals with SQL because nobody stopped me. With the magic of [recursive CTE](https://sqlite.org/lang_with.html). -### Dependencies -- sqlite3 -- imagemagick - -### Setup -- Using justfile: `just setup` -- Directly: `sqlite3 fun.db < setup.sql` - -### Generate image -- Using justfile: `just image src/mandelbrot.sql` -- Directly: `sqlite3 fun.db < src/mandelbrot.sql && ./image.sh mandelbrot fun.db` - +[How it works](https://ediblemonad.dev/coding4fun/2026-06-03-creative-coding-in-sqlite.html) <table> <tr> @@ -36,5 +25,32 @@ Generating some visuals with SQL because nobody stopped me. With the magic of [r <img src="media/voronoi.png" /> </td> </tr> + <tr> + <td width="50%" valign="top"> + <h3><a href="./src/wavey.sql">Wavey</a></h3> + <img src="media/wavey.gif" /> + </td> + <td width="50%" valign="top"></td> + </tr> </table> +--- + +## Setup and run + +### Dependencies +- sqlite3 +- imagemagick + +### Setup +- Using justfile: `just setup` +- Directly: `sqlite3 fun.db < setup.sql` + +### Generate image +- Using justfile: `just image src/mandelbrot.sql` +- Directly: `sqlite3 fun.db < src/mandelbrot.sql && ./image.sh mandelbrot fun.db` + +### Generate video +- Using justfile: `just video src/wavey.sql` +- Directly: `sqlite3 fun.db < src/wavey.sql && ./video.sh wavey fun.db` + @@ -2,10 +2,14 @@ set -eu +[ $# -lt 1 ] && echo "Fuck" && exit 1; +IMAGE_ID="$1" +DB="${2:-fun.db}" + db() { sqlite3 -tabs -noheader "$DB" "$@"; } ppm() { - image_id="$1" + local image_id="$1" echo "P3" db "SELECT width, height FROM images WHERE id='$image_id'" echo "255" @@ -16,9 +20,4 @@ save_png() { magick ppm:- "media/$1.png"; } display() { magick display ppm:-; } - -[ $# -lt 1 ] && echo "Fuck" && exit 1; -image_id="$1" -DB="${2:-fun.db}" - -ppm "$image_id" | tee >(save_png "$image_id") | display +ppm "$IMAGE_ID" | tee >(save_png "$IMAGE_ID") | display @@ -9,12 +9,22 @@ image file *args: #!/usr/bin/env sh set -eu image_id=$(just run "{{file}}" {{args}}) - just show-image "$image_id" + just gen-image "$image_id" -show-image image_id: +gen-image image_id: echo "Displaying {{image_id}}" 1>&2 ./image.sh "{{image_id}}" +video file *args: + #!/usr/bin/env sh + set -eu + video_id=$(just run "{{file}}" {{args}}) + just gen-video "$video_id" + +gen-video video_id: + echo "Displaying {{video_id}}" 1>&2 + ./video.sh "{{video_id}}" + repl *args: rlwrap sqlite3 "{{DB}}" {{args}} diff --git a/media/wavey.gif b/media/wavey.gif Binary files differnew file mode 100644 index 0000000..d5015ca --- /dev/null +++ b/media/wavey.gif @@ -4,7 +4,17 @@ PRAGMA temp_store = MEMORY; CREATE TABLE images ( id TEXT PRIMARY KEY, width INTEGER NOT NULL, - height INTEGER NOT NULL + height INTEGER NOT NULL, + video_id TEXT, + frame INTEGER DEFAULT 0, + FOREIGN KEY(video_id) REFERENCES videos(id) ON DELETE CASCADE +); + +CREATE TABLE videos ( + id TEXT PRIMARY KEY, + width INTEGER NOT NULL, + height INTEGER NOT NULL, + fps INTEGER NOT NULL ); CREATE TABLE pixels ( @@ -15,7 +25,7 @@ CREATE TABLE pixels ( r INTEGER NOT NULL CHECK(r >= 0 AND r <= 255), g INTEGER NOT NULL CHECK(g >= 0 AND g <= 255), b INTEGER NOT NULL CHECK(b >= 0 AND b <= 255), - -- FOREIGN KEY(image_id) REFERENCES images(id) ON DELETE CASCADE, + FOREIGN KEY(image_id) REFERENCES images(id) ON DELETE CASCADE, UNIQUE(image_id, x, y) ); @@ -24,3 +34,9 @@ BEFORE INSERT ON images BEGIN DELETE FROM pixels WHERE image_id = NEW.id; END; + +CREATE TRIGGER delete_frames_when_video_reinserted_because_i_said_so_and_to_make_rerunning_easier +BEFORE INSERT ON videos +BEGIN + DELETE FROM images WHERE video_id = NEW.id; +END; diff --git a/src/wavey.sql b/src/wavey.sql new file mode 100644 index 0000000..70da417 --- /dev/null +++ b/src/wavey.sql @@ -0,0 +1,33 @@ +INSERT OR REPLACE INTO videos (id, width, height, fps) VALUES ('wavey', 400, 400, 10) RETURNING id; + +WITH RECURSIVE + video AS (SELECT * FROM videos WHERE id = 'wavey'), + _frames(frame) AS ( + SELECT 1 UNION ALL + SELECT frame + 1 FROM _frames, video WHERE frame < 50 + ) +INSERT INTO images (id, width, height, frame, video_id) SELECT CONCAT('wavey/', frame), width, height, frame, 'wavey' FROM _frames, video; + +WITH RECURSIVE + video AS (SELECT * FROM videos WHERE id = 'wavey'), + horizontal(x) AS (SELECT width FROM video UNION ALL SELECT x - 1 FROM horizontal WHERE x > 1), + vertical(y) AS (SELECT height FROM video UNION ALL SELECT y - 1 FROM vertical WHERE y > 1), + _frames(id, frame) AS (SELECT id, frame FROM images WHERE video_id = (SELECT id FROM video)), + _pixels(frame_id, x, y, r, g, b) AS + (SELECT + frames_id, + x, y, + ROUND(100 * value), + ROUND(240 * value*value), + ROUND(255 * value) + FROM (SELECT *, + (CASE + WHEN SIN((x - 4.0*frame) / 20.0)*10.0 > y - 150 THEN 1.0 + WHEN SIN((x - 0.5*frame) / 20.0)*10.0 > y - 200 THEN 0.75 + WHEN SIN((x + 8.0*frame) / 20.0)*10.0 > y - 280 THEN 0.5 + ELSE 0.3 + END) AS value, + _frames.id AS frames_id + FROM vertical, horizontal, _frames) + ) +INSERT INTO pixels (image_id, x, y, r, g, b) SELECT frame_id, x, y, r, g, b FROM _pixels; diff --git a/video.sh b/video.sh new file mode 100755 index 0000000..59c5d28 --- /dev/null +++ b/video.sh @@ -0,0 +1,47 @@ +#!/usr/bin/env sh + +set -eu + +[ $# -lt 1 ] && echo "Fuck" && exit 1; +VIDEO_ID="$1" +DB="${2:-fun.db}" + +db() { sqlite3 -tabs -noheader "$DB" "$@"; } + +frame_ppm() { + local image_id="$1" + echo "P3" + db "SELECT width, height FROM images WHERE id='$image_id'" + echo "255" + db "SELECT r,g,b FROM pixels WHERE image_id='$image_id' ORDER BY y ASC, x ASC" +} + +video_info() { + local video_id="$1" + db "SELECT fps FROM videos WHERE id='$video_id'" +} + +video_frames() { + local video_id="$1" + db "SELECT id FROM images WHERE video_id='$video_id' ORDER BY frame ASC" | while IFS= read image_id; do + frame_ppm "$image_id"; + done; +} + +save_video() { + local id="$1" + local fps="$2" + ffmpeg -y -f image2pipe -framerate "$fps" -i pipe:0 "media/$id.gif" +} + +display() { + local fps="$1"; + ffplay - -framerate "$fps" -autoexit; +} + +## Main stuff + +data="$(video_info "$VIDEO_ID")" +fps="$data" + +video_frames "$VIDEO_ID" | tee >(save_video "$VIDEO_ID" "$fps") | display "$fps" |
