func (d *DataSourceController) DoFetchDataSourceMetaData(dataConn *colonycore.Connection, from string) (bool, []*colonycore.FieldInfo, error) {
if err := d.checkIfDriverIsSupported(dataConn.Driver); err != nil {
return false, nil, err
}
var conn dbox.IConnection
conn, err := helper.ConnectUsingDataConn(dataConn).Connect()
if err != nil {
return false, nil, err
}
defer conn.Close()
var query = conn.NewQuery().Take(1)
if !toolkit.HasMember([]string{"csv", "json"}, dataConn.Driver) {
query = query.From(from)
}
cursor, err := query.Cursor(nil)
if err != nil {
return false, nil, err
}
defer cursor.Close()
data := toolkit.M{}
var connDriver string
if dataConn.Driver == "jdbc" {
connDriver = strings.Split(dataConn.Settings.GetString("connector"), ":")[1]
} else {
connDriver = dataConn.Driver
}
if !toolkit.HasMember([]string{"json", "mysql"}, connDriver) {
err = cursor.Fetch(&data, 1, false)
} else {
dataAll := []toolkit.M{}
err = cursor.Fetch(&dataAll, 1, false)
if err != nil {
return false, []*colonycore.FieldInfo{}, errors.New("No data found")
}
if len(dataAll) > 0 {
data = dataAll[0]
}
}
metadata := d.parseMetadata(data)
if err != nil {
return false, nil, err
}
return true, metadata, nil
}
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) GetDataSourceCollections(r *knot.WebContext) interface{} {
r.Config.OutputType = knot.OutputJson
payload := map[string]interface{}{}
err := r.GetPayload(&payload)
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
connectionID := payload["connectionID"].(string)
if connectionID == "" {
return helper.CreateResult(true, []string{}, "")
}
dataConn := new(colonycore.Connection)
err = colonycore.Get(dataConn, connectionID)
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
if err := d.checkIfDriverIsSupported(dataConn.Driver); err != nil {
return helper.CreateResult(false, nil, err.Error())
}
if toolkit.HasMember([]string{"csv", "json"}, dataConn.Driver) {
return helper.CreateResult(true, []string{dataConn.Driver}, "")
}
var conn dbox.IConnection
conn, err = helper.ConnectUsingDataConn(dataConn).Connect()
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
defer conn.Close()
collections := conn.ObjectNames(dbox.ObjTypeTable)
return helper.CreateResult(true, collections, "")
}
func (d *DataSourceController) FetchDataSourceMetaData(r *knot.WebContext) interface{} {
r.Config.OutputType = knot.OutputJson
payload := map[string]interface{}{}
err := r.GetPayload(&payload)
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
connectionID := payload["connectionID"].(string)
from := payload["from"].(string)
if connectionID == "" {
return helper.CreateResult(true, []toolkit.M{}, "")
}
dataConn := new(colonycore.Connection)
err = colonycore.Get(dataConn, connectionID)
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
if err := d.checkIfDriverIsSupported(dataConn.Driver); err != nil {
return helper.CreateResult(false, nil, err.Error())
}
var conn dbox.IConnection
conn, err = helper.ConnectUsingDataConn(dataConn).Connect()
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
defer conn.Close()
var query = conn.NewQuery().Take(1)
if dataConn.Driver != "weblink" {
query = query.From(from)
}
cursor, err := query.Cursor(nil)
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
defer cursor.Close()
data := toolkit.M{}
if dataConn.Driver != "weblink" {
err = cursor.Fetch(&data, 1, false)
} else {
dataAll := []toolkit.M{}
err = cursor.Fetch(&dataAll, 1, false)
if len(dataAll) > 0 {
data = dataAll[0]
}
}
if err != nil {
return helper.CreateResult(false, nil, err.Error())
}
metadata := []*colonycore.FieldInfo{}
for key, val := range data {
label := key
lookup := new(colonycore.Lookup)
lookup.LookupFields = []string{}
meta := new(colonycore.FieldInfo)
meta.ID = key
meta.Label = label
meta.Type, _ = helper.GetBetterType(val)
meta.Format = ""
meta.Lookup = lookup
metadata = append(metadata, meta)
}
return helper.CreateResult(true, metadata, "")
}
func (d *DataGrabberController) AutoGenerateDataSources(payload *colonycore.DataGrabberWizardPayload, formatTime string) ([]*colonycore.DataGrabber, error) {
var connSource dbox.IConnection
var connDest dbox.IConnection
var collObjectNames []string
var dirpath string
isNosql := false
result := []*colonycore.DataGrabber{}
dataConnSource := new(colonycore.Connection)
//pengambilan data untuk mengecek driver destination (mongo, json, csv, sql dan lain-lain)
dataConnDest := new(colonycore.Connection)
err := colonycore.Get(dataConnDest, payload.ConnectionDestination)
if err != nil {
return result, err
}
if !toolkit.HasMember([]string{"mysql", "hive"}, dataConnDest.Driver) {
//mengambil nilai object atau tabel yang ada didestination
connDest, err = helper.ConnectUsingDataConn(dataConnDest).Connect()
if err != nil {
return result, err
}
defer connDest.Close()
if toolkit.HasMember([]string{"json", "csv"}, dataConnDest.Driver) {
var filedata string
dirpath, filedata = filepath.Split(dataConnDest.FileLocation)
collObjectNames = []string{strings.Split(filedata, ".")[0]}
} else {
collObjectNames = connDest.ObjectNames(dbox.ObjTypeAll)
}
//connection source/from
err := colonycore.Get(dataConnSource, payload.ConnectionSource)
if err != nil {
return result, err
}
connSource, err = helper.ConnectUsingDataConn(dataConnSource).Connect()
if err != nil {
return result, err
}
defer connSource.Close()
isNosql = true
}
for key, each := range payload.Transformations {
if tDest := strings.TrimSpace(each.TableDestination); tDest != "" {
var connectionIDDest string
if isNosql {
//pengecekan tidak adanya tabel di connection destination
if each.TableDestination != "" && !toolkit.HasMember(collObjectNames, each.TableDestination) {
//pengambilan ds metadata sesuai dengan table source
var queryS = connSource.NewQuery().Take(1)
if !toolkit.HasMember([]string{"csv", "json"}, dataConnSource.Driver) {
queryS = queryS.From(each.TableSource)
}
csr, err := queryS.Cursor(nil)
if err != nil {
return result, err
}
defer csr.Close()
data := toolkit.M{}
if !toolkit.HasMember([]string{"json", "mysql"}, dataConnSource.Driver) {
err = csr.Fetch(&data, 1, false)
} else {
dataAll := []toolkit.M{}
err = csr.Fetch(&dataAll, 1, false)
if err != nil {
return result, err
}
if len(dataAll) > 0 {
data = dataAll[0]
}
}
if toolkit.HasMember([]string{"csv", "json"}, dataConnDest.Driver) {
// filepath.WalkFunc
o := new(colonycore.Connection)
exts := filepath.Ext(dataConnDest.FileLocation)
extstrim := strings.TrimPrefix(exts, ".")
newpath := filepath.Join(dirpath, each.TableDestination+exts)
connectionIDDest = fmt.Sprintf("conn_%s_%s", extstrim, formatTime)
o.ID = connectionIDDest
o.Driver = extstrim
o.Host = newpath
if dataConnDest.Driver == "csv" {
o.Settings = toolkit.M{"newfile": true, "useheader": true, "delimiter": ","}
} else {
o.Settings = toolkit.M{"newfile": true}
}
if strings.HasPrefix(o.Host, "http") {
fileType := helper.GetFileExtension(o.Host)
o.FileLocation = fmt.Sprintf("%s.%s", filepath.Join(EC_DATA_PATH, "datasource", "upload", o.ID), fileType)
file, err := os.Create(o.FileLocation)
if err != nil {
return nil, err
}
defer file.Close()
resp, err := http.Get(o.Host)
if err != nil {
return nil, err
}
defer resp.Body.Close()
_, err = io.Copy(file, resp.Body)
if err != nil {
return nil, err
}
} else {
o.FileLocation = o.Host
}
err := colonycore.Save(o)
if err != nil {
return result, err
}
newconnDest, err := helper.ConnectUsingDataConn(o).Connect()
if err != nil {
return result, err
}
defer newconnDest.Close()
err = newconnDest.NewQuery().Save().Exec(toolkit.M{"data": data})
if err != nil {
return result, err
}
} else {
err = connDest.NewQuery().Save().From(each.TableDestination).Exec(toolkit.M{"data": data})
if err != nil {
return result, err
}
}
}
}
var prevDS string
var nextDS string
mapGrabber := []*colonycore.Map{}
for i := 0; i < 2; i++ {
var valueFrom string
var connectionID string
prefix := ""
if t := strings.TrimSpace(payload.Prefix); t != "" {
prefix = fmt.Sprintf("%s_", t)
}
cdsID := fmt.Sprintf("%sDS_%d_%d_%s", prefix, i, key, formatTime)
if i == 0 { //table source
valueFrom = each.TableSource
connectionID = payload.ConnectionSource
} else { //table destination
valueFrom = each.TableDestination
if !toolkit.HasMember([]string{"csv", "json"}, dataConnDest.Driver) {
connectionID = payload.ConnectionDestination
} else {
connectionID = connectionIDDest
}
}
squery := fmt.Sprintf(`{"from":"%s", "select":"*"}`, valueFrom)
queryinf := toolkit.M{}
json.Unmarshal([]byte(squery), &queryinf)
dataDs := []colonycore.DataSource{}
cursor, err := colonycore.Find(new(colonycore.DataSource), dbox.Eq("ConnectionID", connectionID))
cursor.Fetch(&dataDs, 0, false)
if err != nil {
return result, err
}
defer cursor.Close()
dataConn := new(colonycore.Connection)
err = colonycore.Get(dataConn, connectionID)
if err != nil {
return result, err
}
resultDataRaw := []toolkit.M{}
if cursor.Count() > 0 {
for _, eachData := range dataDs {
resultEachData := toolkit.M{}
qFrom := eachData.QueryInfo.Get("from").(string)
isSelectExists := eachData.QueryInfo.Has("select")
if qFrom == valueFrom && isSelectExists {
resultEachData.Set(valueFrom, eachData.ID)
resultDataRaw = append(resultDataRaw, resultEachData)
if i == 1 {
for _, eachMetadata := range eachData.MetaData {
mapGrabberField := colonycore.Map{Source: eachMetadata.ID, SourceType: eachMetadata.Type, Destination: eachMetadata.ID, DestinationType: eachMetadata.Type}
mapGrabber = append(mapGrabber, &mapGrabberField)
}
}
}
}
if len(resultDataRaw) == 0 {
resultEachData := toolkit.M{}
resultEachData.Set(valueFrom, cdsID)
resultDataRaw = append(resultDataRaw, resultEachData)
cds := new(colonycore.DataSource)
cds.ID = cdsID
cds.ConnectionID = connectionID
cds.MetaData = []*colonycore.FieldInfo{}
cds.QueryInfo = queryinf
_, metadata, err := CreateDataSourceController(d.Server).DoFetchDataSourceMetaData(dataConn, valueFrom)
if err != nil {
return result, err
}
cds.MetaData = metadata
err = colonycore.Save(cds)
if err != nil {
return result, err
}
if i == 1 {
for _, eachMetadata := range metadata {
mapGrabberField := colonycore.Map{Source: eachMetadata.ID, SourceType: eachMetadata.Type, Destination: eachMetadata.ID, DestinationType: eachMetadata.Type}
mapGrabber = append(mapGrabber, &mapGrabberField)
}
}
}
} else {
resultEachData := toolkit.M{}
resultEachData.Set(valueFrom, cdsID)
resultDataRaw = append(resultDataRaw, resultEachData)
cds := new(colonycore.DataSource)
cds.ID = cdsID
cds.ConnectionID = connectionID
cds.MetaData = []*colonycore.FieldInfo{}
cds.QueryInfo = queryinf
_, metadata, err := CreateDataSourceController(d.Server).DoFetchDataSourceMetaData(dataConn, valueFrom)
if err != nil {
return result, err
}
cds.MetaData = metadata
err = colonycore.Save(cds)
if err != nil {
return result, err
}
if i == 1 {
for _, eachMetadata := range metadata {
mapGrabberField := colonycore.Map{Source: eachMetadata.ID, SourceType: eachMetadata.Type, Destination: eachMetadata.ID, DestinationType: eachMetadata.Type}
mapGrabber = append(mapGrabber, &mapGrabberField)
}
}
}
for _, resd := range resultDataRaw {
if i == 0 { //table source
prevDS = resd.Get(valueFrom).(string)
break
} else { //table destination
nextDS = resd.Get(valueFrom).(string)
break
}
}
}
prefix := ""
if t := strings.TrimSpace(payload.Prefix); t != "" {
prefix = fmt.Sprintf("%s_", t)
}
owiz := new(colonycore.DataGrabber)
owiz.ID = fmt.Sprintf("%sDG_%s_%s", prefix, strconv.Itoa(key), formatTime)
owiz.DataSourceOrigin = prevDS
owiz.DataSourceDestination = nextDS
owiz.IsFromWizard = true
owiz.GrabInterval = 20
owiz.IntervalType = "seconds"
owiz.InsertMode = "append"
// owiz.Maps = []*colonycore.Map{}
owiz.Maps = mapGrabber
owiz.PostTransferCommand = ""
owiz.PreTransferCommand = ""
owiz.TimeoutInterval = 20
owiz.UseInterval = false
owiz.RunAt = []string{}
err := colonycore.Save(owiz)
if err != nil {
return result, err
}
result = append(result, owiz)
}
}
return result, nil
}
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
}
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
}
