4. SQL Database Tools

Having an SQL database is well and good; unfortunately, having "SQL" merely implies the presence of a powerful database engine, and the situation you find may be comparable to merely buying an automobile engine, where in order to go out driving, you need to add in other components such as a body, steering wheel, wheels and tires, and the likes.

In the case of a database, the additional components that may need to be added to the database "engine" include:

• Report generator

• Query tool

• Interactive Form/Transaction-oriented front end

  There are a host of Web-to-DBMS tools available for Linux, but few tools in the style of Oracle SQL*Forms or Sybase PowerBuilder.

• Non-interactive Batch/Transaction processor

• For server-oriented DBMSes, some set of administration tools to support the DBA

• MDB Tools

  MDB Tools is a planned set of libraries and utilities to facilitate exporting data from MS Access databases (mdb files) into a multiuser database such as Oracle Sybase , DB2, Informix , MySQL, PostgreSQL , or similar.

• Data dictionary management tool

  These tools are often not tightly integrated with the RDBMS system

  • ERWIN Data Modeler

    It does not run on Linux, and so does not, strictly speaking, belong here, but is a popular (expensive!) tool that many other data modelling tools emulate.

  • DIA.

  • Dert.

  • Druid : The Database Manager

    A toolset for interactively constructing database schemas along with documentation. Implemented in Java, and compatible with most SQL DBMSes at least insofar as they conform with the SQL-92 standard.

  • SchemaSpy

    A "Java"-based tool that uses 21 to help construct a series of web pages analyzing a DBMS schema including diagrams of the interrelations between tables. By using JDBC, it can analyze schemas for numerous DBMSes including free ones such as "PostgreSQL" as well as proprietary ones such as 1 and 26.

SQL does not specify standards for these sorts of things, and database system vendors all get ample opportunity to "lock you in" to their tool sets by encouraging you to use their proprietary tools to do these things.

The realm of "4GL" languages unfortunately represents a sizable body of what may be characterized as proprietary languages to tie you either to a language vendor or to a database vendor.

The SQL/CLI (aka ODBC) SQL Call Level Interface provides a commonly-used interface that can allow the construction of "standardized" tools that are capable of accessing data from any sort of database system. Unfortunately, the main vendor promoting ODBC has been MSFT, and they have used every opportunity to try to promote their database products in their ODBC tool implementations.

You nonetheless are likely to need some sorts of tools to generate reports, queries, enter data, and do offline/background processing.

Many middleware interfaces have been proposed to resolve this; none are entirely satisfactory, as they still represent fairly proprietary systems that lock you into a "marriage" with their ways of doing things.

The increasing growth of web-based applications has had the result that some middleware interfaces have been constructed to interface directly with web server software such as Apache.

You thus might either embed SQL queries into web page source code to be interpreted by an Apache extension, or generate database updates from the results of an HTML form, via a CGI gateway, perhaps written in Perl or Python. Middleware tools include a lot of tools to aid in this sort of approach.

Many database systems have had interfaces created for scripting languages such as Perl, Python, and TCL to allow easy "scripting" of applications.

I have also written a document on Linux Application Frameworks that suggests ideas about how one might build up an architecture to deploy applications.