github.com/lmorg/murex@v0.0.0-20240217211045-e081c89cd4ef/builtins/optional/select/sqlite3.go (about)

     1  package sqlselect
     2  
     3  import (
     4  	"database/sql"
     5  	"fmt"
     6  	"regexp"
     7  	"strings"
     8  )
     9  
    10  const (
    11  	sqlCreateTable = `CREATE TABLE IF NOT EXISTS %s (%s);`
    12  
    13  	sqlInsertRecord = `INSERT INTO %s VALUES (%s);`
    14  
    15  	sqlQuery = `SELECT %s FROM %s %s %s;`
    16  )
    17  
    18  var (
    19  	rxQuery      = regexp.MustCompile(`(?i)\s+(WHERE|GROUP BY|ORDER BY)\s+`)
    20  	rxCheckFrom  = regexp.MustCompile(`(?iU)(\s+)?FROM\s+(\P{C})+($|\s+(WHERE|GROUP BY|ORDER BY)[\s]+)`)
    21  	rxPipesMatch = regexp.MustCompile(`^(<[a-zA-Z0-9]+>[\s,]*)+$`)
    22  	rxVarsMatch  = regexp.MustCompile(`^(\$[-_a-zA-Z0-9]+[\s,]*)+$`)
    23  	rxPipesSplit = regexp.MustCompile(`[\s,]+`)
    24  )
    25  
    26  func createDb() (*sql.DB, error) {
    27  	db, err := sql.Open(driverName, ":memory:" /*"file:debug.db"*/)
    28  	if err != nil {
    29  		return nil, fmt.Errorf("could not open database: %s", err.Error())
    30  	}
    31  
    32  	return db, nil
    33  }
    34  
    35  func openTable(db *sql.DB, name string, headings []string) (*sql.Tx, error) {
    36  	var err error
    37  
    38  	if len(headings) == 0 {
    39  		return nil, fmt.Errorf("cannot create table '%s': no titles supplied", name)
    40  	}
    41  
    42  	var sHeadings string
    43  	for i := range headings {
    44  		sHeadings += fmt.Sprintf(`"%s" NUMERIC,`, headings[i])
    45  	}
    46  	sHeadings = sHeadings[:len(sHeadings)-1]
    47  
    48  	query := fmt.Sprintf(sqlCreateTable, name, sHeadings)
    49  	_, err = db.Exec(query)
    50  	if err != nil {
    51  		return nil, fmt.Errorf("could not create table '%s': %s\n%s", name, err.Error(), query)
    52  	}
    53  
    54  	tx, err := db.Begin()
    55  	if err != nil {
    56  		return nil, fmt.Errorf("could not create transaction: %s", err.Error())
    57  	}
    58  
    59  	return tx, nil
    60  }
    61  
    62  func insertRecords(tx *sql.Tx, name string, records []interface{}) error {
    63  	if len(records) == 0 {
    64  		return fmt.Errorf("no records to insert into transaction on table %s", name)
    65  	}
    66  
    67  	values, err := createValues(len(records))
    68  	if err != nil {
    69  		return fmt.Errorf("cannot insert records into transaction on table %s: %s", name, err.Error())
    70  	}
    71  
    72  	_, err = tx.Exec(fmt.Sprintf(sqlInsertRecord, name, values), records...)
    73  	if err != nil {
    74  		return fmt.Errorf("cannot insert records into transaction on table %s: %s", name, err.Error())
    75  	}
    76  
    77  	return nil
    78  }
    79  
    80  func createValues(length int) (string, error) {
    81  	if length == 0 {
    82  		return "", fmt.Errorf("no records to insert")
    83  	}
    84  
    85  	values := strings.Repeat("?,", length)
    86  	values = values[:len(values)-1]
    87  
    88  	return values, nil
    89  }
    90  
    91  func createQueryString(pipes []string, parameters string) string {
    92  	split := rxQuery.Split(parameters, 2)
    93  	match := rxQuery.FindString(parameters)
    94  
    95  	switch len(split) {
    96  	case 1:
    97  		return fmt.Sprintf(sqlQuery, split[0], "main", match, "")
    98  
    99  	case 2:
   100  		if len(pipes) > 0 {
   101  			return fmt.Sprintf(sqlQuery, split[0], strings.Join(pipes, ", "), match, split[1])
   102  		}
   103  
   104  		return fmt.Sprintf(sqlQuery, split[0], "main", match, split[1])
   105  
   106  	default:
   107  		panic("unexpected length of split")
   108  	}
   109  }