sqlclient

package
v0.4.2 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 17, 2025 License: Apache-2.0 Imports: 10 Imported by: 0

Documentation

Overview

Package sqlclient provides a structured interface for interacting with the ClusterIQ database. This package is designed exclusively for use by the API layer to enforce architectural boundaries, ensure data integrity, and centralize all database-related operations, including transactions, queries, and audit logging.

Index

Constants

View Source 
const (
	SelectScheduledActionsQueryConditionsPlaceholder = "<CONDITIONS>"

	// SelectScheduledActionsQuery returns the list of scheduled actions on the inventory with all the parameters needed for action execution
	// ARRAY_AGG is used for joining every instance on the same row
	SelectScheduledActionsQuery = `
		SELECT
			schedule.id,
			schedule.type,
		  schedule.time,
		  schedule.cron_exp,
			schedule.operation,
			schedule.status,
			schedule.enabled,
			clusters.id AS cluster_id,
			clusters.region,
			clusters.account_name,
		ARRAY_AGG(instances.id::TEXT) FILTER (WHERE instances IS NOT NULL) AS instances
		FROM schedule
		JOIN clusters ON schedule.target = clusters.id
		JOIN instances ON clusters.id = instances.cluster_id
		` + SelectScheduledActionsQueryConditionsPlaceholder + `
		GROUP BY
			schedule.id,
			schedule.time,
			schedule.operation,
			clusters.id
		ORDER BY
			id ASC
`

	// EnableActionQuery enables the action to be re-scheduled on next agent polling
	EnableActionQuery = `
		UPDATE
			schedule
		SET
			enabled = true
		WHERE id = $1
`

	// DisableActionQuery disables the action to don't be re-scheduled on next agent polling
	DisableActionQuery = `
		UPDATE
			schedule
		SET
			enabled = false
		WHERE id = $1
`

	// SelectScheduledActionByIDQuery returns scheduled action on the inventory for a specific ID
	SelectScheduledActionsByIDQuery = `` /* 558-byte string literal not displayed */

	// InsertScheduledActionQuery inserts new scheduled actions on the DB
	InsertScheduledActionsQuery = `` /* 191-byte string literal not displayed */

	// InsertCronActionQuery inserts new Cron actions on the DB
	InsertCronActionsQuery = `` /* 199-byte string literal not displayed */

	// PatchScheduledActionsQuery
	PatchScheduledActionsQuery = `` /* 147-byte string literal not displayed */

	// PatchCronActionsQuery
	PatchCronActionsQuery = `` /* 155-byte string literal not displayed */

	// PatchActionStatusQuery
	PatchActionStatusQuery = `
		UPDATE
			schedule
		SET
			status = $2,
			enabled = $3
		WHERE
			id = $1
	`

	// DeleteScheduledActionQuery
	DeleteScheduledActionsQuery = `DELETE FROM schedule WHERE id=$1`

	// SelectExpensesQuery returns every expense in the inventory ordered by instanceID
	SelectExpensesQuery = `
		SELECT * FROM expenses
		ORDER BY instance_id
	`

	// SelectLastExpensesQuery returns the last expense for every instance older
	// than 1 day. This is used for obtaining the list of instances that need
	// Billing information update because all the instances returned by this
	// query doesn't have expenses for the current day
	SelectLastExpensesQuery = `` /* 382-byte string literal not displayed */

	SelectLastExpensesQueryVOPT = `` /* 368-byte string literal not displayed */

	// SelectExpensesByInstanceQuery returns expense in the inventory for a specific InstanceID
	SelectExpensesByInstanceQuery = `
		SELECT * FROM expenses
		WHERE instance_id = $1
		ORDER BY date
	`

	// InsertExpensesQuery inserts into a new expense for an instance
	InsertExpensesQuery = `` /* 191-byte string literal not displayed */

	// SelectInstancesQuery returns every instance in the inventory ordered by ID
	SelectInstancesQuery = `
		SELECT * FROM instances
		JOIN tags ON
			instances.id = tags.instance_id
		ORDER BY name
	`
	// SelectInstancesOverview returns the total count of all instances
	SelectInstancesOverview = `
		SELECT COUNT(*) as count FROM instances
	`

	// SelectInstancesByIDQuery returns an instance by its ID
	SelectInstancesByIDQuery = `
		SELECT * FROM instances
		JOIN tags ON
			instances.id = tags.instance_id
		WHERE id = $1
		ORDER BY name
	`

	// SelectClustersQuery returns every cluster in the inventory ordered by Name
	SelectClustersQuery = `
		SELECT * FROM clusters
		ORDER BY name
	`
	// SelectClustersOverview returns the number of clusters grouped by status
	SelectClustersOverview = `` /* 218-byte string literal not displayed */

	// InsertEventQuery insert a new audit event
	InsertEventQuery = `` /* 319-byte string literal not displayed */

	// SelectClusterEventsQuery returns audit log events related to a specific cluster.
	SelectClusterEventsQuery = `` /* 265-byte string literal not displayed */

	// SelectSystemEventsQuery returns system-wide audit logs.
	SelectSystemEventsQuery = `` /* 625-byte string literal not displayed */

	// UpdateEventStatusQuery updates the result status of an audit log entry based on its ID.
	UpdateEventStatusQuery = `UPDATE audit_logs SET result=$1 WHERE id=$2`
	// SelectClusterAccountNameQuery returns an cluster by its Name
	SelectClusterAccountNameQuery = `
		SELECT account_name FROM clusters
		WHERE id = $1
	`

	// SelectClusterRegionQuery returns an cluster by its Name
	SelectClusterRegionQuery = `
		SELECT region FROM clusters
		WHERE id = $1
	`

	// SelectClustersByIDuery returns an cluster by its Name
	SelectClustersByIDuery = `
		SELECT * FROM clusters
		WHERE id = $1
		ORDER BY name
	`

	// SelectClusterTags returns the cluster's tags
	SelectClusterTags = `
		SELECT DISTINCT ON (key) key,value,instance_id FROM instances
		JOIN tags ON
			id=instance_id
		WHERE cluster_id = $1
	`

	// SelectInstancesOnClusterQuery returns every instance belonging to a cluster
	SelectInstancesOnClusterQuery = `
		SELECT * FROM instances
		WHERE cluster_id = $1
		ORDER BY id
	`

	// SelectAccountsQuery returns every instance in the inventory ordered by Name
	SelectAccountsQuery = `
		SELECT * FROM accounts
		ORDER BY name
	`
	// SelectProvidersOverviewQuery returns data about cloud providers with their account and cluster counts,
	// excluding those marked as "UNKNOWN" and not counting Terminated clusters
	SelectProvidersOverviewQuery = `` /* 315-byte string literal not displayed */

	// SelectAccountsByNameQuery returns an instance by its Name
	SelectAccountsByNameQuery = `
		SELECT * FROM accounts
		WHERE name = $1
		ORDER BY name
	`

	// SelectClustersOnAccountQuery returns an cluster by its Name
	SelectClustersOnAccountQuery = `
		SELECT * FROM clusters
		WHERE account_name = $1
		ORDER BY name
	`

	// InsertInstancesQuery inserts into a new instance in its table
	InsertInstancesQuery = `` /* 794-byte string literal not displayed */

	// InsertClustersQuery inserts into a new instance in its table
	InsertClustersQuery = `` /* 815-byte string literal not displayed */

	// InsertAccountsQuery inserts into a new instance in its table
	InsertAccountsQuery = `` /* 404-byte string literal not displayed */

	// InsertTagsQuery inserts into a new tag for an instance
	InsertTagsQuery = `` /* 180-byte string literal not displayed */

	// DeleteInstanceQuery removes an instance by its ID
	DeleteInstanceQuery = `DELETE FROM instances WHERE id=$1`

	// DeleteClusterQuery removes an cluster by its name
	DeleteClusterQuery = `DELETE FROM clusters WHERE id=$1`

	// DeleteAccountQuery removes an account by its name
	DeleteAccountQuery = `DELETE FROM accounts WHERE name=$1`

	// DeleteTagsQuery removes a Tag by its key and instance reference
	DeleteTagsQuery = `DELETE FROM tags WHERE instance_id=$1`

	// Run 'check_terminated_instances()' SQL function
	UpdateTerminatedInstancesQuery = `SELECT check_terminated_instances()`
	// Run 'check_terminated_clusters()' SQL function
	UpdateTerminatedClustersQuery = `SELECT check_terminated_clusters()`

	// UpdateInstanceStatus updates the status of a  set of instances based on their clusterID
	UpdateStatusClusterByClusterIDQuery = `UPDATE clusters SET status=$1 WHERE id=$2`

	// UpdateInstanceStatus updates the status of a  set of instances based on their clusterID
	UpdateStatusInstancesByClusterIDQuery = `UPDATE instances SET status=$1 WHERE cluster_id=$2`

	// CheckStatusQuery checks if the requested status exists on the DB
	CheckStatusQuery = `SELECT EXISTS (SELECT 1 FROM status WHERE value=$1)`
	// SelectScannerLastScanTimestamp returns the latest scan timestamp across all accounts
	SelectScannerLastScanTimestamp = `SELECT MAX(last_scan_timestamp) as last_scan_timestamp FROM accounts;`
)

