PC World 1997 November/PCWorld_1997-11_cd.bin/software/programy/gratex/REPORTDB.SQL

Text File | 1997-08-12 | 32KB | 939 lines

use patrik
go
CREATE PROCEDURE PatrikDropTable @name varchar(32)
AS
    DECLARE  @cname varchar(32), @tname varchar(32), @s varchar(255)
    DECLARE ahoj insensitive CURSOR FOR
       SELECT OBJECT_NAME(constid), OBJECT_NAME(fkeyid) FROM sysreferences WHERE rkeyid=OBJECT_ID(@name) FOR READ ONLY
    OPEN ahoj
__fetch:
    FETCH NEXT FROM ahoj INTO @cname, @tname
    IF @@FETCH_STATUS!=0 GOTO __end
    SELECT @s='ALTER TABLE '+@tname+' DROP CONSTRAINT '+@cname
    exec (@s)
    GOTO __fetch
__end:
    CLOSE ahoj
    DEALLOCATE ahoj
    SELECT @s='DROP TABLE '+@name
    exec (@s)
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='orderby') EXEC PatrikDropTable 'orderby'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='graph') EXEC PatrikDropTable 'graph'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='keys') EXEC PatrikDropTable 'keys'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='report') EXEC PatrikDropTable 'report'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='object') EXEC PatrikDropTable 'object'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='param') EXEC PatrikDropTable 'param'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='graph_pos') EXEC PatrikDropTable 'graph_pos'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='graph_attribute') EXEC PatrikDropTable 'graph_attribute'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='graph_color') EXEC PatrikDropTable 'graph_color'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='layout') EXEC PatrikDropTable 'layout'
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='attribute') EXEC PatrikDropTable 'attribute'
GO
DROP PROCEDURE PatrikDropTable
PRINT 'Transfering tables ...'
PRINT 'Creating table orderby'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_orderby')>0
   DROP TABLE _orderby
GO
CREATE TABLE orderby
(
   report_id int NOT NULL,
   orderby_id int NOT NULL,
   attribute_id int NULL,
   b_desc bit NOT NULL,
   PRIMARY KEY NONCLUSTERED (report_id, orderby_id)
)
PRINT 'Creating table graph'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_graph')>0
   DROP TABLE _graph
GO
CREATE TABLE graph
(
   report_id int NOT NULL,
   graph_id int NOT NULL,
   name varchar(255) NULL,
   graphtype int NULL,
   c_surx int NULL,
   c_sury int NULL,
   c_surz int NULL,
   c_bk int NULL,
   c_graph int NULL,
   c_palettestart int NULL,
   c_paletteend int NULL,
   c_text int NULL,
   c_podlozka int NULL,
   lwidth int NULL,
   bold_r int NULL,
   dec_dig int NULL,
   pier float NULL,
   transparent float NULL,
   light_intensity float NULL,
   flags int NULL,
   rot_a float NULL,
   rot_b float NULL,
   rot_c float NULL,
   nscolid int NULL,
   c_wall1 int NULL,
   c_wall2 int NULL,
   c_wall3 int NULL,
   bar3d_type int NULL,
   col int NULL,
   lfHeight int NULL,
   lfFlags int NULL,
   lfPitchAndFamily tinyint NULL,
   lfFaceName varchar(32) NULL,
   reserved float NULL,
   glHeight int NULL,
   glFlags int NULL,
   glPitchAndFamily int NULL,
   glFaceName varchar(32) NULL,
   PRIMARY KEY NONCLUSTERED (report_id, graph_id)
)
PRINT 'Creating table keys'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_keys')>0
   DROP TABLE _keys
GO
CREATE TABLE keys
(
   object_id int NOT NULL,
   object_depid int NOT NULL,
   key_id int NOT NULL,
   flags int NOT NULL,
   key1 int NULL,
   key2 int NULL,
   key3 int NULL,
   key4 int NULL,
   key5 int NULL,
   key6 int NULL,
   key7 int NULL,
   key8 int NULL,
   depkey1 int NULL,
   depkey2 int NULL,
   depkey3 int NULL,
   depkey4 int NULL,
   depkey5 int NULL,
   depkey6 int NULL,
   depkey7 int NULL,
   depkey8 int NULL,
   PRIMARY KEY NONCLUSTERED (object_id, object_depid, key_id)
)
PRINT 'Creating table report'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_report')>0
   DROP TABLE _report
GO
CREATE TABLE report
(
   report_id int NOT NULL,
   db varchar(30) NOT NULL,
   name varchar(30) NULL,
   description varchar(255) NULL,
   flags int NOT NULL DEFAULT (0),
   sample_lines int NOT NULL DEFAULT (10),
   rel_flags int NOT NULL,
   rel_x int NOT NULL,
   rel_y int NOT NULL,
   last_changed varchar(32) NULL,
   s_select text NULL,
   rel_scale int NULL,
   s_where text NULL,
   PRIMARY KEY CLUSTERED (report_id),
   UNIQUE NONCLUSTERED (db, name)
)
PRINT 'Creating table object'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_object')>0
   DROP TABLE _object
GO
CREATE TABLE object
(
   object_id int NOT NULL,
   report_id int NOT NULL,
   type int NOT NULL,
   table_name varchar(255) NOT NULL,
   alias_name varchar(30) NULL,
   flags int NOT NULL,
   vx int NOT NULL,
   vy int NOT NULL,
   cx int NOT NULL,
   cy int NOT NULL,
   index_used varchar(32) NULL,
   set_name varchar(30) NULL,
   PRIMARY KEY NONCLUSTERED (object_id)
)
PRINT 'Creating table param'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_param')>0
   DROP TABLE _param
GO
CREATE TABLE param
(
   report_id int NOT NULL,
   set_name varchar(30) NOT NULL,
   name varchar(30) NOT NULL,
   value varchar(30) NOT NULL
)
PRINT 'Creating table graph_pos'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_graph_pos')>0
   DROP TABLE _graph_pos
GO
CREATE TABLE graph_pos
(
   report_id int NULL,
   graph_id int NULL,
   ord int NULL,
   x int NULL,
   y int NULL
)
PRINT 'Creating table graph_attribute'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_graph_attribute')>0
   DROP TABLE _graph_attribute
GO
CREATE TABLE graph_attribute
(
   report_id int NOT NULL,
   graph_id int NOT NULL,
   colid int NOT NULL,
   ord int NOT NULL
)
PRINT 'Creating table graph_color'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_graph_color')>0
   DROP TABLE _graph_color
GO
CREATE TABLE graph_color
(
   report_id int NOT NULL,
   graph_id int NOT NULL,
   ord int NOT NULL,
   color int NOT NULL,
   PRIMARY KEY NONCLUSTERED (report_id, graph_id, ord)
)
PRINT 'Creating table layout'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_layout')>0
   DROP TABLE _layout
GO
CREATE TABLE layout
(
   report_id int NOT NULL,
   flags int NULL,
   m_head int NULL,
   m_top int NULL,
   m_bottom int NULL,
   m_foot int NULL,
   m_left int NULL,
   m_right int NULL,
   t_head text NULL,
   t_foot text NULL,
   hHeight int NULL,
   hFlags int NULL,
   hPitchAndFamily tinyint NULL,
   hFaceName varchar(32) NULL,
   rHeight int NULL,
   rFlags int NULL,
   rPitchAndFamily tinyint NULL,
   rFaceName varchar(32) NULL,
   h0Height int NULL,
   h0Flags int NULL,
   h0PitchAndFamily tinyint NULL,
   h0FaceName varchar(32) NULL,
   h1Height int NULL,
   h1Flags int NULL,
   h1PitchAndFamily tinyint NULL,
   h1FaceName varchar(32) NULL,
   h2Height int NULL,
   h2Flags int NULL,
   h2PitchAndFamily tinyint NULL,
   h2FaceName varchar(32) NULL,
   f0Height int NULL,
   f0Flags int NULL,
   f0PitchAndFamily tinyint NULL,
   f0FaceName varchar(32) NULL,
   f1Height int NULL,
   f1Flags int NULL,
   f1PitchAndFamily tinyint NULL,
   f1FaceName varchar(32) NULL,
   f2Height int NULL,
   f2Flags int NULL,
   f2PitchAndFamily tinyint NULL,
   f2FaceName varchar(32) NULL,
   PRIMARY KEY NONCLUSTERED (report_id)
)
PRINT 'Creating table attribute'
GO
IF (SELECT COUNT(*) FROM sysobjects WHERE name='_attribute')>0
   DROP TABLE _attribute
GO
CREATE TABLE attribute
(
   report_id int NOT NULL,
   attribute_id int NOT NULL,
   object_id int NULL,
   obj_attr_name varchar(255) NOT NULL,
   name varchar(255) NULL,
   is_null varchar(255) NULL,
   agregate int NOT NULL,
   align int NOT NULL,
   flags int NOT NULL,
   texcp int NOT NULL,
   texcp_val int NULL,
   bexcp int NOT NULL,
   bexcp_val int NULL,
   colid int NULL,
   PRIMARY KEY NONCLUSTERED (report_id, attribute_id)
)
GO
PRINT 'Transfering indexes ...'
GO
CREATE INDEX repfk ON object(report_id) WITH FILLFACTOR=100 ON 'default'
GO
CREATE INDEX ref1 ON param(report_id) WITH FILLFACTOR=100 ON 'default'
GO
CREATE INDEX ref1 ON graph_pos(report_id,graph_id) WITH FILLFACTOR=100 ON 'default'
CREATE INDEX ref2 ON graph_pos(ord) WITH FILLFACTOR=100 ON 'default'
GO
CREATE INDEX attr_ref ON graph_attribute(colid) WITH FILLFACTOR=100 ON 'default'
CREATE INDEX graph_ref ON graph_attribute(graph_id) WITH FILLFACTOR=100 ON 'default'
CREATE INDEX report_ref ON graph_attribute(report_id) WITH FILLFACTOR=100 ON 'default'
GO
CREATE INDEX graph_ref ON graph_color(graph_id) WITH FILLFACTOR=100 ON 'default'
CREATE INDEX report_ref ON graph_color(report_id) WITH FILLFACTOR=100 ON 'default'
GO
CREATE INDEX objfk ON attribute(object_id) WITH FILLFACTOR=100 ON 'default'
CREATE INDEX repfk ON attribute(report_id) WITH FILLFACTOR=100 ON 'default'
GO
PRINT 'Transfering triggers ...'
GO
PRINT 'Trans. graph_attribute_ITrig'
GO
CREATE TRIGGER graph_attribute_ITrig ON graph_attribute FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM graph r, inserted i WHERE i.report_id=r.report_id AND i.graph_id=r.graph_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''graph_attribute'' poruÜuje RI s ''graph'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. graph_attribute_UTrig'
GO
CREATE TRIGGER graph_attribute_UTrig ON graph_attribute FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id) OR UPDATE(graph_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM graph r, inserted i WHERE i.report_id=r.report_id AND i.graph_id=r.graph_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''graph_attribute'' poruÜuj· RI s ''graph'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. graph_color_ITrig'
GO
CREATE TRIGGER graph_color_ITrig ON graph_color FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM graph r, inserted i WHERE i.report_id=r.report_id AND i.graph_id=r.graph_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''graph_color'' poruÜuje RI s ''graph'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. graph_color_UTrig'
GO
CREATE TRIGGER graph_color_UTrig ON graph_color FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id) OR UPDATE(graph_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM graph r, inserted i WHERE i.report_id=r.report_id AND i.graph_id=r.graph_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''graph_color'' poruÜuj· RI s ''graph'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. report_DTrig'
GO
CREATE TRIGGER report_DTrig ON report FOR DELETE
AS
/*PatrikB*/
DELETE orderby FROM orderby, deleted WHERE deleted.report_id=orderby.report_id
DELETE object FROM object, deleted WHERE deleted.report_id=object.report_id
DELETE layout FROM layout, deleted WHERE deleted.report_id=layout.report_id
DELETE attribute FROM attribute, deleted WHERE deleted.report_id=attribute.report_id
DELETE param FROM param, deleted WHERE deleted.report_id=param.report_id
/*PatrikE*/
GO
PRINT 'Trans. report_UTrig'
GO
CREATE TRIGGER report_UTrig ON report FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id)
 IF @rc<2
  UPDATE orderby SET orderby.report_id=inserted.report_id
  FROM orderby, deleted, inserted
  WHERE orderby.report_id=deleted.report_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''report'' '
   RETURN
END
IF UPDATE(report_id)
 IF @rc<2
  UPDATE object SET object.report_id=inserted.report_id
  FROM object, deleted, inserted
  WHERE object.report_id=deleted.report_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''report'' '
   RETURN
END
IF UPDATE(report_id)
 IF @rc<2
  UPDATE layout SET layout.report_id=inserted.report_id
  FROM layout, deleted, inserted
  WHERE layout.report_id=deleted.report_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''report'' '
   RETURN
END
IF UPDATE(report_id)
 IF @rc<2
  UPDATE attribute SET attribute.report_id=inserted.report_id
  FROM attribute, deleted, inserted
  WHERE attribute.report_id=deleted.report_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''report'' '
   RETURN
END
IF UPDATE(report_id)
 IF @rc<2
  UPDATE param SET param.report_id=inserted.report_id
  FROM param, deleted, inserted
  WHERE param.report_id=deleted.report_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''report'' '
   RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. orderby_ITrig'
GO
CREATE TRIGGER orderby_ITrig ON orderby FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''orderby'' poruÜuje RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. orderby_UTrig'
GO
CREATE TRIGGER orderby_UTrig ON orderby FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''orderby'' poruÜuj· RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. graph_DTrig'
GO
CREATE TRIGGER graph_DTrig ON graph FOR DELETE
AS
/*PatrikB*/
DELETE graph_attribute FROM graph_attribute, deleted WHERE deleted.report_id=graph_attribute.report_id AND deleted.graph_id=graph_attribute.graph_id
DELETE graph_color FROM graph_color, deleted WHERE deleted.report_id=graph_color.report_id AND deleted.graph_id=graph_color.graph_id
/*PatrikE*/
GO
PRINT 'Trans. graph_UTrig'
GO
CREATE TRIGGER graph_UTrig ON graph FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id) OR UPDATE(graph_id)
 IF @rc<2
  UPDATE graph_attribute SET graph_attribute.report_id=inserted.report_id, graph_attribute.graph_id=inserted.graph_id
  FROM graph_attribute, deleted, inserted
  WHERE graph_attribute.report_id=deleted.report_id AND graph_attribute.graph_id=deleted.graph_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''graph'' '
   RETURN
END
IF UPDATE(report_id) OR UPDATE(graph_id)
 IF @rc<2
  UPDATE graph_color SET graph_color.report_id=inserted.report_id, graph_color.graph_id=inserted.graph_id
  FROM graph_color, deleted, inserted
  WHERE graph_color.report_id=deleted.report_id AND graph_color.graph_id=deleted.graph_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''graph'' '
   RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. keys_ITrig'
