Tech

Tech FAQ

Database

For our database needs, we are using SQLite. Why SQLite?

Query Language Support: It supports SQL, which is the standard database query language.

Platform Support: It is well supported on Windows, Unix (Including all major Linux distributions), and Mac OS X. In fact, it ships by default on OS X and Linux, and only requires bundling a DLL on Windows. (iOS and Android also ship with SQLite).

Tool Support: SQLite is supported by a number of commercial, freeware, and open-source tools.

Simple Database installation. You only need to have the proper .sqlite database file available. What's more, databases can be stored on USB disks, etc.

Encryption Support: Versions of SQLite with encryption are available.

Small, Fast, and Lightweight. We don't need raw partition support or row-level security. We do need something small, simple, and reliable that we can bundle with our application if need-be.

Programming Language Support: SQLite is supported by almost every programming language and toolkit, including our preferred FreePascal and Zeos Database Library.

Open Source: SQLite is open source, and well supported by the community.

GUID

Some people have asked why we have these long GUID fields all over the database. The short answer is: To enable synchronization between databases.

During the prototype design phase, we used normal integer ID fields for most tables, however conflicts can arise when copies of the same database are edited on multiple machines. We want to allow this, so each local copy will store a local ID, as well as a global GUID. GUID identifiers are not used for tables with standard identifiers like country code, language code, etc. They will be used for data that is PlusAlpha specific and not machine specific. GUIDs stored in the PlusAlpha are currently stored as text. While we are aware that this is somewhat wasteful of space, it eases debugging (compared with binary data), and SQLite does not have a GUID field type.

Hash

If you look at the database, you will see hash fields in various places. These are MD5 Hashes, stored as text. We store these as text for similar reasons as the GUID fields. Hashes are used mainly to match text and to detect duplicates. We use MD5 because it is sufficient for our purposes and included in the FreePascal standard library. (We do not use MD5 for anything to do with encryption).

Pascal

We use Pascal (and specifically, FreePascal) because it provides the following benefits:

Easy install. Simply copy the compiled EXE to the machine where you want to use it. It isn't necessary to set up registry entries, etc. Friendly installer packages can be set up as well, but that's optional.

No Runtime Required: Unlike some other cross-platform solutions, Free Pascal binaries don't need any external run-time (such as Java or .Net, etc.). Users can download it and run it. The only external dependency is SQLite.

Portable. In keeping with #1 and #2 programs compiled with FreePascal can easily be run from portable hard disks, USB disks, etc.

True Compilation: Programs are compiled to native binaries, which means that they run at a similar speed to other compiled languages like C, and don't need to have any scripting language installed.

Cross-Platform Support: FreePascal targets all of the platforms we want to support, and more. We can easily generate binaries for Windows, Mac OS, and Linux for both 32bit and 64bit. This support also extends to GUI applications, without requiring the use of non-native GUI widget libraries like GTK and QT.

Multi-Mode Support: GUI, CLI, and web applications are easy to create in FreePascal. We plan to support GUI and CLI usage scenarios from the start for most features. Mobile and Web interfaces may come later.

Multi-Vendor Support: Although we are targeting FreePascal, it is also possible that we may offer support for Delphi and other compilers in the future.

Open Source: Free Pascal is open source, and parts of Delphi are open source as well. When something goes wrong, it't not a mysterious black box. There is great community support available for FreePascal as well.

Third Party Libraries

We are trying to avoid the use of third party libraries as much as possible, in order to make it easier to download and compile the application. In some cases, that means forgoing an awesome GUI widget, or writing more code than we have to.

Currently we are using:

ZEOS Database Library: Please download this on your own.

CSVDocument: This is a small 3rd party library for reading CSV files. Since it's small, we included it in the repository.

Object Oriented vs. Procedural Programming

We are trying to use OOP as much as possible in our design, but in some cases procedures (and functions) just make more sense.

The main advantage we see of using classes over normal procedures is encapsulation of state, and hiding of implementation details - but if you think about it, Pascal's Units already offer that. An additional advantage of classes over units is the ability to instantiate multiple objects at runtime. This is somewhat unnecessary for most of our objects, but we are doing it anyway. (For example, since you are only likely to import one CSV file at a time, there isn't much of a requirement to instantiate multiple TCSVFileImporter objects simultaneously). On the other hand, for objects that you will likely only need one of, or are likely to be re-used, we are creating a default object in the hosting unit.

