Monday, 17 January 2011

“Choose Life” For DBAs

I’m really really sorry. Someone tagged #sqlmoviequotes on Twitter and I got carried away…

SELECT TOP(1) * FROM LIFE.

SELECT TOP(1) * FROM JOB.

SELECT TOP(1) * FROM CAREER.

SELECT TOP(1) * FROM FAMILY.

SELECT TOP(1) * FROM television ORDER BY SIZE DESC

SELECT * FROM washing_machine CROSS JOIN car CROSS JOIN compact_disc_player CROSS JOIN electrical_tin_opener

SELECT * FROM health, cholestorol, dental_insurance
    WHERE health.status = 'good' and cholestorol.level < 5
   
SELECT * FROM mortgage WHERE interest_rate = 'fixed'

SELECT TOP(1) * FROM home WHERE TYPE = 'starter'

SELECT * FROM person
    INNER JOIN friendship ON person.id = friendship.person_id and friendship.friend_id = 'ME'

SELECT * FROM leisurewear
    INNER JOIN luggage ON leisurewear.color_scheme = luggage.color_scheme
   
SELECT TOP(3) * FROM lounge_furniture WHERE payment_plan = 'hire purchase'
    AND range_id IN (SELECT range_id FROM fabric_option GROUP BY range_id HAVING COUNT(*) > @RANGE_SIZE)
   
SELECT 'diy', CURRENT_USER FROM activity WHERE DATEPART(dw, activity_date) = 1 AND DATEPART(hh, activity_date) < 12

DECLARE @your_mouth INT
DECLARE junk_food CURSOR FOR SELECT * FROM FOOD WHERE TYPE = 'junk'
WHILE @@CURRENT_SHOW IN (SELECT * FROM SHOW WHERE keyword IN ('mind_numbing', 'spirit-crushing')) BEGIN
    FETCH NEXT FROM junk_food INTO @your_mouth
END

BEGIN

    sp_start_job 'brat'
    sp_start_job 'brat'
    sp_start_job 'brat'

    KILL @@SPID
END

SELECT * FROM events WHERE EventDate > GETDATE()

SELECT TOP(1) * FROM LIFE

Monday, 10 January 2011

Mapping a Drive Letter to a Subversion Repository with CollabNet, WebDrive and WebDAV

This is quite neat. As part of a business-wide agile initiative, I’m looking into solutions for storing and collaborating on documents – something that gives *me* the history and auditing capabilities of something like Subversion, but gives the rest of the team something clean and easy that fits well with current working practises.

So… mapped drive letters. Everyone knows about drive letters – “just stick it on the R: drive” is nice and easy, and as long as everyone’s R: drive points to the same place and the fileserver behind it’s getting backed up, you’re sorted. Unless you want to revert a document that’s been corrupted, or accidentally deleted, or you just want to get back an earlier revision because you realize you’ve done something dumb. Then you need to mess around with tapes and stuff, and that’s just no fun at all.

Plus, of course, we have a wiki, which isn’t much fun to edit because the constant round-tripping from WYSIWYG->markup->HTML->WYSIWYG tends to clobber newlines and formatting, but it *is* a great place to keep stuff because it doesn’t get lost.

So, requirements for document storage:

  1. As easy to use as a drive letter.
  2. Security. Windows / LDAP authentication to control who can read and who can write.
  3. Revision history – just a record of who made changes, and when.
  4. Ability to revert changes to an earlier revision
  5. HTTP accessible so you can read stuff with just a web browser.

There’s two things you can do with a list of requirements like that… speak to vendors, or hack something together yourself. You wanna speak to vendors, you go ahead; I shan’t stop you. Still here? Good. Let’s hack.

1. Install Subversion on the server.

For this, I’m using the CollabNet Subversion Edge stack – a single installer combining Subversion, Apache and the ViewVC web front-end. It’s very, very neat, and (having done this the hard way) much easier than setting up mod_dav_svn yourself. Once it’s up, use the web interface (linked from the Start menu on your server) to set up a new repo – call it doc_repo – and then verify that if you browse to http://myserver/viewvc/doc_repo/ you get the ViewVC web front-end view of your new, empty repository.

If you’re after Windows/LDAP authentication, that’s also configurable from the Subversion Edge web interface – and CollabNet has detailed notes on how this works.

image

2. Install WebDrive on your workstation.

This is a commercial package that’ll map a Windows drive letter to a WebDAV share. This is supposedly something that Windows is capable of doing natively, but I have never, ever got this to work, not even once. I would be glad to hear recommendations for free / open-source alternatives for this, since it’s currently the only bit of this set-up that costs money. There’s apparently also a netdrive.exe floating around but licensing for NetDrive seems to be a little confused.

3. Hack the Subversion config file.

On the server, open up C:\Program Files\Subversion\data\conf\svn_viewvc_httpd.conf. We’re not going to edit this file – you’ll need to find the bit that looks like:

<Location /svn/>
   DAV svn
   SVNParentPath "C:\Program Files\Subversion\data\repositories"
   SVNReposName "CollabNet Subversion Repository"
  AuthzSVNAccessFile "C:\Program Files\Subversion\data/conf/svn_access_file"
  SVNListParentPath On
  Allow from all
  AuthType Basic
  AuthName "CollabNet Subversion Repository"
  AuthBasicProvider csvn-file-users ldap-users
  Require valid-user
</Location>

and copy it. Then open up C:\Program Files\Subversion\data\conf\httpd.conf – which is the regular Apache configuration file – and paste the copied section right at the end, and make the following changes:

# Change Location to be the URL path of your WebDAV repo – I’ve used webdrive here
<Location /webdrive/>
    DAV svn
    SVNParentPath "C:\Program Files\Subversion\data\repositories"
    SVNReposName "Subversion WebDAV"
    AuthzSVNAccessFile "C:\Program Files\Subversion\data/conf/svn_access_file"
    SVNListParentPath On
    Allow from all
    AuthType Basic
    AuthName "Document Repository"
    AuthBasicProvider csvn-file-users ldap-users
    Require valid-user
# Add the two lines below
    ModMimeUsePathInfo on
    SVNAutoversioning on
</Location>

Now restart the web server (using ApacheMonitor.exe from C:\Program Files\Subversion\bin\ on your server) and check that you can see http://myserver/webdrive/ in a normal Web browser – the screen should say “Collection of Repositories” with your doc_repo repository listed underneath.

4. Connect WebDrive to your new WebDEV-Enabled Repository

Nearly there. Finally, fire up WebDrive on your workstation and create a new connection. Enter the Site Address/URL as http://myserver/webdrive/doc_repo/ – note that you must put the repo name in the URL otherwise WebDrive will complain with an error something like:

Unable to connect to server, error information below

Error: Socket receive failure (4507)
Operation: Connecting to server
Winsock Error: WSAECONNRESET (10054)

Correct settings will look something like this:

image

Hit Connect, and Windows explorer will fire up a new N: drive window pointing at your repo.

5. Witness the Awesome Power of Autoversioning!

Create an empty folder, create a text file in it, then browse to http://myserver/viewvc/doc_repo/ and you should see your new folder, and file, along with the Subversion history recording who created the file and when:

image

I like that a lot.