Isok -- Query Based Data Integrity Managment For PostgreSQLIsok -- Query Based Data Integrity Managment For PostgreSQL
Why Use Isok?Why Use Isok?
How Isok WorksHow Isok Works
A Start-To-Finish Set of ExamplesA Start-To-Finish Set of Examples
The OS SideThe OS Side???TITLE???1???TITLE???2???TITLE???3???TITLE???4???TITLE???5
Database SetupDatabase Setup???TITLE???1
Our First QueryOur First Query???TITLE???1???TITLE???2???TITLE???3???TITLE???4???TITLE???5???TITLE???6???TITLE???7
Resolving WarningsResolving Warnings???TITLE???1???TITLE???2???TITLE???3
A Query That Looks For Missing CountriesA Query That Looks For Missing Countries???TITLE???1???TITLE???2???TITLE???3
InstallationInstallation
RequirementsRequirements
Quick-StartQuick-Start
Normal InstallNormal Install
SQL InstallSQL Install
Preparing, While Logged-In to Un*xPreparing, While Logged-In to Un*x
Re-Building to Disable FeaturesRe-Building to Disable Features
Building for and Installing in The Cloud (Installing From SQL)Building for and Installing in The Cloud (Installing From SQL)
Installing in the PostgreSQL Server's OSInstalling in the PostgreSQL Server's OS
Running Regression TestsRunning Regression Tests
Loading Into PostgreSQLLoading Into PostgreSQL
UninstallingUninstalling
Uninstalling From the OSUninstalling From the OS
Uninstalling From PostgreSQLUninstalling From PostgreSQL
An Overview of the TablesAn Overview of the TablesThe Isok TablesTable 1The Isok Support TablesTable 2Key To Entity Relationship DiagramsFigure 1Isok Entity Relationship DiagramFigure 2
The Main TablesThe Main Tables???TITLE???The Main Tables
ISOK_QUERIESISOK_QUERIESInserting a query into ISOK_QUERIES using dollar quotingExample 1
IQName (Isok Query Name)IQName
ErrorError
TypeType
First_RunFirst_Run
Last_RunLast_Run
Keep (Keep old results)Keep
RoleRole
Search_PathSearch_Path
QueryQuery
The first returned column, the ID columnID
The second returned column, the Msg columnMsg
The third returned column, the Extra JSON columnExtra
CommentComment
ISOK_RESULTS (Isok query Results)ISOK_RESULTS???TITLE???ISOK_RESULTS (Isok query Results)???TITLE???ISOK_RESULTS (Isok query Results)
IRID (Integrity Results Identifier)IRID
IQName (Integrity Query Name)IQName
First_SeenFirst_Seen
Last_SeenLast_Seen
Last_RoleLast_Role
Last_SchemasLast_Schemas
Resolved (Date and Time Resolved)Resolved
Deferred_ToDeferred_To
CategoryCategory
Keep_UntilKeep_Until
QR_ID (Query Result IDentifier)QR_ID
QR_Message (Query Result Message)QR_Message
QR_Extra (Query Result Extra JSON data)QR_Extra
NotesNotes
The Support TablesThe Support Tables
IQ_TYPES (Integrity Query Types)IQ_TYPES
Key: IQTypeIQTypeKey: IQTypeIQType
IR_TYPES (Isok Result Types)IR_TYPES
Key: IRTypeIRTypeKey: IRTypeIRType
The Functions (Running Isok)The Functions
run_isok_queriesrun_isok_queries()
InputInput
iqname_queryiqname_query
DescriptionDescription
Query Execution OrderQuery Execution Order
The Record of Query ExecutionThe Record of Query Execution
Deletion of Old ResultsDeletion of Old Results
ExamplesExamplesExecuting all ISOK_QUERIESExample 2Executing a single ISOK_QUERIES.QueryExample 3Executing many specific ISOK_QUERIES.Query-sExample 4Executing ISOK_QUERIES of the “bdate” typeExample 5
Security ConsiderationsAppendix A
Limiting AccessLimiting Access
What Queries Access MattersWhat Queries Access Matters
The Search PathThe Search Path
RolesRoles
Mitigation StrategiesMitigation Strategies
Creating an Audit TrailCreating an Audit Trail
Frequently Asked QuestionsAppendix B
Frequently Asked QuestionsFrequently Asked Questions
Q:Q:
Local Copies of the DocumentationAppendix CFinding the Documentation of Locally Installed ExtensionsExample C.1
Periodic ExecutionAppendix D
Example Periodic Reporting via Email Using systemdExample Periodic Reporting via Email Using systemdSample /etc/systemd/system/isok_report.service FileExample D.1???TITLE???1???TITLE???2???TITLE???3???TITLE???4???TITLE???5???TITLE???6Sample /etc/systemd/system/isok_report.timer FileExample D.2???TITLE???1???TITLE???2???TITLE???3Sample /usr/local/bin/pg_isok_report FileExample D.3???TITLE???1
Techniques For Making Local Extensions to IsokAppendix E
Wrap run_isok_queries()Wrap run_isok_queries()
Extend Issue ClassificationExtend Issue Classification
Fully Utilize ISOK_RESULTS.QR_ExtraFully Utilize ISOK_RESULTS.QR_Extra
Modify Isok's Generated SQLModify Isok's Generated SQL
DevelopingAppendix F
Tool RequirementsTool Requirements
Building and DistributingBuilding and Distributing
Acknowledgments and HistoryAppendix G
The Gombe Mother-Infant Project AcknowledgmentsThe Gombe Mother-Infant Project Acknowledgments
The Babase AcknowledgmentsThe Babase Acknowledgments
The SokweDB AcknowledgmentsThe SokweDB Acknowledgments
Licensing Terms -- Licensed Under The AGPL v3.0+ (Examples Excepted)Appendix H
GNU Affero General Public License version 3 Appendix I Preamble the section called “ Preamble ” 0. Definitions. the section called “ 0. Definitions. ” 1. Source Code. the section called “ 1. Source Code. ” 2. Basic Permissions. the section called “ 2. Basic Permissions. ” 3. Protecting Users’ Legal Rights From Anti-Circumvention Law. the section called “ 3. Protecting Users’ Legal Rights From Anti-Circumvention Law. ” 4. Conveying Verbatim Copies. the section called “ 4. Conveying Verbatim Copies. ” 5. Conveying Modified Source Versions. the section called “ 5. Conveying Modified Source Versions. ” 6. Conveying Non-Source Forms. the section called “ 6. Conveying Non-Source Forms. ” 7. Additional Terms. the section called “ 7. Additional Terms. ” 8. Termination. the section called “ 8. Termination. ” 9. Acceptance Not Required for Having Copies. the section called “ 9. Acceptance Not Required for Having Copies. ” 10. Automatic Licensing of Downstream Recipients. the section called “ 10. Automatic Licensing of Downstream Recipients. ” 11. Patents. the section called “ 11. Patents. ” 12. No Surrender of Others’ Freedom. the section called “ 12. No Surrender of Others’ Freedom. ” 13. Remote Network Interaction; Use with the GNU General Public License. the section called “ 13. Remote Network Interaction; Use with the GNU General Public License. ” 14. Revised Versions of this License. the section called “ 14. Revised Versions of this License. ” 15. Disclaimer of Warranty. the section called “ 15. Disclaimer of Warranty. ” 16. Limitation of Liability. the section called “ 16. Limitation of Liability. ” 17. Interpretation of Sections 15 and 16. the section called “ 17. Interpretation of Sections 15 and 16. ” How to Apply These Terms to Your New Programs the section called “ How to Apply These Terms to Your New Programs ”
CC0 1.0 Universal DeedAppendix J
No CopyrightNo Copyright
Other InformationOther Information
NoticeNotice
CC0 1.0 Universal Appendix K