LIBD:Themes:Database-Languages-and-API

Version du 16 décembre 2010 à 19:42

Modeling and Meta-modeling

Database Languages and API

<Back to Themes & Resources page>

• Description

Interacting with databases requires languages and protocols, be they for database building, exploitation or administration. The level of language depends on its goal and on the skill of its target user. We present and discuss some of the languages and API’s we have developed as well as some contributions to language processing. They can be classified into five categories:

1. abstract language for information system design: ADL
2. DBMS languages: SPHINX DDL and DML, NUL, IDML, T-SQL, NDBS, SQL-Script
3. API: wrappers, T-ODBC, conceptual API
4. languages for CASE tools: Voyager 2, Grasyla
5. language processing: co-transformation, program analysis

• Data Access Description Language (ADL) (abstract language). The ADL language, borrowed from the IDML interface, has been used as an abstract data manipulation language to describe database algorithms. Formely a companion language of the GAM (notably to study performance of database programs [P76-02] [P77-01]), it has been extended recently to complement the GER in A. Cleves’s thesis [PHD09]. In book [B86], ADL was used to describe abstract database programs in a logical database design process. The latter book also describes ADL transformations following data structure transformations (what will be called later co-transformations). This framework allows programs to be described at the conceptual level, then to be automatically transformed into algorithms compliant with logical database schemas. ADL has also been used as the basis for the rule language of the decision support data model [P90-02] and [B94].

• SPHINX DDL and DML (DBMS languages). One of results of the project Large Administrative Databases (1971-1977) was a DBMS based on a Entity-relationship model. It included a data definition language and a data manipulation language for COBOL programs [P78-01] [P78-03]. A detailed specification of these languages can be found in the final report of the project ([R78-01] to [R79-05]).

• NUL (DBMS language). The Navigational User Language (NUL) was designed to allow end-users to query databases through an Entity-relationship view of data. A query was incrementally built by defining simple objects derived from the conceptual schema and from already defined objects. An interactive interpreter was developed on top of the SPHINX DBMS. NUL is described in paper [P76-01] and in system manuals [R78-01] to [R78-05].

• IDML (DBMS languages). Based on the experience of system SPHINX, we experimented wrapper technology to interface application programs with CODASYL databases through a high-level neutral data manipulation language based on a binary data model (1978-1981). This language will be reused later under the name ADL. A prototype wrapper generator was developed [P78-02] [P81-01].

• NDBS (DBMS languages). NDBS (Network Database System, 1986-1996) is an educational database management environment allowing Turbo-Pascal programs to manage and use complex data in an efficient, though very intuitive, way. NDBS comprised a runtime library (database handler), a DDL compiler, a 4GL Query Language, a data dictionary, a report generator, a SQL/NDBS converter, an import/export tool (the language of which has been adopted as the external format for DB-MAIN metadata [the *.lun format]), etc. The last version (under the name Pyramid) was developed by D. Rossi, then Master student in the University of Namur. Two original aspects: (1) the data model was a variant of the ER model, (2) the physical engine was a fairly strict implementation of the principles developed in the course of Database Technology I gave in the 80's. [NDBS-86]
• Temporal language T-SQL (DBMS language). One of the result of project TimeStamp is a variant of SQL extended with bi-temporal dimensions. This variant was named T-SQL (a subset of TSQL2). [P01-02]

• Scripting Language for SQL (DBMS language). MS Access provides two ways to execute SQL statements: as Access queries (simple but single queries only) and embedded in VB programs (powerful but complex). We have designed a scripting language (SQL-Script) through which one can write simple SQL programs. A SQL-Script program is made up of a sequence of SQL statements, but also of variables, macros, loops, alternatives and interaction with users. An interpreter for SQL-Script has been developed in MS Access. [SQL-Script]

• Wrappers (API). A wrapper is a software component attached to a data source (such as a file and a database) and that provides its users (typically application programs) with a data model that is different from that of the data source. Practically, a wrapper is notably defined by its API. The wrapper technology and architecture have been extensively studied, experimented and used since 1981 in various contexts.

1. Database access code generation [B86].
2. Legacy database interoperability [P99-01] [P01-03] [P04-08] [P05-03] [P06-08].
3. Information system evolution and migration [P03-03] [P04-04] [P06-04] [P08-05] [PHD09].
4. High-level database programming through conceptual object classes to support fast system evolution [P10-05].

