Stroker: create, write, and read an
SQLite
Database
Draw one or more strokes with your finger.
Then pull down the menu and raise or lower the most recent stroke,
or delete it,
or delete all of them.
I lowered the mouth.
The
max(stroke)
in the
StrokeView
constructor returns
NULL
if the table contains no rows.
If I was updating a single row for
“raise”
and
“lower”, I would have called
update.
But I was updating many rows.
MainActivity.java.
I didn’t change
onCreate,
but I added four
if
statements to
onOptionsItemSelected.
-
StrokeView.java
defines a subclass of
View.
-
Helper.java
defines a subclass of
SQLiteOpenHelper.
-
activity_main.xml.
The
RelativeLayout
contains a
StrokeView.
-
menu_main.xml
is the
options
menu
displayed by the
Activity
when you tap the three white dots in the
action bar.
-
strings.xml
AndroidManifest.xml
-
build.gradle
(Module: app)
Create the project
Select the folder
app/java/edu.nyu.scps.stroker
in the Android Studio
project
view.
Pull down
File → New → Java Class
Create New Class
Name: Helper
Kind: Class
OK
Edit your new file
Helper.java
and insert the words
extends SQLiteOpenHelper.
Click on the word
Helper
and pull down
Code → Implement Methods…
OK
Also create
StrokeView.java.
adb: the Android debug bridge
Open a Terminal window on your Mac, or a
cmd.exe
Command Prompt window in Microsoft Windows.
ca1784a34445a8d0308
is my
Azpen
A727 tablet.
0088080744830RTG
is my Amazon Fire HD 6 tablet.
192.168.57.101:5555
is the Genymotion Samsung Galaxy S5 emulator.
emulator-5554
is the
Android emulator
Nexus 5 emulator.
My table contains three strokes, each going from left to right.
The examples with
min(x)
print the leftmost point in each stroke.
The examples with
min(_id)
print the (chronologically) first point in each stroke.
adb devices
List of devices attached
ca1784a34445a8d0308 device
0088080744830RTG device
192.168.57.101:5555 device
emulator-5554 device
adb devices -l
List of devices attached
ca1784a34445a8d0308 device usb:340787200X product:polaris_inet model:A727 device:polaris-inet
0088080744830RTG device usb:336592896X product:full_ariel model:KFARWI device:ariel
192.168.57.101:5555 device product:vbox86p model:Samsung_Galaxy_S5___4_4_4___API_19___1080x1920 device:vbox86p
emulator-5554 device product:sdk_phone_x86 model:Android_SDK_built_for_x86 device:generic_x86
adb -s 192.168.57.101:5555 shell
root@vbox86p:/ # pwd
root@vbox86p:/ # ls (lowercase LS for "list")
root@vbox86p:/ # ls -l (minus lowercase L)
root@vbox86p:/ # cd /data/data/edu.nyu.scps.stroker/databases
root@vbox86p:/data/data/edu.nyu.scps.stroker/databases # pwd
/data/data/edu.nyu.scps.stroker/databases
root@vbox86p:/data/data/edu.nyu.scps.stroker/databases # ls -l strokes.db
-rw-rw---- u0_a68 u0_a68 20480 2015-07-29 17:22 strokes.db
root@vbox86p:/data/data/edu.nyu.scps.stroker/databases # od -c strokes.db | head -1
0000000 S Q L i t e f o r m a t 3 \0
root@vbox86p:/data/data/edu.nyu.scps.stroker/databases # sqlite3 strokes.db
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help
sqlite> .tables
android_metadata points
sqlite> .dump android_metadata
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE android_metadata (locale TEXT);
INSERT INTO "android_metadata" VALUES('en_US');
COMMIT;
sqlite> .schema points
CREATE TABLE points (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
stroke INTEGER,
x REAL,
y REAL
);
sqlite> .headers on
sqlite> .mode column
sqlite> .show
sqlite> select * from points limit 5;
_id stroke x y
---------- ---------- ---------------- ---------------
1 0 263.755798339844 903.40283203125
2 0 269.454895019531 903.40283203125
3 0 287.804748535156 903.40283203125
4 0 328.031280517578 909.19165039062
5 0 370.656799316406 911.89855957031
sqlite> select max(stroke) from points;
max(stroke)
-----------
2
sqlite> select _id, stroke, min(x), y from points group by stroke;
_id stroke min(x) y
---------- ---------- ---------------- ---------------
1 0 263.755798339844 903.40283203125
19 1 362.664215087891 1056.3232421875
35 2 380.647552490234 1197.2498779296
sqlite> select _id, stroke, min(x) as x, y from points group by stroke;
_id stroke x y
---------- ---------- ---------------- ---------------
1 0 263.755798339844 903.40283203125
19 1 362.664215087891 1056.3232421875
35 2 380.647552490234 1197.2498779296
sqlite> select min(_id), stroke, x, y from points group by stroke;
sqlite> select stroke, x, y from (select min(_id), stroke, x, y from points group by stroke);
sqlite> .exit
root@vbox86p:/data/data/edu.nyu.scps.stroker/databases # exit
Pull a file from the emulator to your Mac or PC
cd ~/Desktop Go to your Desktop on your mac or PC.
pwd Make sure you arrived there.
adb -s 192.168.57.101:5555 pull /data/data/edu.nyu.scps.stroker/databases/strokes.db strokes.db
1294 KB/s (20480 bytes in 0.015s)
ls -l strokes.db
-rw-r--r-- 1 myname mygroup 20480 Jul 29 19:11 strokes.db
sqlite3 strokes.db
sqlite> .headers on
sqlite> .mode column
sqlite> select * from points limit 5;
sqlite> .exit
sqlite3 strokes.db '.headers on' '.mode column' 'select * from points limit 5;'
sqlite3 strokes.db '.headers on' '.mode column' 'select * from points limit 5;' > select.txt
ls -l select.txt
Things to try
-
Suppose you have
pulled
strokes.db
to your Mac or PC and modified it there with
sqlite3.
Can you
push
the file from your computer back to the correct directory in your emulator or device?
- Display a
Toast
in the constructor and
onCreate
method of the
Helper.
(To create the
Toast
in
onCreate,
you will have to give the
Helper
a field that holds the
Context.)
If the database file
/data/data/edu.nyu.scps.stroker/databases/strokes.db
does not already exist,
verify that both methods are called when the app is launched.
If the database file has already been created by a previous run of the app,
verify that only the constructor is called.
Before running the app,
you can use the following commands to remove the database file
and the directories that contain it.
The
-ld
option of
ls
lists the directory itself,
not the files and subdirectories in the directory.
The
-fR
option of
rm
removes the directory and all of its contents,
including subdirectories, sub-subdirectories, etc.
adb -s 192.168.57.101:5555 shell
root@vbox86p:/ # cd /data/data
root@vbox86p:/data/data # ls -ld edu.nyu.scps.stroker
drwxr-x--x u0_a53 u0_a53 2014-05-24 15:55 edu.nyu.scps.sqlite
root@vbox86p:/data/data # rm -fR edu.nyu.scps.stroker
root@vbox86p:/data/data # ls -ld edu.nyu.scps.stroker
edu.nyu.scps.sqlite: no such file or directory
root@vbox86p:/data/data # exit
adb -s 192.168.57.101:5555 shell rm -fR /data/data/edu.nyu.scps.stroker
- When the orientation of the device changes
(e.g., from portrait to landscape),
verify that the
MainActivity
and
Helper
are destroyed and re-created,
but that the
onCreate
method of the
Helper
is not called.
-
Draw a circle at the start of each stroke.
Remove the word
Cursor
from the statement that creates the
Cursor
in the
for
loop in
onDraw.
Then insert the following code immediately before this statement.
Cursor cursor = db.query(
TABLE_NAME,
new String[] {"min(_id"), "x", "y"},
"stroke = ?",
new String[] {String.valueOf(i)},
"stroke", //group by
null, //having
null //order by
);
if (cursor.moveToFirst()) {
int xIndex = cursor.getColumnIndex("x");
int yIndex = cursor.getColumnIndex("y");
canvas.drawCircle(cursor.getFloat(xIndex), cursor.getFloat(yIndex), 20, paint);
}
cursor.close();
Or you could draw a circle at the leftmost point along each stroke.
Or the topmost.
-
Can you hide or disable the appropriate menu entries
when the table is empty?