get posts from mattermost postgres database

mm_get_posts.md

get posts from mattermost postgres database

connect database

docker exec -it gitlab gitlab-psql -d mattermost_production

show table schema

\dt;
\d posts;
\d userid;
\d channels;
\d teams;

select

get posts after a certain time

echo $(( `date --date "2 day ago" +%s` * 1000))
1608385690000

\pset format wrapped
\set TIME '1608385690000'
select
 to_timestamp(p.createat/1000.0+3600*9) as createat    ,
 p.id,
 users.username as username,
 channels.name,
 message
from
  (select * from posts where createat >= :'TIME') as p
inner join
  users
on p.userid = users.id
inner join
  channels 
on channelid = channels.id
where originalid = ''
order by p.createat DESC, p.updateat DESC
;

get posts in a specific channel with createat,updateat,deleteat

select
 to_timestamp(p.createat/1000.0) as createat    ,
 users.username as username,
 message,
 to_timestamp(p.updateat/1000.0) as updateat    ,
 to_timestamp(p.deleteat/1000.0) as deleteat    ,
 users.nickname as nickname,
 p.id           
from
  (select *
   from posts 
   where channelid = (select id from channels where name = 'blackhole')
  ) as p
inner join
  users
on p.userid = users.id
order by p.createat DESC
;

get posts in a specific channel with createat

\set CH_NAME 'blackhole'
select
 to_timestamp(p.createat/1000.0) as createat    ,
 users.username as username,
 message
from
  (select *
   from posts 
   where channelid = (select id from channels where name = :'CH_NAME')
  ) as p
inner join
  users
on p.userid = users.id
where originalid = ''
order by p.createat DESC, p.updateat DESC
;

get posts in a specific channel with createat

\set CH_ID 'm5437e7ppjgfxjutqiayj4yucr'
select
 to_timestamp(p.createat/1000.0) as createat    ,
 users.username as username,
 message
from
  (select *
   from posts 
   where channelid = :'CH_ID'
  ) as p
inner join
  users
on p.userid = users.id
where originalid = ''
order by p.createat DESC, p.updateat DESC
;

get posts include specific keyword with channel name

docker exec -it gitlab gitlab-psql -d mattermost_production -nx

\pset format wrapped
\set KEYWORD '%KEY WORD%'
select
 to_timestamp(p.createat/1000.0) as createat    ,
 p.id,
 users.username as username,
 channels.name,
 message
from
  posts as p
inner join
  users
on p.userid = users.id
inner join
  channels 
on channelid = channels.id
where originalid = ''
  and message like :'KEYWORD'
order by p.createat DESC, p.updateat DESC
;

get posts include specific keyword and channel name

\pset format wrapped
\set KEYWORD '%KEY WORD%'
\set CH_NAME 'blackhole'
select
 p.id,
 to_timestamp(p.createat/1000.0) as createat    ,
 users.username as username,
 channels.name,
 message
from
  posts as p
inner join
  users
on p.userid = users.id
inner join
  channels 
on channelid = channels.id
where originalid = ''
  and message like :'KEYWORD'
  and channels.name = :'CH_NAME'
order by p.createat DESC, p.updateat DESC
;

get posts include specific keyword and team name

\pset format wrapped
\set KEYWORD '%KEY WORD%'
\set TEAM_NAME 'akb46'
select
 p.id,
 to_timestamp(p.createat/1000.0) as createat    ,
 users.username as username,
 channels.name,
 message
from
  posts as p
inner join
  users
on p.userid = users.id
inner join
  channels 
on channelid = channels.id
inner join
  teams 
on teamid = teams.id
where originalid = ''
  and message like :'KEYWORD'
  and teams.name = :'TEAM_NAME'
order by p.createat DESC, p.updateat DESC
;

get channels

select id,type,lastpostat,displayname,name,header,purpose from channels where teamid = 'rcct64cza3bq9p7ibz3n5zpdhr';

delete posts

select * from posts where id = 'xtt1qknb5bnxfmyop4g9uc1y9a';
DELETE FROM posts WHERE id = 'xtt1qknb5bnxfmyop4g9uc1y9a';

auth data

select id,username,email,authservice,authdata from users;
\d oauthauthdata;

select id,username,password,email,authservice,authdata,emailverified, from users where email = 'xxxxx';