Lesson 13 Persistence: SQL Databases Victor Matos Cleveland State - PDF document

Lesson 13 Lesson 13 Persistence: SQL Databases Victor Matos Cleveland State University Portions of this page are reproduced from work created and shared by Google and used according to terms described in the Creative Commons 3.0 Attribution License. SQL Databases Using SQL databases in Andorid Included into the core Android architecture there is an standalone Database Management System (DBMS) called SQLite which can be used to: Create a database Create a database, Define SQL tables, indices, queries, views, triggers I Insert rows, t Delete rows, Change rows, Run queries and Administer a SQLite database file. 13 ‐ 2

Lesson 13 SQL Databases Characteristics of SQLite • Transactional SQL database engine. • Small footprint (less than 400KBytes) • Typeless • S Serverless l • Zero ‐ configuration • The source code for SQLite is in the public domain. • According to their website, SQLite is the most widely deployed SQL database engine in the world . Reference : http://sqlite.org/index.html 13 ‐ 3 SQL Databases Characteristics of SQLite 1. SQLite implements most of the SQL ‐ 92 standard for SQL. 2. It has partial support for triggers and allows complex queries (exceptions include: right/full outer joins, grant/revoke, updatable views ). 3. SQLITE does not implement referential integrity constraints through the foreign key constraint model. 4. SQLite uses a relaxed data typing model . 5. Instead of assigning a type to an entire column, types are assigned to individual values (this is similar to the Variant type in Visual Basic). 6. 6. There is no data type checking, therefore it is possible to insert a string There is no data type checking, therefore it is possible to insert a string into numeric column and so on. Documentation on SQLITE available at http://www.sqlite.org/sqlite.html GUI tools for SQLITE: SQL Administrator http://sqliteadmin.orbmu2k.de/ SQL Expert http://www.sqliteexpert.com/download.html 13 ‐ 4

Lesson 13 SQL Databases Creating a SQLite database ‐ Method 1 SQLiteDatabase. openDatabase ( myDbPath, null , SQLiteDatabase. CREATE_IF_NECESSARY ); If the database does not exist then create a new one. Otherwise, open the existing database according to the flags: OPEN_READWRITE, OPEN_READONLY, CREATE_IF_NECESSARY . Parameters path to database file to open and/or create factory an optional factory class that is called to instantiate a cursor when factory an optional factory class that is called to instantiate a cursor when query is called, or null for default flags to control database access mode Returns the newly opened database Throws SQLiteException if the database cannot be opened 13 ‐ 5 SQL Databases Example1: Creating a SQLite database ‐ Method 1 package cis470.matos.sqldatabases; public class MainActivity extends Activity { SQLiteDatabase db; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout. activity_main); TextView txtMsg = (TextView) findViewById(R.id. txtMsg ); // path to the external SD card (something like: /storage/sdcard/...) // String storagePath = Environment.getExternalStorageDirectory().getPath(); // path to internal memory file system (data/data/cis470.matos.databases) File storagePath = getApplication().getFilesDir(); String myDbPath = storagePath + "/" + "myfriends"; txtMsg.setText("DB Path: " + myDbPath); try { try { db = SQLiteDatabase. openDatabase(myDbPath, null, SQLiteDatabase. CREATE_IF_NECESSARY); // here you do something with your database ... db.close(); txtMsg.append("\nAll done!"); } catch (SQLiteException e) { txtMsg.append("\nERROR " + e.getMessage()); } }// onCreate }// class 13 ‐ 6

Lesson 13 SQL Databases Example1: Creating a SQLite database ‐ Using Memory SQLite Database is stored using Internal Memory Path: /data/data/ cis470.matos.sqldatabases/ Where: cis470.matos.sqldatabases is the package’s name 13 ‐ 7 SQL Databases Example1: Creating a SQLite database on the SD card Using: SQLiteDatabase db; String SDcardPath = Environment . getExternalStorageDirectory() .getPath() + "/myfriends"; db = SQLiteDatabase. openDatabase ( SDcardPath, null, SQLiteDatabase.CREATE_IF_NECESSARY ); Manifest must include: <uses ‐ permission android:name= "android.permission.WRITE_EXTERNAL_STORAGE" /> <uses ‐ permission android:name= "android.permission.READ_EXTERNAL_STORAGE" /> 13 ‐ 8

