Beispiel #1
0
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
}
Beispiel #2
0
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
}
Beispiel #3
0
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
}