Apr 192014
 

This seven steps will perform the upgrade of PostgreSQL from version 9.1 to version 9.3.
This also works in Ubuntu and it can also be used to upgrade between any version numbers.

To upgrade between any versions, just changed the 9.1 for the legacy version number and the 9.3 for the new version number.

First install the necessary dependencies

sudo apt-get update
sudo apt-get -y install python-software-properties

Second, add the PostgreSQL repository

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Third, setup the repository

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'

Fourth, install PostgreSQL 9.3.

sudo apt-get install postgresql-9.3 postgresql-server-dev-9.3 postgresql-contrib-9.3

Fifth, perform the upgrade process.
The upgrade process is performed by having both servers running at the same time. Note that the new 9.3 version will run on a different port, 5433 as specified in the script bellow, and it will be set to the default port latter when the legacy version is uninstalled and the default port becomes available.

sudo su -l postgres
psql -d template1 -p 5433
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
\q #logout from database
service postgresql stop
/usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.3/main/ -O " -c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"
exit # logout postgresql back to previous user

Sixth, remove the 9.1 version.

sudo apt-get remove postgresql-9.1

Seventh, set the new version server port back to the default value and restart the service.

sudo vim /etc/postgresql/9.3/main/postgresql.conf # find old port of 5433 and change it to 5432
sudo service postgresql restart
Mar 062014
 

When working on Ubuntu Servers, sometimes the defined keyboard layout is not aligned with the physical keyboard one’s using.

In order to permanently change it, just execute the following two commands.

First, configure the keyboard:

sudo dpkg-reconfigure keyboard-configuration

Test the keyboard, in particular the characters like slash, asterisk, etc.. If things are not ok, just configure it again with different options.

Next, configure the console:

sudo dpkg-reconfigure console-setup

And that’s it.

If you want to configure the size of your TTY, i. e. the text console resolution, check ChangeTTYResolution.

Nov 082013
 

While developing for the web, one has to take into account the various browsers that people use.
Depending on the kind of project and target users, this may range from an easy option focused on a single browser up to the dawnting task of supporting the most used browsers world wide.

To help in this task, Microsoft has provided a set of combinations of Windows and Internet Explorer versions in ready to use virtual machines available to all major operating systems.
You can get them here in the Modern.IE web site in the virtual tools section.

Nov 042013
 

To correctly secure a Drupal installation in Linux, just follow these simple steps:

[root@localhost]cd /path_to_drupal_installation
[root@localhost]chown -R vsftpd:www-data .
[root@localhost]find . -type d -exec chmod u=rwx,g=rx,o= '{}' \;
[root@localhost]find . -type f -exec chmod u=rw,g=r,o= '{}' \;

Change the vsftpd by your own user name, ftp deamon user or what evere user you need.

[root@localhost]cd /path_to_drupal_installation/sites
[root@localhost]find . -type d -name files -exec chmod ug=rwx,o= '{}' \;
[root@localhost]for d in ./*/files
do
   find $d -type d -exec chmod ug=rwx,o= '{}' \;
   find $d -type f -exec chmod ug=rw,o= '{}' \;
done

Full article and explanation from Securing file permissions and ownership.

May 272013
 

While developing Oracle PL/SQL code, sometimes one needs to debug via the old way, using DBMS_OUTPUT.PUT_LINE or inserting data into a table for a later inspection.
But when working with cursors, this is not an easy task, specially if one does not know the real output of the cursor when using dynamic SQL or simply because the cost of writing a specific output for each cursor one needs to evaluate is too high.

In order to aid this task, here’s a procedure that will print out the result of any cursor. By default, it will print the first 10 rows, but to print the entire result, just call the PRINT_CURSOR procedure with the v_maxRows = 0.

create or replace
PROCEDURE PRINT_CURSOR (
    p_refcursor IN OUT SYS_REFCURSOR, v_maxRows IN NUMBER default 10 )
AS
  v_desc DBMS_SQL.DESC_TAB;
  v_cols BINARY_INTEGER;
  v_cursor BINARY_INTEGER;
  v_varchar2 VARCHAR2(4000);
  v_number   NUMBER;
  v_date     DATE;
  v_data     VARCHAR2(32767);
  v_curRow   NUMBER;
BEGIN
  /* Convert refcursor "parameter" to DBMS_SQL cursor... */
  v_cursor := DBMS_SQL.TO_CURSOR_NUMBER(p_refcursor);
  /* Describe the cursor... */
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_cols, v_desc);
  /* Define columns to be fetched. We're only using V2, NUM, DATE for example... */
  FOR i IN 1 .. v_cols
  LOOP
    IF v_desc(i).col_type = 2 THEN
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_number);
    ELSIF v_desc(i).col_type = 12 THEN
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_date);
    ELSE
      DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_varchar2, 4000);
    END IF;
  END LOOP;
  /* Now output the data, starting with header... */
  DBMS_OUTPUT.NEW_LINE;
  FOR i IN 1 .. v_cols
  LOOP
    v_data := v_data ||
    CASE v_desc(i).col_type
    WHEN 2 THEN
      LPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
    WHEN 12 THEN
      RPAD(v_desc(i).col_name, 22)
    ELSE
      RPAD(v_desc(i).col_name, v_desc(i).col_max_len+1)
    END || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_data);
  v_data := NULL;
  FOR i IN 1 .. v_cols
  LOOP
    v_data := v_data ||
    CASE v_desc(i).col_type
    WHEN 2 THEN
      LPAD('-', v_desc(i).col_max_len+1, '-')
    WHEN 12 THEN
      RPAD('-', 22, '-')
    ELSE
      RPAD('-', v_desc(i).col_max_len+1, '-')
    END || ' ';
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(v_data);
  /* Fetch all data... */
  v_curRow := v_maxRows;
  WHILE DBMS_SQL.FETCH_ROWS(v_cursor) > 0
  LOOP
    v_data := NULL;
    FOR i IN 1 .. v_cols
    LOOP
      IF v_desc(i).col_type = 2 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_number);
        v_data                := v_data || LPAD(v_number, v_desc(i).col_max_len+1) || ' ';
      ELSIF v_desc(i).col_type = 12 THEN
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_date);
        v_data := v_data || RPAD(v_date, 22) || ' ';
      ELSE
        DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_varchar2);
        v_data := v_data || RPAD(v_varchar2, v_desc(i).col_max_len+1) || ' ';
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(v_data);
    if  v_maxRows <> 0 then
       v_curRow :=  v_curRow - 1;
       exit when 0 = v_curRow;
    end if;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
