· 6 years ago · Jun 21, 2019, 06:14 PM
1Skip to content
2
3Search or jump to…
4
5Pull requests
6Issues
7Marketplace
8Explore
9
10@BeqaKhaburdzania
114
12 Code Issues 0 Pull requests 0 Projects 0 Wiki Security Insights
13AndroidFinalProjectServer/database files/procedures.sql
146511383 on Jul 14, 2015
15442 lines (319 sloc) 13.3 KB
16
17-- --------------------------------------------------------------------------------
18-- Routine DDL
19-- Note: The procedure inserts new user into 'users' table.
20-- --------------------------------------------------------------------------------
21DELIMITER $$
22DROP PROCEDURE IF EXISTS `insert_user` $$
23CREATE PROCEDURE `insert_user` (userName nvarchar(32), userSurname nvarchar(32), email varchar(128),
24 userPassword text, phone varchar(32), card_number text, primary_number text, isAdmin int)
25BEGIN
26
27 insert into users(user_name, user_surname, user_email, user_password, user_phone, user_card_number, user_primary_number, user_is_admin)
28 value(userName, userSurname, email, userPassword, phone, card_number, primary_number, isAdmin);
29
30END $$
31
32
33-- --------------------------------------------------------------------------------
34-- Routine DDL
35-- Note: The procedure saves category into categories table. Also it finds out category id and adds it into map_category_product table.
36-- --------------------------------------------------------------------------------
37DELIMITER $$
38DROP PROCEDURE IF EXISTS `insert_category` $$
39CREATE PROCEDURE `insert_category` (out categoryID int, categoryName nvarchar(32))
40BEGIN
41
42 insert into categories(category_name)
43 value(categoryName);
44
45 set categoryID = (select category_id from categories where category_name = categoryName);
46END $$
47
48-- --------------------------------------------------------------------------------
49-- Routine DDL
50-- Note: The procedure inserts new product into products table. Also makes new entry into map_category_product
51-- --------------------------------------------------------------------------------
52DELIMITER $$
53DROP PROCEDURE IF EXISTS `insert_product` $$
54CREATE PROCEDURE `insert_product` (out productID int, productName nvarchar(32), productDescription text, productPrice double,
55 productImageLink text)
56BEGIN
57
58 insert into products(product_name, description, price, image_link)
59 value(productName, productDescription, productPrice, productImageLink);
60
61 set productID = (select product_id from products where product_name = productName);
62
63END $$
64
65
66-- --------------------------------------------------------------------------------
67-- Routine DDL
68-- Note: The storage procedure fills map_category_product table.
69-- --------------------------------------------------------------------------------
70DELIMITER $$
71DROP PROCEDURE IF EXISTS `fill_map_category_product` $$
72CREATE PROCEDURE `android_final_project`.`fill_map_category_product` (categoryID int, productID int)
73BEGIN
74
75 declare tableID int;
76
77 set tableID = (select id from map_category_product where id_of_category = categoryID and id_of_product = productID);
78 select tableID;
79 if tableID is null then
80 insert into map_category_product(id_of_category, id_of_product)
81 value(categoryID, productID);
82 end if;
83
84END $$
85
86
87-- --------------------------------------------------------------------------------
88-- Routine DDL
89-- Note: The procedure fills fill_map_menu_product table.
90-- --------------------------------------------------------------------------------
91DELIMITER $$
92DROP PROCEDURE IF EXISTS `fill_map_menu_product` $$
93CREATE PROCEDURE `android_final_project`.`fill_map_menu_product` (menuID int, categoryID int)
94BEGIN
95
96 insert into map_menu_product(id_of_menu, id_of_product)
97 value(menuID, categoryID);
98
99END $$
100
101
102-- --------------------------------------------------------------------------------
103-- Routine DDL
104-- Note: The procedure gets all products from products table for appropriate category id.
105-- --------------------------------------------------------------------------------
106DELIMITER $$
107DROP PROCEDURE IF EXISTS `all_products_by_category` $$
108CREATE PROCEDURE `android_final_project`.`all_products_by_category` (categoryID int)
109BEGIN
110
111 select product_id, product_name, description, price, image_link from products
112 inner join map_category_product
113 on products.product_id = map_category_product.id_of_product
114 where map_category_product.id_of_category = categoryID;
115
116END $$
117
118
119-- --------------------------------------------------------------------------------
120-- Routine DDL
121-- Note: The procedure gives all products which are not in appropriate category.
122-- If category is removed, maping also removed from map_category_product. The procedure return correct
123-- products, because it uses left join statement on products table and map_category_product table.
124-- --------------------------------------------------------------------------------
125DELIMITER $$
126DROP PROCEDURE IF EXISTS `all_products_out_of_category` $$
127CREATE PROCEDURE `android_final_project`.`all_products_out_of_category` (categoryID int)
128BEGIN
129
130 select product_id, product_name, description, price, image_link from products
131 left join map_category_product
132 on products.product_id = map_category_product.id_of_product
133 where map_category_product.id_of_category != categoryID
134 group by products.product_id;
135
136END $$
137
138-- --------------------------------------------------------------------------------
139-- Routine DDL
140-- Note: The procedure inserts new menu into menus table and saves menu id in menuID parameter.
141-- --------------------------------------------------------------------------------
142DELIMITER $$
143DROP PROCEDURE IF EXISTS `insert_menu` $$
144CREATE PROCEDURE `insert_menu` (out menuID int, menuName nvarchar(32), menuDescription text, menuPrice double, menuImageLink text)
145BEGIN
146
147 insert into menus(menu_name, description, price, image_link)
148 values(menuName, menuDescription, menuPrice, menuImageLink);
149
150 set menuID = (select menu_id from menus where menu_name = menuName);
151
152END $$
153
154
155-- --------------------------------------------------------------------------------
156-- Routine DDL
157-- Note: The procedure minsert news entry into appropriate table.
158-- --------------------------------------------------------------------------------
159DELIMITER $$
160DROP PROCEDURE IF EXISTS `insert_news` $$
161CREATE PROCEDURE `android_final_project`.`insert_news` (out newsID int, newsName nvarchar(32), newsDescription text, newsFromDate long,
162 newsToDate long)
163BEGIN
164
165 insert into news(news_name, description, from_date, to_date)
166 value(newsName, newsDescription, newsFromDate, newsToDate);
167
168 set newsID = (select id from news where news_name = newsName);
169
170END $$
171
172
173-- --------------------------------------------------------------------------------
174-- Routine DDL
175-- Note: The procedure updates product by product id.
176-- --------------------------------------------------------------------------------
177DELIMITER $$
178DROP PROCEDURE IF EXISTS `update_product` $$
179CREATE PROCEDURE `android_final_project`.`update_product` (poductID int, productName nvarchar(32), productDescription text,
180 productPrice double, productImageLink text)
181BEGIN
182
183 update products
184 set product_name = productName, description = productDescription, price = productPrice, image_link = productImageLink
185 where product_id = poductID;
186
187END $$
188
189
190-- --------------------------------------------------------------------------------
191-- Routine DDL
192-- Note: The procedure updates menus by menu id.
193-- --------------------------------------------------------------------------------
194DELIMITER $$
195DROP PROCEDURE IF EXISTS `update_menu` $$
196CREATE PROCEDURE `android_final_project`.`update_menu` (menuID int, menuName nvarchar(32), menuDescription text,
197 menuPrice double, menuImageLink text)
198BEGIN
199
200 update menus
201 set menu_name = menuName, description = menuDescription, price = menuPrice, image_link = menuImageLink
202 where menu_id = menuID;
203
204END $$
205
206
207-- --------------------------------------------------------------------------------
208-- Routine DDL
209-- Note: The procedure updates category by id.
210-- --------------------------------------------------------------------------------
211DELIMITER $$
212DROP PROCEDURE IF EXISTS `update_category` $$
213CREATE PROCEDURE `android_final_project`.`update_category` (categoryID int, categoryName nvarchar(32))
214BEGIN
215
216 update categories
217 set category_name = categoryName
218 where category_id = categoryID;
219
220END $$
221
222-- --------------------------------------------------------------------------------
223-- Routine DDL
224-- Note: The procedure updates news table.
225-- --------------------------------------------------------------------------------
226DELIMITER $$
227DROP PROCEDURE IF EXISTS `update_news` $$
228CREATE PROCEDURE `android_final_project`.`update_news` (newsID int, newsName nvarchar(32), newsDescription text,
229 newsFromDate long, newsToDate long)
230BEGIN
231
232 update news
233 set news_name = newsName, description = newsDescription, from_date = newsFromDate, to_date = newsToDate
234 where id = newsID;
235
236END $$
237
238
239DELIMITER $$
240
241CREATE PROCEDURE `android_final_project`.`increase_version_number` (itemName nvarchar(32))
242BEGIN
243 declare newVersionNumber int;
244 set newVersionNumber = (select version_number from versions where version_item_name = itemName) + 1;
245
246 update versions
247 set version_number = newVersionNumber
248 where version_item_name = itemName;
249END $$
250
251
252
253
254# selects procedures:
255
256
257
258DELIMITER $$
259DROP PROCEDURE IF EXISTS `select_user` $$
260CREATE PROCEDURE `android_final_project`.`select_user` (userEmail varchar(128), userPassword text)
261BEGIN
262
263 select * from users where user_email = userEmail and user_password = userPassword;
264
265END $$
266
267
268DELIMITER $$
269DROP PROCEDURE IF EXISTS `select_categories` $$
270CREATE PROCEDURE `android_final_project`.`select_categories` ()
271BEGIN
272 select * from categories;
273END $$
274
275
276DELIMITER $$
277DROP PROCEDURE IF EXISTS `select_news` $$
278CREATE PROCEDURE `android_final_project`.`select_news` ()
279BEGIN
280 select * from news;
281END $$
282
283
284DELIMITER $$
285DROP PROCEDURE IF EXISTS `select_menus` $$
286CREATE PROCEDURE `android_final_project`.`select_menus` ()
287BEGIN
288 select * from menus;
289END $$
290
291
292DELIMITER $$
293DROP PROCEDURE IF EXISTS `select_menus_products` $$
294CREATE PROCEDURE `android_final_project`.`select_menus_products` (menuID int)
295BEGIN
296
297 select product_id, product_name, description, price, image_link from products
298 inner join map_menu_product
299 on products.product_id = map_menu_product.id_of_product
300 where map_menu_product.id_of_menu = menuID;
301
302END $$
303
304
305
306DELIMITER $$
307DROP PROCEDURE IF EXISTS `select_products_by_menu` $$
308CREATE PROCEDURE `select_products_by_menu` (menuID int)
309BEGIN
310 select product_name, description, price, image_link from
311 products inner join map_menu_product
312 on product_id = id_of_product
313 where id_of_menu = menuID;
314
315END $$
316
317DELIMITER $$
318DROP PROCEDURE IF EXISTS `select_products_out_of_menu` $$
319CREATE PROCEDURE `select_products_out_of_menu` (menuID int)
320BEGIN
321
322 select product_name, description, price, image_link from
323 products inner join map_menu_product
324 on product_id = id_of_product
325 where id_of_menu != menuID;
326
327END $$
328
329
330DELIMITER $$
331DROP PROCEDURE IF EXISTS `select_categories_by_product` $$
332CREATE PROCEDURE `select_categories_by_product` (productID int)
333BEGIN
334
335 select category_id, category_name from
336 categories left join map_category_product
337 on category_id = id_of_category
338 where id_of_product = productID;
339
340END $$
341
342
343DELIMITER $$
344DROP PROCEDURE IF EXISTS `select_categories_out_of_product` $$
345CREATE PROCEDURE `select_categories_out_of_product` (productID int)
346BEGIN
347
348 select category_id, category_name from
349 categories left join map_category_product
350 on category_id = id_of_category
351 where id_of_product != productID or id_of_product is null
352 group by category_id;
353
354END $$
355
356DELIMITER $$
357DROP PROCEDURE IF EXISTS `select_produts` $$
358CREATE PROCEDURE `android_final_project`.`select_produts` ()
359BEGIN
360 select * from products;
361END $$
362
363DELIMITER $$
364DROP PROCEDURE IF EXISTS `select_version_number` $$
365CREATE PROCEDURE `android_final_project`.`select_version_number` (out vNumber int, vName nvarchar(32))
366BEGIN
367 set vNumber = (select version_number from versions where version_item_name = vName);
368END $$
369
370# deletes:
371
372DELIMITER $$
373DROP PROCEDURE IF EXISTS `remove_product_from_category` $$
374CREATE PROCEDURE `remove_product_from_category` (categoryID int, productID int)
375BEGIN
376
377 delete from map_category_product
378 where id_of_category = categoryID and id_of_product = productID;
379
380END $$
381
382
383DELIMITER $$
384DROP PROCEDURE IF EXISTS `remove_product_from_menu` $$
385CREATE PROCEDURE `remove_product_from_menu` (menuID int, productID int)
386BEGIN
387
388 delete from map_menu_product
389 where id_of_menu = menuID and id_of_product = productID;
390
391END $$
392
393
394DELIMITER $$
395DROP PROCEDURE IF EXISTS `remove_user` $$
396CREATE PROCEDURE `android_final_project`.`remove_user` (userID int)
397BEGIN
398
399 delete from users
400 where user_id = userID;
401
402END $$
403
404
405DELIMITER $$
406DROP PROCEDURE IF EXISTS `remove_product` $$
407CREATE PROCEDURE `android_final_project`.`remove_product` (productID int)
408BEGIN
409
410 delete from map_category_product
411 where id_of_product = productID;
412
413 delete from map_menu_product
414 where id_of_product = productID;
415
416 delete from products
417 where product_id = productID;
418
419END $$
420
421
422DELIMITER $$
423DROP PROCEDURE IF EXISTS `remove_category` $$
424CREATE PROCEDURE `android_final_project`.`remove_category` (categoryID int)
425BEGIN
426
427 delete from map_category_product
428 where id_of_category = categoryID;
429
430 delete from categories
431 where category_id = categoryID;
432
433END $$
434
435
436DELIMITER $$
437DROP PROCEDURE IF EXISTS `remove_menu` $$
438CREATE PROCEDURE `android_final_project`.`remove_menu` (menuID int)
439BEGIN
440
441 delete from map_menu_product
442 where id_of_menu = menuID;
443
444 delete from menus
445 where menu_id = menuID;
446
447END $$
448
449
450DELIMITER $$
451DROP PROCEDURE IF EXISTS `remove_news` $$
452CREATE PROCEDURE `android_final_project`.`remove_news` (newsID int)
453BEGIN
454
455 delete from news
456 where id = newsID;
457
458END $$