LightSpeed Designer + Postgres -> How?

 Can anyone lend a hand and describe how to get the LightSpeed designer import an existing data-base from Postgres into the Designer surface.

I can see that LS 2.2 uses Npgsql.dll 1.0, I've configured the Provider/Connection string...

But what's next?

I've tried dragging a dummy entity, Entity1, so I could right-click and "Update from source" but that reports all selected entities are up to date...

So I've also tries changing the name of the entity to an existing DB table, when I right click and "Update from source" on that, the designer suggests to remove the Entity...

I'm obviously doing something VERY wrong... but what?

Using the official LS 2.2 Express...

Should I try a nightly build?

I don't think we've made any changes in the nightlies that would affect this, so I wouldn't worry about using a nightly for now.  What you're describing sounds a bit strange, so forgive me if any of the questions I ask seem stupid.

When you say you dragged a dummy entity, did you drag this from the toolbox or from Server Explorer?  If the latter, then what you are seeing is expected behaviour -- the Entity1 definition in the model should be in sync with the Entity1 schema in the database.

Can you verify the connection string and check in pgadmin that the database contents are what you're expecting?  It sounds as though the connection string is pointing to a database that contains an Entity1 or Entity1s table (and does NOT contain the "expected" table that you tried in step 2).  I am therefore wondering if you inadvertently dragged an Entity1 off an old test database and the connection string is pointing to the wrong place.

Are table names in the database pluralised?  If so you will need to set Pluralize Database Names in the model properties so that the designer can locate your existing tables.

