package com.forsir.android.mhfinance.helper;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.forsir.android.mhfinance.storeObjects.IStorable;
public class DbAdapter {
private static final String TAG = "dbAdapter";
private static final int DATABASE_VERSION = 70;
private static final String DATABASE_NAME = "finance";
public static final String ID = "_id";
public static final String ACCOUNT_TABLE = "accounts";
public static final String ACCOUNT_NAME = "account_name";
public static final String CATEGORY_TABLE = "categories";
public static final String CATEGORY_NAME = "category_name";
public static final String CATEGORY_PARENT = "category_id";
public static final String PAYMENT_TABLE = "payment";
public static final String PAYMENT_ACCOUNT = "account_id";
public static final String PAYMENT_TRANSFER_ACCOUNT = "transfer_account_id";
public static final String PAYMENT_CATEGORY = "category_id";
public static final String PAYMENT_DEPOSIT = "deposit";
public static final String PAYMENT_WITHDRAW = "withdraw";
public static final String PAYMENT_REMAINDER = "remainder";
public static final String PAYMENT_ACTUALIZED = "actualized";
public static final String PAYMENT_TYPE = "payment_type";
public static final String PAYMENT_DATE = "date";
public static final String PAYMENT_MONTH = "month";
public static final String PAYMENT_TIME = "time"; // is not used
public static final String PAYMENT_DESCRIPTION = "description";
public static final String PAYMENT_REPEATING = "repeating";
public static final String REPEATING_TABLE = "repeating";
public static final String REPEATING_ACTUAL_DATE = "actual_date";
public static final String REPEATING_END_DATE = "end_date";
public static final String REPEATING_TYPE = "repeating_type";
public static final String REPEATING_EVERY_N = "every_n";
private DatabaseHelper mDbHelper;
private SQLiteDatabase mDb;
private final Activity mActivity;
/**
* Database creation sql statements
*/
private static final String PAYMENT_TABLE_CREATE = "create table " + PAYMENT_TABLE + " (" + ID + " integer primary key autoincrement, " + PAYMENT_ACCOUNT
+ " integer not null references " + ACCOUNT_TABLE + "(" + ID + ") on delete restrict, " + PAYMENT_TRANSFER_ACCOUNT + " integer references " + ACCOUNT_TABLE
+ "(" + ID + ") on delete restrict, " + PAYMENT_CATEGORY + " integer references " + CATEGORY_TABLE + "(" + ID + ") on delete restrict, " + PAYMENT_DEPOSIT
+ " real not null," + PAYMENT_WITHDRAW + " real not null, " + PAYMENT_REMAINDER + " real, " + PAYMENT_ACTUALIZED + " bool null, " + PAYMENT_TYPE + " int, "
+ PAYMENT_DATE + " long not null, " + PAYMENT_MONTH + " long not null, " + PAYMENT_DESCRIPTION + " text not null, " + PAYMENT_REPEATING
+ " integer references " + REPEATING_TABLE + "(" + ID + ") on delete restrict);";
private static final String ACCOUNT_TABLE_CREATE = "create table " + ACCOUNT_TABLE + " (" + ID + " integer primary key autoincrement, " + ACCOUNT_NAME
+ " text not null);";
private static final String CATEGORY_TABLE_CREATE = "create table " + CATEGORY_TABLE + " (" + ID + " integer primary key autoincrement, " + CATEGORY_PARENT
+ " integer references " + CATEGORY_PARENT + "(" + ID + ") on delete restrict," + CATEGORY_NAME + " text not null);";
private static final String REPEATING_TABLE_CREATE = "create table " + REPEATING_TABLE + " (" + ID + " integer primary key autoincrement, " + REPEATING_ACTUAL_DATE
+ " long," + REPEATING_END_DATE + " long not null, " + REPEATING_TYPE + " integer, " + REPEATING_EVERY_N + " integer default 1);";
private static final String INDEX_DATE = "create index if not exists index_" + PAYMENT_TABLE + " on " + PAYMENT_TABLE + " (" + PAYMENT_DATE + ");";
public final static String SELECT_PAYMENT_WITH_ACCOUNT = PAYMENT_TABLE + " inner join " + ACCOUNT_TABLE + " on " + PAYMENT_TABLE + "." + PAYMENT_ACCOUNT + "="
+ ACCOUNT_TABLE + "." + ID + ", " + CATEGORY_TABLE + " on " + PAYMENT_TABLE + "." + PAYMENT_CATEGORY + "=" + CATEGORY_TABLE + "." + ID;
// payment inner join category on payment_category=category.id
public final static String SELECT_PAYMENT_WITH_CATEGORY = PAYMENT_TABLE + " inner join " + CATEGORY_TABLE + " on " + PAYMENT_TABLE + "." + PAYMENT_CATEGORY + " = "
+ CATEGORY_TABLE + "." + ID;
private final Context mCtx;
private static class DatabaseHelper extends SQLiteOpenHelper {
DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
Log.w(TAG, "creating tables");
db.execSQL(ACCOUNT_TABLE_CREATE);
db.execSQL(CATEGORY_TABLE_CREATE);
db.execSQL(REPEATING_TABLE_CREATE);
db.execSQL(PAYMENT_TABLE_CREATE);
db.execSQL(INDEX_DATE);
// Test.fillData(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion == 60) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ".");
db.execSQL("DROP TABLE IF EXISTS " + REPEATING_TABLE);
db.execSQL(REPEATING_TABLE_CREATE);
} else if (oldVersion == 50) {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ".");
// TODO maybe copy payments
db.execSQL("DROP TABLE IF EXISTS " + REPEATING_TABLE);
db.execSQL(REPEATING_TABLE_CREATE);
db.execSQL("DROP TABLE IF EXISTS " + PAYMENT_TABLE);
db.execSQL(PAYMENT_TABLE_CREATE);
} else {
Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + PAYMENT_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + ACCOUNT_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE);
db.execSQL("DROP TABLE IF EXISTS " + REPEATING_TABLE);
onCreate(db);
}
}
}
public int getVersion() {
return DATABASE_VERSION;
}
public DbAdapter(Context ctx, Activity activity) {
mCtx = ctx;
mActivity = activity;
}
public DbAdapter open() throws SQLException {
mDbHelper = new DatabaseHelper(mCtx);
mDb = mDbHelper.getWritableDatabase();
return this;
}
public void close() {
mDbHelper.close();
}
public long createEntry(IStorable entry) {
final long id = mDb.insert(entry.getTableName(), null, entry.getContent());
entry.setId(id);
return id;
}
public long createEntry(String table, ContentValues context) {
final long check = mDb.insert(table, null, context);
return check;
}
public boolean deleteEntry(IStorable entry) {
return mDb.delete(entry.getTableName(), ID + "=" + entry.getId(), null) > 0;
}
public boolean deleteEntries(IStorable entry, String where) {
return mDb.delete(entry.getTableName(), where, null) > 0;
}
public Cursor fetchAllEntries(IStorable entry) {
final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), null, null, null, null, null);
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
return cursor;
}
public Cursor fetchAllEntries(String table, String[] columns) {
final Cursor cursor = mDb.query(table, columns, null, null, null, null, null);
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
return cursor;
}
public Cursor fetchAllEntries(IStorable entry, String sort) {
final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), null, null, null, null, sort);
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
return cursor;
}
public Cursor fetchAllEntries(IStorable entry, String where, String sort) {
final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), where, null, null, null, sort);
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
return cursor;
}
public Cursor fetchAllEntries(IStorable entry, String where, String sort, int count) {
final Cursor cursor = mDb.query(entry.getTableName(), entry.getColumns(), where, null, null, null, sort, String.valueOf(count));
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
return cursor;
}
public Cursor fetchEntry(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) throws SQLException {
final Cursor cursor = mDb.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
public Cursor fetchEntry(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, int count)
throws SQLException {
final Cursor cursor = mDb.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, String.valueOf(count));
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
public Cursor fetchEntry(IStorable entry) throws SQLException {
final Cursor cursor = mDb.query(true, entry.getTableName(), entry.getColumns(), ID + "=" + entry.getId(), null, null, null, null, null);
if (mActivity != null) {
mActivity.startManagingCursor(cursor);
}
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
public IStorable getEntry(IStorable entry) {
final Cursor cursor = fetchEntry(entry);
if (cursor.moveToFirst()) {
entry.fillFromCursor(cursor);
}
cursor.close();
return entry;
}
public boolean updateEntry(IStorable entry) {
return mDb.update(entry.getTableName(), entry.getContent(), ID + "=" + entry.getId(), null) > 0;
}
public int getCount(IStorable entry) {
final Cursor cursor = fetchEntry(entry.getTableName(), new String[] { "count(*)" }, null, null, null, null, null);
if (cursor == null) {
return 0;
}
if (!cursor.moveToFirst()) {
return 0;
}
final int returnCount = cursor.getInt(0);
cursor.close();
return returnCount;
}
public void beginTrasaction() {
mDb.beginTransaction();
}
public void commitTrasaction() {
mDb.setTransactionSuccessful();
}
public void endTrasaction() {
mDb.endTransaction();
}
}
|