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 standard interface 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 not how 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 CITYBerlin
SQL Processor (takes the SQL command and
transforms it)
aSelect ALL,CUSTOMER,CITYBerlin
SQL Server
Planner (creates a plan from sql statement)
USE CUSTOMER GO TOP DO WHILE !EOF
IF(FIELD-gtCITYBerlin) ADD RECORD TO
RESULT ENDDO
Optimizer (checks for indexes and rewrites plan)
USE CUSTOMER INDEX CITIES SEEK Berlin DO WHILE
CityBerlin 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 CITYBerlin
USE CUSTOMER GO TOP DO WHILE !EOF
IF(FIELD-gtCITYBerlin) ADD RECORD TO
RESULT ENDDO
USE CUSTOMER INDEX CITIES SEEK Berlin DO WHILE
CityBerlin 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
12
Pass-Through SQL

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

CodingTime
13
How does Pass-Through SQL work?
14
Pass-Through SQL

• Allows you to write SQL statements for remote SQL
  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
Universal SQL
16
Universal SQL

• 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!

CodingTime
17
U-SQL data path
U-SQL-SELECT ...
18
Performance to the maximum!

• 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

19
Benefits and positioning

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

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
Arctica Client/Server solution

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

23
Feature overview PostgreSQL Server

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

24
Lets see it...

• 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

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
Availability

• We wont 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.

28
We believe that

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

29
Summary

• 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

30
(No Transcript)