WHONET SQLite data files
Summary
For the past twenty years, WHONET has relied on a simple but robust data file structure called dBASE (also known as DBF files), which has worked well for WHONET users for many years. WHONET and BacLink are now ready to support a newer and more modern data structure called SQLite which offers many advantages over dBASE.
- Modern database platform, described as "the most used database engine in the world".
- Compatible with modern versions of Microsoft Windows, MacOS, and Linux. WHONET is currently only natively-compatible with Microsoft Windows, but SQLite may allow emulation systems to function much better than with the dBASE structure on MacOS and Linux.
- Multi-user data entry.
- Compatible with modern web browsers, which will be important for our future web version of WHONET.
- Data are managed as individual files in exactly the same way that we use WHONET dBASE files, so your data management will remain the same as it has always been.
- Much simpler to use than MySQL, SQL Server, Oracle, etc., requiring no database server.
- Increased performance.
- More compact files (smaller file size) versus dBASE when storing the same information.
- Supports much larger data files (dBASE has a strict 2GB limit).
- Modern security features.
- More sophisticated capabilities, transaction support, indices, views, etc.
- Some computers have compatibility problems with dBASE files. SQLite avoids these issues entirely.
- SQLite is recommended for long-term data storage by the United States Library of Congress.
Our new versions of WHONET support both the existing WHONET dBASE files, as well as the new WHONET SQLite files. You can use any combination of the old dBASE and new SQLite files without issue, assuming you do not have one of the compatibility problems referenced above.
Performance comparison
The below table gives examples of the performance improvement using WHONET SQLite data files using one year of data from a 700-bed hospital and over 70,000 samples.
| WHONET 5.6 - dBASE | WHONET 2019 - dBASE | WHONET 2022 - dBASE | WHONET 2022 - SQLite | |
|---|---|---|---|---|
| Escherichia coli - % RIS (n=5092) | 0:10 | 0:06 | 0:05 | 0:04 |
| Organism summary (n=70798) | 0:50 | 0:30 | 0:19 | 0:16 |
| Location summary (n=70798) | 5:52 | 6:30 | 0:20 | 0:17 |
| WHO GLASS export (n=70798) | Not applicable | 2:03 | 1:20 | 0:46 |
| EARS-Net export (n=70798) | 0:06 | 0:06 | 0:05 | 0:03 |
| BacLink 2 - dBASE | BacLink 2019 - dBASE | BacLink 2022 - dBASE | BacLink 2022 - SQLite | |
|---|---|---|---|---|
| 1 year of data (180 MB) | Too much data | 3:24 | 3:15 | 3:13 |
How to create SQLite data files
-
In WHONET's data entry module or BacLink's 'File format' simply choose SQLite from the list of file types for your new data files.
- WHONET also has a convenient feature 'Update data files to SQLite' accessible from the 'Data entry' menu.
- This feature will transform your WHONET dBASE files into SQLite, while leaving the original data files unchanged. You may use it to transform many files at once.
- There is no requirement for you to update your existing dBASE files to SQLite, but you will notice faster analysis times if you do.
-
You may wish to change the default 'File format' for BacLink's output files to SQLite. This setting can be found by pressing 'Edit format' then 'New data file' in BacLink.
How to view SQLite data files
- You may use WHONET's 'Data entry' module to view and edit the SQLite files by pressing 'View database.'
- dBASE files could be opened but not edited with Excel. This is not possible with SQLite, but you can use a freely-available tool such as DB Browser for SQLite. This software is very similar to Microsoft Access and works with a variety of operating systems.