Home Tutorials Download Beta Store Forum Documentation KnowledgeBase Wiki Blog
Main Page About Help FAQ Special pages Log in

SQLite Plugin

From ShiVa Wiki

Contents

Prerequisites

In order to successfully use, and understand, the SQlite Database plugin, I suggest you do the following steps.


Please read the following sections for basic information on Plugins themselves. This tutorial assumes you have basic knowledge of plugin creation. And will concentrate on the actual plugin functionality.

Introduction to Plugins on ShiVa.Developer

How to Install and Use Plugins on ShiVa.Developer

How to Create Plugins on ShiVa.Developer


You will also need to download the SQLite plugin source code from here: com.AO.SQLDatabase.rar


And finally, here is the Web Site for SQLite SQL Database: www.sqlite.org

You should read through the SQLite FAQ for basic questions on the SQLite database. http://www.sqlite.org/faq.html


I would also look up "SQL Commands" on any search engine to get a basic understanding of how SQL databases work. There are thousands of pages on this subject, and anything you would ever want to do has example on the web somewhere. Just look it up. SQL Databases are very common, and well documented.


There is also a thread in the Shiva3D forums discussing this plugin: http://www.shivaengine.com/developer/forum/viewtopic.php?f=42&t=24193

Please direct questions or bugs there. I do check that thread a lot, and will be glad to help you if I can.

Introduction.

What is SQlite, and why should anyone use it? What are the advantages and disadvantages?

SQlite is a fully functional SQL database that is totally contained inside the plugin. There is no server to maintain. Just a single file on your "Disk" System. This external file can be edited, backed up, or modified by any normal SQlite GUI software out there.


I personally use: http://www.sqlmaestro.com/products/sqlite/

However, Any SQLite editor will do.


The advantages are simple. Its a fully functional, super fast, and totally reliable, SQL database. It can store up to 10Gigabyte relational databases easily. In short, once you get this plugin up and running. "Data Storage" isn't even an issue any more. You simply use the plugin on any Shiva app you make.


The main disadvantage is small, but there. The SQLite database, because of its speed and server-less mode of operation, the database itself is hard to edit. So some thought has to be made in the initial database creation. However, I have found this isn't much of an issue, with a GUI editor, a new database can be crated very quickly, and the data transferred over very easily with a little practice. Its not that much of an issue, but it is there.


The other disadvantage is that an SQLite database can only be accessed by one "user" at one time. If database access lag becomes a problem, you will need to switch to another Database system. But SQLite can handle hundreds, if not thousands, of users accessing the Database at once. Which basically never happens in a Shiva App in any case. But it is a consideration you should be aware of.

Actually Getting Started

Ok, now you are ready. Time to bring the Shiva Drudgery of data storage to an end. If you haven't downloaded the SQLite Plugin source code, do it now. We are ready to use it.


You will need to download the SQLite plugin source code from here: com.AO.SQLDatabase.rar

Extract the files from the "rar" package anywhere you want. I suggest a permanent place for the original files. In case you mess up, and need to start over.


Get a GUI SQLite editor. Create a database, add the columns "UserName" and "Password" to a table called "User". Fill in some data for testing. Save the Sqlite Database file anywhere you want to. The Operation of GUI editors is outside the scope of this tutorial. However, There is great documentation on the SQlite GUI editors, they are very easy and straight forward to use.


WARNING!!!! Do not use this plugin for anything other then data insertion, updating, and reading. ALL DATABASE CONSTRUCTIONAN OR MAINTENANCE SHOULD BE DONE IN A GUI EDITOR!!! This plugin was NEVER designed for anything other then non destructive operations on a database. There IS NO ERROR CHECKING of any kind. It will gladly destroy an entire database if you send the wrong command. SELECT, UPDATE and INSERT SQL statements are all the SQL commands you should use inside this plugin. Unless you really know what you are doing!! And I do kinda know what I'm doing, and I destroyed quite a few Databases being "Smart". Dont you be the next victim. Use the Editor. It has error checking, and is much safer to use.


Add the plugin to your Shiva Projects. The easiest way is to simply copy the entire "com.AO.SQLDatabase" folder that was extracted from the "rar" to the plugins directory of your Shiva Games. Yep, copy the entire folder over. The plugin "SQlDatabase" will show up in your "Plugins" section of your "Data Explorer" menu in the Shiva editor.


Now, drag and drop the "SQLDatabase" plugin from the Data Explorer window, to the "Plugins" tab in the "Game Editor" menu in the Shiva3D Editor. You must reference the plugin for Shiva to be able to use it.


Ok now, we are really ready. Open Shiva and start a new "Game". Call the game "SQLTest". I suggest storing the SQlite Database where you export your game to. But it really doesn't matter.

Shiva Coding for Plugin Use.

Ok, we are now ready to use the plugin.


Open up the SQLTest game if you haven't already. Create a New MainAI to run your game. Add the following code to you onInit section to get started.

Example Code

 
function MainAI.onInit ( )
 
--Execute SQL Command
            local sDatabase = "Path to the Database File you Created"
            local sUserName = "User Name to test return string with"
            local sSQLString = "SELECT UserName, Password FROM Users WHERE UserName = '"..sUserName.."'"
            local nSQLResult, sSelect = AO_SQLcom.SQLCommandDB(sDatabase, sSQLString)
            if (nSQLResult ~= 0) then
-- Query Failed
              log.warning( "Query Failed: "..nSQLResult )
            else
              log.message( "Data Returned: "..sSelect )
            end
 
