Archive database support in OpenAccess ORM?

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

    Posted 21 Apr 2011 Link to this post

    Hi Guys,

    Another issue I am dealing with is determining the best way to support the archive database system we have developed. In order to move older data out of the 'working set' for our database, we maintain archive databases which allow us to move data from the main database, into the archive databases when the data is past a certain age. However there are situations where we still want to be able to dig into the archives for various reasons (reporting is one), so we have special SQL queries we have developed that allow us to work with the data in the archive tables.

    The problem is not everything can be moved into the archive tables of obvious reasons. If we are say moving a customer order from the main table into the archive table, we will copy everything related to that order into the archive table and delete it from the main table, however the data in the archive table will now have to reference data in the main table. For instance the customer_id stored in the archived order must reference the customer in the main table, since the customer is still and active account. Likewise references to the products purchased also point to the main database tables.

    This all works great in regular SQL, since we just do joins to the tables in different databases, since you just need to prefix the database name in front of the tables. Ie:

    select * from archivedb.orders o join maindb.customers c on (o.customers_id = c.customers_id)

    But I don't see how that could be done with an ORM like OpenAccess, or can it? Is it possible to reference tables from a different database for the schema? So that I could include the customers table from the main database and the orders table from the archive database in the same schema, so when I issued something similar to the above in LINQ or OQL, it would generate the correct SQL?

    If not, does anyone have any suggestions to make this easier? I suppose I could move the archive tables from the archive databases (we keep one per year) to tables in the main database, with a year prefix perhaps. I think then I could model it with an ORM like so:

    select * from 2010_orders o join customers c on (o.customers_id = c.customers_id)

    But I would really like to be able to keep the archives in totally separate databases, as it makes it easier to manage (and easy to take an old archive off-line).
  2. Serge
    Admin
    Serge avatar
    375 posts

    Posted 26 Apr 2011 Link to this post

    Hi Kendall Bennett,

     As long you have a single database (with multiple schemas) you will be able to write similar queries with OpenAccess. However you cannot achieve a similar scenario with multiple databases. When having the archive tables in a different schema (and preferably with a prefix) you will face no problems. However you should know that you will only be able to write queries over classes (objects) from one database instance as the context is tightly bound with a connection string. 

    I hope this proves to be helpful. Do not hesitate to let us know if you face further trouble.

    All the best,
    Serge
    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
Back to Top