dbscript 1.00 released

December 30, 2009

The major new feature of version 1.00 of dbscript is the new SQL parser with the following improvements:

  • support for SQL Server 2005 and 2008 features
  • detailed log of parser results (successfully parsed statements as well as skipped clauses and text ranges)
  • parser continues after unrecognized or faulty statements
  • interpretation of sp_addextendedproperty to extract object and column descriptions (MS_Description)
  • dependency analysis of views, stored procedures, etc.

The descriptions and dependencies extracted by parsing or importing from a live database are included in SQL scripts and generated XML files, and thus available in all generated documentation. XSL style sheets have been adapted to display object and column descriptions.

Output samples of generated documentation:

Single HTML file documentation

MediaWiki documentation

ScrewTurn Wiki documentation

The new features lay the groundwork for future versions of dbscript the implement parsers for other SQL dialects (schema import from database is already supported for Oracle and PostgreSql), dependency diagrams, and more.

dbscript is available for download here.

Please send your feedback πŸ˜‰

Advertisements

CSS Link List

December 29, 2009

In a web application with its custom CSS that also defined hyperlinks, Internet Explorer would render disabled hyperlinks greyed-out, whereas Firefox showed them as normal text.

Tracking down this different behavior, I found that you can define CSS styles not only by class and tag, but also by attributes using the [attribute] notation:

*[disabled], a[disabled]:hover, a[disabled]:visited
{
color:gray;
}

If you are looking for CSS definitions for fixed headers, footers and sidebars, take a look at these sample CSS. I’m thinking about including them in the next version of dbscript.


Link List: SQL Server and Collations

December 24, 2009

Recent posts covered collation support and Unicode in SQL Server.

Just a couple of links with more information on this topic:


Collation Support in SQL Server Versions

December 22, 2009

MS SQL Server adds new collations with every version. MSDN described the naming conventions of Windows collations and lists the Windows collation names supported in SQL Server.

This is the list of collations each SQL Server version (2000, 2005, 2008) supports (all suffixes such as _BIN, _BIN2, _AI, _AS, _CI, _CS, _KS, _WS removed):

Name 2000 2005 2008
Albanian x x x
Arabic x x x
Assamese x
Azeri_Cyrillic x x
Azeri_Latin x x
Bashkir x
Bengali x
Bosnian_Cyrillic x
Bosnian_Latin x
Breton x
Chinese_Hong_Kong_Stroke x x
Chinese_PRC x x x
Chinese_PRC_Stroke x x x
Chinese_Simplified_Pinyin x
Chinese_Simplified_Stroke_Order x
Chinese_Taiwan_Bopomofo x x x
Chinese_Taiwan_Stroke x x x
Chinese_Traditional_Bopomofo x
Chinese_Traditional_Pinyin x
Chinese_Traditional_Stroke_Count x
Chinese_Traditional_Stroke_Order x
Corsican x
Croatian x x x
Cyrillic_General x x x
Czech x x x
Danish_Greenlandic x
Danish_Norwegian x x x
Dari x
Divehi x x
Estonian x x x
Finnish_Swedish x x x
French x x x
Frisian x
Georgian_Modern_Sort x x x
German_PhoneBook x x x
Greek x x x
Hebrew x x x
Hindi x
Hungarian x x x
Hungarian_Technical x x x
Icelandic x x x
Indic_General x x
Japanese x x x
Japanese_Bushu_Kakusu x
Japanese_Unicode x x x
Japanese_XJIS x
Kazakh x x
Khmer x
Korean * x x
Korean_Wansung * x x
Lao x
Latin1_General x x x
Latvian x x x
Lithuanian x x x
Lithuanian_Classic x
Macedonian_FYROM * x x
Maltese x
Maori x
Mapudungan x
Modern_Spanish x x x
Mohawk x
Nepali x
Norwegian x
Pashto x
Persian x
Polish x x x
Romanian x x x
Romansh x
Sami_Norway x
Sami_Sweden_Finland x
Serbian_Cyrillic x
Serbian_Latin x
Slovak x x x
Slovenian x x x
Syriac x x
Tamazight x
Tatar x x
Thai x x x
Tibetan x
Traditional_Spanish x x x
Turkish x x x
Turkmen x
Uighur x
Ukrainian x x x
Upper_Sorbian x
Urdu x
Uzbek_Latin x x
Vietnamese x x x
Welsh x
Yakut x

*: Different names in SQL Server 2000:Β  Korean_Wansung, Korean_Wansung_Unicode, Macedonian.

This information has been retrieved with the fn_helpcollations() function.


Splitting long text columns into multiple NVARCHAR records

December 22, 2009

Management Studio truncates strings displayed in the result grid which keeps you from viewing the whole string stored in a text column.

A question on Stack Overflow made me create this SELECT statement to split long strings into several records:

SELECT texts.id, 
       SUBSTRING(texts.text_column, number*100+1, 100)
FROM
    (SELECT texts.id, texts.text_column, 
        (99+LEN(texts.text_column))/100 AS l
     FROM texts) AS texts
OUTER APPLY
    (SELECT TOP(l) number
     FROM master.dbo.spt_values val
     WHERE name IS NULL
     ORDER BY number) n

In this example, the resulting strings are 100 characters long. The built-in table master.dbo.spt_values contains integer values from 0 to 2047.


Checking web.config on Application Start

December 16, 2009

During development, changes to configuration files are inevitable, such as adding connection strings or service bindings. When several programmers work in the same project, synching these changes manually is prone to errors, so you better have your application startup code check the settings are there.

Let’s start with the simple task: hooking the config file checker into the startup sequence in Global.asax.cs:

namespace My.Namespace
{
  public class Global: System.Web.HttpApplication
  {

