A large query I created

This is for low user count systems, and low number of rows returned.
It returns information for financial planning aggregates.
It could be optimized a little more, I know.
Broken into 2 pieces, I could have cut this down a bit...first making a view, and then pulling from that...but then the summaries have to have blank text fields....so this might be simplest...seriously.


SELECT * FROM ((
SELECT IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = c.category_idAND cr.merger_id = '0'LIMIT 1 ), c.category_name ) category, IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = cc.category_idAND cr.merger_id = '0'LIMIT 1 ), cc.category_name ) subcategory, a.activity_name activity_name, m.ppp_abc, m.ppp_xyz, m.ppp_synergy, m.ppp_result, m.ppi_order, m.ppi_sale, m.ppi_cogs, m.ppi_rd, m.ppi_sm, m.ppi_admin, m.aap_action, m.aap_start, m.aap_completion, m.aap_manager, m.aap_status, m.api_order, m.api_sale, m.api_cogs, m.api_rd, m.api_sm, m.api_admin, (IFNULL( m.api_order, 0 ) - IFNULL( m.ppi_order, 0 ) ) AS fis_order, (IFNULL( m.api_sale, 0 ) - IFNULL( m.ppi_sale, 0 ) ) AS fis_sale, (IFNULL( m.api_cogs, 0 ) - IFNULL( m.ppi_cogs, 0 ) ) AS fis_cogs, (IFNULL( m.api_rd, 0 ) - IFNULL( m.ppi_rd, 0 ) ) AS fis_rd, (IFNULL( m.api_sm, 0 ) - IFNULL( m.ppi_sm, 0 ) ) AS fis_sm, (IFNULL( m.api_admin, 0 ) - IFNULL( m.ppi_admin, 0 ) ) AS fis_admin, a.activity_id activity_id, cc.category_id category_id, c.category_id subcategory_id, cc.parent_id, a.orderingFROM category ccLEFT JOIN category c ON cc.parent_id = c.category_idLEFT JOIN activity a ON a.category_id = cc.category_idLEFT JOIN `master` m ON m.activity_id = a.activity_idAND m.merger_id =0WHERE cc.parent_idIN ( 6, 0 ) ORDER BY cc.parent_id DESC , cc.category_id, a.ordering)UNION (
SELECT IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = cc.category_idAND cr.merger_id = '0'LIMIT 1 ), cc.category_name ) category, IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = c.category_idAND cr.merger_id = '0'LIMIT 1 ), c.category_name ) subcategory, 'Summary'activity_name, ''ppp_abc, ''ppp_xyz, ''ppp_synergy, ''ppp_result, SUM( ppi_order ) ppi_order, SUM( ppi_sale ) ppi_sale, SUM( ppi_cogs ) ppi_cogs, SUM( ppi_rd ) ppi_rd, SUM( ppi_sm ) ppi_sm, SUM( ppi_admin ) ppi_admin,'' aap_action,'' aap_start, ''aap_completion, ''aap_manager, ''aap_status, SUM( api_order ) api_order, SUM( api_sale ) api_sale, SUM( api_cogs ) api_cogs, SUM( api_rd ) api_rd, SUM( api_sm ) api_sm, SUM( api_admin ) api_admin, (SUM( api_order ) - SUM( ppi_order ) ) AS fis_order, (SUM( api_sale ) - SUM( ppi_sale ) ) AS fis_sale, (SUM( api_cogs ) - SUM( ppi_cogs ) ) AS fis_cogs, (SUM( api_rd ) - SUM( ppi_rd ) ) AS fis_rd, (SUM( api_sm ) - SUM( ppi_sm ) ) AS fis_sm, (SUM( api_admin ) - SUM( ppi_admin ) ) AS fis_admin, a.activity_id, c.category_id, c.parent_id, c.parent_id, 1000000000FROM `activity` aLEFT JOIN `master` m ON m.activity_id = a.activity_idLEFT JOIN category c ON a.category_id = c.category_idLEFT JOIN category cc ON cc.category_id = c.parent_idWHERE m.merger_id =0AND c.parent_id =6GROUP BY category, subcategory)UNION (SELECT IFNULL( (
SELECT category_nameFROM `category_rename` crWHERE cr.category_id = cc.category_idAND cr.merger_id = '0'LIMIT 1 ), cc.category_name ) category,
'Final Subtotal' subcategory, 'Summary'activity_name, ''ppp_abc, ''ppp_xyz, ''ppp_synergy, ''ppp_result, SUM( ppi_order ) ppi_order, SUM( ppi_sale ) ppi_sale, SUM( ppi_cogs ) ppi_cogs, SUM( ppi_rd ) ppi_rd, SUM( ppi_sm ) ppi_sm, SUM( ppi_admin ) ppi_admin,'' aap_action ,'' aap_start, ''aap_completion, ''aap_manager, ''aap_status, SUM( api_order ) api_order, SUM( api_sale ) api_sale, SUM( api_cogs ) api_cogs, SUM( api_rd ) api_rd, SUM( api_sm ) api_sm, SUM( api_admin ) api_admin, (SUM( api_order ) - SUM( ppi_order ) ) AS fis_order, (SUM( api_sale ) - SUM( ppi_sale ) ) AS fis_sale, (SUM( api_cogs ) - SUM( ppi_cogs ) ) AS fis_cogs, (SUM( api_rd ) - SUM( ppi_rd ) ) AS fis_rd, (SUM( api_sm ) - SUM( ppi_sm ) ) AS fis_sm, (SUM( api_admin ) - SUM( ppi_admin ) ) AS fis_admin, a.activity_id, c.category_id, c.parent_id, (c.parent_id - 0.5) parent_id, 1000000000FROM `activity` aLEFT JOIN `master` m ON m.activity_id = a.activity_idLEFT JOIN category c ON a.category_id = c.category_idLEFT JOIN category cc ON cc.category_id = c.parent_idWHERE m.merger_id =0AND c.parent_id =6GROUP BY category))combinedORDER BY parent_id DESC , category_id, ordering

How the city hurts your brain

http://www.boston.com/bostonglobe/ideas/articles/2009/01/04/how_the_city_hurts_your_brain/

Clickjacking

Hold on to your milk and cookies. That's not Santa's Christmas list for Peter you're clicking on. I mean it is, but you've just send someone your bank account information. Thought you were safe without having your JavaScript enabled? Think again.

Explanation:
http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9115818

Proof of Concept:
http://www.planb-security.net/notclickjacking/iframetrick.html

How do you fix it? Mostly, you can't but this helps.
http://noscript.net/

The lesson for developers:
Prevention helps somewhat, like developing sites with short cookie life, and detecting how a page is opened to prevent misuse.

The lesson for end users:
Don't work with trusted and untrusted sites simultaneously. Work with secure sites with no other sites open.

Video format basics you wish you knew about

Once upon time, video codecs and formats were really only the concern of AV nerds, anime freaks and hardcore not-so-legal movie downloaders....

More here.

Why simply CAPTCHAs are not enough

I was looking at different CAPTCHAs today and reading about the failures their various failures. The problem as it exists is not that we can not find a problem that requires a human to solve. That is only the first peice, because CAPTCHAs as a rule defeat automation and then only part of the time. That leaves computers working in isolation (after a program of hack tool has been created) to succeed only some of the time. This does not preclude hack programs from doing all the busy work and showing a human a CAPTCHA to solve in order to continue.

Imagine the very best case scenario of this - a single person who has developed program to expedite spamming 100 times doesn't need to enter in all 100 complete entries, but instead solve 100 CAPTCHA alone. Now try to imagine the worst case scenario - programmer has developed a program to solve CAPTCHAs automatically by farming them off as CAPTCHAs to his porn site for willing patrons to fill out and verify (similar in a sense to the work of the reCAPTCHA project).

This leads us back to square one, because either we are now battling a potentially less intelligible human or the one who developed the measure to break the CAPTCHA in the first place.

Several solutions as I see it exist to this problem:

1) Variety- Add fresh dose of variety to the CAPTCHA creation. In order to prevent CAPTCHAs from being broken as easily they need to do things that even people are not expecting...at least not all the time. CAPCHTAs can have differing instructions included for how to solve them. "Enter all but the third letter" or "Enter only the vowels from above". They can have pieces that play into solving them that are not in the same place in the page or in the same place on each page. They can be moving (flash based) CAPCHTAs or items that are dynamic in nature.

