Data Import and Processing in Primefaces

To complement the data export tool here is a data import solution: Steps 1. upload a preformated* spreadsheet file (ms xl or similar) 2. [data gets populated into a clearing/holding table for validation and corrections if any] 3. click Commit to execute an PL sql function (that processes the data from the holding table into the relevant table(s)

---

---

Requirements 
1. temporary table to hold the imported data
2. a preformated XL file 3. PF componenets (datatable and fileupload)
4. ActionListeners
5. pl/sql function to process data from the holding table into the necessary table(s)

Dynamic SQL UPDATE

NEED TO UPDATE table columns with dynamic values (not known in advance).

A. ORACLE 

i.Single-Column update

   [Working example.. just replace table names]

   UPDATE station s

        SET vhf_network_id = 

                (

                SELECT vhf_network_id

                FROM vhf_network v  

                WHERE s.client_license_id = v.client_license_id

                )

        WHERE s.station_charge_id = 5;
 

ii. Multiple update

    The key here is to maintain the order and count of the columns in the source columnlist and destination columnlist

  UPDATE
    SET () = 

          (
          SELECT
          FROM
          WHERE

          )

    WHERE ;

B. POSTGRES EQUIVALENT

   Working example (difference with the above is the absence of INNER SELECT)

  UPDATE station s
      SET vhf_network_id = v.vhf_network_id
    FROM vhf_network v
    WHERE s.client_license_id = vhf_network.client_license_id;

Fixing Django ImportError in production

Hi,

"TemplateSyntaxError: Caught ImportError while rendering: No module named blahblahblah"  

In Development all imports work perfectly untill u move to production....

I 'lost' almost 16 man-hours trying to troubleshoot this. This is almost always a path issue.. There are several options one of them adding the path using os.path but the one that worked for me is to fully qualify the module name.

This means prefixing the project name to the module name such that if your project name was 'my_project' and the missing module/app is 'registration_app' you'll need to use my_project.registration_app EVERYWHERE in your .py files..

ONE Trigger Function to rule them all

In many of my earlier applications/frameworks av always implemented table audit facility via java/python code...

granted this worked ok... unless the (malicious or otherwise) user accessed the apps DB via other means eg terminal, ide, et al...  

Recently av been doing this via DB triggers (...silence please.. i know this is not DB agnostic...) and had quite a challenge creating a 'universal' trigger function that will be used by all auditable tables...

The following is the solution, of course assembled from several blogs and sites and some fiddling on SQLFiddle......

Prerequisites (for those who wish to use this function as it is):

• Audited table must capture a login id(bigint)
• Must have an AFTER trigger calling this fxn
• Table p_audit must be created in advance

CREATE OR REPLACE FUNCTION if_modified_func() returns TRIGGER AS $$
DECLARE
      pk        bigint;
      pk_name        varchar(100);
      pk_datatype    varchar(50);
BEGIN

    IF TG_WHEN <> 'AFTER' THEN
         RAISE EXCEPTION 'Audit Function may only run as an AFTER trigger';
    END IF;

    --first we identify the pk field of the db
    EXECUTE
    '(SELECT
        pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
      FROM pg_index, pg_class, pg_attribute
      WHERE pg_class.oid = ''' || TG_TABLE_NAME || '''::regclass
        AND indrelid = pg_class.oid
        AND pg_attribute.attrelid = pg_class.oid
        AND pg_attribute.attnum = any(pg_index.indkey)
        AND indisprimary)'
      INTO pk_name, pk_datatype;

    --TO GET record_id start here => http://www.sqlfiddle.com/#!15/64e41/2/0

    if TG_OP = 'UPDATE' then
        EXECUTE 'SELECT $1.'||pk_name INTO pk USING NEW;

        INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
                  record_id, db_user, action_timestamp, data_before, data_after, updated_cols, narrative)
        VALUES(txid_current(), NEW.login_id, TG_TABLE_NAME, 'U', inet_client_addr(), inet_client_port(), current_query(),
                pk, CURRENT_USER, CURRENT_TIMESTAMP, hstore(OLD.*), hstore(NEW.*), akeys(hstore(NEW.*) - hstore(OLD.*)), NULL);

        return NEW;
    elsif TG_OP = 'DELETE' then
        EXECUTE 'SELECT $1.'||pk_name INTO pk USING OLD;

        INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
                  record_id, db_user, action_timestamp, data_before)
        VALUES(txid_current(), OLD.login_id, TG_TABLE_NAME, 'D', inet_client_addr(), inet_client_port(), current_query(),
                pk, CURRENT_USER, CURRENT_TIMESTAMP, hstore(OLD.*));

        return OLD;
    elsif TG_OP = 'INSERT' then
        EXECUTE 'SELECT $1.'||pk_name INTO pk USING NEW;

        INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
              record_id, db_user, action_timestamp, data_before, data_after, updated_cols, narrative)
        VALUES(txid_current(), NEW.login_id, TG_TABLE_NAME, 'I', inet_client_addr(), inet_client_port(), current_query(),
                pk,  CURRENT_USER, CURRENT_TIMESTAMP, NULL, hstore(NEW.*), NULL, NULL);

        return NEW;
    end if;
