Need guidance on database with my movie media program

[stevey]

I am working on a media center program. The program imports a list of movies that I have and displays it all in a GUI that you can navigate around. It shows the movie title, date, actors, plot, box image, etc.

I am having trouble figuring out the best way to handle the database. My first version simply used _INetGetSource() and using string search cleaned up the data and saved the data to individual .ini files for each movie. As I started working with it, I realized that it could end up being slow due to having to load the data, then open/write/save to .ini files over and over (rather than keeping the file open during all data changes). Also having a ton of folders and .ini files for each movie doesn't seem the best way to go. I started searching and found that IMDB publicly post their database files with all the movie details (http://www.imdb.com/interfaces/), so that is what I am wanting to work with. The files are pretty large for just plain text (they are .list file extensions - not too sure how to handle these with AutoIT). Apparently they can work with a SQL database. But that is something I have not ever worked with. After I did some searching when I found out that IMDB post their entire database you can download I found two sites (http://imdbpy.sourceforge.net/ and http://www.jmdb.de/index.html) that are related to what I am wanting to do.

If somebody could give me some guidance with which way I should handle the database/storing/retrieving of information for this program that would be a great help. I really don't understand what SQL would do in this case, I will have all the data I need on my local machine that the program is installed on and would not have any need to share it to other computers. If there are any questions then please ask away. I don't need anybody to write me code, i just need to know how I should be handling this part of the program and I can do my own research in more detail.

Thanks in advance

[stevey]

Any ideas?

[Realm]

Hello stevey,

I agree that SQLite is the way to go, not only is it ACID (atomicity, consistency, isolation, durability), but I have heard it can quickly interface with db that are over 100k records strong. The developers of SQLite guarantee 100k records while remaining ACID. Also AutoIt has a UDF for SQLite commands in the Help file. I am still having difficulties attempting to understand building SQL databases myself.

However, if you wish to stay with the old trusty file format. I personally would not suggest ini file for this particular project. If you did go with ini, Then AngelofDeath is right, you would need to create a new section for each movie, making one very large file for large movie collections. Again, I suggest going with a Normal Text file.

In your text file, you will create 1 line for each movie, an example:

Movie Title;2010;Comedy;2 Hours 10 Minutes;DVD;A Comment;A Description

After you have create a text file. you can load the file into a Single Dimension array:

#include <File.au3>

_FileReadToArray(@ScriptDir & '\MovieFile.txt',$movie_collection)

And then you could create a GUI with a Listview, and add the collection to it like this:

Local $listItem[UBound($movie_collection)]
For $i = 1 To UBound($movie_collection)-1
    $movie_data = StringSplit($movie_collection,';')
    $listItem[$i] = GUICtrlCreateListViewItem($movie_data[1] & '|' $movie_data[2] & '|' & $movie_data[3] & '|' _
        & $movie_data[4] & '|' & $movie_data[5] & '|' & $movie_data[6] & '|' & $movie_data[7], $listview)
Next

I have been using this method for a while now, and surprisingly it is prettey simple, yet remains pretty fast for even large files of records. One project alone has over 25k records, and it can load the file into an array, then display in a listview as I gave examples of above in jest a couple seconds.

Good Luck

Realm

P.S. Because there is limited documentation and help for SQL functionality with AutoIt at the moment, and if you still want to further learn how to use SQLite, I would suggest googling for tutorials and such. I know that www.DreamInCode.com has a few. I have seen many ppl ask for SQL help here, and go unanswered, unelss they have provided code.

Edit: Fixed Typos

Edited November 16, 2010 by Realm

[jchd]

Ahem, I believe that SQLite is just what you need for your project.

SQLite commonly handles DBs as large as tens of Gb (some even hundreds) without problem and often faster than unaffordable commercial DB engines. While it _does_ have its limitations (not being a client/server thing is one), it's free, has superior free support, comes in highly portable full source, is easily expandable, ...

You're lucky to enjoy good UDF support for SQLite with the latest available Autoit release.

Now why choose a DB rather than anything else? Simple: to be able to use the power of a DB to organize, index, search and retrieve your data. Imagine what bloody constructs you would have to build to retrieve all films made by author X within date range Y1 to Y2 and featuring actor Z. Or those dramas featuring _both_ actors A and B, ... Each of these (very simple) queries would require a distinct construct, sorting, filtering, while they all can be produced as the result of elementary SQL queries _way_ more efficiently.

Granted, it's difficult and time consuming to go thru a DB design process on AutoIt forum but I've done my best to help some newcomers to start their project using SQLite.

First familiarize yourself with the simple examples in the help file and browse the SQLite documentation on the SQLite website. Then start with a toy project and some SQLite management tool. I highly recommend SQLite Expert Pro, but you can also get along with a free Firefox extension. Once you get a graps at how SQL works and understand how to design your DB, then start coding it but leave the GUI for later: use your management tool to display/change the DB until it settles to a working form.

If you're completely lost, chime again.

[stevey]

Thanks a lot guys... I am making progress! I am just working with a small database i made with just a couple rows and columns. I have no problem loading and saving it in SQLite3.exe and viewing the table, etc, but when I try to use Autoit to load the database and print the contents of a file, it keeps saying the table is not found.

I also tried to run an example script _SQLite_SQLiteExe but it passes the error @error: 1 - Can't create new Database, no idea what is going on there...

[jchd]

There may be something wrong with launching sqlite.exe in the version you use. I don't really have time to dig into that, as using the CLI (command-line interface) is cumbersome at best.

Download and install a real SQLite DB manager like SQLite Expert, the Firefox extension or some other one. These tools allow you to monitor your DB, display it, change data and [re]design it in seconds. It's also a great debugging tool for your SQL. They are way more easier to use than the CLI.

Show the AutoIt code you use.

[stevey]

My code is basically the same as _SQLite_GetTable example script but slightly modified.

The GetTable example code is as follows:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open () ; Open a :memory: database
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

;Example Table
;   Name        | Age
;   -----------------------
;   Alice       | 43
;   Bob         | 28
;   Cindy       | 21

If Not _SQLite_Exec (-1, "CREATE TEMP TABLE persons (Name, Age);") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Alice','43');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Bob','28');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())
If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Cindy','21');") = $SQLITE_OK Then _
        MsgBox(16, "SQLite Error", _SQLite_ErrMsg ())

; Query
$iRval = _SQLite_GetTable (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
;~  $aResult Looks Like this:
;~      [0]    = 8
;~      [1]    = Name
;~      [2]    = Age
;~      [3]    = Alice
;~      [4]    = 43
;~      [5]    = Bob
;~      [6]    = 28
;~      [7]    = Cindy
;~      [8]    = 21
    _ArrayDisplay($aResult, "Query Result")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf

_SQLite_Close ()
_SQLite_Shutdown ()

I used SQLite3.exe to make a database with the same information and saved it DB1.db so then i modified the code to make it open the DB1.db file and not enter any data into the db file.

Code looks like this:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <Array.au3>

Local $aResult, $iRows, $iColumns, $iRval

_SQLite_Startup ()
If @error Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit - 1
EndIf
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open (@ScriptDir & "DB1.db") ; Instead of example "Open a :memory: database", I am trying to load my database file DB1.db
If @error Then
    MsgBox(16, "SQLite Error", "Can't Load Database!")
    Exit - 1
EndIf

;Example Table
;   Name        | Age
;   -----------------------
;   Alice       | 43
;   Bob         | 28
;   Cindy       | 21



;Example code would enter the data into the table here, but since I am loading a saved database with the same data I should be able to skip this step.


; Query
$iRval = _SQLite_GetTable (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
;~  $aResult Looks Like this:
;~      [0]    = 8
;~      [1]    = Name
;~      [2]    = Age
;~      [3]    = Alice
;~      [4]    = 43
;~      [5]    = Bob
;~      [6]    = 28
;~      [7]    = Cindy
;~      [8]    = 21
    _ArrayDisplay($aResult, "Query Result")
Else
    MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ())
EndIf

_SQLite_Close ()
_SQLite_Shutdown ()

I have a SQLite DB manager.

[jchd]

The SQL in the example shown creates a temp (= temporary) table. Temp tables are dropped at closing the DB connection.

Try the same without the temp qualifier, it should work. Or post your sample DB.

[stevey]

Yes, when i made the DB1.db file i left out the temp, i just entered CREATE TABLE persons (Name, Age);

I can view the DB1.db file just fine in SQLite (after closing and reopening it) and the DB manager and the contents are being saved and showing correctly

Here is a link to the DB1.db file:

http://www.mediafire.com/?doja12y31px2voe

Edited November 18, 2010 by stevey

[jchd]

This works for me:

#include #include #include Local $aResult, $iRows, $iColumns, $iRval _SQLite_Startup () If @error Then MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit - 1 EndIf ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF) _SQLite_Open (@ScriptDir & "DB1.db") ; Open a :memory: database If @error Then MsgBox(16, "SQLite Error", "Can't Load Database!") Exit - 1 EndIf ;Example Table ; Name | Age ; ----------------------- ; Alice | 43 ; Bob | 28 ; Cindy | 21 If Not _SQLite_Exec (-1, "CREATE TABLE if not exists persons (Name, Age);") = $SQLITE_OK Then _ MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Alice','43');") = $SQLITE_OK Then _ MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Bob','28');") = $SQLITE_OK Then _ MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) If Not _SQLite_Exec (-1, "INSERT INTO persons VALUES ('Cindy','21');") = $SQLITE_OK Then _ MsgBox(16, "SQLite Error", _SQLite_ErrMsg ()) ; Query $iRval = _SQLite_GetTable (-1, "SELECT * FROM persons;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then ;~ $aResult Looks Like this: ;~ [0] = 8 ;~ [1] = Name ;~ [2] = Age ;~ [3] = Alice ;~ [4] = 43 ;~ [5] = Bob ;~ [6] = 28 ;~ [7] = Cindy ;~ [8] = 21 _ArrayDisplay($aResult, "Query Result") Else MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg ()) EndIf _SQLite_Close () _SQLite_Shutdown ()

What do you get?

[stevey]

Did you mean to post that in code tag?

[jchd]

Call it a mouse slip!

test.au3

[stevey]

Ok, i cleaned up the code you posted and it is working... However I am confused.

Every time I run the script, it adds more lines to the array that is displayed, but when I view the DB1.db file in my manager it still show just the 3 entries (the ones I entered and saved in SQLite.exe). How are the results being remember from each time I run the script even when i close and reopen autoit? Shouldn't it just show my original DB1.db and another copy of each entry that is being added when the script is ran?

[stevey]

Also, any reason why my first example script didn't post the table contents? The only difference I see is that you modified the table before posting the results of the table. Why would it say table not found in my script?

Edit: I just modified my original script and had it add data and it now will show the array of results (weird?) How would I have it show the results in cases that I dont need to add any new data to the table?

Edited November 18, 2010 by stevey

[jchd]

I believe it has something to do with what @ScriptDir means

Hint: @ScriptDir doesn't include a final backslash, hence, the actual file created/opened is ...