Lars Schou Synopsis 2011

bawltherapistΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 6 μήνες)

60 εμφανίσεις

Lars Schou



Synopsis 2011

Android SQLite














All right everyone, line up alphabetically according to your height.








Lars Schou



Synopsis 2011

Android SQLite


2

Table of contents


1.

Introduction

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

3

2.

Problem Definition

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

4

3.

Methods for this synopsis

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

5

4.

Background Knowledge

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

6

5.

Problem Solving

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

7

5.1

Setting up the Database

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

7

5.2

Use the SQLite database (Insert, Select, Join, etc.)

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

8

5.3

Performance (SQLite vs. Files)

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

9

5.4

Going Online (From SQLite to mySQL)

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

10

6.

Evaluation

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

12

7.

Literature List

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

13





Lars Schou



Synopsis 2011

Android SQLite


3

1.

Introduction



One of the main things about being a programmer is to investigate new areas that may be completely new.
A way of doing this is to start with an idea, that not necessary is a huge revolutionary program that will
make you rich.
In this synopsis I will dig into the Android
Database
world


a world that a
s

a
Java
programmer I
THINK I
should be pretty comfortable
, because of the
syntax

I both Android and

Java

is alike
.

But is it also when we are working with the database environment?

The Android Database


SQLite makes me think of the basis of a database. What is a database really? Well
we all know that we can st
ore data, but

it’s

also how we are

getting back the da
ta! How are we going to
present the data that we have got back, and who should be able to see it
, and how fast can we get it
?

In these days we can read in all kinds of newspapers that
,

smartphones

has a

huge security problem.

People don’t have antivirus


people forget that a smartphone is actually a computer!

So how should we as programmer secure the data?
Is there really a security problem in SQLite?

So if we can’t put some data


then what can we put
into the database? Why is it even there in the first
place, if we

could just use files or

the server we have in our basement with MS
-
SQL or MySQL
.



Maybe Google (the founder of android) already has answered some of these questions, and solved some
of the
problems for us


who knows?!

THIS is
what

that I
want

to investigate!

So
I’
ll

dig into the SQLite world and see what it wants and what it can offer to us. Let’s put some words on
the ugly questions and try to make some pretty answers out of them.





Lars Schou



Synopsis 2011

Android SQLite


4

2.

Problem Definition


To investigate the
database world I’ve started with giving myself some questions
that I think would be relevant and realistic to answer

within
the timeframe
.
I have
an idea of
an
Application
I want to build; An A
ndroid game called



Dino’s Cake Quiz


Part of Dino world

.


I will not go deeply into the game in this synopsis, since that is not relevant.

I don’t even think I will make it possible to start the actual game.

It’s more relevant to focus on the things ar
ound the game
-

users and high score lists and their relationship.


If

I look back at the introduction I
have

some
inspiration to some questions that

will lead me through the
synopsis ‘problems’
.


This
is the questions I’ve come up
with
:

1)

What is the

design of the SQLite database?

One of the things I noticed before I started this project is that, the SQLite database does not
have any user interface for controlling the DB.

Everything is controlled in the API.

A quick look in the SQLite omitted definit
ion
1

tells me

that methods like
-

JOIN, ALTER, TRIGGER
and VIEW
are
stripped down.


2)

Do
I need to use
the SQLite database
? Could i

use fi
les instead? Or something else?


First of all I know that the
methods built into SQLite are

enough for me, since I have no plan on
making extremely advanced
queries
.

But it’s still interesting that maybe something could be saved as files, such as
the high score list

which has no security issues, and then just
read the file. Is there a performanc
e difference?






3)

How do I transfer data through different DBMS?


So when it’s up and running
and
people are playing the

game, i
t would be nice that people ha
ve

a

user account and they ha
ve

a chance to see a ‘global’ high score.

I could also be nice to
impleme
nt IN
-
app payment with
a
credit card.

These data should then be sent to a server somewhere, but which one? MySQL, MS
-
SQL?

I’ll try to find out how to connect to an online database for global storage.






1

Source : SQLite.org/omitted.html

Lars Schou



