Reconstruct incremental files changes from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Reconstruct incremental files changes from a table

Author

Topic

Gudea
Starting Member

18 Posts

Posted - 2012-11-05 : 14:09:14

Hi.

I am dveloping an application that stores versions of a project. Each version stores only file changes (new and modified ones).

What I need is to historically reconstruct the product state at any version.

Tables:
Project (Project_Id, Project_Name)
Version (Version_Id, Verion_Number, Version_Project_Id)
File (File_Id, File_Name, File_Path, File_Size, File_Created_Date, File_ModifiedDate)

Version number 1 contains the whole project files.
Verions 2 and 3 contains only files modified and/or added.

I need to list project state at version 2, or 3, or 4... and so.
So I need to list the last version of every file.

Test tables creation:


USE [master]
GO
/****** Object:  Database [TestProjectVersions]    Script Date: 11/05/2012 15:39:24 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'TestProjectVersions')
BEGIN
CREATE DATABASE [TestProjectVersions] ON  PRIMARY 
( NAME = N'TestProyectVersions', FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestProyectVersions.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestProyectVersions_log', FILENAME = N'C:\Archivos de programa\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestProyectVersions_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'TestProjectVersions', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [TestProjectVersions].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [TestProjectVersions] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestProjectVersions] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestProjectVersions] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestProjectVersions] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestProjectVersions] SET ARITHABORT OFF
GO
ALTER DATABASE [TestProjectVersions] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [TestProjectVersions] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestProjectVersions] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestProjectVersions] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestProjectVersions] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestProjectVersions] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [TestProjectVersions] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestProjectVersions] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestProjectVersions] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestProjectVersions] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestProjectVersions] SET  ENABLE_BROKER
GO
ALTER DATABASE [TestProjectVersions] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestProjectVersions] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestProjectVersions] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestProjectVersions] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestProjectVersions] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestProjectVersions] SET  READ_WRITE
GO
ALTER DATABASE [TestProjectVersions] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestProjectVersions] SET  MULTI_USER
GO
ALTER DATABASE [TestProjectVersions] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestProjectVersions] SET DB_CHAINING OFF
GO
USE [TestProjectVersions]
GO
/****** Object:  ForeignKey [FK_File_Version]    Script Date: 11/05/2012 15:39:24 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_File_Version]') AND parent_object_id = OBJECT_ID(N'[dbo].[File]'))
ALTER TABLE [dbo].[File] DROP CONSTRAINT [FK_File_Version]
GO
/****** Object:  ForeignKey [FK_Version_Project]    Script Date: 11/05/2012 15:39:24 ******/
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Version_Project]') AND parent_object_id = OBJECT_ID(N'[dbo].[Version]'))
ALTER TABLE [dbo].[Version] DROP CONSTRAINT [FK_Version_Project]
GO
/****** Object:  Table [dbo].[File]    Script Date: 11/05/2012 15:39:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[File]') AND type in (N'U'))
DROP TABLE [dbo].[File]
GO
/****** Object:  Table [dbo].[Version]    Script Date: 11/05/2012 15:39:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))
DROP TABLE [dbo].[Version]
GO
/****** Object:  Table [dbo].[Project]    Script Date: 11/05/2012 15:39:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Project]') AND type in (N'U'))
DROP TABLE [dbo].[Project]
GO
/****** Object:  Table [dbo].[Project]    Script Date: 11/05/2012 15:39:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Project]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Project](
	[Project_Id] [int] NOT NULL,
	[Project_Name] [varchar](50) NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
	[Project_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Version]    Script Date: 11/05/2012 15:39:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Version](
	[Version_Id] [int] NOT NULL,
	[Version_Number] [int] NOT NULL,
	[Version_Project_Id] [int] NOT NULL,
 CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED 
(
	[Version_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object:  Table [dbo].[File]    Script Date: 11/05/2012 15:39:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[File]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[File](
	[File_Id] [int] NOT NULL,
	[File_Name] [varchar](50) NOT NULL,
	[File_Path] [varchar](3000) NOT NULL,
	[File_Size] [bigint] NOT NULL,
	[File_CreatedDate] [datetime] NOT NULL,
	[File_ModifiedDate] [datetime] NOT NULL,
	[File_Version_Id] [int] NOT NULL,
 CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED 
(
	[File_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_File_Version]    Script Date: 11/05/2012 15:39:24 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_File_Version]') AND parent_object_id = OBJECT_ID(N'[dbo].[File]'))
ALTER TABLE [dbo].[File]  WITH CHECK ADD  CONSTRAINT [FK_File_Version] FOREIGN KEY([File_Version_Id])
REFERENCES [dbo].[Version] ([Version_Id])
GO
ALTER TABLE [dbo].[File] CHECK CONSTRAINT [FK_File_Version]
GO
/****** Object:  ForeignKey [FK_Version_Project]    Script Date: 11/05/2012 15:39:24 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Version_Project]') AND parent_object_id = OBJECT_ID(N'[dbo].[Version]'))
ALTER TABLE [dbo].[Version]  WITH CHECK ADD  CONSTRAINT [FK_Version_Project] FOREIGN KEY([Version_Project_Id])
REFERENCES [dbo].[Project] ([Project_Id])
GO
ALTER TABLE [dbo].[Version] CHECK CONSTRAINT [FK_Version_Project]
GO

Test data:


/****** Object:  Table [dbo].[File]    Script Date: 11/05/2012 15:43:59 ******/
DELETE FROM [dbo].[File]
GO
/****** Object:  Table [dbo].[Version]    Script Date: 11/05/2012 15:43:59 ******/
DELETE FROM [dbo].[Version]
GO
/****** Object:  Table [dbo].[Project]    Script Date: 11/05/2012 15:43:59 ******/
DELETE FROM [dbo].[Project]
GO
/****** Object:  Table [dbo].[Project]    Script Date: 11/05/2012 15:43:59 ******/
INSERT [dbo].[Project] ([Project_Id], [Project_Name]) VALUES (1, N'Accounting application')
/****** Object:  Table [dbo].[Version]    Script Date: 11/05/2012 15:43:59 ******/
INSERT [dbo].[Version] ([Version_Id], [Version_Number], [Version_Project_Id]) VALUES (1, 1, 1)
INSERT [dbo].[Version] ([Version_Id], [Version_Number], [Version_Project_Id]) VALUES (2, 2, 1)
INSERT [dbo].[Version] ([Version_Id], [Version_Number], [Version_Project_Id]) VALUES (6, 3, 1)
/****** Object:  Table [dbo].[File]    Script Date: 11/05/2012 15:43:59 ******/
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (1, N'Start.exe', N'\Start.exe', 200000, CAST(0x00009FEC00000000 AS DateTime), CAST(0x00009FED00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (2, N'Classes.dll', N'\Model\Classes.dll', 340000, CAST(0x00009FEC00000000 AS DateTime), CAST(0x00009FEC00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (3, N'DAL.dll', N'\DAL\DAL.dll', 200000, CAST(0x00009FEC00000000 AS DateTime), CAST(0x00009FED00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (4, N'BR.dll', N'\Model\BR.dll', 400000, CAST(0x00009FEC00000000 AS DateTime), CAST(0x00009FED00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (5, N'Logo.gif', N'\Imgs\Logo.gif', 20010, CAST(0x00009FEA00000000 AS DateTime), CAST(0x00009FEA00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (6, N'Tools.dll', N'\DAL\Tools.dll', 30000, CAST(0x00009FCC00000000 AS DateTime), CAST(0x00009FCC00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (7, N'Tools.dll', N'\Model\Tools.dll', 520000, CAST(0x00009FEA00000000 AS DateTime), CAST(0x00009FEA00000000 AS DateTime), 1)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (8, N'Classes.dll', N'\Model\Classes.dll', 342000, CAST(0x0000A00700000000 AS DateTime), CAST(0x0000A00700000000 AS DateTime), 2)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (9, N'Start.exe', N'\Start.exe', 200100, CAST(0x0000A00700000000 AS DateTime), CAST(0x0000A00700000000 AS DateTime), 2)
INSERT [dbo].[File] ([File_Id], [File_Name], [File_Path], [File_Size], [File_CreatedDate], [File_ModifiedDate], [File_Version_Id]) VALUES (10, N'Tools.dll', N'\DAL\Tools.dll', 32000, CAST(0x0000A02600000000 AS DateTime), CAST(0x0000A02600000000 AS DateTime), 6)

This, will give me the initial project state (full initial version)


SELECT File_Id, File_Name, File_Path, File_Size, File_CreatedDate, File_ModifiedDate
FROM [File]
INNER JOIN
Version
ON Version_Id = File_Version_Id
WHERE
Version_Project_Id = 1
AND
Version_Id = 1

Passing Version_Id = 6 will bring only one file (the one that changed).

What I need is to bring the whole project of Verion 1, with Files from Verion 2 replaced and also from Version 3

I can't get that results.
Any help?

Thanks
Regards

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-06 : 01:19:28

hi,


SELECT File_Id, File_Name, File_Path, File_Size, File_CreatedDate, File_ModifiedDate, Version_Id
FROM (SELECT File_Id, File_Name, File_Path, File_Size, File_CreatedDate, File_ModifiedDate, Version_Id
	, ROW_NUMBER() over(partition by file_name, file_path ORDER BY File_CreatedDate DESC) rn
	FROM [File]
	INNER JOIN
	Version
	ON Version_Id = File_Version_Id
	WHERE
	Version_Project_Id = 1
	) t
WHERE t.rn = 1

--
Chandu

Gudea
Starting Member

18 Posts

Posted - 2012-11-06 : 12:44:10

Wow. That "partition by" did the trick.
Worked like a charm.

Actually I needed to get the Project state at any version (not always last). Just added a WHERE condition Version_Id <= @Version_Id

And wanted to consider last version of a file by Version_Number it doesn't matter the date it was created / modified.

The query resulted like this:


DECLARE @Version_Id int
SET @Version_Id = 2

SELECT File_Id, File_Name, File_Path, File_Size, File_CreatedDate, File_ModifiedDate, Version_Id
FROM (SELECT File_Id, File_Name, File_Path, File_Size, File_CreatedDate, File_ModifiedDate, Version_Id
	, ROW_NUMBER() over(partition by file_name, file_path ORDER BY Version_Number DESC) rn
	FROM [File]
	INNER JOIN
	Version
	ON Version_Id = File_Version_Id
	WHERE
	Version_Project_Id = 1
	AND Version_Id <= @Version_Id
	) t
WHERE t.rn = 1
ORDER BY File_Path

Thanks a lot Chandu.
Regards

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-08 : 00:14:45

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources