|
|
|
|
Removing Event Handlers using Reflection[^]
I planned to look at the solution in the link but I was looking to remove the event handlers in one particular scenario and I ended up using the code below:
// Ensure that there is only one eventHandler instance assigned to the BalloonTipClicked; can be extended to EventHandler[]
private EventHandler _balloonTipClickedEventHandler;
private EventHandler BalloonTipClickedEventHandler
{
get { return _balloonTipClickedEventHandler; }
set
{
niNotifier.BalloonTipClicked -= _balloonTipClickedEventHandler;
if (value != null)
{
_balloonTipClickedEventHandler = value;
niNotifier.BalloonTipClicked += _balloonTipClickedEventHandler;
}
}
}
private void Notify(int timeout, string title, string message, EventHandler eventHandler)
{
BalloonTipClickedEventHandler = eventHandler;
niNotifier.ShowBalloonTip(timeout, title, message, ToolTipIcon.Info);
}
modified 4-Nov-11 11:25am.
|
|
|
|
|
|
|
There are four ways to set an application to auto start upon user login in a Windows environment.
- Add a link to the HKCU Run key in
- Add a link to the HKLM Run key in
- Add a link to the Current User's Startup folder
- Add a link to All Users' Startup folder
Note: Special permissions may be required when setting a link outside the Current User context.
|
|
|
|
|
|
Context | Knowldege Center Site | Need | Allow for pages with predefined styles to be added | Feature | Support a template engine in combination with a set of stylesheets | Requirement | Implement an editor for manipulating text, images, and tables without interfering with stylesheets' fonts, colors, alignments, and overall structure. |
There are a number of available WYSIWYG editors, such as CKEditor[^] and TinyMCE[^]. However, with their full spectrum of functionality, they are heavy-weight and detract from the predictability of the content layout. While a much simpler tool is required, it seems uneffective to implement one from scratch.
It was decided to use the ExtJS Ext.form.HtmlEditor[^] component as the system already uses the ExtJS framework. But in order for this to work, the component had to be configured correctly and extended to handle:
The copy and paste problem turn out to be a little tricky. There are a lot of bits and pieces of information on the web that, when collected, seemed random (and inconsitent); e.g.,
cleanWordHtml: function(html){
html = html.replace(/<!--[\s\S]*-->/g,"");
html = html.replace(/<o:p>\s*<\/o:p>/g, "") ;
html = html.replace(/<o:p>.*?<\/o:p>/g, " ") ;
html = html.replace( /\s*mso-[^:]+:[^;"]+;?/gi, "" ) ;
html = html.replace( /\s*MARGIN: 0cm 0cm 0pt\s*;/gi, "" ) ;
html = html.replace( /\s*MARGIN: 0cm 0cm 0pt\s*"/gi, "\"" ) ;
html = html.replace( /\s*TEXT-INDENT: 0cm\s*;/gi, "" ) ;
html = html.replace( /\s*TEXT-INDENT: 0cm\s*"/gi, "\"" ) ;
html = html.replace( /\s*TEXT-ALIGN: [^\s;]+;?"/gi, "\"" ) ;
html = html.replace( /\s*PAGE-BREAK-BEFORE: [^\s;]+;?"/gi, "\"" ) ;
html = html.replace( /\s*FONT-VARIANT: [^\s;]+;?"/gi, "\"" ) ;
html = html.replace( /\s*tab-stops:[^;"]*;?/gi, "" ) ;
html = html.replace( /\s*tab-stops:[^"]*/gi, "" ) ;
html = html.replace( /\s*face="[^"]*"/gi, "" ) ;
html = html.replace( /\s*face=[^ >]*/gi, "" ) ;
html = html.replace( /\s*FONT-FAMILY:[^;"]*;?/gi, "" ) ;
html = html.replace(/<(\w[^>]*) class=([^ |>]*)([^>]*)/gi, "<$1$3") ;
html = html.replace( /<(\w[^>]*) style="([^\"]*)"([^>]*)/gi, "<$1$3" ) ;
html = html.replace( /\s*style="\s*"/gi, '' ) ;
html = html.replace( /<SPAN\s*[^>]*>\s* \s*<\/SPAN>/gi, ' ' ) ;
html = html.replace( /<SPAN\s*[^>]*><\/SPAN>/gi, '' ) ;
html = html.replace(/<(\w[^>]*) lang=([^ |>]*)([^>]*)/gi, "<$1$3") ;
html = html.replace( /<SPAN\s*>(.*?)<\/SPAN>/gi, '$1' ) ;
html = html.replace( /<FONT\s*>(.*?)<\/FONT>/gi, '$1' ) ;
html = html.replace(/<\\?\?xml[^>]*>/gi, "") ;
html = html.replace(/<\/?\w+:[^>]*>/gi, "") ;
html = html.replace( /<H\d>\s*<\/H\d>/gi, '' ) ;
html = html.replace( /<H1([^>]*)>/gi, '' ) ;
html = html.replace( /<H2([^>]*)>/gi, '' ) ;
html = html.replace( /<H3([^>]*)>/gi, '' ) ;
html = html.replace( /<H4([^>]*)>/gi, '' ) ;
html = html.replace( /<H5([^>]*)>/gi, '' ) ;
html = html.replace( /<H6([^>]*)>/gi, '' ) ;
html = html.replace( /<\/H\d>/gi, '<br>' ) ;
html = html.replace( /<(U|I|STRIKE)> <\/\1>/g, ' ' ) ;
html = html.replace( /<(B|b)> <\/\b|B>/g, '' ) ;
html = html.replace( /<([^\s>]+)[^>]*>\s*<\/\1>/g, '' ) ;
html = html.replace( /<([^\s>]+)[^>]*>\s*<\/\1>/g, '' ) ;
html = html.replace( /<([^\s>]+)[^>]*>\s*<\/\1>/g, '' ) ;
html = html.replace( /(<P)([^>]*>.*?)(<\/P>)/gi, "<div$2</div>" ) ;
html = html.replace( /(<font|<FONT)([^*>]*>.*?)(<\/FONT>|<\/font>)/gi, "<div$2</div>") ;
html = html.replace( /size|SIZE = ([\d]{1})/g, '' ) ;
html = html.replace(/<!--(\w|\W)+?-->/gi, '');
html = html.replace(/<title>(\w|\W)+?<\/title>/gi, '');
html = html.replace(/\s?class=\w+/gi, '');
html = html.replace(/\s+style='[^']+'/gi, '');
html = html.replace(/<(meta|link|\/?o:|\/?style|\/?div|\/?st\d|\/?head|\/?html|body|\/?body|\/?span|!\[)[^>]*?>/gi, '');
html = html.replace(/(<[^>]+>)+ (<\/\w+>)+/gi, '');
html = html.replace(/\s+v:\w+=""[^""]+""/gi, '');
html = html.replace(/(\n\r){2,}/gi, '');
// // http://www.tim-jarrett.com/labs_javascript_scrub_word.php
html = html.replace(new RegExp(String.fromCharCode(8220), 'gi'), '"'); //"
html = html.replace(new RegExp(String.fromCharCode(8221), 'gi'), '"');
html = html.replace(new RegExp(String.fromCharCode(8216), 'gi'), "'");
html = html.replace(new RegExp(String.fromCharCode(8217), 'gi'), "'");
html = html.replace(new RegExp(String.fromCharCode(8211), 'gi'), "-");
html = html.replace(new RegExp(String.fromCharCode(8212), 'gi'), "--");
html = html.replace(new RegExp(String.fromCharCode(189), 'gi'), "1/2");
html = html.replace(new RegExp(String.fromCharCode(188), 'gi'), "1/4");
html = html.replace(new RegExp(String.fromCharCode(190), 'gi'), "3/4");
html = html.replace(new RegExp(String.fromCharCode(169), 'gi'), "(C)");
html = html.replace(new RegExp(String.fromCharCode(174), 'gi'), "(R)");
html = html.replace(new RegExp(String.fromCharCode(8230), 'gi'), "...");
return html;
}
I ended up refining and splitting the set into tag replacement and character replacement sets that work in the Ext HtmlEditor component (and do not interfere with its tags).
dirtyHtmlTags: [
{regex: /<!--[\s\S]*?-->/gi, replaceVal: ""},
{regex: /<\\?\?xml[^>]*>/gi, replaceVal: ""},
{regex: /<\/?\w+:[^>]*>/gi, replaceVal: ""},
{regex: /\s*MSO[-:][^;"']*/gi, replaceVal: ""},
{regex: /\s*MARGIN[-:][^;"']*/gi, replaceVal: ""},
{regex: /\s*PAGE[-:][^;"']*/gi, replaceVal: ""},
{regex: /\s*TAB[-:][^;"']*/gi, replaceVal: ""},
{regex: /\s*LINE[-:][^;"']*/gi, replaceVal: ""},
{regex: /\s*FONT-SIZE[^;"']*/gi, replaceVal: ""},
{regex: /\s*LANG=(["'])[^"']*?\1/gi, replaceVal: ""},
{regex: /<(P|H\d)[^>]*>([\s\S]*?)<\/\1>/gi, replaceVal: "$2"},
{regex: /\s*\w+=(["'])(( |\s|;)*|\s*;+[^"']*?|[^"']*?;{2,})\1/gi, replaceVal: ""},
{regex: /<span[^>]*>( |\s)*<\/span>/gi, replaceVal: ""},
//{regex: /<([^\s>]+)[^>]*>( |\s)*<\/\1>/gi, replaceVal: ""},
// http://www.codinghorror.com/blog/2006/01/cleaning-words-nasty-html.html
{regex: /<(\/?title|\/?meta|\/?style|\/?st\d|\/?head|\/?html|\/?body|!\[)[^>]*?>/gi, replaceVal: ""},
{regex: /(\n(\r)?){2,}/gi, replaceVal: ""}
],
cleanHtml: function(html) {
if (!html) return;
Ext.each(this.dirtyHtmlTags, function(tag, idx){
html = html.replace(tag.regex, tag.replaceVal);
});
// http://www.tim-jarrett.com/labs_javascript_scrub_word.php
html = html.replace(new RegExp(String.fromCharCode(8220), 'gi'), '"'); //"
html = html.replace(new RegExp(String.fromCharCode(8221), 'gi'), '"');
html = html.replace(new RegExp(String.fromCharCode(8216), 'gi'), "'");
html = html.replace(new RegExp(String.fromCharCode(8217), 'gi'), "'");
html = html.replace(new RegExp(String.fromCharCode(8211), 'gi'), "-");
html = html.replace(new RegExp(String.fromCharCode(8212), 'gi'), "--");
html = html.replace(new RegExp(String.fromCharCode(189), 'gi'), "1/2");
html = html.replace(new RegExp(String.fromCharCode(188), 'gi'), "1/4");
html = html.replace(new RegExp(String.fromCharCode(190), 'gi'), "3/4");
html = html.replace(new RegExp(String.fromCharCode(169), 'gi'), "(C)");
html = html.replace(new RegExp(String.fromCharCode(174), 'gi'), "(R)");
html = html.replace(new RegExp(String.fromCharCode(8230), 'gi'), "...");
return Ext.ux.form.HtmlLintEditor.superclass.cleanHtml.call(this, html);
}
These regular expressions seem to cover most cases and have been successfully tested for the purposes of the project I worked on. I thought I might share the info.
See Also:
Introduction to Ranges[^]
Intercepting the Clipboard data on Paste[^]
|
|
|
|
|
|
Context
Imagine that you have few lookup tables[^]. Each one can have its own lookup method; e.g.,
string LookupCountryCodes(string query);
string LookupStateCodes(string query);
string LookupCardTypes(string query)
Likely, these methods differ only in the table /entity name that they apply to. Every time a new lookup table / entity is added, the lookup method has to be copied. Readability, extensibility, and maintainability of the code suffers.
Idea
Instead, we can define the lookup as a generic method[^]; e.g.,
string Lookup<T>(string query) where T: class, ILookupEntity, new();
Then, given a table / entity name, we can find the associated:
table / entity type
var t = Type.GetType(typeName);
if (t == null)
{
var types = from assembly in System.AppDomain.CurrentDomain.GetAssemblies()
from assemblyType in assembly.GetTypes()
where assemblyType.Name == typeName
select assemblyType;
t = types.FirstOrDefault();
}
Lookup method
var method = <owner_of_Lookup_method>.GetType()
.GetMethod("Lookup", new Type[] {typeof (string)})
.MakeGenericMethod(entityType);
where the <owner_of_Lookup_method> is either a static class or an instance of an object with a Lookup method definition.
Implementation
Putting it all together:
private Dictionary<string, Type> typeCache = new Dictionary<string, Type>();
public bool TryFindType(string typeName, out Type t)
{
lock (typeCache)
{
if (!typeCache.TryGetValue(typeName, out t))
{
t = Type.GetType(typeName);
if (t == null)
{
var types = from assembly in System.AppDomain.CurrentDomain.GetAssemblies()
from assemblyType in assembly.GetTypes()
where assemblyType.Name == typeName
select assemblyType;
t = types.FirstOrDefault();
}
typeCache[typeName] = t;
}
}
return t != null;
}
public string Get(string entityTypeName, string query)
{
Type entityType;
if (TryFindType(entityTypeName, out entityType))
{
var method = <owner_of_Lookup_method>.GetType()
.GetMethod("Lookup", new Type[] {typeof (string)})
.MakeGenericMethod(entityType);
return Convert.ToString(method.Invoke(<owner_of_Lookup_method>, new[] { query }));
}
return string.Empty;
}
See also:
How to use reflection to call generic method[^]
GetType returns null[^]
GetType returns null (2)[^]
How to invoke method with parameters[^]
Object does not match target type+Reflection[^]
Overcoming problems with MethodInfo.Invoke of methods with by-reference value type arguments[^]
modified on Wednesday, August 31, 2011 12:20 AM
|
|
|
|
|
|
|
|
|
|
Display your notes as a collapsible outline to help you keep your thoughts organized.
I like to take notes as I read (or otherwise gather) information to highlight any points of interest that I find in the subject at hand. I find that these notes are most useful when they are focused and compact. An outline lets me organize the information in layers of increasing detail and, in this context, the ability to choose which details are visible becomes important to me.
I wanted an easy/automated access to a collapsible outline layout given any layered/indented text.
The solution comprises:
Model an html <pre> tag that contains the notes as raw text
View a stylesheet for customizing the look and feel
Controller a javascript parser that creates html tags based on the notes
The outline layers are defined by the indentation. The indentation is defined by white space or tab characters. That's it.
How to use it:
1. Open the Outline.html file in a text editor, such as Notepad.
2. Paste your notes in the <pre id='rawcontent'> tag.
3. Open the Outline.html file in a browse, such as Firefox.
4. (Optional) Use Save As..Web Page, Complete to export your outline
How it works:
1. We use regular expressions and recursion to parse the notes into a nested array of details:
content: function(text){
var parse = function(arr){
var newarr = [];
for(var i = 0, len = arr.length; i < len; i++){
if (/[\S]+/.test(arr[i])){
var subarr = arr[i].replace(/([ ]{2,4}(?=[\S])|\t(?=[\S]))/gm, "").split(/[\n](?=[\S])/m);
if (subarr.length > 1){
newarr.push([subarr[0], parse(subarr.slice(1))]);
} else {
newarr.push(subarr[0]);
}
}
}
return newarr;
};
return parse(text.split(/[\n](?=[\S])/m));
}
2. We spin through the array and create the detail html tags
See the attached OutlineParser.js (https://github.com/iguigova/snippets_js/tree/master/outline[^])
Q&A:
1. Can the solution be deployed as a single file?
Yes. Modify the html file to include the contents of the css file in a <style> tag and the contents of the js files in a <script> tag. Please let me know if you need help with that and I will be happy to assist.
2. Do I need to format the notes with any special attributes?
No. As long as you have used white spaces (2, 3, or 4) or a tab for the notes hierarchy, you are ready to paste the text in the html file and view it in your browser.
3. Can I use and/or modify the files freely?
Yes. The files are provided as-is and there is no warranty but you are welcomed to use them as you wish. Please note that some of the code has been borrowed from other people as indicated in the comments; do leave the references to their work.
Your feedback is greatly appreciated.
Thank you.
|
|
|
|
|
Crack-free walls (Project Euler):
Consider the problem of building a wall out of 2 x 1 and 3 x 1 bricks (horizontal x vertical dimensions) such that, for extra strength, the gaps between horizontally-adjacent bricks never line up in consecutive layers, i.e. never form a "running crack".
There are eight ways of forming a crack-free 9 x 3 wall, written W(9,3) = 8.
Calculate W(32,10).
Let
L = length of the row
k = the number of 2 x 1 blocks in a row
l = the number of 3 x 1 blocks in a row
A(k, l) = (k + l)! / k!l! = the number of arrangements of k 2 x 1 blocks and l 3 x 1 blocks
Then
(1) The number of rows of length L is:
R = \forall (k, l) \sum A(k, l)\mid 2*k + 3*l = L
(2) Cracks can occur at positions p in [2, L - 2]. The number of cracks between two rows of length L is:
C = \forall (k, l) \forall p \forall (m, n) \sum (A(k, l)*A(k-m, l-n) \mid 2*k + 3*l = L, p\in [2, L - 2], 2*m + 3*n = p, k - m >= 0, l - n >= 0
(3) The number of ways of forming a crack-free L x 2 wall is:
W(L, 2) = R*R - R - C = R*(R - 1) - C, (i.e., there are R*R walls, R walls with duplicate rows, C walls with cracks)
(4) In order to calculate the number of walls of any height it is enough to know the pattern of increase of cracks with the increase of height.
Observations (in progress):
Let
Wi = the number of walls of height i
Si = the number of splits at height i
s = the number of splits of a row
Then
Wi+1 = Wi + Si
Rows with s = 0 do not contribute to the pattern and can be ignored
Rows with s = 1 do not contribute to the pattern and their count can be carried as a constant
L = 9, W(9, 1) = {333, 3222, 2322, 2232, 2223} = 5
| 2 | 3 | 4 | 5 | 6 | 7 position
------------------------------------------------------
333 | | x | | | x | | 2
3222 | | x | | x | | x | 1
2322 | x | | | x | | x | 1
2232 | x | | x | | | x | 1
2223 | x | | x | | x | | 1
-------------------------------------------------------
| 3 | 2 | 2 | 2 | 2 | 3 number of cracks
1 | 2 | 3 | 4 | 5 | 6 | 7 wall height
-------------------------------------------------------
5 | 6 | 8 | 10 | 14 | 18 | 24 number of crack-free walls, W
| 1 | 2 | 2 | 4 | 4 | 8 number of splits, S
| 0 | 1 | 1 | 2 | 2 | 4 number of splits / 2
Graph of row relationships:
| 2322 |(1) -- | 333 |(2) -- | 2232 | (1)
| 3222 |(1) -- | 2223 |(1)
Graph of significant row relationships:
| 333 |
L = 10, W(10, 1) = {2332, 22222, 2233, 2323, 3223, 3232, 3322} = 7
| 2 | 3 | 4 | 5 | 6 | 7 | 8 position
------------------------------------------------------
2332 | x | | | x | | | x | 0
22222 | x | | x | | x | | x | 1
2233 | x | | x | | | x | | 2
2323 | x | | | x | | x | | 1
3223 | | x | | x | | x | | 1
3232 | | x | | x | | | x | 1
3322 | | x | | | x | | x | 2
-------------------------------------------------------
| 4 | 3 | 2 | 4 | 2 | 3 | 4 number of cracks
1 | 2 | 3 | 4 | 5 | 6 wall height
-------------------------------------------------------
7 | 8 | 12 | 18 | 28 | 44 number of crack-free walls, W
| 2 | 4 | 6 | 10 | 16 number of splits, S
| 1 | 2 | 3 | 5 | 8 number of splits / 2
Graph of row relationships:
| 2332 |(0)
| 3223 |(1) -- | 22222 |(1)
| 3322 |(2) -- | 2233 |(2)
| |
| 2323 |(1) | 3232 |(1)
Graph of significant row relationships:
| 3322 | -- | 2233 |
L = 11, W(11, 1) = {22223, 22232, 22322, 23222, 32222, 2333, 3233, 3323, 3332} = 9
| 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 position
------------------------------------------------------
22223 | x | | x | | x | | x | | 1
22232 | x | | x | | x | | | x | 1
22322 | x | | x | | | x | | x | 2
23222 | x | | | x | | x | | x | 1
32222 | | x | | x | | x | | x | 1
2333 | x | | | x | | | x | | 1
3233 | | x | | x | | | x | | 2
3323 | | x | | | x | | x | | 2
3332 | | x | | | x | | | x | 1
-------------------------------------------------------
| 5 | 4 | 3 | 4 | 4 | 3 | 4 | 5 number of cracks
1 | 2 | 3 | 4 | 5 | 6 wall height
-------------------------------------------------------
9 | 12 | 18 | 28 | | number of crack-free walls, W
| 3 | 6 | 10 | | number of splits, S
| 1 | 3 | 5 | | number of splits / 2
Graph of row relationships:
| 22223 |(1) -- | 32222 |(1)
| 2333 |(1) -- | 3332 |(1)
| 3323 |(2) -- | 22322 |(2) -- | 3233 |(2)
| |
| 23222 |(1) | 22232 |(1)
Graph of significant row relationships:
| 3323 | -- | 22322 | -- | 3233 |
Calculations:
1 import math
2
3 def get_factors(length):
4
5 factors = [(length/2 - length%2, length%2)]
6 while factors[0][0] >= 3:
7 factors[0:0] = (factors[0][0] - 3, factors[0][1] + 2),
8 return factors
9
10 def count_arrangements(x, y):
11 return math.factorial(x + y) / (math.factorial(x) * math.factorial(y))
12
13 def count_rows(rows):
14
15 return sum([count_arrangements(row[0], row[1]) for row in rows])
16
17 def count_cracks(length, rows):
18 return sum([count_arrangements(cracks[0], cracks[1])*count_arrangements(row[0]-cracks[0], row[1]-cracks[1]) for row in rows for c in range(2, length - 1) for cracks in get_factors(c) if row[0] - cracks[0] >= 0 if row[1] - cracks[1] >= 0])
19
20
21
22 def get_rows(length, rows = [[0]]):
23 if (length > 1):
24 if (len(rows[0]) < length):
25 r = [row + [1] for row in rows if ((row[-2:] == [0, 0]) or (row[-2:] == [1, 0]) or (row[-2:] == [0]))]
26
27 if (len(rows[0]) < length - 1):
28 r.extend([row + [0] for row in rows if ((row[-1:] == [1]) or (row[-2:] == [1, 0]) or (row[-2:] == [0]))])
29
30 return get_rows(length, r)
31 else:
32 return rows
33 return []
34
35
36 def count_splits(rows):
37 return sum([len(filter(lambda z: z == 1, [count_cracked(row1, row2) for row2 in rows])) - 1 for row1 in rows])
38
39 def count_cracked(row1, row2):
40 if len(row1) == len(row2):
41 return len(filter(lambda z: z > 1, map(lambda x, y: x + y, row1, row2)))
42
43
44 def count_walls(length, height):
45 pass
46
47
48 def get_time(fcall, i):
49 import timeit
50 t = timeit.Timer("e205_07.%s" % fcall, "import e205_07")
51 print "%s, %d calls/batch" % (t.repeat(3, i), i)
52
53
54 if __name__=='__main__':
55
56
modified on Tuesday, August 31, 2010 3:35 PM
|
|
|
|
|
(originally posted on July 17, 2009)
I thought that this may be useful to somebody…
My Visual Studio 2005 was getting slower and slower. Today it was freezing for 1-2 minutes every time I saved. So I searched the internet and eventually ran FileMon.
It turns out that each time I save, the IDE queries WebsiteCache folder in C:\Documents and Settings\iguigova\Local Settings\Application Data\Microsoft\WebsiteCache.
Then I spent at least an hour to delete its contents of about 80 000 folders being accumulated since December (I even took a snapshot of it and had to use the DOS Prompt to get rid of all the files).
The issue has been raised with Microsoft: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=347228[^]
Because I could not find a setting on the IDE that controls this behavior, I created a batch file to run every night to clean WebsiteCache:
rmdir /s /q "C:\Documents and Settings\<your username>\Local Settings\Application Data\Microsoft\WebsiteCache"
mkdir "C:\Documents and Settings\<your username>\Local Settings\Application Data\Microsoft\WebsiteCache"
I also cleaned some temp files and the RecycleBin and everything runs smoother now.
|
|
|
|
|
MSSQL Server 2005 provides native support for the XML data type, and new methods to directly parse and read the data.
The following two articles discuss the MSSQL Server 2005 XML capabilities:
http://www.setfocus.com/TechnicalArticles/Articles/sql-server-2005-xml.aspx[^]http://www.15seconds.com/Issue/050803.htm[^]
And here is a basic example:
DECLARE @XMLText XML
SET @XMLText = '
<Customers>
<Customer>
<FirstName>Kevin</FirstName>
</Customer>
<Customer>
<FirstName>Steve</FirstName>
</Customer>
</Customers>'
SELECT @XMLText.query('/Customers/Customer/FirstName')
Now let's assume that we have a table (#MAP ) that contains a xml mapping to another table (#NODE ). In order to validate the mappings, we need to check if a node referenced in a xml map exists in the #NODE table.
A solution is to use a cursor.
And here is a basic example of a cursor:
DECLARE @tablename sysname
DECLARE tables_cursor CURSOR FOR
SELECT name
FROM sys.objects
WHERE type = 'U' AND UPPER(NAME) LIKE UPPER('%%')
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('SELECT Top 1 * FROM ' + @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
CLOSE tables_cursor
DEALLOCATE tables_cursor
However, since cursors are the slowest way to access data inside MSSQL Server (http://www.sqlteam.com/article/cursors-an-overview[^]), the following snippet includes both the cursor and the set-based way to scroll through and validate the mappings:
CREATE TABLE #NODE
(
ID INT IDENTITY,
NAME NVARCHAR(MAX)
)
INSERT INTO #NODE VALUES ('TESTNODE')
CREATE TABLE #MAP
(
ID INT IDENTITY,
XML_TEXT NVARCHAR(MAX),
IS_ENABLED BIT
)
INSERT INTO #MAP VALUES ('<MAP><INPUT><NODE><ID>1</ID></NODE></INPUT><OUTPUT>1234</OUTPUT></MAP>', 1)
INSERT INTO #MAP VALUES ('<MAP><INPUT><NODE><ID>11</ID></NODE></INPUT><OUTPUT>1234</OUTPUT></MAP>', 1)
UPDATE #MAP SET IS_ENABLED = 0
FROM #MAP M
LEFT JOIN #NODE N ON N.ID = ISNULL((CAST(M.XML_TEXT AS XML)).value('(//NODE/ID)[1]', 'INT'), 0)
WHERE N.ID IS NULL
DROP TABLE #MAP
DROP TABLE #NODE
|
|
|
|
|
On Update
http://haacked.com/archive/2004/02/28/sql-auto-increment.aspx[^]
On Insert
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/e021ead3-5dd4-4f2b-a79e-a9258384f313[^]
CREATE TABLE #NODE
(
ID INT IDENTITY,
DOMAIN_ID INT,
NAME VARCHAR(32)
)
INSERT INTO #NODE VALUES (100, 'Bloggers')
INSERT INTO #NODE VALUES (100, 'Vloggers')
INSERT INTO #NODE VALUES (500, 'Joe')
INSERT INTO #NODE VALUES (500, 'Jane')
INSERT INTO #NODE VALUES (500, 'Bob')
CREATE TABLE #NODE_LINKS
(
ID INT IDENTITY,
RANK INT,
LEFT_NODE_ID INT,
RIGHT_NODE_ID INT
)
DECLARE @RANK_OFFSET INT
SELECT @RANK_OFFSET = COUNT(ID) FROM #NODE WHERE DOMAIN_ID = 500
INSERT INTO #NODE_LINKS (RANK, LEFT_NODE_ID, RIGHT_NODE_ID)
SELECT (ROW_NUMBER() OVER (ORDER BY Jobs.ID) - 1) % @RANK_OFFSET, Jobs.ID, Personnel.ID
FROM #NODE Jobs
JOIN #NODE Personnel ON Personnel.DOMAIN_ID = 500
WHERE Jobs.DOMAIN_ID = 100
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS COUNTER, * FROM #NODE_LINKS ORDER BY LEFT_NODE_ID
DROP TABLE #NODE_LINKS
DROP TABLE #NODE
Good Luck!
|
|
|
|
|
Setup:
A data from one module is passed into another so that Module 1 takes a (flat) file as input and feeds an xml structure into Module 2. While Module 2 is internal, Module 1 is designed to handle different customer inputs into the system. However, it is not always possible to generate the expected Module 2 xml structure from the flat file in one step.
|Customer| -> file -> |Module 1| -> xml(products-product-detail) -> |Module 2|
There are at least two ways to accomodate the (multi-step) translation process:
- Add re-parsing logic directly in the code - not smart enough as we do not know what input formats will need to be supported by Module 1 in the future
- Apply customizable xsl transformations - providing decoupled solution with a reliable techology
For example:
Given a list of details, we can group them into product(s) in order to conform to the expected Module 2 hierarchy.
List of details:
='1.0'="iso-8859-1"
<PRODUCTS>
<DETAIL>
<DESC>D1</DESC>
<PRODUCT_NAME>APL</PRODUCT_NAME>
</DETAIL>
<DETAIL>
<DESC>D2</DESC>
<PRODUCT_NAME>ANL</PRODUCT_NAME>
</DETAIL>
<DETAIL>
<DESC>D3</DESC>
<PRODUCT_NAME>APL</PRODUCT_NAME>
</DETAIL>
</PRODUCTS>
List of details grouped by product:
='1.0'="iso-8859-1"
<PRODUCTS>
<PRODUCT>
<PRODUCT_NAME>ANL</PRODUCT_NAME>
<DETAIL>
<DESC>D2</DESC>
<PRODUCT_NAME>ANL</PRODUCT_NAME>
</DETAIL>
</PRODUCT>
<PRODUCT>
<PRODUCT_NAME>APL</PRODUCT_NAME>
<DETAIL>
<DESC>D1</DESC>
<PRODUCT_NAME>APL</PRODUCT_NAME>
</DETAIL>
<DETAIL>
<DESC>D3</DESC>
<PRODUCT_NAME>APL</PRODUCT_NAME>
</DETAIL>
</PRODUCT>
</PRODUCTS>
In order to achieve this grouping, we can use the Muenchian method as described here:
http://www.jenitennison.com/xslt/grouping/muenchian.html[^]
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes" encoding="iso-8859-1"/>
<xsl:key name="details-by-product" match="DETAIL" use="PRODUCT_NAME" />
<xsl:template match="PRODUCTS">
<PRODUCTS>
<xsl:for-each select="DETAIL[count(. | key('details-by-product', PRODUCT_NAME)[1]) = 1]">
<xsl:sort select="PRODUCT_NAME" />
<PRODUCT>
<PRODUCT_NAME><xsl:value-of select="PRODUCT_NAME"/></PRODUCT_NAME>
<xsl:for-each select="key('details-by-product', PRODUCT_NAME)">
<DETAIL>
<xsl:apply-templates/>
</DETAIL>
</xsl:for-each>
</PRODUCT>
</xsl:for-each>
</PRODUCTS>
</xsl:template>
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
See also:
In order to test the transformation, we can initiate the xslt in a script as described here:
http://msdn.microsoft.com/en-us/library/ms762796(VS.85).aspx[^]
var oArgs = WScript.Arguments;
if (oArgs.length == 0)
{
WScript.Echo ("Usage : cscript xslt.js xml xsl");
WScript.Quit();
}
xmlFile = oArgs(0) + ".xml";
xslFile = oArgs(1) + ".xsl";
var xsl = new ActiveXObject("MSXML2.DOMDOCUMENT.6.0");
var xml = new ActiveXObject("MSXML2.DOMDocument.6.0");
xml.validateOnParse = false;
xml.async = false;
xml.load(xmlFile);
if (xml.parseError.errorCode != 0)
WScript.Echo ("XML Parse Error : " + xml.parseError.reason);
xsl.async = false;
xsl.load(xslFile);
if (xsl.parseError.errorCode != 0)
WScript.Echo ("XSL Parse Error : " + xsl.parseError.reason);
try
{
WScript.Echo (xml.transformNode(xsl.documentElement));
}
catch(err)
{
WScript.Echo ("Transformation Error : " + err.number + "*" + err.description);
}
In order to apply the transformation at run-time, we can use the XslCompiledTransform as described here:
http://msdn.microsoft.com/en-us/library/system.xml.xsl.xslcompiledtransform_members.aspx[^]
private string ApplyStylesheet(string xml, string stylesheet)
{
if (!string.IsNullOrEmpty(stylesheet))
{
XmlReaderSettings settingsReader = new XmlReaderSettings();
settingsReader.ValidationType = ValidationType.None;
settingsReader.ProhibitDtd = false;
settingsReader.XmlResolver = null;
StringReader strReader = new StringReader(xml);
XmlReader xmlReader = XmlReader.Create(strReader, settingsReader);
StringWriter stringWriter = new StringWriter();
XmlTextWriter xmlWriter = new XmlTextWriter(stringWriter);
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(stylesheet);
xslt.Transform(xmlReader, xmlWriter);
xmlWriter.Close();
xmlReader.Close();
return stringWriter.ToString();
}
return xml;
}
Good luck!
|
|
|
|
|
|
In dealing with durations in MSSQL server, I have found the following three resources helpful:
If you need to report a duration in a certain granularity such as "Task runs for ## minutes ## seconds" , then refer to this[^] post:
SELECT *,
(TOTAL_SS) / 31536000 AS YY,
DATEDIFF(MONTH, 0, END_TIME - START_TIME) % 12 AS MM,
(TOTAL_SS % 31536000) / 604800 AS WW,
(TOTAL_SS % 31536000) / 86400 AS DD,
(TOTAL_SS % 86400) / 3600 AS HH,
(TOTAL_SS % 3600)/60 AS MI,
(TOTAL_SS % 60) AS SS
FROM (
SELECT START_TIME,
END_TIME,
END_TIME - START_TIME AS TOTAL,
DATEDIFF(ss, 0, END_TIME - START_TIME) AS TOTAL_SS
FROM Tasks
WHERE END_TIME >= START_TIME
) AS Q1
If you need to report a duration in a certain format such as "## hours 00 minutes 00 seconds", then you may need to pad the number of minutes and seconds to ensure that, for example, 3 minutes is represented as 03. Padding for integers is explained here[^]:
DECLARE @CH CHAR(1) = '0',
@LEN INT = 2,
@N INT = 1
SELECT CASE WHEN @LEN > LEN(@N) THEN REPLICATE(@CH, @LEN - LEN(@N)) ELSE '' END + CAST(@N AS VARCHAR) AS PADDED_NUMBER
If you need to extract the date part of a DATETIME timestamp, you can use the fact that internally dates are treated as FLOAT as described in more detail here[^]:
SELECT CAST (FLOOR(CAST (GETDATE() AS FLOAT)) AS DATETIME) AS DATE_ONLY
|
|
|
|
|
Excellent article, exactly what I need ! Greetings from Varna!
|
|
|
|
|