[sqlite] Integrating sqlite with Core Data and iCloud

[Simon Slavin]

On 23 Apr 2015, at 9:29pm, Jeff M  wrote:

> Has there been any discussion about integrating sqlite with Apple's iCloud, 
> either by using Apple's Core Data as the Virtual Machine's database engine 
> (so the VM would operate on Core Data objects) or by otherwise modifying the 
> existing backend?

Apple has already tightly integrated SQLite with iCloud.  You can read about it 
here:



This follows the standard Core Data model: the database is used to store 
persistent objects and your API handles objects rather than making SQLite calls.

You might instead be asking whether you could write an app which makes SQLite 
calls to have data stores in iCloud.  This could be done by writing a SQLite 
VFS which stored data in iCloud.  I don't know how well this would work, 
especially if more than one user was trying to use a database.  It might be 
difficult to implement because the iCloud API doesn't map neatly onto the 
SQLite VFS API.  But this is far more detailed than I have ever learned to 
program myself.

What would be easier is to use SQLite to store a database on your iCloud Drive, 
as available in OS X 10.10 and above and some versions of Windows.  But there's 
nothing to do here: the drive is just a mounted volume like a Flash drive or an 
AFP mount.  Just use the right path in sqlite3_open().  And experiment with 
concurrent multi-user use before you promise it, since iCloud Drive locking is, 
I think, implemented with using SMB2.

Simon.

[sqlite] building SQLite DLL with Visual C++

[Drago, William @ CSG - NARDA-MITEQ]

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jay Smith
> Sent: Thursday, April 23, 2015 3:47 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] building SQLite DLL with Visual C++
>
> Thank you Adam for responding to my post. I have the windows binaries
> downloaded.
>
> At this point I am just following the instructions in the book. And I
> really am not sure what I need the dll for.
>
> Here's the scenario. I have created a program in vb2012. The program
> stores less than 20 fields of data. I am currently saving the data in a
> html format. I just recently discovered SQLite. I am now in the process
> of changing over to a database to store data. I studied SQL and Oracle
> 10 years ago. I have almost completed the database for the project.  My
> problem is how to integrate the SQL db into my VB program.

Your subject line says C++, but the line above says VB, so is your program 
written in C++ or VB?
If it's VB just get SQLite with NuGet (Tools > NuGet Package Manager, then 
search for System.Data.SQLite Core).

-Bill


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] building SQLite DLL with Visual C++

[Adam Devita]

Good day,
I don't work in VB, so I can't help you in specifics on that. A quick
search on a search engine  pops up some videos on how to use sqlite in
a Visual Basic project.  The archives of this list have a lot of
questions where people are introduced to open, prep sql, (bind), step,
fetch, finalize, close. It is often a good idea to specify which book
you are working through for a question relating to a published
tutorial, as some future person may be reading the same book and could
find the thread helpful.  Some people on the list likely have a copy
of the same book on their shelf.  Someone on  this list might even be
the author.

Adam




On Thu, Apr 23, 2015 at 3:47 PM, Jay Smith  wrote:
> Thank you Adam for responding to my post. I have the windows binaries
> downloaded.
>
> At this point I am just following the instructions in the book. And I
> really am not sure what I need the dll for.
>
> Here's the scenario. I have created a program in vb2012. The program stores
> less than 20 fields of data. I am currently saving the data in a html
> format. I just recently discovered SQLite. I am now in the process of
> changing over to a database to store data. I studied SQL and Oracle 10
> years ago. I have almost completed the database for the project.  My
> problem is how to integrate the SQL db into my VB program.
>
> On Wed, Apr 22, 2015 at 10:16 AM, Adam Devita  wrote:
>
>> Good day,
>>
>>
>> Why are you compiling a dll instead of using the pre-compiled windows
>> binaries at http://www.sqlite.org/download.html?
>>
>> Are you adding some sort of extra wrapper?
>>
>> Why are you not adding the amalgamated c source in your project (turn
>> off use pre-compiled headers for that file) ?
>>
>> If you insist on creating your own dll, try the vs wizard to create a
>> dll project, then add code to it.
>>
>> regards,
>> Adam DeVita
>>
>>
>> On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik 
>> wrote:
>> > On 4/21/2015 11:01 AM, Jay Smith wrote:
>> >>
>> >> Before I sent the last message I had signed up to become a user.
>> >> My previous message was bounced.  WHY
>> >
>> >
>> > I, for one, have received both your original and this new message.
>> > --
>> > Igor Tandetnik
>> >
>> >
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users at mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> --
>> --
>> VerifEye Technologies Inc.
>> 151 Whitehall Dr. Unit 2
>> Markham, ON
>> L3R 9T1
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thanks Igor
the only response I got to the first message was "it has been bounced"


