LIBD:Outils

Version du 17 avril 2017 à 12:46

<Retour à la page d'accueil / Back>

SQLfast

SQLfast is a software environment that allows non expert users to easily create, examine, modify, query and process relational databases through a user friendly graphical interface. The SQLfast environment also provides a simple but rich programming language intended primarily to casual users with no or little experience in database programming.

SQLfast is portable, self-contained, self-documented, ready to run and requires no installation. Launching it just requires double-clicking on SQLfast.exe, nothing more. The software, the manuals and the tutorials are available here.

Last tutorial updates

2017-04-17: Chapters 1 to 20 [full text]

2017-04-17: Chapter 20. Metadata [full text]

2017-04-07: Chapter 19. Recursive programming [full text]

2017-04-07: Dictionary of SQLfast commands and parameters [full text]

2017-04-01: Chapter 18. Importing and exporting data [full text].

2017-03-05: Chapter 48. Directory management [full text].

2016-12-05: Chapter 52. Path finders, rovers and Ariadne's thread [full text]

• Type: High-level database programming interface and software environment for casual and non expert users
• Description of the SQLfast language

SQLfast is a language allowing easy database processing and manipulation. The core of the SQLfast language is SQL, the universal language for database definition and manipulation. Any sequence of SQL statements is a valid SQLfast script, whatever its length. SQLfast also comprises statements that provide for interaction with script users, text generation, file manipulation, decision and iteration.

The main goal of SQLfast is to allow the rapid and easy development of small database applications by casual, non expert users. It does not compete with standard programming languages, such as C, Java or C# in which large and complex database applications are written. By automating most tedious and obscure parts of database programming and user interaction, it allows script writers to concentrate on the problem logic.

This document (in English) and this one (in French) compare the SQLfast language with standard programming languages for querying a database, interacting with the user and downloading a file. They show that a single SQLfast statement replaces from 80 to 100 statements written in Java or Python.

Despite its simplicity, the SQLfast language includes powerful features such as the full SQL language, variables, GUI, comprehensive control structures (procedures, recursive calls, if-else, for-endfor, while-endwhile, etc.), a versatile text generator, dynamic statement and script execution, external program interface, transactions, automatic metadata management, file management, web access, SMTP protocol, base64 and SHA256 conversion, debugging and much more.

• Description of the SQLfast software environment

The SQLfast environment does not require any installation. Once the SQLfast archive has been downloaded, the SQLfast folder is extracted and copied anywhere, from the desktop to a USB flash memory. SQLfast is selfcontained. There is no need to install a database manager nor GUI libraries, to compile modules to adapt them to the user equipment, to create accounts and to define access rights.

Due to its simplicity, its ease of use and its power, SQLfast is intended to audiences that have little time and/or knowledge to develop professional programs. In particular, it can be used by:

• non-expert users: SQLfast provides an easy way to write small data processing tools customized to their exact needs, without resorting to complex programming or using powerful but complex softwares.
• non-expert but motivated users: SQLfast is quite appropriate for the development of small and simple single-user applications for, e.g., library management, statistical analysis, simple data mining, data extraction and transformation (ETL), geographic information systems, photo album, text analysis, still and animated graphical display of data, and so on.
• expert users: SQLfast is a fine environment for fast database application prototyping.
• students: the SQLfast language basically is SQL but it is also an ideal support to practical work in database introductory and advanced courses. It allows students to play with SQL in a secure environment and also to develop in a few lines powerful scripts without the burden of learning and using standard programming languages. In addition, the graphical interface provides a user friendly way to approach database concepts without even using SQL: databases can be built, examined and their contents can be viewed, queried, modified through direct manipulation.
• teachers: SQLfast includes a powerful tutorial engine that allows instructors to develop and use didactic material. An SQLfast tutorial is a hypertext comprising formatted text, images, SQL and SQLfast code (that can be executed from within the tutorial), internal and external links. The standard distribution already includes several dozens of tutorials in three domains: SQLfast user manuals, SQL learning and database programming. New tutorials can be written and integrated in minutes through the SQLtuto language.

SQLfast runs on Windows, OS X and Linux machines (only the Windows version is available at present time). Its default database manager is SQLite 3 but versions for MySQL and MS Access are being developed.

Finally, an important feature of the SQLfast environment is its extendability: new functions and new resources can be developed and integrated quite easily.

