[sqlite] Integrating sqlite with Core Data and iCloud

[Scott Perry]

There are a lot of people who go on this journey, and I don't recommend it. If 
you want to take advantage of iCloud, it's best to use Core Data directly, 
perhaps with an API shim of your own making that makes it easier and more 
suitable to your needs.

On Apr 25, 2015, at 12:41 PM, Jeff M  wrote:
> On Apr 24, 2015, at 2:44 AM, Simon Slavin  wrote:
>> On 24 Apr 2015, at 6:59am, Jeff M  wrote:
>> 
>>> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
>>> Data.  Core Data then takes care of the iCloud issues.
>> 
>> I imagine you'd do that by writing a VFS which used Core Data for storage.  
>> Core Data could store your data in any of the formats it has drivers for, 
>> including plaintext files and SQLite.  And Core Data could store your data 
>> in any medium it has drivers for, including local storage and iCloud.
>> 
>> The result might be slow and inefficient, since you're building a DBMS 
>> (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite).
>> 
>> If you use Core Data the resulting file wouldn't look like a normal SQLite 
>> database.  Core Data stores objects.  It doesn't store the rows and columns 
>> you refer to with SQL commands.  Maybe your objects would be table rows.
>> 
>> Simon.
> 
> Originally, I wanted to map my tables, rows, and columns to similarly-named 
> Core Data entities and attributes so I could continue to use the SQL 
> language.  I was hoping to hook into the SQL parser to get the benefits of 
> where.c, but I realize now that's impractical.

This is possible, but at each step of the way there are reasons to not do it. 
You'd need to:

Create tooling that generates Core Data models from SQL schemas. This means you 
should probably give up on the ability to make schema changes at runtime. This 
code will also need to produce a shim that provides Core Data object access to 
SQLite when it needs row data.

Write a vdbe replacement (this has been done before, but I can't find a link at 
the moment) and write a replacement that converts parsed queries or vm 
operations into Core Data requests (which will then be converted back into 
SQLite). Here you will either have to give up certain queries that aren't 
supported by the Core Data request language (further limiting functionality), 
or write sufficient compatibility code (regressing performance).

If your goal is to use iCloud as the backend, this means you'll likely have to 
deal with data conflicts, probably at your shim layer. Constantly-connected 
database systems don't have to deal with this problem because they are always 
online, and its likely that the right conflict resolution decisions will vary 
based on your use case.

> Your suggestion of working at the file system level is interesting, but my 
> objects would be disk blocks.  I could use a simple Core Data model: one 
> entity (representing the entire database file) and create one object per 
> block (each having a binary attribute containing one block of data).  It 
> would be easy to map each file system read() and write() to the corresponding 
> objects.  Using Core Data as a memory array would earn me the Kludge of The 
> Year Award.  But, I see data corruption in my future.

I can't recommend this approach either, due to the conflict resolution issues I 
mention above. If you're using Core Data as a vfs layer and somehow conflict 
resolution leaves you with pages from two different forks of the database, it's 
likely that your database will be irrecoverably corrupted. You'll also lose out 
on a lot of useful features such as model migration, meaning that you'll have 
to manually orchestrate your own schema changes.

> Can you point me to some sample source code (outside of SQLite itself) that 
> implements sqlite3_vfs_register()?
> 
> Jeff
> ___
> 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

[Justin Clift]

On 25 Apr 2015, at 20:41, Jeff M  wrote:
>> On Apr 24, 2015, at 2:44 AM, Simon Slavin  wrote:
>> 
>> On 24 Apr 2015, at 6:59am, Jeff M  wrote:
>> 
>>> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
>>> Data.  Core Data then takes care of the iCloud issues.
>> 
>> I imagine you'd do that by writing a VFS which used Core Data for storage.  
>> Core Data could store your data in any of the formats it has drivers for, 
>> including plaintext files and SQLite.  And Core Data could store your data 
>> in any medium it has drivers for, including local storage and iCloud.
>> 
>> The result might be slow and inefficient, since you're building a DBMS 
>> (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite).
>> 
>> If you use Core Data the resulting file wouldn't look like a normal SQLite 
>> database.  Core Data stores objects.  It doesn't store the rows and columns 
>> you refer to with SQL commands.  Maybe your objects would be table rows.
>> 
>> Simon.
> 
> Originally, I wanted to map my tables, rows, and columns to similarly-named 
> Core Data entities and attributes so I could continue to use the SQL 
> language.  I was hoping to hook into the SQL parser to get the benefits of 
> where.c, but I realize now that's impractical.
> 
> Your suggestion of working at the file system level is interesting, but my 
> objects would be disk blocks.  I could use a simple Core Data model: one 
> entity (representing the entire database file) and create one object per 
> block (each having a binary attribute containing one block of data).  It 
> would be easy to map each file system read() and write() to the corresponding 
> objects.  Using Core Data as a memory array would earn me the Kludge of The 
> Year Award.  But, I see data corruption in my future.
> 
> Can you point me to some sample source code (outside of SQLite itself) that 
> implements sqlite3_vfs_register()?

