/// <summary> /// データを削除するSQLを生成する。 /// </summary> /// <param name="mode"></param> /// <param name="id"></param> /// <param name="speciallistOnly"></param> /// <returns></returns> public static string deleteRecord(LinearEnum.PlaylistMode mode, long id, bool speciallistOnly) { StringBuilder sb = new StringBuilder(); string targetTable = ""; switch (mode) { case LinearEnum.PlaylistMode.NORMAL: targetTable = PLAYLIST_TABLE_NAME; break; default: //targetTable = SPECIALLIST_TABLE_NAME; break; } sb.Append("DELETE FROM "); sb.Append(targetTable); sb.Append(" WHERE ID = "); sb.Append(id.ToString()); if (speciallistOnly) { sb.Append(" AND CLASS = "); sb.Append(((int)mode).ToString()); } return(sb.ToString()); }
public static string getRatingWhereString(LinearEnum.PlaylistMode playlistMode) { switch (playlistMode) { case LinearEnum.PlaylistMode.NORMAL: return("RATING > 0 "); case LinearEnum.PlaylistMode.FAVORITE: return("RATING = 9 "); case LinearEnum.PlaylistMode.EXCLUSION: return("RATING = 0 "); } return(""); }
/* * パブリックメソッド */ #region Public Method /// <summary> /// プレイリスト取得用SQL生成 /// </summary> /// <param name="baseSql"></param> /// <param name="playlistMode"></param> /// <param name="filterString"></param> /// <returns></returns> public static string selectPlaylist( string baseSql, LinearEnum.PlaylistMode playlistMode, string filterString, LinearEnum.FilteringMode filteringMode, ConditionGridItemInfo conditionItem) { bool isNotOrder = false; bool isFullSQL = false; StringBuilder sb = new StringBuilder(); //sb.Append(baseSql); // プレイリストによる絞り込み sb.Append(WHERE_STRING); sb.Append(getRatingWhereString(playlistMode)); if (!String.IsNullOrEmpty(filterString)) { sb.Append(AND_STRING); if (filterString.Length < 3 || !Regex.IsMatch(filterString, @"^[a-zA-Z0-9 \-]+$")) { sb.Append( "IFNULL(TITLE,'') || IFNULL(ARTIST,'') || IFNULL(ALBUM,'') || IFNULL(TAG,'') LIKE '%" + escapeSQL(filterString) + "%'"); } else { sb.Append( "(( IFNULL(TITLE,'') || IFNULL(ARTIST,'') || IFNULL(ALBUM,'') || IFNULL(TAG,'') LIKE '%" + escapeSQL(filterString) + "%' ) OR ("); sb.Append("ISMATCHMIGEMO('" + escapeSQL(filterString) + "',IFNULL(TITLE,'') || IFNULL(ARTIST,'') || IFNULL(ALBUM,'') || IFNULL(TAG,'')) ))"); } //sb.Append(AND_STRING); } // コンディションによる絞り込み switch (filteringMode) { case LinearEnum.FilteringMode.DEFAULT: // カスタム if (!String.IsNullOrEmpty(conditionItem.Value)) { if (!(conditionItem.Value.Substring(0, 3).ToUpper().Equals("AND") || conditionItem.Value.Substring(0, 2).ToUpper().Equals("OR"))) { isFullSQL = true; } sb.Append(" "); string customSql = conditionItem.Value; if (!StringUtils.hasString(customSql, "#LIMIT#") || LinearGlobal.LinearConfig.DatabaseConfig.LimitCount == 0) { customSql = customSql.Replace("#LIMIT#", ""); } else { customSql = customSql.Replace( "#LIMIT#", " LIMIT " + LinearGlobal.LinearConfig.DatabaseConfig.LimitCount.ToString()); } sb.Append(customSql); isNotOrder = true; } break; case LinearEnum.FilteringMode.TAG: if (conditionItem.Value != null) { sb.Append(AND_STRING); if (!String.IsNullOrEmpty(conditionItem.Value)) { sb.Append("ISMATCHTAG(TAG, '" + escapeSQL(conditionItem.Value) + "')"); } else { sb.Append("("); sb.Append(filteringMode.ToString() + " = ''"); sb.Append(OR_STRING); sb.Append(filteringMode.ToString() + " IS NULL"); sb.Append(")"); } } break; case LinearEnum.FilteringMode.FOLDER: if (conditionItem.Value != null) { sb.Append(AND_STRING); if (!String.IsNullOrEmpty(conditionItem.Value)) { sb.Append("GETDIRNAME(FILEPATH) = '" + escapeSQL(conditionItem.Value) + "'"); } else { sb.Append("GETDIRNAME(FILEPATH) = ''"); sb.Append(OR_STRING); sb.Append("GETDIRNAME(FILEPATH) IS NULL"); } } break; default: if (conditionItem.Value != null) { sb.Append(AND_STRING); if (!String.IsNullOrEmpty(conditionItem.Value)) { if (filteringMode != LinearEnum.FilteringMode.GENRE) { sb.Append(filteringMode.ToString() + " = '" + escapeSQL(conditionItem.Value) + "'"); } else { // ジャンルだけ大文字でマッチングする sb.Append("trim(upper(" + filteringMode.ToString() + ")) = '" + escapeSQL(conditionItem.Value) + "'"); } } else { sb.Append(filteringMode.ToString() + " = ''"); sb.Append(OR_STRING); sb.Append(filteringMode.ToString() + " IS NULL"); } if (filteringMode == LinearEnum.FilteringMode.ALBUM) { sb.Append("ORDER BY ABS(PL.TRACK)"); isNotOrder = true; } } break; } LAST_WHERE_SQL = sb.ToString(); if (!isNotOrder) { if (LinearGlobal.SortMode == LinearEnum.SortMode.DEFAULT) { sb.Append(ORDERBY_STRING); } else { if (LinearGlobal.FilteringMode == LinearEnum.FilteringMode.ARTIST && LinearGlobal.ShuffleMode == LinearEnum.ShuffleMode.OFF) { sb.Append(ORDERBY_STRING_ORIGINAL_SORT); } else { sb.Append(ORDERBY_STRING); } } LAST_WHERE_SQL = sb.ToString(); } if (isFullSQL) { return(baseSql + " " + conditionItem.Value); } if (LinearGlobal.ShuffleMode == LinearEnum.ShuffleMode.OFF) { LAST_WHERE_SQL = LAST_WHERE_SQL.Replace("#NOSHUFFLE#", ""); return(baseSql + LAST_WHERE_SQL); } else { return(SQLResource.SQL029.Replace("#SQL#", baseSql + LAST_WHERE_SQL)); } }
/// <summary> /// フィルタリングコンボ選択用SQL /// </summary> /// <param name="playlistMode"></param> /// <param name="condtionModeName"></param> /// <returns></returns> public static string selectConditionList( LinearEnum.PlaylistMode playlistMode, LinearEnum.FilteringMode condtionMode, string filterString) { string conditionModeName; if (condtionMode != LinearEnum.FilteringMode.FOLDER) { conditionModeName = Enum.GetName(typeof(LinearEnum.FilteringMode), condtionMode); } else { conditionModeName = "GETDIRNAME(FILEPATH)"; } string ratingCondition = getRatingWhereString(playlistMode); string result = ""; if (condtionMode != LinearEnum.FilteringMode.GENRE) { result = SQLResource.SQL012.Replace("#condMode#", conditionModeName) .Replace("#RATING#", ratingCondition); } else { // ジャンルだけすべて大文字でグループ化する result = SQLResource.SQL012.Replace("#condMode#", "trim(upper(" + conditionModeName + "))") .Replace("#RATING#", ratingCondition); } if (!String.IsNullOrEmpty(filterString)) { if (filterString.Length < 3 || !Regex.IsMatch(filterString, @"^[a-zA-Z0-9 \-]+$")) { result = result.Replace("#FILTERING#", AND_STRING + "IFNULL(" + conditionModeName + ",'') LIKE '%" + escapeSQL(filterString) + "%' "); } else { result = result.Replace("#FILTERING#", AND_STRING + "(( IFNULL(" + conditionModeName + ",'') LIKE '%" + escapeSQL(filterString) + "%' ) OR (" + "ISMATCHMIGEMO('" + escapeSQL(filterString) + "',IFNULL(" + conditionModeName + ",'')) ))"); } } else { result = result.Replace("#FILTERING#", ""); } if (LinearEnum.FilteringMode.ALBUM.ToString().Equals(conditionModeName.ToUpper())) { result = result.Replace("#condCount#", LinearGlobal.LinearConfig.ViewConfig.AlbumJudgeCount.ToString()); } else { result = result.Replace("#condCount#", "2"); } if (LinearGlobal.LinearConfig.ViewConfig.GroupListOrder == (int)LinearEnum.EnumGroupListOrder.COUNT_DESC) { switch (condtionMode) { case LinearEnum.FilteringMode.ARTIST: case LinearEnum.FilteringMode.GENRE: case LinearEnum.FilteringMode.TAG: case LinearEnum.FilteringMode.FOLDER: result += " ORDER BY COUNT(*) DESC"; break; case LinearEnum.FilteringMode.ALBUM: result += " ORDER BY MAX(DATE) DESC"; break; case LinearEnum.FilteringMode.YEAR: result += " ORDER BY " + conditionModeName + " DESC"; break; } } else { switch (condtionMode) { case LinearEnum.FilteringMode.ARTIST: result += " ORDER BY ARTIST ASC"; break; case LinearEnum.FilteringMode.GENRE: result += " ORDER BY GENRE ASC"; break; case LinearEnum.FilteringMode.TAG: result += " ORDER BY TAG ASC"; break; case LinearEnum.FilteringMode.FOLDER: result += " ORDER BY GETDIRNAME(FILEPATH) ASC"; break; case LinearEnum.FilteringMode.ALBUM: result += " ORDER BY ALBUM ASC"; break; case LinearEnum.FilteringMode.YEAR: result += " ORDER BY YEAR ASC"; break; } } return(result); }