database

README

This is the local datastore on the Raspberry Pi device

Documentation

Index

• Constants
• Variables
• func AddVendor(db *sqlite3.Conn, vendorId, vendorDisplayName string) (int64, error)
• func AddVendorProduct(db *sqlite3.Conn, productCode string, vendorId, itemId int64) error
• func InitializeDB(coords ConnCoordinates) (*sqlite3.Conn, error)
• type Account
  • func FetchOrCreateDefaultAccount(db *sqlite3.Conn) (*Account, error)
  • func GetAccount(db *sqlite3.Conn, email string) (*Account, error)
  • func GetAllAccounts(db *sqlite3.Conn) ([]*Account, error)
  • func GetDesignatedAccount(db *sqlite3.Conn) (*Account, error)
  • func (a *Account) Add(db *sqlite3.Conn) error
  • func (a *Account) Update(db *sqlite3.Conn, newEmail, newApi string) error
• type ConnCoordinates
• type Item
  • func GetFavoriteItems(db *sqlite3.Conn, a *Account) ([]*Item, error)
  • func GetItems(db *sqlite3.Conn, a *Account) ([]*Item, error)
  • func GetSingleItem(db *sqlite3.Conn, a *Account, id int64) (*Item, error)
  • func (i *Item) Add(db *sqlite3.Conn, a *Account) (int64, error)
  • func (i *Item) Delete(db *sqlite3.Conn) error
  • func (i *Item) Favorite(db *sqlite3.Conn) error
  • func (i *Item) Unfavorite(db *sqlite3.Conn) error
  • func (i *Item) Update(db *sqlite3.Conn) error
• type Vendor
  • func GetAllVendors(db *sqlite3.Conn) []*Vendor
  • func GetVendor(db *sqlite3.Conn, vendorId int64) *Vendor
• type VendorProduct
  • func GetVendorProducts(db *sqlite3.Conn, itemId int64) []*VendorProduct

Constants

const (
	// Default database filename
	SQLITE_PATH = "/data"
	SQLITE_FILE = "PiScanDB.sqlite"

	// Default sql definitions file
	TABLE_SQL_DEFINITIONS = "tables.sql"

	// Execution constants
	BAD_PK = -1

	// Default Account (for those who don't want to register)
	ANONYMOUS_EMAIL = "anonymous@example.org"

	// Prepared Statements
	// User accounts
	ADD_ACCOUNT    = "insert into account (email, api_code) values ($e, $a)"
	GET_ACCOUNT    = "select id, api_code from account where email = $e"
	GET_ACCOUNTS   = "select id, email, api_code from account"
	UPDATE_ACCOUNT = "update account set email = $e, api_code = $a where id = $i"

	// Products
	ADD_ITEM           = "insert into product (barcode, product_desc, product_ind, is_edit, account) values ($b, $d, $i, $e, $a)"
	UPDATE_ITEM        = "update product set product_desc = $d, product_ind = $n, is_edit = $e where id = $i"
	GET_EXISTING_ITEM  = "select id from product where barcode = $b and product_desc = $d"
	GET_ITEMS          = "select id, barcode, product_desc, product_ind, strftime('%s', posted) from product where account = $a order by posted desc"
	GET_FAVORITE_ITEMS = "" /* 142-byte string literal not displayed */
	DELETE_ITEM        = "delete from product where id = $i"
	FAVORITE_ITEM      = "update product set is_favorite = 1 where id = $i"
	UNFAVORITE_ITEM    = "update product set is_favorite = 0 where id = $i"

	// Commerce
	ADD_VENDOR         = "insert into vendor (vendor_id, display_name) values ($v, $n)"
	ADD_VENDOR_PRODUCT = "insert into product_availability (vendor, product_code, product) values ($v, $p, $i)"
	GET_VENDOR         = "select id, vendor_id, display_name from vendor where id = $i"
	GET_VENDORS        = "select distinct id, vendor_id, display_name from vendor"
	GET_VENDOR_PRODUCT = "select pa.id, v.id, pa.product_code from vendor v, product_availability pa where v.id = pa.vendor and pa.product = $i"
)

Variables

var (
	INTERVALS   = []string{"year", "month", "day", "hour", "minute"}
	SECONDS_PER = map[string]int64{"minute": 60, "hour": 3600, "day": 86400, "month": 2592000, "year": 31536000}
)

Functions

func AddVendor

func AddVendor(db *sqlite3.Conn, vendorId, vendorDisplayName string) (int64, error)

func AddVendorProduct

func AddVendorProduct(db *sqlite3.Conn, productCode string, vendorId, itemId int64) error

func InitializeDB

func InitializeDB(coords ConnCoordinates) (*sqlite3.Conn, error)

Types

type Account

type Account struct {
	Id      int64
	Email   string
	APICode string
}

func FetchOrCreateDefaultAccount

func FetchOrCreateDefaultAccount(db *sqlite3.Conn) (*Account, error)

FetchOrCreateDefaultAccount returns the existing local client account (in single-user mode), or creates it, if it does not exist yet

func GetAccount

func GetAccount(db *sqlite3.Conn, email string) (*Account, error)

func GetAllAccounts

func GetAllAccounts(db *sqlite3.Conn) ([]*Account, error)

func GetDesignatedAccount

func GetDesignatedAccount(db *sqlite3.Conn) (*Account, error)

GetDesignatedAccount implements single-user mode (for now): it returns either the anonymous account, or the first non-anonymous account found on the sqlite database

func (*Account) Add

func (a *Account) Add(db *sqlite3.Conn) error

func (*Account) Update

func (a *Account) Update(db *sqlite3.Conn, newEmail, newApi string) error

type ConnCoordinates

type ConnCoordinates struct {
	DBPath       string
	DBFile       string
	DBTablesPath string
}

type Item

type Item struct {
	Id              int64
	Desc            string
	Barcode         string
	Index           int64
	Since           string
	UserContributed bool
	ForSale         []*VendorProduct
}

func GetFavoriteItems

func GetFavoriteItems(db *sqlite3.Conn, a *Account) ([]*Item, error)

func GetItems

func GetItems(db *sqlite3.Conn, a *Account) ([]*Item, error)

func GetSingleItem

func GetSingleItem(db *sqlite3.Conn, a *Account, id int64) (*Item, error)

func (*Item) Add

func (i *Item) Add(db *sqlite3.Conn, a *Account) (int64, error)

func (*Item) Delete

func (i *Item) Delete(db *sqlite3.Conn) error

func (*Item) Favorite

func (i *Item) Favorite(db *sqlite3.Conn) error

func (*Item) Unfavorite

func (i *Item) Unfavorite(db *sqlite3.Conn) error

func (*Item) Update

func (i *Item) Update(db *sqlite3.Conn) error

type Vendor

type Vendor struct {
	Id          int64
	VendorId    string
	DisplayName string
}

func GetAllVendors

func GetAllVendors(db *sqlite3.Conn) []*Vendor

func GetVendor

func GetVendor(db *sqlite3.Conn, vendorId int64) *Vendor

type VendorProduct

type VendorProduct struct {
	Id          int64
	ProductCode string
	Vendor      *Vendor
}

func GetVendorProducts

func GetVendorProducts(db *sqlite3.Conn, itemId int64) []*VendorProduct