maŁgorzata plechawska-wÓjcik mobile databases › wp-content › uploads › 2016 › 06 ›...

MAŁGORZATA PLECHAWSKA-WÓJCIK

MOBILE DATABASES

Mobile Application Development in Environment Monitoring –a New Program of Master Studies in English

Agenda2

Databases for mobile technology - overview

Single file databases

Mobile Client-Server Database

Web services

Relational database in mobile environment

Mobile platform databases:

Android example

Databases – a review

3

Data –

application requirements

Large amount of data

Common data for:

many programs

multiple users of the same program

Data persistence: long life

Program A

Data

In Out Program BIn Out

Program AIn Out

4

Data stored in files - problems

Sharing data – efficiency and conflicts

Solution (intermediate layer) Database Management System (DBMS)

Database

DBMS

Program AIn Out

5

What is a database?

Organized collection of data stored in an external

computer memory

Reflected part of reality

Features:

durability

compliance with reality

6

Database

A database system: DBMS

and database

A database schema

describes the structure of

data and links between

them

The database is managed

by Database Management

System (DBMS)

Computer

system

Database

system

Database

Schema

DBMS

Users

7

DB - desired properties8

Sharing data

No redundancy

Integrity, consistency

Data security

Authorization of data access

Data confidentiality

Data abstraction

Program-data independence

Reliability of data access

The efficiency of data processing

Parallel access

Database consistency9

Correct reflection of reality

Regarding constraints defined by the user

Concurrent access to data

Cohesion after failure

Resistance to user errors

Efficiency of data processing

Data access methods

Structure and optimization of access methods

Ensuring the independence between application

and physical access methods

10

Database data characteristics

Durability

The large size of a data volume

Mechanisms of efficient data access

Complexity

Structural complexity

Semantic complexity

Integrity restrictions

11

DB users and their models

Analysts - Designers

Developers

End users

Admins

12

Basic terms (2)

Data type

Data field (domain)

Data properties (general, physical and logical)

Relationship between data and their groups (type, size)

Integrity of the data and its levels

Redundancy

Undefind value (Null)

13

Data organization

Physical data structures

Orderedfiles

Hash files

Grouped files

Tree Indexes

Bitmap indexes

The physical organization of data on disk

It does not affect the operation of applications / programs

It is transparent to users

14

Transactionality

It ensures the consistency of the entire database

It is the interaction unit in the database

Features transaction (ACID):

Atomicity

Consistency

Isolation

Durability

15

DBMS - tasks

Organization of DB structure (DB schema definition)

Construction of DB (file system)

Data processing:

Data updating (inserting, updating, deleting)

Data searching (DB selects)

DB administration

Concurrent access to data

Ensuring data security

Ensuring the accuracy of DB in practice

16

DBMS – special features

Transaction processing

Optimization of processing

Locking resources (conflicts resolve )

Preventing deadlocks

The system of user accounts and access rights

DB monitoring

17

Database languages

Data Definition Language (DDL) – defines an

implementation scheme

Data Manipulation Language (DML)

Query Language

Data Control Language (DCL) – transactions

management

18

DBMS - architecture

Single Level Architecture

Two level Architecture: client-server

Three-level Architecture : Web server - application

server - BD server

Distributed architecture - multiple BD servers

19

Double-layer architecture:

Client-Server20

Client-Server architecture

The functions of the database server:

Data manipulating

Checking of the DB integrity

Access control

Blocking

Resolving Query Application

Organisation of work with multiple BD

Application functions (client):

User Interface

Query syntax

Implementation of complex queries

21

Client-Server architecture

Cons:

The need for purchasing application servers and BDservers

Strong customer

Pros:

Increase efficiency in network

Raising the DB security level (centralized handling, storage during operation, atomic transactions)

Flexible configurations

Ability to work in wide area networks

22

Three-level architecture23

Application server

The division of the application into two parts:

Transaction (computing)

User interface

Thin (weak) client

Decoupling of applications from DBMS (universal,

open application architecture)

24

Distributed processing25

Distributed processing

Transparency for the customer

Seed

The size of the DB

Reliability

Problems:

Communication

Transactional

Integrity

Maintaining the integrity and validity

Replication

26

Criteria for the database systems

division

The data model

Relational

Object-oriented

Object-relational

Semistructural (XML)

Hierarchic

Network

27

Mobile databases - overview

28

What is a mobile database?29

„A mobile database is either a stationary database that can be connected to by a mobile computing device (e.g.smartphones) over a mobile network, or a database which is actually stored by the mobile device” (*)

Two meanings:

Access to a database from a mobile client: a simpler solution Access from mobile clients to a centralized database, eg.

through the base station or proxy server

The ability to re-use existing technologies

Mobile database system: more demanding Stronger mobile device can support a more powerful database

Databases can be also stored on the outer memory cards

(*)Organize your business with a mobile

database, Kevin Ebi, Microsoft.com

Features of a modern

mobile device30

Operation system kept up-to-date

Quad-Core Processor + GPU

Independent of telecom operators

Efficient battery

Fast and capacious storage

HD Screen

2 high-quality cameras

NFC (ang. Near Field Communication)

Support for 4G and LTE

Dust and water resistance

Dual SIM

…

Computational power of mobile

devices31

The standard assumption

Mobile devices are limited in terms of computing power,

memory, etc ...

But…

1997: 233 MHz, 32 MB RAM, 800 x 600, HDD 2GB (PC)

2012: > 1,4 GHz quad core, 1 GB RAM, 720 x 1280

GB memory cards

The mobile device already in 2012 was an excellent

quality computer

Mobile computing power32

Operating systems33

iOS

Android

Windows Phone

BlackBerry OS

…

Smartphone sales34

Smartphone sales35

Mobile system market36

Mobile system market37

Mobile databases38

Mobile database system is a distributed system which: supports the connection from mobile clients

provides a complete database system capabilities

enable mobility for mobile devices using wireless communication technology

The database is available: mobile clients can accessto a database even when they are on the move

The database can be installed on the client’s mobile device

Connection is stablished via wireless links

Why mobile databases?39

People around the world more and more often employ smartphonesas accessing the web tools

In 2013 web traffic coming from mobile reached 17.4% increasing more than 6% compared to 2012 and doubling compared to 2011

By the end of 2016 number of regular mobile web users among American adults will reach 58%

58% of U.S. adults own smartphones and 42% own tablets

More than 58% of U.S. adults declare to be regular mobile web users, what confirms fast blurring boundaries of the way people use the Web.

Many people (44%) claim that they sleep with their phone next to their bed to be sure they will not miss any calls, messages or other updates

More than quarter of cell owners admit their device is so important that they cannot live without it


The number of smartphones in use worldwide -

more than a billion in 2012

The next billion devices can be achieved in 2016

Over half of mobile phone users globally will have

smartphones in 2018

Companies are focused on the mobility of workers

Powerful and lightweight computing devices and

low cost of telephone communications paved the

way for applications based on the data


Mobile data-based applications enable to access

data from anywhere at any time

Examples:

Vendors can update sales data on the way

Reporters can add news anytime

Doctors can charge a patient's medical history from any

location

Mobile database systems are needed to support

application data processing capabilities


Mobile database is a database with which user can connect using your mobile device through a wireless cellular network or other medium

Mobile Database:

Physically separated from the central database server

Placed on mobile devices

Capable to communicate with a central database server or other client mobile

Enable to support local queries without establishingconnection to the server

Mobile databases43

Web

server

computer system

Database

serverDatabase

server

Base

station

