[sqlite] Using SQLite for storing photo's

[Ketil Froyn]

I would definitely store the pictures in separate files. The main reason is
compatibility. If you store them as blobs in sqlite, you have to extract
them first to view or edit them in a another program.i Are you sure you've
covered all your use cases with your program?

Ketil
On 14 Apr 2016 7:37 p.m., "Cecil Westerhof"  wrote:

> I am thinking about storing my photo's in SQLite. This has to be done in a
> blob of-course, but I was wondering if there are any caveats, or if anyone
> has helpful tips.
>
> One thing I was wondering: what is the best way to make a backup? I would
> think a normal dump is not very handy. It would probably better to store
> the pictures them-self in one file pro picture and only store the
> (meta-)data in SQL. What would be a good way?
>
> --
> Cecil Westerhof
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Using SQLite for storing photo's

[R Smith]

On 2016/04/14 10:18 PM, Cecil Westerhof wrote:
> 2016-04-14 22:07 GMT+02:00 Clemens Ladisch :
>
>> Cecil Westerhof wrote:
>>> what is the best way to make a backup?
>> With the backup API: .
>> (Also available as .backup in the shell.)
>>
> ?I should be more precise in my communication. :'-(
>
> I prefer to make my backup with .dump. Then I have just a text based
> backup. This works fine for the regular data, but not for photos: they can
> be 13 MB big. (And I am not even using raw. When I am going to do that,
> they will become much bigger.) So I would like to have my photos split out
> when making the dump.
>
> But maybe it will be better to use external blobs.
>
> Something to think about.
>

The BLOBS and data inside SQLite is quite safe, and the backup API does 
a magnificent job, why would you prefer text dumps to backed up DB's?

Either way, you do not need to use the .dump to make data dumps, you can 
quite easily push data out using a query in which you can SELECT the 
fields you'd like, sans those you do not wish to dump. The caveat here 
is that you would not be able to reconstruct the DB perfectly from the 
dump, but then if you don't dump the BLOBs, that was never going to 
happen anyway.

I know from the old days with other DB engines, text dumps made one feel 
safe because nobody really knew what magic went on inside the files the 
DB engine used. With SQLite it's quite different though, it's very open, 
there's a myriad things that can read SQLite DBs, including the always 
free always available sqlite3.exe from the web site, and even some 
people here have data recovery systems for SQLite if you do succeed in 
hurting a DB file. Apart from maliciousness, there's really nothing that 
can hurt an SQLite file that cannot also hurt a text file - I don't 
think there is any legitimate reason to prefer text dumps over SQLite 
files anymore (especially for backups) - but that's up to you.

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

2016-04-14 22:07 GMT+02:00 Clemens Ladisch :

> Cecil Westerhof wrote:
> > what is the best way to make a backup?
>
> With the backup API: .
> (Also available as .backup in the shell.)
>

?I should be more precise in my communication. :'-(

I prefer to make my backup with .dump. Then I have just a text based
backup. This works fine for the regular data, but not for photos: they can
be 13 MB big. (And I am not even using raw. When I am going to do that,
they will become much bigger.) So I would like to have my photos split out
when making the dump.

But maybe it will be better to use external blobs.

Something to think about.

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

2016-04-14 21:37 GMT+02:00 Simon Slavin :

>
> On 14 Apr 2016, at 8:10pm, Cecil Westerhof  wrote:
>
> > ?I would like to have everything in one file.
>
> From previous discussions on this list, what you want to do will work
> fine.  Of course you should make sure your operating system is able to deal
> with a very big file, and that your backup protocol works.
>

?Well, I will try. I have a ?few? applications that I want to write using
SQLite, so I will not get bored for some time. :-D

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Teg]

Hello Cecil,

I  have multiple 30-40 GB Sqlite DB's which contain images as blobs. I
back   them   up   by   copying   them  to  my  backup  drive.  Unless
your application runs 24x7, I see no reason to do anything complicated with
the backup.

My  image  blobs are in a table which contains an image and a rowid to
ID  the  image. The meta data is all in a different table. It would be
simply  to  split  this  out into multiple DB's but from a performance
perspective,  I  don't  see  the point. It's plenty fast enough for my
usage.

I'm  not  using  the Blob IO routines. I just copy them into memory in
one  go  when  I  need  them.  If  my machine was slower I might do it
differently.  As long as I can retrieve and view the images as fast as
I  can  page them, I'm satisfied.  I do have export routines to export
them as images or CBR/CBZ files. 

I like them all in one file too.

C


Thursday, April 14, 2016, 4:18:58 PM, you wrote:

CW> 2016-04-14 22:07 GMT+02:00 Clemens Ladisch :

>> Cecil Westerhof wrote:
>> > what is the best way to make a backup?
>>
>> With the backup API: .
>> (Also available as .backup in the shell.)
>>

CW> ?I should be more precise in my communication. :'-(

CW> I prefer to make my backup with .dump. Then I have just a text based
CW> backup. This works fine for the regular data, but not for photos: they can
CW> be 13 MB big. (And I am not even using raw. When I am going to do that,
CW> they will become much bigger.) So I would like to have my photos split out
CW> when making the dump.

CW> But maybe it will be better to use external blobs.

CW> Something to think about.




-- 
 Tegmailto:Teg at djii.com

[sqlite] Using SQLite for storing photo's

[Clemens Ladisch]

Cecil Westerhof wrote:
> what is the best way to make a backup?

With the backup API: .
(Also available as .backup in the shell.)


Regards,
Clemens

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

2016-04-14 19:41 GMT+02:00 Simon Slavin :

>
> On 14 Apr 2016, at 6:37pm, Cecil Westerhof  wrote:
>
> > I am thinking about storing my photo's in SQLite.
>
> Doesn't answer your question, but you should definitely read
>
> 
>

?I would like to have everything in one file. Performance is not very high
on my list, because it will be a single user application. But it is
something to think about?.

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Simon Slavin]

On 14 Apr 2016, at 8:10pm, Cecil Westerhof  wrote:

> ?I would like to have everything in one file.

[sqlite] Using SQLite for storing photo's

[Cecil Westerhof]

I am thinking about storing my photo's in SQLite. This has to be done in a
blob of-course, but I was wondering if there are any caveats, or if anyone
has helpful tips.

One thing I was wondering: what is the best way to make a backup? I would
think a normal dump is not very handy. It would probably better to store
the pictures them-self in one file pro picture and only store the
(meta-)data in SQL. What would be a good way?

-- 
Cecil Westerhof

[sqlite] Using SQLite for storing photo's

[Simon Slavin]

On 14 Apr 2016, at 6:37pm, Cecil Westerhof  wrote:

> I am thinking about storing my photo's in SQLite.

Doesn't answer your question, but you should definitely read



Simon.