-
Notifications
You must be signed in to change notification settings - Fork 1
/
fiarahub.sql
1471 lines (1305 loc) · 73.7 KB
/
fiarahub.sql
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
-- Create fiarahub tables
-- Create department table
CREATE TABLE `department` (
`department_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`description` VARCHAR(1000),
PRIMARY KEY (`department_id`)
) ENGINE=MyISAM;
-- Create category table
CREATE TABLE `category` (
`category_id` INT NOT NULL AUTO_INCREMENT,
`department_id` INT NOT NULL,
`name` VARCHAR(100) NOT NULL,
`description` VARCHAR(1000),
PRIMARY KEY (`category_id`),
KEY `idx_category_department_id` (`department_id`)
) ENGINE=MyISAM;
-- Create product table
CREATE TABLE `product` (
`product_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`description` VARCHAR(1000) NOT NULL,
`price` DECIMAL(10,2) NOT NULL,
`discounted_price` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
`image` VARCHAR(150),
`image_2` VARCHAR(150),
`thumbnail` VARCHAR(150),
`display` SMALLINT(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`),
FULLTEXT KEY `idx_ft_product_name_description` (`name`, `description`)
) ENGINE=MyISAM;
-- Create product_category table
CREATE TABLE `product_category` (
`product_id` INT NOT NULL,
`category_id` INT NOT NULL,
PRIMARY KEY (`product_id`, `category_id`)
) ENGINE=MyISAM;
-- Create attribute table (stores attributes such as Size and Color)
CREATE TABLE `attribute` (
`attribute_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL, -- E.g. Color, Size
PRIMARY KEY (`attribute_id`)
) ENGINE=MyISAM;
-- Create attribute_value table (stores values such as Yellow or XXL)
CREATE TABLE `attribute_value` (
`attribute_value_id` INT NOT NULL AUTO_INCREMENT,
`attribute_id` INT NOT NULL, -- The ID of the attribute
`value` VARCHAR(100) NOT NULL, -- E.g. Yellow
PRIMARY KEY (`attribute_value_id`),
KEY `idx_attribute_value_attribute_id` (`attribute_id`)
) ENGINE=MyISAM;
-- Create product_attribute table (associates attribute values to products)
CREATE TABLE `product_attribute` (
`product_id` INT NOT NULL,
`attribute_value_id` INT NOT NULL,
PRIMARY KEY (`product_id`, `attribute_value_id`)
) ENGINE=MyISAM;
-- Create shopping_cart table
CREATE TABLE `shopping_cart` (
`item_id` INT NOT NULL AUTO_INCREMENT,
`cart_id` CHAR(32) NOT NULL,
`product_id` INT NOT NULL,
`attributes` VARCHAR(1000) NOT NULL,
`quantity` INT NOT NULL,
`buy_now` BOOL NOT NULL DEFAULT true,
`added_on` DATETIME NOT NULL,
PRIMARY KEY (`item_id`),
KEY `idx_shopping_cart_cart_id` (`cart_id`)
) ENGINE=MyISAM;
-- Create orders table
CREATE TABLE `orders` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`total_amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
`created_on` DATETIME NOT NULL,
`shipped_on` DATETIME,
`status` INT NOT NULL DEFAULT '0',
`comments` VARCHAR(255),
`customer_id` INT,
`auth_code` VARCHAR(50),
`reference` VARCHAR(50),
`shipping_id` INT,
`tax_id` INT,
PRIMARY KEY (`order_id`),
KEY `idx_orders_customer_id` (`customer_id`),
KEY `idx_orders_shipping_id` (`shipping_id`),
KEY `idx_orders_tax_id` (`tax_id`)
) ENGINE=MyISAM;
-- Create order_details table
CREATE TABLE `order_detail` (
`item_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`product_id` INT NOT NULL,
`attributes` VARCHAR(1000) NOT NULL,
`product_name` VARCHAR(100) NOT NULL,
`quantity` INT NOT NULL,
`unit_cost` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`item_id`),
KEY `idx_order_detail_order_id` (`order_id`)
) ENGINE=MyISAM;
-- Create shipping_region table
CREATE TABLE `shipping_region` (
`shipping_region_id` INT NOT NULL AUTO_INCREMENT,
`shipping_region` VARCHAR(100) NOT NULL,
PRIMARY KEY (`shipping_region_id`)
) ENGINE=MyISAM;
-- Create customer table
CREATE TABLE `customer` (
`customer_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`credit_card` TEXT,
`address_1` VARCHAR(100),
`address_2` VARCHAR(100),
`city` VARCHAR(100),
`region` VARCHAR(100),
`postal_code` VARCHAR(100),
`country` VARCHAR(100),
`shipping_region_id` INT NOT NULL default '1',
`day_phone` varchar(100),
`eve_phone` varchar(100),
`mob_phone` varchar(100),
PRIMARY KEY (`customer_id`),
UNIQUE KEY `idx_customer_email` (`email`),
KEY `idx_customer_shipping_region_id` (`shipping_region_id`)
) ENGINE=MyISAM;
-- Create shipping table
CREATE TABLE `shipping` (
`shipping_id` INT NOT NULL AUTO_INCREMENT,
`shipping_type` VARCHAR(100) NOT NULL,
`shipping_cost` NUMERIC(10, 2) NOT NULL,
`shipping_region_id` INT NOT NULL,
PRIMARY KEY (`shipping_id`),
KEY `idx_shipping_shipping_region_id` (`shipping_region_id`)
) ENGINE=MyISAM;
-- Create tax table
CREATE TABLE `tax` (
`tax_id` INT NOT NULL AUTO_INCREMENT,
`tax_type` VARCHAR(100) NOT NULL,
`tax_percentage` NUMERIC(10, 2) NOT NULL,
PRIMARY KEY (`tax_id`)
) ENGINE=MyISAM;
-- Create audit table
CREATE TABLE `audit` (
`audit_id` INT NOT NULL AUTO_INCREMENT,
`order_id` INT NOT NULL,
`created_on` DATETIME NOT NULL,
`message` TEXT NOT NULL,
`code` INT NOT NULL,
PRIMARY KEY (`audit_id`),
KEY `idx_audit_order_id` (`order_id`)
) ENGINE=MyISAM;
-- Create review table
CREATE TABLE `review` (
`review_id` INT NOT NULL AUTO_INCREMENT,
`customer_id` INT NOT NULL,
`product_id` INT NOT NULL,
`review` TEXT NOT NULL,
`rating` SMALLINT NOT NULL,
`created_on` DATETIME NOT NULL,
PRIMARY KEY (`review_id`),
KEY `idx_review_customer_id` (`customer_id`),
KEY `idx_review_product_id` (`product_id`)
) ENGINE=MyISAM;
-- Populate department table
INSERT INTO `department` (`department_id`, `name`, `description`) VALUES
(1, 'Regional', 'Proud of your country? Wear a T-shirt with a national symbol stamp!'),
(2, 'Nature', 'Find beautiful T-shirts with animals and flowers in our Nature department!'),
(3, 'Seasonal', 'Each time of the year has a special flavor. Our seasonal T-shirts express traditional symbols using unique postal stamp pictures.');
-- Populate category table
INSERT INTO `category` (`category_id`, `department_id`, `name`, `description`) VALUES
(1, 1, 'French', 'The French have always had an eye for beauty. One look at the T-shirts below and you''ll see that same appreciation has been applied abundantly to their postage stamps. Below are some of our most beautiful and colorful T-shirts, so browse away! And don''t forget to go all the way to the bottom - you don''t want to miss any of them!'),
(2, 1, 'Italian', 'The full and resplendent treasure chest of art, literature, music, and science that Italy has given the world is reflected splendidly in its postal stamps. If we could, we would dedicate hundreds of T-shirts to this amazing treasure of beautiful images, but for now we will have to live with what you see here. You don''t have to be Italian to love these gorgeous T-shirts, just someone who appreciates the finer things in life!'),
(3, 1, 'Irish', 'It was Churchill who remarked that he thought the Irish most curious because they didn''t want to be English. How right he was! But then, he was half-American, wasn''t he? If you have an Irish genealogy you will want these T-shirts! If you suddenly turn Irish on St. Patrick''s Day, you too will want these T-shirts! Take a look at some of the coolest T-shirts we have!'),
(4, 2, 'Animal', ' Our ever-growing selection of beautiful animal T-shirts represents critters from everywhere, both wild and domestic. If you don''t see the T-shirt with the animal you''re looking for, tell us and we''ll find it!'),
(5, 2, 'Flower', 'These unique and beautiful flower T-shirts are just the item for the gardener, flower arranger, florist, or general lover of things beautiful. Surprise the flower in your life with one of the beautiful botanical T-shirts or just get a few for yourself!'),
(6, 3, 'Christmas', ' Because this is a unique Christmas T-shirt that you''ll only wear a few times a year, it will probably last for decades (unless some grinch nabs it from you, of course). Far into the future, after you''re gone, your grandkids will pull it out and argue over who gets to wear it. What great snapshots they''ll make dressed in Grandpa or Grandma''s incredibly tasteful and unique Christmas T-shirt! Yes, everyone will remember you forever and what a silly goof you were when you would wear only your Santa beard and cap so you wouldn''t cover up your nifty T-shirt.'),
(7, 3, 'Valentine''s', 'For the more timid, all you have to do is wear your heartfelt message to get it across. Buy one for you and your sweetie(s) today!');
-- Populate product table
INSERT INTO `product` (`product_id`, `name`, `description`, `price`, `discounted_price`, `image`, `image_2`, `thumbnail`, `display`) VALUES
(1, 'Arc d''Triomphe', 'This beautiful and iconic T-shirt will no doubt lead you to your own triumph.', 14.99, 0.00, 'arc-d-triomphe.gif', 'arc-d-triomphe-2.gif', 'arc-d-triomphe-thumbnail.gif', 0),
(2, 'Chartres Cathedral', '"The Fur Merchants". Not all the beautiful stained glass in the great cathedrals depicts saints and angels! Lay aside your furs for the summer and wear this beautiful T-shirt!', 16.95, 15.95, 'chartres-cathedral.gif', 'chartres-cathedral-2.gif', 'chartres-cathedral-thumbnail.gif', 2),
(3, 'Coat of Arms', 'There''s good reason why the ship plays a prominent part on this shield!', 14.50, 0.00, 'coat-of-arms.gif', 'coat-of-arms-2.gif', 'coat-of-arms-thumbnail.gif', 0),
(4, 'Gallic Cock', 'This fancy chicken is perhaps the most beloved of all French symbols. Unfortunately, there are only a few hundred left, so you''d better get your T-shirt now!', 18.99, 16.99, 'gallic-cock.gif', 'gallic-cock-2.gif', 'gallic-cock-thumbnail.gif', 2),
(5, 'Marianne', 'She symbolizes the "Triumph of the Republic" and has been depicted many different ways in the history of France, as you will see below!', 15.95, 14.95, 'marianne.gif', 'marianne-2.gif', 'marianne-thumbnail.gif', 2),
(6, 'Alsace', 'It was in this region of France that Gutenberg perfected his movable type. If he could only see what he started!', 16.50, 0.00, 'alsace.gif', 'alsace-2.gif', 'alsace-thumbnail.gif', 0),
(7, 'Apocalypse Tapestry', 'One of the most famous tapestries of the Loire Valley, it dates from the 14th century. The T-shirt is of more recent vintage, however.', 20.00, 18.95, 'apocalypse-tapestry.gif', 'apocalypse-tapestry-2.gif', 'apocalypse-tapestry-thumbnail.gif', 0),
(8, 'Centaur', 'There were never any lady centaurs, so these guys had to mate with nymphs and mares. No wonder they were often in such bad moods!', 14.99, 0.00, 'centaur.gif', 'centaur-2.gif', 'centaur-thumbnail.gif', 0),
(9, 'Corsica', 'Borrowed from Spain, the "Moor''s head" may have celebrated the Christians'' victory over the Moslems in that country.', 22.00, 0.00, 'corsica.gif', 'corsica-2.gif', 'corsica-thumbnail.gif', 0),
(10, 'Haute Couture', 'This stamp publicized the dress making industry. Use it to celebrate the T-shirt industry!', 15.99, 14.95, 'haute-couture.gif', 'haute-couture-2.gif', 'haute-couture-thumbnail.gif', 3),
(11, 'Iris', 'Iris was the Goddess of the Rainbow, daughter of the Titans Thaumas and Electra. Are you up to this T-shirt?!', 17.50, 0.00, 'iris.gif', 'iris-2.gif', 'iris-thumbnail.gif', 0),
(12, 'Lorraine', 'The largest American cemetery in France is located in Lorraine and most of the folks there still appreciate that fact.', 16.95, 0.00, 'lorraine.gif', 'lorraine-2.gif', 'lorraine-thumbnail.gif', 0),
(13, 'Mercury', 'Besides being the messenger of the gods, did you know that Mercury was also the god of profit and commerce? This T-shirt is for business owners!', 21.99, 18.95, 'mercury.gif', 'mercury-2.gif', 'mercury-thumbnail.gif', 2),
(14, 'County of Nice', 'Nice is so nice that it has been fought over for millennia, but now it all belongs to France.', 12.95, 0.00, 'county-of-nice.gif', 'county-of-nice-2.gif', 'county-of-nice-thumbnail.gif', 0),
(15, 'Notre Dame', 'Commemorating the 800th anniversary of the famed cathedral.', 18.50, 16.99, 'notre-dame.gif', 'notre-dame-2.gif', 'notre-dame-thumbnail.gif', 2),
(16, 'Paris Peace Conference', 'The resulting treaties allowed Italy, Romania, Hungary, Bulgaria, and Finland to reassume their responsibilities as sovereign states in international affairs and thus qualify for membership in the UN.', 16.95, 15.99, 'paris-peace-conference.gif', 'paris-peace-conference-2.gif', 'paris-peace-conference-thumbnail.gif', 2),
(17, 'Sarah Bernhardt', 'The "Divine Sarah" said this about Americans: "You are younger than we as a race, you are perhaps barbaric, but what of it? You are still in the molding. Your spirit is superb. It is what helped us win the war." Perhaps we''re still barbaric but we''re still winning wars for them too!', 14.99, 0.00, 'sarah-bernhardt.gif', 'sarah-bernhardt-2.gif', 'sarah-bernhardt-thumbnail.gif', 0),
(18, 'Hunt', 'A scene from "Les Tres Riches Heures," a medieval "book of hours" containing the text for each liturgical hour of the day. This scene is from a 14th century painting.', 16.99, 15.95, 'hunt.gif', 'hunt-2.gif', 'hunt-thumbnail.gif', 2),
(19, 'Italia', 'The War had just ended when this stamp was designed, and even so, there was enough optimism to show the destroyed oak tree sprouting again from its stump! What a beautiful T-shirt!', 22.00, 18.99, 'italia.gif', 'italia-2.gif', 'italia-thumbnail.gif', 2),
(20, 'Torch', 'The light goes on! Carry the torch with this T-shirt and be a beacon of hope for the world!', 19.99, 17.95, 'torch.gif', 'torch-2.gif', 'torch-thumbnail.gif', 2),
(21, 'Espresso', 'The winged foot of Mercury speeds the Special Delivery mail to its destination. In a hurry? This T-shirt is for you!', 16.95, 0.00, 'espresso.gif', 'espresso-2.gif', 'espresso-thumbnail.gif', 0),
(22, 'Galileo', 'This beautiful T-shirt does honor to one of Italy''s (and the world''s) most famous scientists. Show your appreciation for the education you''ve received!', 14.99, 0.00, 'galileo.gif', 'galileo-2.gif', 'galileo-thumbnail.gif', 0),
(23, 'Italian Airmail', 'Thanks to modern Italian post, folks were able to reach out and touch each other. Or at least so implies this image. This is a very fast and friendly T-shirt--you''ll make friends with it!', 21.00, 17.99, 'italian-airmail.gif', 'italian-airmail-2.gif', 'italian-airmail-thumbnail.gif', 0),
(24, 'Mazzini', 'Giuseppe Mazzini is considered one of the patron saints of the "Risorgimiento." Wear this beautiful T-shirt to tell the world you agree!', 20.50, 18.95, 'mazzini.gif', 'mazzini-2.gif', 'mazzini-thumbnail.gif', 2),
(25, 'Romulus & Remus', 'Back in 753 BC, so the story goes, Romulus founded the city of Rome (in competition with Remus, who founded a city on another hill). Their adopted mother is shown in this image. When did they suspect they were adopted?', 17.99, 16.95, 'romulus-remus.gif', 'romulus-remus-2.gif', 'romulus-remus-thumbnail.gif', 2),
(26, 'Italy Maria', 'This beautiful image of the Virgin is from a work by Raphael, whose life and death it honors. It is one of our most popular T-shirts!', 14.00, 0.00, 'italy-maria.gif', 'italy-maria-2.gif', 'italy-maria-thumbnail.gif', 0),
(27, 'Italy Jesus', 'This image of Jesus teaching the gospel was issued to commemorate the third centenary of the "propagation of the faith." Now you can do your part with this T-shirt!', 16.95, 0.00, 'italy-jesus.gif', 'italy-jesus-2.gif', 'italy-jesus-thumbnail.gif', 0),
(28, 'St. Francis', 'Here St. Francis is receiving his vision. This dramatic and attractive stamp was issued on the 700th anniversary of that event.', 22.00, 18.99, 'st-francis.gif', 'st-francis-2.gif', 'st-francis-thumbnail.gif', 2),
(29, 'Irish Coat of Arms', 'This was one of the first stamps of the new Irish Republic, and it makes a T-shirt you''ll be proud to wear on St. Paddy''s Day!', 14.99, 0.00, 'irish-coat-of-arms.gif', 'irish-coat-of-arms-2.gif', 'irish-coat-of-arms-thumbnail.gif', 0),
(30, 'Easter Rebellion', 'The Easter Rebellion of 1916 was a defining moment in Irish history. Although only a few hundred participated and the British squashed it in a week, its leaders were executed, which galvanized the uncommitted.', 19.00, 16.95, 'easter-rebellion.gif', 'easter-rebellion-2.gif', 'easter-rebellion-thumbnail.gif', 2),
(31, 'Guiness', 'Class! Who is this man and why is he important enough for his own T-shirt?!', 15.00, 0.00, 'guiness.gif', 'guiness-2.gif', 'guiness-thumbnail.gif', 0),
(32, 'St. Patrick', 'This stamp commemorated the 1500th anniversary of the revered saint''s death. Is there a more perfect St. Patrick''s Day T-shirt?!', 20.50, 17.95, 'st-patrick.gif', 'st-patrick-2.gif', 'st-patrick-thumbnail.gif', 0),
(33, 'St. Peter', 'This T-shirt commemorates the holy year of 1950.', 16.00, 14.95, 'st-peter.gif', 'st-peter-2.gif', 'st-peter-thumbnail.gif', 2),
(34, 'Sword of Light', 'This was the very first Irish postage stamp, and what a beautiful and cool T-shirt it makes for the Irish person in your life!', 14.99, 0.00, 'sword-of-light.gif', 'sword-of-light-2.gif', 'sword-of-light-thumbnail.gif', 0),
(35, 'Thomas Moore', 'One of the greatest if not the greatest of Irish poets and writers, Moore led a very interesting life, though plagued with tragedy in a somewhat typically Irish way. Remember "The Last Rose of Summer"?', 15.95, 14.99, 'thomas-moore.gif', 'thomas-moore-2.gif', 'thomas-moore-thumbnail.gif', 2),
(36, 'Visit the Zoo', 'This WPA poster is a wonderful example of the art produced by the Works Projects Administration during the Depression years. Do you feel like you sometimes live or work in a zoo? Then this T-shirt is for you!', 20.00, 16.95, 'visit-the-zoo.gif', 'visit-the-zoo-2.gif', 'visit-the-zoo-thumbnail.gif', 2),
(37, 'Sambar', 'This handsome Malayan Sambar was a pain in the neck to get to pose like this, and all so you could have this beautiful retro animal T-shirt!', 19.00, 17.99, 'sambar.gif', 'sambar-2.gif', 'sambar-thumbnail.gif', 2),
(38, 'Buffalo', 'Of all the critters in our T-shirt zoo, this is one of our most popular. A classic animal T-shirt for an individual like yourself!', 14.99, 0.00, 'buffalo.gif', 'buffalo-2.gif', 'buffalo-thumbnail.gif', 0),
(39, 'Mustache Monkey', 'This fellow is more than equipped to hang out with that tail of his, just like you''ll be fit for hanging out with this great animal T-shirt!', 20.00, 17.95, 'mustache-monkey.gif', 'mustache-monkey-2.gif', 'mustache-monkey-thumbnail.gif', 2),
(40, 'Colobus', 'Why is he called "Colobus," "the mutilated one"? He doesn''t have a thumb, just four fingers! He is far from handicapped, however; his hands make him the great swinger he is. Speaking of swinging, that''s what you''ll do with this beautiful animal T-shirt!', 17.00, 15.99, 'colobus.gif', 'colobus-2.gif', 'colobus-thumbnail.gif', 2),
(41, 'Canada Goose', 'Being on a major flyway for these guys, we know all about these majestic birds. They hang out in large numbers on a lake near our house and fly over constantly. Remember what Frankie Lane said? "I want to go where the wild goose goes!" And when you go, wear this cool Canada goose animal T-shirt.', 15.99, 0.00, 'canada-goose.gif', 'canada-goose-2.gif', 'canada-goose-thumbnail.gif', 0),
(42, 'Congo Rhino', 'Among land mammals, this white rhino is surpassed in size only by the elephant. He has a big fan base too, working hard to make sure he sticks around. You''ll be a fan of his, too, when people admire this unique and beautiful T-shirt on you!', 20.00, 18.99, 'congo-rhino.gif', 'congo-rhino-2.gif', 'congo-rhino-thumbnail.gif', 2),
(43, 'Equatorial Rhino', 'There''s a lot going on in this frame! A black rhino is checking out that python slithering off into the bush--or is he eyeing you? You can bet all eyes will be on you when you wear this T-shirt!', 19.95, 17.95, 'equatorial-rhino.gif', 'equatorial-rhino-2.gif', 'equatorial-rhino-thumbnail.gif', 2),
(44, 'Ethiopian Rhino', 'Another white rhino is honored in this classic design that bespeaks the Africa of the early century. This pointillist and retro T-shirt will definitely turn heads!', 16.00, 0.00, 'ethiopian-rhino.gif', 'ethiopian-rhino-2.gif', 'ethiopian-rhino-thumbnail.gif', 0),
(45, 'Dutch Sea Horse', 'I think this T-shirt is destined to be one of our most popular simply because it is one of our most beautiful!', 12.50, 0.00, 'dutch-sea-horse.gif', 'dutch-sea-horse-2.gif', 'dutch-sea-horse-thumbnail.gif', 0),
(46, 'Dutch Swans', 'This stamp was designed in the middle of the Nazi occupation, as was the one above. Together they reflect a spirit of beauty that evil could not suppress. Both of these T-shirts will make it impossible to suppress your artistic soul, too!', 21.00, 18.99, 'dutch-swans.gif', 'dutch-swans-2.gif', 'dutch-swans-thumbnail.gif', 2),
(47, 'Ethiopian Elephant', 'From the same series as the Ethiopian Rhino and the Ostriches, this stylish elephant T-shirt will mark you as a connoisseur of good taste!', 18.99, 16.95, 'ethiopian-elephant.gif', 'ethiopian-elephant-2.gif', 'ethiopian-elephant-thumbnail.gif', 2),
(48, 'Laotian Elephant', 'This working guy is proud to have his own stamp, and now he has his own T-shirt!', 21.00, 18.99, 'laotian-elephant.gif', 'laotian-elephant-2.gif', 'laotian-elephant-thumbnail.gif', 0),
(49, 'Liberian Elephant', 'And yet another Jumbo! You need nothing but a big heart to wear this T-shirt (or a big sense of style)!', 22.00, 17.50, 'liberian-elephant.gif', 'liberian-elephant-2.gif', 'liberian-elephant-thumbnail.gif', 2),
(50, 'Somali Ostriches', 'Another in an old series of beautiful stamps from Ethiopia. These big birds pack quite a wallop, and so will you when you wear this uniquely retro T-shirt!', 12.95, 0.00, 'somali-ostriches.gif', 'somali-ostriches-2.gif', 'somali-ostriches-thumbnail.gif', 0),
(51, 'Tankanyika Giraffe', 'The photographer had to stand on a step ladder for this handsome portrait, but his efforts paid off with an angle we seldom see of this lofty creature. This beautiful retro T-shirt would make him proud!', 15.00, 12.99, 'tankanyika-giraffe.gif', 'tankanyika-giraffe-2.gif', 'tankanyika-giraffe-thumbnail.gif', 3),
(52, 'Ifni Fish', 'This beautiful stamp was issued to commemorate National Colonial Stamp Day (you can do that when you have a colony). When you wear this fancy fish T-shirt, your friends will think it''s national T-shirt day!', 14.00, 0.00, 'ifni-fish.gif', 'ifni-fish-2.gif', 'ifni-fish-thumbnail.gif', 0),
(53, 'Sea Gull', 'A beautiful stamp from a small enclave in southern Morocco that belonged to Spain until 1969 makes a beautiful bird T-shirt.', 19.00, 16.95, 'sea-gull.gif', 'sea-gull-2.gif', 'sea-gull-thumbnail.gif', 2),
(54, 'King Salmon', 'You can fish them and eat them and now you can wear them with this classic animal T-shirt.', 17.95, 15.99, 'king-salmon.gif', 'king-salmon-2.gif', 'king-salmon-thumbnail.gif', 2),
(55, 'Laos Bird', 'This fellow is also known as the "White Crested Laughing Thrush." What''s he laughing at? Why, at the joy of being on your T-shirt!', 12.00, 0.00, 'laos-bird.gif', 'laos-bird-2.gif', 'laos-bird-thumbnail.gif', 0),
(56, 'Mozambique Lion', 'The Portuguese were too busy to run this colony themselves so they gave the Mozambique Company a charter to do it. I think there must be some pretty curious history related to that (the charter only lasted for 50 years)! If you''re a Leo, or know a Leo, you should seriously consider this T-shirt!', 15.99, 14.95, 'mozambique-lion.gif', 'mozambique-lion-2.gif', 'mozambique-lion-thumbnail.gif', 2),
(57, 'Peru Llama', 'This image is nearly 100 years old! Little did this little llama realize that he was going to be made immortal on the Web and on this very unique animal T-shirt (actually, little did he know at all)!', 21.50, 17.99, 'peru-llama.gif', 'peru-llama-2.gif', 'peru-llama-thumbnail.gif', 2),
(58, 'Romania Alsatian', 'If you know and love this breed, there''s no reason in the world that you shouldn''t buy this T-shirt right now!', 15.95, 0.00, 'romania-alsatian.gif', 'romania-alsatian-2.gif', 'romania-alsatian-thumbnail.gif', 0),
(59, 'Somali Fish', 'This is our most popular fish T-shirt, hands down. It''s a beauty, and if you wear this T-shirt, you''ll be letting the world know you''re a fine catch!', 19.95, 16.95, 'somali-fish.gif', 'somali-fish-2.gif', 'somali-fish-thumbnail.gif', 2),
(60, 'Trout', 'This beautiful image will warm the heart of any fisherman! You must know one if you''re not one yourself, so you must buy this T-shirt!', 14.00, 0.00, 'trout.gif', 'trout-2.gif', 'trout-thumbnail.gif', 0),
(61, 'Baby Seal', 'Ahhhhhh! This little harp seal would really prefer not to be your coat! But he would like to be your T-shirt!', 21.00, 18.99, 'baby-seal.gif', 'baby-seal-2.gif', 'baby-seal-thumbnail.gif', 2),
(62, 'Musk Ox', 'Some critters you just don''t want to fool with, and if I were facing this fellow I''d politely give him the trail! That is, of course, unless I were wearing this T-shirt.', 15.50, 0.00, 'musk-ox.gif', 'musk-ox-2.gif', 'musk-ox-thumbnail.gif', 0),
(63, 'Suvla Bay', ' In 1915, Newfoundland sent its Newfoundland Regiment to Suvla Bay in Gallipoli to fight the Turks. This classic image does them honor. Have you ever heard of them? Share the news with this great T-shirt!', 12.99, 0.00, 'suvla-bay.gif', 'suvla-bay-2.gif', 'suvla-bay-thumbnail.gif', 0),
(64, 'Caribou', 'There was a time when Newfoundland was a self-governing dominion of the British Empire, so it printed its own postage. The themes are as typically Canadian as can be, however, as shown by this "King of the Wilde" T-shirt!', 21.00, 19.95, 'caribou.gif', 'caribou-2.gif', 'caribou-thumbnail.gif', 2),
(65, 'Afghan Flower', 'This beautiful image was issued to celebrate National Teachers Day. Perhaps you know a teacher who would love this T-shirt?', 18.50, 16.99, 'afghan-flower.gif', 'afghan-flower-2.gif', 'afghan-flower-thumbnail.gif', 2),
(66, 'Albania Flower', 'Well, these crab apples started out as flowers, so that''s close enough for us! They still make for a uniquely beautiful T-shirt.', 16.00, 14.95, 'albania-flower.gif', 'albania-flower-2.gif', 'albania-flower-thumbnail.gif', 2),
(67, 'Austria Flower', 'Have you ever had nasturtiums on your salad? Try it--they''re almost as good as having them on your T-shirt!', 12.99, 0.00, 'austria-flower.gif', 'austria-flower-2.gif', 'austria-flower-thumbnail.gif', 0),
(68, 'Bulgarian Flower', 'For your interest (and to impress your friends), this beautiful stamp was issued to honor the George Dimitrov state printing works. You''ll need to know this when you wear the T-shirt.', 16.00, 14.99, 'bulgarian-flower.gif', 'bulgarian-flower-2.gif', 'bulgarian-flower-thumbnail.gif', 2),
(69, 'Colombia Flower', 'Celebrating the 75th anniversary of the Universal Postal Union, a date to mark on your calendar and on which to wear this T-shirt!', 14.50, 12.95, 'colombia-flower.gif', 'colombia-flower-2.gif', 'colombia-flower-thumbnail.gif', 1),
(70, 'Congo Flower', 'The Congo is not at a loss for beautiful flowers, and we''ve picked a few of them for your T-shirts.', 21.00, 17.99, 'congo-flower.gif', 'congo-flower-2.gif', 'congo-flower-thumbnail.gif', 2),
(71, 'Costa Rica Flower', 'This national flower of Costa Rica is one of our most beloved flower T-shirts (you can see one on Jill, above). You will surely stand out in this T-shirt!', 12.99, 0.00, 'costa-rica-flower.gif', 'costa-rica-flower.gif', 'costa-rica-flower-thumbnail.gif', 0),
(72, 'Gabon Flower', 'The combretum, also known as "jungle weed," is used in China as a cure for opium addiction. Unfortunately, when you wear this T-shirt, others may become hopelessly addicted to you!', 19.00, 16.95, 'gabon-flower.gif', 'gabon-flower-2.gif', 'gabon-flower-thumbnail.gif', 2),
(73, 'Ghana Flower', 'This is one of the first gingers to bloom in the spring--just like you when you wear this T-shirt!', 21.00, 18.99, 'ghana-flower.gif', 'ghana-flower-2.gif', 'ghana-flower-thumbnail.gif', 2),
(74, 'Israel Flower', 'This plant is native to the rocky and sandy regions of the western United States, so when you come across one, it really stands out. And so will you when you put on this beautiful T-shirt!', 19.50, 17.50, 'israel-flower.gif', 'israel-flower-2.gif', 'israel-flower-thumbnail.gif', 2),
(75, 'Poland Flower', 'A beautiful and sunny T-shirt for both spring and summer!', 16.95, 15.99, 'poland-flower.gif', 'poland-flower-2.gif', 'poland-flower-thumbnail.gif', 2),
(76, 'Romania Flower', 'Also known as the spring pheasant''s eye, this flower belongs on your T-shirt this summer to help you catch a few eyes.', 12.95, 0.00, 'romania-flower.gif', 'romania-flower-2.gif', 'romania-flower-thumbnail.gif', 0),
(77, 'Russia Flower', 'Someone out there who can speak Russian needs to tell me what this plant is. I''ll sell you the T-shirt for $10 if you can!', 21.00, 18.95, 'russia-flower.gif', 'russia-flower-2.gif', 'russia-flower-thumbnail.gif', 0),
(78, 'San Marino Flower', '"A white sport coat and a pink carnation, I''m all dressed up for the dance!" Well, how about a white T-shirt and a pink carnation?!', 19.95, 17.99, 'san-marino-flower.gif', 'san-marino-flower-2.gif', 'san-marino-flower-thumbnail.gif', 2),
(79, 'Uruguay Flower', 'The Indian Queen Anahi was the ugliest woman ever seen. But instead of living a slave when captured by the Conquistadores, she immolated herself in a fire and was reborn the most beautiful of flowers: the ceibo, national flower of Uruguay. Of course, you won''t need to burn to wear this T-shirt, but you may cause some pretty hot glances to be thrown your way!', 17.99, 16.99, 'uruguay-flower.gif', 'uruguay-flower-2.gif', 'uruguay-flower-thumbnail.gif', 2),
(80, 'Snow Deer', 'Tarmo has produced some wonderful Christmas T-shirts for us, and we hope to have many more soon.', 21.00, 18.95, 'snow-deer.gif', 'snow-deer-2.gif', 'snow-deer-thumbnail.gif', 2),
(81, 'Holly Cat', 'Few things make a cat happier at Christmas than a tree suddenly appearing in the house!', 15.99, 0.00, 'holly-cat.gif', 'holly-cat-2.gif', 'holly-cat-thumbnail.gif', 0),
(82, 'Christmas Seal', 'Is this your grandmother? It could be, you know, and I''d bet she''d recognize the Christmas seal on this cool Christmas T-shirt.', 19.99, 17.99, 'christmas-seal.gif', 'christmas-seal-2.gif', 'christmas-seal-thumbnail.gif', 2),
(83, 'Weather Vane', 'This weather vane dates from the 1830''s and is still showing which way the wind blows! Trumpet your arrival with this unique Christmas T-shirt.', 15.95, 14.99, 'weather-vane.gif', 'weather-vane-2.gif', 'weather-vane-thumbnail.gif', 2),
(84, 'Mistletoe', 'This well-known parasite and killer of trees was revered by the Druids, who would go out and gather it with great ceremony. Youths would go about with it to announce the new year. Eventually more engaging customs were attached to the strange plant, and we''re here to see that they continue with these cool Christmas T-shirts.', 19.00, 17.99, 'mistletoe.gif', 'mistletoe-2.gif', 'mistletoe-thumbnail.gif', 3),
(85, 'Altar Piece', 'This beautiful angel Christmas T-shirt is awaiting the opportunity to adorn your chest!', 20.50, 18.50, 'altar-piece.gif', 'altar-piece-2.gif', 'altar-piece-thumbnail.gif', 2),
(86, 'The Three Wise Men', 'This is a classic rendition of one of the season�s most beloved stories, and now showing on a Christmas T-shirt for you!', 12.99, 0.00, 'the-three-wise-men.gif', 'the-three-wise-men-2.gif', 'the-three-wise-men-thumbnail.gif', 0),
(87, 'Christmas Tree', 'Can you get more warm and folksy than this classic Christmas T-shirt?', 20.00, 17.95, 'christmas-tree.gif', 'christmas-tree-2.gif', 'christmas-tree-thumbnail.gif', 2),
(88, 'Madonna & Child', 'This exquisite image was painted by Filipino Lippi, a 15th century Italian artist. I think he would approve of it on a Going Postal Christmas T-shirt!', 21.95, 18.50, 'madonna-child.gif', 'madonna-child-2.gif', 'madonna-child-thumbnail.gif', 0),
(89, 'The Virgin Mary', 'This stained glass window is found in Glasgow Cathedral, Scotland, and was created by Gabriel Loire of France, one of the most prolific of artists in this medium--and now you can have it on this wonderful Christmas T-shirt.', 16.95, 15.95, 'the-virgin-mary.gif', 'the-virgin-mary-2.gif', 'the-virgin-mary-thumbnail.gif', 2),
(90, 'Adoration of the Kings', 'This design is from a miniature in the Evangelistary of Matilda in Nonantola Abbey, from the 12th century. As a Christmas T-shirt, it will cause you to be adored!', 17.50, 16.50, 'adoration-of-the-kings.gif', 'adoration-of-the-kings-2.gif', 'adoration-of-the-kings-thumbnail.gif', 2),
(91, 'A Partridge in a Pear Tree', 'The original of this beautiful stamp is by Jamie Wyeth and is in the National Gallery of Art. The next best is on our beautiful Christmas T-shirt!', 14.99, 0.00, 'a-partridge-in-a-pear-tree.gif', 'a-partridge-in-a-pear-tree-2.gif', 'a-partridge-in-a-pear-tree-thumbnail.gif', 0),
(92, 'St. Lucy', 'This is a tiny detail of a large work called "Mary, Queen of Heaven," done in 1480 by a Flemish master known only as "The Master of St. Lucy Legend." The original is in a Bruges church. The not-quite-original is on this cool Christmas T-shirt.', 18.95, 0.00, 'st-lucy.gif', 'st-lucy-2.gif', 'st-lucy-thumbnail.gif', 0),
(93, 'St. Lucia', 'Saint Lucia''s tradition is an important part of Swedish Christmas, and an important part of that are the candles. Next to the candles in importance is this popular Christmas T-shirt!', 19.00, 17.95, 'st-lucia.gif', 'st-lucia-2.gif', 'st-lucia-thumbnail.gif', 2),
(94, 'Swede Santa', 'Santa as a child. You must know a child who would love this cool Christmas T-shirt!?', 21.00, 18.50, 'swede-santa.gif', 'swede-santa-2.gif', 'swede-santa-thumbnail.gif', 2),
(95, 'Wreath', 'Hey! I''ve got an idea! Why not buy two of these cool Christmas T-shirts so you can wear one and tack the other one to your door?!', 18.99, 16.99, 'wreath.gif', 'wreath-2.gif', 'wreath-thumbnail.gif', 2),
(96, 'Love', 'Here''s a Valentine''s day T-shirt that will let you say it all in just one easy glance--there''s no mistake about it!', 19.00, 17.50, 'love.gif', 'love-2.gif', 'love-thumbnail.gif', 2),
(97, 'Birds', 'Is your heart all aflutter? Show it with this T-shirt!', 21.00, 18.95, 'birds.gif', 'birds-2.gif', 'birds-thumbnail.gif', 2),
(98, 'Kat Over New Moon', 'Love making you feel lighthearted?', 14.99, 0.00, 'kat-over-new-moon.gif', 'kat-over-new-moon-2.gif', 'kat-over-new-moon-thumbnail.gif', 0),
(99, 'Thrilling Love', 'This girl''s got her hockey hunk right where she wants him!', 21.00, 18.50, 'thrilling-love.gif', 'thrilling-love-2.gif', 'thrilling-love-thumbnail.gif', 2),
(100, 'The Rapture of Psyche', 'Now we''re getting a bit more serious!', 18.95, 16.99, 'the-rapture-of-psyche.gif', 'the-rapture-of-psyche-2.gif', 'the-rapture-of-psyche-thumbnail.gif', 2),
(101, 'The Promise of Spring', 'With Valentine''s Day come, can Spring be far behind?', 21.00, 19.50, 'the-promise-of-spring.gif', 'the-promise-of-spring-2.gif', 'the-promise-of-spring-thumbnail.gif', 0);
-- Populate product_category table
INSERT INTO `product_category` (`product_id`, `category_id`) VALUES
(1, 1), (2, 1), (3, 1), (4, 1), (5, 1), (6, 1), (7, 1), (8, 1), (9, 1),
(10, 1), (11, 1), (12, 1), (13, 1), (14, 1), (15, 1), (16, 1), (17, 1),
(18, 1), (19, 2), (20, 2), (21, 2), (22, 2), (23, 2), (24, 2), (25, 2),
(26, 2), (27, 2), (28, 2), (29, 3), (30, 3), (31, 3), (32, 3), (33, 3),
(34, 3), (35, 3), (36, 4), (37, 4), (38, 4), (39, 4), (40, 4), (41, 4),
(42, 4), (43, 4), (44, 4), (45, 4), (46, 4), (47, 4), (48, 4), (49, 4),
(50, 4), (51, 4), (52, 4), (53, 4), (54, 4), (55, 4), (56, 4), (57, 4),
(58, 4), (59, 4), (60, 4), (61, 4), (62, 4), (63, 4), (64, 4), (81, 4),
(97, 4), (98, 4), (65, 5), (66, 5), (67, 5), (68, 5), (69, 5), (70, 5),
(71, 5), (72, 5), (73, 5), (74, 5), (75, 5), (76, 5), (77, 5), (78, 5),
(79, 5), (80, 6), (81, 6), (82, 6), (83, 6), (84, 6), (85, 6), (86, 6),
(87, 6), (88, 6), (89, 6), (90, 6), (91, 6), (92, 6), (93, 6), (94, 6),
(95, 6), (96, 7), (97, 7), (98, 7), (99, 7), (100, 7), (101, 7);
-- Populate attribute table
INSERT INTO `attribute` (`attribute_id`, `name`) VALUES
(1, 'Size'), (2, 'Color');
-- Populate attribute_value table
INSERT INTO `attribute_value` (`attribute_value_id`, `attribute_id`, `value`) VALUES
(1, 1, 'S'), (2, 1, 'M'), (3, 1, 'L'), (4, 1, 'XL'), (5, 1, 'XXL'),
(6, 2, 'White'), (7, 2, 'Black'), (8, 2, 'Red'), (9, 2, 'Orange'),
(10, 2, 'Yellow'), (11, 2, 'Green'), (12, 2, 'Blue'),
(13, 2, 'Indigo'), (14, 2, 'Purple');
-- Populate product_attribute table
INSERT INTO `product_attribute` (`product_id`, `attribute_value_id`)
SELECT `p`.`product_id`, `av`.`attribute_value_id`
FROM `product` `p`, `attribute_value` `av`;
-- Populate shipping_region table
INSERT INTO `shipping_region` (`shipping_region_id`, `shipping_region`) VALUES
(1, 'Please Select') , (2, 'US / Canada'),
(3, 'Europe'), (4, 'Rest of World');
-- Populate shipping table
INSERT INTO `shipping` (`shipping_id`, `shipping_type`,
`shipping_cost`, `shipping_region_id`) VALUES
(1, 'Next Day Delivery ($20)', 20.00, 2),
(2, '3-4 Days ($10)', 10.00, 2),
(3, '7 Days ($5)', 5.00, 2),
(4, 'By air (7 days, $25)', 25.00, 3),
(5, 'By sea (28 days, $10)', 10.00, 3),
(6, 'By air (10 days, $35)', 35.00, 4),
(7, 'By sea (28 days, $30)', 30.00, 4);
-- Populate tax table
INSERT INTO `tax` (`tax_id`, `tax_type`, `tax_percentage`) VALUES
(1, 'Sales Tax at 8.5%', 8.50),
(2, 'No Tax', 0.00);
-- Change DELIMITER to $$
DELIMITER $$
-- Create catalog_get_departments_list stored procedure
CREATE PROCEDURE catalog_get_departments_list()
BEGIN
SELECT department_id, name FROM department ORDER BY department_id;
END$$
-- Create catalog_get_department_details stored procedure
CREATE PROCEDURE catalog_get_department_details(IN inDepartmentId INT)
BEGIN
SELECT name, description
FROM department
WHERE department_id = inDepartmentId;
END$$
-- Create catalog_get_categories_list stored procedure
CREATE PROCEDURE catalog_get_categories_list(IN inDepartmentId INT)
BEGIN
SELECT category_id, name
FROM category
WHERE department_id = inDepartmentId
ORDER BY category_id;
END$$
-- Create catalog_get_category_details stored procedure
CREATE PROCEDURE catalog_get_category_details(IN inCategoryId INT)
BEGIN
SELECT name, description
FROM category
WHERE category_id = inCategoryId;
END$$
-- Create catalog_count_products_in_category stored procedure
CREATE PROCEDURE catalog_count_products_in_category(IN inCategoryId INT)
BEGIN
SELECT COUNT(*) AS categories_count
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = inCategoryId;
END$$
-- Create catalog_get_products_in_category stored procedure
CREATE PROCEDURE catalog_get_products_in_category(
IN inCategoryId INT, IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
-- Prepare statement
PREPARE statement FROM
"SELECT p.product_id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, p.thumbnail
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = ?
ORDER BY p.display DESC
LIMIT ?, ?";
-- Define query parameters
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inCategoryId;
SET @p4 = inStartItem;
SET @p5 = inProductsPerPage;
-- Execute the statement
EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
END$$
-- Create catalog_count_products_on_department stored procedure
CREATE PROCEDURE catalog_count_products_on_department(IN inDepartmentId INT)
BEGIN
SELECT DISTINCT COUNT(*) AS products_on_department_count
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
INNER JOIN category c
ON pc.category_id = c.category_id
WHERE (p.display = 2 OR p.display = 3)
AND c.department_id = inDepartmentId;
END$$
-- Create catalog_get_products_on_department stored procedure
CREATE PROCEDURE catalog_get_products_on_department(
IN inDepartmentId INT, IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT DISTINCT p.product_id, p.name,
IF(LENGTH(p.description) <= ?,
p.description,
CONCAT(LEFT(p.description, ?),
'...')) AS description,
p.price, p.discounted_price, p.thumbnail
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
INNER JOIN category c
ON pc.category_id = c.category_id
WHERE (p.display = 2 OR p.display = 3)
AND c.department_id = ?
ORDER BY p.display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inDepartmentId;
SET @p4 = inStartItem;
SET @p5 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
END$$
-- Create catalog_count_products_on_catalog stored procedure
CREATE PROCEDURE catalog_count_products_on_catalog()
BEGIN
SELECT COUNT(*) AS products_on_catalog_count
FROM product
WHERE display = 1 OR display = 3;
END$$
-- Create catalog_get_products_on_catalog stored procedure
CREATE PROCEDURE catalog_get_products_on_catalog(
IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
PREPARE statement FROM
"SELECT product_id, name,
IF(LENGTH(description) <= ?,
description,
CONCAT(LEFT(description, ?),
'...')) AS description,
price, discounted_price, thumbnail
FROM product
WHERE display = 1 OR display = 3
ORDER BY display DESC
LIMIT ?, ?";
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inShortProductDescriptionLength;
SET @p3 = inStartItem;
SET @p4 = inProductsPerPage;
EXECUTE statement USING @p1, @p2, @p3, @p4;
END$$
-- Create catalog_get_product_details stored procedure
CREATE PROCEDURE catalog_get_product_details(IN inProductId INT)
BEGIN
SELECT product_id, name, description,
price, discounted_price, image, image_2
FROM product
WHERE product_id = inProductId;
END$$
-- Create catalog_get_product_locations stored procedure
CREATE PROCEDURE catalog_get_product_locations(IN inProductId INT)
BEGIN
SELECT c.category_id, c.name AS category_name, c.department_id,
(SELECT name
FROM department
WHERE department_id = c.department_id) AS department_name
-- Subquery returns the name of the department of the category
FROM category c
WHERE c.category_id IN
(SELECT category_id
FROM product_category
WHERE product_id = inProductId);
-- Subquery returns the category IDs a product belongs to
END$$
-- Create catalog_get_product_attributes stored procedure
CREATE PROCEDURE catalog_get_product_attributes(IN inProductId INT)
BEGIN
SELECT a.name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id IN
(SELECT attribute_value_id
FROM product_attribute
WHERE product_id = inProductId)
ORDER BY a.name;
END$$
-- Create catalog_get_department_name stored procedure
CREATE PROCEDURE catalog_get_department_name(IN inDepartmentId INT)
BEGIN
SELECT name FROM department WHERE department_id = inDepartmentId;
END$$
-- Create catalog_get_category_name stored procedure
CREATE PROCEDURE catalog_get_category_name(IN inCategoryId INT)
BEGIN
SELECT name FROM category WHERE category_id = inCategoryId;
END$$
-- Create catalog_get_product_name stored procedure
CREATE PROCEDURE catalog_get_product_name(IN inProductId INT)
BEGIN
SELECT name FROM product WHERE product_id = inProductId;
END$$
-- Create catalog_count_search_result stored procedure
CREATE PROCEDURE catalog_count_search_result(
IN inSearchString TEXT, IN inAllWords VARCHAR(3))
BEGIN
IF inAllWords = "on" THEN
PREPARE statement FROM
"SELECT count(*)
FROM product
WHERE MATCH (name, description) AGAINST (? IN BOOLEAN MODE)";
ELSE
PREPARE statement FROM
"SELECT count(*)
FROM product
WHERE MATCH (name, description) AGAINST (?)";
END IF;
SET @p1 = inSearchString;
EXECUTE statement USING @p1;
END$$
-- Create catalog_search stored procedure
CREATE PROCEDURE catalog_search(
IN inSearchString TEXT, IN inAllWords VARCHAR(3),
IN inShortProductDescriptionLength INT,
IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
IF inAllWords = "on" THEN
PREPARE statement FROM
"SELECT product_id, name,
IF(LENGTH(description) <= ?,
description,
CONCAT(LEFT(description, ?),
'...')) AS description,
price, discounted_price, thumbnail
FROM product
WHERE MATCH (name, description)
AGAINST (? IN BOOLEAN MODE)
ORDER BY MATCH (name, description)
AGAINST (? IN BOOLEAN MODE) DESC
LIMIT ?, ?";
ELSE
PREPARE statement FROM
"SELECT product_id, name,
IF(LENGTH(description) <= ?,
description,
CONCAT(LEFT(description, ?),
'...')) AS description,
price, discounted_price, thumbnail
FROM product
WHERE MATCH (name, description) AGAINST (?)
ORDER BY MATCH (name, description) AGAINST (?) DESC
LIMIT ?, ?";
END IF;
SET @p1 = inShortProductDescriptionLength;
SET @p2 = inSearchString;
SET @p3 = inStartItem;
SET @p4 = inProductsPerPage;
EXECUTE statement USING @p1, @p1, @p2, @p2, @p3, @p4;
END$$
-- Create catalog_get_departments stored procedure
CREATE PROCEDURE catalog_get_departments()
BEGIN
SELECT department_id, name, description
FROM department
ORDER BY department_id;
END$$
-- Create catalog_add_department stored procedure
CREATE PROCEDURE catalog_add_department(
IN inName VARCHAR(100), IN inDescription VARCHAR(1000))
BEGIN
INSERT INTO department (name, description)
VALUES (inName, inDescription);
END$$
-- Create catalog_update_department stored procedure
CREATE PROCEDURE catalog_update_department(IN inDepartmentId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000))
BEGIN
UPDATE department
SET name = inName, description = inDescription
WHERE department_id = inDepartmentId;
END$$
-- Create catalog_delete_department stored procedure
CREATE PROCEDURE catalog_delete_department(IN inDepartmentId INT)
BEGIN
DECLARE categoryRowsCount INT;
SELECT count(*)
FROM category
WHERE department_id = inDepartmentId
INTO categoryRowsCount;
IF categoryRowsCount = 0 THEN
DELETE FROM department WHERE department_id = inDepartmentId;
SELECT 1;
ELSE
SELECT -1;
END IF;
END$$
-- Create catalog_get_department_categories stored procedure
CREATE PROCEDURE catalog_get_department_categories(IN inDepartmentId INT)
BEGIN
SELECT category_id, name, description
FROM category
WHERE department_id = inDepartmentId
ORDER BY category_id;
END$$
-- Create catalog_add_category stored procedure
CREATE PROCEDURE catalog_add_category(IN inDepartmentId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000))
BEGIN
INSERT INTO category (department_id, name, description)
VALUES (inDepartmentId, inName, inDescription);
END$$
-- Create catalog_update_category stored procedure
CREATE PROCEDURE catalog_update_category(IN inCategoryId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000))
BEGIN
UPDATE category
SET name = inName, description = inDescription
WHERE category_id = inCategoryId;
END$$
-- Create catalog_delete_category stored procedure
CREATE PROCEDURE catalog_delete_category(IN inCategoryId INT)
BEGIN
DECLARE productCategoryRowsCount INT;
SELECT count(*)
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = inCategoryId
INTO productCategoryRowsCount;
IF productCategoryRowsCount = 0 THEN
DELETE FROM category WHERE category_id = inCategoryId;
SELECT 1;
ELSE
SELECT -1;
END IF;
END$$
-- Create catalog_get_attributes stored procedure
CREATE PROCEDURE catalog_get_attributes()
BEGIN
SELECT attribute_id, name FROM attribute ORDER BY attribute_id;
END$$
-- Create catalog_add_attribute stored procedure
CREATE PROCEDURE catalog_add_attribute(IN inName VARCHAR(100))
BEGIN
INSERT INTO attribute (name) VALUES (inName);
END$$
-- Create catalog_update_attribute stored procedure
CREATE PROCEDURE catalog_update_attribute(
IN inAttributeId INT, IN inName VARCHAR(100))
BEGIN
UPDATE attribute SET name = inName WHERE attribute_id = inAttributeId;
END$$
-- Create catalog_delete_attribute stored procedure
CREATE PROCEDURE catalog_delete_attribute(IN inAttributeId INT)
BEGIN
DECLARE attributeRowsCount INT;
SELECT count(*)
FROM attribute_value
WHERE attribute_id = inAttributeId
INTO attributeRowsCount;
IF attributeRowsCount = 0 THEN
DELETE FROM attribute WHERE attribute_id = inAttributeId;
SELECT 1;
ELSE
SELECT -1;
END IF;
END$$
-- Create catalog_get_attribute_details stored procedure
CREATE PROCEDURE catalog_get_attribute_details(IN inAttributeId INT)
BEGIN
SELECT attribute_id, name
FROM attribute
WHERE attribute_id = inAttributeId;
END$$
-- Create catalog_get_attribute_values stored procedure
CREATE PROCEDURE catalog_get_attribute_values(IN inAttributeId INT)
BEGIN
SELECT attribute_value_id, value
FROM attribute_value
WHERE attribute_id = inAttributeId
ORDER BY attribute_id;
END$$
-- Create catalog_add_attribute_value stored procedure
CREATE PROCEDURE catalog_add_attribute_value(
IN inAttributeId INT, IN inValue VARCHAR(100))
BEGIN
INSERT INTO attribute_value (attribute_id, value)
VALUES (inAttributeId, inValue);
END$$
-- Create catalog_update_attribute_value stored procedure
CREATE PROCEDURE catalog_update_attribute_value(
IN inAttributeValueId INT, IN inValue VARCHAR(100))
BEGIN
UPDATE attribute_value
SET value = inValue
WHERE attribute_value_id = inAttributeValueId;
END$$
-- Create catalog_delete_attribute_value stored procedure
CREATE PROCEDURE catalog_delete_attribute_value(IN inAttributeValueId INT)
BEGIN
DECLARE productAttributeRowsCount INT;
SELECT count(*)
FROM product p
INNER JOIN product_attribute pa
ON p.product_id = pa.product_id
WHERE pa.attribute_value_id = inAttributeValueId
INTO productAttributeRowsCount;
IF productAttributeRowsCount = 0 THEN
DELETE FROM attribute_value WHERE attribute_value_id = inAttributeValueId;
SELECT 1;
ELSE
SELECT -1;
END IF;
END$$
-- Create catalog_get_category_products stored procedure
CREATE PROCEDURE catalog_get_category_products(IN inCategoryId INT)
BEGIN
SELECT p.product_id, p.name, p.description, p.price,
p.discounted_price
FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = inCategoryId
ORDER BY p.product_id;
END$$
-- Create catalog_add_product_to_category stored procedure
CREATE PROCEDURE catalog_add_product_to_category(IN inCategoryId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000),
IN inPrice DECIMAL(10, 2))
BEGIN
DECLARE productLastInsertId INT;
INSERT INTO product (name, description, price)
VALUES (inName, inDescription, inPrice);
SELECT LAST_INSERT_ID() INTO productLastInsertId;
INSERT INTO product_category (product_id, category_id)
VALUES (productLastInsertId, inCategoryId);
END$$
-- Create catalog_update_product stored procedure
CREATE PROCEDURE catalog_update_product(IN inProductId INT,
IN inName VARCHAR(100), IN inDescription VARCHAR(1000),
IN inPrice DECIMAL(10, 2), IN inDiscountedPrice DECIMAL(10, 2))
BEGIN
UPDATE product
SET name = inName, description = inDescription, price = inPrice,
discounted_price = inDiscountedPrice
WHERE product_id = inProductId;
END$$
-- Create catalog_remove_product_from_category stored procedure
CREATE PROCEDURE catalog_remove_product_from_category(
IN inProductId INT, IN inCategoryId INT)
BEGIN
DECLARE productCategoryRowsCount INT;
SELECT count(*)
FROM product_category
WHERE product_id = inProductId
INTO productCategoryRowsCount;
IF productCategoryRowsCount = 1 THEN
CALL catalog_delete_product(inProductId);
SELECT 0;
ELSE
DELETE FROM product_category
WHERE category_id = inCategoryId AND product_id = inProductId;
SELECT 1;
END IF;
END$$
-- Create catalog_get_categories stored procedure
CREATE PROCEDURE catalog_get_categories()
BEGIN
SELECT category_id, name, description
FROM category
ORDER BY category_id;
END$$
-- Create catalog_get_product_info stored procedure
CREATE PROCEDURE catalog_get_product_info(IN inProductId INT)
BEGIN
SELECT product_id, name, description, price, discounted_price,
image, image_2, thumbnail, display
FROM product
WHERE product_id = inProductId;
END$$
-- Create catalog_get_categories_for_product stored procedure
CREATE PROCEDURE catalog_get_categories_for_product(IN inProductId INT)
BEGIN
SELECT c.category_id, c.department_id, c.name
FROM category c
JOIN product_category pc
ON c.category_id = pc.category_id
WHERE pc.product_id = inProductId
ORDER BY category_id;
END$$
-- Create catalog_set_product_display_option stored procedure
CREATE PROCEDURE catalog_set_product_display_option(
IN inProductId INT, IN inDisplay SMALLINT)
BEGIN
UPDATE product SET display = inDisplay WHERE product_id = inProductId;
END$$
-- Create catalog_assign_product_to_category stored procedure
CREATE PROCEDURE catalog_assign_product_to_category(
IN inProductId INT, IN inCategoryId INT)
BEGIN
INSERT INTO product_category (product_id, category_id)
VALUES (inProductId, inCategoryId);
END$$
-- Create catalog_move_product_to_category stored procedure
CREATE PROCEDURE catalog_move_product_to_category(IN inProductId INT,
IN inSourceCategoryId INT, IN inTargetCategoryId INT)
BEGIN
UPDATE product_category
SET category_id = inTargetCategoryId
WHERE product_id = inProductId
AND category_id = inSourceCategoryId;
END$$
-- Create catalog_get_attributes_not_assigned_to_product stored procedure
CREATE PROCEDURE catalog_get_attributes_not_assigned_to_product(
IN inProductId INT)
BEGIN
SELECT a.name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id NOT IN
(SELECT attribute_value_id
FROM product_attribute
WHERE product_id = inProductId)
ORDER BY attribute_name, av.attribute_value_id;
END$$
-- Create catalog_assign_attribute_value_to_product stored procedure
CREATE PROCEDURE catalog_assign_attribute_value_to_product(
IN inProductId INT, IN inAttributeValueId INT)
BEGIN
INSERT INTO product_attribute (product_id, attribute_value_id)
VALUES (inProductId, inAttributeValueId);
END$$
-- Create catalog_remove_product_attribute_value stored procedure
CREATE PROCEDURE catalog_remove_product_attribute_value(
IN inProductId INT, IN inAttributeValueId INT)
BEGIN
DELETE FROM product_attribute
WHERE product_id = inProductId AND
attribute_value_id = inAttributeValueId;
END$$
-- Create catalog_set_image stored procedure
CREATE PROCEDURE catalog_set_image(
IN inProductId INT, IN inImage VARCHAR(150))
BEGIN
UPDATE product SET image = inImage WHERE product_id = inProductId;
END$$
-- Create catalog_set_image_2 stored procedure
CREATE PROCEDURE catalog_set_image_2(
IN inProductId INT, IN inImage VARCHAR(150))
BEGIN
UPDATE product SET image_2 = inImage WHERE product_id = inProductId;
END$$
-- Create catalog_set_thumbnail stored procedure
CREATE PROCEDURE catalog_set_thumbnail(
IN inProductId INT, IN inThumbnail VARCHAR(150))
BEGIN
UPDATE product
SET thumbnail = inThumbnail
WHERE product_id = inProductId;
END$$
-- Create shopping_cart_add_product stored procedure
CREATE PROCEDURE shopping_cart_add_product(IN inCartId CHAR(32),
IN inProductId INT, IN inAttributes VARCHAR(1000))
BEGIN
DECLARE productQuantity INT;
-- Obtain current shopping cart quantity for the product
SELECT quantity
FROM shopping_cart
WHERE cart_id = inCartId
AND product_id = inProductId
AND attributes = inAttributes
INTO productQuantity;
-- Create new shopping cart record, or increase quantity of existing record
IF productQuantity IS NULL THEN