SqlServer updatable views for user base data access encapsulation

Recently there was a disscussion at the georeference.org on how to provide a user / user group SQL SERVER data access encapsulation in manifold (and pretty much in any other client) so different clients modify the very same data source but do not have the access to other users data.

The answer was - the updatable views:

https://msdn.microsoft.com/en-us/library/ms180800(v=sql.110).aspx#Restrictions

https://msdn.microsoft.com/en-us/library/ms187956(v=sql.110).aspx

When a view is created with the 'WITH VIEW_METADATA' then it becomes update able straight away but there are some restrictions (see links above). The other option is to use 'INSTEAD OF' triggers. It is way more flexible, though one needs to create the triggers of course ;)

In manifold, due to the way it inserts the data - first goes a geom with nulls for all the fields, then goes the data collected through the 'instant data' window or the direct object / table edit - objects are not populated with the data needed to filter the dataset and therefore are not editable / delete able until the data is fixed on the DB side and a drawing / table is refreshed. This pretty much is a show stopper with the 'WITH VIEW_METADATA' approach .

Luckily there are the 'INSTEAD OF' triggers. The most important is  the insert trigger - this is the place the fields used for view filtering are populated. One can then opt for a full set of the 'INSTEAD OF' triggers or use a mixed approach - triggers with the view metadata.

A longer version with a complete set of the 'INSTEAD OF' triggers:

--cleanup, cleanup, everybody cleanup
-------------------------------------
IF OBJECT_ID('dbo.vwTest_TypeX', 'V') IS NOT NULL
	DROP VIEW [dbo].[vwTest_TypeX];
GO

IF OBJECT_ID('dbo.vwTest_TypeY', 'V') IS NOT NULL
	DROP VIEW [dbo].[vwTest_TypeY];
GO

IF OBJECT_ID('dbo.tblTest', 'U') IS NOT NULL
	DROP TABLE [dbo].[tblTest];
GO
-------------------------------------


