Data Wise Business Solutions

I have been getting into biking more and more.   Biking is a good exercise.   I have read health articles that just 3 hours a week can reduce hart-attack and stokes by 50%.

I purchased a Word Combination Lock for the bike.    It has four dials with ten letters each.   I couldn’t find a word I liked so I searching the web for samples.  But I didn’t find any lists.  

Now for the real purpose of this blog.   Using Regular Expressions.    I used regular expressions to produce a list of possible combinations.     This is how I did it:

1) first off, you need a list of English words.    I did a search and found a huge list of words (WordList.txt).  There was a lot of garbage in the file I found, so I modified it a bit. 

2) Create a table to hold the word list:
    Create table wordlist (word varchar(255));

3) Import the list into your database.    There is many ways to get data into your database.    I used toad's import tool.

There are duplicates in the list.   That is why there isn’t a primary key defined.

4) Time for the query:

Letter options for each dial.   The default combination is SHED.    As you can see from the list, there is runs, fast, bike, loop, play and well.     

Using the letter combinations for each dial, we can use simple regular expression to produce a list of words that can be formed.  

See http://en.wikipedia.org/wiki/Regular_expression for definition and history of regular expression.

The Query:

select distinct word
from wordlist
where ucase (word) REGEXP '[STDMRFBLPW][HWRYUAIOLE][ERMTNSKOAL][DGKMSTEPYL]'
and length(word)=4;

Looking at the query.   

1. select distinct word
Remember there are duplicates in the word list.  Distinct will eliminate duplicates the list.   Note, distinct shouldn’t be used in any production environment

2.  where ucase (word)...
It is good practice to match the case that you are expressing. Ucase will convert all words in the table to upper case.    

Do note, that some database engines will not use an index when changing case.   Thus producing a slower query.

3.  Looking at the simple version ...word regexp...  is similar to ...word = 'SHED' or word='SHEG' and etc.   looking at each character that matches each dial letter.

For the Microsoft SQL Server users:   there really isn’t a good way to do regular expression.    Click here for a goole search that might help.

The Result.  The query produced a list of 5026 possible words.    See Word_Lock_Sample_List.txt for the list.   The list isn't the best. The source file did have non-existent words. 

There are several reasons you may want to create script to add indexes. If you have used SQL Server DTS application to transfer data from one database to another, the data will be transferred over, but not the indexes. If you used the enterprise manager (or management studio) to script a table create, indexes again will not be created. Want a brute force way of recreating all of your indexes? Read further.

I  have discussed with other articles that a good database design shouldn’t use nulls.   See Real World Use of nulls.  Nulls can cause issues in application development.

I was helping out a friend with a query and noticed that he had written a check (in WHERE) for a column with two conditions.   One of them being null.   The column was a status column (I.e true/false).    First off, All status columns should have a default value of zero (false).   

To help him with his query and worry about fixing the database later I helped him to write his query better.

Before:
    Select...
    From...
    Where status <> 1 OR status is null. 

On the top of my list of best practices is never to use OR.   He didn’t think he had another option until I showed him this version:

After:
    Select...
    From...
    Where coalesce(status,0) <> 1

Notice NO OR.   Coalesce will return the first non null.   In above example, if status is null, it will then return 0.  If status was 0 or 1, it would return that value.

Should also note that coalesce will work on all database engines. 

Now lets fix the database issue.   How to add a default to an existing column?  
First lets look at a sample table.  Col1 and Col2 allow nulls:

CREATE TABLE Table1 (
   Table1ID INT AUTO_INCREMENT NOT NULL,
   Col1 INT,
   Col2 VARCHAR(50),
  PRIMARY KEY (Table1ID)
);

Put some data into that table (mysql code):
insert into Table1 (Col1, Col2)
VALUES (1,NULL), (NULL,'A'), (null,null);

we get:

select * from Table1;

Table1ID    Col1    Col2
1            1      NULL
2            NULL   A
3            NULL   NULL

Now lets update col1.  Set a default and make it not nullable.

-mysql
ALTER TABLE Table1
 CHANGE Col1 Col1 INT(11) NOT NULL DEFAULT '0';

--mssql  v2000 and 2005
ALTER TABLE Table1
 alter column Col1 INT NOT NULL DEFAULT '0';
 
--mssql  v2000 and 2005
ALTER TABLE Table1
 alter column Col1 INT NOT NULL DEFAULT '0';

--mssql 2008
ALTER TABLE Table1
add constraint dfTable1 default '0' for Col1

Looking at the existing data you will see that Col1 values that were once null are now 0
select * from Table1;
Table1ID  Col1  Col2
1         1     NULL
2         0     A
3         0     NULL

The Problem: 

1. if you have moved a site from one directory to another
2. you were prototyping a site with a different name.
3. Or you had a custom theam that changed while the old version was being used.

After the move or rename, you get a blank screen. 

The Solution:  

To verify there is a problem, look at the last few records of the sessions table.    If you see errors like “failed to open stream: No such file or directory in” then you do have to follow steps.

Step 1:   Edit settings.php file.
The file is located .../sites/<sitename>/

you may have to change the permissions of the file to allow for write

find this section:# $conf = array(
#   'site_name' => 'My Drupal site',
#   'theme_default' => 'minnelli',
#   'anonymous' => 'Visitor',

replace it with:
 $conf = array(
   'site_name' => 'My Drupal site',
   'theme_default' => 'minnelli');
#   'anonymous' => 'Visitor',

The site should now come up.  There will be errors and missing images

Step 2:  edit the files table records
for every record that has the old path, change the path.  

Step 3:   edit node_revisions table records
edit nodes that have hard coded image/file reference.  

Step 4:  Edit  settings.php file again
This time, put back the # char at the begin of each line.     This disables the forced theme.

DONE
Your site should be up and runing at this point.  

If it isn't, select a new theme.  using http://<sitename>/admin/build/themes

Some times, there is a need to insert records into a table that has a auto number increment column. If you are needing to load one or many records and have to preserve an ID you need to turn off the auto numbering. Another reason might be that you need a zero record for a data warehouse. Below are two examples. One My Sql -- which has a few more lines of code. The other is Microsoft SQL Server.