Hmmm, searching for that function name across GitHub returns about 27k results.

16.6k+ for just C:

  https://github.com/search?l=c=sqlite3_vfs_register=Code=?

6k+ for C++:

  https://github.com/search?l=cpp=sqlite3_vfs_register=Code=?

You'll probably not be short of example stuff to drawn from... :D

Regards and best wishes,

Justin Clift

[sqlite] Integrating sqlite with Core Data and iCloud

[Jeff M]

> On Apr 24, 2015, at 2:44 AM, Simon Slavin  wrote:
> 
> On 24 Apr 2015, at 6:59am, Jeff M  wrote:
> 
>> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
>> Data.  Core Data then takes care of the iCloud issues.
> 
> I imagine you'd do that by writing a VFS which used Core Data for storage.  
> Core Data could store your data in any of the formats it has drivers for, 
> including plaintext files and SQLite.  And Core Data could store your data in 
> any medium it has drivers for, including local storage and iCloud.
> 
> The result might be slow and inefficient, since you're building a DBMS 
> (SQLite) on top of a DBMS (Core Data) on top of a DBMS (SQLite).
> 
> If you use Core Data the resulting file wouldn't look like a normal SQLite 
> database.  Core Data stores objects.  It doesn't store the rows and columns 
> you refer to with SQL commands.  Maybe your objects would be table rows.
> 
> Simon.

Originally, I wanted to map my tables, rows, and columns to similarly-named 
Core Data entities and attributes so I could continue to use the SQL language.  
I was hoping to hook into the SQL parser to get the benefits of where.c, but I 
realize now that's impractical.

Your suggestion of working at the file system level is interesting, but my 
objects would be disk blocks.  I could use a simple Core Data model: one entity 
(representing the entire database file) and create one object per block (each 
having a binary attribute containing one block of data).  It would be easy to 
map each file system read() and write() to the corresponding objects.  Using 
Core Data as a memory array would earn me the Kludge of The Year Award.  But, I 
see data corruption in my future.

Can you point me to some sample source code (outside of SQLite itself) that 
implements sqlite3_vfs_register()?

Jeff

[sqlite] Integrating sqlite with Core Data and iCloud

[Simon Slavin]

On 24 Apr 2015, at 6:59am, Jeff M  wrote:

> I don't need to map SQLite to iCloud -- I only need to map SQLite to Core 
> Data.  Core Data then takes care of the iCloud issues.

I imagine you'd do that by writing a VFS which used Core Data for storage.  
Core Data could store your data in any of the formats it has drivers for, 
including plaintext files and SQLite.  And Core Data could store your data in 
any medium it has drivers for, including local storage and iCloud.

The result might be slow and inefficient, since you're building a DBMS (SQLite) 
on top of a DBMS (Core Data) on top of a DBMS (SQLite).

If you use Core Data the resulting file wouldn't look like a normal SQLite 
database.  Core Data stores objects.  It doesn't store the rows and columns you 
refer to with SQL commands.  Maybe your objects would be table rows.

Simon.

[sqlite] Integrating sqlite with Core Data and iCloud

[Jeff M]

On Apr 23, 2015, at 3:51 PM, Simon Slavin  wrote:
> 
> 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.

It's precisely that tight integration that I want to take advantage of, but 
without using Core Data directly and losing the ability to write queries using 
SQL.  Core Data's use of SQLite is just coincidental and would not change.  
SQLite would be in the path twice:

My app --> my special SQLite --> Core Data backend --> Core Data's normal 
SQLite and iCloud.

Such a version of SQLite might be useful for people upgrading legacy apps for 
the cloud and who (like me) much prefer using the SQL language instead of Core 
Data objects.


> You might instead be asking whether you could write an app which makes SQLite 
> calls to have data stores in iCloud.

Yes, almost...

> 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.

I don't need to map SQLite to iCloud -- I only need to map SQLite to Core Data. 
 Core Data then takes care of the iCloud issues.

Also, I have no multi-user issues -- one app, one user, one database.

> 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.

It the database file was small, iCloud Drive or Dropbox might work.  But that 
fails with large files -- the user's device would be constantly syncing the 
entire database, even for small changes.  This is why Core Data works -- it 
only syncs transactions.

Jeff

[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] 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