Detailed Software Design: CVSQL

1. CVSQL Detailed software design

2. Introduction Today, we will not talk about wacuum cleaners 

3. Current project status • All required parts of the project are done within the planned schedule • The current status of the project is: OK • There are no problems in the project (including cultural )

4. Current project status

5. Current project status

6. Programming interface • For the development we’ve used 3 different programing language: • JAVA – JDBC driver • C++ – SQL validator & SQL parser • C – server, GUI, CVS library • CVS for source code exchange • Email, Netmeeting and Skype for text, program sharing and voice communication

7. Todays topics: How things work? • JDBC CVSQL driver • XML Bridge / Server • SQL Parser • CVS Library • Error handling • GUI – We’ve talked about that last time 

8. Project structure

9. JDBC CVSQL driver • Current progress: • the main structure is defined • the programming has began • Planned progrees: • to define main structure • to start the programming • Current status: • OK

10. JDBC CVSQL driver - structure Important functions: • CVSQLDriver • constructor • CVSQLConnection • Establishes the connection • CVSQLPreparedStatement • Defines the SQL query template • CVSQLCallableStatement • Defines the final SQL query • CVSQLStatement • Executes the SQL Query • CVSQLResultSet • Returns the result of the SQL Query

11. XML Bridge / Server • Current progress: • the simulator is finished • the development of the real server is in progress • Planned progrees: • to finish simulator • the developing of the real server should begin • Current status: • OK

12. XML Bridge / Server - structure Important parts: • LOGIN username • the client introduces • PASSWORD password • the client authorizes • SQL Statement • the client send one or more SQL statements and recieves the XML reply for each of the queries • … • QUIT • the client sends quit command and terminates the connection with the XML Bridge / server application

13. Transaction example Server: <db> <result> <value>0</value> <text>OK</text> </result> <definition> <coldef id="1"> <type>int</type> <name>id</name> </coldef> <coldef id="2"> <type>text</type> <name>username</name> </coldef> </definition> <values> <row> <value col="1">3</value> <value col="2">shakira</value> </row> <row> <value col="1">5</value> <value col="2">britney</value> </row> </values> </db> Client: quit Server: <resp> <number>0</number> <text>OK</text> </resp> Client: login mylogin Server: <resp> <number>0</number> <text>OK</text> </resp> Client: password mysecret Server: <resp> <number>0</number> <text>OK</text> </resp> Client: SELECT * FROM table;

