Search Unity

  1. Unity 2019.2 is now released.
    Dismiss Notice

SimpleSQL - SQLite integration with Unity3D

Discussion in 'Assets and Asset Store' started by echo17, Jul 12, 2012.

  1. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Yes

    Not that I know of, but I mostly just use queries and modification commands.

    No, it is a compiled dll, but can still be run on the Indie (free) versions of Unity for PC, Mac, and iOS. Android will require the pro version of Unity to run.

    It is a wrapper around the sqlite.dll. So there will be some slight overhead when calling functions, but mostly that is just passing parameters on to sqlite. The majority of SimpleSQL's code is in simplification of connecting to, querying, and updating databases. It also frees you up from having to do a lot of post build work to get your databases to work in xcode.

    Thanks, hope you like it!
     
  2. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I've added a FAQ with information on how to set up your queries with Javascript here:

    http://www.echo17.com/forum/index.php?topic=257.msg431#msg431

    You're getting this error because Unity Javascript files don't see C# files unless those files are placed in the "Standard Assets" folder. See these links for more information:

    http://forum.unity3d.com/threads/29703-Javascript-error-does-not-denote-valid-type
    http://docs.unity3d.com/Documentation/ScriptReference/index.Script_compilation_28Advanced29.html
     
  3. duke

    duke

    Joined:
    Jan 10, 2007
    Posts:
    762
    Right. I was having problems where i'd get blank/default data records back from the database. Turning on the rename feature fixed this (renamed to *.sqlite). A bit odd!
     
    Last edited: May 26, 2013
  4. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    That is strange. I've always set my rename to *.sqlite, so I guess I never came across those oddities. Thanks for the info!
     
  5. DukeGrimm

    DukeGrimm

    Joined:
    Sep 29, 2012
    Posts:
    49
    I'm running Unity3d Pro 4.x
    New Project
    Imported Simple SQL
    Moved Editor and Plugins folder to assets as per manual.
    Did Build All.
    Received missing reference error to UnityEngine every time "SimpleSQLManager" is referenced.


    Full Exception:
    P.S. Yes UnityEngine is referenced, I presume the error is a red herring for some sort of configuration error.
     
  6. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Hey there,

    I replied to your post and email, but I'll add my thoughts here as well in case anyone else is getting the issue. You might try to change your configuration settings from the SimpleSQL menu. Set it to some other platform (like Mac if you have it set to PC) and then set it back again. Wait a few seconds between switching as it takes a little bit for Unity to copy the appropriate dlls into your folders. You'll see the spinning wheel at the bottom of Unity while it copies.
     
  7. DukeGrimm

    DukeGrimm

    Joined:
    Sep 29, 2012
    Posts:
    49
    I found the problem causing my issue. I was running Unity 4.1.1x , Apparently they caused a bug by doing some sort of strong signing of the UnityEngine.dll and UnityEditor.dll which causes the error I was getting when Compiled assemblies reference them.

    Solution: Update Unity to at least 4.1.2f1


    Patch notes from that update:
     
  8. megmaltese

    megmaltese

    Joined:
    Mar 19, 2009
    Posts:
    2,153
    Hi, I'll need this plugin in the future but I would like to see it working.
    Website and also the instructions are offline.
    A video would be nice too.
     
  9. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Looks like the website is working now. My hoster may have been temporarily down for maintenance or something. I'll add the video idea to my list of items to look into. Thanks!
     
  10. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Ah, yes. I had forgotten about the Unity bug. This happened in one of my other plugins and I just had the person roll back to Unity's last stable release. Glad they sorted this out and got a patch. Thanks for the feedback!
     
  11. akasurreal

    akasurreal

    Joined:
    Jul 17, 2009
    Posts:
    439
    I just wanted to say, this has been one of the best purchases I have made. Great job on this!
     
  12. megmaltese

    megmaltese

    Joined:
    Mar 19, 2009
    Posts:
    2,153
    Just went to your website... again down.
    May I suggest you to change your provider? :rolleyes:
     
  13. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thanks, glad you are enjoying it!
     
  14. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Not sure why it goes down, seems to be back up again. Honestly, I am on the site almost constantly (the forum) and have not seen it down once.
     
  15. josemoreira

    josemoreira

    Joined:
    Jul 23, 2012
    Posts:
    35
    Hello echo17!
    I've been looking for a solution to a new game i 'm designing and i found two choices, your SimpleSQL and the SQLiteKit.
    To be honest, i want to buy your plugin. I know your stuff for a very long time now and i know that you are probably one of the most respected developers. And of course, the best support available.

    The thing is, SQLiteKit has two advantages over your plugin SimpleSQL which are: Playmaker support and android basic support as well.
    Do you plan to add this features in a near future as well?
     
  16. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thanks for the kind words!

    Playmaker support has been on my list for a while, and probably due for an implementation. One tricky part of doing this is that SQL is a very dynamic language. Providing a limited interface to interact with your database may actually hinder you, as opposed to being able to fully code a SQL query. I'm a bit biased here, though since I'm a DB programmer by profession in my "real" job :) I'll take a look and see what all might be possible and if it would be beneficial.

    Android basic will be a bit tougher. The way the project is packaged on this platform doesn't allow me to use the same tricks to get around Unity's unmanaged dll for basic license problem. I'll have to look into this, though I've spent literally months already to no avail. Haven't given up yet, though :)

    Sorry I can't give you a definite yes or a timeline. Both of these issues require some R&D, which is always hard to estimate feasibility and duration.
     
  17. josemoreira

    josemoreira

    Joined:
    Jul 23, 2012
    Posts:
    35
    Those features would be so great! I will keep an eye in this thread and future updates ;)
    BTW, i will send an email with a simple question if you could help me with a "thing".

    Thanks for your attention.
     
  18. megmaltese

    megmaltese

    Joined:
    Mar 19, 2009
    Posts:
    2,153
  19. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Oh, those pesky casing issues :) Thanks for pointing this out!
     
  20. RossoXIII

    RossoXIII

    Joined:
    Jan 8, 2013
    Posts:
    10
    So I was trying to find a way to check if a value is already present on a table without iterating trough all the table values.

    After a long time I was able to do it with Java. I am a sql newbie. It might be usefull to someone, so i will post it here.

    var valorV : List.<QueryAnimalUser> = dbManagerUser.Query.<QueryAnimalUser> ("SELECT COUNT(*) AS answer FROM animalStatsUser WHERE animalCode= 70001");

    var valorI : QueryAnimalUser;
    valorI = valorV[0];
    var result : int = valorI.answer;

    if(result > 0){
    // register already exists
    }

    else{
    //do something (insert register)
    }


    Even if there is not a column named "answer" in your database, the get/set must be declared as an "int" on your C# file so Simple SQL can return the value trough it.

    Hope it helps someone :)
     
  21. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Sounds like a good way to do it, thanks for the info. FYI, since you are just wanting to return a single record instead of a list, you can use the function "QueryFirstRecord" instead. It will return a single instance of your generic table definition filled with the first record. It also passes back if the record exists in an "out" parameter so you can quickly see if you have any data.

    Have a look at the API docs for this here: QueryFirstRecord Documentation
     
  22. RossoXIII

    RossoXIII

    Joined:
    Jan 8, 2013
    Posts:
    10
    I didn't knew you had API documentation.... I feel really dumb. Is this new?

    Yep your right, I did not knew the QueryFirstRecord. In my case what I did was enough beacuse there can be only a single record since the AnimalCode acts as the Primary key :)

    Your Plugin is great Echo, as of now with a little efford since i had no SQL background, I am able to do with it everything my project needs.
     
  23. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Yep, it is fairly new, no need to feel dumb :)

    That's great, glad it is working out for you!
     
  24. skinsomnia

    skinsomnia

    Joined:
    Jan 15, 2013
    Posts:
    2
    Hi there,

    I just bought SimpleSQL. Looks great. I am wondering tho, is it possible to store a GameObject variable inside the database?
    I appologise if this was already answered. I couldn't find the answer.

    Regards
     
  25. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thanks for the kind words!

    You can store integers, floats, and strings in a SQLite database (see this link for more information on SQLite's specs: http://www.sqlite.org/datatype3.html).

    You can store the properties of a gameobject, like the transform position / rotation / and scale. You can store things like the gameobject's name, etc. Is there something specific you are trying to keep track of?
     
  26. Welias D.

    Welias D.

    Joined:
    Jun 12, 2013
    Posts:
    36
    After 3 failed attempts of writing and implementing my own sqlite (was so close all three times!) I gave up and ordered this, so far I love it! My time was worth more than this cost!

    Well, I spoke too soon... I am getting the same error I received the last two times I tried to implement my own SQLite so I must be doing something wrong:

    I am using the standard weapons class and when I compile and run I get this error. The sqlite3.dll is there as well as the runtime dll both from the original asset. ok I have copied the database file (.bytes file) in both the root of the game and in the assets and in plugins as well as making sure the locations contain sqlite3.dll and other runtime.dll files that came with the asset.... still no go.

    I am going to dig and see if I can find an answer but wanted to post here since I have already spent 2 days on this.
     
    Last edited: Jun 25, 2013
  27. Welias D.

    Welias D.

    Joined:
    Jun 12, 2013
    Posts:
    36
    Looks like recompiling with x86 architecture instead of x86_64 did the trick. That dllnotfound error is usually so difficult to nail down, glad I got it quick this time! Of course now I wonder if that was all that was wrong with my version of sqlite!!!! oh well... I am moving forward! Thanks!
     
  28. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Awesome, glad you found the solution. Thanks for posting here and for the kind words!

    Happy querying :)
     
  29. Welias D.

    Welias D.

    Joined:
    Jun 12, 2013
    Posts:
    36
    Should this work?

    Code (csharp):
    1. int lastObjectID = dbManager.Execute("SELECT * FROM objects WHERE objectid = (SELECT MAX(objectid) FROM objects)");
    I will keep digging but it is not giving me a very good error, simply stating:

     
  30. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    The "Execute" function is used for non-query processes like UPDATE, DELETE, or INSERT. For SELECT, you'll need to use "Query" instead. To store the results of a query, you'll need to use one of two methods:

    1) ORM (Object Relational Mapping): create a class structure to hold the query results. Store the results, then read through them to get your information

    The ORM class to hold the data might look something like this (note I just made up some fields since I don't know what is actually in your table):

    Code (csharp):
    1.  
    2.  
    3. using SimpleSQL;
    4.  
    5. public class Objects
    6. {
    7.     [PrimaryKey]
    8.     public int ObjectID { get; set; }
    9.  
    10.     public string SomeStringData { get; set; }
    11.     public int SomeIntData { get; set; }
    12. }
    13.  
    14.  
    You could then query and store the information like this:

    Code (csharp):
    1.  
    2.  
    3. using UnityEngine;
    4. using System.Collections.Generic;
    5. using SimpleSQL;
    6.  
    7. public class QueryORM : MonoBehaviour
    8. {
    9.     public SimpleSQLManager manager;
    10.  
    11.     void Start ()
    12.     {
    13.         List<Objects> objects = manager.Query<Objects>("SELECT * FROM Objects WHERE ObjectID = (SELECT MAX(ObjectID) FROM Objects)");
    14.  
    15.         foreach (Objects obj in objects)
    16.         {
    17.             Debug.Log(obj.ObjectID + " " + obj.SomeStringData + " " + obj.SomeIntData);
    18.         }
    19.     }
    20.    
    21. }
    22.  
    23.  

    2) DataTable: store the results of your query directly in a System.Data.DataTable and parse the information. For this method, you'll need to be sure you have selected System.Data from the SimpleSQL options window (go to the Unity menu SimpleSQL > Options). Your database manager will need to have the "SimpleSQLManager_WithSystemData" script attached instead of the "SimpleSQLManager" script. Note that this method does not require you to set up a class to map your data, you just have to parse it yourself:

    Code (csharp):
    1.  
    2.  
    3. using UnityEngine;
    4. using SimpleSQL;
    5. using System.Data;
    6.  
    7. public class QueryDataTable : MonoBehaviour
    8. {
    9.     public SimpleSQLManager_WithSystemData manager;
    10.  
    11.     void Start ()
    12.     {
    13.         int rowIndex = 0;
    14.  
    15.         DataTable dt = manager.Query("SELECT * FROM Objects WHERE ObjectID = (SELECT MAX(ObjectID) FROM Objects)");
    16.         foreach (DataRow dr in dt.Rows)
    17.         {
    18.             Debug.Log("Row: " + rowIndex);
    19.  
    20.             foreach (DataColumn dc in dt.Columns)
    21.             {
    22.                 Debug.Log(dc.ColumnName + " : " + dr[dc.ColumnName]);
    23.             }
    24.  
    25.             rowIndex++;
    26.         }
    27.     }
    28. }
    29.  
    30.  
    For more information, please check out the user manual and API links on the website here: http://echo17.com/simplesql.html
     
  31. Welias D.

    Welias D.

    Joined:
    Jun 12, 2013
    Posts:
    36
    Thanks! Those options work perfectly!
     
  32. Ostwind

    Ostwind

    Joined:
    Mar 22, 2011
    Posts:
    2,768
    I didn't read every post but what are you doing in your code when you say it requires Android Pro but works with iOS basic? Plugins have always been supported on mobile platforms no matter if its basic or pro but with desktop it has been required to have pro for native code plugins.
     
  33. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,085
    I've purchased SimpleSQL yesterday and I have to say: This is awesome. Thank you for creating it and publishing via the Asset Store!!! The documentation is very complete (including "basics" I wouldn't expect), it works quite robust so far (haven't deployed to devices, yet, though ;-) ) ... and is just really fun to work with.

    One thing I'm missing (but it's not a biggie): Support for the Guid data type, also the nullable version of it. I noticed that it works for DateTime (also DateTime?, which is awesome ;-) ). I'm working around this using a string field with MaxLength(36) and having some custom properties to be able to work with type-safe GUIDs. Having "native GUIDs" would be really awesome (of course, SQLite doesn't support that, but "native" in my classes and doing the conversion stuff "under the hood" would still be better than having to write that boilerplate code myself ;-) ).

    Another thing that I'd like to have: An attribute with which I can tell the OR-mapper to stay away from certain properties. EDIT: Well, actually, this is just missing in the PDF documentation ;-) ... and I just found out that the attributes are under "Extra Docs" in the API documentation. Ok, that problem solved ;-)

    In any case, that's all really just "nice-to-have"-stuff ... I really like the way this whole package is set up and look forward to incremental improvements ;-)
     
    Last edited: Jun 29, 2013
  34. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    There is actually some unmanaged code in the sqlite3.dll file that is required for SimpleSQL. iOS doesn't require this file since all apple devices already come with sqlite3.dll. Android does not, unfortunately.
     
  35. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thanks for the kind words! I'm glad you are enjoying SimpleSQL.

    As you've discovered, sqlite actually only supports 5 data types: NULL, INTEGER, REAL, TEXT, BLOB. This means that any other type that you come up with will just be a derivation of one of these. In the case of GUID, it would just be a TEXT field as you've done.

    http://www.sqlite.org/datatype3.html

    When you talk about having custom properties, what exactly are you looking for? There's not much that can be done with a guid since it is just a string, so I'm not sure what customization you would need for that.

    Yeah, I felt the attributes were more of an API thing, so I put them there. I'll probably update the docs to include some of the API stuff.
     
  36. tal1m0n

    tal1m0n

    Joined:
    Jun 9, 2013
    Posts:
    5
    Hi I just purchased the SimpleSQL to use with Playmaker. Learning C# and now SQL is very much on my list of things to do but at the moment this is kind of going over my head. I've looked through the basic examples, it seems straight forward to pull data from the database and manipulate it using scripts, but how do I get the information from the database and put them into variables that Playmaker can access?

    I started creating a card game / turn based strategy where you put together deck from large list of cards, draw cards from the deck to the hand, champ card goes on main gameboard, land / infastructure cards go to village board, and dead / used cards go into graveyard. Realized very quickly that I'd need a database to keep track of all the stats and information.

    The solution is probably something very obvious. I'll keep working through the documentation and hopefully figure it out soon, but would be great if there was a Playmaker custom action for the SimpleSQL functions.
     
  37. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Playmaker scripts are highly requested and on my todo list. I have no idea of the implementation date for this, however. You can subscribe to my social media links below (Facebook, Twitter, Google+) to get updates.
     
  38. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Good news everyone!

    I created some Playmaker scripts for SimpleSQL that will allow you to query or update your databases! You can find the scripts (and examples) on the website at:

    http://echo17.com/simplesql.html

    Be sure to read the "readme.txt" file in the action script folder since it has important information about how to setup your project before using the scripts.

     
    Last edited: Jul 3, 2013
  39. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    More Good News!


    SimpleSQL has been verified to work on the Android Free license! I've submitted an update to the Asset Store to indicate this (with graphics and text), but your current version will work just fine as well.

    I didn't realize that Unity had relaxed their stance on non-managed plugins for the free licenses. Sorry if anyone passed this plugin up due to thinking it wouldn't work on Android Free. Happy querying!
     
    Last edited: Jul 20, 2013
  40. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    SimpleSQL 1.3.1 Approved and in the Asset Store

    Download here: http://u3d.as/content/echo17/simple-sql/387

    Be sure to back up your work and download the update into a new project. You can copy the code over into your current projects using your OS's file system to ensure no import errors occur.

    Check out this link for a complete list of changes: Changelog

    FYI, you can follow these updates on my Facebook, Google+, and Twitter feeds as well (links in my signature)
     
  41. akasurreal

    akasurreal

    Joined:
    Jul 17, 2009
    Posts:
    439
    Just curious, is that changelog missing stuff? The version I have is 1.2.2 but don't even see that in the list. Was it a full 1.1 to 1.3 just for a cosmetic change? =)
     
  42. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    When you submit a package to Unity you have to provide a unique version number. Sometimes I realize after I've submitted that I've left something out, so I'll resubmit (and give a new version number). Since there really isn't any change in these little bumps, I usually leave them off the change log.

    I bumped it from 1.2.2 to 1.3.1 (was supposed to be 1.3.0, but again forgot something :) ) for the cosmetic change. I thought it warranted a new minor release version due to the fact that SimpleSQL has been tested to run on both versions of Android.

    [EDIT]

    Realized that I had indeed left off version 1.2.2 from the change log. It's there now, thanks!

    [/EDIT]
     
    Last edited: Jul 25, 2013
  43. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Good News!

    SimpleSQL version 1.4.0 is now available in the asset store. This version adds some generic table, row, and column classes that let you store data without using an ORM or System.Data classes. These new classes are extremely lightweight and can be useful for quickly grabbing a value or values without having to set up an ORM class.

    Also, this eliminates the need to use the System.Data classes with Playmaker! Now you can use Playmaker with mobile devices without a pro license. I've updated the Playmaker scripts and examples here:

    http://echo17.com/simplesql.html



    The updated API is available here:

    http://echo17.com/support/simplesql_api_documentation/namespaces.html

     
    Last edited: Sep 11, 2013
  44. RossoXIII

    RossoXIII

    Joined:
    Jan 8, 2013
    Posts:
    10
    Hello echo. I am one of your happy customers :)
    Are you planning adding support for WP8?
    It's all I i currently need to make a working version of my game on that platform.

    All the best!
     
  45. smallUser

    smallUser

    Joined:
    Sep 18, 2013
    Posts:
    4
    Hey Echo,
    Invoice 301276157
    Date 08/21/2013

    Looks like this can not works with MS SQL, i mean how to use remote MS SQL ?
     
  46. jashan

    jashan

    Joined:
    Mar 9, 2007
    Posts:
    3,085
    Just FYI - you should definitely not post your invoice number on a public forum ... and you may consider editing the post and remove that information and send a private message instead ;-)
     
  47. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I don't currently have WP8, so I'm not sure what all would be required to make it work there. Have you tried the Mac/PC settings in the Control panel? That usually covers most platforms.
     
  48. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    SimpleSQL uses sqlite, not MS SQL. I have no plans to integrate MS SQL support since this is not the engine it runs on. Also, SimpleSQL is designed for local db access (storing app settings, etc) and won't work with remote databases.
     
  49. Tripwire

    Tripwire

    Joined:
    Oct 12, 2010
    Posts:
    442
    Hi echo17,

    I'm making an app about money on iOS and Android, and I need some advice on how to use SimpleSQL plugin to achieve the following.
    I have a user managemen, each user has:
    Name
    Gender
    Picture
    Pincode (password)
    Cash amount
    Cash the user saved
    Pictures for the Wishlist (these can be multiple items (i think of saving links to the pictures on the device itself)
    What he bought
    what he saved

    It's a lot of data. How would you do this in SimpleSQL?
     
  50. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Whooo-boy, that's a huge question! I'm glad to give some guidance, but database design is a pretty big topic that can easily encompass a full college curriculum. :eek:

    I'll give you a simple high-level view of how I would go about this:

    Create these tables and their fields with types in parenthesis:

    table: User
    fields: UserID (INTEGER), Name (TEXT), Gender (TEXT), PicturePath (TEXT), Pincode (TEXT), CashAmount (INTEGER), CashSaved (INTEGER)

    table: WishList
    fields: UniqueID (INTEGER), UserID (INTEGER), PicturePath (TEXT)

    table: Purchased
    fields: UniqueID (INTEGER), UserID (INTEGER), Description (TEXT)

    This will create a relational database structure where the User table is your primary table and the WishList and Purchased tables are linked to the User table by the UserID field. This allows you to have a single entry for your user, with multiple entries for the wish list and purchased items.

    I wasn't sure what you meant by "what he saved" since you already have a CashSaved field, so I don't know if this should be stored in a different table, or in the primary User table. If these will be a list of items, then you would probably want to create another table like the WishList and Purchased tables to store these since the relationship is many-to-one.

    When querying your user information, you can provide a sql statement something like:

    Code (csharp):
    1.  
    2.  
    3. using UnityEngine;
    4. using System.Collections.Generic;
    5. using SimpleSQL;
    6.  
    7. public class ShowUser : MonoBehaviour
    8. {
    9.     public SimpleSQLManager manager;
    10.     public int userID;
    11.  
    12.     void Start ()
    13.     {
    14.         string sql = "SELECT " +
    15.                 "u.UserID, " +
    16.                 "u.Name, " +
    17.                 "u.Gender, " +
    18.                 "u.PicturePath, " +
    19.                 "u.PinCode, " +
    20.                 "u.CashAmount, " +
    21.                 "u.CashSaved, " +
    22.                 "w.PicturePath, " +
    23.                 "p.Description " +
    24.             "FROM " +
    25.                 "User u " +
    26.                 "INNER JOIN WishList w " +
    27.                     "ON u.UserID = w.UserID " +
    28.                 "INNER JOIN Purchased p " +
    29.                     "ON u.UserID = p.UserID " +
    30.             "WHERE " +
    31.                 "u.UserID = ? ";
    32.  
    33.         bool recordExists;
    34.         User_Complete user = manager.QueryFirstRecord<User_Complete>(out recordExists, sql, userID);
    35.  
    36.         if (recordExists)
    37.         {
    38.             Debug.Log("User Found!");
    39.             Debug.Log("UserID: " + user.UserID);
    40.             Debug.Log("Name: " + user.Name);
    41.             Debug.Log("CashAmount: " + ((float)(user.CashAmount) / 100.0f).ToString("$#,###,###,##0.00"));
    42.         }
    43.     }
    44. }
    45.  
    46.  
    With User_Complete table structure like:

    Code (csharp):
    1.  
    2.  
    3. using System.Collections.Generic;
    4. using SimpleSQL;
    5.  
    6. public class User_Complete
    7. {
    8.     [PrimaryKey]
    9.     public int UserID { get; set; }
    10.  
    11.     public string Name { get; set; }
    12.  
    13.     public string Gender { get; set; }
    14.  
    15.     public string PicturePath { get; set; }
    16.  
    17.     public string PinCode { get; set; }
    18.  
    19.     // Note that this is stored as an integer. We will need to divide by 100 to get the actual amount
    20.     public int CashAmount { get; set; }
    21.  
    22.     // Note that this is stored as an integer. We will need to divide by 100 to get the actual amount
    23.     public int CashSaved { get; set; }
    24.  
    25.     public string PicturePath { get; set; }
    26.  
    27.     public string Description { get; set; }
    28. }
    29.  
    30.  
    Note that I store the cash values as integers without any decimal points. So $622.43 would be stored 62243. At runtime, when I am displaying the amount, I divide by 100, so the final display comes back to 622.43. I've learned the hard way that sqlite (the underlying database structure) does not play nice with decimals or floats and will introduce rounding errors. That is why I store all values as integers and then just format them as needed when displaying. You would need to have some logic in place for localization. I believe some Asian currencies use three decimal places instead of the western two, so that gets a bit tricky.

    Also note that the ORM class structure used to store the data actually encompasses multiple tables. That is because we are doing joins in our query.

    I store the picture paths as text. That way I can load picture resources at runtime by using Unity's LoadResource. Have a look at this FAQ for more information:

    http://www.echo17.com/forum/index.php?topic=257.msg763#msg763

    Again, this is just the tip of a very massive iceberg. I don't have any specific examples or books to recommend to you, but doing a Google search on sql database design should point you in the right direction, specifically sqlite database design. If you want to see a working example of a financial application that uses sqlite, you can take a look at my program "iVelopes" which is a personal budgeting app:

    http://echo17.com/ivelopes.html