Anatoly Lubarsky

T-SQL: Get Filename from Filepath

Anatoly Lubarsky — Fri, 27 Jun 2008 03:13:00 GMT

Many times applications store operating system paths to files in the database. And sometimes need to get only Filename portion out of Filepath column in MSSQL. How to do that ?

Disclaimer: I know - it's much easier to get the filename using C#, however many times we need to get the list of filenames filtered/grouped by some criteria.

Since there is no split function analog in T-SQL - I will use SUBSTRING, REVERSE and CHARINDEX to manipulate strings:

SELECT REVERSE(SUBSTRING(REVERSE(@fpath), 0, CHARINDEX('\', REVERSE(@fpath), 1)))

Enjoy :)

ASP.NET: How to Bind RSS Feed To Repeater

Anatoly Lubarsky — Sat, 21 Jun 2008 14:18:00 GMT

Repeater seems to be the easiest, light, most simple and generic control in ASP.NET to render repeated items. For example in data driven applications it is easy to render table contents (i.e. table rows) with Repeater. All developer needs to do is to bind a custom class collection, Array, List or whatever to the Repeater.

However one can feel some lack of support in Repeater for RSS feeds in other words it is not easy to bind repeated to dynamic online XML structure like RSS.

Below I tried to demonstrate a small and simple example of how to bind RSS feed to ASP.NET Repeater control on the fly. It's implemented as a control which we can drop anywhere on ASP.NET page.

< %@ Control Language="c#" AutoEventWireup="true" EnableViewState="false" %>
< %@ Import namespace="System.Xml" %>
< script runat="server" language="C#">
    public string rssUrl = "http://blogs.x2line.com/al/rss.aspx";
    private System.Xml.XmlDocument doc;
   
    public override void DataBind()
    {
        doc = new System.Xml.XmlDocument();
        doc.Load(rssUrl);
         
        base.DataBind();
    }
         
    public void Page_Load(System.Object s, System.EventArgs e)
    {
        this.DataBind();
    }
< /script>

< asp:Repeater 
    runat="server" 
    id="rptrRss" 
    DataSource='< %# doc.SelectNodes("/rss/channel/item[position()<=5]") %>'>
    < HeaderTemplate>
        < div>
            < a href='< %# doc.SelectSingleNode("/rss/channel/image/link").InnerText %>'>
            < img 
                src='< %# doc.SelectSingleNode("/rss/channel/image/url").InnerText %>' 
                alt='< %# doc.SelectSingleNode("/rss/channel/image/title").InnerText %>' />
            < /a>
    < /HeaderTemplate>
    < ItemTemplate>
        < a 
            href='< %# (Container.DataItem as XmlNode)["link"].InnerText %>'>
            < %# (Container.DataItem as XmlNode)["title"].InnerText %>
            (< %# (Container.DataItem as XmlNode).SelectSingleNode("author |
title[not(../author)]").InnerText %>)
        < /a>
    < /ItemTemplate>
    < FooterTemplate> 
        < /div>
    < /FooterTemplate>
< /asp:Repeater>

Maybe useful to make widgets. Enjoy :)

Bathroom Stall Vandalism - 3000 Users on BEBO

Anatoly Lubarsky — Sun, 15 Jun 2008 23:11:00 GMT

Bathroom Stall Vandalism on BEBO has passed 3000 users. Previous 2000 mark was back in April - Bathroom Stall Vandalism - 2000 Users on BEBO.

Speaking of which - Bathroom Stall Vandalism Gallery was launched on Facebook this weekend. Bathroom Stall Vandalism Gallery is supposed to complement Bathroom Stall Vandalism on Facebook which growth is somewhat slow lately (comparing to that on BEBO). More Bathroom creative screenshots will be posted soon.

Enjoy

How to: Get Total Physical Memory

Anatoly Lubarsky — Sat, 14 Jun 2008 05:59:00 GMT

Q: My app needs to know how much RAM the machine it's running on has. How can this information be aquired ?

Is there any _managed_ way of getting total physical memory installed in the machine ?

You can retrieve the amount of physical memory on the computer by using WMI (You could use a WMI query, the property is "TotalPhysicalMemory" which is in the "Win32_ComputerSystem" class.). However: WMI would almost always be the slowest way to retrieve system values using C#.

Therefore I advice to use Win32 API - GlobalMemoryStatus function included in kernel32.dll:

public struct MemoryStatus 
{ 
    public uint Length;  
    public uint MemoryLoad; 
    public uint TotalPhysical; 
    public uint AvailablePhysical; 
    public uint TotalPageFile; 
    public uint AvailablePageFile; 
    public uint TotalVirtual; 
    public uint AvailableVirtual; 
} 

[DllImport("kernel32.dll")] 
public static extern void GlobalMemoryStatus(out MemoryStatus stat);

From managed code we can use it like so:

MemoryStatus stat = new MemoryStatus();
GlobalMemoryStatus(out stat);
long ram = (long)stat.TotalPhysical;

Hope this helps.

Break Machine - Street Dance

Anatoly Lubarsky — Fri, 06 Jun 2008 02:09:00 GMT

I always thought that 80s was the music era. Music industry has never reached the amount of talent since then. My theory is that in 80s it was music where most talented would try to go to express themselves. in 90s and 2000s it is IT and internet industry which possess most talented people. In 50s and 60s it was like science - physics (?).

So I decided to post some 80s classics - "Street Dance" by Break Machine (with Fred Zarr on the keyboards).

"...music such as this gets remembered and praised because it is history..."

Enjoy

Natural Key vs. Surrogate Key - Userid in Social Networks

Anatoly Lubarsky — Fri, 30 May 2008 13:36:00 GMT

All of us (almost) are the members of different social networks: Facebook, Bebo, Myspace, Hi5, etc. We ARE users. User is a real entity - a real person.

One of the problems is the ability to identify a user in a unique way across the internet.

There are 2 approaches to identify entity - both inherited from Relational database model.

Natural Key

Natural Key - a key which has logical reference to (or the same as) already existing unique entity attribute(s). The key has to identify a real entity whose existence can be verified. The most common example is person national ID number, which identifies national passport (?) in a unique way. However the ideal candidate for the persons natural key would be some fictional Global Universe ID. For social networks the best candidate so far is users EMAIL address since:

Email address is unique.
Email identifies a user in a unique way across all common social networks. The same email represents the same user in Facebook, Bebo, Myspace, etc.

The problem: usually email address cannot be shared once this or another social network decides to open its platform and share the user data due to privacy issues. For example email address is not accessible within Facebook API. The second issue is that actually the same user can have several mail addresses, which makes it less trivial to identify the user. Should we use a combination of all user emails to be able to perform identification in a unique way or just use the primary email address ?

Surrogate Key

Surrogate Key - a generated key which is not related to the entity attributes. Usually integer (INT or BIGINT, sometimes GUID). For example my userid on facebook is 500491268 and on bebo - 5282127447.

"Because the surrogate key is completely unrelated to the data of the row to which it is attached, the key is disassociated from that row. Disassociated keys are unnatural to the application's world, resulting in an additional level of indirection from which to audit."

As we can see the surrogate key is not unique across the internet. We get real problem when social networks (and their competitors) open their platforms to 3rd parties and decide to share the data. In order to identify the user in a unique way we need to scale the surrogate key to include another surrogate key which identifies the platform, but it remains still impossible to associate the same user on different platforms.

Conclusion

Recently we can see how different social networks are trying to win and dominate the social network space by trying to develop Connect Schemas in order to make connect more sites and applications and make them use their surrogate key to identify users. Recent examples are: Facebook Connect and Google Friend Connect.

I think this approach will not survive in the long run. The solution is the creation of something like Global Universe ID which identifies a person in the universe, globally and historically unique and shareable. LOL

MySql: Add a Column at a Specific Position

Anatoly Lubarsky — Wed, 28 May 2008 19:17:00 GMT

I'm playing with MySql for the 1st time in my life recently. Rather quickly I'm getting used to it mostly due to my experience with MSSQL as DBA.

While changing table structure I just came across one interesting feature and decided to post about it.

It is possible to use MySql ALTER TABLE command and add a column at a specific position within a table row using AFTER command. MSSQL for example cannot do the same within ALTER TABLE and in order to perform such manipulation one need to create and drop temporary table.

"To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table."

Example:

ALTER TABLE Product ADD Price DECIMAL(10, 2) NOT NULL AFTER ProductName;

MSSQL 2008: Spatial Data Types - Geography and Geometry

Anatoly Lubarsky — Thu, 15 May 2008 02:28:00 GMT

In addition to new DATETIME data types introduced in MSSQL 2008 there are geography and geometry data types introduced - useful for storing and manipulating geodetic data. Geometry data type is used to store flat map data like points, lines, and shapes. Geography data type takes account of the curvature of the Earth in addition. Both provide the ability to perform spatial operations on geography instances. Geography and Geometry are likely to be supported in MSSQL 2008 Express versions.

"The ability to use the geography data type in a Microsoft SQL Server database enables you to extend business applications to handle geospatial data that describes simple and complex geographic elements on the surface of the Earth."

Geography data type has functions which support parsing back from the known geo formats supported by the OGC (Open Geospatial Consortium) like text formats, binary representations, GML (Geography Markup Language - XML for geo data).

Small example of spatial operations - let's assume we need to find the area (i.e. in square meters) of the stored polygons (SalesRegion of SalesPerson table). The query:

SELECT SalesRegionName, SalesRegion.STArea()
  FROM SalesPerson

Other supported functions include STIntersects(), STUnion() for polygons and STDistance() for points.

To boost performance one can CREATE SPATIAL INDEX for the data of GEOGRAPHY or GEOMETRY column.

Enjoy :)

MSSQL 2008: What is DATETIME2

Anatoly Lubarsky — Tue, 13 May 2008 19:13:00 GMT

MSSQL 2008 introduces several new DATETIME datatypes for T-SQL. It is obvious that TIME and DATE provide the ability to store separately DATE and TIME parts of DATETIME. It was not possible to do in previous versions of MSSQL, however it was possible to get either part of the DATETIME using DATETIME functions, for example a couple of years ago I posted about how to Get only Date from DateTime.

TIME
DATE
DATETIME2
DATETIMEOFFSET

What is DATETIME2 ? It is an improved version of DATETIME. It we compare between them:

Storage: DATETIME2 takes 6-8 bytes, DATETIME takes 8 bytes.
Accuracy: DATETIME2 (100 nanosec), DATETIME (333 nanosec).
Range: DATETIME2 (0001/01/01 - 9999/12/31), DATETIME (1753/01/01 - 9999/12/31).

Sounds cool. Except strange name used for the datatype...(LOL) Seems like folks in Microsoft copied VARCHAR2 convention from Oracle which I didn't understand either.

DATETIMEOFFSET datatype is like DATETIME2 plus in addition it can store TIMEZONE in hh:mm while the actual datetime is stored in UTC. This datatype takes up to 10 bytes in storage however.

EDIT: There are also new system functions introduced: SYSDATETIME(), SYSUTCDATETIME(). Both return system time AS DATETIME2. And also SYSDATETIMEOFFSET() which returns system time as DATETIMEOFFSET.

HTH

T-SQL: LIKE Operator and Wildcard Characters

Anatoly Lubarsky — Thu, 08 May 2008 02:37:00 GMT

An interesting issue I came into recently. Suppose you have a search procedure - which consists of some SELECT query which gets a query phrase as a parameter and searches through a table column using LIKE.

The most common pattern is to add % at the beginning and the end of the search parameter so the query looks like so:

WHERE Col LIKE '%' + @param + '%'

Or you may perform concatenation before running the query as well :) However need to take into account the following. The search parameter which usually comes down to the procedure from the application user interface can contain T-SQL reserved wildcard characters such as => % [ ] _ etc. Such input will cause the query to return wrong results.

Therefore it is useful to remember to filter out or replace these characters so MSSQL will consider these characters literals. For example if we have rectangular braces => [], need to replace the opening brace => [ with the sequence => [[] before running the query.