Re: [sqlite] Music Files

2006-11-29 Thread LuYanJun
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: <sqlite-users@sqlite.org>
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

2006-11-28 Thread John Stanton
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

2006-11-28 Thread made hendra
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

2006-11-28 Thread John Stanton
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

2006-11-28 Thread made hendra
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

2006-11-28 Thread John Stanton

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

2006-11-28 Thread Isaac Raway

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

2006-11-28 Thread John Stanton
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: <sqlite-users@sqlite.org>
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

2006-11-27 Thread made hendra
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

2006-11-27 Thread Lloyd
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: <sqlite-users@sqlite.org>
> 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

2006-11-27 Thread LuYanJun
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: <sqlite-users@sqlite.org>
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

2006-11-27 Thread Alex Roston
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

2006-11-27 Thread John Stanton
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

2006-11-27 Thread Nicolas Williams
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

2006-11-27 Thread John Stanton
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

2006-11-27 Thread sebcity

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

2006-11-27 Thread John Stanton

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

2006-11-27 Thread sebcity

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]
-