func Save(db *sqlite3.Conn, table string, uid string, obj *jason.Object) error {
handle_err := func(col string, e error) {
if e != nil {
logger.Error("Error inserting %v into %v: %v", col, table, e.Error())
}
}
err := db.Exec(fmt.Sprintf("insert or ignore into %v ( uid ) values (?)", table), uid)
handle_err("", err)
for k, v := range obj.Map() {
if k == "uid" {
continue
}
// Are we a string or a number?
if i, err := v.Int64(); err == nil {
err = db.Exec(fmt.Sprintf("update %v set %v = ? where uid = ?", table, k), i, uid)
handle_err(k, err)
}
if f, err := v.Float64(); err == nil {
err = db.Exec(fmt.Sprintf("update %v set %v = ? where uid = ?", table, k), f, uid)
handle_err(k, err)
}
if s, err := v.String(); err == nil {
err = db.Exec(fmt.Sprintf("update %v set %v = ? where uid = ?", table, k), s, uid)
handle_err(k, err)
}
if arr, err := obj.GetFloat64Array(k); err == nil {
s := fmt.Sprintf("%v", arr)
err = db.Exec(fmt.Sprintf("update %v set %v = ? where uid = ?", table, k), s, uid)
handle_err(k, err)
}
}
return err
}
func fetchItems(db *sqlite3.Conn, a *Account, sql string) ([]*Item, error) {
// find all the items for this account
results := make([]*Item, 0)
args := sqlite3.NamedArgs{"$a": a.Id}
row := make(sqlite3.RowMap)
for s, err := db.Query(sql, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
barcode, barcodeFound := row["barcode"]
desc, descFound := row["product_desc"]
ind, indFound := row["product_ind"]
since, sinceFound := row["strftime('%s', posted)"]
if barcodeFound {
result := new(Item)
result.Id = rowid
result.Barcode = barcode.(string)
if descFound {
result.Desc = desc.(string)
}
if indFound {
result.Index = ind.(int64)
}
if sinceFound {
result.Since = calculateTimeSince(since.(string))
}
result.ForSale = GetVendorProducts(db, rowid)
results = append(results, result)
}
}
return results, nil
}
func (i *Item) Update(db *sqlite3.Conn) error {
// update the Item with with user contribution (description)
args := sqlite3.NamedArgs{"$d": i.Desc,
"$n": i.Index,
"$e": i.UserContributed,
"$i": i.Id}
return db.Exec(UPDATE_ITEM, args)
}
func copyDb(src *sqlite3.Conn, dst *sqlite3.Conn) {
backup, err := src.Backup("main", dst, "main")
if err != nil {
return
}
defer backup.Close()
backup.Step(-1)
}
func AddVendor(db *sqlite3.Conn, vendorId, vendorDisplayName string) (int64, error) {
args := sqlite3.NamedArgs{"$v": vendorId,
"$n": vendorDisplayName}
result := db.Exec(ADD_VENDOR, args)
if result == nil {
pk := getPK(db, "vendor")
return pk, result
}
return BAD_PK, result
}
func getPK(db *sqlite3.Conn, table string) int64 {
// find and return the most recently-inserted
// primary key, based on the table name
sql := fmt.Sprintf("select seq from sqlite_sequence where name='%s'", table)
var rowid int64
for s, err := db.Query(sql); err == nil; err = s.Next() {
s.Scan(&rowid)
}
return rowid
}
func getExistingItem(db *sqlite3.Conn, barcode, desc string) int64 {
// lookup the barcode and product desc
// combination and return the primary key,
// if the product has already been saved
args := sqlite3.NamedArgs{"$b": barcode, "$d": desc}
var rowid int64
rowid = BAD_PK // default value, in case no match
for s, err := db.Query(GET_EXISTING_ITEM, args); err == nil; err = s.Next() {
s.Scan(&rowid)
}
return rowid
}
func GetAllVendors(db *sqlite3.Conn) []*Vendor {
results := make([]*Vendor, 0)
row := make(sqlite3.RowMap)
for s, err := db.Query(GET_VENDORS); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
vendor, vendorFound := row["vendor_id"]
vendorName, vendorNameFound := row["display_name"]
if vendorFound && vendorNameFound {
v := Vendor{Id: rowid, VendorId: vendor.(string), DisplayName: vendorName.(string)}
results = append(results, &v)
}
}
return results
}
func GetVendor(db *sqlite3.Conn, vendorId int64) *Vendor {
result := new(Vendor)
row := make(sqlite3.RowMap)
args := sqlite3.NamedArgs{"$i": vendorId}
for s, err := db.Query(GET_VENDOR, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
vendor, vendorFound := row["vendor_id"]
vendorName, vendorNameFound := row["display_name"]
if vendorFound && vendorNameFound {
result.Id = rowid
result.VendorId = vendor.(string)
result.DisplayName = vendorName.(string)
}
}
return result
}
func GetAccount(db *sqlite3.Conn, email string) (*Account, error) {
// get the account corresponding to this email
result := new(Account)
args := sqlite3.NamedArgs{"$e": email}
row := make(sqlite3.RowMap)
for s, err := db.Query(GET_ACCOUNT, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
api, apiFound := row["api_code"]
if apiFound {
result.APICode = api.(string)
result.Id = rowid
result.Email = email
break
}
}
return result, nil
}
func (i *Item) Add(db *sqlite3.Conn, a *Account) (int64, error) {
// insert the Item object
// but first check if it's a duplicate or not
itemPk := getExistingItem(db, i.Barcode, i.Desc)
if itemPk != BAD_PK {
return itemPk, nil
}
args := sqlite3.NamedArgs{"$b": i.Barcode,
"$d": i.Desc,
"$i": i.Index,
"$e": i.UserContributed,
"$a": a.Id}
result := db.Exec(ADD_ITEM, args)
if result == nil {
pk := getPK(db, "product")
return pk, result
}
return BAD_PK, result
}
func GetVendorProducts(db *sqlite3.Conn, itemId int64) []*VendorProduct {
results := make([]*VendorProduct, 0)
row := make(sqlite3.RowMap)
args := sqlite3.NamedArgs{"$i": itemId}
for s, err := db.Query(GET_VENDOR_PRODUCT, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
vendorPk, vendorPkFound := row["id"]
productCode, productCodeFound := row["product_code"]
if vendorPkFound && productCodeFound {
result := new(VendorProduct)
result.Id = rowid
result.ProductCode = productCode.(string)
result.Vendor = GetVendor(db, vendorPk.(int64))
results = append(results, result)
}
}
return results
}
func GetAllAccounts(db *sqlite3.Conn) ([]*Account, error) {
// find all the accounts currently registered
results := make([]*Account, 0)
row := make(sqlite3.RowMap)
for s, err := db.Query(GET_ACCOUNTS); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
email, emailFound := row["email"]
api, apiFound := row["api_code"]
if emailFound && apiFound {
result := new(Account)
result.APICode = api.(string)
result.Id = rowid
result.Email = email.(string)
results = append(results, result)
}
}
return results, nil
}
func create_table(tbname string, tbcreate string, conn *sqlite3.Conn) (created bool) {
tbcount := -1
created = false
query := "SELECT count(*) as count FROM sqlite_master " +
"WHERE type='table' AND name='" + tbname + "'"
ret, err := conn.Query(query)
if err != nil {
Err("create_table error: '" + tbname + "': " + err.Error())
} else {
ret.Scan(&tbcount)
ret.Close()
if tbcount <= 0 {
err := conn.Exec(tbcreate)
if err != nil {
panic("Can't create table: " + tbname + ", " + err.Error())
} else {
Debug(tbname + " created")
created = true
}
}
}
return created
}
func AddVendorProduct(db *sqlite3.Conn, productCode string, vendorId, itemId int64) error {
args := sqlite3.NamedArgs{"$v": vendorId,
"$p": productCode,
"$i": itemId}
return db.Exec(ADD_VENDOR_PRODUCT, args)
}
func (a *Account) Add(db *sqlite3.Conn) error {
// insert the Account object
args := sqlite3.NamedArgs{"$e": a.Email, "$a": a.APICode}
return db.Exec(ADD_ACCOUNT, args)
}
func NewReaderDB(conn *sqlite3.Conn) *ReaderDB {
var err error
r := &ReaderDB{conn: conn}
r.selectSymbDecl, err = conn.Prepare(`
SELECT st.name, st.unisr, f2.path, st.line, st.col
FROM symbol_decls st, symbol_uses su, files f1, files f2
WHERE
-- symbol use and symbol declaration join
su.dec_file = st.file AND
su.dec_line = st.line AND
su.dec_col = st.col AND
-- symbol declaration to file join
f2.id = st.file AND
-- symbol use and file join
su.file = f1.id AND
-- select input
f1.path = ? AND su.line = ? AND su.col = ?;
`)
if err != nil {
log.Panic("prepare select symbol ", err)
}
r.selectSymbUses, err = conn.Prepare(`
SELECT f2.path, su2.line, su2.col
FROM files f1, files f2, symbol_uses su1, symbol_uses su2
WHERE
-- symbol use and files join
f1.id = su1.file AND
f2.id = su2.file AND
-- symbol uses with same declaration
su1.dec_file = su2.dec_file AND
su1.dec_line = su2.dec_line AND
su1.dec_col = su2.dec_col AND
-- select input
f1.path = ? AND su1.line = ? AND su1.col = ?;
`)
if err != nil {
log.Panic("prepare select symbol uses ", err)
}
r.selectSymbDef, err = conn.Prepare(`
SELECT f1.path, fdd.def_line, fdd.def_col
FROM files f1, files f2, symbol_decls sd, symbol_uses su,
func_decs_defs fdd
WHERE
-- symbol decls and files join
f1.id = fdd.def_file AND
-- symbol use and files join
f2.id = su.file AND
-- symbol uses and symbol decls join
su.dec_file = sd.file AND
su.dec_line = sd.line AND
su.dec_col = sd.col AND
-- symbol decls and symbol defs join
sd.file = fdd.dec_file AND
sd.line = fdd.dec_line AND
sd.col = fdd.dec_col AND
-- select input
f2.path = ? AND su.line = ? AND su.col = ?;
`)
if err != nil {
log.Panic("prepare select symbol def ", err)
}
r.selectSymbDefs, err = conn.Prepare(`
SELECT f1.path, sd1.line, sd1.col
FROM files f1, files f2, symbol_decls sd1, symbol_decls sd2,
symbol_uses su
WHERE
-- symbol decls and files join
f1.id = sd1.file AND
-- file and symbol uses join
f2.id = su.file AND
-- symbol uses and symbol decls join
su.dec_file = sd2.file AND
su.dec_line = sd2.line AND
su.dec_col = sd2.col AND
-- symbol decls and symbol decls same USR
sd1.unisr = sd2.unisr AND
sd1.def = 1 AND
-- select input
f2.path = ? AND su.line = ? AND su.col = ?;
`)
if err != nil {
log.Panic("prepare select symbol defs ", err)
}
return r
}
func (a *Account) Update(db *sqlite3.Conn, newEmail, newApi string) error {
// update this Account's email and API code
args := sqlite3.NamedArgs{"$i": a.Id, "$e": newEmail, "$a": newApi}
return db.Exec(UPDATE_ACCOUNT, args)
}
func (i *Item) Delete(db *sqlite3.Conn) error {
// delete the Item
args := sqlite3.NamedArgs{"$i": i.Id}
return db.Exec(DELETE_ITEM, args)
}
func (i *Item) Unfavorite(db *sqlite3.Conn) error {
// update the Item, to show it is not a favorite for this Account
args := sqlite3.NamedArgs{"$i": i.Id}
return db.Exec(UNFAVORITE_ITEM, args)
}
func NewWriterDB(conn *sqlite3.Conn) *WriterDB {
var err error
r := &WriterDB{conn: conn}
// DB selects
r.selectFileInfo, err = conn.Prepare(`
SELECT file_info FROM files WHERE path = ?;
`)
if err != nil {
log.Panic("prepare select hash ", err)
}
r.selectFileDeps, err = conn.Prepare(`
SELECT f1.path FROM files f1, files f2, files_deps d
WHERE f2.path = ? AND f2.id = d.depend AND f1.id = d.id;
`)
if err != nil {
log.Panic("prepare select hash ", err)
}
// DB inserts
r.insertFile, err = conn.Prepare(`
INSERT INTO files(path, file_info) VALUES (?, ?);
`)
if err != nil {
log.Panic("prepare insert files ", err)
}
r.insertSymb, err = conn.Prepare(`
INSERT OR IGNORE INTO symbol_decls(name, unisr, file, line, col, param)
SELECT ?, ?, id, ?, ?, ? FROM files
WHERE path = ?;
`)
if err != nil {
log.Panic("prepare insert symbol ", err)
}
r.insertFuncDef, err = conn.Prepare(`
INSERT OR IGNORE INTO symbol_decls(name, unisr, file, line, col, def)
SELECT ?, ?, id, ?, ?, 1 FROM files
WHERE path = ?;
`)
if err != nil {
log.Panic("prepare insert func def ", err)
}
r.insertFuncDecDef, err = conn.Prepare(`
INSERT OR IGNORE INTO func_decs_defs
SELECT f1.id, ?, ?, f2.id, ?, ? FROM files f1, files f2
WHERE f1.path = ? AND f2.path = ?;
`)
if err != nil {
log.Panic("prepare insert func dec/def ", err)
}
r.insertSymbUse, err = conn.Prepare(`
INSERT OR IGNORE INTO symbol_uses
SELECT 0, f1.id, ?, ?, f2.id, ?, ? FROM files f1, files f2
WHERE f1.path = ? AND f2.path = ?;
`)
if err != nil {
log.Panic("preapre insert symbol use ", err)
}
r.insertFuncCall, err = conn.Prepare(`
INSERT OR REPLACE INTO symbol_uses
SELECT 1, f1.id, ?, ?, f2.id, ?, ? FROM files f1, files f2
WHERE f1.path = ? AND f2.path = ?;
`)
if err != nil {
log.Panic("preapre insert func call ", err)
}
r.insertDepend, err = conn.Prepare(`
INSERT OR IGNORE INTO files_deps
SELECT f1.id, f2.id FROM files f1, files f2
WHERE f1.path = ? AND f2.path = ?;
`)
if err != nil {
log.Panic("prepare insert files deps ", err)
}
// DB (only) delete
r.delFileRef, err = conn.Prepare(`
DELETE FROM files WHERE path = ?;
`)
if err != nil {
log.Panic("prepare delete file ", err)
}
r.delDepends, err = conn.Prepare(`
DELETE FROM files WHERE id IN (
SELECT d.id FROM files_deps d, files f
WHERE f.path = ? AND d.depend = f.id
);
`)
if err != nil {
log.Panic("prepare delete dependencies ", err)
}
return r
}