Databases
StackOverflow2013
Postsdbo
POSupersedes clause in database structure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSupersedes clause in database structure
    primarykey
    data
    text
    <p>Imagine a database table that looks like this:</p> <pre><code>create table [dbo].[user] ( id int IDENTITY(1,1), username varchar(50) NOT NULL, firstname varchar(20) NOT NULL, lastname varchar(30) NOT NULL, currentid int NULL, processedby varchar(50) NOT NULL, processeddate varchar(50) NOT NULL processedaction varchar(50) NOT NULL ) </code></pre> <p>What I want to do is to setup NHibernate to load it into my user object, but I only want the current version of the object "user" to be brought back. I know how to do a SQL select to do this on my own, and I feel as if there's something in nHibernate with the usage of triggers and event listeners, but can anyone tell me how to implement the nHibernate repository so I can:</p> <ul> <li>{Repository}.GetCurrent(id) &lt;- pass it any of the ids that are assigned to any of the historical or the current record, and get back the current object.</li> <li>{Repository}.Save(user) &lt;- I want to always insert the changes to a new row, and then update the old versions to link back to the new id.</li> </ul> <hr> <p>Edit</p> <p>So, there's some confusion here, and maybe I explained it wrong... What I'm trying to do is this, in regards to always getting the current record back...</p> <pre><code>Select uc.* FROM User uo JOIN User uc on uo.currentid=uc.id WHERE uo.id==:id </code></pre> <p>But, I don't want to expose "CurrentID" to my object model, since it has no bearing on the rest of the system, IMHO. In the above SQL statement, uo is considered the "original" object set, and uc is considered the current object in the system.</p> <hr> <p>Edit #2:</p> <p>Looking at this as a possible solution. <a href="http://ayende.com/blog/4196/append-only-models-with-nhibernate" rel="nofollow">http://ayende.com/blog/4196/append-only-models-with-nhibernate</a></p> <p>I'm honestly being pigheaded, as I'm thinking about this backward. In this way of running a database, the autoincrementing field should be the version field, and the "id" field should be whatever the autoincrementer's value has at the time of the initial insert.</p> <hr> <p>Answer:</p> <p>I don't want to take @Firo's fury, and I'm not going to remove it from him, as he took me down the right path... what I wound up with was:</p> <ol> <li>Created a base generic class with two types given a. type of the object's "ID" b. type of the object itself.</li> <li>instantiate all classes.</li> <li>create a generic interface IRepository class with a type of the object to store/retrieve.</li> <li>create an abstract generic class with a type of the object to store/retrieve.</li> <li>create a concrete implementation class for each type to store/retrieve.</li> <li><p>inside of the create/update, the procedure looks like:</p> <pre><code>Type Commit(Type item) { var clone = item.DeepClone(); _Session.Evict(item); clone.Id = 0; clone.ProcessedDate = DateTime.Now; if (clone.Action.HasValue) { if (clone.Action == ProcessedAction.Create) clone.Action = ProcessedAction.Update; } else { clone.Action = ProcessedAction.Create; } clone.ProcessedBy = UserRepos.Where(u =&gt; u.Username == System.Threading.Thread.CurrentPrincipal.Identity.Name).First().Current; var savedItem = (_Session.Merge(clone) as Type); _Session.CreateQuery("UPDATE Type SET CurrentID = :newID where ID=:newID OR CurrentID=:oldID") .SetParameter("newID", savedItem.Id) .SetParameter("oldID", item.Id) .ExecuteUpdate(); return savedItem; } </code></pre></li> <li><p>In the delete method, we simply update the {object}.Action = ProcessedAction.Delete</p></li> </ol> <p>I wanted to do this another way, but realizing we need to eventually do historical comparisons, we weren't able to ask nHibernate to filter the deleted objects, as the users will want to see that. We'll create a business facade to take care of the deleted records.</p> <p>Again, much thanks to @Firo for his help with this.</p> <p>So, with all that, I can finally do this:</p> <pre><code>var result = {Repository}.Where(obj =&gt; obj.Id == {objectID from caller}).FirstOrDefault(); if (result != null) { return result.Current; } else { return null; } </code></pre> <p>and always get my current object back for any requesting ID. Hope it helps someone that is in my situation.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRichard B
    1. USRichard B
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou want the logic you have described to be implemented in NH mapping or in your repository class?
      singulars
      1. POSupersedes clause in database structure
      1. UShival
    2. COWould like it so that I issue a {repository}.save(object); in my controller, and that somewhere lower in the stack, the feature can be implemented.
      singulars
      1. POSupersedes clause in database structure
      1. USRichard B
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload