Saturday, December 22, 2012

Lowercase all but the first letter

Problem
User's input all uppercase names. It needs to be normal casing or it messes up
the formatting of the page.
 
Solution
Run this MySQL script  
  
 
UPDATE `table` 
SET `field` = REPLACE(CONCAT(UPPER(LEFT(`field`, 1)), 
LOWER(SUBSTRING(`field`, 2))),' , ', ', ')
 
References
1. http://stackoverflow.com/questions/7238965/mysql-update-script-problem-all-txt-is-lower-case-just-first-letter-is-upper-ca  

Saturday, December 15, 2012

SRM 563 div 2 250-pt

Problem
http://community.topcoder.com/stat?c=problem_statement&pm=12334

Approach
Check if T contains S. If so, remove it, and see if they're equal. I realized i couldn't just do a straight replace, because it would replace all occurrences. So i went to look at the documentation and discovered the replaceFirst method. This is exactly what I needed.

Code 

public class FoxAndHandleEasy
{
public String isPossible(String S, String T)
{
if (T.replaceFirst(S, "").equals(S))
return "Yes";
else
return "No";
}
}

Friday, December 14, 2012

SRM 564 div 2 250-pt

Problem
http://community.topcoder.com/stat?c=problem_statement&pm=12325 

Approach
Create two new strings:
1. reverse = append new chars to the front
2. faux = only add chars if it's different from the previous one

Looking at the documentation for appending chars to Strings in Java i relized i should be using the StringBuffer class, and then I found the reverse() function, which meant I didn't need to build the reverse string, just use the reverse() method.

So my approach was correct, I just didn't know about reverse() and didn't think to use StringBuffer right away, so I need to get more familiar with Java.


Code
public class FauxPalindromes
{
public String classifyIt(String word)
{
StringBuffer reverse = new StringBuffer(word);
reverse.reverse();
if (reverse.toString().equals(word))
return "PALINDROME";

char lastChar = word.charAt(0);
StringBuffer faux = new StringBuffer();
faux.append(lastChar);

for (int i = 1; i < word.length(); i++)
{
if (word.charAt(i) != lastChar)
{
lastChar = word.charAt(i);
faux.append(lastChar);
}
}
reverse = new StringBuffer(faux.toString());
reverse.reverse();

if (reverse.toString().equals(faux.toString()))
return "FAUX";

return "NOT EVEN FAUX";
}
}

The owner of this queue does not have sufficient privileges to work with queue

In CRM
1. Settings
2. Customizations
3. Customize the system
4. Entities
5. Email
6. Uncheck "Automatically move records to the owner's default queue when a record is created or assigned" checkbox
7. Save and Publish

http://social.microsoft.com/Forums/en/crm/thread/b35ad118-ac41-4aab-8a08-969737bbff2b

Tuesday, December 11, 2012

TopCoder SRM 562 Div 2 250 pt

I haven't done TopCoder in quite some time. In fact, I haven't done much real coding in awhile. Most coding I've been doing is high level easy stuff. I want to get back to thinking algorithmically, so I'm going to start making myself do TopCoder practice problems every day. So here's the question link and my approach and code. 

http://community.topcoder.com/stat?c=problem_statement&pm=12319&rd=15184

My approach and solution

import java.util.Arrays;

public class CucumberMarket
{
public String check(int[] price, int budget, int k)
{
/*
If any combination of k elements in price > budget then return NO, else YES

So need to build all possible combos of size k, check if the value is greater than budget.

This calls for a recursive solution.

Wait, we just need the k largest elements in price[] and then see if the sum > budget. So I can just sort the array

*/
Arrays.sort(price); //sorts in ascending order
int sum = 0;
for(int i = price.length - 1; i >= price.length - k; i--)
{
sum += price[i];
}
if(sum > budget)
return "NO";
else
return "YES";

}
}
Code references
1. Java array sort - http://viralpatel.net/blogs/java-tip-how-to-sort-array-in-java-java-util-arrays/

Friday, December 7, 2012

Detecting and removing web addresses from user entered text

Problem
I'm working on a website where users enter product descriptions. Some people put their own website in the description. This is a problem because it may potentially lead to customers buying directly from their website, thus the website loses revenue.

Solution

//ex: http://www.google.com
     $txt = preg_replace('#https?://www\.[a-z\.0-9]+/?[\w|/.]*#i', '', $txt);
//ex: www.google.com
   $txt = preg_replace('#www?\.[a-z\.0-9]+/?[\w|/.]*#i', '', $txt);
//ex: http://google.com
    $txt = preg_replace('#https?://[a-z\.0-9]+/?[\w|/.]*#i', '', $txt);

the part at the end, /?\S*, gets everything after an initial slash and stops after whitespace. For example: www.google.com/hello/hi/bye

References
1. http://regexpal.com/
2. http://stackoverflow.com/questions/13756938/remove-all-urls-from-text-with-php 

Tuesday, October 23, 2012

The specified sa password does not meet strong password requirements

Problem:
When installing SQL Server 2008 R2 when you go to specify the sa password you get an error message stating that the password doesn't meet complexity requirements. 

Solution
From this thread  msdn thread, the post that helped me was the following:

Several simple solution steps:

If computer is on a domain
1. Go to Administrative Tools / Domain Security Policy :: Security Settings | Account Policies | Password Policy
2. Ensure "Minimum password length" and "Password must meet complexity requirements" set to "Not Defined" state.

Not on domain
3. Go to Administrative Tools / Local Security Policy :: Security Settings | Account Policies | Password Policy
4. Turn "Minimum password length" to zero and "Password must meet complexity requirements" to "Disable"

Monday, October 15, 2012

HTML / Javascript for switching images based on a time interval

Here's a code snippet for flipping an image every second. In this example there's only 2 images. 

<!DOCTYPE html>
<html>
<body>
<img id="img" src="image2.jpg">

<script>
var images = new Array()
images[0] = "image1.jpg";
images[1] = "image2.jpg";
setInterval(changeImage, 1000);
var x=0;

function changeImage()
{
document.getElementById("img").src=images[x%2]
x++;
}



</script>

<button onclick="int=window.clearInterval(int)">Stop</button>

</body>
</html>


Source: http://stackoverflow.com/questions/3264739/image-change-every-30-seconds-loop

Wednesday, October 3, 2012

In place Upgrade from Windows Server 2008 R2 standard to enterprise

I installed 40 GB of RAM on this server and installed WS 2008 R2 Standard edition. To my surprise the amount of memory it says is "40 GB (32 GB usable)". The reason it says 32 GB usable is because Standard edition only supports a max of 32 GB!

I thought i was going to have to completely reinstall to get Enterprise installed. Fortunately i found this blog post: http://jamiebaldanza.org/2010/09/08/in-place-windows-server-2008-r2-standard-to-enterprise-upgrade/

You can do an inplace upgrade with one command: dism  /online /set-edition: ServerEnterprise /ProductKey:xxxx


Tuesday, September 25, 2012

Lots of duplicate contacts in CRM 2011

Problem
Just discovered this problem. There are thousands of duplicate contact records in our CRM 2011. At first when i discovered there were duplicates i thought it was a data entry error, but there are literally thousands of them, so it must be systematic.

Cause
It turns out there's a new setting in CRM 2011 that automatically creates contacts if you track an email using CRM for Outlook. The problem is we were missing duplicate detection rules which would have prevented the duplicates from being created. I read somewhere that this may possibly cause the email tracking to not work though, so i'm opting to have users turn off that setting.

Solution
1. Turn off the "Auto Create Contact" setting. This is in File->Options->Email tab, then at the bottom there's a checkbox called "Create". Uncheck it

2. Create a Duplicate Detection Rule for Contacts. I created mine with the conditions "Account is exact match, full name is exact match, email is exact match"

3. Run the Duplication Detection job on Contacts. This will show all potential duplicates based on the rule you just created.

4. In my scenario i have to Merge records, because duplicates have been created for several months now. So users have probably used the duplicate contacts for cases, activities, etc...

Security Alert - Outlook - the name on the security certificate is invalid or does not match the name of the site

Problem
Whenever i open Outlook i always get this Security Alert. 














When i go to View Certificate it is the public CA issued cert. This name at the top (the whited out part) says the Exchange server's FQDN.

Solution
Here's a very good explanation of why this error occurs and how to fix it.
http://www.shudnow.net/2007/08/10/outlook-2007-certificate-error/




Monday, September 17, 2012

How to retrieve Dell service tag from command line

1. Start->run->cmd
2. wmic bios get serialnumber

The alphanumeric serial number returned is the Dell service tag.

Tuesday, September 4, 2012

Robocopy

This will create a directory with YYYYMMDD date format appended to it. For example, C:/Backup 20120904/, then it will use robocopy to copy all directories in the source to the destination folder


for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set yyyymmdd=%%k%%j%%i
set "myDir=C:\Backup %yyyymmdd%"
mkdir "%myDir%"
robocopy C:\Source\ "%myDir%" /E


Stick this in a batch file and run it using task scheduler.

References
1. http://social.technet.microsoft.com/wiki/contents/articles/1073.robocopy-and-a-few-examples.aspx
2. http://superuser.com/questions/39377/script-to-create-folders-in-multiple-directories-using-yyyymmdd-date-as-the-fold

Monday, September 3, 2012

De-duping a huge amount of files (theoretical solution)

My math-major friend sent me this link: http://hardware.slashdot.org/story/12/09/02/1223201/ask-slashdot-how-do-i-de-dupe-a-system-with-42-million-files

we discussed the most efficient solution.

Problem: We have 4.2 million files, which takes up 4.9TB of space. I want to get rid of duplicate files.

Solution (theoretical):

Step 1: Check the filesize of every file. If a filesize is unique to file we know it cannot be a duplicate.

pseudocode
we have an inverted index, map[filesize, list[filepath]], this is called fsIndex

  1. for each directory
    1.  for each file in the directory
    2.  look in fsIndex for filesize
    3.  if filesize exists, put the filepath in fsIndex
    4. else, put the filesize, new list(with filepath) in fsIndex
  2. for each entry in fsIndex
    1. if there is only 1 filepath for this filesize, delete the entry 
so now we have an index with potential dup files.

big-O
N = number of files
1. reading all N file descriptors = O(N)
2. iterating through the index = O(N)

therefore O(N)

Step 2: Compare the CRC32 checksum of every potential dup file. Same checksum = duplicate

pseudocode
from step 1 we have an inverted index with all possible dup files in it, this is still called fsIndex
we have another inverted index, map[checksum, list[filepath]], this is called csIndex

  1.  for each filepath in fsIndex
    1. run CRC32 on file, this outputs the checksum
    2. if checksum exists in csIndex then put the filepath into this entry's list
    3. else, put the checksum, new list(with filepath) in csIndex
  2. for each entry in csIndex
    1.  if the entry's list has more than 1 element, for each entry in the list after the 1st element
      1.  (do whatever)* to the file at the filepath, because it's a duplicate
*(do whatever) = delete, or output the filepath somewhere so the user can manually decide which files to delete.

big-O
N = number of files
1. reading all N files from the hard disk = O(N)
2. iterating through the index = O(N)

therefore O(N)

Overall big-O
Step 1 gives us O(N), Step 2 gives us O(N), therefore the overall O(N) for this solution is O(N), where N is the number of files.

Note: each file is only read from the disk once, this minimizes disk reads (which is the bottleneck)
Additionally we could multi-thread the checksum gathering in Step 2 part 1. This would maximize processor throughput.

 
 

Thursday, August 23, 2012

Change max user connections to 1 now cant connect

Problem
Thinking it was a good idea, i changed max user connections to 1. Well, now i cannot connect to the server through SSMS. It results in error 223.

Solution
1. Start -> Run->cmd
2. sqlcmd -A -S <yourserver> -E
3.sp_configure 'show advanced options', 1
4. GO
5. Hit Enter, this will say "Configuration option 'show advanced options' changed...."
6. RECONFIGURE
7. GO
8. Hit Enter, it'll say nothing
9. sp_configure 'user connections', 0
10. GO
11. Hit Enter, it'll say "Configuration user connections' changed..."
12. RECONFIGURE
13. GO
14. It'll say nothing
15. Restart the SQL Server

http://social.msdn.microsoft.com/Forums/zh/sqldatabaseengine/thread/d3797fc1-357d-461d-b95e-233eb537c203

Hyper-V Heartbeat: No Contact

Problem
In hyper-v manager i'm seeing Heartbeat: No Contact.

Other symptoms:
1. Network adapter is not detected
2. When logging in with a domain user it says "The security database on the server does not have a computer account for this workation trust relationship"
3. When logging in with a local account it results in "Apply user settings..." loading screen sitting there forever

Solution

I tried multiple things to solve this. Here's all the steps i took in reverse order, so if the last thing i did is actually what solved it then you'll try that first and save lots of time. If it's a combo of all these things then it won't matter what order you do it in.


Solution 1
Apply what this KB says manually: http://support.microsoft.com/kb/2004121

  1. Start->regedit
  2. Navigate to HKLM\SYSTEM\CurrentControlSet\Services\HTTP and create the following Multi-string value: DependOnService
  3. Double click the new DependOnService entry
  4. Type CRYPTSVC in the Value Data field and click OK.
  5. Reboot the server
Solution 2

Remove and re-add the computer to the domain

1. Right-click Computer choose Manage

2. Click on System Properties

3. Click on Computer Name

4. Click Change

5. Click the Workgroup radio group, then type in the domain admin password

6. Restart the computer

7. Repeat sets 1-4, this type add the computer to the domain


Solution 3


1. Start the virtual in Safe Mode - with networking support. To do this keep hitting F8 while it's loading.

2. From Actions select Insert Integration Services Disk

3. Restart

4. While it's restarting keep hitting F8 and choose to restart in Safe Mode - with networking support, otherwise the "Setup configuration blah blah" gets stuck

Solution 4

1. Shut down the virtual machine

2. In Hyper-V Manager right-click and choose Settings

3. Remove the Virtual Network Adapter

4. At the top click on Add Hardware, then choose Legacy Network Adapter

5. Once that's added turn on the virtual machine



References
1. http://support.microsoft.com/kb/2004121

Friday, July 27, 2012

Write a treeview to file

For future reference: I took this from here: http://www.codeproject.com/Articles/13099/Loading-and-Saving-a-TreeView-control-to-an-XML-fi and added one line. I used this code in another project, an am just putting this here for future / easy reference.

// Xml tag for node, e.g. 'node' in case of <node></node>
private const string XmlNodeTag = "node";

// Xml attributes for node e.g. <node text="Asia" tag="" 
// imageindex="1"></node>
private const string XmlNodeTextAtt = "text";
private const string XmlNodeTagAtt = "tag";
private const string XmlNodeImageIndexAtt = "imageindex";
 
 
//Deserialization = Loading the Xml into a TreeView 
 
public void DeserializeTreeView(TreeView treeView, string fileName)
{
   XmlTextReader reader = null;
   try
   {
        // disabling re-drawing of treeview till all nodes are added
        treeView.BeginUpdate();    
        reader = new XmlTextReader(fileName);
        TreeNode parentNode = null;
        while (reader.Read())
        {
             if (reader.NodeType == XmlNodeType.Element)
             {      
                  if (reader.Name == XmlNodeTag)
                  {
                       TreeNode newNode = new TreeNode();
                       bool isEmptyElement = reader.IsEmptyElement;
                
                       // loading node attributes
                       int attributeCount = reader.AttributeCount;
                       if (attributeCount > 0)
                       {
                          for (int i = 0; i < attributeCount; i++)
                          {
                              reader.MoveToAttribute(i);
                              SetAttributeValue(newNode, 
                                           reader.Name, reader.Value);
                          }        
                       }
                       // add new node to Parent Node or TreeView
                       if(parentNode != null)
                          parentNode.Nodes.Add(newNode);
                       else
                          treeView.Nodes.Add(newNode);
                
                       // making current node 'ParentNode' if its not empty
                       if (!isEmptyElement)
                       {
                          parentNode = newNode;
                       }
                  }                          
             }
             // moving up to in TreeView if end tag is encountered
             else if (reader.NodeType == XmlNodeType.EndElement)
             {
                  if (reader.Name == XmlNodeTag)
                  {
                           parentNode = parentNode.Parent;
                  }
             }
             else if (reader.NodeType == XmlNodeType.XmlDeclaration)
             { 
                  //Ignore Xml Declaration                    
             }
             else if (reader.NodeType == XmlNodeType.None)
             {
                  return;
             }
             else if (reader.NodeType == XmlNodeType.Text)
             {
                  parentNode.Nodes.Add(reader.Value);
             }
    
        }
   }
   finally
   {
        // enabling redrawing of treeview after all nodes are added
        treeView.EndUpdate();      
        reader.Close(); 
   }
}
 
  private void SetAttributeValue(TreeNode node, 
                     string propertyName, string value)
  {
       if (propertyName == XmlNodeTextAtt)
       {                
            node.Text = value; 
            node.Name = value; //this sets the Node's key
 }
       else if (propertyName == XmlNodeImageIndexAtt) 
       {
            node.ImageIndex = int.Parse(value);
       }
       else if (propertyName == XmlNodeTagAtt)
       {
            node.Tag = value;
       }  
  } 

//Serialization = Saving the TreeView to an XML file
public void SerializeTreeView(TreeView treeView, string fileName) 
  {
       XmlTextWriter textWriter = new XmlTextWriter(fileName, 
                                     System.Text.Encoding.ASCII);
       // writing the xml declaration tag
       textWriter.WriteStartDocument();
       //textWriter.WriteRaw("\r\n");
       // writing the main tag that encloses all node tags
       textWriter.WriteStartElement("TreeView");
       
       // save the nodes, recursive method
       SaveNodes(treeView.Nodes, textWriter);
       
       textWriter.WriteEndElement();
         
       textWriter.Close();
  }
 
 
private void SaveNodes(TreeNodeCollection nodesCollection, 
   XmlTextWriter textWriter)
  {
       for(int i = 0; i < nodesCollection.Count; i++)
       {
            TreeNode node = nodesCollection[i];
            textWriter.WriteStartElement(XmlNodeTag);
            textWriter.WriteAttributeString(XmlNodeTextAtt, 
                                                       node.Text);
            textWriter.WriteAttributeString(
                XmlNodeImageIndexAtt, node.ImageIndex.ToString());
            if(node.Tag != null) 
                 textWriter.WriteAttributeString(XmlNodeTagAtt, 
                                             node.Tag.ToString());
            // add other node properties to serialize here  
            if (node.Nodes.Count > 0)
            {
                 SaveNodes(node.Nodes, textWriter);
            }     
            textWriter.WriteEndElement();
       }
  } 

How to use
To save call: SerializeTreeView(yourTreeView, yourFileName); 
To load call: DeserializeTreeView(yourTreeView, yourFileName);

A tool for determining which files have changed

Problem
We integrate with a big product. One of our most complex products is tightly integrated, which means we need to keep track of changes in the software we're integrating with.

The problem is there are hundreds of scripts/forms that integrate with multiple scripts/forms. So checking this by hand would literally take DAYS of work.

Solution
First, i had to go through our source files and map them to the source files that they integrate with. I kept track of this in a table in a Word doc.

Then I made a tool where i could enter the mapping, save it in an XML file, then compare relevant source files in two directories (the previous version, and the new version), then show which files have changed, and which files of ours we need to check for compatibility.

It's rather simple, it checks if the contents of the source file are the exact same. It doesn't do diffing. After this tool is used, I must then load up the changed files in a diffing tool -- such as WinMerge -- and see what changes were made, and then check our files for compatibility. This actual checking for compatibility could be automated, but that would be incredibly complex, and human judgement is needed for this sort of thing i think.

Code


 


using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.IO;
using System.Xml;

namespace Development_Utilities.Forms
{
   
    public partial class formOurSoftwareCompatibility : Form
    {
        // Xml tag for node, e.g. 'node' in case of <node></node>
        private const string XmlNodeTag = "node";

        // Xml attributes for node e.g. <node text="Asia" tag=""
        // imageindex="1"></node>
        private const string XmlNodeTextAtt = "text";
        private const string XmlNodeTagAtt = "tag";
        private const string XmlNodeImageIndexAtt = "imageindex";
        Dictionary<string, List<string>> mapping = new Dictionary<string, List<string>>();
        public formOurSoftwareCompatibility()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            txtOurSoftware.Text = "";
        }

        private void button4_Click(object sender, EventArgs e)
        {
            txtOtherSoftware.Text = "";
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
parseOtherSoftwareString();
        }

