From Bioinformatics.Org Wiki

up to BIRCH Developer's Wiki

---

We need to replace ACEDB with a different database client/database engine

Original file in /home/psgendb/BIRCHDEV/doc/bioLegato/birchdb.

It may be possible, and even preferrable, to write a script that converts models.wrm into an SQL schema. That may end up being easier and a lot more reliable than trying to write a schema by hand.

Contents 1 Strategy 1.1 Export from ACeDB to hsqldb 1.1.1 Create a sample database using hsqldb 1.2 Choose or create DB client 2 GUI Clients

Strategy

Export from ACeDB to hsqldb

This must be fully automated so that birchdb can continue to be maintained in ACeDB until we have a reliable relational database working.

Create a sample database using hsqldb

Create a sample database and export to CSV or SQL. We need to make sure that one-to-many relationships are preserved during export. Once we're sure of the format of the exported database, we need to write code to translate a .ace file to whatever can be imported.

Choose or create DB client

GUI Clients

Search for "SQL GUI"

• http://hsqldb.org/
  • 100% JAVA; can be run from Java API
  • Disappointing. GUI is awkward and can be very frustrating to use.
  • both GUI and command line
  • can import CSV/DSV
• http://www.squirrelsql.org/
  • 100% JAVA
  • link to documentation? Where is it?
  • No way to create a database. This application seems to assume a standalone database elsewhere.
  • No apparent CLI client.
• DB Browser for SQLite
  • Seems to be GUI only, but the --help may imply running it from the command line. The GUI is pretty intuitive.
  • Has an open in external application feature
  • Appears to have some [video tutorials https://github.com/sqlitebrowser/sqlitebrowser/wiki/Video-tutorials], which we should check out.
  • Distributed as a .appimage file ie. self-contained binary (requires FUSE to run)
  • Can export SQL, CSV or JSON.
  • Can import SQL or CSV, and read in sqlite directly. (not JSON?)
• SQLite Studio
  • Can import/export sqlite, SQL, CSV, JSON
  • Generally pretty easy to use, but can sometimes be frustrating.
  • Has both GUI and command line clients
  • FOSS
• DBeaver
  • commercial, but there is a PRO version
  • even free version requires a license
  • NOT TESTED
• http://dspro.sourceforge.net/
  • NOT SUPPORTED. Latest version 2005.
  • No apparent documentation
  • 100% Java with a full API
  • Not obvious that it has a command line client.
  • Claims to support just about any database system, but not clear what sort of files it can import and export.
• https://en.wikipedia.org/wiki/NoSQL