cftopper.com

MySQL - Create a user with access to just one database

Quick Tip: I wanted a user with access to just one database so I looked up the docs and ran:
GRANT ALL ON mydatabase.* TO username@"%"
IDENTIFIED BY 'mypassword';

But when I logged in as this user, he could see all the databases. The only way to give the user full access to just one database, so far as I can see is by spelling out what the user can do with

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON mydatabase.* TO username@"%"
IDENTIFIED BY 'mypassword';
Posted by Topper at 11:27AM Permalink Comments [0] - Leave a comment

eTag()

eTags are cool - read more about them here - eTags.

eTag() is a handy function that you can use to perform client-side programmable caching of your coldfusion pages.

To get the benefit just call <cfset eTag( "My Page" )> and the next time a user visits this page, they will get a 403 not modified response and execution will stop here.

Getting advanced... add in the date..

<cfset eTag( "#pageName#_#lastUpdatedDate#" )>

... this will regenerate the page IF the lastUpdatedDate has changed.

We use this extensively in Teamwork Project Manager to reserve dynamic pages almost instantly. It also reduces server load because processing stops.

Here is the code:

<!--- eTag will return a 304 not modified response if possible --->
<cffunction name="eTag" output="no">
    <cfargument name="eTag" type="string" required="yes">
    <cfargument name="useHash" type="boolean" required="no" default="yes">
   
    <cfif useHash>
        <cfset ARGUMENTS.eTag = Hash( ARGUMENTS.eTag )>
    </cfif>
   
    <cfheader name="ETag" value="""#ARGUMENTS.eTag#""">

    <cfif ( StructKeyExists( CGI, 'HTTP_IF_NONE_MATCH' ) and CGI.HTTP_IF_NONE_MATCH contains ARGUMENTS.eTag )>
        <cfcontent reset="yes">
        <!--- nothing has changed, return nothing --->
        <cfheader statuscode="304" statustext="Not Modified">           
        <cfabort>
    </cfif>
   
</cffunction>
Tags: ColdFusion | Tips | Tools
Posted by Topper at 02:14PM Permalink Comments [6] - Leave a comment

The BP Party

THE BP PARTY: In about 7 weeks I am leaving the 20's behind but instead of getting depressed I am taking the opposite tact - Guys, you are all invited to my joint 30th birthday (with my legendary first cousin Brian) on the 21st February at The Maritime Hotel in Bantry.

The BP Party - your invited!

In case you can't read the writing, download the big version here - The BP Party (pdf)

And please spread the word in case I miss anyone.
Details again:

Date: February 21st 2009
Time: 9pm
Activity: Copious Drinking
Notes: It's fancy dress! You must come dressed up as something beginning with B or P. Prient, Princess, Baker, Pink, Boy George, Barney.. whatever you want..
Accomodation: Lots of B&Bs in town or just contact me directly - My house is crash friendly.
Posted by Topper at 09:13AM Permalink Comments [1] - Leave a comment

Form Wizard launched

Yesterday I released version 1.1 of Form Wizard for ColdFusion.

It will save your hours and hours of tedious work designing forms for your website, and then doing the javascript validation, database store, email notifications etc.

And the look and feel can be extended to integrate with any website design.


Form Wizard for ColdFusion


It's the shanizzle.
Tags: My Work | Tools | WebDev
Posted by Topper at 08:24PM Permalink Comments [10] - Leave a comment

Tip: Change the Railo port to port 80

We are really getting into Railo these days and enjoying it's blazing speed.
Here's a quick Railo tip..

To change the default railo server port from the current default of 8600 to port 80:

  1. Open the C:\Program Files\Railo\conf\resin.conf file.
    (You can use the free Notepad++ for this if you don't have another suitable editor, windows notepad won't do.)
  2. You'll find <http address="*" port="8600"/> around line 62
  3. Change that to port 80 and restart the server.
Tags: Tips
Posted by Topper at 07:57PM Permalink Comments [0] - Leave a comment

MySQL Optimisation Tip #2: Store counters in seperate tables

Store counters in seperate tables


A common scenario, you have a "pages" table and you want to store a hit counter in that table so you will know which pages are the most popular, you would have something like:

