func ReadVariable(f *dbox.Filter, in toolkit.M) *dbox.Filter {
if (f.Op == "$and" || f.Op == "$or") && strings.Contains(reflect.TypeOf(f.Value).String(), "dbox.Filter") {
fs := f.Value.([]*dbox.Filter)
for i, ff := range fs {
bf := ReadVariable(ff, in)
fs[i] = bf
}
f.Value = fs
} else {
if reflect.TypeOf(f.Value).Kind() == reflect.Slice {
fSlice := f.Value.([]interface{})
// nilai fSlice : [@name1 @name2]
for i := 0; i < len(fSlice); i++ {
// nilai fSlice [i] : @name1
if string(cast.ToString(fSlice[i])[0]) == "@" {
fSlice[i] = in.Get(strings.Replace(cast.ToString(fSlice[i]), "@", "", 1), "")
}
}
f.Value = fSlice
} else if string(cast.ToString(f.Value)[0]) == "@" {
f.Value = in.Get(strings.Replace(cast.ToString(f.Value), "@", "", 1), "")
}
}
return f
}
func ReadVariable(f *dbox.Filter, in toolkit.M) *dbox.Filter {
f.Field = strings.ToLower(f.Field)
if (f.Op == "$and" || f.Op == "$or") &&
strings.Contains(reflect.TypeOf(f.Value).String(), "dbox.Filter") {
fs := f.Value.([]*dbox.Filter)
/* nilai fs : [0xc082059590 0xc0820595c0]*/
for i, ff := range fs {
/* nilai ff[0] : &{umur $gt @age} && ff[1] : &{name $eq @nama}*/
bf := ReadVariable(ff, in)
/* nilai bf[0] : &{umur $gt 25} && bf[1] : &{name $eq Kane}*/
fs[i] = bf
}
f.Value = fs
return f
} else {
if reflect.TypeOf(f.Value).Kind() == reflect.Slice {
if strings.Contains(reflect.TypeOf(f.Value).String(), "interface") {
fSlice := f.Value.([]interface{})
/*nilai fSlice : [@name1 @name2]*/
for i := 0; i < len(fSlice); i++ {
/* nilai fSlice [i] : @name1*/
if string(cast.ToString(fSlice[i])[0]) == "@" {
for key, val := range in {
if cast.ToString(fSlice[i]) == key {
fSlice[i] = val
}
}
}
}
f.Value = fSlice
} else if strings.Contains(reflect.TypeOf(f.Value).String(), "string") {
fSlice := f.Value.([]string)
for i := 0; i < len(fSlice); i++ {
if string(fSlice[i][0]) == "@" {
for key, val := range in {
if fSlice[i] == key {
fSlice[i] = val.(string)
}
}
}
}
f.Value = fSlice
}
return f
} else {
if string(cast.ToString(f.Value)[0]) == "@" {
for key, val := range in {
if cast.ToString(f.Value) == key {
f.Value = val
}
}
}
return f
}
}
return f
}
func (fb *FilterBuilder) BuildFilter(f *dbox.Filter) (interface{}, error) {
fm := ""
if f.Op == dbox.FilterOpEqual {
fm = fm + f.Field + " = " + StringValue(f.Value, "non")
} else if f.Op == dbox.FilterOpNoEqual {
fm = fm + f.Field + " <>" + StringValue(f.Value, "non")
} else if f.Op == dbox.FilterOpGt {
fm = fm + f.Field + " > " + StringValue(f.Value, "non")
} else if f.Op == dbox.FilterOpGte {
fm = fm + f.Field + " >= " + StringValue(f.Value, "non")
} else if f.Op == dbox.FilterOpLt {
fm = fm + f.Field + " < " + StringValue(f.Value, "non")
} else if f.Op == dbox.FilterOpLte {
fm = fm + f.Field + " <= " + StringValue(f.Value, "non")
} else if f.Op == dbox.FilterOpContains {
fm = CombineIn("LIKE ", f)
} else if f.Op == dbox.FilterOpEndWith {
fm = fm + f.Field + " LIKE '%" + cast.ToString(f.Value) + "'"
//fm = CombineIn("START ", f)
} else if f.Op == dbox.FilterOpStartWith {
fm = fm + f.Field + " LIKE '" + cast.ToString(f.Value) + "%'"
//fm = CombineIn("START ", f)
} else if f.Op == dbox.FilterOpIn {
fm = CombineIn("IN", f)
} else if f.Op == dbox.FilterOpNin {
fm = CombineIn("NOT IN", f)
} else if f.Op == dbox.FilterOpOr || f.Op == dbox.FilterOpAnd {
fs := f.Value.([]*dbox.Filter)
for _, ff := range fs {
// nilai ff : &{name $eq Roy}
bf, _ := fb.BuildFilter(ff)
// nilai bf : name = 'Roy'
if fm == "" {
fm = "(" + cast.ToString(bf)
} else {
if f.Op == dbox.FilterOpOr {
fm += " OR " + cast.ToString(bf)
} else {
fm += " AND " + cast.ToString(bf)
}
}
}
fm += ")"
} else {
//return nil, fmt.Errorf("Filter Op %s is not defined", f.Op)
}
return fm, nil
}
func (c *Connection) Connect() error {
ci := c.Info()
host := ci.Host
db := ci.Database
username := ci.UserName
pass := ci.Password
path := cast.ToString(ci.Settings["path"])
delimiter := cast.ToString(ci.Settings["delimiter"])
ConnectionString := host + "," + db + "," + username + "," + pass + "," + path + "," + delimiter
e := c.RdbmsConnect("hive", ConnectionString)
if e != nil {
return err.Error(packageName, modConnection, "Connect", e.Error())
}
return nil
}
func (q *Query) execQueryPartUpdate(dt toolkit.M, Cond QueryCondition) error {
if len(dt) == 0 {
return errorlib.Error(packageName, "Query", modQuery, "data to update is not found")
}
writer := q.Connection().(*Connection).writer
reader := q.Connection().(*Connection).reader
tempHeader := []string{}
for _, val := range q.Connection().(*Connection).headerColumn {
tempHeader = append(tempHeader, val.name)
}
for {
foundChange := false
recData := toolkit.M{}
dataTemp, e := reader.Read()
for i, val := range dataTemp {
recData.Set(tempHeader[i], val)
}
if len(Cond.Find) > 0 || (len(Cond.Find) == 0 && toolkit.IdField(dt) == "") {
foundChange = Cond.getCondition(recData)
}
// Check ID IF Condition Not Found
if nameid := toolkit.IdField(dt); nameid != "" && !foundChange {
if recData.Has(nameid) && dt[nameid] == recData[nameid] {
foundChange = true
}
}
if foundChange && len(dataTemp) > 0 {
for n, v := range tempHeader {
if dt.Has(v) {
dataTemp[n] = cast.ToString(dt[v])
}
}
}
if e == io.EOF {
if dataTemp != nil {
writer.Write(dataTemp)
writer.Flush()
}
break
} else if e != nil {
return errorlib.Error(packageName, modQuery, "Update", e.Error())
}
if dataTemp != nil {
writer.Write(dataTemp)
writer.Flush()
}
}
return nil
}
func (fb *FilterBuilder) BuildFilter(f *dbox.Filter) (interface{}, error) {
fm := ""
// vals := ""
//drivername := dbox.Connection
// drivername := new(Connection)
//drivername := fb.GetDriver()
// drivername := fb.Connection().(*Connection).Drivername
// fmt.Println("drivernamenya adalah : ", drivername)
if f.Op == dbox.FilterOpEqual {
fm = fm + f.Field + "= '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpNoEqual {
fm = fm + f.Field + "<>'" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpGt {
fm = fm + f.Field + " > '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpGte {
fm = fm + f.Field + " >= '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpLt {
fm = fm + f.Field + " < '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpLte {
fm = fm + f.Field + " <= '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpIn {
fm = CombineIn("IN", f)
} else if f.Op == dbox.FilterOpNin {
fm = CombineIn("NOT IN", f)
} else if f.Op == dbox.FilterOpContains {
fm = CombineIn("NOT IN", f)
} else if f.Op == dbox.FilterOpOr || f.Op == dbox.FilterOpAnd {
// bfs := []interface{}{}
fs := f.Value.([]*dbox.Filter)
for _, ff := range fs {
bf, _ := fb.BuildFilter(ff)
// if eb == nil {
// bfs = append(bfs, bf)
// }
if fm == "" {
fm = cast.ToString(bf)
} else {
if f.Op == dbox.FilterOpOr {
fm = fm + " OR " + cast.ToString(bf)
} else {
fm = fm + " AND " + cast.ToString(bf)
}
}
}
//fm.Set(f.Op, bfs)
} else {
//return nil, fmt.Errorf("Filter Op %s is not defined", f.Op)
}
return fm, nil
}
func ReadVariable(f *dbox.Filter, in toolkit.M) *dbox.Filter {
if (f.Op == "$and" || f.Op == "$or") &&
strings.Contains(reflect.TypeOf(f.Value).String(), "dbox.Filter") {
fs := f.Value.([]*dbox.Filter)
// nilai fs : [0xc082059590 0xc0820595c0]
for i, ff := range fs {
// nilai ff[0] : &{umur $gt @age} && ff[1] : &{name $eq @nama}
bf := ReadVariable(ff, in)
// nilai bf[0] : &{umur $gt 25} && bf[1] : &{name $eq Kane}
fs[i] = bf
}
f.Value = fs
return f
} else {
if reflect.TypeOf(f.Value).Kind() == reflect.Slice {
fSlice := f.Value.([]interface{})
// nilai fSlice : [@name1 @name2]
for i := 0; i < len(fSlice); i++ {
// nilai fSlice [i] : @name1
if string(cast.ToString(fSlice[i])[0]) == "@" {
for key, val := range in {
if strings.Replace(cast.ToString(fSlice[i]), "@", "", 1) == key {
fSlice[i] = val
}
}
}
}
f.Value = fSlice
return f
} else {
if string(cast.ToString(f.Value)[0]) == "@" {
for key, val := range in {
if strings.Replace(cast.ToString(f.Value), "@", "", 1) == key {
f.Value = val
}
}
}
return f
}
}
return f
}
func (fb *FilterBuilder) BuildFilter(f *dbox.Filter) (interface{}, error) {
fm := ""
if f.Op == dbox.FilterOpEqual {
fm = fm + f.Field + " = " + rdbms.StringValue(f.Value, "oracle") + ""
} else if f.Op == dbox.FilterOpNoEqual {
fm = fm + f.Field + " <> " + rdbms.StringValue(f.Value, "oracle") + ""
} else if f.Op == dbox.FilterOpGt {
fm = fm + f.Field + " > " + rdbms.StringValue(f.Value, "oracle") + ""
} else if f.Op == dbox.FilterOpGte {
fm = fm + f.Field + " >= " + rdbms.StringValue(f.Value, "oracle") + ""
} else if f.Op == dbox.FilterOpLt {
fm = fm + f.Field + " < " + rdbms.StringValue(f.Value, "oracle") + ""
} else if f.Op == dbox.FilterOpLte {
fm = fm + f.Field + " <= " + rdbms.StringValue(f.Value, "oracle") + ""
} else if f.Op == dbox.FilterOpIn {
fm = CombineIn("IN", f)
} else if f.Op == dbox.FilterOpNin {
fm = CombineIn("NOT IN", f)
} else if f.Op == dbox.FilterOpContains {
fm = CombineIn("NOT IN", f)
} else if f.Op == dbox.FilterOpOr || f.Op == dbox.FilterOpAnd {
// f
fs := f.Value.([]*dbox.Filter)
for _, ff := range fs {
bf, _ := fb.BuildFilter(ff)
if fm == "" {
fm = "(" + cast.ToString(bf)
} else {
if f.Op == dbox.FilterOpOr {
fm += " OR " + cast.ToString(bf)
} else {
fm += " AND " + cast.ToString(bf)
}
}
}
fm += ")"
} else {
//return nil, fmt.Errorf("Filter Op %s is not defined", f.Op)
}
return fm, nil
}
func (q *Query) execQueryPartUpdate(dt toolkit.M) error {
var e error
e = q.startWriteMode()
if e != nil {
return err.Error(packageName, modQuery, "Exec-Update: ", e.Error())
}
writer := q.writer
reader := q.reader
tempHeader := []string{}
for _, val := range q.headerColumn {
tempHeader = append(tempHeader, val.name)
}
var i int = 0
for {
i += 1
dataTemp, e := reader.Read()
if toolkit.HasMember(q.indexes, i) && len(dataTemp) > 0 {
for n, v := range tempHeader {
if dt.Has(v) {
dataTemp[n] = cast.ToString(dt[v])
}
}
}
if e == io.EOF {
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
break
} else if e != nil {
_ = q.endWriteMode()
return err.Error(packageName, modQuery, "Exec-Update:", e.Error())
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
}
q.execOpr = true
e = q.endWriteMode()
if e != nil {
return err.Error(packageName, modQuery, "Exec-Update: ", e.Error())
}
return nil
}
func (fb *FilterBuilder) BuildFilter(f *dbox.Filter) (interface{}, error) {
fm := ""
// vals := ""
if f.Op == dbox.FilterOpEqual {
fm = fm + f.Field + "= '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpNoEqual {
fm = fm + f.Field + "<>'" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpGt {
fm = fm + f.Field + " > '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpGte {
fm = fm + f.Field + " >= '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpLt {
fm = fm + f.Field + " < '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpLte {
fm = fm + f.Field + " <= '" + cast.ToString(f.Value) + "'"
} else if f.Op == dbox.FilterOpOr || f.Op == dbox.FilterOpAnd {
// bfs := []interface{}{}
fs := f.Value.([]*dbox.Filter)
for _, ff := range fs {
bf, _ := fb.BuildFilter(ff)
// if eb == nil {
// bfs = append(bfs, bf)
// }
if fm == "" {
fm = cast.ToString(bf)
} else {
if f.Op == dbox.FilterOpOr {
fm = fm + " OR " + cast.ToString(bf)
} else {
fm = fm + " AND " + cast.ToString(bf)
}
}
}
//fm.Set(f.Op, bfs)
} else {
//return nil, fmt.Errorf("Filter Op %s is not defined", f.Op)
}
return fm, nil
}
func (q *Query) execQueryPartInsert(dt toolkit.M) error {
var e error
e = q.startWriteMode()
if e != nil {
return err.Error(packageName, modQuery, "Exec-Insert: ", e.Error())
}
if q.newheader {
q.setNewHeader(dt)
}
writer := q.writer
// reader := q.reader
dataTemp := []string{}
for _, v := range q.headerColumn {
if dt.Has(v.name) {
dataTemp = append(dataTemp, cast.ToString(dt[v.name]))
} else {
dataTemp = append(dataTemp, "")
}
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
q.execOpr = true
e = q.endWriteMode()
if e != nil {
return err.Error(packageName, modQuery, "Exec-Insert: ", e.Error())
}
return 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) setConfigParam() {
ci := q.Connection().(*Connection).Info()
q.newfile = ci.Settings.Get("newfile", false).(bool)
q.isUseHeader = ci.Settings.Get("useheader", false).(bool)
// set header from reader =============== }
dateformat := ci.Settings.Get("dateformat", "").(string)
q.headerColumn = make([]headerstruct, 0, 0)
tdread, e := q.reader.Read()
for i, v := range tdread {
ts := headerstruct{}
ts.name = string(i)
ts.dataType = ""
if q.isUseHeader {
ts.name = v
}
q.headerColumn = append(q.headerColumn, ts)
}
if q.isUseHeader && e == nil {
tdread, e = q.reader.Read()
}
isCheckType := true
ix := 0
for isCheckType && e != io.EOF {
isCheckType = false
for i, v := range tdread {
if v != "" {
matchNumber := false
matchFloat := false
matchDate := false
formatDate := "((^(0[0-9]|[0-9]|(1|2)[0-9]|3[0-1])(\\.|\\/|-)(0[0-9]|[0-9]|1[0-2])(\\.|\\/|-)[\\d]{4}$)|(^[\\d]{4}(\\.|\\/|-)(0[0-9]|[0-9]|1[0-2])(\\.|\\/|-)(0[0-9]|[0-9]|(1|2)[0-9]|3[0-1])$))"
matchDate, _ = regexp.MatchString(formatDate, v)
if !matchDate && dateformat != "" {
d := cast.String2Date(v, dateformat)
if d.Year() > 1 {
matchDate = true
}
}
x := strings.Index(v, ".")
if x > 0 {
matchFloat = true
v = strings.Replace(v, ".", "", 1)
}
matchNumber, _ = regexp.MatchString("^\\d+$", v)
q.headerColumn[i].dataType = "string"
if matchNumber {
q.headerColumn[i].dataType = "int"
if matchFloat {
q.headerColumn[i].dataType = "float"
}
}
if matchDate {
q.headerColumn[i].dataType = "date"
}
}
}
for _, v := range q.headerColumn {
if v.dataType == "" {
isCheckType = true
}
}
if isCheckType {
tdread, e = q.reader.Read()
}
ix++
if ix > 10 {
break
}
}
for _, v := range q.headerColumn {
if v.dataType == "" {
v.dataType = "string"
}
}
_ = q.resetReader()
// ===================== }
if ci.Settings.Has("mapheader") {
smh := ci.Settings["mapheader"].([]toolkit.M)
for i, val := range smh {
ts := headerstruct{}
for name, dt := range val {
ts.name = name
ts.dataType = cast.ToString(dt)
}
if (i + 1) < len(q.headerColumn) {
q.headerColumn[i] = ts
} else {
q.headerColumn = append(q.headerColumn, ts)
}
}
}
}
func (q *Query) execQueryPartInsert(dt toolkit.M) error {
if len(dt) == 0 {
return errorlib.Error(packageName, "Query", modQuery, "data to insert is not found")
}
writer := q.Connection().(*Connection).writer
reader := q.Connection().(*Connection).reader
dataTemp := []string{}
if q.Connection().(*Connection).setNewHeader {
q.Connection().(*Connection).SetHeaderToolkitM(dt)
q.Connection().(*Connection).setNewHeader = false
for _, v := range q.Connection().(*Connection).headerColumn {
dataTemp = append(dataTemp, v.name)
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
dataTemp = []string{}
}
// Check ID Before Insert
if nameid := toolkit.IdField(dt); nameid != "" {
var colsid int
for i, val := range q.Connection().(*Connection).headerColumn {
if val.name == nameid {
colsid = i
}
}
for {
dataTempSearch, e := reader.Read()
for i, val := range dataTempSearch {
if i == colsid && val == dt[nameid] {
return errorlib.Error(packageName, modQuery, "Insert", "Unique id is found")
}
}
if e == io.EOF {
break
} else if e != nil {
return errorlib.Error(packageName, modQuery, "Insert", e.Error())
}
}
}
for _, v := range q.Connection().(*Connection).headerColumn {
if dt.Has(v.name) {
dataTemp = append(dataTemp, cast.ToString(dt[v.name]))
} else {
dataTemp = append(dataTemp, "")
}
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
return nil
}
func (q *Query) insertBulk(parm toolkit.M) error {
var e error
if parm == nil {
parm = toolkit.M{}
}
driverName := q.GetDriverDB()
// driverName = "oracle"
tablename := ""
data := parm.Get("data")
var attributes string
var dataM toolkit.M
var dataMs []toolkit.M
if toolkit.IsSlice(data) {
e = toolkit.Unjson(toolkit.Jsonify(data), &dataMs)
if e != nil {
return 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 errorlib.Error(packageName, modQuery, "Exec: data extraction", "Data encoding error: "+e.Error())
}
}
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 := ""
_, hasInsert := parts[dbox.QueryPartInsert]
if hasInsert {
commandType = dbox.QueryPartInsert
} else {
_, e = q.ExecOut(parm)
return e
// return errorlib.Error(packageName, "Query", modQuery+".InsertBulk", "Invalid Operation")
}
fromParts, hasFrom := parts[dbox.QueryPartFrom]
if !hasFrom {
return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
}
tablename = fromParts.([]*dbox.QueryPart)[0].Value.(string)
session := q.Session()
attributeList := extractFields(dataMs[0])
var datas []string
for _, dataVal := range dataMs {
var values string
tmp := toolkit.M{}
for _, attr := range attributeList {
tmp.Set(attr, dataVal.Get(attr))
}
values = extractDataBulk(attributeList, tmp, driverName)
// toolkit.Printf("test: \n %v \n------\n %v \n------\n %v \n------\n %v \n", attributeList, dataVal, tmp, values)
datas = append(datas, values)
}
attributes = "(" + strings.Join(attributeList, ",") + ")"
if attributes != "" && nil != datas {
var statement string
if driverName == "hive" {
/*statement = "INSERT INTO " + tablename + " VALUES " + values
e = sessionHive.Exec(statement, nil)*/
return errorlib.Error(packageName, modQuery+".Exec", commandType,
"Not Implemented Yet for HIVE")
} else {
statement = fmt.Sprintf("INSERT INTO "+tablename+" "+attributes+" VALUES %s", strings.Join(datas, ","))
_, 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")
}
return nil
}
func (c *Cursor) Fetch(m interface{}, n int, closeWhenDone bool) error {
tableData := []toolkit.M{}
// var e error
rows, e := c.session.Query(c.QueryString)
var valueType reflect.Type
if n == 1 {
valueType = reflect.TypeOf(m).Elem()
} else {
valueType = reflect.TypeOf(m).Elem().Elem()
}
if e != nil {
return e
}
defer rows.Close()
columns, e := rows.Columns()
if e != nil {
return e
}
count := len(columns)
values := make([]interface{}, count)
valuePtrs := make([]interface{}, count)
for rows.Next() {
for i := 0; i < count; i++ {
valuePtrs[i] = &values[i]
}
rows.Scan(valuePtrs...)
entry := toolkit.M{}
for i, col := range columns {
var v interface{}
val := values[i]
// b, ok := val.([]byte)
// // toolkit.Println("i : ", i, " :col: ", col, " :val: ", val, " :b : ", b, " :type data : ", toolkit.Value(val))
// var out interface{}
// e = toolkit.Unjson(b, &out)
// // toolkit.Println("i : ", i, "b : ", b, " :out: ", v, " :: error : ", e)
// if e != nil {
// ok = false
// }
// if ok {
// v = out
// toolkit.Println("error OK :: ", ok, " :v :", v)
// } else {
// toolkit.Println("error OK :: ", ok, " :b :", b)
// v = string(b)
// }
v = val
entry.Set(strings.ToLower(col), v)
}
if valueType.Kind() == reflect.Struct {
for i := 0; i < valueType.NumField(); i++ {
namaField := strings.ToLower(valueType.Field(i).Name)
dataType := strings.ToLower(valueType.Field(i).Type.String())
if entry.Has(namaField) {
if strings.Contains(dataType, "int") {
entry.Set(namaField,
cast.ToInt(entry[namaField], cast.RoundingAuto))
} else if strings.Contains(dataType, "time.time") {
entry.Set(namaField,
cast.String2Date(cast.ToString(entry[namaField]), "2006-01-02 15:04:05"))
}
}
}
}
tableData = append(tableData, entry)
}
// toolkit.Println("... ::: ", tableData)
maxIndex := toolkit.SliceLen(tableData)
var e2 error
if e2 != nil {
return e2
}
end := c.start + n
if end > maxIndex || n == 0 {
end = maxIndex
}
if c.start >= maxIndex {
e2 = errors.New("No more data to fetched!")
} else {
e2 = toolkit.Serde(tableData[c.start:end], m, "json")
}
c.start = end
return e2
}
func (q *Query) Exec(parm toolkit.M) error {
var e error
q.save = false
// useHeader := q.Connection().Info().Settings.Get("useheader", false).(bool)
if parm == nil {
parm = toolkit.M{}
}
data := parm.Get("data", nil)
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 {
// return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
// }
// tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)
// var where interface{}
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
q.save = true
}
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 errorlib.Error(packageName, modQuery, "Cursor", e.Error())
}
}
//Check setNewHeader First
if q.Connection().(*Connection).setNewHeader && commandType != dbox.QueryPartInsert {
q.Connection().(*Connection).Close()
filename := q.Connection().(*Connection).Info().Host
os.Remove(filename)
return errorlib.Error(packageName, "Query", modQuery, "Only Insert Permited")
}
q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Append
if hasDelete || hasUpdate {
q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Create
}
q.Connection().(*Connection).ExecOpr = false
if !q.Connection().(*Connection).setNewHeader && (commandType != dbox.QueryPartSave || (commandType == dbox.QueryPartSave && q.Connection().(*Connection).writer == nil)) {
e = q.Connection().(*Connection).StartSessionWrite()
}
if e != nil {
return errorlib.Error(packageName, "Query", modQuery, e.Error())
}
writer := q.Connection().(*Connection).writer
reader := q.Connection().(*Connection).reader
var execCond QueryCondition
execCond.Find, _ = toolkit.ToM(where)
switch commandType {
case dbox.QueryPartInsert, dbox.QueryPartSave:
var dataTemp []string
dataMformat, _ := toolkit.ToM(data)
// fmt.Println("LINE338:", q.Connection().(*Connection).setNewHeader)
if q.Connection().(*Connection).setNewHeader {
q.Connection().(*Connection).SetHeaderToolkitM(dataMformat)
q.Connection().(*Connection).setNewHeader = false
for _, v := range q.Connection().(*Connection).headerColumn {
dataTemp = append(dataTemp, v.name)
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
// fmt.Println("LINE342:", q.Connection().(*Connection).headerColumn)
dataTemp = []string{}
}
for _, v := range q.Connection().(*Connection).headerColumn {
if dataMformat.Has(v.name) {
dataTemp = append(dataTemp, cast.ToString(dataMformat[v.name]))
} else {
dataTemp = append(dataTemp, "")
}
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
case dbox.QueryPartDelete:
var tempHeader []string
for _, val := range q.Connection().(*Connection).headerColumn {
tempHeader = append(tempHeader, val.name)
}
for {
foundDelete := true
recData := toolkit.M{}
dataTemp, e := reader.Read()
for i, val := range dataTemp {
recData.Set(tempHeader[i], val)
}
foundDelete = execCond.getCondition(recData)
if e == io.EOF {
if !foundDelete && dataTemp != nil {
writer.Write(dataTemp)
writer.Flush()
}
break
} else if e != nil {
return errorlib.Error(packageName, modQuery, "Delete", e.Error())
}
if !foundDelete && dataTemp != nil {
writer.Write(dataTemp)
writer.Flush()
}
}
case dbox.QueryPartUpdate:
var tempHeader []string
if data == nil {
break
}
dataMformat, _ := toolkit.ToM(data)
for _, val := range q.Connection().(*Connection).headerColumn {
tempHeader = append(tempHeader, val.name)
}
for {
foundChange := false
recData := toolkit.M{}
dataTemp, e := reader.Read()
for i, val := range dataTemp {
recData.Set(tempHeader[i], val)
}
foundChange = execCond.getCondition(recData)
if foundChange && len(dataTemp) > 0 {
for n, v := range tempHeader {
if dataMformat.Has(v) {
dataTemp[n] = cast.ToString(dataMformat[v])
}
}
}
if e == io.EOF {
if dataTemp != nil {
writer.Write(dataTemp)
writer.Flush()
}
break
} else if e != nil {
return errorlib.Error(packageName, modQuery, "Update", e.Error())
}
if dataTemp != nil {
writer.Write(dataTemp)
writer.Flush()
}
}
}
q.Connection().(*Connection).ExecOpr = true
if commandType != dbox.QueryPartSave {
e = q.Connection().(*Connection).EndSessionWrite()
}
return 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
}
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) Exec(parm toolkit.M) error {
var e error
if parm == nil {
parm = toolkit.M{}
}
// fmt.Println("Parameter Exec : ", parm)
dbname := q.Connection().Info().Database
tablename := ""
if parm == nil {
parm = toolkit.M{}
}
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 string
if data != nil {
var reflectValue = reflect.ValueOf(data)
if reflectValue.Kind() == reflect.Ptr {
reflectValue = reflectValue.Elem()
}
var reflectType = reflectValue.Type()
for i := 0; i < reflectValue.NumField(); i++ {
namaField := reflectType.Field(i).Name
dataValues := reflectValue.Field(i).Interface()
stringValues := StringValue(dataValues, q.GetDriverDB())
if i == 0 {
attributes = "(" + namaField
values = "(" + stringValues
setUpdate = namaField + " = " + stringValues
} else {
attributes += " , " + namaField
values += " , " + stringValues
setUpdate += " , " + namaField + " = " + stringValues
}
}
attributes += ")"
values += ")"
}
//=================================END OF EXTRACTION=======================================
temp := ""
parts := crowd.From(q.Parts()).Group(func(x interface{}) interface{} {
qp := x.(*dbox.QueryPart)
temp = toolkit.JsonString(qp)
return qp.PartType
}, nil).Data
fromParts, hasFrom := parts[dbox.QueryPartFrom]
if !hasFrom {
return errorlib.Error(packageName, "Query", modQuery, "Invalid table name")
}
tablename = fromParts.([]interface{})[0].(*dbox.QueryPart).Value.(string)
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 {
} 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
}
if data == nil {
//---
multi = true
} else {
if where == nil {
id := toolkit.Id(data)
if id != nil {
where = (toolkit.M{}).Set("_id", id)
}
} else {
multi = true
}
}
session := q.Session()
if dbname != "" && tablename != "" && multi == true {
}
if commandType == dbox.QueryPartInsert {
} else if commandType == dbox.QueryPartUpdate {
statement := "UPDATE " + tablename + " SET " + setUpdate + " WHERE " + cast.ToString(where)
fmt.Println("Update Statement : ", statement)
_, e = session.Exec(statement)
if e != nil {
fmt.Println(e.Error())
}
} else if commandType == dbox.QueryPartDelete {
if where == nil {
statement := "DELETE FROM " + tablename
fmt.Println(statement)
_, e = session.Exec(statement)
if e != nil {
fmt.Println(e.Error())
}
} else {
statement := "DELETE FROM " + tablename + " where " + cast.ToString(where)
fmt.Println(statement)
_, e = session.Exec(statement)
if e != nil {
fmt.Println(e.Error())
}
}
} else if commandType == dbox.QueryPartSave {
statement := "INSERT INTO " + tablename + " " + attributes + " VALUES " + values
fmt.Println("Insert Statement : ", statement)
_, e = session.Exec(statement)
if e != nil {
fmt.Println(e.Error())
}
}
if e != nil {
return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
}
return 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 (c *Connection) SetHeaderData(useHeader bool) {
ci := c.Info()
var headerrows int
headerrows = 5
// startdatarows := 0
if ci.Settings.Has("headerrows") {
// headerrows = cast.ToInt(ci.Settings["headerrows"])
}
// Dummy :
var tempstruct []headerstruct
n := 1
for i, _ := range c.reader.Sheet["HIST"].Rows[headerrows].Cells {
_ = i
ts := headerstruct{}
ts.name = cast.ToString(n)
ts.dataType = "string"
tempstruct = append(tempstruct, ts)
n += 1
}
c.headerColumn = tempstruct
// for i, v := range tempData {
// ts := headerstruct{}
// ts.name = string(i)
// ts.dataType = "string"
// if useHeader {
// ts.name = v
// }
// tempstruct = append(tempstruct, ts)
// }
// if useHeader && e != io.EOF {
// tempData, e = c.reader.Read()
// }
// isCheckType := true
// ix := 0
// for isCheckType && e != io.EOF {
// ix += 1
// isCheckType = false
// for i, v := range tempData {
// if v != "" {
// matchNumber := false
// matchFloat := false
// matchDate := false
// formatDate := "((^(0[0-9]|[0-9]|(1|2)[0-9]|3[0-1])(\\.|\\/|-)(0[0-9]|[0-9]|1[0-2])(\\.|\\/|-)[\\d]{4}$)|(^[\\d]{4}(\\.|\\/|-)(0[0-9]|[0-9]|1[0-2])(\\.|\\/|-)(0[0-9]|[0-9]|(1|2)[0-9]|3[0-1])$))"
// matchDate, _ = regexp.MatchString(formatDate, v)
// if !matchDate && dateformat != "" {
// d := cast.String2Date(v, dateformat)
// if d.Year() > 1 {
// matchDate = true
// }
// }
// x := strings.Index(v, ".")
// if x > 0 {
// matchFloat = true
// v = strings.Replace(v, ".", "", 1)
// }
// matchNumber, _ = regexp.MatchString("^\\d+$", v)
// tempstruct[i].dataType = "string"
// if matchNumber {
// tempstruct[i].dataType = "int"
// if matchFloat {
// tempstruct[i].dataType = "float"
// }
// }
// if matchDate {
// tempstruct[i].dataType = "date"
// }
// }
// }
// for _, v := range tempstruct {
// if v.dataType == "" {
// isCheckType = true
// }
// }
// if isCheckType {
// tempData, _ = c.reader.Read()
// }
// // fmt.Println(ix, "-", isCheckType)
// // fmt.Println(tempstruct)
// if ix > 5 {
// break
// }
// }
// c.headerColumn = tempstruct
// c.file.Close()
// c.file, _ = os.Open(ci.Host)
// c.reader = csv.NewReader(c.file)
// c.SetReaderParam()
// if useHeader {
// tempData, _ = c.reader.Read()
// }
}
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
}
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
}
func (c *Cursor) Fetch(m interface{}, n int, closeWhenDone bool) error {
fmt.Println(c.QueryString)
rows, e := c.session.Query(c.QueryString)
var valueType reflect.Type
if n == 1 {
valueType = reflect.TypeOf(m).Elem()
} else {
valueType = reflect.TypeOf(m).Elem().Elem()
}
if e != nil {
return e
}
defer rows.Close()
columns, e := rows.Columns()
if e != nil {
return e
}
count := len(columns)
tableData := []toolkit.M{}
values := make([]interface{}, count)
valuePtrs := make([]interface{}, count)
// valueint := values
for rows.Next() {
for i := 0; i < count; i++ {
valuePtrs[i] = &values[i]
}
// rows.Scan(valuePtrs...)
rows.Scan(valuePtrs...)
entry := toolkit.M{}
for i, col := range columns {
var v interface{}
val := values[i]
// fmt.Println("Nilai val : ", val)
b, ok := val.([]byte)
if ok {
v = string(b)
} else {
v = val
}
entry.Set(strings.ToLower(col), v)
// entry.Set(col, values[i])
// e = toolkit.DecodeByte(val.([]byte), v)
// toolkit.FromBytes(toolkit.ToBytes(val, ""), "", v)
// entry.Set(col, v)
}
if valueType.Kind() == reflect.Struct {
for i := 0; i < valueType.NumField(); i++ {
namaField := strings.ToLower(valueType.Field(i).Name)
dataType := strings.ToLower(valueType.Field(i).Type.String())
if entry.Has(namaField) {
fmt.Println("isi entry : ", entry[namaField], dataType)
if strings.Contains(dataType, "int") {
entry.Set(namaField,
cast.ToInt(entry[namaField], cast.RoundingAuto))
} else if strings.Contains(dataType, "time.time") {
entry.Set(namaField,
cast.String2Date(cast.ToString(entry[namaField]), "2006-01-02 15:04:05"))
}
}
}
}
tableData = append(tableData, entry)
}
fmt.Println("Nilai table data : ", tableData)
if e != nil {
return e
}
if n == 0 {
// *m.(*[]map[string]interface{}) = tableData
// toolkit.Unjson(toolkit.Jsonify(tableData), m)
e = toolkit.Serde(tableData, m, "json")
fmt.Println("Nilai Model : ", m)
} else {
end := c.start + n
if end > len(tableData) {
e = errors.New("index out of range")
} else {
// *m.(*[]map[string]interface{}) = tableData[0:n]
//toolkit.Unjson(toolkit.Jsonify(tableData[0:n]), m)
e = toolkit.Serde(tableData[0:n], m, "json")
}
}
return e
}
func (q *Query) execQueryPartSave(dt toolkit.M) error {
if len(dt) == 0 {
return errorlib.Error(packageName, modQuery, "save", "data to insert is not found")
}
writer := q.Connection().(*Connection).writer
reader := q.Connection().(*Connection).reader
tempHeader := []string{}
for _, val := range q.Connection().(*Connection).headerColumn {
tempHeader = append(tempHeader, val.name)
}
// Check ID Before Insert
checkidfound := false
if nameid := toolkit.IdField(dt); nameid != "" {
q.updatessave = true
var colsid int
for i, val := range q.Connection().(*Connection).headerColumn {
if val.name == nameid {
colsid = i
}
}
for {
dataTempSearch, e := reader.Read()
for i, val := range dataTempSearch {
if i == colsid && val == dt[nameid] {
checkidfound = true
break
}
}
if e == io.EOF {
break
} else if e != nil {
return errorlib.Error(packageName, modQuery, "Save", e.Error())
}
}
}
if checkidfound {
e := q.Connection().(*Connection).EndSessionWrite()
if e != nil {
return errorlib.Error(packageName, modQuery, "Save", e.Error())
}
q.Connection().(*Connection).TypeOpenFile = TypeOpenFile_Create
e = q.Connection().(*Connection).StartSessionWrite()
if e != nil {
return errorlib.Error(packageName, modQuery, "Save", e.Error())
}
e = q.execQueryPartUpdate(dt, QueryCondition{})
if e != nil {
return errorlib.Error(packageName, modQuery, "Save", e.Error())
}
// time.Sleep(1000 * time.Millisecond)
} else {
//Change to Do Insert
dataTemp := []string{}
for _, v := range q.Connection().(*Connection).headerColumn {
if dt.Has(v.name) {
dataTemp = append(dataTemp, cast.ToString(dt[v.name]))
} else {
dataTemp = append(dataTemp, "")
}
}
if len(dataTemp) > 0 {
writer.Write(dataTemp)
writer.Flush()
}
}
return nil
}
