// Use this for initialization
void Start () {
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
const string sql = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.IDCARTE = '1' AND atk.IDATK='1'";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
IDCARTE = reader.GetInt32(0);
}
reader.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
public Store(string fname, string cachedir)
{
updates = new ArrayList ();
newchans = new ArrayList ();
connection = new SqliteConnection ("URI=file:" + fname);
connection.Open ();
IDbCommand dbcmd = connection.CreateCommand ();
// setting encoding only works on sqlite3
//dbcmd.CommandText = "PRAGMA encoding = \"UTF-8\";";
//if (dbcmd.ExecuteNonQuery () != 0) {
// throw new Exception ("Couldn't set encoding");
//}
string sql = "PRAGMA table_info(channel)";
dbcmd = connection.CreateCommand ();
dbcmd.CommandText = sql;
try {
IDataReader result = dbcmd.ExecuteReader ();
if (!result.Read ()) {
// schema doesn't exist.
System.Console.WriteLine ("Creating new database schema");
CreateSchema ();
}
result.Close ();
} catch (Exception e) {
// TODO: catch further exceptions here.
System.Console.WriteLine ("Error executing reader: {0}", e.ToString ());
}
_cachedir = cachedir;
InitializeCache ();
// TODO: catch problems making the cache dir
}
// Use this for initialization
void Start()
{
//E:\Etude\Projet\Projet_4e_semestre\GITHUB\New_Unity_Project\Assets
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
const string sql = "select nomcarte from carte";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
string NomCarte = reader.GetString(0);
Debug.Log("Carte: " + NomCarte + "\n");
}
reader.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
/// <summary>
/// Creates a new connection to the icontent.cache file.
/// </summary>
/// <param name="filename">The file to connect to.</param>
public IGADatabaseConnector(String filename)
{
try {
sqlite = new SqliteConnection("URI=file:" + filename + ",version=3");
sqlite.Open();
SqliteCommand query = new SqliteCommand("SELECT [appId] FROM [contentlist] LIMIT 1", sqlite);
Object result = query.ExecuteScalar();
sqlite.Close();
if (result == null)
{
this._appID = 0;
}
else
{
this._appID = (int)result;
}
if (this._appID > 0) {
this._appSupported = Common.AppInfos.ContainsKey(this._appID);
} else {
this._appSupported = false;
}
if (this._appSupported)
{
_appInfo = Common.AppInfos[this._appID];
}
} catch (Exception) {
throw new DatabaseConnectionFailureException();
}
}
public SqliteStore(string connectionString, string table)
: base(table)
{
this.connectionString = connectionString;
dbcon = new SqliteConnection(connectionString);
dbcon.Open();
}
public void Initialise(string connectionString)
{
m_connectionString = connectionString;
m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString);
m_connection = new SqliteConnection(m_connectionString);
m_connection.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_connection, assem, "EstateStore");
m.Update();
m_connection.Close();
m_connection.Open();
Type t = typeof(EstateSettings);
m_Fields = t.GetFields(BindingFlags.NonPublic |
BindingFlags.Instance |
BindingFlags.DeclaredOnly);
foreach (FieldInfo f in m_Fields)
if (f.Name.Substring(0, 2) == "m_")
m_FieldMap[f.Name.Substring(2)] = f;
}
public SqliteCommand (string sqlText, SqliteConnection dbConn, IDbTransaction trans)
{
sql = sqlText;
parent_conn = dbConn;
transaction = trans;
sql_params = new SqliteParameterCollection ();
}
public SQLiteGenericData(string connectionString)
{
m_Connection = new SqliteConnection(connectionString);
m_Connection.Open();
Migration m = new Migration(m_Connection, this.GetType().Assembly, "GenericStore");
m.Update();
}
override public void Dispose()
{
if (m_conn != null)
{
m_conn.Close();
m_conn = null;
}
}
public void init(int idcarte, string numserie, int idatk, int idatk2)
{
this.idcarte = idcarte;
this.numserie = numserie;
xdeck = this.transform.position.x;
ydeck = this.transform.position.y;
zdeck = this.transform.position.z;
ObservJ1 = false;
ObservJ2 = false;
mettredsdeck = false;
const string connectionString = "URI=file:E://Etude/Projet/Projet_4e_semestre/GITHUB/New_Unity_Project/Assets/testbdd.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
IDbCommand dbcmd = dbcon.CreateCommand();
try
{
dbcon.Open();
string sql = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.NUMSERIE = '" + this.numserie + "' AND atk.IDATK=" + this.idatk + "";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
NomCarte = reader.GetString(0);
puisscrea = reader.GetInt32(1);
vitcrea = reader.GetInt32(2);
typecrea = reader.GetString(3);
pvcrea = reader.GetInt32(4);
nomatk = reader.GetString(5);
dommatk = reader.GetInt32(6);
vitatk = reader.GetInt32(7);
}
reader.Dispose();
string sql2 = "SELECT DISTINCT nomcarte, puisscrea, vitcrea, typecrea, pvcrea, nomatk, dommatk, vitatk FROM carte INNER JOIN creature ON creature.IDCARTE = carte.IDCARTE INNER JOIN j_crea_atk On j_crea_atk.IDCREA = creature.IDCARTE INNER JOIN atk On atk.IDATK = j_crea_atk.IDATK WHERE carte.NUMSERIE = '" + this.numserie + "' AND atk.IDATK=" + this.idatk2 + "";
dbcmd.CommandText = sql2;
IDataReader reader2 = dbcmd.ExecuteReader();
while (reader2.Read())
{
nomatk2 = reader2.GetString(5);
dommatk2 = reader2.GetInt32(6);
vitatk2 = reader2.GetInt32(7);
}
reader2.Dispose();
}
catch (Exception ex)
{
Debug.Log(ex.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
public virtual SqliteConnection getNewConnection()
{
SqliteConnectionStringBuilder conStringBuilder = new SqliteConnectionStringBuilder();
conStringBuilder.Uri = config.DatabaseUri;
conStringBuilder.Version = config.DatabaseVersion;
SqliteConnection con = new SqliteConnection(conStringBuilder.ConnectionString);
con.Open ();
return con;
}
public SqliteDataKit()
{
conn = new SqliteConnection ();
if (!File.Exists (Path.Combine (Configuration.GetInstance ().UserConfigDir, dbfile)))
isNew = true;
conn.ConnectionString = "URI=file:" + Path.Combine (Configuration.GetInstance ().UserConfigDir, dbfile);
conn.Open ();
if (isNew)
SetupDb ();
}
public static IDataReader ExecuteSQL(string db, string sql)
{
var connection = new SqliteConnection(db);
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sql;
var result = command.ExecuteReader();
connection.Close();
return result;
}
public new void ExecuteNonQuery(string sql, string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = 1200;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
public new object ExecuteScalar(string sql, string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = 1200;
conn.Open();
object retVal = cmd.ExecuteScalar();
conn.Close();
return retVal;
}
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_connectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "RegionStore");
int version = m.Version;
if (version <= 14)
{
if (version == 0)
{
//read rex tables and add to rex database
m_log.Info("[regionstore] converting rex tables to rexobjectproperties");
if (!ConvertLegacyRexDataToModreX())
{
conn.Close();
return false;
}
m_log.Info("[RegionStore] Update region migrations");
//Add new field to Land table
SqliteCommand addAuthbyerIDCmd = new SqliteCommand(addAuthbyerID, conn);
addAuthbyerIDCmd.ExecuteNonQuery();
//Change migration to version 1
m.Version = 1;
}
//Run migrations up to 9
//Note: this run migrations only to point nine since only those files exist in application resources.
m.Update();
//Skip over 10. Change version to 10
//This skips adding of the ClickAction since that already exists in 0.4 database
//m.Version = 10;
}
conn.Close();
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[RegionStore] Migration failed. Reason: {0}", e);
return false;
}
}
protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
{
lock (connection)
{
SqliteConnection newConnection =
(SqliteConnection)((ICloneable)connection).Clone();
newConnection.Open();
cmd.Connection = newConnection;
//Console.WriteLine("XXX " + cmd.CommandText);
return cmd.ExecuteReader();
}
}
public Database(string file)
{
bool create = false;
if (!File.Exists (file)) {
create = true;
}
try {
StreamReader srVersion = new StreamReader (file+".version");
dbVersion = srVersion.ReadToEnd ();
if (dbVersion != null) {
dbVersion = dbVersion.Trim();
}
srVersion.Close();
}
catch {
dbVersion = null;
}
string connectionString = "URI=file:"+file;
connection = new SqliteConnection(connectionString);
connection.Open();
command = connection.CreateCommand();
if (create) {
Conf.EmptyCache();
Assembly thisAssembly = Assembly.GetEntryAssembly ();
Stream stream = thisAssembly.GetManifestResourceStream("Database.sql");
if (stream != null) {
StreamReader sr = new StreamReader (stream);
string sql = sr.ReadToEnd();
command.CommandText = sql;
command.ExecuteNonQuery();
StreamWriter swVersion = new StreamWriter (file+".version", false);
swVersion.Write (Defines.VERSION);
swVersion.Close();
dbVersion = Defines.VERSION;
}
else {
System.Console.WriteLine("Error creating the database");
}
}
if (dbVersion == null || !dbVersion.Equals (Defines.VERSION)) {
UpdateDatabase(file, dbVersion);
}
}
/// <summary>
/// <list type="bullet">
/// <item>Initialises AssetData interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// <item>use default URI if connect string is empty.</item>
/// </list>
/// </summary>
/// <param name="dbconnect">connect string</param>
override public void Initialise(string dbconnect)
{
if (dbconnect == string.Empty)
{
dbconnect = "URI=file:Asset.db,version=3";
}
m_conn = new SqliteConnection(dbconnect);
m_conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(m_conn, assem, "AssetStore");
m.Update();
return;
}
//Diese Methode Schreibt ein Gegebenes Auto in die Datenbank.
// Das Auto wird als Parameter gegeben
public void addauto(Autos autodaten)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
_connection .Open();
sql = "INSERT INTO AUTOS (KENNZEICHEN, STATUS) Values ('"+ autodaten.getKennzeichen ()+"','"+autodaten.getStatus ()+"')";
_command.CommandText = sql;
_command.ExecuteNonQuery();
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
//Debug.Log (autodaten.getKennzeichen ());
}
// Hier wird eine gegebene Drone in die Tabelle DRONEN eingefüllt
public void addDrone(Drone drone)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
_connection .Open();
sql = "INSERT INTO DRONEN (DRONENNAME,AKTUELLERKNOTEN,HOMEPUNKTID, STATUS,CARTOSHOW) Values ('"+ drone.getName()+"','2','2','0','ef')";
_command.CommandText = sql;
_command.ExecuteNonQuery();
_command.Dispose();
_command = null;
_connection .Close();
_connection.Dispose ();
_connection = null;
sql = null;
}
/// <summary>
/// <list type="bullet">
/// <item>Initialises Inventory interface</item>
/// <item>Loads and initialises a new SQLite connection and maintains it.</item>
/// <item>use default URI if connect string string is empty.</item>
/// </list>
/// </summary>
/// <param name="dbconnect">connect string</param>
public void Initialise(string dbconnect)
{
if (!m_Initialized)
{
m_Initialized = true;
if (dbconnect == string.Empty)
{
dbconnect = "URI=file:inventoryStore.db,version=3";
}
m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect);
conn = new SqliteConnection(dbconnect);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "InventoryStore");
m.Update();
SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn);
invItemsDa = new SqliteDataAdapter(itemsSelectCmd);
// SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa);
SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn);
invFoldersDa = new SqliteDataAdapter(foldersSelectCmd);
ds = new DataSet();
ds.Tables.Add(createInventoryFoldersTable());
invFoldersDa.Fill(ds.Tables["inventoryfolders"]);
setupFoldersCommands(invFoldersDa, conn);
m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions");
ds.Tables.Add(createInventoryItemsTable());
invItemsDa.Fill(ds.Tables["inventoryitems"]);
setupItemsCommands(invItemsDa, conn);
m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions");
ds.AcceptChanges();
}
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_connectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "InventoryStore");
if (m.Version == 0)
{
//Apply all changes to db
SqliteCommand addSalePriceCmd = new SqliteCommand(addSalePrice, conn);
addSalePriceCmd.ExecuteNonQuery();
SqliteCommand addSaleTypeCmd = new SqliteCommand(addSaleType, conn);
addSaleTypeCmd.ExecuteNonQuery();
SqliteCommand addCreationDateCmd = new SqliteCommand(addCreationDate, conn);
addCreationDateCmd.ExecuteNonQuery();
SqliteCommand addGroupIDCmd = new SqliteCommand(addGroupID, conn);
addGroupIDCmd.ExecuteNonQuery();
SqliteCommand addGroupOwnedCmd = new SqliteCommand(addGroupOwned, conn);
addGroupOwnedCmd.ExecuteNonQuery();
SqliteCommand addFlagsCmd = new SqliteCommand(addFlags, conn);
addFlagsCmd.ExecuteNonQuery();
//then change version number
m.Version = 1;
}
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[InventoryStore] Migration failed. Reason: {0}", e);
return false;
}
}
public ArrayList Select(string quer)
{
Resultd = new ArrayList();
string connectionString = "URI=file:" + Application.dataPath + "/Qjournaldb.s3db";
IDbConnection dbcon = new SqliteConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
dbcmd.CommandText = quer;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
Resultd.Add(reader.GetString(2));
//Debug.Log(reader.GetString(2));
}
// clean up
reader.Dispose();
dbcmd.Dispose();
dbcon.Close();
return Resultd;
}
// Hier wird überprüft ob eine bestimmte Tabelle in der Datenbank existieren. Die Anzahl der Funde wird Zurückgegeben. 1=Existiert; 0=Gibt es nicht
public int abfrageexisttabelle(string Tabellenname)
{
IDbConnection _connection = new SqliteConnection(_strDBName);
IDbCommand _command = _connection .CreateCommand();
string sql;
IDataReader _reader;
_connection .Open();
sql = "SELECT count(name) as Count FROM sqlite_master WHERE type='table' AND name='"+Tabellenname+"'";
_command.CommandText = sql;
_reader = _command.ExecuteReader();
_reader.Read ();
_connection .Close();
_connection.Dispose ();
_connection = null;
_command.Dispose ();
_command = null;
int anzahl = System.Convert.ToInt32 (_reader ["Count"]);
_reader.Close();
_reader.Dispose ();
_reader = null;
return anzahl;
}
public SQLiteAuthenticationData(string connectionString, string realm)
: base(connectionString)
{
m_Realm = realm;
m_connectionString = connectionString;
if (!m_initialized)
{
m_Connection = new SqliteConnection(connectionString);
m_Connection.Open();
using (SqliteConnection dbcon = (SqliteConnection)((ICloneable)m_Connection).Clone())
{
dbcon.Open();
Migration m = new Migration(dbcon, GetType().Assembly, "AuthStore");
m.Update();
dbcon.Close();
}
m_initialized = true;
}
}
internal static IDbConnection GetProvider (string s) {
foreach (IDbConnection dc in providers) {
if (dc.ConnectionString == s) {
return dc;
}
}
#if USE_SQLITE
IDbConnection dbConn = new SqliteConnection ();
#else
OleDbConnection dbConn = new OleDbConnection ();
#endif
dbConn.ConnectionString = s;
dbConn.Open ();
if (!VerifyDatabase (dbConn)) {
throw new InvalidOperationException ();
}
providers.Add (dbConn);
return dbConn;
}
public void Create()
{
try
{
if(File.Exists(_uri))
{
_conn.Dispose();
// We want to start with a fresh db for each full run
// The database is created on the first open()
File.Delete(_uri);
_conn = new SqliteConnection (_connectionString);
}
}
catch(Exception e)
{
throw e;
}
SqliteCommand createCommand = new SqliteCommand("CREATE TABLE t1(t TEXT, f FLOAT, i INTEGER, b TEXT);",_conn);
SqliteCommand insertCommand = new SqliteCommand("INSERT INTO t1 (t, f, i, b ) VALUES('" + stringvalue + "',123,123,'123')",_conn);
try
{
_conn.Open();
createCommand.ExecuteNonQuery();
insertCommand.ExecuteNonQuery();
}
catch(Exception e)
{
throw new AssertionException("Create table failed",e);
}
finally
{
_conn.Close();
}
}
public bool Convert()
{
try
{
SqliteConnection conn = new SqliteConnection(m_connectionString);
conn.Open();
Assembly assem = GetType().Assembly;
Migration m = new Migration(conn, assem, "UserStore");
if (m.Version == 0)
{
m.Version = 1;
}
conn.Close();
return true;
}
catch (Exception e)
{
m_log.ErrorFormat("[UserStore] Migration failed. Reason: {0}", e);
return false;
}
}
public stats_default_page_values rep_DefaultReport_data(SqliteConnection db, List<Scene> m_scene)
{
stats_default_page_values returnstruct = new stats_default_page_values();
returnstruct.all_scenes = m_scene.ToArray();
lock (db)
{
string SQL = @"SELECT COUNT(DISTINCT agent_id) as agents, COUNT(*) as sessions, AVG(avg_fps) as client_fps,
AVG(avg_sim_fps) as savg_sim_fps, AVG(avg_ping) as sav_ping, SUM(n_out_kb) as num_in_kb,
SUM(n_out_pk) as num_in_packets, SUM(n_in_kb) as num_out_kb, SUM(n_in_pk) as num_out_packets, AVG(mem_use) as sav_mem_use
FROM stats_session_data;";
SqliteCommand cmd = new SqliteCommand(SQL, db);
SqliteDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
sdr.Read();
returnstruct.total_num_users = Convert.ToInt32(sdr["agents"]);
returnstruct.total_num_sessions = Convert.ToInt32(sdr["sessions"]);
returnstruct.avg_client_fps = Convert.ToSingle(sdr["client_fps"]);
returnstruct.avg_sim_fps = Convert.ToSingle(sdr["savg_sim_fps"]);
returnstruct.avg_ping = Convert.ToSingle(sdr["sav_ping"]);
returnstruct.total_kb_out = Convert.ToSingle(sdr["num_out_kb"]);
returnstruct.total_kb_in = Convert.ToSingle(sdr["num_in_kb"]);
returnstruct.avg_client_mem_use = Convert.ToSingle(sdr["sav_mem_use"]);
}
}
return returnstruct;
}
