|Wednesday, 20 August 2014|
Xamarin.Forms with Zumero
I am a Xamarin fanboy, so my excitement about Xamarin.Forms is perhaps unsurprising.
But I see an awful lot of potential for this technology. I want to show you some of
the stuff we've been doing with Xamarin.Forms here at Zumero.
Andrew Jackson in Two Minutes
First I am going to race through this demo very quickly. Then I'll circle back around
and explain things.
STEP ONE: Download ZAG and run it
and download the ZAG application. For this demo, I'm using ZAG on Mac OS X
(but you could choose Windows or Linux) and I am targetting iOS (but you could
choose Android or Windows Phone).
When you run the app, you should see something like this:
STEP TWO: New Database
Under the "File" menu, choose "New Database...".
You should see this dialog:
Fill in the Server URL and DBFile exactly as shown in the screen shot (Server URL: "http://demo.zumero.com:8080". DBFile: "demo"). Click the OK button.
You should see a dialog asking you where to save the local copy of the database:
Click OK. You should see "Syncing with the ZSS Server":
And when the sync is complete, at the bottom of the ZAG window, you should see "Sync result: 0 (success)".
STEP THREE: Generate
Under the "Generate" menu, find and choose the item labeled "Xamarin.Forms C#":
You will be asked three questions, and you should be able to just click OK on all
STEP FOUR: Open the sln file
You can quit the ZAG application now. It should have generated a folder somewhere like
/Users/eric/Documents/demo.zssdemo/. Under that folder you should find a
Xamarin solution tree that looks something like this:
Double-click the demo.sln file to open it in Xamarin Studio. You should see
four C# projects: a Portable Class Library called "demo.Shared", plus one app
target each for iOS, Android, and Windows Phone 8:
STEP FIVE: Build and run the app
If you build and run the demo.iOS app in the iPhone simulator, you should
see something like this:
STEP SIX: Sync
Click the "Sync" button in the upper right. You should see:
The defaults should be fine. Just tap the "Sync Now" button. When the
sync is completed, you should see a list of tables:
STEP SEVEN: Andrew Jackson
Tap the "presidents" table. In an iPad instance of the simulator, you would see this:
And tap the seventh item. You should see Andrew Jackson, the only U.S.
president ever to kill someone in a duel:
What is ZAG?
ZAG is short for "ZSS App Generator". It's a desktop app
which generates ready-to-build source code and build scripts
for mobile apps that sync using ZSS.
We think of ZAG as a way for getting people started faster.
Many people come to our product without much experience in
mobile app development. ZAG can be used to give them
a starting point, sort of like sample code that is customized
for their data.
What is Zumero for SQL Server?
Zumero for SQL Server (ZSS) is a solution for
data sync between SQL Server and mobile devices.
More info from Dan Bricklin about offline in mobile apps: http://bricklin.com/offline.htm
More info about Zumero on our website: zumero.com
More info about Zumero from my previous blog entry: here
What is demo.zumero.com:8080?
This is a publicly accessible ZSS server provided so that
folks can play with Zumero clients more easily. It contains
some basic sample data such as U.S. presidents and the
periodic table of the elements.
In real-world scenarios, a customer would use ZAG to generate
their starter app after they have completed setup of their ZSS
What is Xamarin?
Xamarin is (IMHO) a great solution for building mobile apps.
One of the main benefits of the Xamarin platform is the ability to
write the non-UI parts of your iOS/Android/WP8 apps in cross-platform
code while implementing a native user interface for each mobile environment.
But I would use Xamarin even for a single-platform app, simply to get
the benefits of working in .NET/C#.
More info on the Xamarin website: http://xamarin.com/
What is Xamarin.Forms?
Xamarin.Forms is Xamarin's solution for making [most of] your
UI code cross-platform as well, while retaining fully native
performance and feel.
In a nutshell, the coolness of Xamarin.Forms lies in the fact
that in the solution generated by ZAG above, the demo.Shared project is a Portable Class Library
even though it contains the entire user interface for the app.
More info on the Xamarin website: http://xamarin.com/forms
What is a Portable Class Library (PCL)?
A PCL is a .NET class library that is annotated with information
about which platforms it should support. This metadata allows the
tooling to enforce portability rules in both the development and the
consumption of the library.
More info on Scott Hanselman's blog: http://www.hanselman.com/blog/CrossPlatformPortableClassLibrariesWithNETAreHappening.aspx
More info on the Xamarin website: http://developer.xamarin.com/guides/cross-platform/application_fundamentals/pcl/introduction_to_portable_class_libraries/
Why does ZAG generate separate projects for iOS, Android, and WinPhone?
Xamarin.Forms can make most of your UI code portable, but not all of it.
The actual building of the mobile app is specific to each platform. But
if you look in the code for each of those platform-specific projects,
you'll see that there isn't much there.
What dependencies does the ZAG-generated app have?
The following NuGet packages will need to be retrieved:
What is the "SQLite-net PCL" NuGet package?
This is the Portable Class Library (PCL) version of SQLite-net,
the popular lightweight SQLite ORM by Frank Krueger (@praeclarum).
More info on GitHub: https://github.com/praeclarum/sqlite-net
More info on the NuGet website: https://www.nuget.org/packages/sqlite-net-pcl/
What is the "SQLitePCL.raw_basic" NuGet package?
SQLitePCL.raw is my Portable Class Library for accessing
SQLite from C#.
More info on Github: https://github.com/ericsink/SQLitePCL.raw
More info on the NuGet website: https://www.nuget.org/packages/SQLitePCL.raw_basic/
What is SQLite?
SQLite is the most popular SQLite database for mobile devices.
More info on the SQLite website: sqlite.org
What is the "Zumero" NuGet package?
This is the Zumero Client SDK in the form of a Portable Class Library in a NuGet package.
More info on the Zumero website: http://zumero.com/dev-center/zss/
More info on the NuGet website: https://www.nuget.org/packages/Zumero/
What do Zumero's client-side SQLite files look like?
As much as possible, they look exactly like they looked in
Table and column names are the same.
All data values are the same (whenever possible).
Foreign keys in SQL Server are reconstructed as foreign keys in SQLite.
Since SQLite does not perform type checking, Zumero adds constraints to do so.
And so on...
What's happening in step two?
ZAG is acting as a Zumero client and synchronizing the data on the server
into a local SQLite file. This file is used to obtain information about the
tables and columns necessary to generate the mobile app.
The same sync is happening in step six, except then it is the
mobile app performing the sync instead of ZAG.
What were those three questions in step three?
The first one is the project name:
Then ZAG wants to know the settings for your sync server. These should
already be filled in with the ones you entered earlier:
Finally, ZAG is asking you where to save the source code and project files
for the app to be generated:
Does a ZAG-generated app allow modifications to the data?
For the Xamarin.Forms C# target, yes. On the item detail page,
you should be able to enter new values in text fields and 'Save' the
But you should get a permission denied error if you try to sync
those changes to our public demo server. :-)
Is ZAG generating UI stuff as XAML or as C# code?
Currently, it's XAML. You'll find the files in the 'xaml' folder
in the demo.Shared project.
Does ZAG generate polished ready-to-use apps?
Oh definitely not. :-)
The output of ZAG should build and run with no
errors (if it doesn't, it's a bug, and please let us know), but
it's just a starting point for further development.
|Friday, 27 June 2014|
Billions without Buzz
I've been thinking a lot lately about the distorted perspective I get when I
extrapolate from my daily sources of content.
Using Twitter buzz to sip from a firehose
I currently follow 304 people on Twitter. These people are a primary
means for me to hear about stuff that is (1) happening, and (2) important to me.
I rely more on echoes than voices. For example, I don't follow
Satya Nadella (@satyanadella). But whenever he tweets something I would find important, I hear about
it anyway, because a dozen people I do follow are talking about it.
Twitter for me is all about conversations. It's about
buzz. I'm interested in what people are talking about. And I like having
the option of
participating in the chatter.
And the people I choose to follow pretty much cover everything
important. I don't miss anything that I need to know.
Or do I?
What am I missing?
Obviously, my method is designed to exclude information. Sometimes
my buzz filter works exactly as I want.
I rarely see any tweets about Miley Cyrus. That is "by design".
I also like the fact that buzz is naturally slanted toward things that are new
even though lots of very important things are old. (As long as I
don't get confused. If I extrapolate from buzz with a
black-and-white mentality, I might believe that
COBOL, SQL, and Subversion are dead because everybody has switched to Java,
Mongo, and Git. Older technologies have little or no buzz, but tons of
people are still using them everyday.)
The problem is the stuff that is new and important (to me) but has very
The boring wave of B2B apps
I am absolutely convinced that the B2B wave of mobile apps has barely
As Benedict Evans (@BenedictEvans) says, mobile is eating the world. But so far, most
of the action is with consumers, not businesses.
And this whole wave has been very high-buzz, mostly because everything had
to get bigger, and the stuff that could not was, er, "disrupted".
Add two billion mobile devices to the world and lots of people
are forced to think about scale in a whole new way:
Manufacturing. Foxconn is enormous. What was the
biggest manufacturing facility on earth before the iPhone?
Venture Capital. I remember when $100M was an exit. Now it's a series A.
Servers. Mobile is accelerating the growth of the cloud, served by
data centers that make Soldier Field look small.
Databases. When you have 500 million users, you can't afford unique
constraints and foreign keys in the db layer anymore.
All this big-ness is causing a lot of disruption. And buzz.
Meanwhile, the majority of the corporate world is still trying to figure
out what to do about mobile.
Consider this in terms of the classic marketing bell curve:
In the consumer world, mobile is
in the conservatives, and starting to sell to the laggards.
In the corporate world, mobile is in the early adopters. It's pre-chasm.
still need high growth to justify their stock price (read: Apple) are distressed about
the notion that everybody on the planet who can afford a smart phone
already has one.
reseach firms are still doing surveys asking corporate IT about when
they are going to dive into mobile even as my Mom has
Has there ever been a technology wave where the consumers
got so far ahead of business?
I was one of those people in the early
80s who had a computer before they were prevalent in the business world.
But I was a hobbyist and a nerd. In terms of volume and sheer revenue,
adoption of PCs was driven by companies, not consumers. They wanted
to run Lotus 1-2-3, so they led the way. It was a long time before computers
solved real problems for consumers in the way that they solved real
problems for business. In fact, I'd argue this didn't happen until around
1995 when the Web came along.
In mobile devices, consumers have gotten so far ahead that they have
provided the client side of the infrastructure that Corporate IT will use.
Going forward, it's going to be BYOD (Bring Your Own Device).
Big companies are not going to buy 10,000 BlackBerries for their workforce
when everybody already has a smart phone. Instead, they have to figure out
how they're going to securely integrate all these different devices into
their corporate systems (which is why cross-platform is getting even more
important in the mobile space, which is contributing to Xamarin's pursuit of
world domination). This is not the kind of policy to which Corporate IT is accustomed,
and that is slowing them down even more.
But this is going to happen. A lot of enterprise apps are going to get written.
And this wave will be very low-buzz compared to the "Angry Birds and Candy Crush" wave.
People won't be talking about it (and even when they do, the sound will get
drowned out by the buzz over the Internet of Things wave).
Quite simply, the B2B apps wave is not interesting enough to get serious buzz:
It won't be as disruptive.
It won't re-challenge our notions of scale. (Walmart's
2.2 million employees would be a small user base by today's standards.)
It's going to be built on, and integrated with, technologies that are old.
will prefer to
add mobile incrementally, with as little
change to existing systems as possible.)
Compared to my first computer(s), an iPhone is a supercomputer.
We are moving into a wave where companies like Procter & Gamble
are going to gain operational efficiency
because all their employees carry supercomputers in their pocket.
And this will be considered boring.
But despite all the yawns, this wave is going to involve a lot of money. Many billions of dollars
are going to move around as all these enterprise apps get written.
In other words, this is going to be boring in the same way that IBM has been boring
for the last decade. This week, my Twitter feed is dominated by people
talking about the Google I/O conference. Nobody is talking
about IBM, even though they have almost twice as much revenue as Google.
That's how buzz works.
If I want to closely follow something that doesn't have much buzz,
I'm gonna have to work harder.
Let's talk about Alex Bratton
I am currently reading a book called Billion Dollar Apps, by Alex Bratton (@alexbratton),
CEO of Lextech, a custom mobile app dev shop in Chicago.
My buzz filter certainly didn't find this book for me:
@alexbratton has even fewer Twitter followers than I do.
@LextechApps has fewer followers than some high school students I know.
Almost all of Bratton's followers are people I don't know.
Google searches reveal no apparent connection between Lextech and Xamarin.
Twitter searches suggest that Bratton and Lextech have seldom or never been mentioned by Scott Hansleman (@shanselman).
There just aren't many connections between Lextech's world and mine. In
fact, it looks like maybe I am the only occupant of the overlapping portion of
the Venn diagram.
Maybe Lextech just doesn't have much buzz.
Or maybe Lextech has the kind of buzz that happens mostly off Twitter. Maybe the CIOs
of Fortune 500 companies share rides on their private jets where they sip Macallan 25 and talk about
how great Lextech is.
Whatever. I've been following Alex Bratton on Twitter largely because he and I were
students at UIUC around the same time, and I sort-of vaguely remember him.
Were it not for this very thin college connection, I might
never have heard of him or his company or his book.
And that would be sad, because Lextech's activities land squarely in an area
of interest for me. Bratton's company may be low-buzz, but they're building
B2B mobile apps for banner-name companies including GE, Fidelity, John Deere, Blue Cross, and H&R Block.
They're riding the front of the wave. I think that's pretty darn cool.
So I am halfway through the book, and two things are already quite clear:
The book is really good.
I find it boring.
Basically, I'm not enjoying this book because it was clearly not
written for people like me.
I enjoy fiction books about crime investigation
and books with lots of curly braces and semicolons. Billion Dollar Apps is
neither of these. It's written for CxOs at big companies who have to make big
decisions about mobile technology.
But I'm forcing myself to read this book for the same reason I force myself to eat
certain unappealing vegetables. I don't like the taste, but I think it's good for me.
Thinking about Lextech and its customers is forcing me to widen my
perspective. It is reminding me that the boredom goes both ways:
Most developers don't care about the details of how John Deere is
using mobile to make its business run better. They tend to focus more on the
technology than on solutions to problems.
Probably nobody at John Deere is spending their time critiquing the
design of Xamarin.Forms or figuring out best practices for PCLs. They see
mobile technology as a solution to their problems.
This book is good because it speaks to its intended
audience, and that group of people doesn't care about the details of why certain
.NET things are incompatible with the AOT compiler in
Xamarin.iOS. Billion Dollar Apps is a book that talks about sensibly applying mobile technology to make
regular non-geek businesses work better.
Like most other geeks, I am prone to getting lost in technology
for its own sake. But I'm not just a developer. I'm an entrepreneur. I need
to keep a sense of balance.
So, I don't particularly want to read this book, but I need to read it.
Bratton's book is about the beginning of a big boring bazaar where beaucoup billions bounce bereft of buzz.
(Sorry, the forced alliteration was shameful, but I couldn't resist.)
I've wandered around a bit, so let me close with a summary of key points
you might take away from this blog entry:
Consider reading Alex Bratton's book.
If you are a CxO of a Fortune 500 company (and you probably are not,
because you're reading my blog), you'll probably like it.
If you are a developer (and you probably are, because you're reading my
blog), you probably won't like it any more than I do. Eat your brussel
Widening your perspective is always good advice.
If you're like me, there's a really good chance that your daily information flow
has boundaries. Those boundaries make you efficient, but they also constantly
protect you from seeing the perspective of people who are not like you. Look outside your
If you are interested in the boring B2B wave of mobile apps, so am I.
Maybe we should talk. Maybe I should be following you on Twitter.
Maybe we should see if we can create a little buzz.
|Wednesday, 4 June 2014|
SQLitePCL.raw NuGet package, 0.2.0-alpha
I have pushed up the second release of the NuGet package(s) for SQLitePCL.raw. There are no API changes, and all tests are passing on all my target platforms (Xamarin.iOS, Xamarin.Android, .NET 4.5, WinRT 8, WinRT 8.1, Windows Phone 8, Windows Phone 8.1 RT, and Windows Phone 8.1 Silverlight).
Changes in this release
Tweak the net45 platform assembly to [hopefully] make it compatible with Mono on non-Windows platforms
Better error messages for build configuration problems
Bundled builds of the sqlite3 library are now compiled with foreign keys defaulting to on
SQLitePCL.tests, a separate NuGet package containing my test suite in source code form
SQLitePCL.ugly, a separate NuGet package containing the "not-in-C#-style" API which I use
for writing the test suite
Changes still coming [soon]
A release which is polished enough for real use
Release notes in the nuspec instead of here in a blog entry
Hopefully, automatic insertion of the Visual C++ Runtime SDK reference on RT-ish platforms
Support for sqlite-net in a
NuGet package. I've been coordinating with @praeclarum and @timheuer on this. He/they accepted my pull request with the
necessary changes (thanks!). And my build system is generating a
csproj/nuspec for proof of concept purposes. Almost there.
SQLitePCL.kitchensink, a NuGet package for advanced users, containing all of the configurations
Better support for SQLCipher
|Friday, 23 May 2014|
First NuGet package for SQLitePCL.raw
I have pushed up the first NuGet package for SQLitePCL.raw.
Is this ready for use on mission-critical applications?
I gave this a version number of "0.1.0-alpha".
By including the -alpha part, I signal to NuGet that this is a pre-release package.
By using 0.1.0 as the version number, I signal to human beings that if you
use this package, everything in your
life will go wrong. Your dog will leave you for someone else. Your favorite TV
show will get canceled. A really dumb VC will send you a term sheet.
This is the first NuGet package, not the last. I gotta start somewhere.
So this package doesn't work at all?
Well, actually, no, it shouldn't be that bad. Underpromise and overdeliver.
I have run my test suite against this package for all of the following environments:
Xamarin.Android (emulator, API level 15)
WinRT 8.1 (x86, on Windows 8.1)
Windows Phone 8.0 (in the emulator)
Windows Phone 8.1 (RT flavor, in the emulator)
Windows Phone 8.1 (Silverlight flavor, in the emulator)
.NET 4.5 (on Windows 8.1)
On all the Windows platforms, the tests all pass. For iOS and Android, the only
failures are the expected ones.
Why do some of your tests fail on iOS and Android?
Because the version of SQLite which is preinstalled by Apple/Google is a bit old,
and some of my tests are explicitly exercising newer SQLite features.
So is this NuGet package ready for testing?
Eric, I am much smarter than you. Can I give you some constructive feedback?
Why is the word "basic" in the name of this package?
I think it likely that I may end up with more than one NuGet package for SQLitePCL.raw.
Different packages for different use cases. So I need them to have different names.
This one is "basic" in the sense that it tries to be the package that most
people will want to use. All of the other contemplated packages would have some sort of less-appealing
word in the name, designed to scare people away. The label for the next one might be "broccoli".
The main tradeoff is the issue of where your app is getting SQLite itself. For
[much] more detail on this, see my recent blog entry on The Multiple SQLite Problem.
Anyway, for this "basic" package, the iOS and Android assemblies use the
SQLite library which is part of the mobile OS, and all of the Windows
assemblies bundle a sqlite3.dll.
Which version of SQLite is bundled on the Windows platforms?
How was the bundled SQLite library compiled?
Can I rely on all future versions of this package having the SQLite library compiled with exactly those options?
How do I find this package?
Direct link: https://www.nuget.org/packages/SQLitePCL.raw_basic
And, it comes up for me if I just search for "SQLitePCL" on NuGet.
Hey, there are TWO results of that search? What's the deal?
Mine is the one called SQLitePCL.raw. The other one is by MSOpenTech.
And actually, my work is a [hopefully friendly] fork of theirs. Thanks to
those folks (whoever they are) for a solid starting point. I am available to
collaborate with them if there is interest.
Why did you fork the other one?
See the README on GitHub for some info on this.
Any caveats when trying to use this on iOS?
AFAIK, no. For me, this Just Works.
Any caveats when trying to use this on Android?
AFAIK, no. For me, this Just Works.
Any caveats when trying to use this on .NET 4.5?
You need to compile for a specific CPU (x86, x64), not "Any CPU". In Visual Studio, right-click on your solution and choose Configuration Manager.
Any caveats when trying to use this on Windows Phone 8.0?
Not really. Just make sure you are building for x86 for the emulator or ARM for an actual device. In Visual Studio, right-click on your solution and choose Configuration Manager.
Any caveats when trying to use this on Windows RT or Windows Phone 8.1?
Build failure: You need to compile for a specific CPU (x86, x64, ARM), not "Any CPU". In Visual Studio, right-click on your solution and choose Configuration Manager.
Runtime failure, file not found: You need to add a reference to the Visual C++ 2013 Runtime. Hopefully a future version of this package will automatically add this reference for you.
What other forms of this package are you planning?
I'm considering one that doesn't bundle any SQLite instances at all. For use with cases where somebody wants to have their own build of SQLite. Or for people who want to use the SQLite vsix SDK builds on visualstudiogallery.msdn.microsoft.com.
Why are some of the platform assemblies in the build directory instead of the lib directory?
Android and iOS are in lib.
All the Windowsy ones are in build, because they're all CPU-specific, so
they need more help than lib can provide. An MSBuild .targets file is used to
inject the appropriate reference.
|Tuesday, 20 May 2014|
Ever have a day like this one?
Check email and notice a message from somebody having trouble using
SQLitePCL.raw on Windows Phone 8.1. Realize that I haven't run the test
suite since I started working on the new build scripts. Assume that I
Hook up the automated test project to the output of the new build system.
Sure enough, the tests fail.
Notice that the error message is different from the one in the user's email.
Realize that the user is actually using the old build system, not the new one.
Wonder how that could have broken.
Bring up the old build system, run the tests. Yep, they fail here too. Must
be something in the actual code.
Dig around for a while and try to find what changed.
Use git to go back to the last commit before I started the new build system
stuff. Rebuild all. Run the tests. They pass. Good. Now I just have to
diff and figure out which change caused the breakage.
git my working directory back to the current version of the code. Rebuild all
and run the tests again to watch them fail again. BUT NOW THEY PASS.
Wonder if perhaps Visual Studio is less frustrating for people who drink
Scotch in the mornings.
Decide that maybe something was flaky in my machine. The tests are passing
again, so there's no problem.
Realize that the user wasn't actually running the test suite. He was trying to
reference from his own project. And he had to do that manually, because I haven't
published the nuget package yet. Maybe he just screwed up the reference or didn't
copy all the necessary pieces.
Run the tests in the new build system to watch them pass there as well. But
here they STILL FAIL.
Decide to take the build system out of the equation and just finish getting things working
right with nuget. Build the unit test package separately in its own solution. Add a reference
to the nuget package and start working out the issues.
Run the tests. Everything throws because the reference got added to the "bait" version
of the PCL instead of the to the WP81 platform assembly. Oh well. This is what I need
to be fixing anyway.
Notice that the .targets file didn't get properly imported into the test project when the
package was installed. Wonder why. But that's gotta be why the platform assembly didn't
Realize that the bait assembly somehow got referenced. Wonder why.
What is Scotch anyway? Go read several articles about single malt whiskey.
Decide to take nuget out of the equation and focus on why the new build system
is producing dlls that won't load.
Google the error message "Package failed updates, dependency or conflict validation".
I need to know exactly what was the cause of the failure.
Realize that the default search engine or IE is Bing. Do the same search in Google.
Get different results.
Become annoyed when co-worker interrupts me to tell me that there is a new trailer for Guardians of the Galaxy.
Read a web page on the Microsoft website which explains how to get the actual details
of that error message. Spend time wandering around Event Viewer until I see the
Realize that the web page is actually talking about WinRT on the desktop, not Windows Phone.
Try to find a way to get developer-grade error messages in the Windows Phone emulator.
Notice that below the error message, Visual Studio's suggested resolution is to instead use a
unit test project that is targeted for Windows Phone, even thought IT ALREADY IS.
Blame Steve Ballmer FOR EVERYTHING.
Wonder if WP81 is the only thing that broke. Run the tests for WinRT. They fail as well.
Get annoyed because the only way Visual Studio can run the unit tests for
just one project is to unload all the others.
Get upset because the Visual Studio Reload Project command doesn't work like the way it did
a week or two ago. Now it reloads all the projects instead of just the one I wanted.
Did the installation of the Xamarin Visual Studio integration break it?
Go back to the very basics. Run the unit tests for plain old .NET 4.5. They pass.
Re-run the unit tests for WinRT to watch them fail again. NOW THEY PASS.
Realize the co-worker is absolutely right. The most important thing is to watch
the Guardians of the Galaxy trailer.
Get annoyed because the sound on my MBP isn't working. Watch the whole trailer
anyway, without sound.
Review all my project settings in the Visual Studio dialogs, just to see if I
notice anything odd.
Go back to my web browser. Realize that the world of Scotch whiskey might actually
be more complicated than Visual Studio.
Go home. Discover that the annual spring invasion of ants in our kitchen is
Fight some more with Visual Studio. Give up. Go to bed.
Wake up the next morning. Discover that the teenager's contribution to our
war against the ants was to leave unrinsed plates by the sink. Thousands of
ants feasting on cheesecake debris and syrup.
Open the laptop. Run diff to compare the csproj and vcxproj files from the old build system
against the new one. See that there are no differences that should make any difference.
Change them all anyway. Update every setting to exactly match the old
build system. One at a time. Run the test suite after each tweak so I can
figure out exactly which of the seeminlgy-harmless changes caused the
Wait. My kid had cheesecake and waffles FOR DINNER?
Become seriously annoyed that Visual Studio changes the Output pane
from "Tests" to "Build" EVERY SINGLE TIME I run the tests.
Finish getting all the settings to match. The tests still don't pass.
Try to remember if I ever done anything successfully. Anything at all.
Distinctly recall that when I was mowing the lawn this weekend, the grass
got shorter. Focus on that accomplishment. Build on that success.
Realize that the old build system works and the new one doesn't. There has
to be a difference that I'm missing. I just have to find it.
Go back to the old build system. Rebuild all. Run the tests so I can watch
them pass and start over from there. BUT NOW THEY'RE FAILING AGAIN.
Go do something else.
|Friday, 16 May 2014|
The Multiple SQLite Problem
Eric, why the #$%! is your SQLite PCL taking so long?
It's Google's fault. And Apple's fault.
No. Yes. Kinda. Not really.
The Multiple SQLite Problem, In a Nutshell
If your app makes use of two separate instances of the SQLite library,
you can end up with a corrupted SQLite data file.
From the horse's mouth
On the SQLite website, section 2.2.1 of How to Corrupt an SQLite Database File is entitled "Multiple copies of SQLite linked into the same application", and says:
As pointed out in the previous paragraph, SQLite takes steps to work around
the quirks of POSIX advisory locking. Part of that work-around involves
keeping a global list (mutex protected) of open SQLite database files. But,
if multiple copies of SQLite are linked into the same application, then
there will be multiple instances of this global list. Database connections
opened using one copy of the SQLite library will be unaware of database
connections opened using the other copy, and will be unable to work around
the POSIX advisory locking quirks. A close() operation on one connection
might unknowingly clear the locks on a different database connection,
leading to database corruption.
The scenario above sounds far-fetched. But the SQLite developers are aware
of at least one commercial product that was released with exactly this bug.
The vendor came to the SQLite developers seeking help in tracking down some
infrequent database corruption issues they were seeing on Linux and Mac.
The problem was eventually traced to the fact that the application was
linking against two separate copies of SQLite. The solution was to change
the application build procedures to link against just one copy of SQLite
instead of two.
At its core, SQLite is written in C. It is plain-old-fashioned
native/umanaged code. If you are accessing SQLite using C#, you are doing so
through some kind of a wrapper. That wrapper is loading the SQLite library
from somewhere. You may not know where. You probably don't [want to] care.
This is an abstraction. And it can leak. C# is putting some distance
between you and the reality of what SQLite really is. That distance can
somewhat increase the likelihood of you accidentally having two instances
of the SQLite library without even knowing it.
SQLite as part of the mobile OS
Both iOS and Android contain an instance of SQLite as part of the basic
operating system. This is a blessing. And a curse.
Built-in SQLite is nice because your app doesn't have to include it.
This makes the size of your app smaller. It avoids the need to compile
SQLite as part of your build process.
But the problem is that the OS has contributed one instance of the
SQLite library that you can't eliminate. It's always there. The
multiple SQLite problem cannot happen if only one SQLite is available
to your app. Anybody or anything which adds one is risking a plurality.
If SQLite is always in the OS, why not always use it?
Because Apple and Google do a terrible job of keeping it current.
iOS 7 ships with SQLite 3.7.13. That shipped in June of 2012.
Android ships with SQLite 3.7.11. That shipped in March of 2012.
Since Android users never update their devices, a large number of them
are still running SQLite 3.7.4, which shipped in December of 2010. (Yes, I know
the sweeping generalization in the previous sentence is unfair. I like Android a lot, but I think
Google's management of the Android world has been bad enough that I'm
entitled to a little crabbiness.)
If you are targeting Android or iOS and using the built-in SQLite library,
you are missing out on at least TWO YEARS of excellent development work by
DRH and his team. Current versions of SQLite are significantly faster,
with many bug fixes, and lots of insanely cool new features. This is just one of the
excellent reasons to bundle a current version of SQLite into your app
instead of using the one in the OS.
And as soon as you do that, there are two instances in play. You
and Apple/Google have collaborated to introduce the risk of database corruption.
AFAIK, no version of Windows includes a SQLite library. This is a blessing.
And a curse. For all of the opposite reasons discussed above.
In general, building a mobile app for Windows (Phone or RT or whatever) means
you have to include SQLite as part of the app. And when doing so, it certainly
makes sense to just use the latest version.
And that introduces another reason somebody might want to use an
application-private version of SQLite instead of the one built-in to iOS or
Android. If you're building a cross-platform app, you probably want all
your platforms using the same version of SQLite. Have fun explaining to your
QA people that your app is built on SQLite 3.8.4 on Windows and 3.7.11 on
Android and 3.7.13 on iOS.
BTW, it's not clear how or if Windows platforms suffer from the data corruption
risk of the multiple SQLite problem. Given that the DRH explanation talks about
workarounds for quirks in POSIX file locking, it seems likely that the situation
on Windows is different in significant ways. Nonetheless, even if using multiple
SQLite instances on Windows platforms is safe, it is still wasteful. And sad.
SQLCipher or SEE
Mobile devices get lost or stolen.
A significant portion of mobile app developers want their data encrypted on
the device. And the SQLite instance built-in to iOS and Android is plain,
with no support for encryption.
The usual solution to this problem is to use SQLCipher (open source, from
Zetetic) or SEE (proprietary, from the authors of SQLite). Both of these
are drop-in replacements for SQLite.
In other words, this is yet another reason the OS-provided SQLite
library might not be sufficient.
SQLite compilation options
SQLite can be compiled in a lot of different ways. Do you want the full-text-search feature?
Do you want foreign keys to be default on or off? What do you want the default thread-safety mode to be? Do you need the column metadata feature?
Do you need ICU for full Unicode support in collations? The list goes on and on.
Did Apple or Google compile SQLite with the exact set of build options your app needs? Maybe. Or maybe your app just needs to have its own.
Adding a SQLite instance without knowing it
Another way to get two SQLite instances is to add a component or library
which includes one. Even if you don't know.
For example, the client side of Zumero (our mobile SQL sync product) needs
to call SQLite. Should it bundle a SQLite library? Or should it always call
the one in the mobile OS (when available)?
Some earlier versions of the Zumero client SDK included a SQLite instance
in our Xamarin component builds. Because, why on earth would we want our
code running against the archaic version of SQLite provided by Apple and
And then we had a customer run into this exact problem. They called
Zumero for sync. And they used Mono.Data.Sqlite for building their app.
Now we ship builds which contain no SQLite library instance, because it
minimizes the likelihood of this kind of accident happening.
There are all kinds of libraries and components and SDKs out there which build
on SQLite. Are they calling the instance provided by the OS? Or are
they bundling one? Do you even know?
So maybe app developers should just be more careful
Knee-jerk reaction: Yes, absolutely.
Better answer: Certainly not.
App developers don't want to think about this stuff. It's a bit of
esoterica that nobody cares about. Most people who started reading this
blog entry gave up several paragraphs ago. The ones that are still here
(both of you) are wondering why you are still reading when right now there are
seven cable channels showing a rerun of Law and Order.
An increasingly easy accident
The multiple SQLite scenario is sounding less far-fetched all the time.
SQLite is now one of the most widely deployed pieces of software in history.
It is incredibly ubiquitous, and still growing. And people love to build
abstractions on top of it.
This problem is going to get more and more common.
And it can have very significant consequences for end users.
Think of it this way
The following requirements are very typical:
App developers want to be using a current version of SQLite (because DRH has actually been working for the last two years).
App developers want their SQLite data on the mobile device to be encrypted (because even grown-ups lose mobile devices).
App developers want to be using the same version of SQLite on all of their mobile app platforms (because it simplifies testing).
App developers want no risk of data corruption (because end users don't like that kind of thing).
App developers want to work with abstractions, also-known-as ORMs and sync tools, also-known-as things that makes their lives easier (because writing mobile apps is insanely expensive and it is important to reduce development costs).
App developers want to NOT have to think about anything in this blog entry (because they are paid to focus on their actual business, which is medicine or rental cars or construction, and it's 2014, so they shouldn't have to spend any time on the ramifications of quirky POSIX file locking).
Those requirements are not just typical, they are reasonable. To ask app developers to give up any of these things would be absurd.
And right now, there is NO WAY to satisfy all the requirements above. In the terminology of high school math, this is a system of equations with no solution.
To be fair
The last several weeks of "the NuGet package is almost ready" are also due to some reasons I can't blame Apple or Google or POSIX for.
When I started working on SQLitePCL.raw, I didn't know nearly enough about MSBuild or NuGet. Anything involving native code with NuGet is pretty tricky. I've spent time climbing the learning curve. My particular way of learning new technologies is to write the code three times. The commit history on GitHub contains the whole story.
Ramifications for SQLitePCL.raw
I want users of my SQLite PCL to have a great experience, so I'm spending
[perhaps too much] time trying to find the sweetest subsets of the requirements
For example: C++/CX is actually pretty cool. I can build a single WP8 component DLL
which is visible to C# while statically building SQLite itself inside.
Fewer pieces. Fewer dependencies. Nice. But if anything else in
the app needs direct access to SQLite, they'll have to include
another instance of the library. Yuck.
Another example: I see [at least] three reasonable choices for iOS:
Use the SQLite provided by iOS. It's a shared library. Access it with P/Invoke, DllImport("sqlite3").
Bundle the latest SQLite. DllImport("__Internal"), and embed a sqlite3.a as a resource and use the MonoTouch LinkWith attribute.
Use the Xamarin SQLCipher component. DllImport("__Internal"), but don't bundle anything, relying on the presence of the SQLCipher component to make the link succeed.
Which one should the NuGet package assume that people want? How do people that prefer the others get a path that Just Works?
So, Eric, when will the SQLitePCL.raw NuGet package be ready
"I don't know the key to success, but the key to failure is trying to please everybody." -- Bill Cosby
|Wednesday, 7 May 2014|
Thoughts on the Bait and Switch PCL Trick
This blog entry started its life as comments on
The Bait and Switch PCL Trick,
an excellent blog entry by Paul Betts.
A little background
I've been working on a PCL for SQLite. It's on GitHub:
Concerns about the bait-and-switch approach
(I assume you've read the Paul Betts blog entry as well as the
Daniel Plaisted blog entry linked therein, so I'm not going to re-describe
the Bait and Switch concept from square 1.)
I first heard about "the PCL pattern now known as Bait and Switch"
several weeks ago in tweets by Miguel de Icaza
which mentioned the efforts of Xamarin guy James Montemagno.
And then the Paul Betts blog entry showed up, which named the pattern
Bait and Switch and proclaimed it to be The Right Way.
Initially, I had concerns. The technique feels fragile.
(The words "Bait and
Switch" don't really contribute to this feeling (at least for me), especially
after seeing Daniel Plaisted on Twitter saying that "the bait-and-switch concept is fundamental to PCLs, because mscorlib is different everywhere". Makes sense.)
But the technique still seems deserving of the word "Trick". The reference assembly (the actual PCL assembly, the one with a
PCL profile) and the platform assemblies don't have much tying them
together. It seems like the trick is something that just happens to work,
almost by accident, and therefore might stop working later, even though I have no reason to believe that it will.
I'm setting this worry aside because the other PCL approach (dependency injection) contains
a trick which feels just as fragile. The standard hack is for the PCL assembly
to use reflection to locate and load and instantiate the platform assembly. This
approach doesn't exactly ooze with robustness either. And in fact, the Bait and Switch
technique seems to work fine on Xamarin whereas the reflection-load technique does not.
So, both of these approaches might seem fragile, but one of them definitely is.
Two kinds of PCLs
If we accept the idea that the Bait and Switch pattern is The Right Way,
then there are several corollaries that follow.
There are two kinds of a PCLs:
A PCL which contains only non-portable code.
A PCL which contains only portable code.
There are no PCLs which contain a mixture of the two.
(To be more precise, I am defining "portable code" as code which
is compiled under a PCL profile, and "non-portable code" is code
which is, er, not compiled under a PCL profile. I am not claiming
that a PCL of type (1) is prohibited from using things like
System.String.Length. :-) )
I find it helpful put some separation between these two very different kinds of PCLs and
to explain them as follows. Perhaps others will find these explanations helpful as well.
For the purpose of this note, I will refer to PCL type (1) as a Wrapper PCL.
A Wrapper PCL is a PCL which exists to provide a portable wrapper around
something that is not portable.
A Wrapper PCL always uses the Bait and Switch pattern.
We can refer to a Wrapper PCL in the singular, but it is actually multiple
assemblies. There is the PCL assembly itself (the one that claims to be
portable according to some profile). And there are platform assemblies,
non-portable assemblies which play the role of the PCL assembly in real
Any consumer of a Wrapper PCL would need to reference exactly one of its
assemblies. A library can reference the PCL assembly itself. An app
must reference one of the platform assemblies.
With a Wrapper PCL, the PCL assembly itself exists only so that libraries
can have something to reference. It is nothing more than a placeholder.
With a Wrapper PCL, the PCL assembly itself contains only stub functions.
Those stub functions never get executed. They might do nothing. They might
throw. They might launch nethack. Nobody knows. Nobody cares.
A Wrapper PCL, despite the presence of "Portable Class Library" in its name,
contains no portable code that ever gets used.
A Wrapper PCL claims a PCL profile (such as profile 78, for example), but
that profile claim is mostly just a list of which platform assemblies
are supposed to be present in the package.
With a Wrapper PCL, we don't care at all about the fact that our compiler
and tooling can gripe about the use of non-portable things (except of
course to complain if somebody actually does try to launch nethack from
a stub). There is no portable code being written, so we don't need the
compiler evaluating our ability to write things that are portable.
There is no special tooling to help ensure that all of the assemblies
within a Wrapper PCL actually do implement the same interface, although
it's not hard to figure out ways of making this happen by code sharing
across all the assemblies.
As for PCL type (2) in the list above, I will refer to this as a
A Feature PCL is a library of portable code. I'll generously assume
that it has some reason to exist. It therefore provides some sort of
functionality. Or a feature. So I call it a Feature PCL.
A Feature PCL does not use Bait and Switch. Or Dependency Injection. It
has no need for such things.
A Feature PCL is not allowed to have non-portable code in it. If it did,
it would not be a PCL. Or it would violate The Right Way, so its offending
non-portable portions should be abstracted out into a separate PCL using
the Bait and Switch pattern.
A Feature PCL is allowed to use things that are allowed by its profile.
Or it is allowed to reference other PCLs that are compatible with that
profile. It is not allowed to reference anything else. The compiler
and tooling help ensure this.
A Feature PCL is [conceptually] just one assembly. It might be implemented
using multiple assemblies. That's fine. What is does NOT have are a
set of assemblies that are designed to be alternatives.
I'm still exploring and experimenting. Feedback welcome, but my
blog software doesn't support comments. I'm @eric_sink on Twitter. I'll also keep an eye on
the Paul Betts blog entry for any further discussion that happens there.
|Friday, 18 April 2014|
(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)
First, go read this blog entry by Miguel de Icaza. Right now. I'll wait.
Welcome back. Now let me apologize. I don't want to be a pedantic
jerk who quibbles about minor details. But the topic here is Unicode, so there
really is no other way.
All Unicode, all the time
The relevant difference with SQLite is easy to describe:
In the world of Windows and SQL Server, you have all kinds of possible code pages.
In SQLite, everything is Unicode.
But if you don't have much context on these issues, I haven't really told you much. Let's go further.
But let's not go too far
I don't want to rewrite articles that have already been written quite well. So you should probably also go read this blog entry by Joel Spolsky. I'll be here when you get back.
OK, now let's get started
SQL Server has two basic ways you store text:
You can use the char/varchar types, which can be used with one of
several collations, each of which implies a specific code page, which implies a
specific character encoding.
Or you can use the nchar/nvarchar types (note the extra 'n'), which
SQLite has no such distinction. All text in SQLite is Unicode.
What the heck is Unicode again?
It's a character set: a collection of characters, each with a number that can be
used to refer to it.
More specifically, it's the only character set which is [trying to be] complete.
If you choose any character set or encoding which is not Unicode, there will be
characters you cannot use.
And what's an encoding?
Saying that SQLite uses Unicode doesn't tell you how the text is actually represented.
Unicode is not an encoding. It
is more abstract than that. There are lots of different ways of representing
Unicode as bytes.
Microsoft's sad history with Unicode
In the Windows and SQL Server world, there is a long history of encoding
Unicode in 16-bit-ish ways. Originally, this was UCS-2, a simple encoding
which represents each character as a 16-bit number. But then the Unicode
consortium realized that 16 bits are not enough, so they expanded the space to 32
bits. This left Microsoft in an awkward spot. UCS-2 is a fundamentally
defective encoding of Unicode, since there are many characters in Unicode that
simply cannot be represented.
If the goal of Unicode is to be complete, it is reasonable to say that, well actually, UCS-2
is not Unicode.
The conceptual replacement for UCS-2 is to use 32 bits for every character.
This encoding is called UCS-4 or UTF-32. But now the wasted space for storing
a simple English string is getting out of hand. Switching the complete works
of Arthur Conan Doyle from ASCII (which is also an encoding) to UCS-4 would take
four times as much space.
Gradually, the world seems to be adopting UTF-8 as the most
popular Unicode encoding. This is a variable width encoding. Sometimes a
single character is represented with just one byte. Sometimes it needs more.
That's very unfortunate, but the only fixed width alternative is UCS-4,
which is also very unfortunate. Choose which problem you prefer,
but keep in mind that almost everybody has chosen to accept the problems of
But Microsoft has so much history with UCS-2 that transitioning everything
to UTF-8 would be really hard. So they have been moving from UCS-2 to UTF-16,
which is basically a variable width encoding built around a 16-bit unit instead
of an 8-bit unit. UTF-16 is approximately the worst correct way of representing Unicode, unless you
have invested billions of dollars in the fundamentally broken UCS-2, in which
case UTF-16 is a pretty awesome way out of the mess you ended up in.
Just remember that if you're going out tonight to a club for pedantic nerds and you want to impress someone,
you've got to keep the terminology straight:
Unicode is an abstraction, not an encoding, not a code page, not a
data format, and not a font.
Saying your text is Unicode says nothing about how it is represented.
It might be UTF-8. It might UTF-16. It might be code point numbers
handwritten on Post-It notes stuck on the wall. All of these are valid
representations of Unicode.
If you ever say anything to suggest that you think Unicode is
16 bits per character, you will be identified as clueless.
If you say that your text is stored in Unicode, you are not entirely incorrect, but people will wonder
about whether you really know the difference between Unicode and the
encodings of same.
SQLite always uses Unicode to represent text.
(Hopefully you are now screaming at me saying, "Yeah, but which encoding?!?")
The best answer to this question is: SQLite uses UTF-8. Forget about everything else.
A more correct answer to this question is: SQLite uses UTF-8 but also supports UTF-16.
Either way, there is no distinction between char and nchar.
There is no
way to save storage space in a column by realizing that you only use lower case english characters
so it's safe to use char instead of nchar.
There are no code pages.
is no way to move your Shift JIS data into SQLite without converting it to Unicode (or storing it as blobs, I suppose).
Microsoft has done a lot of things right, but its history with Unicode is
very unfortunate. And it's not entirely their fault. They
adopted Unicode early and it changed underneath them.
With respect to its emphasis on UTF-8, SQLite is far more typical of most
non-Microsoft software today.
|Tuesday, 8 April 2014|
(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)
Different types of, er, types
At the SQL language level, the biggest difference with SQLite is the way it deals with data types. There are three main differences to be aware of:
There are only a few types
And types are dynamic
(But not entirely, because they have affinity)
And type declarations are weird
Okay, so actually that's FOUR things, not three. But the third one doesn't
really count, so I'm not feeling terribly obligated to cursor all the way back
up to the top just to fix the word "three". Let's keep moving.
Only a few types
SQLite values can be one of the following types:
The following table shows roughly how these compare to SQL Server types:
In SQLite, all integers are up to 64 bits wide (like bigint), but smaller values are stored more efficiently.
In SQLite, all floating point numbers are 64 bits wide.
In SQLite, all strings are Unicode, and it doesn't care about widths on TEXT columns.
Width doesn't matter here either.
These are problematic, as SQLite 3 does not have a fixed point type.
(In Zumero, we handle synchronization of these by mapping them to INTEGER
and handling the scaling.)
SQLite has no data types for dates or times. However, it does have a rich
set of built-in functions for manipulating date/time values represented as
text (ISO-8601 format), integer (unix time) or real (Julian day).
Types are dynamic
In SQL Server, the columns in a table are strictly typed. If you define a
column to be of type smallint, then every value in that column must be a 16 bit
In contrast, SQLite's approach might be called "dynamic
typing". Quoting from its own documentation: "In SQLite, the
datatype of a value is associated with the value itself, not with its
For example, the following code will fail on SQL Server:
CREATE TABLE [foo] (a smallint);
INSERT INTO [foo] (a) VALUES (3);
INSERT INTO [foo] (a) VALUES (3.14);
INSERT INTO [foo] (a) VALUES ('pi');
But on SQLite, it will succeed. The value in the first row is an INTEGER.
The value in the second row is a REAL. The value in the third row is a TEXT
sqlite> SELECT a, typeof(a) FROM foo;
The column [a] is a container that simply doesn't care what you place in it.
Well, actually, it does care. A little.
A SQLite column does not have a
type requirement, but it does have a type preference, called an affinity. I'm
not going to reiterate the type affinity rules from the SQLite
website here. Suffice it to say that sometimes SQLite will change the
type of a value to fit match the affinity of the column, but you probably
don't need to know this, because:
If you declare of column of type TEXT and always insert TEXT into it, nothing weird will happen.
If you declare of column of type INTEGER and always insert INTEGER into it, nothing weird will happen.
If you declare of column of type REAL and always insert REAL into it, nothing weird will happen.
In other words, just store values of the type that matches the column. This
is the way you usually do things anyway.
Type declarations are weird
In a column declaration, SQLite has a rather funky set of rules for how it parses the type. It uses these rules to try its very best to Do The Right Thing when somebody ports SQL code from another database.
For example, all of the columns in the following table end up with TEXT
affinity, which is probably what was intended:
CREATE TABLE [foo]
But in some cases, the rules are funky. Here are more declarations which all
end up with TEXT affinity, even though none of them look right:
CREATE TABLE [foo]
And if you want to be absurd, SQLite will let you. Here's an example of a declaration of a column with INTEGER affinity:
CREATE TABLE [foo]
[d] My wife and I went to Copenhagen a couple weeks ago
to celebrate our wedding anniversary
and I also attended SQL Saturday while I there
and by the way we saw
Captain America The Winter Soldier
there as well which means I got to see it
before all my friends back here in Illinois
and the main reason this blog entry is late is
because I spent most of the following week gloating
SQLite will accept nearly anything as a type name. Column [d] ends up
being an INTEGER because its ridiculously long type name contains the characters "INT" (in "Winter
Perhaps we can agree that this "feature" could be easily abused.
There are only four types anyway. Pick a name for each type and stick to it.
Once again, the official names are:
(If you want a little more latitude, you can use INT for INTEGER. Or VARCHAR
for TEXT. But don't stray very far, mkay?)
Pretend like these are the only four things that SQLite will allow, and then it
will never surprise you.
SQLite handles types very differently from SQL Server, but its approach is mostly a superset of your
existing habits. The
differences explained above might look like a big deal, but in practice, they probably won't affect you
all that much.
|Friday, 14 March 2014|
(This entry is part of a series. The audience: SQL Server developers. The topic: SQLite on mobile devices.)
I can't use SQLite! It doesn't support foreign keys!
Actually, it does. Since version 3.6.19, released in October 2009. Before
the first iPad. Windows 7 was not quite out yet.
Miley Cyrus was still Hannah Montana.
You're wrong! This code should fail but doesn't!
CREATE TABLE [to] (pk int PRIMARY KEY);
CREATE TABLE [from] (other REFERENCES [to](pk) );
INSERT INTO [from] (other) VALUES (3);
Fair enough. It is easy to get the impression that SQLite still lacks
support for foreign keys because the feature is [usually] turned off by default, to preserve
How to turn SQLite foreign key support on
To enable the enforcement of foreign key constraints, execute the following SQL statement, shortly
after you open your SQLite database connection:
PRAGMA foreign_keys = 1;
I always use SQLite with foreign keys. In fact, I always open SQLite
connections with a helper function
which (1) opens the SQLite connection and (2) immediately executes the PRAGMA
Once you've got the feature turned on, foreign key support in SQLite is generally
similar to what a SQL Server dev would expect. Full documentation is available on the SQLite website, but there are a few things worth highlighting.
SQLite supports simple foreign keys (with one column referencing one
column), or composite foreign keys (with multiple columns referencing
SQLite does support ON DELETE and ON CASCADE.
SQLite allows a foreign key constraint to be deferred, which
means that its enforcement is postponed until the transaction is committed.
This is a handy feature that I really wish SQL Server had.
Shameless, blatantly self-serving commercial remark
Zumero for SQL Server automatically syncs foreign key constraints. Microsoft Sync Framework does not.
PRAGMA foreign_keys = 1;
|Copyright 2001-2014 Eric Sink. All Rights Reserved