Exemple #1
0
func EmployeeFindByEnable(enable bool, order []string, skip, limit int) dbox.ICursor {
	c, _ := DB().Find(new(Employee),
		toolkit.M{}.Set("where", dbox.Eq("enable", enable)).
			Set("order", order).
			Set("skip", skip).
			Set("limit", limit))
	return dbox.NewCursor(c)
}
Exemple #2
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var (
		e        error
		dataMaps []toolkit.M
	)
	q.ReadFile(&dataMaps, q.Connection().(*Connection).filePath)
	cursor := dbox.NewCursor(new(Cursor))

	filters, e := q.Filters(in)
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	commandType := filters.GetString("cmdType")
	if commandType != dbox.QueryPartSelect {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", "Cursor is only working with select command, for "+commandType+" please use .Exec instead")
	}

	aggregate := false
	hasWhere := filters.Has("where")

	if !aggregate {
		var whereFields []*dbox.Filter
		var dataInterface interface{}
		json.Unmarshal(toolkit.Jsonify(dataMaps), &dataInterface)

		if hasWhere {
			whereFields = filters.Get("where").([]*dbox.Filter)
			// jsonSelect = fields
			cursor.(*Cursor).isWhere = true
		}
		cursor = cursor.SetConnection(q.Connection())
		cursor.(*Cursor).whereFields = whereFields
		cursor.(*Cursor).jsonSelect = filters.Get("select").([]string)
		cursor.(*Cursor).readFile = toolkit.Jsonify(dataMaps)
	} else {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", "No Aggregate function")
	}
	return cursor, nil
}
Exemple #3
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	dbname := q.Connection().Info().Database
	cursor := dbox.NewCursor(new(Cursor))
	if q.GetDriverDB() == "hive" {
		session := q.SessionHive()
		cursor.(*Cursor).sessionHive = session
	} else {
		session := q.Session()
		cursor.(*Cursor).session = session
	}
	cursor.(*Cursor).DateFormat = q.Connection().(*Connection).DateFormat
	driverName := q.GetDriverDB()
	// driverName = "oracle"
	var QueryString string

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()
	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]
	freeQueryParts, hasFreeQuery := parts["freequery"]

	idMap := toolkit.M{}
	if hasFrom {
		tablename := ""
		tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)

		selectParts, hasSelect := parts[dbox.QueryPartSelect]
		var attribute string
		incAtt := 0
		if hasSelect {
			for _, sl := range selectParts.([]*dbox.QueryPart) {
				for _, fid := range sl.Value.([]string) {
					if incAtt == 0 {
						attribute = fid
					} else {
						attribute = attribute + ", " + fid
					}

					idMap.Set(fid, fid)
					incAtt++
				}
			}
		} else {
			_, hasUpdate := parts[dbox.QueryPartUpdate]
			_, hasInsert := parts[dbox.QueryPartInsert]
			_, hasDelete := parts[dbox.QueryPartDelete]
			_, hasSave := parts[dbox.QueryPartSave]

			if hasUpdate || hasInsert || hasDelete || hasSave {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					"Valid operation for a cursor is select only")
			}
		}

		aggrParts, hasAggr := parts[dbox.QueryPartAggr]

		var aggrExpression string
		if hasAggr {
			incAtt := 0
			// for _, aggr := range aggrParts.([]interface{}) {
			for _, aggr := range aggrParts.([]*dbox.QueryPart) {
				// qp := aggr.(*dbox.QueryPart)
				/* isi qp :  &{AGGR {$sum 1 Total Item}}*/
				aggrInfo := aggr.Value.(dbox.AggrInfo)
				/* isi Aggr Info :  {$sum 1 Total Item}*/

				if incAtt == 0 {
					if driverName == "hive" {
						aggrExpression = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as " + aggrInfo.Alias
					} else {
						aggrExpression = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
					}
				} else {
					if driverName == "hive" {
						aggrExpression += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as " + aggrInfo.Alias
					} else {
						aggrExpression += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
							cast.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
					}
				}
				incAtt++
			}
			/* isi Aggr Expression :  sum(1) as 'Total Item', max(amount) as 'Max Amount', avg(amount) as 'Average Amount'*/
		}

		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				fs := p.Value.([]*dbox.Filter)
				for _, f := range fs {
					if in != nil {
						f = ReadVariable(f, in)
					}
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					e.Error())
			}
		}

		var orderExpression string
		orderParts, hasOrder := parts[dbox.QueryPartOrder]
		if hasOrder {
			qp := orderParts.([]*dbox.QueryPart)[0]
			for i, fid := range qp.Value.([]string) {
				if i == 0 {
					if string(fid[0]) == "-" {
						orderExpression = strings.Replace(fid, "-", "", 1) + " DESC"
					} else {
						orderExpression = fid + " ASC"
					}
				} else {
					if string(fid[0]) == "-" {
						orderExpression += ", " + strings.Replace(fid, "-", "", 1) + " DESC"
					} else {
						orderExpression += ", " + fid + " ASC"
					}
				}
			}
			// for _, oval := range orderParts.([]interface{}) {
			// 	qp := oval.(*dbox.QueryPart)
			// 	for i, fid := range qp.Value.([]string) {
			// 		if i == 0 {
			// 			if string(fid[0]) == "-" {
			// 				orderExpression = strings.Replace(fid, "-", "", 1) + " DESC"
			// 			} else {
			// 				orderExpression = fid + " ASC"
			// 			}
			// 		} else {
			// 			if string(fid[0]) == "-" {
			// 				orderExpression += ", " + strings.Replace(fid, "-", "", 1) + " DESC"
			// 			} else {
			// 				orderExpression += ", " + fid + " ASC"
			// 			}
			// 		}
			// 	}
			// }
		}

		skip := 0
		skipParts, hasSkip := parts[dbox.QueryPartSkip]
		if hasSkip {
			skip = skipParts.([]*dbox.QueryPart)[0].Value.(int)
		}

		take := 0
		takeParts, hasTake := parts[dbox.QueryPartTake]
		if hasTake {
			take = takeParts.([]*dbox.QueryPart)[0].Value.(int)
		}

		partGroup, hasGroup := parts[dbox.QueryPartGroup]
		var groupExpression string
		if hasGroup {
			for _, aggr := range partGroup.([]*dbox.QueryPart) {
				// qp := aggr.(*dbox.QueryPart)
				groupValue := aggr.Value.([]string)
				for i, val := range groupValue {
					if i == 0 {
						groupExpression += val
					} else {
						groupExpression += ", " + val
					}
				}
			}
			/* isi group expression :  GROUP BY nama*/
		}

		if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

		}
		if hasAggr {
			if hasSelect && attribute != "" {
				QueryString = "SELECT " + attribute + ", " + aggrExpression + " FROM " + tablename
			} else {
				QueryString = "SELECT " + aggrExpression + " FROM " + tablename
			}

		} else {
			if attribute == "" {
				QueryString = "SELECT * FROM " + tablename
			} else {
				if driverName == "oci8" {
					_, idVal := toolkit.IdInfo(idMap)
					QueryString = "SELECT " + attribute + ", rank() over(order by " + idVal.(string) + " asc) rn FROM " + tablename
				} else {
					QueryString = "SELECT " + attribute + " FROM " + tablename
				}
			}
		}

		if hasWhere {
			QueryString += " WHERE " + cast.ToString(where)
		}
		if hasGroup {
			QueryString += " GROUP BY " + groupExpression
		}
		if hasOrder {
			QueryString += " ORDER BY " + orderExpression
		}

		if driverName == "mysql" {
			if hasSkip && hasTake {
				QueryString += " LIMIT " + cast.ToString(take) +
					" OFFSET " + cast.ToString(skip)
			} else if hasSkip && !hasTake {
				QueryString += " LIMIT " + cast.ToString(9999999) +
					" OFFSET " + cast.ToString(skip)
			} else if hasTake && !hasSkip {
				QueryString += " LIMIT " + cast.ToString(take)
			}
		} else if driverName == "mssql" {
			if hasSkip && hasTake {
				QueryString += " OFFSET " + cast.ToString(skip) + " ROWS FETCH NEXT " +
					cast.ToString(take) + " ROWS ONLY "
			} else if hasSkip && !hasTake {
				QueryString += " OFFSET " + cast.ToString(skip) + " ROWS"
			} else if hasTake && !hasSkip {
				top := "SELECT TOP " + cast.ToString(take) + " "
				QueryString = strings.Replace(QueryString, "SELECT", top, 1)
			}

		} else if driverName == "oci8" {
			if hasSkip && hasTake {
				var lower, upper int
				upper = skip + take
				lower = upper - take + 1

				QueryString = "select * from (" + QueryString + ") t1 WHERE t1.rn BETWEEN " + cast.ToString(lower) + " AND " + cast.ToString(upper)
			} else if hasSkip && !hasTake {
				QueryString = "select * from (" + QueryString +
					") t1 WHERE t1.rn > " + cast.ToString(skip)
			} else if hasTake && !hasSkip {
				QueryString = "select * from (" + QueryString +
					") WHERE ROWNUM <= " + cast.ToString(take)
			}

		} else if driverName == "postgres" {
			if hasSkip && hasTake {
				QueryString += " LIMIT " + cast.ToString(take) +
					" OFFSET " + cast.ToString(skip)
			} else if hasSkip && !hasTake {
				QueryString += " LIMIT ALL" +
					" OFFSET " + cast.ToString(skip)
			} else if hasTake && !hasSkip {
				QueryString += " LIMIT " + cast.ToString(take)
			}
		} else if driverName == "hive" {
			if hasSkip && hasTake {
				var lower, upper int
				upper = skip + take
				lower = upper - take

				//toolkit.Println("take: ", take, "skip: ", skip, "lower: ", lower, "upper: ", upper)
				QueryString = "SELECT " + attribute + " FROM (SELECT *,row_number() over () as rowid FROM " + tablename + ") x WHERE rowid > " +
					toolkit.ToString(lower) + " and rowid <= " + toolkit.ToString(upper)
			} else if hasSkip && !hasTake {

			} else if hasTake && !hasSkip {
				QueryString += " LIMIT " + toolkit.ToString(take)
			}
		}
		// toolkit.Println(QueryString)
		var querystmt string
		if where != nil {
			querystmt = "select count(*) from " + tablename +
				" where " + cast.ToString(where)
		} else {
			querystmt = "select count(*) from " + tablename
		}

		/*populate fetch.count*/
		var rowCount int
		if driverName == "hive" {
			// rowCount = 999999999
			// row := sessionHive.Exec(querystmt)
			// rowCount = toolkit.ToInt(row[0], "auto")
		} else {
			rows, _ := cursor.(*Cursor).session.Query(querystmt)
			for rows.Next() {
				rows.Scan(&rowCount)
			}
		}
		if rowCount <= skip {
			rowCount = 0
		} else {
			rowCount -= skip
		}
		if rowCount >= take && take > 0 {
			rowCount = take
		}
		cursor.(*Cursor).count = rowCount
		cursor.(*Cursor).driver = driverName
		/*assign cursor.QueryString*/
		cursor.(*Cursor).QueryString = QueryString
	} else if hasProcedure {
		procCommand := procedureParts.([]*dbox.QueryPart)[0].Value.(interface{})

		spName := procCommand.(toolkit.M)["name"].(string) + " "
		params, hasParam := procCommand.(toolkit.M)["parms"]
		orderparam, hasOrder := procCommand.(toolkit.M)["orderparam"]
		ProcStatement := ""

		if driverName == "mysql" {
			paramstring := ""
			if hasParam && hasOrder {
				paramToolkit := params.(toolkit.M)
				orderString := orderparam.([]string)
				for i := 0; i < len(paramToolkit); i++ {
					if i == 0 {
						if strings.Contains(orderString[i], "@@") {
							paramstring = "(" + strings.Replace(orderString[i], "@@", "@", 1)
						} else if StringValue(paramToolkit[orderString[i]], driverName) != "''" {
							paramstring = "(" + StringValue(paramToolkit[orderString[i]], driverName)

						} else {
							paramstring = "("
						}

					} else {
						if strings.Contains(orderString[i], "@@") {
							paramstring += ", " + strings.Replace(orderString[i], "@@", "@", 1)
						} else {
							paramstring += ", " + StringValue(paramToolkit[orderString[i]], driverName)

						}
					}
				}

			} else if hasParam && !hasOrder {
				return nil, errorlib.Error(packageName, modQuery, "procedure", "please provide order parameter")
			} else {
				paramstring = "("
			}
			paramstring += ");"

			ProcStatement = "CALL " + spName + paramstring
		} else if driverName == "mssql" {
			paramstring := ""
			incParam := 0
			if hasParam {
				for key, val := range params.(toolkit.M) {
					if key != "" {
						if incParam == 0 {
							paramstring = key + " = " + StringValue(val, driverName) + ""

						} else {
							paramstring += ", " + key + " = " + StringValue(val, driverName) + ""
						}
						incParam += 1
					}
				}
				paramstring += ";"
			}

			ProcStatement = "EXECUTE " + spName + paramstring
		} else if driverName == "oci8" {
			var paramstring string
			var variable string
			var isEmpty bool
			if hasParam && hasOrder {
				paramToolkit := params.(toolkit.M)
				orderString := orderparam.([]string)
				for i := 0; i < len(paramToolkit); i++ {
					if i == 0 {
						if strings.Contains(orderString[i], "@@") {
							variable = "var " + strings.Replace(orderString[i], "@@", "", 1) +
								" " + cast.ToString(paramToolkit[orderString[i]]) + ";"
							paramstring = "(" + strings.Replace(orderString[i], "@@", ":", 1)
							isEmpty = false
						} else if StringValue(paramToolkit[orderString[i]], driverName) != "''" {
							paramstring = "(" + StringValue(paramToolkit[orderString[i]], driverName)
							isEmpty = false
						}

					} else {
						if strings.Contains(orderString[i], "@@") {
							variable += "var " + strings.Replace(orderString[i], "@@", "", 1) +
								" " + cast.ToString(paramToolkit[orderString[i]]) + ";"
							paramstring += ", " + strings.Replace(orderString[i], "@@", ":", 1)
						} else {
							paramstring += ", " + StringValue(paramToolkit[orderString[i]], driverName)

						}
					}
				}
				if !isEmpty {
					paramstring += ");"
				}
			} else if hasParam && !hasOrder {
				return nil, errorlib.Error(packageName, modQuery, "procedure", "please provide order parameter")
			}

			ProcStatement = variable + "EXECUTE " + spName + paramstring
			// toolkit.Println("ProcStatement>", ProcStatement)
		} else if driverName == "postgres" {
			paramstring := ""
			if hasParam && hasOrder {
				paramToolkit := params.(toolkit.M)
				orderString := orderparam.([]string)
				for i := 0; i < len(paramToolkit); i++ {
					if i == 0 {
						if strings.Contains(orderString[i], "@@") {
							paramstring = "(" + strings.Replace(orderString[i], "@@", "@", 1)
						} else if StringValue(paramToolkit[orderString[i]], driverName) != "''" {
							paramstring = "(" + StringValue(paramToolkit[orderString[i]], driverName)

						} else {
							paramstring = "("
						}

					} else {
						if strings.Contains(orderString[i], "@@") {
							paramstring += ", " + strings.Replace(orderString[i], "@@", "@", 1)
						} else {
							paramstring += ", " + StringValue(paramToolkit[orderString[i]], driverName)

						}
					}

					// fmt.Println("Print value order", paramstring)
				}

			} else if hasParam && !hasOrder {

				return nil, errorlib.Error(packageName, modQuery, "procedure", "please provide order parameter")

			} else {
				paramstring = "("
			}
			paramstring += ")"

			ProcStatement = "SELECT " + spName + paramstring
		}

		cursor.(*Cursor).QueryString = ProcStatement

		// fmt.Println("Proc Statement : ", ProcStatement)
	} else if hasFreeQuery {
		querySyntax := freeQueryParts.([]*dbox.QueryPart)[0].Value.(interface{})
		syntax := querySyntax.(toolkit.M)["syntax"].(string)
		cursor.(*Cursor).QueryString = syntax
	}
	return cursor, nil
}
Exemple #4
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error

	aggregate := false
	tablename := ""

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		for _, xsheet := range q.Reader().Sheets {
			tablename = xsheet.Name
			break
		}
	} else {
		tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)
	}

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
		// else {
		// 	return nil, errorlib.Error(packageName, modQuery, "Cursor",
		// 		"Invalid operation for a cursor, select syntax not found")
		// }
	}

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	var where []*dbox.Filter
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				// if len(in) > 0 {
				f = ReadVariable(f, in)
				// }
				where = append(where, f)
			}
		}
	}

	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	// cursor.(*Cursor).file = q.File()
	cursor.(*Cursor).reader = q.Reader()
	cursor.(*Cursor).headerColumn = q.Connection().(*Connection).headerColumn
	cursor.(*Cursor).sheetname = tablename
	cursor.(*Cursor).count = cursor.(*Cursor).Count()
	cursor.(*Cursor).fetchRow = 0
	cursor.(*Cursor).rowstart = q.Connection().(*Connection).rowstart

	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	if !aggregate {
		cursor.(*Cursor).ConditionVal.Find = where

		if fields != nil {
			cursor.(*Cursor).ConditionVal.Select = fields
		}

		if hasSort {
			cursor.(*Cursor).ConditionVal.Sort = sort
		}
		cursor.(*Cursor).ConditionVal.skip = skip
		cursor.(*Cursor).ConditionVal.limit = take

	} else {
		/*		pipes := toolkit.M{}
				mgoPipe := session.DB(dbname).C(tablename).
					Pipe(pipes).AllowDiskUse()
				//iter := mgoPipe.Iter()

				cursor.(*Cursor).ResultType = QueryResultPipe
				cursor.(*Cursor).mgoPipe = mgoPipe
				//cursor.(*Cursor).mgoIter = iter
		*/
	}
	return cursor, nil
}
Exemple #5
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error

	aggregate := false

	if q.Connection().(*Connection).setNewHeader {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return nil, errorlib.Error(packageName, "Cursor", modQuery, "Only Insert Query Permited")
	}

	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
			//fmt.Printf("Where: %s", toolkit.JsonString(where))
		}
		//where = iwhere.(toolkit.M)
	}

	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	cursor.(*Cursor).file = q.File()
	cursor.(*Cursor).reader = q.Reader()
	cursor.(*Cursor).headerColumn = q.Connection().(*Connection).headerColumn
	cursor.(*Cursor).count = 0
	// fmt.Println(cursor.(*Cursor).headerColumn)
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	if !aggregate {
		// fmt.Println("Query 173 : ", where)
		cursor.(*Cursor).ConditionVal.Find, _ = toolkit.ToM(where)

		if fields != nil {
			cursor.(*Cursor).ConditionVal.Select = fields
		}

		if hasSort {
			cursor.(*Cursor).ConditionVal.Sort = sort
		}
		cursor.(*Cursor).ConditionVal.skip = skip
		cursor.(*Cursor).ConditionVal.limit = take

	} else {
		/*		pipes := toolkit.M{}
				mgoPipe := session.DB(dbname).C(tablename).
					Pipe(pipes).AllowDiskUse()
				//iter := mgoPipe.Iter()

				cursor.(*Cursor).ResultType = QueryResultPipe
				cursor.(*Cursor).mgoPipe = mgoPipe
				//cursor.(*Cursor).mgoIter = iter
		*/
	}
	return cursor, nil
}
Exemple #6
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	dbname := q.Connection().Info().Database
	tablename := ""

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	//return nil, errorlib.Error(packageName, modQuery, "Cursor", "asdaa")
	//fmt.Printf("Query parts: %s\n", toolkit.JsonString(q.Parts()))
	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		return nil, errorlib.Error(packageName, "Query", "Cursor", "Invalid table name")
	}
	tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	aggrParts, hasAggr := parts[dbox.QueryPartAggr]
	aggrExpression := toolkit.M{}
	if hasAggr {
		aggregate = true
		aggrElements := func() []*dbox.QueryPart {
			var qps []*dbox.QueryPart
			for _, v := range aggrParts.([]interface{}) {
				qps = append(qps, v.(*dbox.QueryPart))
			}
			return qps
		}()
		for _, el := range aggrElements {
			aggr := el.Value.(dbox.AggrInfo)
			//if aggr.Op == dbox.AggrSum {
			aggrExpression.Set(aggr.Alias, toolkit.M{}.Set(aggr.Op, aggr.Field))
			//}
		}
		//toolkit.Printf("Aggr: %s\n", toolkit.JsonString(aggrExpression))
	}
	partGroup, hasGroup := parts[dbox.QueryPartGroup]
	if hasGroup {
		aggregate = true
		groups := func() toolkit.M {
			s := toolkit.M{}
			for _, v := range partGroup.([]interface{}) {
				gs := v.(*dbox.QueryPart).Value.([]string)
				for _, g := range gs {
					if strings.TrimSpace(g) != "" {
						s.Set(g, "$"+g)
					}
				}
			}
			return s
		}()
		if len(groups) == 0 {
			aggrExpression.Set("_id", "")
		} else {
			aggrExpression.Set("_id", groups)
		}
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}
	//fmt.Printf("Result: %s \n", toolkit.JsonString(fields))
	//fmt.Printf("Database:%s table:%s \n", dbname, tablename)
	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartOrder]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	//where := toolkit.M{}
	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
			//fmt.Printf("Where: %s\n", toolkit.JsonString(where))
		}
		//where = iwhere.(toolkit.M)
	}

	pipes := []toolkit.M{}
	pipe := parts["pipe"]
	if pipe != nil {
		aggregate = true
		pipes = pipe.([]interface{})[0].(*dbox.QueryPart).Value.([]toolkit.M)
	}

	session := q.Session()
	mgoColl := session.DB(dbname).C(tablename)
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	cursor.(*Cursor).isPoolingSession = q.usePooling

	if aggregate == true {
		if len(pipes) == 0 {
			pipes = append(pipes, toolkit.M{}.Set("$group", aggrExpression))
		}
		if hasWhere {
			pipes = append(append([]toolkit.M{}, toolkit.M{}.Set("$match", where)), pipes...)
		}
		mgoPipe := session.DB(dbname).C(tablename).
			Pipe(pipes).AllowDiskUse()
		//toolkit.Printf("Pipe: %s \n", toolkit.JsonString(pipes))
		//iter := mgoPipe.Iter()

		cursor.(*Cursor).ResultType = QueryResultPipe
		cursor.(*Cursor).mgoPipe = mgoPipe
		//cursor.(*Cursor).mgoIter = iter

	} else {
		mgoCursor := mgoColl.Find(where)
		count, e := mgoCursor.Count()
		if e != nil {
			//fmt.Println("Error: " + e.Error())
			return nil, errorlib.Error(packageName,
				modQuery, "Cursor", e.Error())
		}
		if fields != nil {
			mgoCursor = mgoCursor.Select(fields)
		}
		if hasSort {
			mgoCursor = mgoCursor.Sort(sort...)
		}
		if skip > 0 {
			mgoCursor = mgoCursor.Skip(skip)
		}
		if take > 0 {
			mgoCursor = mgoCursor.Limit(take)
		}
		cursor.(*Cursor).ResultType = QueryResultCursor
		cursor.(*Cursor).mgoCursor = mgoCursor
		cursor.(*Cursor).count = count
		//cursor.(*Cursor).mgoIter = mgoCursor.Iter()
	}

	if cursor == nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", "Unable to initialize cursor. This is likely caused by unimplemented command or invalid series of query")
	}

	//fmt.Println("Where: " + toolkit.JsonString(where))
	return cursor, nil
}
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	dbname := q.Connection().Info().Database
	session := q.Session()
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	driverName := q.GetDriverDB()
	//driverName := "mssql"

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]

	if hasFrom {
		tablename := ""
		tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

		skip := 0
		if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
			skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
				Value.(int)
		}

		take := 0
		if takeParts, has := parts[dbox.QueryPartTake]; has {
			take = takeParts.([]interface{})[0].(*dbox.QueryPart).
				Value.(int)
		}

		var fields toolkit.M
		selectParts, hasSelect := parts[dbox.QueryPartSelect]
		var attribute string
		incAtt := 0
		if hasSelect {
			fields = toolkit.M{}
			for _, sl := range selectParts.([]interface{}) {
				qp := sl.(*dbox.QueryPart)
				for _, fid := range qp.Value.([]string) {
					if incAtt == 0 {
						attribute = fid
					} else {
						attribute = attribute + "," + fid
					}
					incAtt++
					fields.Set(fid, 1)
				}
			}
		} else {
			_, hasUpdate := parts[dbox.QueryPartUpdate]
			_, hasInsert := parts[dbox.QueryPartInsert]
			_, hasDelete := parts[dbox.QueryPartDelete]
			_, hasSave := parts[dbox.QueryPartSave]

			if hasUpdate || hasInsert || hasDelete || hasSave {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					"Valid operation for a cursor is select only")
			}
		}
		//fmt.Printf("Result: %s \n", toolkit.JsonString(fields))
		//fmt.Printf("Database:%s table:%s \n", dbname, tablename)
		var sort []string
		sortParts, hasSort := parts[dbox.QueryPartSelect]
		if hasSort {
			sort = []string{}
			for _, sl := range sortParts.([]interface{}) {
				qp := sl.(*dbox.QueryPart)
				for _, fid := range qp.Value.([]string) {
					sort = append(sort, fid)
				}
			}
		}

		//where := toolkit.M{}
		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]interface{}) {
				fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
				for _, f := range fs {
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					e.Error())
			} else {
				//fmt.Printf("Where: %s", toolkit.JsonString(where))
			}
			//where = iwhere.(toolkit.M)
		}

		if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

		}
		if !aggregate {
			QueryString := ""
			if attribute == "" {
				QueryString = "SELECT * FROM " + tablename
			} else {
				QueryString = "SELECT " + attribute + " FROM " + tablename
			}
			if cast.ToString(where) != "" {
				QueryString = QueryString + " WHERE " + cast.ToString(where)
			}
			cursor.(*Cursor).QueryString = QueryString
		}
	} else if hasProcedure {
		procCommand := procedureParts.([]interface{})[0].(*dbox.QueryPart).Value.(interface{})
		fmt.Println("Isi Proc command : ", procCommand)

		spName := procCommand.(toolkit.M)["name"].(string) + " "
		params := procCommand.(toolkit.M)["parms"]
		incParam := 0
		ProcStatement := ""

		if driverName == "mysql" {
			paramValue := ""
			paramName := ""

			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramValue = "('" + val.(string) + "'"
					paramName = key
				} else {
					paramValue += ",'" + val.(string) + "'"
				}
				incParam += 1
			}
			paramValue += ", " + paramName + ")"

			ProcStatement = "CALL " + spName + paramValue
		} else if driverName == "mssql" {
			paramstring := ""
			incParam := 0
			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramstring = key + " = '" + val.(string) + "'"
				} else {
					paramstring += ", " + key + " = '" + val.(string) + "'"
				}
				incParam += 1
			}
			paramstring += ";"

			ProcStatement = "EXECUTE " + spName + paramstring
		} else if driverName == "oracle" {
			paramstring := ""
			incParam := 0
			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramstring = key + " = '" + val.(string) + "'"
				} else {
					paramstring += ", " + key + " = '" + val.(string) + "'"
				}
				incParam += 1
			}
			paramstring += ";"

			ProcStatement = "EXECUTE " + spName + paramstring

		}

		cursor.(*Cursor).QueryString = ProcStatement

		fmt.Println("Proc Statement : ", ProcStatement)
	}
	return cursor, nil
}
Exemple #8
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	dbname := q.Connection().Info().Database
	tablename := ""

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if hasFrom == false {
		return nil, errorlib.Error(packageName, "Query", "Cursor", "Invalid table name")
	}
	tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]interface{})[0].(*dbox.QueryPart).
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	var attribute string
	incAtt := 0
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				if incAtt == 0 {
					attribute = fid
				} else {
					attribute = attribute + "," + fid
				}
				incAtt++
				fields.Set(fid, 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}
	//fmt.Printf("Result: %s \n", toolkit.JsonString(fields))
	//fmt.Printf("Database:%s table:%s \n", dbname, tablename)
	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	//where := toolkit.M{}
	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
			//fmt.Printf("Where: %s", toolkit.JsonString(where))
		}
		//where = iwhere.(toolkit.M)
	}

	session := q.Session()
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

	}
	if !aggregate {
		QueryString := ""
		if attribute == "" {
			QueryString = "SELECT * FROM " + tablename
		} else {
			QueryString = "SELECT " + attribute + " FROM " + tablename
		}
		if cast.ToString(where) != "" {
			QueryString = QueryString + " WHERE " + cast.ToString(where)
		}
		cursor.(*Cursor).QueryString = QueryString
	} else {

	}
	return cursor, nil
}
Exemple #9
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	aggregate := false
	t, _ := ioutil.ReadFile(q.Connection().(*Connection).filePath)
	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())
	cursor.(*Cursor).readFile = t

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	// var fields toolkit.M
	var fields []string
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		// fields = toolkit.M{}
		for _, sl := range selectParts.([]interface{}) {
			qp := sl.(*dbox.QueryPart)
			for _, fid := range qp.Value.([]string) {
				fields = append(fields, fid)
				// fields.Set(fid, fid)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}

	// //where := toolkit.M{}
	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]interface{}) {
			fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}
		where, e = fb.Build()
		if e != nil {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				e.Error())
		} else {
		}
	}

	if !aggregate {
		var whereFields interface{}
		var dataInterface interface{}
		json.Unmarshal(t, &dataInterface)
		count, ok := dataInterface.([]interface{})

		if !ok {
			return nil, errorlib.Error(packageName,
				modQuery, "Cursor", "the file contains invalid json data")
		}
		cursor.(*Cursor).count = len(count)
		/*if fields != nil {
			q.Connection().(*Connection).FetchSession()
			// jsonSelect = fields
		}*/
		if where != nil {
			whereFields = where
			// jsonSelect = fields
			cursor.(*Cursor).isWhere = true
		}
		// cursor.(*Cursor).tempPathFile = q.Connection().(*Connection).tempPathFile
		cursor.(*Cursor).whereFields = whereFields
		cursor.(*Cursor).jsonSelect = fields
	} else {

	}
	return cursor, nil
}
Exemple #10
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error

	aggregate := false

	if q.Connection().(*Connection).setNewHeader {
		q.Connection().(*Connection).Close()
		filename := q.Connection().(*Connection).Info().Host
		os.Remove(filename)
		return nil, errorlib.Error(packageName, "Cursor", modQuery, "Only Insert Query Permited")
	}

	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)

	groupParts := c.Group(func(x interface{}) interface{} {
		return x.(*dbox.QueryPart).PartType
	}, nil).Exec()

	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	skip := 0
	if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
		skip = skipParts.([]*dbox.QueryPart)[0].
			Value.(int)
	}

	take := 0
	if takeParts, has := parts[dbox.QueryPartTake]; has {
		take = takeParts.([]*dbox.QueryPart)[0].
			Value.(int)
	}

	var fields toolkit.M
	selectParts, hasSelect := parts[dbox.QueryPartSelect]
	if hasSelect {
		fields = toolkit.M{}
		for _, sl := range selectParts.([]*dbox.QueryPart) {
			// qp := sl.(*dbox.QueryPart)
			for _, fid := range sl.Value.([]string) {
				fields.Set(strings.ToLower(fid), 1)
			}
		}
	} else {
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasUpdate || hasInsert || hasDelete || hasSave {
			return nil, errorlib.Error(packageName, modQuery, "Cursor",
				"Valid operation for a cursor is select only")
		}
	}

	var sort []string
	sortParts, hasSort := parts[dbox.QueryPartSelect]
	if hasSort {
		sort = []string{}
		for _, sl := range sortParts.([]*dbox.QueryPart) {
			// qp := sl.(*dbox.QueryPart)
			for _, fid := range sl.Value.([]string) {
				sort = append(sort, fid)
			}
		}
	}

	var where []*dbox.Filter
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				// if len(in) > 0 {
				f = ReadVariable(f, in)
				// }
				where = append(where, f)
			}
		}
	}

	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	cursor.(*Cursor).file = q.File()
	cursor.(*Cursor).reader = q.Reader()
	cursor.(*Cursor).headerColumn = q.Connection().(*Connection).headerColumn
	cursor.(*Cursor).count = 0
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	if !aggregate {
		cursor.(*Cursor).ConditionVal.where = where
		if fields != nil {
			cursor.(*Cursor).ConditionVal.Select = fields
		}

		if hasSort {
			cursor.(*Cursor).ConditionVal.Sort = sort
		}
		cursor.(*Cursor).ConditionVal.skip = skip
		cursor.(*Cursor).ConditionVal.limit = take
		if skip > 0 && take > 0 {
			cursor.(*Cursor).ConditionVal.limit += skip
		}

		e = cursor.(*Cursor).generateIndexes()

	} else {
		/*		pipes := toolkit.M{}
				mgoPipe := session.DB(dbname).C(tablename).
					Pipe(pipes).AllowDiskUse()
				//iter := mgoPipe.Iter()

				cursor.(*Cursor).ResultType = QueryResultPipe
				cursor.(*Cursor).mgoPipe = mgoPipe
				//cursor.(*Cursor).mgoIter = iter
		*/
	}
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}
	return cursor, nil
}
Exemple #11
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var (
		e        error
		dataMaps []toolkit.M
	)
	q.ReadFile(&dataMaps, q.Connection().(*Connection).filePath)
	cursor := dbox.NewCursor(new(Cursor))
	cursor = cursor.SetConnection(q.Connection())

	filters, e := q.Filters(in)
	if e != nil {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", e.Error())
	}

	commandType := filters.GetString("cmdType")
	if commandType != dbox.QueryPartSelect {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", "Cursor is only working with select command, for "+commandType+" please use .Exec instead")
	}

	aggregate := false
	hasWhere := filters.Has("where")
	hasAggregate := filters.Get("aggregate").(bool)
	if hasAggregate {
		aggregate = true
	}

	if !aggregate {
		if hasWhere {
			// toolkit.Println("where:", toolkit.JsonString(filters.Get("where")))
			cursor.(*Cursor).whereFields = filters.Get("where").([]*dbox.Filter)
			cursor.(*Cursor).isWhere = true
			cursor.(*Cursor).indexes = dbox.Find(dataMaps, filters.Get("where", []*dbox.Filter{}).([]*dbox.Filter))
		}
		// toolkit.Println("skip:", toolkit.JsonString(filters.Get("skip")))
		skip := 0
		if skip = filters.Get("skip").(int); skip > 0 {
			cursor.(*Cursor).skip = skip
		}
		// toolkit.Println("take:", toolkit.JsonString(filters.Get("take")))
		take := 0
		if take = filters.Get("take").(int); take > 0 {
			cursor.(*Cursor).take = take
		}

		if sort := filters.Get("sort").([]string); toolkit.SliceLen(sort) > 0 {
			fb := new(FilterBuilder)
			// toolkit.Printf("sorter:%v\n", sort)
			sorter := fb.SortFetch(sort, dataMaps)

			cursor.(*Cursor).datas = sorter
		} else {
			cursor.(*Cursor).datas = dataMaps
		}
		var count int
		if hasWhere {
			count = toolkit.SliceLen(cursor.(*Cursor).indexes)
		} else {
			count = toolkit.SliceLen(cursor.(*Cursor).datas)
		}
		if count <= skip {
			count = 0
		} else {
			count -= skip
		}
		if count >= take && take > 0 {
			count = take
		}
		cursor.(*Cursor).count = count

		cursor.(*Cursor).jsonSelect = filters.Get("select").([]string)
	} else {
		return nil, errorlib.Error(packageName, modQuery, "Cursor", "No Aggregate function")
	}
	return cursor, nil
}
Exemple #12
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

	// aggregate := false
	dbname := q.Connection().Info().Database
	session := q.Session()
	cursor := dbox.NewCursor(new(Cursor))
	cursor.(*Cursor).session = session
	// driverName := q.GetDriverDB()
	driverName := "oracle"
	var QueryString string

	/*
		parts will return E - map{interface{}}interface{}
		where each interface{} returned is slice of interfaces --> []interface{}
	*/
	parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		return qp.PartType
	}, nil).Data

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]

	if hasFrom {
		tablename := ""
		tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)

		skip := 0
		if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
			skip = skipParts.([]interface{})[0].(*dbox.QueryPart).
				Value.(int)
		}

		take := 0
		if takeParts, has := parts[dbox.QueryPartTake]; has {
			take = takeParts.([]interface{})[0].(*dbox.QueryPart).
				Value.(int)
		}

		aggrParts, hasAggr := parts[dbox.QueryPartAggr]

		var aggrExpression string
		if hasAggr {
			// aggregate = true
			incAtt := 0
			for _, aggr := range aggrParts.([]interface{}) {
				qp := aggr.(*dbox.QueryPart)
				// isi qp :  &{AGGR {$sum 1 Total Item}}
				aggrInfo := qp.Value.(dbox.AggrInfo)
				// isi Aggr Info :  {$sum 1 Total Item}

				if incAtt == 0 {
					aggrExpression = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
						cast.ToString(aggrInfo.Field) + ")" + " as '" + aggrInfo.Alias + "'"
				} else {
					aggrExpression += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) +
						"(" + cast.ToString(aggrInfo.Field) + ")" + " as '" + aggrInfo.Alias + "'"
				}
				incAtt++
			}
			QueryString = "SELECT " + aggrExpression + " FROM " + tablename
			// isi Aggr Expression :  sum(1) as 'Total Item', max(amount) as 'Max Amount', avg(amount) as 'Average Amount'
		}

		selectParts, hasSelect := parts[dbox.QueryPartSelect]
		var attribute string
		incAtt := 0
		if hasSelect {
			for _, sl := range selectParts.([]interface{}) {
				qp := sl.(*dbox.QueryPart)
				for _, fid := range qp.Value.([]string) {
					if incAtt == 0 {
						attribute = fid
					} else {
						attribute = attribute + "," + fid
					}
					incAtt++
				}
			}
			if attribute == "" {
				QueryString = "SELECT * FROM " + tablename
			} else {
				QueryString = "SELECT " + attribute + " FROM " + tablename
			}
		} else {
			_, hasUpdate := parts[dbox.QueryPartUpdate]
			_, hasInsert := parts[dbox.QueryPartInsert]
			_, hasDelete := parts[dbox.QueryPartDelete]
			_, hasSave := parts[dbox.QueryPartSave]

			if hasUpdate || hasInsert || hasDelete || hasSave {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					"Valid operation for a cursor is select only")
			} else {
				QueryString = "SELECT * FROM " + tablename
			}
		}
		var sort []string
		sortParts, hasSort := parts[dbox.QueryPartSelect]
		if hasSort {
			sort = []string{}
			for _, sl := range sortParts.([]interface{}) {
				qp := sl.(*dbox.QueryPart)
				for _, fid := range qp.Value.([]string) {
					sort = append(sort, fid)
				}
			}
		}

		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]interface{}) {
				fs := p.(*dbox.QueryPart).Value.([]*dbox.Filter)
				for _, f := range fs {
					if in != nil {
						f = ReadVariable(f, in)
					}
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, errorlib.Error(packageName, modQuery, "Cursor",
					e.Error())
			} else {
			}
			QueryString += " WHERE " + cast.ToString(where)
		}

		partGroup, hasGroup := parts[dbox.QueryPartGroup]
		var groupExpression string
		if hasGroup {
			// aggregate = true
			for _, aggr := range partGroup.([]interface{}) {
				qp := aggr.(*dbox.QueryPart)
				groupValue := qp.Value.([]string)
				for i, val := range groupValue {
					if i == 0 {
						groupExpression += val
					} else {
						groupExpression += ", " + val
					}
				}
			}
			QueryString += " GROUP BY " + groupExpression
			// isi group expression :  GROUP BY nama
		}

		if dbname != "" && tablename != "" && e != nil && skip == 0 && take == 0 && where == nil {

		}

		cursor.(*Cursor).QueryString = QueryString
	} else if hasProcedure {
		procCommand := procedureParts.([]interface{})[0].(*dbox.QueryPart).Value.(interface{})
		fmt.Println("Isi Proc command : ", procCommand)

		spName := procCommand.(toolkit.M)["name"].(string) + " "
		params := procCommand.(toolkit.M)["parms"]
		incParam := 0
		ProcStatement := ""

		if driverName == "mysql" {
			paramValue := ""
			paramName := ""

			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramValue = "('" + val.(string) + "'"
					paramName = key
				} else {
					paramValue += ",'" + val.(string) + "'"
				}
				incParam += 1
			}
			paramValue += ", " + paramName + ")"

			ProcStatement = "CALL " + spName + paramValue
		} else if driverName == "mssql" {
			paramstring := ""
			incParam := 0
			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					paramstring = key + " = '" + val.(string) + "'"
				} else {
					paramstring += ", " + key + " = '" + val.(string) + "'"
				}
				incParam += 1
			}
			paramstring += ";"

			ProcStatement = "EXECUTE " + spName + paramstring
		} else if driverName == "oracle" {
			paramstring := ""
			incParam := 0
			for key, val := range params.(toolkit.M) {
				if incParam == 0 {
					if strings.Contains(key, "@@") {
						paramstring = "(" + strings.Replace(key, "@@", ":", 1)
					} else {
						paramstring = "('" + val.(string) + "'"
					}
				} else {
					if strings.Contains(key, "@@") {
						paramstring += "," + strings.Replace(key, "@@", ":", 1)
					} else {
						paramstring += ",'" + val.(string) + "'"
					}
				}
				incParam += 1
			}
			paramstring += ");"

			ProcStatement = "EXECUTE " + spName + paramstring

		}

		cursor.(*Cursor).QueryString = ProcStatement

		fmt.Println("Proc Statement : ", ProcStatement)
	}
	return cursor, nil
}