SQLiteKit

I get the following error when importing this into my project:

Assets/sqlitekit/DemoObject.cs(268,81): error CS0117: `Application' does not contain a definition for `streamingAssetsPath'

 

It's defenetly the project settings problem. Application - is Unity Class

Which OS android, ios, pc?

Did you use stripping level "micro corelib " for iOS ?

 

I'm using PC/Mac ... everything else is working fine, and in MonoDevelop autocompletion works for "Application.streamingAssetsPath"

I'm going to just start a blank project and see if importing works. PS. I'm on Unity 4 Pro

 

Please email me your project. I will look at.

 

Hi,

Is there any reason why I could not access two DB at the same time?

 

May be, I never try it. there is may be a problem.

Could you send your project to me?

 

Yesterday we purchased your sqlitekit and import that package in a new project but its giving me a bunch of errors. Those errors saying that "Assets/sqlitekit/Playmaker Integration/Scripts/SQLiteOpenAction.cs(7,35): error CS0246: The type or namespace name `FsmStateAction' could not be found. Are you missing a using directive or an assembly reference?"


So what is the solution for that?


OK... i got it. After reading readme.txt. I just deleted that playmaker folder from it and problem solved.

 

Hi, i'm having trouble with the foreign keys, i'm using on delete cascade but this appears is not working right, any suggestion?

i read that sqlite supports foreign keys until 3.6.19, so i don't know if this is a problem?

 

Hello darkmax,

Could you send me an example of your code, basically it is port of 3.7.7 so it have to work, but i never try it.

Thanks in advance,

 

query.GetFieldType() only returns a number. - Would you please fill me in on what type is represented by what number?
Some more documentation would be really helpful!

PS: At the moment I'm especially interested in type 5.

 

I have more questions:

• Is there a way to know if a DB is open? I guess db.Connection() returns null if no DB is open? Is there an easier way?
• Is it good practice to Open a DB that I need occasionally in Start and only close it in the end?
• Does anything bad happen if I never close it?

 

1 Right now this is the only way : db.Connection() , but I will strongly recomend do not do that at all! the best practice is catch exception on db.Open() and make db = null if it fail . this way you will now for sure that database is not ready.
2. MonoBehavior -> on Start() {create database} on OnDestroy( close database ). if live time of this object represent time line for you database, but if it permanent daabase I will recomend to check my examples for multithreading, or use Singletone. But the best is the part of my Playmaker Integration code, check SQliteAsync and ThreadQueue, you will love it.
3. Not much, but if you in Editor you will expect difficulties to reopen database. It will require to restart Editor.

Best regards

 

Please check code inside GetString or GetInteger, you will find TYPE codes: Sqlite3.SQLITE_TEXT, Sqlite3.SQLITE_INTEGER, Sqlite3.SQLITE_FLOAT..... This is internal sqlite3 library types.

 

Hello darkmax

I created small test and it works perfectly. Could you share database creation code.
I post my example:

using UnityEngine;
using System.IO;
using System.Collections;

public class NewBehaviourScript : MonoBehaviour {

// Use this for initialization
void Start () {
string dbfilename = Application.streamingAssetsPath+"/tsss.db";
SQLiteDB db = new SQLiteDB();
if(File.Exists(dbfilename))
File.Delete(dbfilename);
db.Open(dbfilename);

SQLiteQuery qr;

qr = new SQLiteQuery(db,"PRAGMA foreign_keys = ON;");
qr.Step();
qr.Release();

qr = new SQLiteQuery(db,"CREATE TABLE child (id INTEGER PRIMARY KEY, parent_id INTEGER, description TEXT, FOREIGN KEY (parent_id) REFERENCES child(id) ON DELETE CASCADE);");
qr.Step();
qr.Release();

qr = new SQLiteQuery(db,"INSERT INTO child(description) VALUES('root');");
qr.Step();
qr.Release();

qr = new SQLiteQuery(db,"INSERT INTO child(parent_id,description) VALUES( 1, 'child');");
qr.Step();
qr.Release();

qr = new SQLiteQuery(db,"DELETE FROM child WHERE description = 'root';");
qr.Step(); // comment to see that records are inside
qr.Release();

db.Close();

}

// Update is called once per frame
void Update () {

}
}

 

Was there any resolution to this issue? I've been fighting this one on Android for a while now with no luck.

 

Soon after posting I realized I wasn't on the latest version of SQLiteKit. Since updating I have not seen the issue described above.

 

How would you save a memorystream to a SQLite table?

 

Hi Robin,
You will need take byte[] array from your memory stream and save it as blob.

 

I'm new to Unity and I was excited to see some solutions for integrating with a database. One thing I'm not clear on is if SQLiteKit works with Android Basic? Some of the other solutions out there only support Android Pro (for reasons I don't understand). Thanks!

 

Sorry for late answer, but email notification on which i relay had fail.

Yes it works with Android Basic, because it don't use system.data which is require for other plugins and which is available only in Pro,
Basically my library it just C# code without any funny includes - no dll ever, (the code is open, so you will enjoy full source access).

 

Hi Oksana,

Great Project, considering to buy it but need some more information.

Do you have stats on the performance of the database on various platforms

Amount of time it takes to Inserts/Read/Del, etc... for various records. IOS, Android, WebPlayer, Mac, OS ?

 

Hello rocki,

nop, I newer try to do so It really depends on device SSD speed, but visually i remember that simple select on my android phone gives me about 500+ reads per second, but again it really depends on device. One more: if you are planning to use asynchronous database (In my demos Playmaker scene) speed will drops more as soon it has queue for income jobs and results. but as benefit your main thread will not wait the reading/writing at all and you may collect statistics at run-time!.

Overall compare to native libraries the speed will be significally slower this is the price to have pure C# sqlite implementation - no native calls or wrappers in my library. But as bonus it make possible to work from memory streams instead file streams to be able accommodate sqlite on WebPlayer where is no file access available.

 

I'd like to call a bunch of queries (INSERTs AND UPDATES) in a row. How can I execute a whole bunch of queries at once (just by passing the whole script as one string)?

 

Hello TomH

Yes you could no problem. You just need to put ':' between each command.
But keep in mind that sqlite has limitation for command string.

How many commands do you need to submit ?

 

Well, more or less a complete dump. I currently use a SQLite database in a standalone build, but I want to be able to test it in the webplayer. So on the start of the webplayer, I want to load SQL-Script and create the whole database in memory.
If you have any suggestions how to make this better, I'm all ears

 

Sure! Just copy your database as it is and open! It's much mose simpler.

 

Hi,
I currently have this kit but can't figure out if it will work for my use case.
I want to create an encrypted db to hold my apps data, which is used to populate the gui pop up lists etc
One use case would be something like a music database where user could select an artist, which would list albums, album select would list tracks and the tracks would have image and streaming preview link.
I would create the db using the google import or in one of the SQLite manager tools, which would be used to periodically update the sb prior to app updates. I need to also encrypt the db so user cannot modify or read it directly, as it may contain some other info.

How can I do this?

Encryption is using SQLCipher and as near as I can determine none of the available manager tools work with this format.

Having an easy to use gui frontend to manage db is part of the app workflow on the maintenance end, so I'm a bit stuck atm.

ty!

 

Hello,

Step 1. Create Google SpearedSheets with your information.
Step 2. Setup Inporter from SQLiteKit to import in to database.

When depends on how you wood like to update database with or without build you will have two options:

1. Put database to a web server and download on startup if require.

2. Put database in to build and do update entire application with new database. - it is more prefrable if you have video or images to be updated

Last Step - read database to fill your gui!

Btw encription is build in with SQLiteKit. so you dont need any extra tools.

Please let me know if you have more quesions

 

Hi,
Thanks for the info.

Some more questions:
I ran the encryption demob but it works of a new db created in real time and
According to http://stackoverflow.com/questions/5648698/how-to-encrypt-an-database-using-sql-cipher?rq=1 you should not encrypt an existing db

What about if I use an alternate DB manager tool to construct my db (as it may have images as blobs - or I don't want to use google spreadsheets due to security) how do I encrypt an existing db if I just drop the db into resources folder?

I'm using SQLite Expert to manage the database, which has type BINARY for image data. How do I map this to a byte[] in Unity without getting a wrong field type exception?

Is it also possible to use another db import apart from the google one (which looks like it's basically a CSV imported?)

Also, the documentation states the spreadsheet has to be shared but it seems it actually needs to be published to web in order to be read properly - the demo url is a published one and I could only get it to work the same when I actually use a published link)

++ the documentation is a bit lightweight. You need more examples, most especially on the Selection side showing query construction with Where filtering and Grouping.


ty!

 

Hello,

Look the best for you it will keep my sqlite database feed by importer from google documents and used unecripted in development mode. when you diside to publish for user just copy and open cloned database with PRAGMA key= .... and close - DONE! - the best it do with editor script.

The is no problem to ecript database and after open again and do UPDATE or INSERT evet CREATE TABLE is ok! with my library.

The Google spreadsheets is the most secure that you can have on the market specially if you need share your data with other editor.
It give you VERY LONG url with is impossible to generate rundomly.

There is no problem with field type just yous BLOB as type.

About keeping images in sqlite database, for that reason? is it dinamic data? You have to really sure that you plan to do because keepeng big files is really bad idea - the first for different platform Unity convert images to different formats - are you plan to keep your image in defferent formats as well ? second loading will require tripple size in memory. 1. byte[] in database, them byte[] in memory then you will copy and last one in tecture buffer befor utity converts in in to platform friendly format for you, and speed ofcourse.

About Exporter, I didn't get do you want to upgrade existing one to import different database or source has to be CVS? if your is second it possible but I can't see a reason why - it 's really much more product pipe-line unfriendly then google. any way as fast fix you could import your CVS to google and import from there - you will get the best place to edit+share your data as well.

Best regards,

 

Hi,
Thanks for the reply.
I did some more testing after I typed my last questions and resolved a few issues myself.

The reason Google docs is not the best is it does not scale for anything but a very simple table system.
If you have a db with primary and FK structure you can't really use Google docs, and hacking the additions in code via post import sql is messy.

I'll drop the images as per your analysis, which I'd also more or less come to the same conclusion.

I think I can use SQLite Expert to manage it, and your tool to process it, plus a small editor script to encrypt it should be fine.

ty!

 

Hi me again, I'm having some trouble with copying the db from streaming assets to persistent data path, it was working great before but when i update to unity 4 it says that "You are trying to load data from a www stream wich has not completed the download yet."
This also happen with the scene demo that is provided on sqlitekit when i click the button "Run sqlite test for streamingAssets db which moved to persistent data"

Note: This only happen on android, on windows is working like it should, but on android doesn't, on ios, mac and web i haven't tried.

 

Hello darkmax,

Oh, yes, this is known bug in unity, I will rewrite demo to avoid it.
The problem is that if I do www get and then while(www.done) { ...wait... } it doesnt work, unity still didn't finish even done flag is on.
Only one way to do it work to make StartCorontine call with the same code and when it will works.

 

you mean StartCoroutine calling Download(www), or moving all the code of the button that copy the db from streaming assets to persistent data in coroutine, I don't have the device here to test?

update: I have now the device and i need to copy all the code of the button and now is working, because just calling download with startcoroutine doesn't work.

Thanks for the solution

 

good, the main idea to load database by www from startcorontine function some how. and after do open.

 

Hello,

I've just purchased SQLiteKit and get errors trying to import data from Google Spreadsheet, (including the sample) data. See attached screen shot and my db link settings (the image is from trying to import the test data included with the package, but the error message is the same when I try my link)

https://docs.google.com/spreadsheet/ccc?key=0AnD1UWAUW_pqdHFQTHdWT3dSLXo2THdLRkJOamJUU3c&usp=sharing

Am I doing something wrong or is this a bug?

 

Here is problem with your column name, according to sqlite3 documents column name has to start from characters, your is numeric.

please try to make something like clmn1,clmn2,....

Please let me know if it helps

 

Hi Oxana,

Thanks for your help, but it did not work. I continue to get the same error. (Yesterday I tried to import your sample google spreadsheet with the no result)
Here is the fixed table:
https://docs.google.com/spreadsheet/ccc?key=0AnD1UWAUW_pqdHFQTHdWT3dSLXo2THdLRkJOamJUU3c&usp=sharing

Here are the two errors I get:
1)
Fail to Open database: C:/Users/Pawel/Documents/Unity Projects/arraymaker/Assets/StreamingAssets/easy.db
With exception:Error with opening database C:/Users/Pawel/Documents/Unity Projects/arraymaker/Assets/StreamingAssets/easy.db !
UnityEngine.Debug:LogError(Object)
SQLiteKitEditor:OnGUI() (at Assets/sqlitekit/Editor/SQLiteKitEditor.cs:93)
UnityEditor.DockArea:OnGUI()

2)

