Developing .NET winforms Database Management IDE Editor

Posted on by avraampiperidis

This article is a project presentation of a SQL database management tool.

Introduction

This article is a project presentation of a SQL database management tool and the whole development process. The project name is LightBox and the source code can be found HERE.

What This Project Is About

This project is a windows SQL database management tool. Some of the main features are the following.

  • Focus on speed and simplicity
  • Ability to handle/edit a large amount of data
  • Multi database support like MySql, Postgresql, SQLite, Oracle
  • MySql dump abilities
  • IntelliSense editor
  • Open-source 
  • It’s not as rich as MySQL workbench, dbeaver, sqldeveloper, or any other full-blown IDE ecosystem and it will never provide such functionality.

Goal

The goal is to provide fast performance and speed in high priority and have a simple UI interface. Also to support as many databases as possible and be stable software with a decent amount of configuration. The loading time of opening the application and the responsiveness is really fast. It designed to handle and represent a large amount of data from tables when needed and have a powerful editor with a good IntelliSense.

The Road So Far

At the moment of writing this article, most of the core functionalities and UI work is completed. Also, the databases supported are MySql, MariaDB, PostgreSQL, SQLite.

The following are the main categories of what is left to be done.

  • Implement SQL statement parser for Oracle, DB2, MSSQL
  • Implement SQL app support for the above vendors
  • All the others like configuration, testing, UI theming, multiple platform support, tunneling, multilingual(never gonna do it), security.

Origin

How and Why I Have Done It in the First Place?

You may ask yourself, why I have done all these and bothered in the first place? why don’t I just use some other tool like dbeaver?

I always loved to code and I still love it when I’m working or as a hobby/side projects. I started coding before I went to university and in my first semester, I already knew that I wanted to become a software engineer, I always loved to create my own tools/frameworks/libraries and work on enterprise systems.

Now this project always was a hobby for me and I’m contributing whenever I can.

When and Where This Project Started

It all started as a university course project we took my and my friend Valandis Pitsas. 

The project back then was to create a MySql dump application (Firedump). It was a .NET windows form application written in C#. It was something we enjoyed doing.

Today Valandis still continues improving MySQL dump (Firedump) and adding extra features. The last feature he added was full incremental backup and delta backups.

Early Stages

In its early stage it was just a MySQL dump app for the university course we did me and Valandis and below is a screenshot of the app.

It had many features like advanced options, the ability to upload the dump in FTP or cloud(dropbox), and others.

One last feature I really wanted to add before presenting the app was a SQL editor with IntelliSense. I thought it would be the coolest thing to show and will blow everyone’s mind.

After some work, this is the early stage of the app.

It looks nothing like the current one and it was super buggy, but it went pretty well in the presentation.

After some years I decided to create a SQL editor as a hobby/side project. I also took some inspiration from SqlTools (oracle client) which I really enjoyed using for years.

I created a new repo, took the good parts from Firedump, and started coding.

Project Architecture Breakdown

Tools

For development, I use Visual Studio 2019 community edition. The app is a windows-form written in C# and runs on .NET 4.6 and above. In theory, it could run on Linux through WINE but I have never tested it.

App Layout

This is the main UI of the app.

The UI is separated into 5 main categories/areas.

Top bar menus and bottom are part of the main form. Left, right and center panels are children of the main form.

An abstract UserControlReference class that extends System.Windows.Forms.UserControl class takes place. Left, right and center content are extending  UserControlReference and main form holds a reference to them.

So for example, if I want to get a reference to the Editor user control or any other control of type UserControlReference I have the following generic method.C#

internal T GetUserControl() where T : UserControlReference
     foreach (UserControlReference c in ChildControls)
         if (c is T)
             return (T)c;
 -- and use it
 var editorControl = GetUserControl();

App Breakdown

Below the analysis of the main core functionalities which proved very difficult to implement.

  • Database connection
  • Options
  • SQL Execution
  • SQL statement parser
  • support and implement different database vendors

Database Connection

There is only one open connection through the app lifecycle.

once a connection to the database opens I pass the reference of the connection to the child components and to whoever needs it. The app never opens two parallel connections.

This is also done for speed and simplicity(both me and the end-user). Once the initial connection handshake is done the connection remains open and is ready to be consumed.

The only way to open many connections is to open many instances of the application.

Options

In options, there is a generic tab option that applies always and a tab per database option.

For example, in the SQLite tab, I have a pragma editor.

SQL Executor

When a selected SQL text from the editor is executed a series of actions take place before and after we see results data populated.

Let us take a simple example of a SQL query

select * from my_table

The first important step is that the execution of the SQL commands happens in a different thread from the main-UI thread. We don’t want to block/hang the UI, some SQL might take a long time to complete and the UI must be unblocked and available to the user(to cancel or abandon or whatever he can do).

The Editor has the query executor and the query executor has the executor thread. The QueryExecutor also handles the executor thread, he is like a dispatcher between Editor and executor thread.

The second most important step is the lazy/fetch loading of data to the results datatable.

For speed and efficiency, I only load chunks of data when needed to results datatable. All the major tools are doing the same thing in different ways.

Let’s assume the table my_table have 1.000.000 rows. It would take a long time to query all the results and render/load it to datatable. 

By default, the first 100 rows are fetched and shown to the user. While the user scrolls to the bottom of the results datatable the next 100 rows are fetched, and so on.

How Fetching Is Implemented

There are two ways to implement fetch.

First and most efficient but hard to implement is to modify the SQL statement and add a limit offset.

The other way is to move the reader-cursor to the position you want to start reading the data.

The second way is implemented in the app since it was a lot easier to do, straightforward, and proved quite decent.

here is a pseudo-code of what happens when datatable reaches the bottom.C#

while(++offset != offsetSize && dbReader.Read());
while(++limit != limitSize && dbReader.Read())
  //do stuff, get db row data here

SQL Statement Parser

Have you ever wondered what happens when you run the selected SQL block in your ide?

A series of steps take place before execution. One of them is to take the SQL block and separate it into SQL statements. After the separation, the execution of the statements follows one by one.

This functionality is supported by almost all SQL development tools, big or small and it’s one of the most difficult tasks I’m facing, to implement for other databases.

In the example picture below, we see the execution of a SQL block and the history result table for every command.

The whole process is what is known as ‘SQL statement parser’ and I had to build my own SQL statement parser.

The most difficult part about SQL blocks is that they could contain triggers, comments, multiline comments, procedures, pure pl/SQL blocks.

Let’s take a look at a more difficult SQL block to parse below.SQL

select 1 from dual;
 select 2 from dual;
 DECLARE
    CURSOR c1 is
       SELECT ename, empno, sal FROM emp
          ORDER BY sal DESC;   -- start with highest paid employee
    my_ename VARCHAR2(10);
    my_empno NUMBER(4);
    my_sal   NUMBER(7,2);
 BEGIN
    OPEN c1;
    FOR i IN 1..5 LOOP
       FETCH c1 INTO my_ename, my_empno, my_sal;
       EXIT WHEN c1%NOTFOUND;  /* in case the number requested /                               / is more than the total       /                               / number of employees          */
       INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
       COMMIT;
    END LOOP;
    CLOSE c1;
 END;

As we can see above the parser must separate the block into the following three statements.

  1. select 1 from dual;
  2. select 2 from dual;
  3. all the other from DECLARE(line 3) to END(line 21);

The above PL/SQL is from Oracle which I don’t support yet due to parser implementation difficulties. But I support MySQL. In MySql, the above PL/SQL would exist as a procedure similar to the following.MySQL

select 1 from dual;
 select 2 from dual;
 DELIMITER $ -- change the delimiter ; to $
 CREATE PROCEDURE my_procedure_name
    DECLARE
    CURSOR c1 is
       SELECT ename, empno, sal FROM emp
          ORDER BY sal DESC;   -- start with highest paid employee
    my_ename VARCHAR2(10);
    my_empno NUMBER(4);
    my_sal   NUMBER(7,2);
 BEGIN
    OPEN c1;
    FOR i IN 1..5 LOOP
       FETCH c1 INTO my_ename, my_empno, my_sal;
       EXIT WHEN c1%NOTFOUND;  /* in case the number requested /                               / is more than the total       /                               / number of employees          */
       INSERT INTO temp VALUES (my_sal, my_empno, my_ename);
       COMMIT;
    END LOOP;
    CLOSE c1;
 END$ -- use the delimiter $
 DELIMITER ; -- change the delimiter back to ;
 select 1;

This is how the MySQL parser works.

The separation of the first two statements is done with a delimiter semicolon.

  1. select 1 from dual;
  2. select 2 from dual;

Then after a word DELIMITER is found it changes the delimiter to the user-defined word/char, in our case the new delimiter is $. Near the end, I use the delimiter $ to end the procedure ‘END$'(line 23, the third statement is from line 3 to 23). Next, the DELIMITER word is found again and the new delimiter changes to ‘;’ parser continues with a delimiter semicolon character.

Support and Implement Many Database Vendors

The factory method pattern proved very useful for making this tool work and support different databases.

By using factories and interfaces I don’t rely on the implementation but on abstractions.

In .NET all database vendors have created their own implementation of System.Data.Common.DbConnection which as Microsoft states

Defines the core behavior of database connections and provides a base class for database-specific connections.

This also applies to System.Data.Common.DbCommand and System.Data.Common.DbDataAdapter.

This helped me a lot, with a simple factory method I have support for all DBS and I’m not relying on the implementation class.C#

System.Data.Common.DbConnection createConnection(dbType) {
     switch(dbType)
       case ORACLE 
         return new OracleConnection();
       case MYSQL 
         return new MySqlConnection();
       case POSTGRES 
         return PostGresConnection();  
       …other dbs

For other  SQL queries that the apps needs for database treeviews, dropdowns, and the whole app, I have a SQL interface, for example.C#

interface SqlCommandsInterface {
     string getDatabases();
     List getTables();
     List getTableFields();
     ..other methods
 }

And in each database vendor class, I’m implementating the SQL method functionality. For example in MySQL and PostgreSQL.C#

class MySqlCommands implements SqlCommandsInterface{
    ...methods implementations
}
class PostGreSqlCommands implements SqlCommandsInterface{
    ...methods implementations
}

This way by combining the factory method I can focus only on method implementation, rely on abstraction, and not have to worry about refactoring.

Dependencies

Some of the most important dependencies/libraries.

SQL statement parser is a standalone .NET core library can be found here and on NUGET.

For the editor, SQL code highlighting, and IntelliSense a special thanks to Pavel Torgashov and his open-source library FastColoredTextBox which I integrated successfully and there is a lot of future abilities to implement. I don’t know what I have done without it.

Testing

Most of the unit tests are in sql_statement_parser library, they are parameterized tests and are very helpful when modifying or creating a new parser.

Development Method

GitHub

All the repos are public on GitHub organization LightHouse

Kanban – Trello 

For task tracker and progress I’m using Trello and is public  

The methodology is KanBan since I’m coding whenever I want and I don’t want to have any pressure sprints or whatever.

The Trello kanban separation is done in 7 main cards.

  1. New Requests/for approval/research/notesEither I’m gonna put some note there for others to read as a guideline or a feature that might do in the future. Usually, it’s just notes, and features I probably never do.
  2. Backlog pool  All the tasks that I’m going to do at some point till the app is 100% completed(except new/unknown features). I tried to analyze everything that needs to be done for the app to be considered complete. Usually, there is always something missing or forgotten.
  3. Work in progress (On What task I’m currently working)
  4. Testing
  5. Bugs/Issues
  6. Done
  7. Closed/Rejected
    Things I never gonna do, just have it here for historical reasons

GitHub Pages

The frontside webpage is part of the lightbox repo in the GitHub pages branch

I made a simple responsive website presenting the app on Github pages. I used Bootstrap and nunjucks which proved very useful. As for implementation I used nunjucks for template engine, created my own sass style guide, @mixin breakpoints, and used the bootstrap grid system.

Problems

Parsers

One of the biggest problems I’m still having and spending most of my time on is to create SQL statement parsers for other databases. I have created parsers for MySQL, MariaDB(using the same parser), and PostgreSQL.

Next, I want to create a parser for SQLite but I’m having a problem with ‘create trigger’ statement. It proved very difficult for me. After SQLite, oracle is next which I have no idea how to do it.

The Faraway Future

SQL Syntax Analyzer (ANTLR)

Maybe use ANTLR not just for statement parsing but for the whole SQL syntax tree analyzer. If done correctly I could provide SQL refactoring, analysis, better autocorrect, and other features.

DevOps

Take advantage of GitHub CI and run tests on every commit.

WEB

Create a web-based app from scratch. Angular or react in front, spring in back. dbeaver did it with cloudBeaver and it’s not even one year old yet. It’s very difficult and it requires R&D to have the same performance and responsiveness on the web since you dealing with DOM.

DEMO

I prepared a 64bit demo release. Requires .NET 4.6 or above, which can be downloaded HERE. Unzip (or not) and run lightbox.exe (note! only MySQL and SQLite are supported for this demo).

I wrote this article first here.

Software Engineer