DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Ribamar Sousa

Ribamar has posted 3 posts at DZone. View Full User Profile

Populate PostgreSQL Tables

03.30.2007

| 4149 views |

        This script in PHP populate with demo data tables Postgresql.
It insert serious registers automatically in a table.
Remove the primary key before run.

<?php
/** Rotina para popular tabela do PostgreSQL com massa de testes
  * ColaboraÃ§Ã£o de Ribamar FS - http://ribafs.net - 14/03/2007
  * Requer remoÃ§Ã£o da chave primÃ¡ia antes da execuÃ§Ã£o.
  */ 
?>

<html><head><title>Inserir Registros de Teste</title></head>
<body bgcolor=''>
<h2 align=center>Cadastrar Tabela do PostgreSQL com Massa de Testes</h2>
<h3 align=center><font color=red>Remova chave primÃ¡ia da tabela, antes de executar</font></h3>
<h4 align=center>Observe que nem todos os tipos de dados foram contemplados, alguns ficarÃ£o como string</h4>

<table align=center>
<form method='POST' name=frmIns action='popula_table_pg.php'>

<tr><td>Host</td><td><input type=text name='host' value='127.0.0.1'></td></tr>
<tr><td>Banco</td><td><input type=text name='banco' value='cliente'></td></tr>
<tr><td>UsuÃ¡rio</td><td><input type=text name='usuario' value='postgres'></td></tr>
<tr><td>Senha</td><td><input type=text name='senha' value='postgres'></td></tr>
<tr><td>Tabela</td><td><input type=text name='tabela' value='clientes'></td></tr>
<tr><td>Registros</td><td><input type=text name='registros' value=5></td></tr>
<tr><td></td><td><input type=submit name='popular' value='Popular'></td></tr>
</form>
</table>
</body>
</html>

<?php
if(isset($_POST['popular'])){
	$host=$_POST['host'];
	$banco=$_POST['banco'];
	$usuario=$_POST['usuario'];
	$senha=$_POST['senha'];
	$tabela=$_POST['tabela'];
	$registros=$_POST['registros'];

	$conexao=pg_connect("host=$host user=$usuario password=$senha dbname=$banco port=5432");
	if (!$conexao){
		die('Erro ao conectar ao banco<br>'.pg_last_error($conexao));
	}
	$str="SELECT * FROM $tabela";
	$consulta= pg_query($conexao,$str);
	$nc=pg_num_fields($consulta);
	$nr=pg_num_rows($consulta);

   $n='';//numericos (int, tinyint, smallint, bigint, etc)
   $r=''; //reais (float e double)
   $s="'";//strings
   $d=date('Y-m-d'); //datas
   $dt=date('Y-m-d H:i:s');//datatimes
   $o=''; //outros

   	$inscod .="INSERT INTO $tabela (";
   	for ($j = 0; $j < $nc; $j++) {
      $campo = pg_field_name($consulta, $j);
		if ($j < $nc-1)	$inscod .= "$campo,";
     	else $inscod .= "$campo";
	}
	$inscod .= ")";	 
    $inscod .= " VALUES (";

   for ($j = 0; $j < $nc; $j++) {   
   $tam   = pg_field_size($consulta, $j);
   if($tam == -1) $tam=20; //Caso queira limitar os campos ao mÃ¡ximo de 20 posiÃ§es
		if ($j < $nc-1){ 
			switch (pg_field_type($consulta, $j)){
				case 'int4':
					$n=str_pad($n,$tam,'12345679890');
					$inscod .= "$n,";
					break;
     			case 'float4':
					$r=str_pad($r,$tam,'1234567890');
					$inscod .= "$r,";
					break;
				case 'bpchar':
				case 'varchar':
					$s=str_pad($s,$tam,"abcdefghijklmnopqrstuvxyz");
					$inscod .= "$s',";
					break;
				case 'date':
		 		    $inscod .= "'$d',";
					break;
				case 'timestamp':
					$inscod .= "'$dt',";
					break;
				case 'text':
					$inscod .= "'$t'";
					break;
				default:
					$o=str_pad($o,$tam,"abcdefghijklmnopqrstuvxyz");
					$inscod .= "'$o',";
					break;
			}
     	}else{
     		switch (pg_field_type($consulta, $j)){
				case 'int4':
					$n=str_pad($n,$tam,'1234567890');
					$inscod .= "$n";
					break;
     			case 'float4':
					$r=str_pad($r,$tam,'1234567890');
					$inscod .= "$r";
					break;
				case 'bpchar':
				case 'varchar':
					$s=str_pad($s,$tam,"abcdefghijklmnopqrstuvxyz");
					$inscod .= "$s'";
					break;
				case 'date':
					$inscod .= "'$d'";
					break;
				case 'timestamp':
					$inscod .= "'$dt'";
					break;
				case 'text':
					$inscod .= "'$t'";
					break;
				default:
					$o=str_pad($o,$tam,"abcdefghijklmnopqrstuvxyz");
					$inscod .= "'$o'";
					break;
     		}
     	}
     	
	}
    $inscod .=");";	
	
    for($r=1;$r<=$registros;$r++){	   
		//echo  $inscod;
	   	if(!pg_query($conexao,$inscod)) die ("Erro na inclusÃ£<br>".pg_last_error($conexao));
    }	   
}
?>

Tags:

Comments

Roger Douglass replied on Sat, 2007/04/21 - 12:35pm

Eu acho que otros poderiam usar seu code melhor se tudo fosse escrito em ingles. Ainda bem que leio portugues, mas creio que a maioria aqui apenas leiam ingles. (I think that other's could use your code better if it was all written in English. It's good that I read Portuguese, but I think that the majority here only read English.) Porem, gostei do que ta' fazendo!