        private void button1_Click(object sender, EventArgs e)
        {
           
        }
        public void parseOtherSoftwareString()
        {
            string OtherSoftware = txtOtherSoftware.Text;
            Regex rgx = new Regex(@"\w+", RegexOptions.IgnorePatternWhitespace);
            foreach (Match m in rgx.Matches(OtherSoftware))
            {
                Add(m.Value, txtOurSoftware.Text, tvMapping);
            }
        }
        public void Add(string OtherSoftware, string OurSoftware, TreeView tv)
        {
            if (tv.Nodes.ContainsKey(OtherSoftware))
            {
                if (!tv.Nodes[OtherSoftware].Nodes.ContainsKey(OurSoftware))
                {
                    TreeNode newNode = new TreeNode(OurSoftware);
                    newNode.Name = OurSoftware;
                    tv.Nodes[OtherSoftware].Nodes.Add(newNode);
                }
            }
            else
            {
                TreeNode OtherSoftwareNode = new TreeNode(OtherSoftware);
                OtherSoftwareNode.Name = OtherSoftware;
                TreeNode OurSoftwareNode = new TreeNode(OurSoftware);
                OurSoftwareNode.Name = OurSoftware;
                OtherSoftwareNode.Nodes.Add(OurSoftwareNode);
                tv.Nodes.Add(OtherSoftwareNode);
            }
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            saveFileDialog1.DefaultExt = "xml";
            if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                SerializeTreeView(tvMapping, saveFileDialog1.FileName);
            }
        }
        public void DeserializeTreeView(TreeView treeView, string fileName)
        {
            XmlTextReader reader = null;
            try
            {
                // disabling re-drawing of treeview till all nodes are added
                treeView.BeginUpdate();
                reader = new XmlTextReader(fileName);
                TreeNode parentNode = null;
                while (reader.Read())
                {
                    if (reader.NodeType == XmlNodeType.Element)
                    {
                        if (reader.Name == XmlNodeTag)
                        {
                            TreeNode newNode = new TreeNode();
                            bool isEmptyElement = reader.IsEmptyElement;

                            // loading node attributes
                            int attributeCount = reader.AttributeCount;
                            if (attributeCount > 0)
                            {
                                for (int i = 0; i < attributeCount; i++)
                                {
                                    reader.MoveToAttribute(i);
                                    SetAttributeValue(newNode,
                                                 reader.Name, reader.Value);
                                }
                            }
                            // add new node to Parent Node or TreeView
                            if (parentNode != null)
                                parentNode.Nodes.Add(newNode);
                            else
                                treeView.Nodes.Add(newNode);

                            // making current node 'ParentNode' if its not empty
                            if (!isEmptyElement)
                            {
                                parentNode = newNode;
                            }
                        }
                    }
                    // moving up to in TreeView if end tag is encountered
                    else if (reader.NodeType == XmlNodeType.EndElement)
                    {
                        if (reader.Name == XmlNodeTag)
                        {
                            parentNode = parentNode.Parent;
                        }
                    }
                    else if (reader.NodeType == XmlNodeType.XmlDeclaration)
                    {
                        //Ignore Xml Declaration                   
                    }
                    else if (reader.NodeType == XmlNodeType.None)
                    {
                        return;
                    }
                    else if (reader.NodeType == XmlNodeType.Text)
                    {
                        parentNode.Nodes.Add(reader.Value);
                    }

                }
            }
            finally
            {
                // enabling redrawing of treeview after all nodes are added
                treeView.EndUpdate();
                reader.Close();
            }
        }
        /// <summary>
        /// Used by Deserialize method for setting properties of
        /// TreeNode from xml node attributes
        /// </summary>
        private void SetAttributeValue(TreeNode node,
                           string propertyName, string value)
        {
            if (propertyName == XmlNodeTextAtt)
            {
                node.Text = value;
                node.Name = value;
            }
            else if (propertyName == XmlNodeImageIndexAtt)
            {
                node.ImageIndex = int.Parse(value);
            }
            else if (propertyName == XmlNodeTagAtt)
            {
                node.Tag = value;
            }
        }

        public void SerializeTreeView(TreeView treeView, string fileName)
        {
            XmlTextWriter textWriter = new XmlTextWriter(fileName,
                                          System.Text.Encoding.ASCII);
            // writing the xml declaration tag
            textWriter.WriteStartDocument();
            //textWriter.WriteRaw("\r\n");
            // writing the main tag that encloses all node tags
            textWriter.WriteStartElement("TreeView");

            // save the nodes, recursive method
            SaveNodes(treeView.Nodes, textWriter);

            textWriter.WriteEndElement();

            textWriter.Close();
        }
        private void SaveNodes(TreeNodeCollection nodesCollection,
   XmlTextWriter textWriter)
        {
            for (int i = 0; i < nodesCollection.Count; i++)
            {
                TreeNode node = nodesCollection[i];
                textWriter.WriteStartElement(XmlNodeTag);
                textWriter.WriteAttributeString(XmlNodeTextAtt,
                                                           node.Text);
                textWriter.WriteAttributeString(
                    XmlNodeImageIndexAtt, node.ImageIndex.ToString());
                if (node.Tag != null)
                    textWriter.WriteAttributeString(XmlNodeTagAtt,
                                                node.Tag.ToString());
                // add other node properties to serialize here 
                if (node.Nodes.Count > 0)
                {
                    SaveNodes(node.Nodes, textWriter);
                }
                textWriter.WriteEndElement();
            }
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                tvMapping.Nodes.Clear();
                DeserializeTreeView(tvMapping, openFileDialog1.FileName);
            }
          
        }

        private void btnOtherSoftwareOld_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtOtherSoftwareOld.Text = folderBrowserDialog1.SelectedPath;
            }
        }

        private void btnOtherSoftwareNew_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog2.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtOtherSoftwareNew.Text = folderBrowserDialog2.SelectedPath;
            }
        }

        private void btnProcess_Click(object sender, EventArgs e)
        {
            tvChanges.Nodes.Clear();
            if (txtOtherSoftwareNew.Text != "" && txtOtherSoftwareOld.Text != "")
            {
                DirectoryInfo OtherSoftwareOld = new DirectoryInfo(txtOtherSoftwareOld.Text);
                DirectoryInfo OtherSoftwareNew = new DirectoryInfo(txtOtherSoftwareNew.Text);

                foreach (FileInfo fileOld in OtherSoftwareOld.GetFiles())
                {
                    string fileName = Path.GetFileNameWithoutExtension(fileOld.FullName);
                    if (tvMapping.Nodes.ContainsKey(fileName))
                    {
                        foreach (FileInfo fileNew in
                            OtherSoftwareNew.GetFiles(fileOld.Name, SearchOption.TopDirectoryOnly))
                        {
                            if (fileNew.Name != fileOld.Name)
                                continue;
                            else
                            {
                                string OtherSoftwareOldTxt = File.ReadAllText(fileOld.FullName);
                                string OtherSoftwareNewTxt = File.ReadAllText(fileNew.FullName);
                                if (OtherSoftwareOldTxt != OtherSoftwareNewTxt)
                                {
                                    TreeNode tn = (TreeNode)tvMapping.Nodes[fileName].Clone();
                                    tvChanges.Nodes.Add(tn);
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}
Note
I got the TreeView serialization / deserialization code from here: http://www.codeproject.com/Articles/13099/Loading-and-Saving-a-TreeView-control-to-an-XML-fi  The only thing i changed was I made it set the Node.Name to the same thing as the Node.Text. This was because loading the XML file into the TreeView, and then adding more nodes, Nodes.ContainsKey was not picking up the existing nodes.

Also note: I chose to do TreeView serialization because there was existing open source out there for it. I could have just as easily done it myself, but why reinvent the wheel?

Wednesday, June 27, 2012

Boolean Mode with weighted relevance in MySQL

Need weighted relevance while doing Boolean Mode in MySQL? Here's script for that.

--BEGIN SCRIPT AREA

 
--OPTIONAL for testing
CREATE TABLE IF NOT EXISTS `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`text` text,
PRIMARY KEY (`test_id`),
FULLTEXT KEY `text` (`text`,`name`)
) ;

INSERT INTO `test` (`id`, `name`, `text`) VALUES
(1, 'apples', 'Apples oranges apples bananas grapes grapefruit avocados apples '),
(2, 'oranges', 'grapes tomatoes apples apples apples plums pairs'),
(3, 'bananas', 'strawberries tomatoes apples peppers'),
(4, 'grapes', 'grapes grapefruit banana strawberries');
--END OF OPTIONAL



DELIMITER $$

DROP FUNCTION IF EXISTS `simple_weighted_word_count`$$
CREATE FUNCTION `simple_weighted_word_count` (in_fields TEXT, in_word TEXT)
RETURNS DECIMAL(10, 2)
BEGIN

DECLARE count DECIMAL (10, 2);
DECLARE remaining_phrase TEXT;
DECLARE remaining_str TEXT;
DECLARE current_phrase TEXT;
DECLARE current_word TEXT;
DECLARE beginning_phrase TEXT;
DECLARE end_phrase TEXT;
DECLARE first_char VARCHAR(1);
DECLARE last_char VARCHAR(1);

SET count = 0;
SET remaining_phrase = in_word;
SET remaining_str = '';

WHILE remaining_phrase != '' DO
SET beginning_phrase = SUBSTRING_INDEX(remaining_phrase, '"', 1);
SET last_char = SUBSTRING(beginning_phrase, LENGTH(beginning_phrase), 1);
SET end_phrase = SUBSTRING(remaining_phrase, LENGTH(beginning_phrase) + 2);
SET current_phrase = SUBSTRING_INDEX(end_phrase, '"', 1);
SET remaining_phrase = SUBSTRING(remaining_phrase, LENGTH(CONCAT(beginning_phrase, current_phrase)) + 4);
SET remaining_str = CONCAT(remaining_str, ' ', TRIM(beginning_phrase));
IF last_char = '-' THEN
SET count = count;
ELSEIF last_char = '>' THEN
IF current_phrase != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_phrase, '')))/LENGTH(current_phrase)) * 3);
END IF;
ELSEIF last_char = '<' THEN
IF current_phrase != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_phrase, '')))/LENGTH(current_phrase)) * 1);
END IF;
ELSEIF last_char = '~' THEN
IF current_phrase != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_phrase, '')))/LENGTH(current_phrase)) * .5);
END IF;
ELSE
IF current_phrase != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_phrase, '')))/LENGTH(current_phrase)) * 2);
END IF;
END IF;
END WHILE;

WHILE remaining_str != '' DO
SET current_word = TRIM(SUBSTRING_INDEX(remaining_str, ' ', 1));
SET remaining_str = SUBSTRING(remaining_str, LENGTH(current_word) + 2);
SET first_char = SUBSTRING(current_word, 1, 1);
IF first_char = '-' THEN
SET count = count;
ELSEIF first_char = '>' THEN
SET current_word = SUBSTR(current_word, 2);
IF current_word != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_word, '')))/LENGTH(current_word)) * 1.5);
END IF;
ELSEIF first_char = '<' THEN
SET current_word = SUBSTR(current_word, 2);
IF current_word != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_word, '')))/LENGTH(current_word)) * .5);
END IF;
ELSEIF first_char = '~' THEN
SET current_word = SUBSTR(current_word, 2);
IF current_word != '' THEN
SET count = count + (((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_word, '')))/LENGTH(current_word)) * .25);
END IF;
ELSE
IF current_word != '' THEN
SET count = count + ((LENGTH(in_fields) - LENGTH(REPLACE(LOWER(in_fields), current_word, '')))/LENGTH(current_word));
END IF;
END IF;
END WHILE;

RETURN count;
END$$

DROP FUNCTION IF EXISTS `weighted_word_count`$$
CREATE FUNCTION `weighted_word_count` (in_fields TEXT, in_words TEXT)
RETURNS TEXT
BEGIN
DECLARE count DECIMAL (10, 2);
DECLARE remaining_text TEXT;
DECLARE current_text TEXT;
DECLARE beginning_text TEXT;
DECLARE end_text TEXT;
DECLARE last_char VARCHAR(1);
DECLARE remaining_phrases TEXT;

SET count = 0;
SET remaining_text = in_words;
SET remaining_phrases = '';

WHILE remaining_text != '' DO
SET beginning_text = SUBSTRING_INDEX(remaining_text, '(', 1);
SET last_char = SUBSTRING(beginning_text, LENGTH(beginning_text), 1);
SET end_text = SUBSTRING(remaining_text, LENGTH(beginning_text) + 2);
SET current_text = SUBSTRING_INDEX(end_text, ')', 1);
SET remaining_text = SUBSTRING(remaining_text, LENGTH(CONCAT(beginning_text, current_text)) + 4);
SET remaining_phrases = CONCAT(remaining_text, ' ', TRIM(beginning_text));
IF last_char = '-' THEN
SET count = count;
ELSEIF last_char = '>' THEN
IF current_text != '' THEN
SET count = count + (simple_weighted_word_count(in_fields, current_text) * 1.5);
END IF;
ELSEIF last_char = '<' THEN
IF current_text != '' THEN
SET count = count + (simple_weighted_word_count(in_fields, current_text) * 0.5);
END IF;
ELSEIF last_char = '~' THEN
IF current_text != '' THEN
SET count = count + (simple_weighted_word_count(in_fields, current_text) * 0.25);
END IF;
ELSE
IF current_text != '' THEN
SET count = count + simple_weighted_word_count(in_fields, current_text);
END IF;
END IF;
END WHILE;

IF remaining_phrases != '' THEN
SET count = count + simple_weighted_word_count(in_fields, remaining_phrases);
END IF;

RETURN count;
END$$

DELIMITER ;


--EXAMPLE QUERY.
SELECT *, MATCH(`name`, `text`) AGAINST ('grape* banana*' IN BOOLEAN MODE) as relevance, weighted_word_count(CONCAT(`name`,`text`),'grape banana') as weighted_word_count
FROM `Test`
WHERE MATCH(`name`, `text`) AGAINST ('grape* banana*' IN BOOLEAN MODE) ORDER BY relevance DESC, weighted_word_count DESC;


--END OF SCRIPT AREA

References
1. I totally snatched this from http://www.codingforums.com/archive/index.php/t-176090.html 
There was an error in this gadget