The Unsealing of...

1. The Unsealing of... SQL our friend - a non competetive approach

2. Overview • SQL introduction, features • Comparison • „Arctica“ overview • Pass-Through-SQL • Universal SQL • Client/Server solution • Summary

3. SQL Introduction SQL is better than sliced bread SQL will resolve all my problems SQL is the future SQL is better than xBase

4. SQL definition • SQL = Structured Query Language • Is a set of commands that lets you access a database • Is a standardinterface for many relational database systems • Comes with a simple command structure for data definition and data manipulation • SQL was intended to be used with programming languages • SQL does not have user-interface or formatting features • In SQL you specify what you want and nothow you do it.

5. SQL in a broader sense! • SQL is Client/Server • You install a SQL server somewhere • You access it via ODBC or its native SQL API remotely • Typical features of a SQL server are: • Transaction management • Dictionaries • Referential integrity and constraints • Attributes associated with SQL are: • Reliability – because its client/server • Performance/Scaleability - is a myth! • Security – because data is accessed only through the server

6. Zooming into... SELECT * FROM CUSTOMER WHERE CITY=‚Berlin‘ SQL Processor (takes the SQL command and transforms it) aSelect := {ALL,{CUSTOMER},{CITY=‚Berlin‘}} SQL Server Planner (creates a plan from sql statement) USE CUSTOMER GO TOP DO WHILE !EOF IF(FIELD->CITY==„Berlin“) ADD RECORD TO RESULT ENDDO Optimizer (checks for indexes and rewrites plan) USE CUSTOMER INDEX CITIES SEEK „Berlin“ DO WHILE City==„Berlin“ ADD RECORD TO RESULT ENDDO Executor (executes plan) Storage Manager (handles Tables and Indexes) Warning: This is a simplified illustration some details are omitted -

7. Comparing languages! With xBase commands you express how you get it. The what is „hidden“ in your logic and coding-style. Your code depends on all details of your data model. SELECT * FROM CUSTOMER WHERE CITY=‚Berlin‘ USE CUSTOMER GO TOP DO WHILE !EOF IF(FIELD->CITY==„Berlin“) ADD RECORD TO RESULT ENDDO USE CUSTOMER INDEX CITIES SEEK „Berlin“ DO WHILE City==„Berlin“ ADD RECORD TO RESULT ENDDO In SQL you express what you want! The how is determined dynamically depending in your concrete data-model and its state.

8. Comparing apples with...

9. The best from both worlds! „Arctica“ objectives and goals

10. „Arctica“ technologies

11. Pass-Through SQL Pass-Through SQL

12. SQL Statements supported are: As language extension SELECT, INSERT, UPDATE and DELETE Specific features are: Automated syntax adaption LOCAL and STATIC variables can be used in SQL statements In fact SQL Pass-Through-Statements are first class language statements, lets see... Pass-Through SQL • Pass-Through SQL CodingTime

13. How does Pass-Through SQL work? • Pass-Through SQL

14. Pass-Through SQL • Pass-Through SQL • Allows you to write SQL statements for remoteSQL Servers in your source code • The compiler does assist you with syntax and semantic verification • You can use variables inside your SQL statements no error prone string concatenation such as it is with other development tools. • SQL Resultsets are Workareas with all the resulting flexibility and intergration into the Xbase++ language • With Pass-Through SQL, SQL becomes a part of the Xbase++ language • Pass-Through SQL is supported by the ADS, ODBC and PostgreSQL DatabaseEngine.

15. Pass-Through SQL Universal SQL

16. SQL Statements supported are: SELECT, INSERT, UPDATE and DELETE Specific feature are: Anything is executed on the client side – its a embedded SQL Engine for various datasources such as DBF Tables or Arrays of Objects.... Any Xbase++ language element including your user-defined functions or objects and methods can be used. No-Limits! Universal SQL • Universal SQL CodingTime

17. U-SQL data path U-SQL-SELECT ... • Universal SQL

18. Leaves optimization to the data-model, sofar the execution engine can decide to re-use indexes or even creates specific ones on the fly. Intelligently caches data, of sub-selects leading to dramatic I/O reduction when performing queries on DBF tables. You can compare it with loading your tables into arrays and then performing your calculation in memory. You express the what – the how is left to the SQL Engine leading to dynamic adaption of performance to your hardware – Dual-Core, Quad-Core Performance to the maximum! • Universal SQL

