Method string Translate(SqlCompilerContext context, SequenceDescriptor descriptor, SequenceDescriptorSection section) is not overridden in postgresql adapter. This leads to wrong ddl create table generation when calling SqlDdl.Create(...). Instead of creating PG SERIAL field type it creates standart MSSQL sql code for sequence: " ... bigint START WITH 1 INCREMENT BY 1 ...". This problem can be solved by using custom Xtensive.Sql.SqlValueType but this breaks encapsulation, because in this case I have to support PG database in my project codebase.

asked Aug 18 '16 at 02:00

EBGeWik's gravatar image

EBGeWik
5333


2 Answers:

Hello EBGeWik

Thank you for the bug report.

PostgreSQL provider wasn't designed to handle sequences as MS SQL Server provider does. PostgreSQL provider doesn't expect that sequence will be owned by a table column. It handles sequence as a database sequence, so such case when you creates SequenceDescriptor instance and set in as a sequence for table column won't work. We should have thrown an exception for such cases, and we will. It is not so hard to fix so, I think, we can fix it in current developing version. But we won't implement the same behavior as we did for MS SQL Server. Here below I will try to explain why we won't.

There is a difference between this two RDBMSs.

Auto-increment column in MS SQL Server is a combination of column of numeric type and sort of sequence. I'd say this is an internal sequence for a table, and each table can have only one such sequence. Declaration of this sequence has its own syntax and it's quite simple.


CREATE TABLE Employee
(
 Id int IDENTITY(1,10),
 FirstName nvarchar (20),
 LastName nvarchar(20)
);

It makes compilation of such query very easy and compiler compile a SqlExprssion tree on one traverse.

In PostgreSQL there are two ways to create an auto-increment column - using special type SERIAL and declaring sequence as source of next values.

SERIAL type is just a type and it has no settings of value generation. Personally, I wouldn't say SERIAL type could be a result of SequenceDescriptor translation.

Another way is pretty much the same as in MS SQL Server, but with one important difference - the sequence which is used for value generation is database sequence and must be created before its first use in CREATE TABLE statement.

This difference makes it impossible to compile SqlExpression tree in one traverse. If we implemented such behavior we would have to break current query compilation, create a sequence, compile its creation to SQL statement and then start compilation of CREATE TABLE statement one more time. This option is not acceptable, moreover you have all you need to create auto-increment column for PostgreSQL but a bit differently.

answered Aug 22 '16 at 10:22

Alexey%20Kulakov's gravatar image

Alexey Kulakov
52715

Hello, Alexey DataObjects documentation declares postgresql support. It means that when I use DataObjects I expect to have possibility to change underlying database to any database that is in support list without any changes in my codebase. In reality I face with situation when code, once written with MSSQL in mind, can't be used with PG. As a client of DataObjects I don't want to know about implementation details of each particular database, I just want to use easy and simple encapsulation API which hides those details.

(Aug 23 '16 at 01:33) EBGeWik EBGeWik's gravatar image

It does support PostgreSQL - as I said you are able to achieve your goal , otherwise, you couldn't do it. SqlExpression-based expressions, SQLDML and SQLDDL, together make very low level API. Basically, it is a SQL query but in form of objects. If you use this API you will have to take into account such details as we do.

(Aug 24 '16 at 05:43) Alexey Kulakov Alexey%20Kulakov's gravatar image

SERIAL type is just a type and it has no settings of value generation. Personally, I wouldn't say SERIAL type could be a result of SequenceDescriptor translation.

Ok, why wouldn't you hide those details behind SequenceDescriptor class? From your point of view as a developer it might be difficult to implement this, but from the user point of view SequenceDescriptor just represents the notion of sequence, no matter has it settings of value generation or not. Why should I describe the same notion of sequence using different terms?

(Aug 29 '16 at 06:34) EBGeWik EBGeWik's gravatar image

One more problem for this topic: System.NotSupportedException: Function 'LastAutoGeneratedId' is not supported. в Xtensive.Sql.Compiler.SqlTranslator.Translate(SqlFunctionType type) в Xtensive.Sql.Drivers.PostgreSql.v8_0.Translator.Translate(SqlFunctionType type)

answered Aug 29 '16 at 03:11

EBGeWik's gravatar image

EBGeWik
5333

Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

Subscription:

Once you sign in you will be able to subscribe for any updates here

Tags:

×6

Asked: Aug 18 '16 at 02:00

Seen: 2,261 times

Last updated: Aug 29 '16 at 06:34

powered by OSQA