Eliminating Mysterious Oracle Indexes

Oracle by default will create cryptic names for indexes unless you specify your own. To get a list of all the indexes currently in use for your schema you can execute:
> select * from user_indexes;
Anything that starts with SYS_ and ends with a $ is an autogenerated index name. It is good to have a common format for your indexes so that any errors are meaningful. address_id_pk is much more readable than SYS_IL0000088969C00006$$. Over here we use the [table]_[column]_[type] format so we can know at a glance where to hunt for the problem.

While doing this I noticed that LOB and CLOB field types automatically generate a SYS_blahblahblah$$ index, polluting your clean design. If that rubs you the wrong way, you can specify the index name for them as well, but the code is a bit messier.

Zend_Paginator with Search Params

I have noticed a couple posts out there asking how to incorporate a search with the Zend_Paginator component so that the search params follow along. Here is one way to do it, assuming we are searching for a person in our database.

1. Set up the action to capture the parameters

2. Set up our router to accept the params

3. Modify the example paginator view partial

4. Pass the search params to the paginatorControl view helper

1. Set up the action to capture the parameters

Throughout this example I assume that the search form is labeled 'search'.

public function listAction()
{
    $form = new My_Form_Search();

    //figure out what page they want to be on with a default of 1
    $page = (int)$this->_getParam('page', 1);
    $recordsPerPage=15;

    //set up our default search
    $model = new Default_Model_Person();
    $select = $model->select();
 
    //capture the input params 
    if ($this->_request->getParam('search',0) || $this->_request->isPost()){

        //if it is a post, populate the form and reset the page to 1
        if ($this->_request->isPost()){
                $form->populate($this->_request->getPost());
                $page = 1;
        }
        else{    //'search' showed up via the GET param
                $form->populate($this->_request->getParams());
        }
        //make sure the submitted data is valid and modify select statement
        if ($form->valid()){
            $search = $form->getValue('search');
            $select->where('name like ?',$search.'%');
        }
    }

    //set up the paginator with our select query       
    $paginator = new Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect($select));

    $paginator->setCurrentPageNumber($page);
    $paginator->setItemCountPerPage($recordsPerPage);

    //set our view parameters. the third one (searchParams) is
    // the key difference from a lot of the other examples out there
    $this->view->paginator = $paginator;
    $this->view->form = $form;
    $this->view->searchParams = array('search'=>$form->getValue('search'));
}

2. Set up our router to accept the params

<config>
    <people_path>
        <route>people/:page/:search</route>
        <defaults>
            <module>people</module>
            <controller>index</controller>
            <action>list</action>
            <page>1</page>
            <search/><!-- by default the search param is empty -->
        </defaults>
        <reqs>
            <page>\d+</page>
        </reqs>
    </people_path>
</config>

3. Modify the example paginator view partial

You need to add the search param to the url router on the view partial. Find the relevant lines and toss in the search param to the param array:

<a href="<?php echo  $this->url(array('page' => $this->previous, 'search'=> $this->search)); ?>">
<a href="<?php echo  $this->url(array('page' => $page, 'search'=> $this->search)); ?>">
<a href="<?php echo  $this->url(array('page' => $this->next, 'search'=> $this->search)); ?>">

4. Pass the search params to the paginatorControl view helper

A little known fact is that you can pass in a fourth parameter to the paginatorControl view helper. We want to give it our search params that we defined above:

echo $this->paginationControl($this->paginator,'Elastic','pagination_control.phtml',$this->searchParams);

And you should be good to go!

NetBeans IDE 6.5 - MSSQL Server Connection

Sure, I don't use this blog anymore but in the hopes that this will shorten the need for others to figure out how to do it...

1. Get and install the Microsoft SQL Server JDBC driver OR the opensource jtds driver (libjtds-java in Synaptic Package Manager)
2. Start up NetBeans
3. Menu -> Window -> Services (or CTRL-5)
   
4. Expand "Databases"
5. Right-click on Drivers and select "New Driver"
6. Click "Add..." in the "New JDBC Driver" window, navigate to the driver (/usr/share/java/jtds.jar), and click "Open"
7. Right-click on either the new "Microsoft SQL Server 2005" or "jTDS" driver option, select "Connect using..."
8. Fill in your database information (port 1433 is often not necessary to enter) and click OK
   
9. Here is the tricky part. It asks you for a Schema. Select "dbo"

Why the "SCHEMA" is "dbo" I do not know, nor do I care particularly. But it works.