func removeDuplicatesUnordered(elements toolkit.M, key []string) []string {
for _, k := range key {
elements.Unset(k)
}
result := []string{}
for key, _ := range elements {
result = append(result, key)
}
return result
}
func (c *Cursor) Fetch(m interface{}, n int, closeWhenDone bool) (
*dbox.DataSet, error) {
if closeWhenDone {
c.Close()
}
e := c.prepIter()
if e != nil {
return nil, errorlib.Error(packageName, modCursor, "Fetch", e.Error())
}
if c.jsonSelect == nil {
return nil, errorlib.Error(packageName, modCursor, "Fetch", "Iter object is not yet initialized")
}
datas := []interface{}{}
dec := json.NewDecoder(strings.NewReader(string(c.readFile)))
dec.Decode(&datas)
ds := dbox.NewDataSet(m)
if n == 0 {
whereFieldsToMap, _ := toolkit.ToM(c.whereFields)
b := c.getCondition(whereFieldsToMap)
var foundSelected = toolkit.M{}
var foundData = []toolkit.M{}
var getRemField = toolkit.M{}
if c.isWhere {
if b {
for _, v := range datas {
for i, subData := range v.(map[string]interface{}) {
getRemField[i] = i //append(getRemField, i)
for _, vWhere := range whereFieldsToMap {
for _, subWhere := range vWhere.([]interface{}) {
for _, subsubWhere := range subWhere.(map[string]interface{}) {
if len(c.jsonSelect.([]string)) == 0 {
if strings.ToLower(subData.(string)) == strings.ToLower(subsubWhere.(string)) {
ds.Data = append(ds.Data, v)
}
} else {
if strings.ToLower(subData.(string)) == strings.ToLower(subsubWhere.(string)) {
foundData = append(foundData, v.(map[string]interface{}))
}
}
}
}
}
}
}
itemToRemove := removeDuplicatesUnordered(getRemField, c.jsonSelect.([]string))
if len(foundData) > 0 {
var found toolkit.M
for _, found = range foundData {
for _, remitem := range itemToRemove {
found.Unset(remitem)
}
ds.Data = append(ds.Data, found)
}
}
} else {
for _, v := range datas {
for _, v2 := range v.(map[string]interface{}) {
for _, vWhere := range c.whereFields.(toolkit.M) {
if reflect.ValueOf(v2).Kind() == reflect.String {
if strings.ToLower(v2.(string)) == strings.ToLower(vWhere.(string)) {
if len(c.jsonSelect.([]string)) == 0 {
ds.Data = append(ds.Data, v)
} else {
// fmt.Println(c.jsonSelect.([]string)[0])
// fmt.Println(v.(map[string]interface{}))
foundData = append(foundData, v.(map[string]interface{}))
}
}
}
}
}
}
if len(foundData) > 0 {
for _, found := range foundData {
for i, subData := range found {
for _, selected := range c.jsonSelect.([]string) {
if strings.ToLower(selected) == strings.ToLower(i) {
foundSelected[i] = subData
} else if selected == "*" {
foundSelected[i] = subData
}
}
}
}
ds.Data = append(ds.Data, foundSelected)
}
}
} else {
if c.jsonSelect.([]string)[0] != "*" {
for _, v := range datas {
for i, _ := range v.(map[string]interface{}) {
getRemField[i] = i
}
}
itemToRemove := removeDuplicatesUnordered(getRemField, c.jsonSelect.([]string))
for _, found := range datas {
toMap := toolkit.M(found.(map[string]interface{}))
for _, remitem := range itemToRemove {
toMap.Unset(remitem)
}
ds.Data = append(ds.Data, found)
}
} else {
ds.Data = datas
}
}
} else if n > 0 {
fetched := 0
fetching := true
///read line
fetchFile, e := os.OpenFile(c.tempPathFile, os.O_RDWR, 0)
defer fetchFile.Close()
if e != nil {
return nil, errorlib.Error(packageName, modQuery+".Exec", "Fetch file", e.Error())
}
c.fetchSession = fetchFile
scanner := bufio.NewScanner(fetchFile)
lines := 0
for scanner.Scan() {
lines++
}
if lines > 0 {
fetched = lines
n = n + lines
}
for fetching {
var dataM = toolkit.M{}
if c.jsonSelect.([]string)[0] != "*" {
for i := 0; i < len(c.jsonSelect.([]string)); i++ {
dataM[c.jsonSelect.([]string)[i]] = datas[fetched].(map[string]interface{})[c.jsonSelect.([]string)[i]]
if len(dataM) == len(c.jsonSelect.([]string)) {
ds.Data = append(ds.Data, dataM)
}
}
} else {
ds.Data = append(ds.Data, datas[fetched])
}
io.WriteString(fetchFile, toolkit.JsonString(dataM)+"\n")
fetched++
if fetched == n {
fetching = false
}
}
}
// c.Close()
return ds, nil
}
func (c *Cursor) Fetch(m interface{}, n int, closeWhenDone bool) error {
if closeWhenDone {
c.Close()
}
e := c.prepIter()
if e != nil {
return errorlib.Error(packageName, modCursor, "Fetch", e.Error())
}
/*if c.jsonSelect == nil {
return errorlib.Error(packageName, modCursor, "Fetch", "Iter object is not yet initialized")
}*/
// var mData []interface{}
datas := []toolkit.M{}
dataJson := []toolkit.M{}
dec := json.NewDecoder(strings.NewReader(string(c.readFile)))
dec.Decode(&datas)
if n == 0 {
whereFieldsToMap, e := toolkit.ToM(c.whereFields)
if e != nil {
return errorlib.Error(packageName, modCursor, "Fetch", e.Error())
}
b := c.getCondition(whereFieldsToMap)
var foundSelected = toolkit.M{}
var foundData = []toolkit.M{}
var getRemField = toolkit.M{}
if c.isWhere {
if b {
for _, v := range datas {
for i, subData := range v {
getRemField[i] = i //append(getRemField, i)
for _, vWhere := range whereFieldsToMap {
for _, subWhere := range vWhere.([]interface{}) {
for _, subsubWhere := range subWhere.(map[string]interface{}) {
if len(c.jsonSelect) == 0 {
if strings.ToLower(subData.(string)) == strings.ToLower(subsubWhere.(string)) {
dataJson = append(dataJson, v)
}
} else {
if strings.ToLower(subData.(string)) == strings.ToLower(subsubWhere.(string)) {
foundData = append(foundData, v)
}
}
}
}
}
}
}
itemToRemove := removeDuplicatesUnordered(getRemField, c.jsonSelect)
if len(foundData) > 0 {
var found toolkit.M
for _, found = range foundData {
for _, remitem := range itemToRemove {
found.Unset(remitem)
}
dataJson = append(dataJson, found)
}
}
} else {
for _, v := range datas {
for _, v2 := range v {
for _, vWhere := range c.whereFields.(toolkit.M) {
if reflect.ValueOf(v2).Kind() == reflect.String {
if strings.ToLower(v2.(string)) == strings.ToLower(vWhere.(string)) {
if len(c.jsonSelect) == 0 {
dataJson = append(dataJson, v)
} else {
foundData = append(foundData, v)
}
}
}
}
}
}
if len(foundData) > 0 {
for _, found := range foundData {
for i, subData := range found {
for _, selected := range c.jsonSelect {
if strings.ToLower(selected) == strings.ToLower(i) {
foundSelected[i] = subData
} else if selected == "*" {
foundSelected[i] = subData
}
}
}
}
dataJson = append(dataJson, foundSelected)
}
}
// toolkit.Unjson(toolkit.Jsonify(dataJson), m)
toolkit.Serde(dataJson, m, "json")
} else {
if c.jsonSelect == nil {
toolkit.Unjson(toolkit.Jsonify(datas), m)
} else {
isSelectedFields := false
for _, selectField := range c.jsonSelect {
if selectField == "*" {
// toolkit.Unjson(toolkit.Jsonify(datas), m)
toolkit.Serde(datas, m, "json")
} else {
isSelectedFields = true
}
}
if isSelectedFields {
for _, v := range datas {
for i, _ := range v {
getRemField[i] = i
}
}
itemToRemove := removeDuplicatesUnordered(getRemField, c.jsonSelect)
for _, found := range datas {
toMap := toolkit.M(found)
for _, remitem := range itemToRemove {
toMap.Unset(remitem)
}
dataJson = append(dataJson, toMap)
}
// toolkit.Unjson(toolkit.Jsonify(dataJson), m)
toolkit.Serde(dataJson, m, "json")
}
}
}
} else if n > 0 {
fetched := 0
fetching := true
c.Connection().(*Connection).FetchSession()
c.tempPathFile = c.Connection().(*Connection).tempPathFile
///read line
fetchFile, e := os.OpenFile(c.Connection().(*Connection).tempPathFile, os.O_RDWR, 0)
defer fetchFile.Close()
if e != nil {
return errorlib.Error(packageName, modQuery+".Exec", "Fetch file", e.Error())
}
c.fetchSession = fetchFile
scanner := bufio.NewScanner(fetchFile)
lines := 0
for scanner.Scan() {
lines++
}
if lines > 0 {
fetched = lines
n = n + lines
}
for fetching {
var dataM = toolkit.M{}
if c.jsonSelect == nil {
dataJson = append(dataJson, datas[fetched])
} else {
for _, selectField := range c.jsonSelect {
if selectField == "*" {
dataJson = append(dataJson, datas[fetched])
} else {
dataM.Set(selectField, datas[fetched][selectField])
if len(dataM) == len(c.jsonSelect) {
dataJson = append(dataJson, dataM)
}
}
}
}
// toolkit.Unjson(toolkit.Jsonify(dataJson), m)
toolkit.Serde(dataJson, m, "json")
io.WriteString(fetchFile, toolkit.JsonString(dataM)+"\n")
fetched++
if fetched == n {
fetching = false
}
}
}
fmt.Sprintln("")
return 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 *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
}
