LIBD:Outils

<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.

Manuals and case studies last updates

2022-09-29: SQLfast manual [full text]

2022-12-28: Dictionary of SQLfast commands and parameters [full text]

2023-06-04: Case study: Classifying objects [full text]

2022-04-14: Case study: The book of which you are the hero [full text]

2021-10-16: Case study: Four hours to save the library [full text]

2021-05-05: Case study: Path finders, rovers and Ariadne's thread [full text]

2020-10-04: Case study: Blockchains [full text]

2020-09-23: Case study: Active databases [full text]

2020-02-01: Case study: Temporal databases - Part 1 [full text]

2019-08-29: Case study: Temporal databases - Part 2 [full text]

2018-08-28: Case study: From data bulk loading to database book writing [full text]

2018-08-28: Case study: Conway's Game of Life [full text]

2018-08-28: Case study: Interactive SQL interpreter [full text]

2018-08-28: Case study: Schema-less databases - Part 1 [full text]

2018-08-28: Case study: Schema-less databases - Part 2 [full text]

2018-08-28: Case study: Schema-less databases - Part 3 [full text]

2018-08-28: Case study: Kings of France - Part 1 [full text]

2018-08-28: Case study: Kings of France - Part 2 [full text] [technical complement]

2018-08-28: Case study: Directory management [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, C# or Python 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, 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, off-line, 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 and Linux machines (through Wine). Its default database manager is SQLite3.

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 with Tkinter, SQLite3 and PIL modules. 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 and 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 (based on the SQLite3 interface)
• new SQL functions, including additional numeric, character and time functions, cryptography, 40 list management functions, file/web functions, temporal algebra operator
• dynamic columns: columns created through their usage instead of by DDL commands
• about 100 system parameters to customize the interface and the behaviour of SQLfast engine
• additional languages:

• SQLdraw: specification of vector graphics, static (still drawing) and dynamic (animated)
• SQLtuto: description of help documents, guides and tutorials; allows the building of complex hypertext, multimedia systems, that may include executable code fragments;
• LTemp: algebraic temporal operators: temporal, coalescing, aggregation, interval normalization

• 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 and complete instructions. The SQLfast pre-processor can (recursively) replace variable references by their values.
• control structures (if-then-else, for-endfor, while-endwhile, procedure call, return, shutdown, error management, libraries, external applications, stop, pause)
• reading/writing in external files, encoding/decoding into secure formats (hexadecimal, base64), secure hashing, encryption
• large object management (text, video, sound, BLOB, bitmap, charts, static and dynamic vector graphics)
• generation of complex data display formats (3 tabular, csv, xml, html, rtf, LateX, JSON, Key-value, SQL, tuples, headers, text, etc.) These formats are defined by generic parameters and can be dynamically extended.
• import processors for standard formats (currently dbf, csv)
• a rich extensible GUI:

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

• an integrated graphical engine renders SQLdraw scripts to produce still and animated drawing (charts, drawing, maps, animated simulation, etc.).
• extensibility mechanisms:

• SQL user-defined function
• user-defined and builtin technical SQLfast scripts (e.g., parameters setting),
• user-defined and builtin functional SQLfast procedures (e.g., data loading, data checking, report generation, rebuilding SQL-DDL code of an unknown database),
• Python external libraries (e.g., string manipulation, file management, geometric algorithms, web access, FCA_Engine),
• Python external applications (e.g., data import, interactive data conversion, ETL, complex dialogues, map digitizer, graphical engine),
• language extension (through script precompilation)
• calling external programs (web browsers, image processors, media processors, Office suite, etc.)

• a built-in data dictionary comprising up to 10 system tables,
• 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: error reporting; variable dumping, powerful tracing subsystem for SQLfast commands, SQL queries and SQL triggers; trace analysis through SQL queries,

• Requirements: Windows from Vista to Win11 (64 bits). All the resources needed are included in the distribution.

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

• Main references and resources:
  • SQLfast environment for Windows. Get it from the SQLfast web page.
  • Python 2.7 (conversion to Python 3 in progress). Not required to execute SQLfast. Must be used to develop extensions to SQLfast.
  • The latest stable version of SQLite3 DBMS is included in the SQLfast distribution (file sqlite3.ddl in the SQLfast folder). As soon as a new version is available in the SQLite download site, just replace the current file with the last version..
  • SQLfast manual - Database application programming [full text]
  • Dictionary of SQLfast commands and parameters [full text].

• SQLfast manual and case studies

The external documentation comprises two important volumes. The first one is the SQLfast manual, an introduction to the development of database applications. The second one is a collection of case studies illustrating the use of database programming to solve large families of problems.

Volume 1: SQLfast MANUAL - INTRODUCTION TO DATABASE PROGRAMMING

• Table of contents

• Chapter 1. Introduction

Chapter contents: What is SQLfast?. The target audiences of SQLfast. Installing and starting SQLfast . The levels of the SQLfast interface. Starting SQLfast with the Basic interface. Selecting another level. Starting SQLfast with the Expert interface (Level 1). The example databases: ORDERS.db and CLICOM.db. SQLfast resources and documentation.

• Chapter 2. Basic scripts

Chapter contents: Preparing the work. Writing and running an SQLfast script. Saving the current script. Loading and modifying a script. Other ways to execute a script. Representing scripts and results in this tutorial. How to write SQLfast statements?. Questions on this first script?. A second script. just as simple. Using variables - First encounter. Variable’s way of life. Interactive SQLfast scripts. To quote or not to quote. SQLfast sessions. Summary.

• Chapter 3. Creating a database

Chapter contents: How to create a new database. How (not) to loose data. Creating and loading the ORDERS database. Calling secondary scripts (first look). The schema of the ORDERS.db database. A new skill: writing blank lines. Temporary tables. In memory databases. Upper case or lower case?. Summary.

• Chapter 4. Updating data

Chapter contents: Extending user-driven data selection. Data modification statements: a short reminder. Inserting rows into a table. About single quotes in SQL values. Updating rows of a table. Variables in statement customization. Variables in full statement specification. Improving data entry box layout. Dialogue box for updating values. Summary.

• Chapter 5. SQL in a nutshell

Chapter contents: Introduction. Creating and modifying the schema of a database. Single table extraction queries. Subqueries. SQL functions. Aggregate (statistical) functions. Joining tables. Set operators. Data grouping. Generalization of the select and from clauses. Data modification. SQL views. Stored procedures and triggers. About SQLite.

• Chapter 6. More on variables

Chapter contents: Time variables and statements. Measuring elapsed time. Oh! Wait a minute!. Variable delimiters - Reminder. We can change the delimiters. Sometimes variable substitution must be disabled. Beware substitution ambiguity. Numeric computation. Character string computation. Set or compute?. Multi-variables assignment. Simulating an array. Variables and SQLfast procedures. What is a variable value after all?. Summary.

• Chapter 7. Taking decisions

Chapter contents: Alternative execution of a script. Skipping statements. If-else-endif block structure. Better controlling user values. Conditions. If. goto. labels and variables. Complement: more on goto and label statements. Summary.

• Chapter 8. Reading data

Chapter contents: Can we process individual rows?. Forcing a query to return one row only. Can we force a query to returns two rows?. Queries that return no row. Looping in the database. Embedded loops. A more elegant script. SQLfast arrays revisited. Empty sets, null values and statistics. Summary.

• Chapter 9. Looping - The final chapter

Chapter contents: Introduction. SQL for loop. Range for loop. File for loop. While loop. Forced loop iteration. Forced loop exit. While (True) loop. Correct loop structure. Variables in loops and if statements. How does a loop work, actually?. Summary.

• Chapter 10. SQLfast expressions

Chapter contents: Introduction. Value of a variable. Binary values. Syntax of SQLfast expressions. Numeric functions. Character string functions. Logical functions. Temporal functions. File/web functions. General functions. User defined functions (UDF). Functions from external libraries. Summary.

• Chapter 11. Output channels, files and directories

Chapter contents: The SQLfast output window: the default output channel. The (SQLfast) output window. User output files. The standard output file. Output variables. Closing an output channel. Setting the default output channel and output mode. Clearing the output window. Example: playing with output channels. Managing files and directories. Summary.

• Chapter 12. Dialogues

Chapter contents: The SQLfast GUI. Standard data entry box. Controlling user action. Update a data set. Text entry box. Selecting one item among several. Selecting items among several. Summary.

• Chapter 13. Displaying data

Chapter contents: Information message box. Displaying a list of values on the screen. Displaying a text. Displaying a picture. Displaying drawings. Won't you please, please help me. Summary.

• Chapter 14. Advanced dialogues

Chapter contents: Multicolumn data entry and selection. Logical field grouping. Logical button grouping. No label please!. Application: building a 2D menu. Data entry through selection in a predefined value list. Predefined values from the database. Displayed vs returned values. Customizing standard buttons. Composite dialogue boxes. Heterogeneous composite boxes. Of styles and colors. Example of complex composite box. Summary.

• Chapter 15. Large data objects

Chapter contents: Beyond elementary data items. Displaying large character strings. Reading and writing (large) text files. Reading and writing large binary files. Storing large objects in a database. About binary values. Binary data encoding/decoding. A touch of cryptography. Where to store large objects. Loading large volumes of data. Summary.

• Chapter 16. Writing data tables - Part 1

Chapter contents: An in-depth look at output tables. Changing the layout of an output table. CSV output format. Key-Value output format. List output format. Summary.

• Chapter 17. Writing data tables - Part 2

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

• Chapter 18. Importing and exporting data

Chapter contents: External data import. CSV data import/export. dBase data import. SQL-DML data import/export. JSON data export/import. Summary.

• Chapter 19. Recursive programming

Chapter contents: Introduction. SQL recursive queries. SQL recursive queries: the Staff application. Recursive queries against cyclic data. Can recursive queries compute everything?. Tree traversal ordering. SQL recursive triggers. Loop-based SQLfast implementation of recursive procedures. SQLfast recursive scripts. Comparison and conclusions.

• Chapter 20. Metadata - Walking on the wild side

Chapter contents: Introduction. The SQLfast dictionary tables. Dictionary or no dictionary?. Examining the schema of an unknown database. From metadata to data. Complement: SQL group_concat function. Summary.

• Chapter 21. SQLdraw: vector graphics, writing in progress.

• Chapter 22. Extending SQLfast

Chapter contents: Introduction. SQLfast utilities. Python external applications and libraries. User-defined functions (UDF). SQLfast language extension. Foreign applications. Developing custom-made external applications and libraries. Developing custom-made UDF for SQLfast and SQL. SQL queries in UDF. Complements: standard external SQLfast function libraries.

• Chapter 23. Aid to SQLfast development, writing in progress.

Chapter contents: Introduction. Two SQLast language levels. Examining the contents of variables and parameters. Enabling/disabling script output display. Tracing script execution. Tracing selected code sections. Tracing trigger execution. Analyzing and processing traces.

• Chapter 24. Storing transient data - The SQLfast lists

Chapter contents: Transient data: temporary tables or SQLfast lists? SQLfast lists. Creating an SQLfast list. Manipulation of SQLfast lists. Set-theoretic manipulations of SQLfast lists. List-based for loop. Standard versions of SQLfast list functions. Three application examples.

• Chapter 25. Dynamic columns and schema-less tables

Chapter contents: Introduction. Dynamic columns. Practical syntax. Metadata of dynamic columns. Technical note. Performance: static vs dynamic columns. Dynamic columns in DBMS (MariaDB)). Summary.

