mpdb

package
v1.0.1 Latest Latest
Warning

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

 Go to latest Published: Mar 11, 2014 License: BSD-3-Clause Imports: 7 Imported by: 0

Documentation

Overview

Package mpdb provides routines for manipulating the database whilst preserving referential integrity as best as possible.

Index

Constants

View Source 
const AddMealPlanSQL = "INSERT INTO mealplan VALUES (NULL, ?, ?, ?)"

SQL statement for adding a meal plan to the database.

View Source 
const AddMealSQL = "INSERT INTO meal VALUES (NULL, ?, ?, ?, '')"

SQL statement for adding a meal.

View Source 
const AddMealTagSQL = "INSERT INTO tag VALUES (?, ?)"

SQL statement for adding a tag to a meal.

View Source 
const CalculateTagScoresSQL = "SELECT tag.tag, MIN(ABS(DATEDIFF(serving.dateserved, ?))) " +
	"FROM tag " +
	"INNER JOIN serving ON serving.mealid = tag.mealid " +
	"GROUP BY tag.tag"

SQL statement to obtain a list of all tags and their distances to every serving of the meals they are attached to.

View Source 
const CountServingsSQL = "SELECT COUNT(serving.dateserved) FROM serving WHERE serving.mealplanid = ?"

SQL statement for returning the number of servings on a meal plan.

View Source 
const CreateSearchPatternsTableSQL = "CREATE TEMPORARY TABLE search_patterns (pattern VARCHAR(255))"
View Source 
const DBDriver = "mysql"

DBDriver is the driver name used when connecting to the database.

View Source 
const DBParams = "?parseTime=true"

DBParams are extra parameters required for the database routines to function.

View Source 
const DeleteMealPlanSQL = "DELETE FROM mealplan WHERE mealplan.id = ?"

SQL statement for deleting a meal plan identified by its ID.

View Source 
const DeleteMealSQL = "DELETE FROM meal WHERE meal.id = ?"

SQL statement to delete a meal.

View Source 
const DeleteMealTagsSQL = "DELETE FROM tag WHERE tag.mealid = ?"

SQL statement for deleting all tags associated with a meal.

View Source 
const DeleteServingSQL = "DELETE FROM serving WHERE serving.mealplanid = ? AND serving.dateserved = ?"

SQL statement for deleting a serving.

View Source 
const DeleteServingsOfSQL = "DELETE FROM serving WHERE serving.mealid = ?"

SQL statement for deleting all servings of a given meal.

View Source 
const DeleteServingsSQL = "DELETE FROM serving WHERE serving.mealplanid = ?"

SQL statement for deleting all servings in a meal plan.

View Source 
const DropSearchPatternsTableSQL = "DROP TABLE search_patterns"
View Source 
const GetMealPlanSQL = "SELECT mealplan.notes, mealplan.startdate, mealplan.enddate FROM mealplan WHERE mealplan.id = ?"

SQL statement for retrieving information about a meal plan.

View Source 
const GetMealSQL = "SELECT meal.name, meal.recipe, meal.favourite FROM meal WHERE meal.id = ?"

SQL statement for fetching information about a meal.

View Source 
const GetMealTagsSQL = "SELECT tag.tag FROM tag WHERE tag.mealid = ?"

SQL statement for fetching tags associated with a meal.

View Source 
const GetServingSQL = "SELECT serving.mealid FROM serving WHERE serving.mealplanid = ? AND serving.dateserved = ?"

SQL statement for retrieving information about a meal serving.

View Source 
const GetServingsSQL = "SELECT serving.dateserved, serving.mealid FROM serving WHERE serving.mealplanid = ?"

SQL statement for retrieving the servings associated with a meal plan.

View Source 
const InsertSearchPatternSQL = "INSERT INTO search_patterns VALUES (?)"
View Source 
const InsertServingSQL = "INSERT INTO serving VALUES (?, ?, ?)"

SQL statement for adding a meal serving.

View Source 
const IsFavouriteSQL = "SELECT meal.favourite FROM meal WHERE meal.id = ?"

SQL statement for testing whether a meal is marked as a favourite.

