public void SettingSelect()
{
// Arrange
SelectQuery select = Select("firstname".Field(), "lastname".Field())
.From("people")
.Build();
// Act
SelectTable selectTable = new SelectTable(select)
.As("p");
// Assert
selectTable.Select.Should().BeSameAs(select);
}
private SelectTable ToSelectTable(ExecutedTable table)
{
var select = new SelectTable();
select.Columns = table.Descriptor.Properties.Select(x => new Sdk.SelectColumn()
{
Name = x.Name, Type = x.Type == null ? "" : x.Type.Name.ToString().ToLower()
}).ToArray();
var list = new List <SelectRow>();
foreach (var row in table.Rows)
{
var selectRow = new SelectRow();
selectRow.Values = row.Values.Select(x => x.Value).ToArray();
list.Add(selectRow);
}
select.Rows = list.ToArray();
return(select);
}
public void CloneTest()
{
// Arrange
SelectQuery query = Select("firstname".Field(), "lastname".Field())
.From("people")
.Build();
SelectTable source = new SelectTable(query)
.As("p");
// Act
ITable copy = source.Clone();
// Assert
SelectTable clone = copy.Should()
.NotBeNull().And
.BeAssignableTo <SelectTable>().Which;
clone.Should()
.BeEquivalentTo(source);
clone.Select.Should()
.NotBeSameAs(query, "select must be cloned as well").And
.BeEquivalentTo(query);
}
// Запись новых значений в базу
public void SetToBase(SelectTable selectTable)
{
SqlCommand command;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
List <string> nameTables = new List <string> {
"Car", "Color", "ContractRect", "Light", "Renter"
};
switch (selectTable)
{
case SelectTable.Cars:
command = new SqlCommand($"DELETE FROM Car", connection);
command.ExecuteNonQuery();
foreach (var item in cars)
{
command = new SqlCommand(
$@"INSERT INTO Car
(number, brand, body, privod, peredacha, motor, light,
climate, color, dorsCount, placeCount, obivka) VALUES
({item.Number}, '{item.Brand}', '{item.Body}', '{item.Privod}', '{item.Peredacha}',
'{item.Motor}', '{item.Light}', '{item.Climate}', '{item.Color}',
{item.DorsCount}, {item.PlaceCount}, '{item.Obivka}')"
, connection);
command.ExecuteNonQuery();
}
break;
case SelectTable.Renters:
command = new SqlCommand($"DELETE FROM Renter", connection);
command.ExecuteNonQuery();
foreach (var item in renters)
{
command = new SqlCommand(
$@"INSERT INTO Renter (id, lastName, firstName, middleName, adres, phone)
VALUES ({item.Id}, '{item.LastName}', '{item.FirstName}', '{item.MiddleName}',
'{item.Address}', '{item.Phone}')", connection);
command.ExecuteNonQuery();
}
break;
case SelectTable.ContractRent:
command = new SqlCommand($"DELETE FROM ContractRent", connection);
command.ExecuteNonQuery().ToString();
foreach (var item in contractRents)
{
command = new SqlCommand(
$@"INSERT INTO ContractRent (renter, car, cost, startRent, finish, carparkNumber, passwordPark) VALUES
('{item.Renter}', '{item.Car}', '{item.Cost}', '{item.StartRent.ToString()}', '{item.FinishRent.ToString()}',
'{item.CarparklNumber}', '{item.PasswordPark}')", connection);
command.ExecuteNonQuery();
}
break;
case SelectTable.Colors:
command = new SqlCommand($"DELETE FROM Color", connection);
command.ExecuteNonQuery().ToString();
foreach (var item in colors)
{
command = new SqlCommand(
$@"INSERT INTO Color (color) VALUES ('{item.Color}')", connection);
command.ExecuteNonQuery();
}
break;
case SelectTable.Lights:
command = new SqlCommand($"DELETE FROM Light", connection);
command.ExecuteNonQuery();
foreach (var item in lights)
{
command = new SqlCommand(
$@"INSERT INTO Light (light) VALUES ('{item.Light}')", connection);
command.ExecuteNonQuery();
}
break;
default:
break;
}
}
}
public async Task <ActionResult> Query([FromBody] JObject jobject)
{
JObject resultJobj = new SelectTable(_identitySvc, _tableMapper, db.Db).Query(jobject);
return(Ok(resultJobj));
}
/// <summary>
/// Обновление данных в БД
/// </summary>
/// <param name="param">Параметр типа MySqlDatabase</param>
/// <param name="selectTable">Таблица для обновления</param>
public void Update(MySqlDatabase param, SelectTable selectTable)
{
switch (selectTable)
{
case SelectTable.Informations:
if (param.Informations != null && param.Informations.Id > 0)
{
string sqlUpdate = "UPDATE info i SET i.City=@city, i.Link=@link WHERE i.Id=@id";
MySqlTransaction transaction = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction();
MySqlCommand command = new MySqlCommand(sqlUpdate, connection, transaction);
MySqlParameter parameterId = new MySqlParameter("@id", MySqlDbType.String);
parameterId.Value = param.Informations.Id;
command.Parameters.Add(parameterId);
MySqlParameter parameterCity = new MySqlParameter("@city", MySqlDbType.String);
parameterCity.Value = param.Informations.City;
command.Parameters.Add(parameterCity);
MySqlParameter parameterLink = new MySqlParameter("@link", MySqlDbType.String);
parameterLink.Value = param.Informations.Link;
command.Parameters.Add(parameterLink);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw new Exception("Ошибка при обновлении данных в БД");
}
finally
{
connection.Close();
}
}
else
{
throw new Exception("Отсутсвует таблица для вставки или неверный идентификатор");
}
break;
case SelectTable.Weathers:
if (param.Weathers != null)
{
string sqlUpdate = "UPDATE weather w SET w.Condition=@condition, w.Date=@date, w.TempMin=@tempMin, w.TempMax=@tempMax, w.Precipitation=@precipitation WHERE w.Id=@id";
MySqlTransaction transaction = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction();
MySqlCommand command = new MySqlCommand(sqlUpdate, connection, transaction);
MySqlParameter parameterId = new MySqlParameter("@id", MySqlDbType.String);
parameterId.Value = param.Weathers.Id;
command.Parameters.Add(parameterId);
MySqlParameter parameterCondition = new MySqlParameter("@condition", MySqlDbType.String);
parameterCondition.Value = param.Weathers.WeatherCondition;
command.Parameters.Add(parameterCondition);
MySqlParameter parameterDate = new MySqlParameter("@date", MySqlDbType.String);
parameterDate.Value = param.Weathers.Date;
command.Parameters.Add(parameterDate);
MySqlParameter parameterTempMin = new MySqlParameter("@tempMin", MySqlDbType.String);
parameterTempMin.Value = param.Weathers.TemperatureMin;
command.Parameters.Add(parameterTempMin);
MySqlParameter parameterTempMax = new MySqlParameter("@tempMax", MySqlDbType.String);
parameterTempMax.Value = param.Weathers.TemperatureMax;
command.Parameters.Add(parameterTempMax);
MySqlParameter parameterPrecipitation = new MySqlParameter("@precipitation", MySqlDbType.String);
parameterPrecipitation.Value = param.Weathers.Precipitation;
command.Parameters.Add(parameterPrecipitation);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw new Exception("Ошибка при обновлении данных в БД");
}
finally
{
connection.Close();
}
}
else
{
throw new Exception("Отсутсвует таблица для вставки или неверный идентификатор");
}
break;
case SelectTable.All:
if ((param.Informations != null && param.Informations.Id > 0) && (param.Weathers != null))
{
string sqlUpdateFirst = "UPDATE info i SET i.City = @city, i.Link = @link WHERE Id = @id";
string sqlUpdateSecond = "UPDATE weather w SET w.Condition=@condition, w.Date=@date, w.TempMin=@tempMin, w.TempMax=@tempMax, w.Precipitation=@precipitation WHERE w.Info_Id=@info_Id";
MySqlTransaction transaction = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction();
MySqlCommand commandFirst = new MySqlCommand(sqlUpdateFirst, connection, transaction);
MySqlParameter parameterId = new MySqlParameter("@id", MySqlDbType.String);
parameterId.Value = param.Informations.Id;
commandFirst.Parameters.Add(parameterId);
MySqlParameter parameterCity = new MySqlParameter("@city", MySqlDbType.String);
parameterCity.Value = param.Informations.City;
commandFirst.Parameters.Add(parameterCity);
MySqlParameter parameterLink = new MySqlParameter("@link", MySqlDbType.String);
parameterLink.Value = param.Informations.Link;
commandFirst.Parameters.Add(parameterLink);
commandFirst.ExecuteNonQuery();
MySqlCommand commandSecond = new MySqlCommand(sqlUpdateSecond, connection, transaction);
MySqlParameter parameterCondition = new MySqlParameter("@condition", MySqlDbType.String);
parameterCondition.Value = param.Weathers.WeatherCondition;
commandSecond.Parameters.Add(parameterCondition);
MySqlParameter parameterDate = new MySqlParameter("@date", MySqlDbType.String);
parameterDate.Value = param.Weathers.Date;
commandSecond.Parameters.Add(parameterDate);
MySqlParameter parameterTempMin = new MySqlParameter("@tempMin", MySqlDbType.String);
parameterTempMin.Value = param.Weathers.TemperatureMin;
commandSecond.Parameters.Add(parameterTempMin);
MySqlParameter parameterTempMax = new MySqlParameter("@tempMax", MySqlDbType.String);
parameterTempMax.Value = param.Weathers.TemperatureMax;
commandSecond.Parameters.Add(parameterTempMax);
MySqlParameter parameterPrecipitation = new MySqlParameter("@precipitation", MySqlDbType.String);
parameterPrecipitation.Value = param.Weathers.Precipitation;
commandSecond.Parameters.Add(parameterPrecipitation);
MySqlParameter parameterInfo_Id = new MySqlParameter("@info_Id", MySqlDbType.String);
parameterInfo_Id.Value = param.Informations.Id;
commandSecond.Parameters.Add(parameterInfo_Id);
commandSecond.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw new Exception("Ошибка при обновлении данных в БД");
}
finally
{
connection.Close();
}
}
else
{
throw new Exception("Отсутсвует таблица для обновления или неверный идентификатор");
}
break;
default:
break;
}
}
/// <summary>
/// Добавление данных в БД
/// </summary>
/// <param name="param">Параметр типа MySqlDatabase</param>
/// <param name="selectTable">Таблица для встакви</param>
public void Insert(MySqlDatabase param, SelectTable selectTable)
{
string sqlInsert;
switch (selectTable)
{
case SelectTable.Informations:
if (param.Informations != null)
{
sqlInsert = "INSERT INTO info(city, link) VALUES(@city, @link)";
MySqlTransaction transaction = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
MySqlCommand command = new MySqlCommand(sqlInsert, connection, transaction);
MySqlParameter parameterCity = new MySqlParameter("@city", MySqlDbType.String);
parameterCity.Value = param.Informations.City;
command.Parameters.Add(parameterCity);
MySqlParameter parameterLink = new MySqlParameter("@link", MySqlDbType.String);
parameterLink.Value = param.Informations.Link;
command.Parameters.Add(parameterLink);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw new Exception("Произошла ошибка при добавлении данных в БД");
}
finally
{
connection.Close();
}
}
else
{
throw new Exception("Отсутсвует таблица для вставки");
}
break;
case SelectTable.Weathers:
if (param.Weathers != null && param.Weathers.InfoId > 0)
{
sqlInsert = "INSERT INTO weather(Condition, Date, TempMin, TempMax, Precipitation, Info_Id) VALUES(@condition, @date, @tempMin, @tempMax, @precipitation, @info_Id)";
MySqlTransaction transaction = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
MySqlCommand command = new MySqlCommand(sqlInsert, connection, transaction);
MySqlParameter parameterCondition = new MySqlParameter("@condition", MySqlDbType.String);
parameterCondition.Value = param.Weathers.WeatherCondition;
command.Parameters.Add(parameterCondition);
MySqlParameter parameterDate = new MySqlParameter("@date", MySqlDbType.String);
parameterDate.Value = param.Weathers.Date;
command.Parameters.Add(parameterDate);
MySqlParameter parameterTempMin = new MySqlParameter("@tempMin", MySqlDbType.String);
parameterTempMin.Value = param.Weathers.TemperatureMin;
command.Parameters.Add(parameterTempMin);
MySqlParameter parameterTempMax = new MySqlParameter("@tempMax", MySqlDbType.String);
parameterTempMax.Value = param.Weathers.TemperatureMax;
command.Parameters.Add(parameterTempMax);
MySqlParameter parameterPrecipitation = new MySqlParameter("@precipitation", MySqlDbType.String);
parameterPrecipitation.Value = param.Weathers.Precipitation;
command.Parameters.Add(parameterPrecipitation);
MySqlParameter parameterInfo_Id = new MySqlParameter("@info_Id", MySqlDbType.String);
parameterInfo_Id.Value = param.Weathers.InfoId;
command.Parameters.Add(parameterInfo_Id);
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw new Exception("Произошла ошибка при добавлении данных в БД");
}
finally
{
connection.Close();
}
}
else
{
throw new Exception("Объект не существует либо отсуствует внешний ключ Info_Id");
}
break;
case SelectTable.All:
string sqlInsertFirst = "INSERT INTO info(`city`, `link`) VALUES(@city, @link)";
string sqlInsertSecond = "INSERT INTO weather(`Condition`, `Date`, `TempMin`, `TempMax`, `Precipitation`, `Info_Id`) VALUES(@condition, @date, @tempMin, @tempMax, @precipitation, LAST_INSERT_ID())";
if (param.Informations != null && param.Weathers != null)
{
MySqlTransaction transaction = null;
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
transaction = connection.BeginTransaction();
MySqlCommand commandFirst = new MySqlCommand(sqlInsertFirst, connection, transaction);
MySqlParameter parameterCity = new MySqlParameter("@city", MySqlDbType.String);
parameterCity.Value = param.Informations.City;
commandFirst.Parameters.Add(parameterCity);
MySqlParameter parameterLink = new MySqlParameter("@link", MySqlDbType.String);
parameterLink.Value = param.Informations.Link;
commandFirst.Parameters.Add(parameterLink);
commandFirst.ExecuteNonQuery();
MySqlCommand commandSecond = new MySqlCommand(sqlInsertSecond, connection, transaction);
MySqlParameter parameterCondition = new MySqlParameter("@condition", MySqlDbType.String);
parameterCondition.Value = param.Weathers.WeatherCondition;
commandSecond.Parameters.Add(parameterCondition);
MySqlParameter parameterDate = new MySqlParameter("@date", MySqlDbType.String);
parameterDate.Value = param.Weathers.Date;
commandSecond.Parameters.Add(parameterDate);
MySqlParameter parameterTempMin = new MySqlParameter("@tempMin", MySqlDbType.String);
parameterTempMin.Value = param.Weathers.TemperatureMin;
commandSecond.Parameters.Add(parameterTempMin);
MySqlParameter parameterTempMax = new MySqlParameter("@tempMax", MySqlDbType.String);
parameterTempMax.Value = param.Weathers.TemperatureMax;
commandSecond.Parameters.Add(parameterTempMax);
MySqlParameter parameterPrecipitation = new MySqlParameter("@precipitation", MySqlDbType.String);
parameterPrecipitation.Value = param.Weathers.Precipitation;
commandSecond.Parameters.Add(parameterPrecipitation);
commandSecond.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
throw new Exception("Ошибка при добавлении данных в БД");
}
finally
{
connection.Close();
}
}
else
{
throw new Exception("Отсутсвует таблица для вставки");
}
break;
default:
break;
}
}
/// <summary>
/// Получение данных из БД по id
/// </summary>
/// <param name="id">Id записи</param>
/// <param name="selectTable">Таблица для выборки</param>
/// <returns>Возвращает MySqlDatabase или null если запись не найдена</returns>
public MySqlDatabase GetItem(int id, SelectTable selectTable)
{
string sqlSelect;
MySqlDatabase result;
switch (selectTable)
{
case SelectTable.Informations:
sqlSelect = "SELECT * FROM info WEHER `id`=@id";
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
MySqlCommand command = new MySqlCommand(sqlSelect, connection);
MySqlParameter parameterId = new MySqlParameter("@id", MySqlDbType.Int32);
parameterId.Value = id;
command.Parameters.Add(parameterId);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result = new MySqlDatabase();
while (reader.Read())
{
result.Informations.Id = (int)reader.GetValue(0);
result.Informations.City = (string)reader.GetValue(1);
result.Informations.Link = (string)reader.GetValue(2);
}
}
else
{
result = null;
}
}
}
finally
{
connection.Close();
}
break;
case SelectTable.Weathers:
sqlSelect = "SELECT * FROM weather WEHER `id`= @id";
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
MySqlCommand command = new MySqlCommand(sqlSelect, connection);
MySqlParameter parameterId = new MySqlParameter("@id", MySqlDbType.Int32);
parameterId.Value = id;
command.Parameters.Add(parameterId);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result = new MySqlDatabase();
while (reader.Read())
{
result.Weathers.Id = (int)reader.GetValue(0);
result.Weathers.WeatherCondition = (string)reader.GetValue(1);
result.Weathers.Date = (string)reader.GetValue(2);
result.Weathers.TemperatureMin = (string)reader.GetValue(3);
result.Weathers.TemperatureMax = (string)reader.GetValue(4);
result.Weathers.Precipitation = (string)reader.GetValue(5);
result.Weathers.InfoId = (int)reader.GetValue(6);
}
}
else
{
result = null;
}
}
}
finally
{
connection.Close();
}
break;
case SelectTable.All:
sqlSelect = "SELECT * FROM info JOIN weather ON info.Id=weather.Info_Id WHERE info.Id=@id";
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
MySqlCommand command = new MySqlCommand(sqlSelect, connection);
MySqlParameter parameterId = new MySqlParameter("@id", MySqlDbType.Int32);
parameterId.Value = id;
command.Parameters.Add(parameterId);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result = new MySqlDatabase();
while (reader.Read())
{
result.Informations.Id = (int)reader.GetValue(0);
result.Informations.City = (string)reader.GetValue(1);
result.Informations.Link = (string)reader.GetValue(2);
result.Weathers.Id = (int)reader.GetValue(3);
result.Weathers.WeatherCondition = (string)reader.GetValue(4);
result.Weathers.Date = (string)reader.GetValue(5);
result.Weathers.TemperatureMin = (string)reader.GetValue(6);
result.Weathers.TemperatureMax = (string)reader.GetValue(7);
result.Weathers.Precipitation = (string)reader.GetValue(8);
result.Weathers.InfoId = (int)reader.GetValue(9);
}
}
else
{
result = null;
}
}
}
finally
{
connection.Close();
}
break;
default:
result = null;
break;
}
return(result);
}
/// <summary>
/// Получение списка данных из БД по id
/// </summary>
/// <param name="selectTable">Таблица для выборки</param>
/// <returns>Возвращает List<MySqlDatabase> или null если запись не найдена</returns>
public List <MySqlDatabase> GetItems(SelectTable selectTable)
{
string sqlSelect;
List <MySqlDatabase> result;
switch (selectTable)
{
case SelectTable.Informations:
sqlSelect = "SELECT * FROM info";
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
MySqlCommand command = new MySqlCommand(sqlSelect, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result = new List <MySqlDatabase>();
while (reader.Read())
{
MySqlDatabase database = new MySqlDatabase
{
Informations = new Info
{
Id = (int)reader.GetValue(0),
City = (string)reader.GetValue(1),
Link = (string)reader.GetValue(2),
}
};
result.Add(database);
}
}
else
{
result = null;
}
}
}
finally
{
connection.Close();
}
break;
case SelectTable.Weathers:
sqlSelect = "SELECT * FROM weather";
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
MySqlCommand command = new MySqlCommand(sqlSelect, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result = new List <MySqlDatabase>();
while (reader.Read())
{
MySqlDatabase database = new MySqlDatabase
{
Weathers = new Weather
{
Id = (int)reader.GetValue(0),
WeatherCondition = (string)reader.GetValue(1),
Date = (string)reader.GetValue(2),
TemperatureMin = (string)reader.GetValue(3),
TemperatureMax = (string)reader.GetValue(4),
Precipitation = (string)reader.GetValue(5),
InfoId = (int)reader.GetValue(6)
}
};
result.Add(database);
}
}
else
{
result = null;
}
}
}
finally
{
connection.Close();
}
break;
case SelectTable.All:
sqlSelect = "SELECT * FROM info JOIN weather ON info.Id=weather.Info_Id";
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
MySqlCommand command = new MySqlCommand(sqlSelect, connection);
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result = new List <MySqlDatabase>();
while (reader.Read())
{
MySqlDatabase database = new MySqlDatabase
{
Informations = new Info
{
Id = (int)reader.GetValue(0),
City = (string)reader.GetValue(1),
Link = (string)reader.GetValue(2)
},
Weathers = new Weather
{
Id = (int)reader.GetValue(3),
WeatherCondition = (string)reader.GetValue(4),
Date = (string)reader.GetValue(5),
TemperatureMin = (string)reader.GetValue(6),
TemperatureMax = (string)reader.GetValue(7),
Precipitation = (string)reader.GetValue(8),
InfoId = (int)reader.GetValue(9)
}
};
result.Add(database);
}
}
else
{
result = null;
}
}
}
finally
{
connection.Close();
}
break;
default:
result = null;
break;
}
return(result);
}
public JsonController(SelectTable _selectTable, DbContext _db, IIdentityService identityService)
{
selectTable = _selectTable;
db = _db;
_identitySvc = identityService;
}
IEnumerator GetColumnTypesQ(String input, string baseData)
{
string url = "http://www.williamrobertfunk.com/applications/vedic/actions/query.php";
WWWForm form = new WWWForm();
form.AddField("dbname", dbname.text);
form.AddField("hostname", hostname.text);
form.AddField("username", username.text);
form.AddField("password", password.text);
form.AddField("query", input);
UnityWebRequest www = UnityWebRequest.Post(url, form);
yield return www.Send();
if (www.isError)
{
Debug.Log(www.error);
}
else
{
VedicDatabase.db = DatabaseBuilder.ConstructDB(dbname.text, baseData);
VedicDatabase.isDatabaseNull = false;
string reply = www.downloadHandler.text;
string podData = reply.Substring(reply.IndexOf("##SelectTable##:{") + 17);
// This Table ID sould be unlike original import
// It should consist of a combo db name it came from, and select query random unique hash
SelectTable sTable = new SelectTable(podData, "DTypes", "FunkDataTypesTable");
DatabaseUtilities.Table t = sTable.GetTable();
List<string> colTypes = new List<string>();
for (int i = 0; i < t.columns[0].fields.Count; i++)
{
colTypes.Add(t.columns[0].fields[i]);
}
int numOfColumns = VedicDatabase.GetNumOfColumns();
List<string> colTypes2 = new List<string>();
for (int i = (colTypes.Count - numOfColumns); i < colTypes.Count; i++)
{
colTypes2.Add(colTypes[i]);
}
int counter = 0;
for (int j = 0; j < VedicDatabase.db.tables.Count; j++)
{
for(int k = 0; k < VedicDatabase.db.tables[j].columns.Count && counter < colTypes2.Count; k++, counter++)
{
VedicDatabase.db.tables[j].columns[k].SetColor(VariableColorTable.GetVariableColor(colTypes2[counter]));
VedicDatabase.db.tables[j].columns[k].SetType(colTypes2[counter]);
}
}
GetKeyColumns("SELECT CONCAT(TABLE_NAME, '.', COLUMN_NAME) AS 'foreign key', " +
"CONCAT(REFERENCED_TABLE_NAME, '.', REFERENCED_COLUMN_NAME) AS 'references' FROM " +
"INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL");
}
}
IEnumerator GetKeyColumnsQ(String input)
{
string url = "http://www.williamrobertfunk.com/applications/vedic/actions/query.php";
WWWForm form = new WWWForm();
form.AddField("dbname", dbname.text);
form.AddField("hostname", hostname.text);
form.AddField("username", username.text);
form.AddField("password", password.text);
form.AddField("query", input);
UnityWebRequest www = UnityWebRequest.Post(url, form);
yield return www.Send();
if (www.isError)
{
Debug.Log(www.error);
}
else
{
string reply = www.downloadHandler.text;
string podData = reply.Substring(reply.IndexOf("##SelectTable##:{") + 17);
// This Table ID sould be unlike original import
// It should consist of a combo db name it came from, and select query random unique hash
SelectTable sTable = new SelectTable(podData, "Keys", "FunkKeysTable");
DatabaseUtilities.Table t = sTable.GetTable();
for (int i = 0; i < t.columns[0].fields.Count; i++)
{
int tabIndex = -1;
int colIndex = -1;
string fKey = t.columns[0].fields[i];
string referenced = t.columns[1].fields[i];
if (fKey != null && referenced != null && fKey.Length > 0 && referenced.Length > 0)
{
for (int j = 0; j < VedicDatabase.db.tables.Count; j++)
{
if (VedicDatabase.db.tables[j].GetName() == referenced.Substring(0, referenced.IndexOf('.')))
{
tabIndex = j;
break;
}
}
if (tabIndex < 0) continue;
for (int k = 0; k < VedicDatabase.db.tables[tabIndex].columns.Count; k++)
{
if (VedicDatabase.db.tables[tabIndex].columns[k].GetName() == referenced.Substring(referenced.IndexOf('.') + 1))
{
colIndex = k;
break;
}
}
if (colIndex < 0) continue;
VedicDatabase.db.tables[tabIndex].columns[colIndex].AddFKey(fKey.Substring(0, fKey.IndexOf('.')), fKey.Substring(fKey.IndexOf('.') + 1));
}
}
GameObject.FindGameObjectWithTag("Analytics").GetComponent<AnalyticManager>().BuildAnalytics();
ViewAssembler.GenerateViewObject(VedicDatabase.db, false, false, -1);
}
}
IEnumerator GetKeyColumnsQ(String input)
{
string url = "http://www.williamrobertfunk.com/applications/vedic/actions/query.php";
WWWForm form = new WWWForm();
form.AddField("dbname", dbname.text);
form.AddField("hostname", hostname.text);
form.AddField("username", username.text);
form.AddField("password", password.text);
form.AddField("query", input);
UnityWebRequest www = UnityWebRequest.Post(url, form);
yield return(www.Send());
if (www.isError)
{
Debug.Log(www.error);
}
else
{
string reply = www.downloadHandler.text;
string podData = reply.Substring(reply.IndexOf("##SelectTable##:{") + 17);
// This Table ID sould be unlike original import
// It should consist of a combo db name it came from, and select query random unique hash
SelectTable sTable = new SelectTable(podData, "Keys", "FunkKeysTable");
DatabaseUtilities.Table t = sTable.GetTable();
for (int i = 0; i < t.columns[0].fields.Count; i++)
{
int tabIndex = -1;
int colIndex = -1;
string fKey = t.columns[0].fields[i];
string referenced = t.columns[1].fields[i];
if (fKey != null && referenced != null && fKey.Length > 0 && referenced.Length > 0)
{
for (int j = 0; j < VedicDatabase.db.tables.Count; j++)
{
if (VedicDatabase.db.tables[j].GetName() == referenced.Substring(0, referenced.IndexOf('.')))
{
tabIndex = j;
break;
}
}
if (tabIndex < 0)
{
continue;
}
for (int k = 0; k < VedicDatabase.db.tables[tabIndex].columns.Count; k++)
{
if (VedicDatabase.db.tables[tabIndex].columns[k].GetName() == referenced.Substring(referenced.IndexOf('.') + 1))
{
colIndex = k;
break;
}
}
if (colIndex < 0)
{
continue;
}
VedicDatabase.db.tables[tabIndex].columns[colIndex].AddFKey(fKey.Substring(0, fKey.IndexOf('.')), fKey.Substring(fKey.IndexOf('.') + 1));
}
}
GameObject.FindGameObjectWithTag("Analytics").GetComponent <AnalyticManager>().BuildAnalytics();
ViewAssembler.GenerateViewObject(VedicDatabase.db, false, false, -1);
}
}
private void button16_Click(object sender, EventArgs e)
{
SelectTable ST = new SelectTable();
ST.ShowDialog();
}
