cG8RASGS

1--
2-- Table structure for table `Client`
3--
4
5DROP TABLE IF EXISTS `Client`;
6/*!40101 SET @saved_cs_client     = @@character_set_client */;
7/*!40101 SET character_set_client = utf8 */;
8CREATE TABLE `Client` (
9  `clientID` bigint(1) NOT NULL AUTO_INCREMENT,
10  `owner` bigint(1) DEFAULT NULL,
11  `emergencyContact` bigint(1) DEFAULT NULL,
12  `vet` bigint(1) DEFAULT NULL,
13  `info` varchar(200) DEFAULT NULL,
14  PRIMARY KEY (`clientID`),
15  UNIQUE KEY `clientID_UNIQUE` (`clientID`),
16  KEY `ownerFK_idx` (`owner`),
17  KEY `contactFK_idx` (`emergencyContact`),
18  KEY `vetFK_idx` (`vet`),
19  CONSTRAINT `contactFK` FOREIGN KEY (`emergencyContact`) REFERENCES `Person` (`personID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
20  CONSTRAINT `ownerFK` FOREIGN KEY (`owner`) REFERENCES `Person` (`personID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
21  CONSTRAINT `vetFK` FOREIGN KEY (`vet`) REFERENCES `Vet` (`vetID`) ON DELETE NO ACTION ON UPDATE NO ACTION
22) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
23
24
25
26
27
28
29
30CREATE DEFINER=`root`@`localhost` PROCEDURE `getInfo`(
31	IN in_clientID BIGINT
32)
33BEGIN
34	SELECT
35		info
36	FROM
37		Client
38	WHERE
39		clientID = in_clientID;
40END
41
42
43
44
45
46
47CREATE DEFINER=`root`@`localhost` PROCEDURE `updateInfo`(
48	IN in_clientID BIGINT,
49	IN in_info VARCHAR(200)
50)
51BEGIN
52	#IN CASE OF ANY FAILURE, OUTPUT WHAT WENT WRONG && WHY, ROLLBACK ALL && END;
53    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
54    
55    BEGIN
56        ROLLBACK;
57        RESIGNAL;
58    END;
59    
60	START TRANSACTION;
61	
62    UPDATE
63		Client
64	SET
65		info = in_info
66	WHERE
67		clientID = in_clientID;
68	COMMIT;
69END