A Study of Open Source Database and Scripting Technologies

by Salih Kansoy

Database Technologies

The Backplane Open Source Database Server:

The Backplane Open Source Database is one of the emerging databases under the GNU software becoming popular; never the less it still has a long way to go before it can rival the likes of Oracle and MySQL.

However the Backplane Open Source Database has some good functionality and it is ranked very close to enterprise database systems such as Oracle. Not to mention that Backplane Open Source Database is a replicated, transactional, fault-tolerant relational database core implementing a subset of SQL and among its strengths is that the Backplane Open Source Database happens to be a large distributed database which runs on a large number of small servers rather than a small number of large servers.

Amongst its strengths is that the Backplane Open Source Database is open source, transactional, truly distributed database running on a large number of small servers which makes it easy and efficient in spreading the database over a WAN (Wide Area Network).

The only downside to this ground breaking database is that it currently only runs under the FreeBSD, however work is being done to port it onto Linux.

The GnuSQL Server:

The GNU SQL server is a multi-user portable DBMS (Data Base Management System); it is in conjunction with SQL89 standards and implements a limited range of SQL92 functionality.

The GnuSQL server's architecture is based on client server architecture for DBMS and is written in C, and the client server architecture is based on an RPC mechanism. Among its strength is that the GnuSQL server implements highly isolated transactions, and static & dynamic query compilation. One of the only downsides to the GnuSQL server is that the system is not so portable and both the server and client are currently only available for UNIX based systems, such as UNIX and Linux distributions.

The mSQL Server:

The mSQL also known as Mini SQL is a lightweight database engine which implements a subset of ANSI SQL. It also provides high-speed access to stored data with the minimum of memory requirement and is deployed on UNIX based platforms, such as UNIX and Linux distributions.

The Mini SQL server is also under the GUN license and is apart of the open-source technologies, the Mini SQL server was originally written by David J. Hughes at Bond University in Australia.

The Mini SQL comes in two distributions, one is the cut down version and is for smaller single processing purposes and the other is the multi process distribution, it is quite similar to MySQL, yet smaller and somewhat less powerful.

Both of these distributions come fully equipped with mSQL server, client interface, a C programming interface for the client software, and a verity of server tools.

Some of special features include interfaces to Mini SQL from Perl, Tel, REXX, Java, WWW interfaces and user contributed software along with a Windows port of the client and other tools.

Mini SQL incorporates a simple client server architecture this is in order to make the Mini SQL server a distributed system, as the server maintains the database, and the client sends commands and queries to the server in return the server sends the requested data.

However there are some major disadvantages and limitations of the Mini SQL server below is a list of some of these disadvantaged limitations:

1. In a WHERE clause, the AND and OR operators have the same priority.
2. Also the Mini SQL server is missing a NOT operator.
3. Missing aggregation functions, such as sum, count, min, max, avg, etc.
4. No implementation of embedded queries.
5. No implementation of views.
6. No implementation of the set operator.
7. No implementation of grouping.

The MySQL Server:

Without a doubt the MySQL server is the world's most popular open source Database Management System. MySQL is a relational management system, and the MySQL server is apart of the open source community and under the GNU license.

Some the MySQL server's strengths include its amazing speed of processing data, its reliability and on top of that its easy to use interface makes it one of the most widely used open source Databases Servers. The MySQL server is a client server system, with a multi threaded SQL server; it can support a verity of back-ends.

Below is a list of all major features of the MySQL server:

1. MySQL was written in C++
2. MySQL is platform independent and is portable.
3. MySQL is under the GNU license and is an open source technology.
4. APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby and Tel.
5. MySQL incorporates kernel threads and that entails that MySQL is a fully threaded system, also means that it can easily support multiple CPUs.
6. Super fast B-tree disk tables with index compression.
7. Super fast thread based memory allocation system.
8. Super fast joins using an optimized one sweep multi join.
9. Temporary tables made of in-memory hash tables.

However good an MySQL server may be it has disadvantages and limitations as it is a universal law of software systems that wherever a software system exist it always contains deficiencies, errors and limitations. However it was difficult to find any real limitations or deficiencies of MySQL, only after vast research some of MySQL servers deficiencies and limitations came to light, and the following will explain a few of these deficiencies.

"The first thing you hear from hard-core database gurus is that MySQL lacks transactions, rollbacks, and subselects. You'll really miss transactions if you're trying to write a banking application, accounting application, or trying to maintain some sort of counter that needs to increment linearly over time. Forget attempting any of those with released versions of MySQL (it should be noted that the unstable 3.23.x series of MySQL now includes transaction support). " (By: Tim Perdue - PHPBuilder.com)

1. No support for transactions special installation can fix this.
2. MySQL application programming interface does not support multiple active statements, this entails that only one cursor may be opened in 4GL, and makes it impossible to implement positioned updates and deletes with a WHERE CURRENT OF clause.
3. Sub-selects, stored procedures, views, CHAR and VARCHARs are limited to 255 characters.

