[TUTORIAL] Mini-tutorial sqlite

[danko9696]

Como veo que hay gente que busca info sobre SQLITE y no llega a acceder a la info posteada en el hilo sobre la consulta excel (que también sería recomendable echarle un vistazo) he pensado que no sería mala idea crear un hilo aparte específico para ello reciclando el mini tutorial y añadiendo un par de cosillas.

Para un uso básico sin conocimientos previos en un principio lo más complicado/lioso creo no es el lenguaje en sí sino preparar los archivos/base de datos:
- Podemos coger una base de datos que ya esté creada (de alguna app instalada) y hacer una copia en una carpeta personal o crear una base de datos nueva. Por ejemplo usando SQLite Editor -> New Database -> seleccionamos ruta donde la queremos crear y nombre de la base de datos (por ejemplo tasker_pruebas).
- A continuación, en TABLES, damos a settings y NEW TABLE. Escribimos el nombre de la tabla, por ejemplo POBLACIONES y ahora hay que añadir los campos que queremos que tenga.
- De los datos que hay que rellenar, para empezar los importante son nombre del campo (obviamente), como NOM_POBLACION, y sobre todo el tipo de dato: número entero, real -número con decimales-, texto, blob (para almacenar datos binarios, como fotos, por ej), fecha, timestamp y time. Para poblaciones podríamos tener nom_poblacion text, c_p text, habitantes, int. Los otros parámetros no son fundamentales para un comienzo.

para ejecutar la consulta desde tasker usar el comando shell con;
sqlite3 <ruta de la base de datos>/nombre_base_datos.db "<consulta>"
por ej, asumiendo que usamos tres variables en tasker:
sqlite3 /storage/emulated/0/Download/test_base_de_datos.db "insert into poblaciones (nom_poblacion,c_p,habitantes) VALUES ('%var_nom_pob','%var_c_p',%var_numhab);"

Luego para leer datos lo mismo que antes pero ahora añadiendo una variable para recoger el resultado de la consulta.
y usando SELECT <campos separados por comas> FROM <tablas separadas por comas> WHERE <condiciónes> ORDER BY <orden> LIMIT <máximo número filas que queremos> Para construir las consultas mejor usa el SQLite Debugger, mejor interface que el otro para ello.

NOTAS:
- si pones SELECT * ...., el asterisco indica que coja todos los campos
- si el select incluye varios campos, en el texto devuelto a la variable indicada estos vendrán separados por tuberías |
- IMPORTANTE para el uso con tasker, si la consulta devuelve más de una fila muy recomendable añadir lo siguiente después de la última comilla: |awk '{printf("%s%v_char_separacion",$0)}'
por ej, sqlite3 ...ruta bd y archivo... "SELECT .... FROM ... WHERE ... " |awk '{printf("%s%v_charsplit",$0)}'
Esto hace que cada fila esté separada por el caracter que queramos (%v_charsplit). Yo normalmente uso alguno raro, como el símbolo del yen o del estilo, algo que creas que no va a aparecer en el texto ni de casualidad.
- Muy útil cuando hay muchos datos. Podemos usar la cláusula LIMIT al final (antes del awk, dado que este no forma parte de la consulta sql sql). Un ...WHERE habitantes>100 AND habitantes<1000 ORDER BY nom_población DESC LIMIT 10 nos dará los 10 primeros resultados con esa condición.
- Como condiciónes están las típicas, >=, =, etc..., pero también LIKE, para comparar texto. La utilidad es que permite ignorar mayúsculas, minúsculas y usar comodines. Un ... WHERE nom_poblacion LIKE 'b%' devuelve los nombres de poblaciones que empiecen por b.
- También muy útil y usando LIMIT: podemos hacer que nos muestre los 100 primeros resultados a partir del resultado número 500. ¿Para que sirve esto?, pués para usar paginación de modo que la consulta sea más rápida y sin variar las condiciones de búsqueda, ya que solo cambiamos que filas son devueltas. Podemos usar un LIMIT %v_offset,20, asumiendo que entran 20 resultados en pantalla.

Por ej, en tasker:
- Pongamos %num_pagina vale 3
- Pulsas botón de siguiente página -> asignas %num_pagina a %num_pagina+1
- Asignas %v_offset a %num_pagina*20
- ... LIMIT %v_offset,20 (te mostraría 20 filas a partir del 80º resultado)
, y de este modo ir cambiando de página en página cambiendo el número de página.



Otra cosa importante si queremos acceder a datos que procedan de más de una tabla, como puede ser el caso de extraer info de una app externa. Pongamos que hay dos tablas:

tabla POBLACION. Campos: ID, NOMPOB
1,Población A
2,Población B
3,Población C

tabla USUARIOS. Campos: ID, NOMBRE, IDPOB
1,Nombre A,2
2,Nombre B,3
3,Nombre C,1

Para la consulta que permita extraer los nombres junto con su población sería:
SELECT usuarios.nombre, poblacion.nompob
FROM poblacion, usuarios
WHERE poblacion.id=usuarios.idpob
o bien
SELECT usuarios.nombre, poblacion.nompob
FROM poblacion INNER JOIN usuarios ON poblacion.id=usuarios.idpob

Nota: las mayúsculas no son importantes pero puede hacer más legible el texto al permitir diferenciar más rapidamente instrucciones SQLITE de nombres de campos y tablas.

Lo de usar IDs es algo muy típico. Cuando busquemos algún dato en una tabla y no lo encontremos de primeras es muy probable que lo que tenga sea algún ID y haya que enlazar la tabla con otra para ello.


Para una tabla simple se reduce a la configuración inicial y la interacción con Tasker. Realmente la complicación se encuentra sobre todo cuando se trata de optimizar rendimiento en bases de datos muy pesadas y con muchas tablas y/o consultas muy complejas pero para archivos pequeños con tablas sueltas no relacionadas directamente entre sí no hace falta mirar practicamente nada. Salvo alguna duda puntual, más allá que lo que he puesto ya sería mirarse algún tutorial de SQLITE.

Actualizar uno o más campos de una tabla:
UPDATE tabla SET campo1=5, campo2=(campo2*campo3)+5
WHERE campo_id=133

Eliminar una fila de la tabla (o más si cambiamos la condición):
DELETE FROM tabla WHERE _id=5
Borrar todas las filas:
DELETE FROM tabla WHERE 1=1
DELETE FROM tabla

[Caravantes]

 Cita: Originalmente Escrito por danko9696

Mini-tutorial sqlite

Excelente explicación. Muchísimas gracias.
Creo que con este post y el de Cace, e asunto ya queda muy bien orientado para todo el mundo, y además es un buen sitio para responde con otras consultas y dudas sobre SQLite.

Reitero el enlace al post de Cace:

[Tutorial] CONSULTA DE UNA TABLA DE DATOS DE EXCEL EN TASKER (nivel básico)
http://www.htcmania.com/showthread.php?t=1152543

[cace0353]

Problema:
Tengo una BD y quiero editar los campos de un registro.

En una escena y mediante "Consulta de variable" cambio los valores que me interesan…
¿Cual seria el comando para substituir los campos originales por los editados al finalizar la edición?

¿Podria hacerlo con "INSERT INTO Tabla_1 (NOM, CODI, ARTICLE,…) VALUES (‘%campo1','%campo2','%campo3'…) " o esto me crearia un nuevo registro?

[danko9696]

 Cita: Originalmente Escrito por cace0353

Problema:
Tengo una BD y quiero editar los campos de un registro.

En una escena y mediante "Consulta de variable" cambio los valores que me interesan…
¿Cual seria el comando para substituir los campos originales por los editados al finalizar la edición?

¿Podria hacerlo con "INSERT INTO Tabla_1 (NOM, CODI, ARTICLE,…) VALUES (‘%campo1','%campo2','%campo3'…) " o esto me crearia un nuevo registro?

El INSERT añade registros, para modificar habría que usar UPDATE. He modificado el post original para indicar la sintaxis.

[cace0353]

Gracias por tu respuesta.

¿Y con que comando puedo obtener el "numero de campo_id" o "row_id" del Registro abierto para editarlo?

¿Podrias escribir la sintaxis?

Enviat des del meu SM-T550 usant Tapatalk

[danko9696]

 Cita: Originalmente Escrito por cace0353

Gracias por tu respuesta.

¿Y con que comando puedo obtener el "numero de campo_id" o "row_id" del Registro abierto para editarlo?

¿Podrias escribir la sintaxis?

Enviat des del meu SM-T550 usant Tapatalk

Eso es sólo un ejemplo, depende de la tabla y es necesario examinarla para averiguar cual de los campos es el identificador. De hecho no es imprescindible que exista tal identificador (aunque sea bastante frecuente), y que de haberlo este sea numérico o que sólo haya un identificador único (puede haber varios). Además puede estar formado por la combinación de varios campos.

Aparte, la condición puede afectar a cualquier número de filas cambiando la condición o todas si la quitas. Por ej. puedes usar un UPDATE tabla SET campo3 = campo3 * 100, por ejemplo, para multiplicar el campo3 de todas las filas por 100.

[cace0353]

 Cita: Originalmente Escrito por danko9696

Eso es sólo un ejemplo, depende de la tabla y es necesario examinarla para averiguar cual de los campos es el identificador. De hecho no es imprescindible que exista tal identificador (aunque sea bastante frecuente), y que de haberlo este sea numérico o que sólo haya un identificador único (puede haber varios). Además puede estar formado por la combinación de varios campos.

Bueno, tema resuelto.

Explico el proceso seguido para ayudar a solventar problemas a otros taskeros que estén como yo...:

En el ordenador uso para crear la base de datos Sqlite Manager (complemento del Firefox) y me aparece, al principio de la tabla, una columna que se genera automáticamente y que tiene el nombre "rowid"

Para extraer el valor de "rowid" del registro que estoy editando utilizo, en la misma tarea asociada al boton de la escena para editar la tabla y siendo %var3 el valor del dato en la columna 3 (COL_3), el sigüiente código:

sqlite3 nombre y path de la base de datos "SELECT ROWID FROM nombre_tabla WHERE COL_3 LIKE '%var3'"
Guardar resultado en: %posicion

Una vez obtenido en %posicion el número de línea edito el registro con la sigüiente acción:

sqlite3 nombre y path de la base de datos "UPDATE nombre_tabla SET COL_1 = '%var1',COL_2 = '%var2',COL_3 = '%var3', COL_4 = '%var4 WHERE ROWID LIKE '%posicion' "

Esto funciona si no edito el campo que está en la columna COL_3. Si lo hago ocurre que %posicion entonces no toma valor (simplemente porque aún no existe el registro en la BD) y me devuelve %posicion y, por tanto, no se cambian los valores de este registro...

Interpreto que al cambiar el valor %var3 que utilizo para obtener %posicion entonces Sqlite3 no encuentra este valor en la tabla y la variable %posicion queda sin asignar.

Solución: Al abrir la escena de consulta de la ficha obtengo el valor de %posicio mediante el primer comando que he escrito, tanto si pienso editar el registro como si no... En este caso, como %posicio ya está asignada, el comando UPDATE me funciona correctamente.

Saludos a todos!

[Rsc]

Buenas, sabe alguien si hay alguna forma de establecer un ancho de columna en las tablas de SQL? El caso es que muestro el resultado en un elemento menú de una escena, y por estética me gustaría que quedase alineado.

Un saludo

[Caravantes]

 Cita: Originalmente Escrito por Rsc

hay alguna forma de establecer un ancho de columna en las tablas de SQL?

Creo que no. Me parece que, por definición, una tabla SQL contiene información sin formato de ningún tipo. O sea que tiene columnas "conceptuales" pero no puede tener una anchura establecida, ni un tipo/tamaño de letra, ni otras cuestiones de formato. Dicho de otra forma: en cada fila, el tercer dato es el que queda asignado a la "tercera columna", pero los datos pueden tener una anchura variable y por ese motivo podría ocurrir que el tercer dato de una fila quedase visualmente debajo del quinto dato de la fila precedente, como en el ejemplo siguiente.

uno; dos; tres; cuatro; cinco; seis
111111111;222222222;33333;4444444;555555;666666

Si quieres que los datos queden alineados tendrás que gestionarlo desde la escena, ya sea mediante las propiedades de la casilla o bien estableciendo un tipo de letra de ancho fijo, o bien añadiendo caracteres de relleno al principio, etc.

[Rsc]

Gracias @Caravantes, ese ejemplo que has puesto, es exactamente lo que me ocurre.

El problema es que desde la escena no encuentro la manera, ya que relleno las etiquetas del elemento menú con un array, por lo tanto me es imposible. Porque todo lo que intente, se aplica para todas las arrays... no se si se me escapa alguna forma de hacerlo.

[danko9696]

 Cita: Originalmente Escrito por Rsc

Buenas, sabe alguien si hay alguna forma de establecer un ancho de columna en las tablas de SQL? El caso es que muestro el resultado en un elemento menú de una escena, y por estética me gustaría que quedase alineado.

Un saludo

No son columnas sino campos, que sirven para almacenar datos que pueden ser usados de multitud de maneras. No hay que confundir algo como sqlite con, por ejemplo, excel o access, que además de base de datos incluyen un interface gráfico para manejarlas donde los campos suelen mostrarse como columnas y hay opciones para ajustar alineamiento, tamaño de fuente, etc...

 Cita: Originalmente Escrito por Rsc

