speakers & devroom managers and their emails from penta

[markvdb]

Hi,

TravisR and Matthew (in #fosdem-video and #fosdem-matrix-bot:fosdem.org need a list with speaker/email mappings from penta to be able to efficiently create matrix invites. The goal is to get these asap, to avoid non-free software use at the devroom level.

I've subscribed everyone I suspect to be at least somewhat familiar with penta's postgres innards to this issue.

Mark

[ara4n]

folks, we really really need this - multiple devrooms are moving to Zoom & Discord to coordinate their tracks because don't know who they are in order to invite them into the backstage rooms in Matrix. Please can we get a list?

[kergon]

I will take this one.

[johanvdw]

Is this ok:

select slug, event.title, conference_track_mail_alias, event_role, first_name, last_name, email
from 
event 
join conference_track using (conference_track_id)
join event_person using (event_id)
join person using (person_id)
where conference_track.conference_id=13;

                                              slug                                               |                                                                      title                                                                      | conference_track_mail_alias | event_role  |     first_name     |      last_name      |                                         email                                          
-------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+-------------+--------------------+---------------------+----------------------------------------------------------------------------------------
 stateofgo                                                                                       | The State of Go                                                                                                                                 | go                          | coordinator | Francesc           | Campoy Flores       | **redacted**

[kergon]

On Tue, Jan 12, 2021 at 11:34:16PM -0800, Johan Van de Wauw wrote:

select slug, event.title, conference_track_mail_alias, event_role, first_name, last_name, email

No - it's more complicated.

[johanvdw]

On Tue, Jan 12, 2021 at 11:34:16PM -0800, Johan Van de Wauw wrote: select slug, event.title, conference_track_mail_alias, event_role, first_name, last_name, email No - it's more complicated.

yep, at least we should filter out the accepted talks, but I guess you have the queries from sending mails anyway.

[kergon]

https://github.com/FOSDEM/pentabarf/commit/1fbf166046e0adab5835efa7399159138eedaae1

[kergon]

view_person.name gives the tidy view of the person's name matrix is working from the XML, so is keying off event_id and person_id login_name is another useful unique identifier slug and conference_room make the view useful without reference to the xml only shows the roles the bot needs to know about excludes cancelled events and the certification track includes accepted events that aren't yet published adds in matrix ID if people choose to fill it in (or if in future the bot writes it back) flattens the data into a single view for convenience

[yoe]

Not sure whether the video user has access to this view, but please remember that SReview will also need to be able to send emails to speakers and track managers after the event to allow for postprocessing and stuff.

[kergon]

A view can be provided. (I'm setting a similar one up for the matrix bot.)

[kergon]

wouter: it's missing the devroom manager aliases (I can add that - they are in the db now) but try it if you want now - select * from view_matrix_bot_export_2021 where event_id = $event_id

[kergon]

...where event_role in ('speaker', 'moderator', 'host');

[yoe]

I'm actually keying on person_id (for speakers as listed in the pentabarf XML dump) rather than event, but I can work with this, thanks.

Track manager aliases are in a completely different location in the SReview database, and I guess they're irrelevant for matrix, so it's fine (and probably even better) if that's in a separate view.

[kergon]

Well you can do this already: select event_id,concat(conference_track_mail_alias, '-devroom-manager@fosdem.org') AS mail_alias from event join conference_track using (conference_track_id) where event_id = 11386;

or I can (later) clean it up into a view

[kergon]

• program / LTs different though

[kergon]

(different just in that you don't add on -devroom-manager)

[markvdb]

Fixed, plus not relevant anymore after switch to pretalx.