源代码控制和存储过程 [英] Source Control and stored procedures

问题描述

我一直想知道如何将 SQL 2000 上的所有存储过程置于源代码控制之下.
我们所有的普通源代码都使用 Subversion，所以如果有使用 Subversion 的问题的解决方案，那就太好了.

I have been wondering how to put all the stored procedures on a SQL 2000 under source control.
We are using Subversion for all our normal source code, so it would be great if there were a solution to the problem using Subversion.

你有什么想法吗?

更新 16-02-2009:这是我用来导出所有存储过程的 vbs 脚本:

Update 16-02-2009: This is the vbs script i used to export all the stored procedures:

Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "*** Database connection string here ***"
con.Open 
Set rs = CreateObject("ADODB.RecordSet")
rs.ActiveConnection = con

strSQL = "SELECT ROUTINE_NAME, ROUTINE_DEFINITION " & _
"FROM INFORMATION_SCHEMA.routines " & _
"WHERE ROUTINE_NAME NOT LIKE 'dt_%' " & _
"ORDER BY 1"

Set fso = CreateObject("Scripting.FileSystemObject")
rs.Open strSQL 
While Not rs.Eof 
    filename = rs("ROUTINE_NAME") & ".sql"
    routineSQL = rs("ROUTINE_DEFINITION")
    Set tf = fso.CreateTextFile(filename, True)
    tf.Write routineSQL
    tf.Close
    set tf = Nothing 
    rs.MoveNext
Wend
Set fso = Nothing 
rs.Close 
Set rs = Nothing 

推荐答案

正如其他人所说，从源代码控制下的单独文本文件中的每个存储过程开始.编写一个删除所有存储过程的脚本，然后从文本文件中重新创建它们(同时记录/报告任何错误)——这个脚本应该很容易运行.然后每次从源代码控制更新时重新运行脚本.对存储过程的所有编辑都应该对文本文件进行，而不是本地数据库上的实时"副本，否则在进行更新时会丢失更改.

As other people have said, start off with each stored proc in a separated text file that is under source control. Write a script that deletes all you stored procedures then re-creates them from the text files (while logging/reporting any errors) – this script should be easy to run. Then every time you update from source control rerun the script. All edits to stored procedures should be done to the text file, not the "live" copy on your local database otherwise you will loose changes when you do a update.

您很快就会希望以某种方式审核您的数据库架构并创建升级脚本等.

You will soon want someway of auditing your database schema and creating upgrade scripts etc.

如果您只使用 SQL 服务器，请考虑使用 SQL 比较来自 Reg-Gate.我认为它会将文本文件中存储的过程(和其他 sql)与数据库中的内容进行比较并同步两者.所以让你使用SqlServer中的编辑工具来编辑实时存储过程.

If you are only using SQL server then consider SQL Compare from Reg-Gate. I think it will compare stored procs (and other sql) in a text file with what is in your database and sync the two. So letting you use the editing tools in SqlServer to edit the live stored procedures.

(截至 2009 年底，Red-Gate 即将发布 Oracle 的 SQL 比较)

(As of the end of 2009, Red-Gate is just about to ship Sql Compare for Oracle)

有人告诉我 ApexSQL 的 Diff 工具是另一种选择，而不是 ApexSQL 的 Sql Compare编辑声称提供源代码控制集成.

I have been told that ApexSQL's Diff tool is another option instead of Sql Compare, ApexSQL's Edit claims to provide source control integration.

在高端考虑Visual Studio Team System 数据库版，但它的成本很高，那么您可能需要为第三方的Oracle 支持支付更多费用.但是，如果您是 Microsoft 的合作伙伴(或可以成为合作伙伴)，您可能会以非常便宜的价格获得一些合作伙伴.

At the high-end consider Visual Studio Team System Database Edition, however it costs a lot, then you may have to pay even more for Oracle support from a 3rd party. But if you are a Microsoft partner (or can become one) you may get some copes very cheaply.

另请参阅 StackOverflow 上的您是否对数据库进行源代码控制? 以获得有关更大问题的一组很好的答案.

See also Do you source control your databases? on StackOverflow for a good set of answers on the bigger problem.

这篇关于源代码控制和存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！