codeiris/gist_sqlite_fast.java - master - laborgit/laborgit-android

codeiris/gist_sqlite_fast.java
278 строк9 KB
acer24@gmail.com
start intValueIfNull() SQLite cursor functions
05 авг 2021, 19:22
05 авг 2021, 19:224fb848d


//--->create string with args
// %s - string
// %d - int, long
// %f - float, double
// %b - boolean
// %c - char
// %t - date
// %% - %
String where = String.format(Locale.US, "%s = %d AND %s = %d", ROLTableEntry.COLUMN_NAME_l_Identifier, l_Identifier, ROLTableEntry.COLUMN_NAME_l_TypeActivity, l_TypeActivity);


//--->PRAGMA
database.executeFast("PRAGMA journal_size_limit = 0").stepThis().dispose();
database.executeFast("VACUUM").stepThis().dispose();
database.executeFast("PRAGMA journal_size_limit = -1").stepThis().dispose();

//--->CREATE TABLE
database.executeFast("CREATE TABLE messages(mid INTEGER PRIMARY KEY, uid INTEGER, read_state INTEGER, send_state INTEGER, date INTEGER, data BLOB, out INTEGER, ttl INTEGER, media INTEGER, replydata BLOB, imp INTEGER, mention INTEGER)").stepThis().dispose();
//--->CREATE INDEX
database.executeFast("CREATE INDEX IF NOT EXISTS uid_mid_idx_messages ON messages(uid, mid);").stepThis().dispose();


//--->DELETE (non retrive values)
database.executeFast("DELETE FROM messages_holes WHERE uid = " + did).stepThis().dispose();
database.executeFast(String.format(Locale.US, "DELETE FROM messages WHERE uid = %d AND mid <= %d", -channelId, maxMessageId)).stepThis().dispose();


//--->SELECT (retrive values)
int i_MainEvent = 0;
try {
	String table = ROLTableEntry.TABLE_NAME;
	String columns = ROLTableEntry.COLUMN_NAME_b_MainEvent;
	String where = String.format(Locale.US, "%s = %d AND %s = %d", ROLTableEntry.COLUMN_NAME_l_Identifier, l_Identifier, ROLTableEntry.COLUMN_NAME_l_TypeActivity, l_TypeActivity);
	String sql = String.format(Locale.US, "SELECT %s FROM %s WHERE %s", columns, table, where);
	SQLiteCursor cursor = getLocalSQLiteOpenHelper().getDatabase().queryFinalized(sql);
	if (cursor.next()) {
		i_MainEvent = cursor.intValue(0);
	}
	cursor.dispose();
} catch (Exception e) {
	FileLog.e(e);
}
return i_MainEvent == 1;


//--->UPDATE simple by string
storageQueue.postRunnable(() -> {
	try {
		database.executeFast("UPDATE messages SET send_state = 2 WHERE mid = " + messageId).stepThis().dispose();
			//or
		final ArrayList<Long> mids
		String midsStr = TextUtils.join(",", mids);
		database.executeFast(String.format(Locale.US, "UPDATE messages SET read_state = read_state | 2 WHERE mid IN (%s)", midsStr)).stepThis().dispose();

	} catch (Exception e) {
		FileLog.e(e);
	}
});


//--->UPDATE simple by state.step();
storageQueue.postRunnable(() -> {
	SQLitePreparedStatement state = null;
	try {
		state = database.executeFast("UPDATE enc_chats SET ttl = ? WHERE uid = ?");
		state.bindInteger(1, chat.ttl);
		state.bindInteger(2, chat.id);
		state.step();
	} catch (Exception e) {
		FileLog.e(e);
	} finally {
		if (state != null) {
			state.dispose();
		}
	}
});


