jchd

ASCII-art Mandelbrot set

12 posts in this topic

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)

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites



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

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Thanks for info.

I will try again when back to home.

mLipok


Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2017-06-04

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

I'll settle for a screenshot (please) :)


2015 - Still no flying cars, instead blankets with sleeves.

Share this post


Link to post
Share on other sites

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:

>"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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

jchd,

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now