[MUSIC] Welcome to part one of the next lesson in this module, which presents an overview of SQLite. After completing this part of the lesson, you'll know why Android supports the SQLite relational database for mobile devices. And you'll also be able to recognize the various SQLite related classes provided by Android. Let's first start by talking about SQLite. So SQLite is basically a relational database that's been optimized for mobile devices. And basically you can create tables which are row and column. Data structures that can be made persistent or that are persistent. Indexes etc., and these things all are exposed by a so called schema. And you could learn more about how this stuff works here, this article. The reason why SQLite was picked for Android is because it's got a relatively small footprint, something on the order of 350kb. And it sits within a single disk file. You can read more about SQLite in general here at this link. Every Android device comes with SQLite configured by default, obviously there are other databases that you could use, but you'd have to go ahead and install them. You don't have to do complicated setup procedures or administration of the database, it works fairly straightforwardly. You just need to define your SQL statements to create and update the database and it takes care of the bulk of the other stuff. If you're familiar at all with the structured query language, the 1992 Spec then SQLite support most these specification. You can read more about that here. And of course it provides the so called ACID properties which stand for Atomic, Consistent, Isolated and Durable. And you'll see what that all means, if you don't already know that, it's a pretty common concept. You'll see what that all means as we start looking at some of the examples that illustrate how it gets used. Typically, when you access an SQLite database this is going to interact with the Android's filesystem and you can read more about that here at this link. Because filesystem access may be slow and/or because you can do sophisticated queries and other kinds of operations that joins and so on with SQLite. You typically want to access the database either concurrently and/or asynchronously. And so what that means is typically when you use the database you'll be interacting with it by an AsyncTask in order to be able to run the processing in a background thread. If you do it with a synchronous model, which is the default, is the synchronous mode. There are also several asynchronous ways of getting access to content that's stored in SQLite database via a content provider. Using something called an AsyncQueryHandler and a CursorLoader. What's cool about all these different mechanisms is that the content provider basically stays the same, it doesn't change but the means by which you access the content provider are ever so slightly different. To be either have a separate thread running or to be doing things in a more asynchronous manner from the applications perspective. So let's talk about some of the SQLite-related classes that you're likely to run into when you use these things. So the SQLiteDatabase is the base class for using an SQLiteDatabase. That's typically what You're going to use to get access to all the operations that you care about, you can read about it more at this link. It provides the various so-called crud operations, so you have insert to add things into the database. You have update to modify things that are already there. You have delete, which will remove some or all of the elements of the database. You also have ways of being able to execute SQL statements directly if you want to get to the raw access to the SQL. That's not a common thing to do but it does come in handy for a few operations. And then of course the other big thing you do with the database, arguably the most common thing that you do is you query it, which is sort of a fancy read operation. And there's a couple of different approaches you can use. There's a query method and the query method as you'll see later has a bunch of parameters that can be filled in with wildcards. There's a rawQuery method which is a little bit lower level. There's also a query builder class, which you can use to build up using the builder pattern. And different techniques give rise to different approaches. I tend to use Query a lot but QueryBuilder is also very popular, whatever works for you is perfectly fine to do for the assignments that we're working on and so on. There's also some other helper classes that get used with SQLite that are also exposed by content providers, probably the most important of which is the ContentValues class. And basically this is used to define key/value tuples. Where the key represents the table column ID and the value represents the content for the table record or records in the column. And these, of course are typically used for inserts and updates of database entries. The recommended way of accessing SQLite is to subclass something called the SQLiteOpenHelper. And that's sort of a convenience class that abstracts and encapsulates certain types of things, especially things like creating the database, and being able to manage versioning and so on. You typically end up calling your class the subclass from SQLiteOpenHelper will go ahead and call up to the superclass in the SQLiteDatabase in order to be able to specify the database name and the current database version number. And then the onCreate method is called, if a database doesn't exist yet and you typically write that method to create the table commands. And then there's also this onUpgrade method which can be used to allow the database schema to evolve over time. Which is important if you're changing your application and you don't want to break what's already there. There's a number of helper methods that you can use to open and return the underlying database. The most common of which are getting the database for readable access which means you can just query it, or for writeable access which allows you to be able to insert, update and delete. This completes part one of our lesson that presents an overview of SQLite [MUSIC]