Base station

Wired (cable)

connection

Wireless

connection

Mobile clientMobile client

Local database

Mobile platforms44

Mobile devices do not always have an Internet connection or the connection speed is not impressive

The problem with connecting to the database "on the fly"

In addition: the risk of failure of the database server, or any connection failure

Mobile apps become useless

Creating a local copy of the database, and only occasionally synchronizing data with the database server

Mobile Client-Server databases45

46

Client-server model is a traditional model of IT

systems

The server can become a single point of failure and

performance bottleneck

Even the storage of a central database on a cluster

of servers can cause a bottleneck and data

inconsistencies

Mobile Client-Server databases

Mobile Peer-to-Peer databases47

48

In case of the mobile P2P databases activities of

the database maintenance are distributed among

mobile clients

Each process plays two roles: client and server

A client who wants to access the data fragment

sends a request to other customers who transfer

request until you can find the desired data

The main problem with this model is to ensure the

availability of data

Mobile Peer-to-Peer databases

Characteristics of mobile

environments49

Features of the mobile environment:

The limited bandwidth of wireless networks

Limited power

Limited resources

Mobility

Exposure to disconnect

Mobile database approaches50

Many mobile applications developers use flat files to store the data

Flat files are files containing records that have no structural link

Pros:

Smaller and easier to manage

Cons:

The application must know the organization structure of records in the file

Developers must implement the database requirements

Requirements for mobile DBMS51

Mobile Database Management System should meet the following requirements:

Small amount of needed memory (memory footprint)

Data storage system optimized for Flash memory and derivatives

Data synchronization mechanisms

Security mechanisms

Low power consumption

Automation of management

Embedded into applications






Security mechanisms




Small Memory Footprint53

Memory consumption is the amount of main memory that an application uses while running

Mobile devices have limited memory, so the database for mobile applications should also have a small footprint

The size of mobile database affects the applicationresources

Mobile DBMS should be configurable so that it can contain only the most important database features






Security mechanisms




Optimized data storage system55

Flash memory is dominant for mobile devices

Their functions:

Small Size

Improved resistance to shock


Short access time

Access is not implemented mechanically (search, rotational latency)

Mobile DBMS must be optimized to take advantage of new storage devices






Security mechanisms




Data synchronization 57

Mobile devices can not maintain a permanent

connection all the time

Users can access and manipulate data on their

devices

Devices are able to store large amounts of data

Mobile DBMS should support the ability to

synchonize to integrate different version of the

data into one coherent version






Security mechanisms




Security59

Security is a very important issue for mobile

applications with centralized data

In particular, if the application works on the critical

data, which might be at risk of data leakage in the

case of failure

The data that are sent over the wireless network

are more prone to security problems

Mobile DBMS should implement full end-to-end

security in order to ensure secure data transfer






Security mechanisms




Low power consumption61

Mobile devices have limited power supply

It is expected that the mobile phones batteries life

of will increase by only 20% over the next 10 years

CPU, monitor and network connections are the main

energy consumers in the mobile device

Mobile database must be optimized for energy

efficiency






Security mechanisms




Automation of management63

In traditional databases the database administrator

(DBA) is responsible for maintaining the database

There are no DBA in mobile DBMS

Mobile DBMS must support self-management and

automatically perform tasks typical for DBA

Some mobile DBMS allow remote management that

enable the DBA to remotely manage a mobile

database






Security mechanisms




Databases embedded

into applications65

Administrators do not have direct access to mobile

devices

Mobile DBMS should be an integral part of the

application and may be provided as part of the

application

The database must be embedded within an

application

It must also be possible to implement the database

as stand-alone, usually multi-transactional SZDB

Existing solutions

for mobile databases66

Mobile databases

Sybase SQL Anywhere

Oracle Lite

Microsoft SQL Server Compact

SQLite

IBM DB2 Everyplace (DB2e)

Embedded databases

TinyDB

PicoDBMS

Sybase SQL Anywhere67

Originally developped by Watcom as Watcom SQL

First version: 1995

Database files are independent of the operating

system and can be moved between supported

platforms

Strong encryption is supported for both the

database files and client-server communication

Oracle Lite68

Omniscience Object Technology, Inc was acquired

by Oracle Corporation in1996

Omniscience ORDBMS became the first version of

the Oracle Lite

Oracle Lite uses 1 MB of memory and can be

installed on 3 MB of hard disk space

Personal Oracle Lite (POL) is a lightweight, single

user relational database that runs on desktops,

laptops, but also on a variety of mobile devices

Microsoft SQL Server Compact 69

Formerly known as the SQL Server Mobile Edition

Available for free to download and distribute

Optimized for architecture, where all applications share the same storage pool

SQL CE is running in the application process that hosts it

Memory consumption approx. 5 MB disk less than 2 MB

SQLite70

SQLite is an open-source mobile database engine

It is no-server database engine that requires no

configuration

SQLite is popular as a mobile database for local

data storage of mobile applications

SQLite engine is not an independent process which

could communicate application

SQLite implements most of the SQL-92 standard

IBM DB2 Everyplace71

The project DB2e was interrupted in April 2013 r.

He had the largest market share after SQL Anywhere

It had the lowest amount of memory (350 KB), as

compared to other commercial mobile database

IBM has replaced DB2e of IBM solidDB

SolidDB is built into the device memory MDBMS

In 2014, SolidDB was sold to the company UNICOM

In-memory type database, typical for

telecommunication devices

Embedded databases72

Embedded database systems are tightly integrated with the application, which needs access to stored data

They are hidden from the end user and require little or even no maintenance

Embedded database require fewer resources in comparison with the mobile database

They are optimized for particular devices such as smart cards and sensors

They support limited, specific features of the standard SQL

PicoDBMS73

PicoDBMS supports only the appropriate features for dedicated IC cards application (smartcard applications)

These applications are used for data management (insert, remove, modify, and search operations)

PicoDBMS supports some SQL standard:

INSERT, UPDATE, DELETE, SELECT

CREATE/DROP TABLE/VIEW

GRANT/REVOKE

Memory footprint is about 30 kB

TinyDB74

TinyDB was developed at the University of Berkeley

It supports only the basic functions needed for

sensor applications

Most sensor applications are used to filter out

certain data (selecting data with the specified

conditions)

TinyDB supports only selected operations of

standard SQL

Its memory footprint is only 3 KB

Comparison75

Target devices Mobile DBMS

Very small devices

with low computing

power

Sensors TinyDB

Electronic cards PicoDBMS

Small devices with

high computing

power Małe

urządzenie z dużą

mocą obliczeniową

Smartphones, PDA,

car navigation,

ultrabooks

Sybase SQL

Anywhere, Oracle

Lite,

MS SQL Server CE,

SQLite

IBM DB2 Everyplace

Functionalities76

TinyDB PicoDBMS Oracle Lite IBM

DB2e

MS SQL Server

Compact

The minimal memory

usage

3 KB 30 KB 970 KB 320 KB 2 MB

SQL SELECT only a part of

SQL99

a part of

SQL99

a part of

SQL99

a part of

SQL99

Views N Y Y Y Y

Constraints N N/A Y Y Y

Parallelism N N Y Y Y

Indexes N Y Y Y Y

Encryption N N/A Y Y Y

Access control N Y Y Y Y

Support for the

MDBMS requirements77

TinyDB PicoDBMS Oracle Lite IBM

DB2e

MS SQL Server

Compact

Small memory

footprint

Y Y Y Y Y

Optimized for Flash

memory

N N N N N