The PostgreSQL Server:

The PostgreSQL is without a doubt one of the most over developed database engine within the open source community, among its superb features is a 16 year old development history and the second most popular open source database, and is rivaled only by the likes of MySQL. Not to mention the PostgreSQL server is under the GNU license and an open source database engine. Many large companies, organisations, universities and large to small businesses run PostgreSQL. This is due to its elegant and intelligent design for the multipurpose use by small, medium and large companies and corporations. It has proven to be the one of the best open source server, and functions well and is stable under heavy loads.

The PostgreSQL server is a cross platform server, and was designed for high volume environments, with legendary reliability and stability, incorporating a GUI (Graphics User Interface) database design and administration tools. Below is a small list of the PostgreSQL server's strengths:

? Acid Complaint.
? ANSI SQL compliant.
? Referential Integrity.
? Replication (databases can be replicated to other slave machines).
? Interfaces for ODBC, JDBC, C, C++, PHP, Perl, TCL, ECPG, Python and Ruby.
? Rules, Views, Triggers, Unicode, Sequences, Inheritance, Outer Joins, Sub-selects and an open API.
? Stored procedures.
? Support for SSL.
? Procedural language.
? Locking system even better than Row-Level locking.
? Partial and Functional indexes.
? Platform independent and portable.

This is by far one of the best and most advanced database engines under the GNU license.

Below is a list of some of the disadvantages of the PostgreSQL server:

? Slower than other servers.
? No support for ANSI SQL 92 or ANSI SQL99 standards.
? No support for hierarchical (only stores tables not other databases).
? Maximum row size limited to 8k.
? Limited to 32 connections.
? Lack of delete column command.
? No view and describe table.

Scripting Technologies

