xsharp.eu

NickFriend wrote:..
The database now has to be initialised which is done with the command line utility initdb in the bin folder:
C:QWPostgreSQLbininitdb.exe -D ../data --username=postgres --auth=trust
-D is the path to where your data will be
--username is the name of the superuser
--auth=trust means you don’t need to specify a password as it’ll trust anyone who can access the computer (obviously there are stricter options available, but this makes installation easy).

"trust", i wouldn't trust - when you create a db you are superuser, so using a password won't hurt. Then you might look into the role-model PG provides to control conveniently access rights.

BTW, have a look at https://postgresql.verite.pro/blog/2019 ... ances.html
about mistakenly open access from internet.
Anyway, postgres.org has at the end of page an ever changing linklist to planet postgres, where you'll find probably answers to any question you never knew you should ask

Karl

Replying on #29:

Hello Nick,

I'm steering clear of ADS as I explained to Stefan.... we need a solution for the long-term and I don't think ADS can provide that any more. Also I've got so used to relying on a "proper" RDBMS that I don't want to go back to DBF in any form now!

I wouldn't bet anything on the future of ADS either and I also fully understand that you wouldn't reintroduce DBF for data. I do however think that for a quick implementation to support an exceptional situation (no internet) one or 2 local DBF files with the free local ADS driver will work quite well and can be implemented easily, without installation issues etc. But as you seem to be well on your way of having solved these the PostgreSQL solution is no doubt very good.

Dick

FFF wrote:Anyway, postgres.org has at the end of page an ever changing linklist to planet postgres, where you'll find probably answers to any question you never knew you should ask

While browsing there, i stumbled over B. Momjian's blogpost re. Order of Select Clause Execution, where he in turn points to:
https://blog.jooq.org/2016/12/09/a-begi ... perations/
"as it explains it better than i could"

Good read!

Karl

Karl,

That's a very interesting read, especially for someone like me who's a total idiot at SQL!

It's also, without mentioning it, a near perfect explanation of how Linq to Entities works with Entity Framework over a database. A typical L2E statement might be something like this

mydatabase.MyTable.Where(p => p.MyColumn == whatevervalue).OrderBy(p => p.MyOtherColumn).Select(.....) etc.

In our entire application there are perhaps a dozen or so lines of raw SQL, things like backup and restore and creating users. All the rest of the database access is through L2E statements like this. I love it as it's so logical and easy to understand.

Nick

Hi Karl,

FFF wrote:

FFF wrote: While browsing there, i stumbled over B. Momjian's blogpost re. Order of Select Clause Execution, where he in turn points to:
Karl

I agree, would be nice if we could have stated FROM Cust WHERE <condition> ORDER BY <order> SELECT *

One thing that I also addressed in my last DD article, the GROUP BY where you have to add the GROUP columns to the select...

I see PG has actually finally listened to ANSI SQL Standard, SELECT Count(*) FROM <Table> PARTITION ... OVER ...

Hi All,

Just an update on where I've gone with this subject. I decided it was well worth investigating thoroughly, so since my post a few days ago describing how to get started with a manual install of PostgreSQL I've been able to do a complete port of the full manufacturer version of our software from SQL Server to PostgreSQL.

The existing version had been working with SQL Server via Entity Framework 6. To work with PostgreSQL it was really necessary to move to the cross-platform Entity Framework Core version. So first I ported from EF6 to EF Core, but still using SQL Server. This required a number of syntax changes as EF Core is a complete reworking of Entity Framework and has changed somewhat as a result. In spite of this the process took only a day.

With the application now working over EF Core, the next step was to swap from the SQL Server provider to the PostgreSQL equivalent which is Npgsql. Obviously the objective of Entity Framework is to abstract out the db access, and in this it's amazingly effective. The basic swap from one provider to the other requires little more than replacing the referenced assemblies and changing a few method calls from eg XXX.UseSqlServer to XXX.UseNpgsql or similar.

Of course things are never that simple. We use an N-Tier architecture, and db access is controlled by an independent application that runs on the server, and which transmits data via WCF to client applications. This means added complications as the data is disconnected from the server and needs to be matched up again on return, and differences in how SQL Server and PostgreSQL handle concurrency control meant having to adjust the data classes a little.

There are also differences in simple things like table and column naming conventions between SQL Server (camel case) and PostgreSQL (snake case) which complicate any raw sql calls.

