org.sakaiproject.site.impl.SiteServiceSqlDefault Maven / Gradle / Ivy
/**********************************************************************************
* $URL: https://source.sakaiproject.org/contrib/rsmart/dbrefactor/site/site-impl/impl/src/java/org/sakaiproject/site/impl/SiteServiceSqlDefault.java $
* $Id: SiteServiceSqlDefault.java 3560 2007-02-19 22:08:01Z [email protected] $
***********************************************************************************
*
* Copyright (c) 2007, 2008 Sakai Foundation
*
* Licensed under the Educational Community License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.opensource.org/licenses/ECL-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
**********************************************************************************/
package org.sakaiproject.site.impl;
/**
* methods for accessing site data in a database.
*/
public class SiteServiceSqlDefault implements SiteServiceSql
{
/**
* returns the sql statement which deletes the groups for a given site from the sakai_site_group table.
*/
public String getDeleteGroupsSql()
{
return "delete from SAKAI_SITE_GROUP where SITE_ID = ?";
}
/**
* returns the sql statement which deletes the group properties for a given site from the sakai_site_group_property table.
*/
public String getDeleteGroupPropertiesSql()
{
return "delete from SAKAI_SITE_GROUP_PROPERTY where SITE_ID = ?";
}
/**
* returns the sql statement which deletes the pages for a given site from the sakai_site_page table.
*/
public String getDeletePagesSql()
{
return "delete from SAKAI_SITE_PAGE where SITE_ID = ?";
}
/**
* returns the sql statement which deletes the page properties for a given site from the sakai_site_page_property table.
*/
public String getDeletePagePropertiesSql()
{
return "delete from SAKAI_SITE_PAGE_PROPERTY where SITE_ID = ?";
}
/**
* returns the sql statement which deletes an individual tool for a given site from the sakai_site_tool table.
*/
public String getDeleteToolSql()
{
return "delete from SAKAI_SITE_TOOL where SITE_ID = ? and TOOL_ID = ?";
}
/**
* returns the sql statement which deletes the tools for a given site from the sakai_site_tool table.
*/
public String getDeleteToolsSql()
{
return "delete from SAKAI_SITE_TOOL where SITE_ID = ?";
}
/**
* returns the sql statement which deletes the tool properties for a given site from the sakai_site_tool_property table.
*/
public String getDeleteToolPropertiesSql()
{
return "delete from SAKAI_SITE_TOOL_PROPERTY where SITE_ID = ?";
}
/**
* returns the sql statement which deletes an individual tool property for a given site from the sakai_site_tool_property table.
*/
public String getDeleteToolPropertySql()
{
return "delete from SAKAI_SITE_TOOL_PROPERTY where SITE_ID = ? and TOOL_ID = ?";
}
/**
* returns the sql statement which deletes a user from a given site.
*/
public String getDeleteUserSql()
{
return "delete from SAKAI_SITE_USER where SITE_ID = ? and USER_ID = ?";
}
/**
* returns the sql statement which deletes users for a given site from the sakai_site_user table.
*/
public String getDeleteUsersSql()
{
return "delete from SAKAI_SITE_USER where SITE_ID = ?";
}
/**
* returns the sql statement which inserts a group into the sakai_site_group table.
*/
public String getInsertGroupSql()
{
return "insert into SAKAI_SITE_GROUP (GROUP_ID, SITE_ID, TITLE, DESCRIPTION) values (?,?,?,?)";
}
/**
* returns the sql statement which inserts a page into the sakai_site_page table.
*/
public String getInsertPageSql()
{
return "insert into SAKAI_SITE_PAGE (PAGE_ID, SITE_ID, TITLE, LAYOUT, POPUP, SITE_ORDER) values (?,?,?,?,?,?)";
}
/**
* returns the sql statement which inserts a tool into the sakai_site_tool table.
*/
public String getInsertToolSql()
{
return "insert into SAKAI_SITE_TOOL (TOOL_ID, PAGE_ID, SITE_ID, REGISTRATION, PAGE_ORDER, TITLE, LAYOUT_HINTS) values (?,?,?,?,?,?,?)";
}
/**
* returns the sql statement which inserts a user into a given site.
*/
public String getInsertUserSql()
{
return "insert into SAKAI_SITE_USER (SITE_ID, USER_ID, PERMISSION) values (?, ?, ?)";
}
/**
* returns the sql statement which is part of the join clause to retrieve sites.
*/
public String getSitesJoin1Sql()
{
return "SAKAI_SITE_USER";
}
/**
* returns the sql statement which is part of the join clause to retrieve sites.
*/
public String getSitesJoin2Sql()
{
return ", SAKAI_USER_ID_MAP";
}
/**
* returns the sql statement which is part of the join clause to retrieve sites.
*/
public String getSitesJoin3Sql()
{
return "SAKAI_USER_ID_MAP";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder1Sql()
{
return "SAKAI_SITE.SITE_ID ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder2Sql()
{
return "SAKAI_SITE.SITE_ID DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder3Sql()
{
return "SAKAI_SITE.TITLE ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder4Sql()
{
return "SAKAI_SITE.TITLE DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder5Sql()
{
return "SAKAI_SITE.TYPE ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder6Sql()
{
return "SAKAI_SITE.TYPE DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder7Sql()
{
return "SAKAI_SITE.PUBLISHED ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder8Sql()
{
return "SAKAI_SITE.PUBLISHED DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder9Sql()
{
return "SAKAI_USER_ID_MAP.EID ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder10Sql()
{
return "SAKAI_USER_ID_MAP.EID DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder11Sql()
{
return "SAKAI_USER_ID_MAP.EID ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder12Sql()
{
return "SAKAI_USER_ID_MAP.EID DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder13Sql()
{
return "SAKAI_SITE.CREATEDON ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder14Sql()
{
return "SAKAI_SITE.CREATEDON DESC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder15Sql()
{
return "SAKAI_SITE.MODIFIEDON ASC";
}
/**
* returns the sql statement which is part of the order clause to retrieve sites.
*/
public String getSitesOrder16Sql()
{
return "SAKAI_SITE.MODIFIEDON DESC";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere1Sql()
{
return "SAKAI_SITE.SITE_ID = SAKAI_SITE_USER.SITE_ID and SAKAI_SITE_USER.USER_ID = ? and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere2Sql()
{
return "SAKAI_SITE.IS_USER = '0' and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere3Sql()
{
return "SAKAI_SITE.IS_SPECIAL = '0' and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere4Sql()
{
return "SAKAI_SITE.PUBLISHED = 1 and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere5Sql()
{
return "SAKAI_SITE.TYPE = ? and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere6Sql()
{
return "SAKAI_SITE.TYPE IN (?";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere7Sql()
{
return "SAKAI_SITE.JOINABLE = '1' and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere8Sql()
{
return "SAKAI_SITE.PUBVIEW = '1' and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere9Sql()
{
return "UPPER(SAKAI_SITE.TITLE) like UPPER(?) and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere10Sql()
{
return "SAKAI_SITE_USER.PERMISSION <= -1 and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere11Sql()
{
return "SAKAI_SITE_USER.PERMISSION <= SAKAI_SITE.PUBLISHED and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere12Sql()
{
return "SITE_ID not in (select SITE_ID from SAKAI_SITE_USER where USER_ID = ? and PERMISSION <= PUBLISHED) and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere13Sql()
{
return "SAKAI_SITE.SITE_ID in (select SITE_ID from SAKAI_SITE_PROPERTY where NAME = ? and UPPER(VALUE) like UPPER(?)) and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere14Sql()
{
return "SAKAI_SITE.CREATEDBY = SAKAI_USER_ID_MAP.USER_ID and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites.
*/
public String getSitesWhere15Sql()
{
return "SAKAI_SITE.MODIFIEDBY = SAKAI_USER_ID_MAP.USER_ID and ";
}
/**
* returns the sql statement which is part of the where clause to retrieve the number of sites.
*/
/*
* public String getSitesCountWhere12Sql() { return "SAKAI_SITE.SITE_ID not in (select SITE_ID from SAKAI_SITE_USER where USER_ID = ? and
* PERMISSION <= PUBLISHED) and "; } "SITE_ID not in (select SITE_ID from SAKAI_SITE_USER where USER_ID = ? and PERMISSION <= PUBLISHED) and "
*/
/**
* returns the sql statement which retrieves the skin and whether the site has been published from the sakai_site table.
*/
public String getSkinSql()
{
return "select SKIN, PUBLISHED from SAKAI_SITE where SITE_ID = ?";
}
/**
* returns the sql statement which retrieves some fields from the sakai_site and sakai_site_tool tables.
*/
public String getToolFields1Sql()
{
return "select REGISTRATION, SAKAI_SITE_TOOL.TITLE, LAYOUT_HINTS, SAKAI_SITE_TOOL.SITE_ID, PAGE_ID, SKIN, PUBLISHED, PAGE_ORDER "
+ "from SAKAI_SITE_TOOL, SAKAI_SITE " + "where SAKAI_SITE_TOOL.SITE_ID = SAKAI_SITE.SITE_ID and TOOL_ID = ?";
}
/**
* returns the sql statement which retrieves various fields from the sakai_site_tool table for a given page.
*/
public String getToolFields2Sql()
{
return "select TOOL_ID, REGISTRATION, TITLE, LAYOUT_HINTS, PAGE_ORDER from SAKAI_SITE_TOOL where PAGE_ID = ? order by PAGE_ORDER ASC";
}
/**
* returns the sql statement which retrieves various fields from the sakai_site_tool table for a given site.
*/
public String getToolFields3Sql()
{
return "select TOOL_ID, PAGE_ID, REGISTRATION, TITLE, LAYOUT_HINTS, PAGE_ORDER from SAKAI_SITE_TOOL where SITE_ID = ? order by PAGE_ID, PAGE_ORDER ASC";
}
/**
* returns the sql statement which retrieves the types of sites that have been created from the sakai_site table.
*/
public String getTypesSql()
{
return "select distinct TYPE from SAKAI_SITE order by TYPE";
}
/**
* returns the sql statement which retrieves various fields from the sakai_site_tool table for a given site.
*/
public String getGroupFieldsSql()
{
return "select SS.GROUP_ID, SS.TITLE, SS.DESCRIPTION " + "from SAKAI_SITE_GROUP SS where SS.SITE_ID = ?";
}
/**
* returns the sql statement which retrieves some fields from the sakai_site and sakai_site_page tables.
*/
public String getPageFields1Sql()
{
return "select PAGE_ID, SAKAI_SITE_PAGE.TITLE, LAYOUT, SAKAI_SITE_PAGE.SITE_ID, SKIN, PUBLISHED, POPUP "
+ "from SAKAI_SITE_PAGE, SAKAI_SITE " + "where SAKAI_SITE_PAGE.SITE_ID = SAKAI_SITE.SITE_ID " + "and PAGE_ID = ?";
}
/**
* returns the sql statement which retrieves various fields from the sakai_site_page table for a given site.
*/
public String getPageFields2Sql()
{
return "select PAGE_ID, TITLE, LAYOUT, POPUP from SAKAI_SITE_PAGE where SITE_ID = ? order by SITE_ORDER ASC";
}
/**
* returns the sql statement which retrieves the site id for a given page from the sakai_site_page table.
*/
public String getSiteId1Sql()
{
return "select SITE_ID from SAKAI_SITE_PAGE where PAGE_ID = ?";
}
/**
* returns the sql statement which retrieves the site id for a given group from the sakai_site_group table.
*/
public String getSiteId2Sql()
{
return "select SS.SITE_ID from SAKAI_SITE_GROUP SS where SS.GROUP_ID = ?";
}
/**
* returns the sql statement which retrieves the site id for a given tool from the sakai_site_tool table.
*/
public String getSiteId3Sql()
{
return "select SITE_ID from SAKAI_SITE_TOOL where TOOL_ID = ?";
}
/**
* returns the sql statement which retrieves the site id and permission for a given user from the sakai_site_user table.
*/
public String getSiteId4Sql()
{
return "select SITE_ID, PERMISSION from SAKAI_SITE_USER " + "where USER_ID = ?";
}
/**
* returns the sql statement which retrieves the user id for a given site from the sakai_site_user table.
*/
public String getUserIdSql()
{
return "select USER_ID, PERMISSION from SAKAI_SITE_USER " + "where SITE_ID = ?";
}
/**
* returns the sql statement which retrieves the group properties from the sakai_site_group_property table for a given site.
*/
public String getGroupPropertiesSql()
{
return "select GROUP_ID, NAME, VALUE from SAKAI_SITE_GROUP_PROPERTY where ( SITE_ID = ? )";
}
/**
* returns the sql statement which retrieves the page properties from the sakai_site_page_property table for a given site.
*/
public String getPagePropertiesSql()
{
return "select PAGE_ID, NAME, VALUE from SAKAI_SITE_PAGE_PROPERTY where ( SITE_ID = ? )";
}
/**
* returns the sql statement which retrieves the tool properties from the sakai_site_tool_property table for a given site.
*/
public String getToolPropertiesSql()
{
return "select TOOL_ID, NAME, VALUE from SAKAI_SITE_TOOL_PROPERTY where ( SITE_ID = ? )";
}
/**
* returns the sql statement which updates a site into the sakai_site table.
*/
public String getUpdateSiteSql(String table)
{
return "update " + table + " set DESCRIPTION = ?, INFO_URL = ? where SITE_ID = ?";
}
/**
* returns the sql statement which is part of the where clause to retrieve sites which are softly deleted
*/
public String getSitesWhereSoftlyDeletedOnlySql()
{
return "SAKAI_SITE.IS_SOFTLY_DELETED = '1' and ";
}
/**
* returns the sql statement which is part of the where clause to only retrieve sites that are NOT softly deleted
* @return
*/
public String getSitesWhereNotSoftlyDeletedSql()
{
return "SAKAI_SITE.IS_SOFTLY_DELETED = '0' and ";
}
/**
* returns part of the where clause to retrieve sites that are unpublished
*/
public String getUnpublishedSitesOnlySql() {
return "SAKAI_SITE.PUBLISHED = '0' and ";
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy