SimpleSQL - SQLite integration with Unity3D

Ah, I see. I have never tried to change code while in Play mode before, so I guess that is why I have not seen this issue. Yeah, that shouldn't affect the build since the code cannot be changed after it is compiled.

 

Hi echo17,
I just bought this asset.
I can retrieve some date using playmaker action (Simple SQL query single row). I tried to create simple dialog system using database, for this purpose I can retrieve some date using playmaker action using this Query:

SELECT dialogText FROM Dialogue WHERE Sequence=? AND DialogID=?

first ? = Sequence Number
second ? = NameOfDialog

However, I want to get other data using this Query:

SELECT ? FROM Dialogue WHERE Sequence=? AND DialogID=?.

first ? should be filled by dynamic variable. But when i tried this query, the result is the content of parameter. Why this is occurred? how to achieve my goal?

Thanks

 

Hi there. I replied to your email, but I'll post the same answer here in case anyone else has this question:

The parameters only work on the where clause of the sql statement. If you want to create a dynamic sql query with the fields, you'll need to use Playmaker's BuildString action. Something like this:



Note that you can still use parameters in the where clause when building the string if you wish. The BuildString action just lets you create other parts of the sql query dynamically.

 

This is not my asset but I wanted to elaborate on the answer to answer your other question as well. You're attempting to use parameters for your field names. The whole purpose of parameterization is that the strings get sanitized before executing the query. This helps to avoid SQL Injection but also helps to make the queries less error prone. For example:

SELECT UserId FROM People WHERE FirstName = Foo
The above would error because Foo needs to be quoted:

SELECT UserId FROM People WHERE FirstName = "Foo"
The above is valid. Using Parameterized queries does this for you. Now the following is also valid in SQL:

SELET "UserId" FROM People WHERE FirstName = "Foo"
In the above, the literal value of "UserId" will always be returned because it's quoted. This is what the parameter is doing for you when you add a question mark.

 

Thanks for elaborating on that! I missed that part of the question.

 

You're very welcome. You answered the important part, just thought I'd answer the rest for the curious.

 

Hi Echo17 and Dustin,
Thank you for the explanation. I thought that dynamic parameters are using ? symbol, . Now is OK, and I enjoy simpleSQL . However, I have another problem (warning) as attached picture. What is the problem?

 

At least one of your SimpleSQL database manager objects doesn't have a database file assigned. If you have more than one db manager in your scene, be sure they all have a file assigned to them. The one manager you have highlighted looks fine, it is probably another one that is giving you the issue. Could it be "BarkData" perhaps?

 

Hi..
Actually BarkData is only game object with SimpleSQL Data table. And now it is removed. But, the warning is still appear.

Another problem: When i created new table to the database attached to DBManager, i cannot access the table name and the fields by SQL Query. I found the solution that i should re-attached the database name to the database manager, Why is it occurred?

 

Not sure what you're asking exactly, but this FAQ might answer your question: http://www.echo17.com/forum/index.php?topic=257.msg453#msg453

 

Hi echo17,

I have a serious performances issue with SimpleSQL.
Here is my example request : SELECT * FROM `players` WHERE `fullname` LIKE 'echo1%'
I'm working with a table composed by 190000+ rows.

`fullname` is indexed (COLLATE NOCASE), so the request is now fast as light, in every SQLite exec programs and Unity Editor with SimpleSQL.
CREATE INDEX idx1 ON players(fullname COLLATE NOCASE)

But... when I test on a device (Nexus 5 / 4.4.4), it seems that idx1 is not used, the query takes ages... (full scan table, like before indexes optimization)

Unity Editor with SimpleSQL before optimization : ~100ms
Unity Editor with SimpleSQL after optimization : ~4ms
Nexus5 with SimpleSQL before optimization : ~440ms
Nexus5 with SimpleSQL after optimization : ~440ms


Are indexes handled on Android with SimpleSQL ?
Otherwise, how can i view the result of a EXPLAIN QUERY PLAN ... with SimpleSQL in order to view the query plan on Android ?

Thank you in advance.

 

In order to check if SQL indexes are well handled on Android, i have tested another query, without the keyword LIKE :
SELECT * FROM `players` WHERE `fullname`=`test test`
with a different idx1 (cause of NOCASE) CREATE INDEX idx1 ON players(fullname)

It worked like a charm.
I have tested my previous query (LIKE) with this index (no collate nocase) and the request took even more time but this is not surprising. Do you know if there is an issue with LIKE keyword and Indexes on Android ?
Otherwise, an explain query plan details via SimpleSQL could help us.

 