View Source 
const LatestVersion = 1
View Source 
const ListAllTagsByNameSQL = "SELECT DISTINCT tag.tag FROM tag ORDER BY tag.tag ASC"

SQL statement to list all tags in the database sorted by name.

View Source 
const ListAllTagsSQL = "SELECT DISTINCT tag.tag FROM tag"

SQL statement to list all tags in the database.

View Source 
const ListMealPlansBetweenSQL = "SELECT mealplan.id, mealplan.startdate, mealplan.enddate " +
	"FROM mealplan " +
	"WHERE ? <= mealplan.enddate && mealplan.startdate <= ?"

SQL statement for listing meal plans that overlap a given date range.

View Source 
const ListMealsByNameSQL = ListMealsSQL + " ORDER BY meal.name"

SQL statement for listing meals sorted by name.

View Source 
const ListMealsSQL = "SELECT meal.id, meal.name, meal.recipe, meal.favourite FROM meal"

SQL statement for listing meals.

View Source 
const ListSuggestionsSQL = "SELECT meal.id, meal.name, meal.recipe, meal.favourite, MIN(ABS(DATEDIFF(serving.dateserved, ?))) " +
	"FROM meal " +
	"LEFT JOIN serving ON meal.id = serving.mealid " +

	"GROUP BY meal.id"

SQL statement to obtain a list of meals along with the distances to their closest servings.

View Source 
const SearchTextFunc = "" /* 134-byte string literal not displayed */
View Source 
const SetFavouriteSQL = "UPDATE meal SET meal.favourite = ? WHERE meal.id = ?"

SQL statement to set the "favourite" status of a meal.

View Source 
const UpdateMealSQL = "UPDATE meal SET meal.name = ?, meal.recipe = ?, meal.favourite = ? WHERE meal.id = ?"

SQL statement for updating the information about a meal.

View Source 
const UpdateNotesSQL = "UPDATE mealplan SET mealplan.notes = ? WHERE mealplan.id = ?"

SQL statement for setting the notes associated with a meal plan.

View Source 
const UpdateSearchTextSQL = "UPDATE meal SET meal.searchtext = " + SearchTextFunc + " WHERE meal.id = ?"

Variables

View Source 
var ClearTablesSQLs = []string{
	"DELETE FROM meal",
	"DELETE FROM tag",
	"DELETE FROM mealplan",
	"DELETE FROM serving",
}

SQL statements to clear tables.

View Source 
var CreateTablesSQLs = []string{
	"CREATE TABLE IF NOT EXISTS meal ( " +
		"id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, " +
		"name VARCHAR(255) NOT NULL, " +
		"recipe TEXT, " +
		"favourite BOOLEAN NOT NULL, " +
		"searchtext TEXT NOT NULL, " +
		"PRIMARY KEY (id) " +
		")",
	"CREATE TABLE IF NOT EXISTS tag ( " +
		"mealid BIGINT UNSIGNED NOT NULL, " +
		"tag VARCHAR(64) NOT NULL, " +
		"PRIMARY KEY (mealid, tag) " +
		")",
	"CREATE TABLE IF NOT EXISTS mealplan ( " +
		"id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, " +
		"notes TEXT, " +
		"startdate DATE NOT NULL, " +
		"enddate DATE NOT NULL, " +
		"PRIMARY KEY (id) " +
		")",
	"CREATE TABLE IF NOT EXISTS serving ( " +
		"mealplanid BIGINT UNSIGNED NOT NULL, " +
		"dateserved DATE NOT NULL, " +
		"mealid BIGINT UNSIGNED NOT NULL, " +
		"PRIMARY KEY (mealplanid, dateserved) " +
		")",
}

SQL statements to create tables.

View Source 
var DBSource = "mealplanner@unix(/var/run/mysqld/mysqld.sock)/mealplanner"

DBSource identifies how to connect to the database. It should take the form "USER:PASS@unix(/PATH/TO/SOCKET)/DBNAME" or "USER:PASS@tcp(HOST:PORT)/DBNAME". By default, it will attempt to connect via the local Unix socket to the 'mealplanner' database, with username 'mealplanner' and no password.