PHP (Personal Home Pages AKA (Hypertext Processor):

PHP is a server side scripting language much like most others such as the listed few below. It is widely used in websites applications to incorporate a database connection to the website. PHP was perhaps set up with one task in mind, that being to make database connectivity through the internet a feasible and simple task, and to a great extent they have done so.

Although PHP is mainly for database usage, it could server a wide range of internet applications, such as both basic and complex string manipulation and file handling and other complex and basic functions, the functionality in PHP easily rivals the likes of Perl and other server side scripting languages.

Another of the PHP's feature is that it under the GNU license and that it is also under the open source community.

PHP can be used to accomplish seemingly impossible tasks. These would often be websites where the storing, retrieving and modification of data is required from a database, such functionality may be needed by an online store such as Amazon, or could even be used by most websites that incorporate account based systems.

Advantages of using PHP:

? Excellent connectivity to all SQL servers.
? Support for MySQL, Oracle, Sybase, ODBC and others.
? Open Source and under the GNU license.
? Cross platform and portable.
? C, C++, Perl, JavaScript and Java like syntax and similarities.
? Deployed as a CGI or as module.
? Tight integration enhances execution speed.
? Fast development time.
? Extensibility.
? Ease of use & learning.
? Vast amount of available documentation and support.
? Rapid development of web applications, no compilation or linking required.
? Can produce powerful systems that would take up to 30 times more time to produce using C or C++.
? PHP applications are stable and browser independent.
? Partially Object Oriented programming language.
? Ptags/Ctags foe easy code navigation.
? Zend optimizer that can enhance performance.
? Much more powerful then the other technologies such as ASP (Active Server Pages - courtesy of Microsoft) and JSP (Java Server Pages - courtesy of Sun Microsystems).
? Extremely large user and developer base.

Disadvantages of using PHP:

? It is not a 100% Object Oriented scripting language.
? Limited to 300,000 lines of code.
? Will not have same performance as C or C++ as it is a scripting language and an interpreted language.

"With hardly any publicity, PHP has swept the Web" Authoritative Netcraft survey

PERL:

Perl is among the most popular scripting languages, it is most widely used on web servers and can accomplish many tasks, and these can be anything from basic display of HTML tags to advanced file read write operation and database connectivity.

Most if not all web server have support for Perl. It is most common to use Perl to implement web page counters, upload applications to cut out the middleware such as FTP (File Transfer Protocol), it is also widely used to implement guest books and similar applications.

The fundamental power of Perl is given to it because it is a server side scripting language and therefore it is permitted to write, read, delete and alter files on the server, this sort of functionality is fundamental in the development of guest books and page counters. Although it may seem simple to write, but in order to implement a guest book or a website counter the use of a data file needs to be incorporated and the data file must be read from written to and appended.

Below is a small list of advantages in using Perl script to implement complex functionality in websites:

? Perl has many built-in functions for character string manipulation.
? Efficient code, complex functionality with minimum of effort.
? Platform independent, and portable.
? Perl is free and is an open source technology.
? Also free for cooperate and profit organizations as well as individual and non profit organizations.
? Under the GNU license.
? Can be compiled into a C executable.
? Execution will be super fast after recompiling into C.
? One of the longest running scripting languages available.
? Very high level of functionality.

Disadvantages of Perl:

? Slow processing as it is an interpreted language.
? Additional libraries mean additional delays in processing.
? Small errors do not affect execution this may make it difficult to debug.
? Possibly harder to maintain code over time.
? Advanced Perl syntax can be cryptic and unreadable to the beginner.
? Several syntax for the same function can cause confusion.

TCL (Tool Command Language):

TCL is a multi purpose tool; it was designed with two things in mind, one being a language, and the other as a library. As already stated TCL is a simple scripting language and that will fulfil its first criteria and yet at the same time being a library that can be embedded into applications giving the user more control over the system and enabling the developer to produce more powerful systems.

TCL is a simple to use tool command language; its original purpose was intended to send commands to text editors, debuggers, illustrators, and shells. It contains a simple yet useful set of instructions and syntax and is also programmable. Users may find the inbuilt code limited in witch case they may wish to add to it and can write their own command procedures to provide more powerful command to their systems then the inbuilt commands. This will allow the user to expand the system where he finds the TCL limited.

The TCL can also be used as a library in which case the TCL can be embedded into applications, the TCL library consists of a parser for the TCL language, routines to implement the TCL built-in commands and procedures that allow each application to extend TCL built-in commands specific to that application.

The application program with the help of the embedded TCL libraries generates TCL command; these commands then are passed to the TCL parser for execution.

Applications can gain some good advantages by incorporating TCL into its system, these consist of:

? Standard syntax.
? TCL is universal with all other TCL systems.
? TCL provides programmability.
? Includes a general programming interface.
? Ease of communication between TCL implemented applications using TCL commands.
? Support for multiple platforms.

ECPG (Embedded SQL C preprocessor):

ECPG is a simple SQL C preprocessor that acts in very much the same way as most other script languages. It was originally written by Linus Tolke till version 0.2, and then Michael Meskes took over and is the current author and the maintainer, along with Thomas Meskes who is the ECPG Manual page maintainer and author.

The ECPG language is one of the least popular and least used language in its category, originally written to serve one purpose, the SQL C preprocessor for the PostgreSQL server. But has been put to use by others than the original author, and amazingly is still maintained and new versions are published.

There is a lack of ECPG support and documentation; in fact it is rather rare to find any systems using ECPG, thus proving its unpopularity and the fact that this language was never meant as a production language or a language for large or cooperate systems. However it is a good method of programming as it embeds the SQL into your C program and this will entail that you will not have to use any special tools to handle your SQL for you. It is also supported by most RDBMS, and that the ECPG tried to comply by the ANSI standards as much as possible.

PYTHON:

Python is an interpreted, interactive, object-oriented programming language and is considered as a server side scripting language that can be used to write server applications, it is often compared with the likes of Tcl, Perl, PHP, Schema and Java.

Python is amongst one of the most widely used Script Languages, Python is employed by the worlds most famous and most widely used Search Engines Google. Google are proud to say that their Search Engine is written using Python, and also state that it caters for all their current and growing needs.

Python is a combination of intelligent syntax and exceptional power, which makes it one of the widest used languages for search engines. Among its advantages is that Python is totally free, although it is not under the GNU license, nor is it a in the open source community however it is free and the reason it is not under the GNU license would be that it free for commercial use and even for resale.

Below is a list of some remarkable abilities of Python.

? Exceptions.
? Very high level dynamic data types.
? Dynamic typing.
? Interfaces to many system calls and libraries.
? Support for various windows systems and others (X11, Motig, Tk, Mac, MFC).
? Built-in modules can be written in C or C++.
? Can be used as an extension language, far applications that need a programmable interface.
? It is portable and can be deployed to a number of systems including UNIX, Windows, DOS, OS/2, Mac, Amiga and others.
? It is totally free although copyrighted, even for commercial use and for resale.
? Extremely clean design.
? 100% pure Object Oriented scripting language.
? It inherits features from a wide range of languages (ABC, Icon, Modula-2/3 and C).
? Object Oriented.

As it can be seen above Python is among one of the most featured languages in its category. But it must be said that Python has some disadvantages.

Below are a limited and a small list of disadvantages of Python:

? Python is an interpreted language thus reducing performance.
? Interfacing C code into Python is a trivial task.
? Limited documentation.
? Not very renowned.

Conclusion:

My preferences for the ease and also for the widest supported technologies would have to be MySQL from the database technologies and PHP from the scripting technologies. Although the comparison of PostgreSQL and MySQL server is operated from a fine line of witch a tie breaker must be derived, and only because of more supporting texts both online and offline for the MySQL server I make my preferences over the , PostgreSQL server. Same can be said about PHP and PERL, but the only problem is that PERL has just as much if not more supporting texts, but on the point of ease of use and the amazing rapid and superb results of effortless PHP coding, I would hence say PHP would have the upper hand.

Written and compiled by Salih Kansoy, originally written as a part of his BSc Dissertation 2nd of April 2003.