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
|
I am evaluating Lightspeed with postgresql I am however a bit in the dark when it comes to the Primary Key. When my model is deployed the identity column is of type int. What if i want this to be of 'serial` type (somewhat cleaner approach). Since i can't see any sequences being rendered i was wondering how it would deal with INSERTS Given this unit test:
[TestMethod] public void TestUserNameIsUnique() { using (var uow = _context.CreateUnitOfWork()) { var user = new User(); user.Username = "TestUser"; user.Password = "TestPass"; uow.Add(user); uow.Add(user); var otherUser = new User() { Password = user.Password, Username = user.Username }; uow.Add(otherUser); uow.SaveChanges(); } }
I'm getting the following exception: Test method Mpdreamz.Domain.Tests.UnitTest1.TestUserNameIsUnique threw exception: Mindscape.LightSpeed.LightSpeedException: An error occurred when trying to select from the sequence 'LightSpeed'. Check the InnerException for details. The error message was: 'ERROR: 42P01: relation "lightspeed" does not exist'. ---> Npgsql.NpgsqlException: ERROR: 42P01: relation "lightspeed" does not exist. I can see lightspeed does the following using the console logger:
SELECT nextval('LightSpeed'); --> Time: 0 ms This sequence is however never generated. Here's my uow:
_context = new LightSpeedContext<MpdreamzModelUnitOfWork>() { ConnectionString = "Server=Localhost;Database=Website;User Id=postgres;Password=Somepass2", DataProvider = DataProvider.PostgreSql8, IdentityMethod = IdentityMethod.Sequence, Logger = new ConsoleLogger(), };
Is there anything special I need to do to get autoincrementing working with Postgresql ? It's simply not generated when I deploy from the designer. |
|
|
The designer does not yet have the ability to generate sequences or key tables. (The reason is that the identity method can be selected at run time via the LightSpeedContext, so the designer doesn't know what the default identity method will be and therefore doesn't know whether a sequence or key table will be required.) You therefore need to run the appropriate SQL script from the Providers directory (under the installation directory). |
|
|
Ok seems to make sense however if I have a postgresql database do I even want to allow LightSpeedContext's with a different identity method ? I am still green behind the ears but my gut says I'd like to dictate the identity method in my model (even to annotate use the most common for each db provider) and disallow different runtime identity methods. I mean what will it mean for the database if you start using the wrong identity method at runtime ? It would also mean the Lightspeed can deploy based on connection string information. Doesn't the designer already generate auto_increment columns for rdbms's that support it (MSSQL, MYSQL) ? From a model first perspective it would be nice if I could say deploy to a postgresql database generate the appropiate serial PK's(instead of ints) if i say deploy to Mysql create auto_increment int primary keys and have all the LightspeedContext's respect my model's. What's the suggested development route here ? In any case the need to massage the database with sql scripts to work nicely is something that should be way more prominently mentioned. I kind of assumed(and hoped!) Lightspeed would take care of this for me. |
|
|
I created the sequence but i am still getting the following error message:
Test method Mpdreamz.Domain.Tests.UnitTest1.TestUserNameIsUnique threw exception: Mindscape.LightSpeed.LightSpeedException: An error occurred when trying to select from the sequence 'LightSpeed'. Check the InnerException for details. The error message was: 'ERROR: 42P01: relation "lightspeed" does not exist'. ---> Npgsql.NpgsqlException: ERROR: 42P01: relation "lightspeed" does not exist. |
|
|
The likeliest cause I can think of for this is case-sensitivity, e.g. PostgreSQL is looking for 'lightspeed' when the sequence is actually named 'LightSpeed' or vice versa. Try recreating the sequence with the other-cased name (or use LightSpeedContext.Logger to check the casing of the issued query). Another possibility is that the sequence has been created in the wrong schema (e.g. the sequence is in 'public' while your LightSpeedContext.Schema is set to 'mpdreamz'; this seems unlikely but I thought I'd mention it just in case. |
|
|
I used the casing as supplied in Sequence.sql in ..\LightSpeed\Providers\PostgreSQL8
DROP SEQUENCE LightSpeed; CREATE SEQUENCE LightSpeed INCREMENT BY 10 START WITH 1; This might need some updating then ? (why the increment by 10 by the way?)
Using "lightspeed" works it selects a nextval now however now i am getting this error: Test method Mpdreamz.Domain.Tests.UnitTest1.TestUserNameIsUnique threw exception: Npgsql.NpgsqlException: ERROR: 42601: syntax error at or near ".". With the logger giving me this:
SELECT COUNT(*) FROM User WHERE (User.Username = 'TestUser' AND User.Id <> 2) The logger actually shows this as:
SELECT COUNT(*)FROM UserWHERE (User.Username = 'TestUser' AND User.Id <> 2) It's not multi lined until i copy paste it into this forum reply box. Is deploying to PostgreSQL really this hard with Lightspeed ? Having to run sql scripts to create the sequence tables Lightspeed expects is not documented anywhere and I am not trying to do anything weird here. I refuse to believe that Lightspeed is in the wrong here for now and just assume it's my green ears but i can't help but feel some disappointment. |
|
|
Regarding casing in the script: the script we provide works on our PostgresSQL test database, and for most of our customers, but we have found that different databases can have different case sensitivity behaviour depending on database setup and the use of the LightSpeedContext.QuoteIdentifiers setting. We'll certainly keep monitoring this but at the moment what we have seems to work for most customers. Regarding the documentation: thanks for the feedback. The need to run scripts *is* documented, but where anyone is likely to find it (it's under Help Topics > LightSpeed > Identity Generation, but even knowing it was there I had to look hard for it). We clearly need at minimum to pull out a top-level help section called "Setting Up Your Database" or something like that. Furthermore, the ability to use the designer to create entity database schemas has created expectations which the identity generation side of things hasn't caught up with yet: you're not the first person to be tripped up by the need to create KeyTables or sequences, so clearly we need to improve this area. Again, thanks for the feedback. Re why the INCREMENT BY amount is 10: this is to reduce the number of round-trips to the database, i.e. querying the sequence for every 10 inserts rather than every single insert. It corresponds to the LightSpeedContext.IdentityBlockSize (and must have the same value). Finally, regarding your syntax error, the SQL looks fine as far as I can see. The only thing I can think of is if this is another casing issue: I'd expect that to throw a "no such column" error rather than a syntax error, but I seem to recall that Npgsql can sometimes be a bit misleading with its errors. (Unfortunately I don't have a PostgreSQL database to hand to test this theory with.) Try running the query in a PostgreSQL admin tool and see if changing the case makes a difference. If this is the case, and the database is under your control, then I'd suggest configuring the database to be case-insensitive. Otherwise we may have to implement a NamingStrategy to convert everything to whatever case PostgreSQL is looking for, but this hasn't been necessary for our other PostgreSQL customers, so I'm hoping it won't be necessary in your case either. Another possibility is that it is confusing the User table for a reserved word, but in that case I'd expect the error to happen near FROM rather than near '.'. |
|
|
Ok I guess I'll have to dig deeper in Postgresql configuration to relax it's casing requirements. I guess regarding the modeling of identity columns this is something where I have to let them go and trust on the database my model generates and that a valid context is supplied. It would be nice if the designer allowed us to model database specific identity column generation schemes to ease deployment. For instance i would really like mindscape to render 'serial' type identity columns instead of using 'int' and only one sequence table. I'm still not sure if I can do this right now. It would also be nice if I could dictate identity methods in the model so that invalid context's would throw an error. What happens now if the wrong identity method is employed by a context? Thanks for the INCREMENT BY explanation it's nice to see Lightspeed implementing a HiLo Id generation scheme to limit database roundtrips. I'll repost if I find anything regarding the Postgresql casing issues. |
|