OT: Database question

[Bill]

Does anyone here use SQLite? I'm trying to cobble together data off a few databases, and it looks as though it ought to be a sensible choice for me.

My aim is to get a simple non-server-based list of tons of audio to record where it's stored and in what format, accessible via the OpenOffice front end and including data pulled off the internet about a whole bunch of CD's that have come my way after a bereavement. I use MySQL via OO, and the idea is to use a fairly standard other SQL db system to be able to transfer to the servered MySQL in the future if appropriate. I'm very much a dabbler with a terrible memory, so when I need to change anything I have to get the books out to remember commands.

The CD data can be pulled into a hugely overcomplex SQLite database, but I'm having a hard time working out how to sit my own simple SQLite database alongside it and copy fields of records between the two.

I've been Googling for days now, skimming through some old SQL books and trying all sorts of guesses at the command line but success evades me. Does anyone know of a simple basic guide for an idiot like me to do this sort of thing?

[Andy Dingley]

Music database? Surely there's something ready-made to download?

[Tim Watts]

If it were me I'd knock up a perl script using:

is the universal interface in perl to SQL databases, the top end being a standard API and the bottom end being a driver.

Even if you don't know perl but are comfortable with C, you could probably knock up something by ripping bits of the example code from

main complication is; is it a one way spat-n-copy (easy, expect 1-2 screens of perl) or a 2-way replication (yukkier - need to define the algorithm first).

HTH

Tim

[Tim Streater]

Here's what I do in PHP to copy something from one SQLite database to another:

myquery ($dbh, "attach database ':memory:' as mem", "GM9"); myquery ($dbh, $create_messages, "GM10"); myquery ($dbh, "attach database other_database as dst", "GM11");

myquery ($dbh, "insert into mem.messages select * from messages where absid='$absid'", "GM12"); myquery ($dbh, "update mem.messages set absid=null", "GM13"); myquery ($dbh, "insert into dst.messages select * from mem.messages", "GM14");

where $create_messages contains a long "create table ..." type of string and absid is a unique pointer to the record you want to copy. The "GM11" etc are just markers so if I get a database failure I know where it happened.

Here's myquery:

function myquery ($dbh, $sql, $where) {

try {

$res = $dbh->query ($sql);

return $res;

}

catch (Exception $e) {

$emsg = $e->getMessage ();

logWrite ("SQLite error, " . $emsg);

$msg = " at " . $where . ", sql: _ " . $sql . " _"; logWrite ($msg);

exit ();

} }

and you needed to connect to (say) first_database with:

$dbh = myconnectdb ("first_database");

function myconnectdb ($db) {

$connstr = "sqlite:" . $db; $dbh = new PDO ($connstr);

$dbh->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

return $dbh;

}

[Bill]

That's what I thought, too, until I started looking. Most are overcomplicated and don't have a simple enough front end, or are too simple and use some odd database backend. SQLite seemed to be good because of its widespread use. Things I need include.... to be able to pull data re CD tracks, to be able to pull metadata from wave files, to be able to build lists of what is kept in what building, to be able to catalogue vinyl, shellac (!), reel tapes, cassettes, DATs, minidisks and a load of other weird formats easily with minimal typing, and to be able to write big or tiny info fields for each item.

I have been using MediaMonkey to pull CD data off the internet and it seems to work fairly well and I can find the data in its SQLite database, but I seem to have problems with the format of the text data when I try to link its database to mine.

[Bill]

Many thanks, Tim and Tim. I haven't ever used Perl or PHP, but have some books! I used to be able to cobble stuff together using Visual C or Borland Builder, so I'll look at that, too.

I've mainly been getting an error that reads "Error: attached databases must use the same text encoding as main database", so I guess I need to look at how the database I'm trying to attach to handles text.

I'll go away and study for a bit.

Thanks again.

[Bill]

In message , Tim Streater writes

Tim, I'm still setting up php to work outside a browser (have had no time today), but would like to check whether this will actually help with my problem, which seems to be that the database I'm wanting to copy from uses TEXT COLLATE IUNICODE format for the text data ( I understand this is to allow case-independent sorting) and the standard SQLite doesn't seem to understand this. Working from a command line, I get error messages like

sqlite> attach database 'MM.db' as MM; Error: attached databases must use the same text encoding as main database

sqlite> CREATE TABLE Songs (SongsKey INTEGER PRIMARY KEY, Songs TEXT COLLATE IUNICODE, Artist TEXT COLLATE IUNICODE); Error: no such collation sequence: IUNICODE

[Tim Streater]

I've never used COLLATE so can't help with that. When I want to sort case-independent I usually do something like:

select * from some-table order by lower(some_key);

Not sure what you mean by "outside a browser".

[Bill]

In message , Tim Streater writes

I'll remember that. That is something I'll need to do later! I think the trouble is actually with the IUNICODE rather than the COLLATE. I've tried every combination of TEXT UNICODE and COLLATE, and each one gives an error message one way or another.

Tomorrow, I'll investigate custom reports that someone has pointed me towards.

Oh dear, I meant web server not browser. I need to have a rest, as is obvious from the time I'm typing this.

I am really grateful for the support!

[Tim Streater]

:-)

What platform you on (sorry if you said earlier and I missed it)? I'm on a Mac and I use PHP5 and SQLite in a Terminal window from the off, no need to do anything.

Keep us posted.

[Bill]

In message , Tim Streater writes

Just a quick update. I'm on Windows XP SP3, and have to stick with Windows of various types because of the audio apps I use.

I seem to have PHP installed, and have managed to get data out from the TEXT COLLATE UNICODE fields into a csv file. I have not done anything yet with PHP except type php at a command line and ctrl-c to get back from the blank line! Need time to find instructions.

I also thought I might be able to append data to an SQLite database from a csv text file at the command line, but haven't found any obvious way.

I'm sure I'm making too much of a meal of this.