Full text search example with SQLCipher for Xamarin.iOS

DemoViewController.cs

using System;
using System.Drawing;

using MonoTouch.Foundation;
using MonoTouch.UIKit;

using System.IO;
using Mono.Data.Sqlcipher;

namespace Demo
{
  public partial class DemoViewController : UIViewController
	{
		public DemoViewController () : base ("DemoViewController", null)
		{
		}
		
		public override void DidReceiveMemoryWarning ()
		{
			// Releases the view if it doesn't have a superview.
			base.DidReceiveMemoryWarning ();
		}

		public override bool ShouldAutorotateToInterfaceOrientation (UIInterfaceOrientation toInterfaceOrientation)
		{
			return (toInterfaceOrientation != UIInterfaceOrientation.PortraitUpsideDown);
		}
		
		public override void ViewDidLoad ()
		{
			base.ViewDidLoad ();

			var connection = GetConnection("demo.db", "test");
			connection.Open();
			CreateTables(connection);
			InsertData(connection);
			QueryData(connection);
		}

		private void CreateTables (SqliteConnection connection) {

			using (var command = connection.CreateCommand()) {
				var createVirtualTable = "CREATE VIRTUAL TABLE IF NOT EXISTS sites USING fts4(domain, url, title, meta_keys, body);";
				var createKeywordTable = "CREATE TABLE IF NOT EXISTS keywords (keyword TEXT);";
				command.CommandText = createVirtualTable;
				command.ExecuteNonQuery ();
				command.CommandText = createKeywordTable;
				command.ExecuteNonQuery ();
			}
		}

		private void InsertData(SqliteConnection connection){

			using (var command = connection.CreateCommand()) {
				var sql = "insert into sites(domain, url, title, meta_keys, body) values(?, ?, ?, ?, ?);";
				command.CommandText = sql;
				var domain = command.CreateParameter();
				domain.Value = "sqlcipher.net";
				var url = command.CreateParameter();
				url.Value = "http://sqlcipher.net";
				var title = command.CreateParameter();
				title.Value = "Home - SQLCipher - Open Source Full Database Encryption for SQLite";
				var keys = command.CreateParameter();
				keys.Value = "sqlcipher, sqlite";
				var body = command.CreateParameter();
				body.Value = "";
				command.Parameters.AddRange(new []{domain, url, title, keys, body});
				command.ExecuteNonQuery ();

				command.CommandText = "insert into keywords(keyword) values(?)";
				var keyword1 = command.CreateParameter();
				keyword1.Value = "SQLCipher";
				var keyword2 = command.CreateParameter();
				keyword2.Value = "SQLite";
				command.Parameters.Clear();
				command.Parameters.AddRange(new []{keyword1, keyword2});
				command.ExecuteNonQuery();
			}
		}

		private void QueryData(SqliteConnection connection){

			using(var command = connection.CreateCommand()){
				command.CommandText = "SELECT keyword FROM keywords INNER JOIN sites ON sites.title MATCH keywords.keyword;";
				var reader = command.ExecuteReader();
				if(reader != null){
					if(reader.HasRows){
						Console.WriteLine("Results found");
					}
					while(reader.Read()){
						var result = reader.GetString(0);
						Console.WriteLine("Result:{0}", result);
					}
				}
			}
		}

		private SqliteConnection GetConnection(String databaseName, String password) {
			string databasePath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), 
			                                   databaseName);
			return new SqliteConnection(String.Format("Data Source={0};Password={1}", databasePath, password));
		}
	}
}