Hi,

Are there any recommendations for how to use LLBLGen pro either db first or model first when working in a development team where every developer has their own copy of the database? E.g. developer a makes some changes to the llblgen project file and their database, then checks in the project file. How do the other developers get from the designer a script that will update their local database?

Thanks

Scott

Hi Scott,

Based on my experience and needs about this, I would recommend you:

1. Make sure your project is versioned somewhere (local, remote, etc) and your team have access to it.
2. Write an initial DB DDL script that contains all the schema. If you work DB-first then generate the script from your DB tool. If you work Model-first, generate the Create DDL Script in LLBLGen Designer.
3. If you make changes in the model, Sync the relational model data.
4. After (3) Export DB Schema Update Script in LLBLGen Designer. This will generate the .sql file with the specific schema changes.
5. Commit and push to your version control.
6. Do (4-5) every time a new change is needed.

After that, your folder solution should end up looking like:

myProyect\ | |- code |- docs |- db\ |--- DDLSQL_CreateScript_20190502-000000 |--- DDLSQL_UpdateScript_20190502-183005 |--- DDLSQL_UpdateScript_20190503-050018

You could change the names to make them more representative to your actual changes:

|- db\ |--- DDLSQL_InitialScript |--- DDLSQL_20190502-183005_OrderFieldsAdded.sql |--- DDLSQL_20190503-050018_FixClients.sql

Every developer that wants to create the DB could execute the scripts in the default order (name): First the base initial script, then all the update ones, you could even write a .bat program that executes all the scripts. This way every body is in sync with the db schema. This also has the benefit that it's easy to track the changes in the source control (commits).

If you initiates a new branch or merge to the main branch in your source control, you could (if you want) to unify the initial DDL script (step 2 above) and delete all the update scripts, this way you won't end up with thousands of .sql files.

Hope that helps

Thanks, yes creating scripts is one way. I was thinking that if you could refresh the llblgen catalog from the local database without actually updating the entities then you could then do a model first update and get the changes you'd need to get from the version of the local db to the one that matches the entities, but as far as I can see there is no way to do that?

ah so the model first step would alter the schema you want the changes on. The thing is that model first works by assuming the model it is mapped on is a projection of that model. So if you have metadata with e.g. some differences in the tables, it will simply overwrite those.

What you want is a kind of diff at schema level. the model first sync would alter any tables to match with the model, so that's not giving you the diff you need, as it might create new tables because the ones that are there don't match the entity (e.g. you renamed Cust to Customer and changed the PK field).

If the schemas all work in different tables, then it's doable this way, but then you could also simply refresh from a database, to get the new tables another person worked on in the project, then switch the sync source to model, create your changes, and export those.

At the moment I indeed don't see a way to do a diff at the schema level as the designer either sees the schema it obtains meta-data from as the ground truth and creates the model from that, or it sees the model as the ground truth and creates the schema from that.

I could just try this, but what would happen if the second developer did not update the llblgen proj, but instead just merged the entity section from the next version, so for example take this change:

           <Field Name="Key" Type="int" IsReadOnly="true" Precision="10" IsPrimaryKey="true" />
+         <Field Name="SettingName" Type="string" MaxLength="30" />
           <Field Name="Settings" Type="byte[]" MaxLength="2147483647" />

and

            <FieldMapping FieldName="Key" TargetFieldName="grid_settings_key" SequenceToUse="SCOPE_IDENTITY()" />
            <FieldMapping FieldName="SettingName" TargetFieldName="setting_nm" />
            <FieldMapping FieldName="Settings" TargetFieldName="settings" />

But not this

                     <Field Name="settings" Ordinal="4" DbType="23" Length="2147483647" />
+                   <Field Name="setting_nm" Ordinal="5" HasDefaultValue="true" DefaultValue="'Default'" DbType="12" Length="30" />
                   </Fields>

And then did a model first sync?

Edit.. What I'm not understanding is how that would be different to the second developer just doing the changes in the designer from his/her's version of the llblgen project before the pull.