Gracias @Caravantes, ese ejemplo que has puesto, es exactamente lo que me ocurre.

El problema es que desde la escena no encuentro la manera, ya que relleno las etiquetas del elemento menú con un array, por lo tanto me es imposible. Porque todo lo que intente, se aplica para todas las arrays... no se si se me escapa alguna forma de hacerlo.

¿Cuantas columnas son a mostrar?

[Rsc]

Muestro 3 columnas, tal que así;

GOLES PLAYER USUARIO
15 MESSI RAUL
13 LUIS SUAREZ KIKO

Donde pongo, goles player etc, son elementos de texto. Y gracias por la aclaración.

[danko9696]

 Cita: Originalmente Escrito por Rsc

Muestro 3 columnas, tal que así;

GOLES PLAYER USUARIO
15 MESSI RAUL
13 LUIS SUAREZ KIKO

Donde pongo, goles player etc, son elementos de texto. Y gracias por la aclaración.

Lo decía porque podrías formatear los campos para simular columnas usando después una fuente monospace y rellenando con espacios. Todo en la propia consulta, aunque construirla sería algo engorroso.

Algo como
SELECT SUBSTR(player,1,15) || substr("@@@@@@@@@@@@@@@",1,15-LEN(SUBSTR(player,1,15))) || "@@@" ||
SUBSTR("@@@",1,3-LEN(SUBSTR(goles,1,3))) || SUBSTR(goles,1,3) || "@@@" ||
SUBSTR(usuario,1,10) || substr("@@@@@@@@@@",1,10-LEN(SUBSTR(usuario,1,10)))
AS fila_formateada
FROM tabla

Puede que haya algún error pero esa sería la idea básica: jugar con las instrucciones de manejo de cadenas de sqlite para formatear el texto de modo que al final tengamos un solo campo de salida ya formateado (también se podría hacer con varios pero ya puestos mejor así).

Una cosa: he visto que los espacios no se suben correctamente, así que los he cambiado por una @, o sea, debes cambiar las @ por espacios en el código que uses. Lo bueno de este sistema es que lo puedes probar directamente en el editor, sin usar Tasker para nada hasta que la consulta funcione correctamente.

[Rsc]

Esto me va a dar horas de entreteniendo, cuando lo consiga hacer, compartiré el resultado, pero tengo dudas de si podré hacerlo desde mi nuevo teléfono sin acceso root, parece que por lo pronto solo me deja usar SQL, desde la nueva acción SQL Query , y supongo que este código es para introducirlo en ejecutar consola.

[danko9696]

 Cita: Originalmente Escrito por Rsc

Esto me va a dar horas de entreteniendo, cuando lo consiga hacer, compartiré el resultado, pero tengo dudas de si podré hacerlo desde mi nuevo teléfono sin acceso root, parece que por lo pronto solo me deja usar SQL, desde la nueva acción SQL Query , y supongo que este código es para introducirlo en ejecutar consola.

Este código es sqlite, ahí tienes el SELECT, el FROM, puedes añadir el WHERE y si quieres, más campos. Si te funcionan consultas "normales" esta también debería hacerlo. Realmente no tiene nada de especial. Aunque no sé bien como va en la última versión con sqlite integrado (que no tengo instalada) ni sus limitaciones, si permite consultas RAW (sqlite puro), como creo recordar que alguién indicó en el otro hilo, carecer de root no debería influir. Y puede que también funcione incluso sin utilizar el modo RAW, simplemente indicando todo el tocho con SUBSTRs (o sea, desde justo después del SELECT hasta justo antes del AS) como campo.

Por cierto, que antes el LEN creo que es un LENGTH. Deberías probarla con sqlitedebugger antes de nada.

[Rsc]

Por lo pronto no consigo hacer consultas, pero si desde el modo RAW puedo insertar datos, supongo que también debería de poder hacer consultas, seguiré probando a ver si lo consigo.

[Rsc]

@danko9696 he conseguido hacer funcionar el código que indicaste, y es justo lo que quería. El problema es que se ve, que los espacios no ocupan igual que las letras y no se termina de quedar bien cuadrado.

Aún así, no comprendo la sintaxis del código, prácticamente llevo esta semana mirando un poco más SQLite y si me sacas de lo más básico... Espero que si logro comprenderlo, pueda hacer modificaciones para terminar de cuadrarlo.

Un saludo

[danko9696]

 Cita: Originalmente Escrito por Rsc

@danko9696 he conseguido hacer funcionar el código que indicaste, y es justo lo que quería. El problema es que se ve, que los espacios no ocupan igual que las letras y no se termina de quedar bien cuadrado.

