func (db *mssql) CreateTableSql(table *core.Table, tableName, storeEngine, charset string) string {
var sql string
if tableName == "" {
tableName = table.Name
}
sql = "IF NOT EXISTS (SELECT [name] FROM sys.tables WHERE [name] = '" + tableName + "' ) CREATE TABLE "
sql += db.QuoteStr() + tableName + db.QuoteStr() + " ("
pkList := table.PrimaryKeys
for _, colName := range table.ColumnsSeq() {
col := table.GetColumn(colName)
if col.IsPrimaryKey && len(pkList) == 1 {
sql += col.String(db)
} else {
sql += col.StringNoPk(db)
}
sql = strings.TrimSpace(sql)
sql += ", "
}
if len(pkList) > 1 {
sql += "PRIMARY KEY ( "
sql += strings.Join(pkList, ",")
sql += " ), "
}
sql = sql[:len(sql)-2] + ")"
sql += ";"
return sql
}
func (b *oracle) CreateTableSql(table *core.Table, tableName, storeEngine, charset string) string {
var sql string
sql = "CREATE TABLE "
if tableName == "" {
tableName = table.Name
}
sql += b.Quote(tableName) + " ("
pkList := table.PrimaryKeys
for _, colName := range table.ColumnsSeq() {
col := table.GetColumn(colName)
/*if col.IsPrimaryKey && len(pkList) == 1 {
sql += col.String(b.dialect)
} else {*/
sql += col.StringNoPk(b)
//}
sql = strings.TrimSpace(sql)
sql += ", "
}
if len(pkList) > 0 {
sql += "PRIMARY KEY ( "
sql += b.Quote(strings.Join(pkList, b.Quote(",")))
sql += " ), "
}
sql = sql[:len(sql)-2] + ")"
if b.SupportEngine() && storeEngine != "" {
sql += " ENGINE=" + storeEngine
}
if b.SupportCharset() {
if len(charset) == 0 {
charset = b.URI().Charset
}
if len(charset) > 0 {
sql += " DEFAULT CHARSET " + charset
}
}
return sql
}
func (engine *Engine) Sync2(beans ...interface{}) error {
tables, err := engine.DBMetas()
if err != nil {
return err
}
structTables := make([]*core.Table, 0)
for _, bean := range beans {
table := engine.TableInfo(bean)
structTables = append(structTables, table)
var oriTable *core.Table
for _, tb := range tables {
if tb.Name == table.Name {
oriTable = tb
break
}
}
if oriTable == nil {
err = engine.CreateTables(bean)
if err != nil {
return err
}
err = engine.CreateUniques(bean)
if err != nil {
return err
}
err = engine.CreateIndexes(bean)
if err != nil {
return err
}
} else {
for _, col := range table.Columns() {
var oriCol *core.Column
for _, col2 := range oriTable.Columns() {
if col.Name == col2.Name {
oriCol = col2
break
}
}
if oriCol != nil {
expectedType := engine.dialect.SqlType(col)
//curType := oriCol.SQLType.Name
curType := engine.dialect.SqlType(oriCol)
if expectedType != curType {
if expectedType == core.Text &&
strings.HasPrefix(curType, core.Varchar) {
// currently only support mysql
if engine.dialect.DBType() == core.MYSQL ||
engine.dialect.DBType() == core.POSTGRES {
engine.LogInfof("Table %s column %s change type from %s to %s\n",
table.Name, col.Name, curType, expectedType)
_, err = engine.Exec(engine.dialect.ModifyColumnSql(table.Name, col))
} else {
engine.LogWarnf("Table %s column %s db type is %s, struct type is %s\n",
table.Name, col.Name, curType, expectedType)
}
} else {
engine.LogWarnf("Table %s column %s db type is %s, struct type is %s",
table.Name, col.Name, curType, expectedType)
}
}
if col.Default != oriCol.Default {
engine.LogWarnf("Table %s Column %s db default is %s, struct default is %s",
table.Name, col.Name, oriCol.Default, col.Default)
}
if col.Nullable != oriCol.Nullable {
engine.LogWarnf("Table %s Column %s db nullable is %v, struct nullable is %v",
table.Name, col.Name, oriCol.Nullable, col.Nullable)
}
} else {
session := engine.NewSession()
session.Statement.RefTable = table
defer session.Close()
err = session.addColumn(col.Name)
}
if err != nil {
return err
}
}
var foundIndexNames = make(map[string]bool)
for name, index := range table.Indexes {
var oriIndex *core.Index
for name2, index2 := range oriTable.Indexes {
if index.Equal(index2) {
oriIndex = index2
foundIndexNames[name2] = true
break
}
}
if oriIndex != nil {
if oriIndex.Type != index.Type {
sql := engine.dialect.DropIndexSql(table.Name, oriIndex)
_, err = engine.Exec(sql)
if err != nil {
return err
}
oriIndex = nil
}
}
if oriIndex == nil {
if index.Type == core.UniqueType {
session := engine.NewSession()
session.Statement.RefTable = table
defer session.Close()
err = session.addUnique(table.Name, name)
} else if index.Type == core.IndexType {
session := engine.NewSession()
session.Statement.RefTable = table
defer session.Close()
err = session.addIndex(table.Name, name)
}
if err != nil {
return err
}
}
}
for name2, index2 := range oriTable.Indexes {
if _, ok := foundIndexNames[name2]; !ok {
sql := engine.dialect.DropIndexSql(table.Name, index2)
_, err = engine.Exec(sql)
if err != nil {
return err
}
}
}
}
}
for _, table := range tables {
var oriTable *core.Table
for _, structTable := range structTables {
if table.Name == structTable.Name {
oriTable = structTable
break
}
}
if oriTable == nil {
//engine.LogWarnf("Table %s has no struct to mapping it", table.Name)
continue
}
for _, colName := range table.ColumnsSeq() {
if oriTable.GetColumn(colName) == nil {
engine.LogWarnf("Table %s has column %s but struct has not related field",
table.Name, colName)
}
}
}
return nil
}
func genCols(table *core.Table, session *Session, bean interface{}, useCol bool, includeQuote bool) ([]string, []interface{}, error) {
colNames := make([]string, 0)
args := make([]interface{}, 0)
for _, col := range table.Columns() {
lColName := strings.ToLower(col.Name)
if useCol && !col.IsVersion && !col.IsCreated && !col.IsUpdated {
if _, ok := session.Statement.columnMap[lColName]; !ok {
continue
}
}
if col.MapType == core.ONLYFROMDB {
continue
}
fieldValuePtr, err := col.ValueOf(bean)
if err != nil {
session.Engine.LogError(err)
continue
}
fieldValue := *fieldValuePtr
if col.IsAutoIncrement {
switch fieldValue.Type().Kind() {
case reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int, reflect.Int64:
if fieldValue.Int() == 0 {
continue
}
case reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint, reflect.Uint64:
if fieldValue.Uint() == 0 {
continue
}
case reflect.String:
if len(fieldValue.String()) == 0 {
continue
}
}
}
if col.IsDeleted {
continue
}
if session.Statement.ColumnStr != "" {
if _, ok := session.Statement.columnMap[lColName]; !ok {
continue
}
}
if session.Statement.OmitStr != "" {
if _, ok := session.Statement.columnMap[lColName]; ok {
continue
}
}
if (col.IsCreated || col.IsUpdated) && session.Statement.UseAutoTime {
val, t := session.Engine.NowTime2(col.SQLType.Name)
args = append(args, val)
var colName = col.Name
session.afterClosures = append(session.afterClosures, func(bean interface{}) {
col := table.GetColumn(colName)
setColumnTime(bean, col, t)
})
} else if col.IsVersion && session.Statement.checkVersion {
args = append(args, 1)
} else {
arg, err := session.value2Interface(col, fieldValue)
if err != nil {
return colNames, args, err
}
args = append(args, arg)
}
if includeQuote {
colNames = append(colNames, session.Engine.Quote(col.Name)+" = ?")
} else {
colNames = append(colNames, col.Name)
}
}
return colNames, args, nil
}
func (c *View) Get() error {
id, err := strconv.ParseInt(c.Req().FormValue("id"), 10, 64)
if err != nil {
return err
}
engine, err := models.GetEngineById(id)
if err != nil {
return err
}
o := GetOrm(engine)
if o == nil {
return fmt.Errorf("get engine %s failed", engine.Name)
}
tables, err := o.DBMetas()
if err != nil {
return err
}
var records = make([][]*string, 0)
var columns = make([]*core.Column, 0)
tb := c.Req().FormValue("tb")
tb = strings.Replace(tb, `"`, "", -1)
tb = strings.Replace(tb, `'`, "", -1)
tb = strings.Replace(tb, "`", "", -1)
var isTableView = len(tb) > 0
sql := c.Req().FormValue("sql")
var table *core.Table
var pkIdx int
var isExecute bool
var affected int64
var total int
var countSql string
var args = make([]interface{}, 0)
start, _ := strconv.Atoi(c.Req().FormValue("start"))
limit, _ := strconv.Atoi(c.Req().FormValue("limit"))
if limit == 0 {
limit = 20
}
if sql != "" || tb != "" {
if sql != "" {
isExecute = !strings.HasPrefix(strings.ToLower(sql), "select")
} else if tb != "" {
countSql = "select count(*) from `" + tb + "`"
sql = fmt.Sprintf("select * from `"+tb+"` LIMIT %d OFFSET %d", limit, start)
//args = append(args, []interface{}{limit, start}...)
} else {
return errors.New("unknow operation")
}
if isExecute {
res, err := o.Exec(sql)
if err != nil {
return err
}
affected, _ = res.RowsAffected()
} else {
if len(countSql) > 0 {
err = o.DB().QueryRow(countSql).Scan(&total)
if err != nil {
return err
}
fmt.Println("total records:", total)
}
rows, err := o.DB().Query(sql, args...)
if err != nil {
return err
}
defer rows.Close()
cols, err := rows.Columns()
if err != nil {
return err
}
if len(tb) > 0 {
for _, tt := range tables {
if tb == tt.Name {
table = tt
break
}
}
if table != nil {
for i, col := range cols {
c := table.GetColumn(col)
if len(table.PKColumns()) == 1 && c.IsPrimaryKey {
pkIdx = i
}
columns = append(columns, c)
}
}
} else {
for _, col := range cols {
columns = append(columns, &core.Column{
Name: col,
})
}
}
for rows.Next() {
datas := make([]*string, len(columns))
err = rows.ScanSlice(&datas)
if err != nil {
return err
}
records = append(records, datas)
}
}
}
engines, err := models.FindEngines()
if err != nil {
return err
}
return c.Render("root.html", renders.T{
"engines": engines,
"tables": tables,
"table": table,
"records": records,
"columns": columns,
"id": id,
"sql": sql,
"tb": tb,
"isExecute": isExecute,
"isTableView": isTableView,
"limit": limit,
"curPage": start / limit,
"totalPage": pager(total, limit),
"affected": affected,
"pkIdx": pkIdx,
"curEngine": engine.Name,
"IsLogin": c.IsLogin(),
})
}