Database for small GTD Todo application lazarus / 16-Databases ---------------------------------------------------------------------- jacmoe · 2017-02-19 17:28 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? ---------------------------------------------------------------------- Phil · 2017-02-19 17:36 Quote from: jacmoe on February 19, 2017, 06:28:07 pmI 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 · 2017-02-19 17:41 That's a very good argument. 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. ---------------------------------------------------------------------- Phil · 2017-02-19 17:48 Quote from: jacmoe on February 19, 2017, 06:41:59 pmJSON, 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 · 2017-02-19 17:52 Excellent! 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.  Thanks a lot for the link. Pascal and Lazarus and web work is definitely new territory. ---------------------------------------------------------------------- jacmoe · 2017-02-19 18:04 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. ---------------------------------------------------------------------- DonAlfredo · 2017-02-19 18:11 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 · 2017-02-19 18:14 Quote from: jacmoe on February 19, 2017, 07:04:34 pmYes, 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 · 2017-02-19 18:16 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). ---------------------------------------------------------------------- jacmoe · 2017-02-19 18:21 Quote from: Phil on February 19, 2017, 07:14:23 pmI 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)) Quote from: Phil on February 19, 2017, 07:14:23 pmWhat 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! Quote from: Phil on February 19, 2017, 07:14:23 pmPascal 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! ---------------------------------------------------------------------- DonAlfredo · 2017-02-19 18:27 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 · 2017-02-19 18:33 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. ---------------------------------------------------------------------- Phil · 2017-02-19 21:05 Quote from: jacmoe on February 19, 2017, 07:21:11 pmAndroid 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/Conc eptual/iCloudDesignGuide/Chapters/iCloudFundametals.html#//apple_ref/d oc/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 · 2017-02-19 21:19 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. ---------------------------------------------------------------------- Phil · 2017-02-19 21:34 Quote from: jacmoe on February 19, 2017, 10:19:06 pmI 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. ---------------------------------------------------------------------- jacmoe · 2017-02-19 22:59 Let's see how it progresses.. I know about Firebase and Android, that's one option. The other options.. ? Well, I am sure I will figure it out once it's relevant. I will bookmark this topic and return to it after a while, because now would be a good time to don the thinking hat, take out the sketch pad while at the same time get the basics of the application up and running. Otherwise I will be stuck in thinking mode for a long, long time. ---------------------------------------------------------------------- Phil · 2017-02-19 23:16 Quote from: jacmoe on February 19, 2017, 11:59:44 pmget the basics of the application up and running. Just make sure you have a path forward for how you want to do syncing. About 5 years ago, I had an idea for an app that I was calling a sort of "data journal". Probably not too different from the kind of functionality that your app will have. It seemed like a lot of people kept journals, formal or informal, of various numeric information, not so much words. For example, people record rain gauge data, how many miles they bicycle, golf scores, etc. So my app had two requirements: (1) That all categories could be user-defined, with a small set of pre-defined starter categories provided as examples, and (2) all categories could have various stats calculated for them (mean, median, frequency, monthly averages, whatever, all user specified). So I created a nice little iOS prototype that used SQLite. But where I stopped was when even 5 years ago it occurred to me that of course this app needed to be able to sync its data, so if I created a desktop version one day, the data would automatically flow in from the mobile version. And I could see that this was a non-trivial exercise. So the app fell by the wayside. Many mobile apps have had this capability for years. And not just calendars and e-mail clients. For example, with Apple's Pages word processor, it doesn't matter where I create or edit the document: iOS, OS X or Web browser (icloud.com). Everything gets synced automatically. I think from day one I would set that as a requirement for any app of this kind, including an app like what you're thinking of. ---------------------------------------------------------------------- jacmoe · 2017-02-19 23:27 Noted.  Good advise! Edit: I do have a preliminary plan to let the application talk JSON with a API sitting on my server - I will write it in PHP (Yii Framework) - so no worries. ---------------------------------------------------------------------- Phil · 2017-02-19 23:37 Quote from: jacmoe on February 20, 2017, 12:27:53 amNoted.  Good advise! The key is you don't want to outgrow your app before it's even finished. That's what happened to me. I could see that without cloud awareness, that even I probably wouldn't use the app. I would also encourage you to do a Web version. That will ensure that you've properly separated the non-UI code from the UI code. I've grown generally pretty skeptical of RAD development as defined by Delphi and Lazarus. RAD often just leads to bad design, where the non-UI code is all mixed into the form code files. A Web app will ensure that you don't make that mistake. The other advantage to a Web version is that it would mean you wouldn't necessarily have to do all desktop and mobile platforms, since users of unsupported platforms would at least have a Web-based fallback to use. ---------------------------------------------------------------------- jacmoe · 2017-02-20 00:19 I am actually approaching this from the opposite direction, so to speak: have been developing web applications for a couple of years, and I want to return to the desktop. So, while the application is primarily developed at the desktop, I think I have a good chance of picturing where it fits in relation to the web. One of the primary goals is to develop an old school offline first desktop application, while at the same time seamlessly allowing it to sync over the wire. Anything that put the cloud into the equation should be done in a pluggable way. Ideally, this is going to be a good application when an apocalypse strikes and the internet goes black.  ((At the same time, it will definitely be able to connect if it comes back online again :p )) ---------------------------------------------------------------------- jacmoe · 2017-02-20 16:40 So, I've decided to try mORMot - with Lazarus/FPC "Fixes", not trunk. I am not using newpascal, because it seems to be just as bad as relying on trunk - and for political reasons. Hopefully, that mORMot framework will allow me to try out different approaches, from simple, in-memory, file-based, to SQLite (statically linked), to talking to a server via JSON. I am used to be using ORM, so I think I will grow to like it. ---------------------------------------------------------------------- jacmoe · 2017-02-20 16:49 Quote from: Phil on February 20, 2017, 12:37:40 amI've grown generally pretty skeptical of RAD development as defined by Delphi and Lazarus. RAD often just leads to bad design, where the non-UI code is all mixed into the form code files. It is indeed possible to write very bad code. That doesn't mean that it's not possible to do things better. I tend to make good use of units, classes, interfaces - so that the form code stays clean. Quote from: Phil on February 20, 2017, 12:37:40 amThe other advantage to a Web version is that it would mean you wouldn't necessarily have to do all desktop and mobile platforms, since users of unsupported platforms would at least have a Web-based fallback to use. I am hoping to combine web and desktop into one application.  Using mORMot (mORMotFPC). ---------------------------------------------------------------------- Phil · 2017-02-20 19:49 Quote from: jacmoe on February 20, 2017, 05:49:53 pmI tend to make good use of units, classes, interfaces - so that the form code stays clean. Well, not exactly. Separating non-UI from UI code means that the non- UI units, in general, do not have any dependencies on any Lazarus units. They only depend on FPC RTL and package units and similar units that also depend only on that, for example Web Service Toolkit (http://wiki.freepascal.org/Web_Service_Toolkit). The UI units are lean and do not contain any "business code" or code that you want to use cross-platform. Quote from: jacmoe on February 20, 2017, 05:49:53 pmI am hoping to combine web and desktop into one application.  Hmm, that statement does not make any sense to me. Maybe you could describe in a couple of sentences how you understand "cloud aware". I don't think we're talking about the same thing at all. ---------------------------------------------------------------------- jacmoe · 2017-02-20 21:26 Quote from: Phil on February 20, 2017, 08:49:57 pmWell, not exactly. Separating non-UI from UI code means that the non-UI units, in general, do not have any dependencies on any Lazarus units. They only depend on FPC RTL and package units and similar units that also depend only on that, for example Web Service Toolkit (http://wiki.freepascal.org/Web_Service_Toolkit). The UI units are lean and do not contain any "business code" or code that you want to use cross-platform. I do understand what you mean. And I have been using that approach before, in C++, where the UI code was completely separate from the base. That was advantageous, because it allowed us to easily port from MFC to wxWidgets to Qt. While at the same time maintaining the ability for the application to run on Linux/Windows/Mac. There are complexities and problems with that approach, however. Especially if you are not going to use anything other than Qt. In which case it would make much more sense to not have to go through the base layer and our custom property macros, when Qt has all of that already (and much more elegant). However, I usually separate UI and logic, and take care to not pollute my units with too many dependencies. When the time comes, I am sure that it will be possible to factor out common code, and factor out UI-less common code as well. For now, though, it is too early to be doing any of that. Except the clean code bit. Quote from: Phil on February 20, 2017, 08:49:57 pmQuote from: jacmoe on February 20, 2017, 05:49:53 pmI am hoping to combine web and desktop into one application.  Hmm, that statement does not make any sense to me. Maybe you could describe in a couple of sentences how you understand "cloud aware". I don't think we're talking about the same thing at all. It sounded much better yesterday, when I wrote it, just before getting to bed. Now, I am sure what it means either.  I guess I meant that I want the application to be self-sufficient. ---------------------------------------------------------------------- Phil · 2017-02-20 21:57 Quote from: jacmoe on February 20, 2017, 10:26:21 pmThere are complexities and problems with that approach, however. Especially if you are not going to use anything other than Qt. In which case it would make much more sense to not have to go through the base layer and our custom property macros, when Qt has all of that already (and much more elegant). Well, wxWidgets and Qt like you to subclass the wxWidgets and Qt classes, everything from the main application class down to the string class. The downside of that is that if you pull your non-UI C++ code out of the main GUI app and put it in a console app or dynamic library, you still have to include some rather large wxWidgets and Qt DLLs, even though you're not using the GUI parts. I've always though that was a pretty dreadful approach. (wxWidgets 3.1 DLLs: 10.6 MB with 32-bit Windows; QT 4 DLLs: 44 MB with 32-bit Windows.) Incidentally, wxWidgets historically has been pretty awful on Mac. My experience is that it's hard for two apps that use wxWidgets to co- exist together on a Mac. Hence on Mac you'll only find wxWidgets apps that started out on Windows, for example, and then brought over to Mac, bad ideas and all. I suppose C++ developers deserve wxWidgets. Qt behaves better on Mac and looks pretty good superficially, although the apps still have that I've-just-been-ported feel to them in many cases. For example, they're never document-based, even though Mac apps normally are document-based. Even Xcode is document-based, meaning you can open up multiple projects at the same time. You won't find any wxWidgets users here. And Qt is pretty much confined to Linux with Lazarus. ---------------------------------------------------------------------- jacmoe · 2017-02-20 22:43 I didn't like wxWidgets (still don't), so I ported the application to Qt, and the rest of the team loved it, so it became the official GUI client.  The reason for wxWidgets was because it was (still is, I guess) very close to MFC, so it was easier to get a cross platform ready gui client up and running. wxWidgets, unfortunately, does not behave and look the same on each platform, and it also had bad Windows 7 support (IIRC), so we needed something that performed well, looked great (on all platforms) and - most importantly: behaved consistently on all platforms. The reason why some people still prefer wxWidgets is that you can statically link it in, alleviating the need to ship a ton of DLLs. Qt has LGPL without any static linking exception, so you need an expensive license to be able to deploy statically linked apps. Qt is neat, though: a great designer, well written library, and one thing that I especially appreciate is the fact that you can use styles (CSS like) so that your application looks great. Lazarus, is going to be great. Because Object Pascal is much more suited than C++ IMO. Real properties, no macro voodoo. ---------------------------------------------------------------------- Phil · 2017-02-21 18:29 Quote from: jacmoe on February 20, 2017, 11:43:46 pmThe reason why some people still prefer wxWidgets is that you can statically link it in, alleviating the need to ship a ton of DLLs. A wxWidgets app that I've been using for several years has over 70 dynamic libraries linked against the wXWidgets libraries, so statically linking wxWidgets code into all of them probably isn't very practical. Note that the last time I compiled wxWidgets on my Mac, it took about 30 minutes. But maybe that's normal for C++; it probably wouldn't be considered acceptable for Pascal, though. The entire process for building SAGA and its dependencies took several hours as I recall. I wrote up some instructions so others could avoid much of the pain of building with C++: https://sourceforge.net/p/saga- gis/wiki/Compiling%20SAGA%20on%20Mac%20OS%20X/ ---------------------------------------------------------------------- jacmoe · 2017-02-21 19:46 Ironically, Lazarus stopped being cooperative today and disallowed me to use mORMot with the following message: QuotemORMot.pas(59681,33) Error: Class or Object types "TSetWeakZeroClass" and "TSQLRecordProperties" are not related ---------------------------------------------------------------------- DonAlfredo · 2017-02-21 19:54 You have to disable "Verify method calls" when debugging ! Remove option -CR, or uncheck in Lazarus. ---------------------------------------------------------------------- jacmoe · 2017-02-21 19:56 Excellent!  That did the trick.  I probably should get familiar with the project options - compiler / linker settings. ---------------------------------------------------------------------- Phil · 2017-02-23 00:54 Quote from: jacmoe on February 20, 2017, 11:43:46 pmQt is neat, though: a great designer, well written library, and one thing that I especially appreciate is the fact that you can use styles (CSS like) so that your application looks great. Something you might find interesting: I just noticed that Microsoft uses 64-bit Qt 4.8 in their excellent Remote Desktop app for Mac, although the Qt .dylib (dynamic libraries) take up more than half of the .app bundle's 39MB footprint. Made with Qt: https://itunes.apple.com/us/app/microsoft-remote- desktop/id715768417?mt=12 I use this app just about every day. ---------------------------------------------------------------------- jacmoe · 2017-02-23 20:00 That looks really neat! One thing that I do not like about Qt is that mobile dev, and perhaps also for Mac, due to LGPL, is the fact that one need to buy a costly commercial license. And the fact that it is not possible to change licenses during development. If I had the money, I would definitely consider using it, because it is rock solid. ---------------------------------------------------------------------- Phil · 2017-02-23 20:50 Quote from: jacmoe on February 23, 2017, 09:00:16 pmOne thing that I do not like about Qt is that mobile dev, and perhaps also for Mac, due to LGPL, is the fact that one need to buy a costly commercial license. I'm not aware of anything on OS X or iOS that would require you to statically link the Qt libraries into your executable. You've always been able to bundle .dylib files with an .app bundle on OS X. Years ago, Apple required iOS apps to have all code statically linked in, but now you can include frameworks (libraries in a special folder) as part of an iOS app. There may be reasons why a commercial license is required, but I don't see that LGPL would be it on Mac or iOS. ---------------------------------------------------------------------- jacmoe · 2017-02-23 21:50 Thanks for the info - that's nice to learn.