Android Open Source - mvfa Database Helper

From Project

Back to project page mvfa.
License

The source code is released under:
GNU General Public License
If you think the Android project mvfa listed in this page is inappropriate, such as containing malicious code/tools or violating the copyright, please email info at java2s dot com, thanks.
Java Source Code

/*
  Copyright (C) 2010 Ben Van Daele (vandaeleben@gmail.com)
/*  w w  w.  j  av  a  2  s . c o m*/
  This program is free software: you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation, either version 3 of the License, or
  (at your option) any later version.

  This program is distributed in the hope that it will be useful,
  but WITHOUT ANY WARRANTY; without even the implied warranty of
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  GNU General Public License for more details.

  You should have received a copy of the GNU General Public License
  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package be.benvd.mvforandroid.data;

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

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHelper extends SQLiteOpenHelper
  {

  private static final String DATABASE_NAME = "mvforandroid.db";
  private static final int SCHEMA_VERSION = 2;

  public final Usage usage;
  public final Credit credit;
  public final Topups topups;
  public final Msisdns msisdns;

  public DatabaseHelper(Context context)
    {
    super(context, DATABASE_NAME, null, SCHEMA_VERSION);

    this.usage = new Usage();
    this.credit = new Credit();
    this.topups = new Topups();
    this.msisdns = new Msisdns();
    }

  @Override
  public void onCreate(SQLiteDatabase db)
    {
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Usage.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "timestamp INTEGER NOT NULL, "
        + "duration INTEGER, " + "type INTEGER, " + "incoming INTEGER, " + "contact TEXT, " + "cost REAL);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Topups.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "amount REAL NOT NULL, "
        + "method TEXT NOT NULL, " + "executed_on INTEGER NOT NULL, " + "received_on INTEGER NOT NULL, " + "status TEXT NOT NULL);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Credit.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "valid_until INTEGER NULL, "
        + "expired INTEGER NOT NULL, " + "sms INTEGER NOT NULL, " + "data INTEGER NOT NULL, " + "credits REAL NOT NULL, "
        + "price_plan TEXT NOT NULL, " + "sms_son INTEGER NOT NULL);");
    db.execSQL("CREATE TABLE IF NOT EXISTS " + Msisdns.TABLE_NAME + " (" + "_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "timestamp INTEGER NOT NULL, "
        + "msisdn TEXT);");
    }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
    switch(oldVersion)
      {
      case 1:
        if(newVersion < 2)
          {
          return;
          }
        // add the column for super-on-net sms's
        db.execSQL("ALTER TABLE " + Credit.TABLE_NAME + " ADD COLUMN sms_son INTEGER NOT NULL DEFAULT 0;");
      }
    }

  private static SimpleDateFormat apiFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

  private static Date getDateFromAPI(String dateString)
    {
    try
      {
      return apiFormat.parse(dateString);
      }
    catch(ParseException e)
      {
      return null;
      }
    }

  public class Credit
    {

    private static final String TABLE_NAME = "credit";

    public void update(JSONObject json, boolean data_only) throws JSONException
      {
      Cursor query = getWritableDatabase().query(TABLE_NAME, new String[] { "_id" }, null, null, null, null, null, null);

      ContentValues values = new ContentValues();

      values.put("valid_until", getDateFromAPI(json.getString("valid_until")).getTime());
      values.put("expired", (Boolean.parseBoolean(json.getString("is_expired")) ? 1 : 0));
      if(Boolean.parseBoolean(json.getString("is_expired"))==true)
        {
        values.put("data", 0);
        values.put("credits", 0);
        }
      else
        {
        values.put("data", Long.parseLong(json.getString("data")));
        values.put("credits", Double.parseDouble(json.getString("credits")));
        }
      values.put("price_plan", json.getString("price_plan"));
      if(!data_only)
        {
        values.put("sms", Integer.parseInt(json.getString("sms")));
        values.put("sms_son", Integer.parseInt(json.getString("sms_super_on_net")));
        }
      else
        {
        values.put("sms", 0);
        values.put("sms_son", 0);
        }
        
      if(query.getCount() == 0)
        {
        // No credit info stored yet, insert a row
        getWritableDatabase().insert(TABLE_NAME, "valid_until", values);
        }
      else
        {
        // Credit info present already, so update it
        getWritableDatabase().update(TABLE_NAME, values, null, null);
        }

      query.close();
      }

    public long getValidUntil()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      long result;

      if(c.moveToFirst())
        result = c.getLong(1);
      else
        result = 0;

      c.close();
      return result;
      }

    public boolean isExpired()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      boolean result;

      if(c.moveToFirst())
        result = c.getLong(2) == 1;
      else
        result = true;

      c.close();
      return result;
      }

    public int getRemainingSms()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      int result;

      if(c.moveToFirst())
        result = c.getInt(3);
      else
        result = 0;

      c.close();
      return result;
      }

    public long getRemainingData()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      long result;

      if(c.moveToFirst())
        result = c.getLong(4);
      else
        result = 0;

      c.close();
      return result;
      }

    public double getRemainingCredit()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      double result;

      if(c.moveToFirst())
        result = c.getDouble(5);
      else
        result = 0;

      c.close();
      return result;
      }

    public int getPricePlan()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      int result;

      if(c.moveToFirst())
        result = c.getInt(6);
      else
        result = 0;

      c.close();
      return result;
      }

    public int getRemainingSmsSuperOnNet()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      int result;

      if(c.moveToFirst())
        result = c.getInt(7);
      else
        result = 0;

      c.close();
      return result;
      }
    }

  public class Usage
    {

    private static final String TABLE_NAME = "usage";

    public static final int TYPE_DATA = 0;
    public static final int TYPE_SMS = 1;
    public static final int TYPE_VOICE = 2;
    public static final int TYPE_MMS = 3;

    public static final int ORDER_BY_DATE = 1;

    public void update(JSONArray jsonArray) throws JSONException
      {
      getWritableDatabase().delete(TABLE_NAME, null, null);

      getWritableDatabase().beginTransaction();
      for(int i = 0; i < jsonArray.length(); i++)
        {
        JSONObject json = jsonArray.getJSONObject(i);
        insert(json);
        }
      getWritableDatabase().setTransactionSuccessful();
      getWritableDatabase().endTransaction();
      }

    public void insert(JSONObject json) throws JSONException
      {

      // "timestamp INTEGER NOT NULL, " +
      // "duration INTEGER NOT NULL, " +
      // "type INTEGER NOT NULL, " +
      // "incoming INTEGER NOT NULL, " +
      // "contact TEXT NOT NULL, " +
      // "cost REAL NOT NULL);");

      ContentValues values = new ContentValues();
      values.put("timestamp", getDateFromAPI(json.getString("start_timestamp")).getTime());
      values.put("duration", json.getLong("duration_connection"));

      if(Boolean.parseBoolean(json.getString("is_data")))
        values.put("type", TYPE_DATA);
      if(Boolean.parseBoolean(json.getString("is_sms")))
        values.put("type", TYPE_SMS);
      if(Boolean.parseBoolean(json.getString("is_voice")))
        values.put("type", TYPE_VOICE);
      if(Boolean.parseBoolean(json.getString("is_mms")))
        values.put("type", TYPE_MMS);

      values.put("incoming", (Boolean.parseBoolean(json.getString("is_incoming")) ? 1 : 0));
      values.put("contact", json.getString("to"));
      values.put("cost", Double.parseDouble(json.getString("price")));

      getWritableDatabase().insert(TABLE_NAME, "timestamp", values);
      }

    public Cursor get(long id)
      {
      return getReadableDatabase().query(TABLE_NAME, null, "_id=" + id, null, null, null, null);
      }

    /**
     * Returns a cursor over the Usage table.
     * 
     * @param isSearch
     *            Whether to include usage records obtained by a search, or
     *            (xor) those obtained through auto-updating.
     * @param order
     *            The constant representing the field to order the cursor
     *            by.
     * @param ascending
     *            Whether the order should be ascending or descending.
     */
    public Cursor get(int order, boolean ascending)
      {
      String orderBy = null;
      switch(order)
        {
        case ORDER_BY_DATE:
          orderBy = "timestamp " + (ascending ? "asc" : "desc");
        }
      return getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, orderBy);
      }

    public long getTimestamp(Cursor c)
      {
      return c.getLong(1);
      }

    public long getduration(Cursor c)
      {
      return c.getLong(2);
      }

    public int getType(Cursor c)
      {
      return c.getInt(3);
      }

    public boolean isIncoming(Cursor c)
      {
      return c.getInt(4) == 1;
      }

    public String getContact(Cursor c)
      {
      return c.getString(5);
      }

    public double getCost(Cursor c)
      {
      return c.getDouble(6);
      }

    }

  public class Topups
    {

    private static final String TABLE_NAME = "topups";

    public void update(JSONArray jsonArray, boolean b) throws JSONException
      {
      getWritableDatabase().delete(TABLE_NAME, null, null);

      for(int i = 0; i < jsonArray.length(); i++)
        {
        JSONObject json = jsonArray.getJSONObject(i);
        insert(json);
        }
      }

    private void insert(JSONObject json) throws JSONException
      {
      ContentValues values = new ContentValues();

      values.put("amount", Double.parseDouble(json.getString("amount")));
      values.put("method", json.getString("method"));
      values.put("executed_on", getDateFromAPI(json.getString("executed_on")).getTime());
      values.put("received_on", getDateFromAPI(json.getString("payment_received_on")).getTime());
      values.put("status", json.getString("status"));

      getWritableDatabase().insert(TABLE_NAME, "timestamp", values);
      }

    public Cursor getAll()
      {
      return getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      }

    public double getAmount(Cursor c)
      {
      return c.getDouble(1);
      }

    public String getMethod(Cursor c)
      {
      return c.getString(2);
      }

    public long getExecutedOn(Cursor c)
      {
      return c.getLong(3);
      }

    public long getReceivedOn(Cursor c)
      {
      return c.getLong(4);
      }

    public String getStatus(Cursor c)
      {
      return c.getString(5);
      }

    }
  
  public class Msisdns
    {
    private static final String TABLE_NAME = "msisdns";
  
    public void update(JSONArray jsonArray) throws JSONException
      {
      getWritableDatabase().delete(TABLE_NAME, null, null);
      for(int i = 0; i < jsonArray.length(); i++)
        {
        String msisdn = jsonArray.getString(i);
        insert(msisdn);
        }
      }
  
    private void insert(String msisdn) throws JSONException
      {
      ContentValues values = new ContentValues();
      values.put("msisdn", msisdn);
      getWritableDatabase().insert(TABLE_NAME, "timestamp", values);
      }
  
    public Cursor getAll()
      {
      return getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      }
  
    public String getMsisdn(Cursor c)
      {
      return c.getString(1);
      }
    
    public String getMsisdnList()
      {
      Cursor c = getReadableDatabase().query(TABLE_NAME, null, null, null, null, null, null);
      String result;

      if(c.moveToFirst())
        result = c.getString(1);
      else
        result = "non";

      c.close();
      return result;
      }
    }
  }
Java Source Code List