Synopsis 2011

Android SQLite


5

3.

Methods for this
synopsis


Since the Android world is t
otally new for me I will use a lot of time reading books about the basis of
android and the SQLite database. I’ve also found a very good android developer on YouTube who’s
uploading lots of nice tutorials, which I of course are going to see. SQLite is not
ONLY for
android,

which means that
there are

several
devices that
use

SQLite as storage.

This is a benefit since there’s lots of people put there ready for helping.

The software I’m going to use is ‘
Eclipse Helios IDE


For Java Developers


itself can’t

do
any Android compiling, but a bundle
-
plugin is available free for download with both
the Android SDK and an Android AVD (virtual machine

running on windows
).


The reason I chose Eclipse is that I’ve red in lots of forums, and intended to some seminars
which all used
eclipse and it should be the be
s
t for Android development.

The eclipse IDE also ha
ve

a very nice feature for
debugging,

called Logcat which is overviewing every activity that’s running
from the

application in the AVD.
This makes it very easy
to see where
a thing goes

wrong.

As hardware I use my laptop for the development, and for running the ‘real’
application is use the AVD(virtual machine) and my phone ;

Samsung Galaxy SII


which runs Android
2.3.6 Gingerbread


The newest
A
ndroid I the

2
-
series.

The SII
has a dual core 1.2G
H
z processor which makes it extremely fast for a phone


actually the fastest on the market right now.
I think it’s worth taking notice that the
Android API I will develop in is Android

2.3

and NOT
A
ndroid 2.3.6 whic
h my phone
is running. The reason I choose to develop in a
n

API
-
version that is ‘old’ is that most
of the devices which is
runnin
g
A
ndroid is running Android 2.3
.

For online storage of data I will find out what is best and easiest
way
for
doing
that. At th
is point I do not
have a clue since I already now know that just calling and using an online database is NOT something that is

just out the box
.
I found out that adding
MySQL

drivers to an android environment
is impossible, s
o I’ll have
to find another way

of sending data from my SQLite database to whatever
online
database I’m going to use.

Since Android is a very attractive topic I’ve experienced that to loan a book from a
Danish library is almost impossible within the short period of time we have to make

this synopsis. Lucky for me one of my teachers has thought about making the android
topic into an elective for the next 4
th

semesters,
so therefore I have loaned some
books from him. Finding book about SQLite on Danish libraries is also impossible, but
si
nce the topic
of both

Android and SQLite is something that caches my interest I’ve
decided to buy some myself.

The main websites I’m going to use is:

http://SQLite.org




All

definitions of the SQLite database.

http://youtube.com/users/thenewboston



Very good android development tutorials incl. SQLite.

http://developer.android.com/



All the android specifications a
nd the Android API


Lars Schou



Synopsis 2011

Android SQLite


6

4.

Background Knowledge


My motivation for doing this synopsis is very clear. I definitely want to learn about the android
environment, because of the tendency of everything developed should also be available on a smartphone.

I’m sure th
at I’m going to need as much knowledge, as possible of developing to the android environment,
when I’m hopefully am going to work at a company as a developer. Besides I’m fascinated by the way
Android makes it possible for everyone to make exactly the Appl
ication he/she want’s by the complete
open source
Android API. If we want to make a GPS receiver, the Android API lets us call the GPS Service in
the phone, and then do whatever we want with that information. Everything is available in the phone incl.
a da
tabase to use

which is very nice
.




In this semester we have had
the
electives
:

Web programming


and

Database and XML

.

This synopsis is about SQLite, but I will use some time getting some knowledge about the android
graphical
user face, because
it’s necessary
for me to know how to build up

some presentation of the data.


The android environment is built on JAVA,
and

the android GUI is built on android views

that are described
in XML
, which is very different
from what we are used to with
frames and panels in the
JAVA

environment.


At the exam I will use, both
code examples and examples
that are running on my phone and/or the android emulator running on my laptop.

Because of my knowle
dge from the 4
th

semester about
SQL (
triggers, views, constraints etc.)
,

I think I
should be pretty

straight

forward
to

