I just found a drawback to my idea of including .sql files in the project and then referencing them from within the code… those .sql files are plainly visible if you Show Package Contents on the resulting built application. If you are at all concerned about protecting your schema as part of your intellectual property, this might not be such a good idea.
That’s a good point. You might want to encodebase64 or use encryption on the text to make it harder for a hacker. The fact is, though, most applications, at some point, will need a schema and/or data upgrade.
I embeded the SQL code for the scheme of the tables in my code. I have not encoded/encrypted the SQL code as of yet. I probably should. But to this point I havent had to.
Unless your database is also encrypted, what’s the point?
my database is encrypted (98% of the time) to prevent people from hacking at the data outside the app.
sb
I agree with Tim. Unless you are encrypting your DB this just won’t matter as anyone will be able to connect to the DB itself and inspect its schema and data anyway.
If I write an app, such as John describes above in that it is capable of creating a data file, modifying it, and then saving it for later use, how could someone ‘connect to the DB itself and inspect its schema’? Isn’t SLQLite built into the Xojo framework? I don’t need to run an external database server for this. How would anyone connect?
Even an external db server, running on the same machine, would have a password set on it that would be hard-coded into the app.
If my SQL statements are also hard-coded into the app, there should be no way for anyone to inspect my schema. Or am I missing something?
An SQLite database can be read and inspected by any number of free/inexpensive utilities. So anyone that can get hold of the file can read it. It requires no password, unless it’s encrypted.
As far as a database server goes you’re right. No one can read/access unless they have the credentials to do so.
However, if someone got access to your executable they could find any string inside of it and with enough patience, using your SQL statements (that aren’t obfuscated) could reproduce the schema. Strings, by default, are hard coded into your application and anyone using a binary editor could suss it all out.
Frankly, for most apps you won’t care. But for some it might be a big deal.
On OS X you run Terminal and then run the sqlite3 command and there you go
You can open any unencrypted database
Or open it in a tool like SqlPro which has a version for sqlite databases
Or anyone of several other tools (valentina, cubesql’s manager app etc)
And when you have a database opened like this you can do “select * from sqlite_master” and there’s all the sql to recreate the schema
It’s just how SQLite works
SQLite databases are stored as files (with Xojo or anything else). What is “embedded” into Xojo is the database engine, not the data. Unless the SQLite database file is encrypted, anyone who can get at the file can open and inspect it using any number of SQLite DB tools.
For example, the macOS Mail app stores much of its data in SQLite databases, which you can find in Application Support and open to view the schemas and data.
It would seem that the topics of obfuscating your schema and encrypting your user’s data may each be large enough to deserve their own thread. Each one might start with the question of when such things would be necessary.
As for the original topic of how to track schema changes across multiple versions and upgrade your data files, it would seem that the answer lies in the scope of your operations. So far, I like Wayne’s method but I may keep my statements in .sql files in order to make the job of writing and maintaining them a little easier.
I’ve sort of been out of this loop for a while. My tool allows me to input DDL (Data Definition Language) & SQL into a text area. The input is then applied to my test database & if it works is saved as a step in the current schema upgrade. It is up to me to ensure no data loss. Using the SQL part I can also insert default data. When I’m finished I use the tool to write the schema control module and generate the new ORM classes.
Writing code to text files is not hard, it simply removes the repetition of doing it yourself. Once you’ve written the code once it’s more like a mail merge.
For one job I did push the updates out to .sql files - I needed to add a couple of tables to the db & the DBA would not allow the application that level of access & insisted on inspecting & applying the updates herself.
Good luck with your projects.
We don’t have a problem exposing our schema, although the user can encrypt their database if they wish to. In our experience, having the schema is a long way from writing an application that can use it. Yes, they could access it and “mess it up” so to speak, but they do that at their own risk. The only users that we have found that are interested in doing that are those that may be thinking of moving to another product and want to transport their existing data.
In our case, our software is user driven and if they need to modify their data for legitimate reasons, we are not opposed to adding options that will allow them to do that as long as we can see a benefit to other users.
We are currently using Sqlite databases because they are small, fast, and flexible. They also accommodate encryption and are very mobile, which is a requirement of our design criteria.