117 lines
3.5 KiB
Go
117 lines
3.5 KiB
Go
package countersql
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
)
|
|
|
|
// Configuration holds the DSN connection string and a resource Semaphore to limit the number of active connections
|
|
// Note: This is a copied design pattern I've used for other projects which had much more data inside a Configuration struct.
|
|
// Examples include semaphores and persistent connection pools, which I've stripped out for this project.
|
|
type Configuration struct {
|
|
DSN string
|
|
}
|
|
|
|
// Connection represents a single connection to the database, however there may be many instances / connections
|
|
// Note: This is a copied design pattern I've used for other projects which had much more data inside a Connection struct.
|
|
type Connection struct {
|
|
DB *sql.DB
|
|
}
|
|
|
|
// HasIPVisited returns true only if the IP address is present in the database
|
|
func (conn Connection) HasIPVisited(ipAddress string) (bool, error) {
|
|
rows, err := conn.DB.Query(`SELECT id FROM visit WHERE ip_address = ? LIMIT 1;`, ipAddress)
|
|
if err != nil {
|
|
return false, fmt.Errorf("SELECT query failed: %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
if !rows.Next() {
|
|
return false, nil
|
|
}
|
|
|
|
return true, nil
|
|
}
|
|
|
|
// GetUniqueVisits counts the number of entires in the visits table, representing one unique source IP address per row
|
|
func (conn Connection) GetUniqueVisits() (int, error) {
|
|
rows, err := conn.DB.Query(`SELECT COUNT(*) FROM visit`)
|
|
if err != nil {
|
|
return 0, fmt.Errorf("SELECT query failed: %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
if !rows.Next() {
|
|
return 0, nil
|
|
}
|
|
|
|
var uniqueVists int
|
|
if err := rows.Scan(&uniqueVists); err != nil {
|
|
return 0, fmt.Errorf("failed to scan database row: %v", err)
|
|
}
|
|
|
|
return uniqueVists, nil
|
|
}
|
|
|
|
// IncrementVisitor accepts an IP address and updates the row matching that IP address
|
|
// It does not check if the row matching the IP address supplied exists or not
|
|
func (conn Connection) IncrementVisitor(ipAddress string) error {
|
|
_, err := conn.DB.Exec(`UPDATE visit SET visits = visits + 1, last_visited = NOW() WHERE ip_address = ?`, ipAddress)
|
|
if err != nil {
|
|
return fmt.Errorf("UPDATE query failed: %v", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// AddVisitor accepts an IP address and inserts a new row into the database as this represents a new unique visitor
|
|
func (conn Connection) AddVisitor(ipAddress string) error {
|
|
_, err := conn.DB.Exec(`INSERT INTO visit (ip_address, visits, last_visited) VALUES (?, '0', NOW())`, ipAddress)
|
|
if err != nil {
|
|
return fmt.Errorf("INSERT query failed: %v", err)
|
|
}
|
|
|
|
return nil
|
|
}
|
|
|
|
// Connect will open a TCP connection to the database with the given DSN configuration
|
|
func (conf Configuration) Connect() (*Connection, error) {
|
|
conn := &Connection{}
|
|
var err error
|
|
|
|
conn.DB, err = sql.Open("mysql", conf.DSN)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("failed to open db: %v", err)
|
|
}
|
|
|
|
return conn, nil
|
|
}
|
|
|
|
// InitializeDatabase will check if tables exist in the database, and if not then create them.
|
|
func (conn Connection) InitializeDatabase() error {
|
|
rows, err := conn.DB.Query(`SHOW TABLES`)
|
|
if err != nil {
|
|
return fmt.Errorf("SHOW TABLES query failed: %v", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
if rows.Next() { // Table already exists, leave with no error
|
|
return nil
|
|
}
|
|
|
|
// Table does not exist, create it
|
|
_, err = conn.DB.Exec(`CREATE TABLE visit (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
ip_address varchar(15) NOT NULL,
|
|
visits int(11) NOT NULL,
|
|
last_visited datetime NOT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;`)
|
|
|
|
if err != nil {
|
|
return fmt.Errorf("failed to create table: %v", err)
|
|
}
|
|
|
|
return nil
|
|
}
|