• Chapter 26. Developing a tutorial

Chapter contents: Introduction. Opening a tutorial. A tutorial is a formatted text. Adding images. Navigating within and between tutorials. Code snippets. Embedded scripts. Embedded script variants. Embedded scripts as a programming system. Inserting video and audio messages. Embedded wrappers. What is the role of a tutorial, after all?. Summary. Technical complement: about wrappers.

• Appendix. A basket of examples, writing in progress.

Appendix contents: Introduction. Consulting customers. Interactive data loading. Comparing the contents of two tables. Displaying the contents of two table in a single table. Dumping a database in XML format (version 1). Dumping a database in XML format (version 2). Data-driven multiple aggregations (table pivoting). Data mining. Condorcet voting paradox. Paged reading of data. Raising temperature. Managing a file of parameters. Dynamic branching. Simulating arrays in SQLfast. Smart writing of data table. Checking functional dependencies in a table. Image selection tool. Managing a photo album. A picture viewer. Collecting web statistics. Computing missing data in incomplete statistical series. The Travelling Salesman problem. Ordering. and more ...

• Index

Volume 2: PROBLEM SOLVING WITH DATABASES - CASE STUDIES

• Case 1. Four hours to save the library, draft version, November 2020.[full text]

Objective: This case study describes the emergency writing of a small application that was to implement the core functions of the management of a small library. The challenge was to replace the current software, lost in a recent crash of the server. All that was left after the accident was the last backup of the database, unfortunately in an unknown format.