Data synchronization

mechanisms

N N Y Y Y

Security mechanisms N Y Y Y Y

Low power

consumption

Y Y N Y Y

Automation of

management

Y Y N/A Y N/A

Flat file databases78

widely used

Easy to use

simple design

Binary or text file

One record in line

Separators

Useful during data migrations

But: Security problems

There are few studies devoted to the security of the file databases

The safety of such files often depends only on operating system securitymechanisms

Examples of database files: XML, JSON

Relational databases - review

79

Agenda80

Application design – a general idea

From requirements to database structure

Entity Relationships technique – Logical Data Model

Relational Schema – Physical Data Model

Model transformations

SQL Code generation

Some challenges and practical solutions of Database

Design

CASE Tools

Application modelling – levels 81

Abstracton in

Computing

Language

Implementation Model

System Analyse

Ideas

Abstraction in

Domain/Business

Language

Conceptual model

ModellingMapping Generating DB

Physical

DataBase modeling82

IdeasER

DiagramRelationalSchema

SQL scriptfor

RDBMSLogical Data Model Physical Data Model

ER design 83

Entity and Relationship

Entity-Relationship Diagram presents data (collections of data) and relationships between them

Developed by Peter Chen (1976)

Different notations: Codd, Hammer, Barker, Martin/IE, SSADM, Bachman, …)

Basis notation:

Rectangles represent data entities

Lines connecting rectangles show relationships among data entities

ERD (Crow’s Foot Notation)84

Entity - rules85

Entity – a class of objects having the same features

Entity – concrete (student, book) or abstract

(transaction, bank account)

Entities must be distinguishable by name (singular,

capital letter)

Entities have instances (occurrence)

Each instance of an entity must be clearly

distinguishable from all other instances of the same

entity by data (id, primary key)

Entity vs. other design artifacts86

Entity is a data Thing – collection of information must be stored

Entity is a Class – objects must be persistence

Entity:

Tangible thing: Object

Unit

Device

Site/location

No-tangible thing: Role played

Events

Facts

Relationships87

Associations between entities

Implemented at entity’s instances

Provide information

Should have names

Characteristics:

Name

Cardinality/Multiplicity (one-to-one, one-to-many, many-to-many)

Modality (mandatory/optional) – zero-or-one or zero-or-more

Crow’s foot

Relationships - notation88

Crows foot

Traditional

Classical ERD

ChildParent

Cardinality of a relationship89

Invoice Customer

? ?

has

Invoice

nr 67/1

Invoice

nr 69/1

Invoice

nr 99/11

Invoice

nr 67/1

n 1

Modality90

Whether the customer is a customer without an

invoice?

Dynamic of data in the application

Symbols:

zero-or-…/one-or-…

Identifying/Non-identifying

relationship91

Strong entity – „typical” relationship

Weak entity – instances cannot exist without

relationship (identifying relationship)

Weak

Identifying

Relationship

Self-relationship (recursive)92

has a manager

has a manager

has a manager

Employee

Employee

Non-identifying

Relationship

Type of relationships93

Binary (two different entities)

Unary (recursive)

Ternary - a relationship between three different

types of things (e.g. product associated with a

customer plus a sales representative)

N-ary (n – any number) – a relationship between n

entities

N-ary relationships realisation94

Many-to-many (n:m)95

Invoice Productoccurs

Invoice Productoccurs

InvoiceItemconsists of

Associative entity

Attributes of entity96

Attribute is one piece of specific information about the

entity (student has a name, phone number, index

number, etc. – attributes of a student)

Attributes = data needed to be stored

Identifier of the entity instance (or key) – an attribute

that uniquely identifies an instance (e.g., student index

number or invoice number)

Derived – an attribute which can be derived (imported

or calculated) from related entities (e.g., bank account

balance or average mark at first semester, user’s age)

Attributes - essence97

Simple (atomic) vs. Composite (e.g. City vs. Adress)

Single-valued vs. Multi-valued (e.g. Name vs.

Phone-numbers)

Features:

Dependence only on the entity instance

Type

Mandatory (not-null)

Business rules

Types of data (attributes)

ERD – Logical Data

Model

Physical Data Model

(depends of RDBMS)

98

MS SQL Sever

Entity with attributes99

Key attribute100

Single

Multiple – a set of attributes

Key attribute design101

Entity may have many Unique Identifiers (UI) –

candidate keys

Primary key (PK)/Primary UI (PUI) – only one key -

can be chosen from candidates

Often – PK is designed as an artificial key:

hidden-automatic (RecId, OId)

not-hidden - Id (e.g. social number, index number,

invoice number, vehicle registration number)

Shouldn’t be taken from other application

Multi-value attributes102

Must be modelled by additional entity

Multi-value attributes (known

number)103

Known number of atomic attributes (e.g. 4 year

studies)

Must be modelled by additional entity

ERD design – step by step104

1. Entities having primary key identification (for

determination of instances)

2. Defined entity attributes

3. Relationships identified using a matrix

4. Many-to-many relationships decomposed using

associative entities (optionally attributes adding)

5. ERD checked with application requirements

Entity 1 Entity 2 Entity 3

Entity 1 x x

Entity 2 x

Entity 3

Computer Aided Software Engineering

CASE Tools

105

CASE tools

Commercial:

ER/Studio

Erwin

MEGA International

ModelRight

Navicat Data Modeler

Oracle Designer

PowerDesigner

System Architect

Toad Data Modeler

Visio

Visual Paradigm

Free:

MySQL Workbench (formerly DBDesigner)

Open ModelSphere

RISE Editor

106

Toad Data Modeler107

Database design tool (Czech Republic)

Draw Entity Relationship Diagrams (ERD), map and

generate SQL scripts automatically

Support all main RDBMS

Freeware version (120 days, max 25 entities)

New project108

New model109

Notation110

Add objects (1) 111

Add objects (2) 112

Entity

Relationships (identifying and no-)

Inheritance

Stamp

Category

Define entity (1)113

Define relationship (1)117

Define relationship (2)118

Define inheritance (1)119

Define inheritance (2)120

Logical to Physical Data Model mapping

Relational Schema

121

Relational Schema122

Tables with rows and columns (both, unordered)

Primary key (unique, not-null)

Foreign key (relationships realization)

Data integrity (different level: value – referential)

Indexes

Views, tabalespace

Triggers, stored procedures

Schema normalization process (theoretical)

Relational Schema on figures123

Foreign key124

Refers to Primary (or Alternative) Key

Should be the same type

Weak

entity

Referential Integrity125

Types:

Restrict

Cascade

Set Null

Set Default

None

Parent – Child actions:

Update – Delete

Insert - Update

Physical Model Design126

Transformation from Logical Data Model – ERD

Reverse engineering from existing DB

Problems of transformations:

Names (done by hand to meet conventions)

PUI PK

Relationships (automatically: associative and weak tables, and FK or PFK)

Types (automatically, using mapping table)

DB constrains (number of columns/size of record/field)

Transformation of inheritance127

Three possible ways

Exclusive inheritance

Transformation details (1)128

Transformation details (2)129

SQL Code130

The choice of mobile database

131

Data replication132

Mobile applications store large amounts of data

Users do not always have the ability to synchronize

with the server

No connection or broken signal

The need to store data on a device

The current database tools allow you to easily

replicate data on a mobile device

Client devices and server databases are synchronized

Tool selection133

Flat data files

Slowing application

Security problems

The platform usually determines the choice of tools, including databases

Adapting to a customer

The choice of more popular platforms

Select the database available at different platforms

Portability is required to avoid the need to support different versions of the software

Might be very costly

Session durability134

Wired connections provide continuous communication (eg. The

synchronization between PDA and PC via a USB connection)

Wireless connections are more prone to communication loss, what requires

a more intelligent client software

Solution:

Built-in automatic data synchronization

For example, Oracle Lite contains a client-side representation of servers and

applications data In case of lost connection, it works based on recently updated

data. The data are updated automatically.

The use of middleware - an intermediary layer between the client device and

the server

If the wireless connection is lost, the intermediate layer keeps communication with

the server, so the session remains open. This layer also provides data

synchronization

Data security135

Ensuring data security is particularly important for applications running over wireless networks

Standards of wireless networks (eg. 802.11 or Wi-Fi) support encryption. Client devices encrypt data before transmission, and access point decrypts it before transmitting it to the server

As a result, data sent by a distribution system such as Ethernet, are transmitted publicly

For mobile use full end-to-end security should be implemented

For example, iAnywhere provides 128-bit encryption between the client device and the server

Connection protocol136

The need to data synchronization between the

device and the server

The database should support the synchronization

Availability of standard connections such as ODBC

and JDBC is critical for most applications

For example, Oracle Lite offers support for ODBC and

JDBC, assuring flexibility

Availability of

development tools137

In general, tools for large databases managing aremore widespread than the tools for mobile applications

iAnywhere supports environment such as Java, Sybase PowerBuilder, Microsoft Visual Basic, Visual C ++, Visual Studio .NET, AppForge MobileVB, Symantec Visual Cafe, Borland Delphi and Metrowerks CodeWarrior

Oracle9i Lite offers a development kit that includesmultiple APIs, integration tools and sample code

Open source tools138

Offer flexibility

Independence from external suppliers, which can

make the changes

The ability to integrate solutions

Relatively broad distribution of open source solutions

is subject to the technical control of community

developers

Support for

database management139

Oracle Lite provides mobile control center server, which provides easy management of mobile database

After launching the application, the administrator uses the control center to assign application access permissions to users and check the status of client devices

Open source solutions (eg. BerkleyDB) do not require special management tools - use the operating system's resources to carry out such tasks as handling backup, recovery management, performance tuning and routine maintenance

These solutions are more integrated with the operating system

SQLite

140

SQLite141

Relational database

Open source

Serverless

Self-sufficient

No configuration required

Small: 350 KB (32-bit)

Selected as the "file format" of application data

SQLite – applications142

500 million copies estimated, 100 million for "large" SQL engine

SQLite architecture143

Lexical analyzer of

SQL commands

Parser

Code generator

Interface

Query processing

mechanism

Virtual machnine

processing code

Storing mechanism

based on the B-tree

Cache of the page

(default – 1024 bytes)

Intermediate

abstraction layer

Memory allocation

Test code

SQLite features144

Transactional

ACID

Single file

File format adapted to different platforms

Small memory requirements

Efficiency

Stability

No need for administration

Self-management

Built-in

Serverless

Ease of integration

Availability

SQLite features –

Transactional145

Transactional database - all queries should be ACID

(Atomic, Consistent, Isolated, Durable)

SQLite supports transactions at the serializable level

the highest level of transaction isolation

ACID is maintained even in case of interrupted

operation, in the case of:

Application crashes

Operating system failure

Power failures and unexpected machine turning off

SQLite features –

Single file146

The entire base is located in one portable file

There are: tables, indexes, triggers, views, etc.

SQLite code is available as one large file (amalgamation) sqlite3.c (with the header file sqlite3.h)

To start using SQLite functions the header file needs to be added to the project

The entire engine is written in C and uses only the basic library functions

approx. 60 thousands code lines in C

Size: 3.5 MB

No additional libraries required

The library functions:

memcpy (), memset (), malloc (), free (), ...

It is easy to add as part of a larger program in C

SQLite features –

Serverless147

In contrast to DBMS in client/server architecture,

SQLite is serverless

The only serverless database with multiple access

support

No need for administration

Benefits of serverless148

No server process running in the background

No configuration files

No interprocess communication mechanisms

No common safety problems

No need to run and manage the process

No risk of configuration problems

No need for server management and administration

Portable file format151

The database is a single ordinary disk file

There is no special naming convention or required

file suffixes

Cross-platform (different processors, 32/64 bit)

Backward compatibility starting with version 3.0.0

(and provided for consecutive versions)

Unrelated to any particular programming language

Other SQLite features153

Functions defined in an application

List of sequences defined in am application

UTF8/UTF16 support

Protection against power loss

malloc () fault tolerance

Full-text searching support

R-Trees based

ATTACH DATABASE function

Large data size stored in BLOB and string

I/O fault tolerance

Efficiency154

It is assumed that the SQLite performance is

comparable to popular DBMS client-server

Tests carried out in different scenarios and for

system various versions show differ results

SQLite is not a competitor for Oracle SQLite, but rather to

fopen ()

SQLite interfaces155

The basic tool: shell client sqlite3

Takes at least one argument - database file name

SQLite provides a standard programming interfaces

for C, C ++ and TCL:

C/C++ fully documented interface includes all

necessary features to use SQLite

TCL interface based on sqlite3 command, with full

specification

SQL-92 Standard157

SQLite supports relational database standard

SQL syntax

transactions

parameterized queries

Exceptions:

No support for right and full outer joins (RIGHT and FULL OUTER JOIN) (LEFT OUTER JOIN join supported)

Support for ALTER TABLE is limited to RENAME TABLE and ADD COLUMN

No support for DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, etc.

Support only for FOR EACH ROW triggers

No support for FOR EACH STATEMENT

Read-only views

Access rights are controlled at the file system level

no sense to support GRANT and REVOKE

SQLite datatypes158

Supported data types:

NULL - empty (undefined) value

INTEGER - integer (signed), stored at from 1 to 8 bytes (as required)

REAL - Float stored on 8 bytes

TEXT - Text encoded with UTF-8, UTF-16BE or UTF-16LE

BLOB - (Binary Large Object) data stored in binary form (such as disk files)

Boolean - integer (integer), 0 (false), 1 (true)

All other types must be converted to the above before writing to adatabase

No validation of data types inserting to database columns

for example a number might be inserted into a text column, and vice versa

Primary key column must be INTEGER and have the name "_id”

SQLite data types - date159

Data can be stored as:

TEXT in ISO8601 convention ("YYYY-MM-DD HH:MM:SS.SSS")

REAL as the number of days (including fractional part) of the

Julian calendar (where the average length of the year is 367.25

days) that have passed since midnight in Greenwich, October 24

4714 BC (Gregorian calendar)

INTEGER - Time Unix time, the number of seconds that have

elapsed since midnight 1 January 1970, 1970-01-01 0:00:00

UTC (Coordinated Universal Time, Greenwich Mean Time)

Data types affinity160

SQLite does not force to define data type stored in

a particular column

In order to increase compatibility with other

databases SQLite force to store data with specified

group type

If the column data type is defined and another

data type is inserted, the conversion is done (if

possible)

PRAGMA commands161

SQLite-specific SQL extension

Used for:

SQLite configuration changes

internal data reading

PRAGMA commands162

auto_vacuum (NONE, FULL, INCREMENTAL) –VACUUM operations calling automatically

cache_size – cache size

database_list – currently open database

encoding – encoding specification (np. UTF-8, UTF-16)

foreign_keys – whether foreign key constraints shouldbe checked

locking_mode – access blocking mode