Apr 222013
 

During data migrations it’s common to have constraints disabled, usually for loading performance or testing purposes.
To aid in such task, we’re sharing two simple scripts, one for disabling and another for enabling.

To disable all enabled constraints and triggers, just execute:

BEGIN
  -- Constraints
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   ORDER BY c.constraint_type DESC)
  LOOP
    -- DISABLE
    execute immediate 'alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name;
  END LOOP;
 
  -- Triggers
  FOR ut IN
    (select ut.table_owner, ut.trigger_name
    from user_triggers ut
    where ut.status = 'ENABLED')
  LOOP
    -- DISABLE
    execute immediate 'ALTER TRIGGER "' || ut.table_owner || '"."' || ut.trigger_name || '" DISABLE;';
  END LOOP;
END;
/

To enable all disabled constraints and triggers, just execute:

BEGIN
  -- Constraints
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLE'
   ORDER BY c.constraint_type DESC)
  LOOP
    -- ENABLE
    execute immediate 'alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name;
  END LOOP;
 
  -- Triggers
  FOR ut IN
    (select ut.table_owner, ut.trigger_name
    from user_triggers ut
    where ut.status = 'DISABLE')
  LOOP
    -- ENABLE
    execute immediate 'ALTER TRIGGER "' || ut.table_owner || '"."' || ut.trigger_name || '" ENABLE;';
  END LOOP;
END;
/
Oct 312012
 

During a migration from SQL Server to Oracle 11g, I’ve came across with a specific functionality of T-SQL FOR XML PATH.
In the scope of the migration, the result of the select clause where this was applied was the concatenation of the values of a column per key.

Here’s an example of what I’ve found on T-SQL during the migration:

Declare @tbl1 table (
  id int,
  Col1 varchar(50),
  Col2 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A001','Y'

INSERT INTO @tbl1
SELECT 2,'A002','N'

INSERT INTO @tbl1
SELECT 3,'A003','N'

INSERT INTO @tbl1
SELECT 4,'A004','Y'

INSERT INTO @tbl1
SELECT 5,'A005','N'

INSERT INTO @tbl1
SELECT 6,'A006','N'

INSERT INTO @tbl1
SELECT 7,'A007','N'

INSERT INTO @tbl1
SELECT 8,'A008','Y'

-- Show current values
select * from @tbl1

-- Get all values when col2 has value 'N'
SELECT c.col1 AS [text()]
FROM @tbl1 c
WHERE c.col2 = 'N'
FOR XML PATH(''),type

-- Aggregate all values from col1 using col2 as key
select c1.col2 [Yes/No],
  (SELECT c.col1 AS [text()]
   FROM @tbl1 c
   WHERE c.col2 = c1.col2
   FOR XML PATH(''),type) as Value
FROM @tbl1 c1
group by c1.col2
order by c1.col2

The execution of this T-SQL script shows how it is possible transform the result of a query into a new result where the values are aggregated by col1, and all col2 values are concatenated.

In order to achieve this kind of aggregate transformation in Oracle, one has to make use of an associative array, as Tom explains in the “String Concatenation” how one can do this. Using the examples in the AskTom web site, first we create the “STRING_AGG_TYPE” type:

CREATE OR REPLACE TYPE string_agg_type AS object
(
  total VARCHAR2(4000),

  static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER,

  member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2) RETURN NUMBER,

  member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,

  member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type) RETURN NUMBER
);
/

