>We are interested in using sqlite as a read only, in memory, parallel
>access database.  We have database files that are on the order of 100GB
>that we are loading into memory.  We have found great performance when
>reading from a single thread.  We need to scale up to have many parallel
>reader threads.  Once the DB is created it never needs to be modified.  How
>can we allow many reader threads on an in memory, write once read many
>times database and achieve multi-core performance?  Is this possible with
>sqlite?

Thanks for all the helpful responses.  I have moved forward experimenting
with using parallel readers on an in memory sqlite database.  I have found
that I get true concurrency (multi-core speed up) when I create a new
connection to my database file on disk in every thread.  I've verified this
by running a single long query and then running the same query in several
threads and ensuring the net time is the same as the single thread query
time.

In order to get parallel readers on an in memory database I first loaded
the file into memory with:
rc = sqlite3_open_v2("file::memory:?cache=shared", &db, SQLITE_OPEN_URI |
SQLITE_OPEN_READWRITE, NULL);
I hold onto the db reference in the main thread after loading the data and
don't close that connection until all the worker threads are done
attempting to run my long query.

I then spawn N threads, each creating their own connection like so:
rc = sqlite3_open_v2("file::memory:?cache=shared", &db, SQLITE_OPEN_URI |
SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX, NULL);
I have experimented with the various flags; however, everything I do gives
me serial performance.  There is some kind of mutex locking the database so
that running my query say two times takes twice as long as running it once
whereas with the disk based approach using this connection string in each
thread:
rc = sqlite3_open("data.sl3", &db);
makes the total time to run the query twice the same as running it just
once.

I am hypothesizing that since we are using 'cache=shared' and hence each
thread is sharing the same cache each read requires locking the database.
What I would like is to get the same kind of behavior as we get with "
file::memory:?cache=shared" wherein every time I open a new connection that
connection points to the same memory; however, does not actually involve
sharing the cache so no global mutex locks the database on every read.

I have put my test code in a gist.
My C code is here:
https://gist.github.com/danielrmeyer/fae54d5993f2800626c616e72782b5eb
I generate the 1.5GB test database with this python 3.4 script:
https://gist.github.com/danielrmeyer/bfa415256502471d1512f2155e76adc2

I compiled the C code on my system with the following command:
gcc -std=gnu99 test.c -o test -lsqlite3 -lpthread (I did download the
amalgamation and copied the sqlite.h and sqlite.o files into my cwd after
building)

I apologize if the C code is not as clean as it could be.  I'm primarily a
Python programmer but figured i'd be more likely to get help with a C test
case so I did my best to hack this together.  The Python GIL was confusing
the situation in any case.

A little background on what I am doing:  I have several large datasets that
I wish to serve up to customers to generate custom reports based on unique
slices of the data.  I am using a cluster of machines with .5TB of memory
each so loading all the data into memory is reasonable in my case.  I've
found that against my production work load I get massive speedups in single
threaded tests against the in memory database relative to the disk
version.  In fact I have found that the single threaded sqlite in memory
tests are faster than all the other database solutions i've looked at so I
am very excited about using sqlite, nevertheless I really need to scale to
many cores.  Also, my work load is highly random so cache is not much
help.  I really want the data in memory.  Any help is greatly appreciated.
I have started experimenting with memory mapped io; however, I have not had
much luck so far.

I would not mind creating a fork of sqlite on github and hacking the code
if someone could give me pointers on what needs to be modified to get this
working.  Certainly if there is an extra flag or URI I need to use to get
concurrent in memory read access that would be great, but I'm willing to
try and modify the source code and sharing with the community if I can
figure out how to get this going.

On Sat, Oct 8, 2016 at 5:00 AM, <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:

