public boolean isTableExtant(String tableName) {
mDatabase =
SQLiteDatabase.openDatabase(
DATABASE_PATH + DATABASE_NAME, null, SQLiteDatabase.OPEN_READONLY);
boolean openDb = mDatabase.isOpen();
if (openDb) {
if (mDatabase == null || !mDatabase.isOpen()) {
mDatabase = getReadableDatabase();
}
if (!mDatabase.isReadOnly()) {
mDatabase.close();
mDatabase = getReadableDatabase();
}
}
Cursor cursor =
mDatabase.rawQuery(
"select DISTINCT tbl_name from sqlite_master where tbl_name = '" + tableName + "'",
null);
if (cursor != null) {
if (cursor.getCount() > 0) { // table does exist
cursor.close();
mDatabase.close();
return true;
}
cursor.close();
}
mDatabase.close();
return false;
}
public int deleteSamples(Set<Long> rowids) {
int ret = 0;
try {
synchronized (dbLock) {
if (db == null || !db.isOpen()) {
db = helper.getWritableDatabase();
}
StringBuilder sb = new StringBuilder();
int i = 0;
sb.append("(");
for (Long rowid : rowids) {
sb.append("" + rowid);
i++;
if (i != rowids.size()) {
sb.append(", ");
}
}
sb.append(")");
if (Constants.DEBUG) Log.d("CaratSampleDB", "Deleting where rowid in " + sb.toString());
ret = delete("rowid in " + sb.toString(), null);
if (db != null && db.isOpen()) {
db.close();
}
}
} catch (Throwable th) {
Log.e(TAG, "Failed to delete samples!", th);
}
return ret;
}
@Test
public void testClose() throws Exception {
SQLiteDatabase database = helper.getWritableDatabase();
assertThat(database.isOpen(), equalTo(true));
helper.close();
assertThat(database.isOpen(), equalTo(false));
}
@Override
public void onOpen(SQLiteDatabase sqLiteDatabase) {
if (L)
Log.d(
Tag,
"onOpen SQLiteDatabase: " + "Db: " + sqLiteDatabase.toString() + sqLiteDatabase.isOpen());
if (!sqLiteDatabase.isOpen())
SQLiteDatabase.openDatabase(sqLiteDatabase.getPath(), null, SQLiteDatabase.OPEN_READWRITE);
}
/**
* @param context the {@link Context} to use.
* @return the db.
* @throws IOException if something goes wrong.
*/
public SQLiteDatabase getDatabase(Context context) throws IOException {
File databaseFile;
try {
databaseFile = ResourcesManager.getInstance(context).getDatabaseFile();
} catch (Exception e) {
throw new IOException(e.getLocalizedMessage());
}
if (databaseHelper == null || !databaseFile.exists()) {
databaseHelper = new DatabaseOpenHelper(databaseFile);
SQLiteDatabase db = databaseHelper.getWritableDatabase(context);
if (GPLog.LOG_ANDROID) {
Log.i(DEBUG_TAG, "Database: " + db.getPath());
Log.i(DEBUG_TAG, "Database Version: " + db.getVersion());
Log.i(DEBUG_TAG, "Database Page Size: " + db.getPageSize());
Log.i(DEBUG_TAG, "Database Max Size: " + db.getMaximumSize());
Log.i(DEBUG_TAG, "Database Open? " + db.isOpen());
Log.i(DEBUG_TAG, "Database readonly? " + db.isReadOnly());
Log.i(DEBUG_TAG, "Database Locked by current thread? " + db.isDbLockedByCurrentThread());
}
}
return databaseHelper.getWritableDatabase(context);
}
/**
* @Title: refreshRecords @Description: refresh records in database
*
* @return boolean operation result
* @throws
*/
public boolean refreshRecords() {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql = "SELECT uid, date FROM logs ORDER BY date";
Cursor cursor = null;
try {
cursor = db.rawQuery(sql, null);
while (cursor.moveToNext()) {
String uid = cursor.getString(cursor.getColumnIndex("uid"));
long date = Long.parseLong(cursor.getString(cursor.getColumnIndex("date")));
if (!Utils.isFileExists(uid, date)) {
// updateRecordState(uid, RecordState.DELETED);
deleteLogByUid(uid);
}
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
if (db != null && db.isOpen()) {
db.close();
}
}
return true;
}
/**
* @Title: getContacts @Description: get contacts
*
* @param page page (10 items per page)
* @return ArrayList<MyContact>
* @throws
*/
public ArrayList<MyContact> getContacts(int page) {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
String sql =
"SELECT DISTINCT name, number FROM logs WHERE record_state_flag = "
+ RecordState.LOCAL.ordinal()
+ " ORDER BY date DESC limit "
+ (page * 10)
+ ", 10";
Cursor cursor = null;
ArrayList<MyContact> contacts = new ArrayList<MyContact>();
try {
cursor = db.rawQuery(sql, null);
while (cursor.moveToNext()) {
String name = cursor.getString(cursor.getColumnIndex("name"));
String number = cursor.getString(cursor.getColumnIndex("number"));
long date = getLatestContactDate(number);
int count = getContactCount(number);
contacts.add(new MyContact(name, number, date, count));
}
return contacts;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
if (db != null && db.isOpen()) {
db.close();
}
}
}
@Override
public void open() throws SQLException {
if (database != null) {
if (database.isOpen()) database.close();
}
database = dbHelper.getWritableDatabase();
}
/** Query entries from the database */
@Override
public Cursor query(
Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
if (database == null || !database.isOpen()) database = databaseHelper.getWritableDatabase();
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
switch (sUriMatcher.match(uri)) {
case SENSOR_DEV:
qb.setTables(DATABASE_TABLES[0]);
qb.setProjectionMap(sensorDeviceMap);
break;
case SENSOR_DATA:
qb.setTables(DATABASE_TABLES[1]);
qb.setProjectionMap(sensorDataMap);
break;
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
try {
Cursor c = qb.query(database, projection, selection, selectionArgs, null, null, sortOrder);
c.setNotificationUri(getContext().getContentResolver(), uri);
return c;
} catch (IllegalStateException e) {
if (Aware.DEBUG) Log.e(Aware.TAG, e.getMessage());
return null;
}
}
public static void deactivate() {
if (null != db && db.isOpen()) {
db.close();
}
db = null;
instance = null;
}
private static ArrayList<SystemSetting> select(String whereClause, String[] selectionArgs) {
ArrayList<SystemSetting> settings = new ArrayList<>();
DataAccess da = new DataAccess();
SQLiteDatabase db = da.getReadableDB();
Cursor cursor = null;
try {
String query = "Select * From " + TableName + " " + whereClause;
cursor = db.rawQuery(query, selectionArgs);
if (cursor != null && cursor.moveToFirst()) {
do {
SystemSetting systemSetting =
new SystemSetting(
cursor.getInt(cursor.getColumnIndex(Id)),
cursor.getInt(cursor.getColumnIndex(Installed)) == 1,
cursor.getString(cursor.getColumnIndex(RingingToneUri)),
cursor.getString(cursor.getColumnIndex(AlarmRingingTone)),
cursor.getInt(cursor.getColumnIndex(VibrateInAlarms)) == 1,
cursor.getInt(cursor.getColumnIndex(SoundInAlarms)) == 1);
settings.add(systemSetting);
} while (cursor.moveToNext());
}
} catch (MyRuntimeException e) {
e.printStackTrace();
} finally {
if (cursor != null && !cursor.isClosed()) cursor.close();
if (db != null && db.isOpen()) db.close();
}
return settings;
}
private synchronized SQLiteDatabase getReadableDatabase() {
if (db != null && db.isOpen()) return db;
db = SQLiteDatabase.openDatabase(DATABASE_FILE, null, SQLiteDatabase.OPEN_READONLY);
return db;
}
private Cursor query(
SQLiteDatabase db,
String table,
String[] columns,
String selection,
String[] selectionArgs,
String groupBy,
String having,
String orderBy,
String limit)
throws Exception {
Cursor c = null;
// make sure getting settings does not fail because of IllegalStateException (db already closed)
boolean success = false;
for (int i = 0; success == false && i < RETRY_QUERY_COUNT; i++) {
try {
if (c != null) c.close();
c = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
success = true;
} catch (IllegalStateException e) {
success = false;
if (db != null && db.isOpen()) db.close();
db = getReadableDatabase();
}
}
if (success == false) throw new Exception("query - failed to execute query on the DB");
return c;
}
/** // * 更新MSG */
public void updateMsg(String dialogid, String msg) {
try {
String sql;
if ((Conf.UID + Conf.OPUID).equals(dialogid)) {
sql =
"UPDATE "
+ TABLENAME
+ " SET logintime = datetime(CURRENT_TIMESTAMP,'localtime') , msg='"
+ msg
+ "' WHERE dialogid='"
+ dialogid
+ "'";
} else {
sql =
"UPDATE "
+ TABLENAME
+ " SET logintime = datetime(CURRENT_TIMESTAMP,'localtime') , msg='"
+ msg
+ "',msgnums=msgnums+1 WHERE dialogid='"
+ dialogid
+ "'";
}
if (db.isOpen()) {
db.execSQL(sql);
}
db.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
/** addDialog */
public void addDialog(BaseJson user) {
try {
Cursor cursor =
db.rawQuery("SELECT * FROM " + TABLENAME + " WHERE uid='" + user.getUid() + "'", null);
boolean flag = false;
while (cursor.moveToNext()) {
flag = true;
}
if (!flag) {
db.execSQL(
"insert into "
+ TABLENAME
+ "(dialogid,uid,nickname,icon,msg,msgnums, logintime) values(?,?,?,?,?,?,datetime(CURRENT_TIMESTAMP,'localtime'))",
new Object[] {
Conf.UID + user.getUid(), user.getUid(), user.getName(), user.getIcon(), "", 0
});
} else {
String sql = "UPDATE " + TABLENAME + " SET msgnums=0 WHERE uid='" + user.getUid() + "'";
if (db.isOpen()) {
db.execSQL(sql);
}
}
cursor.close();
db.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
/**
* Genera el lista de preguntas obtenidas aleatoriamente.
*
* @return Listado de traducciones para las preguntas.
*/
private List<Traduccion> generarPreguntas() {
VocabularioSQLHelper vocabularioH =
new VocabularioSQLHelper(this, "vocabularioBD", null, version);
SQLiteDatabase vocabularioBD = vocabularioH.getWritableDatabase();
List<Traduccion> listaCompleta = new ArrayList<Traduccion>();
Traduccion t = null;
int numElem = 0;
/* Obtener todas las palabras del directorio. */
if (vocabularioBD != null && vocabularioBD.isOpen()) {
vocabularioBD.execSQL("DELETE FROM tErrores");
Cursor c =
vocabularioBD.rawQuery(
"SELECT palabra, traduccion "
+ "FROM tPalabras WHERE "
+ "carpeta = '"
+ carpeta
+ "' AND "
+ "idioma = '"
+ idioma
+ "'",
null);
while (c.moveToNext()) {
t = new Traduccion(c.getString(0), c.getString(1));
listaCompleta.add(t);
}
}
vocabularioBD.close();
numElem = listaCompleta.size();
if (numElem <= totalPreguntas) totalPreguntas = numElem;
return seleccionarPalabras(listaCompleta, obtenerAleatorios(totalPreguntas, numElem));
}
/** Insert entry to the database */
@Override
public Uri insert(Uri uri, ContentValues initialValues) {
if (database == null || !database.isOpen()) database = databaseHelper.getWritableDatabase();
ContentValues values =
(initialValues != null) ? new ContentValues(initialValues) : new ContentValues();
switch (sUriMatcher.match(uri)) {
case SENSOR_DEV:
long accel_id = database.insert(DATABASE_TABLES[0], Gravity_Sensor.DEVICE_ID, values);
if (accel_id > 0) {
Uri accelUri = ContentUris.withAppendedId(Gravity_Sensor.CONTENT_URI, accel_id);
getContext().getContentResolver().notifyChange(accelUri, null);
return accelUri;
}
throw new SQLException("Failed to insert row into " + uri);
case SENSOR_DATA:
long accelData_id = database.insert(DATABASE_TABLES[1], Gravity_Data.DEVICE_ID, values);
if (accelData_id > 0) {
Uri accelDataUri = ContentUris.withAppendedId(Gravity_Data.CONTENT_URI, accelData_id);
getContext().getContentResolver().notifyChange(accelDataUri, null);
return accelDataUri;
}
throw new SQLException("Failed to insert row into " + uri);
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
}
/** Close the meta-db. */
public static void closeDB() {
if (mMetaDb != null && mMetaDb.isOpen()) {
mMetaDb.close();
mMetaDb = null;
Timber.d("Closing MetaDB");
}
}
public int countSamples() {
try {
synchronized (dbLock) {
if (db == null || !db.isOpen()) {
try {
db = helper.getWritableDatabase();
} catch (android.database.sqlite.SQLiteException ex) {
Log.e(TAG, "Could not open database", ex);
return -1;
}
}
Cursor cursor = db.rawQuery("select count(timestamp) FROM " + SAMPLES_VIRTUAL_TABLE, null);
if (cursor == null) {
// There are no results
return -1;
} else {
int ret = -1;
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
ret = cursor.getInt(0);
cursor.moveToNext();
}
cursor.close();
return ret;
}
}
} catch (Throwable th) {
Log.e(TAG, "Failed to query oldest samples!", th);
}
return -1;
}
// 打开SQLite数据库连接
public SQLiteDatabase openConnection() {
if (!db.isOpen()) {
// 读写方式获取SQLiteDatabase
db = getWritableDatabase();
}
return db;
}
public long insertGPS(
String device_id,
String time,
String latitude,
String longitude,
String altitude,
String bearing,
String accuracy,
String provider,
String speed,
int i) {
if (db.isOpen() == false) return 0;
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_ROWID1, device_id);
initialValues.put(KEY_TIME1, time);
initialValues.put(KEY_LAT, latitude);
initialValues.put(KEY_LONG, longitude);
initialValues.put(KEY_ALT, altitude);
initialValues.put(KEY_BEARING, bearing);
initialValues.put(KEY_ACCURACY, accuracy);
initialValues.put(KEY_PROVIDER, provider);
initialValues.put(KEY_SPEED, speed);
Log.i(device_id.toString(), time.toString());
return db.insert(DATABASE_TABLE[i], null, initialValues);
}
public void closeConnection() {
try {
if (db != null && db.isOpen()) db.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @Title: insertNewLog @Description: insert new log to database
*
* @param callLog MyCallLog instance
* @param recordFlagState is record exists
* @param recordState record storage state
* @return boolean operation result
* @throws
*/
public boolean insertNewLog(
MyCallLog callLog, RecordFlagState recordFlagState, RecordState recordState) {
SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
String sql =
"INSERT INTO logs (name, number, duration, date, type, uid, record_flag, log_state_flag, record_state_flag) VALUES(?, ?, ?, ?, ?, ?, ?, 0, ?)";
try {
db.execSQL(
sql,
new Object[] {
callLog.getName(),
callLog.getNumber(),
callLog.getDuration(),
callLog.getDate(),
callLog.getCallType().ordinal(),
Check.getCRC32(callLog),
recordFlagState.ordinal(),
recordState.ordinal()
});
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (db != null && db.isOpen()) {
db.close();
}
}
return true;
}
/** Closes a previously opened database connection. */
public void closeDatabase() {
if (mDatabase != null) {
mDatabase.close();
Timber.d("closeDatabase, database %s closed = %s", mDatabase.getPath(), !mDatabase.isOpen());
mDatabase = null;
}
}
/**
* @Title: updateRecordFlags @Description: check existence of those records, and update record
* flags in database.
*
* @return boolean operation result
* @throws
*/
@Deprecated
public boolean updateRecordFlags() {
SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
String sql = "SELECT uid, date, record_flag FROM logs ORDER BY date DESC";
Cursor cursor = null;
try {
cursor = db.rawQuery(sql, null);
while (cursor.moveToNext()) {
String uid = cursor.getString(cursor.getColumnIndex("uid"));
long date = Long.parseLong(cursor.getString(cursor.getColumnIndex("date")));
int recordFlag = cursor.getInt(cursor.getColumnIndex("record_flag"));
boolean fileExists = Utils.isFileExists(uid, date);
if (recordFlag == 0 && fileExists) {
updateRecordFlag(uid, RecordFlagState.EXIST);
updateRecordState(uid, RecordState.LOCAL);
} else if (recordFlag == 1 && !fileExists) {
updateRecordFlag(uid, RecordFlagState.NOT_EXIST);
}
}
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
if (cursor != null && !cursor.isClosed()) {
cursor.close();
}
if (db != null && db.isOpen()) {
db.close();
}
}
return true;
}
private static ArrayList<Application> select(String whereClause, String[] selectionArgs) {
ArrayList<Application> applications = new ArrayList<>();
DataAccess da = new DataAccess();
SQLiteDatabase db = da.getReadableDB();
Cursor cursor = null;
try {
String query = "Select * From " + TableName + " " + whereClause;
cursor = db.rawQuery(query, selectionArgs);
if (cursor != null && cursor.moveToFirst()) {
do {
Application app =
new Application(
cursor.getInt(cursor.getColumnIndex(Id)),
cursor.getInt(cursor.getColumnIndex(Uid)),
cursor.getString(cursor.getColumnIndex(AppName)),
cursor.getString(cursor.getColumnIndex(PackageName)),
cursor.getInt(cursor.getColumnIndex(Removed)) == 1);
applications.add(app);
} while (cursor.moveToNext());
}
} catch (MyRuntimeException e) {
e.printStackTrace();
} finally {
if (cursor != null && !cursor.isClosed()) cursor.close();
if (db != null && db.isOpen()) db.close();
}
return applications;
}
public void close() {
if (!db.isOpen()) {
return;
}
db.close();
db = null;
}
public void checkDB() {
if (!db.isOpen()) {
OpenHelper openHelper = new OpenHelper(this.context);
this.db = openHelper.getWritableDatabase();
}
}
public static SQLiteDatabase getDatabase(Context context) {
if (database == null || !database.isOpen()) {
database = new DBHelper(context, DATABASE_NAME, null, VERSION).getWritableDatabase();
}
return database;
}
public void deactivate() {
if (null != database && database.isOpen()) {
database.close();
}
database = null;
instance = null;
}