search

Epi-Info / Epi-Info-Community-Edition

Other
19 stars 10 forks source link

Fully implement MySQL, PostgreSQL, or MongoDB #9

Open cdc-dpbrown opened 8 months ago

cdc-dpbrown commented 8 months ago

Epi Info 7 can use either MS SQL Server or Access as its backend database and host an Epi Info 7 project. Additionally, Epi Info 7 can read and analyze data in other databases and flat files like Mongo DB, MySQL, PostgreSQL, Excel, and CSV files. The later have not been completely implemented in Epi Info 7 to allow them to be used to contain an Epi Info 7 project.

It would be helpful to our end-users to have another full-function database option because although Microsoft Access is basically free, it can’t be hosted on a server like MS SQL Server. The downside to MS SQL Server is that it is expensive and may negate the benefit of a free Epi Info 7 to some of our limited resource end-users. MySQL, PostgreSQL, and MongoDB are free and open-source relational databases that are often found at hosting sites and are already partially implemented in Epi Info 7.

The classes that handle interactions with the databases implement common interfaces: IdbDriverFactory, IDataSource, and DbDriverBase:IDbDriver. MS SQL Server and MS Access fully implement the interfaces whereas the others only implement enough to read the data for analysis.

MySQL is used in the Epi Info Android Companion so there may be a benefit to fully implementing MySQL first. The goal of this issue would be to pick a database to implement first then implement it.

erik1066 commented 3 months ago

Hi @cdc-dpbrown, is anyone working on this?

cdc-dpbrown commented 3 months ago

Hi Erik, No, it's open. John is working on adding SQLite, I think mainly because it's used in our mobile apps. I got that wrong in my writeup of this issue. MySQL in Desktop and Web Survey is what we hope to have one day. Thanks for asking. Thank you, David

erik1066 commented 3 months ago

@cdc-dpbrown Would Epi Info be OK with PostgreSQL instead of MySQL? Newer versions of the MySQL DLL from NuGet are GPL 2.0 licensed only and incompatible with Apache 2.0. I don't think it can be included in the Epi Info 7 project as a consequence without putting all of Epi Info under GPL 2.0 license terms.

There might be some compatible alternative implementations of MySQL for .NET. I am unsure if those could alternatives be trusted. Security of the software supply chain is a top concern.

PostgreSQL's Npgsql project is very permissively licensed in comparison to MySQL.

cdc-dpbrown commented 3 months ago

Good point. There it is then. SQLite and PostgreSQL it is. Thank you.

erik1066 commented 3 months ago

@cdc-dpbrown Understood. I coded a partially functional DbDriver for Postgres last night and have been testing it in Form Designer, but it needs some bug fixes before I can do a PR back to the main repo. See https://github.com/erik1066/epi-info-community-edition/tree/erik1066/postgresql. I'll keep you all updated.

zfj4 commented 3 months ago

@erik1066 , @cdc-dpbrown I'm working on a driver for SQLite and it's not a small task. So far it will build a DB with Form Designer and now I'm working on Enter.

cdc-dpbrown commented 3 months ago

I'm going to close this issue because it a has morphed beyond its description. New issue: https://github.com/Epi-Info/Epi-Info-Community-Edition/issues/12

Regarding MySQL, we may need to drop the read/analyze functionality we already have in Epi Info 7.