Cambiare l’owner di tutte le funzioni in un database postgresql

Talvolta può presentarsi la necessità di modificare l’owner degli oggetti presenti in uno schema del database postresql.
Di seguito un metodo per cambiare l’owner superuser postgres delle funzioni presenti in uno schema di un database postgresql utilizzando la console psql e il salvataggio  su file esterno .

Per elencare le funzioni presenti nello schema public si può utilizzare la query

SELECT p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
 FROM pg_catalog.pg_namespace n
 JOIN pg_catalog.pg_proc p
 ON p.pronamespace = n.oid
 WHERE n.nspname = 'public'

Posizionarsi in una directory in cui lo user postgres ha il permesso di scrittura oppure creare una directory ad hoc.

$ mkdir /home/postgres 
$ sudo su 
$ chown postgres:postgres
$ cd /home/postgres
$ su postgres

Avviare la console di postgresql e selezionare il database con lo funzioni il cui owner va modificato

$ psql
selezionare il database con le tabella da cambiare 
postgres=# \c eth2010

Per generare i comandi per la modifica dell’owner delle tabelle si può usare la query:

eth2010=# select 'alter function '||n.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to NUOVO_USER ;' from pg_proc p join pg_namespace n ON p.pronamespace = n.oid where n.nspname = 'public';

Il risultato della query sara del tipo

?column? 
 
 
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
 alter function public.chkpass_in(cstring) owner to NUOVO_USER;
 alter function public.chkpass_out(chkpass) owner to NUOVO_USER;
 alter function public._getsingleproductid(integer) owner to NUOVO_USER;
 alter function public._getmenu() owner to NUOVO_USER;
 alter function public.__getsetoflinks(integer, integer) owner to NUOVO_USER;
 alter function public._bblock_global(character varying) owner to NUOVO_USER;
 alter function public._catalog() owner to NUOVO_USER;
 alter function public._deletemenu(integer) owner to NUOVO_USER;
...
...
...
(217 rows)

salviamo la query nel file esterno alterfunctions_own ridirigendo l’output da terminale con

eth2010=# \o alterfunctions_own

rieseguire la query sostituendo NUOVO_USER con lo user desiderato.
Il risultato verrà salvato sul file esterno alterfunctions_own che conterrà a questo punto  un elenco di comandi alter function 

eth2010=# select 'alter function '||n.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to NUOVO_USER ;' from pg_proc p join pg_namespace n ON p.pronamespace = n.oid where n.nspname = 'public';

Per eseguire la lista di comandi alter function importare il file con

eth2010=# \i alterfunctions_own

psql:alterfx1_own.sql:3: ERROR: syntax error at or near "?"
LINE 1: ?column? ...
 ^
psql:alterfx1_own.sql:637: ERROR: syntax error at or near "317"
LINE 1: (217 rows)

Il file generato contiene in testa e in coda caratteri che non rappresentano comandi validi e che generano alcuni sintax error tuttavia  in genere non vale la pena di pulire il file.

Modificare l’owner delle funzioni di un database con una query plpgsql

La query cambia l’owner di tutte le funzioni del database in cui viene eseguita. Consente di precisare lo schema su cui agire in genere public  definito da:

  • in_schema = primo parametro
  • new_owner = secondo parametro
-- FUNCTION: public.__chown_functions(character varying, character varying)

-- DROP FUNCTION public.__chown_functions(character varying, character varying);

