What database should I choose not to worry about size limit?

Question

Hi,

I want to make rather simple database, which should store videofiles, and soundfiles. I wanted to use MS Access database (mdb), but this is limited to 2 GB. I do not want to use server - client solution, cause it should be single application using only local database stored on local harddrive. That's the idea.

May you give me an advice?

thanx

Answer 1

If you want to store binary data like video/audio data, you should consider using the filesystem and putting metadata like width, height, length, author, ... in a database of your choice. I like MySQL.

Answer 2

How about Sqlite? That would be suited for your needs I'm sure, plus, it's public domain. There are bindings for different languages also. See here for the Delphi interface for Sqlite.

Edit: after zebrabox's comment - You can create a simple table to identify different resources and paths...something like this

CREATE TABLE "BlobId" (
    "BLOB_TYPE" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE "BlobInfo" (
    "BlobInfo_Id" INTEGER PRIMARY KEY NOT NULL,
    "BlobInfo_Date" TEXT NOT NULL,
    "BlobInfoPath" TEXT NOT NULL
);

BlobId table would have the values 1 for Image, 2 for Audio, 3 for Video, and it would be logically linked to BlobInfo table

BlobInfo
1, 2010-02-13 04:05:40  C:\Blobs\SomeImage1.jpg
1, 2010-02-13 04:05:40  C:\Blobs\SomeImage2.jpg
2, 2010-02-13 04:05:40  C:\Blobs\SomeSound.wav
3, 2010-02-13 04:05:40  C:\Blobs\SomeVide.mp3

By using Sqliteman which is the Sqlite Manager GUI front-end for administering the Sqlite databases, will make the job easier. The onus is on you to make sure that the path is flexible enough to be moved in conjunction with the database.

Hope this helps, Best regards, Tom.

Answer 3

Firebird is one of the best choice for Delphi and one embedded version exist

And an article about Firebird and blob storage

Answer 4

I'd go with embedded Firebird. You can have a maintenance-free desktop application and if you want you can scale it later on to a full C/S system. Also you have enough libraries (free or not - Zeos, UIB, IBObjects, FIBPlus) to use it from Delphi. Not mentioning that the latest Delphi has a DBX driver OOTB.

Also, having large amounts of data in the database blobs (if you design it right) doesn't affect the search speed.

HTH

Answer 5

Hello,

I use Advantage Database Server

and it has a local server option and very easy to use. I have about 12000 records in my main table with some 70000 linked list table each with a picture and an audio file (full mp3 song) and about 800 video clips in the database.

all is working very good.

you should give a test.

Best Regards.

Answer 6

PostgreSQL is not embedded, but you can run it on your local machine (in a separate process). It can store BLOBs in the "bytea" data type. Open source and free.

PS I think the maximum size of a bytea field may be 4GB, because there's a four byte length field, but I can't see that explicitly stated in the postgres documentation.

Answer 7

As suggested before I also recommend FIREBIRD. Delphi has Native Support for this, it requires minimal overhead in code, easy to install, zero maintenance, transportable if needed, super feature-set and best of all: it's totally free for even Commercial products.

Comparing MySQL and Firebird might be considered by some comparing a toy toolset (MySQL) to a real Pro's tools (Firebird). - I hope this does not start a war...

Answer 8

I recommend DB2. DB2 since version 9 supports very large tables. The maximum is monstrously large. 512000 petabytes, half a zettabyte.

Answer 9

We have used postgreSQL using Devart pg components with great success in medium sized database apps. We did some limited benchmarking with this combination and found it 2-3 x the speed of using ADO etc.