MediaDAO.java
/*
* Copyright (C) 2000 - 2024 Silverpeas
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* As a special exception to the terms and conditions of version 3.0 of
* the GPL, you may redistribute this Program in connection with Free/Libre
* Open Source Software ("FLOSS") applications as described in Silverpeas's
* FLOSS exception. You should have received a copy of the text describing
* the FLOSS exception, and it is also available here:
* "https://www.silverpeas.org/legal/floss_exception.html"
*
* 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 Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
package org.silverpeas.components.gallery.dao;
import org.silverpeas.components.gallery.constant.MediaMimeType;
import org.silverpeas.components.gallery.constant.MediaType;
import org.silverpeas.components.gallery.model.InternalMedia;
import org.silverpeas.components.gallery.model.Media;
import org.silverpeas.components.gallery.model.MediaCriteria;
import org.silverpeas.components.gallery.model.MediaPK;
import org.silverpeas.components.gallery.model.MediaWithStatus;
import org.silverpeas.components.gallery.model.Photo;
import org.silverpeas.components.gallery.model.Sound;
import org.silverpeas.components.gallery.model.Streaming;
import org.silverpeas.components.gallery.model.Video;
import org.silverpeas.components.gallery.socialnetwork.SocialInformationGallery;
import org.silverpeas.core.date.period.Period;
import org.silverpeas.core.io.media.Definition;
import org.silverpeas.core.media.streaming.StreamingProvider;
import org.silverpeas.core.media.streaming.StreamingProvidersRegistry;
import org.silverpeas.core.persistence.datasource.OperationContext;
import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQueries;
import org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery;
import org.silverpeas.core.persistence.jdbc.sql.ResultSetWrapper;
import org.silverpeas.core.socialnetwork.model.SocialInformation;
import org.silverpeas.core.util.CollectionUtil;
import org.silverpeas.core.util.DateUtil;
import org.silverpeas.kernel.logging.SilverLogger;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import static org.silverpeas.core.persistence.jdbc.DBUtil.getUniqueId;
import static org.silverpeas.core.persistence.jdbc.sql.JdbcSqlQuery.*;
public class MediaDAO {
private static final String GALLERY_PATH_TABLE = "SC_Gallery_Path";
private static final String SELECT_INTERNAL_MEDIA_PREFIX =
"I.mediaId, I.fileName, I.fileSize, I.fileMimeType, I.download, I.beginDownloadDate," +
" I.endDownloadDate, ";
private static final String GALLERY_MEDIA_TABLE = "SC_Gallery_Media";
private static final String GALLERY_PHOTO_TABLE = "SC_Gallery_Photo";
private static final String GALLERY_VIDEO_TABLE = "SC_Gallery_Video";
private static final String GALLERY_SOUND_TABLE = "SC_Gallery_Sound";
private static final String GALLERY_STREAMING_TABLE = "SC_Gallery_Streaming";
private static final String GALLERY_INTERNAL_TABLE = "SC_Gallery_Internal";
private static final String MEDIA_ID_CRITERIA = "mediaId = ?";
private static final String MEDIA_ID_PARAM = "mediaId";
private static final String INSTANCE_ID_PARAM = "instanceId";
private MediaDAO() {
}
/**
* Gets the media behind the specified criteria.
* @param criteria the media criteria.
* @return the media behind the criteria, null if no media found and throws
* {@link IllegalArgumentException} if several media are found.
* @throws SQLException on SQL error
* @throws IllegalArgumentException on bad argument
*/
public static Media getByCriteria(final MediaCriteria criteria)
throws SQLException {
return unique(findByCriteria(criteria));
}
/**
* Finds media according to the given criteria.
* @param criteria the media criteria.
* @return the media list corresponding to the given criteria.
*/
public static List<Media> findByCriteria(final MediaCriteria criteria) throws SQLException {
MediaSQLQueryBuilder queryBuilder = MediaSQLQueryBuilder.selectBuilder();
criteria.processWith(queryBuilder);
JdbcSqlQuery selectQuery = queryBuilder.result();
final Map<String, Photo> photos = new HashMap<>();
final Map<String, Video> videos = new HashMap<>();
final Map<String, Sound> sounds = new HashMap<>();
final Map<String, Streaming> streamings = new HashMap<>();
List<Media> media = selectQuery.execute(row -> {
String mediaId = row.getString(1);
MediaType mediaType = MediaType.from(row.getString(2));
String instanceId = row.getString(3);
MediaInfo mediaInfo = new MediaInfo(mediaId, mediaType, instanceId);
return fetchCurrentMedia(photos, videos, sounds, streamings, row, mediaInfo);
});
decoratePhotos(media, photos);
decorateVideos(media, videos);
decorateSounds(media, sounds);
decorateStreamings(media, streamings);
return queryBuilder.orderingResult(media);
}
private static Media fetchCurrentMedia(final Map<String, Photo> photos,
final Map<String, Video> videos, final Map<String, Sound> sounds,
final Map<String, Streaming> streaming, final ResultSetWrapper row, final MediaInfo mediaInfo)
throws SQLException {
final Media currentMedia;
switch (mediaInfo.getMediaType()) {
case Photo:
currentMedia = new Photo();
photos.put(mediaInfo.getMediaId(), (Photo) currentMedia);
break;
case Video:
currentMedia = new Video();
videos.put(mediaInfo.getMediaId(), (Video) currentMedia);
break;
case Sound:
currentMedia = new Sound();
sounds.put(mediaInfo.getMediaId(), (Sound) currentMedia);
break;
case Streaming:
currentMedia = new Streaming();
streaming.put(mediaInfo.getMediaId(), (Streaming) currentMedia);
break;
default:
currentMedia = null;
}
if (currentMedia == null) {
// Unknown media ...
SilverLogger.getLogger(MediaDAO.class)
.warn("Unknown media type {0}", mediaInfo.getMediaType());
return null;
}
currentMedia.setMediaPK(new MediaPK(mediaInfo.getMediaId(), mediaInfo.getInstanceId()));
currentMedia.setTitle(row.getString(4));
currentMedia.setDescription(row.getString(5));
currentMedia.setAuthor(row.getString(6));
currentMedia.setKeyWord(row.getString(7));
currentMedia.setVisibilityPeriod(
Period.check(Period.from(new Date(row.getLong(8)), new Date(row.getLong(9)))));
currentMedia.setCreationDate(row.getTimestamp(10));
currentMedia.setCreatorId(row.getString(11));
currentMedia.setLastUpdateDate(row.getTimestamp(12));
currentMedia.setLastUpdatedBy(row.getString(13));
return currentMedia;
}
/**
* Adding all data of photos.
* @param media the list of media that have not been yet decorated.
* @param photos indexed photo media to decorate.
* @throws SQLException on SQL error
*/
private static void decoratePhotos(List<Media> media, Map<String, Photo> photos)
throws SQLException {
if (!photos.isEmpty()) {
Collection<Collection<String>> idGroups =
CollectionUtil.split(new ArrayList<>(photos.keySet()));
String queryBase = SELECT_INTERNAL_MEDIA_PREFIX +
"P.resolutionW, P.resolutionH from SC_Gallery_Internal I join SC_Gallery_Photo P on I" +
".mediaId = P.mediaId where I.mediaId";
for (Collection<String> mediaIds : idGroups) {
select(queryBase).in(mediaIds).execute(row -> {
String mediaId = row.getString(1);
mediaIds.remove(mediaId);
Photo currentPhoto = photos.get(mediaId);
decorateInternalMedia(row, currentPhoto);
currentPhoto.setDefinition(Definition.of(row.getInt(8), row.getInt(9)));
return null;
});
// Not found
for (String mediaIdNotFound : mediaIds) {
Photo currentPhoto = photos.remove(mediaIdNotFound);
media.remove(currentPhoto);
SilverLogger.getLogger(MediaDAO.class)
.warn("Photo not found (removed from result): {0}", mediaIdNotFound);
}
}
}
}
/**
* Adding all data of videos.
* @param media the list of media that have not been yet decorated.
* @param videos indexed video media to decorate.
* @throws SQLException on SQL error
*/
private static void decorateVideos(List<Media> media, Map<String, Video> videos)
throws SQLException {
if (!videos.isEmpty()) {
Collection<Collection<String>> idGroups =
CollectionUtil.split(new ArrayList<>(videos.keySet()));
String queryBase = SELECT_INTERNAL_MEDIA_PREFIX +
"V.resolutionW, V.resolutionH, V.bitrate, V.duration from SC_Gallery_Internal I join " +
"SC_Gallery_Video V on I.mediaId = V.mediaId where I.mediaId";
for (Collection<String> mediaIds : idGroups) {
select(queryBase).in(mediaIds).execute(row -> {
String mediaId = row.getString(1);
mediaIds.remove(mediaId);
Video currentVideo = videos.get(mediaId);
decorateInternalMedia(row, currentVideo);
currentVideo.setDefinition(Definition.of(row.getInt(8), row.getInt(9)));
currentVideo.setBitrate(row.getLong(10));
currentVideo.setDuration(row.getLong(11));
return null;
});
// Not found
for (String mediaIdNotFound : mediaIds) {
Video currentVideo = videos.remove(mediaIdNotFound);
media.remove(currentVideo);
SilverLogger.getLogger(MediaDAO.class)
.warn("Video not found (removed from result): {0}", mediaIdNotFound);
}
}
}
}
/**
* Adding all data of sounds.
* @param media the list of media that have not been yet decorated.
* @param sounds indexed sound media to decorate.
* @throws SQLException on SQL error
*/
private static void decorateSounds(List<Media> media, Map<String, Sound> sounds)
throws SQLException {
if (!sounds.isEmpty()) {
Collection<Collection<String>> idGroups =
CollectionUtil.split(new ArrayList<>(sounds.keySet()));
String queryBase = SELECT_INTERNAL_MEDIA_PREFIX +
"S.bitrate, S.duration from SC_Gallery_Internal I join SC_Gallery_Sound S on I.mediaId " +
"= S.mediaId where I.mediaId";
for (Collection<String> mediaIds : idGroups) {
select(queryBase).in(mediaIds).execute(row -> {
String mediaId = row.getString(1);
mediaIds.remove(mediaId);
Sound currentSound = sounds.get(mediaId);
decorateInternalMedia(row, currentSound);
currentSound.setBitrate(row.getLong(8));
currentSound.setDuration(row.getLong(9));
return null;
});
// Not found
for (String mediaIdNotFound : mediaIds) {
Sound currentSound = sounds.remove(mediaIdNotFound);
media.remove(currentSound);
SilverLogger.getLogger(MediaDAO.class)
.warn("Sound not found (removed from result): {0}", mediaIdNotFound);
}
}
}
}
/**
* Adding all data of streamings.
* @param media the list of media that have not been yet decorated.
* @param streamings indexed streaming media to decorate.
* @throws SQLException on SQL error
*/
private static void decorateStreamings(List<Media> media, Map<String, Streaming> streamings)
throws SQLException {
if (!streamings.isEmpty()) {
Collection<Collection<String>> idGroups =
CollectionUtil.split(new ArrayList<>(streamings.keySet()));
String queryBase =
"S.mediaId, S.homepageUrl, S.provider from SC_Gallery_Streaming S where S.mediaId";
for (Collection<String> mediaIds : idGroups) {
select(queryBase).in(mediaIds).execute(row -> {
String mediaId = row.getString(1);
mediaIds.remove(mediaId);
Streaming currentStreaming = streamings.get(mediaId);
currentStreaming.setHomepageUrl(row.getString(2));
StreamingProvidersRegistry.get().getByName(row.getString(3))
.ifPresent(currentStreaming::setProvider);
return null;
});
// Not found
for (String mediaIdNotFound : mediaIds) {
Streaming currentStreaming = streamings.remove(mediaIdNotFound);
media.remove(currentStreaming);
SilverLogger.getLogger(MediaDAO.class)
.warn("Streaming not found (removed from result): {0}", mediaIdNotFound);
}
}
}
}
/**
* Centralization of internal media decoration.
* @param rsw the wrapper of the result set.
* @param iMedia the internal media instance to decorate.
*/
private static void decorateInternalMedia(ResultSetWrapper rsw, InternalMedia iMedia)
throws SQLException {
iMedia.setFileName(rsw.getString(2));
iMedia.setFileSize(rsw.getLong(3));
iMedia.setFileMimeType(MediaMimeType.fromMimeType(rsw.getString(4)));
iMedia.setDownloadAuthorized(rsw.getInt(5) == 1);
iMedia.setDownloadPeriod(getPeriod(rsw));
}
/**
* Counts media according to the given criteria.
* @param criteria the media criteria.
* @return the number of media count corresponding to the given criteria.
*/
public static long countByCriteria(final MediaCriteria criteria) throws SQLException {
MediaSQLQueryBuilder queryBuilder = MediaSQLQueryBuilder.countBuilder();
criteria.processWith(queryBuilder);
JdbcSqlQuery selectQuery = queryBuilder.result();
return selectQuery.executeUnique(row -> row.getLong(1));
}
/**
* Gets a period from the specified result set.
* @param rsw the wrapper of the result set.
* @return the period guessed from the given indexes of start and end date information.
* @throws SQLException on SQL data fetching
*/
private static Period getPeriod(ResultSetWrapper rsw)
throws SQLException {
Date begin = rsw.getDateFromLong(6);
if (begin == null) {
begin = DateUtil.MINIMUM_DATE;
}
Date end = rsw.getDateFromLong(7);
if (end == null) {
end = DateUtil.MAXIMUM_DATE;
}
return Period.check(Period.from(begin, end));
}
/**
* Saves (insert or update) a media.
* @param context the context of save operation.
* @param media the media to save.
* @return the id of the saved media.
* @throws SQLException on SQL error
*/
public static String saveMedia(OperationContext context, Media media) throws SQLException {
JdbcSqlQueries updateQueries = new JdbcSqlQueries();
// The current Uuid
String uuid = media.getId();
boolean isInsert = !isSqlDefined(uuid) ||
countAll().from(GALLERY_MEDIA_TABLE).where(MEDIA_ID_CRITERIA, uuid).execute() == 0;
// A new ID
if (isInsert) {
uuid = getUniqueId();
media.getMediaPK().setId(uuid);
}
// Media
updateQueries.add(prepareSaveMedia(context, media, isInsert));
// Photo
if (media.getType().isPhoto()) {
updateQueries.addAll(prepareSavePhoto(media.getPhoto(), isInsert));
}
// Video
if (media.getType().isVideo()) {
updateQueries.addAll(prepareSaveVideo(media.getVideo(), isInsert));
}
// Sound
if (media.getType().isSound()) {
updateQueries.addAll(prepareSaveSound(media.getSound(), isInsert));
}
// Streaming
if (media.getType().isStreaming()) {
updateQueries.add(prepareSaveStreaming(media.getStreaming(), isInsert));
}
// Execution of update queries
updateQueries.execute();
return uuid;
}
/**
* Prepares query and parameters in order to save a photo.
* @param photo the photo media to save.
* @param isInsert true to indicate an insert context, false to indicated an update one.
* @return the prepared query to save data at photo media level.
*/
private static List<JdbcSqlQuery> prepareSavePhoto(Photo photo, boolean isInsert) {
List<JdbcSqlQuery> updateQueries = new ArrayList<>();
updateQueries.add(prepareSaveInternalMedia(photo, isInsert));
final JdbcSqlQuery photoSave;
if (isInsert) {
photoSave = insertInto(GALLERY_PHOTO_TABLE);
photoSave.withInsertParam(MEDIA_ID_PARAM, photo.getId());
} else {
photoSave = update(GALLERY_PHOTO_TABLE);
}
Definition definition = photo.getDefinition();
photoSave.withSaveParam("resolutionW", definition.getWidth(), isInsert);
photoSave.withSaveParam("resolutionH", definition.getHeight(), isInsert);
if (!isInsert) {
photoSave.where(MEDIA_ID_CRITERIA, photo.getId());
}
updateQueries.add(photoSave);
return updateQueries;
}
/**
* Prepares query and parameters in order to save a video.
* @param video the video media to save.
* @param isInsert true to indicate an insert context, false to indicated an update one.
* @return the prepared query to save data at video media level.
*/
private static List<JdbcSqlQuery> prepareSaveVideo(Video video, boolean isInsert) {
List<JdbcSqlQuery> updateQueries = new ArrayList<>();
updateQueries.add(prepareSaveInternalMedia(video, isInsert));
final JdbcSqlQuery videoSave;
if (isInsert) {
videoSave = insertInto(GALLERY_VIDEO_TABLE);
videoSave.withInsertParam(MEDIA_ID_PARAM, video.getId());
} else {
videoSave = update(GALLERY_VIDEO_TABLE);
}
Definition definition = video.getDefinition();
videoSave.withSaveParam("resolutionW", definition.getWidth(), isInsert);
videoSave.withSaveParam("resolutionH", definition.getHeight(), isInsert);
videoSave.withSaveParam("bitrate", video.getBitrate(), isInsert);
videoSave.withSaveParam("duration", video.getDuration(), isInsert);
if (!isInsert) {
videoSave.where(MEDIA_ID_CRITERIA, video.getId());
}
updateQueries.add(videoSave);
return updateQueries;
}
/**
* Prepares query and parameters in order to save a sound.
* @param sound the sound media to save.
* @param isInsert true to indicate an insert context, false to indicated an update one.
* @return the prepared query to save data at sound media level.
*/
private static List<JdbcSqlQuery> prepareSaveSound(Sound sound, boolean isInsert) {
List<JdbcSqlQuery> updateQueries = new ArrayList<>();
updateQueries.add(prepareSaveInternalMedia(sound, isInsert));
final JdbcSqlQuery soundSave;
if (isInsert) {
soundSave = insertInto(GALLERY_SOUND_TABLE);
soundSave.withInsertParam(MEDIA_ID_PARAM, sound.getId());
} else {
soundSave = update(GALLERY_SOUND_TABLE);
}
soundSave.withSaveParam("bitrate", sound.getBitrate(), isInsert);
soundSave.withSaveParam("duration", sound.getDuration(), isInsert);
if (!isInsert) {
soundSave.where(MEDIA_ID_CRITERIA, sound.getId());
}
updateQueries.add(soundSave);
return updateQueries;
}
/**
* Prepares query and parameters in order to save a streaming.
* @param streaming the streaming to save.
* @param isInsert true to indicate an insert context, false to indicated an update one.
* @return the prepared query to save data at streaming media level.
*/
private static JdbcSqlQuery prepareSaveStreaming(Streaming streaming, boolean isInsert) {
final JdbcSqlQuery streamingSave;
if (isInsert) {
streamingSave = insertInto(GALLERY_STREAMING_TABLE);
streamingSave.withInsertParam(MEDIA_ID_PARAM, streaming.getId());
} else {
streamingSave = update(GALLERY_STREAMING_TABLE);
}
streamingSave.withSaveParam("homepageUrl", streaming.getHomepageUrl(), isInsert);
streamingSave.withSaveParam("provider", streaming.getProvider()
.map(StreamingProvider::getName)
.orElseThrow(() -> new IllegalArgumentException("Provider MUST exists")), isInsert);
if (!isInsert) {
streamingSave.where(MEDIA_ID_CRITERIA, streaming.getId());
}
return streamingSave;
}
/**
* Prepares query and parameters in order to save a media.
* @param context the context of save operation.
* @param media the media to save.
* @param isInsert true to indicate an insert context, false to indicated an update one.
* @return the prepared query to save data at common media level.
*/
private static JdbcSqlQuery prepareSaveMedia(OperationContext context, Media media,
boolean isInsert) {
final JdbcSqlQuery mediaSave;
if (isInsert) {
mediaSave = insertInto(GALLERY_MEDIA_TABLE);
mediaSave.withInsertParam(MEDIA_ID_PARAM, media.getId());
} else {
mediaSave = update(GALLERY_MEDIA_TABLE);
}
mediaSave.withSaveParam("mediaType", media.getType(), isInsert);
mediaSave.withSaveParam(INSTANCE_ID_PARAM, media.getInstanceId(), isInsert);
mediaSave.withSaveParam("title", media.getTitle(), isInsert);
mediaSave.withSaveParam("description", media.getDescription(), isInsert);
mediaSave.withSaveParam("author", media.getAuthor(), isInsert);
mediaSave.withSaveParam("keyword", media.getKeyWord(), isInsert);
mediaSave
.withSaveParam("beginVisibilityDate", media.getVisibilityPeriod().getBeginDate().getTime(),
isInsert);
mediaSave.withSaveParam("endVisibilityDate", media.getVisibilityPeriod().getEndDate().getTime(),
isInsert);
Timestamp saveDate = new Timestamp(new Date().getTime());
if (isInsert) {
media.setCreationDate(saveDate);
media.setCreator(context.getUser());
media.setLastUpdateDate(saveDate);
media.setLastUpdater(context.getUser());
mediaSave.withInsertParam("createDate", media.getCreationDate());
mediaSave.withInsertParam("createdBy", media.getCreatorId());
mediaSave.withInsertParam("lastUpdateDate", media.getLastUpdateDate());
mediaSave.withInsertParam("lastUpdatedBy", media.getLastUpdatedBy());
} else if (!context.isUpdatingInCaseOfCreation()) {
media.setLastUpdateDate(saveDate);
media.setLastUpdater(context.getUser());
mediaSave.withUpdateParam("lastUpdateDate", media.getLastUpdateDate());
mediaSave.withUpdateParam("lastUpdatedBy", media.getLastUpdatedBy());
}
if (!isInsert) {
mediaSave.where(MEDIA_ID_CRITERIA, media.getId());
}
return mediaSave;
}
/**
* Prepares query and parameters in order to save a media.
* @param iMedia the internal media to save.
* @param isInsert true to indicate an insert context, false to indicated an update one.
* @return the prepared query to save data at internal media level.
*/
private static JdbcSqlQuery prepareSaveInternalMedia(InternalMedia iMedia, boolean isInsert) {
final JdbcSqlQuery iMediaSave;
if (isInsert) {
iMediaSave = insertInto(GALLERY_INTERNAL_TABLE);
iMediaSave.withInsertParam(MEDIA_ID_PARAM, iMedia.getId());
} else {
iMediaSave = update(GALLERY_INTERNAL_TABLE);
}
iMediaSave.withSaveParam("fileName", iMedia.getFileName(), isInsert);
iMediaSave.withSaveParam("fileSize", iMedia.getFileSize(), isInsert);
iMediaSave.withSaveParam("fileMimeType", iMedia.getFileMimeType().getMimeType(), isInsert);
iMediaSave.withSaveParam("download", iMedia.isDownloadAuthorized() ? 1 : 0, isInsert);
Long beginDate = iMedia.getDownloadPeriod().getBeginDatable().isDefined() ?
iMedia.getDownloadPeriod().getBeginDate().getTime() : null;
iMediaSave.withSaveParam("beginDownloadDate", beginDate, isInsert);
Long endDate = iMedia.getDownloadPeriod().getEndDatable().isDefined() ?
iMedia.getDownloadPeriod().getEndDate().getTime() : null;
iMediaSave.withSaveParam("endDownloadDate", endDate, isInsert);
if (!isInsert) {
iMediaSave.where(MEDIA_ID_CRITERIA, iMedia.getId());
}
return iMediaSave;
}
/**
* Deletes the specified media (and its album links).
* @param media the media to delete.
* @throws SQLException on SQL error
*/
public static void deleteMedia(Media media) throws SQLException {
String mediaId = media.getId();
JdbcSqlQueries updateQueries = new JdbcSqlQueries();
updateQueries.add(deleteFrom(GALLERY_MEDIA_TABLE).where(MEDIA_ID_CRITERIA, mediaId));
if (MediaType.Photo == media.getType() || MediaType.Video == media.getType() ||
MediaType.Sound == media.getType()) {
updateQueries.add(deleteFrom(GALLERY_INTERNAL_TABLE).where(MEDIA_ID_CRITERIA, mediaId));
}
switch (media.getType()) {
case Photo:
updateQueries.add(deleteFrom(GALLERY_PHOTO_TABLE).where(MEDIA_ID_CRITERIA, mediaId));
break;
case Video:
updateQueries.add(deleteFrom(GALLERY_VIDEO_TABLE).where(MEDIA_ID_CRITERIA, mediaId));
break;
case Sound:
updateQueries.add(deleteFrom(GALLERY_SOUND_TABLE).where(MEDIA_ID_CRITERIA, mediaId));
break;
case Streaming:
updateQueries.add(deleteFrom(GALLERY_STREAMING_TABLE).where(MEDIA_ID_CRITERIA, mediaId));
break;
default:
SilverLogger.getLogger(MediaDAO.class).warn("Unknown media type: {0}", media.getId());
break;
}
updateQueries.execute();
deleteAllMediaPath(media);
}
/**
* Saves the album link for the given media.
* @param media the media that must be associated to the given album.
* @param albumId the identifier of the album.
* @throws SQLException on SQL error
*/
public static void saveMediaPath(Media media, String albumId) throws SQLException {
List<?> pathParams =
Arrays.asList(media.getId(), media.getInstanceId(), Integer.valueOf(albumId));
boolean isInsert = countAll().from(GALLERY_PATH_TABLE)
.where("mediaId = ? and instanceId = ? and nodeId = ?", pathParams).execute() == 0;
if (isInsert) {
Iterator<?> paramIt = pathParams.iterator();
JdbcSqlQuery insert = insertInto(GALLERY_PATH_TABLE);
insert.withInsertParam(MEDIA_ID_PARAM, paramIt.next());
insert.withInsertParam(INSTANCE_ID_PARAM, paramIt.next());
insert.withInsertParam("nodeId", paramIt.next());
insert.execute();
}
}
/**
* Deletes the album links of the specified media.
* @param media the media for which all album links must be deleted.
* @throws SQLException on SQL error
*/
public static void deleteAllMediaPath(Media media) throws SQLException {
deleteFrom(GALLERY_PATH_TABLE)
.where("mediaId = ? and instanceId = ?", media.getId(), media.getInstanceId()).execute();
}
/**
* Gets the identifier list of albums which the given media is associated to.
* @param media the media aimed.
* @return the identifier list of albums in which the given media is attached to.
* @throws SQLException on SQL error
*/
public static Collection<String> getAlbumIdsOf(Media media) throws SQLException {
return select("N.NodeId")
.from("SC_Gallery_Path P", "SB_Node_Node N")
.where("P.mediaId = ?", media.getId())
.and("N.nodeId = P.NodeId")
.and("P.instanceId = ?", media.getInstanceId())
.and("N.instanceId = P.instanceId")
.execute(row -> String.valueOf(row.getInt(1)));
}
/**
* get my SocialInformationGallery according to the type of data base used (PostgresSQL,Oracle,
* MMS).
* @param userId the identifier of a user.
* @param period the period on which the data are requested.
* @return List<SocialInformation>
* @throws SQLException on SQL error
*/
public static List<SocialInformation> getAllMediaIdByUserId(String userId, Period period)
throws SQLException {
return create(
"(select createDate AS dateinformation, mediaId, 'new' as type from SC_Gallery_Media " +
"where createdBy = ? and createDate >= ? and createDate <= ? ) " +
"union (select lastUpdateDate AS dateinformation, mediaId , " +
"'update' as type from SC_Gallery_Media where lastUpdatedBy = ? and lastUpdateDate <>" +
" createDate and lastUpdateDate >= ? and lastUpdateDate <= ? ) order by " +
"dateinformation desc, mediaId desc", userId, period.getBeginDatable(),
period.getEndDatable(), userId, period.getBeginDatable(), period.getEndDatable()).execute(row -> {
Media media = getByCriteria(MediaCriteria.fromMediaId(row.getString(2))
.withVisibility(MediaCriteria.VISIBILITY.FORCE_GET_ALL));
MediaWithStatus withStatus =
new MediaWithStatus(media, "update".equalsIgnoreCase(row.getString(3)));
return new SocialInformationGallery(withStatus);
});
}
/**
* get list of socialInformationGallery of my contacts according to the type of data base
* used(PostgresSQL,Oracle,MMS) .
* @param userIds the identifiers of users.
* @param availableComponents the list of available components.
* @param period the period on which the data are requested.
* @return the information for social data.
* @throws SQLException on SQL error
*/
public static List<SocialInformation> getSocialInformationListOfMyContacts(List<String> userIds,
List<String> availableComponents, Period period) throws SQLException {
JdbcSqlQuery query = create("(select createDate as dateinformation, mediaId, 'new' as type");
query.addSqlPart("from SC_Gallery_Media where createdBy").in(userIds);
query.and(INSTANCE_ID_PARAM).in(availableComponents);
query.and("createDate >= ? and createDate <= ?)", period.getBeginDatable(),
period.getEndDatable());
query.addSqlPart("union (select lastUpdateDate as dateinformation, mediaId, 'update' as type");
query.addSqlPart("from SC_Gallery_Media where lastUpdatedBy").in(userIds);
query.and(INSTANCE_ID_PARAM).in(availableComponents);
query.and("lastUpdateDate <> createDate");
query.and("lastUpdateDate >= ? and lastUpdateDate <= ?)", period.getBeginDatable(),
period.getEndDatable());
query.addSqlPart("order by dateinformation desc, mediaId desc");
return query.execute(row -> {
Media media = getByCriteria(MediaCriteria.fromMediaId(row.getString(2))
.withVisibility(MediaCriteria.VISIBILITY.FORCE_GET_ALL));
MediaWithStatus withStatus =
new MediaWithStatus(media, "update".equalsIgnoreCase(row.getString(3)));
return new SocialInformationGallery(withStatus);
});
}
private static class MediaInfo {
private final String mediaId;
private final MediaType mediaType;
private final String instanceId;
public MediaInfo(final String mediaId, final MediaType mediaType, final String instanceId) {
this.mediaId = mediaId;
this.mediaType = mediaType;
this.instanceId = instanceId;
}
public String getMediaId() {
return mediaId;
}
public MediaType getMediaType() {
return mediaType;
}
public String getInstanceId() {
return instanceId;
}
}
}