END;
$$ language plpgsql;


Finaly...
if you think this is coool....... just say it

=====
Graphically  u may present it like this

Emptying/Reloading a postgres Database without droping it

Hey.. i know u r not a lazy DBA......

Ever had to re-load a postgres database without having the privileges needed to create a database/schema nor the ones needed to drop it ?

Case In Point;- On OpenShift (RedHat Cloud) you cannot drop the provided database nor create another one...

The schema had so many tables, thus dropping each table one by one is never an option.

After some googling i came up with this.. and found it worthwile to share;

run DELETE SCHEMA public CASCADE;
then CREATE SCHEMA public;

voila!!!!!!

Avoid 'SQL LIKE' - Use Full Text Search

Hello random googler,

Welcome back

Do u often find your self needing to do 'dirty' SQL LIKEs like the following;

SELECT member_id 

FROM vw_members

WHERE upper(member_name) LIKE 'JAY%' OR

upper(member_county) LIKE 'NAI%' OR

upper(staff_no) LIKE 'S01%' OR 

upper(mobile_no) LIKE '072%' OR 'i am tired of this stuff and this is only one permutation assuming the first 3 characters hav been filled...ouuuch'

? (yes question mark... this is where the question ends!!!)


If you answer is yes OR 'Y%'  ;-) ;-) then you are ready for FULL TEXT SEARCH

ONE TECHNIQUE...
Make your SQL VIEWS read for FTS by always having a column called 'ts_doc' that should contain the 'searchable' columns(of ur choice) ??????????


EG (postgres)

CREATE OR REPLACE VIEW vw_members AS

SELECT members.member_id, members.member_name, member.staff_no, members.mobile_no, andanyothercolumnyouwhishcoziassumeuknowsql,

 to_tsvector(COALESCE(members.member_name,'')||' '||COALESCE(member.staff_no,'') ||' '|| COALESCE(members.mobile_no,'')) as ts_doc

So that in your queries just become

SELECT blahblah

FROM vw_members

WHERE ts_doc @@ to_tsquery(' searchQuery:*') ";

NB: searchQuery is nothing but a cleanup to create a valid FTS query:-
searchQuery = searchQuery.replace(" ",":* & ") - in java, but of course u can use ur prefered technique... php, sql,

CONCLUSION
With the above u can use more than one search text(in whatever order or case) to narrow down the result..
for example with searchQuery like 'mik DS1 nai' it will search in all the columns (defined in ts_doc) for words starting with mik or DS1 or nai... thereby matching all entries for nairobi, all names starting with mik eg mike,mikel etc etc

NB: approach is 'begins with....'
(those who do LIKE '%xyz%' look for another evangelist !!!!)

example is in postgres

adios

Oracle and Postgres Cummulative Sum aka Running Totals

Hello Random Googler !!!

You landed here today... u r welcome.

Now consider the following output.

The 'interesting' column is the last one

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	3000
Shamim	01-JUL-13	1000	4000
Abdul	01-JUL-13	1000	5000
Jim	01-AUG-13	1000	6000
ZeGuru	05-SEP-13	1000	7000
Duli	06-OCT-13	1000	8000

Another example
(grouped by date... sorry partitioned by date)

Name	Date	Amount	Cumulative Sum
Mike	01-JUN-13	1000	1000
John	01-JUN-13	1000	2000
Omar	01-JUL-13	1000	1000
Shamim	01-JUL-13	1000	2000
Abdul	01-JUL-13	1000	3000
Jim	01-AUG-13	1000	1000
ZeGuru	05-SEP-13	1000	1000
Duli	06-OCT-13	1000	1000

The first one was achieved by;

SELECT name, date, amount, sum(amount) over(order by payment_id) cummulative_sum
FROM vw_payment

THE second was achieved by;

SELECT name, date, amount, sum(amount) over(partition by date, order by payment_id) cummulative_sum
FROM vw_payment

(the partition by clause was used to restart the running totals for each date)


do i need to say that payment_id is just the PK (incremental/serial integer) of the original table ?

By the way this works for both PostgreSQL and Oracle

Again u r welcome

Oracle isNumeric function

AFAIK there is no in-built function to do such but there are lots of ways to achieve the same using the functions already available.

Here is a list of some;

Option 1: Using to_number()
Attempt to convert the input/variable to a anumber.. if it succeeds then isNumeric is true otherwise false

CREATE OR REPLACE FUNCTION isNumeric (var in varchar) return char is

   dummy number;

begin

     select to_number(trim(var)) into dummy from dual; 

   if (dummy is null) then

      return '0';

   else

      return '1';

  end if;  

exception

   when others then

       return '0';

end;

/

Option 2: Using regex

Find foreign table using a given CONSTRAINT NAME

Ever tried to delete a record only to be told get ORA-02292
"integrity constraint (CONSTRAINT_NAME) violated - child record found"

How do you identify the table that is related to the constraint mentioned above. Of course if you explicitly name ur FK constraints(very unlikely) you have no problem.

ORACLE




SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_name = 'SYS_C0011582'

TRUE/REAL data pagination

I was recently implementing Lazy Loading in Primefaces

One of the insights i would like to share is that real efficient pagination is achieved by fetching data from the database (backend) in chunks as opposed to loading everything in memory.

This saves both bandwidth and memory (client side).

The trick here is to use OFFSET and LIMIT sql clauses in conjuction with ORDER BY.

Example 1: Returning the first 100 rows from a table called employee:

SELECT employee_id, employee_name, remarks FROM employee ORDER BY employee_id ASC LIMIT 100;

Example 2: Returning 10 rows from the table employee starting at row 15

SELECT employee_id, employee_name, remarks FROM employee ORDER BY employee_id ASC OFFSET 15 LIMIT 10;

Migrating from PostgreSQL 8.3 to 9.0 and beyond - Byte Array perspective

My Nerd migos,

Its bn quite sometime since my last post.

I want to share my agonies (and help u avoid the same) when migrating applications from Postgres 8.3 (or below) to 9.0 and beyond. If you had/have tables with bytea then they will/may JUST stop working.

Some guy(s) with infinite wisdom decided to change the representation of byte array from the default (escape) to hex !!! I googled my head silly until i found a solution at stackoverflow.com (forgot the thread sorry!!!)

Ok straight to the solution. Export the 8.3 db (i prefer pg_dump --inserts dbname > /path/to/dumpfile.sql) then import (just use psql or ur favorite tool to run the script created by the pg_dump command).

The next thing is to change a configuration flag in postgresql.conf. Change bytea_output to 'escape' instead of the default 'hex'. restart the server and hug yourself.

"At least the postgres guy with infinite wisdom never forgot to add this flag for backward compatibility"

Plz dont ask me what is a bytea datatype. If u dont know it means u dont have it in ur DB so relax

Hope this helps

XOR in Oracle

I had a situation where an XOR implementation was needed in a SELECT query. Since Oracle 10g doesnt have this operator predefined i did some gymnastics and ended up with this solution.

I decided to share this (among others) since human knowledge belongs to mankind

1. XOR in oracle

Example 1 - chars only

Eg we want to display results where ismale='1' XOR iscitizen='1' (ie either but not both)

Solution
SELECT ismale, iscitizen, (ismale + iscitizen) as xor
FROM tablename
WHERE ismale + iscitizen = 1;

NB: oracle will add(mathematical) chars as it would integers

Example 2 - varchars

Eg we want to display results where gender='male' XOR countryid='KE' (ie either but not both)

Solution
SELECT gender, countryid, (decode(gender,'male','1','0') + decode(countryid,'KE','1','0')) as xor
FROM tablename
WHERE (decode(gender,'male','1','0') + decode(countryid,'KE','1','0')) = 1;

NB: oracle will add chars as it would integers

Oracle 10g - Configure Email

UTL Mail Package for sending mail

1. Install the package
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

Package Paths my be something like this:
i)On live db > @/global/oracle/app/product/10.2.0/db_1/rdbms/admin/utlmail.sql
ii)On local db > @> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/utlmail.sql

2. GRANT
GRANT execute ON utl_mail TO ;
(or)
GRANT execute ON utl_mail TO PUBLIC;

3. Configure smtp server
> alter system set smtp_out_server = '172.100.3.45:25' scope=both;

I prefer setting this inside the procedure/function sending the mail


4. Restart the DBMS
>shutdown immediate
>startup


SENDING EMAIL

Showing only the relevant part of the function :

IF(email = '1')THEN

--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '172.100.3.98:25';
UTL_MAIL.send(sender => 'imisadmin@cit.go.ke', --to confirm that this email is from imis
recipients => recepts, --client email address(es)
cc => rec_user.email, --carbon copy to relevant officer
--bcc => 'iitambo@dewcis.com', --bind cc to developer
subject => sub,
message => msg,
mime_type => 'text; charset=us-ascii');
END IF;

Oracle 10g - Command Line

NB: Linux Server Assummed

1. Manual Startup

#xhost + //disables access control
#su - oracle //switch to user oracle (execute home folder)
#export ORACLE_SID=crm //for crm SID
#sqlplus / as sysdba //connect to sqlplus as sysdba
SQL>startup //start up the db
SQL>quit //
#lsnrctl start lsnrcrm //start listener
#emctl start dbconsole //start Enterprise Manager

NB: shutting down = shutdown immediate (from withing sqlplus)

2. Related (run as user oracle)

#dbca //db config assistant
db type: General Purpose
Global Db Name : eg dump.fsm
SID : eg dumpfsm

Option - > Generate DB Creation Scripts (Default location: /home/oracle/oracle/admin/dump/scripts)

#netca //configure listener