Search Unity

  1. Calling all beginners! Join the FPS Beginners Mods Challenge until December 13.
    Dismiss Notice
  2. It's Cyber Week at the Asset Store!
    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
    Alright, I think I've ironed this bug out. Check out version 1.6.3 available in the Asset Store now. I also tested on the Android device itself and it worked for me. Let me know how it goes for you!
     
    Nadan likes this.
  2. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    334
    Working on the editor, but not yet on the device. I need to investigate this.
     
  3. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Try building the demo that comes with the plugin onto an Android device. I was able to run the simple query scene successfully. If that works, you can rule out SimpleSQL and gradually narrow down what is failing in your particular setup.

    There were a lot of changes in Unity 5 which could easily account for problems with other plugins or custom scripts.
     
  4. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    334
    I tried to build the 07_SimpleTable, but it's only saying "Note that the database manager...". When in editor it shows the database table.

    But now I think I'm onto something. Remember when I said I deleted the SimpleSQL and downloaded it again? Well I did that, and I also deleted the plugins folder. This sounds like why it's working on editor and not on the device. So when I copy the folders Plugins and Editor to the root it says:

    Code (csharp):
    1. The GUID for Assets/Plugins/SimpleSQL_Runtime.dll is already in use by Assets/SimpleSQL/Plugins/SimpleSQL_Runtime.dll. Assigning a new guid.
    2.  
    3. The GUID for Assets/Editor/SimpleSQL_Editor.dll is already in use by Assets/SimpleSQL/Editor/SimpleSQL_Editor.dll. Assigning a new guid.
    And the I try to build, an error:

    Code (csharp):
    1. Found plugins with same names and architectures, Assets/SimpleSQL/Plugins/SimpleSQL_Runtime.dll () and Assets/Plugins/SimpleSQL_Runtime.dll (). Assign different architectures or delete the duplicate.
    2. UnityEditor.AndroidPluginImporterExtension:CheckFileCollisions(String)
    3. UnityEditorInternal.PluginsHelper:CheckFileCollisions(BuildTarget) (at C:/buildslave/unity/build/Editor/Mono/Plugins/PluginsHelper.cs:25)
    4. UnityEditor.HostView:OnGUI()
    5.  
    6. Plugins colliding with each other.
    Now what do I do? Do I delete the Plugins and Editor folders under the SimpleSQL folder?
     
  5. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    334
    It seems that after I delete the Plugins and Editor folders to prevent from "Plugins colliding with each other" it brakes.
     
  6. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I would wipe out the SimpleSQL folder entirely. Also, probably better to kill the "Plugins/Android" folder as well since it has the Android sqlite library located there. You may have to delete the folders in your OS's file system if Unity won't let you. Then redownload SimpleSQL and see if that works. Unity has some issues when updating assets, so we developers often recommend wiping out the existing plugins before downloading new ones. (And of course, always back up your work before upgrading!)

    Try creating a new project with just SimpleSQL and build that. If it works, then it is definitely a Unity Asset Database glitch that is causing the issue in your project.
     
  7. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    334
    Ok, I tried that. I started a clean project and it says: Plugins colliding with each other.

    Can you have folders /Plugins and /Editor at two place? In the root and in the SimpleSQL folder and it should work that way?




    I also tried _not_ to copy the /Plugins and /Editor folders to the Asset root like it says in the SimpleSQL instructions (pdf)

    "Your final game project layout should have the two dll's in their respective directories:
    SimpleSQL_Editor.dll in the Editor directory and SimpleSQL_Runtime.dll in the Plugins directory"

    This will build the game without "Plugins colliding with each other." error, but it's not working on the device. Only reading the "Note that database..." when it should show the "Weapons, name: sword" etc. that are on the example project.
     
    Last edited: Mar 6, 2015
  8. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    You shouldn't try to move the SimpleSQL folders around as this will definitely cause problems since the plugin has to extract the appropriate libraries. It is looking for the folder structure it comes shipped with in order to find the right folder to extract to. If you move the files around, it will extract to a different location leaving you with two copies of the runtime library.

    Yes, you can have multiple Editor and Plugins folders in your project. Unity will recognize them all and process the scripts contained in the correct order. See these links for more details:
    http://docs.unity3d.com/Manual/ScriptCompileOrderFolders.html
    http://docs.unity3d.com/Manual/SpecialFolders.html

    Sorry, but I can't replicate this issue. I am able to successfully build, deploy, and run a demo scene on my Asus tablet. Perhaps it has something to do with your Android settings, or the version of JDK or the Android SDK, or your platform, or any other number of factors particular to your setup.

    After you create a new project and import SimpleSQL (without moving any files), go to "Tools > SimpleSQL > Options" switch to any other platform, wait a bit for the spinning wheel to stop, then switch to Android and wait again for the spinning wheel to stop. Immediately set up your build from that without changing anything. If that fails, it is likely something to do with your device, build settings, or development machine because I am not able to get any errors with this setup.
     
    Last edited: Mar 6, 2015
  9. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    334
    I think I found a solution for the problem on my end.

    This morning I read the release notes of Unity 5 and noticed this:

    https://unity3d.com/unity/whats-new/unity-5.0

    Unity editor is now 64-bit!
    • Note that this affects native plugins used in the editor, which now also need to be 64-bit.
    Then I installed the 32-bit version of Unity 5 instead of 64-bit version that I had before, imported SimpleSQL from Asset Store and it worked right of the bat.

    Maybe you can replicate the issue if you download the 64-bit version of Unity 5?
     
  10. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I'm using the 64 bit version on both pc and mac. I built the android test using 64 bit mac.
     
  11. Nadan

    Nadan

    Joined:
    Jan 20, 2013
    Posts:
    334
    I build iOS version with 64bit Mac and the Android version with 64bit PC/Windows 7. It seems that the 32bit PC/Windows 7 build doesn't always work either. I couldn't build working version twice, restarting Unity helped but it was very random. I also tried to change the options as you recommended. Frustrating because I didn't get any errors, then tested with device and sometimes it worked and sometimes not.

    But I managed to ship updates for my game as 32bit android versions. As I said before there was no problem with Mac and iOS. Hopefully coming Unity 5 patches will fix this in the future. I didn't do any changes to the database/SimpleSQL. So it could be that there are some issues with Unity 5 and Windows version.
     
  12. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Very possible. Thanks for the continued feedback. I'm sure there will be some bumps along the road with the new version of Unity. Hopefully they'll smooth out soon.
     
  13. NoEnd

    NoEnd

    Joined:
    Mar 7, 2014
    Posts:
    14
    Dear echo,

    I'm planning to buy your plug in, but afraid that it will not work with IL2CPP, could you please confirm that it is working flawlessly?

    Regards
     
  14. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Yep, it works with IL2CPP so long as you are using Unity 4.6.3+. There was a bug in Unity in earlier versions that wouldn't allow external libraries which SimpleSQL uses. 4.6.3 fixed the issue.
     
  15. desyashasyi

    desyashasyi

    Joined:
    Nov 22, 2012
    Posts:
    73
    Hello echo15,
    I would like to know how to access database in all scene. I tried to write database in a scene, but i cannot retrieve data in another scene. Thanks
     
  16. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    The data is persistent on the device's drive, so you can access it from all scenes (and even outside of Unity). Be sure you create a SimpleSQL database manager in all scenes where you want to access the data and link the database template to all the managers. Also, please read these FAQs since they might be of use in your situation:

    http://www.echo17.com/forum/index.php?topic=257.msg518#msg518
    http://www.echo17.com/forum/index.php?topic=257.msg453#msg453
     
  17. NoEnd

    NoEnd

    Joined:
    Mar 7, 2014
    Posts:
    14
    @echo17 if I want to export for iOS and Android. Should I keep the optimization on Android as it the larger one or shall change it based on what I'm building for?
     
    Last edited: Mar 20, 2015
  18. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    The platform settings are not just optimizations, but sometimes necessary libraries. For instance, when you switch to the Android platform, it extracts the android sqlite.so package from the SimpleSQL editor dll into the project. I don't know if this package will cause errors on iOS or not. If you aren't getting errors, then you should be okay to leave it, otherwise you'll need to switch the platform based on what you are building.
     
  19. NoEnd

    NoEnd

    Joined:
    Mar 7, 2014
    Posts:
    14
    Thanks, I Really Really Like your plugin... It is Very "Simple"
     
  20. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thanks for the kind words, glad you are enjoying it!
     
  21. BFGames

    BFGames

    Joined:
    Oct 2, 2012
    Posts:
    1,543
    Hi, great product!

    Just wanted to drop a comment that might help others:
    I was using the sqliteadmin program suggested in the documentation, but it saved the file in some wrong version which gave me the error that the file was either encrypted or not a database. After i switched to the firefox plugin, everything worked fine.

    So if you get that error try another admin tool.

    Cheers.
     
  22. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Thanks for the tip! I've always used the Firefox plugin so I didn't realize other administrators gave that error. Appreciate the heads up...
     
  23. CplMulder

    CplMulder

    Joined:
    May 12, 2014
    Posts:
    51
    Hi Echo17,

    Great product and a very promising ORM so far...

    Also having the same issue with SQLite Administrator as above, are there any plans to look into this. I have used SQLite Administrator when using SQLite databases extensively in other .NET projects without any problems. Not sure why SImpleSQL sees the database as corrupted....

    I would really rather not have to install Firefox and the plugin specially just to edit my SQLite databases for use in SimpleSQL (seeing as I have learned and successfully used SQLite Administrator for so long)

    Thanks,

    Chris
    London
     
  24. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    It is definitely odd, but SimpleSQL is just a wrapper around the sqlite libraries, so the compatibility is probably something with the version of the sqlite dll. I'm not sure what SQLite Administrator is creating, but SimpleSQL attempts to be compatible with older versions of the library so there the administrator is probably creating a newer version.

    SimpleSQL extracts a pre-made empty database file from the editor dll when you select the create new database. The database file was made in Firefox sqlite admin.

    Since this is really outside the scope of SimpleSQL, you might try contacting the developers of SQLite Administrator to see why you can't open a (possibly) older version of the sqlite file. Be sure to send them an example database file for testing as that will probably help them out.
     
    Last edited: Mar 31, 2015
  25. NoEnd

    NoEnd

    Joined:
    Mar 7, 2014
    Posts:
    14
    Just to be sure, does your extension supports Arabic language?
     
  26. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I haven't tried specifically with the Arabic language, but I have tested with Chinese and Russian. You just have to be sure to use parameters in your sql strings instead of string concatenation to make it work properly. Please see this FAQ for more information:

    http://www.echo17.com/forum/index.p...15df0bd7777ce8911dc9c&topic=257.msg399#msg399
     
  27. NoEnd

    NoEnd

    Joined:
    Mar 7, 2014
    Posts:
    14
    Following up on this, I made a test on english letters and I still get empty string, please check my code and advice.

    First the table definition class:
    Code (CSharp):
    1. using System;
    2. using System.Collections.Generic;
    3. using System.Linq;
    4. using System.Text;
    5. using SimpleSQL;
    6.  
    7. class wordlist
    8. {
    9.     public string full_word { get; set; }
    10.  
    11.     public string letter_1 { get; set; }
    12.  
    13.     public string letter_2 { get; set; }
    14.  
    15.     public string letter_3 { get; set; }
    16.  
    17.     public string letter_4 { get; set; }
    18. }
    19.  
    second, List definition and query:

    Code (CSharp):
    1. public SimpleSQL.SimpleSQLManager dbManager;
    2.  
    3. List<wordlist> Words_List = new List<wordlist>();
    4.  
    5. Words_List = dbManager.Query<wordlist>("Select * From wordlist2 limit 5");
    6.         Debug.Log(Words_List.Count);
    7.         Debug.Log(Words_List[1].full_word);
    and by executing above code, I get this in the Console:

    1- 5
    2- Null

    clearly the list is receiving the values, thats why it has 5 items.

    but when printing one of the values I get Null.
     
  28. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I'm not sure what would be causing this without seeing your data. I typically avoid using "SELECT *" because it hides the columns from you as a programmer which can be tricky to debug. I usually explicitly list each column to be sure that I am matching the ORM I have set up. It may be that the collumn "full_word" does not exist, but I can't be sure without seeing a project example.

    If you would like me to take a look, please see this FAQ: http://www.echo17.com/forum/index.php?topic=257.msg950#msg950
     
  29. vortexilation

    vortexilation

    Joined:
    Sep 25, 2009
    Posts:
    287
    Is there any continuation on supporting encryption?
     
  30. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    No, there are no plans to incorporate encryption into SimpleSQL. However, the plugin comes with the complete source code, so feel free to modify it to meet your needs.
     
  31. multix

    multix

    Joined:
    Aug 5, 2013
    Posts:
    16
    Thinking about buying that tool...

    What happens to the working database (parameter "overwrite if exist" is unchecked) if I UPDATE my application via Appstore? Is that the same behaviour like unistalling/reinstalling and I loose all the data stored so far?

    If I provide some data in the project database as well, will that data be copied over to the working database as well in the very first run ((parameter "overwrite if exist" is still unchecked)?
     
  32. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    When you update an application via AppStore, only the executable is overwritten. The user's data is preserved in the sandbox environment of the device. As long as you keep the "overwrite if exists" unchecked, the user's entered data will always be preserved.

    Yes, that is correct. The first time the application is run, the project database will always be copied over to the working directory (sandbox on mobiles). Every time the application is run after that, the only time the database is copied over is if the "overwrite if exists" is set to true.

    Generally speaking, the only time you'd want to overwrite the database is if the data is static, like application settings. You would never want to overwrite the data if the user has modified at runtime. For dynamic data like this, you would need to create an upgrade process to keep the runtime database in sync with the project database structure. This can be done in a series of steps, modifying the database as necessary for each version until the database in the working directory matches what you have in the project, thus preserving the data itself. Note that you can have more than one database in a project. I usually keep two in my applications: one for static data (which is always overwritten) and one for dynamically entered user data (which is never overwritten).

    Have a look at this FAQ for more information: http://www.echo17.com/forum/index.php?topic=257.msg453#msg453
     
  33. jrhee

    jrhee

    Joined:
    Dec 5, 2013
    Posts:
    72
    Hi,

    What is the best practice for performing an UPDATE if a PK exists, INSERT if not (using text keys)?

    My scenario:

    I'm using two databases, StaticDB and SaveDB. I have a 'Character' table in both databases; entries in StaticDB.Character define all persistent characters, while entries in SaveDB.Character store updated versions of character data based on player progress.

    On game load I merge the data into a single list. If an entry with a matching CharacterID exists in SaveDB.Character, I add this to the merged list; otherwise I add the entry from Static.Character. I then use the merged list to spawn my character objects. This is working fine for loading.

    When saving, I need to UPDATE the existing record if it exists, or INSERT a new one. If possible I'd like to stick with SimpleSQLManager's UpdateTable and Insert methods rather than executing SQL strings so I don't have to update my queries every time my data model changes.

    Thanks!
     
  34. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I would call QueryFirstRecord, looking for the existing record. If the output parameter "recordExists" is true, then call your UpdateTable function, else call the Insert function. Have a look here for details concerning the "QueryFirstRecord" function: http://echo17.com/support/simplesql...anager.html#a13e3805999c4a53391ce3b2937486504

    If you decide to use queries, have a look at this link. I have not tried it myself, so I'm not sure if it will work, but it seems to be pretty much what you are looking for: http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update
     
  35. vee41

    vee41

    Joined:
    Nov 14, 2013
    Posts:
    32
    Hi!

    How would you approach a situation where I would like to allow users to share their databases between each other? ie. I'd give users an UI to modify content of the database to an extent, and then allow users to load content others have shared.

    Two methods I thought of:

    a) Simple way would be to replace the working copy of the database with DB that user selects from available databases. I do not know if this could cause some issues.

    b) A bit logner road: Instead of users exporting the whole database file and sharing that, they'd export content of the databse (in XML for example) and share that. Then I'd build interfaces to import the data to the local database when user imports shared XML file.

    Would you have any ideas on what would be a good practice to approach this? The DB structure does not change, only the content.
     
  36. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    The two methods you described would probably work well. You could also upload data to a server, but that would require a server database, web hosting, and scripting on the server side to process requests. You would also need to create scripts in your application to interface to this server since SimpleSQL is a local solution only.

    I have a very similar situation to this with one of my iOS apps called "iVelopes" (https://itunes.apple.com/us/app/ivelopes/id350035867?mt=8). It is a budgeting app that allows users to share the same financial data so that it is always in sync. For this, I use Dropbox, uploading the entire database when one user makes a change, and downloading the entire database when another device detects a change has occurred. This assumes you can share the entire database, but for this app that is what I needed. For this app, each user signs in with the same Dropbox credentials so that they access the same Dropbox account where the cloud instance of the database is stored.

    Keep in mind, your project can have multiple databases, not just one. So you could store private info in some databases, and publically overwritable databases could be uploaded / downloaded without worrying about stomping on other user's data. Just note that as the database size grows, your users will have to use more bandwidth to transfer the data (which could be prohibitive on mobile data plans), unless you have some kind of grooming or archiving process on the publically shared database.

    In case you are interested, I've also created a Dropbox wrapper for Unity called "Boxit", which you can find here: https://www.assetstore.unity3d.com/en/#!/content/4096
     
  37. vee41

    vee41

    Joined:
    Nov 14, 2013
    Posts:
    32
    Thanks for the quick reply! Glad to know both approaches work. I won't be needing any online functionality initially, so I'll go with users downloading and installing the content to proper location themselves. Then I plan to use Resources.Load() to grab the list of available database files and allow the user to choose the content that is copied as active DB. This sort of old school way to distribute user created content should suffice.
     
  38. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Be aware that Resources.Load only loads internal files from the compiled project build (embedded resources, in other words). It won't allow you to load resources from a file system location. For that you will need to use System.IO .NET file functions and structures like FileInfo, DirectoryInfo, etc.

    Also, SimpleSQL by default only reads and writes to the application's persistent data path. This is in order to be compatible with all platforms that Unity supports. If you need to access files outside of this path, you will need to tweak the source code in the Initialize function of the SimpleSQLManager class. This will only work on computer platforms like Mac and PC since mobile devices have very strict limitations on where you can access data. Please see this FAQ for information on modifying the source code: http://www.echo17.com/forum/index.php?topic=257.msg2183#msg2183
     
  39. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
  40. Duffer123

    Duffer123

    Joined:
    May 24, 2015
    Posts:
    1,038
    @ echo17,

    If I use Simple SQL but not the ORM stuff (I will use it, but for the purpose of this question) and only the open. stuff, is the resultant project still compatible with Android, iOS etc? What about the dlls.?

    [edit] Also, any chance of a 50% sale on SimpleSQL in the ordinary asset store (not just Level11)?
     
  41. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I'm not sure what is meant by "open. stuff", but yes, you can use SimpleSQL without the ORM. There are two other options besides ORM: System.Data or SimpleSQL's really light-weight table and row classes. I would highly recommend using ORM, however, because it makes coding much simpler. You don't have to cast every field to the appropriate type which saves on typing and errors. Also, you get the added bonus of intellisense. Once the classes are set up you will save a ton of time using them.

    The dlls are managed code, so they will work on all versions of Unity.

    Unfortunately, that is out of my control. We can submit our plugins to the store for sales, but it is up to the Asset Store staff to determine where, when, and if the product goes on sale.
     
  42. Duffer123

    Duffer123

    Joined:
    May 24, 2015
    Posts:
    1,038
    @ echo17,

    Many thanks. Well, I had to ask. will be buying this in a week's time when next paid. don't worry, having read the manual, i am sold on ORM... ;)
     
  43. Duffer123

    Duffer123

    Joined:
    May 24, 2015
    Posts:
    1,038
    @echo17
    I couldnt wait and so bought it now.

    I have another question - let's say I use largely ORM SimpleSQL and some nifty UI to create an application in Unity to manage, create and edit SQLITE databases (with option to save out as .byte) and/or I simply want to save out the runtime sqlite database and copy it over the Project equivalent - how would I go about doing that?
     
  44. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    That's a good question. I suppose you would use the System.IO classes to copy the file to the location that you want. SimpleSQL runtime databases are stored in the device's working directory (Application.persistentDataPath). I would imagine something as simple as File.Copy(...) should do the trick. http://www.echo17.com/forum/index.php?topic=257.msg518#msg518

    Just keep in mind that if you are planning on making and selling a plugin that does this within Unity you won't be able to include SimpleSQL since you can't distribute the plugin with your own plugins. If you are creating an application (compiled -- source hidden) or the plugin is just for your own use, then all is well.
     
    Last edited: Jun 2, 2015
  45. Duffer123

    Duffer123

    Joined:
    May 24, 2015
    Posts:
    1,038
    echo17,

    Panic not - the idea was basically for my own use or, in the highly unlikely event it was useful, a compiled version... ;)

    You can see how exporting out the altered database(s) would be v useful...
     
  46. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    Indeed :)
     
  47. Duffer123

    Duffer123

    Joined:
    May 24, 2015
    Posts:
    1,038
    @echo17

    - If you could post some further c# script on how to export the runtime sqlite database(s) out that would be incredibly helpful.

    - Also, with ORM, I've been wondering how I might, with a method, get the string for the path/reference of an object or gameObject to make it easier to save that reference as a string field in a SQLITE database. Is that possible? If so, could you post some C# script?

    - I was familiar with SQL statement syntax and structure before coming to Unity 5.x. In SQL statements, what's with the '\'s and the additional "s??? - see for instance pp 51-52 of your manual:- sql = "CREATE
    TABLE \"StarShip\" "+ "(\"StarShipID\" integer PRIMARY KEY NOT NULL ," etc.

    Also - sql = "ALTER TABLE \"LocationMapping\" ADD COLUMN \"NewField\" INTEGER";

    Why not just

    sql = "CREATE TABLE Starship(StarshipID INTEGER PRIMARY KEY NOT NULL, StarshipName TEXT)" ? ; and ???

    sql = "ALTER TABLE LocationMapping ADD COLUMN NewField Integer"; ???

    - When I've used SQL statements before it wasn't with the '\'..? Why are they included?

    Any guidance gratefully received... ;)
     
    Last edited: Jun 7, 2015
  48. Duffer123

    Duffer123

    Joined:
    May 24, 2015
    Posts:
    1,038
    Another question, following on or building on the others above - let's say I've created an RPG and I want to be able to allow for multiple save files (databases) that the runtime can save out to and then read from later. How best to do that if the system is only looking at the [temporary] sql database in runtime? What the happens if the player closes the 'game' and comes back in later and wants to load in the current position in their game? (hope you understand what I'm getting at?)
     
  49. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    This would just be a System.IO file copy operation. https://msdn.microsoft.com/en-us/library/system.io.file(v=vs.110).aspx

    Roughly:

    Code (CSharp):
    1. string fromPath = Application.persistentDataPath + "/" + workingDatabaseFileName;
    2. string toPath = Application.dataPath + "/" + projectDatabasePathAndFileName;
    3.  
    4. File.Copy(fromPath, toPath, true);
    5.  
    Note: I used the true parameter in File.Copy to overwrite the existing file. You will need to be sure the project file is closed before copying over it.

    Check out this FAQ: http://www.echo17.com/forum/index.php?topic=257.msg939#msg939

    Since the sql string is enclosed in quotation marks, the only way to include qoutation marks within the string is to use an escape sequence. So if you want something like ALTER TABLE "LocationMapping" ADD COLUMN "NewField" INTEGER inside a string, you have to escape the quotes like the example you showed. If sqlite doesn't need these fields surrounded by quotes, then you can leave them out.

    Have a look at this link for more info: http://stackoverflow.com/questions/14480724/escape-double-quotes-in-string
     
  50. echo17

    echo17

    Joined:
    Nov 24, 2011
    Posts:
    1,195
    I'm not sure what you mean by "temporary" database at runtime. The runtime database located at the application's persistentDataPath (the working directory) is the permanent database. Any changes made to this database will persist. Next time you run the game or access the database anything you've done to it previously will still be there. Have a look at this FAQ:

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