Open Closed

abp database is not suitable for oracle #2112

User avatar
0
Sinerji created
  • ABP Framework version: v4.4.4
  • UI type:MVC
  • DB provider: ORACLE
  • Tiered (MVC) or Identity Server Separated (Angular): yes

Tables and secondary keys in abp database are not suitable for oracle database. Oracle does not allow column or secondary key names of more than 30 characters. What should we do for this?

Go to accepted answer
21 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

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

  • User Avatar
    0
    Sinerji created

    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 ?

  • User Avatar
    0
    maliming created
    Support Team Fullstack Developer

    hi

    Haven't you tried this?

    builder.ConfigureIdentityServer(options =>
{
    options.DatabaseProvider = EfCoreDatabaseProvider.Oracle;
});
  • User Avatar
    0
    Sinerji created

    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<Guid>(type: "RAW(16)", nullable: false),
                Scope = table.Column<string>(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);
            });
  • User Avatar
    0
    maliming created
    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");
});
  • User Avatar
    0
    alper created
    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.

  • User Avatar
    0
    Sinerji created

    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 ?

  • User Avatar
    0
    alper created
    Support Team Director

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

  • User Avatar
    0
    alper created
    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?

  • User Avatar
    0
    Sinerji created

    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.

  • User Avatar
    0
    alper created
    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.

  • User Avatar
    0
    alper created
    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.

  • User Avatar
    0
    alper created
    Support Team Director

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

  • User Avatar
    0
    alper created
    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

  • User Avatar
    0
    berkansasmaz created
    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

    1. I created a new ABP project with the above command.
    2. Then I adjusted my connection strings according to myself
    3. I was able to successfully create default ABP tables with MvcOracleProject.DbMigrator.
    4. 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.

  • User Avatar
    0
    Sinerji created

    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

    1. I created a new ABP project with the above command.
    2. Then I adjusted my connection strings according to myself
    3. I was able to successfully create default ABP tables with MvcOracleProject.DbMigrator.
    4. 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

  • User Avatar
    0
    berkansasmaz created
    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

    1. https://stackoverflow.com/a/41402458/9922629
    2. https://stackoverflow.com/a/3085571/9922629
  • User Avatar
    0
    Sinerji created

    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

    1. https://stackoverflow.com/a/41402458/9922629
    2. 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.

  • User Avatar
    0
    alper created
    Support Team Director

    hi @sinerji,

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

  • User Avatar
    1
    berkansasmaz created
    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.

  • User Avatar
    0
    Sinerji created

    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.

    2 1
Assignee
User avatar berkansasmaz
Labels
None yet
Made with ❤️ on ABP v9.1.0-preview. Updated on November 01, 2024, 05:35