Keywords: rapid application development, application prototyping, application architecture, GUI

• Case 3. Interactive SQL interpreter, draft version, September 25, 2017.[full text]

Objective: An interactive SQL interpreter is this kind of graphical interface through which one enters an SQL query and that displays the result of its execution in a text window. They are used, for instance, to learn SQL or to test and tune SQL queries that are to be integrated in application programs. There are many of them available for free on the internet or included in relational DBMS. In this study, we will build, step by step, our own SQL interpreter by implementing the functions and features we want to use, notably (but not exclusively) to train students in writing SQL queries.

Starting from a very tiny interpreter (just 7 character long!) we will build a series of more comprehensive and versatile versions, up to the last one, that will be able, not only to execute the queries submitted by the user, but also to evaluate their correctness.

All these versions are available as two ready to run applications..

Keywords: SQL interpreter, GUI, learning SQL, query evaluation, multiset, set operator

• Case 4. Schema-less databases - Part 1, draft version, September 10, 2017. [full text]

Objective: This document is the first of a series of three case studies that explore alternative data models that organize the data in structures that provide more flexibility than standard relational tables. We observe that this flexibility makes it easier to dynamically modify the schema of the database but, as an unfortunate consequence, the schema is less expressive. In some of these data models, the schema is practically devoid of any information, hence the name schema-less.

