CVSQL2 System Overview: Modular SQL Parsing and Execution

1. The Design CVSQL 2

2. System Overview

3. System Components • CVSQL Server • Three network interfaces • Modular data source provider framework • Decoupled SQL parsing and execution • Client utility • Uses new XML-RPC network interface • JDBC driver • Uses updated CVSQL2 XML network interface

4. CVSQL Server

5. CVSQL Server • Networking component • CVSQL1 and CVSQL2 XML protocol support • XML-RPC protocol support • SQL parser • Parses the SQL query string • SQL engine • Executes the query • Data Provider modules • CVS log data provider • Palantir event provider • Server configuration and control

6. Networking • CVSQL XML protocol support • Legacy software support • CVSQL2 extension – “protocol 2.0” • response byte count report • used only on client's explicit request • XML-RPC support • Uses available third-party XML-RPC C++ implementation (XmlRpcPP) • Desired network interface selection is done via configuration file • Only one network interface active at any moment

7. SQL Parser • Completely rewritten from ground-up • Modular • Configurable lexical analyzer • Recursive-descent syntax parser • Validates the string representation of SQL query and transforms it into SQLQuery object • SQLQuery object contains: • SQLQuerySelection – description of selected columns • SQLQueryTables – a list of data sources for the query • SQLQueryExpression – a selection filter for the data • GroupBy, OrderBy – data ordering and grouping

8. SQL Engine • Executes the SQL query • Works on ResultSet objects • join, filter • The result is also a ResultSet object • Supported data types: • String, DateTime, Integer, Decimal, Null • Database independent • Table list and structure provided by the SQLDatabase object

9. Data Providers • Gather data from external sources • CVS log, Palantir event log • Present the data as ResultSet objects • Unnamed objects – only the SQLDatabase object knows table names and their references to table providers • Have intimate knowledge of data structure • data fields, column types • Register upon program start • module type, reference to object factory • Created by database separately for each table • Take additional options to control data gathering

10. Server Configuration • Server configuration file example: <?xml version="1.0"?> <cvsql> <authentication> <user login="jdoe” info="John Doe" pwd="1f6fc7c0d324b0e4a7d7c278e2b3a5f4"/> </authentication> <server type="cvsql" port="9090"/> <!-- <server type="xmlrpc" port="80"/> --> <databases> <database path="/etc/cvsql/db/cvsql2.xml"/> </databases> </cvsql>

11. Database Configuration • Database configuration file example: <?xml version="1.0"?> <cvsql_database name="cvsql2" log="/var/log/cvsql/cvsql2.log"> <users> <user login="senko"/> </users> <table name="users" type="cvsql" path="/var/lib/cvs/cvsql2/"> <option name="table" value="users"/> <option name="ttl" value="60"/> <option name="cvs" value="/usr/bin/cvs"/> </table> </cvsql_database>

12. The Client • Simple command-line utility • Operation: • Get the query string input from the user • Send the query to the server using XMLRPC • Obtain the result in XML format and present it back to the user • Uses third party XML-RPC library (XmlRpcPP)

13. JDBC Driver • CVSQLAPI • Contains all I/O logic and XML parsing • Send() CVSQLServerResponse • Throws CVSQLException • CVSQLException for I/O or general error • CVSQLProtocolException for XML errors . • CVSQLServerResponse • Encapsulates the three important XML elements • resp / result • definition • values

14. JDBC Driver (cont'd) • Handling XML Chunks • CVSQL XML protocol sends chunks, not documents • Multiple root elements • resp for non-data/error • db for row-data • Proposed solution • Protocol "upgradability" • InputStream wrapper

15. JDBC Driver (cont'd) • Protocol Upgradability • Server assumes v1.0 protocol for compatibility • Client requests protocol upgrade • Sends "protocol 2.0" command • Occurs at any time • Protocol switch takes effect AFTER server's response

16. JDBC Driver (cont'd) • XML Protocol 2.0 • Root XML elements prefixed with • XML element byte length in ASCII decimal • ASCII newline (0x0a) • Example >>> protocol 2.0 <<< <resp> <number>0</number> <text>OK</text> </resp> >>> login user <<< 41 <resp> ... </resp>

17. JDBC Driver (cont'd) • InputStream Wrapper • XMLElementInputStream extends InputStream • Knowledge of expected XML chunk length (data-length) • Remembers bytes read from input stream since last reset() (read-bytes) • Behavior • InHeader read() returns bytes from the XML processing header (<?xml version=...) • InData && read-bytes < data-length read() calls are passed directly • InData && read-bytes >= data-length read() returns EOF (-1)

18. JDBC Driver (cont'd) • CVSQLResultSet • Version 2.0 adds constructor for static ResultSet, not created from server response • Allows for use within CVSQLDatabaseMetaData and CVSQLResultSetMetaData classes • CVSQLDriver • Accepts URLs containing sub-protocol "cvsql" • Example jdbc:cvsql@server:port • Version 1.0 code needs to be verified safe with malformed URLs

19. JDBC Driver (cont'd) • CVSQLStatement • Result set data is obtained during the call to execute. • In version 1.0, protocol exceptions are not detected until a call to getResultSet • In version 2.0, exceptions are thrown in execute for bad queries as well as I/O errors . • CVSQLDatabaseMetaData • Version 1.0 used static values for limited meta-data support • Version 2.0 uses the CVSQL Server v2.0 DESCRIBE command to obtain database info • Limited only by data returned by DATABASE query

20. JDBC Driver (cont'd) • CVSQLConnection • Version 1.0 needs review and rework • Work carefully with the Properties object to avoid RuntimeExceptions

21. Project Status • Requirements defined • except for Palantir – still in the research phase • System design completed • We're ready to code • work already started on SQL parser and engine • Overall status: On Track! ;-) • Questions? Thank you CVSQL 2 Team