SQLite – Practical Use of DB Clients and BLOBs

sqlite

I'm looking for an SQLite client that can help me to make practical use of the DB. The plan is to insert mp3's as BLOBs (or perhaps just as references to external files). Each record will also include transcripts/notes/tags.

I'd like to be able to access this DB in such a way that I can click on the mp3 and listen to it, then add text data in the other fields, and finally search on text in the DB. LibreOffice Base is a nice client that allows me to define forms, but I'm not clear if simple access to the mp3 is possible. Are there other options?

I should say up front that I can do bash scripting and C, but I don't have python.

Best Answer

Following up on the comments, one option to simplify this requirement without resorting to developing a user interface to link sound files and a database, and considering your skills in shell programming, is using command line tools to edit/retrieve tags on MP3 files (Here's a QA with plenty of resources and alternatives for reading and manipulating mp3 tags from the command line), and using XMMS2 or similar to build and maintain the library of files, and see or manually edit individual file's MP3 tags.

The benefit of this approach is that XMMS2 is not only very good at handling a library of sounds/music (including creating playlists and so forth) but also in editing MP3 tags, which can be manipulated from command line scripts through a variety of open source well documented and popular tools.

Related Solutions

Limits of SQLite

Check out https://stackoverflow.com/q/784173/27310 to get an idea of how big SQLite databases can get in theory without performance problems. However, I'd suggest that you use a good database abstraction layer so you don't have to worry about the underlying database at all. That way, once you hit SQLite's working limits, you can switch over to a non-embedded RDBMS without much work.

How to use CREATE TABLE … AS with Primary Key in SQLite

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );

You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
  PHIL   INTEGER PRIMARY KEY,
  SUCKS  INTEGER NOT NULL
);

INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );

Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);

Hope that helps!

Best Answer

Related Solutions

Limits of SQLite

How to use CREATE TABLE … AS with Primary Key in SQLite

Related Question