PostreSQL - Enabling Implicit Cast From Integer To Boolean

I’ve been using MySQL for a while with my pet projects, but recently I’ve been writing some more complicated queries, and I’m running into a few obnoxious limitations, mostly involving known view performance problems. I’ve decided to start testing the waters in PostgreSQL, so I tried importing my data into PG by running a MySQL-generated insert statement, only to run into a PostgreSQL newbie gotcha; “Column is of type boolean but expression is of type integer”.

By default, PG does not automatically interpret “1″ or “0″ as a boolean. The arguments in favor of this limitation are that it prevents “unintended” conversions. However, I’ve been developing in Python for years, which does do this automatic conversion, and its never bitten me.

The code to disable this feature is relatively simple, although I can’t find anyone who’s mentioned it, so I post it here:

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
 SELECT c.oid
 FROM pg_cast c
 inner join pg_type src ON src.oid = c.castsource
 inner join pg_type tgt ON tgt.oid = c.casttarget
 WHERE src.typname LIKE 'int%' AND tgt.typname LIKE 'bool%'
)

This SQL updates PG’s int-to-bool cast path to “implicit” mode, meaning PG will now automatically cast an integer to boolean if the target type is a boolean. Otherwise, in the default “emplicit” mode, you’d have to use the CAST() syntax.

After the update, the insert statement runs perfectly.

PostgreSQL - Comandi utili per amministrare il db

Verificare l'utilizzo degli indici

select * from pg_stat_user_indexes

Verificare lo stato delle tabelle

select * from pg_stat_user_tables

Verificare le query in esecuzione

select * from pg_stat_activity

Verificare l'utilizzo dei checkpoint

select * from pg_stat_bgwriter

SpagoBI - Multi value profile attribute

In Spago è possibile definire un attributo di profilo (profile attribute) di tipo multi-value.

A livello di definizione dell'attributo i valori si dichiarano con la seguente sintassi:

{splitter_character {list_of_values_separated_by_splitter_character} }

Ad esempio ipotizziamo di dichiarare il profile attribute "store_city" in questo modo:

{,{Padova,Roma,Venezia}}

Dove il primo carattere (",") rappresenta il separatore utilizzato nell'elenco.

All'interno dei LOV è possibile utilizzare gli attributi multi value con questa sintassi:

${name_of_profile_attribute(prefix;splitter;suffix)}

Dove il risulatato sarà: 

prefix + list of values separeted by the splitter + suffix

Esempio:

select * from customers 

where city in ${store_city( (' ; ',' ; ') )}

(in giallo il prefisso e il suffisso, in azzurro il separatore)
Diventerà: 

select * from customers where city in ('Padova','Roma','Venezia')

SpagoBI - Documento con formato di output excel o pdf o...

Per determinare il formato di output di un documento SpagoBI è sufficiente aggiungere un parametro denominato outputType.
Questo paramentro determina il formato con cui il documento deve essere prodotto e può assumere i seguenti valori:

• PDF
• XLS
• JPG
• CSV
• XML
• TXT
• PPT
• RTF
• JRXML
• HTML
• ...

Questo parametro può essere utilizzato anche quando si schedula un documento per determinare il formato del file da inviare via mail (o da salvare).

SpaboBI - Modificare le impostazioni di esportazione in excel

Aprire il file

\webapps\SpagoBIJasperReportEngine\WEB-INF\classes\jasperreport.properties

e aggiungere i parametri di esportazione excel impostandoli secondo le esigenze:

net.sf.jasperreports.awt.ignore.missing.font=true
net.sf.jasperreports.export.xls.create.custom.palette=false
net.sf.jasperreports.export.xls.one.page.per.sheet=false
net.sf.jasperreports.export.xls.remove.empty.space.between.rows=true
net.sf.jasperreports.export.xls.remove.empty.space.between.columns=true
net.sf.jasperreports.export.xls.white.page.background=false
net.sf.jasperreports.export.xls.detect.cell.type=true
net.sf.jasperreports.export.xls.size.fix.enabled=false
net.sf.jasperreports.export.xls.ignore.graphics=false
net.sf.jasperreports.export.xls.collapse.row.span=true
net.sf.jasperreports.export.xls.ignore.cell.border=true
net.sf.jasperreports.export.xls.ignore.cell.background=true
net.sf.jasperreports.export.xls.max.rows.per.sheet=0
net.sf.jasperreports.export.xls.wrap.text=true

è anche possibile aggiungere:

#net.sf.jasperreports.export.xls.password=pa22w0rd 

JasperServer - Import / Export

./js-export.sh --everything --output-zip /mnt/backup/jasperRepository.zip

mysqldump

The most simple way is to issue this command:

mysqldump -u [user] -p [database_name] > [backupfile].dump

This command is going to ask you for the [user] password and then will create a script which later can be used to retore the data.

Another way is to use the optimized way.

mysqldump --opt -u [user_name] -p [database_name] > [backup_file].dump

This command will use an optimized method, and will include in the script MySQL commands that will erase (drop) tables that already exists and create them again before populate the data inside.

Maybe the best way to run this command is to use the option of gzip the output file. (for obvious reasons)

Once you have your backup file, you may want to restore it someday, this is the way to do it. (remember tu unzip your file, if zipped, before)

mysql [database_name] < [backup_file].dump

Remeber that you can run

man mysqldump

for more help.

Backuping a single table from a database
mysqldump -u user_name -p database_name table_name > /var/www/backups/table_name.sql

Restoring the table into another database
mysql -u -p database_name < /var/www/backups/table_name.sql