Recent

Author Topic: Database for small GTD Todo application  (Read 14466 times)

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Database for small GTD Todo application
« on: February 19, 2017, 06:28:07 pm »
I am making a Get Things Done, Ticket, Pomodoro kind of application.

I want it to be completely stand-alone, so I don't want to use SQLite. Which also is probably overkill?

I have begun using Tdbf (DBFLaz) - version 7 - but now I am beginning to have doubts:

Would ZMSQL be a better option?
I am put off by the fact that it uses CSV, but if it has some clear advantages, then I will reconsider.

I like that Tdbf comes with Lazarus, and that it creates "proper" binary database files, like SQLite, ..

Am I right to have chosen that option?
According to the wiki, it is half-dead and unsupported, but perhaps the wiki is out of date?
« Last Edit: February 19, 2017, 10:26:17 pm by jacmoe »
more signal - less noise

Phil

  • Hero Member
  • *****
  • Posts: 2737
Re: Database for small GTD Todo application
« Reply #1 on: February 19, 2017, 06:36:14 pm »
I want it to be completely stand-alone, so I don't want to use SQLite. Which also is probably overkill?

An advantage to SQLlte is that it's so ubiquitous, already installed on many systems (iOS, Android, macOS). Only Windows won't have it already installed, although the .dll is small for inclusion with your app and I believe you can link in the SQLite binary into your executable so even the .dll won't need to be distributed.

Another advantage obviously is that the SQLite files created by your app can also be read by other apps, not just your app, so the data is less likely to be end up stranded.

The data from that sort of app would probably be modest in size, so even JSON files would probably do.

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: Database for small GTD Todo application
« Reply #2 on: February 19, 2017, 06:41:59 pm »
That's a very good argument. ;D
I will reconsider using SQLite.

JSON, yes. I have plans to support that in the future when my application is ready to talk to other devices.

The wiki is not very helpful, but fortunately this forum has quite a few posts about JSON. :)
« Last Edit: February 19, 2017, 06:47:33 pm by jacmoe »
more signal - less noise

Phil

  • Hero Member
  • *****
  • Posts: 2737
Re: Database for small GTD Todo application
« Reply #3 on: February 19, 2017, 06:48:07 pm »
JSON, yes. I have plans to support that in the future when my application is ready to talk to other devices.
Do you know of any Lazarus/FPC JSON packages that will come in handy?

FPC includes fpjson and jsonparser. There are also 3rd party JSON parsers for Pascal out there too, I think. The advantage to using a 3rd party one would be that you could then compile your app with either FPC or Delphi. If you use the FPC JSON units, it won't be compilable by Delphi; if you use the Delphi JSON units, it won't be compilable by FPC. (Same with XML, etc.)

SQLite can be used anywhere. Here's the server side code for a simple Web app that reads from a SQLite db and returns JSON:

https://macpgmr.github.io/MacXPlatform/WebAppOverview.html#Server


jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: Database for small GTD Todo application
« Reply #4 on: February 19, 2017, 06:52:29 pm »
Excellent! :D

Normally I am a web developer so good to hear that I can create similar applications using Lazarus/FPC.

I will look into fpjson and jsonparser.  ;D

Thanks a lot for the link. Pascal and Lazarus and web work is definitely new territory.
« Last Edit: February 19, 2017, 06:55:04 pm by jacmoe »
more signal - less noise

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: Database for small GTD Todo application
« Reply #5 on: February 19, 2017, 07:04:34 pm »
Yes, I will use SQLite - and worry about distributing my app later.
Great advise.
It means that I can use a wide array of tools/frameworks with the database.
And good to hear that JSON and Pascal gets along nicely as well.
more signal - less noise

DonAlfredo

  • Hero Member
  • *****
  • Posts: 1738
Re: [Solved] Database for small GTD Todo application
« Reply #6 on: February 19, 2017, 07:11:45 pm »
You could consider using the mORMot.
Static SQLite3 has just been added for many platforms.
Meaning you distribute only a single executable, without the need of sqlite libs.

See: https://github.com/synopse/mORMot

Besides, with the new RTTI from trunk/NewPascal, all mORMot advanced features are now available for FPC !

Phil

  • Hero Member
  • *****
  • Posts: 2737
Re: Database for small GTD Todo application
« Reply #7 on: February 19, 2017, 07:14:23 pm »
Yes, I will use SQLite - and worry about distributing my app later.

