Exploring the Database

[VanguardH]

Ok, had a bit of a play this afternoon. I think I'll have to put the app on hold for the mo. Maybe I was a little too excited!!!

I've downloaded a package called SQLite2009 Pro Enterprise Manager from http://osenxpsuite.net/?xp=3&uid=managementtools this is Windows only, but freeware. This enables us to be able to edit/write to the files. WAIT!!!!!!!!

Another package I've looked at is view only, but may also have it's uses. This is SQTView from downloads.com. This is also Windows only. I've also tried a php based package called SQLiteManager, but had problems getting it to upload the database. Mainly cause I was rushing, and couldn't be bothered to figure out any more.

Back to what I was saying.
THIS IS EXPERIMENTAL. DO NOT USE ON REAL DDS LIBRARIES YOU EXPECT TO BE ABLE TO LOAD ON YOUR DDS!!!
If you look inside your .library folder you'll see a number of files (obviously a backup in a safe place!!).

database : the main database library. Contains tables for tracks, playlists, playlist tracks... etc. full details on way.
blobs_database : stores 2 tables. One for blobs.

So far I've been playing with the import and export of these tables to the various formats. It isn't quite as straight forward as I had imagined!!

Here's the scenario so far. If I want to add a record I need to add it via an SQL (either MySQL or SQLite3 syntax). Not too bad, but the app has to send it to a command shell parser.

Now, if I want to get that data back, I'd need to do an SQL Query of the tables. Again not that difficult but then I need to programatically parse the resulted record set and split it into cells and stuff. So the edit is trickier still, but not impossible!!!

I had been hoping that a precompiled VB dll (yes.... VB..... I know, but not going to a masterpiece of a program is it!!) would already exist, but I can't find one. My other option was a PHP program, but you've not seen my PHP!!!.

More Later,

Van.

[vokf]

Van,
I have some scripts to use the SQLite3 database;

This is the main batch file. Run in the same dir as the sqlite executable AND the database (from the DDS)

Code: Select all

del tracks.txt
sqlite3 database ".read sqlite_export.txt"


This file should be named "sqlite_export.txt" and is the config/control file for sqlite.

Code: Select all

.mode insert tracks
.output tracks.txt
select title, album, from tracks;


Obviously, the "select" statement can be tweaked to expose more fields in the DDS database.

The output from this code, will be a file named "tracks.txt". This will contain SQL INSERT statements that can be run on mySQL (or most other database engines).

If you read the sqlite docs, you can export CSV etc, so importing into a spreadsheet will be easy.


I actually use this on my website, and have a simple ajax song look-up;
http://www.dancesoundsdisco.co.uk/music.html

I'm using a simple bit of Javascript, and some basic PHP to run a query on the mySQL database, and populating the <div> with the output.
It works quite well - I'm working on a new site, and considering doing a real-time youtube lookup for any songs found.

Unfortunately, my web server only has sqlite2, and not sqlite3, so I cannot natively use the DDS database (until cpanel updates their sqlite package), so I have to run the above script, then bring the output into the database on my site manually - its only 5mins, and I only do monthly.

I hope this helps,
Jason

[VanguardH]

Thanks for this... I've been reworking what I've been thinking a bit. Mainly has I had to completely rebuild the library. Getting the playlists in and edited is a must, however, I have found an issue with the database files locking themselves and consequently not allowing the DDS to update them. I'm still researching this on both Mac and PC.