SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 7.3.421 Pro] - Bug: Source Control

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 7.3.421 Pro] - Bug: Source Control
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post [SA 7.3.421 Pro] - Bug: Source Control Reply with quote
7.3 beta properly retrieves code for triggers, for example, the code shows
Code:

CREATE TRIGGER [legacy].[v_jaratok_INSTEAD_OF_INSERT]


However, changing that code and trying to push the changes into the database fails, because the trigger in Repo Browser is categorized as a procedure. The process stops when it tries to drop the trigger using DROP PROCEDURE instead of DROP TRIGGER. See the error message below:

Quote:

Can't delete object "legacy.v_jaratok_INSTEAD_OF_INSERT". Reason: A database error occurred while executing the following code:
DROP PROCEDURE [legacy].[v_jaratok_INSTEAD_OF_INSERT]

Errors:Msg 3705, Level 16, State 1, Cannot use DROP PROCEDURE with 'legacy.v_jaratok_INSTEAD_OF_INSERT' because 'legacy.v_jaratok_INSTEAD_OF_INSERT' is a trigger. Use DROP TRIGGER.


EDIT: Also, the file stored for a view also stores its triggers but changing that triggers only results in changing the state of the file storing the trigger. It should show the file for the view as modified, too.
Wed Oct 07, 2015 5:32 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Thank you very much for your bug report. I have opened a bug ticket for this issue.
Wed Oct 07, 2015 9:24 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Here is the question I'm passing back. Do you use factory default or customized version of "Objects" query?
In the intellisense popups do you see that trigger listed in the object's list? If yes, is it shown with procedure icon and type "procedure"
Thu Oct 08, 2015 12:55 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post Reply with quote
Yes, I use a custom"Objects (MSSQL) + Typed Synonyms" for the Objects query and was already investigating if it could be the cause for this issue. Unfortunately, it might be. Actually, it tricks Objects macro to believe that triggers are procedures so that I could select them from popup. I guess it would work properly if I reverted to the default one but I'd loose the ability to use popups for selecting triggers. Both features are very important to me.

May I request a feature that would introduce another object type for the $OBJECT()$, namely the trigger type? It would allow selecting triggers natively, without the modified Objects query.

Below it is the query:

Code:

if @@version like '%SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%SQL Server 2014%'
or @@version like '%SQL Azure%'

  select
     name, CASE WHEN type = 'SN' THEN '^' +
       CASE CAST(OBJECTPROPERTYEX(object_id,'BaseType') AS VARCHAR)
       WHEN 'AF' THEN 'F'
       WHEN 'FN' THEN 'F'
       WHEN 'FS' THEN 'F'
       WHEN 'FT' THEN 'U'
       WHEN 'IF' THEN 'U'
       WHEN 'IT' THEN 'T'
       WHEN 'P' THEN 'P'
       WHEN 'PC' THEN 'P'
       WHEN 'TR' THEN 'P'
       --WHEN 'TR' THEN 'TR'
       WHEN 'S' THEN 'T'
       WHEN 'SQ' THEN 'I'
       WHEN 'TF' THEN 'U'
       WHEN 'TT' THEN 'K'
       WHEN 'U' THEN 'T'
       WHEN 'V' THEN 'V'
       WHEN 'X' THEN 'P'
       ELSE ' '
       END
     ELSE CASE type WHEN 'TR' THEN 'P' ELSE type END END,
     object_id, create_date, modify_date
  from
     [$DB_NAME$].sys.all_objects
  where
     type in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS', 'TR')
     and (
           schema_id = :SCHEMA_ID
           or (
               1 = :SCHEMA_ID
               and user_name() = 'dbo'
               and schema_id = 4
               and name in ('sysobjects','sysindexes','syscolumns','systypes','syscomments','sysfiles1','syspermissions','sysusers','sysproperties','sysdepends','sysreferences','sysfulltextcatalogs','sysindexkeys','sysforeignkeys','sysmembers','sysprotects','sysfulltextnotify','sysfiles','sysfilegroups')
              )
         )
     and not (name = 'dtproperties')
     and not (name like 'dt%' and type = 'P')

  union all

  SELECT t.NAME + COALESCE(CHAR(0) + t1.name
      + case
      when t1.name in ('varchar', 'char', 'binary', 'varbinary')
        then '(' + case t1.max_length when -1 then 'max' else convert(varchar, t1.max_length) end + ')'
      when t1.name in ('nvarchar', 'nchar')
        then '(' + case t1.max_length when -1 then 'max' else convert(varchar, t1.max_length / 2) end + ')'
      when t1.name in ('decimal', 'numeric')
        then '(' + convert(varchar, t1.[precision]) + ',' + convert(varchar, t1.scale) + ')'
      else ''
      END, ''),
      COALESCE(o.type, 'TD'),
      COALESCE(tt.type_table_object_id, t.user_type_id),
      o.create_date, o.modify_date
  FROM [$DB_NAME$].sys.types t
  LEFT JOIN [$DB_NAME$].sys.types t1 ON t.system_type_id = t1.user_type_id
  LEFT JOIN [$DB_NAME$].sys.table_types tt ON tt.name = t.name
  LEFT JOIN [$DB_NAME$].sys.objects o ON tt.type_table_object_id = o.[object_id]
  WHERE t.is_user_defined = 1 and t.schema_id = :SCHEMA_ID

else
if @@version like '%SQL Server 2005%'

  select
     name, CASE WHEN type = 'SN' THEN '^' +
       CASE CAST(OBJECTPROPERTYEX(object_id,'BaseType') AS VARCHAR)
       WHEN 'AF' THEN 'F'
       WHEN 'FN' THEN 'F'
       WHEN 'FS' THEN 'F'
       WHEN 'FT' THEN 'U'
       WHEN 'IF' THEN 'U'
       WHEN 'IT' THEN 'T'
       WHEN 'P' THEN 'P'
       WHEN 'PC' THEN 'P'
       WHEN 'S' THEN 'T'
       WHEN 'SQ' THEN 'I'
       WHEN 'TF' THEN 'U'
       WHEN 'TT' THEN 'K'
       WHEN 'U' THEN 'T'
       WHEN 'V' THEN 'V'
       WHEN 'X' THEN 'P'
       ELSE ' '
       END
     ELSE type END,
     object_id, create_date, modify_date
  from
     [$DB_NAME$].sys.all_objects
  where
     type in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS', 'TR')
     and (
           schema_id = :SCHEMA_ID
           or (
               1 = :SCHEMA_ID
               and user_name() = 'dbo'
               and schema_id = 4
               and name in ('sysobjects','sysindexes','syscolumns','systypes','syscomments','sysfiles1','syspermissions','sysusers','sysproperties','sysdepends','sysreferences','sysfulltextcatalogs','sysindexkeys','sysforeignkeys','sysmembers','sysprotects','sysfulltextnotify','sysfiles','sysfilegroups')
              )
         )
     and not (name = 'dtproperties')
     and not (name like 'dt%' and type = 'P')

else

  select
     name, xtype, id, crdate, crdate
  from
    [$DB_NAME$].dbo.sysobjects
  where
     xtype in ('U','S','V','P','X','RF','FN','TF','IF')
     and uid = :SCHEMA_ID
     and not (name = 'dtproperties')
     and not (name like 'dt%' and xtype = 'P')

Thu Oct 08, 2015 9:56 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
I'm afraid these 2 things are incompatible. Substituting object type with incompatible type would throw source control interface off, as you can see in the case of triggers.
Thu Oct 08, 2015 9:47 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post Reply with quote
Yes, that's why introducing the object type trigger would come in handy.
Fri Oct 09, 2015 2:06 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post Reply with quote
Okay, I made some experiments with the original DB query for Objects and run into some serious issues which make me believe that you really, really should handle triggers on their own, as a separate object. Here's what I discovered.
With the factory version of the Object DB query:
#1. The code of the trigger is in the file for the table, which if fine, they're related after all.
#2. There's still no separate object for the trigger, which would come in handy in case it's the only thing that changed, and is an arguable decision (see #5 below).
#3. Changing the trigger and committing the changes now properly drops and recreates it instead of failing with DROP PROCEDURE, which was a side-effect of my tinkering with the DB query.

And now the bad ones:
#4. The ability to quickly retrieve trigger code from the editor is lost, since they no longer appear in popups.
#5. Whenever I change the trigger through the repo browser and commit the changes to the database, a disaster happens. The trigger is dropped and recreated properly and since the trigger code is coupled with the table code so is the table (even if it has not been changed), resulting in DATA LOSS. That's a critical error.

So restoring the original DB query only made things worse instead of fixing them.
Fri Oct 09, 2015 2:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
I have reported this to the dev team, we are actively investigating it.
Fri Oct 09, 2015 9:40 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1390

Post Reply with quote
Thank you very much.
Fri Oct 09, 2015 9:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
The is no "intelligent" solution available at this time. The scripts stored in the repository are by definition CREATE scripts, when applied to the database, they are applied as DROP and CREATE for existing objects and CREATE for new. Reconciliation of differences between the scripts and existing database design is almost like a rocket science, it's very unlikely the scripts can be reliably converted in database-commit-time to ALTER commands, there are way to many potential issues and complications. By design, the database source control is mostly a one way script from database to repository, and the other way is mostly for deploying changes to new environments or updating development schemas in continuous integration environments where unit tests of test-data generation projects can be used in conjunction to populate the recreated tables after each run.


We are adding a warning to the release version 7.3 to check for existing tables and display potential-data-loss message before executing the DROP and CREATE scripts for tables.
Sun Oct 11, 2015 8:40 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.