Archiv der Kategorie: Versioning

PL/SQL-Function for SVN-Revision

If you are using Subversion-Keywords in you PLSQL-Project you can automatically get the info for all these Keywords for Packages, Procedures and Functions.

Just use a function get_svn_info to find out, which version, date and so on are located actually in you database:

Funcion-Declaration for the Package-Header:

/**
*
* Gibt die Subversion-Informationen des Package-Bodies zurueck
* @is_type mögliche Ausprägungen:
* 'version','url','autor','datum','all'
* default: 'version'
* @return angeforderte Subversion-Info des Package Bodies
*/
FUNCTION  get_svn_Info
(is_type                     IN VARCHAR2 DEFAULT 'version')
RETURN VARCHAR2;

 

Record-declaration in the Package-Body:

TYPE typ_svn_info IS RECORD (
version  VARCHAR2 (256) := '$Rev: 209690 $',
autor    VARCHAR2 (256) := '$Author: authorname $',
datum    VARCHAR2 (256) := '$Date: 2016-02-09 09:32:47 +0100 (Di, 09. Feb 2016) $',
url      VARCHAR2 (256) := '$URL: svn://dir1/oracle/trunk/myschema/MYSCHEMA.MYPACKAGE.packbody $'
);

And the function itself in the Package-Body:

/**
*
* Gibt die Subversion-Informationen des Package-Bodies zurueck
* @is_type mögliche Ausprägungen:
* 'version' oder 'revision','url','autor','datum','name','all'
* default: 'version'
* @return angeforderte Subversion-Info des Package Bodies
*/
FUNCTION  get_svn_Info
(is_type                     IN VARCHAR2 DEFAULT 'version')
RETURN VARCHAR2
is

c_convert   varchar2 (4000);
infos       typ_svn_info;

begin

case lower(is_type)
when 'version' then     c_convert := infos.version;
when 'revision' then    c_convert := infos.version;
when 'url' then         c_convert := infos.url;
when 'autor' then       c_convert := infos.autor;
when 'datum' then       c_convert := infos.datum;
when 'name'  then       c_convert := substr (infos.url,instr(infos.url,'/',-1)+1); -- name = part after the last slash
when 'all' then
c_convert := get_svn_Info('url')
||' Ver.'||get_svn_Info('version')
||' von:'||upper(get_svn_Info('autor'))
||' letzte Aenderung:'||get_svn_Info('datum')
;
return c_convert;
else
return 'Parameter existiert nicht! (all, autor, url, datum, version, name)';
end case;

c_convert := replace(c_convert,'$','');
c_convert := trim(substr(c_convert,instr(c_convert,':',1,1)+1));
return c_convert;
exception
when others then return '-1';
end get_svn_info;

Examplecall:

set serveroutput on
exec dbms_output.put_line(myschema.mypackage.get_svn_Info('name')||' '||myschema.mypackage.get_svn_Info('Revision')||' vom '||myschema.mypackage.get_svn_Info('datum')||' ('||myschema.mypackage.get_svn_Info('autor')||')');

Result:

myschema.my_package.packbody 209690 vom 2016-02-09 09:32:47 +0100 (Di, 09. Feb 2016) (authorname)

Subversion SVN: Set Keyword-Properties for existing files

If you want to use in an already existing Project the keyword-substitution in Subversin as described here: Subversion Keywords (Revision,Author,URL,Date,ID) you have to set the properties to substitute these keywords for all existing files, too.

This can be done e.g. in Tortoise SVN in the contextmenu-properties-new
Just check in the needed Keywords an click OK.
You can the properties for every single file or recursively for an directory:

Subversion - TortiseSVN set Properties-Dialog
Subversion – TortiseSVN set Properties-Dialog

After setting this properties, alle the files have to be checked in!

More information can be found here.

Subversion Keywords (Revision,Author,URL,Date,ID)

It is useful if every (touched) DB-Object contains in the future the following lines:

Procedures/Functions must contain in the Header-Comment:

/*** URL: $URL$ */
/*** Revision: $Rev$ */
/*** Author: $Author$ */
/*** Date: $Date$ */
/*** ID: $Id$ */

Table-Create-Statements have to be enhanced with the following statement:

COMMENT ON TABLE IS ‚
URL: $URL$
Revision: $Rev$
Author: $Author$
Date: $Date$
ID: $Id$
‚;

—————————————————————————————————————————-

Explanation:

These variables will be replaced by every SVN-Checkin (Subversion-VersionsControll-System) with their pendants.
Example:
$URL$ => $URL: http://…/branches/SDLCV11726/svn-test.txt $
$Rev$ => $Rev: 13558 $
$Author$ => $Author: herzogg $
$Date$ => $Date: 2012-09-13 15:58:28 +0200 (Do, 13 Sep 2012) $
$Id$ => $Id: svn-test.txt 13558 2012-09-13 13:58:28Z herzogg $

So it is possible to see in the Database which version is the Table/Function/Procedure and so on,
what is very helpful.

—————————————————————————————————————————-
Configuration of Tortoise SVN

To activate this feature in SVN please open SVN-Settings ([context-menu]->[TortoiseSVN]->[Settings]
Then Click [EDIT] on the Subversion Configuration File on the General – Tab:

Add or uncomment the following line in this config-file in the [miscellany] – Section

enable-auto-props=yes

Add following lines in this config-file in the [auto-props] – Section
(Add new file-endings if you are using different…)
*.sql = svn:keywords=URL Rev Author Date Id
*.fnc = svn:keywords=URL Rev Author Date Id
*.prc = svn:keywords=URL Rev Author Date Id
*.pks = svn:keywords=URL Rev Author Date Id
*.pkb = svn:keywords=URL Rev Author Date Id
*.txt = svn:keywords=URL Rev Author Date Id

How to set these properties for existing files in a already running Project.