$Rev: 1035 $ $Date: 2005-02-10 13:19:21 -0600 (Thu, 10 Feb 2005) $
Queries for the message board's web interface.
select mb.create_session()
update mb.session
set stop = 'now',
stop_because = 0
where session_id = <id>
insert into mb.usersession (session_id, user_id, start)
values (<id>,
<uid>,
'now')
update mb.usersession
set stop = 'now'
where session_id = <id>
and user_id = <uid>
select count(s.session_id) as total_sessions,
count(us.user_id) as registered_sessions,
count(s.session_id) - count(us.user_id) as guest_sessions
from mb.session s
natural left join mb.usersession us
where s.stop is null
select s.session_id as session_id,
u.user_id as user_id,
u.username as user,
s.start
from mb.session s
natural left join mb.usersession us
natural left join mb._user u
where s.stop is null
select u.user_id as uid,
u.password = crypt(<password>, u.password)
as correct
from mb._user u
where u.username = <username>
update mb._user
set password = crypt(<password>, gen_salt('md5'))
where user_id = <id>
insert into mb._user
(username, password, email, realname)
values (<username>,
crypt(<password>, gen_salt('md5')),
<email>,
<realname>)
select forum.forum_id as id,
forum.name as "Name",
forum.description as "Description",
coalesce(mmi.thread_count, 0) as "Threads",
coalesce(mmi.message_count, 0) as "Msgs",
mmi.latest as "Latest Message"
from mb.forum
natural left join (
select thread.forum_id,
count(distinct thread.thread_id) as thread_count,
count(*) as message_count,
max(message.when_created) as latest
from mb.message
natural join mb.thread
where message.active
and thread.active
group by thread.forum_id
) as mmi
where forum.read_group_id is null
order by coalesce(mmi.latest,forum.when_created) desc
select forum.forum_id as id,
forum.name as "Name",
forum.description as "Description",
coalesce(mmi.thread_count, 0) as "Threads",
coalesce(mmi.message_count, 0) as "Msgs",
coalesce(mmi_unread.message_count, 0) as "Unread",
mmi.latest as "Latest Message"
from mb.forum
left join (
select thread.forum_id,
count(distinct thread.thread_id) as thread_count,
count(*) as message_count,
max(message.when_created) as latest
from mb.message
join mb.thread
on (message.thread_id = thread.thread_id)
where message.active
and thread.active
group by thread.forum_id
) as mmi on (forum.forum_id = mmi.forum_id)
left join (
select thread.forum_id,
count(*) as message_count
from mb.message
join mb.thread
on (message.thread_id = thread.thread_id)
left join mb.user_thread
on (user_thread.thread_id = thread.thread_id)
where message.active
and thread.active
and (message.when_created > user_thread.when_last_read
or user_thread.when_last_read is null)
and (user_thread.user_id = <uid>
or user_thread.user_id is null)
group by thread.forum_id
) as mmi_unread on (forum.forum_id = mmi_unread.forum_id)
where (forum.read_group_id is null or exists (
select 'x'
from mb.group_membership
where group_id = forum.read_group_id
and user_id = <uid>))
order by coalesce(mmi.latest,forum.when_created) desc
select forum.name,
mb.wrap_message(forum.description) as description,
newthread_group_id is null or exists (
select 'x'
from mb.group_membership
where group_id = forum.newthread_group_id
and user_id = <user_id>)
as newthread_allowed
from mb.forum
where forum_id = <forum_id>
and (forum.read_group_id is null or exists (
select 'x'
from mb.group_membership
where group_id = forum.read_group_id
and user_id = <user_id>))
select thread.thread_id,
thread.subject as "Subject",
thread.when_created as "When created",
thread.owner_id,
owner.username as "Owner",
count(*) as "Msgs",
max(message.when_created) as "Latest message"
from mb.thread_v as thread
join mb.message on (thread.thread_id = message.thread_id)
left join mb._user as owner
on (thread.owner_id = owner.user_id)
where thread.forum_id = <forum_id>
and thread.active
and message.active
group by thread.thread_id,
thread.subject,
thread.when_created,
thread.owner_id,
owner.username
order by max(message.when_created) desc
select first_unread_id,
thread.thread_id,
thread.subject as "Subject",
thread.when_created as "When created",
thread.owner_id,
owner.username as "Owner",
count(message.*) as "Msgs",
coalesce(unread_count,0) as "Unread",
max(message.when_created) as "Latest message"
from mb.thread_v as thread
join mb.message on (thread.thread_id = message.thread_id)
left join mb._user as owner
on (thread.owner_id = owner.user_id)
left join (
select message.thread_id,
min(message.message_id) as first_unread_id,
count(*) as unread_count
from mb.message
left join mb.user_thread
on (user_thread.thread_id = message.thread_id)
where active
and message.when_created >
coalesce(user_thread.when_last_read,
'-infinity'::timestamp)
and (user_thread.user_id is null
or user_thread.user_id = <uid>)
group by message.thread_id
) mmi_unread on (thread.thread_id = mmi_unread.thread_id)
where thread.forum_id = <forum_id>
and thread.active
and message.active
group by thread.thread_id,
thread.subject,
thread.when_created,
thread.owner_id,
owner.username,
first_unread_id,
coalesce(unread_count,0)
order by max(message.when_created) desc
select mb.setlastread(<thread_id>,
<user_id>)
select forum.forum_id,
forum.name as forum_name,
thread.subject,
thread.when_created,
thread.owner_id,
owner.username as owner,
post_group_id is null or exists (
select 'x'
from mb.group_membership
where group_id = forum.post_group_id
and user_id = <user_id>)
as post_allowed,
user_thread.when_last_read
from mb.forum
join mb.thread_v as thread on (forum.forum_id = thread.forum_id)
left join mb._user owner on (thread.owner_id = owner.user_id)
left join (
select thread_id,
when_last_read
from mb.user_thread
where user_id = <user_id>
) user_thread on (thread.thread_id = user_thread.thread_id)
where thread.thread_id = <thread_id>
and (forum.read_group_id is null
or exists (
select 'x'
from mb.group_membership
where user_id = <user_id>
and group_id = forum.read_group_id))
select message.message_id as id,
message.owner_id,
owner.username as owner,
message.subject,
mb.wrap_message(message.body) as body,
message.when_created
from mb.message
left join mb._user owner on (message.owner_id = owner.user_id)
where message.thread_id = <thread_id>
and message.active
order by message.when_created
select username,
realname,
email,
url,
when_created
from mb._user u
where user_id = <id>
select *
from mb.post_message(<user_id>,
<forum_id>,
<thread_id>,
<subject>,
<body>)
as (url varchar, thread_subject varchar, thread_owner varchar)
select realname,
email
from mb._user join mb.user_thread
on (_user.user_id = user_thread.user_id)
where user_thread.thread_id = <thread_id>
and user_thread.user_id <> <user_id>
and when_last_read > coalesce(when_last_notified,'-infinity')
and want_notification
update mb.user_thread
set when_last_notified = now()
where user_thread.thread_id = <thread_id>
and user_thread.user_id <> <user_id>
and when_last_read > coalesce(when_last_notified,'-infinity')
and want_notification
Generated by Axamol SQL Library.