View Source 
var DeleteTablesSQLs = []string{
	"DROP TABLE IF EXISTS meal",
	"DROP TABLE IF EXISTS tag",
	"DROP TABLE IF EXISTS mealplan",
	"DROP TABLE IF EXISTS serving",
}

SQL statements to delete tables.

View Source 
var Migrations = []*Migration{

	&Migration{0, 1, []string{
		"ALTER TABLE meal ADD COLUMN searchtext TEXT NOT NULL",
		"UPDATE meal SET meal.searchtext = " + SearchTextFunc,
	}},
}

Functions

func AddMeal 

func AddMeal(q Queryable, meal *mpdata.Meal) (err error)

AddMeal adds the information in 'meal' to the database as a new record, then sets 'meal.ID' to the identifier of this new record.

func AddMealPlan 

func AddMealPlan(q Queryable, mp *mpdata.MealPlan) (err error)

AddMealPlan adds the information contained in 'mp' to the database as a new meal plan record. It assigns the identifier of the newly created record to the ID field of the meal plan.

func AddMealTags 

func AddMealTags(q Queryable, mealID uint64, tags []string) (err error)

AddMealTags adds the the list of tags given in 'tags' to the meal identified by 'mealID'.

func AddMealWithTags 

func AddMealWithTags(q Queryable, mt mpdata.MealWithTags) (err error)

AddMealWithTags combines 'AddMeal' and 'AddMealTags'.

func AddServing 

func AddServing(q Queryable, serving *mpdata.Serving) (err error)

AddServing adds the information containing in 'serving' to a new serving record in the database.

func AttachMealTags 

func AttachMealTags(q Queryable, meals []*mpdata.Meal) (mts []mpdata.MealWithTags, err error)

func AutoFillMealPlan 

func AutoFillMealPlan(q Queryable, mp *mpdata.MealPlan) (err error)

AutoFillMealPlan assigns servings to every day in 'mp' using the top suggestion for each day.

func AutoFillMealPlanDay 

func AutoFillMealPlanDay(q Queryable, mpID uint64, date time.Time) (err error)

AutoFillMealPlanDay assigns a serving to day 'date' on the meal plan identified by 'mpID' using the top suggestion.

func ClearTables 

func ClearTables(q Queryable) (err error)

ClearTables deletes all records from the entire database.

func Connect 

func Connect() (db *sql.DB, err error)

Connect creates a new connection to the database using DBDriver and DB_SOURCE.

func CountServings 

func CountServings(q Queryable, mpID uint64) (numServings int, err error)

CountServings returns the number of servings in the meal plan identified by 'mpID'.

func CreateTables 

func CreateTables(q Queryable) (err error)

CreateTables creates the database tables if they do not exist.

func DeleteMeal 

func DeleteMeal(q Queryable, mealID uint64) (err error)

DeleteMeal deletes the meal record identified by 'mealID'. If no such meal exists, no error is raised.

func DeleteMealPlan 

func DeleteMealPlan(q Queryable, mpID uint64) (err error)

DeleteMealPlan deletes the meal plan record identified by 'mpID'. If no such meal plan exists, no error is raised.

func DeleteMealTags 

func DeleteMealTags(q Queryable, mealID uint64) (err error)

DeleteMealTags deletes all tags in the database associated with the meal identified by 'mealID'. If no such tags exist, no error is raised.

func DeleteMealWithTags 

func DeleteMealWithTags(q Queryable, mealID uint64) (err error)

DeleteMealWithTags deletes the meal record identified by 'mealID', and all tag records associated with it.

func DeleteServing 

func DeleteServing(q Queryable, mpID uint64, date time.Time) (err error)

DeleteServing deletes the serving at 'date' on the meal plan identified by 'mpID'. If no such serving exists, no error is raised.

func DeleteServings 

func DeleteServings(q Queryable, mpID uint64) (err error)

DeleteServings deletes all servings on the meal plan identified by 'mpID'. If no such servings exist, no error is raised.

func DeleteServingsOf 

func DeleteServingsOf(q Queryable, mealID uint64) (err error)

DeleteServingsOf deletes all servings of the meal identified by 'mealID'. IF no such servings exist, no error is raised.

func DeleteTables 

func DeleteTables(q Queryable) (err error)