2) Statistical trustworthiness - People are most trusted when they act like we expect normal people to act. They post up to a certain limit per day. They take a certain amount of time to get things done. They do certain things, and avoid others. By flagging the actions and inactions of trustworthy perople, machines and their related information, we know enough about them to know whether thay should be allowed to create another account, or post that message. We do not have to mine data everywhere to accuire this kind of information, but we do have to assume that anyone that starts with a blank slate could be as wretched as the worst spammer.

3) Private/Public Keys - Implement something like PGP (Pretty Good Privacy) which has a Private/Public Key system to sign documents, require a registration, and use the system to authenticate as a real person. This eliminates privacy just as Statistical Trustworthiness begins to do.

Looking at the kinds of solutions that are possible, the somber conclusion is that Application Service Providers are either going to have to work much harder, or the internet is going to have to become more regulated to keep spam and misuse from becoming a larger threat.

Adobe AIR and real Ajax advantage

When originally confronted with Adobe AIR, it seemed silly to be developing anything like that for a desktop when the browser is two clicks away. However, take a look at Google's Gmail application and I think you see many of the same ideas are leveraged into the user experience.

Connectivity - When the connection dies on a page, it literally kills the user experience. Google and Adobe both know and combat this with the appropriate ajax.

Load Time - How many times should your interface graphics and interaction logic have to be reloaded? With intelligent design, not more than once a day. This frees more bandwidth for unique data that is important to the user and minimizes wait time. Everyone hates the fact that windows takes anywhere from a half minute to several minutes to load, yet no one complains about a couple seconds of load time. All of that time accumilates however and people spend upwards of 10 to 20 minutes daily waiting for page loads.

Ease of Design - Have you ever tried to make a windows app look good? I can guarantee you...not fun, and not usually very easy. The dev time on true windows applications tends to be longer than web applications. However, once you've developed the web application, an ajax or Adobe Air application is the next logical step.

Memory Footprint - Air applications should have a smaller overall footprint than IE and Firefox if for no other reason than because Flash Applications have always been produced to load only what is needed. That means you for all proctical purposes should be truncating features you don't need from your browser. With IE and Firefox, you are pretty much stuck with whatever they give you.

Spry - A better Ajax?

When I had first heard of Spry - Dreamweaver's solution to the ajax craze, I couldn't see the advantage to it. I suppose being married into Dreamweaver theoretically makes it advantageous, but for all the simplicity and intuitiveness that jquery brings to the table, I find that it is hard to beat.


Spry does feature some good demos, but it has little in the way of real community so far as I can tell. Their demos seem fast and well done...certainly nothing to complain about from a front end perspective. The documentation also seems rather available.

Stupid jquery tricks

•You have a form that needs an intermediate confirmation page.

This is easiest done by making the form a self-submit form. Use jquery styles to hide the plaintext version of what is input, so it only shows during confirmation, Make a class called confirmation and hide it during the first run. On the second run, show the plaintext and hide the input boxes (or change them to type hidden).

<script language="JavaScript" type="text/javascript" src="./jquery.js">

  <script language="javascript">

  $(document).ready(function() {

  $('.confirmation').hide();

  $('.inputfields').show();

  });
 </script>
 

•Modify a style for an html element without access to the header section of the document.

<script language="JavaScript" type="text/javascript" src="./jquery.js">

<script language="javascript">

$(document).ready(function() {

$('label').css("font-weight","bold");

});

</script>

•Make an item by id show or hide by clicking on anchors.

By applying a unique id to anchors with class 'edit', we can distinguish which items they are supposed to show and hide by appending to that id and making a new unique id based off of the original.

<script language="JavaScript" type="text/javascript" src="./jquery.js">

<script language="javascript">

$(document).ready(function() {

$('a.edit').click(function() {

$('#' + this.id + '_hide').toggle(400);

});

});


</script>

Sometimes you want a GET and a POST - Javascript Howto

