func getEvents(c *gin.Context) {
user := c.MustGet("user").(*models.User)
q := squirrel.Select("event.*, p.name as project_name").
From("event").
LeftJoin("project as p on event.project_id=p.id").
OrderBy("created desc")
projectObj, exists := c.Get("project")
if exists == true {
// limit query to project
project := projectObj.(models.Project)
q = q.Where("event.project_id=?", project.ID)
} else {
q = q.LeftJoin("project__user as pu on pu.project_id=p.id").
Where("p.id IS NULL or pu.user_id=?", user.ID)
}
var events []models.Event
query, args, _ := q.ToSql()
if _, err := database.Mysql.Select(&events, query, args...); err != nil {
panic(err)
}
for i, evt := range events {
if evt.ObjectID == nil || evt.ObjectType == nil {
continue
}
var q squirrel.SelectBuilder
switch *evt.ObjectType {
case "task":
q = squirrel.Select("tpl.playbook as name").
From("task").
Join("project__template as tpl on task.template_id=tpl.id")
default:
continue
}
query, args, _ := q.ToSql()
name, err := database.Mysql.SelectNullStr(query, args...)
if err != nil {
panic(err)
}
if name.Valid == true {
events[i].ObjectName = name.String
}
}
c.JSON(200, events)
}
func (d *DbMap) ManyToManyBuilder(m Model, mapping Mapping, selectStr string) (sq.SelectBuilder, error) {
var slct string
slct = selectStr
if selectStr == "" {
slct = "*"
}
other := mapping.OtherModel(m)
kname := m.KeyName()
k := m.Key()
w := sq.Select("*").From(d.TableName(mapping)).Where(sq.Eq{kname: k})
rows, err := d.Query(mapping, w)
if err != nil {
return sq.SelectBuilder{}, err
}
keys := make([]interface{}, 0)
for _, r := range rows {
if itr, ok := r.(Mapping); ok {
keys = append(keys, itr.OtherKey(m))
}
}
kname = other.KeyName()
return sq.Select(slct).From(d.TableName(other)).Where(sq.Eq{kname: keys}), nil
}
// Wrapper for querying a Database struct
func Count(db *sql.DB, timeRange *database.TimeRange) (*database.Count, error) {
// Query
queryBuilder := sq.
Select("COUNT(*) AS total", "COUNT(DISTINCT ip) AS uniqueCount").
From("visits")
// Add time constraints if timeRange provided
if timeRange != nil {
if !timeRange.Start.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time >= %d", timeRange.Start.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
if !timeRange.End.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time <= %d", timeRange.End.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
}
query, _, err := queryBuilder.ToSql()
if err != nil {
return nil, err
}
// Exec query
count := database.Count{}
err = db.QueryRow(query).Scan(&count.Total, &count.Unique)
if err != nil {
return nil, err
}
return &count, nil
}
// lookup() + ロック
func (self *Handle) LookupForUpdateX(obj Model, ids ...interface{}) bool {
b := sq.Select(obj.GetFields()).From(obj.GetTable())
for i, field := range obj.GetPrimaryKeys() {
b = b.Where(sq.Eq{field: ids[i]})
}
b = b.Suffix("FOR UPDATE")
s, args, err := b.ToSql()
if err != nil {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"ids": ids,
"err": err,
}).Panic("Fail To Build lookupForUpdate SQL")
}
row := self.Conn.QueryRow(s, args...)
err = self.RowScan(obj, row)
if err == sql.ErrNoRows {
return false
} else if err != nil {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"ids": ids,
"err": err,
}).Panic("Fail to lookupForUpdate()")
}
return true
}
// LocationRecords ...
func (sdb *SQLiteNewtonDB) LocationRecords(ownerID, since, until int64, limit uint64, ascending bool) ([]LocationRecord, error) {
builder := squirrel.Select("timestamp, latitude, longitude, ownerID").From("location_records")
builder = builder.Where(squirrel.Eq{"owner_id": ownerID})
if ascending {
builder = builder.OrderBy("timestamp ASC")
} else {
builder = builder.OrderBy("timestamp DESC")
}
if limit > 0 {
builder = builder.Limit(limit)
}
if since != -1 {
builder = builder.Where(squirrel.Expr("timestamp > ?", since))
}
if until != -1 {
builder = builder.Where(squirrel.Expr("timestamp < ?", until))
}
query, args, err := builder.ToSql()
if err != nil {
return nil, err
}
records := []LocationRecord{}
err = sdb.db.Select(&records, query, args...)
if err != nil {
return nil, NewtonErr(err)
}
return records, nil
}
func userEventsGet(w http.ResponseWriter, r *http.Request) {
user_id := mux.Vars(r)["id"]
query, args, err := sq.Select("e.name", "e.start_time", "e.end_time", "e.active",
"e.ongoing", "e.city", "e.latitude", "e.longitude",
"ec.cat_cd", "ec.cat_val").
From("user u").
Join("user_events ue ON u.user_id = ue.user_id").
Join("event e ON ue.event_id = e.event_id ").
LeftJoin("event_category ec ON e.cat_cd = ec.cat_cd").
Where(sq.Eq{"u.user_id": user_id}).
ToSql()
errors.CheckHttpErr(err != nil, "Internal Error", 500)
events := make([]model.Event, 0)
rows, err := db.DB.Queryx(query, args...)
errors.CheckHttpErr(err != nil, "Internal Error", 500)
for rows.Next() {
var event model.Event
err := rows.StructScan(&event)
errors.CheckHttpErr(err != nil, "Internal Error", 500)
events = append(events, event)
}
out, err := json.MarshalIndent(events, "", " ")
errors.CheckHttpErr(err != nil, "Internal Error", 500)
w.Write(out)
}
func ProjectMiddleware(c *gin.Context) {
user := c.MustGet("user").(*models.User)
projectID, err := util.GetIntParam("project_id", c)
if err != nil {
return
}
query, args, _ := squirrel.Select("p.*").
From("project as p").
Join("project__user as pu on pu.project_id=p.id").
Where("p.id=?", projectID).
Where("pu.user_id=?", user.ID).
ToSql()
var project models.Project
if err := database.Mysql.SelectOne(&project, query, args...); err != nil {
if err == sql.ErrNoRows {
c.AbortWithStatus(404)
return
}
panic(err)
}
c.Set("project", project)
c.Next()
}
func InventoryMiddleware(c *gin.Context) {
project := c.MustGet("project").(models.Project)
inventoryID, err := util.GetIntParam("inventory_id", c)
if err != nil {
return
}
query, args, _ := squirrel.Select("*").
From("project__inventory").
Where("project_id=?", project.ID).
Where("id=?", inventoryID).
ToSql()
var inventory models.Inventory
if err := database.Mysql.SelectOne(&inventory, query, args...); err != nil {
if err == sql.ErrNoRows {
c.AbortWithStatus(404)
return
}
panic(err)
}
c.Set("inventory", inventory)
c.Next()
}
func EnvironmentMiddleware(c *gin.Context) {
project := c.MustGet("project").(models.Project)
envID, err := util.GetIntParam("environment_id", c)
if err != nil {
return
}
query, args, _ := squirrel.Select("*").
From("project__environment").
Where("project_id=?", project.ID).
Where("id=?", envID).
ToSql()
var env models.Environment
if err := database.Mysql.SelectOne(&env, query, args...); err != nil {
if err == sql.ErrNoRows {
c.AbortWithStatus(404)
return
}
panic(err)
}
c.Set("environment", env)
c.Next()
}
func selectTest(dbmap *gorp.DbMap) User {
// パターン 1
dbmap.AddTableWithName(User{}, "users").SetKeys(false, "Id")
obj, err := dbmap.Get(User{}, 1)
checkErr(err, "not found data!")
u := obj.(*User)
log.Printf("id : %d, name %s, score %d", u.Id, u.Name, u.Score)
// パターン 2 (こちらの場合はSQLを書くのでAddTable不要)
var user User // user := User{}
err2 := dbmap.SelectOne(&user, "select * from users where id = 2")
checkErr(err2, "not found data!")
log.Printf("id : %d, name %s, score %d", user.Id, user.Name, user.Score)
// パターン 3 (squirrelでSQL生成)
sb := sql_builder.Select("*").From("users")
sb = sb.Where(sql_builder.Eq{"id": 3})
sql, args, sql_err := sb.ToSql()
log.Println(sql)
checkErr(sql_err, "SQL error!!")
var user3 User // user := User{}
err3 := dbmap.SelectOne(&user3, sql, args[0])
checkErr(err3, "not found data!")
log.Printf("id : %d, name %s, score %d", user3.Id, user3.Name, user3.Score)
return user3
}
func (self *Handle) CountX(obj Model, buildFn func(sq.SelectBuilder) sq.SelectBuilder) int {
b := sq.Select("count(*)").From(obj.GetTable())
b = buildFn(b)
s, args, err := b.ToSql()
if err != nil {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"err": err,
}).Panic("Fail To Build CountX SQL")
}
count := 0
err = self.Conn.QueryRow(s, args...).Scan(&count)
if err == sql.ErrNoRows {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"err": err,
}).Panic("Fail To Get Count SQL")
} else if err != nil {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"err": err,
}).Panic("Fail to CountX()")
}
return count
}
func (self *Handle) RetrieveX(obj Model, buildFn func(sq.SelectBuilder) sq.SelectBuilder) bool {
b := sq.Select(obj.GetFields()).From(obj.GetTable())
b = buildFn(b)
s, args, err := b.ToSql()
if err != nil {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"err": err,
}).Panic("Fail To Build RetrieveX SQL")
}
row := self.Conn.QueryRow(s, args...)
err = self.RowScan(obj, row)
if err == sql.ErrNoRows {
return false
} else if err != nil {
log.WithFields(log.Fields{
"table": obj.GetTable(),
"err": err,
}).Panic("Fail to RetrieveX()")
}
return true
}
func (d *DbMap) BelongsToBuilder(m Belongings, belong Model, selectStr string) sq.SelectBuilder {
if selectStr == "" {
selectStr = "*"
}
t := d.TableName(belong)
kname := m.FKName(belong)
k := m.FK(belong)
return sq.Select(selectStr).From(t).Where(sq.Eq{kname: k})
}
func (d *DbMap) HasOneBuilder(m HasOne, theOne Model, selectStr string) sq.SelectBuilder {
if selectStr == "" {
selectStr = "*"
}
t := d.TableName(theOne)
kname := m.FKName(theOne)
k := m.FK(theOne)
return sq.Select(selectStr).From(t).Where(sq.Eq{kname: k})
}
func (d *DbMap) HasManyBuilder(m HasMany, b Model, selectStr string) sq.SelectBuilder {
var slct string
slct = selectStr
if selectStr == "" {
slct = "*"
}
kname := m.FKNameInBelongings(b)
return sq.Select(slct).From(d.TableName(b)).Where(sq.Eq{kname: m.FKInBelongings(b)})
}
func (d *DbMap) FindOrCreate(holder Model, key interface{}) error {
d.Tracer.TraceOn()
q, args, _ := sq.Select("*").From(d.TableName(holder)).Where(sq.Eq{holder.KeyName(): key}).ToSql()
err := d.DbMap.SelectOne(holder, q, args...)
d.Tracer.TraceOff()
if err != nil {
return d.Create(holder)
}
return nil
}
func (d *DbMap) WhereBuilder(
m Model,
eq map[string]interface{},
selectStr string,
) sq.SelectBuilder {
if selectStr == "" {
selectStr = "*"
}
return sq.Select(selectStr).From(d.TableName(m)).Where(sq.Eq(eq))
}
func (d *DbMap) Exists(m Model) bool {
table := d.TableName(m)
keyname := m.KeyName()
key := m.Key()
q, args, _ := sq.Select("count(*)").From(table).Where(sq.Eq{keyname: key}).ToSql()
count, err := d.SelectInt(q, args...)
if err != nil {
return false
}
return int(count) > 0
}
func (d *DbMap) Get(holderHasKey Model) error {
q, args, err := sq.Select("*").From(d.TableName(holderHasKey)).
Where(sq.Eq{holderHasKey.KeyName(): holderHasKey.Key()}).ToSql()
if err != nil {
return err
}
d.Tracer.TraceOn()
err = d.DbMap.SelectOne(holderHasKey, q, args...)
d.Tracer.TraceOff()
return err
}
func (d *DbMap) FindWhere(holder Model, eq map[string]interface{}) error {
q, args, err := sq.Select("*").From(d.TableName(holder)).
Where(sq.Eq(eq)).ToSql()
if err != nil {
return err
}
d.Tracer.TraceOn()
err = d.DbMap.SelectOne(holder, q, args...)
d.Tracer.TraceOff()
return err
}
func TestQuery(t *testing.T) {
dbmap, a := testInit(t)
cols := []string{"user_id", "name", "group_id"}
sqlmock.ExpectQuery("SELECT (.*) FROM users WHERE (.*)").
WithArgs(1).
WillReturnRows(sqlmock.NewRows(cols).FromCSVString("1,John,1"))
q := sq.Select("*").From("users").Where(sq.Eq{"user_id": 1})
_, err := dbmap.Query(testUser{}, q)
a.Nil(err)
}
func (s *mySQLStore) ListFeatures() ([]*models.Feature, error) {
query := sq.Select("*").From("feature")
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
features := []*models.Feature{}
err = meddler.QueryAll(s.db, &features, sql, args...)
if err != nil {
return nil, err
}
featuresByID := make(map[int64]*models.Feature)
envsByID := make(map[int64]*models.Environment)
envs, err := s.ListEnvironments()
if err != nil {
return nil, err
}
for _, env := range envs {
envsByID[env.ID] = env
}
for _, feature := range features {
featuresByID[feature.ID] = feature
feature.Status = make(map[string]bool)
for _, env := range envs {
feature.Status[env.Name] = false
}
}
stats, err := s.listStatus()
if err != nil {
return nil, err
}
for _, stat := range stats {
feature := featuresByID[stat.FeatureID]
env := envsByID[stat.EnvironmentID]
feature.Status[env.Name] = stat.Enabled
}
return features, err
}
func (s *mySQLStore) ListEnvironments() ([]*models.Environment, error) {
query := sq.Select("*").From("environment")
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
environments := []*models.Environment{}
err = meddler.QueryAll(s.db, &environments, sql, args...)
return environments, err
}
func (s *mySQLStore) listStatus() ([]*status, error) {
query := sq.Select("*").From("feature_status")
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
status := []*status{}
err = meddler.QueryAll(s.db, &status, sql, args...)
return status, err
}
func GetInventory(c *gin.Context) {
project := c.MustGet("project").(models.Project)
var inv []models.Inventory
query, args, _ := squirrel.Select("*").
From("project__inventory").
Where("project_id=?", project.ID).
ToSql()
if _, err := database.Mysql.Select(&inv, query, args...); err != nil {
panic(err)
}
c.JSON(200, inv)
}
// Wrapper for querying a Database struct grouped by a property
func GroupBy(db *sql.DB, property string, timeRange *database.TimeRange) (*database.Aggregates, error) {
// Query
queryBuilder := sq.
Select(property, "COUNT(*)").
From("visits")
// Add time constraints if timeRange provided
if timeRange != nil {
if !timeRange.Start.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time >= %d", timeRange.Start.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
if !timeRange.End.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time <= %d", timeRange.End.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
}
// Set query Group By condition
query, _, err := queryBuilder.GroupBy(property).ToSql()
if err != nil {
return nil, err
}
// Exec query
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
list := database.Aggregates{}
for rows.Next() {
aggregate := database.Aggregate{}
rows.Scan(&aggregate.Id, &aggregate.Total)
// For countries, get fullname as Label
if property == "countryCode" {
aggregate.Label = geoip.GetCountry(aggregate.Id)
} else {
aggregate.Label = aggregate.Id
}
list.List = append(list.List, aggregate)
}
return &list, nil
}
// Wrapper for querying a Database struct
func Query(db *sql.DB, timeRange *database.TimeRange) (*database.Analytics, error) {
// Query
queryBuilder := sq.
Select("time", "event", "path", "ip", "platform", "refererDomain", "countryCode").
From("visits")
// Add time constraints if timeRange provided
if timeRange != nil {
if !timeRange.Start.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time >= %d", timeRange.Start.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
if !timeRange.End.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time <= %d", timeRange.End.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
}
query, _, err := queryBuilder.ToSql()
if err != nil {
return nil, err
}
// Exec query
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
analytics := database.Analytics{}
for rows.Next() {
analytic := database.Analytic{}
var analyticTime int64
rows.Scan(&analyticTime,
&analytic.Event,
&analytic.Path,
&analytic.Ip,
&analytic.Platform,
&analytic.RefererDomain,
&analytic.CountryCode)
analytic.Time = time.Unix(analyticTime, 0).UTC()
analytics.List = append(analytics.List, analytic)
}
return &analytics, nil
}
// Wrapper for querying a Database struct over a time interval
func Series(db *sql.DB, interval int, timeRange *database.TimeRange) (*database.Intervals, error) {
// Query
queryBuilder := sq.
Select(fmt.Sprintf("(time / %d) * %d AS startTime", interval, interval), "COUNT(*)").
From("visits")
// Add time constraints if timeRange provided
if timeRange != nil {
if !timeRange.Start.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time >= %d", timeRange.Start.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
if !timeRange.End.Equal(time.Time{}) {
timeQuery := fmt.Sprintf("time <= %d", timeRange.End.Unix())
queryBuilder = queryBuilder.Where(timeQuery)
}
}
// Set query Group By condition
query, _, err := queryBuilder.GroupBy("startTime").ToSql()
if err != nil {
return nil, err
}
// Exec query
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
// Format results
intervals := database.Intervals{}
for rows.Next() {
result := database.Interval{}
var startTime int
rows.Scan(&startTime, &result.Total)
// Format Start and End from TIMESTAMP to ISO time
result.Start = time.Unix(int64(startTime), 0).UTC().Format(time.RFC3339)
result.End = time.Unix(int64(startTime+interval), 0).UTC().Format(time.RFC3339)
intervals.List = append(intervals.List, result)
}
return &intervals, nil
}
func (s *mySQLStore) GetFeatureByName(name string) (*models.Feature, error) {
feature := new(models.Feature)
query := sq.Select("*").From("feature").Where(sq.Eq{"name": name})
sql, args, err := query.ToSql()
if err != nil {
return nil, err
}
log.Debug(sql)
if err := meddler.QueryRow(s.db, feature, sql, args...); err != nil {
return nil, err
}
envs, err := s.ListEnvironments()
if err != nil {
return nil, err
}
if len(envs) == 0 {
return feature, nil
}
feature.Status = make(map[string]bool)
envNames := make(map[int64]string)
for _, env := range envs {
feature.Status[env.Name] = false
envNames[env.ID] = env.Name
}
status, err := s.listStatusByFeatureID(feature.ID)
if err != nil {
return nil, err
}
for _, st := range status {
name := envNames[st.EnvironmentID]
if name == "" {
continue
}
feature.Status[name] = st.Enabled
}
return feature, nil
}
func GetEnvironment(c *gin.Context) {
project := c.MustGet("project").(models.Project)
var env []models.Environment
q := squirrel.Select("*").
From("project__environment").
Where("project_id=?", project.ID)
query, args, _ := q.ToSql()
if _, err := database.Mysql.Select(&env, query, args...); err != nil {
panic(err)
}
c.JSON(200, env)
}
