Wednesday, 22 May 2013


SleepTracker, still in beta mode, but it's definitely getting there.

Tracking sleep
Tracking sleep has become a habit of mine. I find it utterly interesting to look at correlations in between sleep and physical and mental achievements, along with my general state of mind. There is no doubt in my mind that lack of sleep, over a longer period of time, is very damaging. People that end up in a situation where they need to drink liters of caffeine drinks to survive through the day are either born stupid, or will become stupid.

My first ever sleep record reads:

In other words I've been tracking my sleep every day since 19th of October 2010, for 946 days. I do have some interesting data, as I've gone through a few interesting periods in life.


SleepBot - trend view.

My tracking started with an Android app called SleepBot. A great app, that is integrated / synchronised with a website for viewing your data. Unfortunately SleepBot is not very good when it comes to preserving old data. Throughout different versions the tracking format has changed, and my data has gotten all messed up. The app is lacking support for change of time zones as well. Too bad if you happen to be up in the air while sleeping.

SleepTracker, the remedy
To save my sleep data (luckily SleepBot has got good export functionality) I've created my own backend system, called SleepTracker. This can be updated with integration functionality towards other apps as well, and maybe one day I'll make my own app. Below is an overview on the architecture.

I'm running Oracle, the database of my choice, on Amazon RDS. For more details read
- Amazon Relational Database Service
Oracle development environment

And of course, I'm not doing anything stupid like using Hibernate or another dull ORM tool for generating the database structure. I've modelled by hand, to make it perfect. Why ORM is stupid? Read
Data modelling and ORM

One part of a larger database.

I've created PL/SQL procedures that handles all data near processing. PL/SQL kicks! This part handles importing, cleaning up the data (fixing wrong data due to SleepBot bugs) and function as the backend of all RPC (Remote Procedure Calls).

Example code:
-- name:        setSession
-- what:        sets a session for given user
-- author:      Frode Klevstul
-- start date:  2013.05.08
procedure setSession
 p_user_pk in
, p_ip_address in ses_session.ip_address%type
, p_session_key in ses_session.session_key%type
, p_last_code_module in ses_session.last_code_module%type
, p_last_parameter_list in ses_session.last_parameter_list%type
c_procedure constant varchar2(32) := 'setSession';


insert into ses_session
(session_key, use_user_fk, ip_address, last_code_module, last_parameter_list, date_last_action)
values (
, p_user_pk
, p_ip_address
, p_last_code_module
, p_last_parameter_list
, systimestamp

when others then
log_lib.exceptionHandle(sqlcode, sqlerrm, g_package||'.'||c_procedure);
end setSession;

A Python script is used for communicating with the PL/SQL RPC code. With Python 2.7 this is a very simple task, and not many lines of code are needed for handling SQL result sets from PL/SQL.

Read more
Stuck with Python 2.4
Local Python development environment

Example code:
print '{'
print '"result_set" : '
print '['

for x in retCursor.description:

  for rowData in retCursor:
    rowObject = {}

    if retCursor.rowcount > 1:
      print ','

    for idx, x in enumerate(rowData):
      rowObject[columnName[idx]] = x

    print json.dumps(
     , ensure_ascii = False
     , encoding = 'utf-8'
     , indent = 4

print ']'
print ', "hits" : ' + str(retCursor.rowcount)
print ', "success" : true'
print '}'

For the front end / user interface I've used Sencha's ExtJS 4.2. A heavy but feature rich JS framework for building web applications.

Example code:
what: A panel for presenting different grids
started: April 2013
who: Frode Klevstul

Ext.define('SleepTracker.view.GridMainView', {
extend: 'Ext.TabPanel',
alias: 'widget.gridmainview',
activeTab: 0,
plain: true,
defaults :{
autoScroll: true,
bodyPadding: 10
items: [{
title: 'Sleep records - day',
items: {xtype: 'sleeprecorddaygrid'},
listeners: {
activate: function(tab) {
var trendView = Ext.ComponentQuery.query('trendview')[0];
var store = Ext.getStore('SleepRecordDays');
trendView.setStore(store, 'sleeprecorddaygrid');

title: 'Sleep records - entries',
items: {xtype: 'sleeprecordgrid'},
listeners: {
activate: function(tab) {
var trendView = Ext.ComponentQuery.query('trendview')[0];
var store = Ext.getStore('SleepRecords');
trendView.setStore(store, 'sleeprecordgrid');
initComponent: function() {

Login to SleepTracker.

No comments:

Post a Comment

Allowed HTML tags:
<a href="">hyperlink</a>

Please, show the courtesy of identifying yourself when adding a comment. Anonymous comments will, most likely, be removed.