SQLiteKit

Great,

It seems that the SQLite version in the asset is: 3.7.7.1

The current official version is 3.8.4, can the SQLite version in the asset also be upgraded?

Edit: Reason is that as of 3.8.3+, Sqlite supports recursion which makes handling of hierarchical information much simpler.

http://stackoverflow.com/questions/7456957/basic-recursive-query-on-sqlite3

http://sqlite.org/lang_with.html

 

look not easy i'am lost !(-here

 

Thanks, for question.
We don't have plans to migrate - it really difficult. I'll will look for free time to do that,

 

Wondering how to do BEGIN and COMMIT with SqlKit. I tried the code with SQLite and it works, but it doesn't work on SqlKit.



BEGIN"; sql += "create table episodes (id integer primary key, season int, name text)"; sql += "insert into episodes(id, season, name) Values(1,2,'bill')"; sql += "insert into episodes(id, season, name) Values(2,3,'bob')"; sql += "COMMIT";

 

Please try use full commands: COMMIT TRANSACTION, BEGIN TRANSACTION

Thanks

 

I tried using the full command but still not working.

I tried the exact command in Sqlite manager and it works. Did you test this functionality with SqlKit?

Cheers.

 

I'll try your code, but I never got this problem before.

 

Hi, there is the problem that you use begin and request and commit in one sqlite step.

There is demo code for you which I tested and it works fine.

Code (csharp):

1.  
2. using UnityEngine;
3. using System.Collections;
4. using System;
5. using System.IO;
6. using System.Threading;
7. using System.Diagnostics;
8. using Community.CsharpSqlite;
9.  
10.  
11. public class DemoObject4 : MonoBehaviour {
12.     private static string FILE = "";
13.      
14.     bool test = false;
15.    
16.     void Start () {
17.         FILE = Application.persistentDataPath + "/demo5.db";
18.     }
19.    
20.     void Update () {
21.    
22.         if(test == true)       
23.         {
24.            
25.             test = false;
26.  
27.             string sql = "";
28.             //sql +=    "BEGIN;";
29.             sql += "create table episodes (id integer primary key, season int, name text);";
30.             sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
31.             sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
32.             //sql += "COMMIT;";
33.            
34.             SQLiteQuery qr = null;
35.             SQLiteDB db = null;
36.            
37.             try{
38.  
39.                 db = new SQLiteDB();
40.                 db.Open(FILE);
41.  
42.                 qr = new SQLiteQuery(db,"BEGIN;");
43.                 qr.Step();
44.                 qr.Release();
45.                 qr = null;
46.  
47.                 qr = new SQLiteQuery(db,sql);
48.                 qr.Step();
49.                 qr.Release();
50.                 qr = null;
51.  
52.                 qr = new SQLiteQuery(db,"COMMIT;");
53.                 qr.Step();
54.                 qr.Release();
55.                 qr = null;
56.  
57.                 db.Close();
58.                 db = null;
59.        
60.                 UnityEngine.Debug.Log("Done, File:" + FILE);
61.             }catch(Exception e)
62.             {
63.                 UnityEngine.Debug.LogError(e.Message);
64.                
65.                 if(qr!=null)
66.                     qr.Release();
67.                 qr = null;
68.                 if(db!=null)
69.                     db.Close();
70.                 db=null;
71.             }
72.  
73.         }
74.        
75.     }
76.    
77.     void OnGUI()
78.     {
79.         if ( GUI.Button(new Rect (170,10,150,50), "Delete DB") )
80.         {
81.             UnityEngine.Debug.Log("DB Deleted");
82.             File.Delete(FILE);
83.         }
84.        
85.         if ( GUI.Button(new Rect (10,10,150,50), "Test") )
86.         {
87.             test = true;
88.         }
89.     }
90.  
91. }
92.  

And in your example you forget put delimiter between commands as well - ";"

Best regards.

 

Thanks for the test code.

Here is what I did in a fresh project to test the code.
On a mac, unity 4.3.4

1. Created a new database called demo5.db in StreamingAssets.
2. Attached the script above to an empty gameobject.
3. Ran the test.

4. with this configuration, I get an error. "SQLite step fail! error: SQL logic error or missing database".

1. sql += "BEGIN;";
   
   
2. sql += "create table episodes (id integer primary key, season int, name text);";
   
3. sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
   
4. sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
   