19. SQL Pass-Through SQL becomes a first class language member, no more dealing with strings to build your command. Ideal to query a set of information from a remote data-source. Resultsets are workares with all their language integration such as FIELD variables, navigational behaviour ... Universal SQL Most application developers deal with data-transformation and data-shuffeling from the data-source to the user-interface. With USQL a single declarative statement creates a ready to use workarea, array, object or a collection of objects which can directly used by the user-interface. Datasources can be workareas, objects, array or your own Data-Provider leading to maximum of flexibility in data-processing USQL is local, allows to offload complex calculations from the server to the client leading to a better workload distribution. Benefits and positioning

20. ! Until now we have talked about productivity enhancements of the data-access and data-management parts of Xbase++!

21. ? Lets talk about the future Client/Server solution at your users sites.

22. The PostgreSQL Server Version 8.2/8.3 A DatabaseEngine which can emulate 100% of the Xbase++ DBF/FOX/CDX/NTX DatabaseEngine features A Datatype extension for the PostgreSQL server to behave exactly as any Xbase++ datatype. A migration wizard to migrate your existing DBF based data model to the PostgreSQL server A SQL Pass-Through Engine to provide 100% transparent access to the PostgreSQL server PostgreSQL procedural language extension adding Xbase++ as a language for stored procedure development. „Arctica“ Client/Server solution

23. Full SQL 92/99 compliant Fully ACID compliant, Referential Integrity, Replication, Rules, Views, Triggers, Unicode, Sequences, Outer Joins, Sub-selects, Stored Procedures, Native SSL support, Procedural languages, MVCC instead of row-level locking, Functional and Partial indexes, Native Kerberos authentication, Support for UNION, UNION ALL and EXCEPT queries, Extensible data type system providing for custom, user-defined datatypes. Special features such as Full text search including ranking of search results (like google!) Geo-Spatial data, you can search for customers located around 100 miles of your location In fact we have evaluated many different SQL Server solutions in the market and ended up with the PostgreSQL due to its reputation, relaibility, featureset and its design. Feature overview PostgreSQL Server

24. Old Clipper 87 application, evolutionary migrated and extended with Xbase++. Uses using the FOXCDX and DBFNTX DatabaseEngines. With a single line of code the application uses the SQL server instead of local DBF/CDX tables User-define function in index Collation tables Lets see it... CodingTime

25. „Arctica“ client/server overview • No limits • Can be mixed with Navigational access • Perfect for large scale solutions SQL Pass-Through • Perfect for migration of existing solutions to a Client/Server platform. • Supports all navigational features Navigational access using USE, SEEK,INDEX,GO, TO TOP - Stored Procedures in Xbase++ PostgreSQL server PQDBE ISAM layer Compiled-Xbase++-Code

26. „Arctica“ client/server solution • Any Xbase++ application using DBFCDX or DBFNTX files can become a 100% SQL client/server solution by literally adding a single line of code: the connection ! • Existing Xbase++ applications can scale up to thousands of users without performance problems. • New Xbase++ solutions can take the best from the navigational and/or SQL world • Xbase++ developers can move their business-logic to the server using Stored-Procedures written in Xbase++ • Connecting different sites becomes a snap: PostgreSQL can use uses TCP/IP and SSL between client and server • No-Limits!

27. We won‘t give any date yet, but: With ServiceLevel #1 SQL Pass-Through for ADS SQL Pass-Through for PostgreSQL follows immediatly or at the same time. PQDBE with navigational support and migration wizard next step after Pass-Through is released Universal SQL needs Version 2.0 runtime, Community Preview as soon as it is ready. Availability

28. SQL integrated into the Xbase++ language and adding the what does increase productivity, performance and makes application less error prone. SQL and Navigational Data-Access are benefiting from each other. As experienced SQL Developers know: SQL SELECT statements can become heavily complex Being able to post process the resultset with navigational commands can remove complexity and add flexibility Moving logic to the SQL Server does introduce a single bootleneek and may lead to heavy server workloads Distributing the workload between client and server is the key for future performance. The embedded U-SQL Engine fits perfectly into that picture. We believe that

29. With the „Arctica“ Database-Technologies Xbase++ becomes the Object Oriented & Relational Database Application Development tool. With Universal SQL any Xbase++ application can benefit from the „embeeded SQL“ engine and can make use of the what approach. With „Arctica“ client/server, existing Xbase++ solutions become first class SQL client/server applications almost without code changes. With SQL Pass-Through development of pure SQL solutions integrated into the navigational power of the Xbase++ language Summary