/* this script needs the table below */
/*
IF ISNULL(OBJECT_ID('L_Last_Sysversions'), 0) <> 0 DROP TABLE L_Last_Sysversions
CREATE TABLE L_Last_Sysversions (
    name varchar(128), 
    id int,
    modify_date datetime,
    type char(2),
    filename varchar(255)
)
*/



SET NOCOUNT ON

/* ************************************** */
/*               PARAMETERS               */
/* ************************************** */
--
-- parameter values from a parameter table Z_Parameters

declare @svn_command as nvarchar(255)
select @svn_command = ParameterValue
from Z_Parameters
where ParameterName = 'svn_command'

declare @svn_wc as nvarchar(255)
select @svn_wc = ParameterValue
from Z_Parameters
where ParameterName = 'svn_workingcopy'


declare @svn_user as nvarchar(255)
select @svn_user = ParameterValue
from Z_Parameters
where ParameterName = 'svn_user'

declare @svn_pass as nvarchar(255)
select @svn_pass = ParameterValue
from Z_Parameters
where ParameterName = 'svn_pass'


--
--alternatively, if not reading values from a parameter table, set them directly:
--set @svn_command = N'"C:\Program Files\CollabNet Subversion Client\svn"'
--set @svn_wc = N'C:\DWH\DBVersioning\DBSource\'
--set @svn_user = N'joe'
--set @svn_pass = N'foobar'

/* ************************************** */



declare @svn_file_prefix as varchar(255)
set @svn_file_prefix = (select DB_Name()) + '.'


CREATE TABLE #tmp (
    name varchar(128), 
    id int,
    modify_date datetime,
    type char(2),
    filename varchar(255)
)

-- Insert the values from the end of the last run into #tmp
INSERT #tmp (name, id, modify_date, type, filename) 
SELECT name, id, modify_date, type, filename
FROM L_Last_Sysversions

DELETE L_Last_Sysversions

INSERT L_Last_Sysversions (name, id, modify_date, type, filename)
SELECT o.name, o.object_id, modify_date, o.type, @svn_file_prefix + o.name + 
 case
	when o.type in ('U', 'C', 'F') then '.Table.sql'
	when o.type = 'V' then '.View.sql'
	when o.type = 'P' then '.StoredProcedure.sql'
	when o.type = 'D' then '.Default.sql'
	when o.type = 'R' then '.Rule.sql'
	when o.type = 'FN' then '.Function.sql'
 end
FROM sys.all_objects o
WHERE o.type IN ('P' ,'U' ,'V', 'FN', 'C', 'D', 'F', 'R')
  and is_ms_shipped=0


declare @out as nvarchar(max)
declare @i int
set @i=0

/* something added: svn add command needed */
select @i=1
from L_Last_Sysversions n
left join #tmp t on t.filename = n.filename
where t.filename is null

/* things deleted and renamed: svn delete command needed */
declare svn_c cursor local forward_only for
	select @svn_command + N' delete --force -q ' + @svn_wc + t.filename as svncommand
	from #tmp t
	left join L_Last_Sysversions n on n.filename = t.filename
	where n.filename is null

open svn_c

fetch next from svn_c into @out
WHILE @@FETCH_STATUS = 0
BEGIN
 print @out
 fetch next from svn_c into @out
END

close svn_c
deallocate svn_c
drop table #tmp

if @i=1 begin
  print @svn_command + N' add ' + @svn_wc + '* --force' /* add new things */
end

print @svn_command + N' commit ' + @svn_wc + ' -m "auto-commit from db" --username ' + @svn_user + N' --password ' + @svn_pass