entryservice/updates-2016Feb.sql at master · lgastmans/entryservice · GitHub

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
ALTER TABLE `status` CHANGE `DurationPeriod` `DurationPeriod` ENUM( 'None', 'Days', 'Weeks', 'Months', 'Years' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL ;
ALTER TABLE `applicant_status` CHANGE `DurationPeriod` `DurationPeriod` ENUM( 'None', 'Days', 'Weeks', 'Months', 'Years' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;

===

ALTER TABLE `applicant` CHANGE `MaritalStatus` `MaritalStatus` ENUM( 'Single', 'Partner', 'Married', 'Couple' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;

===

ALTER TABLE `children` ADD INDEX ( `SchoolID` ) ;
ALTER TABLE `children` ADD FOREIGN KEY ( `SchoolID` ) REFERENCES `entryservice`.`school` (
`ID`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE `children` ADD `Sex` ENUM( 'M', 'F' ) NOT NULL ;
ALTER TABLE `children` ADD `NationalityID` INT NULL ,
ADD INDEX ( `NationalityID` ) ;
ALTER TABLE `children` ADD FOREIGN KEY ( `NationalityID` ) REFERENCES `entryservice`.`nationality` (
`ID`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;
ALTER TABLE `children` ADD `ResServiceNum` INT NULL ;
ALTER TABLE `applicant` ADD `IsArchived` BOOLEAN NULL DEFAULT FALSE ;
ALTER TABLE `applicant` ADD `AVName` VARCHAR( 64 ) NULL AFTER `Surname` ,
ADD INDEX ( `AVName` ) ;

===

ALTER TABLE `applicant` CHANGE `NationalityID` `NationalityID` INT( 11 ) NULL ;

===

ALTER TABLE `visa` CHANGE `VisaType` `VisaType` ENUM( 'Visa', 'Bussiness', 'Tourist', 'PIO', 'OCI', 'Student', 'Entry', 'RP', 'Other' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;

===

ALTER TABLE `applicant` CHANGE `BirthDate` `BirthDate` DATE NULL DEFAULT NULL ;
ALTER TABLE `passport` CHANGE `IssuedDate` `IssuedDate` DATE NULL DEFAULT NULL ;
ALTER TABLE `passport` CHANGE `ValidTill` `ValidTill` DATE NULL DEFAULT NULL ;
ALTER TABLE `visa` CHANGE `IssuedDate` `IssuedDate` DATE NULL DEFAULT NULL ;
ALTER TABLE `visa` CHANGE `ValidTill` `ValidTill` DATE NULL DEFAULT NULL ;
ALTER TABLE `indianID` CHANGE `IssuedDate` `IssuedDate` DATE NULL DEFAULT NULL ;
ALTER TABLE `indianID` CHANGE `ValidTill` `ValidTill` DATE NULL DEFAULT NULL ;

===

ALTER TABLE `visa` CHANGE `VisaType` `VisaType` ENUM( 'Visa', 'Bussiness', 'Tourist', 'PIO', 'OCI', 'Student', 'Entry', 'RP', 'Other', 'Stay Visa' ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ;
ALTER TABLE `applicant_status` CHANGE `StartedOn` `StartedOn` DATE NULL DEFAULT NULL ;
ALTER TABLE `extension` CHANGE `ExtendedOn` `ExtendedOn` DATE NULL DEFAULT NULL ;
ALTER TABLE `absence` CHANGE `AbsentTill` `AbsentTill` DATE NULL DEFAULT NULL ;
ALTER TABLE `absence` CHANGE `AbsentOn` `AbsentOn` DATE NULL DEFAULT NULL ;
ALTER TABLE `contact` CHANGE `CountryID` `CountryID` INT( 11 ) NULL DEFAULT NULL ;
ALTER TABLE `interview` CHANGE `DateInterviewed` `DateInterviewed` DATE NULL DEFAULT NULL ;
ALTER TABLE `children` CHANGE `IssuedDate` `IssuedDate` DATE NULL DEFAULT NULL ;
ALTER TABLE `children` CHANGE `ValidTill` `ValidTill` DATE NULL DEFAULT NULL ;
ALTER TABLE `children` CHANGE `BirthDate` `BirthDate` DATE NULL DEFAULT NULL ;

===

ALTER TABLE `applicant_status` ADD `IsCurrent` BOOLEAN NULL DEFAULT NULL AFTER `StatusID` ;
UPDATE applicant_status SET IsCurrent =1 WHERE (CompletedOn IS NULL)