Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

[_ph_]

Another option: Copy the meta-data to an in-memory-DB. As you "load all meta
data into memory" anyway this seems a viable option.

ATTACH ':memory:' AS mem
INSERT INTO mem.Table SELECT * FROM Table

(repeat for each table, you may want to create/recreate constraint and
indices in the attached DB, of course)

During this copy, you can make the schema adjustments. 

---

I'm storing both raw and meta data in an SQLite DB, create that
in-memory-copy only for the meta data, and use this as the
in-memory-representation - no separate C++ class graph for the data
structure. 

(There's basically a C++ class for each table holding one row, and fille on
demand from the in memory db. It's not blazingly fast - you can quickly
accumulate a few ms for a bunch of queries - but good enough certainly, and
fun to use.)

When creating that "snapshot", I make adjustments to the schema, so read
compatibility is in one place. 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/How-to-upgrade-a-DB-without-write-access-to-its-file-or-to-the-direction-it-s-in-tp70408p70574.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

[Dominique Devienne]

On Wed, Aug 7, 2013 at 6:36 PM, Richard Hipp  wrote:

> On Wed, Aug 7, 2013 at 12:15 PM, Dominique Devienne  >wrote:
>
> > On Wed, Aug 7, 2013 at 2:45 PM, Clemens Ladisch 
> > wrote:
> >
> > > Dominique Devienne wrote:
> > > > We can of course copy the db file somewhere else with r/w access, or
> > copy
> > > > the DB into an in-memory DB (for each table, create table memdb.foo
> as
> > > > select * from dskdb.foo) and upgrade and read that one instead, but I
> > was
> > > > wondering whether there's another better solution we could use?
> > >
> > > You can use the backup API to copy an entire database at once:
> > > 
> > >
> >
> > Thanks. That's more efficient and less code for sure, and what we're
> using
> > now.
> >
> > I just thought there might be a different trick possible to avoid
> > duplicating the whole DB, like forcing the journal to be in-memory, or
> > using WAL instead, or something.
> >
> > If that's the best we can do, then so be it.
> >
>
> That's probably about the best that is built-in.  However...
>
> You could write a "shim" VFS to fake a filesystem that appears to provide
> read/write semantics but which really only reads.  All writes would be
> stored in memory and would be forgotten the moment you close the database
> connection.
>

Thanks for the suggestion Richard. I didn't think of that, and that would
indeed reduce our memory footprint.

We'll keep using the backup API for now, in our patch release, but will
definitely investigate using a VFS in the future instead, for our next
feature release.

Thanks again, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

[Richard Hipp]

On Wed, Aug 7, 2013 at 12:15 PM, Dominique Devienne wrote:

> On Wed, Aug 7, 2013 at 2:45 PM, Clemens Ladisch 
> wrote:
>
> > Dominique Devienne wrote:
> > > We can of course copy the db file somewhere else with r/w access, or
> copy
> > > the DB into an in-memory DB (for each table, create table memdb.foo as
> > > select * from dskdb.foo) and upgrade and read that one instead, but I
> was
> > > wondering whether there's another better solution we could use?
> >
> > You can use the backup API to copy an entire database at once:
> > 
> >
>
> Thanks. That's more efficient and less code for sure, and what we're using
> now.
>
> I just thought there might be a different trick possible to avoid
> duplicating the whole DB, like forcing the journal to be in-memory, or
> using WAL instead, or something.
>
> If that's the best we can do, then so be it.
>

That's probably about the best that is built-in.  However...

You could write a "shim" VFS to fake a filesystem that appears to provide
read/write semantics but which really only reads.  All writes would be
stored in memory and would be forgotten the moment you close the database
connection.

The "VFS" is an object that sets in between the SQLite core and the
operating system and provides a uniform interface to operating-system
services.  SQLite comes with built-in VFSes for unix and windows.  But you
can add additional VFSes at run-time.  A "shim" VFS is one that passes most
of the work through to one of the original VFSes but modifies some of the
requests.

So, your shim VFS would keep an in-memory record of all modified database
pages.  Read requests first check this modification cache and reply from it
if present, otherwise it passes the read request down to the original
(real) VFS.  Write requests simply update the modification cache.



>
> The reason I'd rather avoid the full copy is that the tables upgraded
> typically will update a tiny subset of the DB, which I've seen as large as
> 100 MB. So rather than duping a 100 MB disk DB into a 100 MB memory DB so I
> can select from it (at the proper schema version) so I can populate my C++
> data structures (likely smaller than 100 MB, but still in the 20-50 MB
> range), that adds quite a bit of memory consumption, while the journal
> itself would grow to only a few MBs.
>
> Thanks again for the suggestion though. --DD
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

[Dominique Devienne]

On Wed, Aug 7, 2013 at 2:45 PM, Clemens Ladisch  wrote:

> Dominique Devienne wrote:
> > We can of course copy the db file somewhere else with r/w access, or copy
> > the DB into an in-memory DB (for each table, create table memdb.foo as
> > select * from dskdb.foo) and upgrade and read that one instead, but I was
> > wondering whether there's another better solution we could use?
>
> You can use the backup API to copy an entire database at once:
> 
>

Thanks. That's more efficient and less code for sure, and what we're using
now.

I just thought there might be a different trick possible to avoid
duplicating the whole DB, like forcing the journal to be in-memory, or
using WAL instead, or something.

If that's the best we can do, then so be it.

The reason I'd rather avoid the full copy is that the tables upgraded
typically will update a tiny subset of the DB, which I've seen as large as
100 MB. So rather than duping a 100 MB disk DB into a 100 MB memory DB so I
can select from it (at the proper schema version) so I can populate my C++
data structures (likely smaller than 100 MB, but still in the 20-50 MB
range), that adds quite a bit of memory consumption, while the journal
itself would grow to only a few MBs.

Thanks again for the suggestion though. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

[Clemens Ladisch]

Dominique Devienne wrote:
> We can of course copy the db file somewhere else with r/w access, or copy
> the DB into an in-memory DB (for each table, create table memdb.foo as
> select * from dskdb.foo) and upgrade and read that one instead, but I was
> wondering whether there's another better solution we could use?

You can use the backup API to copy an entire database at once:



Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to "upgrade" a DB without write access to its file, or to the direction it's in?

[Dominique Devienne]

We use SQLite for persistence of our project meta-data (the data itself is
still in a proprietary binary format). For now, on project load, the
meta-data DB file is read in full into memory (C++ objects) then closed,
and re-written from scratch on save or save-as, instead of trying to update
it in place.

When loading an older project, we need to upgrade (within a transaction)
the meta-data DB to the schema version understood by the current
application, load the upgraded DB, then rollback the DB file, to leave the
original project untouched.

That's working fine, except when the user opening the project has read-only
access to the DB-file (case#1) or even to the directory the DB file is in
(case#2).

We run in normal-mode, not wal-mode, i.e. SQLite creates a journal file on
the upgrade, and possibly also changes the .db itself (to record the
ongoing transaction? I don't know). But in the read-only cases described
above, the upgrade fails, and the user thus cannot open the project.

My question is then whether there's a way to perform the to-be-rolled-back
upgrade in such a way to not run afoul of the read-only file/dir
permissions?

We can of course copy the db file somewhere else with r/w access, or copy
the DB into an in-memory DB (for each table, create table memdb.foo as
select * from dskdb.foo) and upgrade and read that one instead, but I was
wondering whether there's another better solution we could use?

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users