SQLite introduction

[footswitch]

Hello everyone!

Let me just say it's been a while. And I'm glad I'm back

I've seen that AutoIt has grown in functionality, and by this I'm looking at the integration of SQLite! Nicely done

And this makes me wonder...

From time to time, I need to make some reports, and this is my current process:

1. Obtain XML files to work with (I have no control over the generation of these files);

2. Convert them to csv with an AutoIt script that I wrote a while ago;

3. Open csv files in Excel;

4. Create Pivot Tables and go from there.

Before asking the actual question, let me tell you I'm working with tens of thousand rows (up to 40,000) and between 12 to 52 columns.

What do YOU, experienced users think that makes more sense:

a) Develop automation in Excel (Open .csv files, create Pivot Tables, perform copy and sort operations etc., until I get the data I need)

Process the data within AutoIt and SQLite and then output the Query results to Excel?

Either way, I'm very unfamiliar with the implementation of *both* solutions (definitely less familiar with SQLite. I know its potencial, but I know nothing about Queries).

So it's kind of the same for me (in terms of my learning needs), but SQLite does get my attention.

One thing is AutoIt's functions: they're mostly straightforward and well explained in the Help file. In fact I believe anyone can learn to do some neat things right from the Help file.

But how about the SQLite UDFs? How would one learn how to use them? How to build Queries, input and output data from and to files, etc.?

I'm a very tutorial-based learner, so site and book suggestions would be most welcome. I'm not afraid of some studying

But please ignore my "curiosity" towards SQLite. How would you do it?

Cheers,

footswitch

[jchd]

Google first for a good SQL tutorial / exercises site. Then practice with SQLite using a third-party SQLite DB manager, like SQLite Expert or the SQLite extension for firefox.

Then try to simulate a small scale problem typical of your applicaion with those tools. See you you can make everything work the way you want.

Only then try to write the skeleton of your application with AutoIt (use simple _ArrayDisplays, MsgBoxes and ConsoleWrites, leaving GUI and finesses alone). Check results with the external tools, correct, retry.

Finaly put decent clothes around that bare-bones program you have. But at this stage, you know it's fully functional so you can concentrate on cosmetic issues.

If you start the other way round, you're likely to spend 3 times more time.

[footswitch]

Google first for a good SQL tutorial / exercises site. Then practice with SQLite using a third-party SQLite DB manager, like SQLite Expert or the SQLite extension for firefox.

Then try to simulate a small scale problem typical of your applicaion with those tools. See you you can make everything work the way you want.

Only then try to write the skeleton of your application with AutoIt (use simple _ArrayDisplays, MsgBoxes and ConsoleWrites, leaving GUI and finesses alone). Check results with the external tools, correct, retry.

Finaly put decent clothes around that bare-bones program you have. But at this stage, you know it's fully functional so you can concentrate on cosmetic issues.

If you start the other way round, you're likely to spend 3 times more time.

Hello again,

Thank you for your reply.

I totally agree on you: Functionality first, cosmetics later.

So I started digging around, and the first thing I wanted to do was import the csv file into a table, which I believe is a bad idea, because I'm already extracting the information from an XML file, so I might as well insert it directly into the database.

BUT, I didn't think of it right away. This means I experimented a bit with the "dot commands" (.separator and .import).

Do I need sqlite3.exe when I want/need to use dot commands?

This is, would I have an additional 520KB footprint if I'd use a dot command?

I also came across something else: The example code for _SQLite_SQLiteExe() isn't working. It's returning @error=1, which means "can't create new database", but then just to make sure it wasn't me, I renamed the sqlite3.exe file and the error is still the same.

So there might be a bug with the @error codes for this function.

Inside the SQLite.au3 include file:

Line 164:

Global $g_hDll_SQLite = 0

Line 1024 (first line of function _SQLite_SQLiteExe()):

If $g_hDll_SQLite = 0 Then Return SetError(1, 0, $SQLITE_MISUSE)

Which means I have to call _SQLite_Startup() BEFORE using _SQLite_SQLiteExe(). (right?)

This isn't specified in the Help file AND is what's currently missing in the Example file.

Anyway I'm not following why it should be used for this particular function, _SQLite_SQLiteExe(). Isn't this function completely independent from the DLL?

Also, since this topic is called "SQLite introduction", wouldn't it be relevant to point out that the official SQLite Help file is installed together with AutoIt? For instance under the root of the SQLite Management folder in AutoIt's Help file.

The SQLite's Help file is located in AutoIt's install folder, under Extras\SQLite\SQLite Syntax.chm

Cheers,

footswitch

EDIT: mistyping

Edited July 16, 2010 by footswitch

[jchd]

I've been a contributor to the SQLite UDF but to be honest I never took the time to try using the CLI (command-line tool AKA sqlite3.exe) thru the AutoIt UDF. To be even more honest, I don't use the CLI at all. I find it's a good thing that it exists (and is cross-platform) but it's way inferior to a decent hird-party DB management tool.

