github.com/status-im/status-go@v1.1.0/wakuv2/persistence/queries.go (about)

     1  package persistence
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  )
     7  
     8  // Queries are the sqlite queries for a given table.
     9  type Queries struct {
    10  	deleteQuery  string
    11  	existsQuery  string
    12  	getQuery     string
    13  	putQuery     string
    14  	queryQuery   string
    15  	prefixQuery  string
    16  	limitQuery   string
    17  	offsetQuery  string
    18  	getSizeQuery string
    19  }
    20  
    21  // NewQueries creates a new set of queries for the passed table
    22  func NewQueries(tbl string, db *sql.DB) (*Queries, error) {
    23  	err := CreateTable(db, tbl)
    24  	if err != nil {
    25  		return nil, err
    26  	}
    27  	return &Queries{
    28  		deleteQuery:  fmt.Sprintf("DELETE FROM %s WHERE key = $1", tbl),
    29  		existsQuery:  fmt.Sprintf("SELECT exists(SELECT 1 FROM %s WHERE key=$1)", tbl),
    30  		getQuery:     fmt.Sprintf("SELECT data FROM %s WHERE key = $1", tbl),
    31  		putQuery:     fmt.Sprintf("INSERT INTO %s (key, data) VALUES ($1, $2)", tbl),
    32  		queryQuery:   fmt.Sprintf("SELECT key, data FROM %s", tbl),
    33  		prefixQuery:  ` WHERE key LIKE '%s%%' ORDER BY key`,
    34  		limitQuery:   ` LIMIT %d`,
    35  		offsetQuery:  ` OFFSET %d`,
    36  		getSizeQuery: fmt.Sprintf("SELECT length(data) FROM %s WHERE key = $1", tbl),
    37  	}, nil
    38  }
    39  
    40  // Delete returns the query for deleting a row.
    41  func (q Queries) Delete() string {
    42  	return q.deleteQuery
    43  }
    44  
    45  // Exists returns the query for determining if a row exists.
    46  func (q Queries) Exists() string {
    47  	return q.existsQuery
    48  }
    49  
    50  // Get returns the query for getting a row.
    51  func (q Queries) Get() string {
    52  	return q.getQuery
    53  }
    54  
    55  // Put returns the query for putting a row.
    56  func (q Queries) Put() string {
    57  	return q.putQuery
    58  }
    59  
    60  // Query returns the query for getting multiple rows.
    61  func (q Queries) Query() string {
    62  	return q.queryQuery
    63  }
    64  
    65  // Prefix returns the query fragment for getting a rows with a key prefix.
    66  func (q Queries) Prefix() string {
    67  	return q.prefixQuery
    68  }
    69  
    70  // Limit returns the query fragment for limiting results.
    71  func (q Queries) Limit() string {
    72  	return q.limitQuery
    73  }
    74  
    75  // Offset returns the query fragment for returning rows from a given offset.
    76  func (q Queries) Offset() string {
    77  	return q.offsetQuery
    78  }
    79  
    80  // GetSize returns the query for determining the size of a value.
    81  func (q Queries) GetSize() string {
    82  	return q.getSizeQuery
    83  }
    84  
    85  // CreateTable creates the table that will persist the peers
    86  func CreateTable(db *sql.DB, tableName string) error {
    87  	sqlStmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (key TEXT NOT NULL PRIMARY KEY ON CONFLICT REPLACE, data BYTEA);", tableName)
    88  	_, err := db.Exec(sqlStmt)
    89  	if err != nil {
    90  		return err
    91  	}
    92  	return nil
    93  }
    94  
    95  func Clean(db *sql.DB, tableName string) error {
    96  	// This is fully controlled by us
    97  	sqlStmt := fmt.Sprintf("DELETE FROM %s;", tableName) // nolint: gosec
    98  	_, err := db.Exec(sqlStmt)
    99  	if err != nil {
   100  		return err
   101  	}
   102  	return nil
   103  }