// 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();
        }

    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;
    }