Showing posts with label database. Show all posts
Showing posts with label database. Show all posts

Friday, July 12, 2013

Android Advanced Cursors

If you have been doing Android development for even a little while, you have probably used the provided SQLite functionality to store and retrieve data in a local database on the Android device.

If not, you can head over to Lars Vogel's excellent tutorial website to see how it all works, or to get a refresher.

For many applications, the basic Cursor is all you need. But what about more advanced requirements? What follows is an overview of some of the more advanced cursor options.

CursorJoiner

If you need data from two or more database tables, the most straightforward thing to do is to use a SQL query that joins the two tables. However, sometimes you already have sources for two separate queries (such as a CursorLoader based on a ContentProvider), and you may wish to just re-use them, without creating a new query (without, e.g., modifying the ContentProvider).

This is where CursorJoiner comes in handy. Unlike the other options listed below, CursorJoiner doesn't produce a new cursor. Instead, it provides an Iterable interface over the joined cursors.

If the only reason that you want to join the two tables is to produce a Cursor, say, for use by a CursorAdapter, then you should probably just stick with writing SQL that joins the two tables. However, if you just want to iterate the data once, then CursorJoiner can be very helpful.

One of the more interesting uses that I've seen for CursorJoiner is for determining what has changed when a LoaderManager.LoaderCallbacks<Cursor> implementation gets an updated Cursor in it's onLoadFinished method.

You can use the CursorJoiner to join the 'old' cursor and the 'new' cursor on their primary key, and then compare them. As you iterate the CursorJoiner, you get a CursorJoiner.Result enum that indicates whether the current row exits in the left cursor, the right cursor, or both.

