Re: [sqlite] Music Files
Ok, Let me do it with C language
const unsigned char* Format(const char* szFormat, ...);
int AudioFillDatabase(const char* audiofile);
int main(void)
{
Open();
execDML(" CREATE TABLE IF NOT EXISTS AudioTable (id INTEGER , \n"
" name VARCHAR , \n"
" data BLOB , \n"
" type VARCHAR);");
AudoFillDatabase(SomeSongname.xxx);
Close();
exit(0);
}
int AudioFillDatabase(const char* audiofile)
{
FILE* fp = fopen(audiofile, "rb");
if ( fp == NULL )
return -1;
else {
fseek(fp, 0, SEEK_END);
long filesize = ftell(fp);
unsigned char* MemFileSize = new unsigned char[filesize + 1];
size_t ret = fread(MemFileSize, filesize, 1, fp);
if ( ret != 0 )
return -1;
else {
unsigned char* buf = new unsigned char[filesize+1024];
buf = Format("insert into AudioTable values (0, 'music', %Q, 'mp3');",
MemFileSize);
execDML(buf);
}
}
const char* format(const char* szFromat, ...)
{
va_list va;
va_start(va, szFormat);
static char* mpBuf = sqlite3_vmprintf(szFormat, va);
va_end(va);
return mpBuf;
}
- Original Message -
From: "Isaac Raway" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, November 28, 2006 11:04 PM
Subject: Re: [sqlite] Music Files
> On 11/27/06, LuYanJun <[EMAIL PROTECTED]> wrote:
>>
>> Can anybody give a simple example for domestrating ?
>> I am puzzled by this topic, how does a music file be sotred in DB as BLOB
>> type?
>
>
> You can insert /any/ kind of data into a SQLite database (or most any other
> sort of DB for that matter). Here's a short Pascal program that would do
> about what you want -- but of course getting the binary data out and into an
> object that can play it is another matter. Also I imagine this would be very
> slow, coming in at around 3 - 5 MB per song that has to be completely loaded
> into memory from the DB before playback and begin. I have not tested this
> but it gives you the idea: 1) load data into a stream / data string 2) write
> as DB BLOB.
>
> var
> MP3Source: string;
> Data: TFileStream
>
> DBFileName: string;
> DB: TSQLiteDatabase;
> MustCreate: boolean;
> begin
> MP3Source := 'SomeSong.mp3';
> DBFileName := 'mp3.db3';
>
> MustCreate := not FileExists(DBFileName);
> DB := TSQLiteDatabase.Create(DBFileName);
> try
>if MustCreate then begin
> DB.ExecSQL('CREATE TABLE mp3(filename STRING PRIMARY KEY, data
> BLOB);');
>end;
>
>Data := TFileStream.Create(MP3Source, fmOpenRead);
>try
> Data.Seek(0);
>
> DB.UpdateBlob('INSERT OR UPDATE INTO mp3(filename, data) ' +
>'VALUES(' + QuotedStr(MP3Source) + ', ?);', Data);
>finally
> FreeAndNil(Data);
>end;
> finally
>DB.Close;
>FreeAndNil(DB);
> end;
> end;
>
Re: [sqlite] Music Files
I cannot be much help since we never use VB or other similar proprietary software prisons. However I did see this set of notes from someone who was using Sqlite from VB and created a modified sqlite.h. http://www.persistentrealities.com/index.php?p=18 If you don't already have a lot of VB code it may be a good time to look at using a different tool which will run on Linux and OS/X as well as Windows. made hendra wrote: Hello John, Wednesday, November 29, 2006, 1:08:56 AM, you wrote: Here are a couple of links to get you started. I suggest you google your way until you find something which suits you. http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers http://www.freevbcode.com/ShowCode.asp?ID=6893 I already have googling and i still not found wrapper that suit on me. I already have try AGS_SQLITE wrapper too, it nice but it still using sqlite2 library. Do you have some reference, where i can learn about making wrapper, a tutorial maybe. I intend to cook wrapper for my self (i hope) :) Best Regards, made hendra - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Music Files
Hello John, Wednesday, November 29, 2006, 1:08:56 AM, you wrote: > Here are a couple of links to get you started. I suggest you google > your way until you find something which suits you. > http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers > http://www.freevbcode.com/ShowCode.asp?ID=6893 I already have googling and i still not found wrapper that suit on me. I already have try AGS_SQLITE wrapper too, it nice but it still using sqlite2 library. Do you have some reference, where i can learn about making wrapper, a tutorial maybe. I intend to cook wrapper for my self (i hope) :) Best Regards, made hendra - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
Here are a couple of links to get you started. I suggest you google your way until you find something which suits you. http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers http://www.freevbcode.com/ShowCode.asp?ID=6893 made hendra wrote: Thanks John, Btw, i'm not a sqlite advance nor vb. I'm not fully understand about vb wrapper. I always using ODBC to create database connection. Any suggest or clue? where i can find vb wrapper for sqlite(3). Tuesday, November 28, 2006, 11:11:04 PM, you wrote: I cannot help you with ODBC, but if you have an embedded sqlite database you may be much better off to use the Sqlite API directly rather than SQL/CLI (ODBC). The Sqlite API handles BLOBs very effectively. Even if you use something like VB you can find an interface (wrapper) for the Sqlite API which will work effectively. You should be aware that Sqlite is not a DBMS server like Sql Server, Oracle or DB2, it is a library of routines to embed in your application. If you use it that way the ODBC route does not make much sense and you lose much of the simplicity of Sqlite. An Sqlite database is just a single file, so you can see how simple that makes managing your application. Backups and recovery are so simple. Distribution of databases cannot be simpler. Best Regards, made hendra - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Music Files
Thanks John, Btw, i'm not a sqlite advance nor vb. I'm not fully understand about vb wrapper. I always using ODBC to create database connection. Any suggest or clue? where i can find vb wrapper for sqlite(3). Tuesday, November 28, 2006, 11:11:04 PM, you wrote: > I cannot help you with ODBC, but if you have an embedded sqlite database > you may be much better off to use the Sqlite API directly rather > than SQL/CLI (ODBC). The Sqlite API handles BLOBs very effectively. > Even if you use something like VB you can find an interface (wrapper) > for the Sqlite API which will work effectively. > You should be aware that Sqlite is not a DBMS server like Sql Server, > Oracle or DB2, it is a library of routines to embed in your application. > If you use it that way the ODBC route does not make much sense and you > lose much of the simplicity of Sqlite. > An Sqlite database is just a single file, so you can see how simple that > makes managing your application. Backups and recovery are so simple. > Distribution of databases cannot be simpler. Best Regards, made hendra - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
made hendra wrote: Hello John, Tuesday, November 28, 2006, 4:08:58 AM, you wrote: sebcity wrote: Is it possible to store mp3 files in a SSQLite database? would they be able to be played from the database? How would you save them? Write them as a BLOB. Are there Sqlite ODBC that support BLOB, or maybe other way to access blob without ODBC? I cannot help you with ODBC, but if you have an embedded sqlite database you may be much better off to use the Sqlite API directly rather than SQL/CLI (ODBC). The Sqlite API handles BLOBs very effectively. Even if you use something like VB you can find an interface (wrapper) for the Sqlite API which will work effectively. You should be aware that Sqlite is not a DBMS server like Sql Server, Oracle or DB2, it is a library of routines to embed in your application. If you use it that way the ODBC route does not make much sense and you lose much of the simplicity of Sqlite. An Sqlite database is just a single file, so you can see how simple that makes managing your application. Backups and recovery are so simple. Distribution of databases cannot be simpler. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
On 11/27/06, LuYanJun <[EMAIL PROTECTED]> wrote:
Can anybody give a simple example for domestrating ?
I am puzzled by this topic, how does a music file be sotred in DB as BLOB
type?
You can insert /any/ kind of data into a SQLite database (or most any other
sort of DB for that matter). Here's a short Pascal program that would do
about what you want -- but of course getting the binary data out and into an
object that can play it is another matter. Also I imagine this would be very
slow, coming in at around 3 - 5 MB per song that has to be completely loaded
into memory from the DB before playback and begin. I have not tested this
but it gives you the idea: 1) load data into a stream / data string 2) write
as DB BLOB.
var
MP3Source: string;
Data: TFileStream
DBFileName: string;
DB: TSQLiteDatabase;
MustCreate: boolean;
begin
MP3Source := 'SomeSong.mp3';
DBFileName := 'mp3.db3';
MustCreate := not FileExists(DBFileName);
DB := TSQLiteDatabase.Create(DBFileName);
try
if MustCreate then begin
DB.ExecSQL('CREATE TABLE mp3(filename STRING PRIMARY KEY, data
BLOB);');
end;
Data := TFileStream.Create(MP3Source, fmOpenRead);
try
Data.Seek(0);
DB.UpdateBlob('INSERT OR UPDATE INTO mp3(filename, data) ' +
'VALUES(' + QuotedStr(MP3Source) + ', ?);', Data);
finally
FreeAndNil(Data);
end;
finally
DB.Close;
FreeAndNil(DB);
end;
end;
Re: [sqlite] Music Files
A BLOB is just a block of indeterminate, untyped data. You reference it by a pointer and a length and recover it the same way. Sqlite will store it as a linked list of pages. Think of it as a file, which is a pointer and a length, stored by the file system as a group or a group of groups of linked disk sectors. When you open a file and memory map it you get a pointer and a length for the file, that is compatible with a BLOB. When you recover a BLOB you can write it to a file using a write API call, which has as its args a file descriptor to an open file, a pointer and a length. LuYanJun wrote: Can anybody give a simple example for domestrating ? I am puzzled by this topic, how does a music file be sotred in DB as BLOB type? TKS. - Original Message - From: "Alex Roston" <[EMAIL PROTECTED]> To: Sent: Saturday, October 28, 2006 4:35 AM Subject: Re: [sqlite] Music Files At one point there was a project that did something like this, and it was called Route66. I think it used mysql, perl and a player called splay. You might google it. Alex sebcity wrote: Is it possible to store mp3 files in a SSQLite database? would they be able to be played from the database? How would you save them? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Music Files
Hello John, Tuesday, November 28, 2006, 4:08:58 AM, you wrote: > sebcity wrote: >> Is it possible to store mp3 files in a SSQLite database? would they be able >> to be played from the database? How would you save them? > Write them as a BLOB. Are there Sqlite ODBC that support BLOB, or maybe other way to access blob without ODBC? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
I think, Through the command line mode it is impossible. For this you have to write a program which open and reads the (binary) data in file and inserts it to the sqlite database. Thanks, Lloyd On Tue, 2006-11-28 at 13:36 +0800, LuYanJun wrote: > Can anybody give a simple example for domestrating ? > I am puzzled by this topic, how does a music file be sotred in DB as BLOB > type? > > TKS. > - Original Message - > From: "Alex Roston" <[EMAIL PROTECTED]> > To: > Sent: Saturday, October 28, 2006 4:35 AM > Subject: Re: [sqlite] Music Files > > > > At one point there was a project that did something like this, and it > > was called Route66. I think it used mysql, perl and a player called > > splay. You might google it. > > > > Alex > > > > sebcity wrote: > > > >>Is it possible to store mp3 files in a SSQLite database? would they be able > >>to be played from the database? How would you save them? > >> > >> > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
Can anybody give a simple example for domestrating ? I am puzzled by this topic, how does a music file be sotred in DB as BLOB type? TKS. - Original Message - From: "Alex Roston" <[EMAIL PROTECTED]> To: Sent: Saturday, October 28, 2006 4:35 AM Subject: Re: [sqlite] Music Files > At one point there was a project that did something like this, and it > was called Route66. I think it used mysql, perl and a player called > splay. You might google it. > > Alex > > sebcity wrote: > >>Is it possible to store mp3 files in a SSQLite database? would they be able >>to be played from the database? How would you save them? >> >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > >
Re: [sqlite] Music Files
At one point there was a project that did something like this, and it was called Route66. I think it used mysql, perl and a player called splay. You might google it. Alex sebcity wrote: Is it possible to store mp3 files in a SSQLite database? would they be able to be played from the database? How would you save them? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
If you have BLOBs over 2GB in size you would not be storing them in some form of library or archive like Sqlite. You would want to use a more efficient storage mechanism with faster access, such as a directory of files. For storing modestly sized images and sound files and accessing them in special ways a BLOB is handy, but it would not be a good way to store movies and similar. To store BVLOBs (Binary Very Large Objects) use files with a link to them in the Sqlite DB. Nicolas Williams wrote: On Mon, Nov 27, 2006 at 02:40:44PM -0600, John Stanton wrote: You store them in the DB as a BLOB type, but save the data as a JPEG, MP3, WAV or whatever it happens to be. The binary data resides as a DB column and the same row may have other columns which could be text to describe the item or a number to store the size etc. But the API does not include any sort of streaming of BLOBs -- you can't just read the first 10MB of a BLOB then the next 10MB, then the next 1GB, etc. Also, the API uses "int" to represent BLOB sizes, so on ILP32 and LP64 platforms it should be limited to 2GB BLOBs, which is not that large. If you really want to store large BLOBs and "stream" them then you have to fragment them yourself, I gather. Bottom line: for very large blobs use files on a filesystem, not SQLite BLOBs. Cheers, Nico - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
On Mon, Nov 27, 2006 at 02:40:44PM -0600, John Stanton wrote: > You store them in the DB as a BLOB type, but save the data as a JPEG, > MP3, WAV or whatever it happens to be. The binary data resides as a DB > column and the same row may have other columns which could be text to > describe the item or a number to store the size etc. But the API does not include any sort of streaming of BLOBs -- you can't just read the first 10MB of a BLOB then the next 10MB, then the next 1GB, etc. Also, the API uses "int" to represent BLOB sizes, so on ILP32 and LP64 platforms it should be limited to 2GB BLOBs, which is not that large. If you really want to store large BLOBs and "stream" them then you have to fragment them yourself, I gather. Bottom line: for very large blobs use files on a filesystem, not SQLite BLOBs. Cheers, Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
You store them in the DB as a BLOB type, but save the data as a JPEG, MP3, WAV or whatever it happens to be. The binary data resides as a DB column and the same row may have other columns which could be text to describe the item or a number to store the size etc. sebcity wrote: Ok,so just save them as BLOB files? Igor Tandetnik wrote: sebcity <[EMAIL PROTECTED]> wrote: Is it possible to store mp3 files in a SSQLite database? would they be able to be played from the database? How would you save them? SQLite does not know anything nor care about music or MP3. It can store strings (e.g. file names), BLOBs (binary data, e.g. contents of said files) and numbers. You could retrieve the raw data from the database (or from the file referred to by the database, depending on your chosen storage scheme) and give it to something that would interpret the data as music and play it. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
Ok,so just save them as BLOB files? Igor Tandetnik wrote: > > sebcity <[EMAIL PROTECTED]> wrote: >> Is it possible to store mp3 files in a SSQLite database? would they >> be able to be played from the database? How would you save them? > > SQLite does not know anything nor care about music or MP3. It can store > strings (e.g. file names), BLOBs (binary data, e.g. contents of said > files) and numbers. You could retrieve the raw data from the database > (or from the file referred to by the database, depending on your chosen > storage scheme) and give it to something that would interpret the data > as music and play it. > > Igor Tandetnik > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Music-Files-tf2714035.html#a7567124 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Music Files
sebcity wrote: Is it possible to store mp3 files in a SSQLite database? would they be able to be played from the database? How would you save them? Write them as a BLOB. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Music Files
Is it possible to store mp3 files in a SSQLite database? would they be able to be played from the database? How would you save them? -- View this message in context: http://www.nabble.com/Music-Files-tf2714035.html#a7566722 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