Variables

This section is empty.

Functions

This section is empty.

Types

type SQLClient 

type SQLClient struct {
	// contains filtered or unexported fields
}

SQLClient defines the SQL interface for the API to interact with the database. It manages database connections and provides methods for interacting with various entities like instances, clusters, accounts, and expenses.

func NewSQLClient 

func NewSQLClient(dbURL string, logger *zap.Logger) (*SQLClient, error)

NewSQLClient initializes a new SQLClient with the given database URL and logger.

Parameters: - dbURL: The connection string for the PostgreSQL database. - logger: Logger instance for logging.

Returns: - A pointer to an SQLClient instance. - An error if the database connection fails.

func (SQLClient) AddEvent 

func (a SQLClient) AddEvent(event models.AuditLog) (int64, error)

AddEvent inserts a new audit event into the database and returns the event ID.

func (SQLClient) CheckStatusValue 

func (a SQLClient) CheckStatusValue(status string) (bool, error)

CheckStatusValue checks if a given status value exists in the database.

Parameters: - status: The status value to check in the database.

Returns: - A boolean indicating whether the status exists (true) or not (false). - An error if the query fails.

func (SQLClient) DeleteAccount 

func (a SQLClient) DeleteAccount(accountName string) error

DeleteAccount deletes an account from the database by its name.

