Keeping SQL code in its own schema

[SvdSinner]

In my database we try to keep independent chunks of the database split into their own schemas. I am going to try to add a feature to allow this.

1. The new naming convention I think makes sense is that if this feature is used, the stored procedures would be named (Chosen Schema).(existingschema prepended to the tablename with the _ extension currently used.) I'm open to tweaking this any other way as long as it can handle two tables with the same name in multiple schemas without conflicting.
2. Is there any particular object or method that would make the most sense to add an optional schema name to enable this feature. (If not provided, there would be no change from the current naming scheme)

If anybody else is interested in this feature and has thought on the implementation, post them here.

[Mimetis]

DMS is already handle Schema (only on SQL and Postgres)
If a table has a schema, all stored procedures and tracking tables will be created in this schema, as well.

Do you think about something else ?

[SvdSinner]

Yes. The idea is to give the option that every bit of SQL code could be put into it's own schema, so that that one schema would fully encapsulate what syncing needs.

My team has had great results in keeping various unrelated sub-systems in their own schemas. It had helped us keep the database organized, especially inside SSMS, and gives us the option that if 5 years from now we switch to something else to sync, everything can be easily removed. This is just our personal preferences, not anything that I'd push onto everybody. (Not many teams embrace multiple schemas in their databases.) A very similar effect already can be done by prefixing the SQL code, but as I've already dug into the code to write in support for XML, TEXT, NTEXT columns, I figured I would try to make it work exactly the way I'd like it to.

I'm not a Postgres user, so I will right this for SQL only. The same logic can probably be moved into Postgres, but I'll leave that to someone with more Postgres experience to port it over.

[Mimetis]

What part of DMS should use schema, that is not actually available?

[SvdSinner]

Not sure I'm following your question. The idea is that if I wanted a "sync" schema to house everything related to DMS, and I wanted to sync tables dbo.Accounts and config.ConfigurationValues, that all of their DMS specific code would go into the sync schema. IOW, sync.scope_info, sync.scope_info_client, sync.dboAccounts_BulkInsert table type, sync.dboAccounts_Changes stored procedure, etc.

[SvdSinner]

I do have the code for this ready. I will probably wait until we get my other pull request fixed and re-merged before I start the pull request for this.

[SvdSinner]

I just noticed this issue: #571 which will be fixed by this feature add.

[sojan1]

Hi @SvdSinner,

How can i get xml datatype synchronised without changing the schema?

Will XML column sync works if we add the xml field with a CAST to VARCHAR(MAX) in the stored procedure and also add the field in BulkType?

#1096

[sojan1]

@Mimetis We made it work by by making a small check in Dotmim.Sync.SqlServer.ChangeTracking and putting a CAST when the OriginalTypeName is XML.

XML can sync with the client database now.