Пример #1
0
func (q *Query) HasPartExec() error {
	var e error
	var lastJson []toolkit.M

	q.ReadFile(&lastJson, q.Connection().(*Connection).filePath)
	if toolkit.SliceLen(lastJson) > 0 {
		getWhere := []*dbox.Filter{}
		for _, v := range q.whereData {
			getWhere = []*dbox.Filter{v}
			i := dbox.Find(q.sliceData, getWhere)

			for idSlice := range q.sliceData {
				if toolkit.HasMember(i, idSlice) {
					idata := dbox.Find(lastJson, getWhere)
					for idx := range lastJson {
						if toolkit.HasMember(idata, idx) {
							lastJson[idx] = q.sliceData[idSlice]
						}
					}
					if toolkit.SliceLen(idata) == 0 {
						lastJson = append(lastJson, q.sliceData[idSlice])
					}
				}
			}
		}
		q.sliceData = lastJson
	} else {
		idx := []int{}
		for _, v := range q.whereData {
			getWhere := []*dbox.Filter{v}
			idx = dbox.Find(q.sliceData, getWhere)

		}
		// toolkit.Printf("newdata>%v\n", idx)
		if toolkit.SliceLen(idx) > 1 {
			newdata := toolkit.M{}
			for idslice, dataslice := range q.sliceData {
				if toolkit.HasMember(idx, idslice) {
					idf, _ := toolkit.IdInfo(dataslice)
					newdata = q.sliceData[idslice]
					toolkit.CopyM(&dataslice, &newdata, false, []string{idf})
				}
			}
			q.sliceData = []toolkit.M{}
			q.sliceData = append(q.sliceData, newdata)
		}

	}

	e = q.WriteFile(q.sliceData)
	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", "HasPartExec", e.Error())
	}
	return nil
}
Пример #2
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

	dbname := q.Connection().Info().Database
	driverName := q.GetDriverDB()
	// driverName = "oracle"
	tablename := ""
	data := parm.Get("data", nil)
	// fmt.Println("Hasil ekstraksi Param : ", data)

	/*========================EXTRACT FIELD, DATA AND FORMAT DATE=============================*/

	var attributes string
	var values string
	var setUpdate, statement string
	var i int

	dataM, e := toolkit.ToM(data)
	if e != nil {
		return errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
	}

	if data != nil {
		for field, val := range dataM {
			namaField := field
			dataValues := val
			stringValues := StringValue(dataValues, driverName)
			if i == 0 {
				attributes = "(" + namaField
				setUpdate = namaField + " = " + stringValues
				values = "(" + stringValues

			} else {
				attributes += ", " + namaField
				setUpdate += ", " + namaField + " = " + stringValues
				values += ", " + stringValues

			}
			i += 1
		}
		attributes += ")"
		values += ")"
	}

	/*=================================END OF EXTRACTION=======================================*/

	temp := ""
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		temp = toolkit.JsonString(qp)
		return qp.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]
	if !hasFrom {

		return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
	}
	tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)

	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 {
				fb.AddFilter(f)
			}
		}

		where, e = fb.Build()
		if e != nil {

		} else {

		}

	}
	commandType := ""
	multi := false

	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasSave := parts[dbox.QueryPartSave]

	if hasDelete {
		commandType = dbox.QueryPartDelete
	} else if hasInsert {
		commandType = dbox.QueryPartInsert
	} else if hasUpdate {
		commandType = dbox.QueryPartUpdate
	} else if hasSave {
		commandType = dbox.QueryPartSave
	}

	var id string
	var idVal interface{}
	if data == nil {
		multi = true
	} else {
		if where == nil {
			id, idVal = toolkit.IdInfo(data)
			if id != "" {
				where = id + " = " + StringValue(idVal, "non")
			}
		} else {
			multi = true
		}
	}

	session := q.Session()
	sessionHive := q.SessionHive()

	if dbname != "" && tablename != "" && multi == true {

	}
	if commandType == dbox.QueryPartInsert {
		if attributes != "" && values != "" {
			if driverName == "hive" {
				statement = "INSERT INTO " + tablename + " VALUES " + values
				e = sessionHive.Exec(statement, nil)
			} else {
				statement = "INSERT INTO " + tablename + " " + attributes + " VALUES " + values
				_, e = session.Exec(statement)
			}

			if e != nil {
				fmt.Println(e.Error())
			}
		} else {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"please provide the data")
		}

	} else if commandType == dbox.QueryPartUpdate {
		if setUpdate != "" {
			var statement string
			if where != nil {
				statement = "UPDATE " + tablename + " SET " + setUpdate +
					" WHERE " + cast.ToString(where)
			} else {
				statement = "UPDATE " + tablename + " SET " + setUpdate
			}
			if driverName == "hive" {
				e = sessionHive.Exec(statement, nil)
			} else {
				_, e = session.Exec(statement)
			}
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType,
					cast.ToString(e.Error()))
			}
		} else {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"please provide the data")
		}

	} else if commandType == dbox.QueryPartDelete {
		var statement string
		if where != nil {
			statement = "DELETE FROM " + tablename + " where " + cast.ToString(where)
		} else {
			statement = "DELETE FROM " + tablename
		}
		if driverName == "hive" {
			e = sessionHive.Exec(statement, nil)
		} else {
			_, e = session.Exec(statement)
		}
		if e != nil {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				cast.ToString(e.Error()))
		}
	} else if commandType == dbox.QueryPartSave {
		if attributes != "" && values != "" {
			var querystmt string
			if where != nil {
				querystmt = "select 1 as data from " + tablename + " where " + cast.ToString(where)
			}
			var rowCount int
			if driverName == "hive" {
				rowCount = 0
				// row := sessionHive.Exec(querystmt, nil)
				// rowCount = toolkit.ToInt(row[0], "auto")
			} else {
				if querystmt != "" {
					rows, _ := session.Query(querystmt)
					for rows.Next() {
						rows.Scan(&rowCount)
					}
				}
			}

			var statement string
			if rowCount == 0 || where == nil {
				if driverName == "hive" {
					statement = "INSERT INTO " + tablename + " VALUES " + values
				} else {
					statement = "INSERT INTO " + tablename + " " +
						attributes + " VALUES " + values
				}
			} else {
				statement = "UPDATE " + tablename + " SET " + setUpdate +
					" WHERE " + cast.ToString(where)
			}

			if driverName == "hive" {
				e = sessionHive.Exec(statement, nil)
			} else {
				_, e = session.Exec(statement)
			}
			if e != nil {
				return errorlib.Error(packageName, modQuery+".Exec", commandType,
					cast.ToString(e.Error()))
			}

		} else if values == "" {
			return errorlib.Error(packageName, modQuery+".Exec", commandType,
				"please provide the data")
		}

	}
	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
	}
	return nil
}
Пример #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
}
Пример #4
0
func (q *Query) ExecOut(parm toolkit.M) (int64, error) {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

	driverName := q.GetDriverDB()
	// driverName = "oracle"
	tablename := ""
	data := parm.Get("data")

	var attributes string
	var values string
	var setUpdate string

	var dataM toolkit.M
	var dataMs []toolkit.M
	var returnId int64

	if toolkit.IsSlice(data) {
		e = toolkit.Unjson(toolkit.Jsonify(data), &dataMs)
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
		}
	} else {
		dataM, e = toolkit.ToM(data)
		dataMs = append(dataMs, dataM)
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
		}
	}

	for _, dataVal := range dataMs {
		temp := ""
		quyerParts := q.Parts()
		c := crowd.From(&quyerParts)
		groupParts := c.Group(func(x interface{}) interface{} {
			qp := x.(*dbox.QueryPart)
			temp = toolkit.JsonString(qp)
			return qp.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
			}
		}

		commandType := ""

		_, hasDelete := parts[dbox.QueryPartDelete]
		_, hasInsert := parts[dbox.QueryPartInsert]
		_, hasUpdate := parts[dbox.QueryPartUpdate]
		_, hasSave := parts[dbox.QueryPartSave]

		if hasDelete {
			commandType = dbox.QueryPartDelete
		} else if hasInsert {
			commandType = dbox.QueryPartInsert
		} else if hasUpdate {
			commandType = dbox.QueryPartUpdate
		} else if hasSave {
			commandType = dbox.QueryPartSave
		}

		if hasInsert || hasUpdate || hasSave {
			attributes, setUpdate, values = extractData(dataVal, driverName)
		} else if hasDelete {

		}

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

		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 {
					fb.AddFilter(f)
				}
			}

			where, e = fb.Build()
			if e != nil {

			} else {

			}

		}

		var id string
		var idVal interface{}

		if where == nil {
			id, idVal = toolkit.IdInfo(dataVal)
			if id != "" {
				where = id + " = " + StringValue(idVal, "non")
			}
		}

		session := q.Session()
		sessionHive := q.SessionHive()

		if commandType == dbox.QueryPartInsert {
			if attributes != "" && values != "" {
				var statement string
				if driverName == "hive" {
					statement = "INSERT INTO " + tablename + " VALUES " + values
					e = sessionHive.Exec(statement, nil)
				} else {
					statement = "INSERT INTO " + tablename + " " + attributes + " VALUES " + values
					var res sql.Result
					res, e = session.Exec(statement)
					if res != nil {
						returnId, _ = res.LastInsertId()
					}
				}

				if e != nil {
					return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
						cast.ToString(e.Error()))
				}
			} else {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					"please provide the data")
			}
		} else if commandType == dbox.QueryPartUpdate {
			if setUpdate != "" {
				var statement string
				if where != nil {
					statement = "UPDATE " + tablename + " SET " + setUpdate +
						" WHERE " + cast.ToString(where)
				} else {
					statement = "UPDATE " + tablename + " SET " + setUpdate
				}
				if driverName == "hive" {
					e = sessionHive.Exec(statement, nil)
				} else {
					_, e = session.Exec(statement)
				}
				if e != nil {
					return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
						cast.ToString(e.Error()))
				}
			} else {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					"please provide the data")
			}

		} else if commandType == dbox.QueryPartDelete {
			var statement string
			if where != nil {
				statement = "DELETE FROM " + tablename + " where " + cast.ToString(where)
			} else {
				statement = "DELETE FROM " + tablename
			}
			if driverName == "hive" {
				e = sessionHive.Exec(statement, nil)
			} else {
				_, e = session.Exec(statement)
			}
			if e != nil {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					cast.ToString(e.Error()))
			}
		} else if commandType == dbox.QueryPartSave {
			if attributes != "" && values != "" {
				var querystmt string
				if where != nil {
					querystmt = "select 1 as data from " + tablename + " where " + cast.ToString(where)
				}
				var rowCount int
				if driverName == "hive" {
					rowCount = 0
					// row := sessionHive.Exec(querystmt, nil)
					// rowCount = toolkit.ToInt(row[0], "auto")
				} else {
					if querystmt != "" {
						rows, _ := session.Query(querystmt)
						for rows.Next() {
							rows.Scan(&rowCount)
						}
					}
				}

				var statement string
				if rowCount == 0 || where == nil {
					if driverName == "hive" {
						statement = "INSERT INTO " + tablename + " VALUES " + values
					} else {
						statement = "INSERT INTO " + tablename + " " +
							attributes + " VALUES " + values
					}
				} else {
					statement = "UPDATE " + tablename + " SET " + setUpdate +
						" WHERE " + cast.ToString(where)
				}

				if driverName == "hive" {
					e = sessionHive.Exec(statement, nil)
				} else {
					_, e = session.Exec(statement)
				}
				if e != nil {
					return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
						cast.ToString(e.Error()))
				}

			} else if values == "" {
				return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType,
					"please provide the data")
			}

		}
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
		}
	}
	return returnId, nil
}
Пример #5
0
func (q *Query) Exec(in toolkit.M) error {
	setting, e := q.prepare(in)
	commandType := setting["commandtype"].(string)
	//toolkit.Printf("Command type: %s\n", commandType)
	if e != nil {
		return err.Error(packageName, modQuery, "Exec: "+commandType, e.Error())
	}

	if setting.GetString("commandtype") == dbox.QueryPartSelect {
		return err.Error(packageName, modQuery, "Exec: "+commandType, "Exec is not working with select command, please use .Cursor instead")
	}

	q.Lock()
	defer q.Unlock()

	var dataM toolkit.M
	var dataMs []toolkit.M

	hasData := in.Has("data")
	dataIsSlice := false
	data := in.Get("data")
	if toolkit.IsSlice(data) {
		dataIsSlice = true
		e = toolkit.Unjson(toolkit.Jsonify(data), dataMs)
		if e != nil {
			return err.Error(packageName, modQuery, "Exec: "+commandType, "Data encoding error: "+e.Error())
		}
	} else {
		dataM, e = toolkit.ToM(data)
		dataMs = append(dataMs, dataM)
		if e != nil {
			return err.Error(packageName, modQuery, "Exec: "+commandType, "Data encoding error: "+e.Error())
		}
	}

	hasWhere := setting.Has("where")
	where := setting.Get("where", []*dbox.Filter{}).([]*dbox.Filter)
	if hasWhere && len(where) == 0 {
		inWhere := in.Get("where")
		if inWhere == nil {
			hasWhere = false
			where = nil
		} else {
			if !toolkit.IsSlice(inWhere) {
				where = append(where, inWhere.(*dbox.Filter))
			} else {
				where = inWhere.([]*dbox.Filter)
			}
		}
	}

	if hasData && hasWhere == false && toolkit.HasMember([]interface{}{dbox.QueryPartInsert, dbox.QueryPartDelete,
		dbox.QueryPartUpdate, dbox.QueryPartSave}, commandType) {
		hasWhere = true
		//toolkit.Println("check where")
		if toolkit.IsSlice(data) {
			ids := []interface{}{}
			idField := ""
			if idField == "" {
				return err.Error(packageName, modQuery, "Exec: "+commandType, "Data send is a slice, but its element has no ID")
			}
			dataCount := toolkit.SliceLen(data)
			for i := 0; i < dataCount; i++ {
				dataI := toolkit.SliceItem(data, i)
				if i == 0 {
					idField = toolkit.IdField(dataI)
				}
				ids = append(ids, toolkit.Id(dataI))
			}
			where = []*dbox.Filter{dbox.In(idField, ids)}
		} else {
			idfield := "_id"
			id := toolkit.Id(data)
			if !toolkit.IsNilOrEmpty(id) {
				where = []*dbox.Filter{dbox.Eq(idfield, id)}
			} else {
				where = nil
				hasWhere = false
			}
		}
	}
	/*
		toolkit.Printf("CommandType: %s HasData: %v HasWhere: %v Where: %s\n",
			commandType, hasData, hasWhere, toolkit.JsonString(where))
	*/
	e = q.openFile(commandType)
	//toolkit.Printf(commandType+" Open File, found record: %d\nData:%s\n", len(q.data), toolkit.JsonString(q.data))
	if e != nil {
		return err.Error(packageName, modQuery, "Exec: "+commandType, e.Error())
	}

	var indexes []interface{}
	if hasWhere && commandType != dbox.QueryPartInsert {
		whereIndex := dbox.Find(q.data, where)
		indexes = toolkit.ToInterfaceArray(&whereIndex)
		//toolkit.Printf("Where Index: %s Index:%s\n", toolkit.JsonString(whereIndex), toolkit.JsonString(indexes))
	}
	if commandType == dbox.QueryPartInsert {
		if !hasData {
			return err.Error(packageName, modQuery, "Exec: "+commandType, "Data is empty")
		}
		if !dataIsSlice {
			dataMs = []toolkit.M{dataM}
		}

		//-- validate
		for _, datam := range dataMs {
			idField, idValue := toolkit.IdInfo(datam)
			toolkit.Serde(dbox.Find(q.data, []*dbox.Filter{dbox.Eq(idField, idValue)}), &indexes, "")
			if len(indexes) > 0 {
				return err.Error(packageName, modQuery, "Exec: "+commandType, toolkit.Sprintf("Data %v already exist", idValue))
			}
		}

		//-- insert the data
		q.data = append(q.data, dataMs...)
	} else if commandType == dbox.QueryPartUpdate {

		//-- valida
		if !hasData {
			return err.Error(packageName, modQuery, "Exec: "+commandType, "Data is empty")
		}

		var dataUpdate toolkit.M
		var updateDataIndex int

		// if it is a slice then we need to update each data passed on its slice
		isDataSlice := toolkit.IsSlice(data)
		if isDataSlice == false {
			isDataSlice = false
			e = toolkit.Serde(data, &dataUpdate, "")
			if e != nil {
				return err.Error(packageName, modQuery, "Exec: "+commandType, "Serde data fail"+e.Error())
			}
		}

		var idField string
		//toolkit.Printf("Indexes: %s\n", toolkit.JsonString(indexes))

		for i, v := range q.data {
			// update only data that match given inde
			if toolkit.HasMember(indexes, i) || !hasWhere {
				if idField == "" {
					idField = toolkit.IdField(v)
					if idField == "" {
						return err.Error(packageName, modQuery, "Exec: "+commandType, "No ID")
					}
				}

				// If dataslice is sent, iterate f
				if isDataSlice {
					e = toolkit.Serde(toolkit.SliceItem(data, updateDataIndex), &dataUpdate, "")
					if e != nil {
						return err.Error(packageName, modQuery, "Exec: "+commandType, "Serde data fail "+e.Error())
					}
					updateDataIndex++
				}
				dataOrigin := q.data[i]
				toolkit.CopyM(&dataUpdate, &dataOrigin, false, []string{"_id"})
				toolkit.Serde(dataOrigin, &v, "")
				q.data[i] = v
			}
		}
	} else if commandType == dbox.QueryPartDelete {
		if hasWhere && len(where) > 0 {
			indexes := dbox.Find(q.data, where)
			if len(indexes) > 0 {
				newdata := []toolkit.M{}
				for index, v := range q.data {
					partOfIndex := toolkit.HasMember(indexes, index)
					if partOfIndex == false {
						newdata = append(newdata, v)
					}
					//toolkit.Println("i:", indexes, ", index:", index, ", p.ofIndex: ", partOfIndex, ", data: ", toolkit.JsonString(newdata))
				}
				q.data = newdata
			}
		} else {
			q.data = []toolkit.M{}
		}
		//toolkit.Printf("Data now: %s\n", toolkit.JsonString(q.data))
	} else if commandType == dbox.QueryPartSave {
		if !hasData {
			return err.Error(packageName, modQuery, "Exec: "+commandType, "Data is empty")
		}

		var dataMs []toolkit.M
		var dataM toolkit.M
		if !toolkit.IsSlice(data) {
			e = toolkit.Serde(&data, &dataM, "json")
			if e != nil {
				return err.Error(packageName, modQuery, "Exec: "+commandType+" Serde data fail", e.Error())
			}
			dataMs = append(dataMs, dataM)
		} else {
			e = toolkit.Serde(&data, &dataMs, "json")
			if e != nil {
				return err.Error(packageName, modQuery, "Exec: "+commandType+" Serde data fail", e.Error())
			}
		}
		//toolkit.Printf("Saving: %s\n", toolkit.JsonString(dataMs))

		for _, v := range dataMs {
			idField, idValue := toolkit.IdInfo(v)
			indexes := dbox.Find(q.data, []*dbox.Filter{dbox.Eq(idField, idValue)})
			if len(indexes) == 0 {
				q.data = append(q.data, v)
			} else {
				dataOrigin := q.data[indexes[0]]
				//toolkit.Printf("Copy data %s to %s\n", toolkit.JsonString(v), toolkit.JsonString(dataOrigin))
				toolkit.CopyM(&v, &dataOrigin, false, []string{idField})
				q.data[indexes[0]] = dataOrigin
			}
		}
	}
	e = q.writeFile()
	if e != nil {
		return err.Error(packageName, modQuery, "Exec: "+commandType+" Write fail", e.Error())
	}
	return nil
}
Пример #6
0
func (q *Query) prepare(in toolkit.M) (out toolkit.M, e error) {
	out = toolkit.M{}

	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
		}
	}

	_, hasUpdate := parts[dbox.QueryPartUpdate]
	_, hasInsert := parts[dbox.QueryPartInsert]
	_, hasDelete := parts[dbox.QueryPartDelete]
	_, hasSave := parts[dbox.QueryPartSave]
	_, hasFrom := parts[dbox.QueryPartFrom]
	procedureParts, hasProcedure := parts["procedure"]

	var tableName string
	if hasFrom {
		fromParts, _ := parts[dbox.QueryPartFrom]
		tableName = fromParts.([]*dbox.QueryPart)[0].Value.(string)
	} else {

		return nil, err.Error(packageName, "Query", "prepare", "Invalid table name")
	}
	out.Set("tableName", tableName)

	if freeQueryParts, hasFreeQuery := parts["freequery"]; hasFreeQuery {
		var syntax string
		qsyntax := freeQueryParts.([]*dbox.QueryPart)[0].Value.(interface{})
		syntax = qsyntax.(toolkit.M)["syntax"].(string)
		out.Set("freequery", syntax)
		out.Set("cmdType", dbox.QueryPartSelect)
	} else if hasInsert || hasUpdate || hasDelete || hasSave {
		if hasUpdate {
			out.Set("cmdType", dbox.QueryPartUpdate)
		} else if hasInsert {
			out.Set("cmdType", dbox.QueryPartInsert)
		} else if hasDelete {
			out.Set("cmdType", dbox.QueryPartDelete)
		} else if hasSave {
			out.Set("cmdType", dbox.QueryPartSave)
		}

		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 {
					fb.AddFilter(f)
				}
			}

			where, e = fb.Build()
			if e != nil {

			}
			out.Set("where", where)
		}

		var dataM toolkit.M
		var dataMs []toolkit.M

		hasData := in.Has("data")
		var dataIsSlice bool
		if hasData {
			data := in.Get("data")
			if toolkit.IsSlice(data) {
				dataIsSlice = true
				e = toolkit.Unjson(toolkit.Jsonify(data), dataMs)
				if e != nil {
					return nil, err.Error(packageName, modQuery, "Exec: ", "Data encoding error: "+e.Error())
				}
			} else {
				dataM, e = toolkit.ToM(data)
				dataMs = append(dataMs, dataM)
				if e != nil {
					return nil, err.Error(packageName, modQuery, "Exec: ", "Data encoding error: "+e.Error())
				}
			}

			var id string
			var idVal interface{}
			if where == nil {
				id, idVal = toolkit.IdInfo(data)
				if id != "" {
					where = id + " = " + StringValue(idVal, "non")
				}
				out.Set("where", where)
			}

			if !dataIsSlice {
				var fields string
				var values string
				var setUpdate string
				var inc int
				for field, val := range dataM {
					stringval := StringValue(val, "non")
					if inc == 0 {
						fields = "(" + field
						values = "(" + stringval
						setUpdate = field + " = " + stringval
					} else {
						fields += ", " + field
						values += ", " + stringval
						setUpdate += ", " + field + " = " + stringval
					}
					inc++
				}
				fields += ")"
				values += ")"
				if hasInsert || hasSave {
					out.Set("fields", fields)
					out.Set("values", values)
				}
				if hasUpdate || hasSave {
					out.Set("setUpdate", setUpdate)
				}
			}
		}
	} else if hasProcedure {
		cmd := procedureParts.([]*dbox.QueryPart)[0].Value.(interface{})

		spName := cmd.(toolkit.M)["name"].(string) + " "
		params, hasParams := cmd.(toolkit.M)["params"]
		orderparam, hasOrder := cmd.(toolkit.M)["orderparam"]
		ProcStatement := ""
		toolkit.Println(spName, params, hasParams, orderparam, hasOrder, ProcStatement)

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

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

				if incAtt == 0 {
					aggrExp = strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
						toolkit.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
				} else {
					aggrExp += ", " + strings.Replace(aggrInfo.Op, "$", "", 1) + "(" +
						toolkit.ToString(aggrInfo.Field) + ")" + " as \"" + aggrInfo.Alias + "\""
				}
				incAtt++
			}
		}
		out.Set("aggr", aggrExp)

		///
		/// Where Condition
		var where interface{}
		if whereParts, hasWhere := parts[dbox.QueryPartWhere]; hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				for _, f := range p.Value.([]*dbox.Filter) {
					if in != nil {
						f = rdbms.ReadVariable(f, in)
					}
					fb.AddFilter(f)
				}
			}
			where, e = fb.Build()
			if e != nil {
				return nil, err.Error(packageName, modQuery, "prepare", e.Error())
			}
		}
		out.Set("where", where)

		///
		/// Sort Condition
		var sort []string
		if sortParts, hasSort := parts[dbox.QueryPartOrder]; hasSort {
			sort = []string{}
			for _, sr := range sortParts.([]*dbox.QueryPart) {
				for _, s := range sr.Value.([]string) {
					sort = append(sort, s)
				}
			}
		}
		out.Set("sort", sort)

		///
		/// Take Condition
		take := 0
		isTake := false
		if takeParts, hasTake := parts[dbox.QueryPartTake]; hasTake {
			isTake = true
			take = takeParts.([]*dbox.QueryPart)[0].Value.(int)
		}
		out.Set("isTake", isTake)
		out.Set("take", take)

		///
		/// Skip Condition
		skip := 0
		isSkip := false
		if skipParts, hasSkip := parts[dbox.QueryPartSkip]; hasSkip {
			isSkip = true
			skip = skipParts.([]*dbox.QueryPart)[0].Value.(int)
		}
		out.Set("isSkip", isSkip)
		out.Set("skip", skip)

		///
		/// Group By Condition
		var groupExp string
		hasAggr := false
		if groupParts, hasGroup := parts[dbox.QueryPartGroup]; hasGroup {
			hasAggr = true
			for _, pg := range groupParts.([]*dbox.QueryPart) {
				for i, grValue := range pg.Value.([]string) {
					if i == 0 {
						groupExp += grValue
					} else {
						groupExp += ", " + grValue
					}
				}
			}
		}
		out.Set("group", groupExp)
		out.Set("hasAggr", hasAggr)

		///
		/// Order By Condition
		var orderExp string
		if orderParts, hasOrder := parts[dbox.QueryPartOrder]; hasOrder {
			for _, ordrs := range orderParts.([]*dbox.QueryPart) {
				for i, oVal := range ordrs.Value.([]string) {
					if i == 0 {
						if string(oVal[0]) == "-" {
							orderExp = strings.Replace(oVal, "-", "", 1) + " DESC"
						} else {
							orderExp = oVal + " ASC"
						}
					} else {
						if string(oVal[0]) == "-" {
							orderExp += ", " + strings.Replace(oVal, "-", "", 1) + " DESC"
						} else {
							orderExp += ", " + oVal + " ASC"
						}
					}
				}
			}
		}
		out.Set("order", orderExp)
	}

	return
}
Пример #7
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	q.Sess = q.Session()

	cursor := new(Cursor)
	var queryString string

	setting, e := q.prepare(in)
	if e != nil {
		return nil, err.Error(packageName, modQuery, "Cursor", e.Error())
	}

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

	if setting.Get("freequery", "").(string) != "" {
		queryString = setting.Get("freequery", "").(string)
	} else {
		if setting.Get("hasAggr", "").(bool) == true {
			if setting.GetString("selectField") != "" {
				queryString = "SELECT " + setting.GetString("selectField") + ", " + setting.GetString("aggr") + " FROM " + setting.GetString("tableName")
			} else {
				queryString = "SELECT " + setting.GetString("aggr") + " FROM " + setting.GetString("tableName")
			}
		} else {
			if setting.GetString("selectField") != "" {
				if (strings.ToLower(q.Connection().(*Connection).GetDriver()) == "oci8" &&
					setting.Get("isSkip").(bool)) || (strings.ToLower(q.Connection().(*Connection).GetDriver()) == "oci8" && setting.Get("isSkip").(bool) && setting.Get("isTake").(bool)) {
					splitField := strings.Split(setting.GetString("selectField"), ", ")
					field := toolkit.M{}
					for _, f := range splitField {
						field.Set(f, f)
					}
					_, idVal := toolkit.IdInfo(field)
					toolkit.Println(splitField, idVal)
					queryString = "SELECT " + setting.GetString("selectField") + ", rank() over(order by " + toolkit.ToString(idVal) + " asc) rn FROM " + setting.GetString("tableName")
				} else {
					queryString = "SELECT " + setting.GetString("selectField") + " FROM " + setting.GetString("tableName")
				}
			} else {
				queryString = "SELECT * FROM " + setting.GetString("tableName")
			}
		}

		if setting.Get("where", "") != nil {
			queryString += " WHERE " + setting.Get("where", "").(string)
		}

		if setting.Get("group", "") != "" {
			queryString += " GROUP BY " + setting.GetString("group")
		}

		if setting.Get("order", "") != "" {
			queryString += " ORDER BY " + setting.Get("order", "").(string)
		}

		if strings.ToLower(q.Connection().(*Connection).GetDriver()) == "mysql" {
			if setting.Get("isSkip").(bool) && setting.Get("isTake").(bool) {
				queryString += " LIMIT " + toolkit.ToString(setting.GetInt("take")) + " OFFSET " + toolkit.ToString(setting.GetInt("skip"))
			} else if setting.Get("isSkip").(bool) && !setting.Get("isTake").(bool) {
				queryString += " LIMIT " + toolkit.ToString(9999999) + " OFFSET " + toolkit.ToString(setting.GetInt("skip"))
			} else if setting.Get("isTake").(bool) && !setting.Get("isSkip").(bool) {
				queryString += " LIMIT " + toolkit.ToString(setting.GetInt("take"))
			}
		} else if strings.ToLower(q.Connection().(*Connection).GetDriver()) == "mssql" {
			if setting.Get("isSkip").(bool) && setting.Get("isTake").(bool) {
				queryString += " OFFSET " + toolkit.ToString(setting.GetInt("skip")) + " ROWS FETCH NEXT " + toolkit.ToString(setting.GetInt("take")) + " ROWS ONLY "
			} else if setting.Get("isSkip").(bool) && !setting.Get("isTake").(bool) {
				queryString += " OFFSET " + toolkit.ToString(setting.GetInt("skip")) + " ROWS"
			} else if setting.Get("isTake").(bool) && !setting.Get("isSkip").(bool) {
				top := "SELECT TOP " + toolkit.ToString(setting.GetInt("take")) + " "
				queryString = strings.Replace(queryString, "SELECT", top, 1)
			}
		} else if strings.ToLower(q.Connection().(*Connection).GetDriver()) == "postgres" {
			if setting.Get("isSkip").(bool) && setting.Get("isTake").(bool) {
				queryString += " LIMIT " + toolkit.ToString(setting.GetInt("take")) + " OFFSET " + toolkit.ToString(setting.GetInt("skip"))
			} else if setting.Get("isSkip").(bool) && !setting.Get("isTake").(bool) {
				queryString += " LIMIT ALL" + " OFFSET " + toolkit.ToString(setting.GetInt("skip"))
			} else if setting.Get("isTake").(bool) && !setting.Get("isSkip").(bool) {
				queryString += " LIMIT " + toolkit.ToString(setting.GetInt("take"))
			}
		} else if strings.ToLower(q.Connection().(*Connection).GetDriver()) == "oci8" {
			if setting.Get("isSkip").(bool) && setting.Get("isTake").(bool) {
				var lower, upper int
				upper = setting.GetInt("skip") + setting.GetInt("take")
				lower = upper - setting.GetInt("take") + 1

				queryString = "select * from (" + queryString + ") t1 WHERE t1.rn BETWEEN " + toolkit.ToString(lower) + " AND " + toolkit.ToString(upper)
			} else if setting.Get("isSkip").(bool) && !setting.Get("isTake").(bool) {
				queryString = "select * from (" + queryString +
					") t1 WHERE t1.rn > " + toolkit.ToString(setting.GetInt("skip"))
			} else if setting.Get("isTake").(bool) && !setting.Get("isSkip").(bool) {
				queryString = "select * from (" + queryString +
					") WHERE ROWNUM <= " + toolkit.ToString(setting.GetInt("take"))
			}
		}
	}

	q.QStatement = queryString
	out, e := q.Statement()
	if e != nil {
		return nil, err.Error(packageName, modQuery, "Cursor", e.Error())
	}
	// toolkit.Println(out)
	cursor.data = out.Get("data", "").(toolkit.Ms)
	cursor.count = out.Get("count", "").(int)
	cursor.Sess = q.Sess
	return cursor, nil
}