Esempio n. 1
0
func (d *DataBrowserController) parseQuery(conn dbox.IConnection, dbrowser colonycore.DataBrowser, datacon *colonycore.Connection) (dbox.IQuery, error) {
	var dataQuery dbox.IQuery

	if dbrowser.QueryType == "nonQueryText" {
		dataQuery = conn.NewQuery().From(dbrowser.TableNames)
	} else if dbrowser.QueryType == "SQL" {
		if toolkit.HasMember(rdbms, datacon.Driver) {
			dataQuery = conn.NewQuery().Command("freequery", toolkit.M{}.
				Set("syntax", dbrowser.QueryText))
		} else {
			return nil, errors.New("Free Text Query with SQL only for RDBMS, please use Dbox")
		}
	} else if dbrowser.QueryType == "Dbox" {
		queryInfo := toolkit.M{}
		toolkit.UnjsonFromString(dbrowser.QueryText, &queryInfo)
		toolkit.Println("queryinfo", queryInfo)

		if qFrom := queryInfo.Get("from", "").(string); qFrom != "" {
			dataQuery = conn.NewQuery()
			dataQuery = dataQuery.From(qFrom)
		}
		if qSelect := queryInfo.Get("select", "").(string); qSelect != "" {
			if qSelect != "*" {
				dataQuery = dataQuery.Select(strings.Split(qSelect, ",")...)
			}
		}
	}
	return dataQuery, nil
}
func (d *DataSourceController) parseQuery(query dbox.IQuery, queryInfo toolkit.M) (dbox.IQuery, MetaSave) {
	metaSave := MetaSave{}

	if qFrom := queryInfo.Get("from", "").(string); qFrom != "" {
		query = query.From(qFrom)
	}
	if qSelect := queryInfo.Get("select", "").(string); qSelect != "" {
		if qSelect != "*" {
			query = query.Select(strings.Split(qSelect, ",")...)
		}
	}
	if qTakeRaw, qTakeOK := queryInfo["take"]; qTakeOK {
		if qTake, ok := qTakeRaw.(float64); ok {
			query = query.Take(int(qTake))
		}
		if qTake, ok := qTakeRaw.(int); ok {
			query = query.Take(qTake)
		}
	}
	if qSkipRaw, qSkipOK := queryInfo["skip"]; qSkipOK {
		if qSkip, ok := qSkipRaw.(float64); ok {
			query = query.Take(int(qSkip))
		}
		if qSkip, ok := qSkipRaw.(int); ok {
			query = query.Take(qSkip)
		}
	}
	if qOrder := queryInfo.Get("order", "").(string); qOrder != "" {
		orderAll := map[string]string{}
		err := json.Unmarshal([]byte(qOrder), &orderAll)
		if err == nil {
			orderString := []string{}
			for key, val := range orderAll {
				orderString = append(orderString, key)
				orderString = append(orderString, val)
			}
			query = query.Order(orderString...)
		}
	}

	if qInsert := queryInfo.Get("insert", "").(string); qInsert != "" {
		if qInsert != "" {
			metaSave.keyword = "insert"
			metaSave.data = qInsert
			query = query.Insert()
		}
	}
	if qUpdate := queryInfo.Get("update", "").(string); qUpdate != "" {
		if qUpdate != "" {
			metaSave.keyword = "update"
			metaSave.data = qUpdate
			query = query.Update()
		}
	}
	if _, qDeleteOK := queryInfo["delete"]; qDeleteOK {
		metaSave.keyword = "delete"
		query = query.Delete()
	}
	if qCommand := queryInfo.Get("command", "").(string); qCommand != "" {
		command := map[string]interface{}{}
		err := json.Unmarshal([]byte(qCommand), &command)
		if err == nil {
			for key, value := range command {
				query = query.Command(key, value)
				break
			}
		}
	}

	if qWhere := queryInfo.Get("where", "").(string); qWhere != "" {
		whereAll := []map[string]interface{}{}
		err := json.Unmarshal([]byte(qWhere), &whereAll)
		if err == nil {
			allFilter := []*dbox.Filter{}

			for _, each := range whereAll {
				where, _ := toolkit.ToM(each)
				filter := d.filterParse(where)
				if filter != nil {
					allFilter = append(allFilter, filter)
				}
			}

			query = query.Where(allFilter...)
		}
	}

	return query, metaSave
}
Esempio n. 3
0
func (g *GrabService) execService() {
	g.LastGrabStat = false
	go func(g *GrabService) {
		for g.ServiceRunningStat {

			if g.LastGrabStat {
				<-time.After(g.GrabInterval)
			} else {
				<-time.After(g.TimeOutInterval)
			}

			if !g.ServiceRunningStat {
				continue
			}

			g.ErrorNotes = ""
			g.LastGrabExe = time.Now()
			g.NextGrabExe = time.Now().Add(g.GrabInterval)
			g.LastGrabStat = true
			g.Log.AddLog(fmt.Sprintf("[%s] Grab Started %s", g.Name, g.Url), "INFO")
			g.GrabCount += 1

			keySetting := []string{}
			switch g.SourceType {
			case SourceType_HttpHtml, SourceType_HttpJson:
				if e := g.ServGrabber.Grab(nil); e != nil {
					g.ErrorNotes = fmt.Sprintf("[%s] Grab Failed %s, repeat after %s :%s", g.Name, g.Url, g.TimeOutIntervalInfo, e)
					g.Log.AddLog(g.ErrorNotes, "ERROR")
					g.NextGrabExe = time.Now().Add(g.TimeOutInterval)
					g.LastGrabStat = false
					g.ErrorFound += 1
				} else {
					g.Log.AddLog(fmt.Sprintf("[%s] Grab Success %s", g.Name, g.Url), "INFO")
				}
				for key, _ := range g.ServGrabber.Config.DataSettings {
					keySetting = append(keySetting, key)
				}
				// keySetting = g.sGrabber.Config.DataSettings
			case SourceType_DocExcel:
				// e = g.sGetData.ResultFromDatabase(key, &docs)
				// if e != nil {
				// 	g.LastGrabStat = false
				// }
				for key, _ := range g.ServGetData.CollectionSettings {
					keySetting = append(keySetting, key)
				}
			}

			// if e := g.ServGrabber.Grab(nil); e != nil {
			// 	g.ErrorNotes = fmt.Sprintf("[%s] Grab Failed %s, repeat after %s :%s", g.Name, g.Url, g.TimeOutIntervalInfo, e)
			// 	g.Log.AddLog(g.ErrorNotes, "ERROR")
			// 	g.NextGrabExe = time.Now().Add(g.TimeOutInterval)
			// 	g.LastGrabStat = false
			// 	g.ErrorFound += 1
			// 	continue
			// } else {
			// 	g.Log.AddLog(fmt.Sprintf("[%s] Grab Success %s", g.Name, g.Url), "INFO")
			// }

			if g.LastGrabStat {
				for _, key := range keySetting {
					var e error
					g.Log.AddLog(fmt.Sprintf("[%s-%s] Fetch Data to destination started", g.Name, key), "INFO")

					docs := []toolkit.M{}
					switch g.SourceType {
					case SourceType_HttpHtml, SourceType_HttpJson:
						e = g.ServGrabber.ResultFromHtml(key, &docs)
					case SourceType_DocExcel:
						e = g.ServGetData.ResultFromDatabase(key, &docs)
						if e != nil {
							g.LastGrabStat = false
						}
					}
					if e != nil || !(g.LastGrabStat) {
						g.ErrorNotes = fmt.Sprintf("[%s-%s] Fetch Result Failed : ", g.Name, key, e)
						g.Log.AddLog(g.ErrorNotes, "ERROR")
					}

					e = g.DestDbox[key].IConnection.Connect()
					if e != nil {
						g.ErrorNotes = fmt.Sprintf("[%s-%s] Connect to destination failed [%s-%s]:%s", g.Name, key, g.DestDbox[key].Desttype, g.DestDbox[key].IConnection.Info().Host, e)
						g.Log.AddLog(g.ErrorNotes, "ERROR")
					}

					var q dbox.IQuery
					if g.DestDbox[key].Collection == "" {
						q = g.DestDbox[key].IConnection.NewQuery().SetConfig("multiexec", true).Save()
					} else {
						q = g.DestDbox[key].IConnection.NewQuery().SetConfig("multiexec", true).From(g.DestDbox[key].Collection).Save()
					}
					xN := 0
					iN := 0
					for _, doc := range docs {
						for key, val := range doc {
							doc[key] = strings.TrimSpace(fmt.Sprintf("%s", val))
						}

						if g.DestDbox[key].Desttype == "mongo" {
							doc["_id"] = toolkit.GenerateRandomString("1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnpqrstuvwxyz", 32)
						}

						e = q.Exec(toolkit.M{
							"data": doc,
						})

						if g.DestDbox[key].Desttype == "mongo" {
							delete(doc, "_id")
						}

						if e != nil {
							g.ErrorNotes = fmt.Sprintf("[%s-%s] Unable to insert [%s-%s]:%s", g.Name, key, g.DestDbox[key].Desttype, g.DestDbox[key].IConnection.Info().Host, e)
							g.Log.AddLog(g.ErrorNotes, "ERROR")
							g.ErrorFound += 1
						} else {
							iN += 1
						}
						xN++
					}
					g.RowGrabbed += xN
					q.Close()
					g.DestDbox[key].IConnection.Close()

					g.Log.AddLog(fmt.Sprintf("[%s-%s] Fetch Data to destination finished with %d record fetch", g.Name, key, xN), "INFO")

					if g.HistoryPath != "" && g.HistoryRecPath != "" {
						recfile := g.AddRecHistory(key, docs)
						historyservice := toolkit.M{}.Set("datasettingname", key).Set("grabdate", g.LastGrabExe).Set("rowgrabbed", g.RowGrabbed).
							Set("rowsaved", iN).Set("note", g.ErrorNotes).Set("grabstatus", "SUCCESS").Set("recfile", recfile)
						if !(g.LastGrabStat) {
							historyservice.Set("grabstatus", "FAILED")
						}
						g.AddHistory(historyservice)
					}
				}
			} else {
				if g.HistoryPath != "" {
					historyservice := toolkit.M{}.Set("datasettingname", "-").Set("grabdate", g.LastGrabExe).Set("rowgrabbed", g.RowGrabbed).
						Set("rowsaved", 0).Set("note", g.ErrorNotes).Set("grabstatus", "FAILED").Set("recfile", "")
					g.AddHistory(historyservice)
				}
			}
		}
	}(g)
}
Esempio n. 4
0
func (d *DataBrowserController) hasAggr(ctx dbox.IConnection, data *colonycore.DataBrowser, conn *colonycore.Connection) (*colonycore.DataBrowser, error) {
	var fieldArr, aggrArr []string
	var indexAggr []map[int]string
	var query dbox.IQuery
	fieldAggr := toolkit.M{}

	for i, v := range data.MetaData {
		if v.Aggregate != "" {
			result := toolkit.M{}
			toolkit.UnjsonFromString(v.Aggregate, &result)
			cursor := []toolkit.M{}

			if data.QueryType == "" {
				aggregate, e := d.dboxAggr(data.TableNames, v.Field, ctx, query, result, fieldAggr, cursor, conn)
				if e != nil {
					return nil, e
				}
				v.Aggregate = toolkit.JsonString(aggregate)
			} else if data.QueryType == "SQL" {
				names := map[int]string{}
				fieldArr = append(fieldArr, v.Field)

				if _, sumOK := result["SUM"]; sumOK {
					aggrArr = append(aggrArr, "SUM("+v.Field+")")
					if len(result) > 1 {
						indexAggr = append(indexAggr, map[int]string{i: "sum"})
					} else {
						names[i] = "sum"
					}
				}
				if _, avgOK := result["AVG"]; avgOK {
					aggrArr = append(aggrArr, "AVG("+v.Field+")")
					if len(result) > 1 {
						indexAggr = append(indexAggr, map[int]string{i: "avg"})
					} else {
						names[i] = "avg"
					}
				}
				if _, maxOK := result["MAX"]; maxOK {
					aggrArr = append(aggrArr, "MAX("+v.Field+")")
					if len(result) > 1 {
						indexAggr = append(indexAggr, map[int]string{i: "max"})
					} else {
						names[i] = "max"
					}
				}
				if _, minOK := result["MIN"]; minOK {
					aggrArr = append(aggrArr, "MIN("+v.Field+")")
					if len(result) > 1 {
						indexAggr = append(indexAggr, map[int]string{i: "min"})
					} else {
						names[i] = "min"
					}
				}
				if _, minOK := result["COUNT"]; minOK {
					aggrArr = append(aggrArr, "COUNT("+v.Field+")")
					if len(result) > 1 {
						indexAggr = append(indexAggr, map[int]string{i: "count"})
					} else {
						names[i] = "count"
					}
				}

				if len(result) > 1 {
					fieldAggr.Set(v.Field, indexAggr)
				} else {
					fieldAggr.Set(v.Field, names)
				}
			} else if data.QueryType == "Dbox" {
				getQuery := toolkit.M{}
				toolkit.UnjsonFromString(data.QueryText, &getQuery)

				aggregate, e := d.dboxAggr(getQuery.Get("from").(string), v.Field, ctx, query, result, fieldAggr, cursor, conn)
				if e != nil {
					return nil, e
				}
				v.Aggregate = toolkit.JsonString(aggregate)
			}
		}
	}

	if data.QueryType == "SQL" {
		// fieldString := strings.Join(fieldArr, ", ")
		aggrString := strings.Join(aggrArr, ", ")
		var queryText string
		r := regexp.MustCompile(`(([Ff][Rr][Oo][Mm])) (?P<from>([a-zA-Z][_a-zA-Z]+[_a-zA-Z0-1].*))`)
		temparray := r.FindStringSubmatch(data.QueryText)
		sqlpart := toolkit.M{}

		for i, val := range r.SubexpNames() {
			if val != "" {
				sqlpart.Set(val, temparray[i])
			}
		}

		if fromOK := sqlpart.Get("from", "").(string); fromOK != "" {
			queryText = toolkit.Sprintf("select %s FROM %s", aggrString, sqlpart.Get("from", "").(string))
			// toolkit.Printf("queryString:%v\n", queryString)
		}

		query = ctx.NewQuery().Command("freequery", toolkit.M{}.
			Set("syntax", queryText))

		csr, e := query.Cursor(nil)
		if e != nil {
			return nil, e
		}
		defer csr.Close()

		cursor := []toolkit.M{}
		e = csr.Fetch(&cursor, 0, false)
		if e != nil {
			return nil, e
		}

		for f, m := range fieldAggr {
			aggrData := toolkit.M{}
			for _, aggs := range cursor {
				for k, agg := range aggs {
					if toolkit.SliceLen(m) > 0 {
						for _, vals := range m.([]map[int]string) {
							for key, val := range vals {
								if strings.Contains(k, f) && strings.Contains(k, data.MetaData[key].Field) && strings.Contains(k, val) {
									aggrData.Set(val, agg)
									data.MetaData[key].Aggregate = toolkit.JsonString(aggrData)
								}
							}
						}
					} else {
						for key, val := range m.(map[int]string) {
							if strings.Contains(k, f) && strings.Contains(k, data.MetaData[key].Field) && strings.Contains(k, val) {
								aggrData.Set(val, agg)
								data.MetaData[key].Aggregate = toolkit.JsonString(aggrData)
								// toolkit.Printf("k:%v f:%v key:%v val:%v agg:%v\n", k, f, key, val, data.MetaData[key].Aggregate)
							}
						}
					}
				}
			}

		}
	}

	return data, nil
}
Esempio n. 5
0
func (d *DataBrowserController) dboxAggr(tblename string, field string, ctx dbox.IConnection, query dbox.IQuery, result, fieldAggr toolkit.M, cursor []toolkit.M, conn *colonycore.Connection) (toolkit.M, error) {
	aggregate := toolkit.M{}

	if conn.Driver == "mongo" {
		field = "$" + field
	}
	query = ctx.NewQuery().From(tblename)
	if result != nil {
		for k, _ := range result {
			if k == "SUM" {
				query = query.Aggr(dbox.AggrSum, field, "SUM")
			}
			if k == "AVG" {
				query = query.Aggr(dbox.AggrAvr, field, "AVG")
			}
			if k == "MAX" {
				query = query.Aggr(dbox.AggrMax, field, "MAX")
			}
			if k == "MIN" {
				query = query.Aggr(dbox.AggrMin, field, "MIN")
			}
			if conn.Driver == "mongo" {
				if k != "COUNT" {
					query = query.Group()
				}
			}

			csr, e := query.Cursor(nil)
			if e != nil {
				return nil, e
			}
			defer csr.Close()

			hasCount := []toolkit.M{}
			if k == "COUNT" {
				csr, e := query.Cursor(nil)
				if e != nil {
					return nil, e
				}
				defer csr.Close()
				count := csr.Count()
				hasCount = append(hasCount, aggregate.Set("count", count))
				aggregate.Set("count", count)
			} else {
				e = csr.Fetch(&cursor, 0, false)
				if e != nil {
					return nil, e
				}
			}

			if _, countOK := aggregate["count"]; countOK {
				cursor = append(cursor, hasCount...)
			}

			for _, agg := range cursor {
				aggregate = agg
				if conn.Driver == "mongo" {
					for f, _ := range aggregate {
						if f == "_id" {
							aggregate.Unset(f)
						}
					}
				}

				fieldAggr.Set(field, aggregate)
				// toolkit.Printf("k:%v fieldArr:%v cursor:%v\n", k, field, fieldAggr)
			}
		}
	}
	return aggregate, nil
}
Esempio n. 6
0
func streamsavedata(intms <-chan toolkit.M, sQ dbox.IQuery, key string, dt toolkit.M) {
	var err error
	iN, note := 0, ""

	for intm := range intms {
		if destDboxs[key].desttype == "mongo" {
			intm.Set("_id", toolkit.GenerateRandomString("", 32))
		}

		if len(intm) == 0 {
			continue
		}
		//Pre Execute Program
		if extCommand.Has("pre") && toolkit.ToString(extCommand["pre"]) != "" {
			sintm := toolkit.JsonString(intm)
			arrcmd := make([]string, 0, 0)

			// if runtime.GOOS == "windows" {
			// 	arrcmd = append(arrcmd, "cmd")
			// 	arrcmd = append(arrcmd, "/C")
			// }

			arrcmd = append(arrcmd, toolkit.ToString(extCommand["pre"]))
			arrcmd = append(arrcmd, sintm)

			// output, err := toolkit.RunCommand(arrcmd[0], arrcmd[1:])
			output, err := toolkit.RunCommand(arrcmd[0], arrcmd[1])
			if err != nil {
				Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to execute pre external command :%s", key, err.Error()), "ERROR")
				note = "Error Found"
				continue
			}

			err = toolkit.UnjsonFromString(output, &intm)
			if err != nil {
				Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to get pre external command output :%s", key, err.Error()), "ERROR")
				note = "Error Found"
				continue
			}
		}

		err = sQ.Exec(toolkit.M{
			"data": intm,
		})

		if err != nil {
			Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to insert data [%s-%s]:%s", key, "csv", destDboxs[key].IConnection.Info().Host, err.Error()), "ERROR")
			note = "Error Found"
			continue
		}

		err = saverechistory(key, intm)
		if err != nil {
			Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to insert record data [%s-%s]:%s", key, "csv", destDboxs[key].IConnection.Info().Host, err.Error()), "ERROR")
			note = "Error Found"
		}

		iN += 1
		if math.Mod(float64(iN), 100) == 0 {
			_ = updatesnapshot(iN, key)
			dt = dt.Set("rowsaved", (toolkit.ToInt(dt.Get("rowsaved", 0), toolkit.RoundingAuto) + iN))
			iN = 0
		}

		//Post Execute Program
		if extCommand.Has("post") {
			sintm := toolkit.JsonString(intm)
			arrcmd := make([]string, 0, 0)

			// if runtime.GOOS == "windows" {
			// 	arrcmd = append(arrcmd, "cmd")
			// 	arrcmd = append(arrcmd, "/C")
			// }

			arrcmd = append(arrcmd, toolkit.ToString(extCommand["post"]))
			arrcmd = append(arrcmd, sintm)

			// output, err := toolkit.RunCommand(arrcmd[0], arrcmd[1:])
			output, err := toolkit.RunCommand(arrcmd[0], arrcmd[1])
			if err != nil {
				Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to execute post external command :%s", key, err.Error()), "ERROR")
				note = "Error Found"
				continue
			}

			err = toolkit.UnjsonFromString(output, &intm)
			if err != nil {
				Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to get post external command output :%s", key, err.Error()), "ERROR")
				note = "Error Found"
				continue
			}
		}
	}
	dt = dt.Set("note", note).
		Set("grabstatus", "done").
		Set("rowsaved", (toolkit.ToInt(dt.Get("rowsaved", 0), toolkit.RoundingAuto) + iN))
	_ = updatesnapshot(iN, key)
	err = savehistory(dt)
	if err != nil {
		Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Unable to save history : %s", key), "ERROR")
	}
	Log.AddLog(fmt.Sprintf("[savedatagrab.%s] Finish save data", key), "INFO")
	destDboxs[key].IConnection.Close()
}