--create test table
-------------------------------------
CREATE TABLE [dbo].[tblTest](
	[OID] [int] IDENTITY(1,1) NOT NULL,
	[Version] [int] NULL,
	[Type] [nvarchar](1) NULL, --Type is used to handle user / user group data encapsulation
	[Geometry] [geometry] NULL,
PRIMARY KEY CLUSTERED 
(
	[OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
-------------------------------------


--views that will be updatable through the INSTEAD OF triggers
-------------------------------------

--View for Team X
CREATE VIEW [dbo].[vwTest_TypeX] as
SELECT
	[OID],
	[Version],
	--Do not select [Type]! Type is used to provide user / user group data encapsulation and should
	--not be handled by the users but rather automaticaly!
	[Geometry]
FROM
	[dbo].[tblTest]

	--Note:
	--can do joins, aggregates and such

WHERE
	[Type] = 'X';

GO

--View for Team Y
CREATE VIEW [dbo].[vwTest_TypeY] as
SELECT
	[OID],
	[Version],
	--Do not select [Type]! Type is used to provide user / user group data encapsulation and should
	--not be handled by the users but rather automaticaly!
	[Geometry]
FROM
	[dbo].[tblTest]

	--Note:
	--can do joins, aggregates and such

WHERE
	[Type] = 'Y';

GO
-------------------------------------


--create INSTEAD OF triggers
-------------------------------------

--team X
-------------------------------------

--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeX_Insert]
ON [dbo].[vwTest_TypeX]
INSTEAD OF INSERT AS
BEGIN
	--Note:
	--need to do an insert to the source table not the view, as the vie is supposed to hide the
	--[Type] column from users;
	--if this was not the case could interact with the view directly as in update / delete triggers
	INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
	SELECT
		[Version],
		'X', --need to apply the type or other criteria used by the view to filter out the
		     --subset of the data
		[Geometry]
	FROM
		INSERTED; 

	--Note:
	--can modify other tables and such

	IF @@ERROR<>0
		RAISERROR('Failed on inserting into [vwTest_TypeX].', 16, 1);
END

GO

--insetad of delete
CREATE TRIGGER [dbo].[vwTest_TypeX_Delete]
ON [dbo].[vwTest_TypeX]
INSTEAD OF DELETE AS
BEGIN
	DELETE FROM 
		[dbo].[vwTest_TypeX] 
	WHERE
		[OID] in (SELECT [OID] FROM DELETED);
       
	--Note:
	--can modify other tables and such

    IF @@ERROR<>0
        RAISERROR('Failed on deleting from [vwTest_TypeX].', 16, 1);
END
GO

--instead of update
CREATE TRIGGER [dbo].[vwTest_TypeX_Update]
ON [dbo].[vwTest_TypeX]
INSTEAD OF UPDATE AS
BEGIN
    IF UPDATE([OID])
            RAISERROR('Cannot update [OID] on [vwTest_TypeX].', 16, 1);
      
	UPDATE
		[dbo].[vwTest_TypeX]
	SET
		[Version] = ins.[Version],
		[Geometry] = ins.[Geometry]
	FROM
		INSERTED ins join [dbo].[vwTest_TypeX] tbl on ins.OID = tbl.[OID] 
      
	--Note:
	--can modify other tables and such

	IF @@ERROR<>0
		RAISERROR('Failed on updating [vwTest_TypeX].', 16, 1);
END
GO
-------------------------------------

--team Y
-------------------------------------
--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeY_Insert]
ON [dbo].[vwTest_TypeY]
INSTEAD OF INSERT AS
BEGIN
	--Note:
	--need to do an insert to the source table not the view, as the vie is supposed to hide the [Type] column from users;
	--if this was not the case could interact with the view directly as in update / delete triggers
	INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
	SELECT
		[Version],
		'Y', --need to apply the type or other criteria used by the view to filter out the
		     --subset of the data
		[Geometry]
	FROM
		INSERTED; 

	--Note:
	--can modify other tables and such

	IF @@ERROR<>0
		RAISERROR('Failed on inserting into [vwTest_TypeY].', 16, 1);
END

GO

--insetad of delete
CREATE TRIGGER [dbo].[vwTest_TypeY_Delete]
ON [dbo].[vwTest_TypeY]
INSTEAD OF DELETE AS
BEGIN
	DELETE FROM 
		[dbo].[vwTest_TypeY] 
	WHERE
		[OID] in (SELECT [OID] FROM DELETED);
       
	--Note:
	--can modify other tables and such

    IF @@ERROR<>0
        RAISERROR('Failed on deleting from [vwTest_TypeY].', 16, 1);
END
GO

--instead of update
CREATE TRIGGER [dbo].[vwTest_TypeY_Update]
ON [dbo].[vwTest_TypeY]
INSTEAD OF UPDATE AS
BEGIN
    IF UPDATE([OID])
        RAISERROR('Cannot update [OID] on [vwTest_TypeY].', 16, 1);
      
	UPDATE
		[dbo].[vwTest_TypeY]
	SET
		[Version] = ins.[Version],
		[Geometry] = ins.[Geometry]
	FROM
		INSERTED ins join [dbo].[vwTest_TypeY] tbl on ins.OID = tbl.[OID] 
      
	--Note:
	--can modify other tables and such

	IF @@ERROR<>0
		RAISERROR('Failed on updating [vwTest_TypeY].', 16, 1);
END
GO
-------------------------------------


And a shorter version that mixes the 'WITH VIEW_METADATA' and only the 'INSTEAD OF INSERT' trigger

--cleanup, cleanup, everybody cleanup
-------------------------------------
IF OBJECT_ID('dbo.vwTest_TypeX', 'V') IS NOT NULL
	DROP VIEW [dbo].[vwTest_TypeX];
GO

IF OBJECT_ID('dbo.vwTest_TypeY', 'V') IS NOT NULL
	DROP VIEW [dbo].[vwTest_TypeY];
GO

IF OBJECT_ID('dbo.tblTest', 'U') IS NOT NULL
	DROP TABLE [dbo].[tblTest];
GO
-------------------------------------


--create test table
-------------------------------------
CREATE TABLE [dbo].[tblTest](
	[OID] [int] IDENTITY(1,1) NOT NULL,
	[Version] [int] NULL,
	[Type] [nvarchar](1) NULL, --Type is used to handle user / user group data encapsulation
	[Geometry] [geometry] NULL,
PRIMARY KEY CLUSTERED 
(
	[OID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
-------------------------------------


--views that will be updatable through the INSTEAD OF triggers
-------------------------------------

--View for Team X
CREATE VIEW [dbo].[vwTest_TypeX]
WITH VIEW_METADATA
AS
SELECT
	[OID],
	[Version],
	--Do not select [Type]! Type is used to provide user / user group data encapsulation and should not be handled by the users but rather automaticaly!
	[Geometry]
FROM
	[dbo].[tblTest]

	--Note:
	--can do joins, aggregates and such

WHERE
	[Type] = 'X';

GO

--View for Team Y
CREATE VIEW [dbo].[vwTest_TypeY]
WITH VIEW_METADATA
AS
SELECT
	[OID],
	[Version],
	--Do not select [Type]! Type is used to provide user / user group data encapsulation and should not be handled by the users but rather automaticaly!
	[Geometry]
FROM
	[dbo].[tblTest]

	--Note:
	--can do joins, aggregates and such

WHERE
	[Type] = 'Y';

GO
-------------------------------------


--create INSTEAD OF triggers
-------------------------------------

--team X
-------------------------------------

--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeX_Insert]
ON [dbo].[vwTest_TypeX]
INSTEAD OF INSERT AS
BEGIN
	--Note:
	--need to do an insert to the source table not the view, as the vie is supposed to hide the [Type] column from users;
	--if this was not the case could interact with the view directly as in update / delete triggers
	INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
	SELECT
		[Version],
		'X', --need to apply the type or other criteria used by the view to filter out the subset of the data
		[Geometry]
	FROM
		INSERTED; 

	--Note:
	--can modify other tables and such

	IF @@ERROR<>0
		RAISERROR('Failed on inserting into [vwTest_TypeX].', 16, 1);
END

GO
-------------------------------------

--team Y
-------------------------------------
--instead of insert
CREATE TRIGGER [dbo].[vwTest_TypeY_Insert]
ON [dbo].[vwTest_TypeY]
INSTEAD OF INSERT AS
BEGIN
	--Note:
	--need to do an insert to the source table not the view, as the vie is supposed to hide the [Type] column from users;
	--if this was not the case could interact with the view directly as in update / delete triggers
	INSERT INTO [dbo].[tblTest] ([Version], [Type], [Geometry])
	SELECT
		[Version],
		'Y', --need to apply the type or other criteria used by the view to filter out the subset of the data
		[Geometry]
	FROM
		INSERTED; 

	--Note:
	--can modify other tables and such

	IF @@ERROR<>0
		RAISERROR('Failed on inserting into [vwTest_TypeY].', 16, 1);
END

GO
-------------------------------------

ExtJs 5 & 'strict mode'

There are some good discussions on why one should or should not use the strict mode when coding in JS. I am not going to elaborate on that but rather focus on what to do to write an ExtJs class / app in strict mode.

Before going further, a bit of reading on the strict mode:

ExtJs implements its own class system and basically does not support strict mode (here, here). This is the case with ExtJs 5 too so far. But luckily it will not stop us from creating an ExtJs class with strict mode enabled.

So let's have a look at an ExtJs class with strict mode enabled class wide:

(function(){
    //Make sure strict mode is on
    'use strict';

    Ext.define('MyApp.Class1', {

        daNumber: null,

        constructor: function(config){
            //do some work
            this.daNumber = 666;
        },

        gimmeDaNumber: function(){
            return this.daNumber;
        }
    });
}());

So far so good, the class behaves as expected, so let's extend it:

More...

Batch resave CorelDRAW files to a lower version

A simple VBA script for batch dumping CorelDRAW files to a lower version.

Sub Resave()

    'remember to add a trailing '\', for example c:\my\path\
    Dim fld As String
    fld = "c:\my\path\"
    
    'make the out fld different than the main fld if needed
    'files are saved with a new name, so original ones will not be overwritten
    'remember to add a trailing '\', for example c:\my\path\
    Dim outFld As String
    outFld = "c:\my\path\out\"
    
    If Len(dir(outFld, vbDirectory)) = 0 Then
       MkDir outFld
    End If
    
    Dim file As String
    
    Dim sopts As StructSaveAsOptions
    Set sopts = CreateStructSaveAsOptions
    With sopts
        'looks like vba is happy to save down to v1...
        'x7 is not happy to reopen them though and the lowest version it is happy with is v12
        'in the gui the lowest save as version is 11 though
        .Version = cdrVersion12
        .Overwrite = True
        .EmbedVBAProject = True
        .Filter = cdrCDR
        .IncludeCMXData = False
        .Range = cdrAllPages
        .EmbedICCProfile = True
        .KeepAppearance = True
    End With
    
    file = dir(fld & "*.cdr")
    
    Do While file <> ""
        Dim doc As Document
        Set doc = OpenDocument(fld & file)
        doc.SaveAs outFld & Replace(file, ".cdr", "_v" & sopts.Version & ".cdr"), sopts
        doc.Close
        file = dir()
    Loop
    
End Sub

GlobalMapper - batch reproject and export geotif to ecw

A simple GlobalMapper script that iterates through all the tiff files in a specified directory, and exports them to ecw in EPSG 2180, 3857 and 4326.

//Exports all the tif files found in the source dir to ecw.
//Reprojects the data to 2180, 3857 and 4326 and the exports it to the appropriate folders

GLOBAL_MAPPER_SCRIPT VERSION=1.00

//Define in / out folders
DEFINE_VAR NAME=source_dir VALUE="C:\here\goes\input\path"
DEFINE_VAR NAME=target_dir VALUE="C:\here\goes\output\path"

//start the loop to iterate through all the files
DIR_LOOP_START DIRECTORY="%source_dir%" FILENAME_MASKS="*.tif" RECURSE_DIR=NO
	//Import the file
	IMPORT FILENAME="%FNAME_W_DIR%"
	
	//assign projection 4326
	LOAD_PROJECTION PROJ=4326
	EXPORT_RASTER FILENAME="%target_dir%\4326\%FNAME_WO_EXT%.ecw" TYPE=ECW BG_TRANSPARENT=YES FORCE_SQUARE_PIXELS=YES
		
	//assign projection 2180
	//Note: gm seems to not properly load 2180, so reading the proj def from a file
	LOAD_PROJECTION FILENAME="%source_dir%\2180.prj"
	EXPORT_RASTER FILENAME="%target_dir%\2180\%FNAME_WO_EXT%.ecw" TYPE=ECW BG_TRANSPARENT=YES FORCE_SQUARE_PIXELS=YES
	
	//assign projection 3857
	//Note: gm seems to not properly load neither 3857 not 900913, so reading the proj def from a file
	LOAD_PROJECTION FILENAME="%source_dir%\3857.prj"
	EXPORT_RASTER FILENAME="%target_dir%\3857\%FNAME_WO_EXT%.ecw" TYPE=ECW BG_TRANSPARENT=YES FORCE_SQUARE_PIXELS=YES
	
	//Unload the loaded data
	UNLOAD_ALL

// End the loop
DIR_LOOP_END