ArgumentException: Getting control 2's position in a group with only 2 controls when doing Repaint
Aborting
UnityEngine.GUILayoutGroup.GetNext () (at C:/BuildAgent/work/7535de4ca26c26ac/Runtime/ExportGenerated/Editor/GUILayoutUtility.cs:511)
UnityEngine.GUILayoutUtility.DoGetRect (Single minWidth, Single maxWidth, Single minHeight, Single maxHeight, UnityEngine.GUIStyle style, UnityEngine.GUILayoutOption[] options) (at C:/BuildAgent/work/7535de4ca26c26ac/Runtime/ExportGenerated/Editor/GUILayoutUtility.cs:304)
UnityEngine.GUILayoutUtility.GetRect (Single minWidth, Single maxWidth, Single minHeight, Single maxHeight, UnityEngine.GUIStyle style, UnityEngine.GUILayoutOption[] options) (at C:/BuildAgent/work/7535de4ca26c26ac/Runtime/ExportGenerated/Editor/GUILayoutUtility.cs:295)
UnityEditor.EditorGUILayout.GetControlRect (Boolean hasLabel, Single height, UnityEngine.GUIStyle style, UnityEngine.GUILayoutOption[] options) (at C:/BuildAgent/work/7535de4ca26c26ac/Editor/MonoGenerated/Editor/EditorGUI.cs:4845)
UnityEditor.EditorGUILayout.IntField (System.String label, Int32 value, UnityEngine.GUIStyle style, UnityEngine.GUILayoutOption[] options) (at C:/BuildAgent/work/7535de4ca26c26ac/Editor/MonoGenerated/Editor/EditorGUI.cs:4088)
UnityEditor.EditorGUILayout.IntField (System.String label, Int32 value, UnityEngine.GUILayoutOption[] options) (at C:/BuildAgent/work/7535de4ca26c26ac/Editor/MonoGenerated/Editor/EditorGUI.cs:4085)
SQLiteKitEditor.OnGUI () (at Assets/sqlitekit/Editor/SQLiteKitEditor.cs:290)
System.Reflection.MonoMethod.Invoke (System.Object obj, BindingFlags invokeAttr, System.Reflection.Binder binder, System.Object[] parameters, System.Globalization.CultureInfo culture) (at /Applications/buildAgent/work/84669f285f6a667f/mcs/class/corlib/System.Reflection/MonoMethod.cs:222)

 

