Christian Long Tech notes and tidbits

From the Oracle docs:

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

• The aggregate_function is any one of the MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV functions. It operates on values from the rows that rank either FIRST or LAST. If only one row ranks as FIRST or LAST, the aggregate operates on a singleton (nonaggregate) set.
• The KEEP keyword is for semantic clarity. It qualifies aggregate_function, indicating that only the FIRST or LAST values of aggregate_function will be returned.
• DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).

Example from the docs:

SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id;


So, here's a simpler explanation of what (I think) is going on with FIRST and LAST.

Step 1: Oracle orders the rows according to the ORDER BY clause. Then, it chooses all the rows that are ranked first. This may be one or several rows (a tie for first)

Step 2: Oracle performs the aggregate function (MIN, MAX, AVG, COUNT, etc.) on the group of values from the rows that were ranked first. If only one row was first (there was no tie), then the aggregate function operates on just one value (from just that one row).

Yow, what a syntax :0

Here, I select the largest order number (max) from all those orders which have the farthest-out due date (last order by sr.due_date):


select max(sr.order_no) keep (dense_rank last order by sr.due_date) "Farthest out order"
from sched_receipts sr
where sr.item_no = my_item_number;


Some other examples:
Select the largest order number (max) among the orders which have the nearest due date (first order by sr.due_date):

select max(sr.order_no) keep (dense_rank first order by sr.due_date)

Select the smallest order number (min) among the orders which have the nearest due date (first order by sr.due_date):

select min(sr.order_no) keep (dense_rank first order by sr.due_date)


Some other links:
OracleBlog: Extra Columns in a GROUP BY
Include non-grouped columns

ActiveState, Inc. makes Komodo, a great IDE for developing software in Python, Perl, and many other languages. I've written before about some of my favorite features.

Now, the bad news. In Komodo 4.3, they introduced a new Find and Replace dialog box, which they tout as a great improvement. I think its ugly and klunky, plus it's not what I'm used to.

Netroots to the rescue! I posted a petition which asks ActiveState to restore the old dialog as an option. Please click on the link below, to visit and sign the petition.

Restore the Komodo Find & Replace dialog
http://www.gopetition.com/online/17507.html

One great advantage of Subverson's new back-end storage format, FSFS, is that you can now store your Subversion repository on a network drive instead of on locally attached storage. This is good for me, as my Subversion "server" is a creaky old machine whose hard drive might go at any minute (side note: those hard drive MTBF ratings always did sound a little fishy).

I wanted to run svnserve as a service on Windows XP, serving a repository located on a Novell network share. A couple of hours later, success. Here's how.

The official Subversion documentation describes how to set up svnserve as a Windows service. The documentation describes a number of caveats, especially concerning escaping double quotes.

In addition to those caveats, note these:

• When mapping the root of the repository, don't use the drive letter. Specify the URN path instead. For example, use "\\someserver\SomeShare\My Repository" instead of "G:\My Repository"
• Make sure the service is set to log on as an account that has permission to access the network share. (See screenshots below)

Step by step, here's what I did:

1. Install Subversion 1.4
2. Register svnserve.exe as a Windows service. Here is the command line. (To work, this code must be all on one long line.)
   sc create svn binpath= "\"C:\Program Files\Subversion\bin\svnserve.exe\" --service --root \"\\YourServer\Data\Path To Your\Repository\"" displayname= "Subversion Server" depend= Tcpip start= auto

   Note that the trailing \  ( as in:  svnserve.exe\"    Your\Repository\" ) is there to escape the double-quote mark that follows it. Make sure to enclose your paths in escaped quotation marks if you have spaces in your paths.

3. Open the Service manager (Start > Programs > Administrative Tools > Services). Double-click on the newly created "Subversion Server" service to show its properties.
   
   On the General tab, verify that the "Path to executable:" is correct
   
   
   On the LogOn tab, specify an account and password with access to the network share
   
   

Of course, running services as a domain user instead of as the LocalServices account goes against all the best advice about security. See this article from Microsoft for more info about running services securely.

Resources
Official Subversion book: Invoking svnserve
From CollabNet: Windows Service Support for svnserve
From Microsoft:
        Services permissions
        Microsoft Windows XP - Services permissions
        Services and Service Accounts Security Planning Guide
Scripts forum: Access mapped drive from a Windows Service

Earlier I mentioned my appreciation of Komodo's "Select Columns of Text" feature. Komodo has lots of other neat text editing tricks that save me a ton of time. First among these is Reflowing Paragraphs.

With one keystroke, I can reformat from this:

to this:

The lines have all been wrapped at SCREEN_WIDTH, the comment characters have been managed, and the indentation has been preserved. Brilliant.

Other handies include: quick uppercase and lowercase, transpose characters, transpose lines, continue line decoration with Shift-Enter, and find-as-you-type (aka incremental search).

One of my favorite features of the Komodo IDE is the ability to select a rectangular block of text. Komodo Docs: Select columns of text in Komodo by pressing the Alt key and then dragging with the mouse. You can also Alt-Shift and Click to select a rectangular block consisting of one or more columns of text.

Imagine my surprise when a fat finger on the Alt key revealed that the same thing is possible in Microsoft Word (I'm using Word 2000). See for yourself.

Just like in Komodo, you can Alt-Shift-Click, or Alt-Drag to select any arbitrary rectangular area in Word. I use this feature all the time in Komodo for code cleanup and reformatting text. It only took me seven years to figure out Word has it too!

Here is an example of using the command line cURL tool to automate logging in to a website and downloading some data. It does the same thing as my PycURL example.

But how do you discover what stimülüs to apply to the server to get the response you desire? Use a tool such as liveHTTPHeaders (Firefox) or ieHTTPHeaders (IE6 - I couldn't get it to install properly with IE7). Install the tool in your browser, and visit your target site as you would normally. The *HTTPHeaders tool will capture the actual HTTP traffic between your computer and the server, which you can then analyze to discover how to mimic a real client using cURL or libcURL.

Note that each call to curl in the following example should exist on its own long line, even though the lines might be displayed as wrapped in your browser.


@echo off
curl -s -S -o C:\Temp\temp.txt -L -b C:\Temp\cookieFile.txt -c C:\Temp\cookieFile.txt -A "Mozilla/4.0 (compatible; MSIE 6.0)" http://interesting.website.com/LogIn.asp

curl -s -S -o C:\Temp\temp.txt -L -e ";auto" -b C:\Temp\cookieFile.txt -c C:\Temp\cookieFile.txt -A "Mozilla/4.0 (compatible; MSIE 6.0)" -d "FormField=URL%20Encoded%20Value" http://interesting.website.com/LogIn.asp

curl -o downloaded_file.txt -e "http://interesting.website.com/referer.asp" -b C:\Temp\cookieFile.txt -c C:\Temp\cookieFile.txt -A "Mozilla/4.0 (compatible; MSIE 6.0)" --url "http://interesting.website.com/do_it.asp?do=0&something=0&interesting=0"

del C:\Temp\cookieFile.txt
del C:\Temp\temp.txt
echo Done!
pause


Here's a little sample of Python code demonstrating the use of PycURL, the Python interface to libcURL. It does the same thing as my cURL example. Refer to this page for a detailed list of libcurl options.


import pycurl, StringIO
# Constants
DOWNLOADED_FILE = r'C:\temp\downloaded_file.txt'
USER_AGENT = 'Mozilla/4.0 (compatible; MSIE 6.0)'
LOGIN_URL = 'http://interesting.website.com/LogIn.asp'
LOGIN_POST_DATA = 'FormField=URL%20Encoded%20Value'
DOWNLOAD_URL = 'http://interesting.website.com/do_it.asp?do=0&something=0&interesting=0'
DOWNLOAD_REFERER = 'http://interesting.website.com/referer.asp'
FILE_MODE = 'wb'

# Set up objects
dev_null = StringIO.StringIO()
slurpp = pycurl.Curl()

# Request login page
slurpp.setopt(pycurl.USERAGENT, USER_AGENT)
slurpp.setopt(pycurl.FOLLOWLOCATION, 1)
#slurpp.setopt(pycurl.AUTOREFERER, 1) # not yet implemented in pycURL
slurpp.setopt(pycurl.WRITEFUNCTION, dev_null.write)
slurpp.setopt(pycurl.COOKIEFILE, '')
slurpp.setopt(pycurl.URL, LOGIN_URL)
slurpp.perform()

# Log in to site
slurpp.setopt(pycurl.POSTFIELDS, LOGIN_POST_DATA)
slurpp.setopt(pycurl.POST, 1)
slurpp.perform()

# Download relevant data
slurpp.setopt(pycurl.HTTPGET, 1)
slurpp.setopt(pycurl.URL, DOWNLOAD_URL)
slurpp.setopt(pycurl.REFERER, DOWNLOAD_REFERER)
outfile = file(DOWNLOADED_FILE, FILE_MODE)
slurpp.setopt(pycurl.WRITEFUNCTION, outfile.write)
slurpp.perform()

# Clean up and close out
outfile.close()
dev_null.close()
slurpp.close()



A nice little walk in the woods, near Oshkosh, Wisconsin.
Latitude: 43.99917
Longitude: -88.77639
Click here for Mapquest Map
Ask GeoURL about other sites near Oshkosh.

AttachmentSize
Map to Waukau Creek Nature Preserve.jpg108.37 KB

Matthias Wandel's ftpdmin is a handy, minimalist ftp server, useful for flinging files around on the fly.

Preston Gralla, author of O'Reilly's Windows XP Hacks shows how to add commands to the right-click context menu for folders in Windows Explorer.

Combining the two, here's an easy way to add an option to the right-click context menu that will start up the ftp server rooted at your current folder. For example, if you were to right-click on the C:\My Stuff folder, you could then choose to start up ftpdmin using C:\My Stuff as the root folder served by the ftp server.

To add the option, run the Registry Editor (regedit.exe), then go to HKEY_LOCAL_MACHINE/Software/Classes/Folder/Shell.
Create a new key called FTP. For the default value, enter whatever text you want to appear when you right-click on a folder-for example, Start FTP Server Here.

Create a new key beneath the FTP key called Command. Set the default value to

Cmd.exe /k ""C:\My Path\To\ftpdmin.exe" "%L""

(note the double double quotes). That value will launch Cmd.exe, which is the Windows 2000/XP command prompt. The /k switch puts the prompt into interactive mode. "C:\My Path\To\ftpdmin.exe" is the path to the ftpdmin executable; modify this value to point to the place you have installed ftpdmin on your system. "%L" provides the name of the current directory. Make sure that you quote properly, as in the example.

Exit the Registry Editor. The new menu option will show up immediately. Note that it won't appear when you right-click on a file; it shows up only when you right-click on a folder.

This bit of VBScript reads the words in a text file, and uses a blank, formatted Word document to print those words in worksheet format.

Good handwriting tracing fonts for kids are hard to find. Here is a good overview, with links to some good ones:

Free Handwriting Fonts for Teachers.

I'm using Print Clearly, from the fontsters of Blue Vinyl. I'm using the TrueType version, not OpenType, since the OpenType version does not behave properly at large sizes on my Win2k/Word2k setup.

Install CheckInstall

A program that takes non-packaged software (such as pylint, DCOracle2) and installs it as an RPM package. This makes it visible/uninstallable from the yast rpm manager.

Install from source, since installing using RPM didn't work for me on Novell SuSE SLES 9

========================= Installation results ===========================
/bin/sh: error while loading shared libraries: /usr/local/lib/installwatch.so: cannot open shared object file: No such file or directory
**** Installation failed. Aborting package creation.


Commands


wget http://asic-linux.com.mx/~izto/checkinstall/files/source/checkinstall-1.6.0.tgz
tar -zxvf checkinstall-1.6.0.tgz
cd checkinstall


Read installation instructions

more INSTALL


Install Checkinstall from source

make
su
make install
/usr/local/sbin/checkinstall


Add /usr/local/sbin to path for user if you like. Add to ~/.profile to make path change persist.

export PATH=$PATH:/usr/local/sbin


On SuSE, to add to the PATH for everybody:
as root make a file /etc/profile.local with the content you want

What to do when wget won't work

Sometimes, you can't wget a file because of the way the server expects cookies to be handled.

wget http://example.com/frozzler-2.2.2.rpm


cUrl is better able to handle cookies.

Obtain cookie at home page,and save the cookie to cookies.txt

curl -c /path/to/temp/cookies.txt -o /dev/null http://example.com/home_page


Get a file, supplying the cookie saved in cookies.txt

curl -b /path/to/temp/cookies.txt -O http://example.com/frozzler-2.2.2.rpm


Or,all in one line, obtain cookie at home page, then get a file. This works by specifying the homepage as the first file to get, and directing its output to /dev/null. In addition, the -b option turns on cUrl's cookie parser. The cookies set during the visit to the first url (home page) are kept in memory, and are used to "authenticate" when requesting the file to be downloaded . Note that, in this case, the -o and -O options must follow (not preceed) the url to which they apply. You can have as many -o and -O options as you have urls. Note also that we indeed use "nada" (or any non-existent file) as the argument to the -b option. This starts the cUrl cookie parser without having to actually load any pre-existing cookies.

curl -b nada --url http://example.com/home_page -o /dev/null --url http://example.com/frozzler-2.2.2.rpm -O


(Novell) SuSE Linux 9

Note: when using su, don't forget to use the -l flag to make the new shell a login shell - that is, so that it gets its PATH variable set as if the user were logging in

See differences below


su
echo $PATH
/usr/sbin:/bin:/usr/bin:/sbin:/usr/X11R6/bin


su -l
echo $PATH
/sbin:/usr/sbin:/usr/local/sbin:/root/bin:/usr/local/bin:
/usr/bin:/usr/X11R6/bin:/bin:/usr/games:/opt/gnome/bin:
/opt/kde3/bin:/usr/local/sbin


On SuSE, to add to the PATH for everybody:
as root make a file /etc/profile.local with the content:


export PATH=$PATH:/usr/local/bla


Then each console or term window you open with any user with an account on that computer will have /usr/local/bla as an element of the path. Of course, replace "/usr/local/bla" with whatever path you want to add to $PATH.

More info about $PATH here. http://www.faqs.org/docs/Linux-mini/Path.html

Here is a command line for Pageant (PuTTY agent) that puts all the pieces together to make a one-click access to servers via ssh.

"C:\path\to\pageant.exe" "D:\path\to\My_private_key.PPK" -c "C:\path\to\putty.exe" -load my_saved_session

Assumes that you have the following
- PuTTY
- Pageant
- A private key (PPK) file in PuTTY format. See PuTTYgen for help on making and converting keys.
- The corresponding public key installed on the server you are logging in to. (e.g. pasted into $HOME/.ssh/authorized_keys)
- a saved PuTTY session containing the info about the server you are contacting

To make this into a shortcut on Windows, right-click on the desktop > New > Shortcut. Paste this command line into the box that says "Type the location of this item".

The first time you click on this shortcut, Pageant will start and ask you to type the passphrase for your key. Pageant will start, and on any subsequent logins, Pageant will supply the key to connect to the server via PuTTY. Of course, if you restart Windows, you will have to again supply the passphrase to Pageant.

Setup for proper use of PuTTY with ncurses (blue) terminal applications, such as SuSE yast and other Linux ncurses applications.

This setup can eliminate problems such as seeing letters instead of line-drawing characters (qqqqqqqqqqq), and seeing black background for blank areas of the ncurses application instead of the blue screen.

Set up a session in PuTTY
In PuTTY Configuration for that session, make the following changes

Terminal > Use background color to erase screen
Terminal > Keyboard > The Function keys and keypad: Linux
Window > Display scrollbar: deselect
Window > Translation > Received data assumed to be in whic character set: UTF-8
Window > Translation > Adjust how PuTTY displays line drawing characters: Unicode mode
Connection > Terminal-type string: linux

After making changes, make sure you Save the session.

Note: these changes were required under PuTTY 0.53. Under PuTTY 0.58, these options still exist, but some may not be necessary.

UPDATE

If you're getting boxes instead of line-drawing characters in PuTTY, the first thing to check is that you have PuTTY set to use a font that contains line-drawing characters. To check (on windows) go to Accessories > System Tools > Character Map. I use Lucida Console for PuTTY. In contrast, Lucida Sans Typewriter does not have line-drawing characters.