RSS 2.0
Sign In
# Saturday, 04 November 2006

My next SQL puzzle (thanks to fabulous XQuery support in SQL Server 2005) is how to reconstruct xml from the hierarchy table. This is reverse to the "Load xml into the table".

Suppose you have:

  select Parent, Node, Name from Data

where
  (Parent, Node) - defines xml hierarchy, and
  Name - xml element name.

How would you restore original xml?


November 8, 2006 To my anonymous reader:

declare @content nvarchar(max);

set @content = '';

with Tree(Node, Parent, Name) as
(
  /* Source tree */
  select Node, Parent, Name from Data
),
Leaf(Node) as
(
  select Node from Tree
  except
  select Parent from Tree
),
NodeHeir(Node, Ancestor) as
(
  select Node, Parent from Tree
  union all
  select
    H.Node, T.Parent
  from
    Tree T inner join NodeHeir H on H.Ancestor = T.Node
),
ParentDescendants(Node, Descendats) as
(
  select
    Ancestor, count(Ancestor)
  from
    NodeHeir
  where
    Ancestor > 0
  group by
    Ancestor
),
Line(Row, Node, Text) as
(
  select
    O.Row, T.Node, O.Text
  from
    ParentDescendants D
    inner join
    Tree T
    on D.Node = T.Node
    cross apply
    (
      select D.Node * 2 - 1 Row, '<' + T.Name + '>' Text
      union all
      select (D.Node + D.Descendats) * 2, '</' + T.Name + '>'
    ) O
  union all
  select
    D.Node * 2 - 1, T.Node, '<' + T.Name + '/>'
  from
    Leaf D inner join Tree T on D.Node = T.Node
)
select top(cast(0x7fffffff as int))
  @content = @content + Text
from
  Line
order by
  Row asc, Node desc
option(maxrecursion 128);

select cast(@content as xml);

Saturday, 04 November 2006 09:50:22 UTC  #    Comments [0] -
SQL Server puzzle
# Thursday, 02 November 2006

Well, I like DasBlog Engine, however it does not allow to add new comments in our blog. This is unfortunate. :-S

In the activity log I regulary see errors related to the CAPTCHA component. For now I have switched it off. I believe we'll start getting comments at least one per year. :-)

Thursday, 02 November 2006 14:03:15 UTC  #    Comments [0] -

# Friday, 27 October 2006

Say you need to load a table from an xml document, and this table defines some hierarchy. Believe me or not, but this is not that case when its better to store xml in the table.

Let's presume the table has:

  • Node - document node id;
  • Parent - parent node id;
  • Name - node name.

The following defines a sample xml document we shall work with:

declare @content xml;

set @content = '
<document>
  <header/>
  <activity>
    <title/>
    <row/>
    <row/>
    <row/>
    <row/>
    <total/>
  </activity>
  <activity>
    <title/>
    <row/>
    <total/>
  </activity>
  <activity>
    <title/>
    <row/>
    <total/>
  </activity>
  <activity>
    <title/>
    <row/>
    <row/>
    <row/>
    <total/>
  </activity>
</document>';

How would you solved this task?

I've been spending a whole day building acceptable solution. This is probably because I'm not an SQL guru. I've found answers using cursors, openxml, pure xquery, and finally hybrid of xquery and sql ranking functions.

The last is fast, and has linear dependency of working time to xml size.

with NodeGroup(ParentGroup, Node, Name) as
(
  select 
    dense_rank() over(order by P.Node),
    row_number() over(order by N.Node),
    N.Node.value('local-name(.)', 'nvarchar(max)')
  from 
    @content.nodes('//*') N(Node) 
    cross apply
    Node.nodes('..') P(Node)
),
Node(Parent, Node, Name) as
(
  select 
    min(Node) over(partition by ParentGroup) - 1, Node, Name
  from 
    NodeGroup 
)
select * from Node order by Node;

Is there a better way? Anyone?

Friday, 27 October 2006 12:23:25 UTC  #    Comments [0] -
SQL Server puzzle
# Monday, 02 October 2006

Return a table of numbers from 0 up to a some value. I'm facing this recurring task once in several years. Such periodicity induces me to invent solution once again but using contemporary features.

November 18:

This time I have succeeded to solve the task in one select:

