Re: [sqlite] Loading Sqlite3 DB into memory

[lau stephen]

Here has a sqlite3 MemVfs implementation.

http://code.google.com/p/sphivedb/downloads/list

spmemvfs-0.3.tar.gz


2010/5/20 Max Cat :
> Hi all,
>
> Is there any way to load a sqlite3 db from a location in memory? Ideally, I'd 
> like to have a memory pointer (eg, something provided via malloc), which I 
> could then use as the handle sqlite3 uses to load the database.
>
> The reason I'm trying to do this: I have an encrypted database file that I 
> will copy into memory, decrypt, and then process. I would like to do this 
> entirely in memory (no unencrypted files on the HD) to reduce the likelihood 
> of casual users snooping in the db file.
>
> If that makes no sense, let me know, and I'll try to clarify.
>
> Thanks in advance for your help! :)
>
> -Max
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Loading Sqlite3 DB into memory

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 05/20/2010 01:21 PM, Max Cat wrote:
> Thanks. In writing the VFS, I see several function pointers that don't appear 
> to have explanations. Can anyone help shed some light on what the following 
> are supposed to do (and what their parameters are)?
> 
>>From http://sqlite.org/c3ref/vfs.html
> 
>   void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename);
>   void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg);
>   void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void);
>   void (*xDlClose)(sqlite3_vfs*, void*);

They are similar to the dlopen/dlerror/dlsym/dlclose methods under Posix.
Check out those man pages.  I also have documentation for them in my Python
wrapper:

  http://apsw.googlecode.com/svn/publish/vfs.html#apsw.VFS.xDlClose

>   int (*xGetLastError)(sqlite3_vfs*, int, char *);
> 
> (The last one seems a bit self evident, but there was no documentation on it, 
> either.)

Don't bother implementing it.  It isn't called anywhere.

  http://apsw.googlecode.com/svn/publish/vfs.html#apsw.VFS.xGetLastError

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkv1qUQACgkQmOOfHg372QSmIACgk0o+05YBwg4fUdTO7LHgnvxM
LoYAoM3crfCQ+Jz8YLw29W7ISzzCYKdS
=Jzfn
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Loading Sqlite3 DB into memory

[Andreas Kupries]

Max Cat wrote:
> Thanks. In writing the VFS, I see several function pointers that don't appear 
> to have explanations. Can anyone help shed some light on what the following 
> are supposed to do (and what their parameters are)?
> 
> From http://sqlite.org/c3ref/vfs.html
> 
>   void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename);
>   void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg);
>   void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void);
>   void (*xDlClose)(sqlite3_vfs*, void*);

These seem to be abstractions of dlopen, dlsym, etc., i.e. the functions for 
dynamically loading shared libraries into a process, accessing its symbols, etc.

-- 
Andreas Kupries
Senior Tcl Developer
ActiveState, The Dynamic Language Experts

P: 778.786.1122
F: 778.786.1133
andre...@activestate.com
http://www.activestate.com
Get insights on Open Source and Dynamic Languages at www.activestate.com/blog
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Loading Sqlite3 DB into memory

[Max Cat]

Thanks. In writing the VFS, I see several function pointers that don't appear 
to have explanations. Can anyone help shed some light on what the following are 
supposed to do (and what their parameters are)?

>From http://sqlite.org/c3ref/vfs.html

  void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename);
  void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg);
  void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void);
  void (*xDlClose)(sqlite3_vfs*, void*);
  int (*xGetLastError)(sqlite3_vfs*, int, char *);

(The last one seems a bit self evident, but there was no documentation on it, 
either.)

Thanks again! :)

-Max

--- On Thu, 5/20/10, Pavel Ivanov  wrote:

From: Pavel Ivanov 
Subject: Re: [sqlite] Loading Sqlite3 DB into memory
To: "General Discussion of SQLite Database" 
Date: Thursday, May 20, 2010, 5:13 AM

You can write your own VFS and then use it to open your encrypted
database file. On open VFS will read file into memory and decrypt it.
Then on each SQLite's request to read the file VFS will copy necessary
part from the unencrypted location to the buffer provided by SQLite...
It should work exactly as you want except some memory copying on each
attempt to read...


Pavel

On Wed, May 19, 2010 at 5:17 PM, Max Cat  wrote:
> Hi all,
>
> Is there any way to load a sqlite3 db from a location in memory? Ideally, I'd 
> like to have a memory pointer (eg, something provided via malloc), which I 
> could then use as the handle sqlite3 uses to load the database.
>
> The reason I'm trying to do this: I have an encrypted database file that I 
> will copy into memory, decrypt, and then process. I would like to do this 
> entirely in memory (no unencrypted files on the HD) to reduce the likelihood 
> of casual users snooping in the db file.
>
> If that makes no sense, let me know, and I'll try to clarify.
>
> Thanks in advance for your help! :)
>
> -Max
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Loading Sqlite3 DB into memory

[Jean-Christophe Deschamps]

>Is there any way to load a sqlite3 db from a location in memory? 
>Ideally, I'd like to have a memory pointer (eg, something provided via 
>malloc), which I could then use as the handle sqlite3 uses to load the 
>database.
>
>The reason I'm trying to do this: I have an encrypted database file 
>that I will copy into memory, decrypt, and then process. I would like 
>to do this entirely in memory (no unencrypted files on the HD) to 
>reduce the likelihood of casual users snooping in the db file.

You can use the backup API to make a copy of the disk DB to a :memory: 
DB and work on this one.  You won't have any memory housekeeping to do: 
everything is handled by SQLite.  Drawback or advantage, depending on 
your precise context: you can't share your memory DB with other processes.

