ASCII-art Mandelbrot set

[jchd]

Example of powerful computation done in pure SQL[ite] showing smart use of a recursive CTE (Common Table Expression), taken from SQLite3 current documentation.

#include <SQLite.au3>
;~ #include <SQLite.dll.au3>    ; download the most recent suitable DLL once and comment this out

Local $hDB
Local $sSQL = "" & _
"WITH RECURSIVE " & _
"  xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.025 FROM xaxis WHERE x<1.2), " & _
"  yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.05 FROM yaxis WHERE y<1.0), " & _
"  m(iter, cx, cy, x, y) AS ( " & _
"    SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis " & _
"    UNION ALL " & _
"    SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m " & _
"     WHERE (x*x + y*y) < 4.0 AND iter<40 " & _
"  ), " & _
"  m2(iter, cx, cy) AS ( " & _
"    SELECT max(iter), cx, cy FROM m GROUP BY cx, cy " & _
"  ), " & _
"  a(t) AS ( " & _
"    SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') " & _
"    FROM m2 GROUP BY cy " & _
"  ) " & _
"SELECT group_concat(rtrim(t),x'0d0a') FROM a;"

_SQLite_Startup()
$hDB = _SQLite_Open()

Local $aRows, $iRows, $iCols
Local $iTime = TimerInit()
_SQLite_GetTable($hDB, $sSQL, $aRows,$iRows, $iCols)
ConsoleWrite("SQL drawing ASCII-art Mandelbrot set (" & Round(TimerDiff($iTime) / 1000, 3) & " s)" & @LF & $aRows[2] & @LF)

[mLipok]

I was trying to use it.

 

--> Press Ctrl+F11 to Restart    or    Ctrl+Break -or- F11 to Stop

!   SQLite.au3 Error

--> Function: _SQLite_Query

--> Query:    WITH RECURSIVE   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.025 FROM xaxis WHERE x<1.2),   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.05 FROM yaxis WHERE y<1.0),   m(iter, cx, cy, x, y) AS (     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis     UNION ALL     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m      WHERE (x*x + y*y) < 4.0 AND iter<40   ),   m2(iter, cx, cy) AS (     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy   ),   a(t) AS (     SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')     FROM m2 GROUP BY cy   ) SELECT group_concat(rtrim(t),x'0d0a') FROM a;

--> Error:    near "WITH": syntax error

 

"Z:TOOLsMacroPROGRAMY__backup_install_!!!!SQL_BACKUPtest.au3" (30) : ==> Subscript used on non-accessible variable.:

ConsoleWrite("SQL drawing ASCII-art Mandelbrot set (" & Round(TimerDiff($iTime) / 1000, 3) & " s)" & @LF & $aRows[2] & @LF)

ConsoleWrite("SQL drawing ASCII-art Mandelbrot set (" & Round(TimerDiff($iTime) / 1000, 3) & " s)" & @LF & $aRows^ ERROR

->11:11:49 AutoIt3.exe ended.rc:1

+>11:11:49 AutoIt3Wrapper Finished.

>Exit code: 1    Time: 0.6206

[jchd]

You need a more recent sqlite3[_x64].dll because support for CTEs is relatively recent (well, around early 2014) and started with version 3.8.3 dated 2014-02-03. More recent releases are bug fixed and greatly enhanced.

[mLipok]

Thanks for info.

I will try again when back to home.

mLipok

[jcpetu]

Hi jchd, is it possible to use DBvisualizer to graph the results? if so, have you got any example.

thanks a lot for sharing.

regards.

[jchd]

You should be able to see the resulting text cell with this tool. Why not?

[jcpetu]

Have you got any example to use it. I nknew about the tool by chance but I don't know how to use it from Autoit.

[jchd]

I never heard of this product so I can't say what it offers. Anyway you can feed the SQL into virtually any SQL engine, possibly with some syntactic change required. Doesn't the sample code work for you?

[JohnOne]

Had to download this 32 bit dll from here for it to work.

Looks good.

[Lakes]

I'll settle for a screenshot (please)

[jchd]

Yes as I stated in #3.

The goal of this post (along with the SQL sudoku solver) was to demonstrate the power of recursive CTE constructs in SQL[ite], which can reveal very efficient ways to perform tasks which would be much harder to code or much less efficient using AutoIt itself. Given that SQLite is so easy to use it can give people some ideas.

Here's the console output:

expandcollapse popup

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\jc\Documents\AutoMAT\tmp\StringManip.au3" /UserParams    
+>09:15:13 Starting AutoIt3Wrapper v.14.727.1229.0 SciTE v.3.5.0.0   Keyboard:0000040C  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64    Environment(Language:040C)
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\jc\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\jc\AppData\Local\AutoIt v3\SciTE 
>Running:(3.3.12.0):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\Users\jc\Documents\AutoMAT\tmp\StringManip.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
SQL drawing ASCII-art Mandelbrot set (0.655 s)
                                                                        .......+#
                                                                      ......*....
                                                                     ....*##*+....
                                                                   ...#++**##++.++.
                                                                  ....+#########+...
                                                               ......+*##########.....
                                                       ....*..........+########*........     .+.
                                                      ...##+....##++#*##########*###.*#.......+..
                                                     ....+####+#*#####################++..+#+++++.
                                                     ....################################+####*..
                                                   ...+..####################################*+..
                                                ....*###*####################################+....
                            ..........................+#######################################+...+.
                            ..+........+...........##############################################++.
                           .....+*++...+#+.+.......+*##########################################*+..
                          ......+###*#######**.....#############################################+..
                         ......++##############*+++##############################################*.
                      ..+.....+##################+*##############################################.
                   ......+.*++*################################################################*.
        ......  .......+++####################################################################...
 ##########################################################################################+.....
        ......  .......+++####################################################################...
                   ......+.*++*################################################################*.
                      ..+.....+##################+*##############################################.
                         ......++##############*+++##############################################*.
                          ......+###*#######**.....#############################################+..
                           .....+*++...+#+.+.......+*##########################################*+..
                            ..+........+...........##############################################++.
                            ..........................+#######################################+...+.
                                                ....*###*####################################+....
                                                   ...+..####################################*+..
                                                     ....################################+####*..
                                                     ....+####+#*#####################++..+#+++++.
                                                      ...##+....##++#*##########*###.*#.......+..
                                                       ....*..........+########*........     .+.
                                                               ......+*##########.....
                                                                  ....+#########+...
                                                                   ...#++**##++.++.
                                                                     ....*##*+....
                                                                      ......*....
                                                                        .......+#

+>09:15:14 AutoIt3.exe ended.rc:0
+>09:15:14 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 1.235

[jcpetu]

jchd,

the sample code works fine, thanks a lot and regards.