//--->UPDATE by cycle
try {
	database.beginTransaction();
	if (dialogsToUpdate.size() > 0) {
		SQLitePreparedStatement state = database.executeFast("UPDATE dialogs SET unread_count = ? WHERE did = ?");
		for (int a = 0; a < dialogsToUpdate.size(); a++) {
			state.requery();
			state.bindInteger(1, dialogsToUpdate.valueAt(a));
			state.bindLong(2, dialogsToUpdate.keyAt(a));
			state.step();
		}
		state.dispose();
	}
	database.commitTransaction();
} catch (Exception e) {
	FileLog.e(e);
}


//--->INSERT INTO (non retrive values)
database.executeFast("INSERT INTO params VALUES(1, 0, 0, 0, 0, 0, 0, NULL)").stepThis().dispose();


//--->REPLACE INTO by simple string. Direct columns.
storageQueue.postRunnable(() -> {
	try {
		database.executeFast(String.format(Locale.US, "REPLACE INTO dialog_settings (column1, column2) VALUES(%d, %d)", did, flags)).stepThis().dispose();
	} catch (Exception e) {
		FileLog.e(e);
	}
});


//--->REPLACE INTO by cycle. All columns.
LongSparseArray<Long> ids = new LongSparseArray<>();
try {
	database.beginTransaction();
	SQLitePreparedStatement state = database.executeFast("REPLACE INTO dialog_settings VALUES(?, ?)");
	for (int a = 0; a < ids.size(); a++) {
		state.requery();
		state.bindLong(1, ids.keyAt(a));
		state.bindLong(2, ids.valueAt(a));
		state.step();
	}
	state.dispose();
	database.commitTransaction();
} catch (Exception e) {
	FileLog.e(e);
}


//--->REPLACE INTO. Save values in non processed columns. Good if it do by luuid.
SQLitePreparedStatement state = null;
try {
	// 1. Insert
	String table = MainEventsTableEntry.TABLE_NAME;
	String columns = MainEventsTableEntry.COLUMN_NAME_luuid;

	getLocalSQLiteOpenHelper().getDatabase().executeFast(String.format(Locale.US, "INSERT OR IGNORE INTO %s (%s) VALUES(%d)", table, columns, mainEvent.get_luuid())).stepThis().dispose();

	// 2. Update
	String set = "";
	set = set + "l_PlanningPeriod = ?,";
	set = set + "l_SharePeriod = ?,";
	set = set + "mem_Description = ?,";
	set = set + "l_TypeLife = ?,";
	set = set + "dbl_SecExpense = ?,";
	set = set + "sensitivity = ?,";
	set = set + "l_AutoGroup = ?,";
//            set = set + "cid = ?,";
//            set = set + "lhash = ?,";
//            set = set + "updated = ?,";
	set = substring(set,0, set.length() - 1);

	String where = String.format(Locale.US, "uid = %d", mainEvent.get_luuid());
	String sql = String.format(Locale.US, "UPDATE %s SET %s WHERE %s", table, set, where);

	state = getLocalSQLiteOpenHelper().getDatabase().executeFast(sql);
	state.bindLong(1, mainEvent.get_l_PlanningPeriod());
	state.bindInteger(2, mainEvent.get_l_SharePeriod());
	state.bindString(3, mainEvent.get_mem_Description());
	state.bindInteger(4, mainEvent.get_l_TypeLife());
	state.bindLong(5, mainEvent.get_dbl_SecExpense());
	state.bindInteger(6, mainEvent.get_sensitivity());
	state.bindInteger(7, mainEvent.get_l_AutoGroup());
//            state.bindInteger(8, mainEvent.get_cid());
//            state.bindLong(9, mainEvent.get_lhash());
//            state.bindLong(10, mainEvent.get_updated());
	state.step();

} catch (Exception e) {
	FileLog.e(e);
} finally {
	if (state != null) {
		state.dispose();

		if (callback != null) callback.onMainEventSaved(mainEvent);
	}
}

