func main() {
db, err := ql.OpenMem()
if err != nil {
panic(err)
}
_, _, err = db.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
CREATE TABLE data (t time, h uint, bps float);
COMMIT;
`)
if err != nil {
panic(err)
}
for _, date := range listDates() {
addToDB(date, db)
}
rss, _, err := db.Run(ql.NewRWCtx(), "SELECT h, min(bps),avg(bps),max(bps), count(t) FROM data GROUP BY h;")
if err != nil {
panic(err)
}
for _, rs := range rss {
if err := rs.Do(false, func(data []interface{}) (bool, error) {
fmt.Printf("hour %2d, min %4.1f, avg %4.1f, max %4.1f, count %3d\n", data[0], data[1], data[2], data[3], data[4])
return true, nil
}); err != nil {
panic(err)
}
fmt.Println("----")
}
}
func (mb *Mailbox) GetMessage() (*Message, error) {
db, err := OpenDB()
if db != nil {
defer db.Close()
}
if err != nil {
return nil, err
}
rss, _, err := db.Run(ql.NewRWCtx(), `
SELECT id, receiveCount, body, mailbox, createdAt
FROM message
WHERE mailbox == $1
LIMIT 1;
`, mb.Id)
var msg Message
if len(rss) > 0 {
r, _ := rss[0].FirstRow()
if r == nil {
return nil, nil
}
rss[0].Do(false, func(data []interface{}) (bool, error) {
msg = Message{
Id: data[0].(string),
ReceiveCount: data[1].(int64),
Body: data[2].(string),
MailboxId: data[3].(string),
CreatedAt: data[4].(time.Time),
}
return false, nil
})
} else {
return nil, nil
}
if err != nil {
return nil, err
}
msg.ReceiveCount++
_, _, err = db.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
UPDATE message
SET receiveCount = $1
WHERE id == $2;
COMMIT;
`, msg.ReceiveCount, msg.Id)
if err != nil {
return nil, err
}
return &msg, err
}
func (key *AccessKey) Create() error {
if key.FullAccess == false {
mb, err := Find(key.MailboxId)
if err != nil {
return err
}
if mb == nil {
return errors.New("Can't generate key. Mailbox not found.")
}
key.Name = key.MailboxId
} else {
if key.Name == "" {
key.Name = GenerateIdentifier()
}
}
if KeyExists(key.Name) {
return errors.New("Key already exists")
}
key.Secret = GenerateIdentifier()
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
INSERT INTO accessToken (
mailbox, token, fullAccess, name
) VALUES (
$1, $2, $3, $4
);
COMMIT;
`, key.MailboxId, key.Secret, key.FullAccess, key.Name)
return err
}
// All returns a slice of all mailboxes.
func All() ([]Mailbox, error) {
var mbxs []Mailbox
rss, _, err := DB.Run(ql.NewRWCtx(), `
SELECT id, lastCheckedInAt, version, host FROM mailbox`)
if err != nil {
return nil, err
}
rss[0].Do(false, func(data []interface{}) (bool, error) {
mb := Mailbox{
Id: data[0].(string),
}
if data[1] != nil {
mb.LastSeen = data[1].(time.Time)
}
if data[2] != nil {
mb.Version = data[2].(string)
}
if data[3] != nil {
mb.Host = data[3].(string)
}
mbxs = append(mbxs, mb)
return true, nil
})
return mbxs, nil
}
// DeployMessage accepts a deployment identifier and adds this mailbox to its
// deployment. A new message will be available for this deployment in the
// mailbox.
func (mb *Mailbox) DeployMessage(depId string) (*Message, error) {
deployment, err := FindDeployment(depId)
if err != nil {
return nil, err
}
if deployment == nil {
return nil, errors.New(fmt.Sprintf("Deployment %s not found", depId))
}
msg := &Message{
Id: GenerateIdentifier(),
Mailbox: mb.Id,
CreatedAt: time.Now(),
Deployment: deployment.Id,
Body: deployment.MessageBody,
}
_, _, err = DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
INSERT INTO message (
id, receiveCount, mailbox, createdAt, deployment, deleted
) VALUES (
$1, $2, $3, $4, $5, false
);
UPDATE deployment
SET totalMessages = totalMessages + 1
WHERE id == $5;
COMMIT;
`, msg.Id, msg.ReceiveCount, msg.Mailbox, msg.CreatedAt,
deployment.Id)
return msg, err
}
func addToDB(date string, db *ql.DB) {
csvfile, err := os.Open(DATA_DIR + date + "-iperf.csv")
checkErr(err)
defer csvfile.Close()
reader := csv.NewReader(csvfile)
reader.FieldsPerRecord = -1 // see the Reader struct information below
rawCSVdata, err := reader.ReadAll()
checkErr(err)
// sanity check, display to standard output
for _, each := range rawCSVdata {
bps, err := strconv.ParseUint(each[len(each)-1], 10, 64)
if err != nil {
fmt.Printf("err parsing bsb: %v in line %v\n", err, each)
bps = 0
}
bpsf := float64(bps) / 1000000.0
t, err := strconv.ParseInt(each[len(each)-2], 10, 64)
if err != nil {
fmt.Printf("err parsing time: %v in line %v\n", err, each)
continue
}
tm := time.Unix(t, 0)
_, _, err = db.Run(ql.NewRWCtx(), fmt.Sprintf("BEGIN TRANSACTION;INSERT INTO data VALUES(parseTime(\"2006-01-02 15:04:05.999999999 -0700 MST\",\"%v\"), %d, %v);COMMIT;", tm, tm.Hour(), bpsf))
if err != nil {
panic(err)
}
}
}
func FindDeployment(id string) (*Deployment, error) {
resp, _, err := DB.Run(ql.NewRWCtx(), `
SELECT id, name, deployedAt, deployedBy, totalMessages, messageBody, asset
FROM deployment
WHERE id == $1;
SELECT count(*)
FROM message
WHERE deployment == $1;
`, id)
if err != nil {
return nil, err
}
var deployment *Deployment
resp[0].Do(false, func(data []interface{}) (bool, error) {
d := readDeploymentData(data)
deployment = &d
return false, nil
})
if deployment != nil {
resp[1].Do(false, func(data []interface{}) (bool, error) {
deployment.TotalMessages = data[0].(int64)
return false, nil
})
}
return deployment, nil
}
// GetMessage returns a message from the mailbox. Once the message is processed
// it should be removed from the queue with Delete.
func (mb *Mailbox) GetMessage() (*Message, error) {
rss, _, err := DB.Run(ql.NewRWCtx(), `
SELECT message.id, deployment.messageBody, message.mailbox,
message.deployment, message.receiveCount, message.lastReceivedAt,
message.createdAt, message.deleted
FROM message, deployment
WHERE
message.mailbox == $1
AND message.deleted == false
AND deployment.id == message.deployment
LIMIT 1;
`, mb.Id)
if err != nil {
return nil, err
}
var msg *Message
if len(rss) > 0 {
r, _ := rss[0].FirstRow()
if r == nil {
return nil, nil
}
rss[0].Do(false, func(data []interface{}) (bool, error) {
msg = readMessageStruct(data)
return false, nil
})
} else {
return nil, nil
}
if err != nil {
return nil, err
}
msg.ReceiveCount++
_, _, err = DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
UPDATE message
SET receiveCount = $1
WHERE id == $2;
COMMIT;
`, msg.ReceiveCount, msg.Id)
if err != nil {
return nil, err
}
return msg, err
}
func do() (err error) {
oDB := flag.String("db", "ql.db", "The DB file to open. It'll be created if missing")
oFlds := flag.Bool("fld", false, "Show recordset's field names.")
flag.Parse()
var src string
switch n := flag.NArg(); n {
case 0:
b, err := ioutil.ReadAll(bufio.NewReader(os.Stdin))
if err != nil {
return err
}
src = string(b)
default:
a := make([]string, n)
for i := range a {
a[i] = flag.Arg(i)
}
src = strings.Join(a, " ")
}
db, err := ql.OpenFile(*oDB, &ql.Options{CanCreate: true})
if err != nil {
return err
}
defer func() {
ec := db.Close()
switch {
case ec != nil && err != nil:
log.Println(ec)
case ec != nil:
err = ec
}
}()
src = "BEGIN TRANSACTION; " + src + "; COMMIT;"
l, err := ql.Compile(src)
if err != nil {
log.Println(src)
return err
}
rs, i, err := db.Execute(ql.NewRWCtx(), l)
if err != nil {
a := strings.Split(strings.TrimSpace(fmt.Sprint(l)), "\n")
return fmt.Errorf("%v: %s", err, a[i])
}
if len(rs) == 0 {
return
}
return rs[len(rs)-1].Do(*oFlds, func(data []interface{}) (bool, error) {
fmt.Println(str(data))
return true, nil
})
}
// qlQuery provides a wrapper to compile a ql query
func qlQuery(db *qlw, key string, wraptx bool, arg ...interface{}) ([]ql.Recordset, int, error) {
var err error
if list, err := qlCompile(key, wraptx); err == nil {
return db.Execute(ql.NewRWCtx(), list, arg...)
}
return []ql.Recordset(nil), 0, err
}
func CreateDB() error {
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
CREATE TABLE properties (
key string,
value string
);
INSERT INTO properties
VALUES ("dbversion", "2");
CREATE TABLE message (
id string,
receiveCount int,
mailbox string,
createdAt time,
lastReceivedAt time,
deployment string,
deleted bool
);
CREATE TABLE mailbox (
id string,
createdAt time,
version string,
host string,
lastCheckedInAt time
);
CREATE TABLE accessToken (
mailbox string,
token string,
name string,
fullAccess bool
);
CREATE TABLE deployment (
id string,
messageBody string,
name string,
deployedAt time,
deployedBy string,
totalMessages int,
open bool,
asset string
);
CREATE TABLE deploymentResponse (
deployment string,
mailbox string,
response string,
respondedAt time,
isError bool
);
COMMIT;`)
return err
}
func (m *Message) Save() error {
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
UPDATE message
SET receiveCount = $2, mailbox = $3, createdAt = $4, deployment = $5,
deleted = $6
WHERE id = $1`, m.Id, m.Mailbox, m.CreatedAt, m.Deployment, m.Deleted)
return err
}
// Marks a message as deleted in the mailbox.
func DeleteMessage(msgId string) error {
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
UPDATE message
SET deleted = true
WHERE id == $1;
COMMIT;
`, msgId)
return err
}
func (dp *Deployment) AddResponse(mailbox, response string, isErr bool) error {
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
INSERT INTO deploymentResponse
(deployment, mailbox, response, respondedAt, isError)
VALUES ($1,$2,$3,$4,$5);
COMMIT;
`, dp.Id, mailbox, response, time.Now(), isErr)
return err
}
func runMigration(version, sql string) error {
log.Infof("Upgrading database to version %s", version)
fullSql := `
BEGIN TRANSACTION;
` + sql + `
UPDATE properties SET value = $1 WHERE key == "dbversion";
COMMIT;`
_, _, err := DB.Run(ql.NewRWCtx(), fullSql, version)
return err
}
func (mb *Mailbox) Checkin(host, version string) error {
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
UPDATE mailbox
SET lastCheckedInAt = $1, host = $2, version = $3
WHERE id == $4;
COMMIT;
`, time.Now(), host, version, mb.Id)
return err
}
func (dp *Deployment) Save() error {
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
UPDATE deployment
SET name = $2, deployedAt = $3, deployedBy = $4, totalMessages = $5,
messageBody = $6
WHERE id == $1;
COMMIT;
`, dp.Id, dp.Name, dp.DeployedAt, dp.DeployedBy, dp.TotalMessages, dp.MessageBody)
return err
}
// Purge will mark all messages in the mailbox as deleted. They will no longer
// be availble when polling for messages.
func (mb *Mailbox) Purge() (int64, error) {
ctx := ql.NewRWCtx()
_, _, err := DB.Run(ctx, `
BEGIN TRANSACTION;
UPDATE message
SET deleted = true
WHERE mailbox == $1;
COMMIT;
`, mb.Id)
return ctx.RowsAffected, err
}
func (msg *Message) Completed() error {
db, err := OpenDB()
if err != nil {
return err
}
_, _, err = db.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
DELETE FROM message
WHERE id = $2;
COMMIT
`, msg.ReceiveCount+1)
return err
}
// Search will return a slice of mailboxes whos identifiers match a given
// pattern. This pattern can be any valid regex. However it will automatically
// convert '*' to '.*'. This allows * to be used as a simple wildcard when
// searching by pattern.
func Search(rawPattern string) ([]Mailbox, error) {
mbxs := []Mailbox{}
pattern := strings.ToLower(strings.Replace(rawPattern, "*", ".*", -1))
rss, _, err := DB.Run(ql.NewRWCtx(),
` SELECT id FROM mailbox WHERE id LIKE $1`, pattern)
if err != nil {
return nil, err
}
rss[0].Do(false, func(data []interface{}) (bool, error) {
mb := Mailbox{Id: data[0].(string)}
mbxs = append(mbxs, mb)
return true, nil
})
return mbxs, nil
}
func (m *Message) Create() error {
if m.Id == "" {
m.Id = GenerateIdentifier()
}
_, _, err := DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
INSERT INTO message (
id, receiveCount, mailbox, createdAt, deployment, deleted
) VALUES (
$1, $2, $3, $4, $5, false
);
COMMIT;
`, m.Id, m.ReceiveCount, m.Mailbox, m.CreatedAt, m.Deployment)
return err
}
// MessageCount returns a cound of all pending messages in the mailbox. This
// will not return messages that were marked as deleted.
func (mb *Mailbox) MessageCount() (int64, error) {
rss, _, err := DB.Run(ql.NewRWCtx(), `
SELECT count()
FROM message
WHERE mailbox == $1 AND deleted == false
`, mb.Id)
if err != nil {
return -1, err
}
var count int64
rss[0].Do(false, func(data []interface{}) (bool, error) {
count = data[0].(int64)
return false, nil
})
return count, nil
}
func Revoke(name string) error {
ctx := ql.NewRWCtx()
_, _, err := DB.Run(ctx, `
BEGIN TRANSACTION;
DELETE FROM accessToken
WHERE name == $1;
COMMIT;
`, name)
if err != nil {
return err
}
if ctx.RowsAffected == 0 {
return errors.New("Key not found")
}
return nil
}
func FindKeyByName(name string) (*AccessKey, error) {
res, _, err := DB.Run(ql.NewRWCtx(), `
SELECT name, mailbox, fullAccess, token
FROM accessToken
WHERE name == $1
LIMIT 1;
`, name)
if err != nil {
return nil, err
}
key := &AccessKey{}
res[0].Do(false, func(data []interface{}) (bool, error) {
ql.Unmarshal(key, data)
return false, nil
})
return key, nil
}
func AssetPending(md5 string) (bool, error) {
rss, _, err := DB.Run(ql.NewRWCtx(), `
SELECT count(message.id)
FROM message, deployment
WHERE message.deployment == deployment.id
AND message.deleted == false
AND deployment.asset == $1`, md5)
if err != nil {
return false, err
}
var count int64 = 1
rss[0].Do(false, func(data []interface{}) (bool, error) {
count = data[0].(int64)
return true, nil
})
return count != 0, nil
}
func KeyExists(name string) bool {
res, _, err := DB.Run(ql.NewRWCtx(), `
SELECT count(*)
FROM accessToken
WHERE name == $1
LIMIT 1;
`, name)
if err != nil {
return false
}
var count int64
res[0].Do(false, func(data []interface{}) (bool, error) {
count = data[0].(int64)
return false, nil
})
return count == 1
}
func GetDBVersion() (string, error) {
var (
dbVersion = ""
)
rss, _, err := DB.Run(ql.NewRWCtx(), `
SELECT value
FROM properties
WHERE key == "dbversion"`)
if err != nil {
return dbVersion, err
}
rss[0].Do(false, func(data []interface{}) (bool, error) {
dbVersion = data[0].(string)
return false, nil
})
return dbVersion, err
}
// Find will return a mailbox or nil for a given mailbox identifier
func Find(id string) (*Mailbox, error) {
var mbx *Mailbox
rss, _, err := DB.Run(ql.NewRWCtx(),
` SELECT id, lastCheckedInAt FROM mailbox WHERE id==$1`, id)
if err != nil {
return nil, err
}
rss[0].Do(false, func(data []interface{}) (bool, error) {
mbx = &Mailbox{
Id: data[0].(string),
}
if data[1] != nil {
mbx.LastSeen = data[1].(time.Time)
}
return false, nil
})
return mbx, nil
}
func AllKeys() ([]*AccessKey, error) {
res, _, err := DB.Run(ql.NewRWCtx(), `
SELECT name, mailbox, fullAccess, token
FROM accessToken`)
if err != nil {
return nil, err
}
keys := []*AccessKey{}
err = res[0].Do(false, func(data []interface{}) (bool, error) {
key := &AccessKey{}
if err := ql.Unmarshal(key, data); err != nil {
return false, err
}
keys = append(keys, key)
return true, nil
})
return keys, nil
}
func Deregister(id string) error {
mb, err := Find(id)
if err != nil {
return err
}
if mb == nil {
return errors.New("Mailbox not found")
}
_, _, err = DB.Run(ql.NewRWCtx(), `
BEGIN TRANSACTION;
DELETE FROM mailbox
WHERE id == $1;
DELETE FROM message
WHERE mailbox == $1;
DELETE FROM accessToken
WHERE mailbox == $1;
COMMIT;
`, mb.Id)
return err
}