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