//--->UPDATE if exist or INSERT
private void putMessagesInternal(final ArrayList<TLRPC.Message> messages, final boolean withTransaction, final boolean doNotUpdateDialogDate, final int downloadMask, boolean ifNoLastMessage, boolean scheduled) {
	try {
		if (withTransaction) {
			database.beginTransaction();
		}

		SQLitePreparedStatement state_dialogs_replace = database.executeFast("REPLACE INTO dialogs VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		SQLitePreparedStatement state_dialogs_update = database.executeFast("UPDATE dialogs SET date = ?, unread_count = ?, last_mid = ?, unread_count_i = ? WHERE did = ?");

		for (int a = 0; a < messagesMap.size(); a++) {
			long key = messagesMap.keyAt(a);
			if (key == 0) {
				continue;
			}
			TLRPC.Message message = messagesMap.valueAt(a);

			int channelId = 0;
			if (message != null) {
				channelId = message.to_id.channel_id;
			}

			SQLiteCursor cursor = database.queryFinalized("SELECT date, unread_count, last_mid, unread_count_i FROM dialogs WHERE did = " + key);
			int dialog_date = 0;
			int last_mid = 0;
			int old_unread_count = 0;
			int old_mentions_count = 0;
			boolean exists;
			if (exists = cursor.next()) {
				dialog_date = cursor.intValue(0);
				old_unread_count = Math.max(0, cursor.intValue(1));
				last_mid = cursor.intValue(2);
				old_mentions_count = Math.max(0, cursor.intValue(3));
			} else if (channelId != 0) {
				getMessagesController().checkChannelInviter(channelId);
			}
			cursor.dispose();

			Integer mentions_count = mentionCounts.get(key);
			Integer unread_count = messagesCounts.get(key);
			if (unread_count == null) {
				unread_count = 0;
			} else {
				messagesCounts.put(key, unread_count + old_unread_count);
			}
			if (mentions_count == null) {
				mentions_count = 0;
			} else {
				mentionCounts.put(key, mentions_count + old_mentions_count);
			}
			long messageId = message != null ? message.id : last_mid;
			if (message != null) {
				if (message.local_id != 0) {
					messageId = message.local_id;
				}
			}

			if (channelId != 0) {
				messageId |= ((long) channelId) << 32;
			}

			if (exists) {
				state_dialogs_update.requery();
				state_dialogs_update.bindInteger(1, message != null && (!doNotUpdateDialogDate || dialog_date == 0) ? message.date : dialog_date);
				state_dialogs_update.bindInteger(2, old_unread_count + unread_count);
				state_dialogs_update.bindLong(3, messageId);
				state_dialogs_update.bindInteger(4, old_mentions_count + mentions_count);
				state_dialogs_update.bindLong(5, key);
				state_dialogs_update.step();
			} else {
				state_dialogs_replace.requery();
				state_dialogs_replace.bindLong(1, key);
				state_dialogs_replace.bindInteger(2, message != null && (!doNotUpdateDialogDate || dialog_date == 0) ? message.date : dialog_date);
				state_dialogs_replace.bindInteger(3, old_unread_count + unread_count);
				state_dialogs_replace.bindLong(4, messageId);
				state_dialogs_replace.bindInteger(5, 0);
				state_dialogs_replace.bindInteger(6, 0);
				state_dialogs_replace.bindLong(7, 0);
				state_dialogs_replace.bindInteger(8, old_mentions_count + mentions_count);
				state_dialogs_replace.bindInteger(9, channelId != 0 ? 1 : 0);
				state_dialogs_replace.bindInteger(10, 0);
				state_dialogs_replace.bindInteger(11, 0);
				state_dialogs_replace.bindInteger(12, 0);
				state_dialogs_replace.bindInteger(13, 0);
				state_dialogs_replace.bindNull(14);
				state_dialogs_replace.step();
			}
		}
		state_dialogs_update.dispose();
		state_dialogs_replace.dispose();


		if (withTransaction) {
			database.commitTransaction();
		}

	} catch (Exception e) {
		FileLog.e(e);
	}
}