In this study, we explore two extreme data models. In the Universal table model, according to which the data of all the source tables are stored in a single table comprising the union of the columns of the source tables. In the second model, called Column-oriented model, each column of the source tables is implemented in an independent table.

Keywords: non-relational data model, NoSQL, schema-less database, universal table model, universal relation, column-oriented data model, Cassandra, data migration, schema conversion

• Case 5. Schema-less databases - Part 2, draft version, September 10, 2017. [full text]

Objective: This document studies a third family of alternative data models, namely the Key-Value data structure. In these models, the information is represented by triples that each defines the value of an attribute of an entity. Several approaches are described, with increasing levels of genericity.

Keywords: non-relational data model, NoSQL, schema-less database, key-value model, triple, triplestore, RDF, SPARQL, description logic, A-BOX, OWL, Redis, Berkley DB, data migration, schema conversion

• Case 6. Schema-less databases - Part 3, draft version, September 10, 2017. [full text]

Objective: This document studies a fourth family of alternative data models, namely the object (or document) models. In these models, the information is represented by complex objects in which properties can be multivalued and composite.

Keywords: non-relational data model, key-value model, object model, NoSQL, schema-less database, multivalued property, composite property, document-oriented DBMS, MongoDB, CouchDB, Azure, Datastore Oracle, metadata, index, data migration, schema conversion

• Case 8. Active databases, draft version, September 2020. [full text]

Objective: This study shows how advanced data structures of SQL can be used to built smarter databases, in particular active databases, that are able to react to external stimuli. It starts with a short reminder of the SQL data structures, including some of these advanced constructs, namely check constraints, views, generated columns and triggers. Then, it presents some usual applications of active databases, such as integrity control, redundancy management, updatableviews, data modification logging, alerters, type-subtype implementation, repair rules, temporal databases and access control. Finally, it explores, through a simple but representative business application, the power of active databases as compared with traditional application development. In this application, we observe the impact of moving the control of business rules from the programs to the database. This unusual application architecture requires special static and dynamic validation techniques. In its conclusion, this study briefly sketches the history of the trigger concept and evaluates the benefits and disadvantages of its use in application development.

Keywords: ECA rules, trigger, business rules, active database, DAG, finding circuits, advanced SQL, data structure, check predicate, updatable view, derived data, inventory management, 2-tier architecture, 3-tier architecture

• Case 9. Temporal databases - Part 1, draft version, November 2020. [full text]

Objective: In this study we examine various ways to organize the data describing the evolution of a population of entities. The basic model consists in storing the successive states of each entity, completed by the time period during which the state was observable. We distinguish between the transaction time, that refers to the data modification time in the database and the valid time, referring to modification events of entities in the real world. This study particularly develops entity-based, attribute-based, event-based and document-oriented temporal database models. In these models, data management is ensured by triggers that automate as far as possible entity creation, modification and deletion operations.

The next study will be devoted to temporal database querying and transformation.

Keywords: temporal data type, temporal database, active databases, history, entity type, time point, time period, time interval, evolution, event, state, transaction time, valid time, bitemporal data, julian day, document-oriented model, JSON, trigger

• Case 10. Temporal databases - Part 2, draft version, August 27, 2019. [full text]

