func CreateBlank(id string) {
db := connect.DBConnect()
defer connect.DBClose(db)
base := new(ModelManager)
model := base.Users()
n := len(model.UserColumns)
query := "CREATE TABLE IF NOT EXISTS blank_" + id + "("
for i := 0; i < n; i++ {
query += model.UserColumns[i] + " " + model.Fields[model.UserColumns[i]].Type + ", "
}
query = query[0:len(query)-2] + ");"
fmt.Println("[DBCreateBlank]: Create table", query)
_, err := db.Exec(query)
utils.HandleErr("[Connect.DBCreateBlank]: Exec", err)
contests := base.Contests()
name := contests.Select(map[string]interface{}{"id": id}, "name")
blanks := base.Blanks()
blanks.Insert(
blanks.Columns[1:],
[]interface{}{
name[0].(map[string]interface{})["name"].(string),
id,
"{" + strings.Join(model.UserColumns[1:], ",") + "}",
"{" + strings.Join(model.UserColNames[1:], ",") + "}",
"{" + strings.Join(model.UserTypes[1:], ",") + "}"})
}
func (this Entity) Insert(fields []string, params []interface{}) {
db := connect.DBConnect()
query := connect.DBInsert(this.TableName, fields)
stmt, err := db.Prepare(query)
utils.HandleErr("[Entity.Insert] Prepare: ", err)
defer connect.DBClose(db, stmt)
_, err = stmt.Exec(params...)
utils.HandleErr("[Entity.Insert] Exec: ", err)
}
func IsExist(login string) (bool, string, string, string) {
db := connect.DBConnect()
var id, hash, salt string
query := connect.DBSelect("users", []string{"login"}, "id", "password", "salt")
stmt, err := db.Prepare(query)
utils.HandleErr("[IsExist] Prepare: ", err)
defer connect.DBClose(db, stmt)
err = stmt.QueryRow(login).Scan(&id, &hash, &salt)
return err != sql.ErrNoRows, id, hash, salt
}
func (this Entity) Update(fields []string, params []interface{}, where string) {
db := connect.DBConnect()
query := connect.DBUpdate(this.TableName, fields, where)
fmt.Println("Update: ", query)
stmt, err := db.Prepare(query)
utils.HandleErr("[Entity.Update] Prepare: ", err)
defer connect.DBClose(db, stmt)
_, err = stmt.Exec(params...)
utils.HandleErr("[Entity.Update] Exec: ", err)
}
func (this *Handler) Index() {
var (
request interface{}
response = ""
)
this.Response.Header().Set("Access-Control-Allow-Origin", "*")
this.Response.Header().Set("Access-Control-Allow-Methods", "GET, POST, OPTIONS")
this.Response.Header().Set("Content-type", "application/json")
decoder := json.NewDecoder(this.Request.Body)
err := decoder.Decode(&request)
utils.HandleErr("[Handler] Decode :", err)
data := request.(map[string]interface{})
switch data["action"] {
case "register":
login, password := data["login"].(string), data["password"].(string)
response = this.HandleRegister(login, password)
fmt.Fprintf(this.Response, "%s", response)
break
case "login":
login, password := data["login"].(string), data["password"].(string)
response = this.HandleLogin(login, password)
fmt.Fprintf(this.Response, "%s", response)
break
case "logout":
response = this.HandleLogout()
fmt.Fprintf(this.Response, "%s", response)
break
case "getId":
sess := this.Session.SessionStart(this.Response, this.Request)
ans := map[string]interface{}{"id": sess.Get("id")}
res, err := json.Marshal(ans)
utils.HandleErr("[Handle.Index] json.Marshal: ", err)
fmt.Fprintf(this.Response, "%s", string(res))
break
case "select":
tableName := data["table"].(string)
fields := data["fields"].([]interface{})
base := new(models.ModelManager)
var model models.Entity
var length int
switch tableName {
case "Users":
length = len(base.Users().UserColumns)
model = base.Users().Entity
break
case "Contests":
length = len(base.Contests().Columns)
model = base.Contests().Entity
break
}
p := make([]string, length)
j := 0
for i, v := range fields {
if v != nil {
p[i] = v.(string)
j++
}
}
pp := make([]string, j)
copy(pp[:], p[:j])
answer := model.Select(nil, pp...)
response, err := json.Marshal(answer)
utils.HandleErr("[HandleLogin] json.Marshal: ", err)
fmt.Fprintf(this.Response, "%s", response)
case "update":
tableName := data["table"].(string)
base := new(models.ModelManager)
var model models.Entity
var length int
switch tableName {
case "Users":
length = len(base.Users().UserColumns)
model = base.Users().Entity
break
case "Contests":
length = len(base.Contests().Columns)
model = base.Contests().Entity
break
}
d := data["data"].(map[string](interface{}))
fields := utils.ArrayInterfaceToString(d["fields"].([]interface{}), length-1)
values := d["userData"].([]interface{})
model.Update(fields, values, fmt.Sprintf("id=%s", data["id"]))
response, err := json.Marshal(map[string]interface{}{"result": "ok"})
utils.HandleErr("[HandleLogin] json.Marshal: ", err)
fmt.Fprintf(this.Response, "%s", response)
break
case "editBlanks":
id := data["id"].(string)
inf := data["data"].([]interface{})
columns := connect.DBGetColumnNames("blank_" + id)
fmt.Println(columns)
used := make(map[string][]int, len(columns))
for i, v := range columns {
used[v] = make([]int, 2)
used[v][0] = 0
used[v][1] = i
}
used["id"][0] = 1
n := len(columns)
db := connect.DBConnect()
defer connect.DBClose(db)
base := new(models.ModelManager)
blanks := base.Blanks()
for i := 0; i < len(inf); i = i + 4 {
colNameObj := inf[i].(map[string]interface{})
colNameDBObj := inf[i+1].(map[string]interface{})
colTypeObj := inf[i+2].(map[string]interface{})
colLenObj := inf[i+3].(map[string]interface{})
var type_ string
switch colTypeObj["value"].(string) {
case "input":
type_ = "varchar(" + colLenObj["value"].(string) + ")"
break
case "select":
type_ = "varchar(" + colLenObj["value"].(string) + ")[]"
break
case "date":
type_ = colLenObj["value"].(string)
break
}
if utils.IsExist(columns, colNameDBObj["value"].(string)) == false {
query := "ALTER TABLE blank_" + id + " ADD COLUMN " + colNameDBObj["value"].(string) + " " + type_
stmt, err := db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
blanks.Update([]string{"columns[" + strconv.Itoa(n) + "]"}, []interface{}{colNameDBObj["value"].(string)}, fmt.Sprintf("contest_id=%s", id))
blanks.Update([]string{"colNames[" + strconv.Itoa(n) + "]"}, []interface{}{colNameObj["value"].(string)}, fmt.Sprintf("contest_id=%s", id))
blanks.Update([]string{"types[" + strconv.Itoa(n) + "]"}, []interface{}{type_}, fmt.Sprintf("contest_id=%s", id))
n++
} else {
used[colNameDBObj["value"].(string)][0] = 1
query := "ALTER TABLE blank_" + id + " ALTER COLUMN " + colNameDBObj["value"].(string) + " TYPE " + type_
stmt, err := db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
blanks.Update([]string{"types[" + strconv.Itoa(used[colNameDBObj["value"].(string)][1]) + "]"}, []interface{}{type_}, fmt.Sprintf("contest_id=%s", id))
}
}
for i, v := range used {
if v[0] == 0 {
query := "UPDATE blanks SET columns = "
query += "(SELECT del_elem_by_index ("
query += "(SELECT columns FROM blanks WHERE contest_id =" + id + "),"
query += strconv.Itoa(v[1]) + "))"
query += "WHERE contest_id =" + id
stmt, err := db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
query = "UPDATE blanks SET colnames = "
query += "(SELECT del_elem_by_index ("
query += "(SELECT colnames FROM blanks WHERE contest_id =" + id + "),"
query += strconv.Itoa(v[1]) + "))"
query += "WHERE contest_id =" + id
stmt, err = db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
query = "UPDATE blanks SET types = "
query += "(SELECT del_elem_by_index ("
query += "(SELECT types FROM blanks WHERE contest_id =" + id + "),"
query += strconv.Itoa(v[1]) + "))"
query += "WHERE contest_id =" + id
stmt, err = db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
query = "ALTER TABLE blank_" + id + " DROP COLUMN IF EXISTS " + i
stmt, err = db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
}
}
query := "UPDATE blanks SET columns = "
query += "(SELECT del_null_from_arr ("
query += "(SELECT columns FROM blanks WHERE contest_id =" + id + ")))"
query += "WHERE contest_id =" + id
stmt, err := db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
query = "UPDATE blanks SET colnames = "
query += "(SELECT del_null_from_arr ("
query += "(SELECT colnames FROM blanks WHERE contest_id =" + id + ")))"
query += "WHERE contest_id =" + id
stmt, err = db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
query = "UPDATE blanks SET types = "
query += "(SELECT del_null_from_arr ("
query += "(SELECT types FROM blanks WHERE contest_id =" + id + ")))"
query += "WHERE contest_id =" + id
stmt, err = db.Prepare(query)
defer connect.DBClose(stmt)
utils.HandleErr("[Handler.Index->editBlank] Prepare: ", err)
_, err = stmt.Exec()
utils.HandleErr("[Handler.Index->editBlank] Exec: ", err)
break
case "addBlank":
id := data["id"].(string)
inf := data["data"].([]interface{})
fmt.Println("inf: ", inf)
columns := connect.DBGetColumnNames("blank_" + id)
var fields []interface{}
for i := 0; i < len(inf); i++ {
field := inf[i].(map[string]interface{})
fields = append(fields, field["value"].(string))
}
query := connect.DBInsert("blank_"+id, columns[1:])
db := connect.DBConnect()
stmt, err := db.Prepare(query)
defer connect.DBClose(db, stmt)
utils.HandleErr("[addBlank] Prepare: ", err)
_, err = stmt.Exec(fields...)
utils.HandleErr("[addBlank] Exec: ", err)
ans := map[string]interface{}{"response": "ok"}
res, err := json.Marshal(ans)
utils.HandleErr("[Handle.Index] json.Marshal: ", err)
fmt.Fprintf(this.Response, "%s", string(res))
}
}
func (this Entity) Select(where map[string]interface{}, fields ...string) []interface{} {
keys := make([]string, len(where))
vals := make([]interface{}, len(where))
j := 0
for i, v := range where {
keys[j] = i
vals[j] = v
j++
}
db := connect.DBConnect()
query := connect.DBSelect(this.TableName, keys, fields...)
stmt, err := db.Prepare(query)
utils.HandleErr("[Entity.Select] Prepare: ", err)
defer connect.DBClose(db, stmt)
rows, err := stmt.Query(vals...)
utils.HandleErr("[Entity.Select] Query: ", err)
rowsInf, err := stmt.Exec(vals...)
utils.HandleErr("[Entity.Select] Exec: ", err)
columns, err := rows.Columns()
utils.HandleErr("[Entity.Select] Columns: ", err)
row := make([]interface{}, len(columns))
values := make([]interface{}, len(columns))
for i, _ := range row {
row[i] = &values[i]
}
l, err := rowsInf.RowsAffected()
utils.HandleErr("[Entity.Select] RowsAffected: ", err)
answer := make([]interface{}, l)
j = 0
for rows.Next() {
rows.Scan(row...)
answer[j] = make(map[string]interface{}, len(values))
record := make(map[string]interface{}, len(values))
for i, col := range values {
if col != nil {
fmt.Printf("\n%s: type= %s\n", columns[i], reflect.TypeOf(col))
switch col.(type) {
default:
utils.HandleErr("Entity.Select: Unexpected type.", nil)
case bool:
record[columns[i]] = col.(bool)
case int:
record[columns[i]] = col.(int)
case int64:
record[columns[i]] = col.(int64)
case float64:
record[columns[i]] = col.(float64)
case string:
record[columns[i]] = col.(string)
case []byte:
record[columns[i]] = string(col.([]byte))
case []int8:
record[columns[i]] = col.([]string)
case time.Time:
record[columns[i]] = col
}
}
answer[j] = record
}
j++
}
return answer
}