Parameters: - accountName: The name of the account to delete.

Returns: - An error if the transaction fails.

func (SQLClient) DeleteCluster 

func (a SQLClient) DeleteCluster(clusterName string) error

DeleteCluster deletes a cluster from the database.

Parameters: - clusterName: The name of the cluster to delete.

Returns: - An error if the database transaction fails.

func (SQLClient) DeleteInstance 

func (a SQLClient) DeleteInstance(instanceID string) error

DeleteInstance deletes an instance and its associated tags from the database.

Parameters: - instanceID: The ID of the instance to delete.

Returns: - An error if the transaction fails.

func (SQLClient) DeleteScheduledAction 

func (a SQLClient) DeleteScheduledAction(actionID string) error

DeleteScheduledAction removes an actions.ScheduledAction action from the DB based on its ID

Parameters:

  • A string containing the action ID to be removed

Returns:

  • An error if the delete query fails

func (SQLClient) DisableScheduledAction 

func (a SQLClient) DisableScheduledAction(actionID string) error

DisableScheduledAction Disables an Action by its ID

Parameters:

  • Action ID

Returns:

  • An array of actions.ScheduledAction with the scheduled actions declared on the DB that are enabled
  • An error if the query fails

func (SQLClient) EnableScheduledAction 

func (a SQLClient) EnableScheduledAction(actionID string) error

EnableScheduledAction enables an Action by its ID

Parameters:

  • Action ID

Returns:

  • An array of actions.ScheduledAction with the scheduled actions declared on the DB that are enabled
  • An error if the query fails

func (SQLClient) GetAccountByName 

func (a SQLClient) GetAccountByName(accountName string) ([]inventory.Account, error)

GetAccountByName retrieves an account by its name from the database.

Parameters: - accountName: The name of the account to retrieve.

Returns: - A slice of inventory.Account objects (usually containing one element). - An error if the query fails.

func (SQLClient) GetAccounts 

func (a SQLClient) GetAccounts() ([]inventory.Account, error)

GetAccounts retrieves all accounts from the database.

Returns: - A slice of inventory.Account objects. - An error if the query fails.

func (SQLClient) GetClusterAccountName 

func (a SQLClient) GetClusterAccountName(clusterID string) (string, error)

GetClusterAccountName retrieves the account name associated with a specific cluster.

Parameters: - clusterID: The unique identifier of the cluster.

Returns: - A string representing the account name. - An error if the query fails or the cluster ID does not exist.

func (SQLClient) GetClusterByID 

func (a SQLClient) GetClusterByID(clusterID string) ([]inventory.Cluster, error)

GetClusterByID retrieves a cluster's details by its unique identifier.

Parameters: - clusterID: The unique identifier of the cluster.

Returns: - A slice containing a single inventory.Cluster object. - An error if the query fails or the cluster ID does not exist.

func (SQLClient) GetClusterEvents 

func (a SQLClient) GetClusterEvents(clusterID string) ([]models.AuditLog, error)

GetClusterEvents retrieves events associated with the given clusterID.

func (SQLClient) GetClusterRegion 

func (a SQLClient) GetClusterRegion(clusterID string) (string, error)

GetClusterRegion retrieves the region where a specific cluster is located.

Parameters: - clusterID: The unique identifier of the cluster.

Returns: - A string representing the region of the cluster. - An error if the query fails or the cluster ID does not exist.

func (SQLClient) GetClusterTags 

func (a SQLClient) GetClusterTags(clusterID string) ([]inventory.Tag, error)

GetClusterTags retrieves the tags associated with a specific cluster.

Parameters: - clusterID: The unique identifier of the cluster.

Returns: - A slice of inventory.Tag objects representing the cluster's tags. - An error if the query fails.

func (SQLClient) GetClusters 

func (a SQLClient) GetClusters() ([]inventory.Cluster, error)

GetClusters retrieves all clusters from the database.

Returns: - A slice of inventory.Cluster objects. - An error if the query fails.

func (SQLClient) GetClustersOnAccount 

func (a SQLClient) GetClustersOnAccount(accountName string) ([]inventory.Cluster, error)

GetClustersOnAccount retrieves all clusters associated with a specific account.

Parameters: - accountName: The name of the account whose clusters will be retrieved.

Returns: - A slice of inventory.Cluster objects. - An error if the query fails.

func (SQLClient) GetClustersOverview 

func (a SQLClient) GetClustersOverview() (models.ClustersSummary, error)

GetClustersOverview returns a summary of cluster statuses It counts the number of clusters that are running, stopped or terminated.

func (SQLClient) GetExpenses 

func (a SQLClient) GetExpenses() ([]inventory.Expense, error)

GetExpenses retrieves all expenses from the database.

Parameters:

Returns: - A slice of inventory.Expense objects. - An error if the query fails.

func (SQLClient) GetExpensesByInstance 

func (a SQLClient) GetExpensesByInstance(instanceID string) ([]inventory.Expense, error)

GetExpensesByInstance retrieves expenses for a specific instance.

Parameters: - instanceID: The ID of the instance.

Returns: - A slice of inventory.Expense objects associated with the instance. - An error if the query fails.

func (SQLClient) GetInstanceByID 

func (a SQLClient) GetInstanceByID(instanceID string) ([]inventory.Instance, error)

GetInstanceByID retrieves an instance by its ID.

Parameters: - instanceID: The ID of the instance to retrieve.

Returns: - A slice of inventory.Instance objects (usually one element). - An error if the query fails.

func (SQLClient) GetInstances 

func (a SQLClient) GetInstances() ([]inventory.Instance, error)

GetInstances retrieves all instances from the database and maps them to inventory.Instance objects.

Returns: - A slice of inventory.Instance objects. - An error if the query fails.

func (SQLClient) GetInstancesOnCluster 

func (a SQLClient) GetInstancesOnCluster(clusterID string) ([]inventory.Instance, error)

GetInstancesOnCluster retrieves all instances belonging to a specific cluster.

Parameters: - clusterID: The unique identifier of the cluster.

Returns: - A slice of inventory.Instance objects representing the instances in the cluster. - An error if the query fails.

func (SQLClient) GetInstancesOutdatedBilling 

func (a SQLClient) GetInstancesOutdatedBilling() ([]inventory.Instance, error)

GetInstancesOutdatedBilling retrieves instances with outdated billing information.

Parameters:

Returns: - A slice of inventory.Instance objects. - An error if the query fails.

func (SQLClient) GetInstancesOverview 

func (a SQLClient) GetInstancesOverview() (models.InstancesSummary, error)

GetInstancesOverview returns a summary of instances grouped by their status. It provides the total count along with counts of running and stopped instances.

func (SQLClient) GetProvidersOverview 

func (a SQLClient) GetProvidersOverview() (models.ProvidersSummary, error)

GetProvidersOverview returns a summary of cloud providers (AWS, GCP, Azure) with their respective account and cluster counts.