• Temporal API T-ODBC (API). The T-SQL language interacts with the database through the temporal API T-ODBC which is an extension of ODBC [P01-02]. The temporal database is an active DB implemented in Oracle 9.

• Conceptual API for system evolution (API). The fast evolution of applications systems forces developers to adopt defensive programing techniques to facilitate system adaptation. We have developed a Java API that offers data manipulation at the conceptual level (according to a variant of the Entity-relationship model). The API is automatically generated by the DB-MAIN CASE tool according to rules that minimize the impact of changes on the database, on the data and on the application programs [P10-05].

• Languages for CASE tools. As any high-end CASE tool, the DB-MAIN environment allows users to develop new functions (checkers, code/report generators, reasoners, etc.) A first language, Voyager 2, was developed in 1995. This complete procedural language provides declarative statements to access the DB-MAIN repository (the database in which DB-MAIN stores the projects, schemas and other products it manages). Later on, ReveR (the spin-off of the LIBD in charge of the maintenance and evolution of the tool) included a JVM (Java virtual machine) into the tool to allow java plug-ins to be developed as well [DB-MAIN]. In parallel, a new research was conducted on generic CASE tool architectures. The proposal comprised three languages, namely a concept definition language, a procedural language and a graphical language (GRASYLA) [P99-03] [P99-06] [PHD00]. The research is ongoing through the development of the Metadone metaCASE tool.

• Co-transformation (language processing). Current studies on software intensive system evolution emphasize the concept of co-transformation. When two artefacts A and B interact (e.g., program B working on database A), modifying A may imply modifying B accordingly. If changes to A can be modeled by transformations, the adaptation of B can, under specific conditions, be carried out automatically. The coordinated modification of A and B is called co-transformation. The idea was first suggested in [B86] to automatically transform ADL algorithms defined on a conceptual schema into algorithms compliant with a logical model such as SQL, standard file structure or CODASYL. More recently, co-transformations were studied in the context of data intensive system evolution. The problem addressed is the following. We consider, as a case study, a legacy database that is to migrate to a modern platform. The best approach consists in reverse engineering the legacy database in order to get its conceptual schema, then to generate a new database according to the new data model. Then the legacy data are transformed and moved to the new database and, finally, the application programs are adapted to the new schema and the new API. The whole process can be described as a co-transformation of schema, data and programs, driven by schema transformation. In this process, program transformation is particularly challenging. The problem is described in [P04-03], [P06-04] and [P06-06], an application in [P08-05] and the full solution, including the use of ASF/SDF for program transformation, in [PHD09].

• Program understanding (language processing). Database reverse engineering requires the elicitation of implicit (i.e., not explicitly declared in the DDL code) data structures and constraints. This process relies on various techniques such as data analysis, schema analysis and program source code analysis. Code analysis is a complex matter but it provides essential information on the data structures. For instance, implicit foreign keys can be detected through the examination of the data validation sections that precede the insert statements in application programs. Identification of programming clichés, dependency graph and program slicing are some techniques that have been studied and implemented in DB-MAIN [P96-01] [P98-01] [P98-02] [PHD03]. Recently, we applied dynamic program analysis techniques to the detection of dynamic SQL statements, which are increasingly used in web applications (through JDBC for instance). [P08-02] [PHD09]

• Keywords

database languages, CASE programming, Voyager 2, DB-MAIN, SQL script, dynamic SQL, temporal API, wrapper, wrapper generator

• Resources

