From 2af4ffce19df7cdb34d4b509a66bc916ffcb88d1 Mon Sep 17 00:00:00 2001
From: Camil Staps
Date: Mon, 3 Feb 2020 09:47:21 +0100
Subject: Initial commit
---
.gitignore | 1 +
README.md | 14 ++++
add.php | 49 ++++++++++++++
config.sample.php | 14 ++++
db.php | 151 +++++++++++++++++++++++++++++++++++++++++
fetch.php | 96 ++++++++++++++++++++++++++
ical.php | 67 +++++++++++++++++++
install.sql | 81 ++++++++++++++++++++++
list.php | 91 +++++++++++++++++++++++++
mail.php | 196 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
nginx.conf | 49 ++++++++++++++
style.css | 33 +++++++++
12 files changed, 842 insertions(+)
create mode 100644 .gitignore
create mode 100644 README.md
create mode 100644 add.php
create mode 100644 config.sample.php
create mode 100644 db.php
create mode 100644 fetch.php
create mode 100644 ical.php
create mode 100644 install.sql
create mode 100644 list.php
create mode 100644 mail.php
create mode 100644 nginx.conf
create mode 100644 style.css
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..4f4773f
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1 @@
+config.php
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..e0a1d3e
--- /dev/null
+++ b/README.md
@@ -0,0 +1,14 @@
+# Agenda
+
+This is the source code for [agenda.hebrewtools.org][], which collects
+information about conferences, workshops, and other events relevant to scholars
+of the near east.
+
+I currently collect calls for papers and other notices of events from the Agade
+mailing list. The events can be filtered by keywords, and such a filtered event
+list can be exported to calendar applications like Google Calendar or Outlook
+in iCal format.
+
+This software is copyright © 2020–present Camil Staps.
+
+[agenda.hebrewtools.org]: https://agenda.hebrewtools.org
diff --git a/add.php b/add.php
new file mode 100644
index 0000000..e538d2e
--- /dev/null
+++ b/add.php
@@ -0,0 +1,49 @@
+
+
+
+ Adding new events
+
+
+
+ $msg['guessed_source']]);
+ $info=$parsed['info'];
+
+ $info['added_by']=$msg['user']['id'];
+ $info['keywords']=array_map ('get_or_add_keyword',$info['keywords']);
+ $info['source']=get_or_add_source ($info['source']);
+
+ $id=add_event ($info);
+ echo 'Added #'.$id.': '.$info['title'].'
';
+
+ mail_mark_seen ($msg_id);
+ } catch (Exception $e){
+ $exceptions[]=$e->getMessage();
+ }
+}
+
+if (count ($exceptions)>0){
+ echo 'Exceptions:
';
+ foreach ($exceptions as $e)
+ echo $e.'
';
+}
+?>
+Back to the list of new events?
+
+
diff --git a/config.sample.php b/config.sample.php
new file mode 100644
index 0000000..556a306
--- /dev/null
+++ b/config.sample.php
@@ -0,0 +1,14 @@
+prepare ('insert into `events` (`title`,`location`,`start_date`,`end_date`,`source`,`added_by`,`description`) values (?,?,?,?,?,?,?)');
+ $ok=$st->execute ([
+ $info['title'],
+ $info['location'],
+ $info['start_date'],
+ $info['end_date'],
+ $info['source'],
+ $info['added_by'],
+ $info['description']
+ ]);
+
+ if (!$ok){
+ $err=$st->errorInfo();
+ throw new Exception ('Error ('.$err[0].', '.$err[1].'): '.$err[2]);
+ }
+
+ $id=$pdo->lastInsertId();
+
+ $st=$pdo->prepare ('insert into `event_keyword` (`event`,`keyword`) values (?,?)');
+ foreach ($info['keywords'] as $keyword_id)
+ $st->execute ([$id,$keyword_id]);
+
+ return $id;
+}
+
+function get_events ($keywords=[])
+{
+ global $pdo;
+
+ if (count ($keywords) > 0){
+ $keywords=$pdo->query (
+ 'select `id` from `keywords` ' .
+ 'where `keyword` in ('.implode (',',array_map ([$pdo,'quote'],$keywords)).')'
+ );
+ $keywords=array_map ('array_shift',$keywords->fetchAll (PDO::FETCH_NUM));
+ }
+
+ $sql='select `events`.*,`sources`.`source` as `source_name` ' .
+ 'from `events` inner join `sources` on `events`.`source`=`sources`.`id` ';
+ if (count ($keywords) > 0){
+ $sql.='where exists (select * from `event_keyword` ' .
+ 'where `event`=`events`.`id` and `keyword` in ('.implode (',',$keywords).')) ';
+ }
+ $sql.='order by `start_date` asc,`end_date` asc,`title` asc';
+
+ $st=$pdo->prepare ($sql);
+ $st->execute();
+ $events=$st->fetchAll (PDO::FETCH_ASSOC);
+
+ $get_keywords_st=$pdo->prepare (
+ 'select `keywords`.`keyword` ' .
+ 'from `event_keyword` inner join `keywords` on `keywords`.`id`=`event_keyword`.`keyword` ' .
+ 'where `event_keyword`.`event`=? ' .
+ 'order by `keywords`.`keyword` asc'
+ );
+ foreach ($events as $i => $event){
+ $get_keywords_st->execute ([$event['id']]);
+ $keywords=$get_keywords_st->fetchAll (PDO::FETCH_NUM);
+ $events[$i]['keywords']=array_map ('array_shift',$keywords);
+ }
+
+ return $events;
+}
+
+function get_keyword ($keyword)
+{
+ global $pdo;
+ $st=$pdo->prepare ('select `id` from `keywords` where `keyword`=?');
+ $st->execute ([$keyword]);
+
+ if ($st->rowCount() > 0){
+ $row=$st->fetch();
+ return $row['id'];
+ }
+
+ return null;
+}
+
+function get_keywords ()
+{
+ global $pdo;
+ $keywords=$pdo->query (
+ 'select `keywords`.`keyword`, count(`event_keyword`.`event`) as `count` ' .
+ 'from `event_keyword` inner join `keywords` on `event_keyword`.`keyword`=`keywords`.`id` ' .
+ 'group by `event_keyword`.`keyword` ' .
+ 'order by `keywords`.`keyword` asc'
+ );
+ return $keywords->fetchAll (PDO::FETCH_ASSOC);
+}
+
+function get_or_add_keyword ($keyword)
+{
+ global $pdo;
+ $id=get_keyword ($keyword);
+
+ if (is_null ($id)){
+ $st=$pdo->prepare ('insert into `keywords` (`keyword`) values (?)');
+ $st->execute ([$keyword]);
+ $id=$pdo->lastInsertId();
+ }
+
+ return $id;
+}
+
+function get_source ($source)
+{
+ global $pdo;
+ $st=$pdo->prepare ('select `id` from `sources` where `source`=?');
+ $st->execute ([$source]);
+
+ if ($st->rowCount() > 0){
+ $row=$st->fetch();
+ return $row['id'];
+ }
+
+ return null;
+}
+
+function get_or_add_source ($source)
+{
+ global $pdo;
+ $id=get_source ($source);
+
+ if (is_null ($id)){
+ $st=$pdo->prepare ('insert into `sources` (`source`) values (?)');
+ $st->execute ([$source]);
+ $id=$pdo->lastInsertId();
+ }
+
+ return $id;
+}
+
+function get_user_by_email ($email)
+{
+ global $pdo;
+ $st=$pdo->prepare ('select * from `users` where `email`=?');
+ $st->execute ([$email]);
+
+ if ($st->rowCount() > 0)
+ return $st->fetch (PDO::FETCH_ASSOC);
+ else
+ throw new Exception ('no user with email '.$email);
+}
diff --git a/fetch.php b/fetch.php
new file mode 100644
index 0000000..1adccc6
--- /dev/null
+++ b/fetch.php
@@ -0,0 +1,96 @@
+
+
+
+ Fetching new events
+
+
+
+
+
+
diff --git a/ical.php b/ical.php
new file mode 100644
index 0000000..92c7eb8
--- /dev/null
+++ b/ical.php
@@ -0,0 +1,67 @@
+ 75){
+ $split_at=75 - strlen ($line);
+ $line.=substr ($val,0,$split_at)."\r\n";
+ $val=substr ($val,$split_at);
+ $val_parts=str_split ($val,74);
+ foreach ($val_parts as $val)
+ $line.=' '.$val."\r\n";
+ } else {
+ $line.=$val."\r\n";
+ }
+
+ return $line;
+}
+
+echo "BEGIN:VCALENDAR\r\n";
+echo icalline ('VERSION','2.0');
+echo icalline ('PRODID','-//HebrewTools//NONSGML '.ICAL_NAME.' '.ICAL_VERSION.'//EN');
+echo icalline ('METHOD','PUBLISH');
+echo icalline ('X-WR-CALNAME',ICAL_NAME);
+echo icalline ('X-WR-CALDESC',ICAL_DESC);
+echo icalline ('X-PUBLISHED-TTL','PT12H'); /* update every 12h */
+echo icalline ('URL','https://'.$_SERVER['HTTP_HOST']);
+
+$events=isset($_GET['keywords']) ? get_events (explode (',',$_GET['keywords'])) : get_events();
+foreach ($events as $event){
+ echo "BEGIN:VEVENT\r\n";
+ echo icalline ('UID','event-'.$event['id'].'@agenda.hebrewtools.org');
+ echo icalline ('SUMMARY',$event['title']);
+ echo icalline ('LOCATION',$event['location']);
+ echo icalline ('DTSTART',date ('Ymd',strtotime ($event['start_date'])));
+ if ($event['end_date']!=$event['start_date'])
+ echo icalline ('DTEND',date ('Ymd',strtotime ($event['end_date'])+24*3600));
+ $description=$event['description'];
+ $description.="\n\nKeywords: ".implode (', ',$event['keywords']);
+ $description.="\nSource: ".$event['source_name'];
+ echo icalline ("DESCRIPTION",$description);
+ echo "END:VEVENT\r\n";
+}
+
+echo "END:VCALENDAR\r\n";
diff --git a/install.sql b/install.sql
new file mode 100644
index 0000000..8eb299a
--- /dev/null
+++ b/install.sql
@@ -0,0 +1,81 @@
+CREATE TABLE `events` (
+ `id` int(11) NOT NULL,
+ `title` text NOT NULL,
+ `location` text NOT NULL,
+ `start_date` date NOT NULL,
+ `end_date` date NOT NULL,
+ `source` int(11) NOT NULL,
+ `added_by` int(11) NOT NULL,
+ `description` mediumtext NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `event_keyword` (
+ `event` int(11) NOT NULL,
+ `keyword` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `keywords` (
+ `id` int(11) NOT NULL,
+ `keyword` text NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `sources` (
+ `id` int(11) NOT NULL,
+ `source` text NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `users` (
+ `id` int(11) NOT NULL,
+ `name` mediumtext NOT NULL,
+ `email` varchar(255) NOT NULL,
+ `pgp_fingerprint` text NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+
+ALTER TABLE `events`
+ ADD PRIMARY KEY (`id`),
+ ADD UNIQUE KEY `title_2` (`title`(255),`start_date`),
+ ADD KEY `start_date` (`start_date`),
+ ADD KEY `end_date` (`end_date`),
+ ADD KEY `source` (`source`),
+ ADD KEY `added_by` (`added_by`);
+ALTER TABLE `events` ADD FULLTEXT KEY `location` (`location`);
+ALTER TABLE `events` ADD FULLTEXT KEY `title` (`title`);
+ALTER TABLE `events` ADD FULLTEXT KEY `description` (`description`);
+
+ALTER TABLE `event_keyword`
+ ADD UNIQUE KEY `event_2` (`event`,`keyword`),
+ ADD KEY `event` (`event`),
+ ADD KEY `keyword` (`keyword`);
+
+ALTER TABLE `keywords`
+ ADD PRIMARY KEY (`id`),
+ ADD UNIQUE KEY `keyword` (`keyword`(127)) USING BTREE;
+ALTER TABLE `keywords` ADD FULLTEXT KEY `keyword_2` (`keyword`);
+
+ALTER TABLE `sources`
+ ADD PRIMARY KEY (`id`);
+ALTER TABLE `sources` ADD FULLTEXT KEY `source` (`source`);
+
+ALTER TABLE `users`
+ ADD PRIMARY KEY (`id`),
+ ADD UNIQUE KEY `email` (`email`),
+ ADD UNIQUE KEY `name` (`name`(127));
+
+
+ALTER TABLE `events`
+ MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
+ALTER TABLE `keywords`
+ MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
+ALTER TABLE `sources`
+ MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
+ALTER TABLE `users`
+ MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
+
+ALTER TABLE `events`
+ ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`),
+ ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`source`) REFERENCES `sources` (`id`);
+
+ALTER TABLE `event_keyword`
+ ADD CONSTRAINT `event_keyword_ibfk_1` FOREIGN KEY (`event`) REFERENCES `events` (`id`),
+ ADD CONSTRAINT `event_keyword_ibfk_2` FOREIGN KEY (`keyword`) REFERENCES `keywords` (`id`);
diff --git a/list.php b/list.php
new file mode 100644
index 0000000..b942fa6
--- /dev/null
+++ b/list.php
@@ -0,0 +1,91 @@
+
+
+
+ Events
+
+
+
+HebrewTools Events List
+Select all / no filters.
+
+';
+ echo '';
+ echo ' '.$keyword.' ('.$record['count'].')
';
+}
+
+$ical_url='https://'.$_SERVER['HTTP_HOST'].'/ical.php';
+if (isset ($_GET['keywords']) && count ($_GET['keywords']) != count ($keywords))
+ $ical_url.='?keywords='.$_GET['keywords'];
+?>
+
+
+
+Export this calendar in iCal format.
+
+
+ | Date(s) |
+ Location |
+ Title |
+ Keywords |
+
+';
+ echo ''.date ('D j M Y',strtotime ($event['start_date']));
+ if ($event['end_date']!=$event['start_date'])
+ echo ' to '.date ('j M',strtotime ($event['end_date']));
+ echo ' | ';
+ echo ''.htmlspecialchars ($event['location']).' | ';
+ echo ''.htmlspecialchars ($event['title']).' | ';
+ foreach ($event['keywords'] as $i => $kw){
+ if (!is_null ($selected_keywords) && !in_array ($kw,$selected_keywords))
+ $event['keywords'][$i]=''.htmlspecialchars ($kw).'';
+ else
+ $event['keywords'][$i]=htmlspecialchars ($kw);
+ }
+ echo ''.implode (', ',$event['keywords']).' | ';
+ echo '';
+}
+?>
+
+
+
+
diff --git a/mail.php b/mail.php
new file mode 100644
index 0000000..6d2d425
--- /dev/null
+++ b/mail.php
@@ -0,0 +1,196 @@
+ 'CRAM_MD5'];
+$imap=imap_open (IMAP_CONN,IMAP_USER,IMAP_PASS,NULL,1,$imap_options);
+
+if (!$imap){
+ echo 'IMAP errors:
';
+ foreach (imap_errors() as $err)
+ echo $err.'
';
+ die();
+}
+
+function verify_signature ($data,$sig)
+{
+ $gnupg=gnupg_init();
+ $sig_details=gnupg_verify ($gnupg,$data,$sig);
+ if (!$sig_details)
+ throw new Exception ('signature verification failed');
+
+ $summary=$sig_details[0]['summary'];
+ if ($summary & 0x0004)
+ throw new Exception ('bad signature');
+ if ($summary & 0x0010)
+ throw new Exception ('key has been revoked');
+ if ($summary & 0x0020)
+ throw new Exception ('key has expired');
+ if ($summary & 0x0040)
+ throw new Exception ('signature has expired');
+ if ($summary & 0x0080)
+ throw new Exception ('can\'t verify: key missing');
+ if ($summary!=3)
+ throw new Exception ('unknown signature verification problem ('.$summary.')');
+
+ return $sig_details[0]['fingerprint'];
+}
+
+function mail_fetch_message ($msg)
+{
+ global $imap;
+
+ $guessed_source=null;
+
+ /* Fetch user */
+ $info=imap_headerinfo ($imap,$msg);
+ $from=$info->from[0]->mailbox.'@'.$info->from[0]->host;
+ $user=get_user_by_email ($from);
+
+ /* Guess source */
+ if (strpos (imap_utf8 ($info->subject),'[agade]')!==false)
+ $guessed_source='Agade';
+
+ /* Fetch the multipart tree */
+ $structure=imap_fetchstructure ($imap,$msg);
+ if ($structure->type!=TYPEMULTIPART)
+ throw new Exception ('not a multipart message');
+ if ($structure->subtype!='SIGNED')
+ throw new Exception ('not a signed message');
+
+ /* Find the body and signature */
+ $body_id=null;
+ $sig_id=null;
+ foreach ($structure->parts as $id => $part){
+ if ($part->type==TYPEAPPLICATION && $part->subtype=='PGP-SIGNATURE')
+ $sig_id=$id+1;
+ else
+ $body_id=$id+1;
+ }
+ if (is_null ($sig_id))
+ throw new Exception ('no signature attachment');
+ if (is_null ($body_id))
+ throw new Exception ('no message body');
+
+ /* Get contents */
+ $headers=imap_fetchmime ($imap,$msg,1,FT_INTERNAL | FT_PEEK);
+ $body=imap_fetchbody ($imap,$msg,$body_id,FT_INTERNAL | FT_PEEK);
+ $sig=imap_fetchbody ($imap,$msg,$sig_id,FT_PEEK);
+
+ /* Verify signature */
+ $fpr=verify_signature ($headers.$body,$sig);
+ if ($fpr!=$user['pgp_fingerprint'])
+ throw new Exception ('illegal key used for signature');
+
+ /* Fetch plain-text body */
+ $body_part=$structure->parts[$body_id-1];
+ $plain_body=null;
+ if ($body_part->type==TYPETEXT && $body_part->subtype=='PLAIN'){
+ $plain_body=$body;
+ } else if ($body_part->type==TYPEMULTIPART){
+ foreach ($body_part->parts as $id => $part)
+ if ($part->type==TYPETEXT && $part->subtype=='PLAIN')
+ $plain_body=imap_fetchbody ($imap,$msg,$body_id.'.'.($id+1),FT_PEEK);
+ } else {
+ throw new Exception ('unknown body part type '.$body_part->type);
+ }
+ if (is_null ($plain_body))
+ throw new Exception ('no plain-text body');
+
+ $plain_body=imap_qprint ($plain_body);
+
+ return [
+ 'user' => $user,
+ 'body' => $plain_body,
+ 'guessed_source' => $guessed_source
+ ];
+}
+
+function mail_get_unseen_messages ()
+{
+ global $imap;
+
+ $unseen_msgs=imap_search ($imap,'UNSEEN');
+ if (!$unseen_msgs)
+ $unseen_msgs=[];
+
+ return $unseen_msgs;
+}
+
+function mail_mark_seen ($msg)
+{
+ global $imap;
+ imap_setflag_full ($imap,$msg,'\Seen');
+}
+
+function mail_finish ()
+{
+ global $imap;
+ imap_close ($imap);
+}
+
+function mail_parse_message ($body,$prefill_info=[])
+{
+ $info=[
+ 'title' => null,
+ 'location' => null,
+ 'start_date' => null,
+ 'end_date' => null,
+ 'keywords' => [],
+ 'source' => null,
+ 'description' => null
+ ];
+ $warnings=[];
+
+ foreach ($prefill_info as $key => $val)
+ $info[$key]=$val;
+
+ $lines=explode ("\n",$body);
+ $i=0;
+ foreach ($lines as $line){
+ $i++;
+ if (trim ($line)=='')
+ break;
+
+ $parts=explode (':',$line,2);
+ $key=trim ($parts[0]);
+ $val=trim ($parts[1]);
+
+ switch ($key){
+ case 'Title':
+ $info['title']=$val;
+ break;
+ case 'Location':
+ $info['location']=$val;
+ break;
+ case 'Source':
+ $info['source']=$val;
+ break;
+ case 'Dates':
+ $parts=explode ('to',$val);
+ if (count ($parts)==2){
+ $info['start_date']=trim ($parts[0]);
+ $info['end_date']=trim ($parts[1]);
+ } else {
+ $info['start_date']=$info['end_date']=$val;
+ }
+ break;
+ case 'Keywords':
+ $info['keywords']=array_map ('trim',explode (';',$val));
+ break;
+ default:
+ $warnings[]='Unknown key '.$key;
+ }
+ }
+
+ $info['description']=trim (implode ("\n",array_slice ($lines,$i)));
+
+ foreach (array_keys ($info) as $key)
+ if (is_null ($info[$key]))
+ $warnings[]='No '.$key.' found';
+
+ return [
+ 'info' => $info,
+ 'warnings' => $warnings
+ ];
+}
diff --git a/nginx.conf b/nginx.conf
new file mode 100644
index 0000000..ac87562
--- /dev/null
+++ b/nginx.conf
@@ -0,0 +1,49 @@
+server {
+ listen [::]:80;
+
+ server_name agenda.hebrewtools.org www.agenda.hebrewtools.org;
+
+ access_log /var/log/nginx/agenda.hebrewtools.org.access.log;
+ error_log /var/log/nginx/agenda.hebrewtools.org.error.log;
+
+ return 301 https://agenda.hebrewtools.org$request_uri;
+}
+
+server {
+ listen [::]:443;
+
+ root /var/www/agenda.hebrewtools.org;
+ index list.php;
+ charset utf-8;
+
+ server_name agenda.hebrewtools.org;
+
+ access_log /var/log/nginx/agenda.hebrewtools.org.access.log;
+ error_log /var/log/nginx/agenda.hebrewtools.org.error.log;
+
+ include /etc/nginx/confsnippets/ssl.conf;
+ ssl_certificate /etc/letsencrypt/live/agenda.hebrewtools.org/fullchain.pem;
+ ssl_certificate_key /etc/letsencrypt/live/agenda.hebrewtools.org/privkey.pem;
+
+ location / {
+ autoindex off;
+ }
+
+ location ~ (add|fetch)\.php {
+ auth_basic "Agenda administration is only available after login:";
+ auth_basic_user_file /etc/nginx/htpasswds/agenda.hebrewtools.org;
+ include /etc/nginx/confsnippets/fastcgi.conf;
+ }
+
+ location ~ \.php$ {
+ include /etc/nginx/confsnippets/fastcgi.conf;
+ }
+
+ include /etc/nginx/confsnippets/letsencrypt.conf;
+
+ location ~ /\. {
+ deny all;
+ }
+
+ include /etc/nginx/confsnippets/expires.conf;
+}
diff --git a/style.css b/style.css
new file mode 100644
index 0000000..0566182
--- /dev/null
+++ b/style.css
@@ -0,0 +1,33 @@
+body {
+ font-family: sans-serif;
+}
+
+a {
+ color: blue;
+}
+
+table {
+ border-collapse: collapse;
+}
+
+tr:first-child {
+ border-bottom: 1px solid black;
+}
+
+th, td {
+ padding-right: 1em;
+ text-align: left;
+ vertical-align: top;
+}
+
+td.description {
+ font-size: 85%;
+}
+
+#keyword-filters {
+ column-width: 20em;
+}
+
+.filtered-keyword {
+ color: gray;
+}
--
cgit v1.2.3