Lhd3Qukp

1-- создаем процедуры
2DELIMITER $$
3
4select "creating proc 1" $$
5
6drop procedure if exists `PROCESS_CHANNELS` $$
7
8create procedure `PROCESS_CHANNELS` ()
9begin
10  declare SS_ID int;
11  declare SS_EXTERNALID varchar(50);
12  -- cursor exhaustion
13  declare V_NOTFOUND BOOL default FALSE;
14
15  -- cursor
16  declare CHANNEL_CURSOR cursor for
17    select max(ID), EXTERNALID from SERVICE_SPEC where DISCRIMINATOR='BROADCASTCHANNEL' group by EXTERNALID;
18
19  -- cursor handler
20  declare continue handler for not found set V_NOTFOUND := TRUE;
21
22  -- exit handler
23  declare exit handler for sqlexception close CHANNEL_CURSOR;
24
25  open CHANNEL_CURSOR;
26  repeat
27    fetch CHANNEL_CURSOR into
28      SS_ID,
29      SS_EXTERNALID;
30
31    -- if not found leave loop  
32    if not V_NOTFOUND then
33      if coalesce(SS_EXTERNALID, "") != "" then
34        call PROCESS_CHANNEL(SS_ID, SS_EXTERNALID);
35      else
36        select concat("not processing channel id = ", SS_ID) from DUAL;
37      end if;
38      set V_NOTFOUND := FALSE;
39    end if;
40  until V_NOTFOUND end repeat;
41  
42  close CHANNEL_CURSOR;
43end $$
44
45
46select "creating proc 2" $$
47
48drop procedure if exists `PROCESS_CHANNEL` $$
49
50create procedure `PROCESS_CHANNEL` (in SS_ID int,
51                                    in SS_EXTERNALID varchar(50))
52begin
53  select concat(concat(concat("processing channel id = ", SS_ID), ", extID = "), SS_EXTERNALID) as "CHANNEL_DATA" from DUAL;
54
55  
56  set @cur_import=(select max(IMPORT_DATE) from EPG_IMPORT);
57  -- select @cur_import from DUAL;
58
59  start transaction;
60
61
62  drop table if exists `NEW_EXTERNALIDS`;
63  create temporary table `NEW_EXTERNALIDS` (EXTERNALID varchar(50) not null, primary key(`EXTERNALID`));
64
65
66  select count(ID) as "total lines in file from this channel" from EPG_IMPORT where CHANNEL_EXTERNALID = SS_EXTERNALID;
67
68  insert into `NEW_EXTERNALIDS` (EXTERNALID) 
69       select ei.EXTERNALID 
70       from EPG_IMPORT ei 
71       where ei.IMPORT_DATE=@cur_import and ei.CHANNEL_EXTERNALID = SS_EXTERNALID and not exists (select s.ID from SCHEDULE s where s.EXTERNALID=ei.EXTERNALID and s.SERVICE_SPEC_ID=SS_ID);
72
73  select count(EXTERNALID) as "lines to insert" from NEW_EXTERNALIDS;
74  
75
76  
77  if (select count(ID) from EPG_IMPORT where CHANNEL_EXTERNALID = SS_EXTERNALID) != 0 then
78    -- вставка программы
79    insert into OBJECT (DISCRIMINATOR, externId) select "PROGRAM", nei.EXTERNALID from NEW_EXTERNALIDS nei where not exists (select ID from OBJECT o where o.externId=nei.EXTERNALID and o.DISCRIMINATOR='PROGRAM');
80
81    insert into CONTENT (ID, 
82                        DISCRIMINATOR, 
83                        NAME, 
84                        DESCRIPTION, 
85                        ACCESS_LEVEL_ID,    
86                        GENRE_ID,           
87                        CONTENT_SPEC_ID, 
88                        DESCRIPTION_LONG, 
89                        IS_PUBLISHED, 
90                        EXTERNALID) 
91      select o.ID, 
92             "PROGRAM", 
93             ei.NAME, 
94             ei.DESCRIPTION, 
95             al.ID, 
96             1, 
97             3, 
98             ei.DESCRIPTION, 
99             1, 
100             o.externId 
101      from NEW_EXTERNALIDS nei
102      join OBJECT o on o.externId = nei.EXTERNALID
103      join EPG_IMPORT ei on ei.EXTERNALID = nei.EXTERNALID 
104      join DICTIONARY al on al.SORT_ORDER = cast(ei.ACCESS_LEVEL_EXTERNALID as unsigned integer)
105      where o.DISCRIMINATOR='PROGRAM'
106            and ei.IMPORT_DATE=@cur_import
107            and al.DISCRIMINATOR='ACCESSLEVELDICTIONARY'
108            and not exists (select cc.ID from CONTENT cc where cc.ID=o.ID);
109
110    -- теперь schedule
111    insert into OBJECT (DISCRIMINATOR, externId) select 'EPG', EXTERNALID from NEW_EXTERNALIDS;
112  
113    insert into SCHEDULE (ID, DISCRIMINATOR, SERVICE_SPEC_ID, CONTENT_ID, START_DATE, EXTERNALID)
114           select o.ID, 'EPG', SS_ID, c.ID, ei.START_DATE, ei.EXTERNALID 
115           from NEW_EXTERNALIDS nei
116           join OBJECT o on nei.EXTERNALID = o.externId
117           join EPG_IMPORT ei on ei.EXTERNALID=nei.EXTERNALID
118           join CONTENT c on c.EXTERNALID=ei.EXTERNALID
119           where o.DISCRIMINATOR='EPG' 
120                 and ei.IMPORT_DATE=@cur_import
121                 and c.DISCRIMINATOR='PROGRAM';
122  
123    -- публикуем то, что добавилось
124    insert into CONTENT_STATE (OBJECT_ID, IS_PUBLISHED) 
125           select o.ID, 1 
126           from OBJECT o 
127           join NEW_EXTERNALIDS nei on o.externId=nei.EXTERNALID 
128           join EPG_IMPORT ei on ei.EXTERNALID=nei.EXTERNALID  
129           where o.DISCRIMINATOR='PROGRAM'
130                 and ei.IMPORT_DATE = @cur_import
131                 and ei.START_DATE >= @yesterday_start and ei.START_DATE < @tomorrow_end; 
132
133    -- publish
134    set @tmp=now();
135  
136    set @tomorrow_end=(((@tmp + interval 2 DAY)- interval extract(HOUR from @tmp) HOUR) - interval extract(MINUTE from @tmp) MINUTE) - interval (extract(SECOND from @tmp)+1) SECOND;
137    set @yesterday_start=(((@tmp - interval 1 DAY)- interval extract(HOUR from @tmp) HOUR) - interval extract(MINUTE from @tmp) MINUTE) - interval extract(SECOND from @tmp) SECOND;
138  
139    -- select @tmp as date, @tomorrow_end as te, @yesterday_start as ys from DUAL;
140  
141  
142    -- select 'publishing';
143    -- update CONTENT set IS_PUBLISHED='1', PUBLISH_DATE=now() where EXTERNALID in (select EXTERNALID from NEW_EXTERNALIDS);
144  
145    -- add data for delta generation
146
147    -- публикуем то, что есть и в файле, и у нас, но у нас распубликовано
148    -- временная таблица для публикации
149    create temporary table topublish (id int(11));
150
151    alter table topublish add foreign key content_fk (id) references CONTENT (ID);
152
153    insert into topublish (id) 
154      select c.ID from CONTENT c, 
155                  EPG_IMPORT ei 
156        where c.IS_PUBLISHED=0 
157          and c.EXTERNALID=ei.EXTERNALID 
158          and ei.CHANNEL_EXTERNALID=SS_EXTERNALID 
159          and ei.IMPORT_DATE = @cur_import 
160          and ei.START_DATE >= @yesterday_start 
161          and ei.START_DATE < @tomorrow_end;
162
163    select count(1) as "published but not inserted" from topublish;
164
165    update CONTENT, topublish tp set IS_PUBLISHED=1 where CONTENT.ID=tp.id;
166    delete CONTENT_STATE from CONTENT_STATE, topublish tp where OBJECT_ID=tp.id;
167    insert into CONTENT_STATE (OBJECT_ID, IS_PUBLISHED) select id, 1 from topublish;
168  
169    -- теперь надо удалить (распубликовать) все записи из будущего, которых нет в новых данных
170    drop table if exists `foo`;
171    create temporary table `foo` (ID int(11), primary key(`ID`), foreign key `content_id_fk` (`ID`) references `CONTENT` (`ID`));
172  
173    insert into `foo` (ID) select epg.CONTENT_ID 
174           from SCHEDULE epg 
175           where epg.EXTERNALID is not null
176                 and epg.SERVICE_SPEC_ID = SS_ID    
177                 and not exists (select ei.ID from EPG_IMPORT ei where ei.IMPORT_DATE=@cur_import and ei.EXTERNALID=epg.EXTERNALID and ei.CHANNEL_EXTERNALID=SS_EXTERNALID);
178  
179  
180    -- распубликуем данные
181    update CONTENT c set IS_PUBLISHED=0 where ID in (select ID from foo);
182    
183    -- -- удалим то, что могло быть на тему дельты для этих записей
184    delete from CONTENT_STATE where OBJECT_ID in (select ID from foo);
185    
186    -- -- вставим дельту 0 для них
187    insert into CONTENT_STATE (IS_PUBLISHED, OBJECT_ID) select 0, f.ID from foo f, SCHEDULE s where s.CONTENT_ID=f.ID and s.START_DATE>=@yesterday_start and s.START_DATE < @tomorrow_end;
188    
189    select count(f.ID) as 'deleted rows:' from foo f, SCHEDULE s where s.CONTENT_ID=f.ID and s.START_DATE>=@yesterday_start and s.START_DATE < @tomorrow_end;
190    drop table topublish;
191  end if;
192
193  commit;
194end $$
195
196
197DELIMITER ; 
198
199select "calling proc";
200
201call PROCESS_CHANNELS();