abp database is not suitable for oracle #2112

0

maliming created 3 years ago

Support Team Fullstack Developer

hi

You can see https://github.com/abpframework/abp/issues/1920#issuecomment-543551659

0

Sinerji created 3 years ago

http://prntscr.com/1z8wmu4

My problem is oracle 12.1 can not create table's because

CONSTRAINT "FK_AbpOrganizationUnits_AbpOrganizationUnits_ParentId" ORA-00972: specifier too long

my problem oracle db standarts. Have abp db for oracle with table names Column character lenght and Foreign Keys need to be max 30 chracter lenght. how can ı import this tables ?

0

maliming created 3 years ago

Support Team Fullstack Developer

hi

Haven't you tried this?

builder.ConfigureIdentityServer(options =>
{
    options.DatabaseProvider = EfCoreDatabaseProvider.Oracle;
});

0

Sinerji created 3 years ago

hi

Haven't you tried this?

builder.ConfigureIdentityServer(options => 
{ 
    options.DatabaseProvider = EfCoreDatabaseProvider.Oracle; 
});

Example :

Migrations file in and oracle 12.1c version can not create this fk it is example. ı write your code but this code what will be do ? I need to small names for database foreign keys or tables or table column names max size 30.

"FK_IdentityServerClientScopes_IdentityServerClients_ClientId" it is more than 30

        migrationBuilder.CreateTable(
            name: "IdentityServerClientScopes",
            columns: table => new
            {
                ClientId = table.Column&lt;Guid&gt;(type: "RAW(16)", nullable: false),
                Scope = table.Column&lt;string&gt;(type: "NVARCHAR2(200)", maxLength: 200, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_IdentityServerClientScopes", x => new { x.ClientId, x.Scope });
                table.ForeignKey(
                    name: "FK_IdentityServerClientScopes_IdentityServerClients_ClientId",
                    column: x => x.ClientId,
                    principalTable: "IdentityServerClients",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

0

maliming created 3 years ago

Support Team Fullstack Developer

hi

You can reconfigure the index or FK name length manually.

https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/EFCoreIdentifier.html#GUID-FA43F1A1-EDA2-462F-8844-45D49EF67607

builder.ConfigureIdentityServer();

builder.Entity<Client>(b =>
{
    b.HasMany(x => x.AllowedScopes).WithOne().HasForeignKey(x => x.ClientId).HasConstraintName("CS_F_Name").IsRequired();
});

builder.Entity<ClientScope>(b =>
{
    b.HasKey(x => new {x.ClientId, x.Scope}).HasName("CS_Key_Name");
});

0

alper created 3 years ago

Support Team Director

in the first post, you mentioned that Column name is too long and it throws exception. That's the Oracle database rule. I think you should create the entity with name less than 30 chars.

0

Sinerji created 3 years ago

in the first post, you mentioned that Column name is too long and it throws exception. That's the Oracle database rule. I think you should create the entity with name less than 30 chars.

Yes ı know but how ı can do it ? Abp tables not mine. Am ı need to change all database tables Index's , Foreign Keys and table column's ?

0

alper created 3 years ago

Support Team Director

can you check out this post https://github.com/abpframework/abp/issues/1920

0

alper created 3 years ago

Support Team Director

I tried to reproduce the issue but I couldn't. I created an Oracle project with the following command

abp new Mvc.TieredOracle11164511 -t app-pro -u mvc --mobile none --database-provider Oracle -csf

First built the solution then run the DbMigrator and no problem!

Maybe you have migrated your project from another database provider to Oracle?

0

Sinerji created 3 years ago

can you check out this post https://github.com/abpframework/abp/issues/1920

My problem is https://github.com/abpframework/abp/issues/7787 this is same for my problem.Not your answer.

I tried to reproduce the issue but I couldn't. I created an Oracle project with the following command

abp new Mvc.TieredOracle11164511 -t app-pro -u mvc --mobile none --database-provider Oracle -csf First built the solution then run the DbMigrator and no problem!

And what is your oracle version ? We try oracle 12.1c version.

Actually, this issue isn't specifically related to Migration process, but it's more about aligning ABP database's object names with Oracle Object Naming Rules.

0

alper created 3 years ago

Support Team Director

is your project ABP v4.4.4? because I can't reproduce it. this problem was happening in the old versions of ABP and we fixed all the names.

0

alper created 3 years ago

Support Team Director

this is the fix https://github.com/abpframework/abp/commit/647b863d6f255ad4b85b20943c582f3384935bde#diff-7fbbc4ab78803763e735bb031bf1df24d543e70d3c22db5064557ad4ff3bf556

HasMaxLength() method limits the column length. For Oracle we have changed the limits in the previous versions of ABP. And you need to delete Migrations folder and create the initial migration again to create it based on your Oracle version.

Be aware that if you create the migrations for MsSQL and converted it to Oracle later you need to delete the migrations folder. To make a proper test create a new project as I stated here https://support.abp.io/QA/Questions/2112#answer-2a473e7d-3691-1265-a0ad-3a003ac2acec and tell me the result.

0

alper created 3 years ago

Support Team Director

if you still have problems, don't hesitate to ask it

0

alper created 3 years ago

Support Team Director

I realized that there's an error in my previous post You need to give the parameter --database-management-system instead of --database-provider! The correct command is:

abp new MvcOracleProject -t app-pro -u mvc --mobile none --database-management-system Oracle -csf

https://docs.abp.io/en/abp/latest/CLI-New-Command-Samples#choose-database-management-system

0

berkansasmaz created 3 years ago

Support Team .NET Developer

Hi,

I tested this question and the test was successful. Here are my steps;

abp new MvcOracleProject -t app-pro -u mvc --mobile none --database-management-system Oracle -csf

I created a new ABP project with the above command.
Then I adjusted my connection strings according to myself
I was able to successfully create default ABP tables with MvcOracleProject.DbMigrator.
I have successfully run the MvcOracleProject.Web project.

That's why I'm closing this question but feel free to reopen it if you run into problems with it.

0

Sinerji created 3 years ago

Hi,

I tested this question and the test was successful. Here are my steps;

abp new MvcOracleProject -t app-pro -u mvc --mobile none --database-management-system Oracle -csf

I created a new ABP project with the above command.

Then I adjusted my connection strings according to myself

I was able to successfully create default ABP tables with MvcOracleProject.DbMigrator.

I have successfully run the MvcOracleProject.Web project.

That's why I'm closing this question but feel free to reopen it if you run into problems with it.

My problem steel continues.Can you share your dbmigration files ? This Create Command is good for oracle but ı can't import abp tables. I say again I can't create tables because Oracle can not create long table names, column names or fk names maxlenght 30. Abp need to configure for this.Or

this is the fix https://github.com/abpframework/abp/commit/647b863d6f255ad4b85b20943c582f3384935bde#diff-7fbbc4ab78803763e735bb031bf1df24d543e70d3c22db5064557ad4ff3bf556

How i can import this fix ? I think this fix can solve ?

Screen :http://prntscr.com/20oemn0

0

berkansasmaz created 3 years ago

Support Team .NET Developer

Below you can see the definitions of AbpOrganizationUnits:

migrationBuilder.CreateTable(
                name: "AbpOrganizationUnits",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "RAW(16)", nullable: false),
                    TenantId = table.Column<Guid>(type: "RAW(16)", nullable: true),
                    ParentId = table.Column<Guid>(type: "RAW(16)", nullable: true),
                    Code = table.Column<string>(type: "NVARCHAR2(95)", maxLength: 95, nullable: false),
                    DisplayName = table.Column<string>(type: "NVARCHAR2(128)", maxLength: 128, nullable: false),
                    ExtraProperties = table.Column<string>(type: "NVARCHAR2(2000)", nullable: true),
                    ConcurrencyStamp = table.Column<string>(type: "NVARCHAR2(40)", maxLength: 40, nullable: true),
                    CreationTime = table.Column<DateTime>(type: "TIMESTAMP(7)", nullable: false),
                    CreatorId = table.Column<Guid>(type: "RAW(16)", nullable: true),
                    LastModificationTime = table.Column<DateTime>(type: "TIMESTAMP(7)", nullable: true),
                    LastModifierId = table.Column<Guid>(type: "RAW(16)", nullable: true),
                    IsDeleted = table.Column<bool>(type: "NUMBER(1)", nullable: false, defaultValue: false),
                    DeleterId = table.Column<Guid>(type: "RAW(16)", nullable: true),
                    DeletionTime = table.Column<DateTime>(type: "TIMESTAMP(7)", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_AbpOrganizationUnits", x => x.Id);
                    table.ForeignKey(
                        name: "FK_AbpOrganizationUnits_AbpOrganizationUnits_ParentId",
                        column: x => x.ParentId,
                        principalTable: "AbpOrganizationUnits",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

It's the same as yours, but after your last words, I did a little research and found that this is related to the Oracle version.

Since the Oracle version I am using is 12.2.0.1, I did not have any problems, you are probably using a lower version.

References

https://stackoverflow.com/a/41402458/9922629
https://stackoverflow.com/a/3085571/9922629

0

Sinerji created 3 years ago

Below you can see the definitions of AbpOrganizationUnits:

migrationBuilder.CreateTable( 
                name: "AbpOrganizationUnits", 
                columns: table => new 
                { 
                    Id = table.Column<Guid>(type: "RAW(16)", nullable: false), 
                    TenantId = table.Column<Guid>(type: "RAW(16)", nullable: true), 
                    ParentId = table.Column<Guid>(type: "RAW(16)", nullable: true), 
                    Code = table.Column<string>(type: "NVARCHAR2(95)", maxLength: 95, nullable: false), 
                    DisplayName = table.Column<string>(type: "NVARCHAR2(128)", maxLength: 128, nullable: false), 
                    ExtraProperties = table.Column<string>(type: "NVARCHAR2(2000)", nullable: true), 
                    ConcurrencyStamp = table.Column<string>(type: "NVARCHAR2(40)", maxLength: 40, nullable: true), 
                    CreationTime = table.Column<DateTime>(type: "TIMESTAMP(7)", nullable: false), 
                    CreatorId = table.Column<Guid>(type: "RAW(16)", nullable: true), 
                    LastModificationTime = table.Column<DateTime>(type: "TIMESTAMP(7)", nullable: true), 
                    LastModifierId = table.Column<Guid>(type: "RAW(16)", nullable: true), 
                    IsDeleted = table.Column<bool>(type: "NUMBER(1)", nullable: false, defaultValue: false), 
                    DeleterId = table.Column<Guid>(type: "RAW(16)", nullable: true), 
                    DeletionTime = table.Column<DateTime>(type: "TIMESTAMP(7)", nullable: true) 
                }, 
                constraints: table => 
                { 
                    table.PrimaryKey("PK_AbpOrganizationUnits", x => x.Id); 
                    table.ForeignKey( 
                        name: "FK_AbpOrganizationUnits_AbpOrganizationUnits_ParentId", 
                        column: x => x.ParentId, 
                        principalTable: "AbpOrganizationUnits", 
                        principalColumn: "Id", 
                        onDelete: ReferentialAction.Restrict); 
                });

It's the same as yours, but after your last words, I did a little research and found that this is related to the Oracle version.

Since the Oracle version I am using is 12.2.0.1, I did not have any problems, you are probably using a lower version.

References

https://stackoverflow.com/a/41402458/9922629
https://stackoverflow.com/a/3085571/9922629

We have been telling our oracle version since the topic was opened, and we also say that there is a character limit in the database due to the oracle version. Doesn't abp have support for this oracle version? We know that if we upgrade the Oracle version, the problem will be fixed. But we need to perform this operation without upgrading the oracle version.

0

alper created 3 years ago

Support Team Director

hi @sinerji,

sorry but as this is a bug of Oracle, we don't provide support on that.

1

berkansasmaz created 3 years ago

Support Team .NET Developer

Sorry for the misunderstanding, my mistake, I should have looked into your comments further.

We do not currently intend to support older versions of Oracle. However, this does not mean that your question does not have a solution. I leave a sample code below.

MvcOracleProjectDbContext.cs

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            /* Include modules to your migration db context */

            builder.ConfigurePermissionManagement();
            builder.ConfigureSettingManagement();
            builder.ConfigureBackgroundJobs();
            builder.ConfigureAuditLogging();
            builder.ConfigureIdentityPro();
            builder.ConfigureIdentityServer();
            builder.ConfigureFeatureManagement();
            builder.ConfigureLanguageManagement();
            builder.ConfigurePayment();
            builder.ConfigureSaas();
            builder.ConfigureTextTemplateManagement();
            builder.ConfigureBlobStoring();
            
            builder.Entity<ClientRedirectUri>(redirectUri =>
            {
                redirectUri.ToTable(AbpIdentityServerDbProperties.DbTablePrefix + "ClientRedirectUris", AbpIdentityServerDbProperties.DbSchema);

                redirectUri.HasKey(x => new { x.ClientId, x.RedirectUri });

                redirectUri.Property(x => x.RedirectUri).HasMaxLength(20).IsRequired(); // Updated line
            });
        }

As you can see from the code, I set the HasMaxLength of a property in the existing ABP table to 20.

Then I created a new migration, you can see the result below:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<string>(
                name: "RedirectUri",
                table: "IdentityServerClientRedirectUris",
                type: "NVARCHAR2(20)",
                maxLength: 20,
                nullable: false,
                oldClrType: typeof(string),
                oldType: "NVARCHAR2(2000)",
                oldMaxLength: 2000);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<string>(
                name: "RedirectUri",
                table: "IdentityServerClientRedirectUris",
                type: "NVARCHAR2(2000)",
                maxLength: 2000,
                nullable: false,
                oldClrType: typeof(string),
                oldType: "NVARCHAR2(20)",
                oldMaxLength: 20);
        }

You are free to change the name of the table, column name, or anything like this.

0

Sinerji created 3 years ago

Sorry for the misunderstanding, my mistake, I should have looked into your comments further.

We do not currently intend to support older versions of Oracle. However, this does not mean that your question does not have a solution. I leave a sample code below.

MvcOracleProjectDbContext.cs

        protected override void OnModelCreating(ModelBuilder builder) 
        { 
            base.OnModelCreating(builder); 
 
            /* Include modules to your migration db context */ 
 
            builder.ConfigurePermissionManagement(); 
            builder.ConfigureSettingManagement(); 
            builder.ConfigureBackgroundJobs(); 
            builder.ConfigureAuditLogging(); 
            builder.ConfigureIdentityPro(); 
            builder.ConfigureIdentityServer(); 
            builder.ConfigureFeatureManagement(); 
            builder.ConfigureLanguageManagement(); 
            builder.ConfigurePayment(); 
            builder.ConfigureSaas(); 
            builder.ConfigureTextTemplateManagement(); 
            builder.ConfigureBlobStoring(); 
             
            builder.Entity<ClientRedirectUri>(redirectUri => 
            { 
                redirectUri.ToTable(AbpIdentityServerDbProperties.DbTablePrefix + "ClientRedirectUris", AbpIdentityServerDbProperties.DbSchema); 
 
                redirectUri.HasKey(x => new { x.ClientId, x.RedirectUri }); 
 
                redirectUri.Property(x => x.RedirectUri).HasMaxLength(20).IsRequired(); // Updated line 
            }); 
        }

As you can see from the code, I set the HasMaxLength of a property in the existing ABP table to 20.

Then I created a new migration, you can see the result below:

        protected override void Up(MigrationBuilder migrationBuilder) 
        { 
            migrationBuilder.AlterColumn<string>( 
                name: "RedirectUri", 
                table: "IdentityServerClientRedirectUris", 
                type: "NVARCHAR2(20)", 
                maxLength: 20, 
                nullable: false, 
                oldClrType: typeof(string), 
                oldType: "NVARCHAR2(2000)", 
                oldMaxLength: 2000); 
        } 
 
        protected override void Down(MigrationBuilder migrationBuilder) 
        { 
            migrationBuilder.AlterColumn<string>( 
                name: "RedirectUri", 
                table: "IdentityServerClientRedirectUris", 
                type: "NVARCHAR2(2000)", 
                maxLength: 2000, 
                nullable: false, 
                oldClrType: typeof(string), 
                oldType: "NVARCHAR2(20)", 
                oldMaxLength: 20); 
        }

You are free to change the name of the table, column name, or anything like this.

We will try with oracle 12.2 version thank you for helping.

21 Answer(s)

References

References