History of programming languages: SQL life-long standardization

According to CodingDojo analysts, SQL is the most important and necessary query language among programming languages, however strange it may sound. The CodingDojo rating takes into account the statistics of the demand for programming languages ​​in the labor market.

After all, DBMS - MySQL, PostgreSQL and Microsoft SQL Server - are common everywhere: in large and small businesses, in hospitals, banks, universities, and so on. In principle, SQL is not limited to desktop devices only: SQLite DBMS has successfully taken its place on Android smartphones and Apple mobile devices. Accordingly, applications such as Skype and Dropbox constantly refer to it.
')
However, there were times when there were no smartphones, and this language already existed. The history of SQL is not years, but decades. They did not immediately believe in him.

System R and IBM

The first mentions of this language are dated 1974. SQL was created within the framework of the project of an experimental relational DBMS System R. The company IBM was engaged in this project.

Initially, the language was called SEQUEL (Structured English Query Language), but then the word “English” disappeared from this phrase, and the abbreviation acquired the form to which we had long been accustomed. On the one hand, SQL was focused on user-friendly, user-friendly formulation of queries to relational databases. On the other hand, almost from the very beginning, it was the so-called “full DB language”. This means that SQL included:

• means for determining and manipulating the database schema;
• means of determining integrity constraints and triggers;
• means of determining database representations;
• tools for determining physical layer structures that support efficient query execution;
• means of authorizing access to relationships and their fields;
• Means for determining transaction save points and performing commit and rollbacks of transactions.

True, it did not implement the means to synchronize access to database objects from side-by-side transactions. The fact is that the developers initially expected that the necessary synchronization is implicitly performed by the DBMS.

The language is implemented in the vast majority of DBMS - both in relational and non-relational. The purpose of the development was to create a simple non-procedural language that could be used by any user, even without programming skills.

Donald D. Chamberlin and Ray Boyce were developing query languages.

SEQUEL was not the only language of this kind. At the University of California, Berkeley, the non-commercial Ingres DBMS was developed, which was a relational DBMS, but used its own QUEL language, which, however, could not withstand the competition in the number of DBMS supporting it compared to SQL.

The most complex and complete version of SQL was implemented in System R. Slightly less functionality was in SQL / DS and DB2 from the same IBM. Only those parts that were insufficiently developed (for example, savepoints) or whose implementation caused too great technical difficulties (for example, integrity constraints and triggers) were removed from SQL System R.

Commercial success

Therefore, the path to the commercial implementation of SQL, which IBM has passed, is called a top-down movement.

Oracle, Informix and Sybase chose a different path - “from the bottom up”: in the first versions of these systems launched onto the market, a significantly limited subset of SQL System R was used. And then they began to gradually expand. However, in the first commercial implementation of SQL in Oracle DBMS, sampling operators did not allow the use of nested subqueries, and it was not possible to formulate queries with connections of several relations.


Distribution of market shares as of 2011

The growing interest of the market in the speedy transition to relational database management systems allowed the developers of the above companies to achieve commercial success. It happened, rather, despite the fact that the DBMS were then very far from perfect. Well, now Oracle, Informix, Sybase and Microsoft SQL Server support powerful enough dialects of SQL.

Standardization

The emergence of numerous SQL dialects and their proliferation should have led to compatibility problems and other inconsistencies.

However, the standardization of the SQL language began very soon - almost simultaneously with the appearance of its first commercial implementations. In 1982, the American National Standards Institute (ANSI) Database Committee was commissioned to develop a standard language specification for relational databases.



After rejecting a number of unsuccessful versions of the standard in 1986, experts came to a common denominator. And in 1987, the SQL / 86 standard was approved by the International Organization for Standardization (ISO).

The basis of the standard was not to take SQL System R. First, this version of the language was not sufficiently developed technically. Secondly, it would be too difficult to implement. Therefore, the SQL language dialect developed by IBM in the early 1980s was taken as the basis. In essence, this dialect was a subset of SQL System R.