As I understand it, you won't be using the disk DB by itself, so you 
can "backup" the baby in one shot as there is no concurrency in your 
situation.
This can be made to work in only a handful of simple calls to the 
SQLite API.

Another possibility is use some RAMdisk driver for your OS and copy the 
disk file there, but then be aware that it might then be easier for 
curious people to dissect your DB.  To overcome this, you'll need to 
wipe the RAMdisk file as well, adding more complexity to your code.

I'd favor the backup way, much simpler.

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

Re: [sqlite] Accessing SQLite from PHP5?

[Gilles Ganault]

On Thu, 20 May 2010 11:42:31 +0100, Simon Slavin
 wrote:
>Releases of SQLite are far more frequent than new releases of PHP, so the 
>answer won't help you much.

Thanks a bunch for the links.

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

Re: [sqlite] Loading Sqlite3 DB into memory

[Pavel Ivanov]

You can write your own VFS and then use it to open your encrypted
database file. On open VFS will read file into memory and decrypt it.
Then on each SQLite's request to read the file VFS will copy necessary
part from the unencrypted location to the buffer provided by SQLite...
It should work exactly as you want except some memory copying on each
attempt to read...


Pavel

On Wed, May 19, 2010 at 5:17 PM, Max Cat  wrote:
> Hi all,
>
> Is there any way to load a sqlite3 db from a location in memory? Ideally, I'd 
> like to have a memory pointer (eg, something provided via malloc), which I 
> could then use as the handle sqlite3 uses to load the database.
>
> The reason I'm trying to do this: I have an encrypted database file that I 
> will copy into memory, decrypt, and then process. I would like to do this 
> entirely in memory (no unencrypted files on the HD) to reduce the likelihood 
> of casual users snooping in the db file.
>
> If that makes no sense, let me know, and I'll try to clarify.
>
> Thanks in advance for your help! :)
>
> -Max
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Introspection and RowID / INTEGER PRIMARY KEY

[Andrew Rodland]

> On 19 May 2010, at 12:05pm, Andrew Rodland wrote:
> > Is there any possibility that in a future version of SQLite, pragma
> > table_info or some other pragma could return a simple boolean "this
> > column is the rowid" indicator? As demonstrated under "Goofy Behavior
> > Alert" at
> > http://www.sqlite.org/lang_createtable.html#rowid it is possible (if
> > unlikely) for a column to appear as type='INTEGER' and pk=1 in the
> > pragma table_info results and yet *not* be the rowid.
> 
> Sometimes no columns are the rowid.  For instance, any case where there are
> no suitable columns:
> 
> CREATE TABLE texts(thisText TEXT)
> 
> This table does still have a rowid, but you can't access it using any of
> the columns you've declared.  Rather than find out what SQLite has done
> internally to create a rowid, might it be possible for you to refer
> explicitly to the column "_ROWID_" in your code ?  Unless this name is
> used in the TABLE definition, it is guaranteed to return the rowid values.
> 
> SELECT _rowid_, * FROM texts

I understand that, but that's not what I need. I'm working on the code that 
loads a database schema into an ORM, and I need to properly set the flag 
"is_auto_increment" which is used to identify a column that will be 
automatically set by the DB. I'm not looking to access the rowid directly, I 
truly am just looking for information about a specific column -- will it have 
a certain behavior or not? 
In SQLite at present the question is equivalent to "is it the INTEGER PRIMARY 
KEY as defined at http://www.sqlite.org/lang_createtable.html#rowid ?" which 
is equivalent to "is it an alias of the rowid?" So that's the question I'm 
looking to be able to answer.

Thanks again

Andrew

P.S. I'd appreciate it if you could CC me on replies, as I'm not on the list 
and having to copy/paste from GMANE is a bit lame and breaks threading :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Loading Sqlite3 DB into memory

[Max Cat]

Hi all,

Is there any way to load a sqlite3 db from a location in memory? Ideally, I'd 
like to have a memory pointer (eg, something provided via malloc), which I 
could then use as the handle sqlite3 uses to load the database.

The reason I'm trying to do this: I have an encrypted database file that I will 
copy into memory, decrypt, and then process. I would like to do this entirely 
in memory (no unencrypted files on the HD) to reduce the likelihood of casual 
users snooping in the db file.

If that makes no sense, let me know, and I'll try to clarify. 

Thanks in advance for your help! :)

-Max



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

Re: [sqlite] Accessing SQLite from PHP5?

[Simon Slavin]

On 20 May 2010, at 10:42am, Gilles Ganault wrote:

> Thanks for the tip. Does someone know if this direct access to SQLite
> is kept up-to-date with SQLite?

Releases of SQLite are far more frequent than new releases of PHP, so the 
answer won't help you much.  In addition, there's a policy about which version 
the three ways of using SQLite functions implement.  See the first comment, by 
Andrew Paul Dickey, on



There is documentation for direct support for SQLite version 3 at




You can find the version of SQLite supported by the sqlite_ calls using



You can find the version of SQLite supported by the sqlite3:: calls using



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

Re: [sqlite] Accessing SQLite from PHP5?

[Gilles Ganault]

On Wed, 19 May 2010 20:50:01 -0400, "J. King"
 wrote:
>FYI, PHP 5.3 provides an SQLite3 module  which  
>offers a version of SQLite more up-to-date than that exposed by PDO.   
>Whereas PDO is an abstracted interface, though, SQLite3 appears to be a  
>slightly more direct mapping of the SQLite API, exposing more  
>SQLite-specific features.

Thanks for the tip. Does someone know if this direct access to SQLite
is kept up-to-date with SQLite?

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