Older blog entries for amars (starting at number 312)

MySQL woes...

For while, actually for as long as i've been working with MySQL, i've been trying to figure out the best way to handle heirarchal relationships between rows. I have three goals, to fetch all children resulting from a single node, count all children from a single node and have them in their respective order.

This simplest, most obvious choice is to have a column to store a node id and a column to store the id of the node's parent. The problem with this method, with regards to MySQL, is to achieve my three goals, one would have to recursively traverse the database via sql call in a recursive function. While it's easy to, say, fetch the immediate children of any given node, it's not good when i want to fetch everything and have it in order.

My most recent attempt is as follows...

Two tables, one with the nodes and the other to handle the relationships. the node table isn't important... just know that there is a unique node id that corresponds to a node in that table. The other table, i call the signature table or more appropriately the relationship table. In the relationship table i have a unique relationship id, for the sake of being able to update/delete it from the command line, a node id, a node signature and a depth. node id is obvious, a node signature consists of the nodes id appended to it's parents signature, separated with ':' (example: :1:2:3:4:) and the depth, calculated upon entry into the database is how deep the node is in it's particular thread.

With such a method, i am able to achieve my first and second goal easily with the use of LIKE. The problem arises when i try to order the results from MySQL. First off, since the signature is text, ORDER BY orders the results alphabetically, which puts 10 before 9, so i can't do a straight ORDER BY signature. The solution is to break it up by parts and order by each part successively. But that requires prior knowledge of how deep the thread goes so you can construct a string of parameters to pass to ORDER BY, which is where the depth comes in.

So theoretically, upon proper depth calculation upon entry, i can achieve my goal in two SELECT statements, first the one to determien the length and the other to select with successive ORDER BY parameters. The next problem therein is the fact, again, that ORDER BY orders alphabetically, so 10 still comes before 9. The solution to that is to type cast it to decimal, which is only supported in 4.x, so i can't use that. The next option is to convert between bases. Since substring_index returns text, it can be seen as outputting a number of base 36 (0-9a-z), which mysql supports.

But that doesn't work either... for some reason MySQL isn't sorting beyong the first parameter... it seems to be ignoring the rest.

So, i'm at a loss. I was hoping i could do this without having to sort it via PHP. If someone knows about this type of thing, i'd be interested in some pointers in the right direction. This kind of stuff pops up all the time, most notably in comment systems. Right now my best option is to use my signature method to fetch all children and sort it with PHP. Sample query is below.

select substring_index(substring_index(signature,':',5),':',-1) as 'e',
substring_index(substring_index(signature,':',4),':',-1) as 'd',
substring_index(substring_index(signature,':',3),':',-1) as 'c',
substring_index(substring_index(signature,':',2),':',-1) as 'b',
signature_id,ticket_id,signature,depth from tickets_signature
order by conv(substring_index(substring_index(signature,':',2),':',-1),36,10) asc,
conv(substring_index(substring_index(signature,':',3),':',-1), 36,10) asc,
conv(substring_index(substring_index(signature,':',4),':',-1),36,10) asc,
conv(substring_index(substring_index(signature,':',5),':',-1) ,36,10) asc;

And resulting output...

| e    | d    | c    | b    | signature_id | ticket_id | signature    | depth |
|      |      |      | 1    |            1 |         1 | :1:          |     0 |
|      |      |      | 2    |            2 |         2 | :2:          |     0 |
|      |      |      | 3    |            3 |         3 | :3:          |     0 |
|      |      |      | 4    |            4 |         4 | :4:          |     0 |
|      |      | 14   | 4    |           14 |        14 | :4:14:       |     1 |
|      | 15   | 14   | 4    |           15 |        15 | :4:14:15:    |     2 |
|      | 16   | 14   | 4    |           16 |        16 | :4:14:16:    |     2 |
| 17   | 16   | 14   | 4    |           17 |        17 | :4:14:16:17: |     3 |
| 18   | 16   | 14   | 4    |           18 |        18 | :4:14:16:18: |     3 |
|      |      |      | 5    |            5 |         5 | :5:          |     0 |
|      |      |      | 6    |            6 |         6 | :6:          |     0 |
|      |      |      | 7    |            7 |         7 | :7:          |     0 |
|      |      | 10   | 7    |           10 |        10 | :7:10:       |     1 |
|      | 13   | 10   | 7    |           13 |        13 | :7:10:13:    |     2 |
|      |      | 11   | 7    |           11 |        11 | :7:11:       |     1 |
|      | 12   | 11   | 7    |           12 |        12 | :7:11:12:    |     2 |
|      |      | 8    | 7    |            8 |         8 | :7:8:        |     1 |
|      | 9    | 8    | 7    |            9 |         9 | :7:8:9:      |     2 |
22 Feb 2003 (updated 22 Feb 2003 at 00:19 UTC) »