SimpleSQL uses the sqlite3.dll library under the hood. Most of SimpleSQL is just wrappers and reflection to simplify the communication with Unity. The index issues you are experiencing would be due to how the sqlite3 library is handling the requests on the Android platform. Unfortunately, I'm not an contributor to the sqlite3 library, so I wouldn't be able to give you an explanation of what is happening. I would suggest searching for this issue generically, looking for anything related to Android, sqlite, and indexing.

You can run the EXPLAIN QUERY PLAN in SimpleSQL just like a regular query. You probably want to send it to a simple table structure to parse, something like:

Code (CSharp):

1.         SimpleSQL.SimpleDataTable dt = dbManager.QueryGeneric("EXPLAIN QUERY PLAN SELECT WeaponName FROM Weapon WHERE WeaponName LIKE '%s%'");
2.         for (int r = 0; r < dt.rows.Count; r++)
3.         {
4.             for (int c = 0; c < dt.columns.Count; c++)
5.             {
6.                 Debug.Log("Row: " + r.ToString() +
7.                             " Column:" + c.ToString() +
8.                             " Data:" + dt.rows[r][c].ToString()
9.                             );
10.             }
11.         }

 

Thank you echo17.

The EXPLAIN QUERY PLAN confirms the issue :
Unity Editor : TABLE joueurs WITH INDEX idx1
Nexus 5 : TABLE joueurs

Like you have suggested, i will search the issue generically. If I find something, i come back here

 

I have read a post about SQLite optimization on Android that was not linked to my issue but it was saying "LIKE can be written BETWEEN "text" AND "textzzzzzz"".
This is true... so I have tried on Android (with collate nocase) and idx1 was correctly used.
The request took 4ms instead of 440ms

Thank you again, have a nice day echo17 !

 

Wow, that is an amazing improvement. I'll be sure to make a FAQ post about this on my forums. Thanks for the extra info and your research!

 

I have Unity free and I haven't found a way to get SQLite working on my Android and iOS games.

So my question is, if I buy this does it work on Unity free?

 

Hello Nadan, i have Unity free + SimpleSQL like you.
I can confirm that everything is working great on Android & iOS.

 

Hello echo17, I have sometimes this error message in UnityEditor and i would like to know if we can get rid of it.

Failed to open database at the working path: C:/Users/myusername/AppData/LocalLow/myusername/AppName\database.sqlite

Have you any advice ?
The database.sqlite size is 13 417 Ko.

 

Hi dafunker, thank you for the info!

I bought SimpleSQL.

 

Thanks for the purchase, and thanks dafunker for the answer.

 

It could be that the file is being locked. There is an issue that has been reported where the database file gets locked when using insert or insertall (ORM commands). If that is the case, try using regular SQL statements and the Execute command for now until I can figure out the issue.

Also, try not to compile code while the Unity editor is playing as that can sometimes place a lock on the database file.

 

SimpleSQL is on sale for 50% off during Unity's Birthday Bonanza Sale!


https://www.assetstore.unity3d.com/en/#!/content/3845​

 

Hello,

I have a question, can you save your stats to a database using this tool?

 

You sure can. As long as you can store your data in integer, string, or float format you can put it into a database. SimpleSQL is a wrapper for the sqlite language, so you may want to familiarize yourself with sqlite first. If it looks like something that can be useful for you, SimpleSQL will vastly make setting up sqlite database interaction in your project much easier.

Keep in mind also that sqlite tables are local storage on the device, SimpleSQL is not a public sql server plugin.

 

Great plugin. Noticed that in WP8 support is not yet implemented.
Would you be open to a request to offer support for this increasingly popular platform.

 

I've attempted to get it to work on WP8 unsuccessfully. I know some of my clients have managed to modify the source enough to work with WP8, but unfortunately it leaves the code in an unusable state for other platforms. If you are interested in these clients' methods, I'll send you information via email. You can contact me by the email address in my signature. I don't have the full methods that they used, just some suggestions that led to a working solution that met their needs.

 

When was the last time you tried to get it working? Reason for asking is that things are constantly changing.

 

It was a few month ago, if I recall correctly. If you want to shoot me an email with your invoice number, I'll send you the info my other clients sent me.

 

hello developer.
I can use this asset with DB Browser for SQLite?

 

Yes. DB Browser is just a sqlite database creation / modification tool that you would use at design time to create your databases. SimpleSQL is a Unity runtime tool that allows you to connect to those databases, where you can query or update the data.

You can also create and modify tables at runtime using SQL commands in SimpleSQL as well.

 

Hi,

Sorry if this has been asked before, but, can we load an entire base in memory?

 

Unfortunately not. SimpleSQL relies on the sqlite library which uses a file-based system. This means that the webplayer platform will not work since it has security restrictions on files.

 

Thank you. I could use simple sql with db manager after I purchased!

 

Hello Echo17,

I'm experiencing an unexpected issue with SimpleSQL + SQLite.

Here is the context :