• Technical detail: An SQLfast script (or program) is not limited in size: it can include just one statement but also several millions of statements. The environment is developed in Python 2.7, Tkinter and an integrated RDBMS. The standard version relies on the SQLite 3 DBMS. Its main design objectives are to hide the complexity of database programming (connections, cursors, transactions, metadata management, etc., of graphical interactions and of data exchange with external sources (e.g., files, web data an email) to provide very high level functions and to provide a secure user-friendly execution environment. The SQLfast language and environment comprise the following components:

• the full SQL language
• variables in which data coming from a database, external files, computations, GUI or web sites can be stored. Variables are typeless and have no size limit. A variable can contain data of any kind but also script fragments. The SQLfast macro processor can (recursively) replace variable references by their values.
• control structures (if-then-else, for-endfor, while-endwhile, calling scripts, libraries, external applications, stop, pause)
• reading/writing in external files, encoding/decoding into secure formats (hexadecimal, base64)
• large object management (text, video, sound, BLOB, geographic objects, charts, drawings)
• generation of complex formats (csv, xml, html, rtf, LateX, JSON, Key-value, SQL, tuples, etc.) These formats are defined by generic parameters.
• import processors for standard formats (e.g., dbf, csv, Access, ESRI shapefiles)
• a rich extensible GUI:

• elementary boxes: messages, data entry forms, radio buttons, check buttons, text, images, drawing, charts, etc.
• composite dialogue boxes: made of an arbitrary number of elementary boxes
• extensible through Python external libraries

• an integrated graphical engine to render SQLdraw scripts to produce still and animated drawing (charts, drawing, maps, animated simulation, etc.). SQLdraw scripts can be generated or manually produced through graphical SQLdraw Editor.
• extensibility mechanisms:

• technical SQLfast scripts (e.g., parameters setting),
• functional SQLfast procedures (e.g., data loading, data checking, report generation),
• Python external libraries (e.g., string manipulation, file management, geometric algorithms, web access),
• Python external applications (e.g., data import, interactive data conversion, complex dialogues, map digitizer, graphical engine),
• language extension (through script precompilation)
• external programs (web browsers, image processors, media processors, Office suite, etc.)

• a builtin schema database (data dictionary)
• a tutorial engine, that can be called from the graphical interface or from within any script
• reflexivity: an SQLfast script can generate, examine, modify and execute SQLfast scripts, including itself.
• development and debugging tools

• Requirements: Windows XP, 7, 8 and 10. All the resources needed by the basic version (built on SQLite 3) are included in the distribution. MySQL and MS Access versions are under development.

• Date: 2012-
• Status: ongoing
• Availability: From the SQLfast web page.

• References and resources:
  • SQLfast environment for Windows. Get it from the SQLfast web page.
  • Python 2.7. To use to develop extensions to SQLfast. [download page]
  • SQLite3 DBMS is included in the SQLfast software.
  • Jean-Luc Hainaut. Dictionary of SQLfast commands and parameters, 60 pages, PReCISE Research Center publication, draft version, April 7, 2017. [full text]
  • Jean-Luc Hainaut. SQLfast: Database processing made easy - Tutorial and Case studies, 900 pages, PReCISE Research Center publication, draft version. This is the complete SQLfast tutorial. Many of its chapters are already available through the links below. However, its 19 first chapters are available as a single document (get them here). These chapters are continuously updated, so that it is recommended to check them regularly to get their last versions.

• The complete SQLfast tutorial. The SQLfast tutorial progressively introduces the reader to the basics of SQLfast, from elementary data manipulation through very short scripts to complex features such as metadata-based script generation, recursive programming and language extension. It also includes some representative applications: database creation and loading, database exploration, database migration, library management, interactive SQL interpreter, a photo album manager and browser, a statistics manager and viewer, a bill of material manager, a GIS (based on ESRI shapefiles), analysis of an undocumented database, topological sorting of a relational schema, GALOIS lattice generation, automatic GUI generation, automated SQL trainer, ontology-based text indexing and retrieval, Conway's Game of Life (cellular automata), Kings of France (tree processing), schema-less (NoSQL) databases, SQL code injection, etc. Selected draft chapters are already available. Note. This tutorial in its current state is a hybrid document. In the near future, it will be split into two distinct volumes, namely "Introduction to database programming with SQLfast" (first tutorial chapters) and "Problem solving with databases" (last application chapters).

• SQLfast Tutorial - 1st pages and Table of contents, draft version, 2016. to be completed [full text]

• SQLfast Tutorial - 1. Introduction, draft version, January 07, 2017. [full text]

Chapter contents: Description of SQLfast. How to start. Example database.

• SQLfast Tutorial - 2. Basic scripts, draft version, January 07, 2017. [full text]

Chapter contents: First scripts. Opening a database. The select query. Using variables. Asking the user to enter data. Interactive scripts.

• SQLfast Tutorial - 3. Getting started with SQL, writing in progress.

• SQLfast Tutorial - 4. Creating a database, draft version, October 24, 2016. [full text]

Chapter contents: Creating a new database. Inserting data in a database. First notion of transaction. Calling an external script. Writing blank lines. In-memory databases.

• SQLfast Tutorial - 5. Updating data, draft version, October 24, 2016. [full text]

Chapter contents: Multiple-field user data entry. Dialogue box layout and mechanics. SQL data modification queries. Interactive data modification. Using variables in query building. Dynamic queries.

• SQLfast Tutorial - 6. More on variables, draft version, October 24, 2016. [full text]

Chapter contents: Playing with dates and times. Measuring elapsed time. Variable/value substitution operator. Computation. SQLfast procedures and variables.

• SQLfast Tutorial - 7. Taking decisions, draft version, October 24, 2016. [full text]

Chapter contents: Alternative control statement. Displaying a message. SQLfast conditions. Skipping statements. Simple loops.

• SQLfast Tutorial - 8. Reading data, draft version, October 24, 2016. [full text]

Chapter contents: Extracting data from the database. Indexed select queries. Queries which return no rows. Coping with empty sets and null values. SQL for loop. Embedded loops.

• SQLfast Tutorial - 9. Looping - The final chapter, draft version, October 24, 2016. [full text]

Chapter contents: SQL-based for loop. Range loops. While loop. Controlling a loop. Loops internals.

• SQLfast Tutorial - 10. SQLfast expressions, draft version, October 24, 2016. [full text]

Chapter contents: Syntax of SQLfast expressions. Numeric expressions. Character string expressions. Logical expressions. Time expressions. General functions. Using external libraries.

• SQLfast Tutorial - 11. Output channels, draft version, October 24, 2016. [full text]

Chapter contents: Where to write? Output channels: console, standard output file, user output file, variable. Changing output channel. Default output channel. Default output mode.

• SQLfast Tutorial - 12. Dialogues, draft version, October 24, 2016. [full text]

Chapter contents: Data entry vs data update. Checking user action. Text entry/update. Selecting one item. Selecting multiple items.

• SQLfast Tutorial - 13. Displaying data, draft version, October 24, 2016. [full text]

Chapter contents: Displaying a message. Message formatting. Displaying a data set. Displaying a text. Displaying a picture. Displaying a drawing. Dialogue help. Loading a file in a variable. Database browsers.

• SQLfast Tutorial - 14. Advanced dialogues, draft version, October 24, 2016. [full text]

Chapter contents: Dialogue box layout: multi-row, multi-column. Field and button grouping. Entry fields with a predefined value list. Displayed vs returned values. Composite dialogue box. Heterogeneous dialogue box. Controlling box styles and colors.

• SQLfast Tutorial - 15. Large data objects, draft version, October 24, 2016. [full text]

Chapter contents: Reading and writing large text files. Reading and writing large binary files. Storing large objects in adatabase.

• SQLfast Tutorial - 16. Writing data tables - Part 1, draft version, October 24, 2016. [full text]

Chapter contents: Displaying the result of select query on the output channel. Changing the output format. CSV output format. Key-Value output format. CSV output format. CSV output format. Running an external program.

• SQLfast Tutorial - 17. Writing data tables - Part 2, draft version, October 24, 2016. [full text]

Chapter contents: XML output format. HTML output format. Tuple output format. RTF output format. SQL output format. LateX output format. JSON output format.

• SQLfast Tutorial - 18. Importing and exporting data, draft version, April 01, 2017. [full text]

Chapter contents: csv export, csv import, dbf import, (writing in progress: SQL-DML export, SQL-DML import, JSON export, JSON import).

• SQLfast Tutorial - 19. Recursive programming, draft version, April 07, 2017. [full text]

Chapter contents: Factorial computing. Recursive tree processing. Tree traversal. Coping with cyclic data. SQL recursive queries. SQL recursive triggers. Loop-based recursion implementation. SQLfast recursive scripts.

• SQLfast Tutorial - 20. Metadata - Walking on the wild side, draft version, April 17, 2017. [full text]

Chapter contents: Database schema documentation. System catalog tables. SQLfast dictionaries. System tables SYS_TABLE, SYS_COLUMN, SYS_KEY, SYS_KEY_COMP. Modifying a schema. Building a dictionary. Dictionary-driven scripts.

• SQLfast Tutorial - 21. Metadata - Inception, writing in progress.

Chapter contents: Exploring an unknown database. Dictionary-driven dialogues. Dictionary-driven data modification.

• SQLfast Tutorial - 22. Active databases, writing in progress.

Chapter contents: What is an active database? Introducing dynamic behaviour in the database. The SQL primitives: check, SQL procedures and triggers. Data integrity control. Data update propagation. Redundancy management. A real size application.

• SQLfast Tutorial - 23. SQLdraw, writing in progress.

Chapter contents: What is SQLdraw? The SQLdraw concepts and language. The SQLdraw editor. The SQLdraw viewer.

• SQLfast Tutorial - 24. Extending SQLfast, draft version, June 6, 2016. [full text]

Chapter contents: How to extend the functionalities of SQLfast. Service scripts. External applications. Function libraries. The LStr library. The LFile library. The LGeo library. External programs. Extending the SQLfast language. The SQLfast compiler-loader. Adding user-defined functions.

• SQLfast Tutorial - 25. Aid to SQLfast development, writing in progress.

• SQLfast Tutorial - 31. From data bulk loading to database book writing, draft version, January 10, 2016.[full text]

Chapter contents: Topological sorting. The database loading problem. Dealing with non acyclic graphs. Finding the independent circuits of a graph. Graph contraction and condensation. Ordering topics for a database book.

• SQLfast Tutorial - 32. Conway's Game of Life, draft version, June 2, 2014. [full text]

Chapter contents: Cellular automata. World and automaton representation. Computing next generation. Graphical display of automaton evolution. Script and database optimization.

• SQLfast Tutorial - 33. Interactive SQLfast interpreters, draft version, June 2, 2014. [full text]

Chapter contents: SQL query execution. Interactive interpreter. Complex graphical user interface.

Statements and functions: askCombo.

• SQLfast Tutorial - 34. Geographic information systems, writing in progress.

• SQLfast Tutorial - 35. Kings of France - Part 1, draft version, October 1, 2014. [full text]

Chapter contents: Representation of Kings of France genealogy. Tree processing. Simple queries.

• SQLfast Tutorial - 36. Kings of France - Part 2, draft version, October 1, 2014. [full text]

Chapter contents: Tree processing. Recursive queries. Recursive scripts. Computing transitive closure. Computing transitive reduction. Computing the projection of a tree. Graphical representation of trees.

Statements and functions: SQL with, SQLdraw.

• SQLfast Tutorial - 37. Text processing, writing in progress

• SQLfast Tutorial - 38. Temporal databases, writing in progress.

Chapter contents: Temporal data. Transaction vs valid times. Historical data representation. Management of historical data. Querying historical data. Temporal join. Declarative temporal projection. Efficient temporal projection. Temporal grouping.

Statements and functions: trigger.

• SQLfast Tutorial - 39. Schema-less databases (1), draft version, April 6, 2014. [full text]

Chapter contents: Non-relational database models. The Universal table models. The column-oriented models. Relational equivalence of non-relational models.

• SQLfast Tutorial - 40. Schema-less databases (2), draft version, February 13, 2014. [full text]

Chapter contents: The Key-Value models. Relational/Key-value models equivalence.

• SQLfast Tutorial - 41. Schema-less databases (3), draft version, April 6, 2014. [full text]

Chapter contents: The object-based models. The document-oriented models. Representation of multivalued, composite attributes. XML and Json object representation. Relational equivalence of non-relational models. Indexing objects. Database model comparison.

Statements and functions: group_concat.

• SQLfast Tutorial - 42. SQL code injection, draft version, February 13, 2014. [full text]

Chapter contents: Principles of SQL code injection. Metadata stealing. System intrusion. Unsecure GUI. Unsecure SQL statement processing. SQL injection countermeasures.

• SQLfast Tutorial - 43. Bill of material, rewriting in progress.

Chapter contents: The BOM management system. Representing a BOM. Acyclic graph processing. Recursive queries. Computing the part list of a part. Computing the closure of a BOM. Computing the cost of a part. Updating a BOM.

Statements and functions: SQL with, create trigger.

• SQLfast Tutorial - 44. The book of which you are the hero, draft version, June 2, 2014. [full text]

Chapter contents: General graph processing. Recursive queries. Graph analysis. Path generation. Circuit detection and counting. User-driven graph traversal. Text-based adventure game representation. Graphical game engine. Automatic novel generation.

Statements and functions: SQL with, askCombo.

• SQLfast Tutorial - 45. Managing a small library, writing in progress.

Chapter contents: Interactive application. Task analysis. Application architecture. Data management functions. File management functions. Data integrity. Graphical user interface.

• SQLfast Tutorial - 46. Test Database Generation, draft version, December 4, 2014. [full text]

Chapter contents: Database performance evaluation. Generating high volume of synthetic data. Integrating heterogeneous data sources. Data cleaning. Data anonymization. Random data extraction. Executing very large scripts. Query performance.

Statements and functions: random(), cross join, fastExec.

• SQLfast Tutorial - 47. Database Performance, writing in progress.

Chapter contents: Database performance tuning. Index types. The roles of cache memories. Performance evaluation pitfalls. Optimizing select queries. Optimizing data modification queries. Optimizing group by and order by queries.

Statements and functions: create index.

• SQLfast Tutorial - 48. Directory management, draft version. March 5, 2017. [full text].

Chapter contents: File and directory management. File and directory hierarchy representation. Tree processing. Recursive queries. Hierarchy evolution. Hierarchy comparison. Searching for file clones.

Statements and functions: selectDirectory, dirFileNamesAll, LFile:splitPath, LFile:infoFile, hashFile, SQL with.

• SQLfast Tutorial - 49. Object/Relational mapping vs Pure SQL, writing in progress.

• SQLfast Tutorial - 50. Lies, damned lies, and statistics, writing in progress.

• SQLfast Tutorial - 51. title pending (Formal Concept Analysis), writing in progress.

• SQLfast Tutorial - 52. Path finders, rovers and Ariadne's thread, draft version. December 5, 2016. [full text].

Chapter contents: Computing the shortest path between cities, Dijkstra's algorithm, controlling a rover, solving mazes.

• SQLfast Tutorial - 53. Business process modeling - Building a workflow engine, writing in progress.

• SQLfast Tutorial - 54. Database prototyping, writing in progress.

• SQLfast Tutorial - 55. Data cleaning, writing in progress.

• SQLfast Tutorial - 56. Database reverse engineering, writing in progress.

• SQLfast Tutorial - Appendix 1. A basket of examples, draft version, writing in progress. April 18, 2016. [full text]

Chapter contents: Comparing the contents of two tables. Displaying the contents of two tables in a single table. Dumping a database in XML format. Data-driven multiple aggregations. Data mining. Paged reading of data. Managing a file of parameters. Image selection tool. Simulating arrays in SQLfast. Smart writing of data table. Checking functional dependencies in a table. Managing a photo album. A picture viewer. Collecting web statistics. Computing missing data in incomplete statistical series. The Travelling Salesman problem. and more ...

• SQLfast Tutorial - Appendix 2. SQLfast installation, draft version, obsolete. February 13, 2014. [full text]

• SQLfast Tutorial - Appendix 3. SQLfast summary, draft version, October 1, 2014. [full text]

• SQLfast Tutorial - Appendix 4. SQL in a nutshell, draft version, February 13, 2014. [full text]

• SQLfast Tutorial - Appendix 8. SQLite transactions, draft version, February 13, 2014. [full text]

• SQLfast Tutorial - Appendix 9. SQLfast internals, obsolete, February 13, 2014. [full text]

DB-MAIN

• Type: CASE and MetaCASE environment
• Description: Graphical CASE tool that supports most database engineering processes. Provides functions for conceptual information analysis, logical design, physical design, code generation, schema integration, schema normalization, quality evaluation, schema mapping, reverse engineering (for a dozen legacy and modern DBMS), schema analysis, code analysis (a.o., depency graphs, pattern searching and program slicing), data analysis, schema transformation. High speed kernel and graphical interface written in C++. New functions can be developed in Voyager 2 (the DB-MAIN external language) or in Java. DB-MAIN includes a method engine and features to extend its meta-schema. It allows the representation of data structures of various paradigms and at any level of abstraction. It relies on the GER (Generic Entity-relationship model) and on transformational operators.
• Requirements: MS Windows, Linux.
• Date: 1993-2014+
• Status: ongoing
• Availability: Free of charge (except for advanced processors). See below
• References and resources:

• DB-MAIN v6 (free, max 500 objects)
  • Software: DB-MAIN v6.5d.zip
  • Manual: Manuel DB-MAIN v65d
• DB-MAIN v8 (free, max 2000 objects)
  • Software: DB-MAIN v8.2b
  • Quick help: db_main v8.chm
  • Manual: Manuel-DB-MAIN-v8.zip
• DB-MAIN v9 (nouvelle interface, gratuit, sans limitation du nombre d'objets)
  • Software: site DB-MAIN
  • Manual: Manuel-DB-MAIN-v9.zip
• First Steps with DB-MAIN (short tutorial)
  • Hlp version: 1st-Step.hlp
  • Pdf version: 1st-Step.pdf
  • Chm version: 1st-Step.chm
• Introduction to database design with DB-MAIN (long tutorial)
  • Pdf version: MiniTut-2002.pdf
• Visit also the DB-MAIN official site www.db-main.eu or the site of ReveR s.a. www.rever.eu, now in charge of the maintenance and evolution of DB-MAIN.

TRAMIS/Master

• Type: CASE environment
• Description: TRAMIS/Master was an extension of ORGA distributed by the small French company CONCIS. In particular, the following features were added:

• support for conceptual analysis
• a larger transformation toolbox
• evaluation functions (notably volume estimation)
• the specification database was implemented in MDBS (a CODASYL-like DBMS)

• Requirements: MS-DOS; MS Windows 3.11; MDBS
• Date: 1986-1991
• Status: discontinued; principles integrated into DB-MAIN
• Availability: software no longer available
• References and resources:
  • Jean-Luc Hainaut,Mario Cadelli,Bernard Decuyper and Olivier Marchand. TRAMIS:a transformation-based database CASE tool, in Proceedings of 5th International Conference on Software Engineering and Applications, EC2 Publish., 1992. [description]
  • Jean-Luc Hainaut, Mario Cadelli, Bernard Decuyper and Olivier Marchand. Database CASE Tool Architecture : Principles for Flexible Design Strategies, in Proceedings of the 4th International Conference on Advanced Information System Engineering (CAiSE’92), Lecture Notes in Computer Science, Volume 593, pages 187-207, Springer-Verlag, 1992. [description]

ORGA

• Type: CASE environment
• Description: ORGA was a companion CASE tool of the industrial IDA environment developed by the team of professor F. Bodard in the 80's and distributed (as well as ORGA) by METSI, a small French company in Information system design. IDA was mainly devoted to the conceptual specification of various aspects of Information systems. The goal of ORGA was the translation of information conceptual schemas into DDL code according to three data models, namely relational (SQL-87), CODASYL DDL (IDS2) and standard files (COBOL). In modern words, ORGA supported the logical design and code generation processes. Three key features of ORGA:

• the architecture of ORGA relies on a transformational toolbox. The conceptual/logical conversion was based on three transformation plans, one for each target data model. In addition, the user was allowed to apply elementary transformations to refine the logical schema. In modern words, ORGA supported Model-Driven Engineering.
• ORGA was developed in C and the specification database was managed through libraries emulating dBASE III structures [1] [2].
• The methodological principles of ORGA were developed in the (French) book Conception assistée des applications informatiques - Conception de la base de données, published by Masson (now DUNOD) in 1986.

• Requirements: MS-DOS; hard disk (recommended!)
• Date: 1984-1985
• Status: discontinued; principles integrated into TRAMIS/Master then in DB-MAIN
• Availability: software no longer available
• References and resources:
  • see TRAMIS/Master

PHENIX

• Type: Reverse Engineering CASE environment
• Description: PHENIX was a CASE tool devoted to the reverse engineering of the file structures of large COBOL applications. It was developed with powerful knowledge management systems (which were very popular in the 80's and 90's), a decision which accounted for the poor performance of the code analyzer. In particular, it included:

• a code analyzer that detected program patterns
• a large transformation toolbox
• an inference engine to detect and identify implicit constructs.

• Requirements: Unix, LeLisp, SMECI, Aida/Masai
• Date: 1989-1992
• Status: discontinued; principles integrated into DB-MAIN
• Availability: software no longer available
• References and resources:
  • Jean-Luc Hainaut, Catherine Tonneau, Michel Joris and Muriel Chandelon. Transformation-based Database Reverse Engineering, in Proceedings of 12th International Conference on Entity-Relationship Approach (ER’93), Lecture Notes in Computer Science, Volume 823, pages 364-375, Springer-Verlag, 1994. [description]
  • Jean-Luc Hainaut, Muriel Chandelon, Catherine Tonneau and Michel Joris. Contribution to a Theory of Database Reverse Engineering, in Proceedings of the 1st Working Conference on Reverse Engineering (WCRE’93), pages 161-170, IEEE Computer Society, 1993. [description]
  • Jean-Luc Hainaut, Catherine Tonneau, Michel Joris and Muriel Chandelon. Schema Transformation Techniques for Database Reverse Engineering, in Proceedings of 12th International Conference on the Entity-Relationship Approach (ER’93), pages 353-372, E/R Institute Publish., 1993. [description]
  • Michel Joris, R. Hoe, Jean-Luc Hainaut, Muriel Chandelon, Tonneau Catherine and Bodart François. PHENIX : methods and tools for database reverse engineering, in Proceedings of 5th International Conference on Software Engineering and Applications, EC2 Publish., 1992. [description]
  • Jean-Luc Hainaut. Database Reverse Engineering, Models, Techniques and Strategies, in Proceedings of the 10th International Conference on the Entity-Relationship Approach (ER’91), pages 729-741, ER Institute, 1991. [description] [full text]

VisiComp

• Type: Graphical computer simulator
• Description: VisiComp (name inspired by VisiCalc) is a graphical simulator for an educational computer. This computer has a very simple architecture: a main memory of up to 100 cells (each accomodating a signed 5-digit number), the standard handful of registers (P-counter, instruction register, logical register, accumulator + its extension register), 37 instructions organized into 7 instruction families, 10 addressing modes, a sequential input device, a sequential output device, bootstrap (kind of MBR). The simulator also includes a monitor that manages and controls all the parts of the computer (interactive + script interpreter), configuration files, input file loader, etc. The computer has been used to illustrate architecture and programming principles such as Von Neuman architecture, basic program structures, I/O buffering, recursion, relocatable programs, bootstrapping and even a complete business application (order management and invoicing with 3 files, almost as powerful as SAP!) The simulator and its additional components required at runtime no more than 12 KB (12,000 bytes!) in RAM. It was developed in TRS-80 Basic then in CP/M Basic.
• Requirements: TRS-80 Model 1 - Level 2 (16 KB RAM); CP/M; BASIC language processor;
• Date: 1982
• Status: discontinued
• Availability: Source code available on paper
• References and resources:
  • Jean-Luc Hainaut, THE VISIBLE COMPUTER - A graphical computer simulator. Technical report, 1982. 75 pages. [full text] This document results from the scanning of poorly printed pages. It is incomplete and probably includes too many errors. In addition, it will probably be considered fairly naive. Consider that it was written 30 years ago and that computer technology has considerable evolved since then, to such an extent that is has become quite complex and impossible to describe in detail to plain lay readers.
  • Source code available on paper.
  • Note: just for the fun, I have developed a new graphical interface for Visicomp in Python/Tkinter. It would take no more than 2-3 weeks to add the logic behind. Anyone interested?

NDBS

• Type: Database Management System
• Description: NDBS (Network Database System) 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, etc. As far as we now, the only industrial use was by an Indian Statistics department, which developed several applications programs based on NDBS. 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.
• Requirements: Windows; written in Turbo-Pascal
• Date: 1986-1996
• Status: discontinued
• Availability: source code (but I must search floppy disks for it).
• References and resources:
  • [TR86-01]Jean-Luc Hainaut, NDBS - A simple database system for small computers. Technical report, 1986. 144 pages. [full text] This text has been recovered from various materials written in the eighties (Mac Word documents) and roughly assembled into a unique FrameMaker document. The result would require in-depth polishing, but, to be frank, I currently have no spare time to carry out this task. Sometimes in the near future perhaps!

SQL-Script Interpreter

• Type: Interpreter for an SQL-based scripting language
• Description: Empty Access application (*.mdb) that allows users to execute SQL scripts on any database. Includes a simple dialog box with two buttons: select a script file and execute it. For information, 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). This tool allows one to execute in one click a script made up of a sequence of SQL statements, but also much more complex scripts with variables, macros, loops, alternatives and interaction with users. This application has been frozen in 2012 since the development of SQLfast (see above).
• Requirements: Microsoft Access
• Date: 2005-2011+
• Status: ongoing
• Availability: available online
• References and resources:
  • Software (mdb): SQL-Interpreter-FR-(v4).mdb
  • Documentation :
    • Installation (French) : Installer-SQL-interpreter-(v4).pdf
    • SQL Script Language(English) : The-SQL-Script-Language.pdf

SPHINX

• Type: Database Management System, Natural Language interface, Data dictionary
• Description: SPHINX is a database management system based on the ER model. It was developed in the 70's as a result of large national project devoted to the development and management of large administrative databases. SPHINX comprised several components:

• a runtime database handler
• a high-level DML language integrated to COBOL
• NUL: an incremental user-oriented query language
• a meta-system (based on SPHINX)

• Requirements: Siemens BS-2000; SESAM data manager (one of the first schema-less inverted-file data manager);
• Date: 1971-1977
• Status: discontinued
• Availability: software no longer available
• References and resources:
  • Baudouin Le 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]
  • 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]
  • Claude Deheneffe, Henri Hennebert and Walter Paulus. A Relational Model for a Data Base, in Proceedings of the IFIP congress 74, pages 1022-1025, North-Holland, 1974. [description] [full text]
  • Jean-Luc Hainaut and Baudouin Le Charlier. An Extensible Semantic Model of Data Base and Its Data language, in Proceedings of the IFIP Congress 74, pages 1026-1030, North-Holland, 1974. [description] [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]

