Handling indexes on tables in OpenAccess?

12 posts, 0 answers
  1. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 10 Jul 2011 Link to this post

    I finally got my model converted over from Entity Framework to OpenAccess, and I noticed that when I updated the model from the database, it pulled in all the information about the table indexes into the RLINQ file. But when I examine the model from the GUI design tools, I don't see any references to the indexes nor how to manage them? Is there some way to add/delete indexes and manage indexes on tables using the OpenAccess designer, or does it just pull them in from the database for reference purposes?
  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 12 Jul 2011 Link to this post

    Hi Kendall Bennett,

    Your observations are correct. The indexes are imported in the rlinq model but currently there is no way to manipulate them with the designer. They are kept only for reference purposes during runtime.

    Regards,
    Alexander
    the Telerik team

    Register for the Q2 2011 What's New Webinar Week. Mark your calendar for the week starting July 18th and book your seat for a walk through of all the exciting stuff we will ship with the new release!

  3. DevCraft banner
  4. Kendall Bennett
    Kendall Bennett avatar
    80 posts
    Member since:
    Feb 2010

    Posted 12 Jul 2011 Link to this post

    Ok thanks. It would be really nice to be able to maintain them in the designer, or at least see what is indexed :)
  5. Igor
    Igor avatar
    145 posts
    Member since:
    Jan 2011

    Posted 08 May 2013 Link to this post

    Hello!

    i faced with the problem, that might be related to the topic starter question.
    I am using TelerikORM (added model with visual designer) with MySQL backend, and adding the index in MySQL like that:

    alter table table_1 add index (date);


    afterwards, when i am performing Telerik ORM action "Updated database from model", it generates instruction to drop index:

    -- Dropping index 'date' which is not configured in OpenAccess but exists on the database.
    ALTER TABLE `table_1` DROP INDEX `date`;

    i dont expect this index to be dropped.
    i found this topic in the manual: http://www.telerik.com/help/openaccess-orm/fluent-mapping-mapping-clr-advanced-defining-indexes.html
    but i am interesting, if there is any option to create indexe via Telerik ORM Designer?

  6. Andrey
    Andrey avatar
    5 posts
    Member since:
    Jul 2012

    Posted 08 May 2013 Link to this post

    Hi, Igor!

    You can add created index in MySql into the model: In the designer, select "Update from Database", and then select the target index.
    After that, when you call the "Updated database from model", removal index instructions will not.
  7. Igor
    Igor avatar
    145 posts
    Member since:
    Jan 2011

    Posted 08 May 2013 Link to this post

    hi Andrey, thank you for your answer.
    i might not want to create index manually in the database, do you know if OpenAccessORM could handle that for me?
    Should i use the instruction like this:

    productConfiguration.HasIndex(x => x.Price)
       .IsUnique()
       .IsClustered()
       .WithName("IX_Products_Price");

    in generated by OpenAccess ORM code or there is another option to accomplish the same?
  8. Yordan
    Admin
    Yordan avatar
    39 posts

    Posted 10 May 2013 Link to this post

    Hello Igor,

    There are three ways of adding indexes in the database using OpenAccess ORM.

    1) If Fluent Mapping is used the way indexes can be added is to write additional code in the PrepareMapping() method. In that method for every configuration indexes can be added with code similar to the one that is pasted in the previous post.

    2) If RLINQ file is used indexes can be added by overriding the OnDatabaseOpen() method in the model class - that is the class that inherits form OpenAccessContext class. For example if an index on table Products have to be added based on the column UnitPrice the code could be similar to the following:
    protected override void OnDatabaseOpen(BackendConfiguration backendConfiguration, MetadataContainer metadataContainer)
    {
        MetaTable metaTableProduct = metadataContainer.Tables.First(t => t.Name.Equals("Products"));
        MetaIndex metaIndex = new MetaIndex("MyIndex", metaTableProduct);
        MetaColumn metaColumnUnitPrice = metaTableProduct.Columns.First(c => c.Name.Equals("UnitPrice"));
        metaIndex.Columns.Add(new MetaIndexColumnMapping("MyColumn", metaColumnUnitPrice, 1, SortOrder.Ascending));
        metaIndex.Clustered = true;
        metaTableProduct.Indexes.Add(metaIndex);
        metadataContainer.Indexes.Add(metaIndex);
     
        base.OnDatabaseOpen(backendConfiguration, metadataContainer);
    }

    In the same class the method UpdateSchema have to be defined too.
    public void UpdateSchema()
    {
        var handler = this.GetSchemaHandler();
        string script = null;
        try
        {
            script = handler.CreateUpdateDDLScript(null);
        }
        catch
        {
            bool throwException = false;
            try
            {
                handler.CreateDatabase();
                script = handler.CreateDDLScript();
            }
            catch
            {
                throwException = true;
            }
            if (throwException)
            {
                throw;
            }
        }
        if (string.IsNullOrEmpty(script) == false)
        {
     
            handler.ForceExecuteDDLScript(script);
     
        }
    }

    After that the code to force the creation of the index in the database is:
    using (EntitiesModel dbContext = new EntitiesModel())
    {
        dbContext.UpdateSchema();    
    }

    3) There is a third options how indexes can be added - by editing the XML file of the model - the RLINQ file. This option however is prone to errors since no intellisense can be used and the structure of the XML file can be corrupted which will lead to errors. 

    If there are any more questions implementing one of the fore-mentioned scenarios please do not hesitate to write back to us.

    Kind regards,
    Yordan
    the Telerik team
    OpenAccess Samples Kit boasts 50+ sample applications providing diverse real-life business solutions. Click to read more and see OpenAccess ORM in action.
  9. Igor
    Igor avatar
    145 posts
    Member since:
    Jan 2011

    Posted 11 May 2013 Link to this post

    Thank you, Yordan! This is what i was looking for
  10. Tuan Hoang
    Tuan Hoang avatar
    5 posts
    Member since:
    Jun 2010

    Posted 20 Mar 2014 Link to this post

    I am using RIA OpenAccess, where I can call UpdateSchema();
    Sorry for newbie question.

    Tuan Hoang Anh
  11. Tuan Hoang
    Tuan Hoang avatar
    5 posts
    Member since:
    Jun 2010

    Posted 22 Mar 2014 Link to this post

    In my OnDatabaseOpen, i add new index and script = handler.CreateUpdateDDLScript(null) alway return null. Why it alway return null ?
  12. Kristian Nikolov
    Admin
    Kristian Nikolov avatar
    206 posts

    Posted 24 Mar 2014 Link to this post

    Hello Tuan,

    Please find the answers to your questions as follows.

    Calling UpdateSchema() in a RIA Service scenario:
    After you have extended the context of your model (the class which inherits from OpenAccessContext) with the UpdateSchema method, you have to expose it through your domain service. To do that, extend your domain service class and add a method which calls UpdateSchema for the DataContext object of the class:
    public void SchemaUpdate()
    {
        this.DataContext.UpdateSchema();
    }
    Then, after rebuilding your solution you should be able to access the SchemaUpdate method through your client. Do note however, that UpdateSchema updates the database to be in sync with the current state of your model and enabling such functionality through the client is generally not recommended.

    CreateUpdateDDLScript(null) always returns null:
    When using a Domain Model (.rlinq file) this behavior is normal. The reason for it is that by default changes made to the entities do not result in schema updates. To alter this behavior, open the Visual Designer and in the properties window of the desired domain class set the Update Schema property to true. Afterwards the CreateUpdateDDLScript method should generate the appropriate script for adding an index to the database.

    I hope this helps. Should you have additional questions, feel free to contact us again.

    Regards,
    Kristian Nikolov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  13. Tuan Hoang
    Tuan Hoang avatar
    5 posts
    Member since:
    Jun 2010

    Posted 27 Apr 2014 in reply to Kristian Nikolov Link to this post

    Thanks Kristian Nikolov. It work now.
Back to Top
DevCraft banner