Please check your sharing settings. I found importer receive wrong ansver from google because no share CVS version of the document/

please check Google SpreadSheet Importer in https://docs.google.com/document/d/1GnXXOFCIcpoOFWiwEL3XioiaIYuLqL5YascgIl1GK9s/pub

Or please share your file with me I will show how to do that.

Thanks.

 

If it is improper sharing setting, how come I get exactly the same failure when I try to import YOUR link?

I tried both the Sharing settings in Google spreadsheet (the Public and open to those with link just the way it is showed on your example).

As to my file -- there is nothing I can send you. I wasn't able to do anything with your software since I've got it yesterday...

 

Additional development on my part.

I've been able to import my database on the mac -- the PC still gives me the same errors as before, even though I am using the same links. I assume I can copy the database to my StreamingAssets folder from the Mac to my PC and it will work from there... Am I right? -- If so, I won't waste time to find out why my PC does not play nice with your importer...

 

Idea, maybe where is a space in filename, maybe, let's have a chat, my skype: connection_1 please contact me. or send email to orangetree.unity@gmail.com

 

Can anyone comment on the license information of this plugin? Unity is having issues displaying the license agreement.

 

There is only MIT licence from SQLite CSharp project. so there is no limitation to using in commercial products.

The unity licence is different. you can't resubmit a code back to asset store or any there else as component. only as end-user product.

This is in short.

 

Hi Oksana,

Is it possible to store a GameObject with many components. Would this be stored as a Blob ?

 

Hi!
I just got SQLite Kit and try to make my game "Dungeon Memory" a bit more dynamic.

I started with the demo file and I can read values from my spanking new database, but I have a problem looping the "Read Database" in the example files for SQLite. I watched the videolink provided on the "documentation" and it explains how the demo works, but nothing about the SQL language. I tried the link to the SQLite docs, but they are gibberish to me.

The provided command is: SELECT * FROM Level1 WHERE id=?
Level 1 is my table, and it reads the value that is inside the "Bind" action.
So I can get the values from one row, but then it stops, no matter how clever my "loops" are made.

How does one make a read db - put values in variables loop with PM and SQLite Kit?

/s

 

I just purchaed the SQLiteKit from the Unity Store. Loading the 3 script demo's work fine for me but when trying the Playmaker demo all the two scripts are detached from the objects.

I've just reinstalling both Playmaker and SQLiteKit but to the same effect.

Do you have a copy of the Demo or perhaps let me know what scripts go onto The "Mono Behaviour Scripts" on the Main camera and "Mono Behaviour Script" attached to the Playmaker GUI.

 

Hello, it's been a week since I've asked this question. Can you please answer. Thanks.

 

I will find out why the demo scene has been lost after uploading to asset store,

Please email me I will send the package directly to you.

orangetree.unity@gmail.com

 

Did you try to do loop with PM,

I think the best if we chat a little via skype ot teamviewer!
I will make for you are small demo.

Please email me: orangetree.unity@gmail.com