public MySqlCommand AddCategory(Repository repository, Category category)
{
string sql = string.Format("INSERT INTO `{0}`(UUID,CategoryFolder,CategoryUUID) VALUES(?UUID,?CategoryFolder,?CategoryUUID)"
, repository.GetCategoryTableName());
MySqlCommand command = new MySqlCommand();
command.CommandText = sql;
command.Parameters.Add(new MySqlParameter("?UUID", category.ContentUUID));
command.Parameters.Add(new MySqlParameter("?CategoryFolder", category.CategoryFolder));
command.Parameters.Add(new MySqlParameter("?CategoryUUID", category.CategoryUUID));
return command;
}
public SqlCeCommand AddCategory(Repository repository, Category category)
{
string sql = string.Format("INSERT INTO [{0}](UUID,CategoryFolder,CategoryUUID) VALUES(@UUID,@CategoryFolder,@CategoryUUID)"
, repository.GetCategoryTableName());
SqlCeCommand command = new SqlCeCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlCeParameter("@UUID", category.ContentUUID));
command.Parameters.Add(new SqlCeParameter("@CategoryFolder", category.CategoryFolder));
command.Parameters.Add(new SqlCeParameter("@CategoryUUID", category.CategoryUUID));
return command;
}
private IEnumerable<string> GetCategoryClause(Repository repository, IEnumerable<IContentQuery<TextContent>> categoryQueries, List<Parameter> parameters)
{
TextContentTranslator translator = new TextContentTranslator();
List<string> categoryQueryList = new List<string>();
foreach (var categoryQuery in categoryQueries)
{
var executor = translator.Translate(categoryQuery);
var categoryVisitor = new MysqlVisitor<TextContent>(parameters);
categoryVisitor.Visite(categoryQuery.Expression);
IEnumerable<Parameter> outParameters;
var categoryQuerySQL = executor.BuildQuerySQL(categoryVisitor, out outParameters);
categoryQueryList.Add(string.Format(@"EXISTS(
SELECT ContentCategory.CategoryUUID
FROM `{0}` ContentCategory,
({1})category
WHERE content.UUID = ContentCategory.UUID AND ContentCategory.CategoryUUID = category.UUID
)", repository.GetCategoryTableName()
, categoryQuerySQL));
}
return categoryQueryList;
}
public SqlCommand DeleteCategory(Repository repository, Category category)
{
string sql = string.Format("DELETE FROM [{0}] WHERE UUID=@UUID AND CategoryFolder=@CategoryFolder AND CategoryUUID=@CategoryUUID"
, repository.GetCategoryTableName());
SqlCommand command = new SqlCommand();
command.CommandText = sql;
command.Parameters.Add(new SqlParameter("@UUID", category.ContentUUID));
command.Parameters.Add(new SqlParameter("@CategoryFolder", category.CategoryFolder));
command.Parameters.Add(new SqlParameter("@CategoryUUID", category.CategoryUUID));
return command;
}
private static void CreateCategoryTable(Repository repository)
{
string ddl = string.Format(@"
CREATE TABLE IF NOT EXISTS `{0}` (
`UUID` nvarchar(36) NOT NULL,
`CategoryFolder` nvarchar(256),
`CategoryUUID` nvarchar(36)
)DEFAULT CHARSET={1};", repository.GetCategoryTableName(), MysqlSettings.Instance.DEFAULT_CHARSET);
ExecuteDDL(repository, ddl);
}
public IEnumerable<Category> ExportCategoryData(Repository repository)
{
var connectionString = repository.GetConnectionString();
string sql = string.Format("SELECT UUID,CategoryFolder,CategoryUUID FROM [{0}] ", repository.GetCategoryTableName());
List<Category> list = new List<Category>();
SqlCeConnection connection;
using (var reader = SQLCeHelper.ExecuteReader(connectionString, new SqlCeCommand() { CommandText = sql }, out connection))
{
try
{
while (reader.Read())
{
Category category = new Category();
category.ContentUUID = reader.GetString(0);
category.CategoryFolder = reader.GetString(1);
category.CategoryUUID = reader.GetString(2);
list.Add(category);
}
}
finally
{
reader.Close();
connection.Close();
}
}
return list;
}
private static void CreateCategoryTable(Repository repository)
{
string ddl = string.Format(@"CREATE TABLE [{0}] (
UUID nvarchar(36) NOT NULL,
CategoryFolder nvarchar(256),
CategoryUUID nvarchar(36)
);", repository.GetCategoryTableName());
ExecuteDDL(repository, ddl);
}
private static void CreateCategoryTable(Repository repository)
{
string ddl = string.Format(@"
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}')
CREATE TABLE [{0}] (
Id int IDENTITY (100,1) PRIMARY KEY,
UUID nvarchar(36) NOT NULL,
CategoryFolder nvarchar(256),
CategoryUUID nvarchar(36)
);", repository.GetCategoryTableName());
ExecuteDDL(repository, ddl);
}
public IEnumerable<Category> ExportCategoryData(Repository repository)
{
string sql = string.Format("SELECT UUID,CategoryFolder,CategoryUUID FROM `{0}` ", repository.GetCategoryTableName());
List<Category> list = new List<Category>();
MySqlConnection connection;
using (var reader = MysqlHelper.ExecuteReader(repository, new MySqlCommand() { CommandText = sql }, out connection))
{
while (reader.Read())
{
Category category = new Category();
category.ContentUUID = reader.GetString(0);
category.CategoryFolder = reader.GetString(1);
category.CategoryUUID = reader.GetString(2);
list.Add(category);
}
connection.Close();
}
return list;
}
public static void DisposeDatabase(Repository repository)
{
SchemaManager.DropTable(repository, repository.GetCategoryTableName());
}