Jump to content

SQLite is so awesome


chriscalef

Recommended Posts

Sorry, but I just had to drop in and share my amazement with sqlite for a second. I've been using it for years, but have only just now taken the time to seriously optimize it. (My old methods were becoming unbearably slow when updating MegaMotion scenes involving a couple of hundred characters.) I could go on at length, but it really boils down to two things: prepared statements, and memory databases.


The second one is obvious, but I hadn't known sqlite could do that, or that it would be so easy. All you have to do is use ":memory:" as the name of your database file, and it creates the database in RAM. Then a few lines of sample code from sqlite.org gets you loading and saving from the regular disk database file, and suddenly all of your interactions are internal instead of doing a separate disk access for every SQL statement. More on this here.


Prepared statements are compiled SQL queries, of a form looking like "... WHERE id=?;", so that you can compile the query once and run it many times in a loop, changing the variables out every time without having to reinterpret the SQL every time. More on this here.


Since my noticeable access lag time dropped to effectively zero when I went to the cached database, I'm not sure how much the prepared statements are actually helping me, but they're definitely a good thing to know about.


Here is my version of sqlite.org's loadOrSaveDb function, modified to work with the T3D SQLiteObject resource.

 

/*
** This function is used to load the contents of a database file on disk
** into the "main" database of open database connection pInMemory, or
** to save the current contents of the database opened by pInMemory into
** a database file on disk. pInMemory is probably an in-memory database,
** but this function will also work fine if it is not.
**
** Parameter zFilename points to a nul-terminated string containing the
** name of the database file on disk to load from or save to. If parameter
** isSave is non-zero, then the contents of the file zFilename are
** overwritten with the contents of the database opened by pInMemory. If
** parameter isSave is zero, then the contents of the database opened by
** pInMemory are replaced by data loaded from the file zFilename.
**
** If the operation is successful, SQLITE_OK is returned. Otherwise, if
** an error occurs, an SQLite error code is returned.
*/
int SQLiteObject::loadOrSaveDb(const char *zFilename, bool isSave) {
int rc;                   /* Function return code */
sqlite3 *pFile;           /* Database connection opened on zFilename */
sqlite3_backup *pBackup;  /* Backup object used to copy data */
sqlite3 *pTo;             /* Database to copy to (pFile or pInMemory) */
sqlite3 *pFrom;           /* Database to copy from (pFile or pInMemory) */

						  /* Open the database file identified by zFilename. Exit early if this fails
						  ** for any reason. */

Con::printf("calling loadOrSaveDb, isSave = %d", isSave);

if (isSave == false)
{//If we're loading, have to create the memory database.
	if (!(SQLITE_OK == sqlite3_open(":memory:", &m_pDatabase)))
	{
		Con::printf("Unable to open a memory database!");
		return 0;
	}
}
rc = sqlite3_open(zFilename, &pFile);
if (rc == SQLITE_OK) {

	/* If this is a 'load' operation (isSave==0), then data is copied
	** from the database file just opened to database pInMemory.
	** Otherwise, if this is a 'save' operation (isSave==1), then data
	** is copied from pInMemory to pFile.  Set the variables pFrom and
	** pTo accordingly. */
	pFrom = (isSave ? m_pDatabase : pFile);
	pTo = (isSave ? pFile : m_pDatabase);

	/* Set up the backup procedure to copy from the "main" database of
	** connection pFile to the main database of connection pInMemory.
	** If something goes wrong, pBackup will be set to NULL and an error
	** code and message left in connection pTo.
	**
	** If the backup object is successfully created, call backup_step()
	** to copy data from pFile to pInMemory. Then call backup_finish()
	** to release resources associated with the pBackup object.  If an
	** error occurred, then an error code and message will be left in
	** connection pTo. If no error occurred, then the error code belonging
	** to pTo is set to SQLITE_OK.
	*/
	pBackup = sqlite3_backup_init(pTo, "main", pFrom, "main");
	if (pBackup) {
		(void)sqlite3_backup_step(pBackup, -1);
		(void)sqlite3_backup_finish(pBackup);
	}
	rc = sqlite3_errcode(pTo);
}

/* Close the database connection opened on database file zFilename
** and return the result of this function. */
(void)sqlite3_close(pFile);

//if (isSave ==  true)  // Actually, cancel this, I'm sure it will happen automatically and if we don't do it here, we can also use
//{                     // this function for periodic saves, such as after saving mission.
//	sqlite3_close(m_pDatabase);
//}

Con::printf("finished loadOrSaveDb, rc = %d", rc);

if (rc == 0)
	return true;
else
	return false;
}

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...