Create if not exists via SimpleSQL class?

[gromiczek]

Hi - Pardon if I've overlooked this somewhere - is there a way to use the "Create if not exists" syntax and generate that database from the SimpleSQL database class? Thanks!

[echo17]

Sorry, I'm not following what you are asking.

If you want to generate a table, you can either using CREATE TABLE syntax, or use the helper function CreateTable<MyClass>. Check out Demo #5 included with the plugin.

If you are wanting to force copying the entire database to your working directory, You can call Initialize(true) on your database manager.

If you are wanting to load a database from your working path at runtime, see this FAQ: link

If you are wanting to load a database from your project at runtime, see this FAQ: link

[gromiczek]

I'm on iOS and still having trouble getting the database to work. I'm creating the database and loading the data at runtime and want to do a check that it doesn't already exist when I start the app, as this database is dynamic and will change during runtime and shouldn't be overwritten. I through about using the SQLite command "CREATE TABLE IF NOT EXISTS" in conjunction with creating the table from the class structure in SimpleSQL, but couldn't figure out how.

This is my first use of a database on iOS and I've been working at this for a few weeks and still can't get it up and running. All is fine and runs as expected in the editor, but not so far on iOS. I've rewritten and reworked and debugged it many times over the past few weeks and still can't get it up and running. I'll try the three things you suggest and see how it goes.

[echo17]

I think what is confusing me is that you are using the terms database and table interchangeably. Creating a database and creating a table are completely different processes.

Here's a FAQ that might help you out: link

[gromiczek]

Edit: I wrote this before your previous reply loaded - and I see what you mean about interchanging the terms database and table. Yes, I have been doing that and will try to be clearer - and have edited the post below to the best of my understanding.

--

Hi - Thanks for your help. I've tried each of the things you've suggested but still can't get things working. Should these approaches also work in the Unity editor as well? Because I tried the Initialize(), the loading from Resources and can't get either of these working in the editor or on my iOS device.

Things I've tried that work in the editor but not on iOS:
- creating and Pre-filling a table in a bytes file and using it as the file in the DBManager
- Creating and filling a table at runtime in a bytes file already attached to my DBManager

...however as I said, neither of these works once I build to the iOS device. I am now understanding that this is likely because the table is in a file in my working directory in the editor, but once I build to iOS I need to some how get a filled (either pre-filled or filled at runtime) table into into a file in my working directory on iOS and this seems to be where I've been stuck for weeks now. Is there something special I need to do if I create and fill a table at runtime on iOS or call Initialize at runtime on iOS to get the filled table into a file the working directory?

The crux of my problem is that I can generally get my program working in the editor, but when I build to iOS my table no longer exists, and when I try to create and fill a new table Xcode says it already exists...but somehow I'm unable to access it.

[echo17]

So one thing I usually recommend, and I do in my own projects, is to have two databases. One database file is for static data that does not change at runtime (like your pre-populated database). The other database file is for dynamic data that will be updated at runtime.

You can set up two database managers in your scene and point to the two databases. The static database you can set to "overwrite if exists". The dynamic database you can leave this setting off. That way, any changes you make to the static database in the editor will automatically get copied to your device's working directory on startup of the application. For changes to the dynamic database, you will have to provide an upgrade path to avoid stomping on the user data. The FAQ here shows how that can be accomplished.

If you absolutely must overwrite the dynamic database, you can try to call Close and Dispose on the database manager object, then delete the file using standard .NET file operations. You can then reload the database (making it copy since it no longer exists in the working directory) by calling Initialize(true) on the database manager object.

[gromiczek]

Okay, just to make sure I understand (and sorry if this is basic!), if I want to have a database with a table that I pre-populate with data, to which I will add additional records, and in which I will update each of the records over time is that dynamic or static? (dynamic?) I don't intend to add new columns or change the table or database's structure, but I will add new records and update the existing ones in the pre-populated table. Would I need to handle the addition of these new records and record updates through upgrades as in the link you attached? Thanks for your help!

[echo17]

By static data, I mean data that is not updated at runtime (while the application is running on the device). You can make changes to it in the editor all you want and it will be updated when the application starts if you have "overwrite if exists" set to true.

If the data is going to be updated at runtime, while the application is running on the device (not the editor), then this is what is considered dynamic data. So that you don't stomp on changes made by the user at runtime, you will need to have an upgrade path in your code for this database type.

[gromiczek]

Each time a user runs the app many records will be added to my table (10s to 100s). Do I need to need to recreate the table every time data is added? Or can I just recreate the table at the end of the user's session? It just seems inefficient to constantly recreate the table. Thanks again.

[echo17]

You should only create the table once. If you are talking about an upgrade path, then you will need to keep a record of what version that the database is stored somewhere, whether that is in your database itself or in a text file or something outside of the database. This can be something as simple as a number or text value. That way you know what changes to make depending on what version of the database the user is on. Please read through this FAQ: link

It might be beneficial to you to get a solid understanding of database design, and sqlite in general. Here are some links to help you get started:

Introduction to databases
SQLite tutorials

[gromiczek]

I've finally figured out the problem (sort of) - it turns out that database and table are working on my iOS device but for some reason the Primary Key has not been Auto Incrementing, so when I check the key of the last inserted record the answer is always 0 -- and I had been mistakenly using that as the row count. How can I fix the primary key so it auto increments when I insert a new record? Currently I have it scripted in the class as:

[PrimaryKey, AutoIncrement] public int MotifID {get; set;}

I don't set it to a value when creating a new record (because I want it to be assigned by the AutoIncrement function in the table), however when I query the table all the MotifIDs come back as 0, which shouldn't be possible. Any thoughts on what might be going wrong here? Thanks!

[echo17]

I'm not able to reproduce, so I'll need to see an example project demonstrating the issue. Please see this FAQ for more information: link

[gromiczek]

In the process of building you a small test case I did sort out the problem. It turns out that in the context of the larger project it appears as though the `set` method for the the primary key in my class was being stripped off the class when it was built to iOS. I fixed this by creating a method that creates a new record from the class, sets its primary key to a number, but does not insert the record into the table, and instead simply logs it to the console. Then I called this method within the program and everything else finally worked as expected. Turns out, everything had been working fine all along, I was just unable to set the real, incremented primary key from the table when I queried the records because (I think) the `set` method was being stripped (as "unused" code) from the primary key in my class.

I did try adjusting the Stripping Level in the Player Settings (setting it from high to medium), but this didn't seem to help. Again, it's only a problem on mobile, so everything has always worked fine in the editor - it's just on iOS where this problem occurs. I'm not sure what exactly is behind this behavior, but at least I've found a work around.

Unity: 2020.3.34
iOS: 15.6.1

[echo17]

Thanks for the update. I usually turn off all stripping when deploying to iOS as it has caused me headaches in the past. I'll take the bigger build size to get a functioning project.

[gromiczek]

Good to know - I'll bear that in mind going forward. Thanks!