declare @count int;

set @count = 1000;

with numbers(value) as
(
  select 0
  union all
  select value * 2 + 1 from numbers where value < @count / 2
  union all
  select value * 2 + 2 from numbers where value < (@count - 1) / 2
)
select
  row_number() over(order by U.V) value
from
  numbers cross apply (select 1 V) U;

Do you have a better solution?

Monday, 02 October 2006 07:27:51 UTC  #    Comments [0] -
SQL Server puzzle | Tips and tricks
# Saturday, 19 August 2006

Do you think they are different? I think not too much.

Language, for a creative programmer, is a matter to express his virtues, and a hammer that brings a good salary for a skilled labourer.

Each new generation of programmers tries to prove itself. But how?

Well, C++ programmers invent their strings and smart pointers, and Java adepts (as they cannot create strings) design their springs and rubies.

It's probably all right - there is no dominance, but when I'm reading docs of someone's last pearl, I'm experiencing deja vu. On the whole, all looks as a chaotic movement.

Other time I think - how it's interesting to build something when you should not design the brick, even if you know that bricks aren't perfect.

Saturday, 19 August 2006 14:34:42 UTC  #    Comments [0] -

# Monday, 14 August 2006

I've been given a task to fix several xsls (in fact many big xsls) that worked with msxml and stoped to work with .NET. At first I thought it will be easy stuff, indeed both implementations are compatible as both implement http://www.w3.org/1999/XSL/Transform.

Well, I was wrong. After a 10 minutes I've been abusing that ignorant who has written xsls. More over, I was wondering how msxml could accept that shit.

So, come to the point. I had following xsl:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform
   version="1.0"
   xmlns:msxsl="urn:schemas-microsoft-com:xslt"
   xmlns:user="http://mycompany.com/mynamespace">

<xsl:template match="/">
<HTML dir="rtl">
...
<BODY dir="rtl">
...
 <TD width="68" dir="ltr" align="middle">
 <FONT size="1" face="David">
  <xsl:variable name="DegB" select="//*[@ZihuyMuzar='27171']" />
  </FONT>
  </TD>
...
 <TD height="19" dir="ltr" align="middle">
 <FONT size="2" face="David">
  <xsl:value-of select="$DegB/*/Degem[1]/@*[3]" />
  %
  </FONT>
  </TD>
...

I don't want to talk about "virtues" of "html" that's produced by this alleged "xsl", however about xsl itself. To my amazement msxml sees $DegB, which is declared and dies in different scope. At first I thought I was wrong: "Must be scope is defined defferently then I thought?", but no. OK, I said to myself I can fix that. I've created another xsl that elevates xsl:variable declarations to a scope where they are visible to xsl:value-of.

But that wasn't my main head ache. Some genius has decided to use third, forth, and so on attribute. What does this mean in the god's sake? How one could rely on this? I'll kill him if I'll find him! There was thousands of such @*[3]. Even if I'll see the original xml how can I be sure that msxml and .NET handle attribute collections in the same order?

There was no other way, but check this assumption. I've verified that both implementations store attributes in xml source order. This easied my pains.

To clarify implementation details I have digged in XmlDocument/XPathDocument implementations in .NET 1.1 and 2.0. I was curious how they store a set of attributes. It's interesting to know that they has decided to keep only ordered list of attributes in either implementation. This means that ordered attribute access is fast, and named access leads to list scan. In my opinion it's dubious solution. Probably the idea behind is that there is in average a few attributes to scan when one uses named access. In my case there were up to 100 attributes per element.

Conclusion?
1. Don't allow to ignorants to come near the xsl.
2. Don't design xmls that use many attributes when you're planning to use .NET xslt.

Monday, 14 August 2006 13:42:06 UTC  #    Comments [0] -

# Wednesday, 21 June 2006

Recently we were creating a BizTalk 2006 project. A map was used to normalize input data, where numbers were stored with group separators like "15,000,000.00" and text (Hebrew in our case) was stored visually like "ןודנול סלפ קנב סדיולל".

We do need to store the output data the xml way, this means numbers as "15000000.00" and Hebrew text in logical form "ללוידס בנק פלס לונדון". Well, it's understood that there are no standard functoids that deal with bidi, as there are no too many people that know about the problem in the first place. However we thought at least that there will not be problems with removing of "," in numbers.