You can safely delete line 1024 in your copy, it's not needed. Alternatively you may want to use the latest version of he UDF (which should appear with the next release or beta). I attach it here for your convenience as it has bug fixes and improvements. SQLite_New.au3

Be warned that this particular version will attempt to _download_ the required x86 or x64 dll if it's not available.

Anyway you would save headaches and much time by using a SQLite manager, at least for the design stage. The firefox etension should be able to import xml readily, possibly csv as well. Expert works fine with many csv but I'm unsure if the option is available in the free version. Dot commands are definitely specific to the CLI and using them means launchig a shell with the CLI and working from there (slow and clumsy). That your import issue be for initial population time or repetitive, I'd consider a third-party tool or homebrew code (respectively) instead as an alternative.

You may also find it useful to learn that several SQLite extensions are available to deal directly with csv files as external tables (using SQLite virtual table mecanism). All of them should be available open-source hence easy to adapt to specific need eventually (csv is highly non-standard or, putting it otherwise, there are just too many amusing, attractive and incompatible standards to choose from).

[footswitch]

Thank you for posting the latest #include file.

I guess I'll stick to the "stable" release for now

Fortunately there are various ways to avoid the CLI.

Also, XML / CSV importing won't be an issue. I'll just extract what I need with my previous AutoIt scripts and replace some lines to integrate directly with SQLite. This will avoid any file-format compatibility issues.

I'm however curious about those SQLite extensions. I'll have to take a look at those.

The firefox extension seems nice! I'll use it as soon as my first data import is successful.

I hope it'll help me build some Queries.

It won't be long before those Queries keep me awake

Thanks again.

footswitch

[jchd]

FYI, the version posted is more "stable" than the latest released version...

Try Expert as well, you'll love it!

[RebolTutorial]

FYI, the version posted is more "stable" than the latest released version...

Try Expert as well, you'll love it!