There may be a time you prefer something to submit via POST, but want to make the variables bookmarkable for easy reference. (One reason to do this is if uploading a file. Obviously you can't hold the file in a get variable but all of the options concerning the upload can be held in this fashion.) This is a cheap easy way to do that:

Remember to put the code block under the form declaration, or the last time won't work.

<script type="text/javascript">
function concatenateFields() { 
concatenateFields = '';
for(i=0;i<document.forms[0].length;i++) {
  concatenateFields += document.forms[0].elements[i].name + '=' + document.forms[0].elements[i].value + "&" ; 
 }
  return concatenateFields;
}
function changeAction(){
  actionfile = 'try.php?';
  document.forms[0].action = actionfile + concatenateFields();
  alert(document.forms[0].action);
  return false;
}

document.forms[0].onsubmit = changeAction
</script>

Insert and count tags automatically using MySQL 5.0/5.1 Triggers

Sometimes, for applications, you don't want or desire the trouble of having to insert pieces of a title for say a blog, or a quoted phrase, into some database that contains/tracks your tags. Such a table probably contains fields about an auto inserted tag, such as the identifier itself, how many times the identifier is found, and whether it is worth utilizing in a popular tag list.


Now, with the power of MySQL triggers you no longer have to rely on the power of outside languages to harness these solutions. There are still some limitations to remember with MySQL triggers.For one, we can not call other stored procedures through a trigger. However, that is really more an inconvenience in organization than in functionality.


Below I will highlight some of the code to update a tag table from an insert to a table I call phrases.

DELIMITER //

CREATE TRIGGER `db_name`.`ins_phrases_update` AFTER INSERT ON `db_name`.`phrases`
 FOR EACH ROW BEGIN 

    declare breakfound numeric default 0;
    declare phraselength , l_loop, temp int default 1;

    set phraselength = length(new.phrase); 
 
    while l_loop < phraselength do
       
       SELECT LOCATE(' ', new.phrase, l_loop) INTO breakfound ;
       
  IF breakfound > l_loop THEN
   INSERT INTO tags (name) VALUES (LOWER(SUBSTRING(new.phrase, l_loop,breakfound-l_loop))) ON DUPLICATE KEY UPDATE total=total+1;

   set temp := l_loop;
   set l_loop := breakfound + 1;
  
  ELSE
   INSERT INTO tags (name) VALUES (LOWER(SUBSTRING(new.phrase, l_loop,phraselength-l_loop))) ON DUPLICATE KEY UPDATE total=total+1;
   
   set temp := l_loop;
   set l_loop := phraselength;
    
  END IF;

    
    end while;

END
//

A few notes of warning to MySQL developers.

• Only one trigger per table operation. While that may sound limiting, I think that again an originizational inconvenience more than anything.
  
• SET can not be used to increment the variable it is acting on. It seems rather Cobal-esque, but you have to declare a temp variable to hold information on any kind of increment or reassign.
  
• Command line for whatever reason seems easier at the moment to create triggers than phpMyAdmin. Possibly my own setup, but if it's an issue, drop to commandline and try the same thing.
  
• Remember DELIMITER before a trigger write, otherwise your MySQL will think you're ending the trigger at the end of your first SQL statement.
  
• This should make sense, but you can not change a "new.value" on and AFTER INSERT operation. With some languages, like php, you can change almost anything temporarily at least, even if it is a special variable.
  

I have only highlighted the INSERT trigger here. Clearly to make this functional, you need also the DELETE, and maybe the UPDATE triggers. The others you will find are trvial, as with DELETE all you need to do is change every "new.phrase" to "old.phrase" and you are pretty much done. UPDATE will need to combine the DELETE and the INSERT operations.

Dreamweaver CS3 aka the "Special Version"

I'm not sure what has ever really been the issue with CS3 but it has in my estimation been the most "crash happy" version of Dreamweaver out there to date. What is really irksome is that they never released any kind of update for this issue, but instead have cranked out another version which is supposed to be an improvement of sorts.

Why does it even crash? The error notices are purposely vague as to this, but I think it has to do with validation checkers breaking the normal function of the program. If there was ever a version of Adobe that could be classified "specials needs", I think this would be it.