BizTalk 2006 does not provide functoids to solve either of these tasks! To answer our needs we have designed two custom functoids.

"Replace string":
Returns a string with text Replaced using a regular expression or search string.
First parameter is a string where to Replace.
Second parameter is a string or regular expression pattern in the format /pattern/flags to Replace.
Third parameter is a string or regular expression pattern that Replaces all found matches.

"Logical to visual converter":
Converts an input "logical" string into a "visual" string.
First parameter is a string to convert.
Optional second parameter is a start embedding level (LTR or RTL).

Wednesday, 21 June 2006 11:01:49 UTC  #    Comments [0] -
BizTalk Server
# Friday, 02 June 2006

Download sample code.

In our recent .NET 2.0 GUI project our client ingenuously asked us to implement undo and redo facility. Nothing unusual nowadays, however it's still not the easiest thing in the world to implement.

Naturally you want to have this feature for a free. You do not want to invest too much time to support it. We had no much time to implement this "sugar" also. I know, I know, this is important for a user, however when you're facing a big project with a lot of logic to be implemented in short time you're starting to think it would be nice to have undo and redo logic that works independently (at least almost independently) on business logic.

Thus, what's that place where we could plug this service? - Exactly! - It's data binding layer.

When you're binding your data to controls the "Type Descriptor Architecture" is used to retrieve and update the data. Fortunately this architecture is allowing us to create a data wrapper (ICustomTypeDescriptor). Such wrapper should track property modifications of the data object thus providing undo and redo service. In short that's all, other are technical details.

Let's look at how undo and redo service goes into the action. Instead of:

  bindingSource.DataSource = data;

you have to write:

  bindingSource.DataSource = Create-UndoRedo-Wrapper(data);

There should also be a class to collect and track actions. User should create an instance of this class to implement the simplest form of code with undo and redo support:

  // Create UndoRedoManager.
  undoRedoManager = new UndoRedoManager();
  // Create undo and redo wrapper around the data object.
  // Bind controls.
  dataBindingSource.DataSource =
    new UndoRedoTypeDescriptor(data, undoRedoManager);

Now turn our attention to the implementation of the undo and redo mechanism. There are two types in the core: UndoRedoManager and IAction. The first one is to track actions, the later one is to define undo and redo actions. UndoRedoManager performs either "Do/Redo", or "Undo" operations over IAction instances. We have provided two useful implementations of the IAction interface: UndoRedoTypeDescriptor - wrapper around an object that tracks property changes, and UndoRedoList - wrapper around the IList that tracks collection modifications. Users may create their implementations of the IAction to handle other undo and redo activities.

We have created a sample application to show undo and redo in action. You can download it from here.

Friday, 02 June 2006 22:49:40 UTC  #    Comments [0] -
Announce
# Tuesday, 30 May 2006

We're building a .NET 2.0 GUI application. A part of a project is a localization. According to advices of msdn we have created *.resx files and sent them to foreign team that performs localization using WinRes tool.

Several of our user controls contained SplitContainer control. We never thought this could present a problem. Unfortunately it is!

When you're trying to open resx for a such user control you're getting:

Eror - Failed to load the resource due to the following error:
System.MissingMethodException: Constructor on type 'System.Windows.Forms.SplitterPanel' not found.

We started digging the WinRes.exe (thanks to .NET Reflector) and found the solution: we had to define the name of split container the way that its parent name appeared before (in ascending sort order) than splitter itself.

Say if you have a form "MyForm" and split container "ASplitContainer" then you should rename split container to say "_ASplitContainer".  In this case resources are stored as:

Name Parent Name
MyForm  
_ASplitContainer MyForm
_ASplitContainer.Panel1 _ASplitContainer
_ASplitContainer.Panel2 _ASplitContainer

This makes WinRes happy. :-)

Tuesday, 30 May 2006 10:13:18 UTC  #    Comments [0] -
Tips and tricks
# Tuesday, 01 November 2005

Today we had spent some time looking for samples of web-services in RPC/encoded style, and we have found a great site http://www.xmethods.com/. This site contains a lot of web-services samples in Document/literal and RPC/encoded styles. We think this link will be useful for both developers and testers.

