forked from skeema/skeema
-
Notifications
You must be signed in to change notification settings - Fork 0
/
skeema_cmd_test.go
1742 lines (1550 loc) · 87 KB
/
skeema_cmd_test.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
package main
import (
"fmt"
"os"
"runtime"
"strings"
"testing"
log "github.com/sirupsen/logrus"
"github.com/skeema/mybase"
"github.com/skeema/skeema/internal/fs"
"github.com/skeema/skeema/internal/tengo"
)
func (s SkeemaIntegrationSuite) TestInitHandler(t *testing.T) {
s.handleCommand(t, CodeBadConfig, ".", "skeema init") // no host
// Invalid environment name
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir mydb -h %s -P %d '[nope]'", s.d.Instance.Host, s.d.Instance.Port)
// Specifying a single schema that doesn't exist on the instance
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir mydb -h %s -P %d --schema doesntexist", s.d.Instance.Host, s.d.Instance.Port)
// Specifying a single schema that is a system schema, regardless of case
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir mydb -h %s -P %d --schema mysql", s.d.Instance.Host, s.d.Instance.Port)
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir mydb -h %s -P %d --schema InFoRMaTiOn_ScHeMa", s.d.Instance.Host, s.d.Instance.Port)
// Successful standard execution. Also confirm user is not persisted to .skeema
// since not specified on CLI.
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
s.verifyFiles(t, cfg, "../golden/init")
if _, setsOption := getOptionFile(t, "mydb", cfg).OptionValue("user"); setsOption {
t.Error("Did not expect user to be persisted to .skeema, but it was")
}
// Specifying an unreachable host should fail with fatal error
s.handleCommand(t, CodeFatalError, ".", "skeema init --dir baddb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port-100)
// host-wrapper with no output should fail
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir baddb -h xyz --host-wrapper='echo \" \"'")
// Test successful init with --user specified on CLI, persisting to .skeema
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema init --dir withuser -h %s -P %d --user root", s.d.Instance.Host, s.d.Instance.Port)
if _, setsOption := getOptionFile(t, "withuser", cfg).OptionValue("user"); !setsOption {
t.Error("Expected user to be persisted to .skeema, but it was not")
}
// Can't init into a dir with existing option file
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Can't init off of base dir that already specifies a schema
s.handleCommand(t, CodeBadConfig, "mydb/product", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Test successful init for a single schema. Source a SQL file first that,
// among other things, changes the default charset and collation for the
// schema in question.
s.sourceSQL(t, "push1.sql")
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema init --dir combined -h %s -P %d --schema product ", s.d.Instance.Host, s.d.Instance.Port)
dir, err := fs.ParseDir("combined", cfg)
if err != nil {
t.Fatalf("Unexpected error from ParseDir: %s", err)
}
mybase.AssertFileSetsOptions(t, dir.OptionFile, "host", "schema", "default-character-set", "default-collation")
if subdirs, err := dir.Subdirs(); err != nil {
t.Fatalf("Unexpected error listing subdirs of %s: %v", dir, err)
} else if len(subdirs) > 0 {
t.Errorf("Expected %s to have no subdirs, but it has %d", dir, len(subdirs))
} else {
for _, sub := range subdirs {
if sub.ParseError != nil {
t.Errorf("Unexpected parse error in %s: %v", sub, sub.ParseError)
}
}
}
if len(dir.SQLFiles) < 1 {
t.Errorf("Expected %s to have *.sql files, but it does not", dir)
}
// Test successful init without a --dir. Also test persistence of --connect-options.
expectDir := fs.HostDefaultDirName(s.d.Instance.Host, s.d.Instance.Port)
if _, err = os.Stat(expectDir); err == nil {
t.Fatalf("Expected dir %s to not exist yet, but it does", expectDir)
}
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema init -h %s -P %d --connect-options='wait_timeout=3'", s.d.Instance.Host, s.d.Instance.Port)
if dir, err = fs.ParseDir(expectDir, cfg); err != nil {
t.Fatalf("Unexpected error from ParseDir: %s", err)
}
mybase.AssertFileSetsOptions(t, dir.OptionFile, "host", "port", "connect-options")
mybase.AssertFileMissingOptions(t, dir.OptionFile, "schema", "default-character-set", "default-collation")
// init should fail if a parent dir (or working directory) has an invalid .skeema file
fs.MakeTestDirectory(t, "hasbadoptions")
fs.WriteTestFile(t, "hasbadoptions/.skeema", "invalid file will not parse")
s.handleCommand(t, CodeBadConfig, "hasbadoptions", "skeema init -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// init should fail in any of various permutations where a non-directory file
// is present where a dir needs to be created; or if an existing dir contains
// a .skeema file and/or one or more *.sql files
fs.WriteTestFile(t, "nondir", "foo bar")
s.handleCommand(t, CodeCantCreate, ".", "skeema init --dir nondir -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
fs.WriteTestFile(t, "okdir/product", "foo bar")
s.handleCommand(t, CodeCantCreate, ".", "skeema init --dir okdir -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
fs.WriteTestFile(t, "alreadyexists/product/.skeema", "schema=product\n")
s.handleCommand(t, CodeCantCreate, ".", "skeema init --dir alreadyexists -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
fs.WriteTestFile(t, "alreadyexists2/product/foo.sql", "CREATE TABLE foo (id int);\n")
s.handleCommand(t, CodeCantCreate, ".", "skeema init --dir alreadyexists2 -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
fs.WriteTestFile(t, "hassql/foo.sql", "foo")
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir hassql --schema product -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
fs.WriteTestFile(t, "hasoptionfile/.skeema", "# comment")
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir hasoptionfile --schema product -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
}
func (s SkeemaIntegrationSuite) TestAddEnvHandler(t *testing.T) {
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// add-environment should fail on a dir that does not exist
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --host my.staging.db.com --dir does/not/exist staging")
// add-environment should fail on a dir that does not already contain a .skeema file
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --host my.staging.db.com staging")
// bad environment name should fail
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --host my.staging.db.com --dir mydb '[staging]'")
// preexisting environment name should fail
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --host my.staging.db.com --dir mydb production")
// non-host-level directory should fail
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --host my.staging.db.com --dir mydb/product staging")
// lack of host on CLI should fail
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --dir mydb staging")
// None of the above failed commands should have modified any files
s.verifyFiles(t, cfg, "../golden/init")
origFile := getOptionFile(t, "mydb", cfg)
// valid dir should succeed and add the section to the .skeema file
// Intentionally using a low connection timeout here to avoid delaying the
// test with the invalid hostname
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema add-environment --host my.staging.invalid --dir mydb staging --connect-options='timeout=10ms'")
file := getOptionFile(t, "mydb", cfg)
origFile.SetOptionValue("staging", "host", "my.staging.invalid")
origFile.SetOptionValue("staging", "port", "3306")
origFile.SetOptionValue("staging", "connect-options", "timeout=10ms")
if !origFile.SameContents(file) {
t.Fatalf("File contents of %s do not match expectation", file.Path())
}
// Nonstandard port should work properly; ditto for user option persisting
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema add-environment --host my.ci.invalid -P 3307 -ufoobar --dir mydb ci --connect-options='timeout=10ms'")
file = getOptionFile(t, "mydb", cfg)
origFile.SetOptionValue("ci", "host", "my.ci.invalid")
origFile.SetOptionValue("ci", "port", "3307")
origFile.SetOptionValue("ci", "user", "foobar")
origFile.SetOptionValue("ci", "connect-options", "timeout=10ms")
if !origFile.SameContents(file) {
t.Fatalf("File contents of %s do not match expectation", file.Path())
}
// localhost and socket should work properly
s.handleCommand(t, CodeSuccess, ".", "skeema add-environment -h localhost -S /var/lib/mysql/mysql.sock --dir mydb development")
file = getOptionFile(t, "mydb", cfg)
origFile.SetOptionValue("development", "host", "localhost")
origFile.SetOptionValue("development", "socket", "/var/lib/mysql/mysql.sock")
if !origFile.SameContents(file) {
t.Fatalf("File contents of %s do not match expectation", file.Path())
}
// valid instance should work properly and even populate flavor. Also confirm
// persistence of ignore-schema and ignore-table.
cfg = s.handleCommand(t, CodeSuccess, "mydb", "skeema add-environment --ignore-schema='^test' --ignore-table='^_' --host %s:%d cloud", s.d.Instance.Host, s.d.Instance.Port)
file = getOptionFile(t, "mydb", cfg)
origFile.SetOptionValue("cloud", "host", s.d.Instance.Host)
origFile.SetOptionValue("cloud", "port", fmt.Sprintf("%d", s.d.Instance.Port))
origFile.SetOptionValue("cloud", "ignore-schema", "^test")
origFile.SetOptionValue("cloud", "ignore-table", "^_")
origFile.SetOptionValue("cloud", "flavor", s.d.Flavor().Family().String())
if !origFile.SameContents(file) {
t.Fatalf("File contents of %s do not match expectation", file.Path())
}
}
func (s SkeemaIntegrationSuite) TestPullHandler(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// In product db, alter one table and drop one table;
// In analytics db, add one table and alter the schema's charset and collation;
// Create a new db and put one table in it
s.sourceSQL(t, "pull1.sql")
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.verifyFiles(t, cfg, "../golden/pull1")
// Revert db back to previous state, and pull again to test the opposite
// behaviors: delete dir for new schema, restore charset/collation in .skeema,
// etc. Also edit the host .skeema file to remove flavor, to test logic that
// adds/updates flavor on pull.
s.cleanData(t, "setup.sql")
fs.WriteTestFile(t, "mydb/.skeema", strings.Replace(fs.ReadTestFile(t, "mydb/.skeema"), "flavor", "#flavor", 1))
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.verifyFiles(t, cfg, "../golden/init")
// Files with invalid SQL should still be corrected upon pull. Files with
// nonstandard formatting of their CREATE TABLE should be normalized, even if
// there was an ignored auto-increment change. Files with extraneous text
// before/after the CREATE TABLE should remain as-is, regardless of whether
// there were other changes triggering a file rewrite. Files containing
// commands plus a table that doesn't exist should be deleted, instead of
// leaving a file with lingering commands. Generator string should be updated.
contents := fs.ReadTestFile(t, "mydb/analytics/activity.sql")
fs.WriteTestFile(t, "mydb/analytics/activity.sql", strings.Replace(contents, "DEFAULT", "DEFALUT", 1))
s.dbExec(t, "product", "INSERT INTO comments (post_id, user_id) VALUES (555, 777)")
contents = fs.ReadTestFile(t, "mydb/product/comments.sql")
fs.WriteTestFile(t, "mydb/product/comments.sql", strings.Replace(contents, "`", "", -1))
contents = fs.ReadTestFile(t, "mydb/product/posts.sql")
fs.WriteTestFile(t, "mydb/product/posts.sql", fmt.Sprintf("# random comment\n%s", contents))
fs.WriteTestFile(t, "mydb/product/noexist.sql", "DELIMITER //\nCREATE TABLE noexist (id int)//\nDELIMITER ;\n")
contents = fs.ReadTestFile(t, "mydb/.skeema")
fs.WriteTestFile(t, "mydb/.skeema", strings.Replace(contents, generatorString(), "skeema:1.4.7-community", 1))
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema pull --debug")
s.verifyFiles(t, cfg, "../golden/init")
contents = fs.ReadTestFile(t, "mydb/product/posts.sql")
if !strings.Contains(contents, "# random comment") {
t.Error("Expected mydb/product/posts.sql to retain its extraneous comment, but it was removed")
}
fs.WriteTestFile(t, "mydb/product/posts.sql", strings.Replace(contents, "`", "", -1))
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema pull --debug")
s.verifyFiles(t, cfg, "../golden/init")
if !strings.Contains(contents, "# random comment") {
t.Error("Expected mydb/product/posts.sql to retain its extraneous comment, but it was removed")
}
// Test behavior with --skip-new-schemas: new schema should not have a dir in
// fs, but changes to existing schemas should still be made
s.sourceSQL(t, "pull1.sql")
s.handleCommand(t, CodeSuccess, ".", "skeema pull --skip-new-schemas")
if _, err := os.Stat("mydb/archives"); !os.IsNotExist(err) {
t.Errorf("Expected os.Stat to return IsNotExist error for mydb/archives; instead err=%v", err)
}
if _, err := os.Stat("mydb/analytics/widget_counts.sql"); err != nil {
t.Errorf("Expected os.Stat to return nil error for mydb/analytics/widget_counts.sql; instead err=%v", err)
}
// If a dir has a bad option file, new schema detection should also be skipped,
// since we don't know what schemas the bad subdir maps to
fs.WriteTestFile(t, "mydb/analytics/.skeema", "this won't parse anymore")
s.handleCommand(t, CodePartialError, ".", "skeema pull")
if _, err := os.Stat("mydb/archives"); !os.IsNotExist(err) {
t.Errorf("Expected os.Stat to return IsNotExist error for mydb/archives; instead err=%v", err)
}
// Start over; Bad option file in a non-leaf dir should yield CodeBadConfig
// and no files should be updated
s.cleanData(t, "setup.sql")
s.reinitAndVerifyFiles(t, "", "")
origMydbConfig := fs.ReadTestFile(t, "mydb/.skeema")
fs.WriteTestFile(t, "mydb/.skeema", origMydbConfig+"\nbad config here")
contents = fs.ReadTestFile(t, "mydb/analytics/activity.sql")
fs.WriteTestFile(t, "mydb/analytics/activity.sql", strings.Replace(contents, "DEFAULT", "DEFALUT", 1))
s.handleCommand(t, CodeBadConfig, ".", "skeema pull")
if contents = fs.ReadTestFile(t, "mydb/analytics/activity.sql"); !strings.Contains(contents, "DEFALUT") {
t.Error("Unexpected behavior from pull with a non-leaf parse error")
}
// Ditto if non-leaf option file is valid but contains a problematic host list
// (but CodePartialError this time)
fs.WriteTestFile(t, "mydb/.skeema", origMydbConfig+"\nhost-wrapper=invalid-binary")
s.handleCommand(t, CodePartialError, ".", "skeema pull")
if contents = fs.ReadTestFile(t, "mydb/analytics/activity.sql"); !strings.Contains(contents, "DEFALUT") {
t.Error("Unexpected behavior from pull with a non-leaf parse error")
}
// Test pull behavior on a "flat" layout (single dir defining host and schema):
// ensure flavor updated; ensure deleted sql file brought back
s.handleCommand(t, CodeSuccess, ".", "skeema init --schema product --dir flat -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
fs.RemoveTestFile(t, "flat/users.sql")
fs.WriteTestFile(t, "flat/.skeema", strings.Replace(fs.ReadTestFile(t, "flat/.skeema"), "flavor", "####", 1))
s.handleCommand(t, CodeSuccess, "flat", "skeema pull")
if _, err := os.Stat("flat/users.sql"); err != nil {
t.Errorf("Expected os.Stat to return nil error for flat/users.sql; instead err=%v", err)
}
if contents := fs.ReadTestFile(t, "flat/.skeema"); !strings.Contains(contents, "flavor") {
t.Error("Expected flat/.skeema to contain flavor after pull, but it does not")
}
}
func (s SkeemaIntegrationSuite) TestLintHandler(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Initial lint should be a no-op that returns exit code 0
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema lint")
s.verifyFiles(t, cfg, "../golden/init")
// Invalid options should error with CodeBadConfig
s.handleCommand(t, CodeBadConfig, ".", "skeema lint --workspace=doesnt-exist")
s.handleCommand(t, CodeBadConfig, "mydb/product", "skeema lint --password=wrong")
// Alter a few files in a way that is still valid SQL, but doesn't match
// the database's native format. Lint with --skip-format should do nothing;
// otherwise lint with default of format should rewrite these files and then
// return exit code CodeDifferencesFound.
productDir, err := fs.ParseDir("mydb/product", cfg)
if err != nil {
t.Fatalf("Unable to obtain dir for mydb/product: %s", err)
}
if len(productDir.SQLFiles) < 4 {
t.Fatalf("Unable to obtain *.sql files from %s", productDir)
}
var commentsFilePath string
rewriteFiles := func(includeSyntaxError bool) {
for _, sf := range productDir.SQLFiles {
contents := fs.ReadTestFile(t, sf.FilePath)
switch sf.FileName() {
case "comments.sql":
commentsFilePath = sf.FilePath
if includeSyntaxError {
contents = strings.Replace(contents, "DEFAULT", "DEFALUT", 1)
}
case "posts.sql":
contents = strings.ToLower(contents)
case "subscriptions.sql":
contents = strings.Replace(contents, "`", "", -1)
case "users.sql":
contents = strings.Replace(contents, " ", " ", -1)
}
fs.WriteTestFile(t, sf.FilePath, contents)
}
}
rewriteFiles(false)
s.handleCommand(t, CodeSuccess, ".", "skeema lint --skip-format")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema lint")
s.verifyFiles(t, cfg, "../golden/init")
// Add a new file with invalid SQL, and also make the previous valid rewrites.
// Lint should rewrite the valid files but return exit code CodeFatalError due
// to there being at least 1 file with invalid SQL.
rewriteFiles(true)
s.handleCommand(t, CodeFatalError, ".", "skeema lint")
// Manually restore the file with invalid SQL; the files should now verify,
// confirming that the fatal error did not prevent the other files from being
// reformatted; re-linting should yield no changes.
contents := fs.ReadTestFile(t, commentsFilePath)
fs.WriteTestFile(t, commentsFilePath, strings.Replace(contents, "DEFALUT", "DEFAULT", 1))
s.verifyFiles(t, cfg, "../golden/init")
s.handleCommand(t, CodeSuccess, ".", "skeema lint")
// Files with SQL statements unsupported by this package should yield a
// warning, resulting in CodeDifferencesFound
fs.WriteTestFile(t, commentsFilePath, "CALL some_proc(123, 234)")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema lint")
// Directories that have invalid options should yield CodeBadConfig
fs.WriteTestFile(t, "mydb/uhoh/.skeema", "this is not a valid .skeema file")
s.handleCommand(t, CodeBadConfig, ".", "skeema lint")
}
func (s SkeemaIntegrationSuite) TestFormatHandler(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Initial format should be a no-op that returns exit code 0
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema format")
s.verifyFiles(t, cfg, "../golden/init")
// Invalid options should error with CodeBadConfig
s.handleCommand(t, CodeBadConfig, ".", "skeema format --workspace=doesnt-exist")
s.handleCommand(t, CodeBadConfig, "mydb/product", "skeema format --password=wrong")
// Alter a few files in a way that is still valid SQL, but doesn't match
// the database's native format. Format with --skip-write should return exit
// CodeDifferencesFound repeatedly; format with default (--write) should return
// CodeDifferencesFound followed by CodeSuccess.
productDir, err := fs.ParseDir("mydb/product", cfg)
if err != nil {
t.Fatalf("Unable to obtain dir for mydb/product: %s", err)
}
if len(productDir.SQLFiles) < 4 {
t.Fatalf("Unable to obtain *.sql files from %s", productDir)
}
var commentsFilePath string
rewriteFiles := func(includeSyntaxError bool) {
for _, sf := range productDir.SQLFiles {
contents := fs.ReadTestFile(t, sf.FilePath)
switch sf.FileName() {
case "comments.sql":
commentsFilePath = sf.FilePath
if includeSyntaxError {
contents = strings.Replace(contents, "DEFAULT", "DEFALUT", 1)
}
case "posts.sql":
contents = strings.ToLower(contents)
case "subscriptions.sql":
contents = strings.Replace(contents, "`", "", -1)
case "users.sql":
contents = strings.Replace(contents, " ", " ", -1)
}
fs.WriteTestFile(t, sf.FilePath, contents)
}
}
rewriteFiles(false)
s.handleCommand(t, CodeDifferencesFound, ".", "skeema format --skip-write")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema format --skip-write")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema format")
s.handleCommand(t, CodeSuccess, ".", "skeema format")
s.verifyFiles(t, cfg, "../golden/init")
// Change a file to contain invalid SQL; format should return
// CodeDifferencesFound repeatedly
rewriteFiles(true)
s.handleCommand(t, CodeDifferencesFound, ".", "skeema format")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema format")
// Manually restore the file with invalid SQL; the files should now verify,
// confirming that the fatal error did not prevent the other files from being
// reformatted; re-formatting again should yield no changes.
contents := fs.ReadTestFile(t, commentsFilePath)
fs.WriteTestFile(t, commentsFilePath, strings.Replace(contents, "DEFALUT", "DEFAULT", 1))
s.verifyFiles(t, cfg, "../golden/init")
s.handleCommand(t, CodeSuccess, ".", "skeema format")
// Files with SQL statements unsupported by this package should not affect
// exit code
fs.WriteTestFile(t, commentsFilePath, "CALL some_proc(123, 234)")
s.handleCommand(t, CodeSuccess, ".", "skeema format --debug")
// Directories that have invalid options should yield CodeBadConfig
fs.WriteTestFile(t, "mydb/uhoh/.skeema", "this is not a valid .skeema file")
s.handleCommand(t, CodeBadConfig, ".", "skeema format")
}
func (s SkeemaIntegrationSuite) TestDiffHandler(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// no-op diff should yield no differences
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// --host and --schema should error if supplied on CLI
s.handleCommand(t, CodeBadConfig, ".", "skeema diff --host=1.2.3.4 --schema=whatever")
// It isn't possible to disable --dry-run with diff
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema diff --skip-dry-run")
if !cfg.GetBool("dry-run") {
t.Error("Expected --skip-dry-run to have no effect on `skeema diff`, but it disabled dry-run")
}
s.dbExec(t, "analytics", "ALTER TABLE pageviews DROP COLUMN domain")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff")
// Confirm --brief works as expected
defer func() {
// --brief manipulates the log level, so we must restore it after
log.SetLevel(log.DebugLevel)
}()
oldStdout := os.Stdout
if outFile, err := os.Create("diff-brief.out"); err != nil {
t.Fatalf("Unable to redirect stdout to a file: %s", err)
} else {
os.Stdout = outFile
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff --brief")
outFile.Close()
os.Stdout = oldStdout
expectOut := fmt.Sprintf("%s\n", s.d.Instance)
actualOut := fs.ReadTestFile(t, "diff-brief.out")
if actualOut != expectOut {
t.Errorf("Unexpected output from `skeema diff --brief`\nExpected:\n%sActual:\n%s", expectOut, actualOut)
}
if err := os.Remove("diff-brief.out"); err != nil {
t.Fatalf("Unable to delete diff-brief.out: %s", err)
}
}
}
func (s SkeemaIntegrationSuite) TestPushHandler(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Verify clean-slate operation: wipe the DB; push; wipe the files; re-init
// the files; verify the files match. The push inherently verifies creation of
// schemas and tables.
s.cleanData(t)
s.handleCommand(t, CodeSuccess, ".", "skeema push")
s.reinitAndVerifyFiles(t, "", "")
// Test bad option values
s.handleCommand(t, CodeBadConfig, ".", "skeema push --concurrent-instances=0")
s.handleCommand(t, CodeBadConfig, ".", "skeema push --alter-algorithm=invalid")
s.handleCommand(t, CodeBadConfig, ".", "skeema push --alter-lock=invalid")
s.handleCommand(t, CodeBadConfig, ".", "skeema push --ignore-table='+'")
s.handleCommand(t, CodeBadConfig, ".", "skeema push --lint-charset=gentle-nudge")
// Make some changes on the db side, mix of safe and unsafe changes to
// multiple schemas. Remember, subsequent pushes will effectively be UN-DOING
// what push1.sql did, since we updated the db but not the filesystem.
s.sourceSQL(t, "push1.sql")
// push from base dir, without any args, should succeed for schemas with safe
// changes (analytics) but not for schemas with 1 or more unsafe changes
// (product). It shouldn't not affect the `bonus` schema (which exists on db
// but not on filesystem, but push should never drop schemas)
s.handleCommand(t, CodeFatalError, "", "skeema push") // CodeFatalError due to unsafe changes not being allowed
s.handleCommand(t, CodeSuccess, "mydb/analytics", "skeema diff") // analytics dir was pushed fine tho
s.assertTableExists(t, "analytics", "pageviews", "") // re-created by push
s.assertTableMissing(t, "product", "users", "credits") // product DDL skipped due to unsafe stmt
s.assertTableExists(t, "product", "posts", "featured") // product DDL skipped due to unsafe stmt
s.assertTableExists(t, "bonus", "placeholder", "") // not affected by push (never drops schemas)
// The "skip whole schema upon unsafe stmt" rule also affects schema-level DDL
if product, err := s.d.Schema("product"); err != nil || product == nil {
t.Fatalf("Unexpected error obtaining schema: %s", err)
} else {
expectCharSet, expectCollation := "utf8", "utf8_swedish_ci"
mysql8029 := tengo.FlavorMySQL80.Dot(29)
if flavor := s.d.Flavor(); flavor.Min(mysql8029) || flavor.Min(tengo.FlavorMariaDB106) {
expectCharSet = "utf8mb3"
if !flavor.Matches(mysql8029) { // MySQL (or variants) of *exactly* 8.0.29 does not update collation names (but 8.0.30+ does)
expectCollation = "utf8mb3_swedish_ci"
}
}
if product.CharSet != expectCharSet || product.Collation != expectCollation {
t.Errorf("Expected schema should have charset/collation=%s/%s from push1.sql, instead found %s/%s", expectCharSet, expectCollation, product.CharSet, product.Collation)
}
}
// Delete *.sql file for analytics.rollups. Push from analytics dir with
// --safe-below-size=1 should fail since it has a row. Delete that row and
// try again, should succeed that time.
if err := os.Remove("mydb/analytics/rollups.sql"); err != nil {
t.Fatalf("Unexpected error removing a file: %s", err)
}
s.handleCommand(t, CodeFatalError, "mydb/analytics", "skeema push --safe-below-size=1")
s.assertTableExists(t, "analytics", "rollups", "")
s.dbExec(t, "analytics", "DELETE FROM rollups")
s.handleCommand(t, CodeSuccess, "mydb/analytics", "skeema push --safe-below-size=1")
s.assertTableMissing(t, "analytics", "rollups", "")
// push from base dir, with --allow-unsafe, will permit the changes to product
// schema to proceed
s.handleCommand(t, CodeSuccess, ".", "skeema push --allow-unsafe")
s.assertTableMissing(t, "product", "posts", "featured")
s.assertTableExists(t, "product", "users", "credits")
s.assertTableExists(t, "bonus", "placeholder", "")
if product, err := s.d.Schema("product"); err != nil || product == nil {
t.Fatalf("Unexpected error obtaining schema: %s", err)
} else {
serverCharSet, serverCollation, err := s.d.DefaultCharSetAndCollation()
if err != nil {
t.Fatalf("Unable to obtain server default charset and collation: %s", err)
}
if serverCharSet != product.CharSet || serverCollation != product.Collation {
t.Errorf("Expected schema should have charset/collation=%s/%s, instead found %s/%s", serverCharSet, serverCollation, product.CharSet, product.Collation)
}
}
// invalid SQL prevents push from working in an entire dir, but not in a
// dir for a different schema
contents := fs.ReadTestFile(t, "mydb/product/comments.sql")
fs.WriteTestFile(t, "mydb/product/comments.sql", strings.Replace(contents, "PRIMARY KEY", "foo int,\nPRIMARY KEY", 1))
contents = fs.ReadTestFile(t, "mydb/product/users.sql")
fs.WriteTestFile(t, "mydb/product/users.sql", strings.Replace(contents, "PRIMARY KEY", "foo int INVALID SQL HERE,\nPRIMARY KEY", 1))
fs.WriteTestFile(t, "mydb/bonus/.skeema", "schema=bonus\n")
fs.WriteTestFile(t, "mydb/bonus/placeholder.sql", "CREATE TABLE placeholder (id int unsigned NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB")
fs.WriteTestFile(t, "mydb/bonus/table2.sql", "CREATE TABLE table2 (name varchar(20) NOT NULL, PRIMARY KEY (name))")
s.handleCommand(t, CodeFatalError, ".", "skeema push")
s.assertTableMissing(t, "product", "comments", "foo")
s.assertTableMissing(t, "product", "users", "foo")
s.assertTableExists(t, "bonus", "table2", "")
// confirm that lint errors (in modified objects only) prevent push:
// drop a PK from a table in bonus schema in the db;
// pull to restore valid filesystem state after prev test;
// remove PKs from 2 tables in the filesystem in product dir;
// add a col to a different table in bonus schema;
// try pushing and confirm no changes are made in product schema (due to
// lint failure), but bonus change proceeds (since the PK-less table there was
// not modified in this diff)
s.dbExec(t, "bonus", "ALTER TABLE placeholder DROP PRIMARY KEY")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
fs.WriteTestFile(t, "mydb/bonus/table2.sql", "CREATE TABLE table2 (name varchar(20) NOT NULL, newcol int, PRIMARY KEY (name))")
contents = fs.ReadTestFile(t, "mydb/product/users.sql")
fs.WriteTestFile(t, "mydb/product/users.sql", strings.Replace(contents, "PRIMARY KEY", "KEY", 1))
contents = fs.ReadTestFile(t, "mydb/product/posts.sql")
fs.WriteTestFile(t, "mydb/product/posts.sql", strings.Replace(contents, "PRIMARY KEY", "KEY", 1))
s.handleCommand(t, CodeFatalError, ".", "skeema push --lint-pk=error")
s.assertTableExists(t, "bonus", "table2", "newcol")
s.handleCommand(t, CodeDifferencesFound, "mydb/product", "skeema diff")
// Confirm behavior of --skip-lint even with --lint-pk=error
s.handleCommand(t, CodeSuccess, ".", "skeema push --lint-pk=error --skip-lint")
s.handleCommand(t, CodeSuccess, ".", "skeema diff --lint-pk=error")
}
func (s SkeemaIntegrationSuite) TestHelpHandler(t *testing.T) {
// Simple tests just to confirm the commands don't error
fs.WriteTestFile(t, "fake-etc/skeema", "# hello world")
s.handleCommand(t, CodeSuccess, ".", "skeema")
s.handleCommand(t, CodeSuccess, ".", "skeema help")
s.handleCommand(t, CodeSuccess, ".", "skeema --help")
s.handleCommand(t, CodeSuccess, ".", "skeema --help=add-environment")
s.handleCommand(t, CodeSuccess, ".", "skeema help add-environment")
s.handleCommand(t, CodeSuccess, ".", "skeema add-environment --help")
s.handleCommand(t, CodeFatalError, ".", "skeema help doesntexist")
s.handleCommand(t, CodeFatalError, ".", "skeema --help=doesntexist")
}
func (s SkeemaIntegrationSuite) TestIndexOrdering(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Add 6 new redundant indexes to posts.sql. Place them before the existing
// secondary index.
contentsOrig := fs.ReadTestFile(t, "mydb/product/posts.sql")
lines := make([]string, 6)
for n := range lines {
lines[n] = fmt.Sprintf("KEY `idxnew_%d` (`created_at`)", n)
}
joinedLines := strings.Join(lines, ",\n ")
contentsIndexesFirst := strings.Replace(contentsOrig, "PRIMARY KEY (`id`),\n", fmt.Sprintf("PRIMARY KEY (`id`),\n %s,\n", joinedLines), 1)
fs.WriteTestFile(t, "mydb/product/posts.sql", contentsIndexesFirst)
// push should add the indexes, and afterwards diff should report no
// differences, even though the index order in the file differs from what is
// in mysql
s.handleCommand(t, CodeSuccess, "", "skeema push")
s.handleCommand(t, CodeSuccess, "", "skeema diff")
// however, diff --exact-match can see the differences
s.handleCommand(t, CodeDifferencesFound, "", "skeema diff --exact-match")
// pull should re-write the file such that the indexes are now last, just like
// what's actually in mysql
s.handleCommand(t, CodeSuccess, "", "skeema pull")
contentsIndexesLast := strings.Replace(contentsOrig, ")\n", fmt.Sprintf("),\n %s\n", joinedLines), 1)
if fileContents := fs.ReadTestFile(t, "mydb/product/posts.sql"); fileContents == contentsIndexesFirst {
t.Error("Expected skeema pull to rewrite mydb/product/posts.sql to put indexes last, but file remained unchanged")
} else if fileContents != contentsIndexesLast {
t.Errorf("Expected skeema pull to rewrite mydb/product/posts.sql to put indexes last, but it did something else entirely. Contents:\n%s\nExpected:\n%s\n", fileContents, contentsIndexesLast)
}
// Edit posts.sql to put the new indexes first again, and ensure
// push --exact-match actually reorders them.
fs.WriteTestFile(t, "mydb/product/posts.sql", contentsIndexesFirst)
if s.d.Flavor().Matches(tengo.FlavorMySQL55) {
s.handleCommand(t, CodeSuccess, "", "skeema push --exact-match")
} else {
s.handleCommand(t, CodeSuccess, "", "skeema push --exact-match --alter-algorithm=copy")
}
s.handleCommand(t, CodeSuccess, "", "skeema diff")
s.handleCommand(t, CodeSuccess, "", "skeema diff --exact-match")
s.handleCommand(t, CodeSuccess, "", "skeema pull")
if fileContents := fs.ReadTestFile(t, "mydb/product/posts.sql"); fileContents != contentsIndexesFirst {
t.Errorf("Expected skeema pull to have no effect at this point, but instead file now looks like this:\n%s", fileContents)
}
}
func (s SkeemaIntegrationSuite) TestForeignKeys(t *testing.T) {
s.sourceSQL(t, "foreignkey.sql")
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Renaming an FK should not be considered a difference by default
oldContents := fs.ReadTestFile(t, "mydb/product/posts.sql")
contents1 := strings.Replace(oldContents, "user_fk", "usridfk", 1)
if oldContents == contents1 {
t.Fatal("Expected mydb/product/posts.sql to contain foreign key definition, but it did not")
}
fs.WriteTestFile(t, "mydb/product/posts.sql", contents1)
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// pull won't update the file unless reformatting
s.handleCommand(t, CodeSuccess, ".", "skeema pull --skip-format")
if fs.ReadTestFile(t, "mydb/product/posts.sql") != contents1 {
t.Error("Expected skeema pull --skip-format to leave file untouched, but it rewrote it")
}
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
if fs.ReadTestFile(t, "mydb/product/posts.sql") != oldContents {
t.Error("Expected skeema pull to rewrite file, but it did not")
}
// Renaming an FK should be considered a difference with --exact-match
fs.WriteTestFile(t, "mydb/product/posts.sql", contents1)
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff --exact-match")
s.handleCommand(t, CodeSuccess, ".", "skeema push --exact-match")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// Changing an FK definition should not break push or pull, even though this
// will be two non-noop ALTERs to the same table
contents2 := strings.Replace(contents1,
"FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)",
"FOREIGN KEY (`user_id`, `byline`) REFERENCES `users` (`id`, `name`)",
1)
if contents2 == contents1 {
t.Fatal("Failed to update contents as expected")
}
fs.WriteTestFile(t, "mydb/product/posts.sql", contents2)
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff")
s.handleCommand(t, CodeSuccess, ".", "skeema push")
fs.WriteTestFile(t, "mydb/product/posts.sql", contents1)
s.handleCommand(t, CodeSuccess, ".", "skeema pull --skip-format")
if fs.ReadTestFile(t, "mydb/product/posts.sql") != contents2 {
t.Error("Expected skeema pull to rewrite file, but it did not")
}
// Confirm that adding foreign keys occurs after other changes: construct
// a scenario where we're adding an FK that needs a new index on the "parent"
// (referenced) table, where the parent table name is alphabetically after
// the child table
s.dbExec(t, "product", "ALTER TABLE posts DROP FOREIGN KEY usridfk")
s.dbExec(t, "product", "ALTER TABLE users DROP KEY idname")
s.handleCommand(t, CodeSuccess, ".", "skeema push")
// Test handling of unsafe operations combined with FK operations:
// Drop FK + add different FK + drop another col
// Confirm that if an unsafe operation is blocked, but there's also a 2nd
// ALTER for same table (due to splitting of drop FK + add FK into separate
// ALTERs) that both ALTERs are skipped.
contents3 := strings.Replace(oldContents, "`body` text,\n", "", 1)
contents3 = strings.Replace(contents3, "`body` text DEFAULT NULL,\n", "", 1) // MariaDB 10.2+
if strings.Contains(contents3, "`body`") || !strings.Contains(contents3, "`user_fk`") {
t.Fatal("Failed to update contents as expected")
}
fs.WriteTestFile(t, "mydb/product/posts.sql", contents3)
s.handleCommand(t, CodeFatalError, ".", "skeema push")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
checkContents := fs.ReadTestFile(t, "mydb/product/posts.sql")
if !strings.Contains(checkContents, "`body`") || strings.Contains(checkContents, "`user_fk`") {
t.Error("Unsafe status did not properly affect both ALTERs on the table")
}
// Test adding an FK where the existing data does not meet the constraint:
// should fail if foreign_key_checks=1, succeed if foreign_key_checks=0
s.dbExec(t, "product", "ALTER TABLE posts DROP FOREIGN KEY usridfk")
s.dbExec(t, "product", "INSERT INTO posts (user_id, byline) VALUES (1234, 'someone')")
fs.WriteTestFile(t, "mydb/product/posts.sql", contents1)
s.handleCommand(t, CodeFatalError, ".", "skeema push --foreign-key-checks")
s.handleCommand(t, CodeSuccess, ".", "skeema push")
}
func (s SkeemaIntegrationSuite) TestAutoInc(t *testing.T) {
// Insert 2 rows into product.users, so that next auto-inc value is now 3
s.dbExec(t, "product", "INSERT INTO users (name) VALUES (?), (?)", "foo", "bar")
// Normal init omits auto-inc values. diff views this as no differences.
s.reinitAndVerifyFiles(t, "", "")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// pull and lint should make no changes
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.verifyFiles(t, cfg, "../golden/init")
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema lint")
s.verifyFiles(t, cfg, "../golden/init")
// pull with --include-auto-inc should include auto-inc values greater than 1
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema pull --include-auto-inc")
s.verifyFiles(t, cfg, "../golden/autoinc")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// Inserting another row should still be ignored by diffs
s.dbExec(t, "product", "INSERT INTO users (name) VALUES (?)", "something")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// However, if table's next auto-inc is LOWER than sqlfile's, this is a
// difference.
s.dbExec(t, "product", "DELETE FROM users WHERE id > 1")
s.dbExec(t, "product", "ALTER TABLE users AUTO_INCREMENT=2")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff")
s.handleCommand(t, CodeSuccess, ".", "skeema push")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// init with --include-auto-inc should include auto-inc values greater than 1
s.reinitAndVerifyFiles(t, "--include-auto-inc", "../golden/autoinc")
// now that the file has a next auto-inc value, subsequent pull operations
// should update the value, even without --include-auto-inc
s.dbExec(t, "product", "INSERT INTO users (name) VALUES (?)", "something")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
if !strings.Contains(fs.ReadTestFile(t, "mydb/product/users.sql"), "AUTO_INCREMENT=4") {
t.Error("Expected mydb/product/users.sql to contain AUTO_INCREMENT=4 after pull, but it did not")
}
}
func (s SkeemaIntegrationSuite) TestUnsupportedAlter(t *testing.T) {
s.sourceSQL(t, "unsupported1.sql")
// init should work fine with an unsupported table
s.reinitAndVerifyFiles(t, "", "../golden/unsupported")
// Back to clean slate for db and files
s.cleanData(t, "setup.sql")
s.reinitAndVerifyFiles(t, "", "../golden/init")
// apply change to db directly, and confirm pull still works
s.sourceSQL(t, "unsupported1.sql")
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema pull --debug --update-partitioning")
s.verifyFiles(t, cfg, "../golden/unsupported")
// back to clean slate for db only
s.cleanData(t, "setup.sql")
// lint should be able to fix formatting problems in unsupported table files
contents := fs.ReadTestFile(t, "mydb/product/subscriptions.sql")
fs.WriteTestFile(t, "mydb/product/subscriptions.sql", strings.Replace(contents, "`", "", -1))
s.handleCommand(t, CodeDifferencesFound, ".", "skeema lint")
s.verifyFiles(t, cfg, "../golden/unsupported")
// diff should return CodeDifferencesFound, vs push should return
// CodePartialError
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff --debug")
s.handleCommand(t, CodePartialError, ".", "skeema push")
// diff/push still ok if *creating* unsupported table
s.dbExec(t, "product", "DROP TABLE subscriptions")
s.assertTableMissing(t, "product", "subscriptions", "")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff")
s.handleCommand(t, CodeSuccess, ".", "skeema push")
s.assertTableExists(t, "product", "subscriptions", "")
// diff/push still ok if altering unsupported table to remove its unsupported
// feature, since the generated alter is verified, even with --skip-verify
contents = fs.ReadTestFile(t, "mydb/product/subscriptions.sql")
contents = strings.Replace(contents, "SUBPARTITION BY HASH (post_id)", "", 1)
contents = strings.Replace(contents, "SUBPARTITION BY HASH (`post_id`)", "", 1)
contents = strings.Replace(contents, "SUBPARTITIONS 2", "", 1)
if strings.Contains(contents, "SUBPARTITION") {
t.Fatalf("Failed to properly remove unsupported clause from subscriptions.sql -- contents:\n%s", contents)
} else {
fs.WriteTestFile(t, "mydb/product/subscriptions.sql", contents)
}
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff --partitioning=modify --skip-verify")
s.handleCommand(t, CodeSuccess, ".", "skeema push --partitioning=modify --skip-verify")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// Coverage for extra non-InnoDB warning text -- just ensuring no panic, and
// no need for allow-unsafe (since diff is not supported, due to USING BTREE
// clause on an index for a MyISAM table)
s.dbExec(t, "product", "ALTER TABLE users ENGINE=MyISAM")
contents = fs.ReadTestFile(t, "mydb/product/users.sql")
contents = strings.ReplaceAll(contents, "credits", "funds")
contents = strings.ReplaceAll(contents, "UNIQUE KEY `name` (`name`)", "UNIQUE KEY `name2` (`name`) USING BTREE")
contents = strings.ReplaceAll(contents, "InnoDB", "MyISAM")
fs.WriteTestFile(t, "mydb/product/users.sql", contents)
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff")
// Make the USING BTREE alter directly so that the table is unsupported, and
// then delete the users.sql file on the fs side. Confirm diff/push still ok
// for *dropping* unsupported table.
s.dbExec(t, "product", "ALTER TABLE users DROP KEY name, ADD UNIQUE KEY `name2` (`name`) USING BTREE")
if err := os.Remove("mydb/product/users.sql"); err != nil {
t.Fatalf("Unexpected error removing a file: %s", err)
}
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff --allow-unsafe")
s.handleCommand(t, CodeSuccess, ".", "skeema push --allow-unsafe")
s.assertTableMissing(t, "product", "users", "")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
}
func (s SkeemaIntegrationSuite) TestIgnoreOptions(t *testing.T) {
s.sourceSQL(t, "ignore1.sql")
// init: valid regexes should work properly and persist to option files
cfg := s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d --ignore-schema='^archives$' --ignore-table='^_'", s.d.Instance.Host, s.d.Instance.Port)
s.verifyFiles(t, cfg, "../golden/ignore")
// pull: nothing should be updated due to ignore options. Ditto even if we add
// a dir with schema name corresponding to ignored schema.
cfg = s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.verifyFiles(t, cfg, "../golden/ignore")
fs.WriteTestFile(t, "mydb/archives/.skeema", "schema=archives")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
if _, err := os.Stat("mydb/archives/foo.sql"); err == nil {
t.Error("ignore-options not affecting `skeema pull` as expected")
}
// diff/push: no differences. This should still be the case even if we add a
// file corresponding to an ignored table, with a different definition than
// the db has.
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
fs.WriteTestFile(t, "mydb/product/_widgets.sql", "CREATE TABLE _widgets (id int) ENGINE=InnoDB;\n")
fs.WriteTestFile(t, "mydb/analytics/_newtable.sql", "CREATE TABLE _newtable (id int) ENGINE=InnoDB;\n")
fs.WriteTestFile(t, "mydb/archives/bar.sql", "CREATE TABLE bar (id int) ENGINE=InnoDB;\n")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// pull should also ignore that file corresponding to an ignored table
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
if fs.ReadTestFile(t, "mydb/product/_widgets.sql") != "CREATE TABLE _widgets (id int) ENGINE=InnoDB;\n" {
t.Error("Expected pull to ignore mydb/product/_widgets.sql entirely, but it did not")
}
// lint: ignored tables should be ignored
// To set up this test, we do a pull that overrides the previous ignore options
// and then edit those files so that they contain formatting mistakes or even
// invalid SQL.
s.handleCommand(t, CodeSuccess, ".", "skeema pull --ignore-table=''")
contents := fs.ReadTestFile(t, "mydb/analytics/_trending.sql")
newContents := strings.Replace(contents, "`", "", -1)
fs.WriteTestFile(t, "mydb/analytics/_trending.sql", newContents)
fs.WriteTestFile(t, "mydb/analytics/_hmm.sql", "CREATE TABLE _hmm uhoh this is not valid;\n")
fs.RemoveTestFile(t, "mydb/archives/bar.sql")
s.handleCommand(t, CodeSuccess, ".", "skeema lint")
if fs.ReadTestFile(t, "mydb/analytics/_trending.sql") != newContents {
t.Error("Expected `skeema lint` to ignore mydb/analytics/_trending.sql, but it did not")
}
// push, pull, lint, format, init: invalid regexes should error with
// CodeBadConfig (except for push with invalid ignore-schema, which needs
// some further refactoring to handle configuration errors differently in
// some code paths)
s.handleCommand(t, CodeBadConfig, ".", "skeema lint --ignore-table='+'")
s.handleCommand(t, CodeBadConfig, ".", "skeema format --ignore-table='+'")
s.handleCommand(t, CodeBadConfig, ".", "skeema pull --ignore-table='+'")
s.handleCommand(t, CodeBadConfig, ".", "skeema pull --ignore-schema='+'")
s.handleCommand(t, CodeBadConfig, ".", "skeema push --ignore-table='+'")
s.handleCommand(t, CodeFatalError, ".", "skeema push --ignore-schema='+'")
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir badre1 -h %s -P %d --ignore-schema='+'", s.d.Instance.Host, s.d.Instance.Port)
s.handleCommand(t, CodeBadConfig, ".", "skeema init --dir badre2 -h %s -P %d --ignore-table='+'", s.d.Instance.Host, s.d.Instance.Port)
}
func (s SkeemaIntegrationSuite) TestDirEdgeCases(t *testing.T) {
s.handleCommand(t, CodeSuccess, ".", "skeema init --dir mydb -h %s -P %d", s.d.Instance.Host, s.d.Instance.Port)
// Invalid option file should break all commands
oldContents := fs.ReadTestFile(t, "mydb/.skeema")
fs.WriteTestFile(t, "mydb/.skeema", "invalid contents\n")
s.handleCommand(t, CodeBadConfig, "mydb", "skeema pull")
s.handleCommand(t, CodeBadConfig, "mydb", "skeema diff")
s.handleCommand(t, CodeBadConfig, "mydb", "skeema lint")
s.handleCommand(t, CodeBadConfig, "mydb", "skeema format")
s.handleCommand(t, CodeBadConfig, ".", "skeema add-environment --host my.staging.db.com --dir mydb staging")
fs.WriteTestFile(t, "mydb/.skeema", oldContents)
// Hidden directories are ignored, even if they contain a .skeema file, whether
// valid or invalid.
fs.WriteTestFile(t, ".hidden/.skeema", "invalid contents\n")
fs.WriteTestFile(t, ".hidden/whatever.sql", "CREATE TABLE whatever (this is not valid SQL oh well)")
fs.WriteTestFile(t, "mydb/.hidden/.skeema", "schema=whatever\n")
fs.WriteTestFile(t, "mydb/.hidden/whatever.sql", "CREATE TABLE whatever (this is not valid SQL oh well)")
fs.WriteTestFile(t, "mydb/product/.hidden/.skeema", "schema=whatever\n")
fs.WriteTestFile(t, "mydb/product/.hidden/whatever.sql", "CREATE TABLE whatever (this is not valid SQL oh well)")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
s.handleCommand(t, CodeSuccess, ".", "skeema lint")
// Referencing an undefined environment should fail gracefully, without panic
// on a nil instance, despite presence of *.sql files
s.handleCommand(t, CodeBadConfig, ".", "skeema format undefinedenv")
s.handleCommand(t, CodeBadConfig, ".", "skeema lint undefinedenv")
s.handleCommand(t, CodeBadConfig, ".", "skeema pull undefinedenv")
s.handleCommand(t, CodeSuccess, ".", "skeema diff undefinedenv")
// Extra subdirs with .skeema files and *.sql files don't inherit "schema"
// option value from parent dir, and are ignored by diff/push/pull as long
// as they don't specify a schema value directly. lint still works since its
// execution model does not require a schema to be defined.
fs.WriteTestFile(t, "mydb/product/subdir/.skeema", "# nothing relevant here\n")
fs.WriteTestFile(t, "mydb/product/subdir/hello.sql", "CREATE TABLE hello (id int);\n")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema lint") // should rewrite hello.sql
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
// Dirs with no *.sql files, but have a schema defined in .skeema, should
// be interpreted as a logical schema without any objects
s.dbExec(t, "", "CREATE DATABASE otherdb")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
s.handleCommand(t, CodeSuccess, ".", "skeema diff")
if contents := fs.ReadTestFile(t, "mydb/otherdb/.skeema"); contents == "" {
t.Error("Unexpectedly found no contents in mydb/otherdb/.skeema")
}
s.dbExec(t, "otherdb", "CREATE TABLE othertable (id int)")
s.handleCommand(t, CodeSuccess, ".", "skeema pull")
if contents := fs.ReadTestFile(t, "mydb/otherdb/othertable.sql"); contents == "" {
t.Error("Unexpectedly found no contents in mydb/otherdb/othertable.sql")
}
fs.RemoveTestFile(t, "mydb/otherdb/othertable.sql")
s.handleCommand(t, CodeFatalError, ".", "skeema diff")
s.handleCommand(t, CodeDifferencesFound, ".", "skeema diff --allow-unsafe")
s.handleCommand(t, CodeSuccess, ".", "skeema push --allow-unsafe")
}
// This test covers usage of clauses that have no effect in InnoDB, but are still
// shown by MySQL in SHOW CREATE TABLE, despite not being reflected anywhere in
// information_schema. Skeema ignores/strips these clauses so that they do not
// trip up its "unsupported table" validation logic.
func (s SkeemaIntegrationSuite) TestNonInnoClauses(t *testing.T) {
// MariaDB does not consider STORAGE or COLUMN_FORMAT clauses as valid SQL.
// Ditto for MySQL 5.5.
if s.d.Flavor().IsMariaDB() {
t.Skip("Test not relevant for MariaDB-based image", s.d.Image)
} else if s.d.Flavor().Matches(tengo.FlavorMySQL55) {
t.Skip("Test not relevant for 5.5-based image", s.d.Image)
}
withClauses := "CREATE TABLE `problems` (\n" +
" `name` varchar(30) /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL,\n" +
" `num` int(10) unsigned NOT NULL /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */,\n" +
" KEY `idx1` (`name`) USING HASH KEY_BLOCK_SIZE=4 COMMENT 'lol',\n" +
" KEY `idx2` (`num`) USING BTREE\n" +
") ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=8;\n"
withoutClauses := "CREATE TABLE `problems` (\n" +
" `name` varchar(30) DEFAULT NULL,\n" +
" `num` int(10) unsigned NOT NULL,\n" +
" KEY `idx1` (`name`) COMMENT 'lol',\n" +