Objective: In this second part of the study of temporal data, we examine the various ways to query and transform them. We first examine simple temporal and non temporal queries, providing themselves temporal and non temporal results. Then, we extend to the temporal dimension the main families of queries of standard, non temporal, SQL: projection (entity-based and generalized), inner join and outer join, aggregation (count, max, min, average, sum). We also describe the SQLfast temporal library LTemp that offers a series of operators intended to write concise and efficient temporal scripts. The various temporal data models described in part 1 are revisited in order to develop conversion algorithms from one model to each of the other ones. Finally, we address the problem of performance by comparing the various algorithms of the temporal operators. To get realistic execution time measures we apply these algorithms to larger temporal databases. The last section is devoted to a short description of the SQL:2011 standard, that introduces some (but not all) concepts of temporal databases.

Keywords: temporal relations, temporal query, temporal projection, coalescing, temporal inner join, temporal outer join, temporal aggregation, stable interval, temporal data model conversion, temporal operator performance, SQL:2011, LTemp library

• Case 11. Kings of France - Part 1, draft version, September 10, 2017. [full text]

Objective: This study describes the French royal dynasty since Hughes Capet in 941. Its underlying goal is to study some properties and algorithms of widespread tree data structures. This first document of a series of two analyzes the dynasty of Kings of France, stores it in a database and extracts some simple information from it. The next study will be devoted to the derivation of more complex information.

Keywords: genealogy, tree, cyclic data structure, interval, ordering relation, temporal query, de Morgan law.

• Case 12. Kings of France - Part 2, draft version, September 10, 2017. [full text] [technical complement]

Objective: In this chapter, we continue the exploitation of the KINGS database through more advanced tree processing applications, based notably on recursive scripts. The central concept from which most of these applications will derive is the transitive closure of table BRANCH, which comprises all direct and indirect ancestor/descendant couples. From it, we will build queries that count the descendants of a member, others that display the hierarchy of these descendants in various graphical way and a transitive reduction query that recovers the contents of table MEMBER from its closure. The last application, tree projection, extracts from table MEMBER a subset in which only kings appear..

Keywords: genealogy, tree, cyclic data structure, transitive closure, transitive reduction, tree projection, recursive CTE, recursive query, tree drawing, tree traversal, depth-first traversal, breadth-first traversal, SQLdraw.

• Case 14. The book of which you are the hero, draft version, November 2020. [full text]

Objective: Game books are traditional text-based adventure games made up of a collection of pages (episodes) connected by references (branches). An episode comprises a text that describes a situation or an action and one or several branches that allow the gamer to jump to other episodes. Many of them are now available as pdf or html documents. In this study, we implement a simple game engine that automates such game books. This engine is based on a game database that can also be used to automatically generate stories.

Actually, this project is a nice opportunity to examine in some detail the concept of graph (a game book basically is a set of nodes and a set of edges) and to develop exploration and transformation algorithms. In particular, we study the structure of a game graph, we identify its abnomalies, we extract its circuits, we build and count the different possible runs from the starting episode to an exit episode, we search for unreachable episodes and dead-end branches and we identify episodes that can be merged.

A representative heroic fantasy game book has been encoded and all the algorithms developed in the study are provided as SQLfast scripts.

Keywords: computer game, game engine, story generation, graph, cyclic graph, acyclic graph, graph transformation, Marimont algorithm, reachability, circuit, elementary circuit, transitive closure, cyclic kernel, set comparison.

• Case 15. Directory management, draft version. November 2020. [full text].

Objective: The contents of storage media, such as hard disks and flash disks, both internal and external, are organized into a hierarchical structure made up of directories and files.

This chapter shows that, when such structures are stored in a database, processes can be designed easily to examine directories, to analyze their contents, to describe their evolution and to discover potential problems. In particular, small applications will be developed to extract statistics, to display the structure and contents of a directory, do identify and describe potentially duplicate files and directories within a root directory or between two directories.

The problem of fast clone detection, that is, of set of files that have exactly the same contents, is also analyzed and solved.

Keywords: directory structure, tree modeling, tree analysis, statistics, tree evolution, duplicate files, clone detection, secure hashing, SHA256, database performance, CTE, recursive queries.

• Case 27. Conway's Game of Life, draft version, November 2020. [full text]

