Thinking about a project that, if it were using a database, the data might consist of 100 rows and 6 columns in 1 table.
It strikes me that this is very little data. Would it make more sense to simply store stuff in a text file (json?, tab-delimited?) and read everything in when the program starts, and filter out what’s not needed at the moment? Thinking that a database might be more than is needed.
Thoughts?
I have used SQLite DB for user configuration settings, layout settings and other such data, sounds like maybe that is what you are doing?
Yes SQLite is perfect for that
But yes you could use other means of storage/retrieval
Use a database it will make life easier in the long run. It offers better scalability if your app evolves, easier data structure (let the DB worry about that) and data integrity and security (if you want).
As Brian mentioned, SQLite is perfect this and is easily implemented.
I like storing data in a text constant. In a situation like you describe, you can parse the data in to 6 arrays with 100 elements or a dictionary with 100 entries and depending on the needs of the program, access and search the data easily and not bother with SQLite.
Now you do not need a second file for your application. All the information is internal.
The structure of the data matters. Back in the day when I used a simple preference system the data structure sucked and my code was clunky like heck. After moving to an SQLite database with now 9 tables my code became much better.
it depends on the way you will have to search through the table.
if it is only with one parameter, then a dictionnary and his key is enough
if you have to search with many criterias, then a database and sql queries will come handy.
and if in the future, more table may come, then definitely a database.
i am a fan of object serialization into json or xml. (for a manageable size of data)
that means a class with some properties is easy to handle.
and it can be a hierarchy, objects in a list of other object.
i try also reduce the overhead with any third party dependencies.
my aim is to have a maintenance-free software in long-term.
xojo lack of a build-in serialization inheritance but it is easy to made.
the advantage of json/xml is you can push it into or get from a web service.
I have never met a project yet that didn’t eventually evolve into situations where DB capabilities was terribly useful if not mandatory. The ability to sort and filter on any criteria with ease, etc.
If you’re not familiar with databases then a simple project like this is a good learning experience getting used to the patterns for opening and reading / writing data using SQL. It’s pretty trivial once you internalize it.
Also TBH I’ve only used Sql Server, Postgres and in the Long Ago, MySql professionally. I get the appeal of SQLite – it’s embeddable and compact – but it also really wants to be single-user and its syntax is rather quirky compared to what I’m accustomed to so I would be loathe to have technical debt around it. Postgres is free and easy to set up particularly if you use something like Postgres.app to get it bootstrapped, and it will always be able to give you anything you ask of it in the future. So that’s my personal preference unless maybe I were trying to develop a desktop or mobile app that had to be 100% self-contained or at least had an offline mode that required that. I have been entirely immersed in server-based systems since the mid 1990s so I don’t consider myself an authority in the realm of local apps except those that are used to maintain or report on connected DBs.
I use SQlite for storing stuff like windows positions (to restore them to the same position when re-opened), user preferences, other system variables, etc. The advantage of storing some system data is that it can be changed using a program such as SQLiteExpert without having to re-compile the program.
It all depends on what you will be doing with your data and what may happen in the future. If you need to do anything more than the simplest search or if the volume of data may increase significantly, then a db is the best way to go.
One point to watch with SQLite: it has non-standard features. Stick to “standard” SQL in case you need to migrate to a heftier system such as MySql (for simultaneous multi-user access, for example), otherwise building a program around non-standard features could cause grief at migration time.
Care to give a link to that ?
(to what is not standard SQL in SQLite)
See https://www.iso.org/standard/53681.html. Also, “Using SQL” by James R Groff and Paul N Weinberg, Osborne McGraw-Hill, 1990, ISBN 0-07-881524-X.
every db system is different, it is a matter of luck to migrate it.
something like user settings would also ok for .ini files but i remember xojo did not provide a INI Class.
Perhaps because only Windows has .ini files.
Here’s a brief comparison between SQLite, MySQL and PostgreSQL: https://logz.io/blog/relational-database-comparison/
Not sure I see the point of comparing SQLite with the others; their use cases are completely different.
I suppose the point of the comparison is to see which one suits one’s use.