And the correspondent body:

CREATE OR REPLACE TYPE BODY string_agg_type
IS

static FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type)
RETURN NUMBER
IS
BEGIN
  sctx := string_agg_type( NULL );
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateIterate(self IN OUT string_agg_type, VALUE IN VARCHAR2 )
RETURN NUMBER
IS
BEGIN
  -- NOTE: Insert a separator in the empty string if you wish
  self.total := self.total || '' || VALUE;
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
  -- NOTE: ltrim(self.total, '') if you've specified a separator in ODCIAggregateIterate
  returnValue := LTRIM(self.total);
  RETURN ODCIConst.Success;
END;

member FUNCTION ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
RETURN NUMBER
IS
BEGIN
  self.total := self.total || ctx2.total;
  RETURN ODCIConst.Success;
END;

END;
/

Then we create the aggregated function “stragg” of the type we’ve just created:

CREATE OR REPLACE FUNCTION stragg(input VARCHAR2 ) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

We are now able to make a similar aggregation in Oracle:

CREATE TABLE tbl1 (
  id NUMBER(10),
  Col1 VARCHAR(50),
  Col2 VARCHAR(50)
);
/

INSERT INTO tbl1 (id, col1, col2)
VALUES (1,'A001','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (2,'A002','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (3,'A003','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (4,'A004','Y');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (5,'A005','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (6,'A006','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (7,'A007','N');
/
INSERT INTO tbl1 (id, col1, col2)
VALUES (8,'A008','Y');
/
COMMIT;
/

-- Show current values
SELECT * FROM tbl1;
/

-- Get all values when col2 has value 'N'
SELECT c.col2, STRAGG(c.col1)
FROM tbl1 c
WHERE c.col2 = 'N'
GROUP BY c.col2;
/

-- Aggregate all values from col1 using col2 as key
SELECT c1.col2 "Yes/No",
  (SELECT STRAGG(c.col1)
   FROM tbl1 c
   WHERE c.col2 = c1.col2
  ) AS val
FROM tbl1 c1
GROUP BY c1.col2
ORDER BY c1.col2;
/

-- drop table tbl1;

And that’s it. This will work in 9i and beyond, for 8i, check the 8i workaround in the same AskTom thread.

Sep 262012
 

The .Net framework has a set of predefined browser capabilities. In short, this means that dependending of the browser being used, .Net will create the appropriate HTML.
Unfortunately, sometimes it does not work as expected. Plus, .Net is not capable of keeping up with the speed of the release cycles of all the browsers that are available.

The end result is that sometimes, our .Net application render differently in some browsers, even if they shouldn’t, and, even worst, some functionalities may not work.
To overcome this problem, .Net has a property that allows to specify what to do with the distinct HTTP agents.
Using the ClientTarget property from the Page class, developers can, for instance, assume everyone is using, at least FireFox 3 or Internet Explorer 7.

In short, the solution is to force all “old” browsers to behave as a certain browser and code it from there. This can be achieved either through the clientTarget options in the web.config application file, setting this property to the entire application, or specifically on a page as an attribute of the Page directive.

Jun 212012
 

Having a good zoom efect on Virtuemart products page details can be easy.
This solution is based on Dynamic Drive Image Power Zoomer v1.1, based on jQuery, and was tested on VirtualMart 2.

The first step is to download the ddpowerzoomer.js file from the Image Power Zoomer v1.1.
Place this file on your VirtueMart template Javascript folder, usually /templates/VM_TEMPLATE/js, where VM_TEMPLATE is your current VirtueMart template.
Open the file and find the init:function. Inside it, find the first div, it should be the first line, and add z-index:100000 to its style, so that it looks like this:

  init:function($){
    var $magnifier=$('<div style="position:absolute;width:100px;height:100px;display:none;overflow:hidden;border:1px solid black;z-index:100000;" />')
      .append('<div style="position:relative;left:0;top:0;" />')
      .appendTo(document.body) //create magnifier container and add to doc
    [...]
  }

This will force the zoomed image div container to be above the Joomla! Modal SqueezeBox, that you can find in then /media/system/js/modal.js file. That is the file loaded by Joomla!, which is non readable by humans, at it’s side there is modal-uncompressed.js, the human readable version.
The SqueezeBox is responsible for making the screen dark and showing the large picture. Since the SqueezeBox sets its z-index to a high value, 65000+, we need to force the zoomed image to be above it, that’s why we’ve set the z-index to 100000.

Next open the VirtueMart template index.php file, located on /templates/VM_TEMPLATE/index.php, and right before closing the head tag, place the following code:

<!-- Zoom Component -->
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script type="text/javascript">
/***********************************************
* Image Power Zoomer- (c) Dynamic Drive DHTML code library (www.dynamicdrive.com)
* This notice MUST stay intact for legal use
* Visit Dynamic Drive at http://www.dynamicdrive.com/ for this script and 100s more
***********************************************/

if (null != SqueezeBox) {
  SqueezeBox.presets.onUpdate=
    function applyZoomEffect() {
      jQuery(document).ready(function($){ //initialize power zoomer on DOM load
        $.getScript("<?php echo $template_path ?>/js/ddpowerzoomer.js", function(data, textStatus, jqxhr) {
          var $imgref=$('#sbox-content > img');      
          options = {powerrange:[2,5], magnifiersize:[150,150]};
        options.largeimagesrc = $imgref.attr('src');
          ddpowerzoomer.setupimage($, $imgref, options)
        });
      });
    }
}
</script>
<!-- End Zoom Component -->

Note that the jQuery loaded is the current stable, 1.7.2. Your template may already load a previous version of jQuery, they should not get in conflict.

Now run your online store and click on an image product, it should have a zoom option

Here’s some explanation on how all this works.
The product large image is dynamically created when the user clicks on the product thumbnail, though a CSS event.
That’s why we have to override the update event of the SqueezeBox, which is responsible for showing the large image.
When the SqueezeBox shows the large image, it will call the applyZoomEffect function that we have defined.
This function will load the ddpowerzoomer.js script file and after it loaded, it will find the large image div container, set the zoom options (check the Image Power Zoomer options) and finally it will set up the zoom for the large image.

Jun 182012
 

Using Ajax in Django applications is quite easy when using dajaxproject, Dajax and Dajaxice.
I assume you have pip installed on your system, if that’s not the case, install it.

The first step is to download and install Dajax core:

pip install -e git://github.com/jorgebastida/django-dajax.git#egg=django-dajax

Copy your flavoured prototype function.

Download and install Dajaxice:

pip install -e git://github.com/jorgebastida/django-dajaxice#egg=django-dajaxice

In the application where the Ajax funtionality is required, create an “ajax.py” file with the required Ajax functions.
In the example below it will load cities from a selected country, a tipical problem of master-detail chained selects.

from dajax.core import Dajax
from dajaxice.decorators import dajaxice_register
from my_app.models import City

@dajaxice_register
def updateCity(request, option):
    dajax = Dajax()
    options = City.objects.filter(country.id=option)
    out = ""
    for o in options[int(option)]:
        out += "%s%s" % (out,o,)

    dajax.assign('#id_city','innerHTML',out)
    return dajax.json()

The Ajax function is a simple function that must be registered with the @dajaxice_register anotation.
It gets the cities from a specific country and assigns those values to the innerHTML of the id_city HTML object.

In the forms, the country must call the “updateCity” Ajax funtion to load the cities on the select country.

[...]
#
# Address
#
class AddressForm(forms.Form):
"""
Address form
"""


[...]

    country = forms.ModelChoiceField(widget = forms.Select(attrs = {'onchange' : "Dajaxice.acg.updateCity(Dajax.process,{'option':this.value});"}), queryset=Country.objects.all(), required=True,  empty_label = lcountry_empty, label=lcountry, help_text = lcountry_help, error_messages={'required': lcountry_required})
    city = forms.ModelChoiceField(queryset=City.objects.all(), required=False, empty_label = lcity_empty, label=lcity, help_text = lcity_help)
    address = forms.CharField(widget=forms.Textarea(attrs={'cols': 20, 'rows': 3}), min_length=10, max_length=50, label='Address')
    zip = forms.CharField(max_length=60, required=True, label='Zip code' )
[...]

The only change here, regarding to a common form definition, is to inject the call of the Ajax “updateCity” function on the “onChange” event. Note that the city must be called “city” in order to bind with the HTML id_city object (remember, Django gives “id_” to the id)

Finally, your HTML file must include the jquery.dajax.core.js file in the HEAD section. Copy it to someplace where the browser will be able to load it, like

<script src="/media/dajax/jquery.dajax.core.js" type="text/javascript" charset="utf-8"></script>

And this is all one requires to do in order to have Ajax working on a Django application.