Programmatic Creation and Update of Model Schema in Database

12 posts, 1 answers
  1. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 20 Sep 2010 Link to this post

    I was able to get software based creation of SQL database, creation of schema, or update of schema to work just fine with versions of Telerik ORM prior to August 2010 using the Database class (which is how most of the examples in the forums and knowledge base do it).  However, for ORM 2010.2.804.5, it does not work.  The variable ddl_script in the code below is either null or an empty string no matter what I do.  If I use the design time tool manually, I can generate the SQL script just fine and manually apply it to the database.  One thing the code does do well is create an empty database, so that part works.

    private static bool _First_Initialize = true; // Only need to do this once, and if parallel, avoids exception on multiple instances
    /// <summary>
    /// Intialialize ORM Database schema
    /// </summary>
    /// <returns>True if database exists</returns>
    public static bool Initialize()
    {
        bool db_exists = false;
        if (_First_Initialize)
        {
            _First_Initialize = false;
            using (MyContext context = new MyContext())
            {
                ISchemaHandler schema = context.GetSchemaHandler();
      
                string ddl_script;
                if (schema.DatabaseExists())
                {
                    ddl_script = schema.CreateUpdateDDLScript(null);
                }
                else
                {
                    schema.CreateDatabase();
                    ddl_script = schema.CreateDDLScript();
                }
                if (!string.IsNullOrWhiteSpace(ddl_script))
                {
                    schema.ExecuteDDLScript(ddl_script);
                }
                db_exists = schema.DatabaseExists();
            }
        }
        else db_exists = true;
        return db_exists;
    }

    I've contributed the supporting project and model code in a complete project to the code community that illustrates many other things that I did get working, but it has not shown up yet.  When it does, I'll create a link to it here.  In the meantime, I was wondering if anyone has solved this problem?
  2. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 23 Sep 2010 Link to this post

    Hi Brian Womack,

     As we already discussed in this thread: http://www.telerik.com/community/code-library/orm/general/350247-orm-n-tier-domain-model-forward-development.aspx this issue is verified and we will introduce some improvements in the schema migration API for Q3. At the moment if you work with the Visual Designer you will have to update the schema manually using the "Update Schema Wizard".

    Kind regards,
    Zoran
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 23 Sep 2010 Link to this post

    By the way, the designer update schema script generation also does not work -- it returns an empty script.  So, I have to delete my database and recreate it.
  5. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 24 Sep 2010 Link to this post

    Hi Brian Womack,

    This is actually not the case on my side I must say. Which option are you selecting from the "Schema Update Strategy" combo-box which is on the first page of the wizard? If you select "Generate and Execute Migration Update Script" I expect that the wizard will generate the script for you. Please also check the "Override schema behavior and mark everything to update the database" checkbox.

    Best wishes,
    Zoran
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  6. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 24 Sep 2010 Link to this post

    If I create a schema, add some data to the database, and add a single property to a table; then the update script generates just fine in the designer.

    However, my model schema is quite a bit more complex, and if I'm making bigger changes like adding or removing associations, it tends to fail most of the time.  I don't have the time to tell you exactly what causes it to fail, but it is likely something with the constraints and foreign keys.

    My models tend to have a lot of the same kind of thing, similar to my ORM N-Tier Domain Model Forward Development project.  However, for the 'real thing', I'll have a lot more associations around one table (2-3 on average that are a combination of 1:n and n:n).

    And yes, I've tried all variations of what you said, with and without execute.  The script box ends up empty always on update (i.e. you changed the schema).

    I just wanted you guys to know there are some serious issues in this area of the code for complex models.  Please look at it very closely.

    Thanks!
  7. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 29 Sep 2010 Link to this post

    Hi Brian Womack,

     You are right, there is still room for improvement in our Update Schema Wizard as it is quite new addition to our product and it had its first version in the current Q2 release. However we will be very glad to have some more concrete feedback whenever possible so that we re-create the very exact problems that you have encountered in order to fix them sooner rather than later.
    Once again, your collaboration is very much appreciated by our team, we look forward to continue in the same spirit.

    All the best,
    Zoran
    the Telerik team
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  8. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 02 Feb 2011 Link to this post

    It seems that even as of now, there is no programmatic creation of database schema support in Telerik ORM as of the latest release in 2010.  Even for the EASY case of no database exists and create the schema.

    This is quite annoying and time-consuming in aggregate for my development team.

    It is my highest priority, please fix it now, feature request of you.  PLEASE include the minimum, at least, in your next release.  If you think it works now, please provide a code sample of how to do it and I'll try it.
  9. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 07 Feb 2011 Link to this post

    Hello Brian Womack,

     Actually there is a way for you to create and update the schema during runtime and the API is exatly the one you have tried in your first post of this forum thread(the one where you call ISchemaHandler).

    However there is one missing part here which causes the script property to always be null or string.Empty. There is a property on the MetaPersistentType called ShouldUpdateSchema which states whether the table to which a persistent type is mapped should be altered(or created if it does not exist). There is such property on the MetaJoinTableAssociation as well which does the same for the joint tables. Please have a look at this source code to see how you can update your model:

    using(MyContext context = new MyContext())
    {
      MetadataContainer metadata = context.Metadata;
      this.SetUpdateSchemaForModel(metadata, true);
      this.UpdateSchema(metadata);
    }
      
    public void SetUpdateSchemaForModel(MetadataContainer metadata, bool shouldUpdate)
    {
      foreach(MetaPersistentType persistenType in metadata.PersistentTypes)
      {
         persistentType.ShouldUpdateSchema = shouldUpdate;
      }
    }
      
    public void UpdateSchema(MetadtaContainer container)
    {
      BackendConfiguration config = MyContext.GetBackendConfiguration();//make sure we use the same backend configuration for update schema and during runtime
      string connectionId = "YourConnectionId";
      OpenAccessContext updateContext = new OpenAccessContext(connectionId, config, metadata);
      using(updateConetxt)
      {
         ISchemaHandler handler = updateContext.GetSchemaHandler();
        //Your code from the first post is here..
      }
    }

    I just saved the additional loop where you can set the ShouldUpdateSchema property on the MetaJoinTableAssociation but I believe that this should be enough for you to be able to build you logic for updating the schema during runtime.

    Regards,

    Zoran
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  10. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 08 Feb 2011 Link to this post

    You guys do, in fact, ROCK!  I stand corrected, your code does support this, EXCEPT for the creation of join tables.  Here is my updated code sample:

    private static bool _First_Initialize = true; // Only need to do this once, and if parallel, avoids exception on multiple instances
     
    public static bool Initialize()
    {
        if (_First_Initialize)
        {
        using (MyContext context = new MyContext())
        {
            MetadataContainer metadata = context.Metadata;
            foreach (MetaPersistentType type in metadata.PersistentTypes)
            {
                type.ShouldUpdateSchema = true;
            }
     
            BackendConfiguration config = RawContext.GetBackendConfiguration();
     
            using (OpenAccessContext update_context = new OpenAccessContext("MyContext", config, metadata))
            {
                ISchemaHandler schema = update_context.GetSchemaHandler();
                bool database_exists = false;
                if (_First_Initialize)
                {
                    _First_Initialize = false;
                    string ddl_script;
                    if (schema.DatabaseExists())
                    {
                        database_exists = true;
                        ddl_script = schema.CreateUpdateDDLScript(null);
                    }
                    else
                    {
                        schema.CreateDatabase();
                        ddl_script = schema.CreateDDLScript();
                    }
     
                    if (!string.IsNullOrWhiteSpace(ddl_script))
                    {
                        schema.ExecuteDDLScript(ddl_script);
                    }
     
                    // Update or Add default data to the database to pre-populate required entities before general use
                    //SomeClass.AddDefaultData(context);
                }
                return database_exists;
            }
        }
    }
  11. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 08 Feb 2011 Link to this post

    Upon further review, I found that the above method works to create the database if it does not exist, and add all the entities specified explicitly in the model.  However, it does not create any of the join tables.  Do you know how to fix that?
  12. Answer
    Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 11 Feb 2011 Link to this post

    Hi Brian Womack,

     Yes, as I mentioned in my previous letter there is a ShouldUpdateSchema property on the MetaJoinTableAssociation object. The MetaJoinTableAssociation, as its name probably suggests, is the object that defines a join table relationship between two Persistent Types. Here is code which shows how to set this property on each MetaJoinTableAssociation:

    foreach(MetaPersistentType type in container.PersistentTypes)
    {
       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;
               }
           }
       }
    }


    Greetings,
    Zoran
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  13. Brian
    Brian avatar
    40 posts
    Member since:
    Oct 2008

    Posted 14 Feb 2011 Link to this post

    Thank you so much for the spoon feeding, Zoran.  I tried to figure it out on my own, but did not put enough energy into it, obviously.  Here is the complete answer, which generates everything for the schema after I delete the database.  WOOT!  This is awesome.

    private static bool _First_Initialize = true; // Only need to do this once, and if parallel, avoids exception on multiple instances
      
    public static bool Initialize()
    {
        if (_First_Initialize)
        {
        using (MyContext context = new MyContext())
        {
            MetadataContainer metadata = context.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 = RawContext.GetBackendConfiguration();
      
            using (OpenAccessContext update_context = new OpenAccessContext("MyContext", config, metadata))
            {
                ISchemaHandler schema = update_context.GetSchemaHandler();
                bool database_exists = false;
                if (_First_Initialize)
                {
                    _First_Initialize = false;
                    string ddl_script;
                    if (schema.DatabaseExists())
                    {
                        database_exists = true;
                        ddl_script = schema.CreateUpdateDDLScript(null);
                    }
                    else
                    {
                        schema.CreateDatabase();
                        ddl_script = schema.CreateDDLScript();
                    }
      
                    if (!string.IsNullOrWhiteSpace(ddl_script))
                    {
                        schema.ExecuteDDLScript(ddl_script);
                    }
      
                    // Update or Add default data to the database to pre-populate required entities before general use
                    //SomeClass.AddDefaultData(context);
                }
                return database_exists;
            }
        }
    }
Back to Top
DevCraft banner