Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Current Events
Join our developers event to win one of the valuable prizes!
posted 2 years ago
viewed 16571 times
Share this article

CUBRID Web Manager - the next generation SQL client with monitoring features

We are very happy to announce the general availability of the next generation Web-based SQL client with powerful system and database monitoring features - CUBRID Web Manager. It is an open source database administration and monitoring tool designed specifically to provide the Web interface for CUBRID, an open source RDBMS highly optimized for Web applications.

In this article I will explain how this project has started, what it looks like, its major features, what open source software have been used to develop CWM, what difficulties we have encountered during and after the development process, and will also mention other internal details.

Background

For the last year or two we have been receiving many requests from users and hosting companies to bring our CUBRID Manager, a desktop GUI-based database administration tool, to the Web. So far it was not possible to access CUBRID Servers from the Web.

This year on March, 2012, we started researching on what such a Web tool should look like, on how it should function and what features it should provide, and finally how we should implement all these.

Goal

We have started from analyzing the existing open source solutions such as phpMyAdmin for MySQL. We have found out that all the features it provides come down to executing a SQL statement either by the application itself or by a user. However we have come up with a list of value-added functionalities that we want to provide to our users but which cannot be accomplished by simply querying the database server. Except for being able to execute valid SQL statements through intuitive UI, there are two key features natively supported in CUBRID that we wanted to bring to the Web. They are:

  • Monitoring host system and database resources such as CPU, memory as well as disk space in real time.
  • Providing an information about slow queries being executed, again in real time.

Besides these functional differences we wanted to make the new CUBRID Web Manager to be administration- and configuration-free as much as possible. To explain what I mean by this, let me give you this example. For instance, to install phpMyAdmin on your server you have to install prerequisite software. You need a Web server running like Apache or nginx, and a PHP engine either as a Web server module or via other libraries like FastCGI. Also you need to make sure you use PHP version 5.2.0 and higher. Briefly, there are many variables users have to take care of before using phpMyAdmin.

In CUBRID Web Manager we wanted to remove all this hassle. So we decided to provide a solution that is very closely integrated with the CUBRID Server and requires no configuration to get started, although configurable if necessary. Just install CUBRID Database Server, and everything is already preconfigured for you. This is what we wanted to achieve.

We have researched a lot about what Internet browsers we should support. We thought about dropping IE6&7 support and provide only modern browser support. But we have found out that IE6 still occupies 21% of market share in China where we have many users. This number is also high in other Southeast Asian countries. So, despite the effort it would require, we have decided CWM will support everything starting from IE6 and Firefox 3.5. The modern browsers such as Chrome 10+, Safari 4+, and Opera 11+ will also be supported.

Thus, we started to design CWM that will work for everyone with a desktop browser. Here we remembered about our DBAs and developers who want or, actually, need to access their database servers on their mobile phones. This is especially important and useful in urgent situations.

Team

For the last 5 months it was our mission to create a powerful Web-based database administration tool with unique monitoring features which can run on almost any Web browser on PC and mobile phones. To accomplish this mission we have brought together a team of five software engineers majoring in C/C++/Java, and a QA expert from our NHN China branch. They are:

  1. Kevin Qian - CWM lead developer and engineer who majors in Java/J2EE.
  2. Martin Lee - CUBRID Manager Server (CMS) lead developer, engineer. Major in C/C++.
  3. Steve Xu: CWM developer, engineer. Major in C/C++.
  4. Santiago Wang: CWM developer, engineer. Major in Java/J2EE.
  5. Frank Wu: CWM/CMS tester, QA engineer.

Thanks to this team of dedicated developers today we have a solid and really powerful Web-based database administration tool with lots of great features.

Free MacBook Air and Amazon Kindle Fire during our CWM Bug Bash event

Last month in August, 2012, we launched a 30-day long CWM Bug Bash developers event dedicated to bringing CUBRID Web Manager out of beta status. In the event we asked users to find and report the remaining bugs in our new tool to make it stable before we officially announce the general availability. Participants could earn point by requesting new features and reporting various bugs. Each reported issue was rewarded depending on its type: new feature (0.5 points)trivial issue (0.5p)minor issue (1p)major issue (2p)critical issue (3p), and blocker issue (4p). Top 4 users would receive the latest model of 13'' MacBook Air and Amazon Kindle Fire tablet computers. Others would receive $50 worth Amazon or iTunes gift cards which they can use to purchase apps, books, or upgrade their OS X from Lion to Mountain Lion.

As a result of this CWM Bug Bash event, 148 issues have been reported by 8 participants. Among them 37 issues have been rejected as they were unreproducible, invalid, or duplicates of already reported issues. Thus, our Dev team has accepted 111 issues. So far we have already fixed 63 issues thanks to our great beta testers.

The stable version of CWM

System architecture

cwm_architecture.png

We have designed CUBRID Web Manager as a server-side plugin for CUBRID Database Server. It consists of two components (this does not include the components of the original CUBRID Server):

  • Server side
  • Client site

Server side

The server side of CWM is actually developed as part of CUBRID Manager Server, which is a part of CUBRID itself. This means the client side is totally isolated from server processes. This allows us to update the server side while making no changes to the client side. CWM is simply a client for CM Server.

  1. The server side includes a set of APIs (CMAPI) to communicate with CUBRID Manager Server. CWM uses these APIs to perform database administration and monitoring.
  2. CCI API, the C driver for CUBRID, is used on the server side by CWM to execute all SQL statements.
  3. The last and major component on the server side of CWM is the nginx Web server which:
    1. listens to a particular port which is set in cm_httpd.conf configuration file;
    2. receives HTTP requests from the CWM client side (i.e. the browser) to its RESTful API;
    3. relays the request to CUBRID Manager Server through either CM Server or CCI APIs;
    4. receives the response from CM Server in the form of a JSON object;
    5. finally sends this JSON object back to the browser.

I will write more about this CUBRID HTTP API in a separate blog.

Client side

CWM is implemented in pure JavaScript which populates the DOM with dynamically generated DOM objects depending on user commands. To accelerate the development process of the client side, we have decided to use a JavaScript framework. Major requirements to such a framework was to:

  • have a rich set of predefined UI components including advanced charting and graphing tools necessary to display the system monitoring status;
  • be able to reuse these UI components;
  • provide detailed documentation for each UI component and their usage;
  • MVC
  • provide support for legacy browsers (IE6+).

This is what we wanted. We wanted to develop fast and produce a stable product and avoid spending time on tweaking the UI. Eventually, among various JavaScript frameworks we have chosen Ext JS by Sencha.

Thus, the entire client-side of CWM is implemented in JavaScript in Ext JS. When a user performs a particular action (eg. clicks on a table, queries a table, drops a database, etc.), the HTTP request is sent to a RESTful API of our httpd server which is listening on a secure 8282 port by default. For every response, the client receives a JSON object with retrieved data or error messages.

Briefly speaking, CWM is simply a Web application which communicates with CUBRID Server via CUBRID Manager's HTTP Interface.

Download and install

The current CUBRID Web Manager version 8.4.1 build 0004 is now stable and can be used safely with any CUBRID v8.4.0+. You can download CWM from http://www.cubrid.org/wiki_tools/entry/cubrid-web-manager-installation-instructions. There you will also see the installation instructions.

Starting from CUBRID 8.4.2 (the upcoming release scheduled for the end of September, 2012), CWM will be integrated into the main server binary, so users will no longer need to install CWM separately.

Until then, when you install CWM, you will find the default cm_httpd.conf configuration file under the /conf directory. This configuration file follows the nginx configuration specification. If you do not change anything, the default configuration will instruct the built-in nginx Web server to listed to secure 8282 SSL port on the same machine. Therefore, to access CWM in the browser, navigate to http://localhost:8282, where localhost can be any remote IP address of your CUBRID host.

Host login

The first thing you will see when you open http://localhost:8282 is the Host Login form. CUBRID Web Manager provides the same authentication service which is available in CUBRID Manager client. In order to use CWM, users have to login to CUBRID Manager Server.