Here is some sample code that determines what has been added to or deleted from a ContentProvider as a CursorLoader driving a CursorAdapter is updated:

   
@Override
public void onLoadFinished(Loader<cursor> loader, Cursor updatedCursor) {
   if(adapter.getCursor() != null) {
      Cursor oldCursor = adapter.getCursor();
      CursorJoiner joiner = 
         new CursorJoiner(oldCursor, 
             new String[] {Thing.ID},
             updatedCursor,
             new String[] {Thing.ID}
      );
            
      for(CursorJoiner.Result joinerResult : joiner) {
         switch (joinerResult) {

            case RIGHT:
               int thingId = updatedCursor.getInt(
                  updatedCursor.getColumnIndex(Thing.ID)
               );
               
               String thingName = updatedCursor.getString(
                  updatedCursor.getColumnIndex(Thing.NAME)
               );
                      
               Log.d(TAG, "new Thing added - id: " + thingId 
                    + ", name: " + thingName);
            break;

            case LEFT:
               int thingId = updatedCursor.getInt(
                  oldCursor.getColumnIndex(Thing.ID)
               );
               
               String thingName = updatedCursor.getString(
                  oldCursor.getColumnIndex(Thing.NAME)
               );
                      
               Log.d(TAG, "Thing deleted - id: " 
                    + thingId + ", name: " + thingName);
            break;

            case BOTH:
               int thingId = updatedCursor.getInt(
                  oldCursor.getColumnIndex(Thing.ID)
               );
               
               String thingName = updatedCursor.getString(
                  oldCursor.getColumnIndex(Thing.NAME)
               );  

               Log.d(TAG, "Thing unchanged - id: " 
                    + thingId + ", name: " + thingName);
            break;                                   
      }
   }        
   updatedCursor.moveToFirst();
   adapter.swapCursor(updatedCursor);
}


MatrixCursor

MatrixCursor is useful if you have a collection of data that is not in the database, and you want to create a cursor for it.  You simply construct it with an array of column names, and optionally an initial capacity. Then, you can add one row at a time to it by passing either an array of objects or an Iterable to its addRow() method. Then use it like any other cursor:
  
String[] columnNames = {"Column1", "Column2"};
MatrixCursor matrixCursor = new MatrixCursor(columnNames);
matrixCursor.addRow(new String[]{"value1", "value2"});
adapter.swapCursor(matrixCursor);

MergeCursor

MergeCursor allows you to present two or more cursors as a single cursor. You could get almost the same effect by doing a sql UNION query. However, MergeCursor allows you to have different columns in the various cursors, and still merge them.

This is very useful if you are presenting a list of heterogeneous items. I've used it with Emil Sjölander's StickyListHeaders library, using a different cursor for each 'section', all presented to the StickyListHeader implementation as a single cursor via a MergeCursor.

One thing to be aware of when presenting heterogeneous items through a MergeCursor - you'll probably want to add a pseudo-column to each of the cursors to make it easy for your ListAdapter (or whatever will be using the cursor) to be able to determine which type of data it's dealing with on each row. A seemingly little-known trick here is that you can include literal columns when creating a cursor using the ContentProvider's query method (and similar methods in other database classes). Just include the literal in the projection parameter to the query:
  
String projection = {Thing.ID, Thing.NAME, "'TYPE_THING' as TYPE"};
Cursor c = qBuilder.query(mDb,
                projection,
                selection,
                selectionArgs,
                groupBy,
                having,
                sortOrder);

CursorWrapper

Finally, CursorWrapper is a Wrapper class for Cursor that delegates all of its calls to the actual cursor object. As the documentation stated, the primary use for this class is to extend a cursor while overriding only a subset of its methods.

One example for the use of CursorWrapper is when you already have a cursor, but only want to present the first few rows of the cursor to a CursorAdapter. Override the getCount() method to return a number smaller than the actual cursor's count, and the adapter will only present that number of results. This is similar to what you would have gotten had you added a LIMIT clause to the sql that produced the cursor.

With this collection of advanced Cursor tricks, you should be able to accomplish whatever you want with Cursors and Adapters.

Tuesday, August 9, 2011

Full-Text Search For Relational Data

Thanks to internet search engines like Google, Yahoo, and Bing, everyone has become accustomed to searching their data effortlessly. Consequently, as developers of web-based applications, we increasingly find that our customers' requirements include being able to search their application's data in a "Google-like" way.

For a very small subset of applications that only need to do full-text search on a few database columns, there a some very simplistic solutions, ranging from using a SQL 'like' clause on every column to database-vendor supplied full-text search functionality for designated columns.

Frequently, however, these simplistic approaches are not enough, either because the data to be searched is large and complex, because the approach's search functionality is limited, or because database neutrality is desired. For these situations, a more advanced solution is needed. Fortunately, there are now a number of solutions available for all of the most popular platforms, languages, and frameworks, and several hosted solutions that are development stack-neutral.


Lucene and Compass
One of the most popular full-text search projects is Apache Lucene, a Java-based, open-source indexing and searching solution. Lucene itself targets indexing and searching of 'documents', but several other projects, most notably Compass, have adapted Lucene for indexing and searching relational data via integration with Object-Relational Mapping libraries such as Java's JPA, Hibernate, and others.

The combination of Lucene and Compass provides Java developers with an easy way to implement Google-like searching of their relational data, including fuzzy searching (spell-check / 'Did you mean...'), stemming ('fishing', 'fished', 'fish', and 'fisher' all match 'fish'), wildcards, and boolean logic.

For users of the Java JPA ORM, usage of Compass is fairly straightforward. For users of the Grails framework, the Grails Searchable Plugin makes implementation of full-text searching almost effortless. I recently added this plugin to a Grails application with a moderately complex data model, and had my full-text search functionality up and running in a few hours, with fine-tuning taking only a few more hours. As the application's data model has grown, very little time has had to be spent on keeping the search functionality up to date.

There are many other programming languages and platforms in use besides Java, so not surprisingly, Lucene has been ported to many of them, including C, C++, .NET, Objective-C, Python, Perl, and Ruby. The most popular of these, due to the popularity of the programming languages, are Lucene.net, which is a C# port targeted at the .NET runtime, and Ferret, which is a Ruby port targeted at the Ruby on Rails framework.

While Lucene and its variants currently dominate the open-source full-text search landscape, there are other well-known projects, including Xapian.


Hosted Solutions
There may be situations where it makes more sense move your full-text search functionality 'to the cloud'. Several hosted solutions are available, some based on Lucene or other open-source technologies, as well as other, proprietary offerings.

Websolr is a hosted version of Apache's Solr which is itself built on Lucene. Other offerings include Flying Sphinx for Rails (which uses the Sphinx search engine), and IndexTank, which is a proprietary offering.

Both Flying Sphinx and IndexTank are available as add-ons for the popular EngineYard and Heroku Ruby-based hosting platforms.

I recently tried out IndexTank on Heroku. IndexTank itself targets indexing and searching documents, but the Tanker gem does a great job of integrating IndexTank with Rails' ActiveRecord ORM. IndexTank's documentation doesn't mention it, and while not available "out of the box", IndexTank does support both fuzzy searching and stemming. All you need to do is send an email to their customer support address, and ask to have it turned on.

If your application can benefit from full-text search, there are plenty of interesting choices, regardless of your platform or programming language.

Saturday, January 30, 2010

Why You Need NoSql in Your Toolbox

Even if you work for Oracle, you still need NoSql databases in your toolbox.

One size does not fit all for programming languages, operating systems, IDEs, shoes, bailouts, or anything else. But for a long time now, many developers have been told that relational databases are really the only choice for persistence. If you need to store some data -- any data -- shove it in a relational database. It is the safe choice, and gives us developers one less thing to worry about, right? Well actually, wrong. RDBMS' are great solutions to many problems, but there are better alternatives for others.

Quick overview of NoSql

A simple definition of a NoSql database is a schema-less database that does not support joins or ACID transactions. In a broad simplification, there are 2 types of NoSql databases:

  1. Key/value -- A "giant hash table in the sky" that stores key value pairs.

    Pros:
    * very fast
    * very scalable
    * simple model
    * many are distributed

    Cons: many data structures (objects) can't be easily modeled as key value pairs

  2. Schema-less -- Databases somewhere in-between key/value and relational including: column-based (different rows in same table can have different columns), document-based, and graph-based

    Pros:
    * Schema-less data model is richer than key/value pairs
    * eventual consistency
    * many are distributed
    * still provide excellent performance and scalability

    Cons: typically no ACID transactions or joins

  3. Relational database

    Pros:
    * rich data models
    * ACID transactions
    * joins, reductions (group by, sum), ordering
    * lots of apps already integrate with RDBMS
    * mature, commercial support easy to find

    Cons:
    * complex
    * object to relational mismatch
    * can be difficult to scale horizontally

Performance and Scalability: Less Features = More Speed/Scaling

"Constraints proceed performance"
- Joe Gregorio, Google

"Constraints proceed performance" really sums up why many of the NoSql databases can be blazingly fast -- they implement fewer features and have some significant constraints when compared to a relational database. The key is determining whether you can live with those constraints. If you can, you reap the performance and scalability benefits. But before you become enamored with the allure of more performance, here's a list of things you'll probably have to give up:
  • joins
  • group by
  • order by
  • indexes
  • ACID transactions
  • SQL as a sometimes frustrating but still powerful query language
  • easy integration with all of the other applications that support SQL
Yikes. That's a pretty hefty price should you actually need one or more of those features. However, for many applications that perform CRUD operations on data largely partitioned by user, you might not need as many as you think.

On the scalability side of the equation, many of the NoSql databases were designed to be distributed so they can scale horizontally. Relational databases are not designed to scale horizontally. If you application really needs to scale up, vertically scaling your relational database is only going to get you so far because eventually you won't be able to buy a big enough box to scale any further. At that point you have to look at sharding your data across multiple database instances, and then you lose many of the benefits of a relational database across instances such as joins, transactions, etc.

In addition to get the performance you need, you very well may need to denormalize some of your data to eliminate slow joins. Additionally, to really scale up you may need to use a distributed caching layer like memcached that requires key/value semantics, in which case you are really starting to diverge from the pure relational model. So when making the comparison, it may be more fair to compare a sharded (data partitioned into multiple databases), partially denormalized relational database using a caching layer with an alternative NoSql database.

Productivity: Object to Relational Mapping Is Not Fun (Unless You Are Weird)

Object to relational mapping is difficult to implement, difficult to reason about, and easy to get wrong. As a developer, you either have to get in the weeds and write your own queries, or rely on a ORM tool like Hibernate which is great until something doesn't work and then you have try to find out why the magic isn't working anymore.

There is a lot of effort put into solving the object to relational mapping problem, and it still hasn't be completely perfected. This is where, depending on your data, a NoSql database can provide an abstraction that maps closer to your object model than relational tables. Column-based stores like BigTable and Cassandra essentially allow different rows in the same table to have different columns. Why is that useful? There a lots of cases where data is optional or slightly different between entities. If I was creating a ratings site, I could rate different things using different criteria, but they could all be stored in the same table. I won't need a table of "things" with a join table and a table of "criteria".

On the other hand, why is that dangerous? Obviously the application could get back data it didn't expect because the schema wasn't enforced by the database. Document-based stores like CouchDB give you even more flexibility where you store entire documents that can contain anything you want. No longer are you a slave to that crusty database administrator and all of his rules, but on the other hand you could easily hang yourself with all of this new rope. What happens later when you need to run reports on this data and you don't have a group by or sum function? You can write your own map reduce functions to essentially do the same thing, but you have to do the work.

Another point to consider is that many "rapid development" web frameworks like Ruby on Rails, Grails, Django, and Lift already have substantial ORM tools baked in. If you use NoSql for your persistence you won't be able to take full advantage of those frameworks. Some of the frameworks are starting to support some NoSql databases, but since NoSql is just a general label and not a specification like SQL, support is one off. For instance, there is a Grails plugin to provide support for Google App Engine's version of JPA.

You can't have it all; you have to decide between the complex features that a relational database supports versus the constraints/benefits that a NoSql database provides. But whether you put 99% of your data in a relational database and only 1% in a NoSql store, or the other way around, both types of databases are likely to be valuable to your business. I like the interpretation of NoSql as "Not Only SQL" meaning both types of databases can and should co-exist within your organization.

Why Now?

Non relational databases have been around since ENIAC, so why are we hearing so much about them now? My opinion is that there are 2 drivers:

1. The massive scalability requirements of some websites has exposed one of the weaknesses of relational databases: scalability. Sites like Google, Amazon, Twitter, and Facebook all use NoSql databases for major components of their site.

2. A strong open source community means that there are many high-quality, production-ready NoSql databases available. There probably have been hundreds of very good, proprietary non-RDMS databases created in the last 20 years, but since they are proprietary, they have been used at one company and no one has ever heard of them. Open source has changed that, and now companies like Amazon, Google, and Facebook are contributing incredibly valuable research and software to the community. Research papers such as Amazon's Dynamo and Google's BigTable inspired many open source implementations.

This is a fairly recently phenomena. I'm not aware of any production-ready open source non-relational databases that existed 10 years ago, and if there were, there weren't many and they weren't popular. Now there are well over 15 (and probably many more, I don't claim to know the exhaustive list) production-ready open source NoSql databases. Am I going to list them all? No, because if I do I'll miss one, and I'll get a comment saying "You didn't mention XYZ database, what's wrong with you?" But I'll list the ones I'm currently looking at in no particular order:
  • Voldemort
  • Cassandra
  • Redis
  • CouchDB
  • MongoDB
  • Terrastore
Some links for more reading: