Sposoby tworzenia bazy danych SQLite w aplikacji Android

Dwoma najbardziej przydatnymi sposobami tworzenia bazy danych w Androidzie są:

automatyczne stworzenie bazy przez system Android
ręczne wczytanie stworzonego wcześniej pliku bazy danych

Automatyczne stworzenie bazy danych

Domyślnym sposobem użycia bazy SQLite w aplikacji Android jest użycie poprzez klasę dziedziczącą po SQLiteOpenHelper. Klasa SQLiteOpenHelper umożliwia podanie nazwy pliku bazy danych oraz jej wersji.

private DBHelper(Context context) {
	super(context, DBContract.DATABASE_NAME, null, DBContract.DATABASE_VERSION);
}

W przypadku, gdy plik o danej nazwie nie istnieje w katalogu przeznaczonym do przetrzymywania danych o aplikacji, plik ten zostaje automatycznie utworzony, a następnie wywoływana jest metoda onCreate na klasie dziedziczącej po SQLiteOpenHelper. Podobnie sytuacja ma się w przypadku podniesienia wersji bazy danych, tym razem jednak wywoływana jest metoda onUpdate. W obu tych metodach można wykonywać operacje na bazie danych.

@Override
public void onCreate(SQLiteDatabase db) {
    // stworzenie tabel, wypełnienie ich danymi...
}
	
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // sprawdzenie wersji aplikacji i ewentualny upgrade struktury bazy danych lub danych
}

Jest to jednak niezbyt wygodne w przypadku gdy posiadamy już bazę danych SQLite i chcemy ją wykorzystać w aplikacji - wymagałoby to od nas wykonania wszystkich zapytań wgrywających dane do nowej bazy w metodzie onCreate. W tym przypadku z pomocą przychodzi nam ręczne wczytanie stworzonego wcześniej pliku bazy danych.

Ręczne wczytanie stworzonego wcześniej pliku bazy danych

W celu wczytania bazy danych z pliku należy wykonać następujące kroki:

umieścić plik bazy danych w katalogu assets danej aplikacji
skopiować plik z katalogu assets do katalogu wskazywanego przez ApplicationContext.getApplicationInfo().dataDir (krok opcjonalny, ale pozwala na utrzymanie spójnej struktury)
nadpisać metody getWritableDatabase() i getReadableDatabase() klasy dziedziczącej po SQLiteOpenHelper, tak by otwierać gotowy plik bazy danych:

@Override
public SQLiteDatabase getWritableDatabase() {
	return SQLiteDatabase.openDatabase(dbFilePath, null, SQLiteDatabase.OPEN_READWRITE);
}
	
@Override
public SQLiteDatabase getReadableDatabase() {
	return SQLiteDatabase.openDatabase(dbFilePath, null, SQLiteDatabase.OPEN_READONLY);
}

Tworzenie kontraktu pomiędzy bazą a aplikacją - najlepsze praktyki

W celu łatwego tworzenia zapytań SQL w kodzie aplikacji, warto stworzyć kontrakt opisujący w Javie strukturę bazy danych. Kontrakt taki powinien składać się z:

klas odzwierciedlających tabele, klasy te powinny zawierać pola typu String opisujące nazwę każdej kolumny danej tabeli

wg strony Android Developer klasa ta powinna implementować interfejs BaseColumns, który dostarcza dwóch pól: _ID i _COUNT
w klasie tej powinny znajdować się również pola zawierające komendy do tworzenia i usuwania tabeli
przykładowa klasa odzwierciedlająca kolumny tabeli:

public static abstract class CategoryTable implements BaseColumns {
	public static final String TABLE_NAME = "Categories";
	public static final String COLUMN_NAME_CATEGORY_ID = "CategoryId";
	public static final String COLUMN_NAME_CATEGORY_NAME = "CategoryName";
	public static final String COLUMN_NAME_CATEGORY_PICTURE = "Picture";


        static final String SQL_CREATE_TABLE = "CREATE TABLE " + TABLE_NAME
			+ " ("
			+ _ID + " INTEGER, "
			+ COLUMN_NAME_CATEGORY_NAME + " TEXT, "
                        + COLUMN_NAME_CATEGORY_PICTURE + " BLOB)";
		
        static final String SQL_DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
}

dodatkowo w kontrakcie warto stworzyć obiekty ułatwiające szybkie iterowanie po polach opisujących tworzenie i usuwanie tabel, np. poprzez stworzenie list z wartościami odpowiednich pól wszystkich klas odzwierciedlających tabele.

Wykonywanie zapytań

Klasa SQLiteDatabase, zwracana przez metody getReadableDatabase() i getWritableDatabase(), udostępnia metody do odpytywania bazy danych. Podstawowe z nich to:

query()

umożliwia wykonanie zapytania SELECT na tabeli
zwraca kursor nad zbiorem rekordów (opis użycia kursorów w sekcji niżej)
jako argumenty można podać m. in.:

kolumny, które zostaną zwrócone
warunki filtrowania wierszy

w przypadku filtrowania można użyć bindowania argumentów, np. w celu określenia filtru na id, można podać wartość filtra równą "id=?", a w kolejnym parametrze podać tablicę ze Stringiem, który będzie automatycznie podstawiony za znak zapytania

warunki grupowania
sposób sortowania
maksymalna ilość zwracanych rekordów

przykładowo w celu uzyskania kursora z wynikiem zawierającym kategorię o ID równym 1 z tabeli Categories (opisanej powyżej) należy wykonać podaną poniżej metodę:

SQLiteDatabase db = DBHelper.getInstance().getReadableDatabase();
Cursor c = db.query(CategoryTable.TABLE_NAME, 
                    new String[] { COLUMN_NAME_CATEGORY_NAME, COLUMN_NAME_CATEGORY_PICTURE }, 
                    CategoryTable._ID + "=?",
                    new String[] { String.valueOf(1) },
                    null, null, null);

update()

umożliwia wykonanie zapytania UPDATE na tabeli
zwraca liczbę zmienionych rekordów
jako argumenty podaje się:

nazwę tabeli
obiekt klasy ContentValues, jest to zwykła mapa zawierająca nazwę kolumny i odpowiadającą jej wartość
warunki filtrowania wierszy - podobnie jak w query, tutaj również można użyć bindowania argumentów

przykładowo, aby zmienić nazwę kategorii o id 1, należy:

SQLiteDatabase db = DBHelper.getInstance().getWritableDatabase();
		
ContentValues values = new ContentValues();
values.put(CategoryTable.COLUMN_NAME_CATEGORY_NAME , "Zmieniona kategoria");

db.update(CategoryTable.TABLE_NAME, values, CategoryTable._ID + "=?", new String[] { String.valueOf(1) });

delete()

umożliwia usunięcie rekordów z tabeli
zwraca liczbę usuniętych rekordów
jako argumenty podaje się:

nazwę tabeli
warunki filtrowania wierszy - podobnie jak w query, tutaj również można użyć bindowania argumentów

na przykład, chcąc usunąć kategorię o id większym od 10, należy:

SQLiteDatabase db = DBHelper.getInstance().getWritableDatabase();

db.delete(CategoryTable.TABLE_NAME, CategoryTable._ID + ">?", new String[] { String.valueOf(10) });

insert()

umożliwia wykonanie zapytania INSERT na tabeli
zwraca id wstawionego rekordu lub -1 w przypadku błędu
jako argumenty podaje się:

nazwę tabeli
obiekt klasy ContentValues

przykładowo, by wstawić nową kategorię należy:

SQLiteDatabase db = DBHelper.getInstance().getWritableDatabase();
		
ContentValues values = new ContentValues();
values.put(CategoryTable.COLUMN_NAME_CATEGORY_NAME, "Nowa nazwa kategorii");
values.put(CategoryTable.COLUMN_NAME_CATEGORY_PICTURE, getBitmapToArray(bitmap));

db.insert(CategoryTable.TABLE_NAME, null, values);

rawQuery()

metoda umożliwiająca wykonywanie złożonych zapytań SELECT, używających więcej niż jedną tabelę
zwraca kursor nad zbiorem rekordów
jako agumenty podaje się:

zapytanie SQL
tablica z argumentami do zbindowania

przykładowo, można stworzyć zapytanie typu SELECT * FROM t JOIN s ON... :

SQLiteDatabase db = DBHelper.getInstance().getReadableDatabase();

Cursor c = db.rawQuery("SELECT * FROM " + DBContract.ProductTable.TABLE_NAME + " p INNER JOIN "
                        + DBContract.CategoryTable.TABLE_NAME + " c ON c." + DBContract.CategoryTable.COLUMN_NAME_CATEGORY_ID
                        + " = p." + DBContract.ProductTable.COLUMN_NAME_CATEGORY_ID, null);

execSQL()

metoda służąca do wykonywania zapytań SQL nie będących zapytaniami SELECT, UPDATE, INSERT i DELETE
nie zwraca niczego
jako argument podaje się zapytanie SQL
przykładowo, aby stworzyć indeks na tabeli Categories, należy wykonać:

SQLiteDatabase db = DBHelper.getInstance().getWritableDatabase();


db.execSQL("CREATE INDEX cat_index ON " + DBContract.CategoryTable.TABLE_NAME 
            + "(" + DBContract.CategoryTable.COLUMN_NAME_CATEGORY_ID + ")");

Użycie kursorów

Metoda select() jako tryb zwracany deklaruje kursor. Służy on pobraniu danych ze zwróconego zbioru rekordów. Kursory zapewniają możliwość przechodzenia po zbiorze wyników w dowolnej kolejności. Pokażemy najbardziej typowy sposób użycia kursorów - iterację po kolejnych rekordach zbioru wynikowego.

W celu przesunięcia się na początek zbioru wynikowego używa się metody moveToFirst(). Przejście do następnego rekordu możliwe jest za pomocą metody moveToNext(). Sprawdzenie całkowitej ilości rekordów w zbiorze wynikowym umożliwia metoda getCount(). Te 3 metody umożliwiają prostą iterację po wynikach zapytania.

c.moveToFirst();
for (int i = 0; i < c.getCount(); ++i) {
    Product product = mapProduct(c);
    products.add(product);
    c.moveToNext();
}
c.close();

Sama iteracja po zbiorze wynikowym jednak nie wystarcza. Gdy kursor wskazuje na konkretny element zbioru wynikowego, należy wydobyć z niego dane o tym rekordzie. W tym celu wykorzystuje się metody kursora zaczynające się od słowa "get", takie jak:

getDouble(),
getFloat(),
getInt(),
getLong(),
getString(),
getBlob()

Każda z tych metod jako argument przyjmuje liczbę określającą pozycję kolumny z której należy wyciągnąć daną wartość. Przykładowo, aby z jednego rekordu można było stworzyć obiekt, należy stworzyć metodę mapującą:

private Product mapProduct(Cursor c) {
    Product product = new Product();
		
    product.setId(c.getInt(0));
    product.setName(c.getString(1));
    product.setCategoryName(c.getString(2));
    product.setPrice(new BigDecimal(c.getDouble(3)));
	
    return product;
}

W bazie danych SQLite możliwe jest również przechowywanie danych typu BLOB, a więc też obrazków. Android oferuje bardzo prosty sposób wyciągania obrazków z bazy danych i tworzenia z nich bitmap. Służy do tego metoda getBlob() kursora, która zwraca tablicę bajtów, oraz fabryka bitmap, dekodująca tablicę bajtów do bitmapy.

byte[] imageBlob = c.getBlob(blobColumnNumber);
Bitmap loadedBitmap = BitmapFactory.decodeByteArray(imageBlob, 0, imageBlob.length);

FTS - opis

FTS, czyli Full Text Search, jest to sposób wyszukiwania danych w dokumentach tekstowych, wykorzystujący tzn. indeksy odwrócone. Są to indeksy, które mapują tokeny na dokładne pozycje w tabeli na których występują. Tworzenie indeksu FTS składa się z 5 kroków:

przetworzenia dokumentu tekstowego
stworzenia wektora słów zawartych w dokumencie
usunięcie stop-słów

np. spójniki, przedimki

stemming

sprowadzenie słowa do formy podstawowej

utworzenie indeksu

Wyszukiwanie FTS różni się od zwykłego wyszukiwania w SQL dostępnego przez słowo kluczowe LIKE. LIKE opiera się na wystąpieniach konkretnego podciągu tekstu, podczas gdy FTS dopasowuje tylko do pełnych słów.

FTS - sposób użycia

Aby móc skorzystać z Full Text Search w SQLite w Androidzie należy stworzyć tabelę wirtualną. Tabela wirtualna jest to obiekt, który wygląda jak normalna tabela, jednak podczas wykonywania zapytań odwołuje się do callbacków zapisanych na tą tabelę w celu wykonania jakichś czynności (np. wykonania kodu innego niż polecenia SQL). Tworzy się ją poleceniem CREATE VIRTUAL TABLE, jak w przykładzie poniżej:

CREATE VIRTUAL TABLE products_fts using fts3(productId, productName, categoryName)

Polecenie to tworzy tabelę wirtualną products_fts, w której znajdują się kolumny productId, productName i categoryName. Oprócz stworzenia tabeli products_fts SQLite tworzy również 3 inne tabele:

%_content - zawiera wszystkie kolumny tabeli wirtualnej poprzedzone odpowiednio napisami "c0", "c1", "c2", oraz kolumnę docId, o ile nie była ona kolumną tabeli wirtualnej
%_segments - służy przechowywaniu odwróconego indeksu

zawiera 2 kolumny: blockId oraz block
kolumna block zawiera B-drzewo zapisane w postaci binarnej

%_segdir - służy przechowywaniu informacji o indeksie

ilość bloków, blok początkowy, blok końcowy, adres głównego węzła

Po stworzeniu tabeli wirtualnej można przejść do wykonywania zapytań FTS. Głównym słowem kluczowym przy wyszukiwaniu FTS jest słowo "MATCH". Przedstawimy teraz możliwe sposoby użycia Free Text Search.

Tabela na której będą pokazane przykłady FTSa:

product_name	category_name
Uncle Bob's Organic Dried Pears	Produce
Singaporean Hokkien Fried Mee	Grains/Cereals
Vegie-spread	Condiments

3 podstawowe typy zapytań FTS:

zapytania o token lub przedrostek

SELECT * FROM table WHERE table MATCH 'Singaporean';
SELECT * FROM table WHERE table MATCH 'Singap*';

Oba zapytania zwrócą ten sam rekord - 'Singaporean Hokkien Fried Mee'. Domyślnie, przy zastosowaniu zapytania "WHERE nazwa_tabeli_wirtualnej MATCH 'token'" FTS próbuje znaleźć token w każdej z kolumn indeksu. Wyspecyfikowanie kolumny możliwe jest na 2 sposoby:

SELECT * FROM table WHERE table MATCH 'product_name: Singaporean'
SELECT * FROM table WHERE product_name MATCH 'Singaporean'

zapytania o frazy

SELECT * FROM table WHERE table MATCH '"Uncle Bob\'s Organic"'
SELECT * FROM table WHERE table MATCH '"Un* Bo* Organic"'

Są to zapytania umożliwiające dokładne określenie kolejności występowania słów w tekście. Oba zapytania z przykładu powyżej zwrócą ten sam rekord.

zapytania typu NEAR

SELECT * FROM table WHERE table MATCH 'Mee NEAR/3 Singaporean NEAR/1 Hokkien'

Zapytania typu NEAR pozwalają na określenie maksymalnej odległości pomiędzy tokenami. Domyślnie jest to odległość 10 tokenów.

