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 weve 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 Weve 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

• Client
• login mylogin
•  
• Server
• ltrespgt
• ltnumbergt0lt/numbergt
• lttextgtOKlt/textgt
• lt/respgt
•  
• Client
• password mysecret
• Server
• ltrespgt
• ltnumbergt0lt/numbergt
• lttextgtOKlt/textgt
• lt/respgt
• Client

Server ltdbgt ltresultgt ltvaluegt0lt/valuegt lttextgt
OKlt/textgt lt/resultgt ltdefinitiongt ltcoldef
id"1"gt lttypegtintlt/typegt ltnamegtidlt/namegt lt
/coldefgt ltcoldef id"2"gt lttypegttextlt/typegt
ltnamegtusernamelt/namegt lt/coldefgt lt/definitiongt
ltvaluesgt ltrowgt ltvalue col"1"gt3lt/valuegt lt
value col"2"gtshakiralt/valuegt lt/rowgt ltrowgt
ltvalue col"1"gt5lt/valuegt ltvalue
col"2"gtbritneylt/valuegt lt/rowgt
lt/valuesgt lt/dbgt    Client quit Server
ltrespgt ltnumbergt0lt/numbergt
lttextgtOKlt/textgt lt/respgt
14
XML Bridge / Server DTD
SQL Query response lt?xml version"1.0"
encoding"UTF-8"?gt lt!ELEMENT db (result,
definition?, values?)gt lt!ELEMENT result (value,
text)gt lt!ELEMENT value (PCDATA)gt lt!ELEMENT text
(PCDATA)gt lt!ELEMENT definition
(coldef)gt lt!ELEMENT coldef (type,
name)gt lt!ELEMENT type (PCDATA)gt lt!ELEMENT name
(PCDATA)gt lt!ATTLIST coldef id CDATA
REQUIRED gt lt!ELEMENT values (row)gt lt!ELEMENT
row (value)gt lt!ELEMENT value (PCDATA)gt lt!ATTLIST
value col CDATA REQUIRED gt

• Server response
• lt?xml version"1.0" encoding"UTF-8"
  standalone"yes"?gt
• lt!DOCTYPE resp
• lt!ELEMENT number (PCDATA)gt
• lt!ELEMENT text (PCDATA)gt
• gt

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

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 133336
author stomic state Exp lines 2 -1 M
Final revision of the main file example for
presentation
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 gt provides the program with the
  CVSQL username
• -password pass gt provides the program with the
  CVSQL password
• -db database gt provides the program with the
  selected database (CVS repository)
• -sql "sql query" gt provides the program with the
  requested SQL query command
• -output file gt redirects the output into a file
  instead of the screen
• -delimiter "delim" gt specifies that the user
  would like the delim to be used as the delimiter
  instead of default one
• -help gt 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?