thinc's Journal thinc's use Perl Journal en-us use Perl; is Copyright 1998-2006, Chris Nandor. Stories, comments, journals, and other submissions posted on use Perl; are Copyright their respective owners. 2012-01-25T02:43:59+00:00 pudge Technology hourly 1 1970-01-01T00:00+00:00 thinc's Journal Finding the postgresql tables that have been clustered <p>At $work we have a job that runs every night that calls clusterdb [1] on our database. clusterdb reruns cluster [2] against any tables that have previously been clustered.</p><p>If you want to know which tables have previously had cluster run on them, the following query does the trick:</p><p><code>SELECT pg_class.relname, pg_index.indisclustered FROM pg_class JOIN pg_index ON pg_class.oid = pg_index.indrelid AND pg_index.indisclustered = 't';<br></code></p><p>This will list all the tables that cluster will reorder.</p><p>[1] <a href=""></a></p><p>[2] <a href=""></a></p> thinc 2010-04-30T20:30:51+00:00 journal DBD::Sybase on solaris 10 <p>Here we go again. This time I need DBD::Sybase on solaris 10 x86.</p><p>First thing you need is the sybase SDK for solaris 10 x86. I think you have to pay for that (the database group took care of that for me, so I have no idea.)</p><p>It gets installed to<nobr> <wbr></nobr>/opt/sybase</p><p>Then to compile DBD::Sybase against it, you do this:</p><p>1. export PATH=$PATH:/usr/sfw/bin/<br>2. export SYBASE=/opt/sybase/<br>3. export SYBASE_OCS=OCS-12_5<br>4.<nobr> <wbr></nobr>/usr/perl5/bin/perlgcc Makefile.PL<br>5. gmake<br>6. gmake test<br>7. sudo gmake install</p><p>1 - you need gmake in your path<br>2,3 - the Makefile.PL looks for the libs you are compiling against in $SYBASE/SYBASE_OCS/lib<br>4 - use the perlgcc for solaris magic<br>5,6,7 - the standard perl module installation, but with gnu make.</p><p>also in your apache configuration (if you're going to be using DBD::Sybase with apache, you need:<br>SetEnv SYBASE<nobr> <wbr></nobr>/opt/sybase/</p> thinc 2006-08-17T00:04:45+00:00 journal perl modules vs. solaris 10 <p>So I've got this solaris 10 workstation, and I want to install DateTime on it.</p><p>No problem, I think, just fire up cpan and "install DateTime".</p><p>Yeah right. This is solaris. Of course I don't have "Sun WorkShop" installed. So I install gcc, and gmake.</p><p>But wait, perl was compiled with Sun WorkShop, so the configure flags are different.</p><p>Then I find out about perlgcc. It works beautifully.</p><p>Here's the magic of how to compile perl modules on solaris 10 without having Sun developer tools installed:</p><blockquote><div><p> <tt># echo $PATH<br>/usr/sbin:/usr/bin:/usr/local/bin/:/usr/sfw/bin/<br>#<nobr> <wbr></nobr>/usr/perl5/bin/perlgcc Makefile.PL<br>...<br># gmake<br>...<br># gmake test<br>...<br># gmake install</tt></p></div> </blockquote><p>Note the<nobr> <wbr></nobr>/usr/sfw/bin/ on path to pick up the gcc and gmake.</p><p>Thank you Alan Burlison and perlgcc, you just made my job a lot easier.</p> thinc 2006-06-21T19:52:04+00:00 journal DBD::Sybase on Debian sarge <p>So I needed to get DBD::Sybase installed today. Here's my notes:</p><p>download:<br>---------<br>$ wget<nobr>m<wbr></nobr> <br>$ wget<br>$ wget</p><p>install:<br>--------<br># apt-get install alien</p><p>$ alien --to-deb sybase-openclient-11.1.1-3.i386.rpm<br>$ alien --to-deb sybase-common-11.9.2-3.i386.rpm<br># dpkg -i sybase-openclient_11.1.1-4_i386.deb<br># dpkg -i sybase-common_11.9.2-4_i386.deb</p><p>make a standard location<br>------------------------<br># ln -s<nobr> <wbr></nobr>/opt/sybase-11.9.2<nobr> <wbr></nobr>/opt/sybase</p><p>fix the locales<br>see<br>-----------------------------------------------<br>emacs<nobr> <wbr></nobr>/opt/sybase/locales/locales.dat</p><p>find the string [linux] and add this directly below:</p><p> &nbsp; &nbsp; &nbsp; &nbsp; locale = en_US, us_english, iso_1</p><p>add to ~/.bashrc:<br>-----------------<br>export SYBASE=/opt/sybase/<br>export PATH=$PATH:$SYBASE/bin</p><p>$ source ~/.bashrc</p><p>install the perl module:<br>------------------------<br>$ tar xvfz DBD-Sybase-1.07.tar.gz<br>$ cd DBD-Sybase-1.07<br>$ perl Makefile.PL<br>$ make<br>$ sudo make install</p> thinc 2006-04-12T20:49:30+00:00 journal postgresql functions <p>So a couple of years ago I started using postgresql, and <b>really</b> wanted a feature that I had used in MS-SQL, which is stored procedures that returned recordsets.</p><p>So I searched and searched trying to find out how to do it.</p><p>Turns out it was a new feature in 7.4 (or mabye 7.3) but nobody knew how to do it. So I kept searcing and finally found a newsgroup post by Tom Lane (I think) that halfway described it. This news post, combined with knowledge gained from my previous searches and investigations yeilded the solution I was after.</p><p>So, since I haven't seen it anywhere else (and it's not doing me any good not sharing it), here is the full text for a function (aka stored procedure) that returns a recordset.</p><blockquote><div><p> <tt>DROP TYPE get_contacts_return CASCADE;<br> &nbsp; <br>CREATE TYPE get_contacts_return AS<br>(<br>&nbsp; &nbsp; &nbsp; "first_name"&nbsp; &nbsp; &nbsp; varchar(50)<br>&nbsp; &nbsp; , "middle_name"&nbsp; &nbsp; &nbsp;varchar(50)<br>&nbsp; &nbsp; , "last_name"&nbsp; &nbsp; &nbsp; &nbsp;varchar(50)<br>&nbsp; &nbsp; , "nickname"&nbsp; &nbsp; &nbsp; &nbsp; varchar(50)<br>&nbsp; &nbsp; , "contact_id"&nbsp; &nbsp; &nbsp; bigint<br>&nbsp; &nbsp; , "title"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;varchar(20)<br>&nbsp; &nbsp; , "address_city"&nbsp; &nbsp; varchar(20)<br>);<br> &nbsp; <br>CREATE FUNCTION get_contacts(<br>&nbsp; &nbsp; &nbsp; integer&nbsp; &nbsp; &nbsp; &nbsp;--contact_type_id<br> &nbsp; )<br>RETURNS SETOF get_contacts_return AS<br>'<br>&nbsp; &nbsp; DECLARE<br> &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; in_contact_type_id&nbsp; &nbsp; &nbsp; ALIAS FOR $1;<br> &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; rec&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; get_contacts_return%rowtype;<br> &nbsp; <br>&nbsp; &nbsp; BEGIN<br>&nbsp; &nbsp; &nbsp; &nbsp; --===================================================--<br>&nbsp; &nbsp; &nbsp; &nbsp; --error checking<br>&nbsp; &nbsp; &nbsp; &nbsp; -------------------------------------------------------<br>&nbsp; &nbsp; &nbsp; &nbsp; IF( in_contact_type_id IS NULL ) THEN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RAISE EXCEPTION ''Invalid contact_type_id: % '' , in_contact_type_id;<br>&nbsp; &nbsp; &nbsp; &nbsp; END IF;<br> &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; --===================================================--<br>&nbsp; &nbsp; &nbsp; &nbsp; --query<br>&nbsp; &nbsp; &nbsp; &nbsp; -------------------------------------------------------<br> &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; FOR rec IN<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT DISTINCT ON ( contact.first_name , )<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; contact.first_name&nbsp; AS first_name<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , contact.middle_name AS middle_name<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , contact.last_name&nbsp; &nbsp;AS last_name<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , contact.nickname&nbsp; &nbsp; AS nickname<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,; &nbsp; &nbsp; &nbsp; &nbsp; AS contact_id<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , contact.title&nbsp; &nbsp; &nbsp; &nbsp;AS title<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,; &nbsp; &nbsp; &nbsp; AS address_city<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM contact<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; JOIN address ON<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; = address.fk_contact_id<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; contact.display = true<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AND<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; contact.contact_type_id = in_contact_type_id<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ORDER BY contact.first_name ,<br>&nbsp; &nbsp; &nbsp; &nbsp; LOOP<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RETURN NEXT rec;<br>&nbsp; &nbsp; &nbsp; &nbsp; END LOOP;<br> &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; RETURN null;<br> &nbsp; <br>&nbsp; &nbsp; END;<br> &nbsp; <br> &nbsp; ' LANGUAGE 'plpgsql';</tt></p></div> </blockquote><p>Note: The select statement is not complete, I have removed some of the where clause.</p><p>It boils down to this:<br>You create a new "recordset" return type, populate it with your select statement, then return it.</p><p>I have no idea if it is faster, uses less memory or if it is any better at all than calling straight sql from dbi, I just thought it was cool and wanted to try it. And <b>They</b> ave always told me that stored procedures are faster and "more efficient" than just sending queries to the database.</p><p>Oh, to call it, use this:</p><blockquote><div><p> <tt>SELECT<br>&nbsp; &nbsp; &nbsp; "first_name"<br>&nbsp; &nbsp; , "middle_name"<br>&nbsp; &nbsp; , "last_name"<br>&nbsp; &nbsp; , "nickname"<br>&nbsp; &nbsp; , "contact_id"<br>&nbsp; &nbsp; , "title"<br>&nbsp; &nbsp; , "address_city"<br>FROM get_contacts( "contact_type_id" );</tt></p></div> </blockquote> thinc 2004-11-11T09:00:59+00:00 journal Makefile fun with Test::Inline <p>So I was playing around with Test::Inline, and wanted to integrate it with the little Makefile I use to ease my development.</p><p>Finally figured out this:</p><blockquote><div><p> <tt>pod_tests: lib/*.pm<br>&nbsp; &nbsp; &nbsp; &nbsp; $(foreach file,$?,pod2test $(file) t/$(notdir $(file)).t<nobr> <wbr></nobr>;)<br> &nbsp; <br>test: pod_tests<br>&nbsp; &nbsp; &nbsp; &nbsp; perl -I$(LIB) -MTest::Harness -e 'runtests(@ARGV)' t/*.t</tt></p></div> </blockquote><p>This generates the inline tests and saves them as t/ everytime I run make test, which works great for what I need.</p> thinc 2004-07-20T20:47:32+00:00 journal wOOt! dishwasher NOT broken <p>Upon further inspection, the diswasher has a "rinse and hold" button which was selected. Pressing "normal wash" resolved the problem. Doh!<nobr> <wbr></nobr>/me slaps forehead.</p><p>Next up: natural language product compatibility:</p><p>cake requires and is compatible with frosting<br>cake:flavor:carrot does not require and is not compatible with whipping cream<br>etc...</p> thinc 2004-06-28T17:13:33+00:00 journal on to the next.... <p>So I knocked the product dependencies into submission, and even gave the product page a nice dhtml tree view (Tree::Simple::View). Now I just have a few niceties to put in (expand all) and displaying the products with their parents in appropriate places ("Cake: Frosting: Chocolate" instead of just "Chocolate").</p><p>In other news, our dishwasher died...luckily I know of a used dishwasher place, and they should be able to repair it or sell us a new (used) one. We'll see.</p> thinc 2004-06-28T17:05:59+00:00 journal Might as well start my own <p> I've started reading others journals and decided that since I'm enjoying it I might as well start my own. I've always been really bad at keeping these things up to date, so we'll see how it goes. </p><p> Well to start, I'm working on this application for a little hotel/resort so they can track their reservations. I got stuck today trying to figure out "product" dependencies. You see, they offer cakes, and you can specify the size of the cake, the type of cake (white, chocolate, carrot), the type of frosting and filling if you want it. So I had to figure out how to make it so that if they select "Whipping Cream" frosting, it only allows you to have "White" cake. </p><p> My original spec only allowed for a one to one (ex: cake requires frosting), but as I was coding it I realized that would not work. There are 4 types of frosting and 8 inch round cake cannot require all of them. </p><p> So I have to redo some of it so that products are in groups and you can have a generic "cake" require "frosting" which has sub items of the frosting types. Then you can also have single dependencies (Whipping Cream Frosting requires White cake). </p><p> So I can have my cake and eat it too...(ouch that was bad) </p> thinc 2004-06-28T04:07:40+00:00 journal