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  })