Ejemplos codigo pdo sqlite pragma sqlite_master

Coleccion de ejemplos comprobados de código pdo_sqlite, pragma , fetch, extension, transaction,sqlite_master

Programando con PDO SQLite3 – estilo orientado a objetos

Este blog es una colección de programas que muestran como usar SQLite.

Algunos programas necesitan que esten creadas previamente las tablas fieras.s3db, films3.s3db, zoomadrid.s3db, udf.s3db. Todas ellas se pueden crear con alguno de los programas adjuntos, solo hay que buscar los apropiados.

PDO significa PHP Data Objects y es una interfaz unificada para acceder a bases de datos en PHP.
Requiere un driver PDO para cada base de datos particular.

Existe una implementación de PDO y driver SQLite v.3, y además es la única que funciona con PHP5.

El código para declarar un objeto SQLite es:
$db = new PDO('sqlite:cds.db3');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
cds.db3 se refiere a una base de datos instalada en el mismo directorio que el programa.
Si éste no es el caso hay que usar un path relativo.

1.- El nombre de la base de datos puede ser cualquiera.
2.-La declaración es diferente de la que se usaba en las versiones 1 y 2 de SQLite, que era
$db = new SQLiteDatabase('cds.db3');. Además es incompatible, si se intenta abrir una SQLite v.3 de este modo dá error.
3.- El usuario debe tener acceso de lectura y escritura al directorio en que está situada cds.db3.
4.-Si el fichero no existe, se crea, si existe el directorio al que apunta.

Muchas de las declaraciones son similares a las usadas en versiones anteriores de SQLite. Por ejemplo:Suponiendo que haya una tabla “discos” podemos hacer;
$resultado = $db->query('SELECT * FROM discos');
Sin embargo los FETCH son algo diferentes, siempre llevan como parámetro una constante predefinida, en la línea siguiente PDO::FETCH_ASSOC que determina lsa características del fetch, más adelante veremos otras opciones:
$fila = $resultado->fetch(PDO::FETCH_ASSOC);
$array_de_filas = $resultado->fetchall(PDO::FETCH_ASSOC);
Atención a los ::

La primera sentencia fetch suministra un array de una fila de la base de datos indexado por el nombre del campo,es particularmente útil porque permite llamar por los nombres de los campos.
La segunda fetchall es un array de arrays y suministra todas las filas.
Para poner en marcha una base de datos SQLite v.3, es muy útil SQLite Administrator
Opino que usar este programa o uno similar es la mejor opción para crear una base de datos SQLite,definir sus campos, índices, vistas, triggers y demás.
Para trabajar en el código use HTML-Kit, que permite una previsualización del código PHP
y por tanto corregir errores más fácilmente.Pulse aquí para las instrucciones de configuración
Finalmente tenga cuidado al cortar y pegar desde este documento.

