|
public static executeReaderFirstDataRow ( |
||
| command | ||
| return | ||
public long getIdFromPath(string path)
{
SQLiteCommand command = new SQLiteCommand("SELECT id FROM `FileNodes` WHERE path = @path;", this.dbConnector.connection);
command.Parameters.AddWithValue("@path", path);
DataRow dr = DBConnector.executeReaderFirstDataRow(command);
if (dr == null)
{
return(0);
}
return(Convert.ToInt32(dr["id"]));
}
public int nrMetadataImagesToProcess()
{
SQLiteCommand command = new SQLiteCommand("SELECT COUNT(id) FROM `FileNodes` WHERE metainfoindexed = 0 LIMIT 1;", this.dbConnector.connection);
DataRow dr = DBConnector.executeReaderFirstDataRow(command);
if (dr != null)
{
return(Convert.ToInt32(dr[0]));
}
else
{
return(-1);
}
}
public int nrImagesFilter()
{
string tableName = this.filterReady();
SQLiteCommand command = new SQLiteCommand("SELECT COUNT(id) FROM `" + tableName + "`;", this.dbConnector.connection);
DataRow dr = DBConnector.executeReaderFirstDataRow(command);
if (dr != null)
{
return(Convert.ToInt32(dr[0]));
}
else
{
return(-1);
}
}
public string getMetadataById(long id)
{
string tableName = this.filterReady();
SQLiteCommand command = new SQLiteCommand("SELECT `all` FROM `Metadata` WHERE id = @id;", this.metaDataDbConnector.connection);
command.Parameters.AddWithValue("@id", id);
DataRow dr = DBConnector.executeReaderFirstDataRow(command);
if (dr != null && dr.Table.Columns.Contains("all"))
{
return(Convert.ToString(dr["all"]));
}
else
{
return(null);
}
}
public DataRow getFirstImage(string orderBy, SortOrder direction, long offset)
{
string tableName = this.filterReady();
SQLiteCommand command;
if (orderBy == null)
{
orderBy = "created";
}
if (direction == null)
{
direction = new SortOrder(SortOrder.Direction.ASC);
}
command = new SQLiteCommand("SELECT * FROM `" + tableName + "` ORDER BY " + orderBy + " " + direction.ToString() + ", id " + direction.ToString() + " LIMIT 1 OFFSET " + Math.Abs(offset) + ";", this.dbConnector.connection);
command.Parameters.AddWithValue("@orderBy", orderBy);
return(DBConnector.executeReaderFirstDataRow(command));
}
public DataRow getRandomImage(long offset)
{
/***
* Single query "SELECT * FROM `Filter` ORDER BY RANDOM() LIMIT 1;" takes approximately 1000ms
* Two separate queries take approximately 100ms (combined)
*
* TODO optimise?
* Test Select "SELECT id, path, ..., ... FROM `Filter` ORDER BY RANDOM() LIMIT 1;" ~ 450ms - 500ms
***/
string tableName = this.filterReady();
SQLiteCommand command = new SQLiteCommand("SELECT id FROM `" + tableName + "` ORDER BY RANDOM() LIMIT 1;", this.dbConnector.connection);
DataRow dr = DBConnector.executeReaderFirstDataRow(command);
if (dr == null)
{
return(null);
}
return(getImageById(Convert.ToInt32(dr["id"]), offset));
}
public DataRow nextMetadataLessImage()
{
SQLiteCommand command = new SQLiteCommand("SELECT * FROM `FileNodes` WHERE metainfoindexed = 0 LIMIT 1;", this.dbConnector.connection);
return(DBConnector.executeReaderFirstDataRow(command));
}
public DataRow getImageById(long id, long offset, string sortByColumn, SortOrder sortByDirection)
{
string tableName = this.filterReady();
SQLiteCommand command;
DataRow dr;
if (offset == 0)
{
command = new SQLiteCommand("SELECT * FROM `" + tableName + "` WHERE (id = @id) LIMIT 1;", this.dbConnector.connection);
command.Parameters.AddWithValue("@id", id);
dr = DBConnector.executeReaderFirstDataRow(command);
if (dr == null)
{
return(this.getFirstImage(sortByColumn, sortByDirection));
}
}
else
{
if (sortByColumn == null)
{
sortByColumn = "path";
}
if (sortByDirection == null)
{
sortByDirection = new SortOrder(SortOrder.Direction.ASC);
}
string than = "<";
if (sortByDirection.isDESC())
{
offset *= -1;
}
if (offset < 0)
{
than = "<";
sortByDirection.setDESC();
}
else
{
than = ">";
sortByDirection.setASC();
}
string sqlValue = "(SELECT " + sortByColumn + " FROM `" + tableName + "` WHERE `" + tableName + "`.id = @id)";
string sql = "FROM `" + tableName + "` WHERE (" + sortByColumn + " " + than + " " + sqlValue + ") " +
" OR ((" + sortByColumn + " = " + sqlValue + " AND id " + than + " @id)) " +
"ORDER BY " + sortByColumn + " " + sortByDirection.ToString() + ", id " + sortByDirection.ToString();
command = new SQLiteCommand(@"SELECT * " + sql + " LIMIT 1 OFFSET " + (Math.Abs(offset) - 1) + ";", this.dbConnector.connection);
command.Parameters.AddWithValue("@id", id);
dr = DBConnector.executeReaderFirstDataRow(command);
if (dr == null)
{
command = new SQLiteCommand(@"SELECT COUNT(`" + tableName + "`.id) " + sql, this.dbConnector.connection);
command.Parameters.AddWithValue("@id", id);
dr = DBConnector.executeReaderFirstDataRow(command);
if (dr != null)
{
if (offset < 0)
{
offset += Convert.ToInt32(dr[0]) + 1;
}
else
{
offset -= Convert.ToInt32(dr[0]) + 1;
}
if (this.nrImagesFilter() > 0)
{
offset = offset % this.nrImagesFilter();
}
dr = getFirstImage(sortByColumn, sortByDirection, offset);
}
}
}
return(dr);
}