understand and us
e

SQLite.

One of the things that grab my

notice
is
that SQLite does not have any user interface, which can make it a
bit diffic
ult to remember all references, constraints, triggers, views etc
. This forces me to draw database
diagrams, which in the end always is nice to have.

Another thing is that SQLite does NOT guarantee
integrity constraints such as a primary key that reference
s to something that is not there!
2

It
’s

there, c
an be
used, but is not guaranteed. This makes the programmer
responsible for make sure in other ways that
‘ugly’ data not is in the database. The JAVA environment helps us a little, because if we try to put a

null into
a table


it will throw a
null pointer

exception

while
‘B
ind
ing’

the
SQL Statement

at runtime
. B
ut if we
put a
column that references another table on a key, and then afterwards delete the referenced column


then
we have no errors raised. A way

of getting around of this
is

to make triggers that cascade the operation.
I’ll
take a closer look on that in the p
roblem solving.







2

(Wikipedia/SQLite)

Lars Schou



Synopsis 2011

Android SQLite


7

5.

Problem Solving


To solve the problems in my problem definition I’m setting up some cases that will come around the
questi
ons. To make it as realistic as possible I have chosen to setup a ‘real’

problem to be solved.

I want a quiz game. A quiz game, where it should be possible to have a personal user account, and a high
score list


both should be able to reach online and off
line (the global high score list can only be updated in
online mode).
If the user logged in is online
-

scores should be saved in an online database, and if the user is
offline the scores should be saved offline (in the phones SQLite db
) and sent whenever the user goes
online.
So let’s get started.

5.1

Setting up the Database


To setup a SQLite database we need to
define: a databa
se name, and a
database version.

I’ll call my database “dinoquiz.db” and
give it the version 1. So whenever I
change something in the database
structure I’ll have to increase the
database version, to actually make the
effect.

The Android API has a helper
c
lass which is called SQLiteOpenHelper
which

need to extend

in order to
manage the database with the version.
It’s done simply by making a class that extends SQLiteOpenHelper, and then just override

the methods
onCreate, onUpdate and optionally onOpen.

Sinc
e this is not a game that is going to production I’ll make it
‘drop and create’ the database onUpdate.

At last I call getWriteableDatabase
()

from the SQLiteOpenHelper
which will create the database if it is not already there or return the existing databas
e from the device.
To
make it simple I’ve create two tables


one called ‘dino_users’ and one called ‘dino_results’. See the
picture

below.

The admin attribute is for later use, which is not relevant for now.




Figure
1



Creation of the database, with override the methods from SQLiteOpenHelper

Lars Schou



Synopsis 2011

Android SQLite


8

5.2

Use the SQLite database (Insert, Select
, J
oin
,
etc.)


Now the database is in the device. I can now start putting data into it. Let’s try it out.

I’ve
defined a
class
User

and

Highscore
,
written

a method to insert a new
high

score

to the database
.

Now something interesting happens, because what happens if I try to put a new high score in, that refers to
a userID, which does not, exists?


NOTHING! That not good, because then I could end up with a database
filled with data that refers to null which
will give some problems when I later on will make views to see the
top 50 (guess how ugly that’s going to be).
H
ow to
get

around with this?

Well if we take a deep look in the
SQLite documentation
3

is says that foreign key constraints is disabled by defaul
t

(for backwards
compatibility)
, but there’
s a way to enable it and use it.

This is how it’s done:

PRAGMA
foreign_keys =
ON
;


The PRAGMA

does not work neither. It seems to me like there’s no way to actually be sure that, constraints
works

in SQLite


But that’s

also what they say themselves.

So I’ll leave this for now, and make the practical part of this after I’ve handed this synopsis in. I think I’m
going to make a check constraint myself, by making a
procedure that will check if the id, the inserted high
sco
re refers to actually is present. If it’s not it will raise an error, else it should just proceed. I’m looking
forward to see how many problems I will run into then, while trying to make procedures, if
-
else, and raise
error.

Okay so let’s try to select s
omething that IS present with a simple join


I want a top50 list of the
result_date, time and points from my personal high score. Here’s my SQL
-
statement I’ve come up to:

SELECT
result_date, time, points

FROM
dino_results

JOIN
dino_users
ON
user_id = us
erid

WHERE
email = "Lars.sc
@gmail.com
"

ORDER BY
points
DESC

LIMIT
50
;




IT

works!


I got a list back just as I expected.

This
tells me
foreign keys actually work, but their constraints and references are not guaranteed to be
checked.








3

Source : SQLite.org/

foreignkeys
.html

Lars Schou



Synopsis 2011

Android SQLite


9

5.3

Performance
(
SQLite

vs
. Files)


Okay, the header might sound weird since every database actually IS files, but they have a lot of structure
,
methods and call
s

‘behind the scenes’ that maybe could take some of the performance. If it turns out that
r/w a top
50 list to a file is faster than r/w to the SQLite, then it could be an idea to only load the top50 once
from
an

online database, and
then write it to the file.

For performance testing I’ll use the System.currentTimeMillis, from the Java API.

I’ll put all
the results into an ArrayList which will be the base of the test.

Below is a scheme with the results.


Top 50

SQLite

TXT
-
File

Read

34

ms

9

ms

Write

524

ms

25

ms


Table
1



50 high scores,
Based on the average of 10 tests.

This is

choking news. To write 50 high scores took 524ms?

I decide to scale it up to 100, and 500

scores to
make it comparable. Maybe SQLite will be much faster on a larger scale. Let’s try it out.

Same approach as before:

Top 100

SQLite

TXT
-
File

Read

54

ms

10

ms

Write

1133

ms

51

ms

Table
2

-

100 high scores, Based on the average of 10 tests.



Top 500

SQLite

TXT
-
File

Read

271

ms

146

ms

Write

6471

ms

279

ms

Table
3

-

500 high scores, Based on the average of 10 tests.


Okay, I don’t think there’s anything to be in doubt of: SQLite is extremely slow in read/write.

The reason I only scaled it up to 100, 500 is that the SQLite db crashes if I go further.



This does
not make me
,

not use the SQLite database at all


there are still reasons to use it.

For example
data that should not be shared to other applications should stay in the SQLite db.


SQLite databases in an Android application are ONLY accessible from the ap
plication that created it
4
.




4

Source :
developer.android.com/guide/topics/security/security.html

Lars Schou



Synopsis 2011

Android SQLite


10

5.4

Going Online (From SQLite to mySQL)


When working on a game for a smartphone, I’ve of course thought about going online with my data.

Think about it


How funny is it to make a game with points and high scores, if it is only you
r own scores
you can reach?

As mentioned, I know that compiling a MySQL library into the android environment is impossible
, but
there’s actually several other ways to send and receive the data
. It can be done with plain http messages to
any kind of server

side language
, which then takes care of the data
. To make it simple I have only taking the
solution I’ve chosen into this synopsis
.

I’ve chosen to use a MySQL database and access it through PHP.

The reason I chose PHP, is that I’ve worked with PHP before,

so to make sure that I do not use too much
time ‘learning’ a new language, I’ve chosen one I’m a little familiar with.

Let’s have a look how I’ve done it.

After creating the MySQL database on the
server
,
with the relevant tables I
create two

new PHP
-
file
s

on my
website

called


insert
Highscore
.php

and
getTop50Highscore.php


I use PHP to get the POST from the HTTP
request
to

store the new result
into

the
database.

Since PHP is a server side language, I don’t
need to worry about the username, and
password to the database, because it never
leaves the server (I
’ve

blurred the password out

here
)
.

In order to send the request from

the Android environment

I need to build up the HTTP
-
POST request.

This is done in plain JAVA, which is pretty simple.
I just need to build up value pairs as parameters to send
the request.

It might seem awkward that there’s no automated way of doing this,

but that just the way it is
for now, in the android environment.


This is how I’ve done it:


Figure
3



Insert a new high score from Android to insertHighscore.php


with HttpPost


Figure
2



i湳nr瑈tg桳h潲e⹰桰

†††††††††
Se瑴tng⁵p⁴ e⁰hp⁴ ⁩n
獥r琠愠te眠桩gh獣ore⁴ ⁴ e⁍ySQL

Lars Schou



Synopsis 2011

Android SQLite


11

The above example was quite simple, so let’s try something that’s a little more complex.

Now I want to get the top 50 list from the database into my Android environment
saved in an
JSONArray

(afterwards I can do whatever I want with it right?).

To make PHP return a list that I can actually use in
JAVA, I can use JSON, which is smaller, faster and
easier to use than XML
5
.


PHP has a method called json_encode, which can
take any kind of objects in parameters.

In this example I put all the results (the actually
top 50) from my SQL
-
query into an array and
return it as a json object.

This can be a little tricky because when I want to
load it into the Java environment, I hav
e to read
every line of the response anyway


and then put it into a List of a kind.

Here’
s a

snippet

on my read on the http response from
my Android environment

:


Figur
5

Read from the HTTP Response


Return a JSONArray

with the results (irrelevant code is dotted out)

Take notice that, I first read the complete response and then afterwards put in into a JSONArray.

The JSONArray now has the result as elements that can be accessed by their key value, ‘username’, ‘time’
and

‘points’


on each row of the array. This is a complete other way of holding and getting back elements
in an array than I’m used to from JAVA, but it actually seems to be nice and easy way for me.

Now you might think that I will put
the top 50 list

into t
he SQLite database now, but that’s not the case.

I actually want to save it as a TXT file, because of the performance which I tested earlier.

My idea at this point is that at the exam I want to show how to check if the phone is online, because if the
phone

is not online, the data should be saved offline (some in TXT, and some in the SQLite db) and then
when the phone is online,
some, but not all (login info) of the data should be merged.


I’m looking forward to present what I’ve got to the exam.





5

Source :
w3schools.com/json/

Figure
4



gettop50hig
hscore.php


Return an Object filled with the result from the query.

Lars Schou



Synopsis 2011

Android SQLite


12

6.

Evaluation


T
o summarize up the synopsis on what
I’
ve reached so far.

I

ve found out that SQLite

s design is not far from what
I

know from other DBMS like MySQL or MSSQL
,

though it is quite lightweight
.
Here
I

think o
f

the

syntax and the way the data is stored and reached again.

I

ve foun
d out so far that primary keys
and
their relationships
are

not checked by their
relational

constraints
.


W
hile you are reading this,
I’
m working on a way to come around with this problem.
This is
because

it is some of the things
I

want to present

to the exam.

I

ve also tried to use files instead of the SQLite, which turned out to be much faster in reading and writing
.

T
hi
s gave me
the idea that, some data could and maybe should be stored in files
instead

of the SQLite
database, for performance optimization.


At the exam
I

want to show h
ow to make the device choose
if
it
should look in the SQLite, Files or my SQLite database for the data.

At last it connected to my MySQL database from
within the Android environment.

This was not as easy as
I’
m used to
from java applicatio
ns, where
an import of a library almost does

all the
work.

So
I

did that

with PHP

and JSON

instead
.

T
his has put a new d
imension on the SQLite database, because
now data is also able to stored an
d loaded in a global database.


At the examination
I

make a brief walkthrough
on the project

to make an overview of the
SQLite, the files,
and the MySQL
.
Then
I

present what
I’
ve come up to with relational constraints.
And at last
I

show how to
work both offline and online and make decisions on how and where data is stored and reached.




Lars Schou



Synopsis 2011

Android SQLite


13

7.

Literatur
e

List


Bucky. (n.d.). http://www.youtube.com/user/thenewboston.

Google Android API, G. (n.d.). http://developer.android.com/reference/packages.html.

Meier, R. (2010).
Professional Android 2 Application Development.

John Wiley and Sons Ltd.

Owens, M. (2010).
The Definitive Guide to SQLite.

APress.

Schools, W. (n.d.). http://www.w3schools.com.

SQLite.org, C. (n.d.). SQLite.org.

Wikipedia/SQLite. (n.d.). http://en.wikipedia.org/wiki/SQLite.