HFSQL

SQL HFSQL supports the ANSI SQL 92 standard. The SQL supported by HFSQL also accepts a large number of additional and specific syntax for SQL Server and Oracle, among others. HFSQL supports sub-queries and nested queries. HFSQL supports union operators (union, cartesian, join, external join), aggregation operators (count, sum, aug, min, max, mean, variance), sort and group operators: (group by, having, order by)... The speed of the SQL engine is optimized: It uses the most discriminating index for the queries. The advanced management of memory caches also improves performance. The engine automatically performs load balancing. If a client executes a large num- ber of queries requiring a lot of resources (CPU, ...), the server automatically bal- ances the load in order not to penalize the other clients. Simultaneously with the SQL code, you can benefit from the functional richness of the WLanguage 5GL. The direct use of WLanguage functions and the call to stored procedures (devel- oped in WLanguage themselves) are possi- ble in your applications. You’ll find at the end of this document the list of SQL functions supported by HFSQL, as well as other programming information (cursor programming). FEATURES HFSQL offers a large number of features. You’ll find the description of the main fea- tures below. The entire online help for HFSQL is available on Internet at doc.windev.com SEVERAL DATABASES ON THE SAME SERVER HFSQL classic supports the presence of multiple databases on the same server. The databases are isolated. Specific rights can be defined on each database. This avoids having to use several servers. AUTOMATIC DATA MODIFICATION (DSS) Which developer hasn’t complained about having to write quick and dirty hack to add a column or increase its size, add an index to an existing table or change the type of data in a column? Writing those scripts is always tricky be- cause they alter the data. With HFSQL these will be things of the past! HFSQL manages the evolution of the data schema transparently thanks to the DSS (Data Schema Synchronization) technol- ogy. No more “hack jobs”! No more scripts! No more risky “Alter table” commands! DSS automatically performs: • The comparison and synchronization of the database structure and data against the ref- erence schema • The addition, deletion or renaming of columns • The change of type, size • The addition/deletion of key/index, addition/dele- tion of constraints • The addition/deletion of triggers and stored pro- cedures. DSS can also be started via command line or pro- gramming. This DSS feature can be executed live (hot), with- out disconnecting the users, transparently, with- out interfering with the applications run- ning. DSS: Automatic update of the data schema (also called Auto. Modif.) INTEGRITY: CONSTRAINTS, DELETIONS, CASCADING UPDATE It is easy to define integrity constraints. The cardinalities can be configured: (0,n); (0,1); (1,n); (3,n); etc. Reflexive links are supported. Constraints examples: • Referential integrity: referential integrity will prevent an author from being deleted, as long as the data- base contains at least one book referring to this author. You cannot delete a row in a table if this row is linked to other table rows. For ex- ample: you cannot delete a customer if there are orders linked to this customer. The referential integrity can be defined for each link, from the data model editor. • Cascading deletion: If a row is deleted in a table, the corre- sponding rows in the linked tables are also deleted (this constraint can be enabled or disabled for each relationship). HFSQL • www.windev.com 7 SELECT Product . Name AS Name , SUM( OrderLine . Quantity ) AS Quantity_Sum , Customer . ZipCode AS ZipCode FROM Product LEFT OUTER JOIN ( ( Customer INNER JOIN Order ON Customer . IDCustomer = Orders . IDCustomer ) INNER JOIN OrderLine ON Orders . IDOrders = OrderLine . IDOrders ) ON Product . Reference = OrderLine . Reference WHERE Customer . ZipCode LIKE ‘34%’ GROUP BY Product . Name , Customer . ZipCode