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 

Tuesday, June 26, 2012

Hyper-V - user can't see any virtual machines

Problem
When a user logs into hyper-v they don't see any virtual machines

Solution
This is because they don't have permission unless they're in an admin group. Here's the concise steps, see the link below for longer instructions.  
  1. Start->type mmc.exe, start mmc.exe
  2.  File -> Add / Remove Snap-in
  3. Authorization Manager -> Add -> OK
  4. Action -> Open Authorization Store
  5. Choose XML File  -> copy and paste this in the address bar C:\ProgramData\Microsoft\Windows\Hyper-V\InitialStore.xml
  6. OK
  7. Expand store -> expand Hyper-V services -> click Role Assignemtns -> click Administrator
  8. Right-click in the area on the right, click "Assign Users and Groups"
  9. Add the person you want
  10. Click OK



 References

1. http://blogs.msdn.com/b/virtual_pc_guy/archive/2008/01/17/allowing-non-administrators-to-control-hyper-v.aspx

Remove all non-alphanumeric characters from a string

In C#:
Regex rgx = new Regex("[^a-zA-Z0-9 -]");
str = rgx.Replace(str, "");
 
This replaces all non-alphanumeric characters with the empty string (""). You can replace 
the "" part with whatever character you wish to replace it with.
 
Reference:
1. Stack Overflow
 

Thursday, June 21, 2012

Apache url rewrite reference

 1.  Goal: Take /category/categoryname?page=# and rewrite into /categorydetail.php?catname=name&page=#

RewriteRule ^category/(.*) categorydetail.php?catname=$1 [QSA,L]

Why? [QSA] will keep the original query string, which in this case is page=#, and append catname=$1


http://www.simonecarletti.com/blog/2009/01/apache-rewriterule-and-query-string/

Tuesday, June 19, 2012

Add Google Adsense to phpnuke site

1. Download blocks here: http://www.clanthemes.com/downloaddetails-11-230-google-adsense-for-phpnuke.html

2. Open each file and change google_ad_client = 'your google adsense pub id'

3. Place the files in the /blocks/ directory

4. Login to your site as Administrator

5. Click on Blocks

6. Enter a title, then select one of the Google Adsense blocks, and then Create Block

Saturday, June 16, 2012

How to get free and private version control in the cloud

Problem
I'm working on a website by myself, and want to use version control. Version control repository on my local machine seems rather silly, so instead I wanted to get a version control that's capable of being stored online ("in the cloud"). There are free services out there, like github.com, but the problem is the free version is open source. What if you don't want to share your code, but still want an online version control for free? Here's how!

Solution
I will explain how I'm doing it, and from this you should be able to easily adapt it for your situation. I'm doing it with a WAMP website.

1. Sign up and get Dropbox - 2 GB for free, automatically syncs folders on your computer to their servers (i.e. the cloud)

2. In the Dropbox folder, which is D:/Dropbox on mine, create a subfolder(s) to put your source files. For example, mine is D:/Dropbox/Code/MyProject

3. (For wamp users only): Create an alias to point to D:/Dropbox/Code/MyProject
  1.   Click the Wamp icon -> Apache -> Alias directories -> Add an alias
  2. Type the url alias, for example: http://localhost/MyProject
  3. Type the project directory, for example: D:/Dropbox/Code/MyProject
  4. Test by navigating to http://localhost/MyProject/ (notice the slash on the end)
4. Open Netbeans (IDE for people who don't use it)-> right-click the project -> Versioning -> initialize git repository -> leave the default

5. Commit all files

Congrats, your version controlled files are now in the cloud for free!!


References
1. Making an alias on wamp: http://www.webdesign.org/web-programming/php/configure-a-virtual-directory-using-wamp.16605.html

Thursday, June 14, 2012

Prometheus

I went and watched Prometheus last night. I really really enjoyed it. This movie definitely reminded me of the Aliens movies. I don't want to give anything away, just that it the mechanics of things they encounter are very similar to that of the aliens in the Aliens movies.

IMDB

Some guy's review on YouTube

Monday, June 11, 2012

Summary of "Your Erroneous Ways" and "How to make friends and influence people"

 Two books I read for self improvement. I believe these provide a good framework for 1) controlling yourself and 2) human relations.


Your Erroneous Zones

  1. You control your thoughts, therefore you control your emotions
  2. Love yourself
  3. Don’t seek approval
  4. Don’t let the past define who you are. Don’t say things like “I’m shy”, because this is behavior that can be changed
  5. Guilt and worry are useless.
  6. Try new things
  7. There’s no right or wrong, just different. Do what you want to do
  8. Life’s not fair, don’t whine about it
  9. Don’t put off doing stuff
  10. Be independent in all relationships
  11. Anger is a choice, don’t choose it



How to win friends and influence people

1.     Handling People

1)      Don’t make fun of people
2)      Don’t criticize people’s things / ideas
3)      Don’t whine
4)      Give sincere appreciation
5)      Give people a good reason to do something

2.     Make people like you

1)      Be interested in the other person
2)      Smile
3)      Remember the person’s name
4)      Be a good listener
5)      Talk in terms of the other person’s interests
6)      Make the other person feel important

3.     How to win people to your way of thinking

1)      Avoid arguments
2)      Respect people’s opinions, don’t say “you’re wrong”
3)      Admit when you’re wrong quickly
4)      Begin in a friendly way
5)      Get the other person agreeing with your logical premises
6)      Let the other person do most of the talking
7)      Let the other person believe that a good idea is their own
8)      See from the other person’s view
9)      Be sympathetic with the person’s ideas
10)   Appeal to nobler motives – not just base motives (i.e. profit motive)
11)   Dramatize ideas
12)   Challenge the other person

4.     Leadership

1)      Begin by praising
2)      Indirectly call out mistakes
3)      Talk about your own mistakes before talking about the other’s
4)      Ask questions instead of orders
5)      Let the other person save face
6)      Praise all improvements, little or big
7)      Give the person a high reputation to live up to
8)      Encourage, make the problem seem easy to fix
9)      Make the person be happy about what you suggest

Mac’s 3 rules of communication in public

  1. Never talk about religion
  2. Never talk about politics
  3. Never talk about your “passions”, unless specifically asked to discuss them. For example, baseball, video games, movies, etc…







Tuesday, June 5, 2012

SQL Server "downgrade path not supported" when trying to attach or restore database

Problem
I attempted to attach a database from SQL Server 2008 R2 into a SQL Server 2005 instance. This resulted in the "downgrade path is not supported" problem. This is because the databases are not backward compatible due to differences in the structure of the data file (MDF)

Solution
From SQL Server 2008 R2 (or whatever you're using), you can use a script generation wizard that will automatically put all the schema and data into one script. This can then be executed on the older version of SQL Server.

Steps
1. Open SSMS for SQL server 2k8 R2

2. Right-click on the database, chooses Tasks, then Generate Scripts

3. Click Next

4. Click Advanced

5.  Change the server version to whichever version you're downgrading to. Then change it the types of data to script to "schema and data". Make any other changes to the defaults based on your situation, then click Ok























6. Click Next

7. Click Finished

8. Wait for the script generation to complete

9. Execute the script in the older version's SSMS



Note 1: In my case the database takes longer to move than it does to install a new instance, which means the generated script will be big too. So on the server where I ran into the problem I simply installed SQL  server 2008 R2 side-by-side with SQL Server 2005, in order to generate the script on the server where it's to be executed. This saved a lot of time!

There was an error in this gadget