pgtop for android: things i learned making this app

PGTop for AndroidThings I learned making this app

Mark [email protected]

Port1and, Oregon, PostgreSQL Users Group

20 January 2011

PGTop for Android

A PostgresSQL stats monitoring appfor Android:http://github.com/markwkm/PGTop

markwkm (PDXPUG) PGTop for Android 20 January 2011 2 / 37

http://github.com/markwkm/PGTop

How did I get started?

Once upon a time I got developer edition G1 from Google...


And it sat idle...

• Postgres JDBC driver for Android platform didn’t work

• Native C development kit too hard for me to just build just the libpqlibrary from the postgres source


And then some motivation...

__ __

/ \~~~/ \ . o O ( PostgreSQL contest! )

,----( oo )

/ \__ __/

/| (\ |(

^ \ /___\ /\ |

|__| |__|-"


\ /

\ /

_.-""""-._

.’ ‘.

/ O O \ . o O ( Free Nexus One! )

__ +----------------+ __

/ \+----------------+/ \

| || || |

| || || |

| || || |

| || || |

\__/| |\__/

\__ __ __/

| | | |

| | | |

\__/ \__/markwkm (PDXPUG) PGTop for Android 20 January 2011 6 / 37

New developments

Dave Cramer builds a version of the postgres JDBC jar that works on Android:http://groups.google.com/group/pgandroid/attach/4ee44940c9f7319f/postgresql.jar?part=2


http://groups.google.com/group/pgandroid/attach/4ee44940c9f7319f/postgresql.jar?part=2

Slapped together and entered PGTop for Android:


Not a winner. :(


Oh well, continue to make a few improvement...


What did I learn along the way?


How to set up a development environment

• Requires 32-bit java development environment• Requires Android sdk

• Android emulator

• Android jars

• Android packaging tools

• Optional to have Eclipse• ui designer gui

• hides xml

• Optional to have Android ndk for developing native C library


How to make a launcher icon

I need to learn how to use Inkscape or Gimp better to make the ascii art iconmeet the recommended guidelines better:


About layouts

Only used scroll view and linear layouts.


About ui widgets

• TextViews (labels)

• EditViews (input text)

• Spinners (selection)

• Buttons

• custom menus


How the threading model works

• child threads don’t have access to ui objects

• use android.os.Handler to pass messages to parent thread


How PGTop uses threads

Create a Handler:

private Handler handler = new Handler() {

@Override

public void handleMessage(Message msg) {

// Update UI objects...

}

}

Child thread loops continuously to query PostgreSQL database for stats andupdates class variables with data.Pass a message to a handler to call the method that updates the UI:

handler.sendEmptyMessage(0);


How to use SQLite3 interface

To store database connection information (oversimplified sample):

public class PGConnectionOpenHelper extends SQLiteOpenHelper

SQLiteDatabase db = openHelper.getReadableDatabase();

Cursor c = db.rawQuery(SELECT_CONNECTIONS, null);

while (c.moveToNext()) {

c.getString(c.getColumnIndex("database"));

}


How to read the stdout/stderr log

The debugger was too hard for me.


__ __

/ \~~~/ \ . o O ( Code review time! )

,----( oo )

/ \__ __/

/| (\ |(

^ \ /___\ /\ |

|__| |__|-"


PostgreSQL JDBC

Now for some simple examples for connecting to a PostgreSQL database andquerying some data. Warning, code formatted to fit better on these slides. . .


Open a Database Connection

Load the PostgreSQL JDBC driver and open a database connection using SSL:

Class.forName("org.postgresql.Driver");

String url;

url = "jdbc:postgresql://pghost:5432/pgdatabase" +

"?sslfactory=org.postgresql.ssl.NonValidatingFactory" +

"&ssl=true";

Connection conn = DriverManager.getConnection(url,

"pguser",

"pgpass");

\\ Don’t forget to close the connection when you’re done.

\\ conn.close();


Execute a Query

Building on the previous slide, select the name of all relations from pg class

and iterate through every row returned:

String sql;

sql = "SELECT relname FROM pg_class WHERE relkind = ’r’;"

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(sql);

while (rs.next()) {

\\ Columns are enumerated starting with 1.

String relname = rs.getString(1);

}

rs.close();

st.close();


Execute a Query with a Bind Value

Building on the previous slide, select the number of all relations from pg class:

String sql = "SELECT COUNT(*) FROM pg_class WHERE relkind = ?;"

PreparedStatement ps = conn.createStatement();

// Bind variables are enumerated starting with 1;

ps.setString(1, "r");

ResultSet rs = ps.executeQuery(sql);

rs.next();

long count = rs.getLong(1);

rs.close();

ps.close();


More JDBC Code Examples

The PostgreSQL JDBC documentation has more examples athttp://jdbc.postgresql.org/documentation/head/


http://jdbc.postgresql.org/documentation/head/

Using cursors


Executing INSERT, UPDATE, DELETE SQL statements


Creating and modifying database objects


Calling stored functions


Handling binary data


JDBC escapes e.g. strings, outer joins, date-time, scalar functions


PostgreSQL Extensions to the JDBC API

• Geometric data types

• Large objects

• Listen/Notify

• Server prepared statements


Connection pools


Further JDBC Reading

JDBC API Documentation and JDBC Specificationhttp://jdbc.postgresql.org/documentation/head/reading.html


http://jdbc.postgresql.org/documentation/head/reading.html

__ __

/ \~~~/ \ . o O ( Thank you! )

,----( oo )

/ \__ __/

/| (\ |(

^ \ /___\ /\ |

|__| |__|-"


Acknowledgements

Hayley Jane Wakenshaw

__ __

/ \~~~/ \

,----( oo )

/ \__ __/

/| (\ |(

^ \ /___\ /\ |

|__| |__|-"


License

This work is licensed under a Creative Commons Attribution 3.0 UnportedLicense. To view a copy of this license, (a) visithttp://creativecommons.org/licenses/by/3.0/us/; or, (b) send aletter to Creative Commons, 171 2nd Street, Suite 300, San Francisco,California, 94105, USA.


http://creativecommons.org/licenses/by/3.0/us/

pgtop for android: things i learned making this app

Technology