This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Can you use lightspeed with Oracle and multiple sequences? For instance, for each entity have a different sequence. I have gotten it to work with a single sequence, but I would like to know whether it is possible to use to use a different sequence for each entity. |
|
|
This isn't currently possible, but several people have requested this feature so we would certainly be willing to look at it if it is a high priority for you. |
|
|
I think this feature is important when you are dealing with Oracle, because Oracle doesn't have auto-increment and normally most DBA-s would create a sequence for each table. Hence, if you have many tables you will end up using the same sequence for each of them, which does work, but it is not as clean and efficent as using multiple sequences. |
|
|
After playing with LightSpeed for a few days, I really like it and I think you guys have done a great job. I am considering buying a lincense for my project. However, the only thing that might prevent me from convincing my manager to buy the lincense is the issue of having a single sequence for all the tables in Oracle. Is there any chance that you can release a version that provides a workaround for it? It would be nice if the workaround can be part of the generated code from the designer, but that might be too much work for you at this moment. However, what about adding something like a 'SequenceName' property to the LightSpeedContext that allows you to switch betwen different sequences. Right now my code looks like this: ctx = New LightSpeedContext(Of TwistModelUnitOfWork)("Ctx") But I would like to change the context to use a sequence name. For instance: ctx = New LightSpeedContext(Of TwistModelUnitOfWork)("Ctx") ctx.SequenceName = 'SEQ_PERSON_ID' ctx.IdentityBlockSize = 1
|
|
|
[EDIT: The technique described here has been superseded by the MultiSequence identity method. Please see http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=1988&PostID=5474 later in this thread for info.]
Then your example code becomes: ctx.IdentityMethod = IdentityMethod.Sequence; If you want to keep the same context, but change the sequence name: ((SwitchableSequenceNamingStrategy)(ctx.NamingStrategy)).SequenceName = "SEQ_WIDGET_ID"; (This can obviously be made safer and neater, but you get the idea.) One caveat is that the sequence specified will be used for ALL inserts performed by units of work created from this context. So if you create a unit of work, add both a Person and a Widget, and then do SaveChanges(), the Person and Widget will both get IDs from whichever sequence is in effect at the time. So this strategy will work only if you're adding only one kind of entity at a time. Let us know if that's an acceptable workaround for your immediate scenario, otherwise we'll take a look at doing a proper per-type implementation. |
|
|
My inital idea to deal with this limitation was to create and cache 7-8 different LightSpeedContext-es and retrieve them through a LightSpeedContext Factory (I have only 7-8 tables where I need to insert), one context for each table, but specify a different sequence for each context. I believe that this would work correctly even for a web application where you have multiple users inserting data simultaneously into the database, as long as I use one context for each table. Let me know if this approach can work and if it does I will go with it for now, because I don't have to deal with a lot of tables. |
|
|
Yes, you can do that. In fact this will remove the need to limit the IdentityBlockSize to 1, as when you reuse a context, it will keep allocating IDs from the same block, which will suit you fine as long as contexts are per-type. You will, however, need to be very careful with your application design, specifically around associations. Consider two tables, Pet and Person, where each Person has an associated Pet. Now an entity can only be in one UOW at a time, and associations can't reach across a UOW boundary. In order to be associated, the Pet and the Person must be in the same UOW. But you can't allow them to be in the same UOW during the insert, or they will get IDs from the same sequence. So you cannot do this: using (IUnitOfWork personUow = PersonContext.CreateUnitOfWork()) What you would need to do instead is: * Create a Pet, add it to a UOW for the PetContext, and save changes on the pet UOW I.e. you must take care each insert takes place in a UOW with the "right" LightSpeedContext. If you don't have a lot of associations, or your design is such that associated sets never get created together (e.g. you have separate Create Pet and Create Person screens), then you should be good to go. If you do need to create associated sets of different types together, then this might get a bit complicated and error-prone. |
|
|
Thanks for brinding this up. I am using LightSpeed with ASP.NET MVC and in my case there is no need to do something like this: using (IUnitOfWork personUow = PersonContext.CreateUnitOfWork()) Instead I believe you can do something like: using (IUnitOfWork personUow = PersonContext.CreateUnitOfWork()) ASP.NET binds really well LINQ and all of their html controls bind to ID-s rather than objects. However, this approach works only if the pet had been already created. For my case, I don't think that I have a situation where I have to create a parent and a child at the same time. The application follows a top down approach, create parent, then create child and so on.
|
|
|
Sweet. As you say, setting the PetId directly is fine from a LightSpeed point of view; useful to know that the ASP.NET data binding will work nicely with that as well. Let us know if you run into any hitches or have any further questions or feature requests. |
|
|
I would second this. Our system has about 200 tables, and every primary key as a corresponding sequence. In any case I would need a mapping of table.field -> sequence_name mapping.
I could live with a delegate where I got the tablename and fieldname, do a dictionary lookup and return the sequence to use. |
|
|
Hi guys, We have now implemented this feature and it will be included in nightly builds dated 11 Jun 2009 and above, available from about 1430 GMT. It should be considered beta at this stage and we welcome your feedback and bug reports. How to use it: There is a new member of the IdentityMethod enum. Set LightSpeedContext.IdentityMethod to IdentityMethod.MultiSequence. You must also provide a naming strategy for the sequences. Create a class which implements INamingStrategy. Most members can just return defaultName: the member you are interested in is GetMultiSequenceName(string, Type), which needs to return the ID sequence name for the appropriate type. A sample implementation might look like this: public string GetMultiSequenceName(string defaultName, Type entityType) { Set LightSpeedContext.NamingStrategy to an instance of this class. You should now be good to go! (Please note that because we have added a new member to INamingStrategy, any existing implementations you have of INamingStrategy will no longer compile. Apologies for any inconvenience; if you don't want to use the MultiSequence method then you can just add a vacuous implementation of GetMultiSequenceName.) Once again we would advise you to treat this feature as beta for now and we would ask for your patience if you run into any bugs. We are also open to feedback e.g. is it more useful to pass the resolved table name instead of the entity type, etc. |
|
|
This is a great. Most Oracle users will ask for this feature and it is nice to have a simple workaround. |
|
|
I second this. It's a needed solution for Oracle users. Just tested the feature shortly, looks like it's working OK. |
|
|
Just a small update. Was looking for a way to have the sequence name as SEQ_TABLE_NAME with "TABLE_NAME" being the actual table name, not of the entity type (i.e. "TableName", without underscores) A bit of dirty reflection, but worked quickly: |
|
|
Bug alert for the last post! So the revised code is: public string GetMultiSequenceName(string defaultName, Type entityType) |
|
|
BTW, since using sequences for Id on rare occasions during testing I noticed PK violations starting to appear. It's irregular so far to be able to pinpoint the symptoms. Anybode else with the same problem? |
|
|
Thanks for the feedback marko. If other people would also prefer to be passed the table name rather than (or in addition to) the entity type, we would be happy to make this change, but because it would be a breaking change to INamingStrategy, I'd like to get feedback from other users before doing so. |
|
|
We had a bug in an earlier build which could cause PK violations if the IdentityBlockSize was larger than the sequence INCREMENT BY value. We believe this was fixed a couple of months ago, but if the two values are different for you, then it might be worth setting them to the same thing and seeing if the violations go away. If so, it means we have missed something in our fix (or reintroduced something in the multi-sequence implementation). Otherwise, we would be very keen to get more info on this -- please do let us know if you are able to detect any kind of pattern. Thanks! |
|
|
OK, I was running default values for both - all PK sequences having an INCREMENT BY of 1, and IdentityBlocksSize of 10. I now changed IdentityBlockSize to 1 and will observe the behavior. I will try to give a feedback. It' s needles to say, we can not really deploy a system randomly "braking" on inserts. |
|
|
Hi all, I have just downloaded LightSpeed, so, please, consider me as a newbe... My problem is that I work in a company producing applications for internal use only based on Oracle DB. This legacy DB uses "sequences" for the tables and each sequence is represented by "SEQ_" + IDFieldName. Question 1: how can I use the "GetMultiSequenceName" member? Question 2: I downloaded, to play with, the free of charge version of LightSpeed available from your server, but does this version have the "workaround" for multiple sequences on Oracle? Thanks in advance 4 your help Francesco |
|
|
1. To use the GetMultiSequenceName member, you need to create a class that implements INamingStrategy. For most members you can just return defaultName, but you will need to implement GetMultiSequenceName to return whatever is correct for the table at hand i.e. "SEQ_" + something. (You will need to figure out the "something.") You then specify this naming strategy on the LightSpeedContext: context.NamingStrategy = new MyNamingStrategy(); (You can also do this in config through the loggerClass attribute.) 2. You will need to download a nightly build to get multiple sequence support. You can get the free version of the latest nightly from http://www.mindscape.co.nz/products/lightspeed/nightlybuilds.aspx (please note the caveat on that page about upgrading from 2.2 RTM). |
|
|
Hi guys.I have a problem with implementing "SwitchableSequenceNamingStrategy". It seems like it gets value from it's default sequence (thought it seems like it doesn't even exists if it is "LightSpeed") or I don't know from where when I try to dataUnitOfWork.Add(someObject) I've done everything according to the post. But It doesn't work. To be precise - I put breakpoints to SwitchableSequenceNamingStrategyс class methods and I fall into some methods e.g. "GetTableName", "GetColumnName", "GetIdColumnName". But I never fall to "GetSequenceName" or "GetMultiSequenceName" (if set ctx.IdentityMethod = IdentityMethod.MultiSequence). Can you suggest what I have missed or what is the trouble. |
|
|
Have you set LightSpeedContext.NamingStrategy? (Or the equivalent in the config.) When using IdentityMethod.MultiSequence, you *must* hook up your naming strategy, otherwise LightSpeed has no way of knowing about it, and will use the default naming strategy (which returns the default name for all sequences). |
|
|
Oops, I missed your note that you were hitting breakpoints on other INamingStrategy methods, so I guess it is hooked up to the LightSpeedContext after all. Is it possible that your entities have a TableAttribute with an IdentityMethod setting which is overriding the context identity method? If you turn on SQL logging, are any queries to a key table or sequence generated? If not, what IDs are being assigned to the new entities? Can you provide us with a small console project that demonstrates the problem? |
|
|
Ivan. You were right It was TableAttribute IdentityMethod set to Sequence. I made it default and things got right. But than, can you explain the meaning of TableAttribute IdentityMethod when using custom naming strategy. And actually I don't understand where it took IDs 'cause shurely I miss "LightSpeed" in database :) And when I traced in output window I did'n see any calls to a sequence or some calls to some table while getting ID :) Magic maybe ;) Thanks a lot.
|
|
|
The meaning of TableAttribute.IdentityMethod is to override the IdentityMethod specified on the LightSpeedContext. It's usually intended for legacy databases where different tables have different kinds of key. For example suppose we generally used integer keys but had some tables with GUID keys. Then we would set the LightSpeedContext.IdentityMethod to, say, Sequence or KeyTable. But now the tables with GUID keys will get allocated integer IDs and it will all go horribly wrong. So in this case you would specify TableAttribute.IdentityMethod = Guid only on the tables with GUID keys. The naming strategy comes into play when LightSpeed needs the name of a key table or sequence, regardless of whether that is specified in the LightSpeedContext or in a TableAttribute. So a custom naming strategy doesn't change the meaning of the TableAttribute.IdentityMethod; it just changes where certain identity methods look for their IDs. I'm as baffled as you are as to how LightSpeed managed to get IDs from a non-existent sequence without even performing a query; but since you've solved the problem I'm not inclined to investigate further...! *grin* |
|
|
Hi ivan, first of all thanks for your reply and excuse me for the timeshift... Well, great, now I downloaded the lastest nightbuild (25th of August). I created the "MultiSequenceNamingStartegy" class implementing the INamingStrategy. Then in the Repository class I set the NamingStrategy as per your suggestion: _ctx.NamingStrategy=new MultiSequenceNamimgStrategy(); What I don't understand (please excuse me, I'm a newbe to LightSpeed) is: where I have to place the GetMultiSequenceName(<defaultName>, <entityType>) to return the correct sequence name? Do I have to place in the Properties of each Entity an Attribute? Or LightSpeed loads the sequence correct name automatically? Thanks in advance for your help Francesco |
|
|
What you need to do: * Implement GetMultiSequenceName in your MultiSequenceNamingStrategy class Now, whenever LightSpeed needs to assign an ID to a new entity, it will call your GetMultiSequenceName implementation to find out what sequence to get the ID from. (Actually, this isn't quite true. If IdentityBlockSize is greater than 1, LightSpeed will only call GetMultiSequenceName when it needs to get another block of IDs; other times it will just allocate from the last block it got, without needing to go to the database at all. But you get the idea.) In a bit more detail, what happens is this: * LightSpeed decides it needs to assign an ID to an entity. Note that the entity itself does NOT know about the naming strategy, so you do NOT need to place any reference to GetMultiSequenceName anywhere in your entity code. LightSpeed will take care of calling this method when it is needed; you don't need to call it yourself. Thus: public class MultiSequenceNamingStrategy : INamingStrategy { public static void Main() { The call to GetMultiSequenceName happens automatically behind the scenes, within LightSpeed's implementation of IUnitOfWork.Add. To summarise, you implement GetMultiSequenceName in your naming strategy class; you never call it. Hope this clarifies how things fit together -- let us know if you need more info! |
|
|
Hi to all, I'm trying to implement MultiSequence and was wondering if tablename instead of entityType.Name was implemented? |
|
|
Hi Delfino, There is an answer within this topic, but a bit unvisible as topic got large. So, copy-paste, to use sequence name with TableName, I use this code: public string GetMultiSequenceName(string defaultName, Type entityType) Maybe Lightspeed guys will give you a better option, but this works for me. |
|
|
Thank you very much, Marko. I did miss that post and repeated the question.
I have another question. What am I suppose to put in all of the other implemented interface methods? For example GetTableName, GetSchema... |
|
|
No problem, just helping a fellow engineer :) For other methods, I just put { return defaultName; |
|
|
Believe it or not, Marko, we are also fellow countrymen :) . Thanx for the help.
Ok, so now I'm using MultiSequence and that works ok when both IdentityBlockSize and Increment of the sequence set to 1 . Since we noticed that by setting the IdentityBlockSize to 1 makes multiple inserts slow (that was confirmed on another thread), I decided to set both IdentityBlockSize and Increment to 10. Is it correct to say that IdentityBlockSize is some way takes next 10 sequences and doesn't release them if not used? This is what happens to me: if I insert only 1 record with IdentityBlockSize set to 10, restarting the application, the next inserted sequence is not last seq. + 1 but last seq. + 9 . Is this expected behaviour?
|
|
|
Fellow countrymen?! LOL! We should start Lightspeed Croatia Fan Club! :) Ivan told me there is a possible bug in LS when using IdentityBlockSize = 10 (reocurring key values I noticed once), and as we're not doing much multiple inserts, we use IdentityBlockSize of 1. Making IncrementBy on sequence = 10 will always give you +10 on each call to oracle sequence, so don't believe this is the right thing to do. For me, your situation looks like expected behaviour. I believe you should try with IdentityBlockSize = 10 (bug is possibly not existing anymore), and IncrementBy = 1. But LS guys will know for sure.
|
|
|
:) :) We should! :) I wanted to post about my unique constraint errors occuring with IBS = 10 and I there it is - in your post already. It seems like the problem is still there, at least in the version we're using. I guess I'll have to go back to IBS = 1...
|
|
|
Delfino, we'll try to reproduce that IdentityBlockSize bug -- we fixed a bug or two with that scenario but it sounds like there's still something lurking. Just to provide some background on how IdentityBlockSize and INCREMENT BY interact, here is how sequence ID generation works: 1. LightSpeed maintains "last value obtained from sequence" and "next ID" values. 2. When an ID is needed, LightSpeed checks if the "next ID" is less than or equal to the "last from sequence" value. If so, LightSpeed increments the "next ID" and returns the previous value. 3. If not, i.e. "next ID" is beyond the allocated block, LightSpeed issues a SELECT NEXTVAL call. 4. Oracle increments the sequence by the INCREMENT BY amount and returns the new value. 5. LightSpeed compares the returned value and the previous "last from sequence" to see if this provides enough to fill the IdentityBlockSize. If not, it keeps issuing SELECT NEXTVAL calls until it is. 6. LightSpeed updates the "last from sequence", and resets "next ID" to be the new "last from sequence" minus the IdentityBlockSize. (This is to handle the case where other instances may have been allocating from the same sequence, and also the initialisation case where "next ID" was originally 0.) Hope this helps to understand what's going on and perhaps to make sense of any issues you're seeing. One consequence of this is that if you use IdentityBlockSize 10 and INCREMENT BY 1, I think you'd actually get the worst of both worlds: the sequence will still effectively get incremented in 10s, but it will take 10 database calls to do it instead of one. And you will incur those 10 database calls even if you're only adding one entity. (You may want to check this with a profiler/monitoring tool: I've not tested this scenario, but that's my reading of the code.) So we really do recommend keeping IBS and INCREMENT BY the same: effectively you should consider IBS with sequences not as *instructing* LightSpeed to use a certain block size, but as *informing* LightSpeed "this is how I've configured my sequence" (because LightSpeed can't figure it out for itself). |
|
|
Good explenation Ivan. And so true, I missed the thought that identity block is immediately filled, mea culpa, so really no point in making it = 10 and IncrementBy = 1. BTW, @Delfino, getting interested in the subject of performance hit on multiple insertes when using sequences with 1 values. I can't find it by searching, so if you have some time, please write a few notes... Also interested in comparisson it with a situation of using sequence for IDs within a before row insert trigger (which is the common thing with Oracle, but not really compatible with Lightspeed AFAIK). |
|
|
Marko, we've had a couple of people tripped up by the "before insert trigger" idiom, which I know is common in Oracle. Do you know of a way in this scenario of finding out what ID was allocated by the trigger? (Similar to the @@SCOPE_IDENTITY function in SQL Server, if you're familiar with that.) If so we might be able to add support for this idiom -- though no promises! |
|
|
Yes, this is one tricky difference for ID generation in two most wide-spreaded databases. P.S. I do have some years of Oracle experience, but I wouldn't call myself an expert, so possibly there are some methods which I am unaware of. |
|
|
OK, I have just looked it up, looks like CURRVAL is ok for transactions, as it will give a value only if NEXTVAL for sequence is called in session. Otherwise an error should happen: So, putting "not-knowing what actually happens in trigger" aside, I guess CURRVAL could be your best bet. |
|
|
Thanks for checking that out, Marko. It sounds like for this to work we'd need the user to configure each entity with the name of the sequence that participates in the before insert trigger, which is probably a bit too inconvenient (and error-prone). Ah well, it was just a thought -- thanks again for your advice. |
|
|
No problem. So, AFAIK, it is common case in Oracle programming that if you need to know ID after insert, you should call the sequence yourself first, and assign the id before insert (and trigger will do nothing). If you don't care about IDs, like for "direct" writing in DB or for bulk-inserts which started this discussion, leave the work to the trigger. But, I guess for LS's use this would make sense only for insert commands without adding to UoWs attached entities (not sure does LS have something like that). |
|