Example #1
0
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
}
Example #2
0
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
}
Example #3
0
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)
}
Example #4
0
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)
}
Example #5
0
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
}
Example #6
0
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
}
Example #7
0
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
}
Example #8
0
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
}
Example #9
0
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
}
Example #10
0
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
}
Example #11
0
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
}
Example #12
0
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
}
Example #13
0
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
}
Example #14
0
File: db.go Project: joseche/cagent
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
}
Example #15
0
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)
}
Example #16
0
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)
}
Example #17
0
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
}
Example #18
0
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)
}
Example #19
0
func (i *Item) Delete(db *sqlite3.Conn) error {
	// delete the Item
	args := sqlite3.NamedArgs{"$i": i.Id}
	return db.Exec(DELETE_ITEM, args)
}
Example #20
0
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)
}
Example #21
0
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
}