Sony Arouje

a programmer's log

Posts Tagged ‘SQLite

Compile SQLite for WinRT with FTS4 unicode61 support

leave a comment »

I was experimenting with the FTS3/FTS4 feature of SQlite in a WinRT app. The default tokenizer ‘simple’ wont tokenize special character like $, @, etc. The solution is to use ‘unicode61’ tokenizer. Unfortunately the SQLite installer for WinRT8.1 comes without unicode61 tokenizer. I searched a lot to get a SQlite WinRT build that supports unicode61 but I was not lucky enough to get one, so I decided to build one myself.

Tim Heuer have a great post explaining about creating a build of SQLite for WinRT, I went through that and acquired all the tools to build, including the SQLite source code. I did exactly the same way Tim explained in the video, finally I got my build. But again it has the same issue, not supporting unicode61 tokenizer. I tried several builds and all these time I build with DSQLITE_ENABLE_FTS4_UNICODE61=1 and other flags I mentioned below.

After several attempt with so many permutations and combinations, I got it working. Tim’s video is a very good reference for building SQLite for WinRT. But if you want unicode61 support then follow the below steps, it’s same as Tim’s explained with some exclusion.

  1. mkdir c:\sqlite
  2. cd sqlite
  3. fossil clone http://www.sqlite.org/cgi/src sqlite3.fossil
  4. fossil open sqlite3.fossil
  5. fossil checkout winrt, never issue this command, it will never include unicode61
  6. Added this step to enable unicode61 tokenizer. Append the below config to Makefile.msc, you can see some config already existing append the below config to it.

    OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_FTS4=1

    OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_FTS3_PARENTHESIS=1

    OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_FTS4_UNICODE61=1

  7. Compile the code by issuing nmake -f Makefile.msc sqlite3.dll FOR_WINRT=1

 

I could build X86 and X64 using VS2012 command prompt for X86 and X64. But when I tried to compile for ARM I was getting some errors, I was fortunate enough to find a solution in StackOverflow. Followed that solution and I got the SQLite builds for X86, X64 and ARM.

I don’t want to spend time in creating a vsix package and install it in my machine, instead I took the backup of SQLite for WinRT version 3.8.5, in my machine the installed path is C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1\ExtensionSDKs\SQLite.WinRT81. Then went into each folder and replaced the lib and sqlite dll with the respective builds I created.

Leave your comments if you have any questions.

 

Happy coding…

Advertisements

Written by Sony Arouje

July 17, 2014 at 6:06 pm

Posted in .NET, WinRT

Tagged with , , , , , ,

Persist domain models in Android using ORMLite

with 9 comments

Recent days I am spending my spare time writing a small app for Android. As usual I have to deal with persistence. I really don’t want to write a persistence mechanism for myself. I started searching for an ORM in Android world. After some evaluation I decided to use ORMLite. It did a pretty good job of mapping my models to db. ORMLite use annotation based configuration, I will go into details later in this post. ORMLite will support entity relationship, lazy loading, etc. Another big advantage is, it’s an open source. Downside is it’s file size, above 200kb (great powers comes with some file size). I felt very comfortable with ORMLite and  decided to use it in my app.

I modified the ContactList test application to see how ORMLite works. Let’s go through in detail.

  1. Download ormlite-android-4.24.jar and ormlite-core-4.24.jar from sourceforge.
  2. Refer the jar files in ContactList project.

Note for non java devs. In Eclipse to refer a jar file, In navigator window right click on the project and select properties. Select Java Build Path from the list and click on Libraries tab. Click on ‘Add External JARs’ button and point to the required jar files.

Next we need to annotate the Contact entity to specify the mapping. Let’s see the Contact entity.

package com.contactlist;

import com.j256.ormlite.field.DatabaseField;
import com.j256.ormlite.table.DatabaseTable;
@DatabaseTable(tableName="contact")
public class Contact {
    Contact(){//used by ORMLite}
    public Contact(String id, String displayName)
    {
        _id=id;
        _displayName=displayName;
    }
    @DatabaseField(columnName="id")
    private String _id;
    @DatabaseField(columnName="display_name")
    private String _displayName;
    
    public String getId(){return _id;}
    public String getDisplayName(){return _displayName;}
}

As you can see the Contact class is annotated with DatabaseTable and specified the table to map. Also the private field is annotated with DatabaseField to map, columnName is optional. If not mentioned then ORMLite will use the variable name as the table column name. Another advantage is, ORMLite works on private variable, so we can make our class immutable.

You can see the basics of using ORMLite in Android here. As per the guideline we need to create a dbHelper extends from OrmLiteSqliteOpenHelper. Let’s see the db helper of ContactList app.

package com.contactlist.repository;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.contactlist.*;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

public class DBHelper extends OrmLiteSqliteOpenHelper  {