Anyway in conclusion, in 5 days I've been able to go from not even having PostgreSQL on my computer to having a large scale LOB application completely ported to use it - this is a tribute to how quick and easy PostgreSQL is to set up and use, and how effective Entity Framework is in abstracting out db access.

I won't bore you with all the details here, but if anyone is interested in more information on either EF6, EF Core, porting from one to the other, and using PostgreSQL through EF Core, just let me know. And as I mentioned, this is in the context of an N-tier architecture using entities disconnected from EF, which is another complete subject in itself.

Incidentally we use a very strict MVVM pattern for our app, and that has also helped enormously - of the 20 or so large projects that make up the VS solution, only two have any reference to Entity Framework, so virtually no changes were needed in 90% of the solution.

Once again thanks to those who pointed me towards PostgreSQL.

Nick

Hi Nick,

We have used Firebird for over ten years and have over 300 sites dependent on it. The install exe is about 6MB. DotNet provider works well. We use the excellent ODBC driver. Never had a problem except I cannot understand why it doesn't get used more often.
Firebird also has an embedded database version. I have heard of users running 1T and bigger databases.
It is fully open source and free.

Regards,
Tim

Hi Nick,
Great news! Why don't you put your experience in an article about using EF.Core and your migration plan?

NickFriend wrote: Of course things are never that simple.

Seems you have made it sound a lot simpler than what is was...

and differences in how SQL Server and PostgreSQL handle concurrency control meant having to adjust the data classes a little.

Would love to hear how you handle this.

There are also differences in simple things like table and column naming conventions between SQL Server (camel case) and PostgreSQL (snake case) which complicate any raw sql calls.

This is probably the most "work" one has to get accustomed to, PG is a lower case system and uses schemas with default %UserID%, public prefix to find tables. It however helps with setting up privileges on schema level when different users have different roles in managing the database.

but if anyone is interested in more information on either EF6, EF Core, porting from one to the other, and using PostgreSQL through EF Core, just let me know. And as I mentioned, this is in the context of an N-tier architecture using entities disconnected from EF, which is another complete subject in itself.

As I mentioned an article to be uploaded in the user contrib area might be useful to the broader user base.

Once again thanks to those who pointed me towards PostgreSQL.

You welcome, we might also point you to XSharp...

Well done and happy PGing,

Hi Johan,

When I get a moment I'll try and organise some sort of documentation, though it would be useful if someone were prepared to translate samples in C# into XSharp for that. If XSharp had appeared a few years earlier I would undoubtedly have used it, but of course like many others I just couldn't wait for Vulcan to get it's act together so had to make the jump to C#. Having said that, as I've mentioned before, I now love the clarity given by curly braces and semi-colons.

In SQL Server you have the Rowver byte[] column which is specifically designed for concurrency control (it is incremented automatically with each update to the record). With Entity Framework all you have to do is include a Rowver type column in your table and EF will automatically use it for concurrency control - if the record you're trying to save has a different Rowver value than the record in the db, a concurrency exception is thrown.

PostgreSQL doesn't have this. What it does have is a hidden system column called xmin of type uint which is automatically added to every table, and which is also incremented automatically with each update of the record. The Npgsql EF provider has an option to switch on the use of this column for concurrency control which then gives you the same effect as the Rowver column.

The trouble arises with a disconnected environment like ours. If you access the database directly using EF from your client application, then it's very easy. In pseudo code: If the company record gets changed by another user in the meantime, a concurrency exception is thrown and you can then handle it however you want. However this implies referencing EF throughout the application, which violates all principles of separation of tiers. So we map the objects returned by EF to in-memory objects disconnected from EF. To handle the concurrency with SQL Server we just include the Rowver column in the in-memory objects and round-trip it to the client. With PostgreSQL though we have to lever out the hidden xmin column and round trip that - luckily the Npgsql provider has options to allow this.

With regard to snake case, this is a real pain as of course C# is case sensitive and I like camel case for my class properties. If you do that though and generate a database in PostgreSQL, as soon as you capitalise anything in PG you have to enclose it in double quotes in raw sql. So this means escaping quotes all over your statements which is a real pain and of course very subject to errors. The trick is to force EF to map all the class and property names related to the db into lower case and let the automatic mapping to/from the database handle the conversion to the in-memory camel case equivalents.

Ho hum!

Nick