MS SQL schema

Obtain metadata of objects of a database can add lots of flexibility. This can be obtained in the following ways:

1) INFORMATION_SCHEMA
e.g.:
select table_name as Name FROM INFORMATION_SCHEMA.Tables where TABLE_TYPE ='VIEW'

2) sysobjects
e.g.:
SELECT name FROM sysobjects WHERE xtype = 'U' -- Tables
SELECT name FROM sysobjects WHERE xtype = 'V' -- Views
SELECT name FROM sysobjects WHERE xtype = 'P' -- Stored Procedures

A Subversion Cheat Sheet

SVN can use a GUI interface such as TortoiseSVN, or a command line version such as slik SVN.

---

Setup ssh key for svn so no need to enter password each time: HOWTO: set up ssh keys

---

A short usage note for Subversion.

Often used svn commands are:

Create a Repository

UNIX: svnadmin create /path/to/repository
Windows: svnadmin create d:/path_to_repository

Checking Out a Project - svn checkout

UNIX: svn checkout file:///repository_name/project/trunk project
Windows: svn checkout file:///d:/repository_name/project/trunk project
Network: svn checkout http://host_name/svn_dir/repository_name/project/trunk project

Getting a List of Projects - svn list

UNIX: svn list --verbose file:///repository_name/project
Network: svn list --verbose http://host_name/svn_dir/repository_name/project

Reviewing Changes - svn status

UNIX: svn status

Adding New Files and Directories - svn add

UNIX: svn add file_or_dir_name

Deleting Files and Directories - svn delete

UNIX: svn delete file_or_dir_name
Network: svn delete -m "Deleting project dir" http://localhost/svn_dir/repository/project_dir

Committing Changes - svn commit

Network: svn commit -m "Saving recent changes" http://localhost/svn_dir/repository/project_dir

Updating Your Local Files - svn update

Network: svn update

Get revision number - svn info

svn info | grep Rev

Check log for the 10 most recent entries:

svn log --limit 10
or
svn log -l 10

MSSQL extended property

MSSQL extended property is used to store metadata information on database objects (tables, views, columns, indexes etc.).

- Extended Property is NOT case sensitive.
- MSDN: add, drop, update, list extended properties:
  http://msdn.microsoft.com/en-us/library/ms180047.aspx

- list all extended properties:
  SELECT * FROM sys.extended_properties;
- list all extended properties for columns in all tables:
  SELECT major_id, minor_id, t.name AS [Table Name], c.name AS [Column Name], value AS [Extended Property]
  FROM sys.extended_properties AS ep
  INNER JOIN sys.tables AS t ON ep.major_id = t.object_id 
  INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
  WHERE class = 1;
- Show all the E.P. of a specific columns in a specific table:
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'T1', 'column', 'id');
  
Add/drop/update/list the Extended Property of a column:
  
- Show an E.P. of a columns in a table:
  SELECT objtype, objname, name, value
  FROM fn_listextendedproperty ('[E.P. name]', 'schema', 'dbo', 'table', 'T1', 'column', 'id');

- Add an E.P. of a column in a table (the IF part is optional check):

  IF NOT Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', 'column', 'id'
  ))
  EXEC sp_addextendedproperty 
    @name = 'caption' 
    ,@value = 'Employee ID' 
    ,@level0type = 'schema', @level0name = dbo
    ,@level1type = 'table', @level1name = 'T1'
    ,@level2type = 'column', @level2name = id;
  GO

- Drop an E.P. of a column in a table (the IF part is optional check):

  IF Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', 'column', 'id'
  ))
  EXEC sp_dropextendedproperty 
    @name = 'caption' 
    ,@level0type = 'schema', @level0name = dbo
    ,@level1type = 'table', @level1name = 'T1'
    ,@level2type = 'column', @level2name = id;
  GO

- Update an E.P. of a column in a table:
  EXEC sp_updateextendedproperty 
    @name = N'Caption'
    ,@value = 'Employee ID must be unique.'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = T1
    ,@level2type = N'Column', @level2name = id;
  GO

Add/drop/update/list the Extended Property of a table:

- The above shows how to add/drop/update/list the E.P. of a column.
  To do the same thing for a table, just ignore the last 2 items for
  level2type/level2name. For the IF part, use NULL for the last two items. E.g.:
  IF Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', NULL, NULL
  ))
  Examples are given below.

- Drop an extended property of a table:  
  IF Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', null, null
  ))
  EXEC sp_dropextendedproperty 
    @name = 'Summary' 
    ,@level0type = 'schema' ,@level0name = dbo
    ,@level1type = 'table' ,@level1name = 'T1';
  GO
  
- Add an extended property to a table:  
  IF NOT Exists (SELECT * FROM fn_listextendedproperty(
    'Summary', 'schema', 'dbo', 'table', 'T1', null, null
  ))
  EXEC sp_addextendedproperty 
    @name = 'Summary' 
    ,@value = 'T1 table''s summary' 
    ,@level0type = 'schema' ,@level0name = dbo
    ,@level1type = 'table' ,@level1name = 'T1';
  GO  

Add/drop/update/list the Extended Property of a view or view's column:

- Same as table, except that use "view" instead of "table" as level1type.
  
Note:

- Since single quote "'" is used as delimiter, it should escaped
  by "''" if it is used in the value.
- Note that string quoted by "'" is allowed to contain new line character.
  So if a value spans multiple lines, it won't be a problem.

Perl OOP and reference

Perl can do OOP for sure. I reviewed relevant material for recent work. Use "Package" to define a class. What's interesting is the use of "bless" keyword. An object is used in the syntax of a C++ object, say you use "->" to retrieve its member variable or function.

The use of reference facilitates a lot of programming features. In Perl using a back slash "\" in front of a variable (scalar, array or hash) turns it into a reference. This can be passed to subroutines, to be used recursively etc. To cast it back and refer to the object, use $/@{}/%{} for scalar/array/hash respectively.

References:
- Object Oriented Programming in PERL

Perl - count number of occurrences

http://www.chengfu.net/2005/10/count-occurrences-perl/

The author wants to find out what's the fastest way of counting occurrences of a string in a text. e.g. '-' in “1-4-7-8-37-5-7-8-3-42″. He gives 5 ways of doing this:

1. my $size = (scalar(@{[$string =~ /-/g]}) + 1);
2. my $size = scalar(split /-/, $string);
3. my $size = (($string =~ s/-//g) + 1);
4. my $size = (($string =~ tr/-//) + 1);
5. my $size = 1; $size++ while $string =~ /-/g;

He found the speed to be 4, 3, 5, 1, 2 in the order of fastest to slowest on his machine.

Doodling in math class

Doodling in math class: http://vihart.com/doodling/

Interesting.

Perlpod

Perlpod: The Plain Old Document format.

Description about Perlpod from the above source:

Pod is a simple-to-use markup language used for writing documentation for Perl, Perl programs, and Perl modules.
Translators are available for converting Pod to various formats like plain text, HTML, man pages, and more.
Pod markup consists of three basic kinds of paragraphs: ordinary, verbatim, and command.