Refactor image.sh to handle png + cleanup sql

9 files changed, 43 insertions, 23 deletions

diff --git a/README.md b/README.md
index ce3a02e..9bafdf2 100644
--- a/README.md
+++ b/README.md
@@ -2,6 +2,19 @@
 
 Generating some visuals with SQL because nobody stopped me
 
+### 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`
+
+
 <table>
   <tr>
     <td width="50%" valign="top">
diff --git a/image.sh b/image.sh
index 6a82ee6..69da148 100755
--- a/image.sh
+++ b/image.sh
@@ -1,13 +1,24 @@
 #!/usr/bin/env sh
 
-DB=fun.db
+set -eu
 
-[ $# -lt 1 ] && echo "Fuck" && exit 1;
+db() { sqlite3 -tabs -noheader "$DB" "$@"; }
+
+ppm() {
+  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"
+}
+
+save_png() { magick ppm:- "media/$1.png"; }
 
-db() { sqlite3 -list -noheader "$DB" "$@"; }
+display() { magick display ppm:-; }
 
+
+[ $# -lt 1 ] && echo "Fuck" && exit 1;
 image_id="$1"
-echo "P3"
-db "SELECT width, height FROM images WHERE id='$image_id'" | awk -F'|' '{ print $1 " " $2 }'
-echo "255"
-db "SELECT r,g,b FROM pixels WHERE image_id='$image_id' ORDER BY y ASC, x ASC" | tr '|' ' '
+DB="${2:-fun.db}"
+
+ppm "$image_id" | tee >(save_png "$image_id") | display
diff --git a/justfile b/justfile
index 763a6a1..9eefddf 100644
--- a/justfile
+++ b/justfile
@@ -13,7 +13,7 @@ image file *args:
 
 show-image image_id:
   echo "Displaying {{image_id}}" 1>&2
-  ./image.sh "{{image_id}}" | tee >(magick ppm:- "media/{{image_id}}.png") | magick display ppm:-
+  ./image.sh "{{image_id}}"
 
 repl *args:
   rlwrap sqlite3 "{{DB}}" {{args}}
diff --git a/media/circle.png b/media/circle.png
index ca7e334..fbb5ac0 100644
--- a/media/circle.png
+++ b/media/circle.png
diff --git a/media/gradient.png b/media/gradient.png
index e688715..34e5996 100644
--- a/media/gradient.png
+++ b/media/gradient.png
diff --git a/setup.sql b/setup.sql
index 7b6732e..3a80a8c 100644
--- a/setup.sql
+++ b/setup.sql
@@ -8,20 +8,19 @@ CREATE TABLE images (
 );
 
 CREATE TABLE pixels (
-  id INTEGER PRIMARY KEY AUTOINCREMENT,
+  id INTEGER PRIMARY KEY,
   image_id TEXT NOT NULL,
   x INTEGER NOT NULL,
   y INTEGER NOT NULL,
   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),
+  -- FOREIGN KEY(image_id) REFERENCES images(id) ON DELETE CASCADE,
   UNIQUE(image_id, x, y)
 );
 
-CREATE TRIGGER delete_pixels_when_image_deleted
-AFTER DELETE ON images
-WHEN (SELECT COUNT(*) FROM images WHERE id = OLD.id) = 0
+CREATE TRIGGER delete_pixels_when_image_reinserted_because_i_said_so_and_to_make_rerunning_easier
+BEFORE INSERT ON images
 BEGIN
-  DELETE FROM pixels WHERE image_id = OLD.id;
+  DELETE FROM pixels WHERE image_id = NEW.id;
 END;
diff --git a/src/circle.sql b/src/circle.sql
index 46874cc..c4ac18a 100644
--- a/src/circle.sql
+++ b/src/circle.sql
@@ -1,9 +1,8 @@
-DELETE FROM images WHERE id = 'circle';
-INSERT INTO images (id, width, height) VALUES ('circle', 200, 200) RETURNING id;
+INSERT OR REPLACE INTO images (id, width, height) VALUES ('circle', 400, 400) RETURNING id;
 
 WITH RECURSIVE
   image AS (SELECT * FROM images WHERE id = 'circle'),
-  circle AS (SELECT width/2 AS cx, height/2 AS cy, 80 AS radius FROM image),
+  circle AS (SELECT width/2 AS cx, height/2 AS cy, 160 AS radius FROM image),
   horizontal(x) AS
     (SELECT width FROM image UNION ALL SELECT x - 1 FROM horizontal WHERE x > 1),
   vertical(y) AS
@@ -11,7 +10,7 @@ WITH RECURSIVE
   _pixels(x, y, r, g, b) AS (SELECT
     x, y,
     150,
-    255 * MAX(0, MIN(1, (SELECT POW(x - cx, 2) + POW(y - cy, 2) - POW(radius, 2) FROM circle))),
+    255 * MAX(0, MIN(1, (SELECT (x - cx)*(x - cx) + (y - cy)*(y - cy) - radius*radius FROM circle))),
     150
     FROM vertical, horizontal
   )
diff --git a/src/gradient.sql b/src/gradient.sql
index f04cdad..4ad1820 100644
--- a/src/gradient.sql
+++ b/src/gradient.sql
@@ -1,5 +1,4 @@
-DELETE FROM images WHERE id = 'gradient';
-INSERT INTO images (id, width, height) VALUES ('gradient', 200, 200) RETURNING id;
+INSERT OR REPLACE INTO images (id, width, height) VALUES ('gradient', 400, 400) RETURNING id;
 
 WITH RECURSIVE
   image AS (SELECT * FROM images WHERE id = 'gradient'),
@@ -8,5 +7,5 @@ WITH RECURSIVE
   vertical(y) AS
     (SELECT height FROM image UNION ALL SELECT y - 1 FROM vertical WHERE y > 1),
   _pixels(x, y, r, g, b) AS
-    (SELECT x, y, mod(x, 255), mod(y, 255), 100 FROM vertical, horizontal)
+    (SELECT x, y, x*255/width, y*255/height, 100 FROM vertical, horizontal, image)
 INSERT INTO pixels (image_id, x, y, r, g, b) SELECT 'gradient', x, y, r, g, b FROM _pixels;
diff --git a/src/mandelbrot.sql b/src/mandelbrot.sql
index 1c25461..b83b751 100644
--- a/src/mandelbrot.sql
+++ b/src/mandelbrot.sql
@@ -1,5 +1,4 @@
-DELETE FROM images WHERE id = 'mandelbrot';
-INSERT INTO images (id, width, height) VALUES ('mandelbrot', 400, 400) RETURNING id;
+INSERT OR REPLACE INTO images (id, width, height) VALUES ('mandelbrot', 400, 400) RETURNING id;
 
 WITH RECURSIVE
   image AS (SELECT *, 0.008 AS scale, (width * 5)/7 AS ox, (height / 2) AS oy FROM images WHERE id = 'mandelbrot'),