Tuesday, 01 November 2005 10:51:37 UTC  #    Comments [0] -
Tips and tricks
# Tuesday, 18 October 2005

Actually this build is used WSE 2.0 SP3 and contains minor bug fixes. All the sources published on GotDotNet site (SCCBridge workspace). Pay attention that for now the sources include SCCProvider project that is MSSCCI implementation. I've decided to publish this project since Microsoft freely publish their interface and documentation for all VSIP.

Tuesday, 18 October 2005 16:08:21 UTC  #    Comments [6] -
SCCBridge
# Thursday, 16 June 2005

As I wrote early in our blog, the latest version SCCBridge allows accessing to SCCBridge server via proxy. Here is some explanation what you should do in order to establish such connection:

1) open RepositoryExplorer;
2) click “Settings” button;
3) set “Default service URL” property to “http://proxy:1234/BridgeServer/Repository.asmx” ;
4) set “Endpoint URL” to “http://serverhost/BridgeServer/Repository.asmx”;
5) click “OK”.

Now you can do login.

 

P.S. Pay attention that “serverhost” is a host name used by proxy to route requests to.

Thursday, 16 June 2005 11:50:43 UTC  #    Comments [1] -
SCCBridge
# Tuesday, 31 May 2005

Yesterday we had ran into following problem: how to retrieve session object from within Java web-service? The crucial point of the problem was that we are generating automatically our web-service from Java bean and this web-service works under WebSphere v5.1.1.

After some time we had spent to find acceptable solution, we have found that it's possible either to implement “session substitution” using EJB SessionBean or somehow to retrieve HttpSession instance.

The first approach has a lot of advantages before the second one, but it requires to implement bunch of EJB objects (session bean itself, home object etc.). The second approach just solve our problem for web-service via HTTP, and no more, but... it requires only few lines to be changed in Java bean code. This second approach is based on implementation of javax.xml.rpc.server.ServiceLifecyle interface for our Java bean. For details take a look at the following article: “Web services programming tips and tricks: Build stateful sessions in JAX-RPC applications“.

Actually, only two additional methods init() and destroy() were implemented. The init() method retrieves (during initialization) an ServletEndpointContext instance that is stored somewhere in private filed of the bean. Further the ServletEndpointContext.getHttpSession() is called in order to get HttpSession. So easy, so quickly - we just was pleased.

Tuesday, 31 May 2005 12:03:33 UTC  #    Comments [0] -
Tips and tricks
# Tuesday, 03 May 2005

In the class definition you often read:

Thread Safety

Any public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Any instance members are not guaranteed to be thread safe.

But do not be very susceptible for these assurances. Sometimes you read this, because of help template had this text.

Let's look at Encoding::GetEncoding static method:

public static Encoding GetEncoding(string name)
{
  return Encoding.GetEncoding(EncodingTable.GetCodePageFromName(name));
}

and

internal static int GetCodePageFromName(string name)
{
   if (name == null)
     throw new ArgumentNullException("name");

  object obj1 = EncodingTable.hashByName[name];

  if (obj1 != null)
    return (int) obj1;

  name = name.ToLower(CultureInfo.InvariantCulture);
  obj1 = EncodingTable.hashByName[name];

  if (obj1 != null)
    return (int) obj1;

  int num1 = EncodingTable.internalGetCodePageFromName(name);

  EncodingTable.hashByName[name] = num1;

  return num1;
}

You see now, in the least successful case, when our encoding isn't still cached, we shall cache it in the EncodingTable.hashByName.

I just want to point out that hashtable write operation isn't thread safe.

Tuesday, 03 May 2005 13:53:58 UTC  #    Comments [0] -
Tips and tricks
# Sunday, 20 February 2005

Eventually the SCCBridge project became really open source project. From now its sources (version 1.1.0.0) are accessible from GotDotNet site. Releases, documentation and some interesting things about the project you still can find here. Welcome to all developers who want to participate in advancing of this project.

Sunday, 20 February 2005 21:43:08 UTC  #    Comments [8] -
SCCBridge
Archive
<2006 November>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Statistics
Total Posts: 364
This Year: 0
This Month: 0
This Week: 0
Comments: 222
Locations of visitors to this page
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2019, Nesterovsky bros
All Content © 2019, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)