Just to be clear, we are using Classes (Delphi compatible), not Objects (Turbo Pascal compatible). We aren't using Objective Pascal, as that is currently only supported on Mac OS X and offers no significant advantages for our use.

Internationalization

This entails three main areas:

1.Infrastructure and character set support. We store everything in the database and all object fields as UTF8. Most hashes will also be of UTF8 strings. ANSI (SJIS, etc.) strings loaded from external sources will be converted to UTF8 as processes.

2.User Interface Internationalization. We are using the standard FreePascal internationalization technology, which means that we declare hard-coded text strings as ResourceString, and the compiler generates .po files that can be translated with standard tools.

3.Database Internationalization. Although simply storing data as UTF8 in text fields guarantees support of any characters the user cares to use, supporting multiple languages for a single piece of text requires a more sophisticated plan. For example, you may want to have a certain vendor name show up in Japanese when logged in in Japanese, and in English when logged in in English. While it may seem that most users of a personal accounting package wouldn't need translation support, there are two reasons why we chose to integrate the support from the start:

We will ship the database with a standard set of accounts. At least those accounts should display in the languages the GUI officially supports. Users won't make a distinction between the program and the data. Having an English GUI with Japanese accounts of vice versa would seem very strange for many people.

Adding the support at a later stage might require a major re-design and touching every component that uses text. We want to at least have a plan for it from the beginning.

Thankfully, we have a relatively simple scheme which handes most of the supported use cases:

Every table where text is to be stored will have a TEXTGUID and/or TEXTNO field. A TEXT field will also be present in some tables for now.

The TEXT table contains the TEXTGUID and TEXTNO fields, together with a language code, which act as a key for the text to be stored or accessed.

When the user stores text in a table, a new TEXTGUID is generated and stored in the text table, along with the desired text and the current language code. (Taken from the login language).

When the user tries to retrieve the text later, the combination of the TEXTGUID and current language ID are used to retrieve the text from the TEXT table.

If the text does not exist in the current language, a new search will be done using a fallback language. (For example, if Japanese it not available, English text can be returned).

Some tables have a TEXT field which can be used in the interim for simple mono-lingual support while testing. This field does not have an accompanying language code. (This makes it easier to verify test data by looking at the tables directly with database utilities). This field will likely be converted to a database view field in the future.

Storing and loading the text from the text table is the responsibility of the object layer. From the application point of view, the text is a part of the record it is dealing with (f.e. The current Ledger Account).

The text table can store text from any of the other tables, but will not be used for system text like user interface translations.

The text table is not currently reference counted, and nothing like Copy-on-write is planned for implementation at the moment. This means that the same text may appear many times in the TEXT table for things like transactions that routinely occur. This functionality can be added later if there is significant merit.

The TEXT table enables translation of account names, vendor names, etc., with relatively little overhead. For entries less likely to be translated (such as individual transaction line-item memos), it only adds the overhead of storing a single GUID twice (38 bytes x 2), and a single database look-up.

Getting Started:

If you want to get started, please follow these steps:

Download and install FreePascal, FreePascal Source, and Lazarus. (These may be separate or together, depending on the platform).

Download the Zeos database library and install it so that Lazarus/FreePascal can find it.

Install GitHub or a Git client of your choice and create a local copy of the PlusAlpha source code.

You will have to create a copy of the database that the program can find when you run it.

For OS X, just run ./dbRefresh_OSX.sh in the source directory

For Windows, the directory varies, but you can find it by stopping the debugger on the line "CompleteDBPath := DatabaseDir + DatabaseName; " in the paDatabase unit. Then use explorer or PowerShell to copy the database file there.

For production use, an empty copy of the database will be stored in the same directory as the program (on windows) or in the application bundle (on OS X), and copied to the appropriate user-specific directory when the user runs the program for the first time. (Unless they are using portable mode, in which case, it will use a copy in the current directory).