· 8 years ago · Jul 17, 2018, 02:28 AM
1use yell_db;
2
3delimiter ;
4
5drop table if exists state;
6
7create table state
8(
9 state_id tinyint(2) unsigned not null primary key,
10 state_code varchar(2) not null,
11 name varchar(64) null
12) engine=InnoDB;
13
14drop table if exists state_ft;
15
16create table state_ft
17(
18 state_id tinyint(2) unsigned not null primary key,
19 name varchar(64) not null,
20 fulltext (name)
21) engine=MyIsam;
22
23
24drop table if exists city;
25
26create table city
27(
28 city_id mediumint unsigned not null primary key,
29 state_id tinyint(2) unsigned null,
30 name varchar(64) not null
31) engine=InnoDB;
32
33
34drop table if exists city_zip_code_area;
35
36create table city_zip_code_area
37(
38 city_id mediumint unsigned not null,
39 zip_code_area char(5) not null,
40 latitude decimal(8,4) default 0.0,
41 longitude decimal(8,4) default 0.0,
42 primary key (city_id, zip_code_area)
43) engine=InnoDB;
44
45
46drop table if exists city_zip_code_neighbour;
47
48create table city_zip_code_neighbour
49(
50 from_city_id mediumint unsigned not null,
51 from_zip_code_area char(5) not null,
52 to_city_id mediumint unsigned not null,
53 to_zip_code_area char(5) not null,
54 distance decimal(8,4) default 0.0,
55 primary key (from_city_id, from_zip_code_area, to_city_id, to_zip_code_area)
56) engine=InnoDB;
57
58
59drop table if exists city_neighbour;
60
61create table city_neighbour
62(
63 from_city_id mediumint unsigned not null,
64 to_city_id mediumint unsigned not null,
65 distance decimal(8,2) not null default 0.0,
66 primary key (from_city_id, to_city_id)
67) engine=InnoDB;
68
69
70drop table if exists city_ft;
71
72create table city_ft
73(
74 city_id mediumint unsigned not null primary key,
75 name varchar(64) not null,
76 fulltext (name)
77) engine=MyIsam;
78
79
80drop table if exists category;
81
82create table category
83(
84 cat_id mediumint unsigned not null primary key,
85 cat_code mediumint unsigned not null default 0,
86 name varchar(64) not null,
87 next_sub_cat_id smallint unsigned not null default 0
88) engine=InnoDB;
89
90drop table if exists category_ft;
91
92create table category_ft
93(
94 cat_id mediumint unsigned not null primary key,
95 name varchar(64) not null,
96 fulltext (name)
97) engine=MyIsam;
98
99
100drop table if exists sub_category;
101
102create table sub_category
103(
104 cat_id mediumint unsigned not null,
105 sub_cat_id smallint unsigned not null default 0,
106 name varchar(64) not null,
107 primary key (cat_id, sub_cat_id)
108) engine=InnoDB;
109
110
111drop table if exists sub_category_ft;
112
113create table sub_category_ft
114(
115 cat_id mediumint unsigned not null,
116 sub_cat_id smallint unsigned not null default 0,
117 name varchar(64) not null,
118 primary key (cat_id, sub_cat_id),
119 fulltext (name)
120) engine=MyIsam;
121
122
123drop table if exists company;
124
125create table company
126(
127 company_id int unsigned not null primary key,
128 name varchar(64) not null,
129 url varchar(128) null,
130 next_branch_id smallint unsigned not null default 0
131) engine=InnoDB;
132
133
134drop table if exists state_city_company;
135
136create table state_city_company
137(
138 state_id tinyint(2) not null,
139 city_id mediumint not null,
140 company_id int unsigned not null,
141 primary key (state_id, city_id, company_id)
142)
143engine=InnoDB;
144
145
146drop table if exists company_state_city;
147
148create table company_state_city
149(
150 company_id int unsigned not null,
151 state_id tinyint(2) not null,
152 city_id mediumint not null,
153 primary key (company_id,state_id, city_id)
154)
155engine=InnoDB;
156
157
158drop table if exists company_ft;
159
160create table company_ft
161(
162 company_id int unsigned not null,
163 name varchar(64) not null,
164 fulltext (name),
165 primary key (company_id)
166)
167engine=MyIsam;
168
169
170drop table if exists company_sub_category;
171
172create table company_sub_category
173(
174 cat_id mediumint unsigned not null,
175 sub_cat_id smallint unsigned not null default 0,
176 company_id int unsigned not null,
177 primary key (cat_id, sub_cat_id, company_id)
178)
179engine=InnoDB;
180
181
182drop table if exists company_branch;
183
184create table company_branch
185(
186 company_id int unsigned not null,
187 branch_id smallint unsigned not null default 0,
188 state_id tinyint(2) unsigned not null,
189 city_id mediumint unsigned not null,
190 cat_id mediumint unsigned not null,
191 sub_cat_id smallint unsigned not null,
192 address varchar(256) null,
193 zip_code varchar(16) null,
194 zip_code_area char(5) null,
195 zip_code_plus4 char(4) null,
196 phone1 varchar(16) null,
197 phone2 varchar(16) null,
198 url varchar(128) null,
199 primary key (company_id, branch_id)
200)
201engine=InnoDB;
202
203
204drop table if exists company_branch_state_city;
205
206create table company_branch_state_city
207(
208 state_id tinyint(2) not null,
209 city_id mediumint not null,
210 company_id int unsigned not null,
211 branch_id smallint unsigned not null,
212 primary key (state_id, city_id, company_id, branch_id)
213)
214engine=InnoDB;
215
216
217drop table if exists company_branch_state_city_sub_category;
218
219create table company_branch_state_city_sub_category
220(
221 state_id tinyint(2) not null,
222 city_id mediumint not null,
223 cat_id mediumint unsigned not null,
224 sub_cat_id smallint unsigned not null,
225 company_id int unsigned not null,
226 branch_id smallint unsigned not null,
227 primary key (state_id, city_id, cat_id, sub_cat_id, company_id, branch_id)
228)
229engine=InnoDB;
230
231
232
233
234/*
235*****************************************
236* S T O R E D P R O C S *
237/****************************************
238*/
239
240
241
242
243
244
245
246
247delimiter ;
248
249drop procedure if exists search;
250
251delimiter #
252
253create procedure search
254(
255in p_categories varchar(256),
256in p_city_name varchar(64),
257in p_branch_limit int,
258in p_min_rank int,
259in p_trace tinyint
260)
261proc_main:begin
262
263declare p_state_id tinyint(2) default 0;
264declare p_city_id mediumint default 0;
265
266if(p_categories is null) then
267 select -1 as err_code, 'please specify search terms' as err_msg;
268 leave proc_main;
269end if;
270
271select a.state_id, a.city_id
272from(
273 select city_id, match(name) against (p_city_name) as rank from city_ft c order by rank desc limit 1
274) ft_search
275inner join city a on ft_search.city_id = a.city_id
276into p_state_id, p_city_id;
277
278set session transaction isolation level read uncommitted;
279
280
281create temporary table category_search
282(
283 cat_id mediumint unsigned,
284 sub_cat_id smallint unsigned,
285 name varchar(64),
286 rank float default 0.0,
287 index using btree(cat_id, sub_cat_id)
288)engine = memory;
289
290insert into category_search
291select distinct
292 cat_id, sub_cat_id, name, match(name) against (p_categories) as rank
293from
294 sub_category_ft
295where
296 match(name) against (p_categories) > p_min_rank limit 250;
297
298set SQL_SELECT_LIMIT = p_branch_limit;
299
300if (p_trace = 1) then
301
302 explain select
303 search.rank,
304 search.name,
305 cb.*
306 from
307 company_branch_state_city_sub_category cbscsc
308 inner join
309 category_search search on
310 cbscsc.state_id = p_state_id and cbscsc.city_id = p_city_id and
311 cbscsc.cat_id = search.cat_id and cbscsc.sub_cat_id = search.sub_cat_id
312 inner join company_branch cb on
313 cbscsc.company_id = cb.company_id and cbscsc.branch_id = cb.branch_id
314 order by
315 search.rank desc;
316
317else
318
319 select
320 search.rank,
321 search.name,
322 cb.*
323 from
324 company_branch_state_city_sub_category cbscsc
325 inner join
326 category_search search on
327 cbscsc.state_id = p_state_id and cbscsc.city_id = p_city_id and
328 cbscsc.cat_id = search.cat_id and cbscsc.sub_cat_id = search.sub_cat_id
329 inner join company_branch cb on
330 cbscsc.company_id = cb.company_id and cbscsc.branch_id = cb.branch_id
331 order by
332 search.rank desc;
333
334end if;
335
336drop table category_search;
337
338set session transaction isolation level repeatable read;
339
340set SQL_SELECT_LIMIT = DEFAULT;
341
342end proc_main #
343
344
345
346
347
348delimiter ;
349
350drop procedure if exists search2;
351
352delimiter #
353
354create procedure search2
355(
356in p_company varchar(64),
357in p_city varchar(64),
358in p_state_code varchar(2),
359in p_branch_limit int,
360in p_min_rank int,
361in p_include_neighbours tinyint,
362in p_neighbour_distance int,
363in p_trace tinyint
364)
365proc_main:begin
366
367-- call search2('starbucks','glendale','CA',20,5,0,10,0);
368-- call search2('starbucks','glendale','CA',20,5,1,10,0);
369
370declare p_city_id mediumint default null;
371declare p_state_id tinyint default null;
372declare p_state_name varchar(64) default null;
373declare p_city_name varchar(64) default null;
374declare p_rank float default null;
375
376
377set p_company = nullif(trim(p_company),'');
378set p_city = nullif(trim(p_city),'');
379set p_state_code = nullif(trim(p_state_code),'');
380
381
382if(p_company is null or p_city is null or p_state_code is null or length(p_state_code) < 2) then
383 select -1 as err_code, 'ERROR : please specify a company, city and state code' as err_msg;
384 leave proc_main;
385end if;
386
387-- select the correct city for the supplied state
388
389select
390 c.state_id, s.name, c.city_id, c.name, 0 into
391 p_state_id, p_state_name, p_city_id, p_city_name, p_rank
392from
393 city c
394inner join state s on c.state_id = s.state_id and s.state_code = p_state_code
395where c.name = p_city limit 1;
396
397if(p_city_id is null) then
398 select -1 as err_code, 'ERROR : unable to determine city and state from paramters supplied' as err_msg;
399 leave proc_main;
400end if;
401
402-- temporary table to store city we're looking for and it's neighbours
403
404create temporary table city_search
405(
406 state_id tinyint unsigned,
407 state_name varchar(64),
408 city_id mediumint unsigned,
409 city_name varchar(64),
410 rank float default 0.0,
411 distance decimal(8,2) default 0.0,
412 primary key(state_id, city_id)
413)engine = memory;
414
415-- temporary table to store company we're looking for
416
417create temporary table company_search
418(
419 state_id tinyint unsigned,
420 state_name varchar(64),
421 city_id mediumint unsigned,
422 city_name varchar(64),
423 company_id int unsigned,
424 company_name varchar(64),
425 rank float default 0.0,
426 distance decimal(8,2) default 0.0,
427 primary key(state_id, city_id, company_id)
428)engine = memory;
429
430
431-- store the city we have located and any it's neighbours
432
433insert into city_search
434 select p_state_id, p_state_name, p_city_id, p_city_name, p_rank, 0;
435
436if(p_include_neighbours = 1) then
437
438 insert into city_search
439 select distinct
440 c.state_id, s.name, cn.to_city_id, tc.name, 0 as rank, cn.distance
441 from
442 city c
443 inner join city_neighbour cn on c.city_id = cn.from_city_id
444 inner join city tc on cn.to_city_id = tc.city_id
445 inner join state s on tc.state_id = s.state_id
446 where
447 c.city_id = p_city_id and cn.distance <= p_neighbour_distance
448 limit 100;
449
450end if;
451
452-- store the companies that have a name like p_company and that are in the state and cities located
453
454insert into company_search
455select distinct
456 city_search.state_id,
457 city_search.state_name,
458 city_search.city_id,
459 city_search.city_name,
460 ft.company_id,
461 ft.name,
462 match(ft.name) against (p_company) as rank,
463 city_search.distance
464from
465 company_ft ft
466inner join company_state_city csc on ft.company_id = csc.company_id
467inner join city_search on csc.state_id = city_search.state_id and csc.city_id = city_search.city_id
468where
469 match(ft.name) against (p_company) > p_min_rank
470order by
471 rank desc limit 250;
472
473
474if(p_trace = 1) then
475 select * from city_search;
476 select * from company_search;
477end if;
478
479-- select the company branches
480
481set SQL_SELECT_LIMIT = p_branch_limit;
482
483
484select
485 cs.rank, cs.distance, cs.state_id, cs.state_name,
486 cs.city_id, cs.city_name, cs.company_id, cs.company_name,
487 cb.branch_id, cb.address, cb.zip_code, cb.phone1, cb.phone2,
488 cb.url
489from
490 company_branch_state_city cbsc
491inner join
492 company_search cs on cs.state_id = cbsc.state_id and cs.city_id = cbsc.city_id and cbsc.company_id = cs.company_id
493inner join
494 company_branch cb on cbsc.company_id = cb.company_id and cbsc.branch_id = cb.branch_id
495order by
496 cs.rank desc;
497
498set SQL_SELECT_LIMIT = DEFAULT;
499
500if(p_trace = 1) then
501
502 explain select 1
503 from
504 company_branch_state_city cbsc
505 inner join
506 company_search cs on cs.state_id = cbsc.state_id and cs.city_id = cbsc.city_id and cbsc.company_id = cs.company_id
507 inner join
508 company_branch cb on cbsc.company_id = cb.company_id and cbsc.branch_id = cb.branch_id
509 order by
510 cs.rank desc;
511
512end if;
513
514-- clean up
515
516drop table company_search;
517drop table city_search;
518
519end proc_main #
520
521delimiter ;
522
523
524
525
526
527
528
529
530delimiter ;
531
532drop procedure if exists search2_boost;
533
534delimiter #
535
536create procedure search2_boost
537(
538in p_company varchar(64),
539in p_city varchar(64),
540in p_state_code varchar(2),
541in p_branch_limit int,
542in p_min_rank int,
543in p_include_neighbours tinyint,
544in p_neighbour_distance int,
545in p_trace tinyint
546)
547proc_main:begin
548
549-- call search2_boost('starbucks','glendale','CA',20,5,0,10,0);
550-- call search2_boost('starbucks','glendale','CA',20,5,1,10,0);
551
552declare p_city_id mediumint default null;
553declare p_state_id tinyint default null;
554
555set p_company = nullif(trim(p_company),'');
556set p_city = nullif(trim(p_city),'');
557set p_state_code = nullif(trim(p_state_code),'');
558
559-- check params
560
561if(p_company is null or p_city is null or p_state_code is null or length(p_state_code) < 2) then
562 select -1 as err_code, 'ERROR : please specify a company, city and state code' as err_msg;
563 leave proc_main;
564end if;
565
566-- select the unique city/state for the city name and state code provided
567
568select
569 c.state_id, c.city_id into p_state_id, p_city_id
570from
571 city c
572inner join state s on c.state_id = s.state_id and s.state_code = p_state_code
573where c.name = p_city limit 1;
574
575-- did we find a city with the name and state code ?
576
577if(p_city_id is null) then
578 select -1 as err_code, 'ERROR : unable to determine city and state from paramters supplied' as err_msg;
579 leave proc_main;
580end if;
581
582-- temporary table to store the city we're looking for and it's neighbours
583
584create temporary table city_search
585(
586 state_id tinyint unsigned,
587 city_id mediumint unsigned,
588 distance decimal(8,2) default 0.0,
589 primary key(state_id, city_id)
590)engine = memory;
591
592create temporary table company_search
593(
594 state_id tinyint unsigned,
595 city_id mediumint unsigned,
596 company_id int unsigned,
597 rank float default 0.0,
598 primary key(state_id, city_id, company_id)
599)engine = memory;
600
601create temporary table company_search_branch
602(
603 state_id tinyint unsigned,
604 city_id mediumint unsigned,
605 company_id int unsigned,
606 branch_id smallint unsigned,
607 rank float default 0.0,
608 primary key(state_id, city_id, company_id, branch_id)
609)engine = memory;
610
611-- store the city we have located and any it's neighbours
612
613insert into city_search select p_state_id, p_city_id, 0;
614
615if(p_include_neighbours = 1) then
616
617 insert into city_search
618 select distinct
619 tc.state_id, cn.to_city_id, cn.distance
620 from
621 city c
622 inner join city_neighbour cn on c.city_id = cn.from_city_id
623 inner join city tc on cn.to_city_id = tc.city_id
624 where
625 c.city_id = p_city_id and cn.distance <= p_neighbour_distance
626 order by distance limit 100;
627
628end if;
629
630-- this might head fuck you we're gonna assume every company we found exists in every state/city we found
631-- worst case 100 cities * 250 companies = 25,000 rows this is 1/10th of the number of possible rows in company_state_city
632
633insert into company_search
634select distinct
635 cs.state_id,
636 cs.city_id,
637 comps.company_id,
638 comps.rank
639from
640 city_search cs
641cross join
642(
643select distinct
644 ft.company_id,
645 ft.name,
646 match(ft.name) against (p_company) as rank
647from
648 company_ft ft
649where
650 match(ft.name) against (p_company) > p_min_rank
651order by
652 rank desc limit 250
653) comps
654order by cs.state_id, cs.city_id;
655
656-- get the companies that actually exist in the states and cities
657
658insert into company_search_branch
659select
660 cbsc.state_id,
661 cbsc.city_id,
662 cbsc.company_id,
663 cbsc.branch_id,
664 cs.rank
665from
666 company_search cs
667inner join
668 company_branch_state_city cbsc on cs.state_id = cbsc.state_id and cs.city_id = cbsc.city_id and cs.company_id = cbsc.company_id;
669
670if(p_trace = 1) then
671 select * from city_search;
672 select * from company_search;
673 select * from company_search_branch;
674end if;
675
676-- select the company branches
677
678set SQL_SELECT_LIMIT = p_branch_limit;
679
680select
681 csb.rank,
682 s.name as state_name,
683 s.state_code,
684 ci.name as city_name,
685 c.name as company_name,
686 cb.*
687from
688 company_search_branch csb
689inner join
690 company_branch cb on csb.company_id = cb.company_id and csb.branch_id = cb.branch_id
691inner join
692 company c on cb.company_id = c.company_id
693inner join
694 state s on cb.state_id = s.state_id
695inner join
696 city ci on cb.city_id = ci.city_id
697order by
698 csb.rank desc;
699
700set SQL_SELECT_LIMIT = DEFAULT;
701
702-- clean up
703
704drop temporary table if exists city_search;
705drop temporary table if exists company_search;
706drop temporary table if exists company_search_branch;
707
708end proc_main #
709
710delimiter ;
711
712
713
714
715
716-- call get_company_branch(81120, 3);
717
718
719delimiter ;
720
721drop procedure if exists get_company_branch;
722
723delimiter #
724
725create procedure get_company_branch
726(
727in p_company_id int unsigned,
728in p_branch_id smallint unsigned
729)
730proc_main:begin
731
732select
733 s.name as state_name,
734 ci.name as city_name,
735 c.name as comapany_name,
736 cb.*
737from
738 company_branch cb
739inner join company c on
740 cb.company_id = c.company_id
741inner join state s on
742 cb.state_id = s.state_id
743inner join city ci on
744 cb.city_id = ci.city_id
745where
746 cb.company_id = p_company_id and cb.branch_id = p_branch_id;
747
748
749end proc_main #
750
751delimiter ;
752
753
754
755
756-- call list_similar_companies(766, null, 3, 6870, 199, null);
757
758
759delimiter ;
760
761drop procedure if exists list_similar_companies;
762
763delimiter #
764
765create procedure list_similar_companies
766(
767in p_company_id int unsigned,
768in p_branch_id smallint unsigned,
769in p_state_id tinyint unsigned,
770in p_city_id mediumint unsigned,
771in p_cat_id mediumint unsigned,
772in p_sub_cat_id smallint unsigned
773)
774proc_main:begin
775
776select
777 s.name as state_name,
778 ci.name as city_name,
779 c.name as comapany_name,
780 cb.*
781from
782 company_branch_state_city_sub_category cbscsc
783inner join company_branch cb on
784 cbscsc.company_id = cb.company_id and cbscsc.branch_id = cb.branch_id
785inner join company c on
786 cb.company_id = c.company_id
787inner join state s on
788 cb.state_id = s.state_id
789inner join city ci on
790 cb.city_id = ci.city_id
791where
792 cbscsc.state_id = p_state_id and cbscsc.city_id = p_city_id and cbscsc.cat_id = p_cat_id and
793 c.company_id <> p_company_id;
794
795
796end proc_main #
797
798delimiter ;
799
800
801
802
803
804
805-- call search_boost('cleaners','Glendale','CA',20,5,0,20,0);
806
807delimiter ;
808
809drop procedure if exists search_boost;
810
811delimiter #
812
813create procedure search_boost
814(
815in p_categories varchar(256),
816in p_city varchar(64),
817in p_state_code varchar(2),
818in p_branch_limit int,
819in p_min_rank int,
820in p_include_neighbours tinyint,
821in p_neighbour_distance int,
822in p_trace tinyint
823)
824proc_main:begin
825
826-- call search_boost('cleaners','Glendale','CA',20,5,0,20,0);
827-- call search_boost('cleaners','Glendale','CA',20,5,1,20,0);
828
829declare p_state_id tinyint(2) default null;
830declare p_city_id mediumint default null;
831
832if(p_categories is null) then
833 select -1 as err_code, 'please specify search terms' as err_msg;
834 leave proc_main;
835end if;
836
837-- select the unique city/state for the city name and state code provided
838
839select
840 c.state_id, c.city_id into p_state_id, p_city_id
841from
842 city c
843inner join state s on c.state_id = s.state_id and s.state_code = p_state_code
844where c.name = p_city limit 1;
845
846-- did we find a city with the name and state code ?
847
848if(p_city_id is null) then
849 select -1 as err_code, 'ERROR : unable to determine city and state from paramters supplied' as err_msg;
850 leave proc_main;
851end if;
852
853-- temporary table to store the city we're looking for and it's neighbours
854
855create temporary table city_search
856(
857 state_id tinyint unsigned,
858 city_id mediumint unsigned,
859 distance decimal(8,2) default 0.0,
860 primary key(state_id, city_id)
861)engine = memory;
862
863insert into city_search values(p_state_id, p_city_id, 0);
864
865if(p_include_neighbours = 1) then
866
867-- fill city_search with neighbours of p_city_id !!
868
869 insert into city_search
870 select distinct
871 tc.state_id, cn.to_city_id, cn.distance
872 from
873 city c
874 inner join city_neighbour cn on c.city_id = cn.from_city_id
875 inner join city tc on cn.to_city_id = tc.city_id
876 where
877 c.city_id = p_city_id and cn.distance <= p_neighbour_distance
878 order by tc.state_id, tc.city_id limit 100;
879
880end if;
881
882create temporary table category_search
883(
884 state_id tinyint unsigned,
885 city_id mediumint unsigned,
886 cat_id mediumint unsigned,
887 sub_cat_id smallint unsigned,
888 name varchar(64),
889 rank float default 0.0,
890 index using btree(cat_id, sub_cat_id)
891)engine = memory;
892
893insert into category_search
894select
895 cs.state_id,
896 cs.city_id,
897 cat.cat_id,
898 cat.sub_cat_id,
899 cat.name,
900 cat.rank
901from
902 city_search cs
903cross join
904(
905select distinct
906 cat_id, sub_cat_id, name, match(name) against (p_categories) as rank
907from
908 sub_category_ft
909where
910 match(name) against (p_categories) > p_min_rank limit 250
911) cat
912order by cs.state_id, cs.city_id, cat.cat_id, cat.sub_cat_id;
913
914if(p_trace = 1) then
915 select * from city_search;
916 select * from category_search;
917end if;
918
919set SQL_SELECT_LIMIT = p_branch_limit;
920
921select
922 cs.rank,
923 s.name as state_name,
924 s.state_code,
925 ci.name as city_name,
926 c.name as company_name,
927 cb.*
928from
929 category_search cs
930inner join
931 company_branch_state_city_sub_category cbscsc on
932 cs.state_id = cbscsc.state_id and cs.city_id = cbscsc.city_id and
933 cs.cat_id = cbscsc.cat_id and cs.sub_cat_id = cbscsc.sub_cat_id
934inner join company_branch cb on
935 cbscsc.company_id = cb.company_id and cbscsc.branch_id = cb.branch_id
936inner join
937 company c on cb.company_id = c.company_id
938inner join
939 state s on cb.state_id = s.state_id
940inner join
941 city ci on cb.city_id = ci.city_id
942order by
943 cs.rank desc;
944
945set SQL_SELECT_LIMIT = DEFAULT;
946
947if(p_trace = 1) then
948
949explain select
950 cs.rank,
951 s.name as state_name,
952 s.state_code,
953 ci.name as city_name,
954 c.name as company_name,
955 cb.*
956from
957 category_search cs
958inner join
959 company_branch_state_city_sub_category cbscsc on
960 cs.state_id = cbscsc.state_id and cs.city_id = cbscsc.city_id and
961 cs.cat_id = cbscsc.cat_id and cs.sub_cat_id = cbscsc.sub_cat_id
962inner join company_branch cb on
963 cbscsc.company_id = cb.company_id and cbscsc.branch_id = cb.branch_id
964inner join
965 company c on cb.company_id = c.company_id
966inner join
967 state s on cb.state_id = s.state_id
968inner join
969 city ci on cb.city_id = ci.city_id
970order by
971 cs.rank desc;
972
973end if;
974
975drop temporary table if exists category_search;
976drop temporary table if exists city_search;
977
978end proc_main #
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996delimiter ;
997
998drop procedure if exists search_all;
999
1000delimiter #
1001
1002create procedure search_all
1003(
1004in p_company_or_category varchar(64),
1005in p_city_or_zipcode varchar(64),
1006in p_state_code varchar(2),
1007in p_branch_limit int,
1008in p_min_rank int,
1009in p_include_neighbours tinyint,
1010in p_neighbour_distance int,
1011in p_trace tinyint
1012)
1013proc_main:begin
1014
1015/*
1016
1017usage:
1018
1019company name, city name & state
1020call search_all('starbucks','glendale','CA',100,5,0,20,0);
1021call search_all('starbucks','glendale','CA',100,5,1,20,0);
1022
1023company name & zipcode
1024call search_all('starbucks','91202',null,100,5,0,20,0);
1025call search_all('starbucks','91202',null,100,5,1,20,0);
1026
1027category, city name & state
1028call search_all('cleaners','Glendale','CA',100,5,0,20,0);
1029call search_all('cleaners','Glendale','CA',100,5,1,20,0);
1030
1031category & zipcode
1032call search_all('cleaners','91202',null,100,5,0,20,0);
1033call search_all('cleaners','91202',null,100,5,1,20,0);
1034
1035*/
1036
1037declare p_city_id mediumint default null;
1038declare p_state_id tinyint default null;
1039
1040set p_company_or_category = nullif(trim(p_company_or_category),'');
1041set p_city_or_zipcode = nullif(trim(p_city_or_zipcode),'');
1042set p_state_code = nullif(trim(p_state_code),'');
1043
1044-- check params
1045
1046if(p_company_or_category is null or p_city_or_zipcode is null) then
1047 select -1 as err_code, 'ERROR : please specify a company, category, city or zipcode' as err_msg;
1048 leave proc_main;
1049end if;
1050
1051-- select the unique city/state for the city name and state code provided
1052
1053if p_state_code is null then
1054
1055 -- we were passed a zipcode
1056
1057 select
1058 c.state_id, c.city_id into p_state_id, p_city_id
1059 from
1060 city_zip_code_area z
1061 inner join city c on z.city_id = c.city_id
1062 where z.zip_code_area = p_city_or_zipcode;
1063
1064else
1065
1066 -- we were passed a city name & state code
1067
1068 select
1069 c.state_id, c.city_id into p_state_id, p_city_id
1070 from
1071 city c
1072 inner join state s on c.state_id = s.state_id and s.state_code = p_state_code
1073 where c.name = p_city_or_zipcode;
1074
1075end if;
1076
1077-- did we find a city with the name/state or zipcode ?
1078
1079if(p_city_id is null or p_state_id is null) then
1080 select -1 as err_code, 'ERROR : unable to determine city from paramters supplied' as err_msg;
1081 leave proc_main;
1082end if;
1083
1084-- temporary table to store the city we're looking for and it's neighbours (optional)
1085
1086create temporary table city_search
1087(
1088 state_id tinyint unsigned,
1089 city_id mediumint unsigned,
1090 distance decimal(8,2) default 0.0,
1091 primary key(state_id, city_id)
1092)engine = memory;
1093
1094/*
1095store the city we have located and optionally include any neighbours
1096within a p_neighbour_distance mile radius
1097*/
1098
1099insert into city_search select p_state_id, p_city_id, 0;
1100
1101if(p_include_neighbours = 1) then
1102
1103 insert into city_search
1104 select distinct
1105 tc.state_id, cn.to_city_id, cn.distance
1106 from
1107 city c
1108 inner join city_neighbour cn on c.city_id = cn.from_city_id
1109 inner join city tc on cn.to_city_id = tc.city_id
1110 where
1111 c.city_id = p_city_id and cn.distance <= p_neighbour_distance
1112 order by c.city_id limit 100;
1113
1114end if;
1115
1116create temporary table company_search
1117(
1118 state_id tinyint unsigned,
1119 city_id mediumint unsigned,
1120 company_id int unsigned,
1121 rank float default 0.0,
1122 orig_city tinyint unsigned,
1123 primary key(state_id, city_id, company_id)
1124)engine = memory;
1125
1126create temporary table category_search
1127(
1128 state_id tinyint unsigned,
1129 city_id mediumint unsigned,
1130 cat_id mediumint unsigned,
1131 sub_cat_id smallint unsigned,
1132 rank float default 0.0,
1133 orig_city tinyint unsigned,
1134 primary key(state_id, city_id, cat_id, sub_cat_id)
1135)engine = memory;
1136
1137create temporary table company_branch_search
1138(
1139 company_id int unsigned,
1140 branch_id smallint unsigned,
1141 state_id tinyint unsigned,
1142 city_id mediumint unsigned,
1143 rank float default 0.0,
1144 orig_city tinyint unsigned,
1145 search_type tinyint unsigned,
1146 primary key(company_id, branch_id, search_type)
1147)engine = memory;
1148
1149
1150insert into company_search
1151select distinct
1152 cs.state_id,
1153 cs.city_id,
1154 comps.company_id,
1155 comps.rank,
1156 case when cs.city_id = p_city_id then 0 else 1 end
1157from
1158 city_search cs
1159cross join
1160(
1161select distinct
1162 ft.company_id,
1163 ft.name,
1164 match(ft.name) against (p_company_or_category) as rank
1165from
1166 company_ft ft
1167where
1168 match(ft.name) against (p_company_or_category) > p_min_rank
1169order by rank desc limit 64
1170) comps;
1171
1172insert into category_search
1173select
1174 cs.state_id,
1175 cs.city_id,
1176 cat.cat_id,
1177 cat.sub_cat_id,
1178 cat.rank,
1179 case when cs.city_id = p_city_id then 0 else 1 end
1180 from
1181 city_search cs
1182cross join
1183(
1184select distinct
1185 cat_id, sub_cat_id, match(name) against (p_company_or_category) as rank
1186from
1187 sub_category_ft
1188where
1189 match(name) against (p_company_or_category) > p_min_rank
1190order by rank desc limit 64
1191) cat;
1192
1193set SQL_SELECT_LIMIT = p_branch_limit;
1194
1195insert into company_branch_search
1196select
1197 cbsc.company_id,
1198 cbsc.branch_id,
1199 cbsc.state_id,
1200 cbsc.city_id,
1201 cs.rank,
1202 cs.orig_city,
1203 0 as serach_type
1204from
1205 company_search cs
1206inner join company_branch_state_city cbsc on
1207 cs.state_id = cbsc.state_id and cs.city_id = cbsc.city_id and cs.company_id = cbsc.company_id;
1208
1209insert into company_branch_search
1210select
1211 cbscsc.company_id,
1212 cbscsc.branch_id,
1213 cbscsc.state_id,
1214 cbscsc.city_id,
1215 cs.rank,
1216 cs.orig_city,
1217 1 as search_type
1218from
1219 category_search cs
1220inner join
1221 company_branch_state_city_sub_category cbscsc on
1222 cs.state_id = cbscsc.state_id and cs.city_id = cbscsc.city_id and
1223 cs.cat_id = cbscsc.cat_id and cs.sub_cat_id = cbscsc.sub_cat_id
1224order by
1225 cs.orig_city, cs.rank desc;
1226
1227select
1228 bs.search_type,
1229 bs.orig_city,
1230 bs.rank,
1231 s.name as state_name,
1232 s.state_code,
1233 ci.name as city_name,
1234 c.name as company_name,
1235 cb.*
1236from
1237 company_branch_search bs
1238inner join
1239 company_branch cb on bs.company_id = cb.company_id and bs.branch_id = cb.branch_id
1240inner join
1241 company c on cb.company_id = c.company_id
1242inner join
1243 state s on cb.state_id = s.state_id
1244inner join
1245 city ci on cb.city_id = ci.city_id
1246order by
1247 bs.orig_city, bs.rank desc;
1248
1249set SQL_SELECT_LIMIT = DEFAULT;
1250
1251if(p_trace = 1) then
1252 select * from city_search;
1253 select * from company_search;
1254 select * from category_search;
1255 select * from company_branch_search;
1256end if;
1257
1258drop temporary table if exists city_search;
1259drop temporary table if exists company_search;
1260drop temporary table if exists category_search;
1261drop temporary table if exists company_branch_search;
1262
1263end proc_main #
1264
1265delimiter ;