CREATE TABLE `pages` (
  `pageId` int(10) unsigned NOT NULL auto_increment,
  `pageName` varchar(255) NOT NULL,
  `pageHTML` text NOT NULL,
  `pageHitCounter` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`pageId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Now when you want to update the hit counter you will do something like:

UPDATES pages set pageHitCounter = pageHitCounter+1 WHERE pageId = 12

This is fine for normal database schemas... but if you are using the MySQL Query Cache feature (which you should), every time you execute the update you will invalidate the cache making the whole cache feature just about useless.

Instead what you can do is store the hitCounter for each page in a seperate table.

So add something like

CREATE TABLE `pageHitCounter` (
  `pageId` int(10) unsigned NOT NULL auto_increment,
  `pageHitCounter` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`pageId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and update that instead. That was a fairly contrived example but you get the idea - be careful not to invalidate the cache.
Posted by Topper at 06:38PM Permalink Comments [3] - Leave a comment

MySQL Optimisation Tip #1 - Turn on the query cache

The Query Cache


If your using MySQL in production you should turn on the query cache - this is off by default ( even if you have installed using the wizard and choosen "dedicated server" ).

The query cache basically remebers the results of your queries and saves them from being executed twice unnecessarily resulting in huge performance improvements.

Bare in mind that any inserts or updates will reset the parts of the query cache that relate to the affect tables. Luckily most applications read from the table much more often that they write data into them.

How to turn on the Query Cache


  1. Open your my.ini (or my.cfg) file in notepad and set query_cache_type to 1. There are 3 settings for this: 0 (disable / off), 1 (enable / on) and 2 (on demand).
    Tip: If you can't find this in your config file, so just add it in.
  2. Now you need to tell MySQL how much memory space to use. For this, find (or add) the query_cache_size option and set it to something that makes sense. This is a global setting so all sites and applications on the server will share this. You could try 30MB to start with
    query-cache-size = 20M
  3. You will need to restart MySQL for the caching to begin.


How to know of the cache is being used


After restarting your server, execute the following SQL:

SHOW STATUS LIKE 'q%';

You will get something like:

Qcache_free_blocks    1613
Qcache_free_memory    202554688
Qcache_hits    679493
Qcache_inserts    207317
Qcache_lowmem_prunes    0
Qcache_not_cached    4532
Qcache_queries_in_cache    3488
Qcache_total_blocks    8788

The important two are Qcache_queries_in_cache and Qcache_not_cached.

Qcache_queries_in_cache is the total number og queries that are currently cached in memory.

Qcache_not_cached is the total number of queries that were executed where MySQL could not use a cached version.

References

Tags: MySQL | Tips
Posted by Topper at 12:59PM Permalink Comments [1] - Leave a comment

ColdFusion Optimisation Tip #1 - Use StructKeyExists instead of isdefined

Use StructKeyExists instead of isdefined


Many of ye know this old nugget already so sorry if you know this already but StructKeyExists is much much much faster than isdefined.

As Simon Whaylay says:
"IsDefined() checks not just if a variable exists, but if it is also syntactically correct. This clearly has runtime implications. That is why, when dealing with structures you should avoid isDefined() in favour of structKeyExists()."

The Proof


TEST 1
IsDefined: #endTime#ms

TEST 2
StructKeyExists: #endTime#ms


The Result


        IsDefined over 100000 loops: 337ms

        StructKeyExists over 100000 loops: 36ms

References


  • http://www.simonwhatley.co.uk/isdefined-vs-structkeyexists
  • http://mkruger.cfwebtools.com/index.cfm?mode=alias&alias=isdefined%20vs%20structkeyexists
  • http://corfield.org/entry/isDefined_vs_structKeyExists
Posted by Topper at 12:57PM Permalink Comments [1] - Leave a comment

About Topper on ColdFusion

Peter Coppinger aka Topper is a neurotic web monster who spends most of his chaotic life developing ColdFusion web applications when not drinking himself into a stupor and scheming his plans for world dominance.

Peter founded Digital Crew way back in 1999. Digital Crew run CFTagStore.com and have also produced lots of powerful ColdFusion tools like ProFlashUpload and CFMyAdmin.

“I made this site to share my thoughts, tips and tools with fellow ColdFusion developers.

If your a ColdFusion developer, go ahead and subscribe to this site and in exchange i'll try to provide quality content to make it worth your while.”
RSS Feed for Topper on ColdFusion

    I'm speaking at CF-United Europe!

    CFDevCon I'm going to be speaking at CFDevCon08! It's my second time speaking in front of more than 10 people so please lend your support.

    The topic is:
    Introducting TeamworkCMS and Site Engine - Building better websites in half the time or something like that..

    My ColdFusion Software

    My Blogs

    Digging

    My Work - Just Finished

    • modules.cit.ie
      Web-=based modules/programmes designer tool and database system for Cork institute of technology.
    • Teamwork Project Manager
      The top secret project is finally released. The project management app will rock your world - give it a go.
    • PMG
      New website for Project Management Group website.
    • Digital Warehouse Wholesale
      Added wholesale products to existing client website.
    • New Digital Crew documentation website
      New version of documentation.digital-crew.com using new InfinityCMS site engine. It's done now. Just add content.
    • PFH Company Webite
      New website/CMS/Newsletter System for prestigious Irish IT company.
    • Module Manager for CIT
      CIT is switching to module based courses. We are making an application for managing/submitting these modules. Gettig there.
    • Bons Secours Cork Hospital Intranet
      New Intranet for Bons Secours hospital in Cork. Considering turning this Intranet system into stand-alone product.
    • Revamping InfinityCMS
      I'm making major improvements to our content management solution, InfinityCMS. Making it faster, more powerful and easier to check into/out-of source control. Done but it's always going to be evolving.
    • BPC Update
      Minor functionality update for internal Pfizer Best Process Chemistry project.

    Archive

    Tags