DB-MAIN

• Type: CASE and MetaCASE environment
• Description: Graphical CASE tool that supports most database engineering processes. Provides functions for conceptual information analysis, logical design, physical design, code generation, schema integration, schema normalization, quality evaluation, schema mapping, reverse engineering (for a dozen legacy and modern DBMS), schema analysis, code analysis (a.o., depency graphs, pattern searching and program slicing), data analysis, schema transformation. High speed kernel and graphical interface written in C++. New functions can be developed in Voyager 2 (the DB-MAIN external language) or in Java. DB-MAIN includes a method engine and features to extend its meta-schema. It allows the representation of data structures of various paradigms and at any level of abstraction. It relies on the GER (Generic Entity-relationship model) and on transformational operators.
• Requirements: MS Windows, Linux.
• Date: 1993-2014+
• Status: ongoing
• Availability: Free of charge (except for advanced processors). See below
• References and resources:

• DB-MAIN v6 (free, max 500 objects)
  • Software: DB-MAIN v6.5d.zip
  • Manual: Manuel DB-MAIN v65d
• DB-MAIN v8 (free, max 2000 objects)
  • Software: DB-MAIN v8.2b
  • Quick help: db_main v8.chm
  • Manual: Manuel-DB-MAIN-v8.zip
• DB-MAIN v9 (nouvelle interface, gratuit, sans limitation du nombre d'objets)
  • Software: site DB-MAIN
  • Manual: Manuel-DB-MAIN-v9.zip
• First Steps with DB-MAIN (short tutorial)
  • Hlp version: 1st-Step.hlp
  • Pdf version: 1st-Step.pdf
  • Chm version: 1st-Step.chm
• Introduction to database design with DB-MAIN (long tutorial)
  • Pdf version: MiniTut-2002.pdf
• Visit also the DB-MAIN official site www.db-main.eu or the site of ReveR s.a. www.rever.eu, now in charge of the maintenance and evolution of DB-MAIN.

TRAMIS/Master

• Type: CASE environment
• Description: TRAMIS/Master was an extension of ORGA distributed by the small French company CONCIS. In particular, the following features were added:

• support for conceptual analysis
• a larger transformation toolbox
• evaluation functions (notably volume estimation)
• the specification database was implemented in MDBS (a CODASYL-like DBMS)

• Requirements: MS-DOS; MS Windows 3.11; MDBS
• Date: 1986-1991
• Status: discontinued; principles integrated into DB-MAIN
• Availability: software no longer available
• References and resources:
  • Jean-Luc Hainaut,Mario Cadelli,Bernard Decuyper and Olivier Marchand. TRAMIS:a transformation-based database CASE tool, in Proceedings of 5th International Conference on Software Engineering and Applications, EC2 Publish., 1992. [description]
  • Jean-Luc Hainaut, Mario Cadelli, Bernard Decuyper and Olivier Marchand. Database CASE Tool Architecture : Principles for Flexible Design Strategies, in Proceedings of the 4th International Conference on Advanced Information System Engineering (CAiSE’92), Lecture Notes in Computer Science, Volume 593, pages 187-207, Springer-Verlag, 1992. [description]

ORGA

• Type: CASE environment
• Description: ORGA was a companion CASE tool of the industrial IDA environment developed by the team of professor F. Bodard in the 80's and distributed (as well as ORGA) by METSI, a small French company in Information system design. IDA was mainly devoted to the conceptual specification of various aspects of Information systems. The goal of ORGA was the translation of information conceptual schemas into DDL code according to three data models, namely relational (SQL-87), CODASYL DDL (IDS2) and standard files (COBOL). In modern words, ORGA supported the logical design and code generation processes. Three key features of ORGA:

• the architecture of ORGA relies on a transformational toolbox. The conceptual/logical conversion was based on three transformation plans, one for each target data model. In addition, the user was allowed to apply elementary transformations to refine the logical schema. In modern words, ORGA supported Model-Driven Engineering.
• ORGA was developed in C and the specification database was managed through libraries emulating dBASE III structures [3] [4].
• The methodological principles of ORGA were developed in the (French) book Conception assistée des applications informatiques - Conception de la base de données, published by Masson (now DUNOD) in 1986.

• Requirements: MS-DOS; hard disk (recommended!)
• Date: 1984-1985
• Status: discontinued; principles integrated into TRAMIS/Master then in DB-MAIN
• Availability: software no longer available
• References and resources:
  • see TRAMIS/Master

PHENIX

• Type: Reverse Engineering CASE environment
• Description: PHENIX was a CASE tool devoted to the reverse engineering of the file structures of large COBOL applications. It was developed with powerful knowledge management systems (which were very popular in the 80's and 90's), a decision which accounted for the poor performance of the code analyzer. In particular, it included:

• a code analyzer that detected program patterns
• a large transformation toolbox
• an inference engine to detect and identify implicit constructs.

• Requirements: Unix, LeLisp, SMECI, Aida/Masai
• Date: 1989-1992
• Status: discontinued; principles integrated into DB-MAIN
• Availability: software no longer available
• References and resources:
  • Jean-Luc Hainaut, Catherine Tonneau, Michel Joris and Muriel Chandelon. Transformation-based Database Reverse Engineering, in Proceedings of 12th International Conference on Entity-Relationship Approach (ER’93), Lecture Notes in Computer Science, Volume 823, pages 364-375, Springer-Verlag, 1994. [description]
  • Jean-Luc Hainaut, Muriel Chandelon, Catherine Tonneau and Michel Joris. Contribution to a Theory of Database Reverse Engineering, in Proceedings of the 1st Working Conference on Reverse Engineering (WCRE’93), pages 161-170, IEEE Computer Society, 1993. [description]
  • Jean-Luc Hainaut, Catherine Tonneau, Michel Joris and Muriel Chandelon. Schema Transformation Techniques for Database Reverse Engineering, in Proceedings of 12th International Conference on the Entity-Relationship Approach (ER’93), pages 353-372, E/R Institute Publish., 1993. [description]
  • Michel Joris, R. Hoe, Jean-Luc Hainaut, Muriel Chandelon, Tonneau Catherine and Bodart François. PHENIX : methods and tools for database reverse engineering, in Proceedings of 5th International Conference on Software Engineering and Applications, EC2 Publish., 1992. [description]
  • Jean-Luc Hainaut. Database Reverse Engineering, Models, Techniques and Strategies, in Proceedings of the 10th International Conference on the Entity-Relationship Approach (ER’91), pages 729-741, ER Institute, 1991. [description] [full text]

VisiComp

• Type: Graphical computer simulator
• Description: VisiComp (name inspired by VisiCalc) is a graphical simulator for an educational computer. This computer has a very simple architecture: a main memory of up to 100 cells (each accomodating a signed 5-digit number), the standard handful of registers (P-counter, instruction register, logical register, accumulator + its extension register), 37 instructions organized into 7 instruction families, 10 addressing modes, a sequential input device, a sequential output device, bootstrap (kind of MBR). The simulator also includes a monitor that manages and controls all the parts of the computer (interactive + script interpreter), configuration files, input file loader, etc. The computer has been used to illustrate architecture and programming principles such as Von Neuman architecture, basic program structures, I/O buffering, recursion, relocatable programs, bootstrapping and even a complete business application (order management and invoicing with 3 files, almost as powerful as SAP!) The simulator and its additional components required at runtime no more than 12 KB (12,000 bytes!) in RAM. It was developed in TRS-80 Basic then in CP/M Basic.
• Requirements: TRS-80 Model 1 - Level 2 (16 KB RAM); CP/M; BASIC language processor;
• Date: 1982
• Status: discontinued
• Availability: Source code available on paper
• References and resources:
  • Jean-Luc Hainaut, THE VISIBLE COMPUTER - A graphical computer simulator. Technical report, 1982. 75 pages. [full text] This document results from the scanning of poorly printed pages. It is incomplete and probably includes too many errors. In addition, it will probably be considered fairly naive. Consider that it was written 30 years ago and that computer technology has considerable evolved since then, to such an extent that is has become quite complex and impossible to describe in detail to plain lay readers.
  • Source code available on paper.
  • Note: just for the fun, I have developed a new graphical interface for Visicomp in Python/Tkinter. It would take no more than 2-3 weeks to add the logic behind. Anyone interested?

NDBS

• Type: Database Management System
• Description: NDBS (Network Database System) 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, etc. As far as we now, the only industrial use was by an Indian Statistics department, which developed several applications programs based on NDBS. 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.
• Requirements: Windows; written in Turbo-Pascal
• Date: 1986-1996
• Status: discontinued
• Availability: source code (but I must search floppy disks for it).
• References and resources:
  • [TR86-01]Jean-Luc Hainaut, NDBS - A simple database system for small computers. Technical report, 1986. 144 pages. [full text] This text has been recovered from various materials written in the eighties (Mac Word documents) and roughly assembled into a unique FrameMaker document. The result would require in-depth polishing, but, to be frank, I currently have no spare time to carry out this task. Sometimes in the near future perhaps!

SQL-Script Interpreter

• Type: Interpreter for an SQL-based scripting language
• Description: Empty Access application (*.mdb) that allows users to execute SQL scripts on any database. Includes a simple dialog box with two buttons: select a script file and execute it. For information, 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). This tool allows one to execute in one click a script made up of a sequence of SQL statements, but also much more complex scripts with variables, macros, loops, alternatives and interaction with users. This application has been frozen in 2012 since the development of SQLfast (see above).
• Requirements: Microsoft Access
• Date: 2005-2011+
• Status: ongoing
• Availability: available online
• References and resources:
  • Software (mdb): SQL-Interpreter-FR-(v4).mdb
  • Documentation :
    • Installation (French) : Installer-SQL-interpreter-(v4).pdf
    • SQL Script Language(English) : The-SQL-Script-Language.pdf

SPHINX

• Type: Database Management System, Natural Language interface, Data dictionary
• Description: SPHINX is a database management system based on the ER model. It was developed in the 70's as a result of large national project devoted to the development and management of large administrative databases. SPHINX comprised several components:

• a runtime database handler
• a high-level DML language integrated to COBOL
• NUL: an incremental user-oriented query language
• a meta-system (based on SPHINX)

• Requirements: Siemens BS-2000; SESAM data manager (one of the first schema-less inverted-file data manager);
• Date: 1971-1977
• Status: discontinued
• Availability: software no longer available
• References and resources:
  • Baudouin Le 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]
  • 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]
  • Claude Deheneffe, Henri Hennebert and Walter Paulus. A Relational Model for a Data Base, in Proceedings of the IFIP congress 74, pages 1022-1025, North-Holland, 1974. [description] [full text]
  • Jean-Luc Hainaut and Baudouin Le Charlier. An Extensible Semantic Model of Data Base and Its Data language, in Proceedings of the IFIP Congress 74, pages 1026-1030, North-Holland, 1974. [description] [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]
  • [TR78-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. [full text]