JSQL

Universal Database Manager

Description

JSQL is a Java GUI app allowing user to establish connection with and manage MS SQL and MySQL database engines. It was created in Java 8 and JavaFX technologies and uses SQLjdbc library for DB communication. It creates a dynamic view of the selected table and provides a simple CRUD interface. JSQL requires user to have an existing account in the target database with proper privileges. In overall, it is a lightweight, intuitive environment, available to everyone.

Table of contents

Authors
Licence
Requirements
Applicative requirements
Non-applicative requirements
Core components
Tests
Test scenarios
Test summary

Authors

1. Jakub Sarnowski (https://github.com/jsarnowski96)
2. Damian Szymański

Licence

JSQL is being distributed under MIT licence. https://opensource.org/licenses/mit-license.php

Requirements (Launch architecture)

• Existing account on either MS SQL or MySQL server instance
• (for MS SQL) enabled Windows SQL Server Authentication mode
• Java software (JRE, JDK for development purposes; version 8)

Applicative requirements

1. User authentication module:
a) User must select DB engine and insert DB user credentials. After successfull authentication user is moved to Table Selection module.
b) Input - DB engine selection, DB user credentials (username/password)
c) Output - Table Selection view
2. DB/Table selection:
a) After selecting database, target table and pressing "Select" button, user is forwarded to Table Overview presenting the information from the specified table which allows for full CRUD operations
b) Input - DB/Table selection
c) Output - View with contents of the selected table
3. Deleting row:
a) User clicks specific cell within the table which he wants to delete and after loading the initial data from the selected row clicks "Delete" button. If operation finished successfully, he is presented with popup success window. In case of failure and/or empty row selected, the popup displays proper error message.
b) Input - selecting row for deletion and pressing "Delete" button
c) Output - success/error popup message box
4. Updating row:
a) User clicks specific cell within the table which he wants to update and after loading the initial data and replacing specified text fields with new values clicks "Update" button. If operation finished successfully, he is presented with popup confirmation window. In case of failure and/or empty row selected, the popup displays proper error message.
b) Input - wybór pola warunkującego i kliknięcie przycisku, aktualizacja danych i wciśnięcie przycisku "Save" c) Output - success/error popup message box
5. Inserting row
a) From the dropdown menu "Actions" user clicks "Add new row". After that, a set of text fields will appear in the bottom part of the window - its structure depends on target table's columns. In order to add a new row to the table, user must click "Insert row" button. If operation finished successfully, he is presented with popup confirmation window. In case of failure and/or empty fields, the popup displays proper error message.
b) Input - navigating to Actions -> Add new row, inserting data into newly rendered text fields and clicking "Insert row" button
c) Output - success/error popup message box
6. Logging out:
a) In order to logout, user must navigate to File -> Logout menu item. After pressing it the current DB session will be closed and user will be redirected back to Login screen.
b) Input - navigating to File -> Logout and pressing button
c) Output - Login screen
7. "About" view:
a) From Table Selection view user can navigate to File -> About. After clicking the menu item the new window will show up, displaying details about project authors/repository
b) Input - navigating to File -> About
c) Output - "About" view

Non-applicative requirements

1. Application is designed for a single user session
   
2. JSQL is a lightweight GUI application which consumes a minor amount of system resources
   
3. Application is free to use and its source code is open to public. Distributed under MIT licence conditions.
   
4. Windows 10 or older required.
   
5. Official app's launch date is set to June 2nd 2020
   
6. Simple and intuitive interface available to everyone - no special skills required in order to use JSQL.
   

Core components

• Views:
  
  • LoginFrame - intercepts user input and invokes proper DB connection methods dependant on the selected DB engine
    
  • TableSelectFrame - allows user to select database and table he wants to display
    
  • TableOverviewFrame - main view for logged user allowing him to perform various CRUD operations
    
  • AboutFrame - displays basic information about project's authors and repository
    
• Classes:
  
  • AppInit - core class which stores some of the DB connection's data globally, as well as all the view instances
    
  • DataBaseSelectModel - stores information about user selection in TableSelectFrame which are externally used later on by TableOverviewFrame
    
  • MSSQLConnection - contains methods used to establish a connection with MS SQL server instance
    
  • MSSQLTransactions - contains declarations of methods performing MS SQL CRUD operations
    
  • MySQLConnection - contains methods used to establish a connection with MySQL server instance
    
  • MySQLTransactions - contains declarations of methods performing MySQL CRUD operations
    
• Models:
  
  • DbInfo - for future use
    
  • EnginesEnum - used in major part of methods executing CRUD operations - it determines whether JSQL should use libraries for MS SQL or MySQL engine
    
  • TableInfo - for future use
    

Tests

Test scenarios

1. User authentication:
   
   • MS SQL:
     a) LoginFrame -> enter correct username and password -> enter correct server name/address -> select MS SQL engine -> press "Login" button
     b) LoginFrame -> enter correct username and password -> enter incorrect server name/address -> select MS SQL engine -> press "Login" button
     c) LoginFrame -> enter incorrect username and password -> enter correct server name/address -> select MS SQL engine -> press "Login" button
     d) LoginFrame -> enter incorrect username and password -> enter incorrect server name/address -> select MS SQL engine -> press "Login" button
     
   • MySQL:
     a) LoginFrame -> enter correct username and password -> enter correct server name/address -> select MySQL engine -> press "Login" button
     b) LoginFrame -> enter correct username and password -> enter incorrect server name/address -> select MySQL engine -> press "Login" button
     c) LoginFrame -> enter incorrect username and password -> enter correct server name/address -> select MySQL engine -> press "Login" button
     d) LoginFrame -> enter incorrect username and password -> enter incorrect server name/address -> select MS SQL engine -> press "Login" button
     
2. Database/Table selection:<br/ >
   • MS SQL:
     a) TableSelectFrame -> select database -> select table -> press "Select" button
     b) TableSelectFrame -> select database -> skip table selection -> press "Select" button
     
   • MySQL:
     a) TableSelectFrame -> select database -> select table -> press "Select" button
     b) TableSelectFrame -> select database -> skip table selection -> press "Select" button
     
3. Table overview frame
   
   • Delete (Applicative requirements 3.):
     a) Select row -> click "Delete"
   • Insert (Applicative requirements 5.):
     a) Menu "Actions" -> Add new row -> Fill data without ID and foreign key(s) -> Press "Insert row" b) Menu "Actions" -> Add new row -> Fill data with ID and foreign key(s) - Press "Insert row" c) Menu "Actions" -> Add new row -> Press "Insert row" e) Menu "Actions" -> Add new row -> Fill with SQL script ("delete from tableName") -> Press "Insert"
   • Update (Applicative requirements 4.) a) Select row -> Fill data without ID and foreign key(s) -> Press "Update" b) Select row -> Fill data with ID and foreign key(s) -> Press "Update" c) Select row -> Clear all fields -> Press "Update" d) Select row -> Fill with invalid data -> Press "Update" e) Select row -> Fill with SQL script ("delete from tableName") -> Press "Update"
4. Logging out
   a) Menu "File" -> Press "Logout"

Test summary

Status - POSITIVE

Test range

JSQLApp

Version

1.0.0

Bugs/errors reported in version

9

Bugs/errors fixed in version

8

ClientBugs reported in version

0

Moved to next version

1 Login with incorrect credentials/server address -> currently no fallback available.

Recipient WZR UG

UML diagrams

Login UML

Login Sequence

Table Select UML

Table Overview UML