For information on using the wiki software see:
Please add to the discussion sections if you can make a useful input.
QERCUS - A desktop database program
- A database for storing all kinds of information with structure, multiple hist-lists and powerful search facilities. It is quite different to the type of databases that are built into Microsoft office, Libre-Office etc. Probably the two most interesting features of Qercus are (i) a full text search capability (using Xapian - a specialist text index/search library), and (ii) the ability to add or remove fields to any record arbitrarily to suit the available data. Some aspects such as XML import/export are unfinished - but all of the core functions work well, and Qercus can be used for real applications. The URLs of websites and files (Image, Pdf and Audio files) can be built into the structure of databases. Qercus can be is used for keeping notes, observations, and catalogues of almost anything - for which the ability to set up parent-child relations between records is often useful. Qercus an also be used for simple 'card index' applications such as address books. Any number of 'types' of record and fields can be declared (using pre-defined base types, such as text, integer, audio, pdf, www, image ...). Colours for each type can be chosen arbitrarily - garish or grey, it's your choice.
- The project provisionally had the name of Knowledge. There is an article here:  Qercus has come a long way since this article was written.
- Currently programmed in Python 2.6 / 2.7.
- The Python prototype uses Canonical Storm to allow Python classes (objects) to be stored on sqlite3. This was very easy to implement. Storm is very impressive.
- Can run on Windows or Linux. Database files are identical whichever platform is used.
- Three example databases (books.qdb, podcasts.qdb and mpapers.qdb) are now supplied.
- Version 0.24b2 has fixes to path handling, particularly for the "any url/file" type field. These should not affect Linux users - the mods. are to permit use of backslashes in filenames on Windows machines. You can, for example, create a button that opens a directory with MS explorer, Nautilus etc. Wordprocessor documents etc. can also be opened in this way. The bug on the Ubuntu Unity desktop described below is fixed in the 12.04 LTS release.
- Version 0.24b1 (release December 2011) allows the tab key to be used to move the keyboard focus in the main search dialogue. Also, Shift-F5 may now be used to bring up the search dialogue ready for text searches. Both the search and tree-search dialogues now have an additional check-button labelled 'Switch'. If this is selected, then the chosen hit-list is selected automatically after the search has been completed.
- Version 0.23b3 includes revised Windows build scripts (and a standalone exe is downloadable for the first time). The external applications (web-browser, mp3 player etc.) listed under Define-Configuration may now include command line flags.
- Version 0.23b2 makes a minor change so that the menu bar works to improve operation on the Unity desktop (tested with Ubuntu 11.10). Also, build improved build scripts for creating a Windows exe distribution are included (an exe build is not presently distributed because of possible copyright issues with DLLs required). By default, the exe distribution is now standalone - Python, Qt etc. need not be installed separately. Note: On the Unity desktop only (Ubuntu 11.04/11.10), menu entries do not grey out once they are black (e.g. look under Marked_Record menu). This is a bug in Unity (see below).
- Version 0.23b1 is the first beta release.
- Version 0.22a1 implements macro commands. These are embedded in fields of base type macro and can be placed in any record. Available commands are listed below. The ViewPanel (i.e. list of records on left hand side of window) now resizes properly. If no bugs are found, this will be released as a beta in a month or two.
- Version 0.21a3 improves import and export functions. While the database is designed for utf-8, it no longer crashes if characters from other unicode character sets are encountered. Fields matched by search operations are now identified on screen in displayed records. There are some other minor bug fixes and refinements. A lot of testing on Ubuntu 11.04/ Kubuntu 11.04, Windows XP and Windows 7. Work on experimental scripts for building a standalone .exe version to simplify distribution of Qercus on the Windows platform.
- Version 0.21a2 includes a number of usability and bug fixes. Individual fields matched during search operations are now identified on screen. Added four new field types for external files. These include fields to allow PDF and Audio (e.g. MP3) files to be displayed or played from databases. New configuration dialogue window to allow external browsers to be specified.
- Version 0.20a2 shows an hour-glass cursor during import, export and re-index operations.
- Version 0.20a1 for the first time has working word indexing and search facilities. Import and export facilities have also be completed to allow data to be read as text to & from other databases (Blackwell Idealist Natural format is only supported so far).
--Andrew22 17:21, 16 April 2012 (UTC)
To make Qercus work you will probably need to install a few things (unless you are using the downloadable .exe build for Windows). For Ubuntu/Kubuntu packages can be installed from the repository. For Windows, installation of the packages needed to make the scripting (source) distribution work is slightly more work as it is necessary to download files from various websites. In most cases it just a matter downloading and double-clicking, but to install Canonical Storm requires shell commands to carry out a 'build' process. NB: choose 32-bit downloads for all Windows versions. The following are required:
- Python 2.6.X or 2.7.X (already installed on Ubuntu/Kubuntu 11.04/11.10). Install this first!
For Windows download Python 2.7.3 from http://www.python.org
- Python-Xapian Bindings 1.2.X (probably already installed on Ubuntu/Kubuntu 11.04/11.10).
For Windows download Xapian bindings 1.2.6 for Python 2.7 from http://www.flax.co.uk/xapian_binaries
- PyQt4 and Nokia Qt4 4.X GPL Use Qt 4.5 or later. For Windows download combined binary package from http://www.riverbankcomputing.co.uk/software/pyqt/download
The path of Qt DLLs (e.g. path C:\Python27\Lib\site-packages\PyQt4\bin) should be added to the system variable PATH automatically. If it is necessary to check this, use Control-Panel--System--Advanced--Environment. These packages are probably already installed on Ubuntu/Kubuntu 11.04/11.10. In the Ubuntu repositories, PyQt4 is in package python-qt4.
- Microsoft Visual C++ 2008 (free). Windows only, optional, but recommend that this is installed prior to building Storm (to give a much faster build). NB: MSVC 2010 will not work.
- Canonical Storm 0.18 or 0.19
In Debian/Ubuntu/Kubuntu repositories a ready-built package named python-storm is available. For Windows download from https://launchpad.net/storm/+download and use a program such as 7-zip to unzip the bz2 file into a temporary directory. To build and install it is necessary to Open a command window (run cmd.exe) and change directory (cd) to where you unzipped Storm. Then type
C:\python27\python.exe setup.py install.
Use Google to help you with any error messages. 'Unable to find vcvarsall.bat' indicates that the C++ compiler has not been found.
Once everything is installed you can run Qercus from the Python editor IDLE (open Qercus.py and type F5), or a shortcut icon can be setup. On Linux make sure the Qercus.py file is executable. For Windows set shortcut properties along the lines of
C:\Python27\pythonw.exe C:\PySource\Qercus\Qercus.py <optional_database_name.qdb>
The start-in directory can be set to whereever you want to store your databases. Running Qercus from IDLE provides error messages that are useful in the event of problems such as missing packages. On Linux, it may be necessary to install IDLE from the repository.
If you wish to try building a Windows executable (see below), you will also need py2exe-0.6.9 for your version of Python, e.g.:
--Andrew22 22:18, 21 January 2013 (UTC)
Exe build scripts (Windows only)
A standalone executable build of Qercus can be downloaded, but if you prefer you can create an executable yourself (from a working installation of Qercus source code, Python interpreter and the packages listed above). If you wish to do this, read this section:
Build scripts are supplied with the download in subdirectory Qercus_ExeBuild. The build system is based on Py2exe (which must be installed in order to produce an .exe build)). A Windows batch file run27.bat automates the build process. Run the Windows cmd.exe application, cd to the build directory, and type run27. The executable and other required files appear in subdirectory QercusExe27. By default, Qt DLLs (GPL) are also copied to make the distribution standalone (edit the py2exe_setup.py file and change the flag QtInclude from True to False to change this). Similarly Microsoft Visual C++ 2008 redistributable DLLs are copied by default, but also can be excluded if desired by changing a flag in the py2exe_setup.py file. Some example Qercus databases are copied to this subdirectory. For redistribution to other machines, the entire QercusExe27 directory can be turned into a Zip file. The '27' is included in filenames to indicate that Python 2.7 is used.
--Andrew22 07:36, 2 April 2012 (UTC)
Hints For Using Qercus
- Make sure that the database file (extension .qdb) is located in a read-write directory and is writeable. There is presently no support for opening databases in read-only mode.
- Files used by the text indexer (Xapian) are saved to subdirectory nature_xapian for a database called nature.qdb. This directory and the files within must also be writeable. All of the information in the database is contained in the nature.qdb file - the nature_xapian subdirectory is only used for indexing. If deleted, the index and the nature_xapian subdirectory can be restored using the function-menu command Re-build index.
- The default subdirectories for audio (e.g. mp3) files, image, and pdf files for a database called nature.qdb would be nature_AudioFiles, nature_ImageFiles and nature_PdfFiles (these subdirectories being in the same directory as nature.qdb). Any available directory can be used if the full pathname is specified when the field is edited. From v0.23b1, filenames in fields (e.g. audio files) are automatically put into a format that is compatible with both Linux and Windows when they are entered (to make databases portable). Directory separators are forward slashes (if backslashes are entered they are converted to forward slashes automatically).
- If any of the default subdirectories is missing it can be created with a file manager.
- When editing audio, image and pdf fields, use a forward slash (even on Windows) to indicate that the file is in a subdirectory of the default subdirectory. For example, if an audio file for a database called nature.qdb is specified as Insects/buzzingbees.mp3, the file would be nature_AudioFiles/Insects/buzzingbees.mp3
- The external mp3 player, pdf viewer and webbrowser are specified using the Define-Configuration menu option. For Ubuntu/Kubuntu, suitable mp3 players include amarok, audacious, potamus. and kmplayer (lowercase names are used for these executables). A lightweight player such as potamus (Linux), audacious (Linux) or VLC Media Player (Windows) is recommended.
- At present, only text is indexed. Therefore searches for float, integer, and datetime fields are significantly slower. For databases containing a few thousand records, the search speed is quite adequate.
- Logical terms AND OR etc. in text searches must be given in uppercase.
- A logical AND will only match fields where the searched words are both present - it will not match records where they occur in different fields.
- The F5 key brings up the search dialogue (just as it does in Blackwell Idealist). From vs 0.24b1, Shift-F5 brings up the search dialogue with the text panel selected (this feature was added as text searches are the most commonly used).
- Double clicking on a field opens it for editing (as an alternative to using the field button).
- The Record Number is an integer that is incremented each time a record is added to the database. It is never decremented.
- The long box on the left hand side is used for listing records in selected hist-lists and up to one marked record (underlined). It is referred to as the ViewPanel.
- Records found by a search operation are referred to as a Hit list. Six separate hit lists are available under the Hit-lists box. Any or all of the hit-lists can be selected using radiobuttons (the deduplicated list appears in the ViewPanel). Many functions grey out unless exactly one hit-list is selected. The radiobutton for non-empty hit-lists has a green background, and the number of entries can be seen as a tool-tip using the mouse cursor..
- Up to two records in the database can be Marked. The 'Marked' box on the left hand side of the screen above the ViewPanel contains two radiobuttons, either or neither of which can be selected with the mouse (but not both). If one of them is selected it is possible to mark the current record by clicking the blue button (top right). When a record has been marked, the radiobutton gains a green surround, and it is possible to see the number of the marked record as a tool-tip using the mouse cursor. When a radiobutton with green surround is selected, the marked record is included in the ViewPanel, but underlined. Marking records is useful (i) as an aide memoire and (ii) to enable some options under the Marked_Record menu. These functions are applicable to parent-child relations between records.
- Parent-child relations can be set up arbitrarily between records, and are another way of adding structure to databases. In a database about insects, for example, you could declare a parent record for all the records about bees, and another for wasps. In the Qercus world, child records can have any number of parents (and vice versa). Reciprocal relations are also possible. Most of the functions appear under the Marked_Record menu (so require a record to be marked and selected), but the Tree Search button allows an immediate search for the children or parents of the currently displayed record. After a tree search, the hit-list in which the search results are stored is not automatically selected at present (do this with the mouse).
- The Display Label (shown to the left of each field) is set in the Define-Fields menu. Qercus works differently from Idealist (which always uses the field type as the label). For example, in Qercus fields of type poem_title and book_title could both use be labelled as 'Title'.
- The tool-tip shown when the mouse cursor is placed over the display label includes the field type.
--Andrew22 21:06, 24 November 2012 (UTC)
Fields of base type macro allow database commands to be embedded in the database to aid searching and navigation. Clicking a button within the field executes the commands. The help is not yet completed, so the list of commands that can be used at present is listed here: FindChildren (of displayed record), SelectHitLists, SearchByText, SearchByRecordType, and SearchByRecordNumber. Argument Hn represents hit-list n, and RECxxx represents record number xxx. If Hn is omitted for search commands, the current hit-list will be overwritten by search results. Examples:
SearchByRecordType H2 "navigType" SelectHitLists H2 H3 SearchByRecordNumber H1 REC576 REC577 SearchByText H4 "bearded AND dwarf"
Import and Export in Idealist Natural Format
This is a simple text format that allows records to be imported and exported to & from the current database. This format is designed to be compatible with the 'Natural' format used by the Blackwell Idealist database. Field and record definitions are not included in files. Idealist did not have captions, e.g. in image fields, so when exporting from Qercus captions are given their own field identifiers, so potentially files could be read into Idealist. Empty captions are skipped while exporting. Importing is a two-pass process; in the first pass Qercus checks that field and record types in the imported file have been defined in the current Qercus database. If there are no missing definitions, the records are loaded in the second pass. The imported records are put into the selected hit list - choosing an empty hit list would normally be a good idea.
If some records are exported to a file, deleted from the database, then imported again, the resulting database will be identical except for the record numbers. There is presently no unique identifier that is exported, but a text field that allows the user to enter a unique identifier could be used..
--Andrew22 18:05, 13 November 2011 (UTC)
In case you are puzzled by the fact that some menu options are always greyed out, the explanation is that these are unfinished features. In v0.23 these are:
- The Undo key (to undo edits in the current record).
- Find and Replace text in current record.
- Import and export as Formatted, XML and BibTex. Import and export XML will allow everything (including record and field definitions) to be read/saved as files. Formatted export will allow the user to decide on formatting information, e.g. for export to a wordprocessor. At present only Idealist Natural Import and Export is supported (see above).
- Hit-list overview. To allow one field type (e.g. author or title) of every record in the view list to be shown in a Window.
- An editable list of stopwords (words ignored by the text indexer). Stopwords are frequently used words such as "if, then, than, it, were, because ..." Xapian implements many stopwords its own, so maybe this is not necessary.
- An editable list of synonyms recognised by the text indexer, e.g. color=colour.
- In the search dialogue, 'Exclude Minimised' and 'Parent Records Only' options.
There are no options for supporting languages other than English, or character sets other than utf-8.
Discussion - Wish list
Ideas for improving Qercus (in addition to completion of unfinished features).
- Support for opening databases in read-only mode, including support for databases on read-only media.
- Password protection for databases.
- Many types of field have caption subfields. A more flexible mechanism for adding subfields to fields and controlling the layout when they are displayed? The presentational layout is determined on a per-field basis (unlike the Idealist approach of having editable "layouts" that are applied to whole records). Fields containing multiple subfields can be referred to as compound fields.
- A pick-list text field type to restrict entry to items defined in a list. For a currency field these might be pounds, dollars, etc.
- Support for non-ascii characters and symbols.
- Indexing of integer, float and date-time types for faster searching (presently only text is indexed).
- Multi-document interface so that multiple databases can be opened at once.
- Default values for fields (with which fields are initialised when created).
- A Windows installer/uninstaller (using Inno Setup or something similar).
- Indexing of external PDF documents in 'pdf' fields. If the field is deleted or modified, the index should be updated. Xapian (the search library that Qercus uses) does appear to support indexing of PDFs. Ideally we would like a facility (scripting?) that would allow a directory of PDFs to be imported with a new record incorporating a pdf field for each - but this could get quite complicated.
--Andrew22 14:04, 19 November 2011 (UTC)
Discussion - GUI design
If you have comments, please add them here.
Discussion - bugs
There are still a number of uncompleted features (see above), but the features that are implemented should be bug free. If you find any bugs, please note them here.
- A bug in the Unity desktop in Ubuntu 11.04 and 11.10 prevents menu bar and menu options from greying out when required to (they stay black) - https://bugs.launchpad.net/bugs/787946 bug report. This will cause confusion and apparently odd behaviour. In Qercus 0.23b2 and later,it is no longer required for menu bar entries to grey out, but the problem with entries under the drop-down menus (Marked_Record etc.) remains. A more formal description of the problem is that function setEnabled(False) does not work on objects of type QAction. The bug appears to have been fixed in Ubuntu 12.04.
- The tool-tips for the hit-list select buttons show the number of records in that hit-list. The value shown can be too high as it is not deduplicated when there are multiple fields within a record that met the search criterion.
- When running macros, a blanket check is always made to ensure that one (and no more) hit-lists is selected. While this simplified the program, some macro commands do not in fact require a hit-list to be specified.
- A dialog showing error Missing Entry Point in QtCore4d.dll (_Z11qWinAppInstv) on running the Windows executable build indicates that Qt DLLs are present on Windows PATH (as well as in the exe build directory). The easiest solution is to remove them from PATH. You could also consider setting up a local application path in the Windows registry.
--Andrew22 12:14, 16 April 2012 (UTC)
Discussion - database backends
Qercus uses the sqlite3 SQL engine (built into the standard Linux and Windows Python distributions), and an Object Relational Mapper called Canonical Storm (for Linux users this is deb package python-storm). Storm provides efficient object orientated features (and is impressively easy to use). From Qercus 0.20 onwards, a text search engine (Xapian - already installed in Ubuntu systems) is used to allow text indexing and searching. Every field within a Qercus database has a unique number (which is the primary key of the fields table). The same number is used as the docid in the Xapian database, thus making a link between the two. I had previously thought that deleting or updating fields would not update the list of indexed words, but I was wrong and it works perfectly.
A C++ version of Qercus would probably offer some performance improvements. Sqlite3 and Xapian are both C++ libraries, but Storm is Python-specific. I'm not sure what would be the best approach here .. a more complicated table structure or a switch to an object orientated database such as MongoDB. An intermediate step would be to write a C++ module for all of the database features, and interface this to the present Python program with e.g. SWIG. In some ways this is quite an attractive idea as the C++ module could then be utilised by other programs, e.g. for a web interface.
--Andrew22 21:24, 11 November 2011 (UTC)
Icons for Linux (qdbicon.png) and Windows (qdbicon.ico) are supplied in the download. New 256x256 pixel icons have been supplied since v0.21a1. These are compatible with Windows 7 as well as XP.