API tools faq
paste
Advertisement
moonlightcheese

Untitled

moonlightcheese
Jan 11th, 2012
181
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 10.86 KB | None | 0 0
raw download clone embed print report
  1. ////////////////////// sqlitehelper example
  2. public static class MyDbOpenHelper extends SQLiteOpenHelper {
  3.    
  4.     MyDbOpenHelper(Context context) {
  5.         //
  6.         super(context,DbSchema.DATABASE_NAME, null, DbSchema.DATABASE_VERSION);
  7.     }
  8.    
  9.     @Override
  10.     public void onCreate(SQLiteDatabase db) {
  11.         resetDb(db);
  12.     }
  13.    
  14.     @Override
  15.     public void onOpen(SQLiteDatabase db) {
  16.         //open db
  17.     }
  18.    
  19.     @Override
  20.     public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  21.         //upgrade db
  22.         SharedPreferences.Editor editor = pref.edit();
  23.         editor.putBoolean("dbdirty", true);
  24.         editor.commit();
  25.         if(writeCSVFile("dashboard_settings_backup.csv")) {
  26.             resetDb(db);
  27.             editor = pref.edit();
  28.             editor.putBoolean("dbdirty", false);
  29.             editor.commit();
  30.         } else {
  31.             //error, couldn't write settings
  32.         }
  33.         if(oldVersion<24) {
  34.             db.execSQL(DbSchema.ScheduleSchema.DROP_TABLE);
  35.             db.execSQL(DbSchema.ScheduleSchema.CREATE_TABLE);
  36.         }
  37.     }
  38.    
  39.     public void resetDb(SQLiteDatabase db) {
  40.         db.execSQL(DbSchema.InboundSchema.DROP_TABLE);
  41.         db.execSQL(DbSchema.OutboundSchema.DROP_TABLE);
  42.         db.execSQL(DbSchema.CheckbookSchema.DROP_TABLE);
  43.         db.execSQL(DbSchema.OnholdSchema.DROP_TABLE);
  44.         db.execSQL(DbSchema.ReconciliationSchema.DROP_TABLE);
  45.         db.execSQL(DbSchema.OnyardSchema.DROP_TABLE);
  46.         db.execSQL(DbSchema.ScheduleSchema.DROP_TABLE);
  47.         db.execSQL(DbSchema.InboundSchema.CREATE_TABLE);
  48.         db.execSQL(DbSchema.OutboundSchema.CREATE_TABLE);
  49.         db.execSQL(DbSchema.CheckbookSchema.CREATE_TABLE);
  50.         db.execSQL(DbSchema.OnholdSchema.CREATE_TABLE);
  51.         db.execSQL(DbSchema.ReconciliationSchema.CREATE_TABLE);
  52.         db.execSQL(DbSchema.OnyardSchema.CREATE_TABLE);
  53.         db.execSQL(DbSchema.ScheduleSchema.CREATE_TABLE);
  54.         Cursor c = null;
  55.         try {
  56.             c = db.query(DbSchema.SiteSchema.TABLE_NAME, null, null, null, null, null, null, null);
  57.             if(c==null || c.getCount()<1) {
  58.                 db.execSQL(DbSchema.SiteSchema.DROP_TABLE);
  59.                 db.execSQL(DbSchema.SiteSchema.CREATE_TABLE);
  60.             } else {
  61.                 c.moveToFirst();
  62.                 List<ContentValues> values = new ArrayList<ContentValues>();
  63.                 do {
  64.                     try {
  65.                         String label = c.getString(c.getColumnIndex(DbSchema.SiteSchema.COLUMN_LABEL));
  66.                         String ip = c.getString(c.getColumnIndex(DbSchema.SiteSchema.COLUMN_IP));
  67.                         String port = c.getString(c.getColumnIndex(DbSchema.SiteSchema.COLUMN_PORT));
  68.                         ContentValues cv = new ContentValues();
  69.                         cv.put(DbSchema.SiteSchema.COLUMN_LABEL, label);
  70.                         cv.put(DbSchema.SiteSchema.COLUMN_IP, ip);
  71.                         cv.put(DbSchema.SiteSchema.COLUMN_PORT, port);
  72.                         values.add(cv);
  73.                     } catch(Exception e) {
  74.                         //couldn't get some stuff
  75.                     }
  76.                 } while(c.moveToNext());
  77.                
  78.                 //now remove the old table
  79.                 db.execSQL(DbSchema.SiteSchema.DROP_TABLE);
  80.                 db.execSQL(DbSchema.SiteSchema.CREATE_TABLE);
  81.                
  82.                 //re-enter old data
  83.                 for(int i=0; i<values.size(); i++) {
  84.                     try {
  85.                         db.insert(DbSchema.SiteSchema.TABLE_NAME, null, values.get(i));
  86.                     } catch(Exception e) {
  87.                         //
  88.                     }
  89.                 }
  90.             }
  91.         } catch(Exception e) {
  92.             if(c==null || c.getCount()<1) {
  93.                 db.execSQL(DbSchema.SiteSchema.DROP_TABLE);
  94.                 db.execSQL(DbSchema.SiteSchema.CREATE_TABLE);
  95.             }
  96.         }
  97.     }
  98. }
  99.  
  100.  
  101. /////////// database structure (DbSchema.java)
  102. package com.conceptualsystems.dashboard;
  103.  
  104. import android.provider.BaseColumns;
  105.  
  106. public final class DbSchema{
  107.     public static final String DATABASE_NAME = "smsdashboard";
  108.     public static final int DATABASE_VERSION = 24;
  109.     public static final String SORT_ASC = " ASC";
  110.     public static final String SORT_DESC = " DESC";
  111.     public static final String[] ORDERS = {SORT_ASC,SORT_DESC};
  112.     public static final int OFF = 0;
  113.     public static final int ON = 1;
  114.    
  115.     public static final class SiteSchema implements BaseColumns {
  116.         public static final String TABLE_NAME = "site";
  117.         public static final String COLUMN_LABEL = "label";
  118.         public static final String COLUMN_IP = "ip";
  119.         public static final String COLUMN_PORT = "port";
  120.         public static final String COLUMN_ACTIVATION_CODE = "code";
  121.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_LABEL + " TEXT NOT NULL," + COLUMN_IP + " TEXT NOT NULL," + COLUMN_ACTIVATION_CODE + " TEXT," + COLUMN_PORT + " TEXT NOT NULL);";
  122.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  123.     }
  124.    
  125.     public static final class ScheduleSchema implements BaseColumns {
  126.         public static final String TABLE_NAME = "schedule";
  127.         public static final String COLUMN_SITE_ID = "site_id";
  128.         public static final String COLUMN_MONDAY = "monday";
  129.         public static final String COLUMN_TUESDAY = "tuesday";
  130.         public static final String COLUMN_WEDNESDAY = "wednesday";
  131.         public static final String COLUMN_THURSDAY = "thursday";
  132.         public static final String COLUMN_FRIDAY = "friday";
  133.         public static final String COLUMN_SATURDAY = "saturday";
  134.         public static final String COLUMN_SUNDAY = "sunday";
  135.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_SITE_ID + " TEXT NOT NULL," + COLUMN_MONDAY + " TEXT NOT NULL," + COLUMN_TUESDAY + " TEXT NOT NULL," + COLUMN_WEDNESDAY + " TEXT NOT NULL," + COLUMN_THURSDAY + " TEXT NOT NULL," + COLUMN_FRIDAY + " TEXT NOT NULL," + COLUMN_SATURDAY + " TEXT NOT NULL," + COLUMN_SUNDAY + " TEXT NOT NULL);";
  136.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  137.     }
  138.    
  139.     /*
  140.     public static final class InboundSchema implements BaseColumns {
  141.         public static final String TABLE_NAME = "inbound";
  142.         public static final String COLUMN_NAME = "ProductName";
  143.         public static final String COLUMN_COUNT = "Count";
  144.         public static final String COLUMN_TOTAL = "Total";
  145.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COUNT + " LONG NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  146.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  147.     }
  148.     */
  149.     public static final class InboundSchema implements BaseColumns {
  150.         public static final String TABLE_NAME = "inbound";
  151.         public static final String COLUMN_NAME = "ProductName";
  152.         public static final String COLUMN_UOM = "UOM";
  153.         public static final String COLUMN_TOTAL = "Total";
  154.         public static final String COLUMN_UNITS = "Units";
  155.         public static final String COLUMN_COG = "COG";
  156.         public static final String COLUMN_PROJECTION_UNITS = "pUnits";
  157.         public static final String COLUMN_PROJECTION_TOTAL = "pTotal";
  158.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COG + " CURRENCY NOT NULL," + COLUMN_UNITS + " FLOAT NOT NULL,"+ COLUMN_PROJECTION_UNITS + " FLOAT,"+ COLUMN_PROJECTION_TOTAL + " CURRENCY," + COLUMN_UOM + " TEXT NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  159.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  160.     }
  161.    
  162.    
  163.     public static final class OutboundSchema implements BaseColumns {
  164.         public static final String TABLE_NAME = "outbound";
  165.         public static final String COLUMN_NAME = "ProductName";
  166.         public static final String COLUMN_UOM = "UOM";
  167.         public static final String COLUMN_TOTAL = "Total";
  168.         public static final String COLUMN_UNITS = "Units";
  169.         public static final String COLUMN_COG = "COG";
  170.         public static final String COLUMN_PROJECTION_UNITS = "pUnits";
  171.         public static final String COLUMN_PROJECTION_TOTAL = "pTotal";
  172.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COG + " CURRENCY NOT NULL," + COLUMN_UNITS + " FLOAT NOT NULL,"+ COLUMN_PROJECTION_UNITS + " FLOAT,"+ COLUMN_PROJECTION_TOTAL + " CURRENCY," + COLUMN_UOM + " TEXT NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  173.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  174.     }
  175.    
  176.     public static final class CheckbookSchema implements BaseColumns {
  177.         public static final String TABLE_NAME = "checkbook";
  178.         public static final String COLUMN_NAME = "CheckPayToOrder";
  179.         public static final String COLUMN_COUNT = "Count";
  180.         public static final String COLUMN_TOTAL = "Total";
  181.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COUNT + " LONG NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  182.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  183.     }
  184.    
  185.     public static final class OnholdSchema implements BaseColumns {
  186.         public static final String TABLE_NAME = "onhold";
  187.         public static final String COLUMN_NAME = "CustomerName";
  188.         public static final String COLUMN_UOM = "UOM";
  189.         public static final String COLUMN_TOTAL = "Total";
  190.         public static final String COLUMN_UNITS = "Units";
  191.         public static final String COLUMN_COG = "COG";
  192.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COG + " CURRENCY NOT NULL," + COLUMN_UNITS + " FLOAT NOT NULL," + COLUMN_UOM + " TEXT NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  193.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  194.     }
  195.    
  196.     public static final class ReconciliationSchema implements BaseColumns {
  197.         public static final String TABLE_NAME = "reconciliation";
  198.         public static final String COLUMN_NAME = "CustomerName";
  199.         public static final String COLUMN_UOM = "UOM";
  200.         public static final String COLUMN_TOTAL = "Total";
  201.         public static final String COLUMN_UNITS = "Units";
  202.         public static final String COLUMN_COG = "COG";
  203.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COG + " CURRENCY NOT NULL," + COLUMN_UNITS + " FLOAT NOT NULL," + COLUMN_UOM + " TEXT NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  204.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  205.     }
  206.    
  207.     public static final class OnyardSchema implements BaseColumns {
  208.         public static final String TABLE_NAME = "onyard";
  209.         public static final String COLUMN_NAME = "CustomerName";
  210.         public static final String COLUMN_UOM = "UOM";
  211.         public static final String COLUMN_TOTAL = "Total";
  212.         public static final String COLUMN_UNITS = "Units";
  213.         public static final String COLUMN_COG = "COG";
  214.         public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_TOTAL + " CURRENCY NOT NULL," + COLUMN_COG + " CURRENCY NOT NULL," + COLUMN_UNITS + " FLOAT NOT NULL," + COLUMN_UOM + " TEXT NOT NULL," + COLUMN_NAME + " TEXT NOT NULL);";
  215.         public static final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
  216.     }
  217. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!