source: trunk/www.guidonia.net/wp/wp-content/plugins/parteibuch-aggregator/bdp-rssaggregator-db.php@ 44

Last change on this file since 44 was 44, checked in by luciano, 15 years ago
File size: 70.2 KB
Line 
1<?php
2
3if( !class_exists('BDPRSS_DB') ) {
4
5 class BDPRSS_DB
6 {
7 var $sitetable;
8 var $cfeedurl, $csitename, $cdescription, $csiteurl, $clastpolltime, $curlindex,
9 $cnextpolltime, $cgmtadjust, $cupdatetime, /*$ctimeindex,*/ $cidentifier,
10 $csitenameoverride, $cpollingfreqmins;
11
12 var $itemtable;
13 var $iidentifier, $ifeedurl, $iitemname, $iitemtext, /*$iitemnum,*/ $iitemurl, $iitemtime,
14 /*$ifeedindex,*/ /*$itimeindex,*/ /*$updateindex*/ $iitemdate;
15
16 var $pbaoutputtable;
17 var $pbaoidentifier, $pbaoname, $pbaopage2hookin,
18 $pbaodefaultlist, $pbaomaxitems, $pbaoformattype,
19 $pbaotemplate_ticker, $pbaoappend_extra_link, $pbaoappend_cache_link, $pbaoadd_social_bookmarks,
20 $pbaosidebarwidget,
21 $pbaomaxlength, $pbaomaxwordlength, $pbaoitem_date_format, $pbaoallowablexhtmltags,
22 $pbaoiscachable, $otemplate_cache, $pbaocacheviewpage,
23 $pba_channel_rssgenerationallowed,
24 $pba_channel_title, $pba_channel_link,
25 $pba_channel_description, $pba_channel_language, $pba_channel_copyright,
26 $otemplate_kalender, $oarchive_date_format,
27 $okalendermonthslist, $okalenderboxtablecaption, $okalender_last, $okalender_next,
28 $okalenderboxdaysofweeklist, $pbao_superparameter;
29
30 var $listtable;
31 var $lidentifier, $lname, $lurls, $llistall;
32
33 var $errortable;
34 var $eidentifier, $efeedurl, $etime, $etext /* , $efeedindex */;
35
36 var $mtablestatus;
37 var $memtable, $mdatetime, $mstatus, $mnotice;
38
39 var $now;
40 var $bdprss_prevent_dupe_updates;
41
42 var $memtablesok, $memtables_were_ok, $serverstatus;
43
44 function BDPRSS_DB()
45 {
46 /* BDPRSS_DB() - initialisition function that sets constant names for later use */
47
48 global $wpdb, $table_prefix;
49
50 $this->now = time();
51 $this->bdprss_prevent_dupe_updates=array();
52
53 // --- site-table
54 $this->sitetable = $table_prefix.'pba_sites';
55 $this->cidentifier = 'identifier'; // primary key
56 $this->cfeedurl = 'feed_url'; // indexed
57 $this->csitename = 'site_name';
58 $this->csitenameoverride = 'site_name_overriden'; // manual override for site feed names
59 $this->cdescription = 'description';
60 $this->csitelicense = 'site_license';
61 $this->csiteurl = 'site_url';
62 $this->clastpolltime = 'last_poll_time'; // time last polled
63 $this->cnextpolltime = 'next_poll_time'; // next scheduked poll
64 $this->cpollingfreqmins = 'polling_freq_in_mins'; // adjustable polling frequency
65 $this->cupdatetime = 'site_update_time'; // time last updated
66 $this->cgmtadjust = 'gmt_adjustment'; // GMT adjustment to pubDate
67
68 // --- item-table
69 $this->itemtable = $table_prefix."pba_items";
70 $this->iidentifier = "identifier"; // primary key
71 $this->ifeedurl = "item_feed_url"; // ( combined unique key
72 $this->iitemurl = "item_url"; // ( combined unique key
73 $this->iitemname = "item_name";
74 $this->iitemsitename = "item_site_name";
75 $this->iitemsiteurl = "item_site_url";
76 $this->iitemlicense = "item_license";
77 $this->iitemtext = "text_body";
78 $this->iitemtime = "item_time"; // item pubDate time
79 $this->iitemdate = "item_date"; // item date
80 $this->iitemupdate = "item_update_time"; // item last updated time -- for debugging
81
82 //pba options table
83 $this->optionstable = $table_prefix."pba_options";
84
85 // --- pbaoutputtable
86 $this->pbaoutputtable = $table_prefix."pba_outputs";
87
88//management
89 $this->pbaoidentifier = "identifier"; // primary key
90 $this->pbaoname = "name"; // a useful handle
91 $this->pbaopage2hookin = "page2hookin"; //wp page to hook output in
92
93//item selection
94 $this->pbaodefaultlist = "default_list"; // default list filter
95 $this->pbaomaxitems = "items_per_site"; //
96 $this->pbaoformattype = "type"; // type of list:
97
98//ticker page formatting
99 $this->pbaotemplate_ticker = "template_ticker";
100
101 $this->pbaoappend_extra_link = "append_extra_link";
102 $this->pbaoappend_cache_link = "append_cache_link";
103 $this->pbaoadd_social_bookmarks = "add_social_bookmarks";
104
105 $this->pbaosidebarwidget = "template_sidebarwidget";
106
107//item formatting
108
109 $this->pbaomaxlength = "max_words_in_synposis";// 0 = no limit
110 $this->pbaomaxwordlength = "max_word_length"; //
111 $this->pbaoitem_date_format = "item_date_format"; //
112 $this->pbaoallowablexhtmltags= "allowable_xhtml_tags"; //
113
114//cache
115 $this->pbaoiscachable = "enable_caching";
116 $this->otemplate_cache = "template_cache";
117 $this->pbaocacheviewpage = "cache_view_page";
118
119//feed
120 $this->pba_channel_rssgenerationallowed = "channel_rssgenerationallowed";
121 $this->pba_channel_title = "channel_title";
122 $this->pba_channel_link = "channel_link";
123 $this->pba_channel_description = "channel_description";
124 $this->pba_channel_language = "channel_language";
125 $this->pba_channel_copyright = "channel_copyright";
126
127//kalender
128 $this->otemplate_kalender = "template_kalender";
129 $this->oarchive_date_format = "archive_date_format";
130 $this->okalendermonthslist = "kalendermonthslist";
131 $this->okalenderboxtablecaption = "kalenderboxtablecaption";
132 $this->okalender_last = "kalender_last";
133 $this->okalender_next = "kalender_next";
134 $this->okalenderboxdaysofweeklist = "kalenderboxdaysofweeklist";
135 $this->pbao_superparameter = "superparameter";
136
137 // --- list-table
138 $this->listtable = $table_prefix."pba_lists";
139
140 $this->lidentifier = "identifier"; // primary key
141 $this->lname = "name"; // a useful handle
142 $this->lurls = "url_list"; // comma separate list
143 $this->llistall = "list_all"; // list all url ids
144
145 // --- error-table
146 $this->errortable = $table_prefix.'pba_errors';
147
148 $this->eidentifier = 'identifier';
149 $this->efeedurl = 'feed_url';
150 $this->etime = 'when_it_happened';
151 $this->etext = 'error_text';
152
153 // --- memory status table
154 $this->mtablestatus = $table_prefix.'pba_m_tablestatus';
155 $this->memtable = 'memtable';
156 $this->mdatetime = 'datetime';
157 $this->mstatus = 'status';
158 $this->mnotice = 'notice';
159
160 // --- site table in memory - probably obsolete - it is a small table and has to be a copy of sitetable on disk
161 $this->msitetable = $table_prefix.'pba_m_sites';
162 $this->midentifier = 'identifier'; // primary key
163 $this->mfeedurl = 'feed_url'; // indexed
164 $this->msitename = 'site_name';
165 $this->msitenameoverride = 'site_name_overriden'; // manual override for site feed names
166 $this->mdescription = 'description';
167 $this->msitelicense = 'site_license';
168 $this->msiteurl = 'site_url';
169 $this->mlastpolltime = 'last_poll_time'; // time last polled
170 $this->mnextpolltime = 'next_poll_time'; // next scheduked poll
171 $this->mpollingfreqmins = 'polling_freq_in_mins'; // adjustable polling frequency
172 $this->mupdatetime = 'site_update_time'; // time last updated
173 $this->mgmtadjust = 'gmt_adjustment'; // GMT adjustment to pubDate
174
175 // --- index item table in memory
176 $this->mitemtable = $table_prefix.'pba_m_items';
177 $this->miid = 'identifier'; // primary key
178 $this->misiteid = 'site_id'; // / index
179 $this->miitemtime = 'item_time'; // | index
180 $this->miitemdate = 'item_date'; // \ index
181
182//initialize values to check serverstatus at class construction
183 $this->serverstatus=array();
184 $this->memtables_were_ok = 1;
185//debug begin
186//$this->create();
187//$this->prefill_table($this->pbaoutputtable);
188//debug end
189 $this->memtablesok=$this->get_memtable_status($this->serverstatus);
190 if($this->memtablesok == 0){
191 $this->memtables_were_ok = 0;
192 $this->create();
193 $this->prefill_memtables();
194 $this->memtablesok=$this->get_memtable_status($this->serverstatus);
195 }
196 $this->highserverload=false;
197 if(isset($this->serverstatus['highloadthreshold']['notice']) && isset($this->serverstatus['pbaload']['notice'])){
198 if($this->serverstatus['highloadthreshold']['notice'] < $this->serverstatus['pbaload']['notice']) $this->highserverload=true;
199 }
200 } // function BDPRSS_DB
201
202 /* --- create --- */
203 function jobaction($injobname="", $action="insert"){
204 //what parameter to take here?
205 //1. if injobname!="" we limit the processed jobs by parameter injobname
206
207 //what to do here with the result?
208 //1. action="insert" - we INSERT IGNORE new waiting jobs without replacing jobs and try to restore backup from optionstable before
209 //2. action="kill" - we REPLACE a job/all jobs to become waiting
210 //3. action="start" - we REPLACE a job to become running
211
212 global $wpdb;
213
214 $injobname=preg_replace('/[^a-zA_Z0-9_-]/','',$injobname);
215
216 //uploading jobs from options table to memtable we will just do on startup
217 if($action=="insert"){
218 $sql="INSERT IGNORE into $this->mtablestatus ( memtable , datetime , status , notice )
219 SELECT name as memtable, last_change as datetime, concat(type, value) as status,
220 notice as notice FROM $this->optionstable WHERE type = 'job'";
221 if($injobname != "") $sql .= " AND name = '".$injobname."'";
222 $result=$wpdb->query($sql);
223 }
224
225 $sql="SELECT name, value FROM $this->optionstable WHERE type = 'jobdefinition' ";
226 if($injobname != "") $sql .= " AND name = '".$injobname."_jobdefinition'";
227 $raw_job_defintions=$wpdb->get_results($sql);
228
229 if(!$raw_job_defintions) return false;
230
231 //build up an array with jobs
232 foreach($raw_job_defintions as $gotrow => $raw_job_defintion){
233 $jobname=str_replace('_jobdefinition','',$raw_job_defintion->name);
234 $valuepairs=explode(',',$raw_job_defintion->value); //a value of a raw_job_defintion to explode looks like: startpoint=a10,\r\nmaxexecutiontime=1000
235 foreach($valuepairs as $valuenumber => $valuepair){
236 $rawvalues=explode('=',$valuepair); // a valuepair to explode looks like \r\nmaxexecutiontime=1000\r\n
237 $jobrow[$jobname][trim($rawvalues[0])]=trim($rawvalues[1]);
238 }
239 //fine - we have got parsed a jobdefinition - so let us now build the values for the job row to insert into db
240 if(!isset($jobrow[$jobname]['startpoint'])){
241 $jobrow[$jobname]['startpoint']='a10';
242 }else{
243 if(substr($jobrow[$jobname]['startpoint'],0,1)=='a'){
244 $jobrow[$jobname]['nextstarttimestamp']=time() + abs(intval(substr($jobrow[$jobname]['startpoint'],1)));
245 }elseif(substr($jobrow[$jobname]['startpoint'],0,1)=='d'){
246 //we need to find out the timepoint of next start, we have pairs like startpoint=d00-03:00h
247 //how can we specify, that a job shall be run each hour or each minute?
248 //we need in the config a cron like syntax with placeholders?
249 $daytmp=substr($jobrow[$jobname]['startpoint'],1,2);
250 $shour=substr($jobrow[$jobname]['startpoint'],4,2);
251 $sminute=substr($jobrow[$jobname]['startpoint'],7,2);
252 //compare the time
253 $later = 0;
254 if((date("H") . date("i")) >= ($shour . $sminute)) $later = 1;
255 if(intval($daytmp) == 0) {
256 $sday = date("d") + $later;
257 $smonth = date("m");
258 }else{
259 $nextmonth = 0;
260 if((date("d") . date("H") . date("i")) >= ($daytmp . $shour . $sminute)) $nextmonth = 1;
261 $sday = $daytmp;
262 $smonth = date("m") + $nextmonth;
263 }
264 $jobrow[$jobname]['nextstarttimestamp'] = mktime($shour, $sminute, 0, $smonth, $sday, date("Y"));
265 }//end if startpoint 0,1 ==
266 $jobrow[$jobname]['nextstart']=date("Y-m-d H:i:s", $jobrow[$jobname]['nextstarttimestamp']);
267 if(!isset($jobrow[$jobname]['maxexecutiontime']))$jobrow[$jobname]['maxexecutiontime'] = 10;
268 $jobrow[$jobname]['killtimestamp']= time() + $jobrow[$jobname]['maxexecutiontime'];
269 $jobrow[$jobname]['killtime']=date("Y-m-d H:i:s", $jobrow[$jobname]['killtimestamp']);
270 }//end if isset startpoint
271
272 //we have now the values for this jobdefinition
273 $insert="REPLACE";
274 if($action=="insert"){
275 $insert="INSERT IGNORE";
276 }
277 $sqlbase=$insert . " INTO $this->mtablestatus ( memtable , datetime , status , notice ) VALUES ";
278 $sqlvalues =" ( '".$jobname."', '".$jobrow[$jobname]['nextstart']."', 'jobwaiting', 'Created at: ".date("Y-m-d H:i:s")."')";
279 if($action=="start") $sqlvalues ="( '".$jobname."', '".$jobrow[$jobname]['killtime']."', 'jobrunning', 'Started at: ".date("Y-m-d H:i:s")."')";
280 $result=$wpdb->query($sqlbase . $sqlvalues);
281 //echo "sql was: " . $sqlbase . $sqlvalues;
282
283 //the optionstable shall be just read when memtables are crashed to reconstruct memtables,anyway we have to write
284 $sqlbase=$insert . " INTO $this->optionstable ( name , last_change , type , value , notice ) VALUES ";
285 $sqlvalues =" ( '".$jobname."', '".$jobrow[$jobname]['nextstart']."', 'job', 'waiting', 'Created at: ".date("Y-m-d H:i:s")."')";
286 if($action=="start") $sqlvalues = " ( '".$jobname."', '".$jobrow[$jobname]['killtime']."', 'job', 'running', 'Started at: ".date("Y-m-d H:i:s")."')";
287 $result=$wpdb->query($sqlbase . $sqlvalues);
288 //echo "Query was: " . $sqlbase . $sqlvalues;
289
290 }// end foreach raw job definitions
291 //print_r($jobrow);
292 return $jobrow;
293 }
294
295 function change_jobstatus($jobname, $newstatus){
296 global $wpdb;
297
298 $jobname=preg_replace('/[^a-zA_Z0-9_-]/','',$jobname);
299 $killtime=date("Y-m-d H:i:s",time()+10);
300 $sql="REPLACE INTO $this->mtablestatus ( memtable , datetime , status , notice ) VALUES ";
301 $sql .=" ( '".$jobname."', '".$killtime."', 'job".$newstatus."', 'Advertized at: ".date("Y-m-d H:i:s")."')";
302 $result=$wpdb->query($sql);
303
304 return $result;
305 }
306
307 function table_exists($tablename)
308 {
309 global $wpdb;
310 return ($wpdb->get_var("show tables like '$tablename'") == $tablename);
311 }
312
313
314 function create($try_memtable_creation=true)
315 {
316 /* create() - create the database tables if they do not already exist */
317
318 global $wpdb;
319
320 $blog_name=get_option('blogname');
321
322 $engine="MYISAM";
323 $btree="";
324 if($try_memtable_creation) $engine="memory";
325 if($try_memtable_creation) $btree =" USING BTREE ";
326
327 if(!$this->table_exists($this->optionstable)){
328 $sql = "CREATE TABLE IF NOT EXISTS $this->optionstable (
329 id INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'an autoincrement id',
330 name VARCHAR( 255 ) NOT NULL COMMENT 'a unique name for the option',
331 last_change TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Just automatic timestamp of last update',
332 type VARCHAR( 255 ) NOT NULL COMMENT 'array, string, bool etc',
333 value TEXT NOT NULL COMMENT 'Binary safe value of the pba option',
334 notice TEXT NOT NULL COMMENT 'A notice for the option',
335 UNIQUE ( name )
336 ) ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci COMMENT = 'globals options for pba'";
337 $result = $wpdb->query($sql);
338 $this->prefill_table($this->optionstable);
339 }
340
341 if(!$this->table_exists($this->pbaoutputtable)){
342 $sql = "CREATE TABLE IF NOT EXISTS $this->pbaoutputtable (
343 identifier int(10) NOT NULL auto_increment,
344 name varchar(255) NOT NULL default '-',
345 page2hookin int(11) NOT NULL default '0' COMMENT 'The wordpress page to hook in this output',
346 default_list int(11) NOT NULL default '0' COMMENT 'The Default List Filter for the Output format',
347 items_per_site int(4) NOT NULL default '10',
348 type enum('countrecentitem','daterecentitem','sitealpha','siteupdate') NOT NULL default 'countrecentitem',
349 template_ticker text COMMENT 'template for ticker',
350 append_extra_link enum('Y','N') NOT NULL default 'Y',
351 append_cache_link enum('Y','N') NOT NULL default 'Y',
352 add_social_bookmarks enum('Y','N') NOT NULL default 'Y',
353 template_sidebarwidget text COMMENT 'template for sidebar widgets',
354 max_words_in_synposis int(4) NOT NULL default '10',
355 max_word_length int(4) NOT NULL default '35',
356 item_date_format varchar(30) default 'd.m.Y \\\u\\\m H:i\\\h',
357 allowable_xhtml_tags varchar(150) default '',
358 enable_caching enum('Y','N') NOT NULL default 'Y',
359 template_cache text NOT NULL COMMENT 'template for the cache page',
360 cache_view_page varchar(200) default NULL,
361 channel_title varchar(100) default 'Feedmix von ".$blog_name."',
362 channel_link varchar(100) default '',
363 channel_description varchar(200) default 'Feedmix generiert mit dem Parteibuch Aggregator)',
364 channel_language varchar(10) default 'de',
365 channel_copyright varchar(100) default 'Verschiedene (Details Siehe Autorenlinks)',
366 template_kalender text NOT NULL,
367 archive_date_format varchar(255) NOT NULL default 'Y-m-d',
368 kalendermonthslist varchar(255) NOT NULL default 'Januar, Februar, M&auml;rz, April, Mai, Juni, Juli, August, September, Oktober, November, Dezember',
369 kalenderboxtablecaption varchar(255) NOT NULL default ' id=\"kalendertable\"',
370 kalender_last varchar(255) NOT NULL default 'Fr&uuml;her',
371 kalender_next varchar(255) NOT NULL default 'Sp&auml;ter',
372 kalenderboxdaysofweeklist varchar(255) NOT NULL default 'Montag, Dienstag, Mittwoch, Donnerstag, Freitag, Samstag, Sonntag',
373 superparameter text NOT NULL COMMENT 'free style paras',
374 PRIMARY KEY (identifier)
375 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci AUTO_INCREMENT=1";
376 $result = $wpdb->query($sql);
377 $this->prefill_table($this->pbaoutputtable);
378 }
379
380 if(!$this->table_exists($this->listtable)){
381 $sql = "CREATE TABLE IF NOT EXISTS $this->listtable (".
382 // about the list
383 "$this->lidentifier int(10) NOT NULL auto_increment, " .
384 "$this->lname varchar(255) NOT NULL default '-', ".
385 "$this->lurls text, ".
386 "$this->llistall enum('Y','N') NOT NULL default 'N', " .
387 "PRIMARY KEY ($this->lidentifier) ) CHARSET=latin1";
388 $result = $wpdb->query($sql);
389 $this->prefill_table($this->listtable);
390 }
391
392 $sql = "CREATE TABLE IF NOT EXISTS $this->errortable (".
393/* changed */ "$this->eidentifier int(10) NOT NULL auto_increment, ".
394 "$this->efeedurl varchar(240) NOT NULL, ".
395 "$this->etime int(15) NOT NULL, ".
396 "$this->etext text NOT NULL, ".
397 "PRIMARY KEY ($this->eidentifier), ".
398/* changed */ "INDEX ($this->efeedurl) ) DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ";
399 $result = $wpdb->query($sql);
400
401 if(!$this->table_exists($this->sitetable)){
402 $sql = "CREATE TABLE IF NOT EXISTS $this->sitetable (".
403 "$this->cidentifier int(10) NOT NULL auto_increment, " .
404 "$this->cfeedurl varchar(240) NOT NULL, ".
405 "$this->csitename varchar(255), ".
406 "$this->csitenameoverride enum('Y','N') NOT NULL default 'N', " .
407 "$this->cdescription varchar(255), ".
408 "$this->csitelicense varchar(255), ".
409 "$this->csiteurl varchar(255), ".
410 "$this->clastpolltime int(15) NOT NULL DEFAULT 1, " .
411 "$this->cnextpolltime int(15) NOT NULL DEFAULT 1, " .
412 "$this->cupdatetime int(15) NOT NULL DEFAULT 1, " .
413 "$this->cgmtadjust float(4,1) NOT NULL DEFAULT 0.0, ".
414 "$this->cpollingfreqmins int(6) NOT NULL DEFAULT 0, ".
415 "PRIMARY KEY ($this->cidentifier), ".
416 "UNIQUE KEY ($this->cfeedurl), ".
417 "INDEX ($this->clastpolltime) ) DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ";
418 $result = $wpdb->query($sql);
419 $this->prefill_table($this->sitetable);
420 }
421
422 $sql = "CREATE TABLE IF NOT EXISTS $this->itemtable (".
423 "$this->iidentifier int(10) NOT NULL auto_increment, ".
424 "$this->ifeedurl varchar(240) NOT NULL, ".
425 "$this->iitemurl varchar(255) NOT NULL, ".
426 "$this->iitemname varchar(255) NOT NULL, ".
427 "$this->iitemsitename varchar(255) NOT NULL, ".
428 "$this->iitemsiteurl varchar(255) NOT NULL, ".
429 "$this->iitemlicense varchar(255) NOT NULL, ".
430 "$this->iitemtext text NOT NULL, ".
431 "$this->iitemtime int(15) NOT NULL, ".
432 "$this->iitemdate date NOT NULL, ".
433 "$this->iitemupdate int(15) NOT NULL, ".
434 "PRIMARY KEY ($this->iidentifier), " .
435 "UNIQUE KEY ($this->ifeedurl (200), $this->iitemurl (100)), ".
436 "INDEX ($this->iitemdate), ".
437 "INDEX ($this->ifeedurl), ".
438 "INDEX ($this->iitemtime) ) DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ";
439 //echo $sql;
440 $result = $wpdb->query($sql);
441
442 $sql= "CREATE TABLE IF NOT EXISTS $this->mtablestatus (
443$this->memtable CHAR( 60 ) NOT NULL ,
444$this->mdatetime DATETIME,
445$this->mstatus CHAR( 10 ) NOT NULL ,
446$this->mnotice CHAR( 50 ) NOT NULL ,
447PRIMARY KEY ( $this->memtable )
448) ENGINE = " . $engine . " DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ";
449 $result = $wpdb->query($sql);
450
451 $sql = "CREATE TABLE IF NOT EXISTS $this->msitetable (".
452 "$this->midentifier int(10) NOT NULL auto_increment, " .
453 "$this->mfeedurl varchar(240) NOT NULL, ".
454 "$this->msitename varchar(255), ".
455 "$this->msitenameoverride enum('Y','N') NOT NULL default 'N', " .
456 "$this->mdescription varchar(255), ".
457 "$this->msitelicense varchar(255), ".
458 "$this->msiteurl varchar(255), ".
459 "$this->mlastpolltime int(15) NOT NULL DEFAULT 1, " .
460 "$this->mnextpolltime int(15) NOT NULL DEFAULT 1, " .
461 "$this->mupdatetime int(15) NOT NULL DEFAULT 1, " .
462 "$this->mgmtadjust float(4,1) NOT NULL DEFAULT 0.0, ".
463 "$this->mpollingfreqmins int(6) NOT NULL DEFAULT 0, ".
464 "PRIMARY KEY " . $btree . " ($this->midentifier), ".
465 "UNIQUE KEY ($this->mfeedurl), ".
466 "INDEX " . $btree . " ($this->mlastpolltime) ) ENGINE = " . $engine . " DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ";
467 $result = $wpdb->query($sql);
468
469 $sql= "CREATE TABLE IF NOT EXISTS $this->mitemtable (
470 $this->miid mediumint(8) unsigned NOT NULL,
471 $this->misiteid smallint(5) unsigned NOT NULL,
472 $this->miitemtime int(15) NOT NULL,
473 $this->miitemdate date NOT NULL,
474 PRIMARY KEY ($this->miid),
475 KEY `Memory_index` " . $btree . " ($this->misiteid,$this->miitemtime),
476 KEY `Memory_index_time_id` " . $btree . " ($this->miitemtime,$this->misiteid)
477 ) ENGINE = " . $engine . " DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ";
478 $result = $wpdb->query($sql);
479
480 if($try_memtable_creation){
481 $this->create(false);
482 }
483
484 }
485
486 function prefill_table($tablename){
487 global $wpdb;
488 if($tablename == $this->sitetable){
489 $feed_url=mysql_real_escape_string(get_pbadefaultsite('feed_url'));
490 $site_name=mysql_real_escape_string(get_pbadefaultsite('site_name'));
491 $description=mysql_real_escape_string(get_pbadefaultsite('description'));
492 $site_license=mysql_real_escape_string(get_pbadefaultsite('site_license'));
493 $site_url=mysql_real_escape_string(get_pbadefaultsite('site_url'));
494 $sql="INSERT INTO $this->sitetable (
495 feed_url , site_name , description , site_license , site_url
496 ) VALUES (
497 '".$feed_url."',
498 '".$site_name."', '".$description."',
499 '".$site_license."' , '".$site_url."'
500 )";
501 }
502 if($tablename == $this->listtable){
503 $sql="INSERT INTO $this->listtable (identifier, name, url_list, list_all)
504 VALUES (1, 'Full Text Ticker', '1', 'Y')";
505 }
506 if($tablename == $this->pbaoutputtable){
507 $outputname=mysql_real_escape_string(get_pbadefaultparameter('outputname'));
508 $template_ticker=mysql_real_escape_string(get_pbadefaultparameter('template_ticker'));
509 $template_sidebarwidget=mysql_real_escape_string(get_pbadefaultparameter('template_sidebarwidget'));
510
511 $channel_language=mysql_real_escape_string(get_pbadefaultparameter('channel_language'));
512 $channel_copyright=mysql_real_escape_string(get_pbadefaultparameter('channel_copyright'));
513
514 $template_cache=mysql_real_escape_string(get_pbadefaultparameter('template_cache'));
515 $template_kalender=mysql_real_escape_string(get_pbadefaultparameter('template_kalender'));
516 $kalendermonthslist=mysql_real_escape_string(get_pbadefaultparameter('kalendermonthslist'));
517 $kalender_last=mysql_real_escape_string(get_pbadefaultparameter('kalender_last'));
518 $kalender_next=mysql_real_escape_string(get_pbadefaultparameter('kalender_next'));
519 $kalenderboxdaysofweeklist=mysql_real_escape_string(get_pbadefaultparameter('kalenderboxdaysofweeklist'));
520
521 $sql="INSERT INTO $this->pbaoutputtable
522 (name, page2hookin, default_list, items_per_site, type, template_ticker,
523 append_extra_link, append_cache_link, add_social_bookmarks, template_sidebarwidget,
524 max_words_in_synposis, max_word_length, item_date_format, allowable_xhtml_tags, enable_caching,
525 template_cache, cache_view_page, channel_title, channel_link,
526 channel_description, channel_language, channel_copyright, template_kalender, archive_date_format,
527 kalendermonthslist,
528 kalenderboxtablecaption, kalender_last, kalender_next,
529 kalenderboxdaysofweeklist, superparameter)
530 VALUES
531 ('".$outputname."', 0, 1, 25, 'countrecentitem', '".$template_ticker."',
532 'Y', 'Y', 'Y', '".$template_sidebarwidget."',
533 100, 40, 'd.m.Y H:i\\\h', '', 'Y',
534 '".$template_cache."', '', 'Parteibuch Aggregator Feed', '',
535 'Parteibuch Aggregator Feedmix', '".$channel_language."', '".$channel_copyright."', '".$template_kalender."', 'Y-m-d',
536 '".$kalendermonthslist."',
537 ' style=&quot;text-align: center;&quot; id=&quot;kalendertable&quot;',
538 '".$kalender_last."', '".$kalender_next."', '".$kalenderboxdaysofweeklist."', '')
539 ";
540 }
541 if($tablename == $this->optionstable){
542 $sql="INSERT INTO $this->optionstable ( name , type , value , notice )
543 VALUES
544 ( 'enable_caching', 'string', 'auto', 'shall be one of Y, N, auto, if not set, default shall be auto' )
545 , ( 'enable_rewriting', 'string', 'Y', 'shall be one of Y, N, if not set, default shall be Y' )
546 , ( 'full_cache_time', 'int', '60', '0 shall disable this cache' )
547 , ( 'kalenderquery_cache_time', 'int', '3600', '0 shall disable this cache' )
548 , ( 'feedlistquery_cache_time', 'int', '7200', '0 shall disable this cache' )
549 , ( 'enable_memtables', 'string', 'auto', 'shall be one of Y, N, auto, if not set, default shall be auto' )
550 , ( 'enable_loaddetection', 'string', 'auto', 'shall be one of Y, N, auto, if not set, default shall be auto' )
551 , ( 'highloadthreshold', 'int', '10', '0 may disable housekeeping jobs' )
552 , ( 'update_oldest_jobdefinition', 'jobdefinition', 'startpoint=a10,\r\nmaxexecutiontime=100', 'shall be started not earlier than 10 seconds after the last time it finished')
553 , ( 'housekeeping_cache_jobdefinition', 'jobdefinition', 'startpoint=d00-03:00h,\r\nmaxexecutiontime=3600', 'Shall be executed each day one time the next time after a page was called after 3 oclock in the morning')
554 , ( 'process_updates_jobdefinition', 'jobdefinition', 'startpoint=a3600,\r\nmaxexecutiontime=100', 'shall be processed one hour - 3600 seconds - after this job ended the last time')
555 , ( 'process_new_jobdefinition', 'jobdefinition', 'startpoint=a3600, \r\nmaxexecutiontime=500', 'new entries to the search index shall be processed 3600 seconds after it run the last time, but only, if there were no unprocessed new records left')
556 , ( 'process_deletes_jobdefinition', 'jobdefinition', 'startpoint=a3600,\r\nmaxexecutiontime=100', 'deletions from the search index shall be done monthly at the first page call after day 3 04:17h.')
557 ";
558 }
559 $result = $wpdb->query($sql);
560 return mysql_insert_id();
561 } //end function
562
563 function get_mysql_variables($varname=""){
564 global $wpdb;
565 $sql="SHOW VARIABLES ";
566 if(is_string($varname) && $varname != '') $sql .= " LIKE '".$varname."'";
567 $result = $wpdb->get_row($sql);
568 if(isset($result->Value)){
569 return $result->Value;
570 } else {
571 return "";
572 }
573 }
574
575 function get_mysql_tablestatus($tablename=""){
576 global $wpdb;
577 $sql="SHOW TABLE STATUS ";
578 if(is_string($tablename) && $tablename != '') $sql .= " LIKE '".$tablename."'";
579 $result = $wpdb->get_row($sql);
580 if($result){
581 return $result;
582 } else {
583 return false;
584 }
585 }
586
587
588 function prefill_memtables(){
589 global $wpdb;
590
591// echo "<br>Filling memtables ... ";
592
593 //set memory status value for all tables to start
594 $sql= "REPLACE INTO $this->mtablestatus (
595 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
596 ) VALUES ( 'sites', NOW( ) , 'start' , 'prefilling'),
597 ( 'items', NOW( ) , 'start' , 'prefilling')";
598 $result = $wpdb->query($sql);
599
600 //fill site memory table
601 $sql= "TRUNCATE TABLE $this->msitetable ";
602 $result = $wpdb->query($sql);
603
604 $sql= "INSERT INTO $this->msitetable
605 SELECT * FROM $this->sitetable";
606 $result = $wpdb->query($sql);
607
608 //fill item index memory table
609 $sql= "TRUNCATE TABLE $this->mitemtable ";
610 $result = $wpdb->query($sql);
611
612 $sql= "INSERT INTO $this->mitemtable
613 SELECT itemtable.$this->iidentifier as $this->miid, msitetable.$this->midentifier as $this->misiteid,
614 itemtable.$this->iitemtime as $this->miitemtime, itemtable.$this->iitemdate as $this->miitemdate
615 FROM $this->itemtable itemtable, $this->msitetable msitetable
616 WHERE itemtable.$this->ifeedurl = msitetable.$this->mfeedurl";
617 $result = $wpdb->query($sql);
618
619 //prefill jobtable
620 $this->jobaction();
621
622 //if all OK, set mem table status to OK
623 $sql= "REPLACE INTO $this->mtablestatus (
624 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
625 ) VALUES ( 'sites', NOW( ) , 'ok', 'prefilled' ),
626 ( 'items', NOW( ) , 'ok' , 'prefilled')";
627 $result = $wpdb->query($sql);
628
629// echo "Memtables filled";
630
631 return true;
632 }
633
634 function mark_entry_as_old_in_statustable($name){
635 //this will make some memtablerows to be recreated at next pagecall
636 global $wpdb;
637 $sql= "UPDATE $this->mtablestatus SET $this->mdatetime = FROM_UNIXTIME( '0' )
638 WHERE $this->memtable = '".$name."'";
639 $result = $wpdb->query($sql);
640 return $result;
641 }
642
643
644//Check the status of the memory tables
645//possible returns
646// 0: not filled - need to be filled
647// 1: ok - filled and fine
648// 2: start - filling in progress going on - please wait
649// 3: start - filling in progress hanging already too long - need to be refilled
650
651
652 function get_memtable_status(&$status)
653 {
654 global $wpdb;
655 $maxstartingtime="7200";
656 $memtablestatus=0;
657 $totalstatus=0;
658 $keycounter=0;
659 $status=array();
660 $status['memtablestatus']=0;
661
662
663 $sql= "SELECT $this->memtable, UNIX_TIMESTAMP() - UNIX_TIMESTAMP($this->mdatetime) as age, $this->mstatus, $this->mnotice FROM $this->mtablestatus";
664 $result=false;
665 $result = $wpdb->get_results($sql);
666// echo "<br>Mem table status: ";
667 if(!$result) {
668// echo "Mem tables not filled!";
669 } else {
670 $jobcounter=0;
671 $maxagejob['age']=0;
672 foreach($result as $key => $r)
673 {
674 $keycounter++;
675 if($r->{$this->mstatus}=="ok" || $r->{$this->mstatus} == "unusable"
676 || $r->{$this->mstatus} == "disabled" || $r->{$this->mstatus} == "value"
677 || substr($r->{$this->mstatus},0,3) == "job"
678 ) $totalstatus++;
679 //$totalstatus.=$r->{$this->mstatus};
680 if($r->{$this->mstatus}=="start"){
681 $memtablestatus=2;
682 if($r->age > $maxstartingtime) $memtablestatus=3;
683 }
684 //copy the detailed status into the overloaded array
685 $status[$r->{$this->memtable}]['status'] = $r->{$this->mstatus};
686 $status[$r->{$this->memtable}]['age'] = $r->age;
687 $status[$r->{$this->memtable}]['notice'] = $r->{$this->mnotice};
688 if(substr($r->{$this->mstatus}, 0,3) == "job") $jobcounter++;
689 if(substr($r->{$this->mstatus}, 0,3) == "job" && $r->age > 0) {
690 //we shall look if we shall do something with a joba job should be run or running
691 if(substr($r->{$this->mstatus}, 3) == "running" ){
692 $status['job2kill']=$r->{$this->memtable};
693 }elseif(substr($r->{$this->mstatus}, 3) == "tostart"){
694 $status['job2restart']=$r->{$this->memtable};
695 }elseif(substr($r->{$this->mstatus}, 3) == "waiting"){
696 if($r->age > $maxagejob['age']){
697 $maxagejob['name']=$r->{$this->memtable};
698 $maxagejob['age']=$r->age;
699 $maxagejob['status']= $r->{$this->mstatus};
700 }
701 }
702 }
703 }
704 }
705
706 //now let's find out a mechanism to bring the whole status into one value
707 if ($totalstatus > 0 && $keycounter == $totalstatus ) {
708 $memtablestatus = 1;
709 $status['memtablestatus']=1;
710
711 //not really elegant to have the number of available jobs here hardcoded, but who cares
712 if($jobcounter == 5){
713 if(isset($status['job2kill'])){
714 //job is running too long - kill this job - this fallback should just be hit in case of a job crash
715 $this->change_jobstatus($status['job2kill'], 'waiting');
716 }elseif(isset($status['job2restart'])){
717 $this->change_jobstatus($status['job2restart'], 'tostart');
718 $status['job2start']['name']=$status['job2restart'];
719 $status['job2start']['time']=time();
720 }elseif(isset($maxagejob['name'])){
721 //announce a new job
722 $this->change_jobstatus($maxagejob['name'], 'tostart');
723 $status['job2start']['name']=$maxagejob['name'];
724 $status['job2start']['time']=time();
725 }
726 }else{
727 $this->jobaction();
728 }
729 }
730
731 $this->check_load($status);
732
733 if(!isset($status['pbacache']) || $status['pbacache']['age'] > 10800) {
734 //check cache
735 $this->check_cache($status);
736 }
737
738 //echo "Wea re here: status_pbacache is " . $status['pbacache'];
739 if(isset($status['pbacache']['status']) && $status['pbacache']['status'] == 'ok') {
740 if(!isset($status['full_cache_time']) || !isset($status['kalenderquery_cache_time']) || !isset($status['feedlistquery_cache_time'])){
741 $this->check_options($status);
742 }
743 }
744
745 if(!isset($status['rewriting']) || $status['rewriting']['age'] > 86400) {
746 //check rewriting
747 $this->check_rewriting($status);
748 }
749
750//print_r($status);
751
752 return $memtablestatus;
753 }
754
755 function check_rewriting(&$status){
756 global $wpdb;
757 $pbadefault=$this->pbaoption('enable_rewriting');
758 if($pbadefault != 'Y') {
759 $status['rewriting']['status'] = 'disabled';
760 $status['rewriting']['notice'] = 'N00003: Rewriting option disabled';
761 $return = false;
762 }elseif($pbadefault == 'Y'){
763 $rewrite_array=get_option('rewrite_rules');
764 if(is_array($rewrite_array)){
765 $rulematch=false;
766 foreach($rewrite_array as $key => $value){
767 if(strstr($key,'ticker-feed')) $rulematch=true;
768 }
769 if($rulematch){
770 $status['rewriting']['status'] = 'ok';
771 $status['rewriting']['notice'] = 'N00004: Permalinks in effect';
772 $return = true;
773 }
774 }
775 if(!isset($return)){
776 $status['rewriting']['status'] = 'unusable';
777 $status['rewriting']['notice'] = 'W00002: Wordpress has not enabled rewrite';
778 $return = false;
779 }
780 }
781 $sql= "REPLACE INTO $this->mtablestatus (
782 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
783 ) VALUES ( 'rewriting', NOW( ) , '".$status['rewriting']['status']."' , '".$status['rewriting']['notice']."')";
784 $result = $wpdb->query($sql);
785 return $return;
786 }
787
788 function check_options(&$status){
789 global $wpdb;
790//initialisation
791 $newstatus=array();
792 $result = $this->get_all_options();
793
794 if(!$result){
795 return false;
796 } else {
797 foreach($result as $key => $r){
798 if($r->name == 'full_cache_time') $newstatus['full_cache_time']=array('status' => 'value', 'notice' => $r->value);
799 if($r->name == 'kalenderquery_cache_time') $newstatus['kalenderquery_cache_time']=array('status' => 'value', 'notice' => $r->value);
800 if($r->name == 'feedlistquery_cache_time') $newstatus['feedlistquery_cache_time']=array('status' => 'value', 'notice' => $r->value);
801 if($r->name == 'highloadthreshold') $newstatus['highloadthreshold']=array('status' => 'value', 'notice' => $r->value);
802 if($r->name == 'enable_loaddetection') $newstatus['loaddetection']=array('status' => 'value', 'notice' => $r->value);
803 }
804
805 if(count($newstatus)>0){
806 $sql= "REPLACE INTO $this->mtablestatus (
807 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
808 ) VALUES ";
809
810 foreach($newstatus as $fieldname => $values){
811 $sql .= "\n( '".$fieldname."', NOW( ) , '".$values['status']."' , '".$values['notice']."'), ";
812 }
813 $sql = preg_replace('/, $/','', $sql);
814 $result = $wpdb->query($sql);
815 $status=array_merge($status, $newstatus); //advertising
816 return $result;
817 }
818 }
819 return false;
820 }
821
822 function pba_loaddetection(){
823 if(function_exists('sys_getloadavg')) {
824 $load=sys_getloadavg();
825 $load[1]=$load[0];
826 }elseif(ini_get('safe_mode')){
827 if(ini_get('safe_mode_exec_dir') == ""){
828 $serverload = "0";
829 } else {
830 preg_match("/averages?: ([0-9\.]+),[\s]+([0-9\.]+),[\s]+([0-9\.]+)/",@exec(ini_get('safe_mode_exec_dir') . 'uptime_wrapper.sh'),$load);
831 }
832 }else{
833 //no safe mode, so directly call system command
834 preg_match("/averages?: ([0-9\.]+),[\s]+([0-9\.]+),[\s]+([0-9\.]+)/",@exec('uptime'),$load);
835 if(!isset($load[1])) preg_match("/averages?: ([0-9\.]+),[\s]+([0-9\.]+),[\s]+([0-9\.]+)/",@exec(dirname(__FILE__) . '/wrapper/uptime_wrapper.sh'),$load);
836 }
837 if(!isset($load[1])) {
838 $serverload = 0;
839 }else{
840 $serverload = abs($load[1]);
841 }
842 return $serverload;
843 }
844
845 function check_load(&$status){
846 global $wpdb;
847 $detectedload=0;
848 //is load detection enabled?
849
850 if(isset($status['loaddetection']['notice'])
851 && isset($status['pbaload']['notice'])
852 && isset($status['pbaload']['age'])
853 ){
854 //option disabled and load set to zero, nothing to do here
855 if($status['loaddetection']['notice'] == 'N' && $status['pbaload']['notice'] == '0') return true;
856
857 //call uptime just every three seconds
858 if($status['loaddetection']['notice'] == 'Y' && $status['pbaload']['age'] < 3) return true;
859
860 //retry once per hour
861 if($status['loaddetection']['notice'] == 'retry' && $status['pbaload']['age'] < 3600) return true;
862
863 if($status['loaddetection']['notice'] == 'Y'
864 || $status['loaddetection']['notice'] == 'auto'
865 || $status['loaddetection']['notice'] == 'retry'
866 ){
867 $detectedload=$this->pba_loaddetection();
868 if($status['loaddetection']['notice'] == 'auto' || $status['loaddetection']['notice'] == 'retry'){
869 $newstatus['loaddetection']['status']='value';
870 if($detectedload > 0){
871 $newstatus['loaddetection']['notice']='Y';
872 }else{
873 $newstatus['loaddetection']['notice']='retry';
874 }
875 $sql= "REPLACE INTO $this->mtablestatus (
876 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
877 ) VALUES ( 'loaddetection', NOW( ) , '".$newstatus['loaddetection']['status']."' , '".$newstatus['loaddetection']['notice']."')";
878 $result = $wpdb->query($sql);
879 }
880 }
881 } //end if isset status load detection
882 $newstatus['pbaload']=array('status' => 'value', 'notice' => abs($detectedload));
883 $sql= "REPLACE INTO $this->mtablestatus (
884 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
885 ) VALUES ( 'pbaload', NOW( ) , '".$newstatus['pbaload']['status']."' , '".$newstatus['pbaload']['notice']."')";
886 $result = $wpdb->query($sql);
887 $status=array_merge($status, $newstatus); //advertising
888 return $result;
889 }
890
891 function check_cache(&$status){
892 global $wpdb;
893 //run on class construction, PBALIB not yet loaded here!!!
894 $cache_status=true;
895 //$cache_path=dirname(__FILE__)."/pbacache/";
896 $cache_path=PBA_CACHE_PATH;
897 $cache_file_handle = @fopen($cache_path."cachetest__t", 'w+');
898 $content=rand() . 'test';
899 if(!@fwrite($cache_file_handle, $content)) $cache_status=false;
900 @fclose($cache_file_handle);
901 if(!$get_cache=@file_get_contents($cache_path."cachetest__t")) $cache_status=false;
902 if(!$get_cache == $content) $cache_status=false;
903 //echo "cache_status " . $cache_status;
904 //exit;
905 @unlink($cache_path."cachetest__t");
906 $pbadefault=$this->pbaoption('enable_caching');
907 if($cache_status==false) {
908 $status['pbacache']['status'] = 'unusable';
909 $status['pbacache']['notice'] = 'W00001: Cachepath not writeable';
910 $return = false;
911 }elseif($pbadefault != 'Y' && $pbadefault != 'auto'){
912 $status['pbacache']['status'] = 'disabled';
913 $status['pbacache']['notice'] = 'N00005: Cachepath is writable';
914 $return = true;
915 }else{
916 $status['pbacache']['status'] = 'ok';
917 $status['pbacache']['notice'] = 'N00002: Checked';
918 $return = true;
919 }
920
921 $sql= "REPLACE INTO $this->mtablestatus (
922 $this->memtable , $this->mdatetime , $this->mstatus, $this->mnotice
923 ) VALUES ( 'pbacache', NOW( ) , '".$status['pbacache']['status']."' , '".$status['pbacache']['notice']."')";
924 $result = $wpdb->query($sql);
925 return $return;
926 }
927
928 /* --- insert --- */
929 function recordError($url, $text)
930 {
931 global $wpdb;
932
933 if(!$url) return;
934
935 // some SQL insertion protection using HTML entities
936 $text = preg_replace("/'/", '&#39;', $text);
937 $text = preg_replace('/"/', '&quot;', $text);
938
939 if(!$text) $text = 'Unknown';
940
941 $sql = "INSERT INTO $this->errortable ($this->efeedurl, $this->etime, $this->etext) ".
942 "VALUES ('$url', '$this->now', '$text')";
943 $result = $wpdb->query($sql);
944 }
945
946 function update_feedurl($feed_id, $pba_change_feed_to) {
947 global $wpdb;
948 //function will return false, if there shall no update of feed url in site tables made
949 //check input values
950 if($feed_id != abs(intval($feed_id)) || $feed_id == 0) return false;
951 if(preg_match('/[\'"]/',$pba_change_feed_to)) return false;
952
953 //return false if the feed url already exists, logic to merge feeds is not implemented
954 $sql="SELECT count(*) + 1 as numberoffeeds from $this->msitetable WHERE $this->mfeedurl = '".$pba_change_feed_to."'";
955 if( $wpdb->get_var($sql) != 1) return false;
956 //OK, so now get the feed url to update
957 $sql="SELECT $this->mfeedurl from $this->msitetable WHERE $this->midentifier = '".$feed_id."'";
958 $oldfeedurl=$wpdb->get_var($sql);
959 if(!$oldfeedurl) return false;
960 $sql="UPDATE $this->itemtable set item_feed_url = '".$pba_change_feed_to."'
961 WHERE item_feed_url = '".$oldfeedurl."'";
962 $result = $wpdb->query($sql);
963 if($result === false) return false;
964 return true;
965 }
966
967 function createpbaoutput($as_copy_from=false)
968 {
969 /* createoutput() -- inserts an empty output format into the output table
970 or copies one with ID as_copy from false*/
971
972 global $wpdb;
973
974 if($as_copy_from===false){
975 return $this->prefill_table($this->pbaoutputtable);
976 } else {
977 $sql = "SELECT * FROM $this->pbaoutputtable WHERE $this->pbaoidentifier = '$as_copy_from'";
978 $row_to_copy = $wpdb->get_row($sql);
979 if($row_to_copy){
980 $fieldnames = $this->pbaoname . ", " . $this->pbaopage2hookin;
981 $values = "'Copy of " . $row_to_copy->{$this->pbaoname} . "', 0";
982 foreach($row_to_copy as $fieldname => $fieldvalue){
983 if($fieldname != $this->pbaoidentifier
984 && $fieldname != $this->pbaoname
985 && $fieldname != $this->pbaopage2hookin
986 ){
987 $fieldnames .= ", " . $fieldname;
988 $fieldvalue = mysql_real_escape_string($fieldvalue);
989 $values .= ", '" . $fieldvalue . "'";
990 }
991 }
992 $sql="INSERT INTO $this->pbaoutputtable ($fieldnames) ".
993 "VALUES ($values)";
994 $result = $wpdb->query($sql);
995 return mysql_insert_id();
996 }
997 }
998 }
999
1000
1001
1002 function createlist($as_copy_from=false)
1003 {
1004 /* createlist() -- inserts an empty output format into the list table
1005 or copies one with ID as_copy from false*/
1006
1007 global $wpdb;
1008
1009 if($as_copy_from===false){
1010 $sql = "INSERT INTO $this->listtable ($this->lname) ".
1011 "VALUES ('New list: please give it a meaningful name')";
1012 $result = $wpdb->query($sql);
1013 return mysql_insert_id();
1014 } else {
1015 $sql = "SELECT * FROM $this->listtable WHERE $this->lidentifier = '$as_copy_from'";
1016 $row_to_copy = $wpdb->get_row($sql);
1017 if($row_to_copy){
1018 $fieldnames = "$this->lname";
1019 $values = "'Copy of " . $row_to_copy->{$this->lname} . "'";
1020 foreach($row_to_copy as $fieldname => $fieldvalue){
1021 if($fieldname != $this->lidentifier && $fieldname != $this->lname){
1022 $fieldnames .= ", " . $fieldname;
1023 $fieldvalue = mysql_real_escape_string($fieldvalue);
1024 $values .= ", '" . $fieldvalue . "'";
1025 }
1026 }
1027 $sql="INSERT INTO $this->listtable ($fieldnames) ".
1028 "VALUES ($values)";
1029 $result = $wpdb->query($sql);
1030 return mysql_insert_id();
1031 }
1032 }
1033 }
1034
1035 function insert_in_sitetable($url, $polltime)
1036 {
1037 global $wpdb;
1038
1039 $sql = "INSERT INTO $this->sitetable ".
1040 "($this->cfeedurl, $this->clastpolltime) ".
1041 "VALUES ('$url', '$polltime')";
1042 $result = $wpdb->query($sql);
1043
1044//mem table
1045 $sql = "INSERT INTO $this->msitetable ".
1046 "($this->mfeedurl, $this->mlastpolltime) ".
1047 "VALUES ('$url', '$polltime')";
1048 $result = $wpdb->query($sql);
1049
1050 }
1051
1052
1053 /* --- insert or modify --- */
1054
1055 function updateItem($url, $title, $counter, $text, $link, $ticks, $itemsitename="", $itemsiteurl="", $itemlicense="")
1056 {
1057 /* updateItem($url, $title, $counter, $text, $link, $ticks)
1058 * -- this function either inserts or updates the item into the itemtable
1059 * -- only enters the time ($ticks) into the database for an insert - we ignore $ticks for updates
1060 * -- returns false on update and true on insert
1061 */
1062
1063 global $wpdb, $bdprss_search;
1064
1065 $sql = "SELECT $this->iidentifier as id, $this->iitemsitename as sitename, $this->iitemname as itemname, $this->iitemtext as itemtext,
1066 md5(concat($this->iitemsitename, ' ', $this->iitemname, ' ', $this->iitemtext)) as md5
1067 FROM $this->itemtable ".
1068 "WHERE $this->ifeedurl='$url' AND $this->iitemurl='$link'";
1069 $hid = $wpdb->get_row($sql);
1070
1071 $md5tocompare=md5($itemsitename . ' ' . $title . ' ' . $text);
1072 if($hid) $hidfromdb2compare = md5($hid->sitename . ' ' . $hid->itemname . ' ' . $hid->itemtext);
1073 $hidid=0;
1074 if($hid) $hidid = $hid->id;
1075
1076 if($hidid > 0 && (!isset($this->bdprss_prevent_dupe_updates[$hidid]) || (isset($this->bdprss_prevent_dupe_updates[$hidid]) && $this->bdprss_prevent_dupe_updates[$hidid]."" !="Done")))
1077 {
1078 if($md5tocompare != $hid->md5 && $md5tocompare != $hidfromdb2compare && rand(1,10) > 0){
1079
1080 $md5debug="MD5 Debug on Update: MD5 in DB: " . $hid->md5 .
1081 " md5compare: " . $md5tocompare .
1082 " md5direct: " . md5($itemsitename . ' ' . $title . ' ' . $text) .
1083 " mysql_real_escaped: " . md5(mysql_real_escape_string($itemsitename . ' ' . $title . ' ' . $text)) .
1084 " mysql_stripped_escaped: " . md5(mysql_real_escape_string(stripslashes($itemsitename . ' ' . $title . ' ' . $text))) .
1085 " hidfromdb2compare: " . $hidfromdb2compare;
1086
1087 $sql = "UPDATE $this->itemtable ".
1088 "SET $this->iitemupdate='".$this->now."', ".
1089 "$this->iitemtext='".mysql_real_escape_string($text)."', ".
1090 "$this->iitemname='".mysql_real_escape_string(htmlentities($title))."', ".
1091 "$this->iitemsitename='".mysql_real_escape_string($itemsitename)."', ".
1092 "$this->iitemsiteurl='".mysql_real_escape_string($itemsiteurl)."', ".
1093 "$this->iitemlicense='".mysql_real_escape_string($itemlicense)."' ".
1094 "WHERE $this->ifeedurl='".mysql_real_escape_string($url)."' " .
1095 "AND $this->iitemurl='".mysql_real_escape_string($link)."' ";
1096 $result = $wpdb->query($sql);
1097
1098 if(class_exists('BDPRSS_SEARCH')){
1099 $result = $bdprss_search->markitem4update($hid->id, $md5tocompare);
1100 if(!$result) $this->recordError($url, "Updated ID: " . $hid->id . " result: " .$result. " md5debug: " . $md5debug);
1101 }
1102
1103 }
1104 $this->bdprss_prevent_dupe_updates[$hid->id]="Done";
1105 return FALSE;
1106 }
1107
1108 $dateStamp = date('Y-m-d', $ticks);
1109 //echo "<br />Title is: " . $title;
1110 $sql = "INSERT IGNORE INTO $this->itemtable ($this->ifeedurl, $this->iitemurl, $this->iitemname, ".
1111 "$this->iitemsitename, $this->iitemsiteurl, $this->iitemlicense, ".
1112 "$this->iitemtext, $this->iitemupdate, $this->iitemtime, $this->iitemdate)".
1113 " VALUES ('".mysql_real_escape_string($url)."',
1114 '".mysql_real_escape_string($link)."',
1115 '".mysql_real_escape_string($title)."',
1116 '".mysql_real_escape_string($itemsitename)."',
1117 '".mysql_real_escape_string($itemsiteurl)."',
1118 '".mysql_real_escape_string($itemlicense)."', ".
1119 "'".mysql_real_escape_string($text)."', '$this->now', '$ticks', '$dateStamp') ";
1120 $result = $wpdb->query($sql);
1121
1122//updating the memtable
1123 $inserted_row_id=mysql_insert_id();
1124 if ($inserted_row_id>0){
1125//mem table preop - shall be replaced later by parameter
1126 $sql = "SELECT $this->midentifier FROM $this->msitetable ".
1127 "WHERE $this->mfeedurl='$url'";
1128 $site_id = $wpdb->get_var($sql);
1129
1130//mem table update
1131 $sql = "INSERT INTO $this->mitemtable
1132 ($this->miid, $this->misiteid, $this->miitemtime, $this->miitemdate)".
1133 " VALUES ('$inserted_row_id', '$site_id', '$ticks', '$dateStamp') ";
1134 $result = $wpdb->query($sql);
1135
1136 } //inserted row id > 0
1137 $this->bdprss_prevent_dupe_updates[$inserted_row_id]="Done";
1138 return TRUE;
1139 }
1140
1141 /* --- modify --- */
1142
1143 function updateTable($tableName, &$valueArray, $identifier, $specialCase=FALSE)
1144 {
1145 global $wpdb;
1146
1147 if(!isset($valueArray[$identifier]) || !$valueArray[$identifier])
1148 {
1149 $this->recordError('SNARK',
1150 "BDPRSS_DB::updateTable() missing identifier ($identifier)".
1151 "in valueArray for $tableName -- this should never happen");
1152 return FALSE;
1153 }
1154
1155 if($specialCase && $tableName==$this->sitetable)
1156 {
1157 if(isset($valueArray['csitename'])) unset($valueArray['csitename']);
1158 if(isset($valueArray['csiteurl'])) unset($valueArray['csiteurl']);
1159 if(isset($valueArray['cdescription'])) unset($valueArray['cdescription']);
1160/*new*/ if(isset($valueArray['csitelicense'])) unset($valueArray['csitelicense']);
1161 }
1162
1163 $sql = "UPDATE $tableName SET ";
1164 foreach($valueArray as $key => $value)
1165 {
1166 if($key==$identifier) continue;
1167 $value = preg_replace('/"/', '&quot;', $value);
1168 $value = preg_replace("/'/", '&#39;', $value);
1169 $sql .= $this->{$key}."='$value', ";
1170 }
1171 //$sql = preg_replace('/^(.*), $/', '\\1', $sql);
1172 $sql = preg_replace('/, $/', '', $sql);
1173 $sql .= " WHERE ".$this->{$identifier}."='".$valueArray[$identifier]."' ";
1174 $result = $wpdb->query($sql);
1175 return $result;
1176 }
1177
1178 function process_new(){
1179 //echo "we hit process_new";
1180 global $bdprss_search;
1181 $pba_search_config['addheap']=500;
1182 $pba_search_config['heapmode']='bulk';
1183 $pba_search_config['bdprss_getidmode']='notinstatus';
1184 $pba_search_config['bdprss_debug_list_id']=0;
1185 $pba_search_config['get_ids4heap2add_max_item_updatetimeage']=100000000;
1186 $pba_search_config['get_ids4heap2add_min_updatetimeage']=0;
1187
1188 //syntax for calling the search index processing:
1189 // $bdprss_search->add_heap2search_index($pba_search_config['addheap'], $pba_search_config['heapmode'], false, $insertfromtemptable,
1190 // $bdprss_getidmode, $bdprss_debug_list_id,
1191 // $get_ids4heap2add_max_item_updatetimeage, $get_ids4heap2add_min_updatetimeage);
1192
1193 $return = $bdprss_search->add_heap2search_index($pba_search_config['addheap'], $pba_search_config['heapmode'], false, true,
1194 $pba_search_config['bdprss_getidmode'], $pba_search_config['bdprss_debug_list_id'],
1195 $pba_search_config['get_ids4heap2add_max_item_updatetimeage'],
1196 $pba_search_config['get_ids4heap2add_min_updatetimeage']);
1197
1198 return $return;
1199 }
1200
1201 function process_updates(){
1202 //echo "we hit process_new";
1203 global $bdprss_search;
1204
1205 $pba_search_config['addheap']=20;
1206 $pba_search_config['heapmode']='bulk';
1207 $pba_search_config['bdprss_getidmode']='processupdates';
1208 $pba_search_config['bdprss_debug_list_id']=0;
1209 $pba_search_config['get_ids4heap2add_max_item_updatetimeage']=100000000;
1210 $pba_search_config['get_ids4heap2add_min_updatetimeage']=1800;
1211
1212 $return = $bdprss_search->add_heap2search_index($pba_search_config['addheap'], $pba_search_config['heapmode'], false, true,
1213 $pba_search_config['bdprss_getidmode'], $pba_search_config['bdprss_debug_list_id'],
1214 $pba_search_config['get_ids4heap2add_max_item_updatetimeage'],
1215 $pba_search_config['get_ids4heap2add_min_updatetimeage']);
1216 return $return;
1217 }
1218
1219 function process_deletes(){
1220 //echo "we hit process_new";
1221 global $bdprss_search;
1222
1223 $pba_search_config['addheap']=10;
1224 $pba_search_config['heapmode']='bulk';
1225 $pba_search_config['bdprss_getidmode']='processdeletes';
1226 $pba_search_config['bdprss_debug_list_id']=0;
1227 $pba_search_config['get_ids4heap2add_max_item_updatetimeage']=100000000;
1228 $pba_search_config['get_ids4heap2add_min_updatetimeage']=1800;
1229
1230 $return = $bdprss_search->add_heap2search_index($pba_search_config['addheap'], $pba_search_config['heapmode'], false, true,
1231 $pba_search_config['bdprss_getidmode'], $pba_search_config['bdprss_debug_list_id'],
1232 $pba_search_config['get_ids4heap2add_max_item_updatetimeage'],
1233 $pba_search_config['get_ids4heap2add_min_updatetimeage']);
1234 return $return;
1235 }
1236
1237
1238 function housekeeping_cache(){
1239 $dummy1="";
1240 $dummy2="";
1241 $cachedeletecounter=PBALIB::pba_cache($dummy1, $dummy2, 'housekeeping', '', 'mixed', 86400, 'OK');
1242 return $cachedeletecounter;
1243 }
1244
1245 function update_oldest()
1246 {
1247 global $wpdb;
1248 // at most we only want to impose the burden of updating one feed on this site user
1249
1250//mem tables present
1251 $sql ="SELECT * FROM $this->msitetable order by $this->mnextpolltime asc limit 1";
1252
1253//no mem tables present
1254 $sql ="SELECT * FROM $this->sitetable order by $this->cnextpolltime asc limit 1";
1255 $site = $wpdb->get_row($sql);
1256
1257// echo "DEBUG update oldest: the site is: ";
1258// print_r($site);
1259
1260
1261 if(!$site) return;
1262 if($site->{$this->mnextpolltime} > $this->now) return;
1263
1264 BDPRSS2::update($site);
1265 }
1266
1267 function updateAll()
1268 {
1269 global $wpdb;
1270
1271 $sql = "SELECT * FROM $this->sitetable order by next_poll_time LIMIT 5";
1272 $sites = $wpdb->get_results($sql);
1273
1274 if($sites)
1275 foreach($sites as $site)
1276 BDPRSS2::update($site);
1277 }
1278
1279
1280 /* --- retrieve --- */
1281
1282 function get_mysql_version()
1283 {
1284 global $wpdb;
1285
1286 $sql = "SELECT version()";
1287 $result = $wpdb->get_var($sql);
1288
1289 return $result;
1290 }
1291
1292 function is_in_sitetable($url)
1293 {
1294 global $wpdb;
1295
1296 $sql = "SELECT * FROM $this->sitetable ".
1297 "WHERE $this->cfeedurl='$url' LIMIT 1";
1298 $result = $wpdb->get_row($sql);
1299
1300 if($result && $result->{$this->cfeedurl} == $url) return TRUE;
1301 return FALSE;
1302 }
1303
1304 function count_in_sitetable()
1305 {
1306 global $wpdb;
1307
1308 $sql = "SELECT COUNT(*) FROM $this->sitetable ";
1309
1310 $result = $wpdb->get_var($sql);
1311
1312 return $result;
1313 }
1314
1315 function countErrors($url='')
1316 {
1317 global $wpdb;
1318
1319 $sql = "SELECT COUNT(*) FROM $this->errortable";
1320 if($url) $sql .= " WHERE $this->efeedurl='$url'";
1321 $result = $wpdb->get_var($sql);
1322
1323 return $result;
1324 }
1325
1326 function getErrors($url='')
1327 {
1328 global $wpdb;
1329
1330 $sql = "SELECT * FROM $this->errortable";
1331 if($url) $sql .= " WHERE $this->efeedurl='$url'";
1332 $sql .= " ORDER BY $this->efeedurl, $this->eidentifier";
1333 $result = $wpdb->get_results($sql);
1334
1335 return $result;
1336 }
1337
1338 function count_in_listtable()
1339 {
1340 global $wpdb;
1341
1342 $sql = "SELECT COUNT(*) FROM $this->listtable ";
1343
1344 $result = $wpdb->get_var($sql);
1345
1346 return $result;
1347 }
1348
1349 function is_in_itemtable($url)
1350 {
1351 global $wpdb;
1352
1353 $sql = "SELECT * FROM $this->itemtable ".
1354 "WHERE $this->ifeedurl='$url' LIMIT 1";
1355 $result = $wpdb->get_row($sql);
1356
1357 if($result && $result->{$this->ifeedurl} == $url) return TRUE;
1358 return FALSE;
1359 }
1360
1361 function get_all_lists()
1362 {
1363 global $wpdb;
1364 $sql = "SELECT * FROM $this->listtable ".
1365 "ORDER BY $this->lidentifier ";
1366 $result = $wpdb->get_results($sql);
1367 return $result;
1368 }
1369
1370 function setoptions($optionsarray){
1371 global $wpdb;
1372 $sql = "REPLACE INTO $this->optionstable ( name , type , value , notice
1373 ) VALUES ";
1374 foreach($optionsarray as $fieldname => $values){
1375 $sql .= "\n( '".$fieldname."', '".$values['type']."', '".$values['value']."', '".$values['notice']."'), ";
1376 }
1377 if(count($optionsarray)>0){
1378 $sql = preg_replace('/, $/','', $sql);
1379 $result = $wpdb->query($sql);
1380 }
1381 return $result;
1382 }
1383
1384 function get_all_options()
1385 {
1386 global $wpdb;
1387 $sql = "SELECT * FROM $this->optionstable ".
1388 "ORDER BY name ";
1389 $result = $wpdb->get_results($sql);
1390 return $result;
1391 }
1392
1393 function detect_memtable($table){
1394 global $wpdb;
1395 $sql = "SHOW CREATE TABLE " . $table;
1396 $show = $wpdb->get_row($sql);
1397 if(stristr($show->{'Create Table'}, 'ENGINE=MEMORY')) return true;
1398 return false;
1399 }
1400
1401 function pbaoption($name){
1402 global $wpdb;
1403 $sql = "SELECT * FROM $this->optionstable ".
1404 "WHERE name = '".$name."' LIMIT 0,1";
1405 $result = $wpdb->get_row($sql);
1406 if($result){
1407 if($result->type == 'string') {
1408 //special string handling needed to be binary safe?
1409 return $result->value;
1410 } elseif($result->type == 'int'){
1411 return abs(intval($result->value));
1412 }
1413 }
1414 return $result;
1415 }
1416
1417 function get_all_pbaoutputs()
1418 {
1419 global $wpdb;
1420 $sql = "SELECT * FROM $this->pbaoutputtable ".
1421 "ORDER BY $this->pbaoidentifier ";
1422 $result = $wpdb->get_results($sql);
1423 return $result;
1424 }
1425
1426 function get_all_sites($ltype='sitealpha')
1427 {
1428 global $wpdb;
1429 $sql = "SELECT * FROM $this->sitetable ";
1430
1431 if($ltype == 'sitealpha')
1432 $sql .= "ORDER BY $this->csitename ";
1433 elseif($ltype == 'siteupdate')
1434 $sql .= "ORDER BY $this->cupdatetime DESC";
1435
1436 $result = $wpdb->get_results($sql);
1437 return $result;
1438 }
1439
1440 function get_site($url)
1441 {
1442 global $wpdb;
1443 $sql = "SELECT * FROM $this->sitetable WHERE $this->cfeedurl='$url' ";
1444 $result = $wpdb->get_row($sql);
1445 return $result;
1446 }
1447
1448 function get_site_by_id($id)
1449 {
1450 global $wpdb;
1451 $sql = "SELECT * FROM $this->sitetable WHERE $this->cidentifier='$id' ";
1452 $result = $wpdb->get_row($sql);
1453 return $result;
1454 }
1455
1456//function will give back a numeric array of feeds when given in a numeric array of ids
1457 function get_feedlist_by_ids($ids)
1458 {
1459 global $wpdb;
1460// echo "Overload: ";
1461// print_r($ids);
1462 $return=false;
1463 if($ids){
1464 if($this->memtablesok==1){
1465 $use_site_table=$this->msitetable;
1466 $use_identifier=$this->midentifier;
1467 $use_feed_url_column_name=$this->mfeedurl;
1468 }else{
1469 $use_site_table=$this->sitetable;
1470 $use_identifier=$this->cidentifier;
1471 $use_feed_url_column_name=$this->cfeedurl;
1472 }
1473 $sql = "SELECT $use_feed_url_column_name as feedurl
1474 FROM $use_site_table WHERE $use_identifier in (";
1475 $virgin = true;
1476 foreach($ids as $id) {
1477 if($virgin){
1478 $sql .= " $id";
1479 }else{
1480 $sql .= ", $id";
1481 }
1482 $virgin = false;
1483 }
1484 $sql .= " ) ";
1485// print_r($sql);
1486 $result = $wpdb->get_results($sql);
1487 foreach($result as $r) {
1488 $return[]=$r->feedurl;
1489 }
1490 }//if ids
1491 return $return;
1492 }
1493
1494 function get_feedurl_from_site_id($id)
1495 {
1496 global $wpdb;
1497 $sql = "SELECT $this->cfeedurl FROM $this->sitetable WHERE $this->cidentifier='$id' ";
1498 $result = $wpdb->get_row($sql);
1499 if(!$result) return FALSE;
1500 return $result->{$this->cfeedurl};
1501 }
1502
1503 function get_siteurl_from_site_id($id)
1504 {
1505 global $wpdb;
1506 $sql = "SELECT $this->csiteurl FROM $this->sitetable WHERE $this->cidentifier='$id' ";
1507 $result = $wpdb->get_row($sql);
1508 if(!$result) return FALSE;
1509 return $result->{$this->csiteurl};
1510 }
1511
1512 function get_list($list_id)
1513 {
1514 global $wpdb;
1515 $sql = "SELECT * FROM $this->listtable WHERE $this->lidentifier='$list_id' ";
1516 $result = $wpdb->get_row($sql);
1517 return $result;
1518 }
1519
1520 function get_pbaoutput_from_page_id($page_id){
1521 global $wpdb;
1522 $sql = "SELECT * FROM $this->pbaoutputtable WHERE page2hookin='$page_id' ORDER by identifier ASC LIMIT 0,1";
1523 $result = $wpdb->get_row($sql);
1524 return $result;
1525 }
1526
1527 function get_pbaoutput($output_id)
1528 {
1529 global $wpdb;
1530 $sql = "SELECT * FROM $this->pbaoutputtable WHERE identifier='$output_id' ";
1531 $result = $wpdb->get_row($sql);
1532 return $result;
1533 }
1534
1535 function get_item($feedurl, $itemurl)
1536 {
1537 global $wpdb;
1538 $sql = "SELECT * FROM $this->itemtable ".
1539 "WHERE $this->ifeedurl='$feedurl' AND $this->iitemurl='$itemurl' ";
1540 $result = $wpdb->get_row($sql);
1541 return $result;
1542 }
1543
1544 function getItemByID($id)
1545 {
1546 global $wpdb;
1547 $sql = "SELECT * FROM $this->itemtable WHERE $this->iidentifier='$id' ";
1548 $result = $wpdb->get_row($sql);
1549 return $result;
1550 }
1551
1552
1553 function getsiteswithupdatetime($maxage=0, $list_id=0){
1554
1555 global $wpdb;
1556
1557 //check input values
1558 $list_id=abs(intval($list_id));
1559 $maxage=abs(intval($maxage)); //0 means filter disabled, age in seconds
1560
1561 //initialisation
1562 $search_query_conditions_list=" ";
1563 $search_query_conditions_maxage=" ";
1564
1565 if($list_id>0) $search_query_conditions_list =" AND r1.site_id in (select sites.identifier AS site_id from (" . $this->listtable . " lists join " . $this->sitetable . " sites) where ((concat(_latin1',',lists.url_list,_latin1',') like concat(_utf8'%,',sites.identifier,_utf8',%')) or (lists.list_all = _latin1'Y')) and lists.identifier = '" . $list_id . "') \n";
1566 if($maxage>0) $search_query_conditions_maxage =" AND r1.item_time > ( UNIX_TIMESTAMP() - '" . $maxage . "' ) ";
1567
1568 $sql = "SELECT r1.site_id, max( r1.item_time ) AS lastupdate, msi.*
1569 FROM " . $this->mitemtable . " r1, " . $this->msitetable . " msi
1570 WHERE r1.site_id = msi.identifier
1571 " . $search_query_conditions_list . "
1572 " . $search_query_conditions_maxage . "
1573 GROUP BY r1.site_id
1574 ORDER BY msi.site_name";
1575
1576 $result = $wpdb->get_results($sql);
1577 return $result;
1578 }
1579
1580
1581 function getmonthlyarchivedates($itemdate="", $list_id=0){
1582 global $wpdb;
1583
1584 //protect input vaues
1585 $list_id=abs(intval($list_id));
1586 if (!ereg("[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]", $itemdate)) $itemdate = date('Y-m-d');
1587
1588 /* let's get all date results for the month according to itemdate
1589 + the last hitting date before the month according to itemdate
1590 + the next hitting date after the month according to itemdate
1591 */
1592
1593 $firstsecondthismonth=mktime(0, 0, 0, substr($itemdate,5,2), 1, substr($itemdate,0,4));
1594 $firstsecondnextmonth=mktime(0, 0, 0, ( substr($itemdate,5,2) +1 ), 1, substr($itemdate,0,4));
1595 if($list_id>0) $search_query_conditions_list="AND r1.site_id in (select sites.identifier AS site_id from (" . $this->listtable . " lists join " . $this->sitetable . " sites) where ((concat(_latin1',',lists.url_list,_latin1',') like concat(_utf8'%,',sites.identifier,_utf8',%')) or (lists.list_all = _latin1'Y')) and lists.identifier = '" . $list_id . "') \n";
1596
1597 //let's try to build a quicker query - is it really quicker?
1598 if($list_id>0) {
1599 $search_table_addition_list=" , (select sites.identifier AS site_id from (" . $this->listtable . " lists join " . $this->msitetable . " sites) where ((concat(_latin1',',lists.url_list,_latin1',') like concat(_utf8'%,',sites.identifier,_utf8',%')) or (lists.list_all = _latin1'Y')) and lists.identifier = '" . $list_id . "') s \n";
1600 $search_query_conditions_list="AND r1.site_id = s.site_id \n";
1601 }
1602
1603/* //leave quicksql as comment, maybe it will be needed later, because for timezone conversions it is fine to use item_time instead of item_date
1604 $quicksql = "(SELECT IFNULL( FROM_UNIXTIME( min( r1.item_time ) , '%Y-%m-%d' ),'0') AS item_date, 'next' AS type
1605 FROM " . $this->mitemtable . " r1 " . $search_table_addition_list . "
1606 WHERE r1.item_time >= '".$firstsecondnextmonth."' " . $search_query_conditions_list . " )
1607 UNION DISTINCT
1608 (SELECT DISTINCT FROM_UNIXTIME( r1.item_time, '%Y-%m-%d' ) AS item_date, 'normal' AS type
1609 FROM " . $this->mitemtable . " r1 " . $search_table_addition_list . "
1610 WHERE r1.item_time >= '".$firstsecondthismonth."'
1611 AND r1.item_time < '".$firstsecondnextmonth."' " . $search_query_conditions_list . " )
1612 UNION DISTINCT
1613 ( SELECT IFNULL( FROM_UNIXTIME( max( r1.item_time ) , '%Y-%m-%d' ),'0') AS item_date, 'last' AS type
1614 FROM " . $this->mitemtable . " r1 " . $search_table_addition_list . "
1615 WHERE r1.item_time < '".$firstsecondthismonth."' " . $search_query_conditions_list . ") ";
1616 $quicksql .= " ORDER BY type, item_date ASC LIMIT 0,40 ";
1617*/ //just as demo how to use item_time fields instead of item_date
1618
1619 $quickersql = "(SELECT IFNULL( FROM_UNIXTIME( min( r1.item_time ) , '%Y-%m-%d' ),'0') AS item_date, 'next' AS type
1620 FROM " . $this->mitemtable . " r1 " . $search_table_addition_list . "
1621 WHERE r1.item_time >= '".$firstsecondnextmonth."' " . $search_query_conditions_list . " )
1622 UNION DISTINCT
1623 (SELECT DISTINCT date_format( r1.item_date, '%Y-%m-%d' ) AS item_date, 'normal' AS type
1624 FROM " . $this->mitemtable . " r1 " . $search_table_addition_list . "
1625 WHERE r1.item_date >= FROM_UNIXTIME('".$firstsecondthismonth."','%Y-%m-%d')
1626 AND r1.item_date < FROM_UNIXTIME('".$firstsecondnextmonth."','%Y-%m-%d') " . $search_query_conditions_list . "
1627 group by r1.item_time )
1628 UNION DISTINCT
1629 ( SELECT IFNULL( FROM_UNIXTIME( max( r1.item_time ) , '%Y-%m-%d' ),'0') AS item_date, 'last' AS type
1630 FROM " . $this->mitemtable . " r1 " . $search_table_addition_list . "
1631 WHERE r1.item_time < '".$firstsecondthismonth."' " . $search_query_conditions_list . " ) ";
1632 $quickersql .= " ORDER BY type, item_date ASC LIMIT 0,40 ";
1633
1634
1635 //echo $quickersql . '<br />';
1636 $result = $wpdb->get_results($quickersql);
1637 return $result;
1638
1639 }
1640
1641
1642 function get_most_recent_item_time($feedurl)
1643 {
1644 global $wpdb;
1645
1646 if($this->memtablesok == 1){
1647 //if feedurl is numeric, we treat it as a site_id
1648 if(abs(intval($feedurl))."" == $feedurl.""){
1649 $sql = "SELECT MAX( $this->miitemtime ) as $this->iitemtime
1650 FROM $this->mitemtable
1651 WHERE $this->misiteid = '$feedurl'";
1652 }else{
1653 $sql = "SELECT MAX( $this->miitemtime ) as $this->iitemtime
1654 FROM $this->mitemtable
1655 WHERE $this->misiteid = (
1656 SELECT $this->midentifier
1657 FROM $this->msitetable
1658 WHERE $this->mfeedurl = '$feedurl' ) ";
1659 }
1660 }else{
1661 $sql = "SELECT MAX($this->iitemtime) FROM $this->itemtable ".
1662 "WHERE $this->ifeedurl='$feedurl' ";
1663 }
1664 $result = $wpdb->get_var($sql);
1665 return $result;
1666 }
1667
1668 //new function to query items for output
1669 //input: object with ids - just to be overloaded, never to be changed, optional parameter order by site name
1670 //output: object with set of rows
1671 function getsitenitems(&$id_result, $orderbysitename=false){
1672 global $wpdb;
1673 if(!$id_result){
1674 return false;
1675 }
1676 $sql = "SELECT i.identifier as itemid, s.identifier as siteid, i.*, s.* FROM $this->itemtable i, $this->msitetable s ";
1677 $sql .= " WHERE i." . $this->ifeedurl . " = s." . $this->mfeedurl . " ";
1678 $virgin = true;
1679 foreach($id_result as $tr) {
1680 if(!$tr->{$this->miid}) continue;
1681 if($virgin)
1682 $sql .= "AND ( ";
1683 else
1684 $sql .= "OR";
1685 $sql .= " i." . $this->iidentifier . "='" . $tr->{$this->miid} . "' ";
1686 $virgin = false;
1687 }
1688 if(!$virgin) $sql .= ") ";
1689 if($orderbysitename){
1690 $sql .= " ORDER BY s." . $this->msitename . " ASC ";
1691 }else{
1692 $sql .= " ORDER BY i." . $this->iitemtime . " DESC ";
1693 }
1694
1695 $itemset = $wpdb->get_results($sql);
1696 return $itemset;
1697 }
1698
1699 /* --- delete --- */
1700
1701 function deleteFeed($rss)
1702 {
1703 global $wpdb, $bdprss_search;
1704
1705 $rss=trim($rss);
1706 $url = $this->get_feedurl_from_site_id($rss);
1707
1708 if(!$url) return;
1709
1710 $sql = "DELETE FROM $this->sitetable WHERE $this->cfeedurl='$url'";
1711 $result = $wpdb->query($sql);
1712
1713 $sql = "DELETE FROM $this->itemtable WHERE $this->ifeedurl='$url'";
1714 $result = $wpdb->query($sql);
1715
1716 $sql = "DELETE FROM $this->errortable WHERE $this->efeedurl='$url'";
1717 $result = $wpdb->query($sql);
1718
1719//delete from search tables
1720 if(class_exists('BDPRSS_SEARCH')){
1721 $result = $bdprss_search->markfeed4delete($rss);
1722 $this->recordError("Attention", "Deleted Feed ID: " . $rss);
1723 }
1724
1725//mem tables
1726 $sql = "DELETE FROM $this->msitetable WHERE $this->midentifier='$rss'";
1727 $result = $wpdb->query($sql);
1728
1729 $sql = "DELETE FROM $this->mitemtable WHERE $this->misiteid='$rss'";
1730 $result = $wpdb->query($sql);
1731
1732 }
1733
1734 function deletelist($list)
1735 {
1736 global $wpdb;
1737
1738 $sql = "DELETE FROM $this->listtable WHERE $this->lidentifier='$list'";
1739 $result = $wpdb->query($sql);
1740 }
1741
1742 function deletepbaoutput($list)
1743 {
1744 global $wpdb;
1745
1746 $sql = "DELETE FROM $this->pbaoutputtable WHERE $this->pbaoidentifier='$list'";
1747 $result = $wpdb->query($sql);
1748 }
1749
1750 function deleteErrors($url)
1751 {
1752 global $wpdb;
1753
1754 $sql = "DELETE FROM $this->errortable ".
1755 "WHERE $this->efeedurl='$url' ";
1756 $result = $wpdb->query($sql);
1757
1758 return $result;
1759 }
1760
1761 function droptable($tablename)
1762 {
1763 global $wpdb;
1764
1765 $sql = "DROP TABLE IF EXISTS $tablename";
1766 $result = $wpdb->query($sql);
1767
1768 return $result;
1769 }
1770
1771 function deleteErrorTable()
1772 {
1773 global $wpdb;
1774
1775 $sql = "DROP TABLE IF EXISTS $this->errortable";
1776 $result = $wpdb->query($sql);
1777
1778 $this->create();
1779 }
1780
1781 function delete_old_items($url)
1782 {
1783 /* delete_old_items($url)
1784 */
1785 global $wpdb, $bdprss_search;
1786
1787 $oldDefined = (int) get_option('bdprss_keep_howlong');
1788 if(!$oldDefined) return;
1789 $oldDefined *= 60 * 60 * 24 * 31; // seconds in a month
1790 $oldDefined = $this->now - $oldDefined;
1791
1792 $sql = "DELETE FROM $this->itemtable ".
1793 "WHERE $this->ifeedurl='$url' ".
1794 "AND ($this->iitemtime<'$oldDefined' OR $this->iitemtime='')";
1795 $result = $wpdb->query($sql);
1796
1797//delete from search tables
1798 if(class_exists('BDPRSS_SEARCH')){
1799 $result=$this->get_site($url);
1800 $result = $bdprss_search->markfeed4delete($result->{$this->cidentifier}, $oldDefined);
1801 $this->recordError($url, "Deleted Entries older as $oldDefined from this feed");
1802 }
1803
1804//mem table
1805 $sql = "DELETE FROM $this->mitemtable ".
1806 "WHERE $this->misiteid in (
1807 SELECT $this->midentifier FROM $this->msitetable
1808 where $this->mfeedurl ='$url') ".
1809 "AND ($this->miitemtime<'$oldDefined' OR $this->miitemtime='')";
1810 $result = $wpdb->query($sql);
1811
1812 return $result;
1813 }
1814
1815 function list_all_tables(){
1816 global $bdprss_search;
1817 $tables=$bdprss_search->pbasearch_list_tables();
1818 $tablenames = array(
1819 $this->errortable, $this->listtable, $this->itemtable, $this->sitetable,
1820 $this->mtablestatus, $this->msitetable, $this->mitemtable, $this->optionstable, $this->pbaoutputtable
1821 );
1822 foreach($tablenames as $tablename){
1823 $tables[$tablename]=true;
1824 }
1825 return $tables;
1826 }
1827
1828 function reset()
1829 {
1830 /* reset() -- delete the database tables -
1831 * god knows why you would do this --
1832 * this is the button of death
1833 */
1834 global $wpdb;
1835
1836 $tablenames = $this->list_all_tables();
1837 //print_r($tablenames);
1838 $dummy1="";
1839 $dummy2="";
1840 $cachedeletecounter=@PBALIB::pba_cache($dummy1, $dummy2, 'clear', '', 'mixed', 180, 'OK');
1841//echo "<br>Uninstall outcommented in bdp-rssaggregator-db.php";
1842//exit;
1843
1844 delete_option('bdprss_update_frequency');
1845 delete_option('bdprss_keep_howlong');
1846 delete_option('widget_name_multi');
1847 foreach($tablenames as $t => $dummy)
1848 {
1849 $sql = "DROP TABLE IF EXISTS $t ";
1850 $result = $wpdb->query($sql);
1851 }
1852 return true;
1853 }
1854
1855 function get_wp_published_pages(){
1856 global $wpdb, $table_prefix;
1857 $sql = "SELECT * FROM " . $table_prefix . "posts
1858 WHERE post_type = 'page' AND post_status = 'publish'
1859 ORDER BY ID";
1860 $result = $wpdb->get_results($sql);
1861// return $sql;
1862 return $result;
1863 }
1864
1865 } // class
1866}//if
1867
1868// Make a single global instance.
1869if ( !isset($bdprss_db) ) $bdprss_db = new BDPRSS_DB();
1870
1871?>
Note: See TracBrowser for help on using the repository browser.