5. sql += "COMMIT;";
   
   5. with the configuration below, it created the table episodes, but the table was empty, no data inside.
   
   
   
   [*] //sql += "BEGIN;";
   
   
   [*] sql += "create table episodes (id integer primary key, season int, name text);";
   
   [*] sql += "insert into episodes(id, season, name) Values(1,2,'bill');";
   
   [*] sql += "insert into episodes(id, season, name) Values(2,3,'bob');";
   
   [*] //sql += "COMMIT;";
   
   
   
   
   
   

 

ok, I see now,

there is correct one:

Code (csharp):

1.  
2. using UnityEngine;
3. using System.Collections;
4. using System;
5. using System.IO;
6. using System.Threading;
7. using System.Diagnostics;
8. using Community.CsharpSqlite;
9.  
10.  
11. public class DemoObject4 : MonoBehaviour {
12.     private static string FILE = "";
13.      
14.     bool test = false;
15.    
16.     void Start () {
17.         FILE = Application.persistentDataPath + "/demo5.db";
18.     }
19.    
20.     void Update () {
21.    
22.         if(test == true)       
23.         {
24.            
25.             test = false;
26.            
27.             SQLiteQuery qr = null;
28.             SQLiteDB db = null;
29.            
30.             try{
31.  
32.                 db = new SQLiteDB();
33.                 db.Open(FILE);
34.  
35.                 qr = new SQLiteQuery(db,"BEGIN;");
36.                 qr.Step();
37.                 qr.Release();
38.                 qr = null;
39.  
40.                 qr = new SQLiteQuery(db,"create table episodes (id integer primary key, season int, name text);");
41.                 qr.Step();
42.                 qr.Release();
43.                 qr = null;
44.  
45.                 qr = new SQLiteQuery(db,"insert into episodes(id, season, name) Values(1,2,'bill');");
46.                 qr.Step();
47.                 qr.Release();
48.                 qr = null;
49.  
50.                 qr = new SQLiteQuery(db,"insert into episodes(id, season, name) Values(2,3,'bob');");
51.                 qr.Step();
52.                 qr.Release();
53.                 qr = null;
54.  
55.                 qr = new SQLiteQuery(db,"COMMIT;");
56.                 qr.Step();
57.                 qr.Release();
58.                 qr = null;
59.  
60.                 db.Close();
61.                 db = null;
62.        
63.                 UnityEngine.Debug.Log("Done, File:" + FILE);
64.             }catch(Exception e)
65.             {
66.                 UnityEngine.Debug.LogError(e.Message);
67.                
68.                 if(qr!=null)
69.                     qr.Release();
70.                 qr = null;
71.                 if(db!=null)
72.                     db.Close();
73.                 db=null;
74.             }
75.  
76.         }
77.        
78.     }
79.    
80.     void OnGUI()
81.     {
82.         if ( GUI.Button(new Rect (170,10,150,50), "Delete DB") )
83.         {
84.             UnityEngine.Debug.Log("DB Deleted");
85.             File.Delete(FILE);
86.         }
87.        
88.         if ( GUI.Button(new Rect (10,10,150,50), "Test") )
89.         {
90.             test = true;
91.         }
92.     }
93.  
94. }
95.  
96.  

 

Thanks, this one works.

Cheers.

 

Hello,
like to work with SQLiteKit, but it would be nice to update it, like it is discussed in this forum.
An I have one question:
I need to get the max value of a coloumn and beelow is the code, but it won't work. So, it seems that the coloumn ConfigurationId is not recognized. If I put the code directly in a sqlite query it work perfectly. but using it in the asset not.
Do have anyone an idea?
thanks for helping
Holger


