Sign in to follow this  
Followers 0
stevey

Need guidance on database with my movie media program

15 posts in this topic

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

Share this post


Link to post
Share on other sites



Any ideas?

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by Realm

My Contributions: Unix Timestamp: Calculate Unix time, or seconds since Epoch, accounting for your local timezone and daylight savings time. RegEdit Jumper: A Small & Simple interface based on Yashied's Reg Jumper Function, for searching Hives in your registry.  

Share this post


Link to post
Share on other sites

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.


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

Share this post


Link to post
Share on other sites

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.


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

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

The GetTable example code is as follows:

#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:

#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.

Share this post


Link to post
Share on other sites

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.


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

#9 ·  Posted (edited)

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 by stevey

Share this post


Link to post
Share on other sites

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?


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

Did you mean to post that in code tag?

Share this post


Link to post
Share on other sites

Call it a mouse slip!

test.au3


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

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?

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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 by stevey

Share this post


Link to post
Share on other sites

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

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


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

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
Sign in to follow this  
Followers 0