DeleteTables drops the database tables if they exist.

func GenerateSuggestions 

func GenerateSuggestions(q Queryable, mpID uint64, date time.Time) (suggs []*mpdata.Suggestion, err error)

GenerateSuggestions calculates a score for each meal in the database based on their suitability for serving on 'date'. These are returned as a list of Suggestions.

func GetDatabaseVersion 

func GetDatabaseVersion(q Queryable) (v uint, err error)

func GetMeal 

func GetMeal(q Queryable, mealID uint64) (meal *mpdata.Meal, err error)

GetMeal fetches information from the database about the meal identified by 'mealID'.

func GetMealPlan 

func GetMealPlan(q Queryable, mpID uint64) (mp *mpdata.MealPlan, err error)

GetMealPlan returns information about the meal plan identified by 'mpID'.

func GetMealPlanWithServings 

func GetMealPlanWithServings(q Queryable, mpID uint64) (mps *mpdata.MealPlanWithServings, err error)

GetMealPlanWithServings returns the information about the meal plan identified by 'mpID' including its servings.

func GetMealTags 

func GetMealTags(q Queryable, mealID uint64) (tags []string, err error)

GetMealTags fetches the list of tags associated with the meal identified by 'mealID'.

func GetMealWithTags 

func GetMealWithTags(q Queryable, mealID uint64) (mt mpdata.MealWithTags, err error)

GetMealWithTags combines GetMeal and GetMealTags.

func GetServing 

func GetServing(q Queryable, mpID uint64, date time.Time) (serving *mpdata.Serving, err error)

GetServing returns information about the meal serving identified by the meal plan identifier 'mpID' and the serving date 'date'.

func GetServings 

func GetServings(q Queryable, mpID uint64) (servings []*mpdata.Serving, err error)

GetServings returns a slice containing the servings that are part of the meal plan identified by 'mpID'.

func InitDB 

func InitDB(debug bool, testData bool) (err error)

InitDB creates the database tables if they don't exist. If 'debug' is true, debug messages are printed. If 'testData' is true, the tables are also cleared and test data are added to them.

func InitialiseVersion 

func InitialiseVersion(q Queryable, debug bool) (err error)

func InsertTestData 

func InsertTestData(q Queryable) (err error)

InsertTestData inserts some predefined meals and meal plans into the database for testing purposes.

func ListAllTags 

func ListAllTags(q Queryable, sortByName bool) (tags []string, err error)

ListAllTags returns a list (without duplicates) of all tags that appear in the database. If the 'sortByName' parameter is true, the tags are sorted into alphabetical order.

func ListMealPlansBetween 

func ListMealPlansBetween(q Queryable, from time.Time, to time.Time) (mps []*mpdata.MealPlan, err error)

ListMealPlansBetween returns a list of all meal plans in the database whose date range (start date to end date) overlaps with the given date range ('from' to 'to').

func ListMeals 

func ListMeals(q Queryable, sortByName bool) (meals []*mpdata.Meal, err error)

ListMeals fetches and returns a list of all meals in the database. If the parameter 'sortByName' is true, the meals are sorted in alphabetical order by name.

func ListMealsWithTags 

func ListMealsWithTags(q Queryable, sortByName bool) (mts []mpdata.MealWithTags, err error)

ListMealsWithTags fetches and returns a list of all meals in the database with their associated tags. If the parameter 'sortByName' is true, the meals are sorted in alphabetical order by name.

func Migrate 

func Migrate(q Queryable, targetVersion uint, debug bool) (err error)

Migrate the database from the current version to 'targetVersion'.

func SearchMeals 

func SearchMeals(q Queryable, words []string, sortByName bool) (meals []*mpdata.Meal, err error)

func SearchMealsWithTags 

func SearchMealsWithTags(q Queryable, words []string, sortByName bool) (mts []mpdata.MealWithTags, err error)

func SetDatabaseVersion 

func SetDatabaseVersion(q Queryable, v uint) (err error)

func ToggleFavourite 

func ToggleFavourite(q Queryable, mealID uint64) (isFavourite bool, err error)

ToggleFavourite toggles the "favourite" status of the meal identified by 'mealID', and returns the new favourite status.

