workspace/JuggleMaster/src/com/jm/db/Dao.java

   1 package com.jm.db;\r
   2 \r
   3 import java.io.ByteArrayOutputStream;\r
   4 import java.io.IOException;\r
   5 import java.io.InputStream;\r
   6 import java.util.ArrayList;\r
   7 import java.util.List;\r
   8 \r
   9 import android.content.ContentValues;\r
  10 import android.content.Context;\r
  11 import android.database.Cursor;\r
  12 import android.database.SQLException;\r
  13 import android.database.sqlite.SQLiteDatabase;\r
  14 import android.database.sqlite.SQLiteStatement;\r
  15 \r
  16 import com.jm.JmPattern;\r
  17 import com.jm.R;\r
  18 import com.jm.common.Constant;\r
  19 import com.jm.pref.EditPrefUtil;\r
  20 import com.jm.utility.Debug;\r
  21 import com.jm.utility.JmException;\r
  22 \r
  23 public class Dao implements IDao<SQLiteDatabase> {\r
  24         private static Dao instance = new Dao();\r
  25         private SQLiteStatement insertStmt = null;\r
  26         private SQLiteStatement updateStmt = null;\r
  27         private SQLiteStatement deleteStmt = null;\r
  28         private SQLiteStatement patternFileDeleteStmt = null;\r
  29 \r
  30         private Context context;\r
  31 \r
  32         public static Dao getInstance() {\r
  33                 return instance;\r
  34         }\r
  35 \r
  36         private Dao() {\r
  37 \r
  38         }\r
  39 \r
  40         public void setContext(Context context) {\r
  41                 this.context = context;\r
  42         }\r
  43 \r
  44         public void init(SQLiteDatabase db, Context context) {\r
  45                 this.context = context;\r
  46 \r
  47                 db.execSQL("create table pattern (" + "id integer primary key, "\r
  48                                 + "type integer not null, " + "siteswap text not null, "\r
  49                                 + "height integer, " + "dwell integer, " + "name integer, "\r
  50                                 + "motion text," + "lang integer," + "idx integer);");\r
  51                 init2to3(db);\r
  52                 start(db);\r
  53                 initData2to3(db, context);\r
  54         }\r
  55 \r
  56         public void initData2to3(SQLiteDatabase db, Context context) {\r
  57                 long id;\r
  58                 id = initPatternList(db, "pattern.jm", false);\r
  59                 initPatternList(db, "pattern_ja.jm", false);\r
  60 \r
  61                 EditPrefUtil pref = new EditPrefUtil(context);\r
  62                 pref.put(Constant.PREF_SELECTED_PATTERN_INDEX, id);\r
  63                 pref.update();\r
  64         }\r
  65 \r
  66         public void init2to3(SQLiteDatabase db) {\r
  67                 db.execSQL("create table pattern_file ("\r
  68                                 + android.provider.BaseColumns._ID\r
  69                                 + " integer primary key autoincrement, "\r
  70                                 + "name text not null, " + "value blob not null, "\r
  71                                 + "iswritable integer not null);");\r
  72         }\r
  73 \r
  74         public void start(SQLiteDatabase db) {\r
  75                 insertStmt = db.compileStatement("insert into pattern ("\r
  76                                 + "type, siteswap, height, dwell, name, motion, lang, idx"\r
  77                                 + ") values (" + "?, ?, ?, ?, ?, ?, ?, ?" + ");");\r
  78                 updateStmt = db.compileStatement("update pattern set "\r
  79                                 + "siteswap = ?, height = ?, dwell = ?, name = ?, motion = ? "\r
  80                                 + "where id = ? and lang = ?;");\r
  81                 deleteStmt = db\r
  82                                 .compileStatement("delete from pattern where id = ? and lang = ?;");\r
  83                 patternFileDeleteStmt = db\r
  84                                 .compileStatement("delete from pattern_file where "\r
  85                                                 + android.provider.BaseColumns._ID + " = ?;");\r
  86         }\r
  87 \r
  88         public void add(JmPattern jp, int index) throws JmException {\r
  89                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
  90                 add(jp, helper.getLangId(), index);\r
  91         }\r
  92 \r
  93         public void addNT(JmPattern jp, int index) throws JmException {\r
  94                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
  95                 addNT(jp, helper.getLangId(), index);\r
  96         }\r
  97 \r
  98         public void add(JmPattern jp, int lang, int index) throws JmException {\r
  99                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 100                 SQLiteDatabase db = helper.getWritableDatabase();\r
 101                 db.beginTransaction();\r
 102                 try {\r
 103                         int i = 1;\r
 104                         SQLiteStatement stmt = insertStmt;\r
 105                         stmt.bindLong(i++, jp.getType());\r
 106                         stmt.bindString(i++, jp.getSiteSwap().toString());\r
 107                         stmt.bindLong(i++, jp.getHeight());\r
 108                         stmt.bindLong(i++, jp.getDwell());\r
 109                         stmt.bindString(i++, jp.getName());\r
 110                         stmt.bindString(i++, jp.motionToString());\r
 111                         stmt.bindLong(i++, lang);\r
 112                         stmt.bindLong(i++, index);\r
 113                         stmt.executeInsert();\r
 114                         db.setTransactionSuccessful();\r
 115                 } catch (SQLException e) {\r
 116                         throw new JmException(e);\r
 117                 } finally {\r
 118                         db.endTransaction();\r
 119                 }\r
 120         }\r
 121 \r
 122         public void addNT(JmPattern jp, int lang, int index) throws JmException {\r
 123                 try {\r
 124                         int i = 1;\r
 125                         SQLiteStatement stmt = insertStmt;\r
 126                         stmt.bindLong(i++, jp.getType());\r
 127                         stmt.bindString(i++, jp.getSiteSwap().toString());\r
 128                         stmt.bindLong(i++, jp.getHeight());\r
 129                         stmt.bindLong(i++, jp.getDwell());\r
 130                         stmt.bindString(i++, jp.getName());\r
 131                         stmt.bindString(i++, jp.motionToString());\r
 132                         stmt.bindLong(i++, lang);\r
 133                         stmt.bindLong(i++, index);\r
 134                         stmt.executeInsert();\r
 135                 } catch (SQLException e) {\r
 136                         throw new JmException(e);\r
 137                 }\r
 138         }\r
 139 \r
 140         public void set(JmPattern jp) throws JmException {\r
 141                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 142                 SQLiteDatabase db = helper.getWritableDatabase();\r
 143                 db.beginTransaction();\r
 144                 try {\r
 145                         int i = 1;\r
 146                         SQLiteStatement stmt = updateStmt;\r
 147                         stmt.bindString(i++, jp.getSiteSwap().toString());\r
 148                         stmt.bindLong(i++, jp.getHeight());\r
 149                         stmt.bindLong(i++, jp.getDwell());\r
 150                         stmt.bindString(i++, jp.getName());\r
 151                         stmt.bindString(i++, jp.motionToString());\r
 152                         stmt.bindLong(i++, jp.getId());\r
 153                         stmt.bindLong(i++, helper.getLangId());\r
 154                         stmt.execute();\r
 155                         db.setTransactionSuccessful();\r
 156                 } catch (SQLException e) {\r
 157                         throw new JmException(e);\r
 158                 } finally {\r
 159                         db.endTransaction();\r
 160                 }\r
 161         }\r
 162 \r
 163         public void delete(int id) throws JmException {\r
 164                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 165                 SQLiteDatabase db = helper.getWritableDatabase();\r
 166                 db.beginTransaction();\r
 167                 try {\r
 168                         int i = 1;\r
 169                         SQLiteStatement stmt = deleteStmt;\r
 170                         stmt.bindLong(i++, id);\r
 171                         stmt.bindLong(i++, helper.getLangId());\r
 172                         stmt.execute();\r
 173                         db.setTransactionSuccessful();\r
 174                 } catch (SQLException e) {\r
 175                         throw new JmException(e);\r
 176                 } finally {\r
 177                         db.endTransaction();\r
 178                 }\r
 179         }\r
 180 \r
 181         public JmPattern[] get(int type) throws JmException {\r
 182                 JmPattern[] list = null;\r
 183                 try {\r
 184                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 185                         SQLiteDatabase db = helper.getReadableDatabase();\r
 186                         list = get(db, type);\r
 187                 } catch (SQLException e) {\r
 188                         throw new JmException(e);\r
 189                 }\r
 190                 return list;\r
 191         }\r
 192 \r
 193         public JmPattern[] get(SQLiteDatabase db, int type) throws JmException {\r
 194                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 195                 String selection = "lang = " + helper.getLangId() + " and type = "\r
 196                                 + type;\r
 197                 String orderBy = "idx";\r
 198                 return get(db, selection, orderBy);\r
 199         }\r
 200 \r
 201         public JmPattern[] get(SQLiteDatabase db, String selection, String orderBy)\r
 202                         throws JmException {\r
 203                 //DatabaseHelper helper = DatabaseHelper.getInstance();\r
 204                 JmPattern[] list = null;\r
 205                 Cursor c = db.query("pattern", new String[] { "id", "type", "siteswap",\r
 206                                 "height", "dwell", "name", "motion" }, selection, null, null,\r
 207                                 null, orderBy);\r
 208                 c.moveToFirst();\r
 209                 list = new JmPattern[c.getCount()];\r
 210                 for (int i = 0; i < list.length; i++) {\r
 211                         JmPattern jp = new JmPattern(c.getInt(0), // id\r
 212                                         c.getInt(1), // type\r
 213                                         c.getString(5), // name\r
 214                                         c.getString(2), // siteswap\r
 215                                         c.getInt(3), // height\r
 216                                         c.getInt(4), // dwell\r
 217                                         JmPattern.getMotion(c.getString(6))); // motions\r
 218                         list[i] = jp;\r
 219                         c.moveToNext();\r
 220                 }\r
 221                 c.close();\r
 222                 return list;\r
 223         }\r
 224 \r
 225         public JmPattern[] getFromId(long id) throws JmException {\r
 226                 JmPattern[] list = null;\r
 227                 try {\r
 228                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 229                         SQLiteDatabase db = helper.getReadableDatabase();\r
 230                         String selection = "id = " + id + " and lang = "\r
 231                                         + helper.getLangId();\r
 232                         Cursor c = db.query("pattern", new String[] { "id", "type",\r
 233                                         "siteswap", "height", "dwell", "name", "motion" },\r
 234                                         selection, null, null, null, null);\r
 235                         c.moveToFirst();\r
 236                         list = new JmPattern[c.getCount()];\r
 237                         for (int i = 0; i < list.length; i++) {\r
 238                                 JmPattern jp = new JmPattern(c.getInt(0), // id\r
 239                                                 c.getInt(1), // type\r
 240                                                 c.getString(5), // name\r
 241                                                 c.getString(2), // siteswap\r
 242                                                 c.getInt(3), // height\r
 243                                                 c.getInt(4), // dwell\r
 244                                                 JmPattern.getMotion(c.getString(6))); // motions\r
 245                                 list[i] = jp;\r
 246                                 c.moveToNext();\r
 247                         }\r
 248                         c.close();\r
 249                 } catch (SQLException e) {\r
 250                         throw new JmException(e);\r
 251                 }\r
 252                 return list;\r
 253         }\r
 254 \r
 255         public int max(int type) throws JmException {\r
 256                 int m = -1;\r
 257                 try {\r
 258                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 259                         SQLiteDatabase db = helper.getReadableDatabase();\r
 260                         String sql = "select max(idx) from pattern where type = " + type\r
 261                                         + " and lang = " + helper.getLangId();\r
 262                         Cursor c = db.rawQuery(sql, null);\r
 263                         c.moveToFirst();\r
 264                         m = c.getInt(0);\r
 265                         c.close();\r
 266                 } catch (SQLException e) {\r
 267                         throw new JmException(e);\r
 268                 }\r
 269                 return m;\r
 270         }\r
 271 \r
 272         public int countAll() throws JmException {\r
 273                 int count = -1;\r
 274                 try {\r
 275                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 276                         SQLiteDatabase db = helper.getReadableDatabase();\r
 277                         String sql = "select count(*) from pattern;";\r
 278                         Cursor c = db.rawQuery(sql, null);\r
 279                         c.moveToFirst();\r
 280                         count = c.getInt(0); // count;\r
 281                         c.close();\r
 282                 } catch (SQLException e) {\r
 283                         throw new JmException(e);\r
 284                 }\r
 285                 return count;\r
 286         }\r
 287 \r
 288         public int count() throws JmException {\r
 289                 int count = -1;\r
 290                 try {\r
 291                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 292                         SQLiteDatabase db = helper.getReadableDatabase();\r
 293                         String sql = "select count(*) from pattern where lang = "\r
 294                                         + helper.getLangId() + ";";\r
 295                         Cursor c = db.rawQuery(sql, null);\r
 296                         c.moveToFirst();\r
 297                         count = c.getInt(0); // count;\r
 298                         c.close();\r
 299                 } catch (SQLException e) {\r
 300                         throw new JmException(e);\r
 301                 }\r
 302                 return count;\r
 303         }\r
 304 \r
 305         public int count(int type) throws JmException {\r
 306                 int count = -1;\r
 307                 try {\r
 308                         DatabaseHelper helper = DatabaseHelper.getInstance();\r
 309                         SQLiteDatabase db = helper.getReadableDatabase();\r
 310                         String sql = "select count(*) from pattern where type = " + type\r
 311                                         + " and lang = " + helper.getLangId() + ";";\r
 312                         Cursor c = db.rawQuery(sql, null);\r
 313                         c.moveToFirst();\r
 314                         count = c.getInt(0); // count;\r
 315                         c.close();\r
 316                 } catch (SQLException e) {\r
 317                         throw new JmException(e);\r
 318                 }\r
 319                 return count;\r
 320         }\r
 321 \r
 322         public PatternFile getPatternFile(long id) {\r
 323                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 324                 SQLiteDatabase db = helper.getReadableDatabase();\r
 325 \r
 326                 String selection = android.provider.BaseColumns._ID + " = ?";\r
 327                 String[] selectionArgs = { String.valueOf(id) };\r
 328                 String orderBy = null;\r
 329                 List<PatternFile> list = getPatternFile(db, selection, selectionArgs,\r
 330                                 orderBy, true);\r
 331                 if (list.size() <= 0) {\r
 332                         return null;\r
 333                 }\r
 334                 return list.get(0);\r
 335         }\r
 336 \r
 337         public List<PatternFile> getPatternFile() {\r
 338                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 339                 SQLiteDatabase db = helper.getReadableDatabase();\r
 340                 String orderBy = "name";\r
 341                 return getPatternFile(db, null, null, orderBy, false);\r
 342         }\r
 343 \r
 344         public List<PatternFile> getPatternFile(SQLiteDatabase db) {\r
 345                 return getPatternFile(db, null, null, null, false);\r
 346         }\r
 347 \r
 348         private List<PatternFile> getPatternFile(SQLiteDatabase db,\r
 349                         String selection, String[] selectionArgs, String orderBy,\r
 350                         boolean isBlob) {\r
 351                 List<PatternFile> list = new ArrayList<PatternFile>();\r
 352                 Cursor cursor = null;\r
 353                 try {\r
 354                         cursor = db.query("pattern_file", new String[] {\r
 355                                         android.provider.BaseColumns._ID, "name", "value",\r
 356                                         "iswritable" }, selection, selectionArgs, null, null,\r
 357                                         orderBy);\r
 358                         int size = cursor.getCount();\r
 359                         if (size > 0){\r
 360                                 cursor.moveToFirst();\r
 361                         }\r
 362                         for (int i = 0; i < size; i++) {\r
 363                                 PatternFile pf = new PatternFile(cursor.getLong(0),\r
 364                                                 cursor.getString(1), (isBlob) ? cursor.getBlob(2)\r
 365                                                                 : null, cursor.getInt(3) != 0);\r
 366                                 list.add(pf);\r
 367                                 cursor.moveToNext();\r
 368                         }\r
 369                 } catch (SQLException e) {\r
 370                         Debug.d(this, null, e);\r
 371                         list = null;\r
 372                 } catch (Exception e) {\r
 373                         Debug.d(this, null, e);\r
 374                         list = null;\r
 375                 } finally {\r
 376                         if (cursor != null) {\r
 377                                 cursor.close();\r
 378                                 cursor = null;\r
 379                         }\r
 380                 }\r
 381                 return list;\r
 382         }\r
 383 \r
 384         // public PatternFile[] getPatternFile(SQLiteDatabase db) throws JmException\r
 385         // {\r
 386         // String selection = null;\r
 387         // String orderBy = "name";\r
 388         // return getPatternFile(db, selection, orderBy);\r
 389         // }\r
 390         //\r
 391         // public PatternFile[] getPatternFile(SQLiteDatabase db, String selection,\r
 392         // String orderBy) throws JmException {\r
 393         // PatternFile[] list = null;\r
 394         // Cursor c = db.query("pattern_file", new String[] { "id", "name" },\r
 395         // selection, null, null, null, orderBy);\r
 396         // c.moveToFirst();\r
 397         // list = new PatternFile[c.getCount()];\r
 398         // for (int i = 0; i < list.length; i++) {\r
 399         // PatternFile pf = new PatternFile(c.getInt(0), // id\r
 400         // c.getString(1)); // name\r
 401         // list[i] = pf;\r
 402         // c.moveToNext();\r
 403         // }\r
 404         // c.close();\r
 405         // return list;\r
 406         // }\r
 407         //\r
 408         // public PatternFile getPatternFile(long id) {\r
 409         // String selection = android.provider.BaseColumns._ID + " = ?";\r
 410         // String[] selectionArgs = { String.valueOf(id) };\r
 411         // String orderBy = null;\r
 412         // List<PatternFile> list = getPatternFile(selection, selectionArgs,\r
 413         // orderBy);\r
 414         // if (list.size() <= 0) {\r
 415         // return null;\r
 416         // }\r
 417         // return list.get(0);\r
 418         // }\r
 419 \r
 420         public long addPatternFile(String name, byte[] blob, boolean isWritable) {\r
 421                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 422                 SQLiteDatabase db = helper.getWritableDatabase();\r
 423                 return addPatternFile(db, name, blob, isWritable);\r
 424         }\r
 425 \r
 426         public long addPatternFile(SQLiteDatabase db, String name, byte[] blob,\r
 427                         boolean isWritable) {\r
 428                 ContentValues values = new ContentValues();\r
 429                 values.put("name", name);\r
 430                 values.put("value", blob);\r
 431                 values.put("iswritable", isWritable ? 1 : 0);\r
 432 \r
 433                 return db.insert("pattern_file", null, values);\r
 434         }\r
 435 \r
 436         public void deletePatternFile(long id) throws JmException {\r
 437                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 438                 SQLiteDatabase db = helper.getWritableDatabase();\r
 439                 db.beginTransaction();\r
 440                 try {\r
 441                         int i = 1;\r
 442                         SQLiteStatement stmt = patternFileDeleteStmt;\r
 443                         stmt.bindLong(i++, id);\r
 444                         stmt.execute();\r
 445                         db.setTransactionSuccessful();\r
 446                 } catch (SQLException e) {\r
 447                         throw new JmException(e);\r
 448                 } finally {\r
 449                         db.endTransaction();\r
 450                 }\r
 451         }\r
 452 \r
 453         public String[] getMenu() {\r
 454                 String[] list = { context.getString(R.string.list0_1),\r
 455                                 context.getString(R.string.list0_2),\r
 456                                 context.getString(R.string.list0_3),\r
 457                                 context.getString(R.string.list0_4),\r
 458                                 context.getString(R.string.list0_5),\r
 459                                 context.getString(R.string.list0_6),\r
 460                                 context.getString(R.string.list0_7),\r
 461                                 context.getString(R.string.list0_8) };\r
 462                 return list;\r
 463 \r
 464         }\r
 465 \r
 466         public long initPatternList(SQLiteDatabase db, String filename,\r
 467                         boolean isWritable) {\r
 468                 InputStream is = null;\r
 469                 try {\r
 470                         is = getClass().getClassLoader().getResourceAsStream(filename);\r
 471                         return insertPatternList(db, is, filename, isWritable);\r
 472                 } finally {\r
 473                         if (is != null) {\r
 474                                 try {\r
 475                                         is.close();\r
 476                                 } catch (IOException e) {\r
 477                                         Debug.d(this, e.getMessage());\r
 478                                 }\r
 479                                 is = null;\r
 480                         }\r
 481                 }\r
 482         }\r
 483 \r
 484         public long insertPatternList(InputStream is, String name,\r
 485                         boolean isWritable) {\r
 486                 DatabaseHelper helper = DatabaseHelper.getInstance();\r
 487                 SQLiteDatabase db = helper.getWritableDatabase();\r
 488                 return insertPatternList(db, is, name, isWritable);\r
 489         }\r
 490 \r
 491         public long insertPatternList(SQLiteDatabase db, InputStream is,\r
 492                         String name, boolean isWritable) {\r
 493                 long id = -1;\r
 494                 try {\r
 495                         byte[] b = toByteArray(is);\r
 496                         if (b == null) {\r
 497                                 return id;\r
 498                         }\r
 499                         Dao dao = Dao.getInstance();\r
 500                         id = dao.addPatternFile(db, name, b, isWritable);\r
 501                 } finally {\r
 502                         close(is);\r
 503                 }\r
 504                 return id;\r
 505         }\r
 506 \r
 507         private void close(InputStream is) {\r
 508                 if (is != null) {\r
 509                         try {\r
 510                                 is.close();\r
 511                         } catch (IOException e) {\r
 512                                 Debug.d(this, e.getMessage());\r
 513                         }\r
 514                 }\r
 515         }\r
 516 \r
 517         private byte[] toByteArray(InputStream inputStream) {\r
 518                 ByteArrayOutputStream bout = new ByteArrayOutputStream();\r
 519                 byte[] buffer = new byte[1024];\r
 520                 try {\r
 521                         while (true) {\r
 522                                 int len;\r
 523                                 len = inputStream.read(buffer);\r
 524                                 if (len < 0) {\r
 525                                         break;\r
 526                                 }\r
 527                                 bout.write(buffer, 0, len);\r
 528                         }\r
 529                         return bout.toByteArray();\r
 530                 } catch (IOException e) {\r
 531                         Debug.d(this, e.getMessage());\r
 532                         return null;\r
 533                 }\r
 534         }\r
 535 \r
 536         @Override\r
 537         public boolean isWritable() {\r
 538                 return true;\r
 539         }\r
 540 }\r