From the PostnUKE API reference...

2.1. Users

This area covers everything related to users. Confirming that a user is logged in, getting a user-specific configuration parameter, or logging a user into the PostNuke system are examples of functions that fall into this area.

Yeah, real fucking useful... if you aren't going to bother saying anything about the topic, why include it at all?

Needless to say, i'm not impressed in the least bit with PostnUKE at all...

Going through the "module" code, because i've been given the task of developing a postnuke module for a client. What good does a templating "engine" do when you have stuff like, OpenTable(); followed by echo "more html"; inside of modules, outside of the realm of templating... is not the goal of templating to separate logic from presentation? Even still, within a module a programmer has to include header.php and footer.php. In 30K+ lines of code/bloat, you'd think that kind of stuff would be handled in a much more elegant manner.

Also, in the Template module, which is supposed to be used as a guide to build new moduels from, there exists a index.html instead of index.php... nowhere is it mentioned in any of the documentation that modules.php looks for *.php. Putting together an empty template to build up from nullifies it's utility when A) documentation is left out and B) you have to do a code walk-through to figure out why the web interface says everything works and was installed properly but modules.php gives a 404 error. Which brings up another issue, why mix terminology? modules.php exists, yet error.php is called and produces a 404 HTTP error.

Another rather disturbing occurence is the fact that the database user/host/passsword is stored in $GLOBALS, accessible to any module, thus overriding the extensive code bloat added to implement variable access permissions. Why is this not seen as a security threat? Someone could easily create and disstribute a module that exposes that information and even exploit it or someone with CVS access could hide such funcionality in the code somewhere... I shouldn't have to worry about trusting the PostnUKE developers protecting such data.

If one more person talks about how great PostnUKE is, i think i'm going to puke... and this is only the beginning.

I'm finding OmniOutliner to be extremely useful as of late. I never really considered usng my powerbook to take notes in class until I discovered it came installed on my powerbook. The OmniGroup/OmniOutliner is another perfect example of why i'm liking the OS X era Apple "platform", as opposed to say Windows or even Linux.

Generally, there are two options for taking notes in a lecture type setting when using a laptop. There's the basic text editor, be it vim, emacs, pico/nano, whatever and there is a word processor, usually MS Word.

While MS Word, AbiWord, Open/Staroffice, etc... have built in facilities for building outlines, they are primarily aesthecally driven, for the purpose of formatting text for presentation, not necessarily producing something that is functional or even useful. I remember having to go through the steps of producing an outline for a professional communications class and it was damn near painful, it's hard to predict exactly how the word processor is going to handle the outline especially after the fact. In addition, that's alot of processing overhead for something that doesn't do a very simple task very well.

While the standard text editor eliminates the bloat and unpredictability of a word processor, what you end up with is an outline that becomes cumbersome when you start having to go back and make changes. Also, (while more than the word processor) what you end up with isn't very functional.