On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik  wrote:

> On 4/21/2015 11:01 AM, Jay Smith wrote:
>
>> Before I sent the last message I had signed up to become a user.
>> My previous message was bounced.  WHY
>>
>
> I, for one, have received both your original and this new message.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] building SQLite DLL with Visual C++

[Jay Smith]

Thank you Adam for responding to my post. I have the windows binaries
downloaded.

At this point I am just following the instructions in the book. And I
really am not sure what I need the dll for.

Here's the scenario. I have created a program in vb2012. The program stores
less than 20 fields of data. I am currently saving the data in a html
format. I just recently discovered SQLite. I am now in the process of
changing over to a database to store data. I studied SQL and Oracle 10
years ago. I have almost completed the database for the project.  My
problem is how to integrate the SQL db into my VB program.

On Wed, Apr 22, 2015 at 10:16 AM, Adam Devita  wrote:

> Good day,
>
>
> Why are you compiling a dll instead of using the pre-compiled windows
> binaries at http://www.sqlite.org/download.html?
>
> Are you adding some sort of extra wrapper?
>
> Why are you not adding the amalgamated c source in your project (turn
> off use pre-compiled headers for that file) ?
>
> If you insist on creating your own dll, try the vs wizard to create a
> dll project, then add code to it.
>
> regards,
> Adam DeVita
>
>
> On Wed, Apr 22, 2015 at 9:51 AM, Igor Tandetnik 
> wrote:
> > On 4/21/2015 11:01 AM, Jay Smith wrote:
> >>
> >> Before I sent the last message I had signed up to become a user.
> >> My previous message was bounced.  WHY
> >
> >
> > I, for one, have received both your original and this new message.
> > --
> > Igor Tandetnik
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Integrating sqlite with Core Data and iCloud

[Jeff M]

Has there been any discussion about integrating sqlite with Apple's iCloud, 
either by using Apple's Core Data as the Virtual Machine's database engine (so 
the VM would operate on Core Data objects) or by otherwise modifying the 
existing backend?

My motivation is to allow existing apps that using the SQL language (via FMDB 
or the sqlite API) to gain Core Data features such as iCloud integration for 
syncing data between devices.  Refactoring apps to use Core Data is often 
impractical.

My dream architecture is:
SQL Language <--> FMDB <--> CD_sqlite (using Core Data (sqlite)) <--> 
iCloud.

What is the feasibility of making sqlite a wrapper for Core Data, or 
integrating iCloud directly into sqlite?  Crazy?

Jeff

[sqlite] Error while creating view through command line

[Simon Slavin]