synchronous – database waits until the data is written to the disk

table_info – displays information about a table

PRAGMA commands –

examples163

Enabling foreign keys in SQLite

PRAGMA foreign_keys = ON

Synchronization disabling - database command after a request does not wait until disk writing data operation is fully performed, but it deals with otheroperations

PRAGMA synchronous = OFF

Counting records affected by INSERT, UPDATE, DELETE operations

PRAGMA count_changes = ON

C/C++ interface – functions164

sqlite3 – database handler

sqlite3_open – opens database

sqlite3_prepare – prepares query

sqlite3_step – queries performed until first/next rowobtaining

sqlite3_column – returns a single column with one rowresults

sqlite3_finalize – destroy previously prepared query

sqlite3_close – closes the database connection

sqlite3_exec – calls SQL functions

sqlite3_table – as above, but caches the results instead of callback calling

SQLite – basic sample165

#include <iostream>

#include <cstdlib>

#include <sqlite3.h>

#pragma comment( lib, "sqlite3.lib" )

using namespace std;

int main(){

sqlite3 *db;

char *zErrMsg = 0;

int rc = sqlite3_open("test.db", &db);

if( rc ) {

cout << "Can’t open database: " << sqlite3_errmsg(db) << endl;

exit(0);

} else

cout << "Opened database successfully\n";

sqlite3_close(db);

return 0;

}

SQLite client in C166

#include <stdio.h>

#include <sqlite3.h>

static int callback(void *nic, int argc, char **argv, char **kol) {

int i;

for(i=0; i<argc; i++)

printf("%s = %s\n", kol[i], argv[i] ? argv[i] : "NULL");

printf("\n");

return 0;

}

int main(int argc, char **argv) {

sqlite3 *db;

char *zErrMsg = 0;

int rc;

rc = sqlite3_open("../demo.db", &db);

rc = sqlite3_exec(db, "SELECT * FROM pracownicy", callback, 0, NULL);

sqlite3_close(db);

return 0;

} d.b. handler

SQL query

Callback function

Callback function

argument

Error message

sql = "CREATE TABLE STUDENCI(" \

"ID INT PRIMARY KEY NOT NULL," \

"NAME TEXT NOT NULL," \

"SURNAME TEXT NOT NULL," \

"AGE INT NOT NULL," \

"ADDRESS CHAR(50)," \

"AVERAGE REAL );";

SQL query creating table167

Return function168 int response(void *NotUsed, int argc, char **argv, char **azColName)

{

int i;

for(i=0; i<argc; i++)

{

cout << azColName[i] << " = ";

cout << (argv[i] ? argv[i] : "NULL") << endl;

}

cout << endl;

return 0;

}

Query performing169

rc = sqlite3_exec(db, sql, odpowiedz, 0, &zErrMsg);

if( rc != SQLITE_OK)

{

cout << "SQL error: " << zErrMsg << endl;

sqlite3_free(zErrMsg);

}

else

cout << "Operation done successfully\n" << endl;

The fourth argument of sqlite3_exec function can be a pointer, that will be

passed to the callback as its first argument

SQL script170

sql =

"INSERT INTO STUDENTS (ID,NAME,SURNAME,AGE,ADDRESS,AVERAGE)"\

"VALUES (1, ’Adam’, ’Nowak’, 20, ’Lublin’, 4.5 );"\


"VALUES (2, ’Beata’, ’Kowalska’, 19, ’Warszawa’, 5.0 ); " \


"VALUES (3, ’Jan’, ’Chrobry’, 21, ’Rzeszow’, 3.5 );"\


"VALUES (4, ’Iwona’, ’Waza’, 22, ’Gliwice’, 4.04 );";

SQL script171

sql = "SELECT * from STUDENTS";

sql =

"SELECT * from STUDENTS where ADRESS = ’Gliwice’";

sql =

"SELECT NAME, SURNAME from STUDENTS";

sql =

"SELECT NAME,SURNAME from STUDENTS where AGE < 21";

sql =

"SELECT NAME,SURNAME from STUDENTS"\

" where AGE < 21 and AVERAGE >= 4.5";

SQL172

sql = "UPDATE STUDENTS set AVERAGE = 3.8 where

ID=3";

sql = "DELETE from STUDENTS where ID=2";

sql = "DROP TABLE STUDENTS";

SQLite console173

Compliation: cl shell.c sqlite3.c

Result file: shell.exe

SQLite console commands174

.exit //exit console

.help //help

.restore ’test.db’ //load database

.tables //table list

SELECT * FROM STUDENTS;

DELETE FROM STUDENTS where ID = 1;

.backup ’test.db’ // save the database into a file

Example database

Chinook Database175

SQLite Manager - DEMO176

SQLite3 in PHP –

SQLite3 class177

Access to SQLite3 database using SQLite3 class

The most important methods:

SQLite3::open – database opening

SQLite3::prepare – preparing query, creating SQLite3Stmtobject

SQLite3::query – directly executing a query, creatingSQLite3Result object

SQLite3::exec – for queries that return no result

SQLite3::changes – the number of rows affected by last query

SQLite3::lastInsertRowID – ID of recently added rowSQLite3::close – database closing

SQLite3 in PHP –

SQLite3Result class178

Objects of this class store query results


columnName – returning name of nth column

columnType – returning type of nth column

fetchArray – getting the next row as an array or

associative array

finalize – closing query results

numColumns – number of columns in result

reset – returning to the first row

SQLite3 in PHP –

SQLite3Stmt class179

Objects of this class store the query result

The object of this class is created after the call of SQLite3::Prepare


bindParam – assigns to the parameter to binding variable

bindValue – assigns to the parameter ‘s value to bindingvariable

clear – clears all currently assigned parameters

close – closes prepared query

execute – executes a query

paramCount – returns the number of parameters

reset – resets a query

SQLite3 in PHP –

basic example180

$db = new SQLite3(’../demo.db’);

$result = $db->query(’SELECT * FROM employees’);

while ($row = $result-> fetchArray(SQLITE3_ASSOC)) {

print_r($row);

}

$db->close();

Associative array indexed by

column names

SQLite3 in PHP –

example with argument181


$query = "SELECT * FROM employees WHERE

name=’„.$argv[1]."’";

$result = $db->query($query);

while ($row = $result-> fetchArray(SQLITE3_ASSOC)) {

print_r($row);

}

$db->close();

SQLite3 in PHP –

example with SQLite3Stmt182


$query = ’SELECT * FROM employees WHERE surname=:surname’;

$stmt = $db->prepare($query);

$stmt->bindValue(’:surname’, $argv[1], SQLITE3_TEXT);

$result = $stmt->execute();

while ($row = $result->fetchArray(SQLITE3_ASSOC)) {

print_r($row);

}

$db->close();

SQLite3 in PHP – PDO,

in memory database183

Connection data errors – exception cathing

try {

$dbh = new PDO(’sqlite::memory:’, null, null);

$dbh->query(’CREATE TABLE FOO (a INTEGER)’);

$dbh->query(’INSERT INTO FOO VALUES (1)’);

$dbh->query(’INSERT INTO FOO VALUES (5)’);

foreach($dbh->query(’SELECT * from FOO’) as $row) {

print_r($row);

}

$dbh = null;

} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();

}

Android platform

SQLite on mobile platforms

185

Android186

System features

Flexible framework enabling full and flexible componentsmanagement

Dalvik virtual machine optimized for mobile devices

Integrated web browser based on WebKit engine

Fast graphics basen on OpenGL ES 1.0

Built-in SQLite database

Support for multimedia formats

Native support for communication

Rich development environment

Device emulator

lLbrary for debugging and profiling

Plug-in for Eclipse

Architecture of Android187

Libraries188

It includes C/C++ libraries used by various system

components

Surface Manager - display system supporting

Media - recording and playing many popular audio

and video formats

FreeType - bitmap and vector font

SQLite - a small, but powerful relational database

SDK189

SDK for Android available through

developer.android.com

Development environment based on Eclipse

The emulator enabling application testing

Example project directory

structure in IDE Eclipse src - contains classes responsible for the application working, for

example activity classes

gen - contains elements generated by the compiler. Location of

elements is defined in XML files. The program, after compiling, will

not contain those elements in the form of inserted files, but in the

form of a R.java file

bin - contains compiled classes and the application in the form of

ready application package – apk (Android Application Package)

libs - contains essential libraries

res - contains all static resources - images, sound files, video etc

plik AndroidManifest.xml –contains settings such as the definition

of the main activity (screen), entire application features, required

permissions

drawable-xxxxx – intended for pictures (ie. in different

resolutions)

190

Android system applications191

Applications are created in Java:

Compiled program files ("dex"), together with data files

Placed in the "apk" file (Android Package), which can be transferred to device for installation

Applications are launched in their own environment:

By default, each application is a separate user (given a unique UID)

The system sets permissions to all application files - onlyapplication should have access

Each application is runs in its own virtual machine

By default, each application is run in a separate Linuxprocess

UID and GID192

UID – user ID

GID – group ID

Android uses these identifiers in mechanisms of

application isolation

Each application has a unique identifier

The application can not read or write to files of other

applications

Resource sharing193

There is possibility to share data between applications,

as well as to use system resources by the application:

Two applications can share files, if they have given the same

Linux user ID (UID)

By sharing the same UID, two applications might be running

in the same process, and share one virtual machine

Application can request permission to gain access to device

resources (contacts, text messages, storage, camera,

Bluetooth, etc.).

Permission must be granted by the user during application

installing

Application components194

Application elements

Each component is the point used by operating system to control theapplication

Components are used to determine the overall application behavior

Not all components are relevant entry points, some components are linked together (they are dependent on each other)

There are four types of application components

Each of them is used for a specific purpose and has assigned life time, which specifies how the component is created and destroyed

Components are:

Activities

Services

Content providers

Broadcast receivers

Activity195

Activity represents a single screen containing the user interface

Examples (based on mail client application)

Activity presenting a list of new e-mails

Activity enabling e-mail creation

Activity showing the contents of read messages

Each activity is independent in action, although they form a consistent user interface

Another application (if it has the rights) can launch another application activities

eg. the camera application after foto making may run activity of e-mail client application to send the made foto

The procedure is implemented as a subclass of Activity class

Service196

Service is a component running in the background. It performs long-term operation or remote processes operations

Examples:

playing music in the background (user at this time can work with another application)

downloading data in the background (data can be retrieved without blocking user interaction with the application)

A service does not provide a user interface

Other components (eg. Activity) can run the service (startService) or binding with them (bindService)

The started service works endlessly, bound service is destroyed when all links disappear

The service is implemented as a subclass of Service class

Content provider197

Content provider manages collections of application data

Data can be stored in files (file system), SQLite database, web or any other place accessible for application

Content provider offer other applications to retrieve or modify data (if supplier allows them to do so)

For example, content provider gives access to contacts

Applications with persmissions can query content provider and obtain information about contact entries

The content provider does not need to be public and can be used to private data managing

The content provider is implemented as a ContentProvidersubclass

Broadcast receiver198

Broadcast receiver is dedicated to receive broadcast messages

The source of broadcast messages may be the system (eg. turned off screen, low battery) or a application (eg. To download data from the network)

Broadcast receiver does not display user interface, but it can create status bar notifications

Broadcast receiver is implemented as a BroadcastReceiver subclass

Intent200

Intention binds one component to another regardless of whether the second component is a part of the actuating applications or other

Intent is a Intent class object, which specifies messages needed to run a component

Intent describes the following:

action - general action to be performed, eg. ACTION_VIEW, ACTION_EDIT, ACTION_MAIN, etc.

data - data to be transmitted to a component, specifying eg. a person from Contacts

category - additional information

type - MIME data type

component - explicitly specify the component (by default, the system automatically determines data)

extras - additional items stored in a Bundle class object

Manifest201

AndroidManifest.xml file contains information about the application:

definitions of components generated by the system

permissions to be obtained from the user (eg. access to contacts, to the Internet, etc.).

the minimum API level required

declarations of hardware and software resources required

libraries to be included (besides Android API’slibraries), eg. Google Maps library

SQLite in Android applications

202

Methods for data storing

in Android203

Two basic ways to store data:

SQLite database - to store structured, self-managed

data

Content providers (content providers) - a generic

interface to use and share data

Data access

By default, data access is limited to the application

owning the data

Content providers provide a standard interface

enabling to share data with other applications

Access to database204

Access to the SQLite database requires access to the file system

Access may be slow - it is recommended to perform asynchronous operations on the database (eg. using AsyncTask class)

Applications create the database files in a directory:

where:

DATA - path returned by Environment.getDataDirectory() method

APP_NAME – aplikaction name

FILENAME – database name

DATA/data/APP_NAME/databases/FILENAMEDATA/data/APP_NAME/databases/FILENAME


android.database package includes all the basic classes needed to work with databases

android.database.sqlite package contains classes dedicated to SQLite database

To create and update a database the class extending the abstractclass android.database.sqlite.SQLiteOpenHelper is commonly used

Class constructor:

public SQLiteOpenHelper(

Context context,

String name,

SQLiteDatabase.CursorFactory factory,

int version)

name of database file

own cursors factory or null

database version


In helper class, one needs to define methods:

public abstract void onCreate (SQLiteDatabase db)

public abstract void onUpgrade (SQLiteDatabase db,

int oldVersion, int newVersion)

onCreate method is called, if a database exists

onUpgrade method is called, if database schema

changes

A good practice is to create separate classes for

each table


Helper class provides methods providing access to

the database in the read and read/write mode

public synchronized SQLiteDatabase

getReadableDatabase()

public synchronized SQLiteDatabase

getWritableDatabase()

Data operations208

android.database.sqlite.SQLiteDatabase class defines methods for SQLite database managing:

public long insert

(String table, String nullColumnHack, ContentValues values)

public int update

(String table, ContentValues values, String whereClause,

String[] whereArgs)

public int delete

(String table, String whereClause, String[] whereArgs)

public void execSQL(String sql)

Column names where NULL value

is inserted

Column names where NULL value

is inserted

The optional WHERE clause. The NULL value

modifies all rows

The optional WHERE clause. The NULL value

modifies all rows

Helper class209

public class DbHelper extends SQLiteOpenHelper {

private static final String TAG = DbHelper.class.getName();

public static final String TABLE_NAME = "comments";

public static final String COLUMN_ID = "_id";

public static final String COLUMN_COMMENT = "comment";

private static final String DATABASE_NAME = "commments.db";

private static final int DATABASE_VERSION = 1;

private static final String DATABASE_CREATE

= "create table " + TABLE_NAME

+ "( " + COLUMN_ID + " integer primary key autoincrement, "

+ COLUMN_COMMENT + " text not null);";

private static final String DATABASE_DROP

= "DROP TABLE IF EXISTS "

+ TABLE_NAME;

Helper class210

public class DbHelper extends SQLiteOpenHelper {

...........................

public DbHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override

public void onCreate(SQLiteDatabase database) {

database.execSQL(DATABASE_CREATE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

Log.w(TAG, "Upgrading database from version " + oldVersion + " to "

+ newVersion + ", which will destroy all old data");

db.execSQL(DATABASE_DROP);

onCreate(db);

}

}

Class of objects stored

in the database211

public class Comment {

private long id;

private String comment;

// ... setery/getery

@Override

public String toString() {

return comment;

}

}

Data operation212

public boolean createComment(Comment comment) {

boolean createSuccessful = false;

ContentValues values = new ContentValues();

// values.put(DbHelper. COLUMN_ID , comment.getId());

values.put(DbHelper. COLUMN_COMMENT , comment.getComment());

SQLiteDatabase db = this.getWritableDatabase();

createSuccessful = db.insert(DbHelper. TABLE_NAME , null, values) >0;

db.close();

return createSuccessful;

}

ContentValues213

ContentValues is Android SDK class designed

specifically to add new rows in the database

Inserting rows - Example no. 2214

public long insertCountry(Country _country) {

// Creating instance of new „row”

ContentValues newCountryValues = new ContentValues();

// Filling all fields of new row

newCountryValues.put(KEY_COUNTRY, _country.getCountryName());

newCountryValues.put(KEY_SHORTCUT, _country.getCountryShortcut());

// Insert the row into a database

return db.insert(DB_TABLE, null, newCountryValues);

}

Updating – Example no. 2215

public boolean updateCountry (long _index, Country _country) {

// Condition inserted into the WHERE clause

String where = KEY_ID + "=" + _index;

// In the same way as in insert statement

ContentValues updateCountryValues = new ContentValues();

updateCountryValues.put(KEY_COUNTRY,

_country.getCountryName());

updateCountryValues.put(KEY_SHORTCUT, _country.getCountryShortcut());

// Updating the raw in meeting the WHERE statement

return db.update (DB_TABLE, updateCountryValues, where, null) > 0;

}

Deleting – Example no. 2216

public boolean deleteCountry (long _index) {

String where = KEY_ID + "=" + _index;

return db.delete (DB_TABLE, where , null) > 0;

}

public void deleteAll () {

db.delete (DB_TABLE, null, null);

}

Query methods217

public Cursor query (String table, String[] columns, String selection,String[] selectionArgs, String groupBy, String having,

String orderBy, String limit)

public Cursor query (String table, String[] columns, String selection,String[] selectionArgs, String groupBy, String having,

String orderBy)

public Cursor query (boolean distinct, String table, String[] columns,String selection, String[] selectionArgs, String groupBy,

String having, String orderBy, String limit)

public Cursor rawQuery (String sql, String[] selectionArgs)

Jeśli nie ma potrzeby definiować poszczególnych warunków, należy wstawić null

Examples218

rawQuery()

SQLiteDatabase db= getReadableDatabase();

Cursor cursor = db.rawQuery("select * from Commentswhere _id = ?", new String[] { id });

query()

return database.query(DATABASE_TABLE,

new String[] { KEY_ROWID, KEY_CATEGORY, KEY_SUMMARY, KEY_DESCRIPTION }, null, null,

null, null, null);

Examples219

return database.query(DATABASE_TABLE,

new String[] { KEY_ROWID, KEY_CATEGORY,

KEY_SUMMARY, KEY_DESCRIPTION },

null, null, null, null, null);

Cursor cursor = getReadableDatabase().rawQuery

("select * from todo where _id = ?",

new String[] { id });

Cursor220

Cursor is a class, whose objects on Android represent results returned by the database query

It has several methods enable to navigate through returned rows:

moveToFirst () - moves the cursor to the first line of the returned result

moveToNext () - moves the cursor to the next row in of result

moveToPrevious () - moves the cursor to the previous row in of result

moveToPosition () - moves the cursor to the desired position

getPosition () - Returns the current position of a Cursor object

Cursor – additional methods221

Android provides also methods for cursor managing

startManagingCursor() – integrates the cursor lifecycle

with the lifecycle of activity to which it belongs. As a

result, if the activity is stopped, deactivate() method is

called automatically. If the activity is resumed -

requery() method is called

stopManagingCursor() – disintegrates lifecycle of the

cursor with lifecycle of the activity

Cursor – simple example222

public Cursor fetchNote(long rowId) throws SQLException {

Cursor mCursor =

mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY},

KEY_ROWID+ "=" + rowId, null, null, null, null, null);

if(mCursor != null) {

mCursor.moveToFirst();

}

return mCursor;

}

Cursor – example223

