CreateUpdateDDLScript Function creates different SQL than dialog

3 posts, 0 answers
  1. Janko
    Janko avatar
    5 posts
    Member since:
    Sep 2010

    Posted 27 Mar 2013 Link to this post

    hi there, 

    i trying to get programmatic creation and updating the database to work, for this i orientate me on this forum thread.

    the "update database from model" dialog generates different sql than the CreateUpdateDDLScript function.

    It seems that it forgets the one-to-one associations?

    I tried to set the ShouldUpdateSchema property on the assiciation if it is a MetaForeignKeyAssociation, but there is not property like this.

    what i am missing?

    the schema is here.

    my update logic code is 

    public static void UpdateOrCreateDb()
        {
            using (eaccomDb dbContext = new eaccomDb())
            {
     
                MetadataContainer metadata = dbContext.Metadata;
                foreach (MetaPersistentType type in metadata.PersistentTypes)
                {
                    // This will create all entity tables in model, but not tables for joins
                    type.ShouldUpdateSchema = true;
                     
                    // Now handle table joins for associations
                    foreach (MetaMember member in type.Members)
                    {
                        MetaNavigationMember navMember = member as MetaNavigationMember;
                        if (navMember != null)
                        {
                            MetaJoinTableAssociation joinAssociation = navMember.Association as MetaJoinTableAssociation;
                            if (joinAssociation != null)
                            {
                                joinAssociation.ShouldUpdateSchema = true; // Only do if there is a join association
                            }
                        }
                    }
                }
     
                BackendConfiguration config = eaccomDb.GetBackendConfiguration();
     
                using (OpenAccessContext update_context = new OpenAccessContext("DbConnection", config, metadata))
                {
                    Telerik.OpenAccess.ISchemaHandler schemaHandler = update_context.GetSchemaHandler();
                    string script = null;
                    if (schemaHandler.DatabaseExists())
                    {
                        script = schemaHandler.CreateUpdateDDLScript(null);
                    }
                    else
                    {
                        schemaHandler.CreateDatabase();
                        script = schemaHandler.CreateDDLScript();
                    }
                    if (!string.IsNullOrEmpty(script))
                    {
                        schemaHandler.ExecuteDDLScript(script);
                    }
                }
            }
        }

    the sql generated by the dialog

    -- eaccom.db.DbLogin
    CREATE TABLE [DbLogin] (
        [CreateDate] datetime NOT NULL,         -- _createDate
        [Id] int NOT NULL,                      -- _id
        [IsActive] tinyint NOT NULL,            -- _isActive
        [LastAccess] datetime NOT NULL,         -- _lastAccess
        [Login] nvarchar(255) NULL,             -- _login
        [PasswortHash] nvarchar(255) NULL,      -- _passwortHash
        [PasswortSalt] nvarchar(255) NULL,      -- _passwortSalt
        CONSTRAINT [pk_DbLogin] PRIMARY KEY ([Id])
    )
     
    go
     
    -- eaccom.db.DbNewsFeedItem
    CREATE TABLE [DbNewsFeedItem] (
        [CreateDate] datetime NOT NULL,         -- _createDate
        [FromDbUserId] int NOT NULL,            -- _dbUser
        [Id] int NOT NULL,                      -- _id
        [txt] nvarchar(255) NULL,               -- _text
        CONSTRAINT [pk_DbNewsFeedItem] PRIMARY KEY ([Id])
    )
     
    go
     
    -- eaccom.db.DbRoles
    CREATE TABLE [DbRoles] (
        [Id] int NOT NULL,                      -- _id
        [RoleName] nvarchar(255) NULL,          -- _roleName
        CONSTRAINT [pk_DbRoles] PRIMARY KEY ([Id])
    )
     
    go
     
    -- System.Collections.Generic.IList`1 eaccom.db.DbRoles._dbUsers
    CREATE TABLE [DbRoles_DbUser] (
        [Id] int NOT NULL,
        [Id2] int NOT NULL,
        CONSTRAINT [pk_DbRoles_DbUser] PRIMARY KEY ([Id], [Id2])
    )
     
    go
     
    -- eaccom.db.DbTag
    CREATE TABLE [DbTag] (
        [Id] int NOT NULL,                      -- _id
        CONSTRAINT [pk_DbTag] PRIMARY KEY ([Id])
    )
     
    go
     
    -- System.Collections.Generic.IList`1 eaccom.db.DbTag._dbNewsFeedItems
    CREATE TABLE [DbTag_DbNewsFeedItem] (
        [Id] int NOT NULL,
        [Id2] int NOT NULL,
        CONSTRAINT [pk_DbTag_DbNewsFeedItem] PRIMARY KEY ([Id], [Id2])
    )
     
    go
     
    -- eaccom.db.DbUser
    CREATE TABLE [DbUser] (
        [Abbreviation] nvarchar(255) NULL,      -- _abbreviation
        [DbLoginId] int NOT NULL,               -- _dbLoginId
        [Firstname] nvarchar(255) NULL,         -- _firstname
        [Id] int NOT NULL,                      -- _id
        [Lastname] nvarchar(255) NULL,          -- _lastname
        CONSTRAINT [pk_DbUser] PRIMARY KEY ([Id])
    )
     
    go
     
    ALTER TABLE [DbNewsFeedItem] ADD CONSTRAINT [ref_DbNewsFeedItem_DbUser] FOREIGN KEY ([FromDbUserId]) REFERENCES [DbUser]([Id])
     
    go
     
    ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbRoles] FOREIGN KEY ([Id]) REFERENCES [DbRoles]([Id])
     
    go
     
    ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbUser] FOREIGN KEY ([Id2]) REFERENCES [DbUser]([Id])
     
    go
     
    ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTag_DbNewsFeedItem_DbTag] FOREIGN KEY ([Id]) REFERENCES [DbTag]([Id])
     
    go
     
    ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTg_DbNwsFdItm_DbNwsFdItm] FOREIGN KEY ([Id2]) REFERENCES [DbNewsFeedItem]([Id])
     
    go
     
    ALTER TABLE [DbUser] ADD CONSTRAINT [ref_DbUser_DbLogin] FOREIGN KEY ([Id]) REFERENCES [DbLogin]([Id])
     
    go
     
    -- Index 'idx_DbNwsFeedItem_FromDbUserId' was not detected in the database. It will be created
    CREATE INDEX [idx_DbNwsFeedItem_FromDbUserId] ON [DbNewsFeedItem]([FromDbUserId])
     
    go
     
    -- Index 'idx_DbRoles_DbUser_Id2' was not detected in the database. It will be created
    CREATE INDEX [idx_DbRoles_DbUser_Id2] ON [DbRoles_DbUser]([Id2])
     
    go
     
    -- Index 'idx_DbTag_DbNewsFeedItem_Id2' was not detected in the database. It will be created
    CREATE INDEX [idx_DbTag_DbNewsFeedItem_Id2] ON [DbTag_DbNewsFeedItem]([Id2])
     
    go

    the CreateUpdateDDLScript sql is:

    -- eaccom.db.DbLogin
    CREATE TABLE [DbLogin] (
        [CreateDate] datetime NOT NULL,         -- _createDate
        [Id] int NOT NULL,                      -- _id
        [IsActive] tinyint NOT NULL,            -- _isActive
        [LastAccess] datetime NOT NULL,         -- _lastAccess
        [Login] nvarchar(255) NULL,             -- _login
        [PasswortHash] nvarchar(255) NULL,      -- _passwortHash
        [PasswortSalt] nvarchar(255) NULL,      -- _passwortSalt
        CONSTRAINT [pk_DbLogin] PRIMARY KEY ([Id])
    )
     
    go
     
    -- eaccom.db.DbNewsFeedItem
    CREATE TABLE [DbNewsFeedItem] (
        [CreateDate] datetime NOT NULL,         -- _createDate
        [FromDbUserId] int NOT NULL,            -- _dbUser
        [Id] int NOT NULL,                      -- _id
        [txt] nvarchar(255) NULL,               -- _text
        CONSTRAINT [pk_DbNewsFeedItem] PRIMARY KEY ([Id])
    )
     
    go
     
    -- eaccom.db.DbRoles
    CREATE TABLE [DbRoles] (
        [Id] int NOT NULL,                      -- _id
        [RoleName] nvarchar(255) NULL,          -- _roleName
        CONSTRAINT [pk_DbRoles] PRIMARY KEY ([Id])
    )
     
    go
     
    -- System.Collections.Generic.IList`1[[eaccom.db.DbUser, eaccom.db, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] eaccom.db.DbRoles._dbUsers
    CREATE TABLE [DbRoles_DbUser] (
        [Id] int NOT NULL,
        [Id2] int NOT NULL,
        CONSTRAINT [pk_DbRoles_DbUser] PRIMARY KEY ([Id], [Id2])
    )
     
    go
     
    -- eaccom.db.DbTag
    CREATE TABLE [DbTag] (
        [Id] int NOT NULL,                      -- _id
        CONSTRAINT [pk_DbTag] PRIMARY KEY ([Id])
    )
     
    go
     
    -- System.Collections.Generic.IList`1[[eaccom.db.DbNewsFeedItem, eaccom.db, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] eaccom.db.DbTag._dbNewsFeedItems
    CREATE TABLE [DbTag_DbNewsFeedItem] (
        [Id] int NOT NULL,
        [Id2] int NOT NULL,
        CONSTRAINT [pk_DbTag_DbNewsFeedItem] PRIMARY KEY ([Id], [Id2])
    )
     
    go
     
    -- eaccom.db.DbUser
    CREATE TABLE [DbUser] (
        [Abbreviation] nvarchar(255) NULL,      -- _abbreviation
        [DbLoginId] int NOT NULL,               -- _dbLoginId
        [Firstname] nvarchar(255) NULL,         -- _firstname
        [Id] int NOT NULL,                      -- _id
        [Lastname] nvarchar(255) NULL,          -- _lastname
        CONSTRAINT [pk_DbUser] PRIMARY KEY ([Id])
    )
     
    go
     
    ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbRoles] FOREIGN KEY ([Id]) REFERENCES [DbRoles]([Id])
     
    go
     
    ALTER TABLE [DbRoles_DbUser] ADD CONSTRAINT [ref_DbRoles_DbUser_DbUser] FOREIGN KEY ([Id2]) REFERENCES [DbUser]([Id])
     
    go
     
    ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTag_DbNewsFeedItem_DbTag] FOREIGN KEY ([Id]) REFERENCES [DbTag]([Id])
     
    go
     
    ALTER TABLE [DbTag_DbNewsFeedItem] ADD CONSTRAINT [ref_DbTg_DbNwsFdItm_DbNwsFdItm] FOREIGN KEY ([Id2]) REFERENCES [DbNewsFeedItem]([Id])
     
    go
     
    -- Index 'idx_DbNwsFeedItem_FromDbUserId' was not detected in the database. It will be created
    CREATE INDEX [idx_DbNwsFeedItem_FromDbUserId] ON [DbNewsFeedItem]([FromDbUserId])
     
    go
     
    -- Index 'idx_DbRoles_DbUser_Id2' was not detected in the database. It will be created
    CREATE INDEX [idx_DbRoles_DbUser_Id2] ON [DbRoles_DbUser]([Id2])
     
    go
     
    -- Index 'idx_DbTag_DbNewsFeedItem_Id2' was not detected in the database. It will be created
    CREATE INDEX [idx_DbTag_DbNewsFeedItem_Id2] ON [DbTag_DbNewsFeedItem]([Id2])
     
    go

    thank for any help,

    Janko
  2. Janko
    Janko avatar
    5 posts
    Member since:
    Sep 2010

    Posted 28 Mar 2013 Link to this post

    i found the solution:

    metadata.DefaultMapping.NullForeignKey = true; 

    was missing.

    public static void UpdateOrCreateDb()
           {
               using (eaccomDb dbContext = new eaccomDb())
               {
                   MetadataContainer metadata = dbContext.Metadata;               
                    
                   /* ---> this was missing */
                   metadata.DefaultMapping.NullForeignKey = true;
                    
                   foreach (MetaPersistentType type in metadata.PersistentTypes)
                   {
                       // This will create all entity tables in model, but not tables for joins
                       type.ShouldUpdateSchema = true;
                        
                       // Now handle table joins for associations
                       foreach (MetaMember member in type.Members)
                       {
                           MetaNavigationMember navMember = member as MetaNavigationMember;
                           if (navMember != null)
                           {
                               MetaJoinTableAssociation joinAssociation = navMember.Association as MetaJoinTableAssociation;
                               if (joinAssociation != null)
                               {
                                   joinAssociation.ShouldUpdateSchema = true; // Only do if there is a join association
                               }
                           }
                       }
                   }
     
                   BackendConfiguration config = eaccomDb.GetBackendConfiguration();
     
                   using (OpenAccessContext update_context = new OpenAccessContext("DbConnection", config, metadata))
                   {
                       Telerik.OpenAccess.ISchemaHandler schemaHandler = update_context.GetSchemaHandler();
                       string script = null;
                       if (schemaHandler.DatabaseExists())
                       {
                           script = schemaHandler.CreateUpdateDDLScript(null);
                       }
                       else
                       {
                           schemaHandler.CreateDatabase();
                           script = schemaHandler.CreateDDLScript();
                       }
                       if (!string.IsNullOrEmpty(script))
                       {
                           schemaHandler.ExecuteDDLScript(script);
                       }
                   }
               }
           }
  3. DevCraft banner
  4. Zsolt
    Zsolt avatar
    1 posts
    Member since:
    Nov 2013

    Posted 17 Jun 2014 Link to this post

    Thank you so much! I have been looking for this solution for months.
Back to Top