Skip to content

Instantly share code, notes, and snippets.

@developernotes
Created April 4, 2013 16:43
Show Gist options
  • Save developernotes/5311963 to your computer and use it in GitHub Desktop.
Save developernotes/5311963 to your computer and use it in GitHub Desktop.
Full text search example with SQLCipher for Xamarin.iOS
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));
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment