README
¶
dbIO is a lightweight, straightforward MySQL interface written in Go.
These scripts are still under developement, but are available for use.
Copyright 2018 by Shawn Rupp
Dependencies:
Prompter
dbIO used Prompter to query the user's MySQL password.
go get github.com/Songmu/prompter
Golang Mysql driver
Required for using go's sql package with MySQL.
go get github.com/go-sql-driver/mysql
iotools
dbIO uses iotools to read in database schema template file.
go get github.com/icwells/go-tools/iotools
Installation
go get github.com/icwells/dbIO
Usage
dbIO stores relevant connection in a DBIO struct which is returned by the Connect function. Below are some examples of usage. See the GoDoc page for a comprehensive list.
Connect and the DBIO struct
dbIO.Connect(host, database, user, password string) *DBIO
Attempts to connect to given sql database with given user name. If the password is left blank, it will prompt for a password from the user before storing the start time (for recording program run time).
Returns a DBIO instance containing:
DB *sql.DB
Host string
Database string
User string
Password string
Starttime time.Time
Columns map[string]string
Creating/Replacing Databases
CreateDatabase can be used to initializes a database with a given name (although NewTables must be called to initialize the tables within the databse).
Similarly, ReplaceDatabase will drop an existing database (if it exists) and re-initialize it (for testing).
dbio.CreateDatabase(host, database, user string)
dbio.ReplaceDatabase(host, database, user, password string)
Additionally, the Ping function can be used to test credentials:
dbio.Ping(host, database, user, password string)
It will return true if a connection was successfully established, or false if it was not.
Uploading to a database
DBIO.NewTables(infile string)
This command will read a text file of tables, columns, and types and initialize new tables if they do not already exist.
The input file should contain a series of valid sql CREATE TABLE commands, with each command seperated by a single space and ending with a semicolon (;). E.G.:
''' CREATE TABLE IF NOT EXISTS Accounts ( account_id INT PRIMARY KEY, Account TEXT, submitter_name TEXT; INDEX (account_id) );
CREATE TABLE IF NOT EXISTS Update_time ( update_number INT PRIMARY KEY AUTO_INCREMENT, Time TEXT ); '''
DBIO.ReadColumns(infile string)
Reads in tables and columns from input file (see above) and stores in DBIO.Columns. The column types and any additional column descriptors will be stored for creating tables.
DBIO.GetTableColumns()
Retrieves names tables and their columns from an existing database and stores in Columns map.
Formatting data for upload
dbIO.FormatMap(data map[string][]string) (string, int)
dbIO.FormatSlice(data [][]string) (string, int)
These functions will format a map or slice of string slices into a comma/parentheses seperated string for upload to a database:
[][]string{{"5, "Apple"}, {"3", "Orange"},}
becomes
"('5','Apple'),('3','Orange')"
They both return a string of the data and an integer of the number rows that were formatted. Both are stand-alone functions and do not use a DBIO struct.
The input data should contian the same number of columns as the table is to be uploaded to. (Map keys are not included in the upload.)
DBIO.UpdateDB(table, values string, l int) int
UpdateDB uploads a formatted string of data (see previous functions) to a given table. It will print the number of rows uploaded (given with l).
It returns an integer (rather than a boolean) so multiple results can be tallied if needed.
Extracting from a database
DBIO.GetRows(table, column, key, target string) [][]string
Returns rows of target columns with key in column. Use "*" for target to select entire row or a comma seperated string of column names for multiple columns.
DBIOEvaluateRows(table, column, op, key, target string) [][]string
Returns rows of target column(s) same as GetRows, except it compares key to the column value using the given operator (>=/=/...; ie. column >= 7).
Documentation
¶
Index ¶
- func FormatMap(data map[string][]string) (string, int)
- func FormatSlice(data [][]string) (string, int)
- func Ping(host, database, user, password string) bool
- type DBIO
- func (d *DBIO) BackupDB(outdir string)
- func (d *DBIO) ColumnContains(table, column, value, target string) [][]string
- func (d *DBIO) Count(table, column, target, op, key string, distinct bool) int
- func (d *DBIO) CountRows(table string) int
- func (d *DBIO) DeleteRow(table, column, value string)
- func (d *DBIO) DeleteRows(table, column string, values []string)
- func (d *DBIO) EvaluateRows(table, column, op, key, target string) [][]string
- func (d *DBIO) Execute(cmd string) [][]string
- func (d *DBIO) GetColumnInt(table, column string) []int
- func (d *DBIO) GetColumnText(table, column string) []string
- func (d *DBIO) GetColumns(table string, columns []string) [][]string
- func (d *DBIO) GetMax(table, column string) int
- func (d *DBIO) GetNumOccurances(table, column string) map[string]int
- func (d *DBIO) GetRows(table, column, key, target string) [][]string
- func (d *DBIO) GetRowsMin(table, column, target string, min int) [][]string
- func (d *DBIO) GetTable(table string) [][]string
- func (d *DBIO) GetTableColumns()
- func (d *DBIO) GetTableMap(table string) map[string][]string
- func (d *DBIO) GetUpdateTimes() map[string]time.Time
- func (d *DBIO) Insert(table, command string) error
- func (d *DBIO) LastUpdate() time.Time
- func (d *DBIO) NewTables(infile string)
- func (d *DBIO) OptimizeTables()
- func (d *DBIO) ReadColumns(infile string) []string
- func (d *DBIO) TruncateTable(table string)
- func (d *DBIO) UpdateColumns(table, idcol string, values map[string]map[string]string) bool
- func (d *DBIO) UpdateDB(table, values string, l int) int
- func (d *DBIO) UpdateRow(table, target, value, column, op, key string) bool
- func (d *DBIO) UploadSlice(table string, values [][]string) error
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func FormatMap ¶
FormatMap converts a map of string slices to a string formatted with parentheses, commas, and appostrophe's where needed. Returns the number of rows formatted.
func FormatSlice ¶
FormatSlice converts a two-dimensional string slice to a string formatted with parentheses, commas, and appostrophe's where needed. Returns the number of rows formatted.
Types ¶
type DBIO ¶
type DBIO struct {
// DB is the database connection. Any SQL query can be run directly using DB.
DB *sql.DB
// Host is the host IP.
Host string
// Database stores the name of the database.
Database string
// User is the MySQL user name used in this session.
User string
// Password stores the user's password. It is not secure, so be careful how you use it.
Password string
// Starttime is the time point after the password is given.
Starttime time.Time
// Columns stores a map with a comma-seperated string of column name for each table.
Columns map[string]string
// contains filtered or unexported fields
}
DBIO is the central struct containing all releveant connection information.
func Connect ¶
Connect attempts to connect to the MySQL database located at host/database using the given user name and password.
func CreateDatabase ¶
CreateDatabase connects to MySQL and creates a new database.
func NewDBIO ¶ added in v1.2.1
NewDBIO returns an initialized struct. If host is left blank, it will default to localHost.
func ReplaceDatabase ¶
ReplaceDatabase deletes the given database and creates a new, empty, one (for testing).
func (*DBIO) BackupDB ¶ added in v1.3.1
BackupDB calls mysldump to back up database to local machine
func (*DBIO) ColumnContains ¶ added in v1.2.5
ColumnContains returns a 2D string slice from table if value is in column.
func (*DBIO) Count ¶
Count returns count of entries from target column(s) in table where key relates to column via op (>=/=/...; ie. column >= 7). Returns total if distinct is false; returns number of unique entries if distinct is true. Give operator, key, and target as emtpy strings to count without evaluating.
func (*DBIO) DeleteRow ¶
DeleteRow deletes a single row from the database where the value in the given column equals value.
func (*DBIO) DeleteRows ¶ added in v1.2.1
DeleteRows deletes rows from the database if the value in the given column is contained in the values slice.
func (*DBIO) EvaluateRows ¶
EvaluateRows returns rows of columns where key relates to target via op (>=/=/...) (i.e. column <= key).
func (*DBIO) GetColumnInt ¶
GetColumnInt returns a slice of all entries in column of integers.
func (*DBIO) GetColumnText ¶
GetColumnText returns a slice of all entries in column of text.
func (*DBIO) GetColumns ¶
GetColumns returns a slice of slices of all entries in given columns.
func (*DBIO) GetNumOccurances ¶
GetNumOccurances returns a map with the number of unique entries in column.
func (*DBIO) GetRowsMin ¶
GetRowsMin returns all rows of target columns where column >= key.
func (*DBIO) GetTableColumns ¶
func (d *DBIO) GetTableColumns()
GetTableColumns extracts table and column names from the database and stores them in the Columns map.
func (*DBIO) GetTableMap ¶
GetTableMap returns the given table as a map with the first column as the key.
func (*DBIO) GetUpdateTimes ¶ added in v1.2.2
GetUpdateTimes returns a map the last update date and time for each table.
func (*DBIO) LastUpdate ¶ added in v1.2.2
LastUpdate returns the time of the most recent update.
func (*DBIO) NewTables ¶
NewTables executes new table commands from infile. See README for infile formatting.
func (*DBIO) OptimizeTables ¶ added in v1.3.3
func (d *DBIO) OptimizeTables()
OptimizeTables calls optimize on all tables in the database.
func (*DBIO) ReadColumns ¶
ReadColumns builds a map of column statements with types from infile. See README for infile formatting.
func (*DBIO) TruncateTable ¶
TruncateTable clears all content from the given table.
func (*DBIO) UpdateColumns ¶ added in v1.2.1
UpdateColumns updates columns (specified as outer map key) in table where column == inner map key with map values. Returns true if successful.
func (*DBIO) UpdateDB ¶
UpdateDB adds new rows to table. Values must be formatted using FormatMap or FormatSlice.
Source Files