Database.java :  » App » skydiveapp-4-android » org » skydiveapp » data » Android Open Source

package org.skydiveapp.data;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public final class Database extends SQLiteOpenHelper
{
  // table constants
  // log entry table
  public static final String LOG_ENTRY_TABLE = "log_entry";
  public static final String LOG_ENTRY_ID = "_id";
  public static final String LOG_ENTRY_JUMP_NUMBER = "jump_number";
  public static final String LOG_ENTRY_DATE = "date";
  public static final String LOG_ENTRY_ALTITUDE_UNIT = "altitude_unit";
  public static final String LOG_ENTRY_EXIT_ALTITUDE = "exit_altitude";
  public static final String LOG_ENTRY_DEPLOYMENT_ALTITUDE = "deployment_altitude";
  public static final String LOG_ENTRY_SKYDIVE_TYPE = "skydive_type";
  public static final String LOG_ENTRY_LOCATION = "location";
  public static final String LOG_ENTRY_AIRCRAFT = "aircraft";
  public static final String LOG_ENTRY_FREEFALL_TIME = "freefall_time_seconds";
  public static final String LOG_ENTRY_CUTAWAY = "cutaway";
  public static final String LOG_ENTRY_DIAGRAM = "diagram";
  public static final String LOG_ENTRY_NOTES = "notes";
  public static final String[] LOG_ENTRY_ALL_COLUMNS =
  {
    LOG_ENTRY_ID,
    LOG_ENTRY_JUMP_NUMBER,
    LOG_ENTRY_DATE,
    LOG_ENTRY_ALTITUDE_UNIT,
    LOG_ENTRY_EXIT_ALTITUDE,
    LOG_ENTRY_DEPLOYMENT_ALTITUDE,
    LOG_ENTRY_SKYDIVE_TYPE,
    LOG_ENTRY_LOCATION,
    LOG_ENTRY_AIRCRAFT,
    LOG_ENTRY_FREEFALL_TIME,
    LOG_ENTRY_CUTAWAY,
    LOG_ENTRY_DIAGRAM,
    LOG_ENTRY_NOTES
  };
  
  // signature table
  public static final String SIGNATURE_TABLE = "signature";
  public static final String SIGNATURE_ID = "_id";
  public static final String SIGNATURE_IMAGE = "image";
  public static final String SIGNATURE_LICENSE = "license";
  public static final String[] SIGNATURE_ALL_COLUMNS =
  {
    SIGNATURE_ID,
    SIGNATURE_IMAGE,
    SIGNATURE_LICENSE
  };
  
  // log_entry-signature table
  public static final String LOG_ENTRY_SIGNATURE_TABLE = "log_entry_signature";
  public static final String LOG_ENTRY_SIGNATURE_ID = "_id";
  public static final String LOG_ENTRY_SIGNATURE_LOG_ENTRY = "log_entry";
  public static final String LOG_ENTRY_SIGNATURE_SIGNATURE = "signature";
  public static final String[] LOG_ENTRY_SIGNATURE_ALL_COLUMNS =
  {
    LOG_ENTRY_SIGNATURE_ID,
    LOG_ENTRY_SIGNATURE_LOG_ENTRY,
    LOG_ENTRY_SIGNATURE_SIGNATURE
  };
  
  // log_entry-rig table
  public static final String LOG_ENTRY_RIG_TABLE = "log_entry_rig";
  public static final String LOG_ENTRY_RIG_ID = "_id";
  public static final String LOG_ENTRY_RIG_LOG_ENTRY = "log_entry";
  public static final String LOG_ENTRY_RIG_RIG = "rig";
  public static final String[] LOG_ENTRY_RIG_ALL_COLUMNS =
  {
    LOG_ENTRY_RIG_ID,
    LOG_ENTRY_RIG_LOG_ENTRY,
    LOG_ENTRY_RIG_RIG
  };
  
  // skydive type table
  public static final String SKYDIVE_TYPE_TABLE = "skydive_type";
  public static final String SKYDIVE_TYPE_ID = "_id";
  public static final String SKYDIVE_TYPE_ACTIVE = "active";
  public static final String SKYDIVE_TYPE_NAME = "name";
  public static final String SKYDIVE_TYPE_NOTES = "notes";
  public static final String SKYDIVE_TYPE_FREEFALL_PROFILE = "freefall_profile";
  public static final String[] SKYDIVE_TYPE_ALL_COLUMNS =
  {
    SKYDIVE_TYPE_ID,
    SKYDIVE_TYPE_ACTIVE,
    SKYDIVE_TYPE_NAME,
    SKYDIVE_TYPE_NOTES,
    SKYDIVE_TYPE_FREEFALL_PROFILE,
  };
  
  // location table
  public static final String LOCATION_TABLE = "location";
  public static final String LOCATION_ID = "_id";
  public static final String LOCATION_ACTIVE = "active";
  public static final String LOCATION_NAME = "name";
  public static final String LOCATION_HOME = "home";
  public static final String LOCATION_NOTES = "notes";
  public static final String[] LOCATION_ALL_COLUMNS =
  {
    LOCATION_ID,
    LOCATION_ACTIVE,
    LOCATION_NAME,
    LOCATION_HOME,
    LOCATION_NOTES,
  };
  
  // aircraft table
  public static final String AIRCRAFT_TABLE = "aircraft";
  public static final String AIRCRAFT_ID = "_id";
  public static final String AIRCRAFT_ACTIVE = "active";
  public static final String AIRCRAFT_NAME = "name";
  public static final String AIRCRAFT_NOTES = "notes";
  public static final String[] AIRCRAFT_ALL_COLUMNS =
  {
    AIRCRAFT_ID,
    AIRCRAFT_ACTIVE,
    AIRCRAFT_NAME,
    AIRCRAFT_NOTES
  };
  
  // rig table
  public static final String RIG_TABLE = "rig";
  public static final String RIG_ID = "_id";
  public static final String RIG_ACTIVE = "active";
  public static final String RIG_NAME = "name";
  public static final String RIG_PRIMARY = "_primary";
  public static final String RIG_ARCHIVED = "archived";
  public static final String RIG_NOTES = "notes";
  public static final String[] RIG_ALL_COLUMNS =
  {
    RIG_ID,
    RIG_ACTIVE,
    RIG_NAME,
    RIG_PRIMARY,
    RIG_ARCHIVED,
    RIG_NOTES
  };
  
  // component table
  public static final String RIG_COMPONENT_TABLE = "rig_component";
  public static final String RIG_COMPONENT_ID = "_id";
  public static final String RIG_COMPONENT_RIG = "rig";
  public static final String RIG_COMPONENT_NAME = "name";
  public static final String RIG_COMPONENT_SERIAL_NUMBER = "serial_number";
  public static final String RIG_COMPONENT_NOTES = "notes";
  public static final String[] RIG_COMPONENT_ALL_COLUMNS =
  {
    RIG_COMPONENT_ID,
    RIG_COMPONENT_RIG,
    RIG_COMPONENT_NAME,
    RIG_COMPONENT_SERIAL_NUMBER,
    RIG_COMPONENT_NOTES
  };
  
  // rig reminder table
  public static final String RIG_REMINDER_TABLE = "rig_reminder";
  public static final String RIG_REMINDER_ID = "_id";
  public static final String RIG_REMINDER_RIG = "rig";
  public static final String RIG_REMINDER_NAME = "name";
  public static final String RIG_REMINDER_LAST_COMPLETED_DATE = "last_completed_date";
  public static final String RIG_REMINDER_INTERVAL = "interval";
  public static final String RIG_REMINDER_INTERVAL_UNIT = "interval_unit";
  public static final String[] RIG_REMINDER_ALL_COLUMNS =
  {
    RIG_REMINDER_ID,
    RIG_REMINDER_RIG,
    RIG_REMINDER_NAME,
    RIG_REMINDER_LAST_COMPLETED_DATE,
    RIG_REMINDER_INTERVAL,
    RIG_REMINDER_INTERVAL_UNIT
  };
  
  // settings table
  public static final String SETTINGS_TABLE = "settings";
  public static final String SETTINGS_UNIT_OF_MEASURE = "unit_of_measure";
  public static final String SETTINGS_EXIT_ALTITUDE = "default_exit_altitude";
  public static final String SETTINGS_DEPLOYMENT_ALTITUDE = "default_deployment_altitude";
  public static final String[] SETTINGS_ALL_COLUMNS =
  {
    SETTINGS_UNIT_OF_MEASURE,
    SETTINGS_EXIT_ALTITUDE,
    SETTINGS_DEPLOYMENT_ALTITUDE
  };
  
  // logbook history table
  public static final String LOGBOOK_HISTORY_TABLE = "logbook_history";
  public static final String LOGBOOK_HISTORY_FREEFALL_TIME = "freefall_time";
  public static final String LOGBOOK_HISTORY_CUTAWAYS = "cutaways";
  public static final String[] LOGBOOK_HISTORY_ALL_COLUMNS =
  {
    LOGBOOK_HISTORY_FREEFALL_TIME,
    LOGBOOK_HISTORY_CUTAWAYS
  };

  // database constants
  public static final String DATABASE_NAME = "SkydiveAppDatabase";
  public static final int DATABASE_VERSION = 1;
    
  // date formatter
  private static DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
  
  private static final String CREATE_LOG_ENTRY_TABLE =
    "create table log_entry (" +
    "_id integer primary key autoincrement, " +
    "jump_number integer not null, " +
    "date text not null, " +
    "altitude_unit text not null, " +
    "exit_altitude integer, " +
    "deployment_altitude integer, " +
    "freefall_time_seconds integer, " +
    "skydive_type integer, " +
    "location integer, " +
    "aircraft integer, " +
    "cutaway integer not null, " +
    "diagram blob, " +
    "notes text, " +
    "foreign key (skydive_type) references skydive_type(_id)" +
    "foreign key (location) references location(_id)" +
    "foreign key (aircraft) references aircraft(_id)" +
    ");";
  
  private static final String CREATE_SIGNATURE_TABLE =
    "create table signature (" +
    "_id integer primary key autoincrement, " +
    "image blob not null, " +
    "license text not null);";
  
  private static final String CREATE_LOG_ENTRY_SIGNATURE_TABLE =
    "create table log_entry_signature (" +
    "_id integer primary key autoincrement, " +
    "log_entry integer not null, " +
    "signature integer not null, " +
    "foreign key (log_entry) references log_entry(_id), " +
    "foreign key (signature) references signature(_id)" +
    ");";
  
  private static final String CREATE_LOG_ENTRY_RIG_TABLE =
    "create table log_entry_rig (" +
    "_id integer primary key autoincrement, " +
    "log_entry integer not null, " +
    "rig integer not null, " +
    "foreign key (log_entry) references log_entry(_id), " +
    "foreign key (rig) references rig(_id)" +
    ");";
  
  private static final String CREATE_SKYDIVE_TYPE_TABLE =
    "create table skydive_type (" +
    "_id integer primary key autoincrement, " +
    "active integer not null, " +
    "name text not null, " +
    "notes text, " +
    "freefall_profile text);";
  
  private static final String CREATE_LOCATION_TABLE =
    "create table location (" +
    "_id integer primary key autoincrement, " +
    "active integer not null, " +
    "name text not null, " +
    "home integer not null, " +
    "notes text);";
  
  private static final String CREATE_AIRCRAFT_TABLE =
    "create table aircraft (" +
    "_id integer primary key autoincrement, " +
    "active integer not null, " +
    "name text not null, " +
    "notes text);";
  
  private static final String CREATE_RIG_TABLE =
    "create table rig (" +
    "_id integer primary key autoincrement, " +
    "active integer not null, " +
    "name text not null, " +
    "_primary integer not null, " +
    "archived integer not null, " +
    "notes text" +
    ");";
  
  private static final String CREATE_RIG_COMPONENT_TABLE =
    "create table rig_component (" +
    "_id integer primary key autoincrement, " +
    "rig integer not null, " +
    "name text not null, " + 
    "serial_number text, " + 
    "notes text, " + 
    "foreign key (rig) references rig(_id)" +
    ");";
  
  private static final String CREATE_RIG_REMINDER_TABLE =
    "create table rig_reminder (" +
    "_id integer primary key autoincrement, " +
    "rig integer not null, " +
    "name text not null, " +
    "last_completed_date text not null, " +
    "interval integer not null, " +
    "interval_unit text not null, " +
    "foreign key (rig) references rig(_id)" +
    ");";
  
  private static final String CREATE_SETTINGS_TABLE =
    "create table settings (" +
    "unit_of_measure text not null, " +
    "default_exit_altitude integer not null, " +
    "default_deployment_altitude integer not null" +
    ");";
  
  private static final String CREATE_LOGBOOK_HISTORY_TABLE =
    "create table logbook_history (" +
    "freefall_time int not null, " +
    "cutaways int not null" +
    ");";

  public Database(Context context)
  {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase db)
  {
    // create tables
    db.execSQL(CREATE_LOCATION_TABLE);
    db.execSQL(CREATE_AIRCRAFT_TABLE);
    db.execSQL(CREATE_SKYDIVE_TYPE_TABLE);
    db.execSQL(CREATE_RIG_TABLE);
    db.execSQL(CREATE_RIG_COMPONENT_TABLE);
    db.execSQL(CREATE_RIG_REMINDER_TABLE);
    db.execSQL(CREATE_SIGNATURE_TABLE);
    db.execSQL(CREATE_LOG_ENTRY_TABLE);
    db.execSQL(CREATE_LOG_ENTRY_RIG_TABLE);
    db.execSQL(CREATE_LOG_ENTRY_SIGNATURE_TABLE);
    db.execSQL(CREATE_SETTINGS_TABLE);
    db.execSQL(CREATE_LOGBOOK_HISTORY_TABLE);
    // insert default skydive types
    Data.insertDefaultSkydiveTypes(db);
    // insert default data
    Data.insertDefaltAircrafts(db);
    // insert default settings
    Data.insertDefaultSettings(db);    
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
  {
    // TODO: upgrade from older version
  }
  
  public static String max(String columnName)
  {
    return "max(" + columnName + ")";
  }
  
  public static boolean isNull(Cursor cursor, String columnName)
  {
    return cursor.isNull(cursor.getColumnIndex(columnName));
  }
  
  public static Integer getInteger(Cursor cursor, String columnName)
  {
    if (isNull(cursor, columnName))
      return null;
    else
      return cursor.getInt(cursor.getColumnIndex(columnName));
  }
  
  public static String getString(Cursor cursor, String columnName)
  {
    return cursor.getString(cursor.getColumnIndex(columnName));
  }
  
  public static Date getDate(Cursor cursor, String columnName)
  {
    String dateStr = getString(cursor, columnName);
    return stringToDate(dateStr);
  }
  
  public static Boolean getBoolean(Cursor cursor, String columnName)
  {
    if (isNull(cursor, columnName))
      return null;
    else
      return getInteger(cursor, columnName) > 0;
  }
  
  public static byte[] getBinaryData(Cursor cursor, String columnName)
  {
    return cursor.getBlob(cursor.getColumnIndex(columnName));
  }
  
  public static String dateToString(Date date)
  {
    return dateFormat.format(date);
  }
  
  public static Date stringToDate(String str)
  {
    try
    {
      if (str == null)
        return null;
      else
        return dateFormat.parse(str);
    }
    catch (ParseException ex)
    {
      Log.e(Database.class.getCanonicalName(), "Could not parse date: " + str);
      return null;
    }
  }
}