SQLite semi Embedded database functionality in AutoIt

[ptrex]

Oeps I overlooked it !!

Sorry, it was attachted.

[blink314]

Ok, I've got the bugs I listed above worked out (I think!). Now on to some new features. I'll put up the next version in a few hours.

NEVERMIND that about the tabs... I was being stupid!

Kevin

Edited November 9, 2005 by blink314

[ptrex]

@blink314

I looked at your example "database" and it looks nice.

The problem you might have with the TABS is that you need to use the GUICtrlSetStyle () GUICtrlSetResizing ($Listview,$GUI_DOCKAUTO) (see help file)

Also see my autosignature and click on "Firewall Log Analyzer for XP". In this application I have used some TABS and control on the tabs in the script there is something mentioned like $GUI_DOCKAUTO.

This will solve you TABS problem (when maximizing the controls don't follow the TABS)

Hope this helps.

Edited November 9, 2005 by ptrex

[blink314]

Yeah got the tabs solved... turns out I had a stray GuiCtrlCreateTabItem("") in there. Well that's fixed! Thanks for the help though,

Kevin

[blink314]

Ok, here is the latest. Mostly it's just bug fixes and GUI additions. The GUI is almost finished for the tab that opens up initially. THis should give an idea of the functions I am going to try to implement. Yes, I know remove field is not implemented in SQLite but SQLite browser does it... so I'll try!

Most of the buttons dont do anything... I'll eventually disable those I havent written code for.

The SQL entry tab will be for testing SQL queries. THis will be sort of a learning tool/database tool. Enjoy,

Kevin

Found bug - Crashes sometimes when creating table... table is entered into database (it shows up when reopening) but during the refresh I get an object error. Fixing!

Bug Update - found that the problem is that "sometimes" after creating a table the program runs through the Database Open function, then goes to the TableItem click function... here it crashes. Weird, because I don't call the tableitem click function from the open function. The only thing I can think is that the event for the TreeItem is somehow being tripped. I've tried resetting focus at all different points and have reduced the occurence of the crash but not eliminated it.

Bug Update 2 - Ok... found the problem. Apparently there is something causing a circular call to the TreeClick event. I've corrected the problem so it will not crash the script (I think) but it still runs in the loop for a few iterations. I'll try to post a little more detail when I upload the next version in a few minutes.

Database.au3

Edited November 10, 2005 by blink314

[blink314]

Ok, before I get ready for bed... a few words.

Basically this program is designed to help me get acquainted with SQL. It is also designed to allow me to use databases in my everyday life. At work (and at home) I have numerous times when I wish I could make a quick database to store info in. Problem is databases seem big and complex. I like GUIs to let me see what I'm doing. This program allows me to see what is actually happening inside the database, and hopefully, will allow me to create small relational databases for my own needs. To that end, an explanation of what each tab is for:

- Database tab: This is the database "frontend." It will be the last part coded because I want this to be a functional view of a relational database. The user should be able to pick information in a visual way and have it displayed. Right now, I'm thinking I will have a plugin system for different types of databases.

- Raw View: This lets me get my head wrapped around the database, showing all it's internals and the data in each piece. It will let me generate tables quickly and put some testing data in it. It would also allow me to look into an unknown database and see how it works.

- SQL Entry: This tab will allow me to test my SQL skills. It will have a box to enter SQL in and a listview to show what data my query would give.

I see these three pieces working together. Hopefully I will be able to get everything working... I may get bored, who knows! However, this type of thing is something I have wanted for a long time and it has many applications for my work.

Please report any bugs, misuses of SQL or obtuse GUI layout. Any other ideas for the project may be considered, but I admit I am writing this primarily for myself and my needs. Feel free to modify as you see fit!

Concerning the bug I mentioned in my last post. If anyone can figure out why the TreeViewItem event is being triggered when I delete all TreeView data and rebuild it that would be much appreciated. The function where I rebuild the tree is called OpenDatabase (Line 302 is the event assignment in question) and TreeTableClick is the event function. To trigger the bug, open a database, click on a blank area in the tree and then click Refresh Database. You should see the ListView flicker several times as it gets regenerated (TreeTableClick resets the ListView). This doesn't happen all the time... can't figure out what is causing it.

So there it is so far... enjoy!

Kevin

Database.au3

[randallc]

Thanks;

Does this thread or any other have an example db I can open and works with this please?

Randall

[ptrex]

éblink314

Hello again. I am not in the office at the moment so I can' t run the latest version today.

I will get back to you tomorrow.

@randallc

If you want a database to play with. You can use the one wich we used for testing the excel export.

Run my example and copy the db forl the \profile\temp folder to an other place; before you close the GUI.

(After closing the Gui the db is deleted.)

Get back to you all later.

[blink314]

Yeah, once you open the database you can add tables and records to it at will to build up a better test DB. I'll be adding some more functions today.

Kevin

OR you can create a new database. Just click open, browse to the folder you want it in and type in a name. I should perhaps make that a little clearer.

Edited November 10, 2005 by blink314

[blink314]

Here is the next version. Unfortunately there is a huge bug with the way I'm refreshing the listviews. Once you refresh one it shows up on all tabs! I tried refreshing without deleting but that did not work. I have a thread started in the GUI forum so hopefully this will be fixed. A few new functions, the enter SQL section kind of works, and some bugs I found are fixed.

Let me know if there are problems!

Kevin

Database.au3

[randallc]

I don't yet know SQL, and was hoping to learn the commands by "seeing" them in this great rogram!

The db @ptrex talks about just shows a little treeview top left, but no data, and I can't seem to generate a table I can see from the "Add table/ Add field commands as they stand; oh well....

Randall

[blink314]

Don't give up so soon! Especially when my documentation stinks as bad as it does! When opening the DB from ptrex's script I get one tree item labeled "test." Click on it and the data from that table should be loaded into the listview. Data is only loaded into the listview when you click on a table. Also, for some reason AutoIt does not show the plus sign by the side of the treeitem until you try to click on it. This should give a list of all fields and all indexes for that table.

As far as adding a table, are you giving a tablename and inserting fields?

Here is a sample of what I type in at the field inputbox:

FieldTry::3 (Don't forget the double colon!)

Add as many fields as you want (make sure one is a primary key) and then click create table. If you only add one field (the Primary Key) you will not be able to add records.

I opened the sqlite3.db and made a table no problem. Let me know if you still can't see the data or make a new table.

Kevin

Edited November 10, 2005 by blink314

[steve8tch]

Thanks alot for that instant tutorial - very helpfull.

Could you throw together a slightly longer tutorial - for us real thickies - along the following lines.

Create table using 'FieldTry::3'

Add in the following values using the 'New record' button.

Go to the SQL entry and type ' some thing - I don't know what to type'

Press 'Process SQL' button to get result.

Once we 'thickies' can see that work - it would be good to see how the same would be coded in a script.

Sorry for asking you this - it is obvious you are already working hard making this GUI really usable - but I think there are quite a few of us that would like to use this stuff - we can see the power of it - its just that we need just a little hand holding to get over that first hurdle.

Thanks alot for your help and contribution

[blink314]

Yeah... that would probably be useful. Give me a little bit. In general, I have created the GUI to be left to right, top to down. Meaning start on the left and work your way down the columns. So fore the create table column, give a table name, insert a few fields, then create the table.

As I said, give me a little and I'll try to give a brief tutorial. My SQL is practically nonexistant... all I know is what I have coded into the script!

Kevin

[blink314]

Ok here is a new version with the Listview/Tab interaction corrected thanks to jpm. Here is also a brief tutorial on how to use this program.

- Follow ptrex's instructions for downloading and installing LiteX.

- Run my script

- Start by opening a database. If you don't have a database generated yet, don't worry, opening a database is the same as creating it.

If you have created a database:

- Create a table. Enter a table name "NewTable", input some fields ("KeyField::3" , "Field1::0" , "Field2::0") and then press Create Table.

- You should see the table appear in the TreeView. Click on the name of the table, right now there is nothing in it! Click on the plus next to the tablename, you should see your three fields staring back at you.

- Skip the "if you have opened a database" step!

If you have opened a database:

- The tables are shown in the TreeView. Click on one to see what's in it. Click on the plus sign to see what fields are in it, along with their characteristics.

Now that you have a table to play with... create some more for fun. Then pick one and delete it. Just cause you can!

Now insert some records.

- Click on a table in the treeview, then click on the New Record button.

- A window comes up... insert some words into each inputbox. Click "insert." The record should pop into the listview.

- Create records at will!

Those are the basics for now. You can see all the disabled controls that I am hoping to add. Refreshing the database just reloads it, and Vacuum just gets rid of dead space from after you deleted a table (SQLite specific!!).

The third tab, the SQL tab is for practice. I'm not sure it works perfectly, but it does do some things right. Once you get a table with about 5 records in it, enter the following into the SQl entry box:

SELECT * FROM [table-name]

A list should appear in the listview below. This is the exact command I use in the script when I show your database in the listview! If you want to create a new table in the SQL window you would type something like this:

CREATE TABLE NewTable(Field1 INTEGER PRIMARY KEY, Field2 TEXT, Field3 TEXT)

Don't get hung up on the SQL tab. It doesn't crash nicely!! One interesting command to try is this:

SELECT sql FROM sqlite_master WHERE tablname='test'

Replace "test" with a table from your database and you will see the orginal CREATE statement in the listview (you may need to expand the column).

But the SQL tab is for practice only. All the basic database actions will be able to be done in the Raw View tab... that's why it's there! To give more of a GUI overview to a database.

The first tab is non-functional right now. And will remain that way for a while!

For SQL syntax look around the web. for LiteX usage within AutoIt look at my script. Any questions, feel free to ask.

Kevin

Database.au3

Edited November 10, 2005 by blink314

[steve8tch]

@blink314

Thankyou.

Edited November 10, 2005 by steve8tch

[randallc]

Thanks, '

I see what I was doing wrong...

Can you stop the listview flickering? -lines while. wend near 508

GUISetState(@SW_LOCK)

While $tabledata.step () = 0

......

WEnd

GUISetState(@SW_UNLOCK)

I should be able to learn something with all that help!

Best, Randall

[blink314]

@randallc

You are the man! I assumed that there was no way to stop that flickering... fixed!

Ok, here is the latest version. Flickering fixed, edit record added, bug fixes (hopefully). Also, I've added a new feature called Beginner Mode (checkbox in the lower right). As people have responded to this script I've realized that many are trying to use it to learn SQL (that's funny... that's what I'm using it for!). Beginner mode will popup a messagebox after every important SQL statement made from the Raw Database tab. This serves two purposes:

1. those of you who want to see what the actual SQL looks like... look to your heart's content.

2. Those of you who are masters of SQL, please look and see if I'm making gross errors. Remember, I have no formal training in SQL and everything I've done I've learned in the past week. I feel slightly scared to be instructing other beginners here!

Finally, I have further refined how this script will work. The more I delve into SQL the more I see it has tons of options. The Raw View GUI will only cover the basics or I would go nuts trying to include everything. For more advanced stuff, you will need to learn SQL and enter the commands from the SQL tab. The Raw Data tab is for newbies to see what the major pieces of a database are as well as some basic commands, and for advanced users to quickly make a database and lay out tables. It also provides a look into the database so you can see what's there.

So, that's that. Enjoy,

Kevin

Database.au3

[ptrex]

@blink314, @randallc, @steve8tch

Hello all. I see that you all got exited about SQLite and discovered the power of it.

This was my sole objective, to show this in my first little example.

As you can see there' s a lot you can do with it. Espescially in combination with AutoIT.

SQLite is the perfect mariage for standalone DB applications.

I' ve tried tha latest version of the database.au3 and the basis looks OK. Nice GUI interface clear structure. Good job so far blink314.

But I also tested it against a database with more tables and columns and than there are some errors comming up. These errors are relating to Array declarations with not enough dimensions.

Line 388 has incorrect number of subscripts error specificaly relating to the $TempArray

If you need more complicated DB file to test with, let me know. I can attach an example of the famous Nwind.db if you want.

Keep on the good work !!

@randallc

I tested your latest version of the LV export to excel. But still I get errors.

As you probably saw I created a thread where an excel grid in incoporated in AutoIT. I will make an example of how to transfert data from the LV to Excel.

Later on we can use this as the report generator for the SQLite application blink314 is building.

But time is fighting against me, so be patient.

[blink314]

@ptrex

Sure link a bigger db! Right now I'm working on some fixes, mainly with how tables are created. But then I plan to move to the import/export to excel so i can build big database quickly. It would be nice to have a big one handy.

Kevin

incidentally, I only use $TempArray when something needs to be put in an array for a short tim... usually when I'm parsing something. I wonder how that test db is structured... I may be making assumptions that are false.

Edited November 12, 2005 by blink314