//Add-Migration vyzva-sqlite-001 -c NewData.Vyzva57Context_Sqlite
 //Update-Database vyzva-sqlite-001 -c NewData.Vyzva57Context_Sqlite
 protected override void OnConfiguring(DbContextOptionsBuilder options) {
   base.OnConfiguring(options);
   var config = ConfigurationManager.ConnectionStrings["Vyzva57-sqlite"];
   var conn = new SqliteConnection(config.ConnectionString);
   //var conn = new SqliteConnection("Data Source=" + MachinesLow.rootDir +  config.ConnectionString);
   options.UseSqlite(conn);
 }

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public virtual DatabaseModel Create(string connectionString, TableSelectionSet tableSelectionSet)
        {
            Check.NotEmpty(connectionString, nameof(connectionString));
            Check.NotNull(tableSelectionSet, nameof(tableSelectionSet));

            ResetState();

            using (_connection = new SqliteConnection(connectionString))
            {
                _connection.Open();
                _tableSelectionSet = tableSelectionSet;

                string databaseName = null;
                try
                {
                    databaseName = Path.GetFileNameWithoutExtension(_connection.DataSource);
                }
                catch (ArgumentException)
                {
                    // graceful fallback
                }

                _databaseModel.DatabaseName = !string.IsNullOrEmpty(databaseName)
                    ? databaseName
                    : _connection.DataSource;

                GetTables();
                GetColumns();
                GetIndexes();
                GetForeignKeys();
                return _databaseModel;
            }
        }

        static BaseDataSQLite()
        {
            #if DEBUG
            _sqliteFile = Paths.DebugSQLitePath;
            #else
            _sqliteFile = Paths.ReleaseSQLitePath;
            #endif

            //_connectionString = String.Format("Data Source=:memory:");
            //_connectionString = String.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=100;Synchronous=Normal;Journal Mode=WAL", _sqliteFile);
            _connectionString = String.Format("Data Source={0}", _sqliteFile);

            if (!File.Exists(_sqliteFile))
            {
                if (!File.Exists(Paths.SqlPath))
                    throw new FileNotFoundException("Cannot find SQL file: " + Paths.SqlPath);
                else {
                    // Generate database
                    var conn = new SqliteConnection(_connectionString);
                    conn.Open();
                    var sql = File.ReadAllText(Paths.SqlPath);
                    conn.Execute(sql);
                    conn.Dispose();
                }
            }
        }

 private async Task<IEnumerable<IPRangeInfo>> LocateIPv6Async(IPAddress ip)
 {
     var flatIP = new FlatIPAddress(ip);
     using (var connection = new SqliteConnection(_connectionString))
     {
         connection.Open();
         return await connection.ExecuteReaderAsync(dataReader =>
         {
             return new IPRangeInfo
             {
                 IPRange = new IPRange
                 {
                     Start = new FlatIPAddress { Low = (long)dataReader["StartLow"], High = (long)dataReader["StartHigh"] }.ToIPAddress(),
                     End = new FlatIPAddress { Low = (long)dataReader["EndLow"], High = (long)dataReader["EndHigh"] }.ToIPAddress(),
                 },
                 AreaId = Convert.ToInt32(dataReader["AreaId"]),
                 ProviderId = Convert.ToInt32(dataReader["ProviderId"]),
             };
         }, @"
             SELECT * FROM IPRangeInfos 
             WHERE (StartHigh < {1} OR (StartHigh = {1} AND StartLow <= {0}))
             AND (EndHigh > {1} OR (EndHigh = {1} AND EndLow >= {0}));",
         flatIP.Low, flatIP.High);
     }
 }

        private static void AddHistoricMessage(Message message)
        {
            var sql = string.Format("INSERT INTO {0} (MessageId, MessageType, Topic, Timestamp, HeaderBag, Body) VALUES (@MessageId, @MessageType, @Topic, @Timestamp, @HeaderBag, @Body)", _sqliteTestHelper.TableName_Messages);
            var parameters = new[]
            {
                new SqliteParameter("MessageId", message.Id.ToString()),
                new SqliteParameter("MessageType", message.Header.MessageType.ToString()),
                new SqliteParameter("Topic", message.Header.Topic),
                new SqliteParameter("Timestamp", SqliteType.Text) { Value =message.Header.TimeStamp.ToString("s")},
                new SqliteParameter("HeaderBag",SqliteType.Text) { Value = JsonConvert.SerializeObject(message.Header.Bag)},
                new SqliteParameter("Body", message.Body.Value),
            };

            using (var connection = new SqliteConnection(_sqliteTestHelper.ConnectionString))
            using (var command = connection.CreateCommand())
            {
                connection.Open();

                command.CommandText = sql;
                //command.Parameters.AddRange(parameters); used to work... but can't with current Sqlite lib. Iterator issue
                for (var index = 0; index < parameters.Length; index++)
                {
                    command.Parameters.Add(parameters[index]);
                }
                command.ExecuteNonQuery();
            }
        }

        public void RunSqlite()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE Test(Value);";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Test VALUES(1);";
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText =
                        "SELECT [type], [name], [tbl_name], [rootpage], [sql], [rowid] FROM [main].[sqlite_master] WHERE [type] LIKE 'table'";
                    using (var dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            System.Console.WriteLine(dr[2].ToString());
                        }
                    }
                }

                var databaseReader = new DatabaseSchemaReader.DatabaseReader(connection);
                var schema = databaseReader.ReadAll();
                var tableList = databaseReader.TableList();
                var tables = databaseReader.AllTables();
                var views = databaseReader.AllViews();
                Assert.NotEmpty(tableList);
            }
        }

        internal SqliteTransaction(SqliteConnection connection, IsolationLevel isolationLevel)
        {
            _connection = connection;
            _isolationLevel = isolationLevel;

            if (isolationLevel == IsolationLevel.ReadUncommitted)
            {
                if (connection.ConnectionStringBuilder.CacheMode != CacheMode.Shared)
                {
                    throw new ArgumentException(Strings.FormatInvalidIsolationLevelForUnsharedCache(isolationLevel));
                }
                connection.ExecuteNonQuery("PRAGMA read_uncommitted = 1;");
            }
            else if (isolationLevel == IsolationLevel.Serializable)
            {
                connection.ExecuteNonQuery("PRAGMA read_uncommitted = 0;");
            }
            else if (isolationLevel != IsolationLevel.Unspecified)
            {
                throw new ArgumentException(Strings.FormatInvalidIsolationLevel(isolationLevel));
            }

            // TODO: Register transaction hooks to detect when a user manually completes a transaction created using this API
            connection.ExecuteNonQuery("BEGIN;");
        }

        public void Ctor_sets_connection_string()
        {
            var connectionSring = "Data Source=test.db";

            var connection = new SqliteConnection(connectionSring);

            Assert.Equal(connectionSring, connection.ConnectionString);
        }

        // This method connects the context with the database
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "ef6NotesManagerV3.sqlite" };
            var connectionString = connectionStringBuilder.ToString();
            var connection = new SqliteConnection(connectionString);

            optionsBuilder.UseSqlite(connection);
        }

        public void ServerVersion_returns_value()
        {
            var connection = new SqliteConnection();

            var version = connection.ServerVersion;

            Assert.StartsWith("3.", version);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = DATABASE_NAME };
            var connectionString = connectionStringBuilder.ToString();
            var connection = new SqliteConnection(connectionString);

            optionsBuilder.UseSqlite(connection);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "WeatherDressing.db" };
            var connectionString = connectionStringBuilder.ToString();
            var connection = new SqliteConnection(connectionString);

            //optionsBuilder.UseSqlite(connection);
        }

 private SqliteConnection Open(string connectionString)
 {
     var connection = new SqliteConnection(connectionString);
     connection.Open();
     connection.EnableExtensions();
     connection.LoadExtension(_spatialiteExtensionName);
     return connection;
 }

 public void TestLite()
 {
     using (SqliteConnection conn = new SqliteConnection(_connectionString))
     {
         conn.Open();
         //var d = conn.Query("SELECT * FROM User;");
     }
 }

        private SqliteTestStore CreateTransient(bool sharedCache)
        {
            _connection = new SqliteConnection(CreateConnectionString(_name, sharedCache));

            _connection.Open();

            return this.AsTransient();
        }

        public void ExecuteReader_throws_when_connection_closed()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                var ex = Assert.Throws<InvalidOperationException>(() => connection.CreateCommand().ExecuteReader());

                Assert.Equal(Strings.FormatCallRequiresOpenConnection("ExecuteReader"), ex.Message);
            }
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "MQTTRawData.db" };
            var connectionString = connectionStringBuilder.ToString();
            var connection = new SqliteConnection(connectionString);
            Debug.WriteLine($"Connecting to:{connection.ConnectionString}");

            optionsBuilder.UseSqlite(connection);
        }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      var connStringBuilder = new SqliteConnectionStringBuilder
      { DataSource = Path.Combine(_appEnv.ApplicationBasePath, _data.Configuration["App:DbFile"]) };
      var connString = connStringBuilder.ToString();
      var conn = new SqliteConnection(connString);

      optionsBuilder.UseSqlite(conn);
    }

        public void ConnectionString_gets_and_sets_value()
        {
            var connection = new SqliteConnection();
            var connectionSring = "Data Source=test.db";

            connection.ConnectionString = connectionSring;

            Assert.Equal(connectionSring, connection.ConnectionString);
        }

        private void CreateAndOpenConnection(bool sharedCache = false)
        {
            _connection = new SqliteConnection(CreateConnectionString(_name, sharedCache));

            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "PRAGMA foreign_keys=ON;";
            command.ExecuteNonQuery();
        }