MySQL and PostgreSQL GUI tools

I am using Arch Linux, and recently there are some issues on MySQL Workbench and pgAdmin 4.

MySQL Workbench with the latest version 8.0.12 has issue to make connection through the SSH tunnelling. As a result, I have to downgrade to version 6.3.10. However, with the recent upgrade of icu package to 62.1, it breaks the MySQL Workbench 6.3.10. In order to solve this, I duplicate the following files,

cp libicudata.so.62 libicudata.so.61
cp libicui18n.so.62 libicui18n.so.61
cp libicuuc.so.62 libicuuc.so.61

So, it is a temporary solution until the package is fixed.

Then, for the PostgreSQL, I use pgAdmin. But again, with the recent upgrade of Python to version 3.7, which causes pgAdmin fails to start. I tried to run pgAdmin through Python 3.6 using virtualenv , but it doesn’t work. So, I have tried other solutions.

I tried Adminer. It depends on PHP and Apache server, similar to phpMyAdmin. However, it is less intuitive, compared to phpMyAdmin. Because Adminer doesn’t allow inline editing value.

So, I tried SQL Workbench/J. It requires Java and PostgreSQL JDBC. Once installed required package, it can work perfectly. Similar to Adminer, SQL Workbench/J can work with database other than PostgreSQL, such as MySQL.

 

Database or spreadsheet?

Sometimes we use spreadsheet to store data as table just like database. Especially the features provided like sorting and filtering. This allows us to look at the data easily without the knowledge of SQL (structured query language). Furthermore, we can add in the rows easily, do calculation using formulae, and also copy-paste the data to any cell. Comparing to database, the data needs to be filled row by row, this is the main drawback. This is why spreadsheet is easier to use.

We can also use spreadsheet to detect duplicated value using sorting, followed by a new column to check the value with the previous row, eg “=if(A2=A1)”, then we can remove all the duplicated row. Besides that, there are other functions that can be used such as concatenate().

However, there are some cases which must be done in database. For example, if there are two sheets of data, namely sheet A and sheet B. The sheet A contains some user information such as (user ID, username, email address); sheet B contains (user ID, gender, contact no.). If both sheet A and sheet B has the same number of rows and matched user ID, then both sheets can be combined easily using spreadsheet by using sorting. However, if the number of rows is different, or the user ID are not matched, what is the solution?

The above problem can be understood using the set theory, where the sheet A and sheet B are the set A and set B. If the set operation is converted into the programming, it will be exhaustive because we need to compare the user ID for set A and set B programmatically. The easier and better solution is using database SQL. Based on the output we need, we can use JOIN clause as the set operations. This is the wonderfulness of database SQL.

There is another problem, that is converting the spreadsheet to database and vice versa. Converting the LibreOffice Calc format to LibreOffice Base format can work. But I didn’t try this before. Therefore, my best solution is to use the MySQL, using phpMyAdmin interface. This is because phpMyAdmin allows us to import the OpenDocument Spreadsheet (ods) format, and even export to OpenDocument Spreadsheet format. Yes, this is how I solved the spreadsheet problems and perform the set operations using JOIN clauses in MySQL.