Every X days, my Application gets an update from a Server that returns a list of GZipped SQL queries, mainly UPDATE statements. Some of them contains WESTERN characters. SQLite3.Prepare2 cries during execution
Here is a simple test case :

dbManager.QueryGeneric("CREATE TABLE IF NOT EXISTS test (test1 text);");
dbManager.QueryGeneric("INSERT INTO test VALUES(\"aéa\");");

The exact same query works fine in SQLite Data Browser and i don't know why.

I'm aware that we must pass by ? parameters for non western characters but é is part of them.
Do you have any idea why the character é is not accepted by SQLite3.Prepare2 method ?

 

I have one more question, sorry for the inconvenience Echo17.

User A have a DB Version 1 and use App Version 1
User B have a DB Version 2 and use App Version 2

The DB Version 1 is entirely outdated, it is not updatable. (column names modified, new columns, different table names)
Since DB Version 2, the DB updates itself and so "Overwrite" flag is set to false in order to keep new data.
Is it possible to Overwrite the DB Version 1 when the User A will update his App to Version 2 ?

 

I'm not sure why é isn't accepted in the string. It's a limitation of how the string is built and passed to the underlying sqlite engine. SQLite Data Browser might sanitize the string behind the scenes, forcing the characters into parameterization (but that is just a guess).

Fortunately, parameters would work very well in this situation. I actually would recommend them in all situations as it makes cleaner and more reusable strings.

 

This is a common dilemma in database programming. For this situation, you'll want to create an upgrade path that modifies the database through code, depending on what version the user is on. Have a look at this FAQ for more information: http://www.echo17.com/forum/index.php?topic=257.msg453#msg453

 

Thank you for both answers Echo17 !

Indeed, I will use parameters, this will be safer.

My issue is now resolved, I have released an update with the final DB structure (with overwrite). Then i will use upgrade paths for next structure modifications in a few weeks when the version propagation will be complete.
Something would be great but I don't know if this is possible. Allowing the developer to overwrite the database at any moment. By example, the developer check DB Version, if this is a version that is not easily upgradable then he calls dbManager.Overwrite().

 

You can do this by calling Initialize(true) on the SimpleSQLManager from your script. This will force the database to reinitialize even if it has already done so. You'll just need to set overwriteIfExists to true before you do the re-initialize call. You can then set overwriteIfExists back to false if that was its prior setting. So something like (psuedocode):

Code (CSharp):

1. SimpleSQLManager dbManager;
2.  
3. bool previousValue = dbManager.overwriteIfExists
4. dbManager.overwriteIfExists = true;
5. dbManager.Initialize(true);
6. dbManager.overwriteIfExists = previousValue;

 

Thanks. Since it's a read only base i'll put it on a RamDrive.

 

can you use a ram drive with webplayers? You have peeked my interest in your solution, I also have a read only base that I would like to be able to use with the webplayer.

 

Hello Echo17, I have found why é or any other western characters were not accepted by SQLite.

Code (csharp):

1. var r = Prepare2(db, query, query.Length, out stmt, IntPtr.Zero);

If query == "é", query.Length will be equal to 1 instead of 2 as expected by SQLite Library (é use 2 bytes [UTF-8]).
You should use Encoding.UTF8.GetByteCount(query) instead of query.Length.

 

Ah, thanks for pointing that out. I'll put it in for a future release.

 

Hi, team.
I use this asset with playmaker and SimpleSQL_Query_SingleRow action.
I got a bug the following.

Get Row : SimpleSQL_Query_SingleRow : There was an error trying to store the results. Be sure your result variable types match the query results

My table columns is all TEXT type (nothing not NULL), and playmaker action variable type is String.
but Everything query is going well.
Could you tell me?

 

Oh sorry for the late response. No local drive access from the Web Player : security limitations

 

Not sure what would cause this without seeing an example. Have a look at this FAQ: http://www.echo17.com/forum/index.p...b54eb5d7bdd902d8dfcd8&topic=257.msg950#msg950

 

Hi,team.
I solved. I retry when I made mini project, bug is nothing.
I understand that I had mistake an number of columns and number of results.
Thanks for reply.

 

Ah, thanks for letting me know!

 

Hi,

I`struggling with a error message:

" Could not write resource to path: .... /Projectfolder/1_Data/..sqlite3.dll Sharing violation on path Projectfolder\sqlte3.dll

UnityEngine.Debug.LogError(Object)
SimpleSQL.RuntimeHelper.CreateFileFromEmbeddedResource(String. String)
SimpleSQL.SimpleSQLManager.Awake()

"

I`confused, because in Play Mode of Unity, there isn`t any kind of failure message, this pops up only in the build version.

The PC build version seems working normally, but on Android there is no working Database.

Do you have any idea what this means or what could causing this?

Many thanks in advance!