LLBLGen version: 5.1.3 Database Type: postgreSql 7.4++ Driver (Npgsql)

The create table SQL create/update generated by the designer does not specify the

DEFAULT nextval('my_seq'::regclass)

for the id column.

The sequence is correctly set in the designer in the Field mappings tab for the entity.

What might I be doing wrong?

Here is an example of the generated SQL:

-- ###############################################################################################################
-- ADD statements for new elements (except FK/UC)
-- ###############################################################################################################

CREATE TABLE "public"."assettype"
(
    "assettypeid" int4 NOT NULL, 
    "name" varchar(50) NOT NULL 
);

CREATE SEQUENCE "public"."assettype_seq" INCREMENT BY 1 NO MAXVALUE START WITH 1 NO CYCLE;

-- ###############################################################################################################
-- ALTER statements for table fields and ADD statements for new primary key constraints
-- ###############################################################################################################

ALTER TABLE "public"."assettype"
    ADD CONSTRAINT "assettype_pk" PRIMARY KEY
    ( 
        "assettypeid" 
    );

-- ###############################################################################################################
-- ADD statements for new foreign key constraints, unique constraints and default values
-- ###############################################################################################################

ALTER TABLE "public"."assettype" 
    ADD CONSTRAINT "assettype_name_unique" UNIQUE
    (
        "name" 
    );

The sequence is created, but it isn't included in the create table or an alter table statement.

What I would expect to see is this line of SQL somewhere in the generated SQL:

ALTER TABLE "public"."assettype" ALTER COLUMN assettypeid SET DEFAULT nextval('assettype_seq');

Could you please share a repro project file? Or create a Helpdesk thread and share the original project file (for privacy).

This is by design: the sequences aren't set as default values on fields. This isn't needed, the sequence value is read automatically by the ORM (through the mapping)

You require this as defaults on the fields?

Our existing postgres database has them on tables already. Also, we are using EF for DAL. I have not used this particular combination of frameworks/tools (LLBL Designer/EF/Postgres) before. So I am unsure at this point if they are needed to be set.

[edit:]

I have not run code yet, but I do not see how the generated code (*ModelBuilder.cs) knows to use the sequence in an insert. While the model builder code has:

config.Property(t => t.AssetId).HasColumnName("assetid").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

It seems to be relying on the table in the database having the default set to the sequence.

EF is EF6? EF6 doesn't have the ability to define sequences for fields so this is indeed a bit of a problem. Hmm... we'll look into how to solve this, as it's not as easy as simply altering the template, as we do want to keep things separated as well: we don't want to enforce the default sequences on people, but for EF 6 it looks like the only choice.

The designer has the ability to specify defaults on types, but we have to check whether we can use that instead. We'll get back to you

Our meta model supports 'default sequences' for databases which support these, and we can utilize those when a sync is performed: we can set these when a sequence is defined on a mapping. Looking into it, it seems a good practice on postgresql to have a default sequence defined in the form of the default value if a sequence is used with a table.

There's a problem however, and that is that your timing couldn't be worse, as v5.2 beta will be released tomorrow and there's no time to change things before release. So changing this (which isn't a simple one-liner, unfortunately, otherwise it would be no problem) takes considerable work on the internal code, settings, docs, tests and we can't do that at this point, so the change will be made in v5.3 (which will be after several months, likely after the summer).

That doesn't help you though, and what might be possible instead for you is to generate the alter table statements with an additional template. I don't know how many tables we're talking about, if there are just a couple, you can do it by hand, but if there are hundreds, it's easier to automate it with a template.

I'll write a template/templatebindings file for you tomorrow to generate the additional DDL SQL script to alter tables with the sequence to set the default. That will help you with this problem now so you don't have to wait till the change is made in a later version.

That is very nice of you. I really appreciate it. We don't have that many tables, though a template addition would be helpful.

Thanks

Please extract the attached zip into a folder. In your postgresql project, in project settings, point the Additional Tasks folder to the tasks folder in the zip. Point the Additional Templates Folder to the templates folder in the zip. Save the project, click the 'reload meta data' button in the toolbar or reload the project.

Then right-click 'Relational Model Data' and select 'Create Create DDL SQL script' . This will give you the default choice for the create ddl sql script but also a new one, PgsqlSetDefaultSequences. Select that preset.

Specify an output folder and generate code. It will generate a .sql script which looks like this:

-- ----------------------------------------------------------------------------------------------------------------
-- Generated by LLBLGen Pro v5.1  Build: 5.1.3
-- PostgreSql 7.x/8.x/9.x DDL Script generated from project: PgsqlUnitTest
-- Project filename: C:\Temp\generatortest\test5\PgsqlTemplateTest.llblgenproj
-- Script generated on: 04-apr-2017 14:02.20
--
-- Script sets default sequences on all fields which have a sequence defined in their mapping.
-- Script assumes tables are already created. Run this after the Create DDL SQL script.
-- It will only set the default of fields which have no default set already in the metadata. 
-- ----------------------------------------------------------------------------------------------------------------
ALTER TABLE "public"."Chair"
    ALTER COLUMN "Id" SET DEFAULT nextval('SEQ_Chair');

It will not set a default on sequenced fields which already have a default set.

Hopefully this makes things easier for you.

Thanks a lot! It certainly will.

Implemented in v5.3 (post EAP)