Database

• What is database ?                                      

• Table with row and column, like an 2D array

• There are several database in general

• SQL - Structural Query Language

• Android use SQLite as it's database

SQL

• A language can manipulate different database

• However, it's not same among different database system 

• SQL is not case-sensitive

• So, talk about some basic SQLite usage

SQLite-Table

• CREATE TABLE                                                 

• DROP TABLE

CREATE TABLE contacts (
    first_name TEXT,
    last_name TEXT, 
    phone_number TEXT,
    height REAL);

DROP TABLE contacts;

SQLite Classes

• SQLite store data dynamic                         

• NULL

• INTEGER

• REAL

• TEXT

• BLOB

SQLite Data Affinity

• TEXT                                                                  

• NUMERIC

• REAL

• INTEGER

• BLOB(NONE)

Reference:                                                     

http://www.sqlite.org/datatype3.html

Database constraints

• The key word can constrain column value

• ex. foreign key, unique, not null, check

CREATE TABLE employers (
    _id INTEGER PRIMARY KEY,
    company_name TEXT);

CREATE TABLE employees (
    name TEXT,
    salary REAL NOT NULL CHECK (salary > 0),
    employer_id REFERENCES employers(_id));
    

SQL Data Manipulation

SELECT

• FROM

• WHERE, ORDER BY, HAVING, LIMIT              

SELECT * FROM contacts;

SELECT first_name, height_in_meters
    FROM contacts
    WHERE last_name = "Smith";

SELECT employees.name, employers.name
    FROM employees, employers
    WHERE employee.employer_id = employer._id
    ORDER BY employer.company_name ASC;

SQL Data Manipulation

INSERT

INSERT INTO contacts(first_name)
    VALUES("Thomas");

INSERT INTO employers
    VALUES(1, "Acme Balloons");

INSERT INTO employees 
    VALUES("Wile E. Coyote", 100000.000, 1);

• Add data to table                                                      

SQL Data Manipulation

UPDATE

UPDATE contacts
    SET height_in_meters = 10, last_name = "Jones";

UPDATE employees
    SET annual_salary = 200000.00
    WHERE employer_id = (
        SELECT _id
            FROM employers
            WHERE company_name = "Acme Balloons");

• update data in the table                   

• used with set, where

Database Transactions

• In general, manipulate database can divide into  two steps

• Read

• Write

• The database will change data both read and write are successful

SQLite3 demo

Android database classes 

• SQLiteDatabase             

• Cursor    

• SQLiteOpenHelper     

• SQLiteQueryBuilder                                   

Prevent malice input

input: "Mark"

INSERT INTO student 
    VALUES( "Mark" );

input: "Mark"); DROP TABLE student; 
        CREATE TABLE haha(
            hahaha TEXT

INSERT INTO student
    VALUES( "Mark"); 
DROP TABLE student; 
CREATE TABLE haha(
    hahaha TEXT);