    protected void Application_Start(object sender, EventArgs e)
    {
      AppChecker.Check();
    }
  }
}

In the AppChecker class, we check whatever settings we want to make sure exists.

This example will check whether required service endpoints and bindings are present in the web.config file. Since the web.config is a valid XML file (otherwise app startup will fail with an exception), we can use the System.Xml namespace to load and parse it.

namespace My.Namespace
{
  public static class AppChecker
  {
    public static void Check()
    {
      Configuration cfg = WebConfigurationManager.OpenWebConfiguration(
          HttpContext.Current.Request.ApplicationPath);
      XmlDocument WebConfig = new XmlDocument();
      WebConfig.Load(cfg.FilePath);

      XmlNode ndSvc =
        WebConfig.SelectSingleNode("/configuration/system.serviceModel");
      if (ndSvc == null)
        throw new Exception(
          "web.config misses /configuration/system.serviceModel");

      CheckContract(ndSvc, "My.Namespace.ServiceClientProxy.IFooService");
      CheckContract(ndSvc, "My.Namespace.ServiceClientProxy.IBarProcess");
    }

All service information is stored in the <system.serviceModel> section (variable ndSvc). In this section, we look up the required service proxy classes. Then we retrieve the binding from the proxy class. If either retrieval fails, the method will raise an exception.

    private static void CheckContract(XmlNode ndSvc, string Contract)
    {
      XmlNode ndEndPoint = ndSvc.SelectSingleNode(
        "client/endpoint[@contract='" + Contract + "']");
      if (ndEndPoint == null)
        throw new Exception(
          "web.config does not contain endpoint for contract " + Contract);

      XmlNode ndBinding = ndSvc.SelectSingleNode(
        "bindings/basicHttpBinding/binding[@name='" +
        ndEndPoint.Attributes["bindingConfiguration"].Value + "']");
      if (ndBinding == null)
        throw new Exception("web.config does not contain binding " +
          ndEndPoint.Attributes["bindingConfiguration"].Value +
          " for endpoint " + ndEndPoint.Attributes["contract"].Value);
    }

If your web application references the assemblies of your proxy classes directly, you may prefer the type-safe method:

    private static void CheckContract(XmlNode ndSvc, Type contract)
    {
      CheckContract(ndSvc, contract.FullName);
    }
  }
}

Transliterating Kana into Latin alphabet using T-SQL

December 14, 2009

One more example of replacing multiple strings using a CTE. After Russian / Cyrillic, it’s time for the Japanese syllabary.

SQL Server provides a couple of Japanese collations of which I found Japanese_Unicode_CI_AS useful: It is insensitive to the distinction between hiragana and katakana, but accent-sensitive to nigori and handakuten.

Let’s start with the table definitions:

CREATE TABLE #T (
    level int identity(1,1),
    old    NVARCHAR(50) COLLATE Japanese_Unicode_CI_AS,
    new    NVARCHAR(50) COLLATE Japanese_Unicode_CI_AS
)

CREATE TABLE #Data (
    data    NVARCHAR(50) COLLATE Japanese_Unicode_CI_AS
)

and the data to be translated, taken randomly from the Japanese Wikipedia:

INSERT INTO #Data (data) VALUES (N'γ²γ‚‰γŒγͺ')

INSERT INTO #Data (data) VALUES (N'γƒ’γƒ©γ‚¬γƒŠ')
INSERT INTO #Data (data) VALUES (N'とうきょう')
INSERT INTO #Data (data) VALUES (N'せんせい')
INSERT INTO #Data (data) VALUES (N'γ‚¦γ‚£γ‚­γƒšγƒ‡γ‚£γ‚’')

INSERT INTO #Data (data) VALUES (N'ウィキポータル')
INSERT INTO #Data (data) VALUES (N'ヨーロッパ')
INSERT INTO #Data (data) VALUES (N'フランス')
INSERT INTO #Data (data) VALUES (N'γ‚€γ‚Ώγƒͺγ‚’')
INSERT INTO #Data (data) VALUES (N'ブルガγƒͺγ‚’')
INSERT INTO #Data (data) VALUES (N'をパγƒͺγ‚«')
INSERT INTO #Data (data) VALUES (N'γ‚’γ‚Έγ‚’')

The CTE as described in the previous posts:

;
WITH CTE (org, calc, data, level) AS
(
    SELECT data, data, data, 1
    FROM	#Data

    UNION ALL

    SELECT CTE.org, CTE.data, CONVERT(NVARCHAR(50),
        REPLACE(CTE.data, #T.old, #T.new)), CTE.level + 1
    FROM	CTE
    INNER JOIN #T ON #T.level = CTE.level
)
SELECT DISTINCT org, data
FROM CTE
WHERE level = (SELECT MAX(LEVEL) FROM CTE c WHERE CTE.org = c.org)
OPTION (MAXRECURSION 1000)

DROP TABLE #Data
DROP TABLE #T

Since the transliteration table has more than 100 records (the default maximum recursion level is 100), the option (MAXRECURSION 1000) has to be added.

This results in the following transliteration of some Japanese kana words:

γ‚’γ‚Έγ‚’ ajia
をパγƒͺγ‚« amerika
γ‚€γ‚Ώγƒͺγ‚’ itaria
γ‚¦γ‚£γ‚­γƒšγƒ‡γ‚£γ‚’ wikipedia
ウィキポータル wikipootaru
さん san
せんせい sensei
とうきょう toukyou
γƒ’γƒ©γ‚¬γƒŠ hiragana
フランス furansu
ブルガγƒͺγ‚’ burugaria
ヨーロッパ yooroppa

This is the contents of the transliteration table:
Read the rest of this entry »