> Send sqlite-users mailing list submissions to
>         sqlite-users@mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>         http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/
> sqlite-users
> or, via email, send a message with subject or body 'help' to
>         sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
>         sqlite-users-ow...@mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>    1. Parallel access to read only in memory database (Daniel Meyer)
>    2. Re: Parallel access to read only in memory database
>       (Joe Mistachkin)
>    3. Re: Parallel access to read only in memory database (Simon Slavin)
>    4. Re: Parallel access to read only in memory database
>       (Stephen Chrzanowski)
>    5. Re: Parallel access to read only in memory database
>       (Keith Medcalf)
>    6. Protecting databases (Damien Sykes-Lindley)
>    7. Re: Protecting databases (Darren Duncan)
>    8. Re: Protecting databases (Damien Sykes-Lindley)
>    9. Re: Protecting databases (Darren Duncan)
>   10. Re: Protecting databases (Darren Duncan)
>   11. Re: Protecting databases (Jean-Christophe Deschamps)
>   12. Re: Protecting databases (R Smith)
>   13. Re: Protecting databases (Damien Sykes-Lindley)
>   14. Error trying to make sqlite3 documentation
>       (Domingo Alvarez Duarte)
>   15. Re: Error trying to make sqlite3 documentation (Richard Hipp)
>   16. Fwd: Lemon: Simple recursive rule causes assertion failed:
>       stateno <= YY_SHIFT_COUNT (Conor O)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Fri, 7 Oct 2016 13:37:47 -0700
> From: Daniel Meyer <meye1...@gmail.com>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Parallel access to read only in memory database
> Message-ID:
>         <CAFcY+X65nGF+jw6u+yFA0fChsEBkfNFoDy-=+NmP86cVrUE
> 9...@mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> We are interested in using sqlite as a read only, in memory, parallel
> access database.  We have database files that are on the order of 100GB
> that we are loading into memory.  We have found great performance when
> reading from a single thread.  We need to scale up to have many parallel
> reader threads.  Once the DB is created it never needs to be modified.  How
> can we allow many reader threads on an in memory, write once read many
> times database and achieve multi-core performance?  Is this possible with
> sqlite?
>
>
> ------------------------------
>
> Message: 2
> Date: Fri, 7 Oct 2016 13:45:56 -0700
> From: "Joe Mistachkin" <sql...@mistachkin.com>
> To: "'SQLite mailing list'" <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Parallel access to read only in memory database
> Message-ID: <630C1D80247B4261AEFA40822A7605A0@LACHRYMOSE>
> Content-Type: text/plain;       charset="us-ascii"
>
>
> Daniel Meyer wrote:
> >
> > How can we allow many reader threads on an in memory, write once read
> many
> > times database and achieve multi-core performance?  Is this possible with
> > sqlite?
> >
>
> Have you tried using the URI "file::memory:?cache=shared" with one of the
> sqlite3_open*() C APIs?  Further details on using URI file names may be
> found here:
>
>         https://www.sqlite.org/uri.html
>
> --
> Joe Mistachkin @ https://urn.to/r/mistachkin
>
>
>
> ------------------------------
>
> Message: 3
> Date: Fri, 7 Oct 2016 21:48:51 +0100
> From: Simon Slavin <slav...@bigfraud.org>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Parallel access to read only in memory database
> Message-ID: <3a3bf749-3164-4d99-9494-0be186f46...@bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 7 Oct 2016, at 9:37pm, Daniel Meyer <meye1...@gmail.com> wrote:
>
> > We have database files that are on the order of 100GB [...] in memory
>
> You have 100GB memory ?
>
> Simon.
>
>
> ------------------------------
>
> Message: 4
> Date: Fri, 7 Oct 2016 17:03:24 -0400
> From: Stephen Chrzanowski <pontia...@gmail.com>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Parallel access to read only in memory database
> Message-ID:
>         <CAANptrH4CK+17piiaPT+vw0HqFNjr4ULwotuJyLfy8jKFwyOUA
> @mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> (My two cents) I just setup two brand new machines in our Colo for ESX.
> Both machines had 256gig of memory.  Not unheard of in server situations.
> ;)
>
> On Fri, Oct 7, 2016 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
> >
> > On 7 Oct 2016, at 9:37pm, Daniel Meyer <meye1...@gmail.com> wrote:
> >
> > > We have database files that are on the order of 100GB [...] in memory
> >
> > You have 100GB memory ?
> >
> > Simon.
> > ___________
> >
>
>
> ------------------------------
>
> Message: 5
> Date: Fri, 07 Oct 2016 17:54:42 -0600
> From: "Keith Medcalf" <kmedc...@dessus.com>
> To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Parallel access to read only in memory database
> Message-ID: <7457e4ca108e2a44b24c1e88fa74b...@mail.dessus.com>
> Content-Type: text/plain; charset="utf-8"
>
>
> Machines with >100GB of RAM have been commonplace for a several years.
> These days, 384 GB is quite common.
>
> Even 1 TB is not a "special build" anymore -- you can buy them "off the
> shelf" from Dell ... (Dell no longer makes custom machines but only sells
> fixed configurations off the boat from china)
>
> > -----Original Message-----
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Simon Slavin
> > Sent: Friday, 7 October, 2016 14:49
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Parallel access to read only in memory database
> >
> >
> > On 7 Oct 2016, at 9:37pm, Daniel Meyer <meye1...@gmail.com> wrote:
> >
> > > We have database files that are on the order of 100GB [...] in memory
> >
> > You have 100GB memory ?
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
>
> ------------------------------
>
> Message: 6
> Date: Sat, 8 Oct 2016 06:46:02 +0100
> From: "Damien Sykes-Lindley" <dam...@dcpendleton.plus.com>
> To: <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] Protecting databases
> Message-ID: <AF793AC5F89843649D24A0AC236B2CC7@Psycheman>
> Content-Type: text/plain;       charset="utf-8"
>
> Hi there,
> My name is Damien Lindley, and I am, among other things, an independent,
> hobbiest programmer. I have been blind since birth and thus all my computer
> work relies on screenreader software and keyboard.
> I have only just come through the brink of scripting into compiled
> programming and so I guess I am still a beginner in many respects. However
> I don’t work in C or C++, so most of my programming, if using a library,
> relies on precompiled static or dynamic libraries. Or of course libraries
> that are written or converted specifically for the language I work in
> (FreeBASIC).
> Recently, I decided I needed to create a piece of software that could
> manage family trees, since there seems to be a lack of screenreader
> accessible genealogy managers out there. I was advised the best way to do
> this is to use a database engine. I was also informed that SQLite is always
> a good choice for databases.
> I must admit, I have never worked with databases before and so now I am in
> the process of learning SQL. However looking at the programming API for
> SQLite I cannot see any means of password protecting the database without
> either buying a commercial extension to do this, or recompiling SQLite with
> the authentication extension. Due to financial constraints and
> unfamiliarity with compiling in C both of these are not an option for me.
> Also I need a secure way to do this, as I think I read that the SQLite
> version simply uses a table to store the user data, which of course can be
> read and accessed elsewhere.
> Are there any other options available for doing this?
> Any help appreciated.
> Thanks.
> Damien.
>
>
> ------------------------------
>
> Message: 7
> Date: Fri, 7 Oct 2016 22:54:36 -0700
> From: Darren Duncan <dar...@darrenduncan.net>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Protecting databases
> Message-ID: <57f88a1c.5020...@darrenduncan.net>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:
> > Hi there,
> > My name is Damien Lindley, and I am, among other things, an independent,
> hobbiest programmer. I have been blind since birth and thus all my computer
> work relies on screenreader software and keyboard.
> > I have only just come through the brink of scripting into compiled
> programming and so I guess I am still a beginner in many respects. However
> I don’t work in C or C++, so most of my programming, if using a library,
> relies on precompiled static or dynamic libraries. Or of course libraries
> that are written or converted specifically for the language I work in
> (FreeBASIC).
> > Recently, I decided I needed to create a piece of software that could
> manage family trees, since there seems to be a lack of screenreader
> accessible genealogy managers out there. I was advised the best way to do
> this is to use a database engine. I was also informed that SQLite is always
> a good choice for databases.
> > I must admit, I have never worked with databases before and so now I am
> in the process of learning SQL. However looking at the programming API for
> SQLite I cannot see any means of password protecting the database without
> either buying a commercial extension to do this, or recompiling SQLite with
> the authentication extension. Due to financial constraints and
> unfamiliarity with compiling in C both of these are not an option for me.
> Also I need a secure way to do this, as I think I read that the SQLite
> version simply uses a table to store the user data, which of course can be
> read and accessed elsewhere.
> > Are there any other options available for doing this?
> > Any help appreciated.
> > Thanks.
> > Damien.
>
> Damien,
>
> Why do you need to password protect the database?
>
> Genealogy information is generally of the public record variety so there is
> nothing sensitive to protect.  I am making genealogy software myself and
> so am
> familiar with many of the relevant issues.
>
> I would say please explain why you think you need password protection for
> this
> project and then the real issue at hand can be addressed.
>
> If yours is a network application and you don't want people on the open
> internet
> from accessing the database, fair enough, but that's an application-level
> solution; what you're asking for here is that people who have direct
> access to
> the SQLite database file are blocked by a password, and this I question.
>
> -- Darren Duncan
>
>
>
> ------------------------------
>
> Message: 8
> Date: Sat, 8 Oct 2016 08:18:12 +0100
> From: "Damien Sykes-Lindley" <dam...@dcpendleton.plus.com>
> To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Protecting databases
> Message-ID: <B69633C98BDF440FB8C0FA9F040B1C6F@Psycheman>
> Content-Type: text/plain; format=flowed; charset="utf-8";
>         reply-type=response
>
> Hi Darren,
> You are correct in that genealogy is generally public. However more often
> than not the information you want to publish may very well differ from what
> is in your private database. You may have private notes telling you what
> you
> need to do. You may have anecdotes shared by many family members that may
> need to be kept private, at least until the involved parties are deceased
> or
> otherwise choose to divulge it publicly themselves.
> Even more importantly I may choose to add an address-book style feature in
> there so you can easily group and contact appropriate family members for
> whatever reason (special occasions etc). Of course that will be private.
> Password protecting it is also good on many levels - if the database is to
> be used online then it is needless to say that authentication would be
> required for various people to view it. Even if I decide to make it local
> only, there is the possibility that anyone sharing the computer or network
> may peruse the database when you don't want them to.
> Kind regards,
> Damien.
> -----Original Message-----
> From: Darren Duncan
> Sent: Saturday, October 08, 2016 6:54 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Protecting databases
>
> On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:
> > Hi there,
> > My name is Damien Lindley, and I am, among other things, an independent,
> > hobbiest programmer. I have been blind since birth and thus all my
> > computer work relies on screenreader software and keyboard.
> > I have only just come through the brink of scripting into compiled
> > programming and so I guess I am still a beginner in many respects.
> However
> > I don’t work in C or C++, so most of my programming, if using a library,
> > relies on precompiled static or dynamic libraries. Or of course libraries
> > that are written or converted specifically for the language I work in
> > (FreeBASIC).
> > Recently, I decided I needed to create a piece of software that could
> > manage family trees, since there seems to be a lack of screenreader
> > accessible genealogy managers out there. I was advised the best way to do
> > this is to use a database engine. I was also informed that SQLite is
> > always a good choice for databases.
> > I must admit, I have never worked with databases before and so now I am
> in
> > the process of learning SQL. However looking at the programming API for
> > SQLite I cannot see any means of password protecting the database without
> > either buying a commercial extension to do this, or recompiling SQLite
> > with the authentication extension. Due to financial constraints and
> > unfamiliarity with compiling in C both of these are not an option for me.
> > Also I need a secure way to do this, as I think I read that the SQLite
> > version simply uses a table to store the user data, which of course can
> be
> > read and accessed elsewhere.
> > Are there any other options available for doing this?
> > Any help appreciated.
> > Thanks.
> > Damien.
>
> Damien,
>
> Why do you need to password protect the database?
>
> Genealogy information is generally of the public record variety so there is
> nothing sensitive to protect.  I am making genealogy software myself and so
> am
> familiar with many of the relevant issues.
>
> I would say please explain why you think you need password protection for
> this
> project and then the real issue at hand can be addressed.
>
> If yours is a network application and you don't want people on the open
> internet
> from accessing the database, fair enough, but that's an application-level
> solution; what you're asking for here is that people who have direct access
> to
> the SQLite database file are blocked by a password, and this I question.
>
> -- Darren Duncan
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ------------------------------
>
> Message: 9
> Date: Sat, 8 Oct 2016 00:31:00 -0700
> From: Darren Duncan <dar...@darrenduncan.net>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Protecting databases
> Message-ID: <57f8a0b4.1030...@darrenduncan.net>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> So, if you don't want to pay the one-time fee for the SQLite Encryption
> Extension et al to get database-level security, your only option really is
> to
> encrypt individual fields at the application level that you want to
> protect, and
> there are various free encryption libraries you can use for that, the
> specific
> options depending on your choice of programming language.  But using those
> has
> nothing to do with SQLite specifically, so your answer wouldn't be found
> on this
> SQLite forum, but rather forums for your programming language. -- Darren
> Duncan
>
> On 2016-10-08 12:18 AM, Damien Sykes-Lindley wrote:
> > Hi Darren,
> > You are correct in that genealogy is generally public. However more
> often than
> > not the information you want to publish may very well differ from what
> is in
> > your private database. You may have private notes telling you what you
> need to
> > do. You may have anecdotes shared by many family members that may need
> to be
> > kept private, at least until the involved parties are deceased or
> otherwise
> > choose to divulge it publicly themselves.
> > Even more importantly I may choose to add an address-book style feature
> in there
> > so you can easily group and contact appropriate family members for
> whatever
> > reason (special occasions etc). Of course that will be private.
> > Password protecting it is also good on many levels - if the database is
> to be
> > used online then it is needless to say that authentication would be
> required for
> > various people to view it. Even if I decide to make it local only, there
> is the
> > possibility that anyone sharing the computer or network may peruse the
> database
> > when you don't want them to.
> > Kind regards,
> > Damien.
> > -----Original Message----- From: Darren Duncan
> > Sent: Saturday, October 08, 2016 6:54 AM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Protecting databases
> >
> > On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:
> >> Hi there,
> >> My name is Damien Lindley, and I am, among other things, an independent,
> >> hobbiest programmer. I have been blind since birth and thus all my
> computer
> >> work relies on screenreader software and keyboard.
> >> I have only just come through the brink of scripting into compiled
> programming
> >> and so I guess I am still a beginner in many respects. However I don’t
> work in
> >> C or C++, so most of my programming, if using a library, relies on
> precompiled
> >> static or dynamic libraries. Or of course libraries that are written or
> >> converted specifically for the language I work in (FreeBASIC).
> >> Recently, I decided I needed to create a piece of software that could
> manage
> >> family trees, since there seems to be a lack of screenreader accessible
> >> genealogy managers out there. I was advised the best way to do this is
> to use
> >> a database engine. I was also informed that SQLite is always a good
> choice for
> >> databases.
> >> I must admit, I have never worked with databases before and so now I am
> in the
> >> process of learning SQL. However looking at the programming API for
> SQLite I
> >> cannot see any means of password protecting the database without either
> buying
> >> a commercial extension to do this, or recompiling SQLite with the
> >> authentication extension. Due to financial constraints and
> unfamiliarity with
> >> compiling in C both of these are not an option for me. Also I need a
> secure
> >> way to do this, as I think I read that the SQLite version simply uses a
> table
> >> to store the user data, which of course can be read and accessed
> elsewhere.
> >> Are there any other options available for doing this?
> >> Any help appreciated.
> >> Thanks.
> >> Damien.
> >
> > Damien,
> >
> > Why do you need to password protect the database?
> >
> > Genealogy information is generally of the public record variety so there
> is
> > nothing sensitive to protect.  I am making genealogy software myself and
> so am
> > familiar with many of the relevant issues.
> >
> > I would say please explain why you think you need password protection
> for this
> > project and then the real issue at hand can be addressed.
> >
> > If yours is a network application and you don't want people on the open
> internet
> > from accessing the database, fair enough, but that's an application-level
> > solution; what you're asking for here is that people who have direct
> access to
> > the SQLite database file are blocked by a password, and this I question.
> >
> > -- Darren Duncan
>
>
>
> ------------------------------
>
> Message: 10
> Date: Sat, 8 Oct 2016 00:36:03 -0700
> From: Darren Duncan <dar...@darrenduncan.net>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Protecting databases
> Message-ID: <57f8a1e3.3050...@darrenduncan.net>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Replying to myself.  This being said, going for the application-level
> encryption
> option would prevent you from using SQLite to do some useful things, such
> as
> being able to do a substring search for text in encrypted fields, since
> encrypted data is just a black box to it.  Typically the application-level
> solution is just encrypting a minimum number of fields, such as credit card
> numbers or SINs or passwords etc, that wouldn't be searched except for a
> whole
> value match.  To use SQLite normally as if it weren't encrypted but with it
> actually encrypted, you need the SEE or similar for that. -- Darren Duncan
>
> On 2016-10-08 12:31 AM, Darren Duncan wrote:
> > So, if you don't want to pay the one-time fee for the SQLite Encryption
> > Extension et al to get database-level security, your only option really
> is to
> > encrypt individual fields at the application level that you want to
> protect, and
> > there are various free encryption libraries you can use for that, the
> specific
> > options depending on your choice of programming language.  But using
> those has
> > nothing to do with SQLite specifically, so your answer wouldn't be found
> on this
> > SQLite forum, but rather forums for your programming language. -- Darren
> Duncan
> >
> > On 2016-10-08 12:18 AM, Damien Sykes-Lindley wrote:
> >> Hi Darren,
> >> You are correct in that genealogy is generally public. However more
> often than
> >> not the information you want to publish may very well differ from what
> is in
> >> your private database. You may have private notes telling you what you
> need to
> >> do. You may have anecdotes shared by many family members that may need
> to be
> >> kept private, at least until the involved parties are deceased or
> otherwise
> >> choose to divulge it publicly themselves.
> >> Even more importantly I may choose to add an address-book style feature
> in there
> >> so you can easily group and contact appropriate family members for
> whatever
> >> reason (special occasions etc). Of course that will be private.
> >> Password protecting it is also good on many levels - if the database is
> to be
> >> used online then it is needless to say that authentication would be
> required for
> >> various people to view it. Even if I decide to make it local only,
> there is the
> >> possibility that anyone sharing the computer or network may peruse the
> database
> >> when you don't want them to.
> >> Kind regards,
> >> Damien.
> >> -----Original Message----- From: Darren Duncan
> >> Sent: Saturday, October 08, 2016 6:54 AM
> >> To: SQLite mailing list
> >> Subject: Re: [sqlite] Protecting databases
> >>
> >> On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:
> >>> Hi there,
> >>> My name is Damien Lindley, and I am, among other things, an
> independent,
> >>> hobbiest programmer. I have been blind since birth and thus all my
> computer
> >>> work relies on screenreader software and keyboard.
> >>> I have only just come through the brink of scripting into compiled
> programming
> >>> and so I guess I am still a beginner in many respects. However I don’t
> work in
> >>> C or C++, so most of my programming, if using a library, relies on
> precompiled
> >>> static or dynamic libraries. Or of course libraries that are written or
> >>> converted specifically for the language I work in (FreeBASIC).
> >>> Recently, I decided I needed to create a piece of software that could
> manage
> >>> family trees, since there seems to be a lack of screenreader accessible
> >>> genealogy managers out there. I was advised the best way to do this is
> to use
> >>> a database engine. I was also informed that SQLite is always a good
> choice for
> >>> databases.
> >>> I must admit, I have never worked with databases before and so now I
> am in the
> >>> process of learning SQL. However looking at the programming API for
> SQLite I
> >>> cannot see any means of password protecting the database without
> either buying
> >>> a commercial extension to do this, or recompiling SQLite with the
> >>> authentication extension. Due to financial constraints and
> unfamiliarity with
> >>> compiling in C both of these are not an option for me. Also I need a
> secure
> >>> way to do this, as I think I read that the SQLite version simply uses
> a table
> >>> to store the user data, which of course can be read and accessed
> elsewhere.
> >>> Are there any other options available for doing this?
> >>> Any help appreciated.
> >>> Thanks.
> >>> Damien.
> >>
> >> Damien,
> >>
> >> Why do you need to password protect the database?
> >>
> >> Genealogy information is generally of the public record variety so
> there is
> >> nothing sensitive to protect.  I am making genealogy software myself
> and so am
> >> familiar with many of the relevant issues.
> >>
> >> I would say please explain why you think you need password protection
> for this
> >> project and then the real issue at hand can be addressed.
> >>
> >> If yours is a network application and you don't want people on the open
> internet
> >> from accessing the database, fair enough, but that's an
> application-level
> >> solution; what you're asking for here is that people who have direct
> access to
> >> the SQLite database file are blocked by a password, and this I question.
> >>
> >> -- Darren Duncan
>
>
>
> ------------------------------
>
> Message: 11
> Date: Sat, 08 Oct 2016 09:55:53 +0200
> From: Jean-Christophe Deschamps <j...@antichoc.net>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Protecting databases
> Message-ID:
>         <mailman.2.1475928001.24897.sqlite-us...@mailinglists.sqlite.org>
> Content-Type: text/plain; charset="us-ascii"; format=flowed
>
> Damien,
>
> At 09:18 08/10/2016, you wrote:
> >Password protecting it is also good on many levels - if the database
> >is to be used online then it is needless to say that authentication
> >would be required for various people to view it.
>
> SQLite can't be put "online" per se. It will then be the duty of the
> host software layer (website software or equivalent using some other
> protocol) to accept only users having been granted access.
>
> >Even if I decide to make it local only, there is the possibility that
> >anyone sharing the computer or network may peruse the database when
> >you don't want them to.
>
> That's the reason why OSes provide user login and file access rights.
> Again this is external to SQLite and its DB file(s). Also please note
> that SQLite is not a client-server engine, so access thru a LAN is
> prone to fail due to network file locking being poorly implemented.
>
> Alternatively you can also use strong DB encryption with or without
> adding the authentication function. Google SQLite encryption and you'll
> find a number of answers, some of them correct.
>
> JcD
>
>
>
> ------------------------------
>
> Message: 12
> Date: Sat, 8 Oct 2016 11:02:55 +0200
> From: R Smith <rsm...@rsweb.co.za>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Protecting databases
> Message-ID: <b375378b-26fd-0f20-7685-29de0c512...@rsweb.co.za>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Hi Damien,
>
> We are obviously all fans of SQLite and keen to involve more people in
> what is one of the best DB systems in existence and most widely used DB
> in the World.
>
> But...
>
> SQLite answers a need that is quite specific (though widely used) in
> that it is very small, very fast and, as a bonus, can be compiled right
> into most software projects so distribution is not an issue - which
> further means it is also server-less. This last feature makes it
> specifically not well suited for large network-server and service type
> applications and not well suited for user control. Encryption is easy
> (as others explained) but your replies indicate you are more interested
> in user control. While some extensions do exist to allow a form of user
> control, I think what you really want is called PostGres or Maria-DB
> (previously MySQL). They are equally free, open-source, they support
> client-server architecture and both storage-encryption and user-control
> are inherent to these systems, and they play very well with web-script
> engines (such as PHP and its ilk).
>
> Use SQLite for local storage that need not be protected from users with
> access to the local file system. Store all your application settings in
> SQLite, etc. It is not uncommon to use multiple database engines in a
> single project. Right tool for the job and such.
>
> Of the two alternate engines above, my preferred is PostGres, but there
> are situations where Maria DB works a treat (such as the usual
> LAMP-stack quick web-server solutions and the like) and it has some easy
> and strong scripting capabilities.
>
> Links:
> https://mariadb.org/
> https://www.postgresql.org/
>
> Good luck,
> Ryan
>
>
> On 2016/10/08 9:18 AM, Damien Sykes-Lindley wrote:
> > Hi Darren,
> > You are correct in that genealogy is generally public. However more
> > often than not the information you want to publish may very well
> > differ from what is in your private database. You may have private
> > notes telling you what you need to do. You may have anecdotes shared
> > by many family members that may need to be kept private, at least
> > until the involved parties are deceased or otherwise choose to divulge
> > it publicly themselves.
> > Even more importantly I may choose to add an address-book style
> > feature in there so you can easily group and contact appropriate
> > family members for whatever reason (special occasions etc). Of course
> > that will be private.
> > Password protecting it is also good on many levels - if the database
> > is to be used online then it is needless to say that authentication
> > would be required for various people to view it. Even if I decide to
> > make it local only, there is the possibility that anyone sharing the
> > computer or network may peruse the database when you don't want them to.
> > Kind regards,
> > Damien.
> > -----Original Message----- From: Darren Duncan
> > Sent: Saturday, October 08, 2016 6:54 AM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Protecting databases
> >
> > On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:
> >> Hi there,
> >> My name is Damien Lindley, and I am, among other things, an
> >> independent, hobbiest programmer. I have been blind since birth and
> >> thus all my computer work relies on screenreader software and keyboard.
> >> I have only just come through the brink of scripting into compiled
> >> programming and so I guess I am still a beginner in many respects.
> >> However I don’t work in C or C++, so most of my programming, if using
> >> a library, relies on precompiled static or dynamic libraries. Or of
> >> course libraries that are written or converted specifically for the
> >> language I work in (FreeBASIC).
> >> Recently, I decided I needed to create a piece of software that could
> >> manage family trees, since there seems to be a lack of screenreader
> >> accessible genealogy managers out there. I was advised the best way
> >> to do this is to use a database engine. I was also informed that
> >> SQLite is always a good choice for databases.
> >> I must admit, I have never worked with databases before and so now I
> >> am in the process of learning SQL. However looking at the programming
> >> API for SQLite I cannot see any means of password protecting the
> >> database without either buying a commercial extension to do this, or
> >> recompiling SQLite with the authentication extension. Due to
> >> financial constraints and unfamiliarity with compiling in C both of
> >> these are not an option for me. Also I need a secure way to do this,
> >> as I think I read that the SQLite version simply uses a table to
> >> store the user data, which of course can be read and accessed elsewhere.
> >> Are there any other options available for doing this?
> >> Any help appreciated.
> >> Thanks.
> >> Damien.
> >
> > Damien,
> >
> > Why do you need to password protect the database?
> >
> > Genealogy information is generally of the public record variety so
> > there is
> > nothing sensitive to protect.  I am making genealogy software myself
> > and so am
> > familiar with many of the relevant issues.
> >
> > I would say please explain why you think you need password protection
> > for this
> > project and then the real issue at hand can be addressed.
> >
> > If yours is a network application and you don't want people on the
> > open internet
> > from accessing the database, fair enough, but that's an application-level
> > solution; what you're asking for here is that people who have direct
> > access to
> > the SQLite database file are blocked by a password, and this I question.
> >
> > -- Darren Duncan
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ------------------------------
>
> Message: 13
> Date: Sat, 8 Oct 2016 12:12:43 +0100
> From: "Damien Sykes-Lindley" <dam...@dcpendleton.plus.com>
> To: "SQLite mailing list" <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Protecting databases
> Message-ID: <2E420EE760904FD59BB3678CDC87E127@Psycheman>
> Content-Type: text/plain; format=flowed; charset="utf-8";
>         reply-type=response
>
> Hi Ryan,
> I think there may have been some misunderstanding here. The application is
> a
> local application and thus needs no server, at least on the data side. But
> multiple people may have access to the same machine. So while user control
> is needed elsewhere, that certainly isn't on the local side. Just a simple
> password would do.
> The network/user control scenario is much more important in the
> collaboration features of the software. I am still trying to work out
> ultimately how best to do this (client/server, peer-to-peer possibly with
> relay server etc). However the specific data that is being collaborated on
> doesn't need to be stored remotely, unless of course I use a
> check-in/check-out style system. Again, collaboration is way into the
> future
> and so I'm not thinking along those lines just yet.
> I have looked into the SEE extension again, just to make sure I am correct
> in my assumptions, and it appears I am. I doubt I even get $2000 disposable
> income in one year, let alone a month so that option would be impossible
> for
> me at this time. Also it appears that you are responsible for compiling
> SEE,
> again which I would find it very hard to do, never having compiled anything
> in C.
> On the other hand, searching is a necessity so it appears that encrypting
> the data from the application itself is also not an option. So I think at
> this point I am stuck
> Cheers.
> Damien.
> -----Original Message-----
> From: R Smith
> Sent: Saturday, October 08, 2016 10:02 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Protecting databases
>
> Hi Damien,
>
> We are obviously all fans of SQLite and keen to involve more people in
> what is one of the best DB systems in existence and most widely used DB
> in the World.
>
> But...
>
> SQLite answers a need that is quite specific (though widely used) in
> that it is very small, very fast and, as a bonus, can be compiled right
> into most software projects so distribution is not an issue - which
> further means it is also server-less. This last feature makes it
> specifically not well suited for large network-server and service type
> applications and not well suited for user control. Encryption is easy
> (as others explained) but your replies indicate you are more interested
> in user control. While some extensions do exist to allow a form of user
> control, I think what you really want is called PostGres or Maria-DB
> (previously MySQL). They are equally free, open-source, they support
> client-server architecture and both storage-encryption and user-control
> are inherent to these systems, and they play very well with web-script
> engines (such as PHP and its ilk).
>
> Use SQLite for local storage that need not be protected from users with
> access to the local file system. Store all your application settings in
> SQLite, etc. It is not uncommon to use multiple database engines in a
> single project. Right tool for the job and such.
>
> Of the two alternate engines above, my preferred is PostGres, but there
> are situations where Maria DB works a treat (such as the usual
> LAMP-stack quick web-server solutions and the like) and it has some easy
> and strong scripting capabilities.
>
> Links:
> https://mariadb.org/
> https://www.postgresql.org/
>
> Good luck,
> Ryan
>
>
> On 2016/10/08 9:18 AM, Damien Sykes-Lindley wrote:
> > Hi Darren,
> > You are correct in that genealogy is generally public. However more often
> > than not the information you want to publish may very well differ from
> > what is in your private database. You may have private notes telling you
> > what you need to do. You may have anecdotes shared by many family members
> > that may need to be kept private, at least until the involved parties are
> > deceased or otherwise choose to divulge it publicly themselves.
> > Even more importantly I may choose to add an address-book style feature
> in
> > there so you can easily group and contact appropriate family members for
> > whatever reason (special occasions etc). Of course that will be private.
> > Password protecting it is also good on many levels - if the database is
> to
> > be used online then it is needless to say that authentication would be
> > required for various people to view it. Even if I decide to make it local
> > only, there is the possibility that anyone sharing the computer or
> network
> > may peruse the database when you don't want them to.
> > Kind regards,
> > Damien.
> > -----Original Message----- From: Darren Duncan
> > Sent: Saturday, October 08, 2016 6:54 AM
> > To: SQLite mailing list
> > Subject: Re: [sqlite] Protecting databases
> >
> > On 2016-10-07 10:46 PM, Damien Sykes-Lindley wrote:
> >> Hi there,
> >> My name is Damien Lindley, and I am, among other things, an independent,
> >> hobbiest programmer. I have been blind since birth and thus all my
> >> computer work relies on screenreader software and keyboard.
> >> I have only just come through the brink of scripting into compiled
> >> programming and so I guess I am still a beginner in many respects.
> >> However I don’t work in C or C++, so most of my programming, if using a
> >> library, relies on precompiled static or dynamic libraries. Or of course
> >> libraries that are written or converted specifically for the language I
> >> work in (FreeBASIC).
> >> Recently, I decided I needed to create a piece of software that could
> >> manage family trees, since there seems to be a lack of screenreader
> >> accessible genealogy managers out there. I was advised the best way to
> do
> >> this is to use a database engine. I was also informed that SQLite is
> >> always a good choice for databases.
> >> I must admit, I have never worked with databases before and so now I am
> >> in the process of learning SQL. However looking at the programming API
> >> for SQLite I cannot see any means of password protecting the database
> >> without either buying a commercial extension to do this, or recompiling
> >> SQLite with the authentication extension. Due to financial constraints
> >> and unfamiliarity with compiling in C both of these are not an option
> for
> >> me. Also I need a secure way to do this, as I think I read that the
> >> SQLite version simply uses a table to store the user data, which of
> >> course can be read and accessed elsewhere.
> >> Are there any other options available for doing this?
> >> Any help appreciated.
> >> Thanks.
> >> Damien.
> >
> > Damien,
> >
> > Why do you need to password protect the database?
> >
> > Genealogy information is generally of the public record variety so there
> > is
> > nothing sensitive to protect.  I am making genealogy software myself and
> > so am
> > familiar with many of the relevant issues.
> >
> > I would say please explain why you think you need password protection for
> > this
> > project and then the real issue at hand can be addressed.
> >
> > If yours is a network application and you don't want people on the open
> > internet
> > from accessing the database, fair enough, but that's an application-level
> > solution; what you're asking for here is that people who have direct
> > access to
> > the SQLite database file are blocked by a password, and this I question.
> >
> > -- Darren Duncan
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ------------------------------
>
> Message: 14
> Date: Sat, 8 Oct 2016 08:32:43 -0300
> From: Domingo Alvarez Duarte <mingo...@gmail.com>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] Error trying to make sqlite3 documentation
> Message-ID: <7c657d31-ec1a-383c-5ad8-1781fb091...@gmail.com>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Hello !
>
> I'm trying to make sqlite3 documentation to use locally and got this error:
>
> ====
>
> make all
>
> ...
>
> Processing ./pages/btreemodule.in
> ./tclsh: missing close-brace
>      while executing
> "hd_puts {"
>      ("eval" body line 590)
>      invoked from within
> "eval "hd_puts \173$text\175""
>      (procedure "hd_resolve" line 5)
>      invoked from within
> "hd_resolve [subst {
>
>      $PREAMBLE
>
>      <div class=fancy>
>      <div
> style="font-size:2em;text-align:center;color:#80a796">$zTitle</div>
>      <div style=..."
>      (procedure "fancyformat_document" line 19)
>      invoked from within
> "fancyformat_document "SQLite B-Tree Module" {hlr50000.txt llr50000.txt} {
>
>    [h1 "Document Overview"]
>
>    [h2 "Scope and Purpose"]
>
>    <p>
>      This docu..."
>      ("eval" body line 38)
>      invoked from within
> "eval {
>
> hd_keywords {btree design}
> source [file join $::DOC pages fancyformat.tcl]
>
> foreach header {btree.h sqliteInt.h} {
>    set fd [open [file join $..."
>      ("eval" body line 2)
>      invoked from within
> "eval "hd_resolve \173$in\175""
>      ("foreach" body line 16)
>      invoked from within
> "foreach infile [lrange $argv 3 end] {
>    cd $HOMEDIR
>    puts "Processing $infile"
>    set fd [open $infile r]
>    set in [read $fd]
>    close $fd
>    if {[strin..."
>      (file "./wrap.tcl" line 783)
> make: *** [base] Error 1
>
> ====
>
> Has someone build the latest documentation ?
>
> Cheers !
>
>
>
> ------------------------------
>
> Message: 15
> Date: Sat, 8 Oct 2016 07:38:18 -0400
> From: Richard Hipp <d...@sqlite.org>
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Error trying to make sqlite3 documentation
> Message-ID:
>         <CALwJ=MzB-7AGoP=HmTfsiEH610BcDSLw33OFcvyfsSsCKzo8Fg@mail.
> gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> On 10/8/16, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:
> > Hello !
> >
> > I'm trying to make sqlite3 documentation to use locally and got this
> error:
> >
> > Has someone build the latest documentation ?
> >
>
> Yes.  https://sqlite.org/draft/index.html was built by typing "make
> all" (or "make fast") and then using rsync to move the result to the
> website.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
>
> ------------------------------
>
> Message: 16
> Date: Sat, 8 Oct 2016 12:05:03 +0100
> From: Conor O <conor.scorp...@gmail.com>
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Fwd: Lemon: Simple recursive rule causes assertion
>         failed: stateno <= YY_SHIFT_COUNT
> Message-ID:
>         <CAB6-+tCjAGEK9eMwMvh9np5unYosrbJfk2U9=
> daez0tisot...@mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> I've been experimenting with some simple rules as I get used to the Lemon
> parser. This very simple parser file works correctly. A database consists
> of an entrylist which consists of any number of commands:
>
>     database ::= entrylist. { printf("Constructed the root\n"); }
>
>     entrylist ::= command.
>     entrylist ::= entrylist command.
>
>     command ::= CMDNAME INTEGER TEXT EOL. { printf("Reducing to command
> state\n"); }
>
> For reference, I'm feeding the parser with:
>     Parse(pParser, CMDNAME, 0);
>     Parse(pParser, INTEGER, 0);
>     Parse(pParser, TEXT, 0);
>     Parse(pParser, EOL, 0);
>
> However, if I enhance the command slightly to accept any number of optional
> arguments:
>
>     database ::= entrylist. { printf("Constructed the root\n"); }
>
>     entrylist ::= command.
>     entrylist ::= entrylist command.
>
>     command ::= CMDNAME cmdargs EOL. { printf("Reducing to command
> state\n"); }
>     cmdargs ::= .
>     cmdargs ::= cmdargs cmdarg.
>
>     cmdarg ::= INTEGER.
>     cmdarg ::= TEXT.
>
> This causes an assertion failure:
>
> > test.exe
> Debug: Input 'CMDNAME'
> Debug: Shift 'CMDNAME', go to state 3
> Debug: Return. Stack=[CMDNAME]
> Debug: Input 'INTEGER'
> Assertion failed: stateno <= YY_SHIFT_COUNT, file testpar.c, line 512
>
> I'm trying to figure out if this is a bug or something I'm just not
> understanding correctly.
>
> Thanks, Conor.
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 106, Issue 8
> ********************************************
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to