private void updateCountriesList() {

countriesCursor.requery();

countries.clear();

if(countriesCursor.moveToFirst()) {

do {

String country =

countriesCursor.getString(DatabaseAdapter.COUNTRY_COLUMN);

String shortcut =

countriesCursor.getString(DatabaseAdapter.SHORTCUT_COLUMN;

Country newCountry = new Country(country, shortcut);

countries.add(newCountry);

} while (countriesCursor.moveToNext());

}

}

Data displaying224

ListViews are views that enable to display a list of items

ListActivities are activities that make easier to use ListViews

To work with databases and ListViewsSimpleCursorAdapter can beused. It enable to define a view for each row of ListViews

SimpleCursorAdapter class maps the column to the views based on a cursor

Data source class 225

public class CommentsDataSource {

private final static String TAG = CommentsDataSource.class.getName();

private SQLiteDatabase db;

private DbHelper dbHelper;

private String[] allColumns = { COLUMN_ID, COLUMN_COMMENT };

public CommentsDataSource(Context context) {

dbHelper = new DbHelper(context);

}

public void open() throws SQLException {

db = dbHelper.getWritableDatabase();

}

public void close() {

dbHelper.close();

}

Data source class 226 public class CommentsDataSource {

...........................

public Comment createComment(String comment) {

ContentValues values = new ContentValues();

values.put(COLUMN_COMMENT, comment);

long id = db.insert(TABLE_NAME, null, values);

Cursor cursor = db.query(TABLE_NAME, allColumns,

COLUMN_ID + " = " + id, null, null, null, null);

cursor.moveToFirst();

Comment newComment = cursorToComment(cursor);

cursor.close();

Log.i(TAG, "Comment inserted with id: " + id);

return newComment;

}

public void deleteComment(Comment comment) {

long id = comment.getId();

Log.i(TAG, "Comment deleted with id: " + id);

db.delete(TABLE_NAME, COLUMN_ID + " = " + id, null);

}

Data source class 227 public class CommentsDataSource {

...........................

public List<Comment> getAllComments() {

List<Comment> comments = new ArrayList<Comment>();

Cursor cursor = db.query(TABLE_NAME, allColumns, null, null, null, null, null);


while (!cursor.isAfterLast()) {

Comment comment = cursorToComment(cursor);

comments.add(comment);

cursor.moveToNext();

}

cursor.close();

return comments;

}

private Comment cursorToComment(Cursor cursor) {

Comment comment = new Comment();

comment.setId(cursor.getLong(0));

comment.setComment(cursor.getString(1));

return comment;

}

}

Using of data source class 228

public class SQLiteDemoActivity extends ListActivity

{

private CommentsDataSource ds;

@Override

public void onCreate(Bundle savedInstanceState) {

................

ds = new CommentsDataSource(this);

ds.open();

List<Comment> values = ds.getAllComments();

................

}


public class SQLiteDemoActivity extends ListActivity {

public void onClick(View view) {Comment comment = null;

switch (view.getId()) {

case R.id.add:comment = ds.createComment("ABCD");

break;

case R.id.delete:if (getListAdapter().getCount() > 0) {

comment = (Comment) getListAdapter().getItem(0);

ds.deleteComment(comment);

adapter.remove(comment);

}

break;

} .............

}


public class SQLiteDemoActivity extends ListActivity{

@Override

protected void onResume() {

ds.open();

super.onResume();

}

@Override

protected void onPause() {

ds.close();

super.onPause();

}

DEMO

231

Example database

Chinook Database232

Example – class structure233

Database

SQLite transaction support234

public void bulkInsert(ArrayList<UserStatus> b) {

SQLiteDatabase db = this.getWritableDatabase();

db.beginTransaction();

for (UserStatus us : b) {

ContentValues cv = new ContentValues();

cv.put(COLUMN_USER_STATUS, bs.getStatus());

long rowId = db.insert(TABLE_USERS, null, cv);

us.set_id(rowId);

}

db.setTransactionSuccessful();

db.endTransaction();

db.close();

}

Content provider235

SQLite database is private for the application that

created it

To share data, content providers might be used

Content provider is usually used as data interface

to SQLite database

The content provider can be used by the

application, or applied to share data with other

applications

Access to content providers through URI

Content providers236

Content providers defined in AndroidManifest.xml:

<provider

android:authorities="de.vogella.android.todos.contentprovider"

android:name=".contentprovider.MyTodoContentProvider" >

</provider>

The content provider should support methods: query, insert,

update, delete, getType i onCreate

Content provider – security and

parallelism237

By default, ContentProvider is available for other

applications. To use it only in private, the definition

should contain

android: exported = false

Possible problems with parallelism, so access should

be implemented in thread-safe way

Content Provider - Example238

package de.vogella.android.contentprovider;

import android.app.Activity;

import android.database.Cursor;

import android.net.Uri;

import android.os.Bundle;

import android.provider.ContactsContract;

import android.widget.TextView;

public class ContactsActivity extends Activity {

/** Called when the activity is first created. */


@Override

public void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_contacts);

TextView contactView = (TextView) findViewById(R.id.contactview);

Cursor cursor = getContacts();

while (cursor.moveToNext()) {

String displayName = cursor.getString(cursor

.getColumnIndex(ContactsContract.Data.DISPLAY_NAME));

contactView.append("Name: ");

contactView.append(displayName);

contactView.append("\n");

}

}


private Cursor getContacts() {

// Run query

Uri uri = ContactsContract.Contacts.CONTENT_URI;

String[] projection = new String[] { ContactsContract.Contacts._ID,

ContactsContract.Contacts.DISPLAY_NAME };

String selection = ContactsContract.Contacts.IN_VISIBLE_GROUP + " = '"

+ ("1") + "'";

String[] selectionArgs = null;

String sortOrder = ContactsContract.Contacts.DISPLAY_NAME

+ " COLLATE LOCALIZED ASC ";

return managedQuery(uri, projection, selection, selectionArgs, sortOrder);

// return new CursorLoader(context, uri, projection, selection, selectionArgs,

sortOrder);

}

Loader –

preferred data access241

Available starting with Android 3.0

Asynchronous data loading mechanism

It can be used inside Activity

Loader follows established data source and automatically

loads data in case of change

It enable to monitor data changes

It provides automatic connection to the last loader's cursor,

eg. after phone orientation change

Another way to access a cursor - replacement methods

startManagingCursor() or managedQuery()

Classes/interfaces

to support loader242

LoaderManager - abstract class for managing of

Loader object instances

LoaderManager.LoaderCallbacks - callback interface

to user-LoaderManager interaction

Loader - abstract class for data reading

AsyncTaskLoader - inherits from Loader, asynchronous

reading

CursorLoader - inherits from AsyncTaskLoader, for

practical use

LoaderManager243

Managing of Loaders

Automatic memory freeing

Automatic data restoring after configuration changes (eg. orientation change)

No need to manually create LoaderMaganer instance

It is enough to call getLoaderManager () for specific activity

There is only one instance of this object for each activity or fragment

Methods:

initLoader () - add loader to LoaderManager

restartLoader () - re-initialize the loader

getLoaderManager().initLoader(LIST_ID, null, this);getLoaderManager().initLoader(LIST_ID, null, this);

getLoaderManager().restartLoader(LIST_ID, null, this);getLoaderManager().restartLoader(LIST_ID, null, this);

LoaderManager.LoaderCallbacks

244

It contains methods to create a loader and to use itsresults

Methods

onCreateLoader() - creation of a new loader for specified ID and parameters

onLoadFinished() – called if previously created Loader finished loading data

onLoadReset() - called if previously created Loader has been reset, and data are not available

public class YourFragment extends Fragment

implements LoaderCallbacks<Cursor> {

//... }

public class YourFragment extends Fragment


//... }

Example of onCreateLoader()245

public Loader<Cursor> onCreateLoader(int id, Bundle args) {

CursorLoader loader = new CursorLoader(

this.getActivity(),

SOME_CONTENT_URI,

projection,

selection,

selectionArgs,

sortOrder);

return loader;

}

concrete example of

onCreateLoader()246


String rawQuery = "SELECT ...";

String[] queryParams = // to substitute placeholders

SQLiteCursorLoader loader = new SQLiteCursorLoader(

getActivity().getApplicationContext(),

yourSqliteOpenHelper,

rawQuery,

queryParams);

return loader;

}

Example of onLoadFinished()247

public void onLoadFinished(Loader<Cursor> loader, Cursor cursor) {

if (cursor != null && cursor.getCount() > 0) {


int idIndex = cursor.getColumnIndex(LentItems._ID);

int nameIndex = cursor.getColumnIndex(LentItems.NAME);

int borrowerIndex =

cursor.getColumnIndex(LentItems.BORROWER);

this.itemId = cursor.getLong(idIndex);

String name = cursor.getString(nameIndex);

String borrower = cursor.getString(borrowerIndex);

((EditText)findViewById(R.id.name)).setText(name);

((EditText)findViewById(R.id.person)).setText(borrower);

}

}

Implementation of

LoaderCallbacks250

The method returns cursor with data to the content provider

public class CursorLoaderExampleActivity extends Activity



String[] projection = { DatabaseSQLiteHelper.COLUMN_ID,

DatabaseSQLiteHelper.COLUMN_BATTERY_LEVEL,

DatabaseSQLiteHelper.COLUMN_BATTERY_STATE_DATE };

CursorLoader cursorLoader = new CursorLoader (this,

BatteryContentProvider.CONTENT_URI, projection, null, null, null);

return cursorLoader;

}

Implementation of

LoaderCallbacks251

Method onLoadFinished refreshes data

public void onLoadFinished(Loader<Cursor> loader, Cursor data) {

if (data == null) return;

ArrayList<String> newlist = new ArrayList<String>();

data.moveToFirst();

while (data.moveToNext()) {

// wypelnienie obiektu newlist

}

lists = newlist;

refreshTextView();

}

public void onLoaderReset(Loader<Cursor> arg0) {

tv.setText("");

}

Where to use Loaders?252

Where you use Loaders:

Access to data via the Internet

Access to the database directly, without Content Provider

Advantages and disadvantages

Simplify the code (+/-)

Possibility of parallel access to other data (Internet, etc.) (+)

Limited opportunity to listen for data changes (-)

maŁgorzata plechawska-wÓjcik mobile databases › wp-content › uploads › 2016 › 06 ›...

Documents