13 // Importing mssql driver package only in dialect file, otherwide not needed
14 _ "github.com/denisenkom/go-mssqldb"
15 "github.com/jinzhu/gorm"
18 func setIdentityInsert(scope *gorm.Scope) {
19 if scope.Dialect().GetName() == "mssql" {
20 for _, field := range scope.PrimaryFields() {
21 if _, ok := field.TagSettingsGet("AUTO_INCREMENT"); ok && !field.IsBlank {
22 scope.NewDB().Exec(fmt.Sprintf("SET IDENTITY_INSERT %v ON", scope.TableName()))
23 scope.InstanceSet("mssql:identity_insert_on", true)
29 func turnOffIdentityInsert(scope *gorm.Scope) {
30 if scope.Dialect().GetName() == "mssql" {
31 if _, ok := scope.InstanceGet("mssql:identity_insert_on"); ok {
32 scope.NewDB().Exec(fmt.Sprintf("SET IDENTITY_INSERT %v OFF", scope.TableName()))
38 gorm.DefaultCallback.Create().After("gorm:begin_transaction").Register("mssql:set_identity_insert", setIdentityInsert)
39 gorm.DefaultCallback.Create().Before("gorm:commit_or_rollback_transaction").Register("mssql:turn_off_identity_insert", turnOffIdentityInsert)
40 gorm.RegisterDialect("mssql", &mssql{})
45 gorm.DefaultForeignKeyNamer
48 func (mssql) GetName() string {
52 func (s *mssql) SetDB(db gorm.SQLCommon) {
56 func (mssql) BindVar(i int) string {
60 func (mssql) Quote(key string) string {
61 return fmt.Sprintf(`[%s]`, key)
64 func (s *mssql) DataTypeOf(field *gorm.StructField) string {
65 var dataValue, sqlType, size, additionalType = gorm.ParseFieldStructForDialect(field, s)
68 switch dataValue.Kind() {
71 case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uintptr:
72 if s.fieldCanAutoIncrement(field) {
73 field.TagSettingsSet("AUTO_INCREMENT", "AUTO_INCREMENT")
74 sqlType = "int IDENTITY(1,1)"
78 case reflect.Int64, reflect.Uint64:
79 if s.fieldCanAutoIncrement(field) {
80 field.TagSettingsSet("AUTO_INCREMENT", "AUTO_INCREMENT")
81 sqlType = "bigint IDENTITY(1,1)"
85 case reflect.Float32, reflect.Float64:
88 if size > 0 && size < 8000 {
89 sqlType = fmt.Sprintf("nvarchar(%d)", size)
91 sqlType = "nvarchar(max)"
94 if _, ok := dataValue.Interface().(time.Time); ok {
95 sqlType = "datetimeoffset"
98 if gorm.IsByteArrayOrSlice(dataValue) {
99 if size > 0 && size < 8000 {
100 sqlType = fmt.Sprintf("varbinary(%d)", size)
102 sqlType = "varbinary(max)"
109 panic(fmt.Sprintf("invalid sql type %s (%s) for mssql", dataValue.Type().Name(), dataValue.Kind().String()))
112 if strings.TrimSpace(additionalType) == "" {
115 return fmt.Sprintf("%v %v", sqlType, additionalType)
118 func (s mssql) fieldCanAutoIncrement(field *gorm.StructField) bool {
119 if value, ok := field.TagSettingsGet("AUTO_INCREMENT"); ok {
120 return value != "FALSE"
122 return field.IsPrimaryKey
125 func (s mssql) HasIndex(tableName string, indexName string) bool {
127 s.db.QueryRow("SELECT count(*) FROM sys.indexes WHERE name=? AND object_id=OBJECT_ID(?)", indexName, tableName).Scan(&count)
131 func (s mssql) RemoveIndex(tableName string, indexName string) error {
132 _, err := s.db.Exec(fmt.Sprintf("DROP INDEX %v ON %v", indexName, s.Quote(tableName)))
136 func (s mssql) HasForeignKey(tableName string, foreignKeyName string) bool {
138 currentDatabase, tableName := currentDatabaseAndTable(&s, tableName)
139 s.db.QueryRow(`SELECT count(*)
140 FROM sys.foreign_keys as F inner join sys.tables as T on F.parent_object_id=T.object_id
141 inner join information_schema.tables as I on I.TABLE_NAME = T.name
143 AND T.Name = ? AND I.TABLE_CATALOG = ?;`, foreignKeyName, tableName, currentDatabase).Scan(&count)
147 func (s mssql) HasTable(tableName string) bool {
149 currentDatabase, tableName := currentDatabaseAndTable(&s, tableName)
150 s.db.QueryRow("SELECT count(*) FROM INFORMATION_SCHEMA.tables WHERE table_name = ? AND table_catalog = ?", tableName, currentDatabase).Scan(&count)
154 func (s mssql) HasColumn(tableName string, columnName string) bool {
156 currentDatabase, tableName := currentDatabaseAndTable(&s, tableName)
157 s.db.QueryRow("SELECT count(*) FROM information_schema.columns WHERE table_catalog = ? AND table_name = ? AND column_name = ?", currentDatabase, tableName, columnName).Scan(&count)
161 func (s mssql) ModifyColumn(tableName string, columnName string, typ string) error {
162 _, err := s.db.Exec(fmt.Sprintf("ALTER TABLE %v ALTER COLUMN %v %v", tableName, columnName, typ))
166 func (s mssql) CurrentDatabase() (name string) {
167 s.db.QueryRow("SELECT DB_NAME() AS [Current Database]").Scan(&name)
171 func (mssql) LimitAndOffsetSQL(limit, offset interface{}) (sql string) {
173 if parsedOffset, err := strconv.ParseInt(fmt.Sprint(offset), 0, 0); err == nil && parsedOffset >= 0 {
174 sql += fmt.Sprintf(" OFFSET %d ROWS", parsedOffset)
178 if parsedLimit, err := strconv.ParseInt(fmt.Sprint(limit), 0, 0); err == nil && parsedLimit >= 0 {
180 // add default zero offset
181 sql += " OFFSET 0 ROWS"
183 sql += fmt.Sprintf(" FETCH NEXT %d ROWS ONLY", parsedLimit)
189 func (mssql) SelectFromDummyTable() string {
193 func (mssql) LastInsertIDReturningSuffix(tableName, columnName string) string {
197 func (mssql) DefaultValueStr() string {
198 return "DEFAULT VALUES"
201 func currentDatabaseAndTable(dialect gorm.Dialect, tableName string) (string, string) {
202 if strings.Contains(tableName, ".") {
203 splitStrings := strings.SplitN(tableName, ".", 2)
204 return splitStrings[0], splitStrings[1]
206 return dialect.CurrentDatabase(), tableName
209 // JSON type to support easy handling of JSON data in character table fields
210 // using golang json.RawMessage for deferred decoding/encoding
215 // Value get value of JSON
216 func (j JSON) Value() (driver.Value, error) {
217 if len(j.RawMessage) == 0 {
220 return j.MarshalJSON()
223 // Scan scan value into JSON
224 func (j *JSON) Scan(value interface{}) error {
225 str, ok := value.(string)
227 return errors.New(fmt.Sprint("Failed to unmarshal JSONB value (strcast):", value))
230 return json.Unmarshal(bytes, j)