func UpdateMeal 

func UpdateMeal(q Queryable, meal *mpdata.Meal) (err error)

UpdateMeal replaces with the information in the database for the meal identified by 'meal.ID' with the information in 'meal'.

func UpdateMealTags 

func UpdateMealTags(q Queryable, mealID uint64, tags []string) (err error)

UpdateMealTags replaces the tags associated with the meal identified by 'mealID' with the list given by 'tags'.

func UpdateMealWithTags 

func UpdateMealWithTags(q Queryable, mt mpdata.MealWithTags) (err error)

UpdateMealWithTags combines UpdateMeal and UpdateMealTags.

func UpdateNotes 

func UpdateNotes(q Queryable, mpID uint64, notes string) (err error)

UpdateNotes sets the notes associated with the meal plan identified by 'mpID' to 'notes'.

func UpdateSearchText 

func UpdateSearchText(q Queryable, mealID uint64) (err error)

func WithConnection 

func WithConnection(f WithConnectionFunc) (err error)

WithConnection opens a connection to the database, calls 'f' with the database as a parameter, then ensures the database is closed even in the event of an error. If an error occurs when closing the database, a 'FailedCloseError' is returned.

func WithTransaction 

func WithTransaction(db *sql.DB, f WithTransactionFunc) (err error)

WithTransaction begins a transaction on the given database connection, calls 'f' with the transaction as a parameter, then ensures the transaction is committed if 'f' completes successfully or rolled back in the event of an error. If an error occurs when committing or rolling back the transaction, a 'FailedCloseError' is returned.

Types

type FailedCloseError 

type FailedCloseError struct {
	What          string // A string used in the error message to identify what resource was being closed.
	CloseError    error  // The error returned when the resource was closed.
	OriginalError error  // The original error that triggered the closing of the resource.
}

FailedCloseError contains information regarding a situation where an error occurs when closing a resource in response to an earlier error.

func (*FailedCloseError) Error 

func (err *FailedCloseError) Error() (msg string)

Error formats the information contained in 'err' into an error message.

type LoggingQueryable 

type LoggingQueryable struct {
	Q Queryable
}

LoggingQueryable wraps a Queryable while logging all executions of its functions to standard output. It is intended for debugging purposes.

func (LoggingQueryable) Exec 

func (lq LoggingQueryable) Exec(query string, args ...interface{}) (result sql.Result, err error)

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (LoggingQueryable) Prepare 

func (lq LoggingQueryable) Prepare(query string) (stmt *sql.Stmt, err error)

Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement.

func (LoggingQueryable) Query 

func (lq LoggingQueryable) Query(query string, args ...interface{}) (rows *sql.Rows, err error)

Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.

func (LoggingQueryable) QueryRow 

func (lq LoggingQueryable) QueryRow(query string, args ...interface{}) (row *sql.Row)

QueryRow executes a query that is expected to return at most one row. QueryRow always return a non-nil value. Errors are deferred until Row's Scan method is called.

type Migration 

type Migration struct {
	From  uint
	To    uint
	Stmts []string
}

func FindMigration 

func FindMigration(from uint, maxTo uint) (m *Migration)

func (*Migration) Apply 

func (m *Migration) Apply(q Queryable) (err error)

type MigrationError 

type MigrationError struct {
	From    uint
	To      uint
	Message string
}

func (MigrationError) Error 

func (e MigrationError) Error() (msg string)

type Queryable 

type Queryable interface {
	Exec(string, ...interface{}) (sql.Result, error)
	Prepare(string) (*sql.Stmt, error)
	Query(string, ...interface{}) (*sql.Rows, error)
	QueryRow(string, ...interface{}) *sql.Row
}

Queryable represents a type that can be queried (either a *sql.DB or *sql.Tx). See documentation on 'database/sql#DB' for information on the methods in this interface.

type WithConnectionFunc 

type WithConnectionFunc func(*sql.DB) error

WithConnectionFunc represents a function that can be used with WithConnection.

type WithTransactionFunc 

type WithTransactionFunc func(*sql.Tx) error

WithTransactionFunc represents a function that can be used with WithTransaction.

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.