Beispiel #1
0
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
}
Beispiel #2
0
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
}
Beispiel #3
0
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
}
Beispiel #4
0
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
}
Beispiel #5
0
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(),
	})
}