Lesson 13 SQL Databases Sharing Limitations Warning • Databases created in the internal /data/data/package space are private to that package. • You cannot access internal databases belonging to other people (instead use Content Providers or external SD resident DBs). • SD stored databases are public . • Access to an SD resident database requires the Manifest to include permissions: <uses ‐ permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" /> <uses ‐ permission android:name="android.permission.READ_EXTERNAL_STORAGE" /> NOTE : SQLITE (as well as most DBMSs) is not case sensitive. 13 ‐ 9 SQL Databases An Alternative Method: openOrCreateDatabase An alternative way of opening/creating a SQLITE database in your local Android’s internal data space is given below SQLit D t b SQLiteDatabase db db = this.openOrCreateDatabase( thi O C t D t b ( "myfriendsDB", MODE_PRIVATE, null); Assume this app is made in a namespace called cis470.matos.sqldatabases , then the full name of the newly created database file will be: /data/data/cis470.matos.sqldatabases/myfriendsDB Internal Memory Package name DB name • The file can be accessed by all components of the same application. • Other MODE values: MODE_WORLD_READABLE, and MODE_WORLD_WRITEABLE were deprecated on API Level 17. • null refers to optional factory class parameter (skip for now) 13 ‐ 10

Lesson 13 SQL Databases Type of SQL Commands Once created, the SQLite database is ready for normal operations such as: creating, altering, dropping resources (tables, indices, triggers, views, queries etc.) or administrating database resources (containers, users, …). ) d i i i d b ( i ) Action queries and Retrieval queries represent the most common operations against the database. • A retrieval query is typically a SQL ‐ Select command in which a table holding a number of fields and rows is produced as an answer to a data request. request. • An action query usually performs maintenance and administrative tasks such as manipulating tables, users, environment, etc. 13 ‐ 11 SQL Databases Transaction Processing Transactions are desirable because they help maintaining consistent data and prevent unwanted data losses due to abnormal termination of execution. In general it is convenient to process action queries inside the protective frame of a database transaction in which the policy of “ complete success or total failure ” is transparently enforced. This notion is called: atomicity to reflect that all parts of a method are fused in an indivisible ‘statement’. 13 ‐ 12

Lesson 13 SQL Databases Transaction Processing The typical Android’s way of running transactions on a SQLiteDatabase is illustrated by the following code fragment (Assume db is a SQLiteDatabase) db. beginTransaction (); try { //perform your database operations here ... db. setTransactionSuccessful() ; //commit your changes } catch (SQLiteException e) { //report problem } finally { db. endTransaction() ; } The transaction is defined between the methods: beginTransaction and endTransaction . You need to issue the setTransactionSuccessful () call to commit any changes. The absence of it provokes an implicit rollback operation ; consequently the database is reset to the state previous to the beginning of the transaction 13 ‐ 13 SQL Databases Create and Populate a SQL Table The SQL syntax used for creating and populating a table is illustrated in the following examples create table tblAMIGO ( recID integer PRIMARY KEY autoincrement, name text , phone text ); insert into tblAMIGO(name, phone) values ('AAA', '555 ‐ 1111' ); The autoincrement value for recID is NOT supplied in the insert statement as it is internally assigned by the DBMS. 13 ‐ 14

Lesson 13 SQL Databases Example 2. Create and Populate a SQL Table • Our Android app will use the execSQL(…) method to manipulate SQL action queries . The example below creates a new table called tblAmigo . • The table has three fields: a numeric unique identifier called recID , and t two string fields representing our friend’s name and phone . t i fi ld ti f i d’ d h • If a table with such a name exists it is first dropped and then created again. • Finally three rows are inserted in the table. Note : For presentation economy we do not show the entire code which should include a transaction frame. db. execSQL ("create table tblAMIGO (" + " recID integer PRIMARY KEY autoincrement + recID integer PRIMARY KEY autoincrement, " + " name text, " + " phone text ); " ); db. execSQL ( "insert into tblAMIGO(name, phone) values ('AAA', '555 ‐ 1111');" ); db. execSQL ( "insert into tblAMIGO(name, phone) values ('BBB', '555 ‐ 2222');" ); db. execSQL ( "insert into tblAMIGO(name, phone) values ('CCC', '555 ‐ 3333');" ); 13 ‐ 15 SQL Databases Example 2. Create and Populate a SQL Table • After executing the previous code snippet, we transfered the database to the developer’s file system and used the SQL ‐ ADMINISTRATION tool. • There we submitted the SQL ‐ Query: select * from tblAmigo . • R Results are shown below. lt h b l 13 ‐ 16