public void TrueTest()
{
SqlSelect s1 = SqlDml.Select();
s1.Where = true;
Console.WriteLine(SqlDriver.Compile(s1).GetCommandText());
}
private void ClearSchema(SqlDriver driver)
{
using (var connection = driver.CreateConnection()) {
connection.Open();
try {
var schema = driver.ExtractSchema(connection, SpecialSchemaAlias);
var foreignKeys = schema.Tables
.Select(t => new {
Table = t,
ForeignKeys = t.TableConstraints.OfType <ForeignKey>()
});
foreach (var dropConstraintText in from foreignKeyInfo in foreignKeys
from foreignKey in foreignKeyInfo.ForeignKeys
select driver.Compile(SqlDdl.Alter(foreignKeyInfo.Table, SqlDdl.DropConstraint(foreignKey))).GetCommandText())
{
using (var command = connection.CreateCommand(dropConstraintText))
command.ExecuteNonQuery();
}
foreach (var table in schema.Tables)
{
var dropTableText = driver.Compile(SqlDdl.Drop(table, true)).GetCommandText();
using (var command = connection.CreateCommand(dropTableText))
command.ExecuteNonQuery();
}
}
finally {
connection.Close();
}
}
}
public void ExtractTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "2006-01-23"));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
private bool ValidateFields(string username, string password)
{
var hashedPassword = PasswordHasher.CreateMD5(password);
var credentialResult = SqlDriver.Fetch($"SELECT username, password FROM users WHERE username='******' AND password='******';");
return(credentialResult != null && credentialResult.Count == 1);
}
public override void SetUp()
{
base.SetUp();
sqlDriver = TestSqlDriver.Create(Url);
sqlConnection = sqlDriver.CreateConnection();
sqlCommand = sqlConnection.CreateCommand();
}
public List <Dictionary <string, object> > GetAllReviews()
{
return(SqlDriver.Fetch($"SELECT users.status, studyProgrammes.program, courseReviews.review," +
$" courseReviews.date FROM courseReviews, studyProgrammes, users WHERE review IS NOT NULL AND " +
$"studyProgrammes.studyProgramId=users.courseId AND users.userId=courseReviews.userId " +
$"AND courseReviews.courseId={StudyProgramId}"));
}
public void UnionTest()
{
SqlSelect s1 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s1.Columns.Add(s1.From["address_id"]);
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s2.Columns.Add(s2.From["address_id"]);
SqlSelect s3 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["address"]));
s3.Columns.Add(s3.From["address_id"]);
Console.WriteLine(SqlDriver.Compile(s1.Union(s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.Union(s2).Union(s3)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(s1.Union(s2.Union(s3))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1, s2)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1, s1.Union(s2))).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1.Union(s2), s1)).GetCommandText());
Console.WriteLine(SqlDriver.Compile(SqlDml.Union(s1.Union(s2), s1.Union(s2))).GetCommandText());
s3.Where = SqlDml.In(50.00, s1.Union(s2));
Console.WriteLine(SqlDriver.Compile(s3).GetCommandText());
SqlQueryRef qr = SqlDml.QueryRef(s1.Union(s2), "qr");
Assert.Greater(qr.Columns.Count, 0);
}
public void ChangeUniversity(string selectedUniversity)
{
if (!selectedUniversity.Equals(GetCurrentUniversity()))
{
var sqlGetNewUniversityID = "select universityid from universities where name ='" + selectedUniversity + "';";
var newUniversityIdFromDB = SqlDriver.Fetch(sqlGetNewUniversityID);
var newUniversityId = ((Dictionary <string, object>)newUniversityIdFromDB[0])["universityId"].ToString();
var sqlUpdateUniversityID = "update users set universityid =" + newUniversityId + " where username ='******';";
try
{
if (SqlDriver.Execute(sqlUpdateUniversityID))
{
MessageBox.Show(Messages.universityUpdateSuccess);
Logger.Log(Messages.universityUpdateSuccess);
}
else
{
MessageBox.Show(Messages.universityUpdateFailed);
Logger.Log(Messages.universityUpdateFailed);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
else
{
MessageBox.Show(Messages.newUniversitySameAsOld);
Logger.Log(Messages.newUniversitySameAsOld);
}
}
public bool AddUser(UserModel user)
{
if (!CheckIfUserExists(user))
{
try
{
var universityId = SqlDriver.Row($"SELECT universityId from universities WHERE name='{user.University}';")["universityId"].ToString();
var courseId = SqlDriver.Row($"SELECT studyProgramId from studyProgrammes WHERE program='{user.Course}';")["studyProgramId"].ToString();
if (SqlDriver.Execute("INSERT INTO users (username, first_name, last_name, email, universityId, courseId, status, password) " +
"values (@0,@1,@2,@3,@4,@5,@6,@7)",
new ArrayList()
{
user.Username, user.FirstName, user.LastName, user.Email, universityId, courseId, user.Status, _passwordHasher.CreateMD5(user.Password)
}))
{
return(true);
}
else
{
_logger.Log("Query cannot be executed");
}
}
catch (Exception ex)
{
_logger.Log("User cannot be added" + Environment.NewLine + ex.Message, "ERROR");
return(false);
}
}
return(false);
}
public void ChangeStatus(string selectedStatus)
{
if (!selectedStatus.Equals(GetCurrentSetting("status")))
{
var sqlUpdateStatus = "UPDATE users SET status='" + selectedStatus + "' WHERE username='******';";
try
{
if (SqlDriver.Execute(sqlUpdateStatus))
{
MessageBox.Show(Messages.statusUpdateSuccess);
Logger.Log(Messages.statusUpdateSuccess);
}
else
{
MessageBox.Show(Messages.statusUpdateFailed);
Logger.Log(Messages.statusUpdateFailed);
}
}
catch (Exception ex)
{
Logger.Log(ex.Message);
}
}
else
{
Logger.Log(Messages.newStatusSameAsOld);
}
}
public List <Dictionary <string, object> > RecommendedCourses(UserModel user)
{
string cities, groups, directions;
if (user.CityPreferences != null)
{
cities = user.CityPreferences.Replace(",", "\",\"");
cities = "\"" + cities + "\"";
}
else
{
cities = "";
}
if (user.GroupPreferences != null)
{
groups = user.GroupPreferences.Replace(",", "\",\"");
groups = "\"" + groups + "\"";
directions = user.DirectionPreferences.Replace(",", "\",\"");
directions = "\"" + directions + "\"";
if (cities != "")
{
return(SqlDriver.Fetch($"SELECT * FROM studyProgrammes WHERE city IN ({cities}) AND \"group\" IN ({groups}) AND direction IN ({directions})"));
}
else
{
return(SqlDriver.Fetch($"SELECT * FROM studyProgrammes WHERE \"group\" IN ({groups}) AND direction IN ({directions})"));
}
}
;
return(null);
}
private void InstantiateGrid()
{
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Variety of courses", typeof(string));
table.Columns.Add("Availability of extracurricular activities ", typeof(string));
table.Columns.Add("Access to faculty", typeof(string));
table.Columns.Add("Quality of academic facilities (library, PCs, etc.)", typeof(string));
table.Columns.Add("Student unions", typeof(string));
table.Columns.Add("Cost of studying", typeof(string));
var universities = SqlDriver.Fetch("SELECT u.universityId,name,avg(variety) as variety,avg(availability) as availability,avg(accessability) as accessability,avg(quality) as quality,avg(unions) as unions,avg(cost) as cost " +
"FROM universities u LEFT JOIN universityReviews ur ON u.universityId=ur.universityId " +
"GROUP BY u.universityId,name");
foreach (Dictionary <string, object> row in universities)
{
table.Rows.Add(row["universityId"], row["name"], row["variety"], row["availability"], row["accessability"], row["quality"], row["unions"], row["cost"]);
}
universitiesGrid.DataSource = null;
universitiesGrid.Rows.Clear();
universitiesGrid.DataSource = table;
}
private void InstantiateProgramsGrid(int universityId)
{
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Group", typeof(string));
table.Columns.Add("Direction", typeof(string));
table.Columns.Add("Program", typeof(string));
table.Columns.Add("Presentation of content", typeof(string));
table.Columns.Add("Clarity of expectations", typeof(string));
table.Columns.Add("Clear feedback on performance", typeof(string));
table.Columns.Add("Encouragment of participation/discussion", typeof(string));
table.Columns.Add("Overall teaching effectiveness", typeof(string));
table.Columns.Add("How satisfied were you with this course", typeof(string));
var programmes = SqlDriver.Fetch($"SELECT studyProgramId,[group],direction,program,city," +
$"avg(presentation) as presentation,avg(clarity) as clarity,avg(feedback) as feedback, avg(encouragement) as encouragement,avg(effectiveness) as effectiveness,avg(satisfaction) as satisfaction " +
$"FROM studyProgrammes left join courseReviews on studyProgramId=courseId" +
$" WHERE universityId = {universityId} group by studyProgramId,[group], direction, program, city");
foreach (Dictionary <string, object> row in programmes)
{
table.Rows.Add(row["studyProgramId"], row["group"], row["direction"], row["program"], row["presentation"], row["clarity"], row["feedback"], row["encouragement"], row["effectiveness"], row["satisfaction"]);
}
programmesGrid.DataSource = null;
programmesGrid.Rows.Clear();
programmesGrid.DataSource = table;
}
// Constructors
internal StorageExceptionBuilder(SqlDriver driver, DomainConfiguration configuration, Func <DomainModel> modelProvider)
{
this.driver = driver;
this.modelProvider = modelProvider;
includeSqlInExceptions = configuration.IncludeSqlInExceptions;
}
public void PositionTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Multiply(SqlDml.Position("b", "abc"), 4));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public virtual void SetUp()
{
CheckRequirements();
sqlDriver = TestSqlDriver.Create(Url);
sqlConnection = sqlDriver.CreateConnection();
try {
sqlConnection.Open();
}
catch (Exception exception) {
Console.WriteLine(exception);
throw;
}
try {
sqlConnection.BeginTransaction();
Catalog = sqlDriver.ExtractCatalog(sqlConnection);
var schema = Catalog.DefaultSchema;
var creator = new ChinookSchemaCreator(sqlDriver);
creator.DropSchemaContent(sqlConnection, schema);
creator.CreateSchemaContent(sqlConnection, schema);
sqlConnection.Commit();
}
catch {
sqlConnection.Rollback();
throw;
}
}
private bool CompareExecuteNonQuery(string commandText, ISqlCompileUnit statement)
{
sqlCommand.CommandText = SqlDriver.Compile(statement).GetCommandText();
sqlCommand.Prepare();
Console.WriteLine(sqlCommand.CommandText);
Console.WriteLine(commandText);
dbCommand.CommandText = commandText;
DbCommandExecutionResult r1, r2;
r1 = GetExecuteNonQueryResult(dbCommand);
r2 = GetExecuteNonQueryResult(sqlCommand);
Console.WriteLine();
Console.WriteLine();
Console.WriteLine(r1);
Console.WriteLine(r2);
if (r1.RowCount != r2.RowCount)
{
return(false);
}
return(true);
}
public override void SetUp()
{
CheckRequirements();
SqlDriver = TestSqlDriver.Create(ConnectionInfo.ConnectionUrl.Url);
SqlConnection = SqlDriver.CreateConnection();
SqlConnection.Open();
sqlCommand = SqlConnection.CreateCommand();
}
private static bool GetCheckConnectionIsAliveFlag(SqlDriver driver)
{
const string fieldName = "checkConnectionIsAlive";
var type = typeof(Xtensive.Sql.Drivers.SqlServer.Driver);
return((bool)type.GetField(fieldName, System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic)
.GetValue(driver));
}
public List <Dictionary <string, object> > GetUniversitiesWithRatings()
{
return(SqlDriver.Fetch("SELECT u.universityId, ud.wikipedia_link, ud.rank_country, ud.rank_world, u.image, u.address, name, round(avg(variety),1) as variety, round(avg(availability),1) as availability, " +
"round(avg(accessability),1) as accessability, round(avg(quality),1) as quality, round(avg(unions),1) as unions, " +
"round(avg(cost),1) as cost " +
"FROM universities u LEFT JOIN universityReviews ur ON u.universityId=ur.universityId LEFT JOIN university_details_lt ud ON u.universityId=ud.universityId " +
"GROUP BY u.universityId,name"));
}
public void SubstringTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Substring("abc", 1, 1));
select.Columns.Add(SqlDml.Substring("Xtensive", 2));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public void SaveReviews()
{
SqlDriver.Execute("INSERT INTO courseReviews (presentation,clarity,feedback,encouragement,effectiveness,satisfaction,review,date,courseId,userId) " +
"values (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)", new ArrayList()
{
Presentation, Clarity, Feedback, Encouragement, Effectiveness, Satisfaction, Review, DateTime.Now.ToString(), StudyProgramId, UserId
});
}
public void SaveReviews()
{
SqlDriver.Execute("INSERT INTO universityReviews (variety,availability,accessability,quality,unions,cost,review,date,universityId,userId) " +
"values (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9)", new ArrayList()
{
Variety, Availability, Accessability, Quality, Unions, Cost, Review, DateTime.Now.ToString(), UniversityId, UserId
});
}
public void ConcatTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Concat("a", "b"));
//select.Columns.Add("User: " + SqlDml.SessionUser()); //NOTE: Not supported by MySQL.
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
protected virtual void TestFixtureSetUp()
{
var parsedUrl = UrlInfo.Parse(Url);
Driver = TestSqlDriver.Create(parsedUrl);
Connection = Driver.CreateConnection();
Connection.Open();
}
public void UniqueTest()
{
SqlSelect s1 = SqlDml.Select();
SqlSelect s2 = SqlDml.Select(SqlDml.TableRef(Catalog.DefaultSchema.Tables["customer"]));
s2.Columns.Add(SqlDml.Asterisk);
s1.Columns.Add(SqlDml.Unique(s2) == true);
Console.WriteLine(SqlDriver.Compile(s1).GetCommandText());
}
public void FreeTextTest()
{
SqlSelect select = SqlDml.Select();
var table = Catalog.DefaultSchema.Tables["Address"];
select.From = SqlDml.QueryRef(SqlDml.FreeTextTable(table, "How can I make my own beers and ales?", EnumerableUtils.One(table.Columns[0].Name).ToList(), EnumerableUtils.One(table.Columns[0].Name).ToList()));
select.Columns.Add(select.From.Asterisk);
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public ForumModel(int questionId)
{
this.questionId = questionId;
var sqlQuestion = SqlDriver.Row($"SELECT question, message, userId FROM questions WHERE questionId = {questionId};");
question = sqlQuestion["question"].ToString();
userId = sqlQuestion["userId"].ToString().TryParse(0);
message = sqlQuestion["message"].ToString();
}
public List <Dictionary <string, object> > GetCoursesWithRatings()
{
return(SqlDriver.Fetch($"SELECT *," +
$"round(avg(presentation),1) as presentation, round(avg(clarity),1) as clarity, round(avg(feedback),1) as feedback," +
$" round(avg(encouragement),1) as encouragement, round(avg(effectiveness),1) as effectiveness, " +
$"round(avg(satisfaction),1) as satisfaction " +
$"FROM studyProgrammes left join courseReviews on studyProgramId=courseId" +
$" WHERE universityId = {UniversityId} group by studyProgramId,[group], direction, program, city"));
}
public int AnswersCount()
{
var answersCount = SqlDriver.Fetch("SELECT * FROM answers WHERE userId =" + UserId + "; ");
if (answersCount != null)
{
return(answersCount.Count);
}
return(0);
}