func (SQLClient) GetScannerLastScanTimestamp added in v0.4.2 

func (a SQLClient) GetScannerLastScanTimestamp() (*time.Time, error)

GetScannerLastScanTimestamp returns the latest scan timestamp across all accounts

func (SQLClient) GetScheduledActionByID 

func (a SQLClient) GetScheduledActionByID(actionID string) ([]actions.Action, error)

GetScheduledActionByID runs the db select query for retrieving a specific scheduled action by its ID

Parameters:

Returns:

  • An array of actions.ScheduledAction with the scheduled actions declared on the DB. It's expected to return an array with a single element, but still being an array for code compatibility
  • An error if the query fails

func (SQLClient) GetScheduledActions 

func (a SQLClient) GetScheduledActions(conditions []string, args []interface{}) ([]actions.Action, error)

GetScheduledActions runs the db select query for retrieving the scheduled actions on the DB

Parameters:

Returns:

  • An array of actions.ScheduledAction with the scheduled actions declared on the DB
  • An error if the query fails

func (SQLClient) GetSystemEvents 

func (a SQLClient) GetSystemEvents() ([]models.SystemAuditLogs, error)

GetSystemEvents retrieves system-wide events.

func (SQLClient) PatchScheduledAction 

func (a SQLClient) PatchScheduledAction(newActions []actions.Action) error

PatchScheduledAction updates Action by its ID

Parameters:

  • Action list

Returns:

  • An error if the query fails

func (SQLClient) PatchScheduledActionStatus 

func (a SQLClient) PatchScheduledActionStatus(actionID string, status string) error

PatchScheduledActionStatus updates Action status by its ID

Parameters:

  • Action list

Returns:

  • An error if the query fails

func (SQLClient) Ping 

func (a SQLClient) Ping() error

Ping performs a ping operation to check if the DB is alive

Parameters:

Returns:

  • An error if the ping fails

func (SQLClient) RefreshInventory 

func (a SQLClient) RefreshInventory() error

RefreshInventory refreshes the database by updating the status of terminated instances and clusters.

Returns: - An error if any update query fails.

func (SQLClient) UpdateClusterStatusByClusterID 

func (a SQLClient) UpdateClusterStatusByClusterID(status string, clusterID string) error

UpdateClusterStatusByClusterID updates the status of a cluster and all its instances in the database.

This function first verifies if the requested status exists in the database. If the status is valid, it updates: 1. The status of the cluster identified by the given `clusterID`. 2. The status of all instances associated with the cluster.

Parameters: - status: The new status to be applied to the cluster and its instances. - clusterID: The unique identifier of the cluster whose status will be updated.

Returns: - An error if the status is invalid, the update operation fails, or no rows are affected.

func (SQLClient) UpdateEventStatus 

func (a SQLClient) UpdateEventStatus(eventID int64, result string) error

UpdateEventStatus updates the result status of an audit event.

func (SQLClient) WriteAccounts 

func (a SQLClient) WriteAccounts(accounts []inventory.Account) error

WriteAccounts inserts multiple accounts into the database in a transaction.

Parameters: - accounts: A slice of inventory.Account objects to insert.

Returns: - An error if the transaction fails.

func (SQLClient) WriteClusters 

func (a SQLClient) WriteClusters(clusters []inventory.Cluster) error

WriteClusters inserts a list of clusters into the database in a transaction.

Parameters: - clusters: A slice of inventory.Cluster objects to insert.

Returns: - An error if the transaction fails or the query encounters an issue.

func (SQLClient) WriteExpenses 

func (a SQLClient) WriteExpenses(expenses []inventory.Expense) error

WriteExpenses writes a batch of expenses to the database in a transaction.

Parameters: - expenses: A slice of inventory.Expense objects to insert.

Returns: - An error if the transaction fails.

func (SQLClient) WriteInstances 

func (a SQLClient) WriteInstances(instances []inventory.Instance) error

WriteInstances writes a batch of instances and their tags to the database in a transaction.

Parameters: - instances: A slice of inventory.Instance objects to insert.

Returns: - An error if the transaction fails.

func (SQLClient) WriteScheduledActions 

func (a SQLClient) WriteScheduledActions(newActions []actions.Action) error

WriteScheduledActions receives an array of actions.ScheduledAction and writes them on the DB

Parameters:

  • An array of actions.ScheduledAction to write on the DB

Returns:

  • An error if the insert fails

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.