SeeQR

<div align="center"> <img src="./assets/readmeImages/logo_readme.png" height=300/>

seeqrapp.com

<p>SeeQR is a <b>convenient one-stop shop</b> for efficient SQL database manipulation and performance testing. SeeQR can be used throughout the database lifecycle, from creation to testing.</p> </div>

For the latest in-depth docs for v14.0.0, please visit our docs site.

Table of Contents

• Table of Contents
• Getting Started
• Built With
• Interface & Features
• Application Architecture and Logic
• Contributing
• Core Team
• License

Getting Started

To get started on contributing and editing databases to this project:

1. Download and install Postgres to access SeeQR's Postgres features and/or MySQL to access its MySQL features.
2. Ensure that psql and/or mysql are available in the $PATH.
3. Create users with passwords and permissions for both PostgreSQL and MySQL. Linked are instructions for Mac (Homebrew) and Linux/WSL.
4. Download the latest version of SeeQR.

Built With

• Electron
• React
• React-Redux
• Typescript
• PostgreSQL
• MySQL
• styled-components
• Material-UI
• React-Flow
• Chart.js
• Faker.js
• Monaco
• React-Force-Graph
• Three.js
• D3.js
• Three-Spritetext

Interface & Features

• Overview
  • Upon application launch, navigate to the config menu by clicking on the gear icon on the top left.
  • Ensure that your usernames, passwords, and ports are accurate in the SeeQR config.
  • Besides using the existing databases, the application also provides various options to create new databases:
    • Importing .sql or .tar files.
    • Navigating to the Create Database view at bottom of sidebar.
    • Copying an existing database (with or without original data).
  • Users can export any database onto their local machine.
  • Users can toggle between the 'DATABASES' view and the 'QUERIES' view.
  • Users can toggle between an 'ER DIAGRAM' view and the 'TABLES' view for each database.

<div align="center"> <img src="./assets/readmeImages/gifs/login.gif" width=800/> <img src="./assets/readmeImages/gifs/importCreateDB.gif" width=800/> </div>

• Databases

  • In the 'DATABASES' view, an interactive Entity Relationship Diagram (ER DIAGRAM) is displayed for the selected database.

    • Users can now save table layout in version 13. <br>

      <div align="center"> <img src="./assets/readmeImages/gifs/save_db_layout.gif" width=800/> </div>

  • Users can select TABLE to see selected database in tabular form.

    • Users can select a table from a list of all the tables in the schema of the currently selected database.

      • Information about the selected table is then displayed.

      • The name and size of the selected database are also displayed at the top of the page.

      • Users can also generate large amounts of foreign-key compliant dummy data for the selected table in the current database. Currently supported data types are:

        • INT

        • BIGINT

        • VARCHAR

        • BOOLEAN

        • DATE

          <br> <div align="center"> <img src="./assets/readmeImages/gifs/dataTypeChange.gif" width=800/> </div>
        <br>

• 3D Database Visualization

  • From the 'DATABASES' view, select the '3D View' tab on the far right of the sidebar to launch the brand new 3D database visualizer.
    • Individual table nodes are distinguished by their different colors
    • Column nodes are joined to table nodes and match their source table color
    • Individual tables related by foreign keys are connected through their respective foreign and primary keys
  • Navigate through the 3D space using left click to rotate, scroll wheel to zoom, and right click to pan.
  • Click and drag nodes to visually organize them
  • Click on a table or column node to quickly view the data stored within <br>
  <div align="center"> <img src="./assets/readmeImages/gifs/3dhome.gif" width=800/> </div> <br> <div align="center"> <img src="./assets/readmeImages/gifs/3dfk.gif" width=800/> </div> <br> <div align="center"> <img src="./assets/readmeImages/gifs/3dtables.gif" width=800/> </div>

<br>

• Create/Edit Database

  • Users can create a new database from scratch by clicking the Create New Database button at the bottom of the sidebar.
  • Users can modify the newly created database as well as any existing databases using the ER Diagram to create/change/delete tables and columns.
  • The Export button will write a .sql file on the user's desktop of the selected database.

<br> <div align="center"> <img src="./assets/readmeImages/gifs/export.gif" width=800/> </div>

• Queries

  • In the 'QUERIES' view, the main panel is where the query input text field is located, utilizing Monaco. The paint button in the top right corner of the panel auto-formats the inputted query.

  • Users can select the database to use in the 'Database' dropdown above the main panel.

  • Users also have the option to execute a labelled/grouped or unlabelled/ungrouped query — simply provide a label/group in the 'Label'/'Group' field above the main panel to identify the query in later comparisons against other queries.

    • Please note that only labelled queries will be saved in the current session for future references.

  • To execute the query, simply select the 'RUN QUERY' button at the bottom of the panel or press 'Ctrl-Enter' on the keyboard.

  • Users have the option to run multiple queries, allowing users to obtain more reliable testing results.

  • Version 13 introduces a new feature that enables users to access and view previous queries. Upon selecting a previous query, it populates the query input field, allowing users to make edits before executing. </br> <br>

    <div align="center"> <img src="./assets/readmeImages/gifs/query.gif" width=800/>
  </div> <br />

• Save/Load Queries

  • In the 'QUERIES' view, the file upload icon will open a file explorer window to select a .JSON to import query data from.
  • The file icon to the right of the upload icon will designate the file path to save query data to if you press the save button on the queries.
  • To save individual query data press the save icon on the individual queries in the dropdowns.
  <br /> <div align="center"> <img src="./assets/readmeImages/gifs/Save_Load_Queries.gif" width=800/> </div>

• Data

  • Once executed, the query's output will be displayed. In addition, for eligible queries, users will be able to view the queries' planning time, execution time, total run time, and plan of execution.
    • Eligible queries include any SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS statement.
  • Users can toggle between the executed query's 'RESULTS' and 'EXECUTION PLAN'.
  • The 'RESULTS' view displays the executed query's returned results.
  • The 'EXECUTION PLAN' view displays the executed query's plan of execution.
    • Within the 'EXECUTION PLAN', users can adjust the thresholds of 'Percentage of Total Duration' and the 'Planner Rows Accuracy' that are used to highlight certain nodes in the tree.
      • The 'Percentage of Total Duration' threshold is used to highlight the nodes whose durations are higher than the set limit, indicating that these nodes may be areas of improvement.
      • The 'Planner Rows Accuracy' threshold is used to highlight the nodes for which the planner's estimate number of rows differs from the actual number of rows, indicating that the database might need vacuuming.
    • Clicking on a node will display additional details regarding that action as well.
  • To execute a new query, simply select the '+' button in the sidebar. To go back to a previously saved query, just select it in the sidebar.
  <br /> <div align="center"> <img src="./assets/readmeImages/gifs/Query_Exec_Plan.gif" width=800/> </div>

• Compare

  • Click on the 'bar graph' icon at the top of the sidebar to get to the 'Compare Queries' view.
  • The comparison table is flexible to the user’s preferences as the user selects which queries to compare side by side.
  • Simply check or uncheck the box next to each saved query to add or remove the query from the graph.
  • Graph will be organized along the x-