SQL1 standard

By 1989, the SQL / 86 standard was somewhat extended, after which the following standard appeared, called ANSI / ISO SQL / 89.

SQL / 89 was the first internationally accepted standard for the SQL language. This language has a lot of flaws: many important concepts are not defined, much is left to the implementations. In this standard, such important sections as the manipulation of the database schema and dynamic SQL are completely absent.

Nevertheless, he played a role in the development of truly standardized relational database management systems. Moreover, with the advent of the SQL / 89 standard, it became possible to design, develop, and maintain information systems that are not too tied to a particular database vendor. In a sense, the emergence of SQL / 89 was the advancement of database technology towards open systems.

Perhaps the most important achievements of the SQL / 89 standard are the clear standardization of the syntax, the semantics of the data sampling operators and the manipulation of data, as well as the fixation of the means of restricting the integrity of the database.

The standard defines two levels of language and a separate means of maintaining integrity. Level 2 is a complete SQL database language that does not include integrity. Level 1 is a specified subset of level 2.

A means of maintaining integrity includes the ability to determine:

• Required restrictions on links between tables;
• check constraints on table rows;
• Default column values ​​when a row is added to a table.

The means of determining foreign keys make it easy to formulate the requirements of the so-called referential integrity of the database. This requirement, common in relational databases, could also be formulated on the basis of the general integrity constraint mechanism of SQL System R, but the formulation based on the concept of a foreign key is simpler and easier to understand.


Possibilities of the Join operation in different standards

SQL2 standard and its additions

Realizing the incompleteness of the SQL standard, specialists from various companies began work on another standard, called SQL2. This work also lasted for several years, many draft standards were issued, until finally in March 1992 the final draft standard was adopted (SQL / 92). This standard is much more complete than the SQL / 89 standard and covers almost all aspects necessary for the implementation of applications: database schema manipulation, transaction management (save points appeared) and sessions (a session is a sequence of transactions, within which temporary relationships are preserved), connections to the database dynamic SQL. Finally, the relational database directories have been standardized, which is generally not directly related to the language, but has a very strong effect on the implementation.

In 1995, the standard was supplemented with the Call Level Interface (SQL / CLI) specification. SQL / CLI is a set of procedure interface specifications whose calls allow you to execute dynamically defined SQL statements. In essence, SQL / CLI is an alternative to dynamic SQL.

The SQL / CLI standard served as the basis for creating the widely used ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) interfaces today.

In 1996, another component was added to the SQL / 92 standard - SQL / PSM (Persistent Stored Modules). The main goal of this specification is to standardize ways to define and use stored procedures, that is, specially designed programs, including SQL statements, which are stored in the database, can be called by applications and executed inside the database system.

Oracle is one of the most popular DBMSs. Moreover, it was there that compatibility with the SQL / 92 standard was first implemented.

And initially, Oracle V2, developed for VAX machines, became the first DBMS supporting the SQL language. It was back in 1979.

Oracle supports a number of different platforms, including Windows, Linux, Max OS X, and Sun Solaris.

Oracle's procedural SQL extension is called PL / SQL (Procedural Language / Structured Query Language) and is based on the syntax of Ada and Pascal. The third key language used in Oracle DBMS along with SQL and PL / SQL is Java.

PL / SQL supports program blocks, as well as a variety of data types for storing numbers, strings, and dates, calculation flow control statements (including conditional jumps and loops) and three types of containers (collections) - variable-length arrays, associative arrays, and nested tables .

SQL3 standard

It was originally planned to finish work on the new standard in 1995. Actually, the work on the new standard was partially completed only in 1999, and for this reason the standard was called SQL: 1999.

Each new version of the standard SQL language was significantly larger than previous versions. So, if the standard SQL / 89 was about 600 pages, then the volume of SQL / 92 was more than 300 pages.