On 23 Apr 2015, at 7:56am, Manoj Kumar Pasumarthi  
wrote:
`
> qAdmin: Cannot perform this operation on a closed dataset.

This is not a SQLite error message.  It is generated by Delphi or by something 
that uses Delphi.

If you try to open the database in the SQLite shell tool instead of that 
application, do you still get an error message ?  if so, what is it ?

Simon.

[sqlite] Destroy all evidence of a database

[Eduardo Morras]

On Wed, 22 Apr 2015 20:07:45 +0100
Simon Slavin  wrote:

> 
> On 22 Apr 2015, at 7:18pm, Scott Hess  wrote:
> 
> > The only way SQLite can get to the disk is using the vfs, so if the
> > vfs encrypts things, all of the files (main db, temp db, journal,
> > everything) will be encrypted.
> 
> Guys.  Guys.  Guys.  My app doesn't have access to any level below
> standard file system calls.  This is a highly secure system.  Any
> calls which talk directly to hardware (e.g. turn the caps lock light
> on, access SMART diagnostics, try to count the number of displays)
> will fail because my app isn't allowed to do that stuff.  Any attempt
> from my app to mount anything will fail.  My app has access to just
> GUI and files.  I don't have to worry about the security setup at OS
> level, merely not leave files about with sensitive information in
> them.

You can reference count the number of files it creates and deletes. If it 
creates more files than it deletes, you have a problem. It doesn't involve big 
changes in sqlite vfs code and even may be implemented in sqlite3 core code as 
a debug feature.

> 
> Simon.

---   ---
Eduardo Morras 

[sqlite] Error while creating view through command line

[Manoj Kumar Pasumarthi]

Hi Kevin,

It is working by the following fix. Instead of attaching the DB, we are opening 
the DB.

open  'SP_R3.s3db' ;

BEGIN TRANSACTION;


CREATE view view1] as select model from windturbine;

COMMIT;

.exit

Regards,
Manoj
-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin Benson
Sent: Thursday, April 23, 2015 2:56 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Error while creating view through command line

On Thu, Apr 23, 2015 at 5:16 AM, Manoj Kumar Pasumarthi < manojkumar.pa at 
hcl.com> wrote:

> Kevin, I am getting two rows as follow:
>
> 0|main|
> 2|sprdb|C:\Users\manojkumar.pa
> \Desktop\SP\DBManagementService\Data\SP_R3.s3db
>
> Regards,
> Manoj
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Kevin 
> Benson
> Sent: Thursday, April 23, 2015 2:43 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Error while creating view through command line
>
> On Thu, Apr 23, 2015 at 4:58 AM, Manoj Kumar Pasumarthi < 
> manojkumar.pa at hcl.com> wrote:
>
> > Thanks for the reply Kevin. But it didn't worked.
> >
>
> If you issue the following after you do your ATTACH:
>
>
> PRAGMA database_list;
>
>
> do you see a row returned for that database you are Attaching?



Looks like you're going to have to provide more information to the list, about 
your code setup and environment, than...
...'My database is getting corrupted and I'm getting a non-SQLite error.'

--
   --
  --
 --???--
K e V i N
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

[sqlite] Error while creating view through command line

[Manoj Kumar Pasumarthi]

Kevin, I am getting two rows as follow:

0|main|
2|sprdb|C:\Users\manojkumar.pa\Desktop\SP\DBManagementService\Data\SP_R3.s3db

Regards,
Manoj

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin Benson
Sent: Thursday, April 23, 2015 2:43 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Error while creating view through command line

On Thu, Apr 23, 2015 at 4:58 AM, Manoj Kumar Pasumarthi < manojkumar.pa at 
hcl.com> wrote:

> Thanks for the reply Kevin. But it didn't worked.
>

If you issue the following after you do your ATTACH:


PRAGMA database_list;


do you see a row returned for that database you are ATTACHing?

--
   --
  --
 --???--
K e V i N
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

[sqlite] Error while creating view through command line

[Manoj Kumar Pasumarthi]

Thanks for the reply Kevin. But it didn't worked.


Regards,
Manoj

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Kevin Benson
Sent: Thursday, April 23, 2015 2:05 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Error while creating view through command line

On Wed, Apr 22, 2015 at 11:49 AM, Manoj Kumar Pasumarthi < manojkumar.pa at 
hcl.com> wrote:

> Hi,
>
> I am trying to create a new view through command line script (sqlite3) 
> as
> follow:
>
>
> attach 'SP_R3.s3db' as sprdb;
>
> BEGIN TRANSACTION;
>
>
> CREATE view sprdb.[view1] as select model from sprdb.[windturbine];
>
> COMMIT;
>
> .exit
>
> After executing this script, DB is getting corrupted.
>
> Can anyone please tell me how to create a new view through scripts. 
> Sample view is fine.
>
> Please tell me whether it is possible or not.
>


Here's a wild hunch/guess. Try removing the underscore from your database
name:


attach 'SPr3.s3db' as sprdb;

BEGIN TRANSACTION;


CREATE view sprdb.[view1] as select model from sprdb.[windturbine];

COMMIT;

.exit


--
   --
  --
 --???--
K e V i N
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

[sqlite] Error while creating view through command line

[Manoj Kumar Pasumarthi]

Hi Simon,

I have tried the text you have given in the shell tool. Still I am getting the 
same error. Before executing the commands I am able to open the database. After 
executing the DB is getting corrupted. I am getting following error when I try 
to open DB.

qAdmin: Cannot perform this operation on a closed dataset.

Regards,
Manoj

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, April 23, 2015 2:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Error while creating view through command line


On 22 Apr 2015, at 4:49pm, Manoj Kumar Pasumarthi  
wrote:

> attach 'SP_R3.s3db' as sprdb;
> 
> BEGIN TRANSACTION;
> 
> 
> CREATE view sprdb.[view1] as select model from sprdb.[windturbine];
> 
> COMMIT;
> 
> .exit
> 
> After executing this script, DB is getting corrupted.

Please run

PRAGMA integrity_check;

on 'SP_R3.s3db' before you create the view to check that it is not already 
corrupt.

Do the commands corrupt the script if you type them into the shell tool 
manually instead of running them as a script ?

If so, please try this instead of your text:

ATTACH 'SP_R3.s3db' AS sprdb;
CREATE VIEW sprdb.view1 AS SELECT model FROM sprdb.windturbine;

and tell us whether that works.

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


::DISCLAIMER::


The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
E-mail transmission is not guaranteed to be secure or error-free as information 
could be intercepted, corrupted,
lost, destroyed, arrive late or incomplete, or may contain viruses in 
transmission. The e mail and its contents
(with or without referred errors) shall therefore not attach any liability on 
the originator or HCL or its affiliates.
Views or opinions, if any, presented in this email are solely those of the 
author and may not necessarily reflect the
views or opinions of HCL or its affiliates. Any form of reproduction, 
dissemination, copying, disclosure, modification,
distribution and / or publication of this message without the prior written 
consent of authorized representative of
HCL is strictly prohibited. If you have received this email in error please 
delete it and notify the sender immediately.
Before opening any email and/or attachments, please check them for viruses and 
other defects.

[sqlite] Error while creating view through command line

[Kevin Benson]

On Thu, Apr 23, 2015 at 5:16 AM, Manoj Kumar Pasumarthi <
manojkumar.pa at hcl.com> wrote:

> Kevin, I am getting two rows as follow:
>
> 0|main|
> 2|sprdb|C:\Users\manojkumar.pa
> \Desktop\SP\DBManagementService\Data\SP_R3.s3db
>
> Regards,
> Manoj
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Kevin Benson
> Sent: Thursday, April 23, 2015 2:43 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Error while creating view through command line
>
> On Thu, Apr 23, 2015 at 4:58 AM, Manoj Kumar Pasumarthi <
> manojkumar.pa at hcl.com> wrote:
>
> > Thanks for the reply Kevin. But it didn't worked.
> >
>
> If you issue the following after you do your ATTACH:
>
>
> PRAGMA database_list;
>
>
> do you see a row returned for that database you are Attaching?



Looks like you're going to have to provide more information to the list,
about your code setup and environment, than...
...'My database is getting corrupted and I'm getting a non-SQLite error.'

--
   --
  --
 --???--
K e V i N

[sqlite] Error while creating view through command line

[Kevin Benson]

On Thu, Apr 23, 2015 at 4:58 AM, Manoj Kumar Pasumarthi <
manojkumar.pa at hcl.com> wrote:

> Thanks for the reply Kevin. But it didn't worked.
>

If you issue the following after you do your ATTACH:


PRAGMA database_list;


do you see a row returned for that database you are ATTACHing?

--
   --
  --
 --???--
K e V i N

[sqlite] Error while creating view through command line

[Kevin Benson]

On Wed, Apr 22, 2015 at 11:49 AM, Manoj Kumar Pasumarthi <
manojkumar.pa at hcl.com> wrote:

> Hi,
>
> I am trying to create a new view through command line script (sqlite3) as
> follow:
>
>
> attach 'SP_R3.s3db' as sprdb;
>
> BEGIN TRANSACTION;
>
>
> CREATE view sprdb.[view1] as select model from sprdb.[windturbine];
>
> COMMIT;
>
> .exit
>
> After executing this script, DB is getting corrupted.
>
> Can anyone please tell me how to create a new view through scripts. Sample
> view is fine.
>
> Please tell me whether it is possible or not.
>


Here's a wild hunch/guess. Try removing the underscore from your database
name:


attach 'SPr3.s3db' as sprdb;

BEGIN TRANSACTION;


CREATE view sprdb.[view1] as select model from sprdb.[windturbine];

COMMIT;

.exit


--
   --
  --
 --???--
K e V i N

[sqlite] json_* functions in sqlite

[James K. Lowden]

On Tue, 21 Apr 2015 18:09:33 -0700
Ashish Sharma  wrote:

> Many times I store JSON data in sqlite. It will be useful if sqlite
> came with functions which understand JSON. Presto has a nice set
> https://prestodb.io/docs/current/functions/json.html

In case you don't know, you could implement functions such as Presto
provides yourself with a set of user-defined functions, without any
help from the SQLite project.  

As to whether JSON should be a supported datatype in SQLite, the answer
is clearly No.  SQLite, let us note, barely recognizes datatypes at
all, and lacks even a date type.  But more than that, JSON as "native"
datatype has two fundamental problems: definition and complexity.  

Definitionally, we should require any datatype have meaningful
operations comparable to those of numbers and strings.  What does it
mean to "add" two JSON objects?  When is one less than another?  Do you
seriously want to propose as a *type* a thing that can't be a primary
key?  

The problem of complexity is that everything in the JSON blob can,
fundamentally, be represented as tables in SQLite.  I realize "modern"
tools read/write JSON, that it's the OODBMS of the Javascript set.  But
that doesn't change the fact that the JSON tree is a graph, and we know
every graph can be represented with tables. 

Why does that matter?  Because a tree-as-datatype would add a whole new
theoretical structure (graph theory) that is 100% redundant to the
relational model embodied in SQLite.  You get a bunch of new functions
and ways to get at the data.  What you do *not* get is additional query
power.  In fact you get less, because graph theory gives you less: no
subsets and no joins, to name just two.  

That's not to say there should be some rule preventing you from storing
JSON in your SQLite database.  You may find it convenient, especially if
supported with some functions that make it possible to compare (or
perhaps update) components of it, because it represents some giant
state-property that for most purposes can be treated as an integral
unit.  It is to say that every JSON-specific feature you add duplicates 
one already present (in a different form) in SQLite.  Add enough of
them and you'll replace the DBMS with itself, if you see what I mean.  

--jkl

[sqlite] Destroy all evidence of a database

[James K. Lowden]

On Wed, 22 Apr 2015 16:56:07 +0100
Simon Slavin  wrote:

> You have made me realise, however, that a nice attack against
> encrypted SQLite databases might be to crash a SQLite application
> while it's processing and examine any journal files, shared memory
> file and temporary index files.  It might be interesting to review
> the various encryption systems widely available for SQLite and figure
> out which of them would be vulnerable to such an attack.

Encryption found its way into DBMS featuredom about 10 years ago, I
guess.  I've always thought it was patently stupid.  A DBMSs job is
store data.  Encryption probably should be done in the application.
Failing that, whole-filesystem encryption solves the problem in a
general way.  For SQLite, an encrypted loopback filesystem would solve
you problem neatly, except for that "no virtual filesystem"
stipulation.  

Coming back to the problem at hand, Scott Hess suggested that you modify
the SQLite VFS to trap all calls to the underlying open(2).  In that
way ISTM you could add each opened filename to a list processed by
a function whose address is passed to atexit(3).  Assuming the task
terminates nomally (unsignalled) all registered files would be deleted
by that function. If signals also need to be dealt with (IIUC they do
not) then I would fork the process that uses SQLite and arrange for the
parent to delete the files when the child terminates.  

What I like about the VFS idea is that it's minimally dependent on
SQLite particulars.  However the code changes in the future, its
relationship to the VFS will be quite stable.  You don't need to know
how many OS files are opened, or by what name.  Just trap and record
each one.  

HTH.  

--jkl

[sqlite] How do non-SQLite DBMS communicate?

[James K. Lowden]

On Wed, 22 Apr 2015 22:28:57 +
"Drago, William @ CSG - NARDA-MITEQ"  wrote:

> When using SQLite the application program accesses the SQLite DBMS
> via its .dll file. 

The DLL is a function-call library.  A function is a named bit of
code.  To "call a function" is to jump to that named bit of code.  The
SQLite functions are part of your application.  That means they execute
in your process's virtual address space.  

> When using something like Oracle Express (a local DBMS) the
> application program is communicating with Oracle Express via some
> sort of network protocol even though there's no network involved.
> What is that called?

See "loopback" in wikipedia.  Oracle is a separate process from yours;
you application communicates with it via TCP/IP.  If the Oracle process
resides on a machine other than the one your process is running on, it
connects to that machine with TCP/IP (probably using a hostname and a
port number).  If the Oracle process is running on the same machine as
yours, your process connects to it, still using TCP/IP, but via the
loopback network, usually by the name "localhost".  

>From the point of view of Oracle and your process, there *is* a "network
involved" either way.  In the case of the loopback address, the network
is emulated by the operating system.  Many parts are missing (wires,
for one, at least ones you can see) but the ends look the same to both
players.  

--jkl

[sqlite] How do non-SQLite DBMS communicate?

[Howard Chu]

Drago, William @ CSG - NARDA-MITEQ wrote:
> All,
>
> I'm not sure how to ask this question.
>
> When using SQLite the application program accesses the SQLite DBMS
> via
its .dll file. When using something like Oracle Express (a local DBMS)
the application program is communicating with Oracle Express via some
sort of network protocol even though there's no network involved. What
is that called?
>
> My reason for asking is, I'm writing a short white paper describing
> my
use of SQLite (to encourage other engineers where I work to use it too)
and I'm trying to explain the differences between SQLite and other local
database systems. So, SQLite databases are accessed via .dll where as
other local databases run a server that is accessed via ???

The generic term is interprocess communication, IPC. You should be able 
to search on those keywords to find out anything else you want to know.

SQLite is in the class known as "embedded databases" because its code is 
embedded in the application code, instead of running in a separate 
process. There are many other DBs of this class but most of them are 
lower level (key value stores) instead of offering the SQL data model. 
Berkeley DB is another well-known example of this type, as is LMDB.

Many embedded DBs not only run within a single process, but also can 
only support read-write access to a database's files from a single 
process (i.e., no support for multi-process concurrency). Berkeley DB 
and LMDB are somewhat rare since they support transactional 
multi-process concurrency.
>
> Can anyone help me fill in those question marks?
> Thanks,
>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com

-- 
   -- Howard Chu
   CTO, Symas Corp.   http://www.symas.com
   Director, Highland Sun http://highlandsun.com/hyc/
   Chief Architect, OpenLDAP  http://www.openldap.org/project/