Titanium JIRA Archive
Appcelerator Community (AC)

[AC-6666] UI freeze while updating the database

GitHub Issuen/a
TypeBug
Priorityn/a
StatusOpen
ResolutionUnresolved
Affected Version/sn/a
Fix Version/sn/a
Componentsn/a
Labelsn/a
ReporterAhmed Mohamed
AssigneeAbir Mukherjee
Created2021-01-25T22:06:35.000+0000
Updated2021-01-28T20:20:06.000+0000

Description

User interface freeze while updating the database. I think this is because we run on the UI thread now, So if we open a database connection in the same thread the UI hangs until the database connection is closed. It's a critical issue and it affects all developers because we all use the backbone *model.save()* or *ti.database* In this test example, you'll see the console logs but the progress bar won't update immediately.
const win = Ti.UI.createWindow({
	backgroundColor: 'white'
});

const view = Ti.UI.createView({
	layout: 'vertical',
	height: Ti.UI.SIZE,
	width: Ti.UI.FILL
});

const progressBar = Ti.UI.createProgressBar({
	width: 250,
	min: 0,
	max: 1000,
	value: 0,
	color: 'blue',
	message: 'Inserting 0 of 1000',
});

const startInsert = Ti.UI.createButton({
	title: "Start Insert to DB",
	color: "#fff",
	backgroundColor: "teal",
	font: {
		fontSize: '16sp'
	},
	top: 24
});

startInsert.addEventListener('click', () => {
	progressBar.value = 0;
	progressBar.message = Inserting 0 of 1000;

	const db = Ti.Database.open('mydbInstalled');
	db.execute('CREATE TABLE IF NOT EXISTS people (name TEXT, phone_number TEXT, city TEXT)');
	for (let i = 0; i < 1000; i++) {
		db.execute('REPLACE INTO people (name, phone_number, city) VALUES (?, ?, ?)', "Me", "123456789", "Here");
		console.warn(lastInsertRowId :: ${db.lastInsertRowId});
		progressBar.value = i;
		progressBar.message = Inserting ${i} of 1000;
	}
	db.close();
});

view.add(progressBar);
view.add(startInsert);
win.add(view);
win.open();

Comments

  1. Rene Pot 2021-01-28

    For completeness, I also commented this on TiSlack, in this specific sample you should be using transactional queries. I made a pretty simple library to do that for you; https://github.com/Topener/ti.dbutil That said, we also offer async methods for execute, but I am not sure they are running on different threads. I pinged someone to give an answer to that. Also from your comments, it is clear your app is not as straightforward as the above sample, and according to your comments, you're also having UI block when using Async methods.
  2. Gavin Matthews 2021-01-28

    I'm guessing [~gmathews] not me :)
  3. Joshua Quick 2021-01-28

    Right. The open(), execute(), and close() methods used in the above code example are blocking. This is the correct behavior for those APIs. You'll want to use the executeAsync() and executeAllAsync() APIs to execute SQL statements on a separate thread. (In Titanium 10.0.0, we'll be adding Promise support to those APIs as well to make them easier to use via async/await.) https://docs.appcelerator.com/platform/latest/#!/api/Titanium.Database.DB-method-executeAsync An alternative solution is to use the existing blocking execute() function in a multitasking way via a JavaScript "function generator" and "yield" statements. This is JavaScript's coroutine equivalent. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/yield

JSON Source