GO
CREATE TRIGGER keys_ITrig ON keys FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM object r, inserted i WHERE i.object_id=r.object_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''keys'' poruÜuje RI s ''object'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. keys_UTrig'
GO
CREATE TRIGGER keys_UTrig ON keys FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(object_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM object r, inserted i WHERE i.object_id=r.object_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''keys'' poruÜuj· RI s ''object'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. object_DTrig'
GO
CREATE TRIGGER object_DTrig ON object FOR DELETE
AS
/*PatrikB*/
DELETE keys FROM keys, deleted WHERE deleted.object_id=keys.object_id
/*PatrikE*/
GO
PRINT 'Trans. object_ITrig'
GO
CREATE TRIGGER object_ITrig ON object FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''object'' poruÜuje RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. object_UTrig'
GO
CREATE TRIGGER object_UTrig ON object FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''object'' poruÜuj· RI s ''report'' '
  RETURN
END
IF UPDATE(object_id)
 IF @rc<2
  UPDATE keys SET keys.object_id=inserted.object_id
  FROM keys, deleted, inserted
  WHERE keys.object_id=deleted.object_id
 ELSE BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44440 'Viacriadkovß kaskßdovß zmena v ''object'' '
   RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. layout_ITrig'
GO
CREATE TRIGGER layout_ITrig ON layout FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''layout'' poruÜuje RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. layout_UTrig'
GO
CREATE TRIGGER layout_UTrig ON layout FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''layout'' poruÜuj· RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. attribute_ITrig'
GO
CREATE TRIGGER attribute_ITrig ON attribute FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''attribute'' poruÜuje RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. attribute_UTrig'
GO
CREATE TRIGGER attribute_UTrig ON attribute FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''attribute'' poruÜuj· RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. param_ITrig'
GO
CREATE TRIGGER param_ITrig ON param FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''param'' poruÜuje RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. param_UTrig'
GO
CREATE TRIGGER param_UTrig ON param FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM report r, inserted i WHERE i.report_id=r.report_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''param'' poruÜuj· RI s ''report'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. graph_pos_ITrig'
GO
CREATE TRIGGER graph_pos_ITrig ON graph_pos FOR INSERT
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF @rc!=(SELECT DISTINCT COUNT(*) FROM graph r, inserted i WHERE i.report_id=r.report_id AND i.graph_id=r.graph_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44447 'Vlo₧enie do tabu╛ky ''graph_pos'' poruÜuje RI s ''graph'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Trans. graph_pos_UTrig'
GO
CREATE TRIGGER graph_pos_UTrig ON graph_pos FOR UPDATE
AS
/*PatrikB*/
DECLARE @rc int
SELECT @rc=@@rowcount
IF UPDATE(report_id) OR UPDATE(graph_id)
IF @rc!=(SELECT DISTINCT COUNT(*) FROM graph r, inserted i WHERE i.report_id=r.report_id AND i.graph_id=r.graph_id)
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR 44446 'Zmeny v detskej tabu╛ke ''graph_pos'' poruÜuj· RI s ''graph'' '
  RETURN
END
/*PatrikE*/
GO
PRINT 'Transfering keys ...'

GO
sp_primarykey orderby, report_id, orderby_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='orderby'
INSERT patrik..rel VALUES('$Sinda', 'orderby', 288, 276, 112, 84)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='orderby'
GO
sp_primarykey graph, report_id, graph_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='graph'
INSERT patrik..rel VALUES('$Sinda', 'graph', 726, 32, 112, 548)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='graph'
GO
sp_primarykey keys, object_id, object_depid, key_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='keys'
INSERT patrik..rel VALUES('$Sinda', 'keys', 72, 257, 112, 340)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='keys'
GO
sp_primarykey report, report_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='report'
INSERT patrik..rel VALUES('$Sinda', 'report', 490, 40, 112, 196)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='report'
GO
sp_primarykey object, object_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='object'
INSERT patrik..rel VALUES('$Sinda', 'object', 75, 54, 112, 196)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='object'
GO
sp_primarykey param, report_id, set_name
DELETE patrik..rel WHERE db='$Sinda' AND tbl='param'
INSERT patrik..rel VALUES('$Sinda', 'param', 420, 25, 0, 0)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='param'
DELETE patrik..rel WHERE db='$Sinda' AND tbl='graph_pos'
INSERT patrik..rel VALUES('$Sinda', 'graph_pos', 30, 545, 0, 0)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='graph_pos'
DELETE patrik..rel WHERE db='$Sinda' AND tbl='graph_attribute'
INSERT patrik..rel VALUES('$Sinda', 'graph_attribute', 976, 110, 112, 84)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='graph_attribute'
GO
sp_primarykey graph_color, report_id, graph_id, ord
DELETE patrik..rel WHERE db='$Sinda' AND tbl='graph_color'
INSERT patrik..rel VALUES('$Sinda', 'graph_color', 991, 20, 112, 84)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='graph_color'
GO
sp_primarykey layout, report_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='layout'
INSERT patrik..rel VALUES('$Sinda', 'layout', 480, 243, 119, 692)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='layout'
GO
sp_primarykey attribute, report_id, attribute_id
DELETE patrik..rel WHERE db='$Sinda' AND tbl='attribute'
INSERT patrik..rel VALUES('$Sinda', 'attribute', 264, 12, 112, 244)
DELETE patrik..patriklog WHERE db='$Sinda' AND tbl='attribute'
GO
sp_foreignkey orderby, report, report_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='orderby' AND depid='report'
INSERT patrik..keysadd VALUES('$Sinda', 'orderby', 'report', 30, '#')
GO
sp_foreignkey orderby, attribute, report_id, attribute_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='orderby' AND depid='attribute'
INSERT patrik..keysadd VALUES('$Sinda', 'orderby', 'attribute', 0, '#')
GO
sp_foreignkey graph, report, report_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='graph' AND depid='report'
INSERT patrik..keysadd VALUES('$Sinda', 'graph', 'report', 0, '#')
GO
sp_foreignkey keys, object, object_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='keys' AND depid='object'
INSERT patrik..keysadd VALUES('$Sinda', 'keys', 'object', 30, '#')
GO
sp_foreignkey object, report, report_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='object' AND depid='report'
INSERT patrik..keysadd VALUES('$Sinda', 'object', 'report', 30, '#')
GO
sp_foreignkey param, report, report_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='param' AND depid='report'
INSERT patrik..keysadd VALUES('$Sinda', 'param', 'report', 0, '')
GO
sp_foreignkey graph_pos, graph, report_id, graph_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='graph_pos' AND depid='graph'
INSERT patrik..keysadd VALUES('$Sinda', 'graph_pos', 'graph', 0, '')
GO
sp_foreignkey graph_attribute, graph, report_id, graph_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='graph_attribute' AND depid='graph'
INSERT patrik..keysadd VALUES('$Sinda', 'graph_attribute', 'graph', 30, 'ffff#rrr')
GO
sp_foreignkey graph_color, graph, report_id, graph_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='graph_color' AND depid='graph'
INSERT patrik..keysadd VALUES('$Sinda', 'graph_color', 'graph', 30, '#')
GO
sp_foreignkey layout, report, report_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='layout' AND depid='report'
INSERT patrik..keysadd VALUES('$Sinda', 'layout', 'report', 30, '#')
GO
sp_foreignkey attribute, report, report_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='attribute' AND depid='report'
INSERT patrik..keysadd VALUES('$Sinda', 'attribute', 'report', 30, '#')
GO
sp_foreignkey attribute, object, object_id
DELETE patrik..keysadd WHERE db='$Sinda' AND id='attribute' AND depid='object'
INSERT patrik..keysadd VALUES('$Sinda', 'attribute', 'object', 0, '#')
GO
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='orderby'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='graph'
INSERT patrik..notes VALUES('$Sinda', 'graph', 'psu', '', 'Graph table')
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='keys'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='report'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='object'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='param'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='graph_pos'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='graph_attribute'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='graph_color'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='layout'
GO
DELETE patrik..notes WHERE db='$Sinda' AND tbl='attribute'
GO
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='attribute'
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 1, 'FK report')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 2, 'PK')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 3, 'parent object, may be NULL')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 4, 'FK name of object''s attribute, if object_id is null then custom field')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 5, 'PK name of atribute')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 7, 'agregate function used')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 10, 'top exception')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 11, 'top exception value')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 12, 'bottom exception')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 13, 'bottom exception value')
INSERT patrik..conspects VALUES('$Sinda', 'attribute', 14, 'col->id; join to graph_attribute')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='graph'
INSERT patrik..conspects VALUES('$Sinda', 'graph', 3, 'graph name')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 4, 'type of graph')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 5, 'surxcolor')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 6, 'surycolor')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 7, 'surzcolor')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 8, 'bk_color')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 9, 'graphcolor')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 10, 'palettestart')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 11, 'paletteend')
GO
INSERT patrik..conspects VALUES('$Sinda', 'graph', 12, 'textcolor')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 13, 'podlozkacolor')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 14, 'lwidth')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 21, 'rot.a')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 22, 'rot.b')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 23, 'rot.c')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 24, 'number of string graph attributes')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 25, 'stena1')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 26, 'stena2')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 27, 'stena3')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 29, 'stlpec')
INSERT patrik..conspects VALUES('$Sinda', 'graph', 31, 'und, ital, bold, strikeout')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='graph_color'
INSERT patrik..conspects VALUES('$Sinda', 'graph_color', 1, 'fk')
INSERT patrik..conspects VALUES('$Sinda', 'graph_color', 2, 'fk')
INSERT patrik..conspects VALUES('$Sinda', 'graph_color', 3, 'order of color')
INSERT patrik..conspects VALUES('$Sinda', 'graph_color', 4, 'color value (RGB)')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='keys'
INSERT patrik..conspects VALUES('$Sinda', 'keys', 1, 'FK Object')
INSERT patrik..conspects VALUES('$Sinda', 'keys', 2, 'FK Object')
INSERT patrik..conspects VALUES('$Sinda', 'keys', 3, 'contraint id')
INSERT patrik..conspects VALUES('$Sinda', 'keys', 4, 'join type, virtual relation')
GO
INSERT patrik..conspects VALUES('$Sinda', 'keys', 5, 'one based indexes to object columns')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='layout'
INSERT patrik..conspects VALUES('$Sinda', 'layout', 1, 'parent report')
INSERT patrik..conspects VALUES('$Sinda', 'layout', 2, 'preview flags')
INSERT patrik..conspects VALUES('$Sinda', 'layout', 3, 'margins')
INSERT patrik..conspects VALUES('$Sinda', 'layout', 9, 'header text')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='object'
INSERT patrik..conspects VALUES('$Sinda', 'object', 3, '1=TABLE, 2=VIEW, 3=REPORT')
INSERT patrik..conspects VALUES('$Sinda', 'object', 4, 'name of table, view or report')
INSERT patrik..conspects VALUES('$Sinda', 'object', 11, 'optimizer hint index')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='orderby'
INSERT patrik..conspects VALUES('$Sinda', 'orderby', 1, 'FK report')
INSERT patrik..conspects VALUES('$Sinda', 'orderby', 2, 'order')
INSERT patrik..conspects VALUES('$Sinda', 'orderby', 3, 'FK')
INSERT patrik..conspects VALUES('$Sinda', 'orderby', 4, 'descent?')
DELETE patrik..conspects WHERE db='$Sinda' AND tbl='report'
INSERT patrik..conspects VALUES('$Sinda', 'report', 6, 'number of lines in report sample data')
INSERT patrik..conspects VALUES('$Sinda', 'report', 7, 'relational model flags')
INSERT patrik..conspects VALUES('$Sinda', 'report', 8, 'position of view viewed in RM')
INSERT patrik..conspects VALUES('$Sinda', 'report', 10, 'NT username who have last changed report')
INSERT patrik..conspects VALUES('$Sinda', 'report', 11, 'column name free select')
GO
use patrik
GO
CREATE TABLE md_def (db varchar(30) NOT NULL, feature varchar(255) NOT NULL, width int NULL, ord int NOT NULL, PRIMARY KEY (db, ord))
CREATE INDEX ref1 ON md_def(db)

CREATE TABLE md_data (db varchar(30) NOT NULL, tbl varchar(30) NOT NULL, colid int NOT NULL, data text NOT NULL, PRIMARY KEY (db, tbl, colid))
CREATE INDEX ref1 ON md_data(db)
CREATE INDEX ref2 ON md_data(db, tbl)

CREATE TABLE ptree (db varchar(30) NOT NULL, parent varchar(30) NOT NULL, child varchar(30) NULL, ch_type int NOT NULL) -- obj, subdir, dirinf
CREATE INDEX ref1 ON ptree(db, parent)
CREATE INDEX ref2 ON ptree(db, child, ch_type)

CREATE TABLE tabexpand (usr varchar(30) NOT NULL, db varchar(30) NOT NULL, tbl varchar(30) NOT NULL, op int NOT NULL, colid int NULL, arg varchar(255) NULL)
CREATE INDEX ref1 ON tabexpand (db, tbl)
CREATE INDEX ref2 ON tabexpand (usr, db, tbl)