SQLITE_MASTER
Todas las bases de datos SQLite disponen de una tabla de sistema sqlite_master
en la que se almacena la información estructural de la base de datos.
Al consultar la tabla de estructura tenemos las columnas siguientes:
1.- type puede ser table, view o index.
2.-name, el nombre del objeto.
3.-tbl_name el nombre de la tabla.
4.-root_page primera página del archivo con datos de este elemento.
5.-sql, sentencias SQL que generan el elemento.
<?php
$db = new PDO("sqlite:fieras.s3db");
// una función para determinar si ya existe la tabla animals
function existe_la_tabla($dataBase, $tablaDB)
{
$result = $dataBase->query("SELECT name FROM sqlite_master WHERE type='table' AND tbl_name=$tablaDB";
return $result;
}
// La llamada a la función
$tabla="animalessssss";
$resultado= existe_la_tabla($db, $tabla);
// el resultado de la llamada
($resultado) ? print("ya existe la tabla ".$tabla) : print("no existe la tabla ".$tabla);
?>
En este caso el resultado es: no existe la tabla animalesssss.


MEMORIA
Podemos situar la base de datos en la memoria. Por ejemplo:
$db = new PDO(‘sqlite::memory:’);
Al trabajar en memoria una vez terminado el programa, la base de datos desaparece.
Mientras existe, $db se comporta como la referencia a un objeto.
Desde luego podemos abrir diferentes bases de datos instanciadas en diferentes objetos,
cada uno independiente de los otros, y pasar datos de unos a otros,
y hacer todo lo que se hace con un objeto.
Las bases de datos en memoria son muy útiles para la programación por permitir usar SQL
Se usan también para poener ejemplos de código.

<?php
try {
/* cada vez se crea una nueva base de datos en memoria */
$db = new PDO('sqlite::memory:');
echo 'database conectada
';
/* creamos una tabla, con tres campos */
$db->exec("CREATE TABLE tableName(tableNameID INTEGER PRIMARY KEY,columna1 TEXT UNIQUE,columna2 TEXT)");
$count = $db->exec("INSERT INTO tableName(columna1,columna2) VALUES ('pepe', 'juan')");
/*** numero de filas afectado ***/
print($count.'
');
$sql = "SELECT * FROM tableName";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print('tipo ='.$row['columna1'] .' animal='. $row['columna2'] . '&ltbr/>');
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
//----le cambiamos a $db el select------
$sql1 = "SELECT columna1 FROM tableName";
// atención al $db seguimos usando la misma base de datos con otro select
$set1= $db->query($sql1) ;
foreach ($set1 as $rowa)
{
print('tipo ='.$rowa['columna1'].'&ltbr/>');
}
?>

CREAR UNA TABLA
El método es el mismo que usamos en la creación de una tabla en memoria.Sin embargo es
mucho más cómodo y directo usar SqliteAdministrator, para crear bases de datos, tablas, campos, etc.
(No es posible usar SqliteAdministrator con las bases de datos en memoria).

Vamos a usar una base de datos fieras.s3db y añadirle una tabla, con tres campos.
Para ello usaremos el método $db->exec(“SQL command”);

Se toman precauciones, la base de datos no se abre, si no existe, o sea no se crea nueva,
lo mismo se hace con la tabla.
Total que el ejemplo sólo funciona si la base de datos existe y no tiene la tabla que se quiere crear.

<?php
try
{
/*** connect to SQLite database ***/
$baseDeDatos = "fieras.s3db";
// la comprobación se limita a constatar si existe el fichero
if(file_exists($baseDeDatos))
{
$db = new PDO("sqlite:$baseDeDatos");
echo 'database abierta';
//comprobar si la tabla necesita ser creada, aqúí usamos sqlite_master
$st = $db->query("SELECT name FROM sqlite_master WHERE type = 'table' AND tbl_name='animals' ");
// el QUERY devuelve false si no se cumple el SELECT
if( !$st )
{
$sqlCreateTable = 'CREATE TABLE animals(id_animal INTEGER PRIMARY KEY AUTOINCREMENT,
id_tipo VARCHAR(40) NOT NULL,id_name VARCHAR(40) NOT NULL)';
$db->exec($sqlCreateTable);
}
else
echo 'La tabla ya existe';
}
else
echo 'La base de datos'. $baseDeDatos.' no existe y no se ha creado';
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Notas
1.- id_animal Integer Primary Key crea una columna Auto-increment.
No tenemos en adelante que preocuparnos por ella, al insertar irá añadiendo números al campo.

INSERT
Para introducir valores en una tabla usamos INSERT y el método exec.
<?php
try {
/*** conectar a la database ***/
$db = new PDO("sqlite::memory:");
echo 'database abierta';
$sqlCreateTable = 'CREATE TABLE animals(id_animal INTEGER PRIMARY KEY AUTOINCREMENT,
id_tipo VARCHAR(40) NOT NULL,id_name VARCHAR(40) NOT NULL)';
$db->exec($sqlCreateTable);
$count = $db->exec("INSERT INTO animals(id_tipo,id_name) VALUES ('kiwi', 'troy')");
/*** numero de filas afectado ***/
echo $count;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
La salida debe ser:
Database abierta
1
Esto muestra que hemos conectado bien y que hemos insertado una fila, sino $count
devolvería 0.
Se podría usar el mismo sistema para introducir más datos, pero es más eficiente usar un TRANSACTION.

CREAR UN PREPARED STATEMENT
Un prepared statement es una sentencia SQL precompilada que acepta cero o varios parámetros.
Hay dos tipos diferentes, con nombres, y con place holders.
Usamos $preSt = $dbh->prepare($insercion)

1.-Con nombres

<?php
try {
/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
/*** un mensaje que muestra que se abrió, o cuidado que se creó ***/
echo 'database abierta';
// hacer el prepared statement
$insercion="INSERT INTO animals(id_tipo,id_name) VALUES (:id_tipo,:id_name)";
$preSt = $dbh->prepare($insercion);

//ligar parámetros
//----------------------------------------------------
$preSt->bindParam(':id_tipo',$id_tipo_val);
$preSt->bindParam(':id_name',$id_name_val);
//----------------------------------------------------
$id_tipo_val = "pájaro";
$id_name_val = "cigueña";
$preSt->execute();

$id_tipo_val = "pájaro";
$id_name_val = "ruiseñor";
$preSt->execute();

$id_tipo_val = "pájaro";
$id_name_val = "jilguero";
$preSt->execute();

}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
NOTAS
1.-Atención a este $preparedStatement ->execute(); No tiene nada que ver con el $dbConn->exec de Crear Tabla.
2.-Los $preparedStatement->bindParam(':column1', $column1_val); ligan las columnas de la tabla y sus valores.
Estamos usando una base datos ya creada fieras.s3db en la que existe dos columnas id_tipo, id_name.
3.-Observar que se ejecutan tres $preSt->execute();
Cada uno con diferentes valores.


2.- Con PlaceHolders ? posicionales. (Reservadores de sitio posicionales).

Atención: No se pueden mezclar con los anteriores, (Con Nombre).
Este ejemplo realiza una query INSERT sustituyendo un name y un value por los placeholders ? posicionales.

<?php
/*** connect to SQLite database ***/
$db = new PDO("sqlite::memory:");
echo 'database abierta &ltbr />';
// crear una tabla
$sqlCreateTable = 'CREATE TABLE numeros(id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(40) NOT NULL,value INTEGER NOT NULL)';
$db->exec($sqlCreateTable);
$stmt = $db->prepare("INSERT INTO numeros(name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);
// insertar una fila
$name = 'one';
$value = 1;
$stmt->execute();
// insertar otra fila con valores diferentes
$name = 'two';
$value = 2;
$stmt->execute();
//query devuelve un set, o si el SELECT no es correcto devuelve false.
$sql = "SELECT * FROM numeros";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print('nombre: '. $row['name'] .'---- '. 'valor = '.$row['value'] . '&ltbr />');
}
?>

QUERY
A diferencia de exec, el método QUERY retorna un objeto, o si el SELECT no es correcto,lo que devuelve QUERY es false, en éste último caso el resultado es que el FOREACH dá error.
<?php
try {
/*** conectar a la base datos ***/
$dbh = new PDO("sqlite:fieras.s3db");
/*** confirmar la conexion ***/
echo 'database abierta';
$sql = "SELECT * FROM animals";
$obj= $dbh->query($sql) ;
/*
Los dos print indican que query($sql) es similar a $set= $dbh->query($sql,PDO::FETCH_BOTH),
(ver más adelante).*/
foreach ($obj as $row)
{
print('tipo ='.$row['id_tipo'].' animal='.$row['id_name']. '&ltbr/>');
print('tipo ='.$row[1] .' animal='. $row[2] . '&ltbr/>');
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
Notas
1.- Atención: Se puede iterar directamente sobre el $obj con foreach. Esto se debe a que internamente
el query implementa el SPL iterator, dando por tanto todos los beneficios de usar SPL.
El mayor beneficio es que los iteradores SPL sólo tratan un elemento a la vez y por tanto se puede trabajar
con sets grandes sin problemas de memoria.
2.-En el bucle try-catch, el try es normal pero catch(PDOException $e) no. Lo trataremos más adelante.

UPDATE
Para actualizar un campo en la base de datos usamos de nuevo el método $dbh->exec
pero con una sentencia SQL, UPDATE
<?php
try
{
/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
/*** a little message to say we did it ***/
echo 'database abierta';
$count = $dbh->exec( "UPDATE animals SET id_name='cernícalo' WHERE id_name='cigueña'");
/*** numero de filas afectado ***/
echo Se alteraron ".$count." filas";
/*** cerrar la conexión ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

De nuevo vemos que la conexión fué correcta y las filas que se modificaron.
Si no se modificó ninguna es que el where no localizó ninguna con la condición especificada.

QUERY CON DOS PARÁMETROS
El query simple se comporta como un query($sel,PDO::FETCH_BOTH)
pero hay otras posiblidad cómo query($sel,PDO::FETCH_NUM) y query($sel,PDO:FETCH_ASSOC.).

En ocasiones es conveniente disponer sólo de un índice asociativo o numérico.

Esto es lo que se hace en el código siguiente con:
($dh->query(' SELECT * FROM animals', PDO::FETCH_ASSOC)
Naturalmente para el índice numérico se usaría: PDO::FETCH_NUM

PDO::FETCH_ASSOC
Para suministrar el equivalente a un array asociativo se usa la constante PDO::FETCH_ASSOC
los nombres de las columnas como índices o claves del array resultante.
<?php
try
{
/*** conectar a la base de datos ***/
$dbh = new PDO("sqlite:fieras.s3db");
echo 'database abierta';
$sel ='SELECT * FROM animals';
$que=$dbh->query($sel,PDO::FETCH_ASSOC);
foreach($que as $rowa)
print($rowa['id_animal']."----------". $rowa['id_name']."&ltbr />");
/*** cerrar la conexión ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
El $que=$dbh->query($sel,PDO::FETCH_ASSOC);
ha devuelto los resultados como un objeto PDOStatement que puede ser iterado directamente.
Los índices resultantes son los nombres de los campos en la base de datos tableName.
/** Se puede comprimir el código pero es menos legible
foreach ($dbh->query('SELECT * FROM animals',PDO::FETCH_ASSOC) as $row)
print($row['id_animal']."........". $row['id_name']."&ltbr />");**/

fetchall(PDO::FETCH_NUM)
El objeto resultado del query, es un objeto de una clase que incluye diferentes
funciones de tipo fetch
que podemos usar para obtener diferentes resultados. Estas funciones pueden recibir parámetros de tipo PDO::
En este caso vamos a usar $obj->fetchall(PDO::FETCH_NUM)
<?php
try
{
/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
echo 'database abierta ';
$sql = "SELECT id_animal,id_tipo,id_name FROM animals";
$obj = $dbh->query($sql);
$fet=$obj->fetchall(PDO::FETCH_NUM);
/*Atención: Aplicamos el PDO::FETCH_NUM sobre fetchall(), no sobre el query().
Esto produce el estallido de la matriz de línea ($row)
*/
foreach($fet as $row)
{
foreach($row as $key=>$val)
{
echo $key.': '.$val.' ';
}
echo '&ltbr />';
}
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

El resultado es:
database abierta
0: 17 1: pájaro 2: cernícalo
0: 18 1: pájaro 2: ruiseñor
0: 19 1: pájaro 2: jilguero
Los índices resultantes son ahora numéricos en cada row.


fetch(PDO::FETCH_BOTH)
Este es un ejemplo de $result = $stmt->fetch(PDO::FETCH_BOTH), al actuar sobre el fetch,
estallamos la matriz y podemos usarla directamente en el FOREACH,
bien con índices de campo o numéricos, según nos convenga.
En realidad el FOREACH del ejemplo lo hace con los dos.
En este caso particular mas bien se hace confusa la devolución de resultados.
En el ejemplo se devuelven ambos índices.
<?php
try
{
/*** connect to SQLite database ***/
$dbh = new PDO("sqlite:fieras.s3db");
echo 'database abierta ';

$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
$result = $stmt->fetch(PDO::FETCH_BOTH);
foreach($result as $key=>$val)
{
echo $key.' - '.$val.'&ltbr />';
}
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

fetchAll(PDO::FETCH_OBJ)
PDO::FETCH_OBJ devuelve un objeto de una clase anónima ,
que mapea los nombres de las columnas de la base de datos como propiedades de objeto
como si fuesen valores de variables de una clase.
No hace falta definir una clase, como en el siguiente FETCH_CLASS.

<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
echo 'Connected to database&ltbr />';
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
$arrBestia = $stmt->fetchAll(PDO::FETCH_OBJ);
/*** propiedades ***/
echo $arrBestia[1]->id_tipo.' ';
echo $arrBestia[1]->id_name.' ';
/*** cerrar conexion ***/
$dbConn = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
NOTA
Usar los nombres de las columnas como propiedades de clase hace más sencillo integrar
los resultados en un ambiente OOP.

fetch(PDO::FETCH_CLASS)
PDO::FETCH_CLASS instancia un array de objetos de la clase especificada.
Los nombres de los campos se mapean a las propiedades (variables) de igual nombre,dentro de la clase especificada.
Específicamente los nombres de de los campos de la base de datos se asignan a las variables de igual nombre en la clase especificada, si no existen se crean.
Esto permite ahorrar código y acelerarlo puesto que los mapeos se tratan internamente.
Suponemos que existe la base de datos fieras.s3db que se creó en CREATE, o bien que ha sido creada con SQLiteAdmin.

<?php
class animales
{
public $id_animal;
public $id_tipo;
public $id_name;
public $tip_name;
public $stapu;
//funcion para poner en mayúsculas la primera letra
retornando public function capitalizeType($pepe)
{
return ucwords($pepe);
}
public function pega()
{
return this->id_tipo."....".$this->id_name."....".$this->stapu."
";
}
}
try {
$dbh = new PDO("sqlite:fieras.s3db");
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
//aquí estamos haciendo el enlace de $stmt con la clase animales
//el $arrObj que retornamos ya es un array de objetos de la clase animales $arrObj = $stmt->fetchall(PDO::FETCH_CLASS, 'animales');
/*** iteramos el objeto directamenie ***/ foreach($arrObj as $bestia)
{
$bestia->stapu="34";
/*** Llamamos a pega y a capitalizeType ***/ echo $bestia->capitalizeType($bestia->pega());
}
/*** close the database connection ***/ $dbConn = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
El código anterior produce una lista de animales con la primera letra en mayúsculas.
Usando la constante PDO::FETCH_CLASS se han suministrado los nombres directamente desde la base datos al $arrObj (que es un array de objetos de la clase animales).
En el objeto se modificaron los resultados, poniendolos con la primera letra en mayúsculas, y definiendo el valor de una de las variables del objeto,realmente podríamos haber definido una propiedad en vez de la variable.

fetchObject()
PDO suministra una alternativa a PDO::fetch y PDO::FETCH_CLASS que es:
PDOStatement::fetchObject().
El resultado es similar pero con una sóla línea.
Usamos un bucle while para ir recorriendo los registros de la base de datos
<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
//definimos una clase animales, diferente de la tabla animals de la base de datos
class animales
{
public $id_animal;
public $id_type;
public $id_name;
public $nombreAnimal;
//funcion para poner en mayúsculas la primera letra
public function capitalizeType($pepe)
{
return ucwords($pepe);
}
}
//-----------------------------------------------------------
/*** aquí estamos haciendo el enlace de $stmt con la clase animales, el $obj que retornamos
ya es un objeto de la clase animales.Con el while recorremos los registros hasta el final.***/
while($obj = $stmt->fetchObject('animales'))
{
// la siguiente sentencia implica que estamos en un objeto de la clase animales
$obj->nombreAnimal = $obj->id_name."-"."animal";
//llamada al método capitalizeType
echo $obj->capitalizeType($obj->id_tipo)." = ".$obj->capitalizeType($obj->id_name).'&ltbr />';
echo $obj->nombreAnimal.'&ltbr />'.'&ltbr />';
}
/*** cerrar la conexión ***/
$dbConn = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
NOTA
Puede escribirse PDO::fetchObject()en vez de PDO::FETCH_OBJ.

FETCH_INTO
La constante PDO::FETCH_INTO nos permite incrustar datos en un array de objetos de una clase.
Los valores iniciales del objeto se sustituyen por los incrustados.
Cómo en PDO::FETCH_CLASS los nombres de los campos se mapean a las propiedades(variables) de la clase.
En este caso, el modo fetch se establece usando el método PDO::setFetchMode(PDO::FETCH_INTO, $bestias)

<?php
try
{
$dbh = new PDO("sqlite:fieras.s3db");
$sql = "SELECT * FROM animals";
$stmt = $dbh->query($sql);
/*** definimos una clase animales, atencion a la diferencia con el campo animals de la base de datos
***/
class animales
{
public $id_animal;
public $id_tipo;
public $id_name;
public $nombreAnimal;
//funcion para poner en mayúsculas la primera letra

public function capitalizeType($pepe)
{
return ucwords($pepe);
}
}
//---------------------------------------------------------------------------
/*** instanciar un nuevo objeto de la clase animales ***/
$bestias = new animales;
$bestias->id_animal = 14;
$bestias->id_tipo = 'pez';
$bestias->id_name = 'merluza';
//----------------------------------------------------------------------
//valor devuelto por el objeto $bestias
echo $bestias->id_name.' ';
/*** set el modo fetch con PDO::setFetchMode() ***/
$stmt->setFetchMode(PDO::FETCH_INTO, $bestias);
/*** loop sobre el array de objetos ahora los valores ya están sustituidos ***/
foreach($stmt as $bestias)
{
echo $bestias->id_name.' ';
echo $bestias->capitalizeType($bestias->id_name).' ' }
/*** cerrar la conexión ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Transacciones(Transactions)
Hemos visto anteriormente como usar varias sentencias INSERT para construir la base de datos inicial.
Esto funciona bien pero tiene mucho código,y con SQLite surge un problema,
el bloqueo de fila para cada acceso.
Usando una TRANSACTION el proceso puede condensarse en un solo acceso.
Las transacciones son muy sencillas y tienen el beneficio de poderse echar atrás si tiene lugar un error,
por ejemplo una caída del sistema.
La transacción se inicia con el método beginTransaction().
Este método suprime el auto-commit, con lo cual cualquier sentencia o query no se ejecuta hasta que
se envía commit.
Cuando se llama a commit, todas las sentencias/querys se ejecutan y se devuelve
la base datos al estado auto-commit.
El ejemplo muestra como podríamos hacerlo en la base de datos zoomadrid.s3db.
<?php
try
{
$dbh = new PDO("sqlite::memory:");
echo 'Conectado a la base de datos';
/*** poner el modo PDO error a excepción ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** Crear una tabla ***/
$table = "CREATE TABLE animals(animal_id INTEGER NOT NULL PRIMARY KEY,
animal_type VARCHAR(25) NOT NULL,
animal_name VARCHAR(25) NOT NULL
)";
$dbh->exec($table);
/*** comenzar la transacción ***/
$dbh->beginTransaction();
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('emu', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('funnel web', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('lizard', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES('dingo', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('kangaroo', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('wallaby', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('wombat', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('koala', 'bruce')");
$dbh->exec("INSERT INTO animals (animal_type, animal_name) VALUES ('kiwi', 'bruce')");
/*** commit la transacción ***/
$dbh->commit();
echo 'Data entered successfully';
$sql = "SELECT * FROM animals";
$set= $dbh->query($sql,PDO::FETCH_ASSOC) ;
foreach ($set as $row)
{
print('tipo ='.$row['animal_type'] .' animal='. $row['animal_name'] . ' ');
}
}
catch(PDOException $e)
{
/*** roll back la transacción si hay fallo ***/
$dbh->rollback();
echo $sql . ' ' . $e->getMessage();
}
?>

ATTACH DATABASE
La sentencia ATTACH DATABASE permite añadir otras bases de datos a la conexión con la base datos principal.
El nombre de la base de datos a añadir debe ir entre comillas .
Las bases de datos añadidas pueden quitarse usando la sentencia DETACH DATABASE.

Se puede leer y escribir a una base datos añadida e incluso se puede modificar su esquema.
Pero no se puede crear, en la base de datos añadida, una tabla con el mismo nombre que otra existente ,
aunque sí se pueden añadir bases de datos que contengan tablas con el mismo nmbre que la base de datos principal,
incluso se pude añadir una base de datos varias veces.
La posibilidad de añadir varias bases de datos permite por ejemplo tener una base de datos
por cada caja de una tienda, sin problemas de bloqueo,
pudiendo tratarlas luego individualmente o agrupadas.
Hay un límite: No se pueden añadir más de 10 bases de datos.

Las tablas en una base de datos añadida se referencian, si son iguales en dos de las tablas,
usando la síntaxis nombreBaseDeDatos.nombreTabla.
Las Transacciones que envuelven bases de datos añadidas son atómicas,
siempre que la base principal no sea “:memory:.
Si la base principal es ":memory:" entonces las transacciones continúan siendo atómicas
dentro de cada fichero de base de datos individual. Pero si el ordenador casca en medio de un COMMIT
puede suceder que algunos ficheros se modifiquen y otros no.
El siguiente ejemplo es una demostración muy simple, en que el objeto db conecta a films3.s3d
y le añade fieras.s3db, permitiendo un SELECT mixto de ambas bases de datos.
El SELECT permite todo lo que permite la sentencia select como claúsulas WHERE.
<?php
try
{
/*** conectar a SQLite database ***/
$db = new PDO("sqlite:films3.s3db");
echo 'database creada
';
$db->exec("ATTACH DATABASE 'fieras.s3db' AS fieras");
$sql = "SELECT id_tipo, id_name, nombrePel FROM fieras.animals,peliculas";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print $row['id_tipo'] .' - '. $row['id_name'] . $row['nombrePel'].' ';
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Una instancia Global
¿Ha necesitado alguna vez una instancia global de su conexión a la base de datos?.
Aquí lo conseguimos mediante el uso de una plantilla SINGLETON.
El objetivo de un Singleton es lograr que la clase tenga un solo objeto(instancia) y
suministrar un punto de acceso a ella.
Una nueva instancia se crea sólo la primera vez que se accede a ella.
En los siguientes accesos se devuelve simplemente la instancia existente.
<?php

class db
{
/*** Declarar la instancia ***/
private static $instance = NULL;
// el constructor se declara privado para que
//nadie pueda crear una nueva instancia usando new.
private function __construct()
{
/*** aquí no hace falta poner nada ***/
}
/***
Devolvemos la instancia de la base de datos o creamos la conexión inicial,
lo que devolvemos es un objeto PDO, el acceso a la función es público
***/
public static function getInstance()
{
if (!self::$instance)
{
self::$instance = new PDO("sqlite:zoomadrid.s3db");
self::$instance-> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return self::$instance;
}
// También hacemos private la función _clone para evitar clonar la instancia.
private function __clone()
{ }
}
//---------------- fin de la clase------------------
try
{
/*** query la base de datos ***/
$sql="SELECT * FROM animals";
$result = db::getInstance($sql->query);
/*** loop ***/
foreach($result as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . ' ';
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
El código anterior porduciría el siguiente resultado:
emu - bruce
funnel web - bruce
lizard - bruce
dingo - bruce
kangaroo - bruce
wallaby - bruce
wombat - bruce
koala - bruce

Este método de acceso ahorra el código creado cuando se llama una nueva instancia del objeto
cada vez que es referenciado. Además si desea pasar el estado del objeto de una referencia a otra no hay necesidad de crearla desde el estado inicial.
Nótese que los métodos constructor y clone se han declarado privados para que una instancia de la clase no pueda ser instanciada ni clonada.

Tratamiento de Error
El tratamiento de errores en PDO viene en varios aromas.
Previamente hemos usado el más sencillo de los bloque try{} ccatch{}
para devolver un error en la conexión de la base de datos.
Pero ¿que pasa con otros errores, por ejemplo si el nombre de un campo no existe?.
Veamos primero como tratamos un error sencillo en el código anterior.
<?php
try
{
$dbh = new PDO("PDO("sqlite:zoomadrid.s3db");
echo 'Connected to database
';
/*** El SELECT con un nombre de campo incorrecto ***/
$sql = "SELECT username FROM animals";
foreach ($dbh->query($sql) as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . '&ltbr />';
}
/*** cerrar conexión ***/


El código anterior producirá el siguiente error:
Connected to database
Warning: Invalid argument supplied for foreach() in /www/pdo.php on line 18

Esto se debe a que no hay tratamiento del error.
El SELECT especifica un campo que no existe pero no podemos ver cual es el error.
Para remediar ésto necesitamos añadir un atributo al tipo de manejo de error que queremos utilizar.

Los tipos de manejo de error son:
Exception
Warning
Silent
Comencemos con exception con el código anterior

<?php
try
{
$dbh = new PDO("PDO("sqlite:zoomadrid.s3db");
/*** echo a message saying we have connected ***/
echo 'Connected to database';
/*** set the error reporting attribute ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** The SQL SELECT statement ***/
$sql = "SELECT username FROM animals";
foreach ($dbh->query($sql) as $row)
{
print $row['animal_type'] .' - '. $row['animal_name'] . '
';
}
/*** close the database connection ***/
$dbh = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
Ahora como el modo de error se puso a Exception, la advertencia aparecería así:
Connected to database
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'username' in 'field list'

Para poner el modo de error a Warning sustituiríamos la línea
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
por:
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
Ahora se desplegaría una advertencia diferente

Connected to database
Warning: PDO::query() [function.PDO-query]: SQLSTATE[42S22]: Column not found: 1054
Unknown column 'username' in 'field list' in /www/pdo.php on line 21
Warning: Invalid argument supplied for foreach() in /www/pdo.php on line 21

Finalmente hay el modo Silent.
Este modo silencia el error de modo que no se emite advertencia.
Sin embargo no detiene el código en el punto de error y todos los errores siguientes se siguen emitiendo.
Para usarlo pondríamos:

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
Ahora se desplegaría la siguiente advertencia:

Connected to database
Warning: Invalid argument supplied for foreach() in /www/pdo.php on line 21
El error se silenció pero aparecen los siguientes.

USER DEFINED FUNCTIONS (UDF)
Se puede extender SQLite para incluir funciones definidas por el usuario que se escriben en PHP,
se pueden usar las funciones predefinidas en ese lenguaje, y las que nosotros definamos..
Con una UDF usando sqliteCreateFunction se pasa la función PHP a una función en SQLite
y se pueden usar todas las facilidades de una base de datos, cómo ordenar o seleccionar.

Las UDF son buenas para trocear strings y ejecutar agrupaciones y listados no standard.
Por ejemplo si se quiere tratar una lista de URLs. quizá procedente de un log,
y crear una lista de nombres de host únicos, en minúsculas y ordenados alfabéticamente.

http://Example.com/directory/index.html y http://example.com/page.html
podrían convertirse en una sola sentencia: http://example.com/.

Para hacer ésto en PHP hay que recoger todos los URLs, procesarlos en un script,ordenarlos
y quitar los duplicados.
En SQL todo esto se haría usando DISTINCT y ORDER BY.

La misión de la UDF es pasar una función escrita en PHP a SQLite.
No se escribe nada en la base de datos propiamente dicha.
Si se abre en otro programa la misma base de datos hay que escribir de nuevo la función PHP
y la sqliteCreateFunction.

Con una UDF como la siguiente se pasa todo éste trabajo a SQLite, que es donde debe estar.
Aparte usamos el QUERY con dos parámetros, y el SELECT es más complicado que los anteriores.

<?php
try
{
// Abrir base de datos
$db = new PDO("sqlite:udf.s3db");
echo 'database abierta'." ".";
$urlses = array('http://Example.com/directory/index.html','http://example.com/page.html');
echo "Inserciones:"." ";
foreach ($urlses as $urla)
{
$sql = "INSERT INTO urls(nom_url) VALUES ('$urla')";
$count=$db->exec($sql);
echo $count." ";
echo $urla." ";
}
// UDF escrita en PHP, parse_url($url) está predefinida en PHP

function url2host($url)
{
$parts = parse_url($url);
return "{$parts['scheme']}: //{$parts['host']}/";
}

// Mapeo de la función PHP url2host() a la SQLite host(),
// e indicación de que host() vá a tener 1 argumento.
$db->sqliteCreateFunction('host', 'url2host', 1);

$sel='SELECT DISTINCT host(lower(nom_url)) AS clean_host FROM urls ORDER BY clean_host';
$r = $db->query($sel , PDO::FETCH_ASSOC);

echo " "."Resultado = ";
foreach ($r as $row) print($row['clean_host']);
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Devuelve:
http://example.com/

Para usar una UDF
1.- Se escribe una función normal en PHP.
Los argumentos de la función son los que se desea pasar en el SELECT
y la función debe retornar solo un valor.

La funcion url2host () toma un URL, llama a la fución PHP predefinida parse_url() para trocear el URL
en sus partes componentes, $parts contiene una matriz con estas partes.
Así, http://example.com/directory/index.html se fracciona en varios trozos,
http se almacena en $parts['scheme'], y example.com en $parts['host'].
Esto devuelve un valor http://example.com/.

Ver, para la definición de parse_url()

2.- Se registra url2host() con SQLite usando: $db->sqlite_create_function(). La función toma cuatro argumentos:
2.1.-El manejador de la base de datos,aquí $db.
2.2.-El nombre que se ha elegido para la función en SQLite, aquí 'host'.
2.3.-El nombre de la función en PHP, aquí 'url2host'.
2.4.-El número de argumentos que se espera. Este último argumento es opcional, pero si se conoce con certeza su número ayuda a SQLite a optimizar el código.

Los nombres de las funciones en SQLite y PHP pueden ser iguales,
pero aquí se ponen diferentes para clarificar la distinción entre ambos.
Se la ha denominado host() en SQLite y url2host() en PHP.
Ahora se pude usar host() en cualquier SQL query, que use la misam conexión a la base de datos.

El SQL de arriba 'SELECT DISTINCT host(lower(nom_url)) AS clean_host FROM urls ORDER BY clean_host',
coge la URL almacenada en lacolumna nom_url, la convierte a minúsculas y llama a la UDF host(),
que la transforma.
Al string devuelto se lo llama AS clean_host;
esto permite referirse a los resultados más tarde a clean_host en el query SQL
así como acceder al valor en PHP usando este nombre.

Como está en SQLite tiene la ventaja de poder ordenar la lista usando ORDER BY clean_host,
lo cual ordena los resutados por orden alfabético.

sqliteCreateAggregate
Registra dos funciones para su uso en sentencias SQL.
Descripción

bool PDO::sqliteCreateAggregate ( string function_name, callback step_func, callback finalize_func [, int num_args] )
Atención:La función es EXPERIMENTAL.

PDO::sqliteCreateAggregate() es similar a PDO::sqliteCreateFunction() excepto que registra dos funciones
que pueden usarse para calcular un resultado agregado a través de todas las filas de un query.


La diferencia clave () es que en PDO::sqliteCreateFunction son necesarias dos funciones para manejar el agregado

1.- step_func se llama para cada línea del set resultado.
Su función PHP debe acumular el resultado y almacenarlo en el contexto de agregación.para la definición de parse_url().
2.- Después de que todas las filas se hayan procesado,
se llamará a finalize_func y ella cogerá los datos del contexto de agregación y devolverá el resultado.
Las funciones Callback deben devolver un tipo comprendido por SQLite, (un escalar).

Parámetros
function_name. El nombre de la función que se usará en las sentencias SQL.
step_func. Callback función llamada para cada fila del set reultado.
finalize_func. Callback función para agregar los datos suministrados por los pasos de la función anterior.
num_args. Una orientación al parser SQLite de que la función callback acepta un número predeterminado de argumentos.

Ejemplo
1.-Creamos una step_func que calcule el mayor largo de un string en una de las columnas de la tabla.
Para cada fila se llama la función max_len_step y se le pasa un parámetro context .
Este context es como cualquier otra variable PHP y puede disponerse para contener un array o incluso un objeto.

Aquí lo usamos simplemente para almacenar el mayor largo que hemos encontrado hasta el momento;
si el string tiene un largo mayor que el máximo anterior modificamos el context a su nuevo largo máximo.

2.-Después de que han procesado todas las filas, SQLite llama la función max_len_finalize
para determinar el resultado agregado.
Aquí podríamos efectuar algun tipo de cálculo basado en los datos del context.
En nuestro ejemplo, hemos ido calculado el resultado a medida que progresaba el query,
así que simplemente devolvemos el valor del context multiplicado por 7.

La llamada a $fe=$que->fetch(PDO::FETCH_NUM) es básica,
sino por defecto el fetch llama a PDO::FETCH_ASSOC que devuelve un array asociativo con dos valores.

<?php

$data = array('one','two','three','four','five','six','seven','eight','nine','ten_diez');
$db = new PDO('sqlite::memory:');
$db->exec("CREATE TABLE strings(a)");
$insert = $db->prepare('INSERT INTO strings VALUES (?)');
foreach ($data as $str)
{
$insert->execute(array($str));
}
$insert = null;

function max_len_step($context, $rownumber, $string)
{
if (strlen($string) > $context)
{
$context = strlen($string);
}
echo " ".string: ".$string." valor máximo hasta ahora : ".$context;
return $context;
}

function max_len_finalize($context, $rownumber)
{
echo ' '."valor máximo último : ".$context.' ';
return $context*7;
}

$db->sqliteCreateAggregate('max_len', 'max_len_step', 'max_len_finalize');

$que=$db->query('SELECT max_len(a) from strings');
$fe=$que->fetch(PDO::FETCH_NUM);
foreach ($fe as $row)
{
print('numero de letras m. ='.$row);
}
//una forma alternativa de obtener el resultado $fe[0]
echo ' '.'num_echo = '.$fe[0];
?>

FORMULARIOS Y OTRAS COSAS
Ahora enviar formularios, es sencillo, ya que PDO protege contra ataques de SQL Inyection.
Aquí usamos lo que se le conoce como Prepared Statements o Comandos Preparados,
es igual de sencillo, cuando preparamos un query (ya sea Insert, Select, Update, Call, etc.).
Usamos dos documentos:

1.- Pdo19_Form.html
2._Pdo19_Form.php
El primero llama al segundo

Pdo19_Form.html

<html>
<head>
<title>Pdo-form</title>
<meta http-equiv="" content="text/html"; charset="iso-8859-1" />
</head>
<body>
<form action="Pdo19_Form.php" method="post">
Cliente:
<input type="submit" name="enviar" value="Envíar cliente">
</form
</body>
</html>

El segundo es:
Pdo19_Form.php

<?php
try
{
$db = new PDO("sqlite::memory:");

$sqlCreateTable = 'CREATE TABLE clientes(id_cliente INTEGER PRIMARY KEY AUTOINCREMENT, nom_cliente VARCHAR(40) NOT NULL)';
$db->exec($sqlCreateTable);

$inserto="INSERT INTO clientes(nom_cliente) VALUES (?)";
$preSt = $db->prepare($inserto);
$preSt->bindParam(1,$nomCliente);

$nomCliente= $_POST["cliente"];
$preSt->execute();

$sql = "SELECT * FROM clientes";
$set= $db->query($sql) ;
foreach ($set as $row)
{
print('nombre: '. $row['nom_cliente'] . ' ');
}
echo $_POST["cliente"];
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

Podemos usar directamente el valor de nuestro $_POST, sin temor a que sea una cadena que pueda perjudicarnos,
ya que PDO automáticamente se encarga de analizar y enviar la cadena como un string.
Usamos un prepare con "placeholders"(marcadores), estos son los signos de interrogación en el WHERE "?", seguido por los bindParam
Estos marcadores se van a reemplazar por los valores que se asignen cuando use execute().
Para actualizar solo se cambiaría el $inserto:
UPDATE Clientes SET Nombre=? WHERE idCliente=?

Extensión de la clase PDO
Otra ventaja de usar PDO, es que podemos extenderla y crear nuestra propia clase. A continuación vemos un ejemplo en el que definimos una clase PDOEx, que extiende PDO, y una segunda clase DBStatement que extiende a PDOStatement.

Se ha ampliado con un atributo PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION y una función grid(), en ésta prestar atención al this que es por definición el propio objeto.

El conjunto permite definir una clase PDO ampliada, y una clase PDOStatement asimismo ampliada.

<?php
class PDOEx extends PDO {
function __construct($dsn) {
parent::__construct($dsn);
$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('DBStatement', array($this)));
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

function grid()
{
$numPara =func_num_args();
$a=true;
if( $numPara == 0)
{ }

if( $numPara == 1)
{
$selGrid =func_get_arg(0);
}

if( $numPara == 2)
{
$selGrid =func_get_arg(0);
$cabGrid =func_get_arg(1);

if(is_array($cabGrid)){$a=false;}

else
{
echo 'El segundo parámetro debe ser un array con los nombres de los campos.';
echo'Como no lo es se usan los nombres de las columnas de la BD'; }
}
$que=$this->query($selGrid,PDO::FETCH_ASSOC);
$fetall=$que->fetchall(PDO::FETCH_ASSOC);

echo '<table border="1">';
echo '<tr>';
if($a)
foreach($fetall as $row)
{
foreach($row as $key=>$val)
{

if($a)echo '<th>'.$key.'</th>';
}
$a=false;
}
}
else
{
foreach ($cabGrid as $cab)
{
echo '<th>'.$cab.'</th>';
}
}

echo '</tr>';

foreach($fetall as $row)
{
foreach($row as $key=>$val)
{
echo '<tr><td>'.$val.'</td>';
}
echo '</tr>';

}
echo '</table>';
}

}

class DBStatement extends PDOStatement {
public $dbh;
protected function __construct($dbh) {
$this->dbh = $dbh;
}
}

try
{
$db = new PDOEx('sqlite:fieras.s3db');
echo "abierta"."
";
$sel ='SELECT * FROM animals';
$cab =array('Num','Especie','Nombre');
$db->grid($sel,$cab);
/*** cerrar la conexión ***/
$db = null;
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>

INTEGRIDAD REFERENCIAL MEDIANTE TRIGGERS
SQLite no soporta la integridad referencial entre tablas de una base datos, pero sí los triggers.
Por medio de los triggers podemos realizar la integridad referencial.

Vamos a usar la base de datos films3.s3db, que tiene tres tablas;
Genero: Puede ser tragedia, comedia,western etc.
Soporte: Puede ser celuloide, MP3, etc.
Peliculas: Que lleva el titulo de la película.

La tabla PELICULAS tiene dependencia de GENERO y SOPORTE.
Los triggers antes de insert y antes de update en Peliculas tienen en cuenta estas dos dependencias.
El trigger film_pel_in before insert on peliculas impide insertar una película adjudicándole género o soporte no existente.

El trigger film_pel_up before update on peliculas impide modificar una película adjudicándole género o soporte no existente.

Para antes de delete hacen falta dos triggers uno en soporte y otro en genero.
El trigger film_sop_de before delete on soporte impide borrar un soporte que tenga películas.
El trigger film_gen_de before delete on genero impide borrar un genero que tenga películas.

Si la dependencia fuera de más tablas, sólo habría que añadir más OR en insert y update y los correspondientes triggers on delete.
Si solo dependiera una tabla, se quitaría un OR y el delete que sobre.

El código siguiente crearía la base de datos, sus tablas, campos y triggers.

<?php
$db = new PDO("sqlite:films.s3db");
CREATE TABLE [genero] (
[id_gen] INTEGER PRIMARY KEY NOT NULL,
[nombreGen] VARCHAR(40) UNIQUE NOT NULL
);

CREATE TABLE [soporte] (
[id_sop] INTEGER NOT NULL PRIMARY KEY,
[nombreSop] VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE [peliculas] (
[id_pel] INTEGER NOT NULL PRIMARY KEY,
[id_genero] INTEGER NOT NULL,
[id_soporte] INTEGER NOT NULL,
[nombrePel] VARCHAR(40) NOT NULL
);

CREATE TRIGGER film_pel_in before insert on peliculas
for each row begin
select case
when
(
(new.id_soporte is not null)
and
(new.id_genero is not null)
and
(
((select id_sop from soporte where id_sop = new.id_soporte) is null)
or
((select id_gen from genero where id_gen = new.id_genero) is null)
)
)
then raise(abort,'No se inserta la película')
end;
end;

CREATE TRIGGER film_pel_up before update on peliculas
for each row begin
select case
when
(
((select id_sop from soporte where id_sop = new.id_soporte) is null)
or
((select id_gen from genero where id_gen = new.id_genero) is null)
)
then raise(abort,'No se modifica la película')
end;
end;

CREATE TRIGGER film_sop_de before delete on soporte
for each row begin
select case
when
((select id_soporte from peliculas where id_soporte = old.id_sop) is not null)
then raise(abort,'No se borra el soporte')
end;
end;

CREATE TRIGGER film_gen_de before delete on genero
for each row begin
select case
when
((select id_genero from peliculas where id_genero = old.id_gen) is not null)
then raise(abort,'No se borra el género')
end;
end;
?>