示例#1
0
文件: engine.go 项目: admpub/xorm
// DumpAll dump database all table structs and data to w with specify db type
func (engine *Engine) dumpTables(tables []*core.Table, w io.Writer, tp ...core.DbType) error {
	var dialect core.Dialect
	if len(tp) == 0 {
		dialect = engine.dialect
	} else {
		dialect = core.QueryDialect(tp[0])
		if dialect == nil {
			return errors.New("Unsupported database type.")
		}
		dialect.Init(nil, engine.dialect.URI(), "", "")
	}

	_, err := io.WriteString(w, fmt.Sprintf("/*Generated by xorm v%s %s, from %s to %s*/\n\n",
		Version, time.Now().In(engine.TZLocation).Format("2006-01-02 15:04:05"), engine.dialect.DBType(), dialect.DBType()))
	if err != nil {
		return err
	}

	for i, table := range tables {
		if i > 0 {
			_, err = io.WriteString(w, "\n")
			if err != nil {
				return err
			}
		}
		_, err = io.WriteString(w, dialect.CreateTableSql(table, "", table.StoreEngine, "")+";\n")
		if err != nil {
			return err
		}
		for _, index := range table.Indexes {
			_, err = io.WriteString(w, dialect.CreateIndexSql(table.Name, index)+";\n")
			if err != nil {
				return err
			}
		}

		rows, err := engine.DB().Query("SELECT * FROM " + engine.Quote(table.Name))
		if err != nil {
			return err
		}
		defer rows.Close()

		cols, err := rows.Columns()
		if err != nil {
			return err
		}
		if len(cols) == 0 {
			continue
		}
		for rows.Next() {
			dest := make([]interface{}, len(cols))
			err = rows.ScanSlice(&dest)
			if err != nil {
				return err
			}

			_, err = io.WriteString(w, "INSERT INTO "+dialect.Quote(table.Name)+" ("+dialect.Quote(strings.Join(cols, dialect.Quote(", ")))+") VALUES (")
			if err != nil {
				return err
			}

			var temp string
			for i, d := range dest {
				col := table.GetColumn(cols[i])
				if d == nil {
					temp += ", NULL"
				} else if col.SQLType.IsText() || col.SQLType.IsTime() {
					var v = fmt.Sprintf("%s", d)
					if strings.HasSuffix(v, " +0000 UTC") {
						temp += fmt.Sprintf(", '%s'", v[0:len(v)-len(" +0000 UTC")])
					} else {
						temp += ", '" + strings.Replace(v, "'", "''", -1) + "'"
					}
				} else if col.SQLType.IsBlob() {
					if reflect.TypeOf(d).Kind() == reflect.Slice {
						temp += fmt.Sprintf(", %s", dialect.FormatBytes(d.([]byte)))
					} else if reflect.TypeOf(d).Kind() == reflect.String {
						temp += fmt.Sprintf(", '%s'", d.(string))
					}
				} else if col.SQLType.IsNumeric() {
					switch reflect.TypeOf(d).Kind() {
					case reflect.Slice:
						temp += fmt.Sprintf(", %s", string(d.([]byte)))
					default:
						temp += fmt.Sprintf(", %v", d)
					}
				} else {
					s := fmt.Sprintf("%v", d)
					if strings.Contains(s, ":") || strings.Contains(s, "-") {
						if strings.HasSuffix(s, " +0000 UTC") {
							temp += fmt.Sprintf(", '%s'", s[0:len(s)-len(" +0000 UTC")])
						} else {
							temp += fmt.Sprintf(", '%s'", s)
						}
					} else {
						temp += fmt.Sprintf(", %s", s)
					}
				}
			}
			_, err = io.WriteString(w, temp[2:]+");\n")
			if err != nil {
				return err
			}
		}
	}
	return nil
}
示例#2
0
func (statement *Statement) genSelectSql(columnStr string) (a string) {
	var distinct string
	if statement.IsDistinct {
		distinct = "DISTINCT "
	}

	var dialect core.Dialect = statement.Engine.Dialect()
	var top string
	var mssqlCondi string

	statement.processIdParam()

	var buf bytes.Buffer
	if len(statement.WhereStr) > 0 {
		if len(statement.ConditionStr) > 0 {
			fmt.Fprintf(&buf, " WHERE (%v)", statement.WhereStr)
		} else {
			fmt.Fprintf(&buf, " WHERE %v", statement.WhereStr)
		}
		if statement.ConditionStr != "" {
			fmt.Fprintf(&buf, " %s (%v)", dialect.AndStr(), statement.ConditionStr)
		}
	} else if len(statement.ConditionStr) > 0 {
		fmt.Fprintf(&buf, " WHERE %v", statement.ConditionStr)
	}
	var whereStr = buf.String()

	var fromStr string = " FROM " + statement.Engine.Quote(statement.TableName())
	if statement.TableAlias != "" {
		if dialect.DBType() == core.ORACLE {
			fromStr += " " + statement.Engine.Quote(statement.TableAlias)
		} else {
			fromStr += " AS " + statement.Engine.Quote(statement.TableAlias)
		}
	}
	if statement.JoinStr != "" {
		fromStr = fmt.Sprintf("%v %v", fromStr, statement.JoinStr)
	}

	if dialect.DBType() == core.MSSQL {
		if statement.LimitN > 0 {
			top = fmt.Sprintf(" TOP %d ", statement.LimitN)
		}
		if statement.Start > 0 {
			var column string = "(id)"
			if len(statement.RefTable.PKColumns()) == 0 {
				for _, index := range statement.RefTable.Indexes {
					if len(index.Cols) == 1 {
						column = index.Cols[0]
						break
					}
				}
				if len(column) == 0 {
					column = statement.RefTable.ColumnsSeq()[0]
				}
			}
			var orderStr string
			if len(statement.OrderStr) > 0 {
				orderStr = " ORDER BY " + statement.OrderStr
			}
			var groupStr string
			if len(statement.GroupByStr) > 0 {
				groupStr = " GROUP BY " + statement.GroupByStr
			}
			mssqlCondi = fmt.Sprintf("(%s NOT IN (SELECT TOP %d %s%s%s%s%s))",
				column, statement.Start, column, fromStr, whereStr, orderStr, groupStr)
		}
	}

	// !nashtsai! REVIEW Sprintf is considered slowest mean of string concatnation, better to work with builder pattern
	a = fmt.Sprintf("SELECT %v%v%v%v%v", top, distinct, columnStr, fromStr, whereStr)
	if len(mssqlCondi) > 0 {
		if len(whereStr) > 0 {
			a += " AND " + mssqlCondi
		} else {
			a += " WHERE " + mssqlCondi
		}
	}

	if statement.GroupByStr != "" {
		a = fmt.Sprintf("%v GROUP BY %v", a, statement.GroupByStr)
	}
	if statement.HavingStr != "" {
		a = fmt.Sprintf("%v %v", a, statement.HavingStr)
	}
	if statement.OrderStr != "" {
		a = fmt.Sprintf("%v ORDER BY %v", a, statement.OrderStr)
	}
	if dialect.DBType() != core.MSSQL && dialect.DBType() != core.ORACLE {
		if statement.Start > 0 {
			a = fmt.Sprintf("%v LIMIT %v OFFSET %v", a, statement.LimitN, statement.Start)
		} else if statement.LimitN > 0 {
			a = fmt.Sprintf("%v LIMIT %v", a, statement.LimitN)
		}
	} else if dialect.DBType() == core.ORACLE {
		if statement.Start != 0 || statement.LimitN != 0 {
			a = fmt.Sprintf("SELECT %v FROM (SELECT %v,ROWNUM RN FROM (%v) at WHERE ROWNUM <= %d) aat WHERE RN > %d", columnStr, columnStr, a, statement.Start+statement.LimitN, statement.Start)
		}
	}
	if statement.IsForUpdate {
		a = dialect.ForUpdateSql(a)
	}

	return
}