Exemple #1
0
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
}
Exemple #2
0
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
}
Exemple #3
0
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
}
Exemple #5
0
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
}
Exemple #7
0
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
}
Exemple #9
0
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
}
Exemple #10
0
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
}
Exemple #11
0
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
}
Exemple #12
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

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

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

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

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

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

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

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

		aggrParts, hasAggr := parts[dbox.QueryPartAggr]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

						} else {
							paramstring = "("
						}

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

						}
					}
				}

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

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

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

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

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

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

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

						} else {
							paramstring = "("
						}

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

						}
					}

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

			} else if hasParam && !hasOrder {

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

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

			ProcStatement = "SELECT " + spName + paramstring
		}

		cursor.(*Cursor).QueryString = ProcStatement

		// fmt.Println("Proc Statement : ", ProcStatement)
	} else if hasFreeQuery {
		querySyntax := freeQueryParts.([]*dbox.QueryPart)[0].Value.(interface{})
		syntax := querySyntax.(toolkit.M)["syntax"].(string)
		cursor.(*Cursor).QueryString = syntax
	}
	return cursor, nil
}
Exemple #13
0
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)
			}
		}
	}
}
Exemple #14
0
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
}
Exemple #15
0
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
}
Exemple #16
0
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
}
Exemple #17
0
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
}
Exemple #18
0
func (q *Query) Cursor(in toolkit.M) (dbox.ICursor, error) {
	var e error
	/*
		if q.Parts == nil {
			return nil, errorlib.Error(packageName, modQuery,
				"Cursor", fmt.Sprintf("No Query Parts"))
		}
	*/

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

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

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

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

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

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

		aggrParts, hasAggr := parts[dbox.QueryPartAggr]

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

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

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

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

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

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

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

		}

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

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

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

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

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

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

			ProcStatement = "EXECUTE " + spName + paramstring

		}

		cursor.(*Cursor).QueryString = ProcStatement

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

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

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

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

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

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

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

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

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

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

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

	}
	return cursor, nil
}
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
}
Exemple #22
0
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()
	// }
}
Exemple #23
0
func (q *Query) Exec(parm toolkit.M) error {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

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

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

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

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

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

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

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

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

	temp := ""
	quyerParts := q.Parts()
	c := crowd.From(&quyerParts)
	groupParts := c.Group(func(x interface{}) interface{} {
		qp := x.(*dbox.QueryPart)
		temp = toolkit.JsonString(qp)
		return qp.PartType
	}, nil).Exec()
	parts := map[interface{}]interface{}{}
	if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
		for _, kv := range groupParts.Result.Data().([]crowd.KV) {
			parts[kv.Key] = kv.Value
		}
	}

	fromParts, hasFrom := parts[dbox.QueryPartFrom]
	if !hasFrom {

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

	var where interface{}
	whereParts, hasWhere := parts[dbox.QueryPartWhere]
	if hasWhere {
		fb := q.Connection().Fb()
		for _, p := range whereParts.([]*dbox.QueryPart) {
			fs := p.Value.([]*dbox.Filter)
			for _, f := range fs {
				fb.AddFilter(f)
			}
		}

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

		} else {

		}

	}
	commandType := ""
	multi := false

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

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

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

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

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

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

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

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

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

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

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

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

	}
	if e != nil {
		return errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
	}
	return nil
}
Exemple #24
0
func (q *Query) ExecOut(parm toolkit.M) (int64, error) {
	var e error
	if parm == nil {
		parm = toolkit.M{}
	}

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

	var attributes string
	var values string
	var setUpdate string

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

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

	for _, dataVal := range dataMs {
		temp := ""
		quyerParts := q.Parts()
		c := crowd.From(&quyerParts)
		groupParts := c.Group(func(x interface{}) interface{} {
			qp := x.(*dbox.QueryPart)
			temp = toolkit.JsonString(qp)
			return qp.PartType
		}, nil).Exec()
		parts := map[interface{}]interface{}{}
		if len(groupParts.Result.Data().([]crowd.KV)) > 0 {
			for _, kv := range groupParts.Result.Data().([]crowd.KV) {
				parts[kv.Key] = kv.Value
			}
		}

		commandType := ""

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

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

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

		}

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

		var where interface{}
		whereParts, hasWhere := parts[dbox.QueryPartWhere]
		if hasWhere {
			fb := q.Connection().Fb()
			for _, p := range whereParts.([]*dbox.QueryPart) {
				fs := p.Value.([]*dbox.Filter)
				for _, f := range fs {
					fb.AddFilter(f)
				}
			}

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

			} else {

			}

		}

		var id string
		var idVal interface{}

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

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

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

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

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

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

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

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

		}
		if e != nil {
			return returnId, errorlib.Error(packageName, modQuery+".Exec", commandType, e.Error())
		}
	}
	return returnId, nil
}
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
}
Exemple #26
0
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
}