Logo Search packages:      
Sourcecode: zope-cmfpgforum version File versions

sql.py

##############################################################################
#
# Copyright (c) 2003 - Emergence by Design Inc. - All Rights Reserved
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307,  USA.
#
##############################################################################

from RoutableSqlMethod import RoutableSqlMethod


class SQLMethods:
    sqlCreateTables=RoutableSqlMethod(
        'sqlCreateTables',
        '',
        '',
        """
create sequence MESSAGE_BOARD_ID_SEQ

<dtml-var sql_delimiter>

create table POSTING (
  POSTING_ID                           integer primary key not null,
  MESSAGE_BOARD_ID                     integer not null,
  PARENT_ID                            integer,
  THREAD_ID                            integer,
  AUTHOR                               varchar(128),
  AUTHOR_IP_ADDRESS                    varchar(256),
  POSTING_SUBJECT                      varchar(256),
  POSTING_BODY                         text,
  DATE_CREATED                         timestamp,
  DATE_LAST_REPLY                      timestamp,
  NUMBER_OF_REPLIES                    integer default 0,
  POSTING_PRIORITY                     integer default 10,
  THREAD_LOCKED                        boolean default 'f',
  SHOW_POSTING                         boolean default 't'
)

<dtml-var sql_delimiter>

create sequence POSTING_ID_SEQ

<dtml-var sql_delimiter>

create table POSTING_KEYWORD (
  POSTING_ID                          integer not null,
  KEYWORD                             varchar(64) not null,
  KEYWORD_IN_SUBJECT                  boolean,
  primary key (POSTING_ID, KEYWORD, KEYWORD_IN_SUBJECT)
)

<dtml-var sql_delimiter>

create index POSTING_KEYWORD_KEYWORD_INDEX on POSTING_KEYWORD(KEYWORD)

<dtml-var sql_delimiter>

COMMIT""")


    getNextval_MESSAGE_BOARD_ID_SEQ=RoutableSqlMethod(
        'getNextval_MESSAGE_BOARD_ID_SEQ',
        '',
        '''

        ''',
        '''
select nextval ('MESSAGE_BOARD_ID_SEQ') as MESSAGE_BOARD_ID
        ''')


    posting_insert_sql=RoutableSqlMethod(
        'posting_insert_sql',
        '',
        '''
POSTING_ID="" MESSAGE_BOARD_ID=""

PARENT_ID="" THREAD_ID=""

AUTHOR_IP_ADDRESS=""

POSTING_SUBJECT="" POSTING_BODY=""
        ''',
        '''
insert into POSTING (
  POSTING_ID,
  MESSAGE_BOARD_ID,
  PARENT_ID,
  THREAD_ID,
  AUTHOR,
  AUTHOR_IP_ADDRESS,
  POSTING_SUBJECT,
  POSTING_BODY,
  DATE_CREATED
)
  values (
  <dtml-var "_.int(POSTING_ID)">,
  <dtml-var "_.int(MESSAGE_BOARD_ID)">,
  <dtml-if PARENT_ID><dtml-var "_.int(PARENT_ID)"> <dtml-else>null </dtml-if>,
  <dtml-if THREAD_ID><dtml-var "_.int(THREAD_ID)"> <dtml-else>null </dtml-if>,
  '<dtml-var "_.str(_.SecurityGetUser())" sql_quote>',
  '<dtml-var "_.str(AUTHOR_IP_ADDRESS)" sql_quote>',
  '<dtml-var "_.str(POSTING_SUBJECT)" sql_quote>',
  '<dtml-var "_.str(POSTING_BODY)" sql_quote>',
  now()
)

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_get_posting_id_seq_nextval=RoutableSqlMethod(
        'posting_get_posting_id_seq_nextval',
        '',
        '''

        ''',
        '''
select nextval ('POSTING_ID_SEQ') as POSTING_ID

        ''')


    message_board_browse_sql=RoutableSqlMethod(
        'message_board_browse_sql',
        '',
        '''
MESSAGE_BOARD_ID=""

query_start="" batch_size=""

thread_sort_order_translated=""
        ''',
        '''
select
  P.POSTING_ID,
  P.MESSAGE_BOARD_ID,
  P.POSTING_SUBJECT,
  P.AUTHOR,
  P.DATE_CREATED,
  P.DATE_LAST_REPLY,
  P.NUMBER_OF_REPLIES,
  P.POSTING_PRIORITY,
  P.THREAD_LOCKED,
  (select
     count(*)
   from
     POSTING P
   where
     P.MESSAGE_BOARD_ID = <dtml-var "_.int(MESSAGE_BOARD_ID)">
     and P.PARENT_ID is null
     and P.THREAD_ID is null
     and P.SHOW_POSTING = 't'
  ) as THE_COUNT
from
  POSTING P
where
  P.MESSAGE_BOARD_ID = <dtml-var "_.int(MESSAGE_BOARD_ID)">
  and P.PARENT_ID is null
  and P.THREAD_ID is null
  and P.SHOW_POSTING = 't'
order by
  P.POSTING_PRIORITY <dtml-if thread_sort_order_translated
    >, <dtml-var thread_sort_order_translated> </dtml-if>
limit
  <dtml-var "_.int(batch_size)">
offset
  <dtml-var "_.int(query_start)-1">

        ''')


    posting_keyword_insert_sql=RoutableSqlMethod(
        'posting_keyword_insert_sql',
        '',
        '''
POSTING_ID="" KEYWORD_LIST=""

KEYWORD_IN_SUBJECT=""
        ''',
        '''
<dtml-in KEYWORD_LIST>

  insert into POSTING_KEYWORD (
    POSTING_ID,
    KEYWORD,
    KEYWORD_IN_SUBJECT
  )
    values (
    <dtml-var "_.int(POSTING_ID)">,
    '<dtml-var "_.str(_['sequence-item'])" sql_quote>',
    <dtml-if KEYWORD_IN_SUBJECT>'t' <dtml-else>'f' </dtml-if>
  )

  <dtml-var sql_delimiter>

</dtml-in KEYWORD_LIST>

COMMIT

        ''')


    thread_get_all_messages_sql=RoutableSqlMethod(
        'thread_get_all_messages_sql',
        '',
        '''
THREAD_ID=""
        ''',
        '''
select
  P.POSTING_ID,
  P.PARENT_ID,
  P.THREAD_ID,
  P.AUTHOR,
  P.POSTING_SUBJECT,
  P.POSTING_BODY,
  P.DATE_CREATED,
  P.DATE_LAST_REPLY,
  P.NUMBER_OF_REPLIES,
  P.POSTING_PRIORITY,
  P.THREAD_LOCKED
from
  POSTING P
where
  (P.POSTING_ID = <dtml-var "_.int(THREAD_ID)"> or P.THREAD_ID = <dtml-var "_.int(THREAD_ID)">)
  and P.SHOW_POSTING ='t'
order by
  P.DATE_CREATED


        ''')


    reply_get_parent_subject=RoutableSqlMethod(
        'reply_get_parent_subject',
        '',
        '''
PARENT_POSTING_ID=""
        ''',
        '''
select
  P.POSTING_SUBJECT
from
  POSTING P
where
  P.POSTING_ID = <dtml-var "_.int(PARENT_POSTING_ID)">

        ''')


    posting_update_increment_number_of_replies=RoutableSqlMethod(
        'posting_update_increment_number_of_replies',
        '',
        '''
PARENT_ID=""
        ''',
        '''
update
  POSTING
set
  NUMBER_OF_REPLIES = NUMBER_OF_REPLIES + 1,
  DATE_LAST_REPLY = now()
where
  POSTING_ID = <dtml-var "_.int(PARENT_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_get_record_sql=RoutableSqlMethod(
        'posting_get_record_sql',
        '',
        '''
POSTING_ID=""
        ''',
        '''
select
  P.*
from
  POSTING P
where
  P.POSTING_ID = <dtml-var "_.int(POSTING_ID)">

        ''')


    posting_update_sql=RoutableSqlMethod(
        'posting_update_sql',
        '',
        '''
POSTING_ID="" POSTING_SUBJECT=""

POSTING_BODY="" AUTHOR_IP_ADDRESS=""
        ''',
        '''
update POSTING set
  POSTING_SUBJECT = '<dtml-var "_.str(POSTING_SUBJECT)" sql_quote>',
  POSTING_BODY = '<dtml-var "_.str(POSTING_BODY)" sql_quote>',
  AUTHOR_IP_ADDRESS = '<dtml-var "_.str(AUTHOR_IP_ADDRESS)" sql_quote>'
where
  POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_keyword_delete_sql=RoutableSqlMethod(
        'posting_keyword_delete_sql',
        '',
        '''
POSTING_ID=""
        ''',
        '''
delete from POSTING_KEYWORD where
POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_get_children=RoutableSqlMethod(
        'posting_get_children',
        '',
        '''
POSTING_ID=""
        ''',
        '''
select
  P.POSTING_ID
from
  POSTING P
where
  P.PARENT_ID = <dtml-var "_.int(POSTING_ID)">

        ''')


    reply_verify_parent_and_thread=RoutableSqlMethod(
        'reply_verify_parent_and_thread',
        '',
        '''
PARENT_POSTING_ID="" THREAD_ID=""

MESSAGE_BOARD_ID=""
        ''',
        '''
select
  P.POSTING_ID
from
  POSTING P
where
  P.POSTING_ID = <dtml-var "_.int(THREAD_ID)">
  <dtml-unless "_.int(PARENT_POSTING_ID) == _.int(THREAD_ID)">
    and P.POSTING_ID in (select P2.THREAD_ID from POSTING P2
                         where P2.POSTING_ID = <dtml-var "_.int(PARENT_POSTING_ID)">)
  </dtml-unless>
  and P.MESSAGE_BOARD_ID = <dtml-var "_.int(MESSAGE_BOARD_ID)">

        ''')


    posting_verify_deletion_args=RoutableSqlMethod(
        'posting_verify_deletion_args',
        '',
        '''
MESSAGE_BOARD_ID="" POSTING_ID=""

THREAD_ID=""
        ''',
        '''
select
  P.POSTING_ID
from
  POSTING P
where
  P.MESSAGE_BOARD_ID = <dtml-var "_.int(MESSAGE_BOARD_ID)">
  and P.POSTING_ID = <dtml-var "_.int(POSTING_ID)">
  <dtml-if "_.int(POSTING_ID)==_.int(THREAD_ID)">
    and P.THREAD_ID is null
    and P.PARENT_ID is null
  <dtml-else>
    and P.THREAD_ID = <dtml-var "_.int(THREAD_ID)">
  </dtml-if>
  <dtml-unless "user_has_required_role__message_board()">
    and P.AUTHOR = '<dtml-var "_.str(_.SecurityGetUser())" sql_quote>'
  </dtml-unless>


        ''')


    posting_verify_deletion_eligibility=RoutableSqlMethod(
        'posting_verify_deletion_eligibility',
        '',
        '''
POSTING_ID=""
        ''',
        '''
select
  P.POSTING_ID,
  P.POSTING_SUBJECT,
  coalesce(P.THREAD_ID, P.POSTING_ID) as THREAD_ID
from
  POSTING P
where
  P.POSTING_ID = <dtml-var "_.int(POSTING_ID)">
  and P.POSTING_ID not in (select P2.PARENT_ID from POSTING P2
                           where P2.PARENT_ID = <dtml-var "_.int(POSTING_ID)">)
  and P.POSTING_ID not in (select P3.THREAD_ID from POSTING P3
                           where P3.THREAD_ID = <dtml-var "_.int(POSTING_ID)">)

        ''')


    posting_reduce_parent_reply_count=RoutableSqlMethod(
        'posting_reduce_parent_reply_count',
        '',
        '''
PARENT_ID=""
        ''',
        '''
update
  POSTING
set
  NUMBER_OF_REPLIES = NUMBER_OF_REPLIES -1
where
  POSTING_ID = <dtml-var "_.int(PARENT_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_delete_sql=RoutableSqlMethod(
        'posting_delete_sql',
        '',
        '''
POSTING_ID=""
        ''',
        '''
delete from POSTING where POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

delete from POSTING_KEYWORD where POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_get_thread_and_parent_sql=RoutableSqlMethod(
        'posting_get_thread_and_parent_sql',
        '',
        '''
POSTING_ID=""
        ''',
        '''
select
  P.THREAD_ID,
  P.PARENT_ID
from
  POSTING P
where
  P.POSTING_ID = <dtml-var "_.int(POSTING_ID)">

        ''')


    posting_reduce_thread_reply_count=RoutableSqlMethod(
        'posting_reduce_thread_reply_count',
        '',
        '''
THREAD_ID=""
        ''',
        '''
update
  POSTING
set
  NUMBER_OF_REPLIES = NUMBER_OF_REPLIES -1
where
  POSTING_ID = <dtml-var "_.int(THREAD_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    thread_delete_sql=RoutableSqlMethod(
        'thread_delete_sql',
        '',
        '''
THREAD_ID=""
        ''',
        '''
delete from POSTING_KEYWORD where POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

delete from POSTING_KEYWORD where POSTING_ID in
  (select POSTING_ID from POSTING where THREAD_ID = <dtml-var "_.int(THREAD_ID)">)

<dtml-var sql_delimiter>

delete from POSTING where POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

delete from POSTING where THREAD_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_reassign_replies=RoutableSqlMethod(
        'posting_reassign_replies',
        '',
        '''
POSTING_ID="" PARENT_ID=""
        ''',
        '''
update
  POSTING
set
  PARENT_ID = <dtml-var "_.int(PARENT_ID)">
where
  PARENT_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    posting_delete_and_update_parents_sql=RoutableSqlMethod(
        'posting_delete_and_update_parents_sql',
        '',
        '''
POSTING_ID=""
        ''',
        '''
update POSTING set NUMBER_OF_REPLIES = NUMBER_OF_REPLIES -1 where POSTING_ID =
(select THREAD_ID from POSTING where POSTING_ID = <dtml-var "_.int(POSTING_ID)">)

<dtml-var sql_delimiter>

update POSTING set NUMBER_OF_REPLIES = NUMBER_OF_REPLIES -1 where POSTING_ID =
(select POSTING.PARENT_ID from POSTING where POSTING.POSTING_ID = <dtml-var "_.int(POSTING_ID)">
  and POSTING.PARENT_ID != POSTING.THREAD_ID)

<dtml-var sql_delimiter>

delete from POSTING where POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

delete from POSTING_KEYWORD where POSTING_ID = <dtml-var "_.int(POSTING_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    thread_change_locked_status=RoutableSqlMethod(
        'thread_change_locked_status',
        '',
        '''
THREAD_ID="" NEW_STATUS=""
        ''',
        '''
update
  POSTING
set
  THREAD_LOCKED = <dtml-if NEW_STATUS>'t' <dtml-else>'f' </dtml-if>
where
  POSTING_ID = <dtml-var "_.int(THREAD_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    thread_change_priority_sql=RoutableSqlMethod(
        'thread_change_priority_sql',
        '',
        '''
THREAD_ID="" NEW_PRIORITY=""
        ''',
        '''
update
  POSTING
set
  POSTING_PRIORITY = <dtml-var "_.int(NEW_PRIORITY)">
where
  POSTING_ID = <dtml-var "_.int(THREAD_ID)">

<dtml-var sql_delimiter>

COMMIT

        ''')


    message_board_search_results_sql=RoutableSqlMethod(
        'message_board_search_results_sql',
        '',
        '''
query_start="" batch_size=""

MESSAGE_BOARD_ID=""

SEARCH_ONLY_SUBJECTS=""

SEARCH_TERM_LIST="" THREAD_ID=""

search_results_sort_order_translated=""
        ''',
        '''
select
  P.POSTING_ID,
  P.THREAD_ID,
  P.AUTHOR,
  P.POSTING_SUBJECT,
  P.DATE_CREATED,
  P.DATE_LAST_REPLY,
  P.NUMBER_OF_REPLIES,
  P.THREAD_LOCKED,
  (
    select
      count(*)
    from
      POSTING P
    where
      P.MESSAGE_BOARD_ID = <dtml-var "_.int(MESSAGE_BOARD_ID)">
      and P.SHOW_POSTING = 't'
      <dtml-if THREAD_ID>
        and (P.THREAD_ID = <dtml-var "_.int(THREAD_ID)"> or P.POSTING_ID = <dtml-var "_.int(THREAD_ID)">)
      </dtml-if THREAD_ID>
      <dtml-in SEARCH_TERM_LIST>
        and P.POSTING_ID in (
                              select
                                PK<dtml-var sequence-number>.POSTING_ID
                              from
                                POSTING_KEYWORD PK<dtml-var sequence-number>
                              where
                                PK<dtml-var sequence-number>.KEYWORD =
                                  '<dtml-var "_.str(_['sequence-item'])" sql_quote>'
                                <dtml-if SEARCH_ONLY_SUBJECTS>
                                  and PK<dtml-var sequence-number>.KEYWORD_IN_SUBJECT = 't'
                                </dtml-if SEARCH_ONLY_SUBJECTS>
                              )
      </dtml-in SEARCH_TERM_LIST>
  ) as THE_COUNT
from
  POSTING P
where
  P.MESSAGE_BOARD_ID = <dtml-var "_.int(MESSAGE_BOARD_ID)">
  and P.SHOW_POSTING = 't'
  <dtml-if THREAD_ID>
    and (P.THREAD_ID = <dtml-var "_.int(THREAD_ID)"> or P.POSTING_ID = <dtml-var "_.int(THREAD_ID)">)
  </dtml-if THREAD_ID>
  <dtml-in SEARCH_TERM_LIST>
    and P.POSTING_ID in (
                          select
                            PK<dtml-var sequence-number>.POSTING_ID
                          from
                            POSTING_KEYWORD PK<dtml-var sequence-number>
                          where
                            PK<dtml-var sequence-number>.KEYWORD =
                              '<dtml-var "_.str(_['sequence-item'])" sql_quote>'
                            <dtml-if SEARCH_ONLY_SUBJECTS>
                              and PK<dtml-var sequence-number>.KEYWORD_IN_SUBJECT = 't'
                            </dtml-if SEARCH_ONLY_SUBJECTS>
                          )
  </dtml-in SEARCH_TERM_LIST>

order by
  <dtml-var "_.str(search_results_sort_order_translated)">
limit
  <dtml-var "_.int(batch_size)">
offset
  <dtml-var "_.int(query_start)-1">
        ''')


Generated by  Doxygen 1.6.0   Back to index