I’m still deep in the process of coming up to speed in XoJo, loving the tool though.
My question for Databases is, which of the following would be the best approach to design a database for XoJo?
Use a tool such as SQLLiteStudio to create, add tables, etc. and create the database file to then connect to and use with XoJo.
or
Do everything through XoJo code
I’ve used SQL databases in the past, but pretty rusty at this point and keeping in line with XoJo’s ability to do a lot of drag and drop type design, I really would like to keep the process as simple as possible so my coding focus is on accessing and manipulating the data, not doing everything from scratch.
My first DB project with XoJo, is basically going to be a simple customer contact information / address book.
Would it be feasible to design and create a SQLite DB using the SQLiteStudio, pull it in to my XoJo project and have it be part of the Resources when I build the app to install and use on any of the Target OSs?
Use a tool for creation (and testing), then create code that does that.
The tool is there to make sure the queries do what you want.
We set them up like “create table if not exists …” after for example in the sqlite .createDatabase method.
Then a subclass adds all methods to add/remove/update etc data.
There are millions of ways to do this, basicly one tip there is “don’t use xojo’s inbuild db browser” instead use code for it.
I’d use the SLite CLI program for all testing of prospective database schemas etc. We have found that other tools used for this purpose sometimes introduce odd characters into tables/strings/SQL when this is copied into Xojo, leading to odd bugs.
I searched for SLite CLI and I get Command Line Shell for SQLite, is that the tool you are recommending?
I’m trying to stay GUI type tools outside of XoJo for this. If I end up needing to use command line stuff outside of XoJo, I may as well just code it in XoJo in the first place.
Well that’s in fact what I do. In my case my app, on first run, creates a number of databases that the user will need as they use the app. So that’s all coded in anyway, along with code to allow the app to detect that it’s a newer version and should update one or more of those databases.
I made my own database tool using xojo, graphical design.
then I have commands to generate sql code to query, or create the database
even generate clipboard datas to paste into xojo ide that generate my controlitems directly.
dim sqlcode as string
sqlcode.AppendText "CREATE TABLE Album ("
sqlcode.AppendText " AlbumId INTEGER NOT NULL ,"
sqlcode.AppendText " Title TEXT NOT NULL ,"
sqlcode.AppendText " ArtistId INTEGER NOT NULL "
sqlcode.AppendText ")"
sqlcode.AppendText "CREATE UNIQUE INDEX [IPK_Album] ON [Album]([AlbumId])"
sqlcode.AppendText "CREATE INDEX [IFK_AlbumArtistId] ON [Album] ([ArtistId])"
SQLExecute( sqlcode)
its better to create and manage a Database with a tool.
for other DBs there are DBeaver, HeidiSQL, mysql Workbench, pgAdmin, MS Management Studio.
IBExpert is handy for Firebird. i not used Firebird with Xojo but there is a odbc driver.
It does seem like many are using external tools for this. For my purposes, I think I’ll be fine with just SQLite and since it seems to be recommended for XoJo databases, it makes the most sense to me. I don’t see myself creating apps to connect to other database flavors. It will be much easier for me to understand and support my apps if I standardize on one solution.
Another question here is, if I do create, test and validate a database with an external tool and all looks good, could I just populate the database with a few sample records to include in the resources for my builds and then just let the user delete those records from the DB once it’s up and running?
My programs often use SQLite (for local data that does not need to be shared) and a RDBMS for transactional data shared among multiple users. I moved a while back from MS SQL Server to PostgreSQL (and a Linux host).
Clearly, you are not limited to just one database. It is the design of the application and the functional needs behind it that will dictate how you proceed and which tools you use.
With regards to tools, you can populate data in your SQLite database with SQLite Studio. I use it sometimes, but I have pretty much settled on Navicat for everything except MSSQL (I have a whole suite of tools for MSSQL that I still use when the need arises). In addition to the tools mentioned earlier in the thread, you may also want to look at Valentina. It is quite decent also.
Now, You can populate the database with a few records IF the records have a value to your users. As example data, for example. Otherwise, I prefer to ship a clean, ready to use product. Leaving reconds in the database that are of no value to the end user just seems sloppy and would project the wrong image.
I suppose I could just include the empty database after some testing to confirm it’s working properly. But the question here is, can I just include the database file in the resources folder when I build the app? My project is not complex and I’m hoping to easily target Mac, Windows and Linux. I’m doing my development on my Mac.
Yes. I would probably recommend that you place (and build your copy steps accordingly) the database file in a safe and authorized folder. Look at SpecialFolder - Xojo Documentation.for inspiration.
Do NOT drag the database into your project in the IDE. Copy it into Resources in a Build Step. On first run, copy the database to the desired location.
The macOS sandbox will chuck a hissy-fit if you try to open an SQLite Database that is ‘inside’ your application, even if read-only. Adding it via a Build Step is a ‘do once then forget’ operation.
You need to copy out the database from ‘inside’ your app (the sandbox doesn’t mind this) to a location it is allowed to open eg Special.ApplicationData.
If you drag it into your app, that locks down the location. Sure it provides the convenience of opening the database for you, but at the expense of zero flexibility. And if you’re distributing the app to others, it’s pretty much guaranteed that either the database will be in a read-only location (inside your app) or in the wrong location.
It’s best to handle everything in code.
There used to be a benefit to dragging the db into your app, but that ship sailed at least 10 years ago. It’s now just a legacy thing that should really be removed.
Another thing to think about when you design your database, especially a local db like sqlite, is to future proof it by adding a version number somewhere. Add a table with a single column and a single row that just holds the version number. Then in your code where you first open the database, check the version number and update the schema if necessary.
… nowhere !
it’s an internal tool I made for me, that’s still alpha stage, that I modify each time I need it
that is not complete for some functions I need too rarely, so it’s not something I can sell.
it is also so much tight with other control classes that are also not for sale
that it is useless to somebody that don’t have all the framework…
I can detail the functions it has to guide you if you want to make your own , but that’s all…
anyway thanks for your interest in my projects !