The very first SQL3 projects covered about 1,500 pages.

However, the developers of SQL3 concluded that with such volumes of the standard, the likelihood of its adoption and subsequent successful support decreases markedly. Therefore, they decided to break the standard into relatively independent parts that could be developed and maintained separately.

In 1999, five parts of the SQL: 1999 standard were adopted.

The first part (SQL / Framework) is devoted to the description of the conceptual structure of the standard. This part provides a detailed summary of the following four parts and formulates requirements for implementations that claim to meet the standard.

The second part of SQL: 1999 (SQL / Foundation) forms the basis of the standard. A type system of the language is introduced, the rules for defining functional dependencies and possible keys are formulated, the syntax and semantics of the basic SQL statements are defined:

• operators of the definition and manipulation of the database schema;
• data manipulation operators;
• transaction management statements;
• database connection control operators, etc.



The third part is the SQL / CLI specification refined compared to SQL / 92. The fourth part specifies the SQL / PSM syntax and semantics of the stored procedure definition language. Finally, in the fifth part, SQL / Bindings, the rules for binding SQL for standard versions of programming languages ​​are defined.

The standard SQL: 1999 should have included several more parts. Among them are the specifications of the following tools:

• distributed transaction management (SQL / Transaction);
• support for temporal data properties (SQL / Temporal);
• external data management (SQL / MED);
• binding to object-oriented programming languages ​​(SQL / OLB);
• support for online analytical processing (SQL / OLAP).

SQL in the 21st century

At the end of 2003, a new version of the international standard SQL: 2003 was adopted and published. Many experts believed that the standard version following SQL: 1999 would only correct the inaccuracies of SQL: 1999. But in fact, SQL: 2003 specifies a number of new and important properties, with some modifications made later in 2008.

The most significant changes to the SQL language specified in Part 2 of the SQL: 2003 standard concern the following aspects:

• data types;
• subroutines called from SQL;
• advanced features of the operator CREATE TABLE;
• new schema object - sequence generator;
• new types of columns - identifying columns (identity column) and generated columns (generated column);
• new operator MERGE;

Undergone some changes in the overall organization of the standard. The SQL: 2003 standard consists of the following parts:

• 9075-1, SQL / Framework;
• 9075-2, SQL / Foundation;
• 9075-3, SQL / CLI;
• 9075-4, SQL / PSM;
• 9075-9, SQL / MED;
• 9075-10, SQL / OLB;
• 9075-11, SQL / Schemata;
• 9075-13, SQL / JRT;
• 9075-14, SQL / XML.

Parts 1–4 and 9–10 with the necessary changes remained the same as in SQL: 1999. Part 5 (SQL / Bindings) has ceased to exist; relevant specifications are included in Part 2.

The section of Part 2 of SQL: 1999, devoted to the information scheme, is highlighted in a separate part 11. Two new parts have appeared - 13 and 14.

Part 13 is fully referred to as “SQL Routines and Types Using the Java Programming Language” (“Using Subroutines and SQL Types in the Java Programming Language”). The appearance of such a part of the standard is justified by increased attention to the Java language from the leading manufacturers of SQL-oriented DBMS.

Finally, the last part of SQL: 2003 is devoted to the specifications of language tools that allow working with XML documents in a SQL environment.

Despite the efforts of developers, the standardization process is clearly not keeping pace with the changes.


Highlights in SQL history

Nevertheless, it can be said that the basic set of SQL statements, including database schema definition statements, data sampling and manipulation, data access authorization, support for embedding SQL in programming languages ​​and dynamic SQL statements, is well established in commercial implementations and more or less corresponds to the standard .

PS SQL cannot be fully attributed to traditional programming languages, it does not contain traditional operators that control program execution, type declaration operators and much more, it contains only a set of standard data access statements stored in a database. However, these "flaws" forgive him, given his place in the history of IT.