CREATE OR REPLACE FUNCTION public.__chown_functions(
	in_schema character varying,
	new_owner character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    -- ROWS 0
AS $BODY$

DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;
 
  r record;
BEGIN


  FOR r IN 
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %', 
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE 
       'alter function ' || r.nspname || '."' || r.proname ||
       '"(' || r.args || ') owner to ' || new_owner;
  END LOOP;


END;

$BODY$;

ALTER FUNCTION public.__chown_functions(character varying, character varying)
    OWNER TO postgres;

 

Risorse:

Plpgsql 

Postgresql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

Annunci

Mysql contro Postgresql

postgresql

MySQL e PostgreSQL sono i due database relazionali  open source più  utilizzati e 

mysqllogo

conosciuti. Lo sviluppo iniziale dei due database ha avuto obiettivi diversi. Mysql ha privilegiato la velocità, l’essenzialità mentre Postgresql l’aderenza agli standard e la completezza rendendo disponibili  diverse funzionalità anche se al prezzo di performance inferiori. Ancora per postgresql si affermava che gli sviluppatori di postgresql erano animati dall’obiettivo di portare in ambito open-source un database con l’impostazione dei database di Oracle ( “open-source” Oracle) . Grazie alla velocità e all’essenzialità, che in alcuni ambiti diventà semplicità, Mysql storicamente è stata la prima scelta nelle applicazioni web appoggiate a database.  Non va trascurato poi che Mysql è entrato nella “scuderia” Oracle.

Nel tempo la situazione si è andata modificando ed oggi i due prodotti stanno in qualche modo convergendo nel senso che le prestazioni di Postgresql sono decisamente migliorate mentre Mysql si è aricchito di funzioni. MySQL ( oggi alla versione 5.6)  rimane comunque il più utilizzato e se una volta lo era per la velocità e la praticità ora lo è per l’ampio numero di progetti in cui è estrato a far parte. Infatti quasi tutte le web application si basano o prevedono la possibilità di scelta di MySQL come database. PostreSQL ( oggi alla versione 9.3.1)  dal canto suo offre comunque una protezione dei dati superiore grazie al rispetto degli standard cosa che non accade con MySQL. Per gli sviluppatori di postgresql affidabilità e integrità dei dati rimangono un obiettivo irrinunciabile.

Architettura

Per quanto riguarda il funzionamento MySQL dispone di un strato SQL mentre può utilizzare più engine dati. Ad esempio il motore di archiviazione myISAM  può essere scelto per situazioni con  maggiore concorrenza in lettura e che non richiedono proprietà ACID. InnoDB per avere il supporto ACID anche se non completo.  Invece Postgresql è un database server unificato con un unico  motore di archiviazione.

Al momento gli elementi distintivi che più contraddistinguono i due e che vanno valutati nella scelta di un database SQL opensource sono l’aderenza agli standard SQL, la conformità ACID e la necessità di usare una piattaforma LAMP. Come affermato in premessa l’aderenza alla normativa ISO è sempre stata un tratto distintivo di postgresql. Quindi se si ha la necessità di sviluppare un progetto che abbia questa caratteristica postgresql diventa la prima scelta.

Porting

Se si desidera la possibilità di trasferimento (porting) del progetto, perchè è cresciuto molto, su database Oracle. Postgresql, rispettando  gli standard ISO per SQL, rende il passaggio sufficentemente agevole. Con MySQL questo non sarà possibile poichè non é conforme agli standard ISO e non segue l’impostazione ACID  Atomicità, Coerenza, Isolamento e Durabilità sono le proprietà logiche che devono contraddistinguere le transazioni) . Nel caso di MySQL il trasferiemento può richiedere una pesante riscrittura del progetto. LAMP e Riutilizzo del Codice Molti progetti girano sul LAMP (Linux, Apache, MySQL, PHP/Pearl/Python) web applications come WordPress, Joomla, Mediawiki, …. si appoggiano preferibilmente su MySQL. Molte di queste applicazioni non prevedono la possibilità di utilizzare postgresql pertanto la scelta operata a questo livello condiziona la scelta del database. Per quanto riguarda la possibilità di riutilizzo di codice va evidenziato che esiste molto più materiale, codice a disposizione per MySQL che per postresql proprio per l’ampia dissusione dello stesso nelle web applicaiton. Risorse:

Per un confronto più articolato : MySQL vs PostgreSQL – WikiVS

Nel blog:

Risorse:

SQLExpress 8 reset sa password Windows server 2008

Può capitare di non disporre o dimenticare la password dell’utente amministratore sa di SQL Express. In questo caso per resettare la password dell’ amministrator sa è necessario avviare SQLExpress in single mode.  Utilizzando SQL Manager Studio una volta  avviato SQLExpress in single mode la procedura per il reset della password di sa risulta decisamente agevole.

Per avviare SQLExpress in single mode accedere via pannello di controllo al servizio SQL Server (SQLEXPRESS)

Fermare il servizio premere il tasto destro del mouse sul servizio e nel tab General in basso inserire nel campo Start Manager l’opzione – m e riavviare il servizio premendo Start.

 

Poichè il single mode è permessa una sola connessione attiva controllare che SQL controllare che SQL Server Agent non sia in esecuzione.

 

Utilizzando il tasto destro del mouse “Avviare SQL Manager Studio” in modalità Amministratore < Run as administrator >.

Quindi modificare la password dell’administrator sa in <Security> <Logins>

Altra possibilità per avviare in single mode SQLExpress

 

Avviare SQL Server Configuration Manager

In Microsfot SQL Server Managemente Studio menu [View]  di Selezionare  [Registered Servers]

Nella sezione Registered Server selezionare il server premere il  Tasto destro del mouse e clikkare SQL Registered Configuration Manager. Seguirà la richiesta per avviare il servizio come amministratore.  A questo livello è possibile controllare il servizio e modificare i parametri

 

 SQL Server Configuration Manager 

Per accedere alle proprietà di SQLExpress si può premere anche il tasto destro del mouse in Object Explorer di SQL Server Manager Studio

 

 

Risorse:

http://msdn.microsoft.com/en-us/library/dd207004.aspx

 

 

Installare Adminer 3.6.3 in Ubuntu 12.04 server

adminer-logoRilasciata in gennaio la versione 3.6.3 di Adminer una interfaccia di amministrazione per database SQL caratterizzata da pulizia ed efficacia oltre a MySQL, supporta PostgreSQL, SQLite, MS SQL, Oracle. A differenza di phpAdmin consiste in un unico file php.

adminer login postgresql

adminer login postgresql

Disponibile anche Adminer-editor un interfaccia user-friendly e di facile utilizzo per editing dei dati dei database per l’utente finale. Adminer e Adminer editor dispongono di plugins e  skins.

Installare Adminer

Per installare adminer in Ubuntu 12.04 è necessario avere installato mysql-server, apache2 e php. Sono necessari anche i moduli php per i database con i quali si intende interagire. Ad esempio
per amministrare mysql dovrà essere installato il modulo php5-mysql, mentre per postgresql è necessario il modulo php5-pgadmin.

I moduli possono essere installati con

sudo apt-get install php5-mysql php5-pgsql

Creare la directory /usr/share/adminer

sudo  mkdir /usr/share/adminer
sudo  cd /usr/share/adminer

scaricare l’ultima versione di adminer

sudo wget "http://www.adminer.org/latest.php"

creare un link simbolico adminer.php

sudo ln -s latest.php adminer.php

Configurare apache perchè utilizzi adminer utilizzando echo e quindi riavviare apache2

echo "Alias /adminer.php /usr/share/adminer/adminer.php" > /etc/apache2/conf.d/adminer.conf

service apache2 restart

Adminer mysql

Disinstallare Adminer

Per disinstallare adminer  rimuovere la directory /adminer e il suo contenuto

sudo  rm -R /usr/share/adminer

rimuovere anche il file di configurazione apache2 e riavvare il web server

sudo rm /etc/apache2/conf.d/adminer.conf
service apache2 restart

Risorse:

 

pgScript vs pl/Pgsql postgresql

postgresql

PgScript è un linguaggio di scripting usato per maneggiare i dati del database postgresql tuttavia non è tipico di Postgresql ma di PgAdmin 3 il tool di amministrazione ad interfaccia grafica di postgresql. Pertanto opera esclusivamente a livello client.

pl/Pgsql invece è di fatto una estensione del linguaggio SQL tipica di Postgresql,  ricorda analogo linguaggio di Oracle. Ovviamente pl/Pgsql opera a livello server.

Ne consegue che le finalità sono completamente differenti .

Esempio di pgScript

 SET @N = 0 ; 
 SET @PROGR = 'pgScript' ;
 -- PRINT @PROGR + 'features' + CAST (@N AS STRING ) ;
 
 SET @N = @N +1 ; 
 PRINT @N ;
 WHILE @N < 10 
 BEGIN 
       SET @N = @N +1 ; 
      PRINT @N ;
 END

pgScript

Risorse:

query di tipo plpgsql 

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

Creare eliminare database e tabelle

Postgresq Numerare le righe di una select query – SQL count row_number

In postgresql per aggiungere un contatore al risultato di una query di tipo select è possibile utilizzare la funzione row_number(). L’uso di row_number() richiede la clausola over.  Il formato generale per numerare le righe di una tabella visualizzata con una select query è :

SELECT 
 row_number() OVER() as rownum, table.*
FROM table;

es:

select row_number() OVER () as num,  sp500.* 
 FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Se è necessario che le i records siano ordinati si può utilizzare l’espressione  OVER ( :

SELECT row_number() OVER ( ORDER BY field nulls last) as rownum, table.* 
  FROM table 
  ORDER BY field ;

es:

select row_number() OVER ( order by date nulls last ) as num,  sp500.* 
FROM sp500 WHERE date > 01-01-2010 ORDER BY date;

Se si desidera inserire anche una colonna con il numero totale si può utilizzare la funzione count (*):

select row_number() OVER( order by date nulls last ) as num,  count(*) over ()  as total; sp500.* FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Oracle utilizza row_number

Count()

In postgresql per contare i records che soddisfano una condizione data si utilizza count utilizzando la forma Sql standard.  Ad esempio:

SELECT  count(c.itemid) AS "Num." ,  c."Country" AS codec
FROM contactsdownloads  INNER JOIN contacts AS c
ON (itemidcontacts) = c.itemid

GROUP BY codec
HAVING  count (c.itemid) > 10

Formato generale

SELECT count(* )  over () , a.*
    FROM  table a  ;

Nella query seguente  viene effettuata la somma e il count a partire non da una tabella ma da  un’altra query.
Questo consente di ottenere un count su due livelli di ragruppamento.
In pratica si effettua un primo raggruppamento dei records che ritornerà il primo conteggio quindi si elaboreranno questi dati con un secondo livello di raggruppamento. Qui si effetturà un nuovo count e si opererà la  somma dei dati del primo count.

SELECT nameprd AS "Name Product" , sum(conta) AS "files downloaded", count (itemidcontacts) AS "N.Users" 
       FROM (SELECT  nameprd , count (itemid) AS conta, itemidcontacts FROM contactsdownloads 
               WHERE createddate > '01-01-2007'
               GROUP BY nameprd, itemidcontacts
               ORDER BY conta DESC ) AS DL

     GROUP BY  nameprd
     ORDER BY sum(conta) DESC;

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

query di tipo plpgsql 

Creare eliminare database e tabelle

Confronto tra i metodi: ExecuteNonQuery, ExecuteReader, ExecuteScalar

I metodi ExecuteNonQuery, ExecuteReader, ExecuteScalar sono i tre principali metodi della classe SqlCommand.Gli oggetti risultanti, una volta instanziata la classe, consentono l’esecuzioni di istruzioni SQL e Stored Procedure in un Database di un Server SQL.

Il metodo ExecuteNonQuery viene usato per eseguire una instruzione SQL e stored procedure di tipo INSERT, DELETE e UPDATE. In questo caso il valore ritornato è il numero di record che sono stati interessati dal comando SQL. Più in generale viene usato per le action queries quindi oltre che per le istruzioni SQL UPDATE, INSERT, DELETE anche per le istruzioni CREATE, ATER, DROP.  Il valore ritornato ( result type ) è di tipo int. Se l’istruzioni SQL coinvolge records il result è il numero degli stessi diversamente è -1. Esempio in  asp.net c#   database postgresql con provider dati Npgsql

...
dbcon = new NpgsqlConnection(NpgsqlConnectionString);
dbcon.Open();
NpgsqlCommand command = new NpgsqlCommand( "DELETE FROM products WHERE itemid = 56", dbcon);
int result reader = command.ExecuteNonQuery();
...

Il metodo ExecuteReader ritorna una collezione di righe e colonne ( records ) e viene pertanto utilizzato per le istruzioni di tipo SELECT. Più in generale ExecuteReader lavora sia con query di tipo SELECT ( Non-Action ) che con le query di tipo Action. Il tipo ritornato essendo una collezione di records sara di tipo DataReader. Esempio in  asp.net c#   database postgresql con provider dati Npgsql

...
dbcon = new NpgsqlConnection(NpgsqlConnectionString);
dbcon.Open();
NpgsqlCommand command = new NpgsqlCommand( "SELECT * FROM products", dbcon);
IDataReader reader = command.ExecuteReader();
...

Il metodo ExecuteScalar ritorna la prima colonna della prima riga della query che viene  lanciata in esecuzione. Le rimanenti righe e colonne sono ignorate. Comodo nel caso di query di tipo Count.  Più in generale vien usato per le query di tipo Non Action con funzioni di aggregazione. Viene ritornato un valore di tipo object.

...
dbcon = new NpgsqlConnection(NpgsqlConnectionString);
dbcon.Open();
NpgsqlCommand command = new NpgsqlCommand( "SELECT COUNT(itemid) AS "Num.", productline FROM products GROUP BY productline", dbcon);
object result = command.ExecuteScalar();
...

Risorse: