Who needs tools like Photoshop, GIMP, and preview when you can just browse and view your photos in MySQL? I mean, who wouldn’t want to just use MySQL for everything, right? Right? Anyone?
It certainly is not easy, and it requires using MySQL in ways that I have not seen anyone else really do, but this is indeed possible. Here, I’ll show you how. But, that’s what makes it so interesting, so Let’s begin! Since I don’t want to keep you in suspense all the way until the end, let me show you right from the beginning here what the end goal is.
Getting to this point is quite a process, so let’s walk through the steps.
Loading an image into MySQL
If you’ve been around SQL databases, you probably know that you should never store media like images or video in the database itself.
Rather, the image should be stored separately, and the database contains paths or URLs to where the images are located.
However, let’s say that you really needed to put that jpeg into mysql.
In that case, a reasonable person would probably use a BLOB type, as it is designed for large chunks of binary data.
However, for my needs, this does not suffice.
Instead, I’m going to create a dedicated database to store my images called image_library.
CREATE DATABASE image_library;
USE image_library;
Within this database, a new table can be created for each image I want to store.
For example, if I want to store the image beach.jpeg, I’d create the table beach_jpeg in this database.
Every pixel from the image will be stored in it’s own row, containing x and y coordinates as well as the r, g, and b data for that pixel.
CREATE TABLE IF NOT EXISTS beach_jpeg (
x INT, y INT,
r SMALLINT, g SMALLINT, b SMALLINT,
PRIMARY KEY(x, y) );
That means, if I want to store a 24 megapixel image, the table will have ~24 million rows to store that ONE image. I don’t recommend such a strategy for any real, practical use-case. However, this will not be the first time we use MySQL in an unintended way in this blog post.
It would be quite annoying to have to load images in manually in this way. So, I have a python script that will help load arbitrary images into the DB:
import sys
from PIL import Image
def main():
image_name = sys.argv[-1]
im = Image.open(image_name)
pix = im.load()
image_name = image_name.replace('.', '_')
print('CREATE DATABASE IF NOT EXISTS image_library;')
print('USE image_library;')
print('CREATE TABLE IF NOT EXISTS ' + image_name + '''(
x INT, y INT,
r SMALLINT, g smallint, b smallint,
PRIMARY KEY(x, y) );''')
width = im.size[0]
height = im.size[1]
for x in range(width):
for y in range(height):
p = im.getpixel((x, y))
print('INSERT INTO ' + image_name + ' (x,y,r,g,b) VALUES', end='')
print('(', x, ',', y, ',', p[0], ',', p[1], ',', p[2], ');')
main()
This allows one to load an image into MySQL like so:
$ python3 imageloader.py | mysql -u YOUR_USER -pYOUR_PASSWORD
Here’s a little sample of what data from one of these tables looks like: Selecting info from this table can be done like so:
mysql> SELECT * FROM beach_jpeg LIMIT 5;
+---+---+------+------+------+
| x | y | r | g | b |
+---+---+------+------+------+
| 0 | 0 | 46 | 77 | 95 |
| 0 | 1 | 46 | 77 | 95 |
| 0 | 2 | 46 | 77 | 95 |
| 0 | 3 | 46 | 77 | 95 |
| 0 | 4 | 46 | 77 | 95 |
+---+---+------+------+------+
5 rows in set (0.00 sec)
Colors in the MySQL shell
Ultimately, I want to display images from within the MySQL shell, not just store them.
No popping up additional GUI windows or using graphics libraries.
This all needs to work with vanilla MySQL shell functionality.
Since the MySQL shell operates in, well, a shell, I’m limited to using text to represent my images.
If you’ve been around a terminal long enough, you’ve probably seen colored text in some tools.
For example, you often see various text colors and background when running commands like npm install.
Colors in the terminal are accomplished with ANSI escape codes. ANSI escape codes can be used to produce a bunch of neat functionality in the terminal - chimes, cursor movement, erasing text, colored text, and more! To get images displaying, I specifically want to leverage ANSI escape codes for color.
Early on, there was a very small set of just 8 colors that text and / or the text background could be set to.
To get one of these colors displayed, the character sequence would need to have the ESC code (or \e as you may see it represented in strings), then followed by the character code for the foreground color, then a ;, then a character code for the background color, then an m, and then your text!
The numbers 30-37 are used to specify different foreground colors, and 40-47 the background color.
You can try out the possible foreground / background combos using this limited set of colors with this script:
for fg in 30 31 32 33 34 35 36 37 ;
do
for bg in 40 41 42 43 44 45 46 47 ;
do
printf "\e[${fg};${bg}mTEST\e[0m"
done
printf "\n"
done
Which produces:
Though this is a good start, I want to be able to represent a much richer spectrum of colors to accurately visualize images. Thankfully, ANSI did not stop at this basic color set. Eventually, as 256 color lookups tables became more common, ANSI added support for a wider gamut. Both the foreground and background could be set to any one of 256 colors. I’ll show an example, just updating the background colors.
for bg in $(seq 1 255);
do
printf "\e[48;5;${bg}mTEST\e[0m"
mod=$(($bg%15))
if (( mod == 0 )) ; then
printf "\n"
fi
done
But, even better, you can also specify precise 24 bit RGB color values for your text and background as well!
for r in $(seq 1 10 255);
do
for g in $(seq 1 10 255);
do
for b in $(seq 1 10 255);
do
printf "\e[48;2;${r};${g};${b}m#\e[0m"
done
printf "\n"
done
done
Very nice. This will do for viewing images.
ANSI escapes in MySQL
If you’re using the MySQL command line tool from within a shell that supports ANSI escape sequences for coloring text (such as zsh in my case), then we should be able to also see such colors in the output!
Also, if you’ve used tools like mycli, you know that this supports colors in the prompts, queries, and output.
If this is the case, one might think a way to get colors in the output of a MySQL command would be to use escape sequences, as shown in the last section.
Build the desired escape-sequenced string, put it in a CHAR or VARCHAR, then dump to the output.
This seems like a reasonable first idea, but there is a problem.
According to the MySQL docs, only a subset of escape sequences are supported.
Unfortunately, no mention of ESC:
Thankfully, there’s a workaround for this.
It’s a bit convoluted, but it works!
- (A) Build our strings using the hexadecimal representation of each underlying character.
You can use an ASCII table to determine the mappings (either use
$man asciior just Google for one). - (B) Use the MySQL
unhexprocedure to convert this to something that can be stored as a `VARBINARY. - (C) When we want do display it, cast the
VARBINARYto aCHAR, and it will interpret the escape sequence!
The big string 1B5B34383B323B3130303B3230303B3130306D201B5B306D is the hexadecimal representation of "\e[48;2;100;200;100m \e[0m".
I use UNHEX to make it binary, then CAST to convert to a CHAR, then REPEAT to display 50 of these green chars.
Displaying a picture, finally!
The next step is to write some SQL that can take one of the tables from the image_library database, examine all of the RGB values, and spit out a visual representation.
I want this to be easy to do right from within the mysql command line.
Due to this, I’m going to implement the necessary functionality as a MySQL stored procedure.
Once implemented and loaded in, it will be as easy as invoking it with a CALL with a few arguments, and then an image will appear!
To do this, I’m going to implement two procedures.
One will be display_image, which will be the one responsible for getting the image “printed” out.
This will have three parameters: The name of the table (image selection), and the width and height (in characters, not pixels) that the caller wants to view the image at.
Before I show you the code, please note: I’m not particularly good at writing actual code with SQL.
This could probably be re-written way cleaner and shorter.
If anyone wants to take up that challenge, by all means.
DELIMITER //
CREATE PROCEDURE display_image(
IN image_name VARCHAR(50),
IN w INT,
IN h INT)
BEGIN
DECLARE v1 INT DEFAULT 0;
DECLARE v2 INT DEFAULT 0;
DECLARE v3 INT DEFAULT 0;
DECLARE v4 INT DEFAULT 0;
DECLARE xv INT DEFAULT 0;
DECLARE yv INT DEFAULT 0;
DECLARE wdh INT DEFAULT 0;
DECLARE hdh INT DEFAULT 0;
DECLARE ravg INT DEFAULT 0;
DECLARE gavg INT DEFAULT 0;
DECLARE bavg INT DEFAULT 0;
SET @query = CONCAT('SELECT MAX(x) FROM ', image_name, ' INTO @pw;');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @query = CONCAT('SELECT MAX(y) FROM ', image_name, ' INTO @ph;');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET wdh = @pw / w;
SET hdh = @ph / h;
DROP TABLE IF EXISTS image_view;
CREATE TEMPORARY TABLE image_view (image VARBINARY(50000));
SET v1 = 0;
WHILE v1 < @ph DO
SET @full_row = '';
SET v2 = 0;
WHILE v2 < @pw DO
SET ravg = 0;
SET gavg = 0;
SET bavg = 0;
SET v3 = 0;
WHILE v3 < wdh DO
SET xv = v2 + v3;
SET v4 = 0;
WHILE v4 < hdh DO
SET yv = v1 + v4;
SET @query = CONCAT(
'SELECT r, g, b FROM ', image_name,
' WHERE x = ', xv, ' AND y = ', yv,
' INTO @rout, @gout, @bout');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET ravg = ravg + @rout;
SET gavg = gavg + @gout;
SET bavg = bavg + @bout;
SET v4 = v4 + 1;
END WHILE;
SET v3 = v3 + 1;
END WHILE;
SET ravg = ravg / (wdh*hdh);
SET gavg = gavg / (wdh*hdh);
SET bavg = bavg / (wdh*hdh);
SET @ret_value = '';
CALL getcolorstring(ravg, gavg, bavg, @ret_value);
SET @full_row = CONCAT(@full_row, @ret_value);
SET v2 = v2 + wdh;
END WHILE;
INSERT INTO image_view (image) VALUES (UNHEX(@full_row));
SET v1 = v1 + hdh;
END WHILE;
SELECT cast(image AS char) FROM image_view;
DROP TABLE IF EXISTS image_view;
END; //
DELIMITER ;
The high-level principle used here is a form of image binning. I actually have another blog post where I talk an algorithm I came up with for image binning. I’ll give a quick summary of what this function does here::
- Divide the full image into “chunks” based on the desired width and height to display. This is loosely handled by the two outer while loops.
- For each chunk:
- Do more looping to compute the average R, G, and B values of all pixels in the chunk
- Get the corresponding
VARBINARYsequence for this average RGB value (the helper function handles this part) - Print out a character with this average
If you take a careful look, you’ll notice there’s a helper procedure called get_color_string.
This takes in the computed average RGB and produces the corresponding hexadecimal string for displaying that character.
The code for that is here:
DELIMITER //
CREATE PROCEDURE get_color_string(
IN r INT,
IN g INT,
IN b INT,
OUT color_string VARCHAR(1000))
BEGIN
DECLARE s_start VARCHAR(1000) DEFAULT '1B5B34383B323B';
DECLARE s_end VARCHAR(1000) DEFAULT '6D201B5B306D';
SET @rd1 = CONCAT('3', CAST(FLOOR((r%10)/1) AS CHAR));
SET @rd2 = CONCAT('3', CAST(FLOOR((r%100)/10) AS CHAR));
SET @rd3 = CONCAT('3', CAST(FLOOR((r%1000)/100) AS CHAR));
SET @gd1 = CONCAT('3', CAST(FLOOR((g%10)/1) AS CHAR));
SET @gd2 = CONCAT('3', CAST(FLOOR((g%100)/10) AS CHAR));
SET @gd3 = CONCAT('3', CAST(FLOOR((g%1000)/100) AS CHAR));
SET @bd1 = CONCAT('3', CAST(FLOOR((b%10)/1) AS CHAR));
SET @bd2 = CONCAT('3', CAST(FLOOR((b%100)/10) AS CHAR));
SET @bd3 = CONCAT('3', CAST(FLOOR((b%1000)/100) AS CHAR));
SET @result = CONCAT(s_start, @rd3, @rd2, @rd1, '3B');
SET @result = CONCAT(@result, @gd3, @gd2, @gd1, '3B');
SET @result = CONCAT(@result, @bd3, @bd2, @bd1, s_end);
SET color_string = @result;
END; //
DELIMITER ;
Is it ugly? Yes. Does it work? Also yes. If you have this code in a file, you can load it into a MySQL session with
mysql> \. displayimage.sql
And, assuming you have images loaded in and ready to go, start displaying them! If I have this image loaded into my database:
Then I can display it in mysql with this function:
To get a higher-resolution view, just make the font size smaller and render the image with a bigger width and height!
Voilà, a picture in MySQL. Now, go uninstall preview, ok?