func (w *DatabaseWorld) SaveThing(thing *Thing) (ok bool) {
tabletext, err := json.Marshal(thing.Table)
if err != nil {
log.Println("Error serializing table data for thing", thing.Id, ":", err.Error())
return false
}
var parent sql.NullInt64
if thing.Parent != 0 {
parent.Int64 = int64(thing.Parent)
parent.Valid = true
}
var owner sql.NullInt64
if thing.Owner != 0 && thing.Type.HasOwner() {
owner.Int64 = int64(thing.Owner)
owner.Valid = true
}
var program sql.NullString
if thing.Program != nil {
program.String = thing.Program.Text
program.Valid = true
}
// TODO: save the allow list
_, err = w.db.Exec("UPDATE thing SET name = $1, parent = $2, owner = $3, adminlist = $4, denylist = $5, tabledata = $6, program = $7 WHERE id = $8",
thing.Name, parent, owner, thing.AdminList, thing.DenyList,
types.JsonText(tabletext), program, thing.Id)
if err != nil {
log.Println("Error saving a thing", thing.Id, ":", err.Error())
return false
}
return true
}
func checkStringForNull(eventStr string, event *sql.NullString) {
if len(eventStr) == 0 {
event.Valid = false
} else {
event.String = eventStr
event.Valid = true
}
}
// Scan implements the Scanner interface.
func (ns *ByteSlice) Scan(value interface{}) error {
n := sql.NullString{String: base64.StdEncoding.EncodeToString(ns.ByteSlice)}
err := n.Scan(value)
//ns.Float32, ns.Valid = float32(n.Float64), n.Valid
ns.ByteSlice, err = base64.StdEncoding.DecodeString(n.String)
ns.Valid = n.Valid
return err
}
func (this *ERole) Scan(value interface{}) error {
var x sql.NullString
err := x.Scan(value)
if !x.Valid || err != nil {
return err
}
*this = ERole(x.String)
return nil
}
func (this *Str) Scan(value interface{}) error {
var s sql.NullString
err := s.Scan(value)
if !s.Valid || err != nil {
return err
}
*this = Str(s.String)
return nil
}
func main() {
c := configFromFile()
s := slack.New(c.SlackToken)
db, err := sql.Open("mysql", c.Datasource)
if err != nil {
log.Fatal(err)
}
defer db.Close()
err = updateChannels(s, db)
if err != nil {
log.Fatal(err)
}
err = updateUsers(s, db)
if err != nil {
log.Fatal(err)
}
rows, err := db.Query("SELECT id, name, latest FROM channels")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id string
var name string
var latest sql.NullString
if err := rows.Scan(&id, &name, &latest); err != nil {
log.Fatal(err)
}
fmt.Printf("import history: #%s\n", name)
if !latest.Valid {
latest.String = ""
}
l, err := addChannelHistory(s, db, id, latest.String)
if err != nil {
log.Fatal(err)
}
if l != "" {
_, err = db.Exec(`UPDATE channels SET latest = ? WHERE id = ?`, l, id)
if err != nil {
log.Fatal(err)
}
}
}
}
func main() {
db, err := sqlx.Connect("mysql", "root:passw0rd@(localhost:3306)/bookszilla?parseTime=true")
if err != nil {
log.Fatalln(err)
}
db.Ping()
for i := 1; i <= 1000; i++ {
url := "http://wmate.ru/ebooks/book" + strconv.Itoa(i) + ".html"
response, err := http.Get(url)
println(url + " " + response.Status)
if err != nil {
fmt.Printf("%s", err)
// os.Exit(1)
} else if response.StatusCode == 200 {
defer response.Body.Close()
doc, err := goquery.NewDocumentFromResponse(response)
if err != nil {
log.Fatal(err)
}
doc.Find("article.post").Each(func(i int, s *goquery.Selection) {
title := s.Find("h1").Text()
println(title)
// author := s.Find("ul.info li").Eq(2).Find("em").Text()
// println(author)
var desctiption sql.NullString
desc := ""
s.Find("p").Each(func(j int, sp *goquery.Selection) {
desc += strings.TrimSpace(sp.Text()) + "\n"
})
if len(desc) > 0 {
desctiption.Scan(desc)
}
// println(desctiption)
sql := "INSERT INTO `books` (`name`, `description`) VALUES (?, ?);"
db.Exec(sql, title, desctiption)
})
}
}
}
func (h Hstore) Value() (driver.Value, error) {
hstore := hstore.Hstore{Map: map[string]sql.NullString{}}
if len(h) == 0 {
return nil, nil
}
for key, value := range h {
var s sql.NullString
if value != nil {
s.String = *value
s.Valid = true
}
hstore.Map[key] = s
}
return hstore.Value()
}
// Input: email string, password string
// Output:
// - Success: session string
// - Failed: {}
func Login(w http.ResponseWriter, r *http.Request) {
var email sql.NullString
email.Scan(r.FormValue("email"))
password := r.FormValue("password")
user := models.User{Email: email, Password: password}
if !repository.Login(&user) {
w.WriteHeader(http.StatusForbidden)
w.Write([]byte(`{"error":"Mat khau hoac email khong dung"}`))
return
}
createUserToken(&user)
repository.UpdateUser(&user)
json.NewEncoder(w).Encode(user)
}
func TestNullTypeString(t *testing.T) {
var b Sqlizer
var name sql.NullString
b = Eq{"name": name}
sql, args, err := b.ToSql()
assert.NoError(t, err)
assert.Empty(t, args)
assert.Equal(t, "name IS NULL", sql)
name.Scan("Name")
b = Eq{"name": name}
sql, args, err = b.ToSql()
assert.NoError(t, err)
assert.Equal(t, []interface{}{"Name"}, args)
assert.Equal(t, "name = ?", sql)
}
func (s *scanner) Scan(src interface{}) error {
var err error
switch s.value.Type().Kind() {
case reflect.Struct:
nt := mysql.NullTime{}
err := nt.Scan(src)
if err != nil {
return err
}
s.value.Set(reflect.ValueOf(nt.Time))
case reflect.Bool:
nb := sql.NullBool{}
err := nb.Scan(src)
if err != nil {
return err
}
s.value.SetBool(nb.Bool)
case reflect.String:
ns := sql.NullString{}
err = ns.Scan(src)
if err != nil {
return err
}
s.value.SetString(ns.String)
case reflect.Int64:
ni := sql.NullInt64{}
err = ni.Scan(src)
if err != nil {
return err
}
s.value.SetInt(ni.Int64)
case reflect.Float64:
ni := sql.NullFloat64{}
err = ni.Scan(src)
if err != nil {
return err
}
s.value.SetFloat(ni.Float64)
}
return nil
}
func main() {
db, err := sql.Open("postgres", "user=postgres password=pass dbname=mecab sslmode=disable")
checkErr(err)
//データの検索
//rows, err := db.Query("SELECT * FROM words LIMIT 3")
rows, err := db.Query("SELECT * FROM words WHERE first_char IS NULL")
checkErr(err)
for rows.Next() {
var surface string
var original string
var reading string
var first_c sql.NullString
var last_c sql.NullString
err = rows.Scan(&surface, &original, &reading, &first_c, &last_c)
checkErr(err)
/*
fmt.Println(surface)
fmt.Println(original)
fmt.Println(reading)
fmt.Println(reading[0:3])
fmt.Println(reading[len(reading)-3 : len(reading)])
fmt.Println(first_c)
fmt.Println(last_c)
*/
first_c.String = reading[0:3]
last_c.String = reading[len(reading)-3 : len(reading)]
//データの更新
stmt, err := db.Prepare("update words set first_char=$1, last_char=$2 where original=$3")
checkErr(err)
first_c.Valid = true
last_c.Valid = true
_, err = stmt.Exec(first_c.String, last_c.String, original)
checkErr(err)
/*
affect, err := res.RowsAffected()
checkErr(err)
fmt.Println(affect)
*/
}
db.Close()
fmt.Println("finish")
}
func (b *BasePmb) CheckAndComvertEmptyToNull(value interface{}) interface{} {
if DBCurrent_I.EmptyStringParameterAllowed {
return value
}
switch value.(type) {
case sql.NullString:
var nstr sql.NullString = value.(sql.NullString)
if nstr.Valid && nstr.String == "" {
nstr.Valid = false
}
return nstr
case *sql.NullString:
var nstr *sql.NullString = value.(*sql.NullString)
if nstr.Valid && nstr.String == "" {
nstr.Valid = false
}
return nstr
case string:
var str string = value.(string)
if str == "" {
var null sql.NullString
null.Valid = false
return null
}
case *string:
var strx string = *value.(*string)
if strx == "" {
var null sql.NullString
null.Valid = false
return null
}
default:
panic("This type not supported :" + GetType(value))
}
return value
}
func (b *BaseConditionQuery) FRES(value interface{}) interface{} {
if (*b.SqlClause).IsAllowEmptyStringQuery() {
return value
}
switch value.(type) {
case sql.NullString:
var nstr sql.NullString = value.(sql.NullString)
if nstr.Valid && nstr.String == "" {
nstr.Valid = false
}
return nstr
case *sql.NullString:
var nstr *sql.NullString = value.(*sql.NullString)
if nstr.Valid && nstr.String == "" {
nstr.Valid = false
}
return nstr
case string:
var str string = value.(string)
if str == "" {
var null sql.NullString
null.Valid = false
return null
}
case *string:
var strx string = *value.(*string)
if strx == "" {
var null sql.NullString
null.Valid = false
return null
}
default:
panic("This type not supported :" + GetType(value))
}
return value
}
func (db *SQLDB) FindContainerByIdentifier(id ContainerIdentifier) (Container, bool, error) {
err := deleteExpired(db)
if err != nil {
return Container{}, false, err
}
var imageResourceSource sql.NullString
if id.ImageResourceSource != nil {
marshaled, err := json.Marshal(id.ImageResourceSource)
if err != nil {
return Container{}, false, err
}
imageResourceSource.String = string(marshaled)
imageResourceSource.Valid = true
}
var imageResourceType sql.NullString
if id.ImageResourceType != "" {
imageResourceType.String = id.ImageResourceType
imageResourceType.Valid = true
}
var containers []Container
selectQuery := `
SELECT ` + containerColumns + `
FROM containers c ` + containerJoins + `
`
conditions := []string{}
params := []interface{}{}
if isValidCheckID(id) {
checkSourceBlob, err := json.Marshal(id.CheckSource)
if err != nil {
return Container{}, false, err
}
conditions = append(conditions, "resource_id = $1")
params = append(params, id.ResourceID)
conditions = append(conditions, "check_type = $2")
params = append(params, id.CheckType)
conditions = append(conditions, "check_source = $3")
params = append(params, checkSourceBlob)
conditions = append(conditions, "stage = $4")
params = append(params, string(id.Stage))
} else if isValidStepID(id) {
conditions = append(conditions, "build_id = $1")
params = append(params, id.BuildID)
conditions = append(conditions, "plan_id = $2")
params = append(params, string(id.PlanID))
conditions = append(conditions, "stage = $3")
params = append(params, string(id.Stage))
} else {
return Container{}, false, ErrInvalidIdentifier
}
if imageResourceSource.Valid && imageResourceType.Valid {
conditions = append(conditions, fmt.Sprintf("image_resource_source = $%d", len(params)+1))
params = append(params, imageResourceSource.String)
conditions = append(conditions, fmt.Sprintf("image_resource_type = $%d", len(params)+1))
params = append(params, imageResourceType.String)
} else {
conditions = append(conditions, "image_resource_source IS NULL")
conditions = append(conditions, "image_resource_type IS NULL")
}
selectQuery += "WHERE " + strings.Join(conditions, " AND ")
rows, err := db.conn.Query(selectQuery, params...)
if err != nil {
return Container{}, false, err
}
for rows.Next() {
container, err := scanContainer(rows)
if err != nil {
return Container{}, false, nil
}
containers = append(containers, container)
}
switch len(containers) {
case 0:
return Container{}, false, nil
case 1:
return containers[0], true, nil
default:
return Container{}, false, ErrMultipleContainersFound
}
}
func getPages(db *sql.DB, id string) ([]Page, error) {
var (
PageIDValue driver.Value
PageBodyValue driver.Value
VolumeValue driver.Value
PageNumberValue driver.Value
book Book
pages []Page
chapter Chapter
page Page
f *os.File
es *elastic.Client
)
var trim string
if strings.HasPrefix(id, "00") {
trim = "00"
} else if strings.HasPrefix(id, "0") {
trim = "0"
}
newid := strings.TrimPrefix(id, trim)
rows, err := db.Query("SELECT id, nass, part, page FROM b" + newid)
if err != nil {
log.Println(err)
return pages, err
}
defer rows.Close()
book, err = getBook(db, id)
if err != nil {
log.Println(err)
return pages, err
}
if *saveJSON == true {
f, err = os.Create("json/" + newid + ".json")
if err != nil {
return pages, err
}
defer f.Close()
}
if *indexDB == true {
es, err = elastic.NewClient(
elastic.SetSniff(false),
elastic.SetURL("http://localhost:32769"),
)
if err != nil {
log.Println(err)
return pages, err
}
}
count := 0
for rows.Next() {
var (
pageid sql.NullString
pagebody sql.NullString
volume sql.NullString
pagenumber sql.NullString
)
if err := rows.Scan(&pageid, &pagebody, &volume, &pagenumber); err != nil {
log.Println(err)
return pages, err
}
if pageid.Valid {
PageIDValue, err = pageid.Value()
if err != nil {
log.Println(err)
return pages, err
}
} else {
PageIDValue = "null"
}
if pagebody.Valid {
PageBodyValue, err = pagebody.Value()
if err != nil {
log.Println(err)
return pages, err
}
} else {
PageBodyValue = "null"
}
if volume.Valid {
VolumeValue, err = volume.Value()
if err != nil {
log.Println(err)
return pages, err
}
} else {
VolumeValue = "null"
}
if pagenumber.Valid {
PageNumberValue, err = pagenumber.Value()
if err != nil {
log.Println(err)
return pages, err
}
} else {
PageNumberValue = "null"
}
page = Page{
PageID: PageIDValue.(string),
PageBody: PageBodyValue.(string),
Volume: VolumeValue.(string),
PageNumber: PageNumberValue.(string),
Chapter: chapter,
Book: book,
}
pages := append(pages, page)
chapter, err = getChapter(db, id, page.PageNumber)
if err != nil {
log.Println(err)
return pages, err
}
if *saveJSON == true {
jsonByte, err := json.Marshal(page)
if err != nil {
log.Println(err)
return pages, err
}
_, err = f.Write([]byte(fmt.Sprintf("%s\n", string(jsonByte))))
if err != nil {
log.Println(err)
return pages, err
}
}
if *indexDB == true {
// index each page
_, err := es.Index().Pretty(true).
OpType("create").
Index("maktabah").
Type("pages").
Id(newid + "-" + strconv.Itoa(count)).
BodyJson(page).
Do()
if err != nil {
log.Println(err)
return pages, err
}
}
count++
}
return pages, nil
}
func fetchResult(itemT reflect.Type, rows *sql.Rows, columns []string) (reflect.Value, error) {
var item reflect.Value
var err error
switch itemT.Kind() {
case reflect.Map:
item = reflect.MakeMap(itemT)
case reflect.Struct:
item = reflect.New(itemT)
default:
return item, db.ErrExpectingMapOrStruct
}
expecting := len(columns)
// Allocating results.
values := make([]*sql.RawBytes, expecting)
scanArgs := make([]interface{}, expecting)
for i := range columns {
scanArgs[i] = &values[i]
}
if err = rows.Scan(scanArgs...); err != nil {
return item, err
}
// Range over row values.
for i, value := range values {
if value != nil {
// Real column name
column := columns[i]
// Value as string.
svalue := string(*value)
var cv reflect.Value
v, _ := to.Convert(svalue, reflect.String)
cv = reflect.ValueOf(v)
switch itemT.Kind() {
// Destination is a map.
case reflect.Map:
if cv.Type() != itemT.Elem() {
if itemT.Elem().Kind() == reflect.Interface {
cv, _ = util.StringToType(svalue, cv.Type())
} else {
cv, _ = util.StringToType(svalue, itemT.Elem())
}
}
if cv.IsValid() {
item.SetMapIndex(reflect.ValueOf(column), cv)
}
// Destionation is a struct.
case reflect.Struct:
index := util.GetStructFieldIndex(itemT, column)
if index == nil {
continue
} else {
// Destination field.
destf := item.Elem().FieldByIndex(index)
if destf.IsValid() {
if cv.Type() != destf.Type() {
if destf.Type().Kind() != reflect.Interface {
switch destf.Type() {
case nullFloat64Type:
nullFloat64 := sql.NullFloat64{}
if svalue != `` {
nullFloat64.Scan(svalue)
}
cv = reflect.ValueOf(nullFloat64)
case nullInt64Type:
nullInt64 := sql.NullInt64{}
if svalue != `` {
nullInt64.Scan(svalue)
}
cv = reflect.ValueOf(nullInt64)
case nullBoolType:
nullBool := sql.NullBool{}
if svalue != `` {
nullBool.Scan(svalue)
}
cv = reflect.ValueOf(nullBool)
case nullStringType:
nullString := sql.NullString{}
nullString.Scan(svalue)
cv = reflect.ValueOf(nullString)
default:
var decodingNull bool
if svalue == "" {
decodingNull = true
}
u, _ := indirect(destf, decodingNull)
if u != nil {
u.UnmarshalDB(svalue)
if destf.Kind() == reflect.Interface || destf.Kind() == reflect.Ptr {
cv = reflect.ValueOf(u)
} else {
cv = reflect.ValueOf(u).Elem()
}
} else {
cv, _ = util.StringToType(svalue, destf.Type())
}
}
}
}
// Copying value.
if cv.IsValid() {
destf.Set(cv)
}
}
}
}
}
}
return item, nil
}
func GenerateTestSuiteHandler(w http.ResponseWriter, r *http.Request) {
DontCache(&w)
// Generate a test suite based on the category IDs given by the user: of the
// category IDs that are live and have a route back to the "root" of the
// test suite hierarchy based on the category IDs given, this generates the
// JavaScript code necessary to add the categories and tests to the test
// framework running in the user's browser
// Sanity-check for given category IDs: it must be a comma-delimited list of
// integers, or * (which denotes all live categories in the category table)
categoryIDs := mux.Vars(r)["catids"]
if categoryIDs == "*" {
// It's enough just to find the top-level categories here: the recursive
// query we run below will find all of the live child categories we also
// need to include in the test suite execution
if err := db.QueryRow("SELECT '{' || array_to_string(array_agg(id),',') || '}' AS categories FROM category WHERE parent IS NULL AND live = true").Scan(&categoryIDs); err != nil {
log.Panicf("Unable to get list of top-level categories: %s\n", err)
}
} else if regexp.MustCompile("^[0-9]+(,[0-9]+)*$").MatchString(categoryIDs) {
categoryIDs = "{" + categoryIDs + "}"
} else {
log.Panic("Malformed category IDs supplied")
}
w.Header().Set("Content-Type", "text/javascript")
fmt.Fprintln(w, "// Automatically-generated BrowserAudit test suite\n")
// The table returned by this query is sorted by the order in which the
// categories and then tests are to be executed; the hierarchy is correct by
// the time it gets here, and so can be printed line-by-line with no further
// processing of the resulting table
// Suggestion for using WITH RECURSIVE courtesy of:
// http://blog.timothyandrew.net/blog/2013/06/24/recursive-postgres-queries/
// (note: WITH RECURSIVE is Postgres-specific)
rows, err := db.Query(`
WITH RECURSIVE touched_parent_categories AS (
SELECT unnest($1::int[]) AS id
UNION
SELECT category.parent AS id FROM category, touched_parent_categories tpc WHERE tpc.id = category.id AND category.parent IS NOT NULL
), touched_child_categories AS (
SELECT unnest($1::int[]) AS id
UNION
SELECT category.id FROM category, touched_child_categories tcc WHERE category.parent = tcc.id
), touched_categories AS (
SELECT id FROM touched_parent_categories
UNION
SELECT id FROM touched_child_categories
), hierarchy AS (
(
SELECT 'c' as type, id, title, description, NULL::test_behaviour AS behaviour, NULL::varchar AS test_function, NULL::smallint AS timeout, parent, array[execute_order] AS execute_order
FROM category
WHERE parent IS NULL AND live = true AND id IN (SELECT id FROM touched_categories)
) UNION (
SELECT e.type, e.id, e.title, e.description, e.behaviour, e.test_function, e.timeout, e.parent, (h.execute_order || e.execute_order)
FROM (
SELECT 't' as type, id, title, NULL::varchar AS description, behaviour, test_function, timeout, parent, execute_order
FROM test
WHERE live = true AND parent IN (SELECT id FROM touched_categories)
UNION
SELECT 'c' as type, id, title, description, NULL::test_behaviour AS behaviour, NULL::varchar AS test_function, NULL::smallint AS timeout, parent, execute_order
FROM category
WHERE live = true AND id IN (SELECT id FROM touched_categories)
) e, hierarchy h
WHERE e.parent = h.id AND h.type = 'c'
)
)
SELECT type, id, title, description, behaviour, test_function, timeout, parent FROM hierarchy ORDER BY execute_order`, categoryIDs)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var rowType string
var id int
var title string
var description sql.NullString
var behaviour sql.NullString
var testFunctionInvocation sql.NullString
var timeoutNullable sql.NullInt64
var parentNullable sql.NullInt64
// NULL description -> empty string (only the case for categories, which
// doesn't matter because they aren't written out for categories in the
// JavaScript below)
description.String = ""
// NULL behaviour -> empty string (only the case for categories, which
// doesn't matter because they aren't written out for categories in the
// JavaScript below)
behaviour.String = ""
// NULL test_function -> empty string (only the case for
// categories, which doesn't matter because they aren't written out for
// categories in the JavaScript below)
testFunctionInvocation.String = ""
if err := rows.Scan(&rowType, &id, &title, &description, &behaviour, &testFunctionInvocation, &timeoutNullable, &parentNullable); err != nil {
log.Fatal(err)
}
// NULL timeout -> JavaScript null in string
var timeout string
if timeoutNullable.Valid {
timeout = strconv.FormatInt(timeoutNullable.Int64, 10)
} else {
timeout = "null"
}
// NULL parent -> JavaScript null in string (only the case for top-level
// categories)
var parent string
if parentNullable.Valid {
parent = strconv.FormatInt(parentNullable.Int64, 10)
} else {
parent = "null"
}
if rowType == "c" { // row represents a category
fmt.Fprintf(
w,
"\nbrowserAuditTestFramework.addCategory(%d, %s, \"%s\", \"%s\");\n",
id,
parent,
strings.Replace(title, "\"", "\\\"", -1),
strings.Replace(description.String, "\"", "\\\"", -1))
} else { // rowType == "t": row represents a test
fmt.Fprintf(
w,
"browserAuditTestFramework.addTest(%d, %s, \"%s\", \"%s\", %s, %s);\n",
id,
parent,
strings.Replace(title, "\"", "\\\"", -1),
behaviour.String,
timeout,
testFunctionInvocation.String)
}
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}
func (db *SQLDB) CreateContainer(container Container, ttl time.Duration) (SavedContainer, error) {
if !(isValidCheckID(container.ContainerIdentifier) || isValidStepID(container.ContainerIdentifier)) {
return SavedContainer{}, ErrInvalidIdentifier
}
tx, err := db.conn.Begin()
if err != nil {
return SavedContainer{}, err
}
defer tx.Rollback()
checkSource, err := json.Marshal(container.CheckSource)
if err != nil {
return SavedContainer{}, err
}
envVariables, err := json.Marshal(container.EnvironmentVariables)
if err != nil {
return SavedContainer{}, err
}
user := container.User
interval := fmt.Sprintf("%d second", int(ttl.Seconds()))
if container.PipelineName != "" && container.PipelineID == 0 {
// containers that belong to some pipeline must be identified by pipeline ID not name
return SavedContainer{}, errors.New("container metadata must include pipeline ID")
}
var pipelineID sql.NullInt64
if container.PipelineID != 0 {
pipelineID.Int64 = int64(container.PipelineID)
pipelineID.Valid = true
}
var resourceID sql.NullInt64
if container.ResourceID != 0 {
resourceID.Int64 = int64(container.ResourceID)
resourceID.Valid = true
}
var resourceTypeVersion string
if container.ResourceTypeVersion != nil {
resourceTypeVersionBytes, err := json.Marshal(container.ResourceTypeVersion)
if err != nil {
return SavedContainer{}, err
}
resourceTypeVersion = string(resourceTypeVersionBytes)
}
var buildID sql.NullInt64
if container.BuildID != 0 {
buildID.Int64 = int64(container.BuildID)
buildID.Valid = true
}
workerName := container.WorkerName
if workerName == "" {
workerName = container.WorkerName
}
var attempts sql.NullString
if len(container.Attempts) > 0 {
attemptsBlob, err := json.Marshal(container.Attempts)
if err != nil {
return SavedContainer{}, err
}
attempts.Valid = true
attempts.String = string(attemptsBlob)
}
var imageResourceSource sql.NullString
if container.ImageResourceSource != nil {
marshaled, err := json.Marshal(container.ImageResourceSource)
if err != nil {
return SavedContainer{}, err
}
imageResourceSource.String = string(marshaled)
imageResourceSource.Valid = true
}
var imageResourceType sql.NullString
if container.ImageResourceType != "" {
imageResourceType.String = container.ImageResourceType
imageResourceType.Valid = true
}
_, err = tx.Exec(`
INSERT INTO containers (handle, resource_id, step_name, pipeline_id, build_id, type, worker_name, expires_at, ttl, check_type, check_source, plan_id, working_directory, env_variables, attempts, stage, image_resource_type, image_resource_source, process_user, resource_type_version)
VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() + $8::INTERVAL, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20)`,
container.Handle,
resourceID,
container.StepName,
pipelineID,
buildID,
container.Type.String(),
workerName,
interval,
ttl,
container.CheckType,
checkSource,
string(container.PlanID),
container.WorkingDirectory,
envVariables,
attempts,
string(container.Stage),
imageResourceType,
imageResourceSource,
user,
resourceTypeVersion,
)
if err != nil {
return SavedContainer{}, err
}
newContainer, err := scanContainer(tx.QueryRow(`
SELECT `+containerColumns+`
FROM containers c `+containerJoins+`
WHERE c.handle = $1
`, container.Handle))
if err != nil {
return SavedContainer{}, err
}
err = tx.Commit()
if err != nil {
return SavedContainer{}, err
}
return newContainer, nil
}
func getChapter(db *sql.DB, id, pageid string) (Chapter, error) {
var (
HeadingValue driver.Value
HeadingLevelValue driver.Value
SubLevelValue driver.Value
PageIDValue driver.Value
chapter Chapter
)
var trim string
if strings.HasPrefix(id, "00") {
trim = "00"
} else if strings.HasPrefix(id, "0") {
trim = "0"
}
id = strings.TrimPrefix(id, trim)
rows, err := db.Query("SELECT tit, lvl, sub, id FROM t" + id + " WHERE id <= '" + pageid + "' ORDER BY id DESC LIMIT 1")
if err != nil {
log.Println(err)
return chapter, err
}
defer rows.Close()
for rows.Next() {
var (
heading sql.NullString
headinglevel sql.NullString
sublevel sql.NullString
pageid sql.NullString
)
if err := rows.Scan(&heading, &headinglevel, &sublevel, &pageid); err != nil {
log.Println(err)
return chapter, err
}
if heading.Valid {
HeadingValue, err = heading.Value()
if err != nil {
log.Println(err)
return chapter, err
}
} else {
HeadingValue = "null"
}
if headinglevel.Valid {
HeadingLevelValue, err = headinglevel.Value()
if err != nil {
log.Println(err)
return chapter, err
}
} else {
HeadingLevelValue = "null"
}
if sublevel.Valid {
SubLevelValue, err = sublevel.Value()
if err != nil {
log.Println(err)
return chapter, err
}
} else {
SubLevelValue = "null"
}
if pageid.Valid {
PageIDValue, err = pageid.Value()
if err != nil {
log.Println(err)
return chapter, err
}
} else {
PageIDValue = "null"
}
chapter = Chapter{
Heading: HeadingValue.(string),
HeadingLevel: HeadingLevelValue.(string),
SubLevel: SubLevelValue.(string),
PageID: PageIDValue.(string),
}
}
return chapter, nil
}
//查看题库题目，不显示题目答案
func FuncListBankQuestion2(bankId int64, parse common.ListParam, aid int64) ([]QUESTION, common.Pagiation, error) {
rs := []QUESTION{}
args := []interface{}{bankId}
args = append(args, aid)
// answerFilter := ""
// if aid!=0{
// answerFilter = " and a.answer_key=?"
// }
statusFilter := ""
//1答对，0答错，-1未答
if parse.Status != "" {
statusFilter = " and a.add1=?"
s := parse.Status
if parse.Status == "-1" {
statusFilter = " and a.status=?"
s = "N"
}
args = append(args, s)
}
typeFilter := ""
if parse.Type != 0 {
typeFilter = " and q.type=?"
args = append(args, parse.Type)
}
if parse.Pa.Pn < 1 || parse.Pa.Ps < 1 {
parse.Pa.Pn = 1
parse.Pa.Ps = 10
}
start := (parse.Pa.Pn - 1) * parse.Pa.Ps
end := parse.Pa.Ps
conn := dbMgr.DbConn()
tx, err := conn.Begin()
if err != nil {
return rs, parse.Pa, err
}
sql_ := fmt.Sprintf(`select SQL_CALC_FOUND_ROWS q.TID, q.NAME, q.BANK_ID, q.PID, q.TAG, q.DESCRIPTION, q.DURATION, q.SCORE, q.Q_ANALYZE, q.TYPE,q.Q_OPTION, q.UID, q.UNAME,q2b.score,q2b.ext,q2b.tid,q2b.seq,q.desc2,a.tid,a.status,a.content,a.paper_snapshot,a.answer_snapshot from
ebs_answer a left join ebs_question q on q.tid=a.qid left join ebs_q2b q2b on q2b.tid=a.q2b_id and q2b.bank_id=?
where a.answer_key=? %s order by q2b.seq asc limit %d,%d`, statusFilter+typeFilter, start, end)
if aid == 0 {
sql_ = fmt.Sprintf(`select SQL_CALC_FOUND_ROWS q.TID, q.NAME, q.BANK_ID, q.PID, q.TAG, q.DESCRIPTION, q.DURATION, q.SCORE, q.Q_ANALYZE, q.TYPE,q.Q_OPTION, q.UID, q.UNAME,q2b.score,q2b.ext,q2b.tid,q2b.seq,q.desc2,a.tid,a.status,a.content,a.paper_snapshot,a.answer_snapshot
from ebs_question q join ebs_q2b q2b on q2b.bank_id=? and q2b.q_id=q.tid left join ebs_answer a on a.answer_key=? and a.q2b_id=q2b.tid where 1=1 %s order by q2b.seq asc limit %d,%d`, statusFilter+typeFilter, start, end)
}
log.D("%v", sql_)
if rows, err := tx.Query(sql_, args...); err != nil {
tx.Commit()
return rs, parse.Pa, err
} else {
columns := []string{"TID", "NAME", "BANK_ID", "PID", "TAG", "DESCRIPTION", "DURATION", "SCORE", "Q_ANALYZE", "TYPE",
"Q_OPTION", "UID", "UNAME", "SCORE"}
for rows.Next() {
vals := []interface{}{}
b := QUESTION{}
for _, v := range columns {
(&b).GetFieldByColumn(v, &vals)
}
var ext, desc2, aStatus, aContent, paperSnapshot, answerSnapshot sql.NullString
var q2bId sql.NullInt64
var q2bSeq, aIdRes sql.NullInt64
vals = append(vals, &ext, &q2bId, &q2bSeq, &desc2, &aIdRes, &aStatus, &aContent, &paperSnapshot, &answerSnapshot)
if err := rows.Scan(vals...); err != nil {
log.E("scan err:%v", err)
}
qParse := QUESTION{}
json.Unmarshal([]byte(paperSnapshot.String), &qParse)
if qParse.Tid != nil {
log.D("qParse:%v", qParse.ToString())
b = qParse
bb, _ := json.Marshal(qParse.Ext)
ext.String = string(bb)
}
b.Q2bId = q2bId.Int64
b.Q2bSeq = q2bSeq.Int64
if b.TYPE != nil {
if *b.TYPE == 35 {
b.DESCRIPTION = &desc2.String
}
}
if aid != 0 {
b.Extra = map[string]interface{}{"aid": aIdRes.Int64, "aStatus": aStatus.String, "aContent": aContent.String}
}
if ext.String != "" {
extParse := EXT{}
json.Unmarshal([]byte(ext.String), &extParse)
b.SCORE = &extParse.Score
b.DURATION = &extParse.Duration
b.Ext = extParse
}
rs = append(rs, b)
}
}
if err := tx.QueryRow(`select FOUND_ROWS()`).Scan(&parse.Pa.Total); err != nil {
tx.Commit()
return rs, parse.Pa, err
}
tx.Commit()
return rs, parse.Pa, nil
}
func (db *SQLDB) CreateContainer(container Container, ttl time.Duration) (Container, error) {
if !isValidID(container.ContainerIdentifier) {
return Container{}, ErrInvalidIdentifier
}
tx, err := db.conn.Begin()
if err != nil {
return Container{}, err
}
checkSource, err := json.Marshal(container.CheckSource)
if err != nil {
return Container{}, err
}
envVariables, err := json.Marshal(container.EnvironmentVariables)
if err != nil {
return Container{}, err
}
user := container.User
interval := fmt.Sprintf("%d second", int(ttl.Seconds()))
var pipelineID sql.NullInt64
if container.PipelineName != "" {
pipeline, err := db.GetPipelineByTeamNameAndName(atc.DefaultTeamName, container.PipelineName)
if err != nil {
return Container{}, fmt.Errorf("failed to find pipeline: %s", err.Error())
}
pipelineID.Int64 = int64(pipeline.ID)
pipelineID.Valid = true
}
var resourceID sql.NullInt64
if container.ResourceID != 0 {
resourceID.Int64 = int64(container.ResourceID)
resourceID.Valid = true
}
var buildID sql.NullInt64
if container.BuildID != 0 {
buildID.Int64 = int64(container.BuildID)
buildID.Valid = true
}
workerName := container.WorkerName
if workerName == "" {
workerName = container.WorkerName
}
var attempts sql.NullString
if len(container.Attempts) > 0 {
attemptsBlob, err := json.Marshal(container.Attempts)
if err != nil {
return Container{}, err
}
attempts.Valid = true
attempts.String = string(attemptsBlob)
}
var imageResourceSource sql.NullString
if container.ImageResourceSource != nil {
marshaled, err := json.Marshal(container.ImageResourceSource)
if err != nil {
return Container{}, err
}
imageResourceSource.String = string(marshaled)
imageResourceSource.Valid = true
}
var imageResourceType sql.NullString
if container.ImageResourceType != "" {
imageResourceType.String = container.ImageResourceType
imageResourceType.Valid = true
}
defer tx.Rollback()
_, err = tx.Exec(`
INSERT INTO containers (handle, resource_id, step_name, pipeline_id, build_id, type, worker_name, expires_at, check_type, check_source, plan_id, working_directory, env_variables, attempts, stage, image_resource_type, image_resource_source, process_user)
VALUES ($1, $2, $3, $4, $5, $6, $7, NOW() + $8::INTERVAL, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)`,
container.Handle,
resourceID,
container.StepName,
pipelineID,
buildID,
container.Type.String(),
workerName,
interval,
container.CheckType,
checkSource,
string(container.PlanID),
container.WorkingDirectory,
envVariables,
attempts,
string(container.Stage),
imageResourceType,
imageResourceSource,
user,
)
if err != nil {
return Container{}, err
}
newContainer, err := scanContainer(tx.QueryRow(`
SELECT `+containerColumns+`
FROM containers c `+containerJoins+`
WHERE c.handle = $1
`, container.Handle))
if err != nil {
return Container{}, err
}
err = tx.Commit()
if err != nil {
return Container{}, err
}
return newContainer, nil
}
func getBook(db *sql.DB, id string) (Book, error) {
var (
BookIDValue driver.Value
BookTitleValue driver.Value
InfoValue driver.Value
BookInfoValue driver.Value
AuthorValue driver.Value
AuthorBioValue driver.Value
CategoryValue driver.Value
DiedValue driver.Value
book Book
)
rows, err := db.Query("SELECT BkId, Bk, Betaka, Inf, Auth, AuthInf, cat, AD FROM main")
if err != nil {
log.Println(err)
return book, err
}
defer rows.Close()
for rows.Next() {
var (
bookid sql.NullString
booktitle sql.NullString
info sql.NullString
bookinfo sql.NullString
author sql.NullString
authorbio sql.NullString
category sql.NullString
died sql.NullString
)
if err := rows.Scan(&bookid, &booktitle, &info, &bookinfo, &author, &authorbio, &category, &died); err != nil {
log.Println(err)
return book, err
}
if bookid.Valid {
BookIDValue, err = bookid.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
BookIDValue = "null"
}
if booktitle.Valid {
BookTitleValue, err = booktitle.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
BookTitleValue = "null"
}
if info.Valid {
InfoValue, err = info.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
InfoValue = "null"
}
if bookinfo.Valid {
BookInfoValue, err = bookinfo.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
BookInfoValue = "null"
}
if author.Valid {
AuthorValue, err = author.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
AuthorValue = "null"
}
if authorbio.Valid {
AuthorBioValue, err = authorbio.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
AuthorBioValue = "null"
}
if category.Valid {
CategoryValue, err = category.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
CategoryValue = "null"
}
if died.Valid {
DiedValue, err = died.Value()
if err != nil {
log.Println(err)
return book, err
}
} else {
DiedValue = "null"
}
book = Book{
BookID: BookIDValue.(string),
BookTitle: BookTitleValue.(string),
Info: InfoValue.(string),
BookInfo: BookInfoValue.(string),
Author: AuthorValue.(string),
AuthorBio: AuthorBioValue.(string),
Category: CategoryValue.(string),
Died: DiedValue.(string),
}
}
return book, nil
}
// Scan implements the Scanner interface.
func (ns *String) Scan(value interface{}) error {
n := sql.NullString{String: ns.String}
err := n.Scan(value)
ns.String, ns.Valid = n.String, n.Valid
return err
}
func setString(s *sql.NullString, v string) {
if v != "" {
s.Valid = true
s.String = v
}
}
"database/sql"
// "fmt"
. "github.com/onsi/ginkgo"
. "github.com/onsi/gomega"
"github.com/triitvn/instagram-go/api/models/repository"
"net/url"
"strconv"
)
var _ = Describe("User", func() {
var params = url.Values{}
params.Add("displayName", "Foo")
params.Add("email", "*****@*****.**")
params.Add("password", "123456")
var email sql.NullString
email.Scan("*****@*****.**")
repository.DeleteUserByEmail(email)
var userId string
Describe("Register", func() {
Context("Register Success", func() {
It("should have ID", func() {
response := Request("POST", "/user?"+params.Encode(), "")
objmap := JsonStrToMap(response.Body)
userId = strconv.Itoa(int(objmap["Id"].(float64)))
func CreateNullString(s string) sql.NullString {
var ns sql.NullString
ns.Valid = true
ns.String = s
return ns
}
func (ns MyNullString) Value() (driver.Value, error) {
n := sql.NullString{String: ns.String}
return n.Value()
}
func TestStringOrNull(t *testing.T) {
var (
nullString sql.NullString
value driver.Value
err error
)
// When the string is empty
nullString = StringOrNull("")
// nullString.Valid should be false
assert.False(t, nullString.Valid)
// nullString.Value() should return nil
value, err = nullString.Value()
assert.Nil(t, err)
assert.Nil(t, value)
// When the string is not empty
nullString = StringOrNull("foo")
// nullString.Valid should be true
assert.True(t, nullString.Valid)
// nullString.Value() should return the string
value, err = nullString.Value()
assert.Nil(t, err)
assert.Equal(t, "foo", value)
}
func (database Database) listAllConnections() (res DbUsersWithConnections) {
res = make(DbUsersWithConnections)
rows, err := database.db.Query(`
-- Left join because we want users without connections as well
SELECT u1.id, u1.username, u2.id, u2.username FROM
user AS u1 LEFT JOIN connection ON u1.id = connection.fromUser
LEFT JOIN user AS u2 ON u2.id = connection.toUser
ORDER BY u1.id
`)
checkErr(err)
defer rows.Close()
for rows.Next() {
var fromUser User
var toUsername sql.NullString
var toId sql.NullInt64
err := rows.Scan(&fromUser.Id, &fromUser.Username, &toId, &toUsername)
checkErr(err)
if toId.Valid {
// this user has at least one connection, unpack the nullable values
toIdValue, _ := toId.Value()
toUsernameValue, _ := toUsername.Value()
res[fromUser] = append(res[fromUser], User{toIdValue.(int64), toUsernameValue.(string)})
} else {
// this user doesn't have any connections
res[fromUser] = []User{}
}
}
return res
}