IMHO .llblgenproj file should modified for just one developer at a time. The conflicts resolution could be hard. Is not like everybody would make model changes. In my experience the model is touched by few people and it's per-feature basis.

Hi, conflicts are not the point of this thread, its getting schema changes from one developer to another.

So I tried my earlier suggestion and this is my findings.

Developer 1 makes the change to the db and does a db first sync in the LLBLGen designer getting the model in sync. He/she then checks in the project file, which create a patch like this:

--- a/src/TheHub.Entities.llblgenproj
+++ b/src/TheHub.Entities.llblgenproj
@@ -223,6 +223,7 @@
           <Field Name="DaysBeforeEmail" Type="int" IsOptional="true" Precision="10" />
           <Field Name="Description" Type="string" IsOptional="true" MaxLength="100" />
           <Field Name="Key" Type="int" Precision="10" IsPrimaryKey="true" />
+         <Field Name="NewCol2" Type="int" IsOptional="true" Precision="10" />
           <Field Name="SetByUserId" Type="string" IsOptional="true" MaxLength="50" />
           <Field Name="SetLocalDateTime" Type="datetime" IsOptional="true">
             <OutputSettingValues>
@@ -6858,9 +6859,9 @@
         <Sequence Name="@@IDENTITY" />
       </SystemSequences>
       <Catalogs>
-       <Catalog Name="TBH" SyncSource="1">
+       <Catalog Name="TBH">
           <Schemas>
-           <Schema Name="dbo" SyncSource="2" LastSyncWithDatabase="2019-05-03T19:27:38.2502937Z">
+           <Schema Name="dbo" SyncSource="2" LastSyncWithDatabase="2019-05-10T12:36:13.2432922Z">
               <Tables>
                 <Table Name="HSE_ALERT">
                   <PkConstraintName Name="PK__HSE_ALER__4A5467AC32774862" />
@@ -6875,6 +6876,7 @@
                     <Field Name="set_local_dtm" Ordinal="8" IsOptional="true" DbType="4" />
                     <Field Name="alert_description" Ordinal="9" IsOptional="true" DbType="12" Length="100" />
                     <Field Name="days_before_email_nbr" Ordinal="10" IsOptional="true" DbType="8" Precision="10" />
+                   <Field Name="new_col2" Ordinal="11" IsOptional="true" DbType="8" Precision="10" />
                   </Fields>
                 </Table>
                 <Table Name="HSE_ARCHIVE_CLAIM">
@@ -13169,6 +13171,7 @@
             <FieldMapping FieldName="DaysBeforeEmail" TargetFieldName="days_before_email_nbr" />
             <FieldMapping FieldName="Description" TargetFieldName="alert_description" />
             <FieldMapping FieldName="Key" TargetFieldName="alert_key" SequenceToUse="SCOPE_IDENTITY()" />
+           <FieldMapping FieldName="NewCol2" TargetFieldName="new_col2" />
             <FieldMapping FieldName="SetByUserId" TargetFieldName="set_by_user_id" />
             <FieldMapping FieldName="SetLocalDateTime" TargetFieldName="set_local_dtm" />
           </FieldMappings>

Developer 2 does a pull and cherry picks the changes from the project file ignoring the Field in the Table and deleting the

TargetFieldName="new_col2"

in the FieldMapping. He/she then opens the project in the LLBLGen designer and does a model first sync which identifies the chang. Then does a Generate Update schema and gets

ALTER TABLE [dbo].[HSE_ALERT] 
    ADD [NewCol2] [int] NULL

Which can then be applied and finally reverts the cherry pick and does a normal pull/merge.

This seems to work and I suppose some of this could be automated (i.e. the cherry pick and editing of the llblgen project file).

I think that would work. In my case I prefer to establish the convention that the developer that make a model change (DB-First or Model-First) must add the .sql to the changes folder, that way all other could see the change they have to apply in the pull. I think this is very close to your findings, it's just a matter of establish the convention in your team.