Do you have PostgreSQL integrated into Server Explorer?  If not, probably the easiest way to import the database is to run the lsgen command-line utility.  See Help Topics > Modelling Tools > Command Line Tool in the help file.  lsgen will create a .lsmodel for your database which you can then add to your project, load into Visual Studio and continue to work with visually.  (You will probably need to do some untangling because lsgen doesn't do any layout -- right-click the model background and choose Auto Arrange to get a rudimentary layout.)  You *might* need to upgrade to a nightly build to get this to work because I think we had a bug handling .lsmodel files that didn't have a corresponding .diagram file -- if so please uninstall 2.2 RTM before installing the nightly or you will get weird HRESULT errors in the designer (this is due to a change in the installer which will allow you to do subsequent upgrades without uninstalling first).

Thanks!

I was indeed able to import the pgsql schema with a nightly build into the designer...

Apologies for jumping in on this thread, but I too have just started developing a new application which will use PostreSql in conjunction with Lightspeed and am experiencing similar frustrations.

I thought it was appropriate to post on this thread as it relates to my problems.

I am also taking an existing PostgreSql database that I want to import into lightspeed.

I noticed that you mentioned the command line tool to do this. I am also very keen to be able to drag tables from my PostgreSql database using Server Explorer, but I dont have the PostgreSql Data Provider integrated into Server Explorer and for the life of me cannot find a way, or any guidelines on the web on how to do this.

Is there any way to integrate npgsql / PosgreSql data provider into Server Explorer so I can just drag my table individually into the lighspeed designer?

Also, is lightspeed dependant upon the version of Npgsql supplied with it or am I able to use the latest version of npgsql. If this is the case, is there anything I need to be aware of, if using a different version of npgsql with the one shipped with Lightspeed.

Any help or guidance would also be appreciated.

Many thanks

Simon

Hello Simon,

As far as I know, Npgsql doesn't offer Server Explorer integration.  The only way I know of to do this is to use something like DevArt dotConnect.  However there are still a couple of gotchas around this -- LightSpeed will internally still use Npgsql to talk to the database, so care is needed not to use any DevArt-specific bits in the connection string.  See http://www.mindscape.co.nz/forums/Thread.aspx?PostID=4518 for more info.

LightSpeed is built and tested against Npgsql 1.0, and you may experience binding errors if you use it with another version.  In this case you should be able to use a binding redirect to get .NET to load your preferred version in place of LightSpeed's expected version.  (Assuming Npgsql 2 is fully compatible with Npgsql 1.)

We will also take a look at revving to Npgsql 2.x though I note they are touting .NET 3.5 and EF support which may mean we can't do this in LightSpeed 2.x (as we want to remain compatible with .NET 2.0).

Hope this helps -- please let us know if you still run into difficulties and we will be glad to help.

Hey guys,

We got a bit vexed about the lack of Server Explorer integration for PostgreSQL too, so I've written a basic provider.  It doesn't do much, just enough to support drag and drop onto the LightSpeed model designer, but it should save you having to muck around with the DevArt components if all you want is to drag tables into LightSpeed.  It's also pretty rough at the moment -- the UI for creating connections is pretty unfriendly, Test Connection probably won't work, and there's nothing much in the way of error trapping.  It has the much vaunted "Works on My Machine" badge of quality but has not been extensively tested, so please let us know (and please be patient!) if you can't get it working.

I've attached the installer as a zip file.  Unzip it, install it, go into Server Explorer and choose Add Connection, and you should now see an option for PostgreSQL Database.  Choose this and set up the connection options, and you should be good to go!

We hope this will help make it a bit easier to use PostgreSQL with the LightSpeed designer!

Cheers,
Ivan

Hmm, we've been doing some more testing of the PostgreSQL provider and it looks like there may be something amiss with the installer.  So by all means give it a whirl but we may need to issue an update for this.  Sorry!

Also, forgot to say in the previous message, but in order to use the provider with the LightSpeed designer, you'll need to upgrade to the 7 May nightly build of LightSpeed, available from  http://www.mindscape.co.nz/products/lightspeed/nightlybuilds.aspx (express edition) or the store (retail editions) after about 1430 GMT.

Okay, it turns out our potential issue was just a hostname resolution issue on the machine we were using for testing.  So if you get a mysterious error saying something like "Selecting objects of type '' is not supported", please check your host name, database name and login credentials.

We'll improve diagnostics on this in a future update.

Hi Ivan,

Once again, this demonstrates the excellent service and support provided by Mindscape.

I am extremely impressed.

I will download the latest nightly build later today and then download the installer and give it a try.

Many thanks

Simon

Thanks Ivan.

Indeed Mindscape saves the day again.

Bravo Ivan and the rest of Mindscape....

Hi,

I have downloaded the nightly build and also downloaded the Data Provider installer and installed all successfully.

I can add a new connection in the Server Explorer without any errors.

Unfortunately when I try and drag a table onto the Mindscape Design Surface, I get the following error:-

Unfortunately one of our automated tests failed (spuriously we think) on the nightly build, so what you've got there is still the previous night's version, without the support for the new provider.  We're kicking off an update now but it may take a little while.  Apologies for the inconvenience and confusion, and I'll post here when the proper nightly is available.

Hi guys,

The 7 May nightly is now available via the download link (Express edition) or the store (retail edition).  This should allow you to use the VS PostgreSQL provider with the designer.  Apologies again for the glitch.

Hello Simon,

We have now updated LightSpeed to build against the 2.0.5 version of Npgsql.  (Thanks damageboy for the pointer!)  This will be included in nightly builds dated 8 May 2009 and above.

Please let us know if you run into any problems or quirks.  Thanks!

(I have also updated the VS provider to use 2.0.5; this shouldn't be an issue because code doesn't build against the VS provider, but I've attached the update just in case.  This also provides *slightly* better diagnostics in the event of connection issues.)

Hi Ivan,

I have downloaded the latest nightly build and the new Provider installer.

I can now successfully drag tables from Server Explorer onto the Lightspeed designer surface.

However, I now have a problem within what appears to be the Lightspeed designer.

Whenever I right click the Lightspeed designer surface I am getting an error:-

"ERROR HRESULT E_FAIL has been returned from a call to a COM Component".

I am sure this is not related to the new provider that you created as I added a new lightspeed model to a completely new project and without any tables or anything being added to the designer, I still got the error.

Sincere apologies as I feel really bad raising issues after all the excellent work you are putting in on our behalf.

Also, you say that you have got lightspeed working with npgsql 2.05. What is required to use this. Do I just need to replace the npgsql dll in the GAC or do I need to do more than this?

Many thanks in advance.

Kind Regards

SImon

 Hi Simon,

Could you please try uninstalling and re-installing LightSpeed? We recently made it so that the LightSpeed installer would automatically upgrade existing versions however that change means the version before that isn't uninstalled at all (annoying, I know!). If you could do this you should never have to manually uninstall again.

Normally this issue manifests itself as the E_FAIL error so hopefully this resolves the issue.

If you uinstall and reinstall it should sort out the versions of the npgsql files that are associated with LightSpeed.

I hope that helps,

John-Daniel Trask

Hi John-Daniel,

Yes, that worked - I no longer get the error when I right click the design surface.

With respect to the version of npgsql, albeit the version installed in the new Postgres Provider for VIsual Studio is 2.0.5.0, I notice that the version installed in the Mindscape Lightspeed Provider folder and also referenced in the GAC is v1.0.0.0

Can I just replace the version in the GAC with 2.0.5 and will Lightspeed use this automatically if I do so?

Many thanks again for the help.

Simon

Looking at the time of your message, you must have been using the 7 May nightly build.  We switched LightSpeed over to 2.0.5 only in the 8 May nightly.  If you upgrade again using the current nightly build you should see 2.0.5 both in the LightSpeed Providers folder and in the GAC.

If you do not wish to upgrade LightSpeed, but want to use 2.0.5, you should use a binding redirect rather than just removing 1.0.0.0 and replacing it with 2.0.5.

Hi,

That worked perfectly - thankyou very, very much for all your help and efforts.

Regards

Simon

It is success on VS2008 with Postgresql 9.0 default port 5432 with npgsql 2.0.11 .net 3.5 and works great!

But I need to work in VS2010 and a different postgresql port number, any update to the VSPostgreSQLProvider?

Steven

I've attached an updated build to this post.  Let us know if you have any trouble downloading it as we have seen some glitches with VSIX files and the forums software in the past.

A couple of issues I noticed in testing:

- It's okay to leave out the port number and it will default to 5432.  However, once you've entered a port number, you can't delete it again -- you'll have to manually enter 5432.

- If you enter incorrect connection information, such as the wrong port number, it can send the provider into an infinite loop of error messages as it keeps trying to reconnect.  This is quite nasty because the only way out of it is to kill Visual Studio, but the fix looks a bit fiddly and I don't have time to work on it right now.  So please do take care!

As always, please let us know if you run into any other problems.

Thanks, but I found 

We're upgrading our website!

404 error on the download link.

on the previous provider I found not port number field to input, will it appear in this vix?

Steven

Maybe you can zip it and post it online.

Thanks!

We've fixed this issue and the link is now working.

Yes, the new version will display a Port option in the connection settings dialog.