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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Reconstruct incremental files changes from a table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Gudea
Starting Member

18 Posts

Posted - 11/05/2012 :  14:09:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 11/06/2012 :  01:19:28  Show Profile  Reply with Quote
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
Go to Top of Page

Gudea
Starting Member

18 Posts

Posted - 11/06/2012 :  12:44:10  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 11/08/2012 :  00:14:45  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.13 seconds. Powered By: Snitz Forums 2000