end
 
 

And yes, that is the entire function of the plugin. You load up the "sDatabase" variable with the path of the database you created with the GUI Editor. Then fill the "sSQLString" with any valid SQL statement.


The plugin will return a variable "nSQLResult" which is the error code from the SQL statement against the database. And finally the "sSelect" will return the data. We will be going over the data returned in the next section.


Here is a great site to learn basic SQL commands: http://www.baycongroup.com/sql_command_reference.htm But, just use Google to look up "SQL Commands". There are literally thousands of paged dedicated to this subject.


The best way to learn how to use this plugin is to keep this SQLTest program handy. Maybe even set up a small workable application. Then keep editing the Database with the GUI editor, and keep sending different SQL statements. Watch what is returned compared to whats in the database, and the SQL Query sent. You will find that for something as simple a game data storage. You will learn everything you need really fast.


And this is truly all the code needed to run a full blown SQL database. You will need to customize it for you app. But this is basically it.

The Plugin Itself

Oh Boy, we are now going into the nuts and bolts of this plugin. There is no need to understand or use any of this information. But its here in case you want to customize the plugin for your own Use.


First off, I suggest you backup all your work before you begin. Its always a good idea when doing something like this.


Go to the directory "Your Project Directory\Plugins\com.AO.SQLDatabase". The go to the directory "Make\Windows" Its the only part of this plugin included in the source code. Open the "SQLDatabase.sln" file.


The "SQLDatabase.sln" is the windows Source code project for this database Plugin.

The file you want to study, and then edit to customize is "AO_SQLcom.cpp". I dont suggest you edit anything else. As shiva3d will overwrite the other files on a plugin rebuild.

C++ Function Call From Shiva3D

 
int Callback_AO_SQLcom_SQLCommandDB ( int _iInCount, const S3DX::AIVariable *_pIn, S3DX::AIVariable *_pOut )
{
    S3DX_API_PROFILING_START( "AO_SQLcom.SQLCommandDB" ) ;
 
    // Input Parameters 
    int iInputCount = 0 ;
    S3DX::AIVariable sName    = ( iInputCount < _iInCount ) ? _pIn[iInputCount++] : S3DX::AIVariable ( ) ;
    S3DX::AIVariable sCommand = ( iInputCount < _iInCount ) ? _pIn[iInputCount++] : S3DX::AIVariable ( ) ;
 
    // Output Parameters 
    S3DX::AIVariable nReturn ;
    S3DX::AIVariable sReturn ;
 
 
//#   pragma TODO( write the code for nReturn, sReturn = AO_SQLcom.SQLCommandDB ( sName, sCommand ) )
	int rc;
 	sqlite3 *db; // sqlite3 db struct
	char *zErrMsg = 0;
	rc = sqlite3_open_v2(sName.GetStringValue ( ), &db, SQLITE_OPEN_READWRITE , 0); 
	if( rc ){
		// failed
		nReturn = -1;
	} else
	{
		// success
		nReturn = 0;
	};
 
		rc = sqlite3_exec(db, sCommand.GetStringValue ( ), SelectCallback, 0, &zErrMsg);
		if( rc!=SQLITE_OK ){
			// SELECT SQL Errored Out
			sqlite3_free(zErrMsg);
		}
 
		nReturn = rc;
		sReturn = sSelectReturn;
 
		sqlite3_close(db);
 
    // Return output Parameters 
    int iReturnCount = 0 ;
    _pOut[iReturnCount++] = nReturn ;
    _pOut[iReturnCount++] = sReturn ;
 
    S3DX_API_PROFILING_STOP( ) ;
    return iReturnCount;
}
 

C++ Function SQL Callback

 
//Define the Select Return String
char sSelectReturn[65000];
static int SelectCallback(void *NotUsed, int argc, char **argv, char **azColName)
{
	int i;
 
	strcpy (sSelectReturn,"");
 
	for(i=0; i<argc; i++)
	{
 
		strcat (sSelectReturn,argv[i]);
		strcat (sSelectReturn,",");
 
	}
	return 0;
}
 

Let us look at this section here. This is the best and easiest place to customize the return string. Just what the #@@$$% is going on here you ask. Well, now that I look at it, im sure this could be programmed better. but it works, and I'm to lazy to fix it.


And to be honest, there is another problem here. Shiva3D data processing is horrible. So the only reliable method I have found to send the data back to Shiva is in string form. This string is then exploded by Shiva to get the data. Its fast, but tedious if you have a lot of data in the select statement.

I used a comma "," to separate to data fields sent back to Shiva. But that causes a limitation. You CANT store a "," in the data fields of the database. Now, so far thats not an isse, but it might be some day. So if you want to store a "," in the data fields, you have to change this line: strcat (sSelectReturn,","); to strcat (sSelectReturn,"Your Delimiter");

Then explode the returned string using the new delimiter.

To Be continued.

Im going to look at this plugin a little more. Im going to try and clean it up a little. Ill add any updates later.


It works in its present state, but Ill see if i can make things a little nicer. But in any case. This is a complete plugin. You just have to edit this callback to return the data in any way that you like.


Personally, I used to idea to keep this plugin as simple as possible, and do my coding in Shiva. And not try and do it in the plugin. This may, or may not be the best way to do it.



Retrieved from "http://www.stonetrip.com/developer/wiki/index.php?title=SQLite_Plugin"

This page has been accessed 2,345 times. This page was last modified on 15 April 2012, at 17:38.