# Welcome Welcome to Tribal Knowledge's official documentation and help resource. # Install Guide 1. [Download](https://www.tribalknowledge.tech/download-plain.html) the software from our site 2. Unzip the file. Place the folder labeled Tribal Knowledge any where on your C: drive. 3. That's it. You are done. If you have any problems you can: * Email us at # Introduction to Tribal Knowledge Knowledge Sharing Process Automation Software There are 4 programs that make up Tribal Knowledge: * Exemplar - Knowledge Sharing Code Review Tool * Ratifier - Data Validation Tool * Kulvert - CI/CD Pipeline for SQL Files * Redactor - Data Masking Tool # About Exemplar Exemplar – Knowledge Sharing Code Review Tool {% embed url="" %} Exemplar - 3 minute intro {% endembed %} Exemplar combines the power of code review and knowledge sharing to provide developers with a comprehensive solution to improve their coding skills. With its advanced technology, Exemplar is able to identify and alert you if you have forgotten to do something important in your code. Exemplar is designed to help developers streamline their coding process and ensure that their code is up to industry standards. Its combination of code review and knowledge sharing makes it easy for developers to get real-time feedback and suggestions from their peers. This not only helps to improve the quality of code, but also fosters collaboration and knowledge sharing among developers. With Exemplar, you no longer have to worry about missing critical steps or making costly mistakes in your code. Its powerful code analysis capabilities identify and highlight any potential issues or forgotten steps in your code, allowing you to address them before they become a problem. This saves you time and effort, and ensures that your code is of the highest quality. Exemplar is easy to use, with a user-friendly interface that makes it accessible to developers of all levels of expertise. Whether you’re a seasoned developer or just starting out, Exemplar is the perfect tool to help you improve your coding skills and ensure that your code is up to par. So why wait? Start using Exemplar today and see for yourself how it can help you improve your coding skills and streamline your development process. Say goodbye to forgotten steps and costly mistakes – with Exemplar, you’ll always be on top of your game. # About Ratifier Data Validation Tool ## SQL Simulator {% embed url="" %} See results of a command before executing it {% endembed %} With SQL simulator, you can test your SQL queries before you submit them to your database to ensure that they work and that you're updating the rows you intended. With a simple and user-friendly interface, you can easily tweak your queries until they're perfect and save yourself time and hassle. ## SQL Sanity Checker {% embed url="" %} Are you tired of wondering if your SQL updates are actually being applied to your database? Ratifier - SQL Sanity Checker has you covered! With the tool, you can verify if your SQL statements have been successfully applied to the database. Our tool will check if the number of updated rows matches the expected result, and also check if the data has changed as intended. You can easily validate your SQL changes, and be confident that your updates have been successfully applied. With Ratifier's SQL Sanity Checker, you can make sure your data is consistent and accurate, and avoid costly data errors. Try it today and streamline your data validation process!
# About Kulvert {% embed url="" %} Tired of having to submit SQL statements to your database, only to find out later that they don't work as expected? With the Kulvert, you can test your SQL statements before sending them to the database. Kulvert will check if the syntax is correct, and verify if the query will return the expected results. You can debug your code, fine-tune your SQL statements, and make sure your updates are only applied to the rows you intended. With Kulvert, you can avoid costly mistakes and save time, allowing you to focus on what really matters: analyzing your data. # About Redactor Coming Soon...... # Supported Languages Static Code Analysis * PLSQL * TSQL(comming soon) * Powerbuilder # Knowledge Sharing Code Review Database Change Request using PLSQL Tom in accounting complained to your boss about a very important report that was off by several million dollars. After researching the issue you find that Tom submitted a database change request that deleted records from Tables A and B but forgot to delete from table C. He also failed to insert into Table D and did not update table E. A code review was conducted by the team lead, but because he had not done that task in about 2 years he forgot to those other steps were necessary. Also there is no referential integrity on these tables. Your boss asks you to find a way to prevent this from happening in the future. You decide to use Tribal Knowledge and set up a check list to automate the code review process. # Create a New Project {% hint style="info" %} Prerequisites 1. [Download Tribal Knowledge](https://www.tribalknowledge.tech/download.html#/) 2. Install Tribal Knowledge {% endhint %} 3. Go to the Tribal Knowledge\Exemplar folder and double click on Exemplar.exe 4. Click "New Project" 5. Click "Create File" 6. Select the folder and type in a file name and click "Save" 7. Click "Create Project" # Create a CheckList * Go to the Jotlist folder and start Jotlist.exe * Create XML similar to the one below and save the file as dbcr\_reveiw\.cl. ```xml 12345 1.0 TABLEA TABLEB TABLEC TABLED TABLEE ``` * On Exemplar main screen click "Code Review" * Then Click on "Checklist" * Then Click "Add Files". Add the dbcr\_reveiw\.cl file and then click close. # Add Files for Code Review Create a text file with the name DBChangeRequest.plsql. Put the following text in the file: ```plsql delete from tableA; delete from tableB; delete from tableC; ``` * On Exemplar main screen click "Code Files" * Click "Add New Files" * Add DBChangeRequest.plsql file # Start Automated Code Review * On Exemplar main screen click "Code Review" * Click "Start" * You will get a message stating that "DMLGROUPEXISTS Checklist Trigger found that a insert SQL is missing for tableD" and "DMLGROUPEXISTS Checklist Trigger found that a update SQL is missing for tableE" * Fix the errors in the DBChangeRequest.plsql and rerun the code review. You should see that there are no more errors. # SQL Sanity Check In this tutorial we will set up tables and data in an oracle database and then run the SQL Sanity Checker to see if those values actually exist. # Oracle Setup Instructions for Oracle Setup # Oracle Database Setup Please run the following scripts in your oracle database. ```plsql CREATE TABLE employees ( employee_id int NOT NULL PRIMARY KEY, employee_ssn varchar2(9) NOT NULL, employee_first_name varchar2(50) NOT NULL, employee_last_name varchar2(50) NOT NULL, employee_address varchar2(50) NOT NULL, city varchar2(50), state varchar2(50), zip varchar2(50) ); CREATE TABLE payroll ( payroll_id int NOT NULL PRIMARY KEY, paycheck_status varchar2(1), account_number varchar2(20), employee_id int REFERENCES employees(employee_id) ); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (1,'123456789', 'MARTINEZ','RODRIGUEZ', '110 WEATHERSFIELD DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (2,'223466781', 'THOMAS','SMITH', '124 NEW CASTLE DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (3,'323476782', 'WHITE','LOPEZ', '364 LEIGH FARM RD','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (4,'423486783', 'HARRIS','WILLIAMS', '413 NOTTINGHAM DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (5,'523496784', 'jason','LEWIS', '5006 MILL HILL LN','chester','va','21111'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (8, 3, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (9, 4, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (10, 5, 'S','00001234'); ``` # Create SQL File for Sanity Check Create a text file with the name payrollmods.plsql. Put the following text in the file: ```plsql insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'V','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'R','00001234'); update payroll set paycheck_status = 'X' where payroll_id = 8; ``` # SQL Server Setup Coming Soon # SQL Server Database Setup Coming Soon # Copy of Create SQL File for Sanity Check Coming Soon # Create a New Project {% hint style="info" %} Prerequisites 1. [Download Tribal Knowledge](https://www.tribalknowledge.tech/download.html#/) 2. Install Tribal Knowledge {% endhint %} 3. Go to the Tribal Knowledge\Ratifier folder and double click on Exemplar.exe 4. Click "New Project" 5. Click "Create File" 6. Select the folder and type in a file name and click "Save" 7. Click "Create Project" # Add SQL File for Sanity Check * On Ratifier main screen click "Code Files" * Click "Add New Files" * Add payrollmods.plsql file # SQL Sanity Check - Datasource Config We need to configure the datasource for the SQL Sanity Checker. 1. On Ratifier main screen click SQL Sanity Check 2. Click on the Data Sources Button 3. Click Add 4. Click Add Oracle 5. Type in the connection information and then click save # SQL Sanity Check - Start Simply click the start button and review the results. # SQL Simulator Submitting SQL to production can be a nerve-wracking experience. With every click of the execute button(or command prompt command), a small voice in your head wonders if you’ve made a mistake that could cost you your job. Fortunately, there is a solution to this anxiety: a SQL simulator. This powerful tool simulates what would happen if you submitted your SQL to production, allowing you to feel confident and secure before actually executing the code. With a SQL simulator, you can test your code and make any necessary adjustments before it goes live. This ensures that you catch any potential problems or errors before they cause damage to your company’s production database. Not only does a SQL simulator help you catch mistakes, but it also gives you a better understanding of how your code will behave in a real-world environment. You can see exactly how your code will interact with the database, giving you a clear picture of what you can expect when you submit it to production. So, if you’re feeling anxious about submitting SQL to production, try using a SQL simulator. It can give you peace of mind, reduce the risk of errors, and help you deliver high-quality sqls to your database. # Technical Overview {% hint style="warning" %} This section assumes you have already completed the steps listed in either the [oracle setup](/ratifier-tutorials/sql-sanity-check/oracle-setup) or [sql server setup](/ratifier-tutorials/sql-simulator/sql-server-setup) guides. {% endhint %} {% hint style="info" %} The term "Simulated Database" just means the blank or empty schema you created during the setup process. {% endhint %} This section covers the technical overview for how the SQL Simulator works starting when you click on the "Simulate SQL" button. # Step 1 Static Code Analysis
SQL Simulator analyzes your SQL Script using static code analysis. It looks for any tables that are referenced. # Step 2 Get Table DDLs
It then goes to the production database and retrieves the DDL's for those tables. {% hint style="info" %} If there are any tables that are linked by a foreign key reference it will retrieve the DDL's for those automatically. {% endhint %} # Step 3 Create Tables
It then creates those tables in the "Simulated Database". {% hint style="info" %} Please note that the schema name that is used in the dev database is "simdb". This is done by using a "SQL Set Rewrite Parser" engine. {% endhint %} # Step 4 Copy Data
Using the "SQL Set Rewrite Parser" engine, SQL Simulator takes the SQL and rewrites it so that retrieves only the needed records from production and copies them to the simulated database. If there are any foreign key tables those tables will get populated automatically by the "SQL Set Rewrite Parser". {% hint style="danger" %} If you omit the where clause in your SQL then the entire table will be copied. {% endhint %} # Step 5 Execute SQL Script
Using the "SQL Set Rewrite Parser" engine again it takes your SQL Script, changes the schema name of any table and executes that against the simulated database. # Oracle Setup Instructions for Oracle Setup # Oracle Database Setup ## SQL Simulator Setup The first thing you will need to do is to set up blank schema for the SQL Simulator to run in. It is important that you set this blank schema up in a non production database. Use the below script for this task: ```plsql create user sql_simulator_1 identified by password; grant create session to sql_simulator_1; grant dba to sql_simulator_1; ``` Next please run the following in a different database than the one you used to create the sql\_simulator\_1 schema. ```plsql CREATE TABLE employees ( employee_id int NOT NULL PRIMARY KEY, employee_ssn varchar2(9) NOT NULL, employee_first_name varchar2(50) NOT NULL, employee_last_name varchar2(50) NOT NULL, employee_address varchar2(50) NOT NULL, city varchar2(50), state varchar2(50), zip varchar2(50) ); CREATE TABLE payroll ( payroll_id int NOT NULL PRIMARY KEY, paycheck_status varchar2(1), account_number varchar2(20), employee_id int REFERENCES employees(employee_id) ); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (1,'123456789', 'MARTINEZ','RODRIGUEZ', '110 WEATHERSFIELD DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (2,'223466781', 'THOMAS','SMITH', '124 NEW CASTLE DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (3,'323476782', 'WHITE','LOPEZ', '364 LEIGH FARM RD','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (4,'423486783', 'HARRIS','WILLIAMS', '413 NOTTINGHAM DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (5,'523496784', 'jason','LEWIS', '5006 MILL HILL LN','chester','va','21111'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (8, 3, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (9, 4, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (10, 5, 'S','00001234'); ``` # Create SQL File for SQL Simulator Create a text file with the name payrollinserts.plsql. Put the following text in the file: ```plsql insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'V','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'R','00001234'); ``` # Create a New Project {% hint style="info" %} Prerequisites 1. [Download Tribal Knowledge](https://www.tribalknowledge.tech/download.html#/) 2. Install Tribal Knowledge {% endhint %} 3. Go to the Tribal Knowledge\Ratifier folder and double click on Ratifier.exe 4. Click "New Project" 5. Click "Create File" 6. Select the folder and type in a file name and click "Save" 7. Click "Create Project" # Add SQL File for SQL Simulator * On Ratifier main screen click "Code Files" * Click "Add New Files" * Add insertrecs.plsql file # Datasource Config We need to configure the datasource for the SQL Simulator. 1. On Ratifier main screen click SQL Simulator 2. Click on the Data Sources Button 3. Click Add 4. Click Add Oracle 5. There are 2 different datasources that you must fill in the connection information for. The source datasource is the database you test your SQL file against. The Simulated database is the database that contains the sql\_simulator\_1 schema that you set up earlier in this tutorial. In the schema name text box be sure to type in "sql\_simulator\_1". {% hint style="danger" %} Please make sure you have a value in the simulated schema name text box blank unless you are using an empty database. This tool adds/drops tables. {% endhint %} # Start Simulation Click on the Simulate SQLs button to run the sql simulator and review the results. # SQL Server Setup # SQL Server Database Setup ## SQL Simulator Setup The first thing you will need to do is to set up blank schema for the SQL Simulator to run in. It is important that you set this blank schema up in a non production database. Use the below script for this task: ```sql CREATE LOGIN sql_simulator_1 WITH PASSWORD = '12345'; GO CREATE USER sql_simulator_1 FOR LOGIN sql_simulator_1; GO CREATE SCHEMA sql_simulator_1; GO ``` Next please run the following in a different database than the one you used to create the sql\_simulator\_1 schema. ```plsql CREATE TABLE employees ( employee_id int NOT NULL PRIMARY KEY, employee_ssn varchar(9) NOT NULL, employee_first_name varchar(50) NOT NULL, employee_last_name varchar(50) NOT NULL, employee_address varchar(50) NOT NULL, city varchar(50), state varchar(50), zip varchar(50) ); CREATE TABLE payroll ( payroll_id int NOT NULL PRIMARY KEY, paycheck_status varchar(1), account_number varchar(20), employee_id int REFERENCES employees(employee_id) ); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (1,'123456789', 'MARTINEZ','RODRIGUEZ', '110 WEATHERSFIELD DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (2,'223466781', 'THOMAS','SMITH', '124 NEW CASTLE DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (3,'323476782', 'WHITE','LOPEZ', '364 LEIGH FARM RD','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (4,'423486783', 'HARRIS','WILLIAMS', '413 NOTTINGHAM DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (5,'523496784', 'jason','LEWIS', '5006 MILL HILL LN','chester','va','21111'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (8, 3, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (9, 4, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (10, 5, 'S','00001234'); ``` # Create SQL File for SQL Simulator Create a text file with the name payrollinserts.tsql. Put the following text in the file: ```plsql insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'V','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'R','00001234'); ``` # Create a New Project {% hint style="info" %} Prerequisites 1. [Download Tribal Knowledge](https://www.tribalknowledge.tech/download.html#/) 2. Install Tribal Knowledge {% endhint %} 3. Go to the Tribal Knowledge\Ratifier folder and double click on Ratifier.exe 4. Click "New Project" 5. Click "Create File" 6. Select the folder and type in a file name and click "Save" 7. Click "Create Project" # Add SQL File for SQL Simulator * On Ratifier main screen click "Code Files" * Click "Add New Files" * Add insertrecs.tsql file # Datasource Config We need to configure the datasource for the SQL Simulator. 1. On Ratifier main screen click SQL Simulator 2. Click on the Data Sources Button 3. Click Add 4. Click Add (Oracle/SQL Server) 5. There are 2 different datasources that you must fill in the connection information for. The source datasource is the database you test your SQL file against. The Simulated database is the database that contains the sql\_simulator\_1 schema that you set up earlier in this tutorial. In the schema name text box be sure to type in "sql\_simulator\_1". {% hint style="danger" %} Please make sure you have a value in the simulated schema name text box blank unless you are using an empty database. This tool adds/drops tables. {% endhint %} # Start Simulation Click on the Simulate SQLs button to run the sql simulator and review the results. # CI/CD Pipeline # Oracle Setup Instructions for Oracle Setup # Oracle Database Setup ## SQL Simulator Setup The first thing you will need to do is to set up blank schema for the SQL Simulator to run in. It is important that you set this blank schema up in a non production database. Use the below script for this task: ```plsql create user sql_simulator_1 identified by password; grant create session to sql_simulator_1; grant dba to sql_simulator_1; ``` Next please run the following in a different database than the one you used to create the sql\_simulator\_1 schema. ```plsql CREATE TABLE employees ( employee_id int NOT NULL PRIMARY KEY, employee_ssn varchar2(9) NOT NULL, employee_first_name varchar2(50) NOT NULL, employee_last_name varchar2(50) NOT NULL, employee_address varchar2(50) NOT NULL, city varchar2(50), state varchar2(50), zip varchar2(50) ); CREATE TABLE payroll ( payroll_id int NOT NULL PRIMARY KEY, paycheck_status varchar2(1), account_number varchar2(20), employee_id int REFERENCES employees(employee_id) ); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (1,'123456789', 'MARTINEZ','RODRIGUEZ', '110 WEATHERSFIELD DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (2,'223466781', 'THOMAS','SMITH', '124 NEW CASTLE DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (3,'323476782', 'WHITE','LOPEZ', '364 LEIGH FARM RD','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (4,'423486783', 'HARRIS','WILLIAMS', '413 NOTTINGHAM DR','chester','va','21111'); insert into employees (employee_id, employee_ssn, employee_first_name, employee_last_name, employee_address, city, state, zip) values (5,'523496784', 'jason','LEWIS', '5006 MILL HILL LN','chester','va','21111'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (8, 3, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (9, 4, 'S','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (10, 5, 'S','00001234'); ``` # Create SQL File for Kulvert Create a text file with the name payrollinserts.plsql. Put the following text in the file: ```plsql insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (6, 1, 'V','00001234'); insert into payroll (payroll_id, employee_id, paycheck_status, account_number) values (7, 2, 'R','00001234'); udpate payroll set paycheck_status = 'X' where payroll_id = 8; ``` # SQL Server Setup Coming Soon # SQL Server Database Setup Coming Soon # Create SQL File for SQL simulator Coming Soon # Create a New Project {% hint style="info" %} Prerequisites 1. [Download Tribal Knowledge](https://www.tribalknowledge.tech/download.html#/) 2. Install Tribal Knowledge {% endhint %} 3. Go to the Tribal Knowledge\Kulvert folder and double click on Exemplar.exe 4. Click "New Project" 5. Click "Create File" 6. Select the folder and type in a file name and click "Save" 7. Click "Create Project" # Add SQL File for Kulvert * On Kulvert main screen click "Code Files" * Click "Add New Files" * Add insertrecs.plsql file # Kulvert - Datasource Config We need to configure the datasource for the SQL Simulator. 1. On Kulvert main screen click Data Sources 2. Click Add 3. Click Add Oracle 4. There are 2 different datasources that you must fill in the connection information for. The target datasource is the database you SQL file will update. The Simulated database is the database that contains the sql\_simulator\_1 schema that you set up earlier in this tutorial. In the schema name text box be sure to type in "sql\_simulator\_1". {% hint style="danger" %} Please note that if you are doing any type of dynamic sql then you will need to use a blank database database and leave the simulated schema name text box blank. {% endhint %} # Kulvert - PipeLine Start On the Main Screen click Pipeline and then click start and then review the output. # About Redactor Information coming soon # About Tribal Knowledge Go to the [website](https://docs.tribalknowledge.tech/about/www.tribalknowledge.tch)