Oprócz 3 podstawowych rodzajów zapytań, FTS3 w SQLite umożliwia tworzenie złożonych zapytań, składających się z funkcji logicznych:

AND
OR
NOT

Przykładowo, zapytanie:

SELECT * FROM table WHERE table MATCH '(Mee OR Pears) AND NOT Singap*'

zwróci tylko rekord "Uncle Bob's Organic Dried Pears", gdyż zaznaczyliśmy, że nie chcemy dostać rekordu z tokenem rozpoczynającym się od "Singap".

ListView - jak używać efektywnie

Poprawne oraz efektywne użycie ListView to temat na osobny bootcamp - chcielibyśmy jednak nakreślić rzeczy na które warto zwrócić uwagę przy używaniu tego widoku.

Optymalizacja metody getView:

Większość logiki, którą można ulepszyć w Adapterze listy znajduje się w metodzie getView():

public View getView(int position, View convertView, ViewGroup parent)

Jest to metoda wywoływana wtedy gdy na ekranie ma pojawić się nowy element listy. Argumentami tej metody są:

pozycja elementu, który ma zostać wyświetlony
parametr convertView klasy View, o którym za chwilę
rodzic do którego zostanie dodany stworzony View

Aby wykorzystać wszystko co przekazuje nam do tej metody Adapter listy, należy:

sprawdzić czy convertView jest null

jeśli nie jest, to znaczy, że mamy okazję użyć scache'owanego View - to znaczy - możemy po prostu update'ować jego widgety
w przypadku gdy convertView jest null musimy inflate'ować widok - jest to stosunkowo wolne.

W ten sposób możemy zapewnić, że zostanie stworzonych tyle View należących do ListView ile maksymalnie jesteśmy w stanie pomieścić na ekranie. Zmniejsza to w znacznym stopniu zużycie pamięci i przyspiesza działanie listy.

@Override
public View getView(int position, View convertView, ViewGroup parent) {
	if (convertView == null) {
		convertView = inflater.inflate(LIST_ITEM_LAYOUT_ID, null);
	}
	initializeWithProduct(convertView, product);
	
	return convertView;
}

Optymalizacja przy pomocy View Holdera:

Kolejną rzeczą, którą warto użyć w ListView jest wzorzec View Holder. Polega on na trzymaniu w zewnętrznym obiekcie referencji do wszystkich widgetów pojedynczego widoku-wiersza listy. Dzięki temu unika się powtarzania wywoływań metody findViewById(), która jest szybka, ale tylko dla widoków statycznych. View Holder może być przetrzymywany jako atrybut Tag widoku do którego dzieci trzyma referencje.

public ProductViewHolder createViewHolder(View view) {
	ProductViewHolder holder = new ProductViewHolder();
	holder.categoryIconView = (ImageView) view
			.findViewById(R.id.categoryIconImageView);
	holder.productNameView = (TextView) view
			.findViewById(R.id.productNameTextView);
	holder.categoryNameView = (TextView) view
			.findViewById(R.id.categoryNameTextView);
	holder.priceView = (TextView) view.findViewById(R.id.priceTextView);
	holder.quantityView = (TextView) view.findViewById(R.id.quantityTextView);
	return holder;
}

W przypadku gdy w ListView chcemy wyświetlić dużą ilość wierszy, warto zastanowić się nad paginacją. Pozwala ona zaoszczędzić pamięci na początku działania aplikacji - szczególnie gdy obiekty przechowujące dane do wyświetlenia zajmują dużo pamięci. Paginacja - od strony bazy danych - wykorzystuje zwykłe zapytanie SELECT z klauzulą LIMIT, określającą który rekord będzie zwrócony jako pierwszy oraz ile rekordów w sumie ma zostać zwróconych.

Więcej na temat poprawnego użycia widoku listy można oglądnąć na jednym z wykładów Google IO, zatytułowanym "The world of ListView". Znajduje się on pod linkiem.