PostgreSQL : Move your stuff out of the public schema

Using the default public schema is not recommended. If you have created a bunch of tables and functions in the public schema, you can use the following scripts to move them to a different schema:

Move tables:

  DO$$   
  DECLARE   
   row record;   
  BEGIN   
   FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'   
   LOOP   
    EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA [new_schema];';   
   END LOOP;   
  END;$$;  

Move functions:

  DO  
 $do$  
 DECLARE  
   sql text;  
 BEGIN  
   SELECT INTO sql  
      string_agg(format('ALTER FUNCTION public.%I(%s) SET SCHEMA new_schema;'  
           ,p.proname, pg_get_function_identity_arguments(p.oid)), E'\n')  
   FROM  pg_proc p  
   JOIN  pg_namespace n ON n.oid = p.pronamespace  
   WHERE nspname = 'public';  
    -- and other conditions, if needed  
   RAISE NOTICE '%', sql; -- for viewing the sql before executing it  
   -- EXECUTE sql; -- for executing the sql  
 END  
 $do$  

RDBMS vs NoSQL

IOHO, to the uninitiated, the problem with choosing a NoSQL solution, comes firstly, not with choosing a NoSQL technology, but with defining NoSQL in terms of ones own requirements.

http://stackoverflow.com/questions/2608103/is-there-any-nosql-that-is-acid-compliant#2608157

Bad code vs "bad a__" code - I love this quote

"
Writing code that is unreadable because it's full of tricks isn't "bad ass" so much as just "bad".
"
Credits: http://stackoverflow.com/questions/1789945/method-like-string-contains-in-javascript#comment-10691511

Dynamic Tables With Mustache

Problem: Generate dynamic tables from a dynamic array with Mustache given that:

1. Total column count is unknown
2. Only one or two column names are known and must be rendered conditionally
3. Helper functions many not be used
4. Data is only provided in arrays. Not model classes

Typical data-set with variable column count where ID is the only column know to always be provided:

[id*]   [Col-1]    [Col-2]    [Col-3]   ...(more)
 1      'Foo'      'Bar'      'Baz'    ...(more)
 2      'Foo'      'Bar'      'Baz'    ...(more)
 3      'Foo'      'Bar'      'Baz'    ...(more)
 ...
(more)

Solution: Mix variating key names with constant key name

In this example below, the variating keys are based on the various column names provided dynamically from the datasource which are ("id"; "name"; "legal_name"; "email"; "signon_email"; "editable") and the constant key name is "field"

array (size=6)
  0 => 
    array (size=2)
      'id' => string '10' (length=2)
      'field' => string 'id' (length=2)
  1 => 
    array (size=2)
      'value' => string 'J. Doe' (length=8)
      'field' => string 'name' (length=8)
  2 => 
    array (size=2)
      'value' => string 'Jane Doe' (length=8)
      'field' => string 'legal_name' (length=8)
  3 => 
    array (size=2)
      'value' => string 'Jane@Doe.com' (length=12)
      'field' => string 'email' (length=12)

array (size=6)
  0 => 
    array (size=2)
      'id' => string '11' (length=2)
      'field' => string 'id' (length=2)
  1 => 
    array (size=2)
      'value' => string 'Jon Doe' (length=8)
      'field' => string 'name' (length=8)
  2 => 
    array (size=2)
      'value' => string 'John Doe' (length=8)
      'field' => string 'legal_name' (length=8)
  3 => 
    array (size=2)
      'value' => string 'John@Doe.com' (length=12)
      'field' => string 'email' (length=12)

The original data source's column name is provided which can be used in Mustache to know what field is being iterated whilst "field" key provides an invariable token name which can be used in Mustache to reference the value for the field as follows:

{{#rows}}
<tr>{{#fields}}
        <td>{{#id}}<a href="foo/{{id}}">{{id}}</a>{{/id}}
            {{^id}}{{field}}: {{value} {{/id}}
        </td>
    {{/fields}}
</tr>
{{/rows}}

The following is produced:

<tr>
        <td><a href="foo/10">10</a></td>
        <td>name: J Doe</td>
        <td>legal_name: Jane Doe</td>
        <td>email: Jane@Doe.com</td>
</tr>
<tr>
        <td><a href="foo/11">11</a></td>
        <td>name: Jon Doe</td>
        <td>legal_name: John Doe</td>
        <td>email: John@Doe.com</td>
</tr>

I have a new blog for Laravel

http://laravel-help.blogspot.com/

How to find distances relative to points of data in the same database

If you have a scenario where you need to calculate the distance in miles, between various point given as longitude and latitude, and these points are in the same database, then simply use the Haver-sine formula to calculate the distances:

 select 
    `origin`.`id` AS `o_id`,
    `destination`.`id` AS `d_id`,
    cast((3959 * 
        acos((((cos(radians(`origin`.`latitude`))
        * cos(radians(`destination`.`latitude`)))
        * cos((radians(`destination`.`longitude`)
        - radians(`origin`.`longitude`))))
        + (sin(radians(`origin`.`latitude`))
        * sin(radians(`destination`.`latitude`))))))
   as decimal (4 , 1 )) AS `miles_apart`
from `point_of_origin` `origin` join `destinations` `destination`;

mysqldump doesn't backup views as views

mysqldump does not dump views as views, but instead, dumps them as tables. Even then, the tables columns all come out as TINYINT(4). If you're not fortunate enough to have Percona's slick backup tools then you'll need another solution.

Here is an over simplified PHP script to get the job done. Simply pipe the output to a .sql file to get it into a neat SQL script file.

<?php
// Configuration for the DB Connection
$_db_name = "test";
$_db_host = "127.0.0.1";
$_db_user = "root";
$_db_pass = "";
$_db_port = '3306';
// ---- end configuration section
$mysqli = new mysqli($_db_host, $_db_user, $_db_pass, $_db_name, $_db_port);
if(!$mysqli) die("Connection error. Aborting\n");
$result = $mysqli->query("SELECT v.TABLE_NAME, v.VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS `v` WHERE v.TABLE_SCHEMA='test';");
if (!$result) die("The 1st query failed to return results. Aborting.\n");
$row = $result->fetch_all();
foreach($row as $a => $b)
{
    echo "CREATE VIEW `".$b[0]."` AS ".$b[1].";\n\n";
}