private int GetMaxId()
{
string sqlCmd = "";
SQLiteQuery qr = null;
int id = 0;

sqlCmd = "SELECT MAX (ConfigurationId) as maxid FROM Configurations";


if (isDBValid (true)) {
qr = new SQLiteQuery (db, sqlCmd);
if (!qr.Step ()) {
// Fehlerausgabe
}
else {
id = qr.GetInteger("maxid");
return id + 1; //setzen letzte Id +1 für neue Id
}
} // if DB Valid
return id;

 

Could you please send database declaration or example database.

It's look like you expect integer, but it might be float so you may get this problem because of types missmatch

 

how do i encrypt my sqlite db file for the use with sqlite kit?

 

Oksana,

Have you done real-world testing with sqliteKit ?
The reason is that I started to test my project on the Webplayer and the database is getting slower and slower with each query to the point of being not useable at all.

 

Hi Oksana,
got it working now. You where right, some data type where wrong.
thanks for support

 

Oksana,

Did you have a chance to test the in memory database, I've been doing more testing and the results are the same. The database is getting slower and slower with each Query. This is quite a serious issue. Please look into this.

Cheers.

 

Oksana, any news on fixing this problem ?

 

Yes, it is really annoying with complex databases.

Like in the wrapper (SQLiteDB.cs), I have tried to use directly the "Sqlite3.sqlite3_open_v2()" method to open my database with the native "in memory" feature. But it doesn't work...

According to the sqlite documentation we should use the open URI mode with that kind of path :
"file:data.db?mode=memory"

This is the function I have written:

Code (CSharp):

1.  
2. public void OpenURI (string uri) // example: "file:data.db?mode=memory"
3.      {
4.              if (db != null) {
5.                      throw new Exception ("Error database already open!");
6.              }
7.  
8.              if (Sqlite3.sqlite3_open_v2 (uri, out db, Sqlite3.SQLITE_OPEN_URI, null) != Sqlite3.SQLITE_OK) {
9.                      db = null;
10.                      throw new IOException ("Error with opening database " + filename + " !");
11.              }
12.      }
13.  

 

I think this project is considered Abandon-ware. No sign of Oksana.

 

I just edited my original question because it didn't make any sense. I have just spoken to Pavel on Skype (completely awesome support), and after 30 minutes I had everything working exactly as I had hoped with his amazing software!

Thank you so much got SQLiteKit! It was worth every penny and then some!

Cheers!
Mike

 

There is qr.Release() is missing. it's locks database.

 

to open native sqlite in memory mode please use wrapper function

Code (csharp):

1.  
2.     public void OpenInMemory()
3.     {
4.         if( db != null )
5.         {
6.             throw new Exception( "Error database already open!" );
7.         }
8.        
9.         if ( Sqlite3.sqlite3_open( ":memory:", out db ) != Sqlite3.SQLITE_OK )
10.         {
11.             db = null;
12.             throw new IOException( "Error with opening database :memory:!" );
13.         }
14.     }
15.  

But still it useless as soon as you can't store the database in this mode even in original library. it's is acting just as temporary cache,

 

It's may happence if you do not relese queries like it should be

Code (csharp):

1.  
2. var qr = new SQLiteQuery(db,"SELECT THAT EVER");
3. if(qr.Step())
4. {
5. // read results here...
6. }
7. qr.Relese()
8.  

 

It seems that SQLite works only on Unity Pro.

http://wiki.unity3d.com/index.php/SQLite

Does SQLiteKit work with the Unity free, Indie version?

 

SQLite works with Unity Free. SQLite Kit is using SQLite for the database.

 

Hi Yukichu!

I'm having problems with SQLite. I installed SQlite with these instructions: http://wiki.unity3d.com/index.php/SQLite

The instruction tell that: "This will give you a License Error if you're using Unity Indie, but it doesn't seem to have an effect on the actual play in the editor, nor does it seem to effect the ability to build stand-alone versions."

Unity editor gives me: "License error. This plugin is only supported in Unity Pro!"

However it seems to work on the Unity editor like the instructions say. But I can't build the game on Android. It gives me this error:

"Error building Player: SystemException: 'System.Net.Sockets' are supported only with Unity Android Pro. Referenced from assembly 'Mono.Data.Tds'."

 

Hello guys.

I have a strange problem, SQLiteKit doesn't allow me to create a column with an "index" name. For example I'm using this query to create a table:

CREATE TABLE IF NOT EXISTS segments (id INTEGER PRIMARY KEY AUTOINCREMENT, entity_id INTEGER, index INTEGER)

After this code gets executed, I recieve this exception:

Exception: Error with prepare query! error:near "index": syntax error
SQLiteUnity.SQLiteQuery..ctor (SQLiteUnity.SQLiteDB sqliteDb, System.String query)

If I change the "index" name in the query to an "_index", everything's just fine. I tried to create the same table structure in SQLite Browser and its got created without any problems. So, I assume that there is some strangeness in the actual SQLiteKit. Can someone explain me why I'm unable to create a column with an "index" name?

Thanks.

 

Please check you project references, SQLiteKit dosn't have any reference to sockets. (it's not a network library)

