public ArtistItem GetArtistsByName(string name)
{
ArtistItem res = new ArtistItem();
res = DatabaseProvider.InstanInstance.GetArtistsByName(name);
if (res != null)
{
return(res);
}
res = new ArtistItem();
string lsSQL = string.Format("select * from ARTISTS WHERE ARTIST_NAME like '{0}' order by ARTIST_NAME",
DatabaseUtility.RemoveInvalidChars(name));
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Id = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID");
res.Name = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_NAME");
res.Img_url = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG");
res.User = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_USER");
res.Tags = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_TAG");
break;
}
return(res);
}
public List <string> GetArtistsLetters(string letter)
{
List <string> res = new List <string>();
string lsSQL =
string.Format(
"select distinct upper(substr(ARTIST_NAME,1,{0})) AS LETTER from ARTISTS WHERE ARTIST_NAME like '{1}%' order by upper(ARTIST_NAME)",
letter.Length + 1, letter);
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
string oldvalue = "";
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
string l = DatabaseUtility.Get(loResultSet, iRow, "LETTER");
if (l.Length == 1)
{
l += " ";
}
if (l != oldvalue)
{
res.Add(l);
}
oldvalue = l;
}
return(res);
}
public string GetPlayedArtistIds(int numPlay)
{
List <string> ids = new List <string>();
string lsSQL =
string.Format(
"select * from (SELECT VIDEOS.VIDEO_ID AS VIDEO_ID, ARTIST_ID, TITLE, IMG_URL, count(PLAY_HISTORY.VIDEO_ID) as num_play FROM VIDEOS, PLAY_HISTORY WHERE VIDEOS.VIDEO_ID=PLAY_HISTORY.VIDEO_ID group by VIDEOS.VIDEO_ID, ARTIST_ID, TITLE, IMG_URL order by count(PLAY_HISTORY.VIDEO_ID) desc)where num_play>" +
numPlay.ToString());
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
string id = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID");
if (!string.IsNullOrEmpty(id) && !ids.Contains(id))
{
ids.Add(id);
}
}
string ret = "";
for (int i = 0; i < ids.Count; i++)
{
ret += "'" + ids[i] + "'";
if (i != ids.Count - 1)
{
ret += ",";
}
}
return(ret);
}
public int ListPicsByDate(string Date, ref List <string> Pics)
{
int Count = 0;
lock (typeof(PictureDatabase))
{
if (m_db == null)
{
return(0);
}
string strSQL = "select strFile from picture where strDateTaken like '" + Date + "%' order by 1";
SQLiteResultSet result;
try
{
result = m_db.Execute(strSQL);
if (result != null)
{
for (Count = 0; Count < result.Rows.Count; Count++)
{
Pics.Add(DatabaseUtility.Get(result, Count, 0));
}
}
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception getting Picture by Date err:{0} stack:{1}", ex.Message,
ex.StackTrace);
Open();
}
return(Count);
}
}
public ArtistItem GetArtist(YouTubeEntry entry)
{
ArtistItem artistItem = new ArtistItem();
string lsSQL = string.Format("select distinct ARTIST_ID from VIDEOS WHERE VIDEO_ID=\"{0}\" ", Youtube2MP.GetVideoId(entry));
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
artistItem = ArtistManager.Instance.GetArtistsById(DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID"));
break;
}
if (string.IsNullOrEmpty(artistItem.Name))
{
artistItem.Name = ArtistManager.Instance.GetArtistName(entry.Title.Text);
}
ArtistItem artistItemLocal = GetArtistsByName(artistItem.Name);
if (artistItemLocal != null)
{
artistItem.Bio = artistItemLocal.Bio;
artistItem.Id = string.IsNullOrEmpty(artistItem.Id) ? artistItemLocal.Id : artistItem.Id;
artistItem.Img_url = !string.IsNullOrEmpty(artistItemLocal.Img_url) ? artistItemLocal.Img_url : artistItem.Img_url;
artistItem.Name = string.IsNullOrEmpty(artistItem.Name) ? artistItemLocal.Name : artistItem.Name;
artistItem.Tags = string.IsNullOrEmpty(artistItem.Tags) ? artistItemLocal.Tags : artistItem.Tags;
artistItem.User = string.IsNullOrEmpty(artistItem.User) ? artistItemLocal.User : artistItem.User;
}
return(artistItem);
}
public void AddArtist(ArtistItem artistItem)
{
try
{
if (string.IsNullOrEmpty(artistItem.Id))
{
return;
}
string lsSQL = string.Format("select distinct ARTIST_ID,ARTIST_IMG from ARTISTS WHERE ARTIST_ID=\"{0}\"", artistItem.Id);
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
if (loResultSet.Rows.Count > 0)
{
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
artistItem.Img_url = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG");
artistItem.Tags = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_TAG");
}
return;
}
lsSQL =
string.Format(
"insert into ARTISTS (ARTIST_ID,ARTIST_NAME,ARTIST_IMG, ARTIST_USER, ARTIST_TAG) VALUES ('{0}','{1}','{2}','{3}','{4}')",
artistItem.Id,
DatabaseUtility.RemoveInvalidChars(artistItem.Name), artistItem.Img_url, artistItem.User,
DatabaseUtility.RemoveInvalidChars(artistItem.Tags));
m_db.Execute(lsSQL);
artistItem.Db_id = m_db.LastInsertID();
}
catch (Exception)
{
}
}
public int GetRotation(string strPicture)
{
if (m_db == null)
{
return(-1);
}
try
{
string strPic = strPicture;
int iRotation = 0;
DatabaseUtility.RemoveInvalidChars(ref strPic);
SQLiteResultSet results =
m_db.Execute(String.Format("select strFile, iRotation from picture where strFile like '{0}'", strPic));
if (results != null && results.Rows.Count > 0)
{
iRotation = Int32.Parse(DatabaseUtility.Get(results, 0, 1));
return(iRotation);
}
AddPicture(strPicture, iRotation);
return(iRotation);
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
Open();
}
return(0);
}
public void GetPath(string strPath, ref ArrayList strPathList, string strKey)
{
if (string.IsNullOrEmpty(strKey))
{
return;
}
if (m_db == null)
{
return;
}
try
{
string sql = string.Format(
"SELECT strPath from tblPath where strPath like '{0}%' and idPath in (SELECT idPath from tblSetting where tblSetting.idPath = tblPath.idPath and tblSetting.tagName = '{1}')"
, strPath, strKey);
SQLiteResultSet results = m_db.Execute(sql);
if (results.Rows.Count == 0)
{
return;
}
for (int iRow = 0; iRow < results.Rows.Count; iRow++)
{
strPathList.Add(DatabaseUtility.Get(results, iRow, "strPath"));
}
}
catch (Exception ex)
{
Log.Error("FolderDatabase.GetPath() exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public int ListDays(string Month, string Year, ref List <string> Days)
{
int Count = 0;
lock (typeof(PictureDatabase))
{
if (m_db == null)
{
return(0);
}
string strSQL = "select distinct substr(strDateTaken,9,2) from picture where strDateTaken like '" + Year + "-" +
Month + "%' order by 1";
SQLiteResultSet result;
try
{
result = m_db.Execute(strSQL);
if (result != null)
{
for (Count = 0; Count < result.Rows.Count; Count++)
{
Days.Add(DatabaseUtility.Get(result, Count, 0));
}
}
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception getting Days err:{0} stack:{1}", ex.Message, ex.StackTrace);
Open();
}
return(Count);
}
}
public static Game[] getGames(String path, Emulator emulator, SearchOption recursive, bool showHidden)
{
List <Game> items = new List <Game>();
SQLiteResultSet result = dbExecute("SELECT * FROM Games WHERE parentemu = " + emulator.UID.ToString());
List <string> dbPaths = new List <string>();
for (int i = 0; i < result.Rows.Count; i++)
{
dbPaths.Add(DatabaseUtility.Get(result, i, 1)); //store list of paths already in DB
}
if (!Directory.Exists(path)) //Rom directory doesn't exist, skip
{
Logger.LogError("Error locating Rom directory for '{0}'", emulator.Title);
return(items.ToArray());
}
foreach (String filter in emulator.Filter.Split(';')) //search directory against each filter
{
string[] gamePaths;
try
{
gamePaths = Directory.GetFiles(path, filter, recursive); //get list of matches
}
catch (Exception ex)
{
Logger.LogError("Error getting files from '{0}' rom directory - {1}", emulator, ex.Message);
continue;
}
foreach (String gamePath in gamePaths)
{
Game item;
if (dbPaths.Contains(gamePath)) //already in database
{
item = createGame(result, dbPaths.IndexOf(gamePath), emulator);
}
else
{
item = getGame(gamePath, emulator); //add new item to database
}
if (item.Visible || showHidden)
{
items.Add(item);
}
}
}
String[] keys = new String[items.Count];
for (int i = 0; i < items.Count; i++)
{
keys[i] = items[i].Title; //Sort items by title
}
Game[] listToReturn = items.ToArray();
Array.Sort(keys, listToReturn);
return(listToReturn);
}
public static List <string> GetAllPaths()
{
SQLiteResultSet result = dbExecute("SELECT path FROM Games");
List <string> dbPaths = new List <string>();
for (int i = 0; i < result.Rows.Count; i++)
{
dbPaths.Add(decode(DatabaseUtility.Get(result, i, 0))); //store list of paths already in DB
}
return(dbPaths);
}
public List <VideoInfo> GetFavoriteVideos(string siteName, string fsQuery)
{
string lsSQL = "select * from favorite_videos";
if (!string.IsNullOrEmpty(siteName))
{
DatabaseUtility.RemoveInvalidChars(ref siteName);
lsSQL += string.Format(" where VDO_SITE_ID='{0}'", siteName);
}
if (!string.IsNullOrEmpty(fsQuery))
{
if (string.IsNullOrEmpty(siteName))
{
lsSQL += string.Format(" where VDO_NM like '%{0}%' or VDO_DESC like '%{0}%'", fsQuery);
}
else
{
lsSQL += string.Format(" and (VDO_NM like '%{0}%' or VDO_DESC like '%{0}%')", fsQuery);
}
}
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
List <VideoInfo> loFavoriteList = new List <VideoInfo>();
if (loResultSet.Rows.Count == 0)
{
return(loFavoriteList);
}
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
var video = CrossDomain.OnlineVideosAppDomain.Domain.CreateInstanceAndUnwrap(typeof(FavoriteDbVideoInfo).Assembly.FullName, typeof(FavoriteDbVideoInfo).FullName) as FavoriteDbVideoInfo;
video.Description = DatabaseUtility.Get(loResultSet, iRow, "VDO_DESC");
video.Thumb = DatabaseUtility.Get(loResultSet, iRow, "VDO_IMG_URL");
video.Length = DatabaseUtility.Get(loResultSet, iRow, "VDO_LENGTH");
video.Airdate = DatabaseUtility.Get(loResultSet, iRow, "VDO_TAGS");
video.Title = DatabaseUtility.Get(loResultSet, iRow, "VDO_NM");
video.VideoUrl = DatabaseUtility.Get(loResultSet, iRow, "VDO_URL");
video.SetOtherFromString(DatabaseUtility.Get(loResultSet, iRow, "VDO_OTHER_NFO"));
video.Id = DatabaseUtility.GetAsInt(loResultSet, iRow, "VDO_ID");
video.SiteName = DatabaseUtility.Get(loResultSet, iRow, "VDO_SITE_ID");
Log.Instance.Debug("Pulled '{0}' out of the database", video.Title);
if (OnlineVideoSettings.Instance.SiteUtilsList.ContainsKey(video.SiteName))
{
SiteSettings aSite = OnlineVideoSettings.Instance.SiteUtilsList[video.SiteName].Settings;
if (aSite.IsEnabled && (!aSite.ConfirmAge || !OnlineVideoSettings.Instance.UseAgeConfirmation || OnlineVideoSettings.Instance.AgeConfirmed))
{
loFavoriteList.Add(video);
}
}
}
return(loFavoriteList);
}
public List <string> GetArtistsLetters()
{
List <string> res = new List <string>();
string lsSQL = string.Format("select distinct upper(substr(ARTIST_NAME,1,1)) AS LETTER from ARTISTS order by ARTIST_NAME");
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Add(DatabaseUtility.Get(loResultSet, iRow, "LETTER"));
}
return(res);
}
public List <string> GetFavoriteCategoriesNames(string siteName)
{
DatabaseUtility.RemoveInvalidChars(ref siteName);
List <string> results = new List <string>();
SQLiteResultSet resultSet = m_db.Execute(string.Format("select CAT_Hierarchy from Favorite_Categories where CAT_SITE_ID = '{0}'", siteName));
for (int iRow = 0; iRow < resultSet.Rows.Count; iRow++)
{
results.Add(DatabaseUtility.Get(resultSet, iRow, "CAT_Hierarchy"));
}
return(results);
}
public List <string[]> GetTags()
{
List <string[]> res = new List <string[]>();
string lsSQL = string.Format(" select * from (" +
"select lower(artist_tag) as tag, count(ARTIST_TAG) as cnt from TAGS group by ARTIST_TAG order by count(ARTIST_TAG) desc" +
") where cnt>50 order by tag");
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Add(new string[]
{
DatabaseUtility.Get(loResultSet, iRow, "tag"), DatabaseUtility.Get(loResultSet, iRow, "cnt")
});
}
return(res);
}
static EmulatorProfile createProfile(SQLiteResultSet result, int rowNum)
{
EmulatorProfile profile = new EmulatorProfile(null, false);
profile.ID = DatabaseUtility.GetAsInt(result, rowNum, 0);
profile.Title = decode(DatabaseUtility.Get(result, rowNum, 1));
profile.EmulatorID = DatabaseUtility.GetAsInt(result, rowNum, 2);
profile.EmulatorPath = decode(DatabaseUtility.Get(result, rowNum, 3));
profile.WorkingDirectory = decode(DatabaseUtility.Get(result, rowNum, 4));
profile.UseQuotes = Boolean.Parse(DatabaseUtility.Get(result, rowNum, 5));
profile.Arguments = decode(DatabaseUtility.Get(result, rowNum, 6));
profile.SuspendMP = Boolean.Parse(DatabaseUtility.Get(result, rowNum, 7));
profile.GoodMergePref1 = decode(DatabaseUtility.Get(result, rowNum, 8));
profile.GoodMergePref2 = decode(DatabaseUtility.Get(result, rowNum, 9));
profile.GoodMergePref3 = decode(DatabaseUtility.Get(result, rowNum, 10));
profile.MountImages = Boolean.Parse(DatabaseUtility.Get(result, rowNum, 11));
return(profile);
}
public List <KeyValuePair <string, uint> > GetSiteIds()
{
string lsSQL = @"select distinct VDO_SITE_ID, max(NumVideos) as NumVideos from
(
select VDO_SITE_ID, count(*) as NumVideos from Favorite_Videos group by VDO_SITE_ID
UNION
select CAT_SITE_ID, 0 from Favorite_Categories
)
group by VDO_SITE_ID";
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
List <KeyValuePair <string, uint> > siteIdList = new List <KeyValuePair <string, uint> >();
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
siteIdList.Add(new KeyValuePair <string, uint>(DatabaseUtility.Get(loResultSet, iRow, "VDO_SITE_ID"), (uint)DatabaseUtility.GetAsInt(loResultSet, iRow, "NumVideos")));
}
return(siteIdList);
}
public ArtistItem GetArtistsById(string id)
{
ArtistItem res = new ArtistItem();
string lsSQL = string.Format("select * from ARTISTS WHERE ARTIST_ID = '{0}' order by ARTIST_NAME", id);
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Id = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID");
res.Name = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_NAME");
res.Img_url = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG");
res.User = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_USER");
res.Tags = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_TAG");
}
;
return(res);
}
public GenericListItemCollections GetRecentlyPlayed()
{
GenericListItemCollections res = new GenericListItemCollections();
try
{
string lsSQL =
string.Format(
"SELECT VIDEOS.VIDEO_ID AS VIDEO_ID, ARTIST_ID, TITLE, IMG_URL FROM VIDEOS, PLAY_HISTORY WHERE VIDEOS.VIDEO_ID=PLAY_HISTORY.VIDEO_ID order by datePlayed DESC");
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
string lastid = "";
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
YouTubeEntry youTubeEntry = new YouTubeEntry();
if (lastid == DatabaseUtility.Get(loResultSet, iRow, "VIDEO_ID"))
{
continue;
}
lastid = DatabaseUtility.Get(loResultSet, iRow, "VIDEO_ID");
youTubeEntry.AlternateUri =
new AtomUri("http://www.youtube.com/watch?v=" + DatabaseUtility.Get(loResultSet, iRow, "VIDEO_ID"));
youTubeEntry.Title = new AtomTextConstruct();
youTubeEntry.Title.Text = DatabaseUtility.Get(loResultSet, iRow, "TITLE");
youTubeEntry.Media = new MediaGroup();
youTubeEntry.Media.Description = new MediaDescription("");
youTubeEntry.Id = new AtomId(youTubeEntry.AlternateUri.Content);
GenericListItem listItem = new GenericListItem()
{
Title = youTubeEntry.Title.Text,
IsFolder = false,
LogoUrl = DatabaseUtility.Get(loResultSet, iRow, "IMG_URL"),
Tag = youTubeEntry,
//ParentTag = artistItem
};
res.Items.Add(listItem);
}
;
}
catch (Exception exception)
{
Log.Error(exception);
}
return(res);
}
public List <ArtistItem> GetArtists()
{
List <ArtistItem> res = new List <ArtistItem>();
string lsSQL = string.Format("select * from ARTISTS order by ARTIST_NAME");
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Add(new ArtistItem()
{
Id = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID"),
Name = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_NAME"),
Img_url = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG"),
User = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_USER"),
Tags = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_TAG")
});
}
return(res);
}
public int GetRotation(string strPicture)
{
// Continue only if it's a picture files
if (!Util.Utils.IsPicture(strPicture))
{
return(-1);
}
if (m_db == null)
{
return(-1);
}
try
{
string strPic = strPicture;
int iRotation = 0;
DatabaseUtility.RemoveInvalidChars(ref strPic);
SQLiteResultSet results =
m_db.Execute(String.Format("select strFile, iRotation from picture where strFile like '{0}'", strPic));
if (results != null && results.Rows.Count > 0)
{
iRotation = Int32.Parse(DatabaseUtility.Get(results, 0, 1));
return(iRotation);
}
if (_useExif)
{
iRotation = Util.Picture.GetRotateByExif(strPicture);
Log.Debug("PictureDatabaseSqlLite: GetRotateByExif = {0} for {1}", iRotation, strPicture);
}
AddPicture(strPicture, iRotation);
return(iRotation);
}
catch (Exception ex)
{
Log.Error("MediaPortal.Picture.Database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
Open();
}
return(0);
}
public List <FavoriteDbCategory> GetFavoriteCategories(string siteName)
{
DatabaseUtility.RemoveInvalidChars(ref siteName);
var results = new List <FavoriteDbCategory>();
SQLiteResultSet resultSet = m_db.Execute(string.Format("select * from Favorite_Categories where CAT_SITE_ID = '{0}'", siteName));
for (int iRow = 0; iRow < resultSet.Rows.Count; iRow++)
{
results.Add(
new FavoriteDbCategory()
{
Name = DatabaseUtility.Get(resultSet, iRow, "CAT_Name"),
Description = DatabaseUtility.Get(resultSet, iRow, "CAT_Desc"),
Thumb = DatabaseUtility.Get(resultSet, iRow, "CAT_ThumbUrl"),
Id = DatabaseUtility.GetAsInt(resultSet, iRow, "CAT_ID"),
RecursiveName = DatabaseUtility.Get(resultSet, iRow, "CAT_Hierarchy")
});
}
return(results);
}
public string GetArtistsImgUrl(string letter)
{
try
{
if (string.IsNullOrEmpty(letter))
{
return(string.Empty);
}
string lsSQL = string.Format("select * from ARTISTS WHERE ARTIST_NAME like '{0}' order by ARTIST_NAME", DatabaseUtility.RemoveInvalidChars(letter));
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
return(DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG"));
}
}
catch (Exception exception)
{
Log.Error(exception);
}
return(string.Empty);
}
public GenericListItemCollections GetTopPlayed(int numPlay)
{
GenericListItemCollections res = new GenericListItemCollections();
try
{
string lsSQL =
string.Format(
"select * from (SELECT VIDEOS.VIDEO_ID AS VIDEO_ID, ARTIST_ID, TITLE, IMG_URL, count(PLAY_HISTORY.VIDEO_ID) as num_play FROM VIDEOS, PLAY_HISTORY WHERE VIDEOS.VIDEO_ID=PLAY_HISTORY.VIDEO_ID group by VIDEOS.VIDEO_ID, ARTIST_ID, TITLE, IMG_URL order by count(PLAY_HISTORY.VIDEO_ID) desc)where num_play>" +
numPlay.ToString());
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
YouTubeEntry youTubeEntry = new YouTubeEntry();
youTubeEntry.AlternateUri = new AtomUri("http://www.youtube.com/watch?v=" + DatabaseUtility.Get(loResultSet, iRow, "VIDEO_ID"));
youTubeEntry.Title = new AtomTextConstruct();
youTubeEntry.Title.Text = DatabaseUtility.Get(loResultSet, iRow, "TITLE");
youTubeEntry.Media = new MediaGroup();
youTubeEntry.Media.Description = new MediaDescription("");
youTubeEntry.Id = new AtomId(youTubeEntry.AlternateUri.Content);
GenericListItem listItem = new GenericListItem()
{
Title = youTubeEntry.Title.Text,
IsFolder = false,
LogoUrl = DatabaseUtility.Get(loResultSet, iRow, "IMG_URL"),
Tag = youTubeEntry,
Title2 = DatabaseUtility.Get(loResultSet, iRow, "num_play"),
//ParentTag = artistItem
};
res.Items.Add(listItem);
}
;
}
catch (Exception exception)
{
Log.Error(exception);
}
return(res);
}
public static Emulator getEmulator(int uid)
{
if (uid == -1)
{
return(getPCDetails());
}
SQLiteResultSet result = dbExecute("SELECT * FROM Emulators WHERE uid=" + uid);
if (result.Rows.Count > 0)
{
Emulator item = new Emulator();
item.UID = DatabaseUtility.GetAsInt(result, 0, 0);
item.PathToEmulator = decode(DatabaseUtility.Get(result, 0, 1));
item.Position = DatabaseUtility.GetAsInt(result, 0, 2);
item.PathToRoms = decode(DatabaseUtility.Get(result, 0, 3));
item.Title = decode(DatabaseUtility.Get(result, 0, 4));
item.Filter = DatabaseUtility.Get(result, 0, 5);
item.WorkingFolder = decode(DatabaseUtility.Get(result, 0, 6));
item.UseQuotes = Boolean.Parse(DatabaseUtility.Get(result, 0, 7));
item.View = DatabaseUtility.GetAsInt(result, 0, 8);
item.Arguments = decode(DatabaseUtility.Get(result, 0, 9));
item.SuspendRendering = Boolean.Parse(DatabaseUtility.Get(result, 0, 10));
item.EnableGoodmerge = Boolean.Parse(DatabaseUtility.Get(result, 0, 11));
item.GoodmergePref1 = decode(DatabaseUtility.Get(result, 0, 12));
item.GoodmergePref2 = decode(DatabaseUtility.Get(result, 0, 13));
item.GoodmergePref3 = decode(DatabaseUtility.Get(result, 0, 14));
item.GoodmergeTempPath = decode(DatabaseUtility.Get(result, 0, 15));
item.Grade = DatabaseUtility.GetAsInt(result, 0, "Grade");
item.Company = decode(DatabaseUtility.Get(result, 0, "Company"));
item.Yearmade = DatabaseUtility.GetAsInt(result, 0, "Yearmade");
item.Description = decode(DatabaseUtility.Get(result, 0, "Description"));
item.MountImages = Boolean.Parse(DatabaseUtility.Get(result, 0, "mountimages"));
return(item);
}
else
{
return(null);
}
}
public static DBValue GetOptions(string property)
{
try
{
lock (thisLock)
{
DBValue retValue;
if (optionsCache.TryGetValue(property, out retValue))
{
return(retValue);
}
// ensure our sql query will be using a valid string
string convertedProperty = property;
DatabaseUtility.RemoveInvalidChars(ref convertedProperty);
string sqlQuery = "SELECT value FROM options WHERE property = '" + convertedProperty + "'";
SQLiteResultSet sqlResults = DBTVSeries.Execute(sqlQuery);
if (sqlResults.Rows.Count > 0)
{
string dbValue = DatabaseUtility.Get(sqlResults, 0, "value");
if (!optionsCache.ContainsKey(property))
{
optionsCache.Add(property, dbValue);
}
return(dbValue);
}
}
}
catch (Exception ex)
{
MPTVSeriesLog.Write("An error occurred (" + ex.Message + ").");
}
return(null);
}
public ArtistItem GetArtistsByName(string name)
{
if (string.IsNullOrEmpty(name))
{
return(null);
}
ArtistItem res = new ArtistItem();
string lsSQL = string.Format("select * from ARTISTS WHERE ARTIST_NAME like \"{0}\" order by ARTIST_NAME",
DatabaseUtility.RemoveInvalidChars(name.Replace('"', '`')));
SQLiteResultSet loResultSet = m_db.Execute(lsSQL);
for (int iRow = 0; iRow < loResultSet.Rows.Count; iRow++)
{
res.Id = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_ID");
res.Name = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_NAME").Replace("''", "'");
res.Img_url = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_IMG");
res.User = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_USER");
res.Tags = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_TAG");
res.Bio = DatabaseUtility.Get(loResultSet, iRow, "ARTIST_BIO");
return(res);
}
return(null);
}
static Game createGame(SQLiteResultSet result, int rowIndex, Emulator parentEmu)
{
Game item = new Game(decode(DatabaseUtility.Get(result, rowIndex, 1)), parentEmu);
if (parentEmu == null)
{
item.ParentEmulator = getEmulator(DatabaseUtility.GetAsInt(result, rowIndex, 2));
}
item.GameID = DatabaseUtility.GetAsInt(result, rowIndex, 0);
item.Title = decode(DatabaseUtility.Get(result, rowIndex, 3));
item.Grade = DatabaseUtility.GetAsInt(result, rowIndex, 4);
item.Playcount = DatabaseUtility.GetAsInt(result, rowIndex, 5);
item.Yearmade = DatabaseUtility.GetAsInt(result, rowIndex, 6);
item.Latestplay = DateTime.Parse(DatabaseUtility.Get(result, rowIndex, 7));
item.Description = decode(DatabaseUtility.Get(result, rowIndex, 8));
item.Genre = decode(DatabaseUtility.Get(result, rowIndex, 9));
item.Company = decode(DatabaseUtility.Get(result, rowIndex, 10));
item.Visible = Boolean.Parse(DatabaseUtility.Get(result, rowIndex, 11));
item.Favourite = Boolean.Parse(DatabaseUtility.Get(result, rowIndex, 12));
item.LaunchFile = decode(DatabaseUtility.Get(result, rowIndex, 13));
item.SelectedProfile = DatabaseUtility.GetAsInt(result, rowIndex, 14);
item.IsInfoChecked = Boolean.Parse(DatabaseUtility.Get(result, rowIndex, 15));
return(item);
}
public int AddPicture(string strPicture, int iRotation)
{
if (String.IsNullOrEmpty(strPicture))
{
return(-1);
}
if (m_db == null)
{
return(-1);
}
try
{
int lPicId = -1;
string strPic = strPicture;
string strDateTaken = String.Empty;
DatabaseUtility.RemoveInvalidChars(ref strPic);
string strSQL = String.Format("select * from picture where strFile like '{0}'", strPic);
SQLiteResultSet results = m_db.Execute(strSQL);
if (results != null && results.Rows.Count > 0)
{
lPicId = Int32.Parse(DatabaseUtility.Get(results, 0, "idPicture"));
return(lPicId);
}
// we need the date nevertheless for database view / sorting
if (!GetExifDetails(strPicture, ref iRotation, ref strDateTaken))
{
try
{
DateTime dat = File.GetLastWriteTime(strPicture);
if (!TimeZone.CurrentTimeZone.IsDaylightSavingTime(dat))
{
dat = dat.AddHours(1); // Try to respect the timezone of the file date
}
strDateTaken = dat.ToString("yyyy-MM-dd HH:mm:ss");
}
catch (Exception ex)
{
Log.Error("PictureDatabaseSqlLite: Conversion exception getting file date - err:{0} stack:{1}", ex.Message,
ex.StackTrace);
}
}
// Save potential performance penalty
if (_usePicasa)
{
if (GetPicasaRotation(strPic, ref iRotation))
{
Log.Debug("PictureDatabaseSqlLite: Changed rotation of image {0} based on picasa file to {1}", strPic,
iRotation);
}
}
// Transactions are a special case for SQLite - they speed things up quite a bit
strSQL = "begin";
results = m_db.Execute(strSQL);
strSQL =
String.Format(
"insert into picture (idPicture, strFile, iRotation, strDateTaken) values(null, '{0}',{1},'{2}')", strPic,
iRotation, strDateTaken);
results = m_db.Execute(strSQL);
if (results.Rows.Count > 0)
{
Log.Debug("PictureDatabaseSqlLite: Added to database - {0}", strPic);
}
strSQL = "commit";
results = m_db.Execute(strSQL);
lPicId = m_db.LastInsertID();
if (g_Player.Playing)
{
Thread.Sleep(50);
}
else
{
Thread.Sleep(1);
}
return(lPicId);
}
catch (Exception ex)
{
Log.Error("PictureDatabaseSqlLite: exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
Open();
}
return(-1);
}
public ArrayList Execute()
{
//build the query
ArrayList movies = new ArrayList();
string whereClause = string.Empty;
string orderClause = string.Empty;
string fromClause = "actors,movie,movieinfo,path";
if (CurrentLevel > 0)
{
whereClause =
"where actors.idactor=movieinfo.idDirector and movieinfo.idmovie=movie.idmovie and movie.idpath=path.idpath";
}
for (int i = 0; i < CurrentLevel; ++i)
{
BuildSelect((FilterDefinition)currentView.Filters[i], ref whereClause, ref fromClause);
}
BuildWhere((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildRestriction((FilterDefinition)currentView.Filters[CurrentLevel], ref whereClause);
BuildOrder((FilterDefinition)currentView.Filters[CurrentLevel], ref orderClause);
//execute the query
string sql;
if (CurrentLevel == 0)
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
FilterDefinition defRoot = (FilterDefinition)currentView.Filters[0];
string table = GetTable(defRoot.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable);
if (table == "actors")
{
sql = String.Format("select * from actors ");
if (whereClause != string.Empty)
{
sql += "where " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, true, false, false);
}
else if (table == "genre")
{
sql = String.Format("select * from genre ");
if (whereClause != string.Empty)
{
sql += "where " + whereClause;
}
if (orderClause != string.Empty)
{
sql += orderClause;
}
VideoDatabase.GetMoviesByFilter(sql, out movies, false, false, true);
}
else if (defRoot.Where == "year")
{
movies = new ArrayList();
sql = String.Format("select distinct iYear from movieinfo ");
SQLiteResultSet results = VideoDatabase.GetResults(sql);
for (int i = 0; i < results.Rows.Count; i++)
{
IMDBMovie movie = new IMDBMovie();
movie.Year = (int)Math.Floor(0.5d + Double.Parse(DatabaseUtility.Get(results, i, "iYear")));
movies.Add(movie);
}
}
else
{
whereClause =
"where actors.idActor=movieinfo.idDirector and movieinfo.idmovie=movie.idmovie and movie.idpath=path.idpath";
BuildRestriction(defRoot, ref whereClause);
sql = String.Format("select * from {0} {1} {2}",
fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, true, true, true);
}
}
else if (CurrentLevel < MaxLevels - 1)
{
bool useMovieInfoTable = false;
bool useAlbumTable = false;
bool useActorsTable = false;
bool useGenreTable = false;
FilterDefinition defCurrent = (FilterDefinition)currentView.Filters[CurrentLevel];
string table = GetTable(defCurrent.Where, ref useMovieInfoTable, ref useAlbumTable, ref useActorsTable,
ref useGenreTable);
sql = String.Format("select distinct {0}.* {1} {2} {3}",
table, fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, useActorsTable, useMovieInfoTable, useGenreTable);
}
else
{
sql =
String.Format(
"select movieinfo.fRating,actors.strActor,movieinfo.strCredits,movieinfo.strTagLine,movieinfo.strPlotOutline,movieinfo.strPlot,movieinfo.strVotes,movieinfo.strCast,movieinfo.iYear,movieinfo.strGenre,movieinfo.strPictureURL,movieinfo.strTitle,path.strPath,movie.discid,movieinfo.IMDBID,movieinfo.idMovie,path.cdlabel,movieinfo.mpaa,movieinfo.runtime,movieinfo.iswatched, movieinfo.strUserReview from {0} {1} {2}",
fromClause, whereClause, orderClause);
VideoDatabase.GetMoviesByFilter(sql, out movies, true, true, true);
}
return(movies);
}