func (d *DataSourceController) ConnectToDataSource(_id string) (*colonycore.DataSource, *colonycore.Connection, dbox.IConnection, dbox.IQuery, MetaSave, error) { dataDS := new(colonycore.DataSource) err := colonycore.Get(dataDS, _id) if err != nil { return nil, nil, nil, nil, MetaSave{}, err } dataConn := new(colonycore.Connection) err = colonycore.Get(dataConn, dataDS.ConnectionID) if err != nil { return nil, nil, nil, nil, MetaSave{}, err } if err := d.checkIfDriverIsSupported(dataConn.Driver); err != nil { return nil, nil, nil, nil, MetaSave{}, err } connection, err := helper.ConnectUsingDataConn(dataConn).Connect() if err != nil { return nil, nil, nil, nil, MetaSave{}, err } query, metaSave := d.parseQuery(connection.NewQuery(), dataDS.QueryInfo) return dataDS, dataConn, connection, query, metaSave, nil }
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 (a *ApplicationController) ConnectToDataSource() (dbox.IConnection, error) { dataConn := new(colonycore.Connection) fmt.Println(dataConn) connection, err := helper.ConnectUsingDataConn(dataConn).Connect() fmt.Println(connection) if err != nil { fmt.Println("error connection ", err) return nil, err } return connection, nil }
func (d *DataBrowserController) connToDatabase(_id string) (dbox.IConnection, *colonycore.Connection, error) { dataConn := new(colonycore.Connection) err := colonycore.Get(dataConn, _id) if err != nil { return nil, nil, err } connection, err := helper.ConnectUsingDataConn(dataConn).Connect() if err != nil { return nil, nil, err } return connection, dataConn, nil }
func (d *DataSourceController) TestConnection(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()) } driver := payload["Driver"].(string) host := payload["Host"].(string) database := payload["Database"].(string) username := payload["UserName"].(string) password := payload["Password"].(string) var settings toolkit.M = nil if settingsRaw := d.parseSettings(payload["Settings"], nil); settingsRaw != nil { settings, err = toolkit.ToM(settingsRaw) if err != nil { return helper.CreateResult(false, nil, err.Error()) } } if err := d.checkIfDriverIsSupported(driver); err != nil { return helper.CreateResult(false, nil, err.Error()) } fakeDataConn := &colonycore.Connection{ Database: database, Driver: driver, Host: host, UserName: username, Password: password, Settings: settings, } err = helper.ConnectUsingDataConn(fakeDataConn).CheckIfConnected() if err != nil { return helper.CreateResult(false, nil, err.Error()) } return helper.CreateResult(true, 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 *DataSourceController) TestConnection(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()) } driver := payload["Driver"].(string) host := payload["Host"].(string) database := payload["Database"].(string) username := payload["UserName"].(string) password := payload["Password"].(string) var settings toolkit.M = nil if settingsRaw := d.parseSettings(payload["Settings"], nil); settingsRaw != nil { settings, err = toolkit.ToM(settingsRaw) if err != nil { return helper.CreateResult(false, nil, err.Error()) } } if err := d.checkIfDriverIsSupported(driver); err != nil { return helper.CreateResult(false, nil, err.Error()) } fakeDataConn := &colonycore.Connection{ Database: database, Driver: driver, Host: host, UserName: username, Password: password, Settings: settings, } if driver == "json" || driver == "csv" { if strings.HasPrefix(host, "http") { fileTempID := helper.RandomIDWithPrefix("f") fileType := helper.GetFileExtension(host) fakeDataConn.FileLocation = fmt.Sprintf("%s.%s", filepath.Join(EC_DATA_PATH, "datasource", "upload", fileTempID), fileType) file, err := os.Create(fakeDataConn.FileLocation) if err != nil { os.Remove(fakeDataConn.FileLocation) return helper.CreateResult(false, nil, err.Error()) } defer file.Close() resp, err := http.Get(host) if err != nil { os.Remove(fakeDataConn.FileLocation) return helper.CreateResult(false, nil, err.Error()) } defer resp.Body.Close() _, err = io.Copy(file, resp.Body) if err != nil { os.Remove(fakeDataConn.FileLocation) return helper.CreateResult(false, nil, err.Error()) } } else { fakeDataConn.FileLocation = host } } err = helper.ConnectUsingDataConn(fakeDataConn).CheckIfConnected() if fakeDataConn.FileLocation != "" && strings.HasPrefix(host, "http") { os.Remove(fakeDataConn.FileLocation) } if err != nil { return helper.CreateResult(false, nil, err.Error()) } return helper.CreateResult(true, nil, "") }
func (d *DataSourceController) ConnectToDataSourceDB(payload toolkit.M) (int, []toolkit.M, *colonycore.DataBrowser, error) { var hasLookup bool toolkit.Println("payload : ", payload) if payload.Has("haslookup") { hasLookup = payload.Get("haslookup").(bool) } _id := toolkit.ToString(payload.Get("browserid", "")) sort := payload.Get("sort") search := payload.Get("search") _ = search take := toolkit.ToInt(payload.Get("take", ""), toolkit.RoundingAuto) skip := toolkit.ToInt(payload.Get("skip", ""), toolkit.RoundingAuto) TblName := toolkit.M{} payload.Unset("browserid") //sorter = "" if sort != nil { tmsort, _ := toolkit.ToM(sort.([]interface{})[0]) fmt.Printf("====== sort %#v\n", tmsort["dir"]) if tmsort["dir"] == "asc" { sorter = tmsort["field"].(string) } else if tmsort["dir"] == "desc" { sorter = "-" + tmsort["field"].(string) } else if tmsort["dir"] == nil { sorter = " " } } else { sorter = " " } dataDS := new(colonycore.DataBrowser) err := colonycore.Get(dataDS, _id) if err != nil { return 0, nil, nil, err } dataConn := new(colonycore.Connection) err = colonycore.Get(dataConn, dataDS.ConnectionID) if err != nil { return 0, nil, nil, err } if err := d.checkIfDriverIsSupported(dataConn.Driver); err != nil { return 0, nil, nil, err } connection, err := helper.ConnectUsingDataConn(dataConn).Connect() if err != nil { return 0, nil, nil, err } if dataDS.QueryType == "" { TblName.Set("from", dataDS.TableNames) payload.Set("from", dataDS.TableNames) } else if dataDS.QueryType == "Dbox" { getTableName := toolkit.M{} toolkit.UnjsonFromString(dataDS.QueryText, &getTableName) payload.Set("from", getTableName.Get("from").(string)) if qSelect := getTableName.Get("select", "").(string); qSelect != "" { payload.Set("select", getTableName.Get("select").(string)) } } else if dataDS.QueryType == "SQL" { var QueryString string if dataConn.Driver == "mysql" || dataConn.Driver == "hive" { QueryString = " LIMIT " + toolkit.ToString(take) + " OFFSET " + toolkit.ToString(skip) } else if dataConn.Driver == "mssql" { QueryString = " OFFSET " + toolkit.ToString(skip) + " ROWS FETCH NEXT " + toolkit.ToString(take) + " ROWS ONLY " } else if dataConn.Driver == "postgres" { QueryString = " LIMIT " + toolkit.ToString(take) + " OFFSET " + toolkit.ToString(skip) } stringQuery := toolkit.Sprintf("%s %s", dataDS.QueryText, QueryString) payload.Set("freetext", stringQuery) // toolkit.Println(stringQuery) } qcount, _ := d.parseQuery(connection.NewQuery(), TblName) query, _ := d.parseQuery(connection.NewQuery() /*.Skip(skip).Take(take) .Order(sorter)*/, payload) var selectfield string for _, metadata := range dataDS.MetaData { tField := metadata.Field if payload.Has(tField) { selectfield = toolkit.ToString(tField) if toolkit.IsSlice(payload[tField]) { query = query.Where(dbox.In(tField, payload[tField].([]interface{})...)) qcount = qcount.Where(dbox.In(tField, payload[tField].([]interface{})...)) } else if !toolkit.IsNilOrEmpty(payload[tField]) { var hasPattern bool for _, val := range querypattern { if strings.Contains(toolkit.ToString(payload[tField]), val) { hasPattern = true } } if hasPattern { query = query.Where(dbox.ParseFilter(toolkit.ToString(tField), toolkit.ToString(payload[tField]), toolkit.ToString(metadata.DataType), "")) qcount = qcount.Where(dbox.ParseFilter(toolkit.ToString(tField), toolkit.ToString(payload[tField]), toolkit.ToString(metadata.DataType), "")) } else { switch toolkit.ToString(metadata.DataType) { case "int": query = query.Where(dbox.Eq(tField, toolkit.ToInt(payload[tField], toolkit.RoundingAuto))) qcount = qcount.Where(dbox.Eq(tField, toolkit.ToInt(payload[tField], toolkit.RoundingAuto))) case "float32": query = query.Where(dbox.Eq(tField, toolkit.ToFloat32(payload[tField], 2, toolkit.RoundingAuto))) qcount = qcount.Where(dbox.Eq(tField, toolkit.ToFloat32(payload[tField], 2, toolkit.RoundingAuto))) case "float64": query = query.Where(dbox.Eq(tField, toolkit.ToFloat64(payload[tField], 2, toolkit.RoundingAuto))) qcount = qcount.Where(dbox.Eq(tField, toolkit.ToFloat64(payload[tField], 2, toolkit.RoundingAuto))) default: query = query.Where(dbox.Contains(tField, toolkit.ToString(payload[tField]))) qcount = qcount.Where(dbox.Contains(tField, toolkit.ToString(payload[tField]))) } } } } } if hasLookup && selectfield != "" { if toolkit.HasMember(ds_flatfile, dataConn.Driver) { query = query.Select(selectfield) qcount = qcount.Select(selectfield) } else { query = query.Select(selectfield).Group(selectfield) qcount = qcount.Select(selectfield).Group(selectfield) } } ccount, err := qcount.Cursor(nil) if err != nil { return 0, nil, nil, err } defer ccount.Close() dcount := ccount.Count() cursor, err := query.Cursor(nil) if err != nil { return 0, nil, nil, err } defer cursor.Close() data := []toolkit.M{} cursor.Fetch(&data, 0, false) if err != nil { return 0, nil, nil, err } if hasLookup && selectfield != "" && !toolkit.HasMember(ds_rdbms, dataConn.Driver) && !toolkit.HasMember(ds_flatfile, dataConn.Driver) { dataMongo := []toolkit.M{} for _, val := range data { mVal, _ := toolkit.ToM(val.Get("_id")) dataMongo = append(dataMongo, mVal) } data = dataMongo } else if hasLookup && selectfield != "" && toolkit.HasMember(ds_flatfile, dataConn.Driver) { /*distinct value for flat file*/ dataFlat := []toolkit.M{} var existingVal = []string{""} for _, val := range data { valString := toolkit.ToString(val.Get(selectfield)) if !toolkit.HasMember(existingVal, valString) { dataFlat = append(dataFlat, val) existingVal = append(existingVal, valString) } } data = dataFlat } return dcount, data, dataDS, nil }
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 }