or email me and we make TeamViewer session to find what happens with your project.

 

It's because INDEX is reserved keyword for sqlite and it's forbidden to use as column name. Just rename it to something else or try to use "[index]" instead of "index" in your request

 

First I only need to know does SQLiteKit work on Unity Free for Android and iOS developement?

Or does it require Unity Pro version?

 

No it's NOT require Unity Pro version.

 

Hi!

I'm trying to read a sqlite database file generated on my server.
With non encrypted file I have no problem, but I can’t read file encripted with SQLCipher.
https://www.zetetic.net/sqlcipher/

Is it possible to read (or decrypt) file with SQLiteKit?

regards.


server side
$ sqlcipher sample.db
SQLCipher version 3.8.6 2014-08-15 11:46:33
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma key="x'257E5178E3BFBC9633ADF5334C9022BC'";
sqlite> create table hoge (id int primary key, name text, description text);
sqlite> insert into hoge (id, name, description) values (1, "name1", "description1"), (2, "name2", "description2"), (3, "name3", "description3");

unity
db = new SQLiteDB();
db.Open(sample.db);
db.Key(“0x257E5178E3BFBC9633ADF5334C9022BC");
qr=newSQLiteQuery(db, “select * from hoge;”);qr.Step();qr.Release();

error message
Exception: Error with prepare query! error:file is encrypted or is not a database

 

It's correct, sqlcipher does fake encryption, it does encrypt entire file, it's not use native sqlite api. So it will not work. Sorry.

 

Hi Oksana,

We’re having a problem when using your plugin SQLLiteKit with the new iOS il2cpp compiler (on Unity 5.0.0f4).

The game works fine in Unity editor but failed when running on the iOs device with below error. P.S. The game works fine and could run on device under Unity 4.
System.Exception: Error with prepare query! error:file is encrypted or is not a database
at VCCharacterMotor+<SubtractNewPlatformVelocity>c__Iterator16E.Reset () [0x00000] in <filename unknown>:0

The query is
Select p.* FROM Player AS p
Could you please kindly help?

P.S. I have sent you an email awhile ago as well.

Many thanks!
Regards,
gshape

 

Hi,
Can you show an example with setBLOB. I would like to save images / textures with your tool. Thank you.

 

Even I am getting this bug. How to fix this bug?

 

how to clear password? it seems that "public void Rekey(string hexkey)" can only change password, if we cant clear pw, we will not open db with other db IDE

 

there is

I don't want because it is useless example, texture has to be stored by unity itself (unity does packaging for ache platform in different way, do you want do store an image in database as DDS, then as PNG to support windows and android?)

 

please contact me on email, I will send you SQLite4Subset, il2cpp does't supported by SQLiteKit only sqlitekit4subset.

 

Hi, there is no way to remove password from encrypted database. it's limitation come from sqlite file format itself.

 

Just need the 'reverse' (opposite) of this example.

Code (csharp):

1. byte[] ablob = qr.GetBlob("blob_field");

Nevermind. I'm working on my own (future free release) solution based on Cheetah Datase. I heard too often "do not know" or "it's impossible" about this issue on Unity's forums.

 

Check out the texture doc, they have all examples you need, the tricky thing about storing texture is hardware, like Oksana mentioned, so, if I remember you have to encode/read in png and specify the compression format when you read from the DB.
But yes... the documentation on this db conversion is bad.

ZJP if you want to try other embeded db, look for iBoxDB in this forum, or SQLITE4Unity, they both work very well and iBox's creator is very active on the forum.

 

Hi, Did you see it?
http://answers.unity3d.com/questions/1015840/sqlitekit-supports-ios9-or-not.html

He says SQLiteKit does not work on iOS9.

Thank you.

 

thanks tsubaki!

 

Hi!

Encrypt my data with SQLiteKit
When my data too big (>100 row), born bugs

SQLite step fail! error: database disk image is malformed
UnityEngine.MonoBehaviourrint(Object)
Test:getTable(String, String, String) (at Assets/Scenes/Test/Test.cs:103)
Test:Start() (at Assets/Scenes/Test/Test.cs:30)

Source code test: http://pastebin.com/xGU4GD9c

How to fix this bug?

P/S:Sorry for my bad english

 

Update to Unity 2020.3