Aún así, prácticamente no comprendo la sintaxis del código, prácticamente llevo esta ssemana mirando un poco más SQLite y si me sacas de lo más básico... Espero que si logro comprenderlo, pueda hacer modificaciones para terminar de cuadrarlo.

Un saludo

Si usas una fuente monospace todos los caracteres deben de ocupar lo mismo, incluidos espacios, y también lo mismo una ´i´ que una ´m´, que en otros casos tendrían un ancho muy distinto. Esa es la razón de usar este tipo de fuentes. Si la fuente que usas es efectivamente monospace entonces hace como esta web y el problema es distinto: cuando hay más de un espacio seguido quita todos menos uno.

Luego, el codigo es un poco engorroso visto todo junto pero si vas por partes es bastante sencillo teniendo a mano lo que hace cada instrucción (fácil de encontrar con google):
Pon que player = "Maradona". Su longitud es 8, así que SUBSTR(player,1,15) = "Maradona". No basta usar player a secas porque hay que prever que la cadena podría ocupar más de 15 caracteres, así que usar SUBSTR te asegura que coges 15 como máximo, pudiendo ser menos.

Para "Maradona" faltan 7 espacios a la derecha para una columa de 15, o concatenarle otra cadena con 15 espacios menos lo que ocupe "Maradona" (15-8), o sea SUBSTR("@@@@@@@@@@@@@@@",1,15-8), pero ese 8 no lo puedes poner directamente, porque depende del player concreto, así lo sacas del resultado de la cadena anterior: LENGTH(SUBSTR(player,1,15)). Con esto ya tienes la primera columna, metes tres espacios después para separar con la siguiente columna y listo. Luego el resto de columnas igual.

Repito de nuevo que debes de tener una web de referencia a mano con las instrucciones. Yo tampoco lo hago de memoria. Puede que me suene que debe de haber alguna instrucción para lo que yo quiero pero si no la he utilizado recientemente tampoco me voy a acordar de su sintaxis. También lo mejor es que lo hagas en PC con un editor de texto, donde puedes usar ratón y sobre todo teclado, y luego lo pases al móvil.

[Rsc]

Gracias de nuevo, con esta nueva explicación ya me queda más claro como funciona el código.

 Cita: Originalmente Escrito por danko9696

Si usas una fuente monospace todos los caracteres deben de ocupar lo mismo

Cuando lo mencionaste en el otro mensaje, pensaba que "fuente menospace" era algún término relacionado con el código SQL, en vez de un tipo de letra. Supongo que eso si que solucionará el problema. No obstante, no me suena que se pueda añadir un tipo de fuente a un app creada con Tasker, por lo que entiendo que cuando los demás se la instalen en sus respectivos teléfonos se seguiría viendo descuadrado. Buscaré la manera y si encuentro la forma de hacerlo lo compartiré por aquí.

Solo una cosa más; cuando trato de buscar información sobre sqlite para implementarlo en Tasker, me cuesta extrapolar el código a Tasker, hay alguna página que recomiendes en la que creas que me puede ser más fácil buscar información? Un saludo.

[danko9696]

 Cita: Originalmente Escrito por Rsc

Cuando lo mencionaste en el otro mensaje, pensaba que "fuente menospace" era algún término relacionado con el código SQL, en vez de un tipo de letra. Supongo que eso si que solucionará el problema. No obstante, no me suena que se pueda añadir un tipo de fuente a un app creada con Tasker, por lo que entiendo que cuando los demás se la instalen en sus respectivos teléfonos se seguiría viendo descuadrado. Buscaré la manera y si encuentro la forma de hacerlo lo compartiré por aquí.

Las fuentes monospace son un tipo de tipos de letra (o un tipo de fuentes). No sé que usas exactamente para mostrar los resultados pero las veces que he utilizado escenas (más bien poco) nunca he tenido problemas para elegir una. En el peor de los casos necesitarías copiar el mismo archivo de fuente a todos los móviles que vayan a usar la app, no es necesario "instalar" nada.

 Cita: Originalmente Escrito por Rsc

Solo una cosa más; cuando trato de buscar información sobre sqlite para implementarlo en Tasker, me cuesta extrapolar el código a Tasker, hay alguna página que recomiendes en la que creas que me puede ser más fácil buscar información? Un saludo.

Realmente no tengo una página de referencia a la que siempre recurra. Simplemente cuando voy a hacer algo con sqlite tiro del buscador, por ejemplo http://lmgtfy.com/?q=sqlite+substr.