14. XML Bridge / Server – DTD Server response: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <!DOCTYPE resp [ <!ELEMENT number (#PCDATA)> <!ELEMENT text (#PCDATA)> ]> SQL Query response: <?xml version="1.0" encoding="UTF-8"?> <!ELEMENT db (result, definition?, values?)> <!ELEMENT result (value, text)> <!ELEMENT value (#PCDATA)> <!ELEMENT text (#PCDATA)> <!ELEMENT definition (coldef*)> <!ELEMENT coldef (type, name)> <!ELEMENT type (#PCDATA)> <!ELEMENT name (#PCDATA)> <!ATTLIST coldef id CDATA #REQUIRED > <!ELEMENT values (row*)> <!ELEMENT row (value*)> <!ELEMENT value (#PCDATA)> <!ATTLIST value col CDATA #REQUIRED >

15. SQL Parser • Current progress: • this part of the project is finished • Planned progrees: • this part of the project must be finished • Current status: • OK

16. SQL Parser - structure Important parts: • Initialization • Before using parser, all supported SQL commands and operators need to be defined using regular expressions. Regular expressions are also used to define identifiers, constants and white spaces. • Sending SQL query • SQL query is send using setText method. This method takes one parameter. It is a string that contains SQL query. • Starting the parser • Parser is started using method go. This method return object of class ParserResult. This object contains table of results and flag that shows was parser successful or error was encountered. • Reading results • After starting parser with method go() result is return as object of class ParserResult. Method getSuccess() returns bool value

17. CVS Library • Current progress: • this part of the project is almost finished • Planned progrees: • this part of the project must be finished by the end of this week • Current status: • OK

18. CVS Library – structure Important parts: • Recieving the CVS LOG request • Connecting to the CVS Repository • Authorising on the CVS Repository • Initialization of CVS transaction • Requesting the CVS command • Parsing the result data comparing it to the WHERE part of the SQL query • Returing the required data structure back to the calling function

19. CVS Library - transaction Client (CVS Library): /var/cvsroot/cvsql Argument main.c log Server: M M RCS file: /var/cvsroot/main.c,v M Working file: main.c M head: 2.9 M branch: M locks: strict M access list: M symbolic names: M keyword substitution: kv M total revisions: 9; selected revisions: 9 M description: M ---------------------------- M revision 2.9 M date: 2003/10/20 13:33:36; author: stomic; state: Exp; lines: +2 -1 M Final revision of the main file example for presentation … Client (CVS Library): BEGIN AUTH REQUEST /var/cvsroot stomic Fs2Ef$he END AUTH REQUEST Server: I LOVE YOU Client (CVS Library): Root /var/cvsroot Valid-responses ok error (…) valid-requests Server: Valid-requests Root (…) ok Client (CVS Library): UseUnchanged Argument -- Directory .

20. CVSQL Error handling • The special care is taken about the proper error handling • Each part of the project has two kinds of errors: • Critical • Non critical • The required error handling for both kind of errors is to try to recover from the error if possible • If it is not possible to recover the project part will follow the following guidelines:

21. CVSQL Error handling 1/3 • GUI non fatal • Present the user with the information message on the screen that explains what mistake is done and how to fix it • GUI fatal • Present the user with the information message on the screen that explains the mistake, write a execution log file entry that lists the user, time, and description of the message, exit the program • Command line non fatal • Try to start the GUI application to fix the problem, if not possible Present the user with the information message on the screen that explains what mistake is done and how to fix it and exit the program • Command line fatal • Present the user with the information message on the screen that explains what mistake is done and how to fix it and exit the program • Local SERVER non fatal • Return the XML response that explains what error occurred • Local SERVER fatal • Return the XML response that explains what, write a execution log file entry that lists all of the important data, try to do a simple restart, if not possible exit the program

22. CVSQL Error handling 2/3 • System SERVER fatal • Return the XML response that explains what, write a execution log file entry that lists all of the important data, try to do a simple restart, if not possible exit the program, notify by email server administrator that a fatal error occurred and put all of the important data regarding a failure in it • CVS Library non fatal • Return the appropriate return code to the calling function • CVS Library fatal • Return the appropriate return code to the calling function • SQL validator non fatal • Return the appropriate return code to the calling function • SQL validator fatal • Return the appropriate return code to the calling function • SQL parser non fatal • Return the appropriate return code to the calling function • SQL parser fatal • Return the appropriate return code to the calling function

23. CVSQL Error handling 3/3 • JDBC non fatal • Return the appropriate return code to the calling function • JDBC fatal • Return the appropriate return code to the calling function

24. GUI • Current progress: • define the command line interface • define the GUI interface • begin the GUI interface programming • Planned progrees: • all interfaces must be defined by the end of this week • the programming on the GUI interface should begin next week • Current status: • OK

25. GUI Structure • Two types of interface • Command line interface • Advanced users • Shell scripts • User GUI interface • Non advanced users • Easy software testing

26. Advanced user interface • -login username => provides the program with the CVSQL username • -password pass => provides the program with the CVSQL password • -db database => provides the program with the selected database (CVS repository) • -sql "sql query" => provides the program with the requested SQL query command • -output file => redirects the output into a file instead of the screen • -delimiter "delim" => specifies that the user would like the delim to be used as the delimiter instead of default one • -help => provides the user with the command line help cvsql –login mycvs –password secret –db myproject –sql "SELECT * FROM changes"

27. GUI Interface • The user is prompted for all requested values • User interface is similar to mysql (MySQL database client) and psql (PostgreSQL database client) • Full functionality as the command line with arguments interface and the XML Bridge / server interface • The user can ask for help at any time during the command line interface • general help for all commands • specific help for some command • the help for using the CVSQL client • short information about the: • CVSQL • CVS • SQL

28. What will you do for Christmas?