Inhalte aufrufen

Profilbild

About SQLite Support

database sqlite

  • Bitte melden Sie sich an, um eine Antwort zu verfassen.
1 Antwort zu diesem Thema

#1 suat_suphi@hotmail.com

suat_suphi@hotmail.com

    Newbie

  • Members
  • 7 Beiträge

Geschrieben: 15 October 2022 - 14:31

I am traying to add SQLite support for database. I did some experiments. But I can't get over a problem.I would like to explain some parts of my work. Please help with SQLite support :)

 

MaintanceController.cs
line 296
                if (_db.Database.ProviderName != "Microsoft.EntityFrameworkCore.Sqlite")
                    model.DatabaseSize = Convert.ToInt64(mbSize * 1024 * 1024);
                else
                    model.DatabaseSize = Convert.ToInt64(mbSize);
 
 
DataProvider.cs
line 306 and 319 
            if (Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite")
                return Database.ExecuteSqlRawAsync($"DELETE FROM {tableName}");
            else
                return Database.ExecuteSqlRawAsync($"TRUNCATE TABLE {tableName}");
 
 
InstallController.cs
line 80
                new SelectListItem { Value = "sqlite", Text = T("UseSqlite"), Selected = isWindows },
 
DbFactory.cs
line 75 
                    case "sqlite":
                        assemblyName = "Smartstore.Data.Sqlite.dll";
                        break;
 
DataSettings.cs
line 198 
                    case "sqlite":
                        assemblyName = "Smartstore.Data.Sqlite.dll";
                        break;
 
DbServiceCollectionExtensions.cs
line 33
                    .AddSQLite()

SmartDbContext.cs

line onmodelcreating

 

modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
if (Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite")
{
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        /*  */
        //foreach (var property in entityType.ClrType.GetProperties().Where(p => p.GetSetMethod(true) != null && (p.PropertyType == typeof(bool) || p.PropertyType == typeof(bool?))))
        //    modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<BoolToZeroOneConverter<int>>();


        foreach (var property in entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(Decimal) || p.PropertyType == typeof(Decimal?)))
            modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<double>();


        //foreach (var property in entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(UInt64) || p.PropertyType == typeof(UInt64?)))
        //    modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<double>();


        foreach (var property in entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)))
            modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<DateTime>();
        foreach (var property in entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(DateTimeOffset?)))
            modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<DateTime?>();


        foreach (var property in entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(TimeSpan)))
            modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<long>();
        foreach (var property in entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(TimeSpan?)))
            modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<long?>();


    }
}
 
 
 

 

Angehängte Bilder


  • GalenKa und RidgeOi gefällt das

#2 suat_suphi@hotmail.com

suat_suphi@hotmail.com

    Newbie

  • Members
  • 7 Beiträge

Geschrieben: 15 October 2022 - 14:35

 
db
public override string EncloseIdentifier(string identifier)
        {
            Guard.NotEmpty(identifier, nameof(identifier));
            return identifier.EnsureStartsWith('`').EnsureEndsWith('`');
        }


        public override string ApplyPaging(string sql, int skip, int take)
        {
            Guard.NotNegative(skip, nameof(skip));
            Guard.NotNegative(take, nameof(take));


            return $@"{sql} LIMIT {take} OFFSET {skip}";
        }


        public override string[] GetTableNames()
        {
            return Database.ExecuteQueryRaw<string>(
                $"SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%';").ToArray();
        }


        public override async Task<string[]> GetTableNamesAsync()
        {
            return await Database.ExecuteQueryRawAsync<string>(
                $"SELECT name FROM sqlite_schema WHERE type ='table' AND name NOT LIKE 'sqlite_%';").AsyncToArray();
        }


        protected override int? GetTableIncrementCore(string tableName)
        {
            return Database.ExecuteScalarInterpolated<ulong>(
                 $"SELECT seq FROM sqlite_sequence WHERE NAME = {tableName}").Convert<int?>();
        }


        protected override async Task<int?> GetTableIncrementCoreAsync(string tableName)
        {
            return (await Database.ExecuteScalarInterpolatedAsync<ulong>(
                $"SELECT seq FROM sqlite_sequence WHERE NAME = {tableName}")).Convert<int?>();
        }


        protected override void SetTableIncrementCore(string tableName, int ident)
        {
            Database.ExecuteSqlRaw(
                $"UPDATE sqlite_sequence SET seq=0 WHERE NAME='{tableName}';");
        }


        protected override Task SetTableIncrementCoreAsync(string tableName, int ident)
        {
            return Database.ExecuteSqlRawAsync(
                $"UPDATE sqlite_sequence SET seq=0 WHERE NAME='{tableName}';");
        }


        public override int ShrinkDatabase()
        {
            return Database.ExecuteQueryRaw<int>($"VACUUM;PRAGMA wal_checkpoint=TRUNCATE;PRAGMA optimize;PRAGMA wal_autocheckpoint;").FirstOrDefault();
        }


        public override async Task<int> ShrinkDatabaseAsync(CancellationToken cancelToken = default)
        {
            //https://www.sqlite.org/pragma.html#pragma_journal_mode
            //https://www.sqlite.org/lang_analyze.html
            return await Database.ExecuteQueryRawAsync<int>($"VACUUM;PRAGMA wal_checkpoint=TRUNCATE;PRAGMA optimize;PRAGMA wal_autocheckpoint;").FirstOrDefaultAsync();
        }


        /// <summary>
        /// GetDatabaseSize
        /// MaintenanceController.cs line 296 change 
        ///     if (_db.Database.ProviderName != "Microsoft.EntityFrameworkCore.Sqlite")
        ///         model.DatabaseSize = Convert.ToInt64(mbSize* 1024 * 1024);
        ///     else
        ///         model.DatabaseSize = Convert.ToInt64(mbSize);
        /// </summary>
        public override decimal GetDatabaseSize()
        {
            return Database.ExecuteQueryRaw<decimal>(
                $"SELECT (page_count * page_size) as size FROM pragma_page_count(), pragma_page_size();").FirstOrDefault();
        }


        public override async Task<decimal> GetDatabaseSizeAsync()
        {
            //https://stackoverflow.com/questions/6364577/how-to-get-the-current-sqlite-database-size-or-package-size-in-android
            return await Database.ExecuteQueryRawAsync<decimal>(
                $"SELECT (page_count * page_size) as size FROM pragma_page_count(), pragma_page_size();").FirstOrDefaultAsync();
        }


        public override async Task<int> InsertIntoAsync(string sql, params object[] parameters)
        {
            //https://stackoverflow.com/questions/8892973/how-to-get-last-insert-id-in-sqlite
            Guard.NotEmpty(sql, nameof(sql));
            return (await Database.ExecuteQueryRawAsync<decimal>(
                sql + "; SELECT last_insert_rowid();", parameters).FirstOrDefaultAsync()).Convert<int>();
        }

  • GalenKa und RidgeOi gefällt das


Auch markiert mit einem oder mehrerer dieser Schlüsselwörter: database, sqlite