Objective: This study is about games, worlds, life and death, borderline SQL applications and dramatic database optimization. The goal of the project is to implement the graphical animation of Conway’s cellular automata, aka Game of Life. A game of life is made up of an infinite array of cells in which live a population of small animals, each of them occupying one cell. The transition of one state of the population to the next one is specified by a set of simple computing rules. The goal of the game is to observe and study the evolution of the population. A game of life is implemented as a table in a database in which each row contains the coordinates and the content of a cell. The algorithms developed in this study load the initial state of a population then compute the next states thanks to the evolution rules. Finally, they visualize this evolution as an animated cartoon. The contribution of this study is twofold. It stresses the importance of database and algorithm optimization (the last version is 1,400 times faster than the first one) and it shows that relational databases and SQL may be quite efficient to develop matrix manipulation procedures (the SQL version is nearly 7 times faster than the equivalent Python program).

This study is also a tribute to E. F. Codd, the inventor of the relational model of databases, who first studied self-replicating cellular automata.

Keywords: cellular automata, replicating system, Conway, glider, Codd, matrix manipulation, algorithm optimization, database optimization, declarative algorithm, table indexing, in-memory database, CTE, recursive query, vector graphics, SQLdraw, animated simulation, Python.

• Case 28. From data bulk loading to database book writing, draft version, November 2020. [full text]

Objective: When data have to be loaded in a database from an external source, the order in which tables are filled is important as far as referential integrity is concerned. This order is determined by the directed graph formed by tables and foreign keys. From this graph one have to derive a linear ordering that represent one of the valid order in which table data are loaded. This derivation is called topological sorting, for which this chapter discusses and implements a simple algorithm. However, things are a bit more complex when the graph is not acyclic, as is often the case for database loading. Therefore, the chapter studies ways to transform a graph that includes circuits into a purely acyclic graph. These techniques are also applied to the ordering of topics when planning the writing of a book.

Keywords: data loading, database schema, (non) acyclic graph, topological sorting, strongly connected components, graph contraction, condensation of a graph, transaction management.

• Case 30. Classifying objects, draft version. June 2023. [full text].

Objective: In this study, we explore a particular way of classifying objects based on their attributes. This technique, called Formal Concept Analysis, or FCA for short, examines the composition of these objects and extracts concepts, that is, classes of objects that share the same set of attributes. By considering the inclusion relationship of the concept object sets, the concepts can be organized as a hierarchy.

Several techniques have been designed to extract concepts from a set of source objects and to build their hierarchy. We analyze the reasoning underlying these techniques and we develop one of the most popular of them, the Chein algorithm. We first translate this iterative algorithm into a Python procedure then we express it as an SQL script.

We propose a third, much simpler and faster technique that produces a remarkable subset of the Chein concept hierarchy. It appears that this technique, which can be coded as a single SQL query or in a small Python procedure, is more appropriate to database schema processing, specifically to conceptual schema normalization and to reverse engineering legacy databases.

The study develops four parametric applications to experiment with these algorithms and to evaluate their performance in time and space.

Keywords: symbolic classification, Formal Concept Analysis (FCA), Galois lattice, set operators, performance evaluation, database optimization, algorithm optimization.

• Case 31. Path finders, rovers and Ariadne's thread, draft version. November 2020. [full text].

Objective: This chapter tackles a widespread optimization problem: computing the shortest path between two cities. The solving technique is based on Dijkstra’s algorithm. It also is applied to two similar applications domains, namely maze solving and controlling a rover on a hostile planet. A general purpose, application independent, solving tool is developed.

Keywords: optimization, shortest path, Dijkstra’s algorithm, maze solving, rover control.

• Case 34. Blockchains, draft version. November 2020. [full text].

Objective: In this study, we examine some fundamental aspects of blockchains, particularly the security of data and the way(s) it is achieved through cryptographic transformations. Basically, a blockchain is a historical database in which the description of operations, generally called transactions, are stored in chronological order. Once recorded, the data of a transaction can never be deleted nor modified.

The document first introduces the elements of cryptography necessary to build a blockchain, notably secure hashing, and symmetric and asymmetric key encryption. Then, it describes the distinctive aspects of blockchains independently of its application domain and applies them to cryptocurrencies. Finally an experimental toolbox, comprising a collection of functions designed to manage and explore blockchains, is built step by step.

Keywords: blockchain, blockchain explorer, proof of work, distributed database, cryptocurrency, trust, security, cryptography, RSA, AES, secure hashing.

Case studies in preparation

• Case 2. Managing a small library: The human factor, writing in progress.
• Case 34. Agent modeling and simulation, writing in progress.

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]