su.dbms.sql

 
 - SU.DBMS.SQL ------------------------------------------------------------------
 From : Akzhan Abdulin                       2:5030/217     17 Feb 2001  15:42:47
 To : All
 Subject : деревья
 -------------------------------------------------------------------------------- 
 

 В пpинципе, я пользуюсь подходом Ильи Звягина и Анатолия Тенцеpа -
 дополнительная таблица.
 
 Я лишь поделюсь фактическими скpиптами:
 
 Это основа:
 
 === Cut ===
 -- здесь часть FOREIGN KEY CONSTRAINT's на самом деле используют DRI
 
 -- это таблица "Произвольный счёт"
 
 CREATE TABLE [ACustomAccount] (
  [Id] [AnAccountId] IDENTITY (1, 1) NOT NULL ,
  [IsRoot] [AnUnorderedBoolean] NOT NULL CONSTRAINT [Set_to_false3] DEFAULT (0),
  [IsSub] [AnUnorderedBoolean] NOT NULL CONSTRAINT [Set_to_false4] DEFAULT (0),
  [IsFolder] [AnUnorderedBoolean] NOT NULL CONSTRAINT [Set_to_false5] DEFAULT
 (0),
  [IsLeaf] [AnUnorderedBoolean] NOT NULL CONSTRAINT [Set_to_false6] DEFAULT (0),
  CONSTRAINT [XPKACustomAccount] PRIMARY KEY  CLUSTERED
  (
   [Id]
  )  ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 
 -- некоторые счета являются счетами-папками
 
 CREATE TABLE [AFolderAccount] (
  [Id] [int] NOT NULL ,
  CONSTRAINT [XPKAFolderAccount] PRIMARY KEY  CLUSTERED
  (
   [Id]
  )  ON [PRIMARY] ,
  CONSTRAINT [aca_is_afa] FOREIGN KEY
  (
   [Id]
  ) REFERENCES [ACustomAccount] (
   [Id]
  )
 ) ON [PRIMARY]
 GO
 
 -- а в папках содержатся подсчета
 
 CREATE TABLE [ASubAccount] (
  [Id] [int] NOT NULL ,
  [FolderAccountId] [int] NOT NULL ,
  [Code] [AnAccountCode] NOT NULL ,
  CONSTRAINT [XPKASubAccount] PRIMARY KEY  NONCLUSTERED
  (
   [Id]
  )  ON [PRIMARY] ,
  CONSTRAINT [XAKSubAccountCode] UNIQUE  NONCLUSTERED
  (
   [FolderAccountId],
   [Code]
  )  ON [PRIMARY] ,
  CONSTRAINT [aca_is_asa] FOREIGN KEY
  (
   [Id]
  ) REFERENCES [ACustomAccount] (
   [Id]
  ),
  CONSTRAINT [contain_subaccounts] FOREIGN KEY
  (
   [FolderAccountId]
  ) REFERENCES [AFolderAccount] (
   [Id]
  ),
  CONSTRAINT [IsValidAccountCode34] CHECK (not([Code] like '%\%'))
 ) ON [PRIMARY]
 GO
 
 -- существует также понятие счетов, являющихся корнями деревьев счетов
 
 CREATE TABLE [ARootAccount] (
  [Id] [int] NOT NULL ,
  [Code] [AnAccountCode] NOT NULL ,
  CONSTRAINT [XPKARootAccount] PRIMARY KEY  CLUSTERED
  (
   [Id]
  )  ON [PRIMARY] ,
  CONSTRAINT [XAKRootAccountCode] UNIQUE  NONCLUSTERED
  (
   [Code]
  )  ON [PRIMARY] ,
  CONSTRAINT [aca_is_ara] FOREIGN KEY
  (
   [Id]
  ) REFERENCES [ACustomAccount] (
   [Id]
  ),
  CONSTRAINT [IsValidAccountCode33] CHECK (not([Code] like '%\%'))
 ) ON [PRIMARY]
 GO
 
 -- а это вспомогательная таблица
 
 CREATE TABLE [AnAccountAndItsAncestors] (
  [AncestorId] [int] NOT NULL ,
  [AccountId] [int] NOT NULL ,
  CONSTRAINT [XPKAnAccountAndItsAncestors] PRIMARY KEY  CLUSTERED
  (
   [AncestorId],
   [AccountId]
  )  ON [PRIMARY] ,
  CONSTRAINT [account_under_ancestors] FOREIGN KEY
  (
   [AccountId]
  ) REFERENCES [ACustomAccount] (
   [Id]
  ),
  CONSTRAINT [is_ancestor_of] FOREIGN KEY
  (
   [AncestorId]
  ) REFERENCES [ACustomAccount] (
   [Id]
  )
 ) ON [PRIMARY]
 GO
 
 -- эта процедура создаёт произвольный счёт
 
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE akCreateACustomAccount @Id AnAccountId OUTPUT, @CheckRights
 BIT = 1
 AS
 BEGIN
   /* initialize execution state */
   DECLARE @Result INT
   SET NOCOUNT ON
   SET @Result = 0
   SET @Id = -1
 
   BEGIN TRANSACTION
   IF (@CheckRights = 1) AND (dbo.akHasConstraint('Accounting:Plan:Change') = 0)
   BEGIN
     SET @Result = -100
     GOTO fin
   END
 
   INSERT
     INTO ACustomAccount
     DEFAULT VALUES
   SET @Result = @@ERROR
 
   IF @Result = 0
   BEGIN
     SET @Id = @@IDENTITY
     INSERT
       INTO AnAccountAndItsAncestors(AccountId, AncestorId)
       VALUES(@Id, @Id)
     SET @Result = @@ERROR
   END
 
   /* finalize transaction */
 fin:
   IF @@TRANCOUNT > 0
   BEGIN
     IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
   END
   RETURN @Result
 END
 
 -- эта процедура обновляет вспомогательную таблицу для одного подсчёта при его
 создании
 
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE PROCEDURE akUpdateSingleSubAccountAncestors @AccountId AnAccountId
 AS
 BEGIN
   /* initialize execution state */
   DECLARE @Result INT
   SET NOCOUNT ON
   SET @Result = 0
 
   BEGIN TRANSACTION
   IF NOT EXISTS(SELECT TOP 1 * FROM AnAccountAndItsAncestors WHERE (AncestorId
 = @AccountId) AND (AccountId = @AccountId))
   BEGIN
     INSERT
       INTO AnAccountAndItsAncestors(AccountId, AncestorId)
       VALUES(@AccountId, @AccountId)
     SET @Result = @@ERROR
     IF @Result <> 0 GOTO fin
   END
   INSERT
     INTO AnAccountAndItsAncestors(AccountId, AncestorId)
     SELECT @AccountId, AAIA.AncestorId FROM AnAccountAndItsAncestors AAIA INNER
 JOIN ASubAccount SA ON (AAIA.AccountId = SA.FolderAccountId) WHERE SA."Id" =
 @AccountId
   SET @Result = @@ERROR
 
   /* finalize transaction */
 fin:
   IF @@TRANCOUNT > 0
   BEGIN
     IF @Result <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION
   END
   RETURN @Result
 END
 GO
 
 === Cut ===
 
 Akzhan
 
 --- FMail/Win32 1.42/g
  * Origin: MT Computers, mailto:akzhan@mtgroup.ru (2:5030/217)
 
 

Вернуться к списку тем, сортированных по: возрастание даты  уменьшение даты  тема  автор