You can also use Rebol from the inventor of Amgi OS (it's free http://www.rebol.com/download-view.html) with or even without these libraries:

http://www.ross-gill.com/page/XML_and_REBOL

http://www.dobeash.com/rebdb.html a text pseudo-relation database which can cope with MILLIONS of rows

or if you still want sqlite use from the same author

http://www.dobeash.com/sqlite.html

[footswitch]

You can also use Rebol from the inventor of Amgi OS (it's free http://www.rebol.com/download-view.html) with or even without these libraries:

http://www.ross-gill.com/page/XML_and_REBOL

http://www.dobeash.com/rebdb.html a text pseudo-relation database which can cope with MILLIONS of rows

or if you still want sqlite use from the same author

http://www.dobeash.com/sqlite.html

Wow, now that's a leap.

Thanks for the reference. It just looks like too much of a learning curve for something that's already half the way there with AutoIt.

Regarding your alternative, the main thing that I'm worried about is that it would need Rebol present in the system and, if not installed, it would need some steps to execute. Am I right or is there a simple way of compiling .r files into .exe files?

But I have to admit: I'm curious about the XML extraction time with other languages.

[footswitch]

FYI, the version posted is more "stable" than the latest released version...

I was expecting that argument

Try Expert as well, you'll love it!

You mean sqlite expert dot com?

I'll have a look into that

Anyways, I've been visiting http://www.w3schools.com/sql, which seems good for the basics.

This is my current Query:

SELECT *,SUM(Quantity) AS "Qty_Total" FROM tblPicking GROUP BY Item_Code,Zone

This gives me total quantities grouped by item code and zone.

But, what I need is to turn DISTINCT Zones into columns (each XML file contains several zones and I can't guess what their name will be) and, for each Item Code, display total quantity in each zone.

I believe this is called Transposing or Pivoting.

Is that doable solely with Queries?

EDIT: Sorry but I can't seem to mantain the Table look

Original table:

Item_Code   Item_Desc                   Quantity    Item_Cost Zone  
1234567     description for 1234567     3           10.12        L
2345678     description for 2345678     1               9.46         L
3456789     description for 3456789     1            7.34        S
2345678     description for 2345678     2            9.46        A
2345678     description for 2345678     1           9.46         A

Intended result:

Item_Code        Item_Desc   Zone A Zone L  Zone S
1234567          description for 1234567            0        1       0
2345678          description for 2345678         3       1       0
3456789          description for 3456789        0        0       1

Also, beginner's question: when I want to perform two Queries (one over another), I have to create a VIEW, right?

Thanks

footswitch

Edited July 18, 2010 by footswitch

[jchd]

That should do what you want:

SELECT *,SUM(Quantity) AS "Qty_Total" FROM tblPicking GROUP BY Zone, Item_Code order by zone, item_code;

EDIT: sorry I misread your intention.

This kind of presentation (using an unbounded number of columns) is essentially anti-SQL as possible. Databases work the other way round!

Beware of NULLs with SUM, or use TOTAL (or coalesce nulls)

Embedded queries is what you need:

Select a, b, c from (select a, b, c from T2 where ...) where ...;

A view is just a permanently available SELECT... with a name (but SQLite allows you to make views editable by using INSTEAD OF triggers).

Edited July 18, 2010 by jchd

[footswitch]

That should do what you want:

SELECT *,SUM(Quantity) AS "Qty_Total" FROM tblPicking GROUP BY Zone, Item_Code order by zone, item_code;

EDIT: sorry I misread your intention.

This kind of presentation (using an unbounded number of columns) is essentially anti-SQL as possible. Databases work the other way round!

Beware of NULLs with SUM, or use TOTAL (or coalesce nulls)

Embedded queries is what you need:

Select a, b, c from (select a, b, c from T2 where ...) where ...;

A view is just a permanently available SELECT... with a name (but SQLite allows you to make views editable by using INSTEAD OF triggers).

I'm sorry, you got me confused: I don't know if you're talking about using embedded Queries for this particular problem or just as an answer to my last question.

So I'm still unsure if SQL itself can handle the case.

I'll just keep searching until I have a viable solution.

At this stage, I really intend to avoid Excel completely, so I'll have to figure out a way of Pivoting the Zones.

[jchd]

Fair enough, my phrasing was ambiguous.

I was answering your other question.

Regarding your pivotal issue, you want the transpose of the output produced by the stroked select in the quote above. This is a no-no is SQL, as column and rows don't play symetrical roles in SQL. No database engine allows for an unbounded number of columns and none disallows unbounded number of rows.

If you want only an output you nonetheless have an easy solution: _SQLite_GetTable2d accepts a parameter ($fSwichDimensions) to request delivery of a transposed result at little additionnal cost, if any.

If you don't need more then that is by far the simplest way.

[footswitch]

Fair enough, my phrasing was ambiguous.

I was answering your other question.

Regarding your pivotal issue, you want the transpose of the output produced by the stroked select in the quote above. This is a no-no is SQL, as column and rows don't play symetrical roles in SQL. No database engine allows for an unbounded number of columns and none disallows unbounded number of rows.

If you want only an output you nonetheless have an easy solution: _SQLite_GetTable2d accepts a parameter ($fSwichDimensions) to request delivery of a transposed result at little additionnal cost, if any.

If you don't need more then that is by far the simplest way.

Well.. seems interesting, but that would transpose the whole table, right?

I'm just guessing, but I believe the purpose would be achievable this way:

(something like this...)

; (...)
Local $aResult,$iRows,$iColumns
_SQLite_GetTable(-1, "SELECT DISTINCT Zone FROM tblPicking",$aResult,$iRows,$iColumns) ; this returns only one column with the several zones (half a dozen)
For $i=$iColumns+1 To $iRows Step $iColumns; i believe this would be accurate but I haven't given it much thought
    _SQLite_Exec(-1,"SELECT Item_Code,SUM(Quantity) AS ""Zone "&$aResult[$i]&""" FROM tblPicking WHERE ""Zone""="""&$aResult[$i]&""" GROUP BY Item_Code")
    ; each query is generating a column with the sum of quantity in the designated zone.
    ; not all item codes will appear, because not all items exist in every zone.
    ; this is where I should include all the Queries in just one, but don't know how... (SUM of Quantity WHERE Zone=A, SUM of Quantity WHERE Zone=L.....)
    ; or perhaps I'd do the Queries separately and somehow join them in the end.
Next

Am I making any sense at all?

[footswitch]

This is it:

(start SQLite Query)

SELECT Item_Code,

TOTAL(CASE WHEN "Zone"="A" THEN "Quantity" END) AS "Zone A",

TOTAL(CASE WHEN "Zone"="L" THEN "Quantity" END) AS "Zone L",

(...) --> Using the script from my previous post it'll be easy to build a dynamic Query sentence with all the possible zones

FROM tblPicagens

GROUP BY Item_Code

ORDER BY Item_Code;

(end SQLite Query)

[jchd]

Yep, but it won't work in the general case, where "zones" are not known a priori and may easily exceed maximum column number. Suppose you're not dealing with zones (whatever that means in your context) but worldwide cities or even villages names...

For building programmatically the above query, you may want to use groupconcat() to enumerate zones, but the thing will rapidly turn a bit ugly.

Best of luck for your project.

[footswitch]

Yep, but it won't work in the general case, where "zones" are not known a priori and may easily exceed maximum column number. Suppose you're not dealing with zones (whatever that means in your context) but worldwide cities or even villages names...

For building programmatically the above query, you may want to use groupconcat() to enumerate zones, but the thing will rapidly turn a bit ugly.

Best of luck for your project.

Don't worry: the Zone naming is unpredictable, yes, but it also won't exceed 6 or 7 distinct values.

Since each of these XML files is a different task, and the script is also prepared to stop the user if there is something "wrong" with the XML file, there's nothing to be afraid of.

Thank you, jchd.