Note that host authentication is different from database user authentication. As an administrator of a CUBRID Server you can login to a host server and administer multiple databases. There can be multiple DBAs who can create their own databases which you may or may not have an access to. In order to access the databases you have access to, you need to login to them after you are logged in as a host server administrator.

cwm_login_host.png

When you login to CWM or CM for the first time ever, the default username and password are admin and admin. Right after the first successful login, you will be prompted to change your password.

System and database monitoring

The first thing you may want to do is to check the health of your database servers. CUBRID Web Manager provides an awesome, real-time monitoring dashboard implemented in a form of a tachometer of sports cars :). Really cool!

cwm_monitors.png

On the dashboard you will see three graphs for CPU usage, Memory usage, and Disk Space usage.

CPU usage

cwm_cpu_usage.png

If you hover over CPU graph, you will see a popover which displays the percentage of CPU used by all system processes, and by CUBRID Database Server. This data gets refreshed every second, so you can see how the graph changes in real time.

The green piece of this half-pie indicates on the system CPU usage, while the red color shows how much of this system usage is actually used by CUBRID Server to manage all databases.

Memory usage

cwm_memory_usage.png

Memory usage also displays how much RAM is used by the system, and how much of it is used by CUBRID, in addition to how much memory is available in total.

Remember that in CUBRID the physical memory is used only by running databases. In other words, if some of your databases have not been started, they do not use the memory. For more info see Important Facts to Know about CUBRID.

Disk space usage

cwm_disk_space_usage.png

Disk space usage graphs displays how much space is available on the server in total, how much of it has already been used, and how much of this usage accounts for CUBRID databases.

At this moment this graph displays the total disk space available to the system, including all available partitions, not just the one where CUBRID Server is installed.

In the next version of CUBRID Web Manager, we will add one more graphs which will display the information about Slow Queries which I have explained above. So stay tuned! Next version will be even cooler!

Broker management

cwm_brokers.png

Just like in CUBRID Manager, in the Brokers tab you can start and stop Brokers, the middleware of CUBRID. If you enable Auto Refresh feature, the information about Brokers will change in real time. You can see various information such as the port a particular broker is listening to, the number of application servers running, the number of jobs queued, the number of transactions and queries per second being processed by the broker, or the number of requests.

Configuration Variables

In the second "Variables" tab, you can see various configuration values used by CUBRID Server, Brokers, and CUBRID Manager. Right now all these values are static.

Databases

cwm_databases.png

When you click on a "Databases" tab, you can see a list of available databases on the current host. Here you can perform various actions on each database: check user privileges, start the database, stop it, or drop it. The same list of database is displayed on the left panel with an icon representing the current status of the database. For example, on the above screenshot you can notice that the demodb database has been started, while the hibernate database is not running.

Tables

cwm_tables.png

When you click on any database, a list of existing tables will be displayed on the left panel. Various operations can be performed on each table on the main panel. You can hover over any action icon to see a tooltip.

Executing SQL statements

cwm_sql.png

In the SQL tab you can execute any SQL, even multiple SQL. In case multiple SQL statements are executed at once, all will be executed, but CWM will display only the results of the latest SELECT query.

Auto backup plan

cwm_backup.png

One of the great features of CUBRID Web Manager is the availability of Auto backup plan which allows to schedule a backup of the entire database. To schedule an automatic backup, open the "Operations" tab and add a backup plan.

In the next version we will enhance the auto backup feature. CWM will allow to see a list of backup plans previous added as well as the detailed information about how many times they have been executed, for how long, and other status information.

Export data

cwm_export.png

Exporting in CUBRID Web Manager works seamlessly. You can export the entire database or a list of selected tables to SQL file, CSV, or in loaddb format which later can be loaded to another database using a cubrid loaddb utility.

Import data

cwm_import.png

Importing in CWM works similarly. In the current stable version, you can import data from SQL and loaddb files. Later we will add more formats.

Conclusion

There are many super great features we have implemented in CUBRID Web Manager. It is really nice, and it is open source! Give it a try and let us know you feedback in the comments below, on Twitter or Facebook. If you have a particular feature request, feel free to ask on our JIRA Issue Tracker, our forum, or ask questions at our Q&A.



comments powered by Disqus