    private static DBHelper _helperInstance;
    public static DBHelper getInstance(Context context)
    {
        if(_helperInstance==null)
            _helperInstance=new DBHelper(context);
        
        return _helperInstance;
    }
    
    public DBHelper(Context context) 
    {
        super(context, AppConstants.DATABASE_NAME, null, AppConstants.DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource)
    {
        try
        {
            TableUtils.createTable(connectionSource, Contact.class);
        }
        catch (Exception e) {
            Log.e(AppConstants.LOG_NAME, "could not create table Contact",e);
        }
    }
    
    @Override
    public void  onUpgrade(SQLiteDatabase database, ConnectionSource connectionSource, int oldVersion, int newVersion)
    {

    }
    
    @Override
    public void close()
    {
        super.close();
        _helperInstance=null;
    }
}

When we implement dbHelper we need to override onCreate and onUpgrade as shown below. onCreate will get called when the app is installed for the first time. onUpgrade will get called when and upgrade to the app, so that we can add scripts for creating new tables, etc. In the above code in onCreate I created the table called contact using one of the class provided by ORMLite.

I done with all the infrastructure stuff. It’s the time to create our repository class to persist our Contacts to a Sqlite db.

package com.contactlist.repository;

import java.io.InputStream;
import java.sql.SQLException;
import java.util.List;
import android.content.ContentResolver;
import android.content.ContentUris;
import android.content.Context;
import android.database.Cursor;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.net.Uri;
import android.provider.ContactsContract;
import com.contactlist.*;
import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;

/**
 * @author sony arouje
 * 
 */
public class ContactRepository {
    private ContentResolver _contentResolver;

    private Context _context;
    public ContactRepository(ContentResolver contentResolver, Context context) {
        this._contentResolver = contentResolver;
        this._context=context;
    }

    public List<Contact> getContacts() {
        ContactList contactList = new ContactList();
        Uri uri = ContactsContract.Contacts.CONTENT_URI;
        String sortOrder = ContactsContract.Contacts.DISPLAY_NAME
                + " COLLATE LOCALIZED ASC";
        Cursor cur = _contentResolver.query(uri, null, null, null, sortOrder);
        if (cur.getCount() > 0) {
            String id;
            String name;
            while (cur.moveToNext()) {
                
                id = cur.getString(cur
                        .getColumnIndex(ContactsContract.Contacts._ID));
                name = cur
                        .getString(cur
                                .getColumnIndex(ContactsContract.Contacts.DISPLAY_NAME));
                Contact c = new Contact(id,name);
                contactList.addContact(c);
            }
        }
        cur.close();
        return contactList.getContacts();
    }

    public void saveContacts(List<Contact> contacts) throws SQLException
    {
        OrmLiteSqliteOpenHelper dbHelper= DBHelper.getInstance(_context);
        Dao<Contact, Integer> daoContact=dbHelper.getDao(Contact.class);
        for (Contact contact : contacts) {
            daoContact.create(contact);
        }
        dbHelper.close();
    }
}

In the above code I persist the Contacts using saveContacts(). I don’t think it really need any explanation, it’s a pretty simple function.

For testing I called the save function ContactListActivity as shown below

_contactRepo=new ContactRepository(getContentResolver(),getApplicationContext());
List<Contact> contactList=_contactRepo.getContacts();

try {
    _contactRepo.saveContacts(contactList);
} catch (SQLException e) {
    e.printStackTrace();
}

 

If you want to verify your db, you can do it very easily through eclipse. You can find a detailed step by step description here to view the db created in the emulator. I use SQLiteSpy to browse the SQlite db.

Download source here (ContactList_ORMLite.zip).

Written by Sony Arouje

August 3, 2011 at 2:52 pm

Posted in Android

Tagged with , , , ,

%d bloggers like this: