github.com/mmatczuk/gohan@v0.0.0-20170206152520-30e45d9bdb69/db/sql/sql_test.go (about) 1 // Copyright (C) 2015 NTT Innovation Institute, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or 12 // implied. 13 // See the License for the specific language governing permissions and 14 // limitations under the License. 15 16 package sql_test 17 18 import ( 19 "fmt" 20 "os" 21 "strings" 22 23 "github.com/cloudwan/gohan/db" 24 . "github.com/cloudwan/gohan/db/sql" 25 "github.com/cloudwan/gohan/db/transaction" 26 "github.com/cloudwan/gohan/schema" 27 28 . "github.com/onsi/ginkgo" 29 . "github.com/onsi/gomega" 30 ) 31 32 var _ = Describe("Sql", func() { 33 34 var conn string 35 var tx transaction.Transaction 36 var sqlConn *DB 37 38 BeforeEach(func() { 39 var dbType string 40 if os.Getenv("MYSQL_TEST") == "true" { 41 conn = "gohan:gohan@/gohan_test" 42 dbType = "mysql" 43 } else { 44 conn = "./test.db" 45 dbType = "sqlite3" 46 } 47 48 manager := schema.GetManager() 49 dbc, err := db.ConnectDB(dbType, conn, db.DefaultMaxOpenConn) 50 sqlConn = dbc.(*DB) 51 Expect(err).ToNot(HaveOccurred()) 52 Expect(manager.LoadSchemasFromFiles( 53 "../../etc/schema/gohan.json", "../../tests/test_abstract_schema.yaml", "../../tests/test_schema.yaml")).To(Succeed()) 54 db.InitDBWithSchemas(dbType, conn, true, false) 55 56 // Insert fixture data 57 fixtureDB, err := db.ConnectDB("json", "test_fixture.json", db.DefaultMaxOpenConn) 58 Expect(err).ToNot(HaveOccurred()) 59 db.CopyDBResources(fixtureDB, dbc, true) 60 61 tx, err = dbc.Begin() 62 Expect(err).ToNot(HaveOccurred()) 63 }) 64 65 AfterEach(func() { 66 schema.ClearManager() 67 if os.Getenv("MYSQL_TEST") != "true" { 68 os.Remove(conn) 69 } 70 }) 71 72 Describe("Query", func() { 73 var s *schema.Schema 74 75 BeforeEach(func() { 76 manager := schema.GetManager() 77 var ok bool 78 s, ok = manager.Schema("test") 79 Expect(ok).To(BeTrue()) 80 }) 81 82 Context("Without place holders", func() { 83 It("Returns resources", func() { 84 query := fmt.Sprintf( 85 "SELECT %s FROM %s", 86 strings.Join(MakeColumns(s, s.GetDbTableName(), false), ", "), 87 s.GetDbTableName(), 88 ) 89 results, err := tx.Query(s, query, []interface{}{}) 90 Expect(err).ToNot(HaveOccurred()) 91 Expect(results[0].Get("tenant_id")).To(Equal("tenant0")) 92 Expect(results[0].Get("test_string")).To(Equal("obj0")) 93 Expect(results[2].Get("tenant_id")).To(Equal("tenant1")) 94 Expect(results[2].Get("test_string")).To(Equal("obj2")) 95 Expect(len(results)).To(Equal(4)) 96 }) 97 }) 98 99 Context("With a place holder", func() { 100 It("Replace the place holder and returns resources", func() { 101 query := fmt.Sprintf( 102 "SELECT %s FROM %s WHERE tenant_id = ?", 103 strings.Join(MakeColumns(s, s.GetDbTableName(), false), ", "), 104 s.GetDbTableName(), 105 ) 106 results, err := tx.Query(s, query, []interface{}{"tenant0"}) 107 Expect(err).ToNot(HaveOccurred()) 108 Expect(results[0].Get("tenant_id")).To(Equal("tenant0")) 109 Expect(results[0].Get("test_string")).To(Equal("obj0")) 110 Expect(results[1].Get("tenant_id")).To(Equal("tenant0")) 111 Expect(results[1].Get("test_string")).To(Equal("obj1")) 112 Expect(len(results)).To(Equal(2)) 113 114 }) 115 }) 116 117 Context("With place holders", func() { 118 It("Replace the place holders and returns resources", func() { 119 query := fmt.Sprintf( 120 "SELECT %s FROM %s WHERE tenant_id = ? AND test_string = ?", 121 strings.Join(MakeColumns(s, s.GetDbTableName(), false), ", "), 122 s.GetDbTableName(), 123 ) 124 results, err := tx.Query(s, query, []interface{}{"tenant0", "obj1"}) 125 Expect(err).ToNot(HaveOccurred()) 126 Expect(results[0].Get("tenant_id")).To(Equal("tenant0")) 127 Expect(results[0].Get("test_string")).To(Equal("obj1")) 128 Expect(len(results)).To(Equal(1)) 129 }) 130 }) 131 }) 132 133 Describe("Generate Table", func() { 134 var server *schema.Schema 135 var subnet *schema.Schema 136 var test *schema.Schema 137 138 BeforeEach(func() { 139 manager := schema.GetManager() 140 var ok bool 141 server, ok = manager.Schema("server") 142 Expect(ok).To(BeTrue()) 143 subnet, ok = manager.Schema("subnet") 144 Expect(ok).To(BeTrue()) 145 test, ok = manager.Schema("test") 146 Expect(ok).To(BeTrue()) 147 }) 148 149 Context("Index in schema", func() { 150 It("Should create index, if schema property should be indexed", func() { 151 _, indices := sqlConn.GenTableDef(test, false) 152 Expect(indices).To(HaveLen(1)) 153 Expect(indices[0]).To(ContainSubstring("CREATE INDEX tests_tenant_id_idx ON `tests`(`tenant_id`(255));")) 154 }) 155 }) 156 157 Context("Relation column name", func() { 158 It("Generate foreign key with default column name when relationColumn not available", func() { 159 table, _ := sqlConn.GenTableDef(server, false) 160 Expect(table).To(ContainSubstring("REFERENCES `networks`(id)")) 161 }) 162 163 It("Generate foreign key with given column same as relationColumn from property", func() { 164 server.Properties = append(server.Properties, schema.NewProperty( 165 "test", 166 "test", 167 "", 168 "test", 169 "string", 170 "subnet", 171 "cidr", 172 "", 173 "varchar(255)", 174 false, 175 false, 176 false, 177 nil, 178 nil, 179 false, 180 )) 181 table, _, err := sqlConn.AlterTableDef(server, false) 182 Expect(err).ToNot(HaveOccurred()) 183 Expect(table).To(ContainSubstring("REFERENCES `subnets`(cidr)")) 184 }) 185 }) 186 187 Context("With default cascade option", func() { 188 It("Generate proper table with cascade delete", func() { 189 table, _ := sqlConn.GenTableDef(server, true) 190 Expect(table).To(ContainSubstring("REFERENCES `networks`(id) on delete cascade);")) 191 table, _ = sqlConn.GenTableDef(subnet, true) 192 Expect(table).To(ContainSubstring("REFERENCES `networks`(id) on delete cascade);")) 193 }) 194 }) 195 196 Context("Without default cascade option", func() { 197 It("Generate proper table with cascade delete", func() { 198 table, _ := sqlConn.GenTableDef(server, false) 199 Expect(table).To(ContainSubstring("REFERENCES `networks`(id) on delete cascade);")) 200 table, _ = sqlConn.GenTableDef(subnet, false) 201 Expect(table).ToNot(ContainSubstring("REFERENCES `networks`(id) on delete cascade);")) 202 }) 203 }) 204 205 Context("Properties modifed", func() { 206 It("Generate proper alter table statements", func() { 207 server.Properties = append(server.Properties, schema.NewProperty( 208 "test", 209 "test", 210 "", 211 "test", 212 "string", 213 "", 214 "", 215 "", 216 "varchar(255)", 217 false, 218 false, 219 false, 220 nil, 221 nil, 222 false, 223 )) 224 table, _, err := sqlConn.AlterTableDef(server, true) 225 Expect(err).ToNot(HaveOccurred()) 226 Expect(table).To(ContainSubstring("alter table`servers` add (`test` varchar(255));")) 227 }) 228 229 It("Create index if property should be indexed", func() { 230 server.Properties = append(server.Properties, schema.NewProperty( 231 "test", 232 "test", 233 "", 234 "test", 235 "string", 236 "", 237 "", 238 "", 239 "varchar(255)", 240 false, 241 false, 242 false, 243 nil, 244 nil, 245 true, 246 )) 247 _, indices, err := sqlConn.AlterTableDef(server, true) 248 Expect(err).ToNot(HaveOccurred()) 249 Expect(indices).To(HaveLen(1)) 250 Expect(indices[0]).To(ContainSubstring("CREATE INDEX servers_test_idx ON `servers`(`test`);")) 251 }) 252 }) 253 }) 254 })