forked from Crell/joindinaudit
/
derive.php
90 lines (71 loc) · 2.61 KB
/
derive.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
<?php
require 'vendor/autoload.php';
use Doctrine\DBAL\Connection;
/**
* Computes derivative data from the raw information.
*/
function derive()
{
makeFirstAppearanceIndex();
computeSpeakersPerCon();
computeNewSpeakersPerCon();
}
function computeSpeakersPerCon()
{
$conn = getDb();
$conn->transactional(function(Connection $conn) {
$result = $conn->executeQuery("SELECT DISTINCT uri, name, start_date, talks_count
FROM event
ORDER BY start_date");
$stmt = $conn->prepare("SELECT COUNT(*) FROM (SELECT DISTINCT talk.speaker
FROM event
INNER JOIN talk ON event.uri = talk.event_uri
WHERE event.uri = :event) AS stuff");
foreach ($result as $event) {
$stmt->execute(['event' => $event['uri']]);
$count = $stmt->fetchColumn();
$conn->update('event', ['num_speakers' => $count], ['uri' => $event['uri']]);
}
});
}
function computeNewSpeakersPerCon()
{
$conn = getDb();
$conn->transactional(function(Connection $conn) {
$result = $conn->executeQuery("SELECT DISTINCT uri, name, start_date, talks_count
FROM event
ORDER BY start_date");
$stmt = $conn->prepare("SELECT COUNT(*) FROM (SELECT DISTINCT talk.speaker
FROM event
INNER JOIN talk ON event.uri = talk.event_uri
INNER JOIN first_appearance ON event.uri = first_appearance.event_uri
AND talk.speaker = first_appearance.speaker
WHERE event.uri = :event) AS stuff");
foreach ($result as $event) {
$stmt->execute(['event' => $event['uri']]);
$count = $stmt->fetchColumn();
$conn->update('event', ['new_speakers' => $count], ['uri' => $event['uri']]);
}
});
}
function makeFirstAppearanceIndex()
{
$conn = getDb();
$conn->transactional(function(Connection $conn) {
$conn->executeQuery("DELETE FROM first_appearance");
$result = $conn->executeQuery("SELECT DISTINCT speaker FROM talk");
$stmt = $conn->prepare("INSERT INTO first_appearance
SELECT talk.speaker as speaker, event.uri as event_uri, event.start_date as event_date, event.name as event_name
FROM event
INNER JOIN talk ON event.uri = talk.event_uri
WHERE talk.speaker = :name
ORDER BY event.start_date
LIMIT 1");
foreach ($result as $record) {
$stmt->execute([
'name' => $record['speaker']
]);
}
});
}
derive();