I assume you're writing this type of app as a way to explore and learn Pascal and the Lazarus LCL and IDE. That's a good way to start. What you might find fun is trying to take a simple app like that and then push it to as many platforms as you can. Once you're beyond Windows and Linux, that's when the fun starts (macOS, iOS, Android, Web) as you have to begin contemplating the use of other tools and more modern ways of doing things. But it can be a great learning experience.

Pascal is quite general and universal now. The perception that it's not starts when people take it and try to do things on newer platforms in the same way that they did things on the "old" platforms (Windows, Linux).

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: [Solved] Database for small GTD Todo application
« Reply #8 on: February 19, 2017, 07:16:52 pm »
Thanks for pointing me to mORMot - I didn't know what it was!
It sounds like the name of a shady character in a Potter novel. :)

I will probably contemplate using it later on - if not with this application, then definitely with something else.
I am used to ORM and MVC from my webwork (Yii Framework / PHP).
more signal - less noise

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: Database for small GTD Todo application
« Reply #9 on: February 19, 2017, 07:21:11 pm »
I assume you're writing this type of app as a way to explore and learn Pascal and the Lazarus LCL and IDE. That's a good way to start.
Yes, you are correct.
This is meant to be a 'Hello World' like application, but something that is going to be useful ((I still haven't found the perfect GTD app))

What you might find fun is trying to take a simple app like that and then push it to as many platforms as you can. Once you're beyond Windows and Linux, that's when the fun starts (macOS, iOS, Android, Web) as you have to begin contemplating the use of other tools and more modern ways of doing things. But it can be a great learning experience.
I will - one of the great features of Lazarus/FPC is that there are tons of supported platforms.
Android is definitely on my list of things to try in the nearest future!

Pascal is quite general and universal now. The perception that it's not starts when people take it and try to do things on newer platforms in the same way that they did things on the "old" platforms (Windows, Linux).
Coming back to Object Pascal after 20 years, yes: it is surprisingly modern and capable. Very exciting!  :D
more signal - less noise

DonAlfredo

  • Hero Member
  • *****
  • Posts: 1738
Re: [Solved] Database for small GTD Todo application
« Reply #10 on: February 19, 2017, 07:27:15 pm »
About the mORMot.
If you want to start using it, my advice would be to start with the samples !
From simple to advanced, they give you a good idea about its possibilities.

https://github.com/synopse/mORMot/tree/master/SQLite3/Samples

Nearly all my software now runs with some part of the mORMot inside !

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: [Solved] Database for small GTD Todo application
« Reply #11 on: February 19, 2017, 07:33:35 pm »
mORMot does look extremely tempting .. :)

I should probably create something that requires it.
It is added to the list of things to try - thanks!

I have to be careful, because the last time I used Object Pascal - 20 years ago - my Hello-World app ended up being a complete PHP editor with autocomplete/syntax highlighting, IE/Firefox preview, etc.
That's inevitable when working with Delphi or Lazarus, I guess.  8)
« Last Edit: February 19, 2017, 07:48:47 pm by jacmoe »
more signal - less noise

Phil

  • Hero Member
  • *****
  • Posts: 2737
Re: Database for small GTD Todo application
« Reply #12 on: February 19, 2017, 10:05:19 pm »
Android is definitely on my list of things to try in the nearest future!

Yes, mobile will probably be the first place where it gets interesting.

Let's review: Don't overlook the older text file formats for your data file. Looking at some of my projects over the years, it appears as though I tend to go with the simplest, most efficient file format that meets the requirements. And the simpler text file formats are often the ones I go with, even if I'm storing data that might normally be stored in a binary format (eg, images as well-known-text).

20 years ago, lots of programs used .ini files. These are still useful, well supported by FPC RTL, although maybe not the best choice if you have non-ASCII data or record-oriented data.

Then came XML. Delphi and Lazarus both use XML for their project formats, probably because that's what was popular at the time. Mac OS X uses key-value "property list" files (.plist) in many places, but the actual file format is XML (although since you almost never work directly with the XML, it could very well be in any file format).

Then came JSON. Newer editors like Microsoft's cross-platform Visual Studio Code store just about everything as .json files. And of course JSON now threatens XML on the Web as the transport format of choice. Part of the success of JSON might just be that it fits so naturally into the conceptual containers that programmers already work with: objects, arrays, key-value pairs, etc.

For efficiency, if you have record-oriented data that can fit into a single table (eg, in Excel), then nothing beats CSV or, even better, tab-separated since you then don't need to put quotes around so many things. Compare the speed of sending the same data across the Web in various file formats and then parsing it: tab, CSV, JSON, XML - that's the order of fast to slow, as most XML parsers are pretty slow, as well as the order of compact to verbose.

An issue that you'll run into is that text formats are read-all, write-all, so with a large XML file, for example, most parsers read in the entire file before you can work with the parsed contents, even if you're only looking for a specific node. Same with writing: change one thing and the entire file is rewritten.

Why is this important? Aren't most computers very fast reading and writing text files? Yes, they are, surprisingly so. However, for an app like what you're describing, you'll probably want to be able to sync changes between the user's computers. For example, say you have a Windows app and a companion Android app. If you add a to-do item on either computer, users nowadays expect that change to show up automatically and almost immediately on all other computers that they have that app on. In other words, you need to create a cloud-aware app.

This is where it gets tricky, since there are various platform-specific ways of doing this, as well as various approaches that you can take. The goal, though, is to meet the user's modern expectations.

As an example, let's take Apple's built-in support for these things:

https://developer.apple.com/library/content/documentation/General/Conceptual/iCloudDesignGuide/Chapters/iCloudFundametals.html#//apple_ref/doc/uid/TP40012094-CH6-SW28

- Key-value storage. Again, these are just .plist files, for app preferences and the like, limited to 1MB per app, probably to discourage developers from using them in ways that they weren't intended.

- Documents. In Apple terminology, just any INI, XML, JSON or SQLite (and many more) could be considered a "document".

- CloudKit. Data stored as individual records.

Earlier on this link's page, we see this example of the type of data to store in the cloud: "Change log files for a SQLite database (a SQLite database’s store file must never be stored in iCloud)".

Now you see what they're doing there. In syncing, the app only sends the change log, not the entire database. This is consistent with other cloud services, like Dropbox, where they've always discouraged working directly with databases in the sync folder, since any change, no matter how small, can trigger a sync of the entire file.

This is also consistent with the way Apple does it themselves in some of their apps. For example, in their Pages word processor and Numbers spreadsheet, the native format is actually a .zip file. Nothing new there. MS Office does the same thing with its .docx, .xlsx, etc. file formats. However, with Apple's apps, table data is stored in the .zip as separate files, one for each record / row. Again, why would they do that? Most likely, this is so if you modify or add a row to a table, only that row gets synced, not the entire .zip file.

So, can you work up a scheme around SQLite log files? Or something on top of the various platform services for this?

I know nothing of the mormot, but if it can be used for syncing partial content of files between computers, then that might be a good, high-level, platform independent option. If it can't, then eliminate it from consideration. (Looking at the github samples, offhand I don't see anything that resembles what I'm trying to describe.)

Like I said, lots of fun, interesting challenges ahead. I can't really recall much discussion here around this issue of syncing data between computers, so you're probably on the Lazarus frontier.

jacmoe

  • Full Member
  • ***
  • Posts: 249
    • Jacmoe's Cyber SoapBox
Re: [Solved] Database for small GTD Todo application
« Reply #13 on: February 19, 2017, 10:19:06 pm »
Yes, I am a fan of simple systems. Which is why I wrote my blog to use a combination of statically served Markdown synced with a Git repository, PHP and heavy caching.
Because nothing really beats plain text: it can be easily backed up, copied, moved around, edited via SSH/Tramp(Emacs) ..

I am toying with the idea of having each item be a self-contained JSON object (in a file), or a simple XML file, and then load them into memory when the application starts.

I will probably make my application support a multitude of options for what back-end to use.
And then, let's see what works better overall.  :)

Thanks a lot for the info - especially about the Mac/iOS - those are alien to me as I am a Windows guy turned Linux geek.
more signal - less noise

Phil

  • Hero Member
  • *****
  • Posts: 2737
Re: [Solved] Database for small GTD Todo application
« Reply #14 on: February 19, 2017, 10:34:54 pm »
I am toying with the idea of having each item be a self-contained JSON object (in a file), or a simple XML file, and then load them into memory when the application starts.

I suppose that's one way you could create sort of "poor man's" cloud-aware app, working in a synced folder and let some other service (eg, Dropbox) do the actual syncing of modified files between the user's computers and devices.

I wouldn't assume that these files would always be small. For example, an obvious enhancement would be to allow a photo to be attached to any to-do item. Now the data files are not so small.

 

TinyPortal © 2005-2018