Tuesday, October 31, 2017
Wednesday, October 22, 2014
send mail in MSSQL
USE [BUPADigitalLoyaltyCard]
GO
/****** Object: StoredProcedure [dbo].[prcSendEmail] Script Date: 10/23/2014 10:34:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- ALTER date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[prcSendEmail]
@email varchar(100)
AS
BEGIN
DECLARE @SMSBodyText VARCHAR(50)
DECLARE @MySubject VARCHAR(50)
DECLARE @bodyText VARCHAR(MAX)
DECLARE @url VARCHAR(100)
DECLARE @formid VARCHAR(10)
Set @MySubject = 'BUPA Digital Loyalty Card - BUPA'
--SET @Email = 'Gary.Templeton@cpm-aus.com.au;'
SELECT top 1 @formid=formid FROM tblLoyaltyCardDetails ORDER BY newid()
SET @url = 'http://10.2.119.228/BupaDLC/BUPADigiLoyaltyCard/index?formid='+ @formid
print @url
Set @MySubject = 'Passkit URL - BUPA'
Set @bodyText = 'Hello
Click on the link below:
'+ @url + '
Regards,
BUPA' IF @email IS NOT NULL print @email Begin EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AGLCustomerCare', @recipients = @email, @blind_copy_recipients = 'reshma.karunakaran@cpm-aus.com.au', @subject = @MySubject, @body = @bodyText, @body_format = 'HTML'; END END
Click on the link below:
'+ @url + '
Regards,
BUPA' IF @email IS NOT NULL print @email Begin EXEC msdb.dbo.sp_send_dbmail @profile_name = 'AGLCustomerCare', @recipients = @email, @blind_copy_recipients = 'reshma.karunakaran@cpm-aus.com.au', @subject = @MySubject, @body = @bodyText, @body_format = 'HTML'; END END
Monday, October 14, 2013
10-things-in-mysql-that-wont-work-as-expected
http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/
Reference from this guys, it seems useful
10. Searching for a NULL
view sourceprint?
1.
SELECT *
2.
FROM a
3.
WHERE a.column = NULL
In SQL, a NULL is never equal to anything, even another NULL. This query won’t return anything and in fact will be thrown out by the optimizer when building the plan.
When searching for NULL values, use this instead:
view sourceprint?
1.
SELECT *
2.
FROM a
3.
WHERE a.column IS NULL
#9. LEFT JOIN with additional conditions
view sourceprint?
1.
SELECT *
2.
FROM a
3.
LEFT JOIN
4.
b
5.
ON b.a = a.id
6.
WHERE b.column = 'something'
A LEFT JOIN is like INNER JOIN except that it will return each record from a at least once, substituting missing fields from b with NULL values, if there are no actual matching records.
The WHERE condition, however, is evaluated after the LEFT JOIN so the query above checks column after it had been joined. And as we learned earlier, no NULL value can satisfy an equality condition, so the records from a without corresponding record from b will unavoidably be filtered out.
Essentially, this query is an INNER JOIN, only less efficient.
To match only the records with b.column = 'something' (while still returning all records from a), this condition should be moved into ON clause:
view sourceprint?
1.
SELECT *
2.
FROM a
3.
LEFT JOIN
4.
b
5.
ON b.a = a.id
6.
AND b.column = 'something'
#8. Less than a value but not a NULL
Quite often I see the queries like this:
view sourceprint?
1.
SELECT *
2.
FROM b
3.
WHERE b.column < 'something'
4.
AND b.column IS NOT NULL
This is actually not an error: this query is valid and will do what’s intended. However, IS NOT NULL here is redundant.
If b.column is a NULL, then b.column < 'something' will never be satisfied, since any comparison to NULL evaluates to a boolean NULL and does not pass the filter.
It is interesting that this additional NULL check is never used for greater than queries (like in b.column > 'something').
This is because NULL go first in ORDER BY in MySQL and hence are incorrectly considered less than any other value by some people.
This query can be simplified:
view sourceprint?
1.
SELECT *
2.
FROM b
3.
WHERE b.column < 'something'
and will still never return a NULL in b.column.
#7. Joining on NULL
view sourceprint?
1.
SELECT *
2.
FROM a
3.
JOIN b
4.
ON a.column = b.column
When column is nullable in both tables, this query won't return a match of two NULLs for the reasons described above: no NULLs are equal.
Here's a query to do that:
view sourceprint?
1.
SELECT *
2.
FROM a
3.
JOIN b
4.
ON a.column = b.column
5.
OR (a.column IS NULL AND b.column IS NULL)
MySQL's optimizer treats this as an equijoin and provides a special join condition, ref_or_null.
#6. NOT IN with NULL values
view sourceprint?
1.
SELECT a.*
2.
FROM a
3.
WHERE a.column NOT IN
4.
(
5.
SELECT column
6.
FROM b
7.
)
This query will never return anything if there is but a single NULL in b.column. As with other predicates, both IN and NOT IN against NULL evaluate to NULL.
This should be rewritten using a NOT EXISTS:
view sourceprint?
1.
SELECT a.*
2.
FROM a
3.
WHERE NOT EXISTS
4.
(
5.
SELECT NULL
6.
FROM b
7.
WHERE b.column = a.column
8.
)
Unlike IN, EXISTS always evaluates to either true or false.
#5. Ordering random samples
view sourceprint?
1.
SELECT *
2.
FROM a
3.
ORDER BY
4.
RAND(), column
5.
LIMIT 10
This query attempts to select 10 random records ordered by column.
ORDER BY orders the output lexicographically: that is, the records are only ordered on the second expression when the values of the first expression are equal.
However, the results of RAND() are, well, random. It's infeasible that the values of RAND() will match, so ordering on column after RAND() is quite useless.
To order the randomly sampled records, use this query:
view sourceprint?
01.
SELECT *
02.
FROM (
03.
SELECT *
04.
FROM mytable
05.
ORDER BY
06.
RAND()
07.
LIMIT 10
08.
) q
09.
ORDER BY
10.
column
#4. Sampling arbitrary record from a group
This query intends to select one column from each group (defined by grouper)
view sourceprint?
1.
SELECT DISTINCT(grouper), a.*
2.
FROM a
DISTINCT is not a function, it's a part of SELECT clause. It applies to all columns in the SELECT list, and the parentheses here may just be omitted. This query may and will select the duplicates on grouper (if the values in at least one of the other columns differ).
Sometimes, it's worked around using this query (which relies on MySQL's extensions to GROUP BY):
view sourceprint?
1.
SELECT a.*
2.
FROM a
3.
GROUP BY
4.
grouper
Unaggregated columns returned within each group are arbitrarily taken.
At first, this appears to be a nice solution, but it has quite a serious drawback. It relies on the assumption that all values returned, though taken arbitrarily from the group, will still belong to one record.
Though with current implementation is seems to be so, it's not documented and can be changed in any moment (especially if MySQL will ever learn to apply index_union after GROUP BY). So it's not safe to rely on this behavior.
This query would be easy to rewrite in a cleaner way if MySQL supported analytic functions. However, it's still possible to make do without them, if the table has a PRIMARY KEY defined:
view sourceprint?
01.
SELECT a.*
02.
FROM (
03.
SELECT DISTINCT grouper
04.
FROM a
05.
) ao
06.
JOIN a
07.
ON a.id =
08.
(
09.
SELECT id
10.
FROM a ai
11.
WHERE ai.grouper = ao.grouper
12.
LIMIT 1
13.
)
#3. Sampling first record from a group
This is a variation of the previous query:
view sourceprint?
1.
SELECT a.*
2.
FROM a
3.
GROUP BY
4.
grouper
5.
ORDER BY
6.
MIN(id) DESC
Unlike the previous query, this one attempts to select the record holding the minimal id.
Again: it is not guaranteed that the unaggregated values returned by a.* will belong to a record holding MIN(id) (or even to a single record at all).
Here's how to do it in a clean way:
view sourceprint?
01.
SELECT a.*
02.
FROM (
03.
SELECT DISTINCT grouper
04.
FROM a
05.
) ao
06.
JOIN a
07.
ON a.id =
08.
(
09.
SELECT id
10.
FROM a ai
11.
WHERE ai.grouper = ao.grouper
12.
ORDER BY
13.
ai.grouper, ai.id
14.
LIMIT 1
15.
)
This query is just like the previous one but with ORDER BY added to ensure that the first record in id order will be returned.
#2. IN and comma-separated list of values
This query attempts to match the value of column against any of those provided in a comma-separated string:
view sourceprint?
1.
SELECT *
2.
FROM a
3.
WHERE column IN ('1, 2, 3')
This does not work because the string is not expanded in the IN list.
Instead, if column column is a VARCHAR, it is compared (as a string) to the whole list (also as a string), and of course will never match. If column is of a numeric type, the list is cast into the numeric type as well (and only the first item will match, at best).
The correct way to deal with this query would be rewriting it as a proper IN list
view sourceprint?
1.
SELECT *
2.
FROM a
3.
WHERE column IN (1, 2, 3)
, or as an inline view:
view sourceprint?
01.
SELECT *
02.
FROM (
03.
SELECT 1 AS id
04.
UNION ALL
05.
SELECT 2 AS id
06.
UNION ALL
07.
SELECT 3 AS id
08.
) q
09.
JOIN a
10.
ON a.column = q.id
, but this is not always possible.
To work around this without changing the query parameters, one can use FIND_IN_SET:
view sourceprint?
1.
SELECT *
2.
FROM a
3.
WHERE FIND_IN_SET(column, '1,2,3')
This function, however, is not sargable and a full table scan will be performed on a.
#1. LEFT JOIN with COUNT(*)
view sourceprint?
1.
SELECT a.id, COUNT(*)
2.
FROM a
3.
LEFT JOIN
4.
b
5.
ON b.a = a.id
6.
GROUP BY
7.
a.id
This query intends to count number of matches in b for each record in a.
The problem is that COUNT(*) will never return a 0 in such a query. If there is no match for a certain record in a, the record will be still returned and counted.
COUNT should be made to count only the actual records in b. Since COUNT(*), when called with an argument, ignores NULLs, we can pass b.a to it. As a join key, it can never be a null in an actual match, but will be if there were no match:
view sourceprint?
1.
SELECT a.id, COUNT(b.a)
2.
FROM a
3.
LEFT JOIN
4.
b
5.
ON b.a = a.id
6.
GROUP BY
7.
a.id
P.S. In case you were wondering: no, the pictures don't have any special meaning. I just liked them.
Thursday, October 3, 2013
Which File Format Should You Choose?
Which File Format Should You Choose?
The help manual of a popular screen capture program offers the following suggestions
GIF format is limited to 256 colors and is a lossless compression file format, a common choice for use on the Web. GIF is a good choice for storing line drawings, text, and iconic graphics at a small file size.
PNG format is a lossless compression file format, which makes it a common choice for use on the Web. PNG is a good choice for storing line drawings, text, and iconic graphics at a small file size.
JPG format is a lossy compressed file format. This makes it useful for storing photographs at a smaller size than a BMP. JPG is a common choice for use on the Web because it is compressed. For storing line drawings, text, and iconic graphics at a smaller file size, GIF or PNG are better choices because they are lossless.
George adds – “JPEGs are for photographs and realistic images. PNGs are for line art, text-heavy images, and images with few colors. GIFs are just fail.”
Wednesday, September 18, 2013
PHP regular expression
http://www.noupe.com/php/php-regular-expressions.html
Getting Started with PHP Regular Expressions
By Noupe Editorial TeamPosted in PHP53 comments
Advertisement
1. What are Regular Expressions
The main purpose of regular expressions, also called regex or regexp, is to efficiently search for patterns in a given text. These search patterns are written using a special format which a regular expression parser understands.
Regular expressions are originating from Unix systems, where a program was designed, called grep, to help users work with strings and manipulate text. By following a few basic rules, one can create very complex search patterns.
As an example, let’s say you’re given the task to check wether an e-mail or a telephone number has the correct form. Using a few simple commands these problems can easily be solved thanks to regular expressions. The syntax doesn’t always seems straightforward at first, but once you learn it, you’ll realize that you can do pretty complex searches easily, just by typing in a few characters and you’ll approach problems from a different perspective.
2. Perl Compatible Regular Expressions
PHP has implemented quite a few regex functions which uses different parsing engines. There are two major parser in PHP. One called POSIX and the other PCRE or Perl Compatible Regular Expression.
The PHP function prefix for POSIX is ereg_. Since the release of PHP 5.3 this engine is deprecated, but let’s have a look at the more optimal and faster PCRE engine.
In PHP every PCRE function starts with preg_ such as preg_match or preg_replace. You can read the full function list in PHP’s documentation.
3. Basic Syntax
To use regular expressions first you need to learn the syntax. This syntax consists in a series of letters, numbers, dots, hyphens and special signs, which we can group together using different parentheses.
In PHP every regular expression pattern is defined as a string using the Perl format. In Perl, a regular expression pattern is written between forward slashes, such as /hello/. In PHP this will become a string, ‘/hello/’.
Now, let’s have a look at some operators, the basic building blocks of regular expressions
Operator Description
^ The circumflex symbol marks the beginning of a pattern, although in some cases it can be omitted
$ Same as with the circumflex symbol, the dollar sign marks the end of a search pattern
. The period matches any single character
? It will match the preceding pattern zero or one times
+ It will match the preceding pattern one or more times
* It will match the preceding pattern zero or more times
| Boolean OR
- Matches a range of elements
() Groups a different pattern elements together
[] Matches any single character between the square brackets
{min, max} It is used to match exact character counts
\d Matches any single digit
\D Matches any single non digit caharcter
\w Matches any alpha numeric character including underscore (_)
\W Matches any non alpha numeric character excluding the underscore character
\s Matches whitespace character
As an addition in PHP the forward slash character is escaped using the simple slash \. Example: ‘/he\/llo/’
To have a brief understanding how these operators are used, let’s have a look at a few examples:
Example Description
‘/hello/’ It will match the word hello
‘/^hello/’ It will match hello at the start of a string. Possible matches are hello or helloworld, but not worldhello
‘/hello$/’ It will match hello at the end of a string.
‘/he.o/’ It will match any character between he and o. Possible matches are helo or heyo, but not hello
‘/he?llo/’ It will match either llo or hello
‘/hello+/’ It will match hello on or more time. E.g. hello or hellohello
‘/he*llo/’ Matches llo, hello or hehello, but not hellooo
‘/hello|world/’ It will either match the word hello or world
‘/(A-Z)/’ Using it with the hyphen character, this pattern will match every uppercase character from A to Z. E.g. A, B, C…
‘/[abc]/’ It will match any single character a, b or c
‘/abc{1}/’ Matches precisely one c character after the characters ab. E.g. matches abc, but not abcc
‘/abc{1,}/’ Matches one or more c character after the characters ab. E.g. matches abc or abcc
‘/abc{2,4}/’ Matches between two and four c character after the characters ab. E.g. matches abcc, abccc or abcccc, but not abc
Besides operators, there are regular expression modifiers, which can globally alter the behavior of search patterns.
The regex modifiers are placed after the pattern, like this ‘/hello/i’ and they consists of single letters such as i which marks a pattern case insensitive or x which ignores white-space characters. For a full list of modifiers please visit PHP’s online documentation.
The real power of regular expressions relies in combining these operators and modifiers, therefore creating rather complex search patterns.
4. Using Regex in PHP
In PHP we have a total of nine PCRE functions which we can use. Here’s the list:
preg_filter – performs a regular expression search and replace
preg_grep – returns array entries that match a pattern
preg_last_error – returns the error code of the last PCRE regex execution
preg_match – perform a regular expression match
preg_match_all – perform a global regular expression match
preg_quote – quote regular expression characters
preg_replace – perform a regular expression search and replace
preg_replace_callback – perform a regular expression search and replace using a callback
preg_split – split string by a regular expression
The two most commonly used functions are preg_match and preg_replace.
Let’s begin by creating a test string on which we will perform our regular expression searches. The classical hello world should do it.
$test_string = 'hello world';
If we simply want to search for the word hello or world then the search pattern would look something like this:
preg_match('/hello/', $test_string);
preg_match('/world/', $test_string);
If we wish to see if the string begins with the word hello, we would simply put the ^ character in the beginning of the search pattern like this:
preg_match('/^hello/', $test_string);
Please note that regular expressions are case sensitive, the above pattern won’t match the word hElLo. If we want our pattern to be case insensitive we should apply the following modifier:
preg_match('/^hello/i', $test_string);
Notice the character i at the end of the pattern after the forward slash.
Now let’s examine a more complex search pattern. What if we want to check that the first five characters in the string are alpha numeric characters.
preg_match('/^[A-Za-z0-9]{5}/', $test_string);
Let’s dissect this search pattern. First, by using the caret character (^) we specify that the string must begin with an alpha numeric character. This is specified by [A-Za-z0-9].
A-Z means all the characters from A to Z followed by a-z which is the same except for lowercase character, this is important, because regular expressions are case sensitive. I think you’ll figure out by yourself what 0-9 means.
{5} simply tells the regex parser to count exactly five characters. If we put six instead of five, the parser wouldn’t match anything, because in our test string the word hello is five characters long, followed by a white-space character which in our case doesn’t count.
Also, this regular expression could be optimized to the following form:
preg_match('/^\w{5}/', $test_string);
\w specifies any alpha numeric characters plus the underscore character (_).
6. Useful Regex Functions
Here are a few PHP functions using regular expressions which you could use on a daily basis.
Validate e-mail. This function will validate a given e-mail address string to see if it has the correct form.
function validate_email($email_address)
{
if( !preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+
([a-zA-Z0-9\._-]+)+$/", $email_address))
{
return false;
}
return true;
}
Validate a URL
function validate_url($url)
{
return preg_match('|^http(s)?://[a-z0-9-]+(.[a-z0-9-]+)*(:[0-9]+)?
(/.*)?$|i', $url);
}
Remove repeated words. I often found repeated words in a text, such as this this. This handy function will remove such duplicate words.
function remove_duplicate_word($text)
{
return preg_replace("/s(w+s)1/i", "$1", $text);
}
Validate alpha numeric, dashes, underscores and spaces
function validate_alpha($text)
{
return preg_match("/^[A-Za-z0-9_- ]+$/", $text);
}
Validate US ZIP codes
function validate_zip($zip_code)
{
return preg_match("/^([0-9]{5})(-[0-9]{4})?$/i",$zip_code);
}
7. Regex Cheat Sheet
Because cheat sheets are cool nowadays, below you can find a PCRE cheat sheet that you can run through quickly anytime you forget something.
Meta Characters
Description
^ Marks the start of a string
$ Marks the end of a string
. Matches any single character
| Boolean OR
() Group elements
[abc] Item in range (a,b or c)
[^abc] NOT in range (every character except a,b or c)
\s White-space character
a? Zero or one b characters. Equals to a{0,1}
a* Zero or more of a
a+ One or more of a
a{2} Exactly two of a
a{,5} Up to five of a
a{5,10} Between five to ten of a
\w Any alpha numeric character plus underscore. Equals to [A-Za-z0-9_]
\W Any non alpha numeric characters
\s Any white-space character
\S Any non white-space character
\d Any digits. Equals to [0-9]
\D Any non digits. Equals to [^0-9]
Pattern Modifiers
Description
i Ignore case
m Multiline mode
S Extra analysis of pattern
u Pattern is treated as UTF-8
8. Useful Readings
15 PHP Regular Expression for Web Developers
Mastering Regular Expressions in PHP
Introduction to PHP Regex
Monday, March 11, 2013
Thursday, March 1, 2012
Create repository
This wiki document explains how to setup Subversion alias SVN on Ubuntu. The intended audience is experienced Linux users and system administrators.
Refer to this link
If you are new to Subversion, this section provides a quick introduction.
Subversion is an open source version control system. Using Subversion, you can record the history of source files and documents. It manages files and directories over time. A tree of files is placed into a central repository. The repository is much like an ordinary file server, except that it remembers every change ever made to files and directories.
Assumptions
It is assumed that you are aware of how to run Linux commands, edit files, start/stop services in an Ubuntu system. It is also assumed that Ubuntu is running, you have sudo access and you want to use Subversion software.
It is also assumed you have an internet connection.
Scope of this document
To make an SVN repository available to access using the HTTP protocol, you must install & configure web server. Apache 2 is proven to work with SVN. The installation of Apache 2 Webserver is beyond the scope of this document. (See ApacheHTTPserver.) However, the configuration of Apache 2 Webserver for SVN is covered in this document.
To access an SVN repository using HTTPS protocol, you must install & configure digital certificate in your Apache 2 web server. The installation and configuration of digital certificate is beyond the scope of this document. (See forum/server/apache2/SSL.)
Installation
Subversion is already in the main repository, so to install Subversion you can simply install the subversion package (see InstallingSoftware).
If it fails reporting dependencies, please locate the packages and install them. If it reports any other issues, please resolve them. If you cannot resolve the issue, please refer the mailing list archive of those packages.
Server Configuration
This step assumes you have installed above mentioned packages on your system. This section explains how to create SVN repository and access the project.
Create SVN Repository
There are several typical places to put a Subversion repository; most common places are: /srv/svn, /usr/local/svn and /home/svn. For clarity's sake, we'll assume we are putting the Subversion repository in /home/svn, and your project's name is simply 'myproject'
There are also several common ways to set permissions on your repository. However, this area is the most common source of errors in installation, so we will cover it thoroughly. Typically, you should choose to create a new group called 'subversion' that will own the repository directory. To do this (see [AddUsersHowto] for details):
Choose System > Administration > Users and Groups from your Ubuntu menu.
Click the 'Manage Groups' button.
Click the 'Add' button.
Name the group 'subversion'
Add yourself and www-data (the Apache user) as users to this group
(Note: in order to see www-data you may need to see FixShowAllUsers)
Click 'OK', then click 'Close' twice to commit your changes and exit the app.
You have to logout and login again before you are a member of the subversion group, and can do check ins.
Now issue the following commands:
$ sudo mkdir /home/svn
$ cd /home/svn
$ sudo mkdir myproject
The SVN repository can be created using the following command:
$ sudo svnadmin create /home/svn/myproject
And use the following commands to correct file permissions:
$ cd /home/svn
$ sudo chown -R www-data:subversion myproject
$ sudo chmod -R g+rws myproject
The last command sets gid for proper permissions on all new files added to your Subversion repository.
If you want to use WebDAV as an access method described below, repeat the chmod -R g+rws myproject command again. This is because svnadmin will create directories and files without group write access. This is no problem for read only access or using the custom svn protocol but when Apache tries to commit changes to the repository linux will deny it access. Also the owner and group are set as root. This can be changed by repeating the chown and chgrp commands listed above.
Access Methods
Subversion repositories can be accessed (checkout) through many different methods-on local disk, or through various network protocols. A repository location, however, is always a URL. The table describes how different URL schemas map to the available access methods.
Schema
Access Method
file:///
direct repository access (on local disk)
http://
Access via WebDAV protocol to Subversion-aware Apache 2 web server
https://
Same as http://, but with SSL encryption
svn://
Access via custom protocol to an svnserve server
svn+ssh://
Same as svn://, but through an SSH tunnel
In this section, we will see how to configure SVN for all these access methods. Here, we cover the basics. For more advanced usage details, you are always recommended to refer the svn book.
Direct repository access (file://)
This is the simplest of all access methods. It does not require any SVN server process to be running. This access method is used to access SVN from the same machine. The syntax is as follows:
$ svn co file:///home/svn/myproject
or
$ svn co file://localhost/home/svn/myproject
NOTE: Please note, if you do not specify the hostname, you must use three forward slashes (///). If you specify the hostname, you must use two forward slashes (//).
The repository permission is dependant on filesystem permission. If the user has read/write permission, he can checkout/commit the changes to the repository. If you set permissions as above, you can give new users the ability to checkout/commit by simply adding them to the Subversion group you added above.
Access via WebDAV protocol (http://)
To access the SVN repository via WebDAV protocol, you must configure your Apache 2 web server.
First install the following package libapache2-svn (see InstallingSoftware).
NOTE: If you have already tried to install the "dav" modules manually, package installation may fail. Simply remove all files beginning with "dav" from the mods-enabled directory, then remove and install the package again. Let the package put files in the correct place, then edit your configuration.
You must add the following snippet in your /etc/apache2/mods-available/dav_svn.conf file:
DAV svn
SVNPath /home/svn/myproject
AuthType Basic
AuthName "myproject subversion repository"
AuthUserFile /etc/subversion/passwd
Require valid-user
NOTE: The above configuration assumes that all Subversion repositories are available under /home/svn directory.
TIP: If you want the ability to browse all projects on this repository by going to the root url (http://www.serveraddress.com/svn) use the following in dav_svn.conf instead of the previous listing:
DAV svn
SVNParentPath /home/svn
SVNListParentPath On
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/subversion/passwd
Require valid-user
NOTE: If a client tries to svn update which involves updating many files, the update request might result in an error Server sent unexpected return value (413 Request Entity Too Large) in response to REPORT request, because the size of the update request exceeds the limit allowed by the server. You can avoid this error by disabling the requrest size limit by adding the line LimitXMLRequestBody 0 between the and lines.
NOTE: To limit any connection to the SVN-Server (private SVN), remove the lines and (i.e. leave only the "Require valid-user" line).
Alternatively, you can allow svn access on a per-site basis. This is done by adding the previous snippet into the desired site configuration file located in /etc/apache2/sites-available/ directory.
Once you add the above lines, you must restart apache2 web server. To restart apache2 web server, you can run the following command:
sudo /etc/init.d/apache2 restart
Next, you must create /etc/subversion/passwd file. This file contains user authentication details.
If you have just installed SVN, the passwd file will not yet exist and needs to be created using the "-c" switch. Adding any users after that should be done without the "-c" switch to avoid overwriting the passwd file.
To add the first entry, ie.. to add the first user, you can run the following command:
sudo htpasswd -c /etc/subversion/passwd user_name
It prompts you to enter the password. Once you enter the password, the user is added.
To add more users after that, you can run the following command:
sudo htpasswd /etc/subversion/passwd second_user_name
If you are uncertain whether the passwd file exists, running the command below will tell you whether the file already exists:
cat /etc/subversion/passwd
Now, to access the repository you can run the following command:
$ svn co http://hostname/svn/myproject myproject --username user_name
It prompts you to enter the password. You must enter the password configured using htpasswd command. Once it is authenticated the project is checked out. If you encounter acces denied, please remember to logout and login again for your memebership of the subversion user-group to take effect.
WARNING: The password is transmitted as plain text. If you are worried about password snooping, you are advised to use SSL encryption. For details, please refer next section.
Access via WebDAV protocol with SSL encryption (https://)
Accessing SVN repository via WebDAV protocol with SSL encryption (https://) is similar to http:// except you must install and configure the digital certificate in your Apache 2 web server.
You can install a digital certificate issued by Signing authority like Verisign. Alternatively, you can install your own self signed certificate.
This step assumes you have installed and configured digital certificate in your Apache 2 web server. Now to access SVN repository please refer the above section. You must use https:// to access the SVN repository.
Access via custom protocol (svn://)
Once the SVN repository is created, you can configure the access control. You can edit /home/svn/myproject/conf/svnserve.conf file to configure the access control.
NOTE: svnserve.conf is sensitive to whitespace, be sure not to leave any whitespace at the start of a line or it will not be able to read the file.
For example, to setup authentication you can uncomment the following lines in the configuration file:
# [general]
# password-db = passwd
After uncommenting the above lines, you can maintain the user list in passwd file. So, edit the file passwd in the same directory and add new user. The syntax is as follows:
username = password
For more details, please refer the file.
Now, to access SVN via svn:// custom protocol either from the same machine or different machine, you can run svnserver using svnserve command. The syntax is as follows:
$ svnserve -d --foreground -r /home/svn
# -d -- daemon mode
# --foreground -- run in foreground (useful for debugging)
# -r -- root of directory to serve
For more usage details, please refer,
$ svnserve --help
Once you run this command, SVN starts listening on default port (3690). To access the project repository, you must run the following command:
$ svn co svn://hostname/myproject myproject --username user_name
Based on server configuration, it prompts for password. Once it is authenticated, it checks out the code from SVN repository.
To synchronize the project repository with the local copy, you can run update sub-command. The syntax is as follows:
$ cd project_dir
$ svn update
For more details about using each SVN sub-command, you can refer the manual. For example, to learn more about co (checkout) command, please run:
$ svn help co
Start svnserve at bootup
One can start the svnserve daemon at bootup using an initd script. Look at Michał Wojciechowski Blog post for instructions and a good initd script for svnserve.
Start svnserve at bootup using xinetd
An alternative method to run svnserve at startup is to install xinetd, and then add the following line to /etc/xinetd.conf (replacing 'svnowner' and '/home/svn' with appropriate values)
svn stream tcp nowait svnowner /usr/bin/svnserve svnserve -i -r /home/svn
Access via custom protocol with SSL encryption (svn+ssh://)
It is not necessary to run the SVN server (svnserve) in order to access SVN repositories on a remote machine using this method. However, it is assumed that the SSH server is running in the remote machine with the repository and it is allowing incoming connections. To confirm, please try to login to that machine using ssh. If you can login, then everything is perfect. If you cannot login, please address it before continuing further.
The svn+ssh:// protocol is used for accessing SVN repositories with SSL encryption for secure data transfer. To access a repository using this method, run the following command:
$ svn co svn+ssh://hostname/home/svn/myproject myproject --username user_name
NOTE: You must use full path (/home/svn/myproject) to access an SVN repository using this method.
Based on the SSH server configuration, it prompts for password. You must enter the password you use to login via ssh. Once it is authenticated, it checks out the code from SVN repository.
You can also refer the SVN book for details about the svn+ssh:// protocol.
Refer to this link
If you are new to Subversion, this section provides a quick introduction.
Subversion is an open source version control system. Using Subversion, you can record the history of source files and documents. It manages files and directories over time. A tree of files is placed into a central repository. The repository is much like an ordinary file server, except that it remembers every change ever made to files and directories.
Assumptions
It is assumed that you are aware of how to run Linux commands, edit files, start/stop services in an Ubuntu system. It is also assumed that Ubuntu is running, you have sudo access and you want to use Subversion software.
It is also assumed you have an internet connection.
Scope of this document
To make an SVN repository available to access using the HTTP protocol, you must install & configure web server. Apache 2 is proven to work with SVN. The installation of Apache 2 Webserver is beyond the scope of this document. (See ApacheHTTPserver.) However, the configuration of Apache 2 Webserver for SVN is covered in this document.
To access an SVN repository using HTTPS protocol, you must install & configure digital certificate in your Apache 2 web server. The installation and configuration of digital certificate is beyond the scope of this document. (See forum/server/apache2/SSL.)
Installation
Subversion is already in the main repository, so to install Subversion you can simply install the subversion package (see InstallingSoftware).
If it fails reporting dependencies, please locate the packages and install them. If it reports any other issues, please resolve them. If you cannot resolve the issue, please refer the mailing list archive of those packages.
Server Configuration
This step assumes you have installed above mentioned packages on your system. This section explains how to create SVN repository and access the project.
Create SVN Repository
There are several typical places to put a Subversion repository; most common places are: /srv/svn, /usr/local/svn and /home/svn. For clarity's sake, we'll assume we are putting the Subversion repository in /home/svn, and your project's name is simply 'myproject'
There are also several common ways to set permissions on your repository. However, this area is the most common source of errors in installation, so we will cover it thoroughly. Typically, you should choose to create a new group called 'subversion' that will own the repository directory. To do this (see [AddUsersHowto] for details):
Choose System > Administration > Users and Groups from your Ubuntu menu.
Click the 'Manage Groups' button.
Click the 'Add' button.
Name the group 'subversion'
Add yourself and www-data (the Apache user) as users to this group
(Note: in order to see www-data you may need to see FixShowAllUsers)
Click 'OK', then click 'Close' twice to commit your changes and exit the app.
You have to logout and login again before you are a member of the subversion group, and can do check ins.
Now issue the following commands:
$ sudo mkdir /home/svn
$ cd /home/svn
$ sudo mkdir myproject
The SVN repository can be created using the following command:
$ sudo svnadmin create /home/svn/myproject
And use the following commands to correct file permissions:
$ cd /home/svn
$ sudo chown -R www-data:subversion myproject
$ sudo chmod -R g+rws myproject
The last command sets gid for proper permissions on all new files added to your Subversion repository.
If you want to use WebDAV as an access method described below, repeat the chmod -R g+rws myproject command again. This is because svnadmin will create directories and files without group write access. This is no problem for read only access or using the custom svn protocol but when Apache tries to commit changes to the repository linux will deny it access. Also the owner and group are set as root. This can be changed by repeating the chown and chgrp commands listed above.
Access Methods
Subversion repositories can be accessed (checkout) through many different methods-on local disk, or through various network protocols. A repository location, however, is always a URL. The table describes how different URL schemas map to the available access methods.
Schema
Access Method
file:///
direct repository access (on local disk)
http://
Access via WebDAV protocol to Subversion-aware Apache 2 web server
https://
Same as http://, but with SSL encryption
svn://
Access via custom protocol to an svnserve server
svn+ssh://
Same as svn://, but through an SSH tunnel
In this section, we will see how to configure SVN for all these access methods. Here, we cover the basics. For more advanced usage details, you are always recommended to refer the svn book.
Direct repository access (file://)
This is the simplest of all access methods. It does not require any SVN server process to be running. This access method is used to access SVN from the same machine. The syntax is as follows:
$ svn co file:///home/svn/myproject
or
$ svn co file://localhost/home/svn/myproject
NOTE: Please note, if you do not specify the hostname, you must use three forward slashes (///). If you specify the hostname, you must use two forward slashes (//).
The repository permission is dependant on filesystem permission. If the user has read/write permission, he can checkout/commit the changes to the repository. If you set permissions as above, you can give new users the ability to checkout/commit by simply adding them to the Subversion group you added above.
Access via WebDAV protocol (http://)
To access the SVN repository via WebDAV protocol, you must configure your Apache 2 web server.
First install the following package libapache2-svn (see InstallingSoftware).
NOTE: If you have already tried to install the "dav" modules manually, package installation may fail. Simply remove all files beginning with "dav" from the mods-enabled directory, then remove and install the package again. Let the package put files in the correct place, then edit your configuration.
You must add the following snippet in your /etc/apache2/mods-available/dav_svn.conf file:
DAV svn
SVNPath /home/svn/myproject
AuthType Basic
AuthName "myproject subversion repository"
AuthUserFile /etc/subversion/passwd
Require valid-user
NOTE: The above configuration assumes that all Subversion repositories are available under /home/svn directory.
TIP: If you want the ability to browse all projects on this repository by going to the root url (http://www.serveraddress.com/svn) use the following in dav_svn.conf instead of the previous listing:
DAV svn
SVNParentPath /home/svn
SVNListParentPath On
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/subversion/passwd
Require valid-user
NOTE: If a client tries to svn update which involves updating many files, the update request might result in an error Server sent unexpected return value (413 Request Entity Too Large) in response to REPORT request, because the size of the update request exceeds the limit allowed by the server. You can avoid this error by disabling the requrest size limit by adding the line LimitXMLRequestBody 0 between the
NOTE: To limit any connection to the SVN-Server (private SVN), remove the lines
Alternatively, you can allow svn access on a per-site basis. This is done by adding the previous snippet into the desired site configuration file located in /etc/apache2/sites-available/ directory.
Once you add the above lines, you must restart apache2 web server. To restart apache2 web server, you can run the following command:
sudo /etc/init.d/apache2 restart
Next, you must create /etc/subversion/passwd file. This file contains user authentication details.
If you have just installed SVN, the passwd file will not yet exist and needs to be created using the "-c" switch. Adding any users after that should be done without the "-c" switch to avoid overwriting the passwd file.
To add the first entry, ie.. to add the first user, you can run the following command:
sudo htpasswd -c /etc/subversion/passwd user_name
It prompts you to enter the password. Once you enter the password, the user is added.
To add more users after that, you can run the following command:
sudo htpasswd /etc/subversion/passwd second_user_name
If you are uncertain whether the passwd file exists, running the command below will tell you whether the file already exists:
cat /etc/subversion/passwd
Now, to access the repository you can run the following command:
$ svn co http://hostname/svn/myproject myproject --username user_name
It prompts you to enter the password. You must enter the password configured using htpasswd command. Once it is authenticated the project is checked out. If you encounter acces denied, please remember to logout and login again for your memebership of the subversion user-group to take effect.
WARNING: The password is transmitted as plain text. If you are worried about password snooping, you are advised to use SSL encryption. For details, please refer next section.
Access via WebDAV protocol with SSL encryption (https://)
Accessing SVN repository via WebDAV protocol with SSL encryption (https://) is similar to http:// except you must install and configure the digital certificate in your Apache 2 web server.
You can install a digital certificate issued by Signing authority like Verisign. Alternatively, you can install your own self signed certificate.
This step assumes you have installed and configured digital certificate in your Apache 2 web server. Now to access SVN repository please refer the above section. You must use https:// to access the SVN repository.
Access via custom protocol (svn://)
Once the SVN repository is created, you can configure the access control. You can edit /home/svn/myproject/conf/svnserve.conf file to configure the access control.
NOTE: svnserve.conf is sensitive to whitespace, be sure not to leave any whitespace at the start of a line or it will not be able to read the file.
For example, to setup authentication you can uncomment the following lines in the configuration file:
# [general]
# password-db = passwd
After uncommenting the above lines, you can maintain the user list in passwd file. So, edit the file passwd in the same directory and add new user. The syntax is as follows:
username = password
For more details, please refer the file.
Now, to access SVN via svn:// custom protocol either from the same machine or different machine, you can run svnserver using svnserve command. The syntax is as follows:
$ svnserve -d --foreground -r /home/svn
# -d -- daemon mode
# --foreground -- run in foreground (useful for debugging)
# -r -- root of directory to serve
For more usage details, please refer,
$ svnserve --help
Once you run this command, SVN starts listening on default port (3690). To access the project repository, you must run the following command:
$ svn co svn://hostname/myproject myproject --username user_name
Based on server configuration, it prompts for password. Once it is authenticated, it checks out the code from SVN repository.
To synchronize the project repository with the local copy, you can run update sub-command. The syntax is as follows:
$ cd project_dir
$ svn update
For more details about using each SVN sub-command, you can refer the manual. For example, to learn more about co (checkout) command, please run:
$ svn help co
Start svnserve at bootup
One can start the svnserve daemon at bootup using an initd script. Look at Michał Wojciechowski Blog post for instructions and a good initd script for svnserve.
Start svnserve at bootup using xinetd
An alternative method to run svnserve at startup is to install xinetd, and then add the following line to /etc/xinetd.conf (replacing 'svnowner' and '/home/svn' with appropriate values)
svn stream tcp nowait svnowner /usr/bin/svnserve svnserve -i -r /home/svn
Access via custom protocol with SSL encryption (svn+ssh://)
It is not necessary to run the SVN server (svnserve) in order to access SVN repositories on a remote machine using this method. However, it is assumed that the SSH server is running in the remote machine with the repository and it is allowing incoming connections. To confirm, please try to login to that machine using ssh. If you can login, then everything is perfect. If you cannot login, please address it before continuing further.
The svn+ssh:// protocol is used for accessing SVN repositories with SSL encryption for secure data transfer. To access a repository using this method, run the following command:
$ svn co svn+ssh://hostname/home/svn/myproject myproject --username user_name
NOTE: You must use full path (/home/svn/myproject) to access an SVN repository using this method.
Based on the SSH server configuration, it prompts for password. You must enter the password you use to login via ssh. Once it is authenticated, it checks out the code from SVN repository.
You can also refer the SVN book for details about the svn+ssh:// protocol.
Recursively remove all .svn directories window
FOR /F "tokens=*" %%G IN ('DIR /B /AD /S *.svn*') DO RMDIR /S /Q "%%G"
Subversion access control
Access Control for Subversion with Apache2 and Authz
My group project at University now consists of three smaller projects that provide an overall RSS service. I want to let the guys work on these, while still letting me keep my other coursework jut accessible to me. At the moment, I just have basic http authentication set-up which isn't so great for pulling off what I want.
Please welcome on stage the Apache2 mod, authz_svn...
If you followed my other howto, you'll have all the pre-requisites for this.
First of all, we need to create an Access Control file.
sudo nano /etc/apache2/svn_access_control
In this file, you'll want to put some rules. I'll first of all go over these and then provide some examples.
Permissions
There are only two types of permission:
Read only - r - a user can check-out a copy of a project.
Read and Write - rw - a user can check-out and commit changes to a project.
Users
These are the same usernames that you have set in your password file that you created in the previous howto. You can always add more users to this file using:
sudo htpasswd2 -m /etc/apache2/dav_svn.passwd bill
When prompted, enter the password for the user.
Repository Location
You specify the above rules in certain locations for the repository. These go between square brackets.
[/]
The above will specify rules for the root of the repository.
[/wowapp/trunk]
The above will specify rules for a project named 'wowapp' in the trunk location.
User Groups
You can create groups of users and then use those for rules. You do this under a special heading in square brackets:
[groups]
mygroup = dave, mike
This will create a group called 'mygroup' which 'dave' and 'mike' belongs to.
And now for some examples.
Examples
[groups]
team = bob, bill
devteam = bob, barry, brett
[/]
@team = r
bob = rw
[/wowapp/trunk]
@team = r
@devteam = rw
brenda = rw
In this example:
Created a group team which has two members; bob and bill.
Created another group, called devteam which has three members; bob, barry, brett.
In the root of the repository, I've given the group team read permissions.
Also, in the root, bob has read and write permissions.
In the trunk of wowapp, the group team has read permission.
Also, the devteam group has read and write permissions.
And another user, called brenda has read and write permissions.
Once you've created your desired access controll file, save the changes in nano by hitting CTRL O, hit enter to save the name, then CTRL X to quit Nano.
We just need to now link this access control file with our Subversion set-up.
sudo nano /etc/apache2/mods-enabled/dav_svn.conf
Here's the example from the previous how-to:
DAV svn
SVNPath /home/svn
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/apache2/dav_svn.passwd
Require valid-user
All you need to add is the following line:
AuthzSVNAccessFile /etc/apache2/svn_access_control
So that the file looks like this:
DAV svn
SVNPath /home/svn
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/apache2/dav_svn.passwd
AuthzSVNAccessFile /etc/apache2/svn_access_control
Require valid-user
Save the file, and then restart Apache2:
sudo /etc/init.d/apache2 restart
You should now have access control working for Subversion over Apache2.
My group project at University now consists of three smaller projects that provide an overall RSS service. I want to let the guys work on these, while still letting me keep my other coursework jut accessible to me. At the moment, I just have basic http authentication set-up which isn't so great for pulling off what I want.
Please welcome on stage the Apache2 mod, authz_svn...
If you followed my other howto, you'll have all the pre-requisites for this.
First of all, we need to create an Access Control file.
sudo nano /etc/apache2/svn_access_control
In this file, you'll want to put some rules. I'll first of all go over these and then provide some examples.
Permissions
There are only two types of permission:
Read only - r - a user can check-out a copy of a project.
Read and Write - rw - a user can check-out and commit changes to a project.
Users
These are the same usernames that you have set in your password file that you created in the previous howto. You can always add more users to this file using:
sudo htpasswd2 -m /etc/apache2/dav_svn.passwd bill
When prompted, enter the password for the user.
Repository Location
You specify the above rules in certain locations for the repository. These go between square brackets.
[/]
The above will specify rules for the root of the repository.
[/wowapp/trunk]
The above will specify rules for a project named 'wowapp' in the trunk location.
User Groups
You can create groups of users and then use those for rules. You do this under a special heading in square brackets:
[groups]
mygroup = dave, mike
This will create a group called 'mygroup' which 'dave' and 'mike' belongs to.
And now for some examples.
Examples
[groups]
team = bob, bill
devteam = bob, barry, brett
[/]
@team = r
bob = rw
[/wowapp/trunk]
@team = r
@devteam = rw
brenda = rw
In this example:
Created a group team which has two members; bob and bill.
Created another group, called devteam which has three members; bob, barry, brett.
In the root of the repository, I've given the group team read permissions.
Also, in the root, bob has read and write permissions.
In the trunk of wowapp, the group team has read permission.
Also, the devteam group has read and write permissions.
And another user, called brenda has read and write permissions.
Once you've created your desired access controll file, save the changes in nano by hitting CTRL O, hit enter to save the name, then CTRL X to quit Nano.
We just need to now link this access control file with our Subversion set-up.
sudo nano /etc/apache2/mods-enabled/dav_svn.conf
Here's the example from the previous how-to:
DAV svn
SVNPath /home/svn
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/apache2/dav_svn.passwd
Require valid-user
All you need to add is the following line:
AuthzSVNAccessFile /etc/apache2/svn_access_control
So that the file looks like this:
DAV svn
SVNPath /home/svn
AuthType Basic
AuthName "Subversion Repository"
AuthUserFile /etc/apache2/dav_svn.passwd
AuthzSVNAccessFile /etc/apache2/svn_access_control
Require valid-user
Save the file, and then restart Apache2:
sudo /etc/init.d/apache2 restart
You should now have access control working for Subversion over Apache2.
Subscribe to:
Posts (Atom)