void InitDatabase()
{
using (var transaction = BeginTransaction())
{
// Insert the predefined rows
DbProperties.InsertRecord(new DbPropertiesRow()
{
});
InitVirtualGroups();
GroupStates.InsertRecord(new DbGroupState()
{
GroupId = YamsterGroup.AllCompanyGroupId,
ShowInYamster = true,
ShouldSync = false,
TrackRead = true
});
GroupStates.InsertRecord(new DbGroupState()
{
GroupId = YamsterGroup.ConversationsGroupId,
ShowInYamster = true,
ShouldSync = true,
TrackRead = true
});
archiveDb.SetObjectVersion("Core", CurrentCoreDbVersion);
transaction.Commit();
}
}
public void OneTimeSetUp()
{
DbProp = new DbProperties
{
DbType = TestSettings.Default.DbType,
DbServer = TestSettings.Default.DbServer,
DbPort = TestSettings.Default.DbPort,
ServiceName = TestSettings.Default.ServiceName,
DbSchemaName = TestSettings.Default.DbSchemaName,
DbUsername = TestSettings.Default.DbUsername,
DbPassword = TestSettings.Default.DbPassword,
};
AlmProp = new AlmProperies
{
AlmServer = TestSettings.Default.AlmServer,
AlmPort = TestSettings.Default.AlmPort,
Domain = TestSettings.Default.Domain,
Project = TestSettings.Default.Project,
AlmAdminName = TestSettings.Default.AlmAdminName,
AlmAdminPassword = TestSettings.Default.AlmAdminPassword,
IsHttps = TestSettings.Default.IsHttps
};
AlmConnetor = AlmConnector.Instance;
AlmConnetor.Init(AlmProp);
AlmConnetor.GetCustomizationData();
}
public DbProperties GetSettings()
{
if (DbProperties != null)
{
return(DbProperties);
}
try
{
var rawProps = ParseJson();
var properties = new DbProperties
{
Server = rawProps[Server],
Port = rawProps[Port],
Username = rawProps[Username],
Password = rawProps[Password],
DatabaseName = rawProps[DatabaseName]
};
DbProperties = properties;
return(properties);
}
catch (Exception)
{
}
return(new DbProperties());
}
public static bool InstallReservoirSensors(this IRepository <IDbProperties> repository, WaterReservoirState state, out WaterReservoirState waterReservoirState)
{
var sensors = state.Sensors;
var dbProperties = new List <IDbProperties>();
foreach (var sensor in sensors.ToList())
{
var dataTypes = new List <IDataTypes>
{
new DataTypes("@reservoir", SqlDbType.NVarChar, 50, state.ReservoirId, ParameterDirection.Input, false, false, ""),
new DataTypes("@tag", SqlDbType.NVarChar, 50, sensor.Tag, ParameterDirection.Input, false, false, ""),
new DataTypes("@role", SqlDbType.NVarChar, 50, sensor.Role, ParameterDirection.Input, false, false, ""),
new DataTypes("@sensorid", SqlDbType.UniqueIdentifier, 0, string.Empty, ParameterDirection.Output, false, false, "@sensorid")
};
var repos = new DbProperties("InstallReservoirSensor", dataTypes, string.Empty, true, "@sensorid", sensor.Tag);
dbProperties.Add(repos);
}
var x = repository.Update(dbProperties);
if (x > 0)
{
var sensorsSpec = new List <SensorSpec>();
foreach (var sensor in sensors.ToList())
{
var outputs = repository.OutPuts;
sensor.SensorId = outputs.ToList().FirstOrDefault(y => y.Identifier == sensor.Tag).Value;
sensorsSpec.Add(sensor);
}
waterReservoirState = new WaterReservoirState(state.ReservoirId, state.Height, state.AlertHeight, sensorsSpec);
return(true);
}
waterReservoirState = state;
return(false);
}
public static AlmDbClient Init(DbProperties prop)
{
if (_inst != null)
{
return(_inst);
}
_inst = new AlmDbClient();
switch (prop.DbType)
{
//1-H2
case 1:
throw new NotImplementedException("H2 database is not supported in ALM");
//2-MSSQL
case 2:
_inst._connector = _inst.SqlInit(prop);
break;
//3-Oracle
case 3:
_inst._connector = _inst.OraInit(prop);
break;
default:
throw new NullReferenceException("Wrong DB Type");
}
return(_inst);
}
//private methods
private void DisplayConnectionString(DbProperties properties)
{
Form.Server = properties.Server;
Form.Port = properties.Port;
Form.Username = properties.Username;
Form.Password = properties.Password;
}
private static void CheckDBVersion()
{
string dbVersion = DbProperties.GetDbVersion();
if (_CurrentdbVersion != dbVersion)
{
throw new Baumax.Contract.Exceptions.WrongDBVersionException(string.Format("DB has version: {0}. Application server can work with DB version: {1}.", dbVersion, _CurrentdbVersion));
}
}
public DbConnection InitConnection(DbProperties dbProperties)
{
Connection = DatabaseFactory.CreateConnection(dbProperties);
if (TestConnection() && dbProperties.DatabaseName != null)
{
return(Connection);
}
return(null);
}
public DbProperties CreateConnection(string server, string port, string username, string password,
string dbName)
{
var properties = new DbProperties
{
Server = server, Port = port, Username = username, Password = password, DatabaseName = dbName
};
DbProperties = properties;
Connection = DatabaseFactory.CreateConnection(properties);
return(properties);
}
// Use this for initialization
void Start()
{
dbProp = GetComponent <DbProperties> (); //Use GetComponent<nameComponent>(); when this belongs to
//the same gameobject your calling it from.
connectionInfo = "Server=" + dbProp.db_host + ";Database=" + dbProp.db + ";Uid=" + dbProp.db_user + ";Pwd=" + dbProp.db_pass + ";";
conn = null;
rdr = null;
cmd = null;
//Consult ();
//Initialize UI Texts
}
public void SaveSettings(DbProperties properties)
{
var rawProps = new Dictionary <string, string>
{
{ Server, properties.Server },
{ Port, properties.Port },
{ Username, properties.Username },
{ Password, properties.Password },
{ DatabaseName, properties.DatabaseName }
};
WriteToFile(JsonConvert.SerializeObject(rawProps));
}
/// <summary>
/// 设置数据库属性
/// </summary>
/// <param name="forDatabase">数据库实例</param>
/// <returns>数据库属性</returns>
protected override DbProperties GetPropertiesFromDb(Database forDatabase)
{
string sql = "Select PRODUCT ,VERSION ,STATUS FROM Product_component_version where substr(product,1,6)='Oracle' ";
//"SELECT SERVERPROPERTY('productversion') AS [version], SERVERPROPERTY ('productlevel') AS [level], SERVERPROPERTY ('edition') AS [edition]";
DbProperties props = null;
System.Data.OracleClient.OracleConnectionStringBuilder builder = new System.Data.OracleClient.OracleConnectionStringBuilder(forDatabase.ConnectionString);
string dbname = builder.UserID;
Dictionary <string, object> dict = forDatabase.ExecuteReadDict(sql);
props = new DbProperties(dbname, "Oracle", dict["VERSION"].ToString(), dict["STATUS"].ToString(),
dict["PRODUCT"].ToString(), true, "||", _sqlToken, ".", "TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", "SYS_GUID()",
"sysdate", "SUBSTR", "nvl", "length");
return(props);
}
private IDbClient SqlInit(DbProperties prop)
{
//_conStr = $"user id={prop.DbUsername};" +
// $"password={prop.DbPassword};" +
// $"server=tcp:{prop.DbServer}, {prop.DbPort};" +
// "Trusted_Connection=false;" +
// $"database={prop.DbSchemaName}; " +
// "connection timeout=30";
_conStr = String.Format("user id={0};password={1};server=tcp:{2}, {3};Trusted_Connection=false;database={4}; connection timeout=30", prop.DbUsername, prop.DbPassword, prop.DbServer, prop.DbPort, prop.DbSchemaName);
IDbClient inst = new SqlDbClient();
return(inst);
}
public static bool CreateToilet(this IRepository <IDbProperties> repository, ToiletSpec spec)
{
var dataTypes = new List <IDataTypes>
{
new DataTypes("@floor", SqlDbType.NVarChar, 50, spec.FloorId, ParameterDirection.Input, false, false, ""),
new DataTypes("@tag", SqlDbType.NVarChar, 50, spec.Tag, ParameterDirection.Input, false, false, ""),
new DataTypes("@toilet", SqlDbType.UniqueIdentifier, 0, string.Empty, ParameterDirection.Output, false, false, "@toilet")
};
var repos = new DbProperties("CreateToilet", dataTypes, string.Empty, true, "@toilet");
var x = repository.Update(new[] { repos });
if (x > 0 || !string.IsNullOrWhiteSpace(repos.Id))
{
spec.ToiletId = repos.Id;
return(true);
}
return(false);
}
public static bool ConstructHostel(this IRepository <IDbProperties> repository, Construction hostel)
{
var dataTypes = new List <IDataTypes>
{
new DataTypes("@name", SqlDbType.NVarChar, 50, hostel.Detail.Name, ParameterDirection.Input, false, false, ""),
new DataTypes("@address", SqlDbType.NVarChar, 50, hostel.Detail.Address, ParameterDirection.Input, false, false, ""),
new DataTypes("@hostel", SqlDbType.UniqueIdentifier, 0, string.Empty, ParameterDirection.Output, false, false, "@hostel")
};
var repos = new DbProperties("ConstructHostel", dataTypes, string.Empty, true, "@hostel");
var x = repository.Update(new[] { repos });
if (x > 0 || !string.IsNullOrWhiteSpace(repos.Id))
{
hostel.Detail.HostelId = repos.Id;
return(true);
}
return(false);
}
public void ConnectDb()
{
//dbProp = gameObject.GetComponent<DbProperties> ();
DbProperties dbProp = GetComponent <DbProperties> ();
Debug.Log(dbProp.db);
MySqlConnection myconn = new MySqlConnection();
myconn.ConnectionString = connectionInfo;
try {
myconn.Open();
Debug.Log("Sucessfully conected");
}
catch (MySqlException error) {
Debug.Log("Failure database connection" + error);
}
}
/// <summary>
/// 设置数据库属性
/// </summary>
/// <param name="forDatabase">数据库实例</param>
/// <returns>数据库属性</returns>
protected override DbProperties GetPropertiesFromDb(Database forDatabase)
{
string sql = "SELECT SERVERPROPERTY('productversion') AS [version], SERVERPROPERTY ('productlevel') AS [level], SERVERPROPERTY ('edition') AS [edition]";
DbProperties props = null;
System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(forDatabase.ConnectionString);
string dbname = builder.InitialCatalog;
if (string.IsNullOrEmpty(dbname))
{
dbname = builder.DataSource;
}
Dictionary <string, object> dict = forDatabase.ExecuteReadDict(sql);
props = new DbProperties(dbname, "SQL Server", dict["VERSION"].ToString(), dict["LEVEL"].ToString(),
dict["EDITION"].ToString(), false, "+", _sqlToken, "_", "cast({0} as datetime)", "CAST(NEWID() AS VARCHAR(36))",
"GETDATE()", "SUBSTRING", "isnull", "len");
return(props);
}
public static bool CreateReservoir(this IRepository <IDbProperties> repository, ReservoirSpec spec)
{
var water = spec;
var dataTypes = new List <IDataTypes>
{
new DataTypes("@hostel", SqlDbType.NVarChar, 50, spec.HostelId, ParameterDirection.Input, false, false, ""),
new DataTypes("@tag", SqlDbType.NVarChar, 50, water.Tag, ParameterDirection.Input, false, false, ""),
new DataTypes("@height", SqlDbType.Int, 0, water.Height.ToString(), ParameterDirection.Input, false, false, ""),
new DataTypes("@reservoir", SqlDbType.UniqueIdentifier, 0, string.Empty, ParameterDirection.Output, false, false, "@reservoir")
};
var repos = new DbProperties("CreateReservoir", dataTypes, string.Empty, true, "@reservoir");
var x = repository.Update(new[] { repos });
if (x > 0 || !string.IsNullOrWhiteSpace(repos.Id))
{
spec.ReservoirId = repos.Id;
return(true);
}
return(false);
}
public DbMySqlConnectionManager(ILoggable loggable, MySqlConnection connection, DbProperties properties) :
this(loggable)
{
Connection = connection;
DbProperties = properties;
}
public static MySqlConnection CreateConnection(DbProperties properties)
{
var conn = new MySqlConnection(properties.CreateConnectionString());
return(conn);
}
void RebuildTables(RebuildableTable tablesToRebuild)
{
using (var transaction = this.BeginTransaction())
{
if ((tablesToRebuild & RebuildableTable.Groups) != 0)
{
Debug.WriteLine("Regenerating group data from archive");
this.Mapper.CreateTable(this.Groups);
InitVirtualGroups();
// NOTE: We don't call CreateTable() for GroupStates
foreach (var archiveGroupRef in archiveDb.ArchiveGroupRefs.QueryAll())
{
UpdateGroup(archiveGroupRef);
}
}
if ((tablesToRebuild & RebuildableTable.Conversations) != 0)
{
Debug.WriteLine("Regenerating conversation data from archive");
this.Mapper.CreateTable(this.Conversations);
foreach (var archiveConversationRef in archiveDb.ArchiveConversationRefs.QueryAll())
{
UpdateConversation(archiveConversationRef);
}
}
// It's important to do Users before Messages, since UpdateMessage() writes
// to the Users table
if ((tablesToRebuild & RebuildableTable.Users) != 0)
{
Debug.WriteLine("Regenerating user data from archive");
this.Mapper.CreateTable(this.Users);
foreach (var archiveUser in archiveDb.ArchiveUserRefs.QueryAll())
{
UpdateUser(archiveUser);
}
}
if ((tablesToRebuild & RebuildableTable.Messages) != 0)
{
Debug.WriteLine("Regenerating message data from archive");
this.Mapper.CreateTable(this.Messages);
// NOTE: We don't call CreateTable() for MessageStates
foreach (var archiveMessage in archiveDb.ArchiveMessages.QueryAll())
{
UpdateMessage(archiveMessage);
}
}
if ((tablesToRebuild & RebuildableTable.Properties) != 0)
{
Debug.WriteLine("Regenerating DbProperties table");
this.Mapper.CreateTable(this.DbProperties);
DbProperties.InsertRecord(new DbPropertiesRow()
{
});
}
if ((tablesToRebuild & RebuildableTable.SyncingFeeds) != 0)
{
Debug.WriteLine("Regenerating SyncingFeeds table");
this.Mapper.CreateTable(this.SyncingFeeds);
}
if ((tablesToRebuild & RebuildableTable.SyncingThreads) != 0)
{
Debug.WriteLine("Regenerating SyncingThreads table");
this.Mapper.CreateTable(this.SyncingThreads);
}
transaction.Commit();
}
}
private IDbClient OraInit(DbProperties prop)
{
//if (prop == null) throw new ArgumentNullException(nameof(prop));
throw new NotImplementedException();
}