Source control does not get triggers?

gemisigo wrote:

I don't know if it ever worked for me, I haven't tried till you mentioned it. But checking the DB Query I can see that it queries the dbo.syscomments, which is a backward compatibility view for SQL 2k. As far as I remember it contains sql definitions for views, procedures, functions, etc. but it has nothing to do with tables. So if that works for you then you must have something else there. Of course, it might be remnant from earlier versions. This one is what I have in effect for SQL Server:

Code:

if @@version like '%SQL Server 2005%'
or @@version like '%SQL Server 2008%'
or @@version like '%SQL Server 2012%'
IF EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WHERE object_id = :OBJECT_ID)
   SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
   FROM [$DB_NAME$].sys.synonyms o
   JOIN [$DB_NAME$].sys.schemas s ON s.schema_id = o.schema_id
   WHERE o.object_id = :OBJECT_ID
ELSE
   SELECT text
   FROM [$DB_NAME$].dbo.syscomments
   WHERE id = :OBJECT_ID
   ORDER BY colid
ELSE
SELECT text
FROM [$DB_NAME$].dbo.syscomments
WHERE id = :OBJECT_ID
ORDER BY colid

here's mine

Code:

IF (@@version LIKE 'Microsoft SQL Server 2005%'
OR @@version LIKE 'Microsoft SQL Server 2008%')
or @@version like '%SQL Server 2012%'
or @@version like '%Denali%'
IF EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WHERE object_id = :OBJECT_ID)
   SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
   FROM [$DB_NAME$].sys.synonyms o
   JOIN [$DB_NAME$].sys.schemas s ON s.schema_id = o.schema_id
   WHERE o.object_id = :OBJECT_ID
ELSE
   SELECT text
   FROM [$DB_NAME$].dbo.syscomments
   WHERE id = :OBJECT_ID
   ORDER BY colid
ELSE
SELECT text
FROM [$DB_NAME$].dbo.syscomments
WHERE id = :OBJECT_ID
ORDER BY colid

Hmm, except for the IF condition they look very much the same to me. I'll experiment with this DB Query a little to see what can be done here. But that'll be later, I've got a bus to catch now.

	gemisigo wrote:
	Hmm, except for the IF condition they look very much the same to me. I'll experiment with this DB Query a little to see what can be done here. But that'll be later, I've got a bus to catch now.

You seriously don't get anything like this:

http://screencast.com/t/p0nq9uokDCeU ?

No. Just the error message I mentioned earlier.

	gemisigo wrote:
	No. Just the error message I mentioned earlier.

Damn. Hope you get that sorted.

Mindflux wrote:

	gemisigo wrote:
	No. Just the error message I mentioned earlier.

Damn. Hope you get that sorted.

It'll be an easy one, provided I get an opportunity to fix it. Besides, I haven't used that feature that much.

Last edited by gemisigo on Sun Jun 02, 2013 6:26 pm; edited 1 time in total

Please wait for a day. That DDL query is used only for quick code preview, but not for full DDL reverse-engineering. For the full DDL for all types of objects we are using saDDL.exe utility. We expect a new build tomorrow that should provide better support for triggers.

	SysOp wrote:
	Please wait for a day. That DDL query is used only for quick code preview, but not for full DDL reverse-engineering. For the full DDL for all types of objects we are using saDDL.exe utility. We expect a new build tomorrow that should provide better support for triggers.

Excellent!

I hope we can find a way to get the perms for tables too for SCS at the very least. Heck for me (in my ideal world) I'd like the table DDL dump to contain the table, constraints, keys, triggers and permissions in one .SQL file. Right now the table (constraints/keys/etc) dump to TableName.SQL and triggers to TriggerName.SQL. I'd prefer the triggers to be tacked on to the table .SQL file (perms too). Is there any way to script the SCS dumps like that?

I support the idea. Also, it would be nice if it was possible to apply formatting to the retrieved DDL prior to saving them to file.

Meanwhile, it seems that SA fails to retrieve the collation for [n][var]char columns in case it does not match database default in SQL Server.

	gemisigo wrote:
	No. Just the error message I mentioned earlier.

That's weird. I tried it on several different servers and the results weren't consistent. Depending on the server sometimes I got the code and sometimes the error message. But I couldn't figure out what's the difference. I'll investigate this later.

I'm afraid formatting the DDL code is not an option. The reverse-engineered DDL code is used in several ways including source control systems. Applying any code transformations would make it different in the source control from the code in the database and so it would appear as the code of each object in the database isn't the same as in the source control system. The comparison is performed in a different unit, and reapplying the formatting before code comparison might be difficult to implement there.

SysOp wrote:

I'm afraid formatting the DDL code is not an option. The reverse-engineered DDL code is used in several ways including source control systems. Applying any code transformations would make it different in the source control from the code in the database and so it would appear as the code of each object in the database isn't the same as in the source control system. The comparison is performed in a different unit, and reapplying the formatting before code comparison might be difficult to implement there.

On the contrary. It's the formatting that could provide some flexibility. I can't speak for others but I like sql formatted regardless of being on the server or saved to the file. Hence I'd prefer the source controlled files to be formatted as well.

Formatting both the retrieved DDL and the code stored in source control could make those differences vanish (okay, not vanish since the formatting does not apply to code there's no rule for, but make them differ less). The DDL might sit unchanged in the repository for ages when comes a grammer/format fanatic (eg. me) and formats it just to make it human readable. Now, the format is changed, but the object itself is not, still the comparison will indicate there are changes.

Also, the format the DDL extractor saves the tables in the workspace might not be the desired one and it would be very inconvenient to open-format-resave-commit each file before committing to the repository (and no, batch formatting would not make it less inconvenient, having to work in a different environment :)

Not to mention that if the database was sent directly to the repository those steps would result in obsolete duplicate versions of the same tables.

	gemisigo wrote:
	IMeanwhile, it seems that SA fails to retrieve the collation for [n][var]char columns in case it does not match database default in SQL Server.

Sorry, my bad, I should have underlined "in case it does not match database default". Right now SA (6.4.208) does retrieve the collations but for every [n][var]char column regardless it matching the database default or not and that might not be the goal. Collations are one of the greatest nuisances since (re)inventing the wheel, that's why it's so important to have them set right, that is, they should only be added to the DDL if they differ from the database default.

	Quote:
	Right now SA (6.4.208) does retrieve the collations but for every [n][var]char column regardless it matching the database default or not and that might not be the goal.

Thank you. We will look into that.

gemisigo wrote:

	gemisigo wrote:
	IMeanwhile, it seems that SA fails to retrieve the collation for [n][var]char columns in case it does not match database default in SQL Server.

Sorry, my bad, I should have underlined "in case it does not match database default". Right now SA (6.4.208) does retrieve the collations but for every [n][var]char column regardless it matching the database default or not and that might not be the goal. Collations are one of the greatest nuisances since (re)inventing the wheel, that's why it's so important to have them set right, that is, they should only be added to the DDL if they differ from the database default.

Personally, I'd like to see everything that needs a collation (default or otherwise) to explicitly declare it in the DDL. That way, if your default collation changes (for whatever reason) it doesn't mess with the table data.