[P10-05] Anthony Cleve, Anne-France Brogneaux, Jean-Luc Hainaut. A Conceptual Approach to Database Applications Evolution, in Proceedings of the 29th International Conference on Conceptual Modeling (ER'2010), Lecture Notes in Computer Science No 6412, pages 132 - 145, Springer-Verlag, 2010. -- to appear. [description]

[PHD09] Anthony Cleve, Program Analysis and Transformation for Data-Intensive System Evolution, PhD Thesis, University of Namur, 2009 [full text]

[P08-05] Anthony Cleve, Jean Henrard, Didier Roland and Jean-Luc Hainaut. Wrapper-based System Evolution - Application to CODASYL to Relational Migration, in Proceedings of the 12th European Conference on Software Maintenance and Reengineering (CSMR’08), pages 13-22, IEEE Computer Society, 2008. [description]

[P08-02] Anthony Cleve, Jean-Luc Hainaut. Dynamic Analysis of SQL Statements for Data-Intensive Applications Reverse Engineering, Proceedings of the 15th Working Conference on Reverse Engineering, (WCRE'08), IEEE Computer Society, 2008.

[P06-08] Philippe Thiran, Jean-Luc Hainaut, Geert-Jan Houben and Djamal Benslimane. Wrapper-based evolution of legacy information systems, ACM Trans. Softw. Eng. Methodol., 15(4): pages 329-359, ACM Press, 2006. [description]

[P06-06] Anthony Cleve and Jean-Luc Hainaut. Co-transformations in Database Applications Evolution, in Generative and Transformational Techniques in Software Engineering, Lecture Notes in Computer Science, Volume 4143, pages 409-421, Springer-Verlag, 2006. [description]

[P06-04] Anthony Cleve. Automating Program Conversion in Database Reengineering - A Wrapper-based Approach, in Proceedings of the 10th European Conference on Software Maintenance and Reengineering (CSMR’06), 22-24 March 2006, Bari, Italy, pages 323-326, IEEE Computer Society, 2006. [description]

[P06-03] Anthony Cleve, Jean Henrard and Jean-Luc Hainaut. Data Reverse Engineering using System Dependency Graphs, in Proceedings of the 13th Working Conference on Reverse Engineering (WCRE’06), pages 157-166, IEEE Computer Society, 2006. [description] [full text]

[P05-03] Philippe Thiran, Hainaut Jean-Luc and Geert-Jan Houben. Database Wrappers Development: Towards Automatic Generation, in Proceedings of the 9th European Conference on Software Maintenance and Reengineering, (CSMR’05), pages 207-216, IEEE Computer Society, 2005. [description] [full text]

[P04-08] Philippe Thiran, Geert-Jan Houben, Hainaut Jean-Luc and Djamal Benslimane. Updating Legacy Databases through Wrappers: Data Consistency Management, in Proceedings of the 11th Working Conference on Reverse Engineering (WCRE’04), IEEE Computer Society, 2004. [description] [full text]

[P04-04] Jean Henrard, Anthony Cleve and Jean-Luc Hainaut. Inverse Wrappers for Legacy Information Systems Migration, in Proceedings of 1st International Workshop on Wrapper Techniques for Legacy Systems, (WCRE’04/WRAP’04), Computer Science Report, Volume 04-34, pages 30-43, Technische Universiteit Eindhoven, 2004. [description] [full text]

[P04-03] Anthony Cleve, Jean Henrard and Jean-Luc Hainaut. Co-transformations in Information System Reengineering, in Proceedings of the 2nd International Workshop on Metamodels, Schemas, and Grammars for Reverse Engineering, (WCRE’04/ATEM-04), Electronic Notes in Theoretical Computer Science, Volume 137, pages 5-15, Elsevier, 2005. [description] [full text]

[PHD03] Jean Henrard, Program comprehension in database reverse engineering, PhD Thesis, University of Namur, 2003[full text]

[P03-03] Jean-Marc Hick and Jean-Luc Hainaut. Strategy for database application evolution: the DB-MAIN approach, in Proceedings of the 22nd International Conference on Conceptual Modeling, (ER’03), Lecture Notes in Computer Science, Volume 2813, pages 291-306, Springer-Verlag, 2003. [description] [full text]

[P01-03] Philippe Thiran and Jean-Luc Hainaut. Wrapper Development for Legacy Data Reuse, in Proceedings of the 8th Working Conference on Reverse Engineering, (WCRE’01), pages 198-207, IEEE Computer Society, 2001. [description] [full text]

[P01-02] Virginie Detienne and Jean-Luc Hainaut. CASE Tool Support for Temporal Database Design, in Proceedings of the 20th International Conference on Conceptual modeling, (ER’01), Lecture Notes in Computer Science, Volume 2224, pages 208-224, Springer-Verlag, 2001. [description] [full text]

[PHD00] Vincent Englebert, A smart meta-CASE: towards an integrated solution, PhD Thesis, University of Namur, 2000 [full text]

[P99-06] Vincent Englebert and Jean-Luc Hainaut. DB-MAIN: A Next Generation Meta-CASE, Information Systems Journal, Special issue on meta-modelling and methodology engineering, 24(2): Pergamon, 1999. [description] [full text]

[P99-03] Vincent Englebert and Jean-Luc Hainaut. GRASYLA: Modelling CASE tool GUIs in Meta-CASEs, in Proceedings of the 3rd International Conference on Computer-Aided Design of User Interface (CADUI’99), pages 217-230, Kluwer, 1999. [description] [full text]

[P99-01] Philippe Thiran, Abdelmajid Chougrani, Jean-Marc Hick and Jean-Luc Hainaut. Generation of Conceptual Wrappers for Legacy Databases, in Proceedings of the 10th International Conference on Database and Expert Systems Applications, (DEXA’99), Lecture Notes in Computer Science, Volume 1677, pages 678-687, Springer-Verlag, 1999. [description] [full text]

[P98-02] Jean Henrard, Didier Roland, Vincent Englebert, Jean-Marc Hick and Jean-Luc Hainaut. Outils d’analyse de programmes pour la rétro-conception de bases de données, in Actes du Xème Congrès INFORSID, pages 237-250, 1998. [description] [full text]

[P98-01] Jean Henrard, Didier Roland, Vincent Englebert, Jean-Marc Hick and Jean-Luc Hainaut. Program Understanding in Databases Reverse Engineering, in Proceedings of the 9th International Conference on Database and Expert Systems Applications, (DEXA’99), Lecture Notes in Computer Science, Volume 1460, pages 70-79, Springer, 1998. [description] [full text]

[P96-01] Jean Henrard, Jean-Marc Hick, Didier Roland, Vincent Englebert and Jean-Luc Hainaut. Techniques d’analyse de programmes pour la rétro-ingénierie de bases de données, in Actes du XIV Congrès INFORSID, pages 215-232, Edit. AFCET, 1996. [description] [full text]

[NDBS-86] Jean-Luc Hainaut. NDBS - A simple database system for small computers. Reference manual. University of Namur, 1986-1996.

[B86] Jean-Luc Hainaut Conception assistée des applications informatiques - 2. Conception de la base de données, Masson, 1986 [description and full text]

[P81-01] Yves Delvaux and Jean-Luc Hainaut. Système portable de manipulation de bases de données, in Actes du congrès AFCET 1981, pages 385-395, Editions Hommes et Techniques, 1981. [description] [full text]

[P78-03] Baudouin Charlier and Jean-Luc Hainaut. Modèles, Langages et Systèmes pour la conception et l’exploitation de bases de données, in Actes du congrès AFCET 1978, pages 179-189, Editions Hommes et Techniques, 1978. [description] [full text]

[P78-02] Jean-Luc Hainaut. IDML : Système d'interaction avec des bases de données CODASYL, in Actes des communications de l'Ecole d'été 1978 de l'AFCET, pages 121-144, Publication FUNDP, 1978. [full text]

[P78-01] Baudouin Charlier. Quelques réflexions concernant les modèles et langages de bases de données, in Actes des communications de l'Ecole d'été 1978 de l'AFCET, pages 176-185, Publication FUNDP, 1978. [full text]

[R78-01] Jean-Luc Hainaut, Baudouin Le Charlier, et al., Système de conception et d'exploitation de bases de données - Volume 1 : Modèles et Langages. Rapport final du projet CIPS I2/15, Institut d'informatique, Université de Namur, 1978

[R78-02] Jean-Luc Hainaut, Baudouin Le Charlier, et al., Système de conception et d'exploitation de bases de données - Volume 2 : Manuel de référence des langages. Rapport final du projet CIPS I2/15, Institut d'informatique, Université de Namur, 1978

[R78-03] Jean-Luc Hainaut, Baudouin Le Charlier, et al., Système de conception et d'exploitation de bases de données - Volume 3 : Une implémentation du modèle d'accès. Rapport final du projet CIPS I2/15, Institut d'informatique, Université de Namur, 1978

[R78-04] Jean-Luc Hainaut, Baudouin Le Charlier, et al., Système de conception et d'exploitation de bases de données - Volume 4 : Le système SPHINX, Utilisation, fonctionnement et description interne. Rapport final du projet CIPS I2/15, Institut d'informatique, Université de Namur, 1978

[R78-05] Jean-Luc Hainaut, Baudouin Le Charlier, et al., Système de conception et d'exploitation de bases de données - Volume 5 : Exemples d'application. Rapport final du projet CIPS I2/15, Institut d'informatique, Université de Namur, 1978

[P76-01] Claude Deheneffe and Henri Hennebert. NUL: A Navigational User’s Language for a Network Structured Data Base, in Proceedings of the 1976 ACM SIGMOD International Conference on Management of Data, pages 135-142, ACM, 1976. [description] [full text]

[SQL-Script] SQL-script Interpreter. <SQL Script interpreter page>

[DB-MAIN] CASE environment DB-MAIN. <DB-MAIN page>

<Back to Themes & Resources page>