OmniOutliner solves both problems. There is relatively low processing overhead to do one thing very well to produce something that is very functional. I've found it very easy to use on my powerbook as it can be easily controlled without the use of a mouse, which helps to keep up with the professor. It's hard to understand how truly easy it is to use form the keyboard without actually using it... cmd+] to indent, cmd+[ to go up a level, up/down arrows to navigate cmd+left/right to expland/collapse a tree and return to add a new row. What you end up with is an outline that can be navigated in the expandable/collapsable tree style in a number of formats, including the default Apple style plist XML serialization format, Rich Text, HTML and ASCII. The advantages of using the Apple Property List format is that functionality is built into the cocoa framework to interact with plist with ease so external apps can make use of the outlines produced by OmniOutline without alot of work, and since it's standardized XML, it'd be relatively easy to interact with the data from any programming language. Not only that, the application itself is scriptable via AppleScript. OmniOutliner also supports many of the formatting that one would expect, like various numbering conventions.

It's not Open Source, but that only becomes a problem when the program has shortcomings, right? Besides, The Omni Group is very develoepr friendly, when it comes to providing the developer with the tools he/she needs.

Anyways, just spreading the word. I recommend anyone who has access to a mac to check it out... and if you don't have a mac, regardless of you're GNU/BSD/Linux/UNIX/Windows tendancies, get one!

Wow. I've never really liked the term blog or blogs in general but always lacked the words to explain why. I think cactus nailed it right on, "ego-masturbation", that's exactly the word I was looking for.

Ever since i started using iChat in OS X, people would complain that when i send them links that i've copied and pasted that the link is dead or doesn't work. Almost always they will simply come back to me and say the link doesn't work and are too lazy to copy and paste the link from their aim window to their browser.

So tonight, with the participation of a seemingly willing person on the other end of the conversation i attempted to diagnose the problem. I thought perhaps it was the way OS X handles urls in the clipboard, that perhaps they are treated differently and there was meta data attached to the link that isn't displayed. By running ethereal, i was able to capture the information that is sent, as it is sent, to see if iChat was adding special characters/formatting to the link (which it does when you copy/paste RTF from one app to another) or if the problem is with AIM.

Typical message as it is sent by iChat:

<body ichatballooncolor="#EBEBEB" ichattextcolor="#000000">
<font face="Helvetica" ABSZ=12 color="#000000"><a href="http://www.google.com">http://www.google.com&lt/a></font>

So, obviously there isn't anything that could be distorting the link in iChats side, unless the ichat* attributes are what fucks it up, or maybe it's the ABSZ=12, neither of which AIM sends me. Unless it's because of those extraneous nonstandard tags which don't seem like they would have any effect on the links at all, it's AIM that is at fault here. But the question i can't seem to answer is why people seem to have a problem with only me. there's never a problem with others and AIM to AIM or me with iChat to iChat or even AIM to iChat. It only happens with iChat to aim, which doesn't make any sense at all, none whatsoever, but... because i'm on a mac and they are on windows and because aim /windows doesn't interpret anchor tags properly, it's my fault because i'm the one using iChat, and that really bugs me. My standard response from here on out will be to just copy and paste the damn link, i don't know why it doesn't work.

Though, after doing this, i tend to think that AOL is doing this bass-ackwards. Sending HTML just seems like a bad idea. The least they could do is exchange CSS stylesheets at the beginning of each chat session instead of having these non-standard non-consistent html attributes. The ideal way to handle it would be (like jabber) to send XML according to a standardized AIM schema with stylesheets to specify formatting details.

Something else noteworthy, iChat seems to simply serialize the chat session when it wants to save a chat session, instead of exporting it to plain-text as a typical chat log. It does this, i suppose, so it can easily restore actual chat sessions instead of serving as a way of keeping logs of chats.

Either way, i seem to have learned something, even if i haven't really solved the riddle of why people can't use the links i send them. At least now i can blame windows guilt-free.

CBS really needs to pick better headlines. "Bullets Found In Colombia Crash Bodies", is sure to be taken the wrong way.

Trash talking
OSCommerce sucks. Do us all a favor and don't use it. Today, I fixed another bug for a client. I'm guessing UPS must have changed it's API recently as far as fetching available shipment options go and converged GNDRES and GNDCOM into simply GND. There is no documentation, nor are there any comments in the code to explain how the UPS shipping module works, so it took some clever curling and to realize that UPS wasn't providing what OSCommerce was expecting.

Hopefully. google will pick this up and someone with a similar problem will pick it up. Maybe the OSCommerce people have fixed it in newer versions, but for those of us for whom it is not an option to simply upgrade because of customizations we've made and use the UPS shipping module and are having problems, one must simply add the GND type to $this->types in the ups class constructor in includes/modules/shipping/ups.php and call it, say, "Ground" or whatever seems appropriate.

Bah. don't know how I can properly convey what I'm trying to say without insulting PHP, but I hate it. What was once something I was enjoying and enthusiastic about is now so routine and menial to the point that I can't stand it. Honestly, to the outsider, I don't have much to complain about, i'm employed (and paid), there's no shortage of work, get to work from home or anywhere there's internet access, for the most part set my own schedule so I can attend school full-time, don't have a dress code to adhere to and have a relatively great deal of creative freedom, as programming goes and managed to have enough to take two 3+ week vacations out of the country. But... as i said it's gotten routine, and routinely annoying as clients have demands but don't know what they want nor do they provide any restrictions or guidelines to follow but complain and want changes after i've started or wrestling over problems with well known and respected open source PHP projects because they were written by monkeys and don't work or talking on the phone or communicating by email with someone who obviously has absolutely no idea what they are talking about and oblivious to their ignorance on the subject.

I've since grown to hate the web as a development platform, if that makes sense.

But I have bills to pay. Dammit.

Well, i solved my only complaint with OS X and my powerbook. At first, it was unclear if i could have my main desktop displayed on my external monitor with the LCD of the powerbook acting as the secondary monitor. Thanks to the Apple forums someone pointed out that in addition to being able to drag the desktop orientation in the arrangement tab of the preference pane, you can drag the menu bar to either desktop. So I did, and now have my main desktop on my 20" monitor with my powerbook on the side running iChat and iTunes.

I'm in heaven.

I'll write a review but wait until after i've used it a full week.

I am the latest proud owner of an Apple 12" PowerBook G4. I opted for additional memory bringing it to 384 Mb and the AirPort Extreme card. Going through the process of setting it up the way i like it. Will write a detailed review sooner or later.

303 older entries...

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!