njr4pksV

1<?php
2/**
3 * Created by PhpStorm.
4 * User: lucuzzuc
5 * Date: 9/12/18
6 * Time: 6:44 PM
7 */
8
9require_once 'database.php';
10
11function createTables($db) {
12
13// delete if exists
14    $sql = "
15DROP TABLE IF EXISTS `comments`;
16DROP TABLE IF EXISTS `likes`;
17DROP TABLE IF EXISTS `edit`;
18DROP TABLE IF EXISTS `users`;";
19    $sth = $db->prepare($sql);
20    if ($sth->execute() === FALSE)
21        echo "cannot drop";
22
23// comments table
24    $sql = "
25CREATE TABLE `comments` (
26  `id` int(11) NOT NULL,
27  `text` text NOT NULL,
28  `date` datetime NOT NULL,
29  `uid` int(11) NOT NULL,
30  `eid` int(11) NOT NULL
31) ENGINE=InnoDB DEFAULT CHARSET=latin1";
32    $sth = $db->prepare($sql);
33    if ($sth->execute() === FALSE)
34        echo "cannot create comments";
35
36// edit table
37    $sql = "
38CREATE TABLE `edit` (
39  `id` int(11) NOT NULL,
40  `uid` int(11) NOT NULL,
41  `path` text NOT NULL
42) ENGINE=InnoDB DEFAULT CHARSET=latin1";
43    $sth = $db->prepare($sql);
44    if ($sth->execute() === FALSE)
45        echo "cannot create edit";
46
47// likes table
48    $sql = "
49CREATE TABLE `likes` (
50  `id` int(11) NOT NULL,
51  `uid` int(11) NOT NULL,
52  `eid` int(11) NOT NULL
53) ENGINE=InnoDB DEFAULT CHARSET=latin1";
54    $sth = $db->prepare($sql);
55    if ($sth->execute() === FALSE)
56        echo "cannot create likes";
57
58// users table
59    $sql = "
60CREATE TABLE `users` (
61  `id` int(11) NOT NULL,
62  `login` varchar(255) NOT NULL,
63  `passwd` text NOT NULL,
64  `mail` varchar(255) NOT NULL,
65  `activateCode` text NOT NULL,
66  `confirmed` int(11) NOT NULL DEFAULT '0'
67) ENGINE=InnoDB DEFAULT CHARSET=latin1";
68    $sth = $db->prepare($sql);
69    if ($sth->execute() === FALSE)
70        echo "cannot create users";
71    return 1;
72};
73
74function createIndexes($db) {
75//-- Indexes for table `comments`
76    $sql = "
77ALTER TABLE `comments`
78  ADD PRIMARY KEY (`id`),
79  ADD KEY `FK_eid` (`eid`),
80  ADD KEY `FK_usserId` (`uid`);
81    ";
82    $sth = $db->prepare($sql);
83    if ($sth->execute() === FALSE)
84        return 0;
85
86//-- Indexes for table `edit`
87    $sql = "
88ALTER TABLE `edit`
89  ADD PRIMARY KEY (`id`),
90  ADD KEY `FK_uid` (`uid`);
91  ";
92    $sth = $db->prepare($sql);
93    if ($sth->execute() === FALSE)
94        return 0;
95
96//-- Indexes for table `likes`
97    $sql = "
98ALTER TABLE `likes`
99  ADD PRIMARY KEY (`id`),
100  ADD UNIQUE KEY `unique_like` (`uid`,`eid`),
101  ADD KEY `FK_editId` (`eid`);
102  ";
103    $sth = $db->prepare($sql);
104    if ($sth->execute() === FALSE)
105        return 0;
106
107//-- Indexes for table `users`
108    $sql = "
109ALTER TABLE `users`
110  ADD PRIMARY KEY (`id`),
111  ADD UNIQUE KEY `login` (`login`),
112  ADD UNIQUE KEY `mail` (`mail`);
113  ";
114    $sth = $db->prepare($sql);
115    if ($sth->execute() === FALSE)
116        return 0;
117    return 1;
118};
119
120function createIncrements($db) {
121//-- AUTO_INCREMENT for table `comments`
122    $sql = "
123ALTER TABLE `comments`
124  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT
125  ";
126    $sth = $db->prepare($sql);
127    if ($sth->execute() === FALSE)
128        return 0;
129
130//-- AUTO_INCREMENT for table `edit`
131    $sql = "
132ALTER TABLE `edit`
133  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
134  ";
135    $sth = $db->prepare($sql);
136    if ($sth->execute() === FALSE)
137        return 0;
138
139//-- AUTO_INCREMENT for table `likes`
140    $sql = "
141ALTER TABLE `likes`
142  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT
143  ";
144    $sth = $db->prepare($sql);
145    if ($sth->execute() === FALSE)
146        return 0;
147
148//-- AUTO_INCREMENT for table `users`
149    $sql = "
150ALTER TABLE `users`
151  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
152  ";
153    $sth = $db->prepare($sql);
154    if ($sth->execute() === FALSE)
155        return 0;
156    return 1;
157};
158
159function createConstraints($db) {
160//-- Constraints for table `comments`
161    $sql = "
162ALTER TABLE `comments`
163  ADD CONSTRAINT `FK_eid` FOREIGN KEY (`eid`) REFERENCES `edit` (`id`),
164  ADD CONSTRAINT `FK_usserId` FOREIGN KEY (`uid`) REFERENCES `users` (`id`);
165  ";
166    $sth = $db->prepare($sql);
167    if ($sth->execute() === FALSE)
168        return 0;
169
170//-- Constraints for table `edit`
171    $sql = "
172ALTER TABLE `edit`
173  ADD CONSTRAINT `FK_uid` FOREIGN KEY (`uid`) REFERENCES `users` (`id`);
174  ";
175    $sth = $db->prepare($sql);
176    if ($sth->execute() === FALSE)
177        return 0;
178
179//-- Constraints for table `likes`
180    $sql = "
181ALTER TABLE `likes`
182  ADD CONSTRAINT `FK_editId` FOREIGN KEY (`eid`) REFERENCES `edit` (`id`),
183  ADD CONSTRAINT `FK_userId` FOREIGN KEY (`uid`) REFERENCES `users` (`id`);
184  ";
185    $sth = $db->prepare($sql);
186    if ($sth->execute() === FALSE)
187        return 0;
188    return 1;
189}
190
191if (!createTables($DB))
192    echo "error while creating tables";
193if (!createIndexes($DB))
194    echo "error while creating indexes";
195if (!createIncrements($DB))
196    echo "error while creating incrementation";
197if (!createConstraints($DB))
198    echo "error while creating constraints";
199else
200    echo "Camagru database correctly created";