LIBD:Outils

Version du 4 janvier 2015 à 18:46

OUTILS / TOOLS

Sommaire

1 SQLfast
2 DB-MAIN
3 TRAMIS/Master
4 ORGA
5 PHENIX
6 VisiComp
7 NDBS
8 SQL-Script Interpreter
9 SPHINX

SQLfast

Type: High-level database programming interface for non-experts
Description

SQLfast is a language and an environment 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 non-professionals. 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. A simple and intuitive 2-line SQLfast script can replace a full page Java/JDBC program that must be written by a programmer (Tutorial, Chapter 1, Section 1.5).

Despite its simplicity, the SQLfast language includes powerful features such as the full SQL language, script variables, GUI, comprehensive control structures (procedures, recursive calls, if-else, for-endfor, while-endwhile, exit, next, continue, stop, pause, goto etc.), a powerful text generator coupled with the select-from-where query, dynamic statement and script execution, external program interface, transactions, automatic metadata management, automatic database analysis, debugging, etc.

The SQLfast environment is particularly easy to install. Once the standard Python environment is available, installing the SQLfast system just requires copying a folder on the disk. 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, geographic information systems, photo album, text analysis, 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.

SQLfast runs on Windows, OS X and Linux machines. 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 extensibility: new functions, new resources and even new statements can be developed quite (or, at least, fairly) 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. It is developed in Python 2.7 and the basic 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.) and of graphical interactions, 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 ou GUI 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
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 subscripts (e.g., parameters setting),
functional subscripts (e.g., data loading, data checking, report generation),
Python external libraries (e.g., string manipulation, file management, geometric algorithms),
Python external applications (e.g., data import, interactive data conversion, complex dialogues, map digitizer,graphical engine),
language extension (through script precompiling)
external programs (web browsers, image processors, media processors, Office suite, etc.)

a builtin schema database (data dictionary)
reflexivity: an SQLfast script can generate, examine, modify and execute SQLfast scripts, including itself.
development and debugging tools

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.
Requirements: standard Python 2.7 distribution. A beta Windows version is available on request. Linux and Mac OS versions will be released soon. A MySQL interface is under development.

Date: 2012-
Status: ongoing
Availability: On request, free of charge. Expected release date of beta: 2015.

References and resources:
- SQLfast environment for Windows. Ask the beta version for testing.
- Python 2.7.8 Win32, Win64, Mac OS, Linux. Note that Mac OS systems natively include the Python environment. [download page]
- SQLite3 DBMS is already included in the Python installation. However, it can be useful to download the very last version (3.8.5 or later). Download it from [SQLite3]. Locate section Precompiled Binaries for Windows. In early 2014, the file was named sqlite-dll-win32-x86-3080500.zip. Open the archive and copy the sqlite.dll file in directory Python 2.7/DLLs of the Python directory.
- Jean-Luc Hainaut. Dictionary of SQLfast commands and parameters, 36 pages, PReCISE Research Center publication, draft version, December 4, 2014. [full text]
- Jean-Luc Hainaut. SQLfast: Database processing made easy - Tutorial and Case studies, 700 pages, PReCISE Research Center publication, draft version, May 2014.
- Many chapters of the tutorial are already available, but in a draft version only. They are continuously updated, so that it is recommended to check them regularly to get their last versions.

SQLfast Tutorial - 1st pages and Table of contents, draft version, 2014. [full text]

SQLfast Tutorial - 1. Introduction, draft version, October 8, 2014. [full text]

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

SQLfast Tutorial - 2. Basic scripts, draft version, June 2, 2014. [full text]

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

Statements and functions: openDB, closeDB, select, set, write, ask.

SQLfast Tutorial - 4. Creating a database, draft version, June 2, 2014. [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.

Statements and functions: createDB, createOrReplaceDB, create table, insert, commiDB, execSQL, createDB InMemory.

SQLfast Tutorial - 5. Updating data, draft version, June 2, 2014. [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.

Statements and functions: insert, delete, update, ask.

SQLfast Tutorial - 6. More on variables, draft version, March 7, 2014. [full text]

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

Statements and functions: date, time, start-timer, timer, create index, drop index, output, wait, parameter delimiters, set-n, substitution, compute, execSQL.

SQLfast Tutorial - 7. Taking decisions, draft version, June 2, 2014. [full text]

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

Statements and functions: if, if-endif, if-else-endif, showMessage, label, goto.

SQLfast Tutorial - 8. Reading data, draft version, June 2, 2014. [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.

Statements and functions: extract, select #n, select #[n1,n2], for-endfor, SQLdiag.

SQLfast Tutorial - 9. Looping - The final chapter, draft version, March 11, 2014. [full text]

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

Statements and functions: for-endfor, while-endwhile, next, exit.

SQLfast Tutorial - 10. SQLfast expressions, draft version, June 2, 2014. [full text]

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

Statements and functions: compute, if, while, execApp, function.

SQLfast Tutorial - 11. Output channels, draft version, June 2, 2014. [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.

Statements and functions: write, select, outputOpen, outputAppend, outcoding, outputtype, outputmode.

SQLfast Tutorial - 12. Dialogues, draft version, June 2, 2014. [full text]

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

Statements and functions: DIALOGbutton, ask/askData, parameter label-separator, ask-u, askText, askText-u, selectOne, selectOne-u, selectMany, selectMany-u, textwrap, stdTextX, stdTextY.

SQLfast Tutorial - 13. Displaying data, draft version, October 3, 2014. [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.

Statements and functions: showMessage, showData, showText, showPicture, showDrawing, maxPictureX, maxPictureY, help, fileToVariable.

SQLfast Tutorial - 14. Advanced dialogues, draft version, August 20, 2014. [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.

Statements and functions: [(B1,B2,B3)], [select-from-where], askCombo, style, color, entryLength.

SQLfast Tutorial - 15. Large data objects, draft version, March 3, 2014. [full text]

Chapter contents: .