Database Interface

Overview

This page documents the proposal for the structure and content of pkg/database to achieve the following goals:

  • Reduce the friction when adding support to a new DBMS(Database Management System).

  • Enable overriding the default read/write operations for a specific DBMS.

  • Expose the filters (WHERE clauses) and sorters (ORDER BY clauses) separately from the rest of the SQL statements so they can be plugged in and out of the different queries.

  • Reduce the maintenance of the codebase.

  • Increase the readability of the codebase.

Proposal

The two pillars of this proposal are:

  • Definition of interfaces that need to be implementing when adding support to a new DBMS.

  • Usage of a SQL Builder.

Database Interfaces

To add a new DBMS the following interfaces must be implemented:

DBReader

Include the methods that should be used by KubeArchive API. Encapsulates the business logic related to the different read database operations: QueryResources, QueryLogUrls, etc.

DBWriter

Include the methods that should be used by KubeArchive sink. Encapsulates the business logic related to the different write database operations: WriteResources, WriteUrls, etc.

Database

Composed of DBReader and DBWriter

DBCreator

Include the methods GetDriverName() and GetConnectionString(). They are used for establishing the database connection in the DBInterface constructor.

DBSelector

Encapsulate the different SELECT SQL statements without the WHERE and ORDER BY clauses.

DBFilter

Encapsulate the different WHERE SQL clauses that can be used in SELECT, DELETE or UPDATE statements.

DBSorter

Encapsulate the different ORDER BY clauses.

DBInserter

Encapsulate the different INSERT SQL statements.

DBDeleter

Encapsulate the different DELETE SQL statement without the WHERE clauses.

Additional requirements for new engine

  • Add a Database implementation to the RegisteredDatabases map assigned to a key with the driver name.

SQL Builder

The go-sqlbuilder library is used to combine the different SQL pieces into one.

This library supports:

  • Building parametrized queries based on different database flavors.

  • Adding custom SQL functions that can be specific of a DBMS.

SQL Builders in Database Interfaces

go-sqlbuilder expose different sqlbuilders:

  • SelectBuilder

  • InsertBuilder

  • DeleteBuilder

Combined with the Database Interfaces the following guidelines should be applied:

  • All the exposed DBSelector functions should return a SelectBuilder

  • All the exposed DBInserter functions should return a InsertBuilder

  • All the exposed DBDeleter functions should return a DeleteBuilder

ORDER BY clauses are only allowed in SELECT statements so:

  • All the exposed DBSorter functions should receive a SelectBuilder and return it properly modified.

WHERE clauses are allowed in several sqlbuilders but all of them include a Cond object that keeps track of the different arguments and expressions involved so:

  • All the exposed DBFilter functions should receive a Cond object of a sqlbuilder and should be used to create the returned expression as a string. Some filters that are expected to be more complex also have a sqlbuilder.WhereClause as a parameter.

Common code

Using go-sqlbuilder allows the creation of a lot of SQL statements in the same way for different DBMS.

To prevent having duplicated code, along with some of the interfaces a total or a partial implementation of the methods that don’t depend on specific DBMS functions will be provided.

Then the DBMS implementation can inherit from the Partials and skip some of the method implementations unless overwriting them is required.

Currently supported engines

KubeArchive currently supports the sql engines PostgreSQL and MariaDB. Examples of those implementations can be found in the pkg/database go package.