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.

Setup email forwarding in linux/unix

Email forwarding can be setup in an email client such as Thunderbird.

Under linux/unix, it can done by admin or by user himself, as explained here.

To do it by the user himself, he only needs to create a file .forward in his account root, and enter the forward email addresses separated by comma or new line. The .forward file should have permission 644.

An example .forward file is:

"|/usr/local/bin/procmail"
example@gmail.com

Java becomes closed source

December 9, 2010: The ASF Resigns From the JCP Executive Committee.

It seems that now Java to Oracle is as C# to Microsoft. Java becomes proprietary language of Oracle. But, C/C++/PHP/Perl/Python/Ruby are still open source.

Since Oracle also owns MySQL, don't know what will happen to MySQL. PostgreSQL is still open source though.