sqlite: adding a column and verify its added

Adding a column to an existing sqlite database.... and verify your change... In my case adding an 'ACTIVE' flag to the map table as a boolean where the default value for the column is 1 (TRUE) First copy my original db to a working copy and altering the work copy (

x.db

).

Pro:bin project$ cp vt.db x.db
Pro:bin project$ sqlite3 x.db 'alter table territory_map add column ACTIVE BOOLEAN DEFAULT 1'

Now validating the structures of the original and copied db by listing them from the sqlite_master

Pro:bin project$ sqlite3 vt.db 'select * from sqlite_master where type="table" and name="TERRITORY_MAP" ' > c1
Pro:bin project$ sqlite3 x.db 'select * from sqlite_master where type="table" and name="TERRITORY_MAP" ' > c2

Verify the columns existence and its default value.

Pro:bin project$ diff c1 c2
21c21
<     UPDATED_BY TEXT DEFAULT 'IMPORT' NULL,
---
>     UPDATED_BY TEXT DEFAULT 'IMPORT' NULL, ACTIVE BOOLEAN DEFAULT 1,

Pro:bin project$ sqlite3 -header x.db 'select map_id, abbrev, category, active from territory_map' | head
MAP_ID|ABBREV|CATEGORY|ACTIVE
1|fnny|REG|1
2|fnny|SC|1
3|clny|REG|1
4|clny|SC|1
5|chvt|SC|1
6|chvt|REG|1

SQLite: inserts or updates on counts with triggers

If a record for the summary does not exist we need to insert a record with zero count and then up the count with 1. How to do this.... here the SQLite if then else version.


DROP TRIGGER IF EXISTS "trigger_year_action_summary" ;
CREATE TRIGGER "trigger_year_action_summary" AFTER INSERT ON "AUDIT_RECORD" FOR EACH ROW
BEGIN

INSERT INTO SUMMARIES ('VALUE','YEAR','MONTH','ENVIRONMENT','ACTION','USER','SUCCESS','HOUR_OF_DAY')
SELECT 0,new.YEAR,new.MONTH,new.ENVIRONMENT,new.ACTION,null,null,null
WHERE NOT EXISTS (
SELECT 1 FROM SUMMARIES WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null

);

UPDATE SUMMARIES
SET value = value + 1
WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null
;

END;


SQLite: pivioting summary with sub selects and triggers

Accounting and summaries can be a drag in SQL. This is just one reminder of such a problem...

One audit table and one summary table with counts counts counts....


DROP TABLE IF EXISTS SUMMARIES;
CREATE TABLE "SUMMARIES" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"value" INT,
"YEAR" INTEGER DEFAULT NULL,
"MONTH" INTEGER DEFAULT NULL,
"ACTION" TEXT DEFAULT NULL,
"ENVIRONMENT" TEXT DEFAULT NULL,
"USER" TEXT DEFAULT NULL,
"SUCCESS" INT DEFAULT NULL,
"HOUR_OF_DAY" INTEGER DEFAULT NULL,
"DAY_OF_WEEK" TEXT DEFAULT NULL,
CONSTRAINT "ALL" UNIQUE ("YEAR", "MONTH", "ACTION", "ENVIRONMENT", "USER", "SUCCESS", "HOUR_OF_DAY", "DAY_OF_WEEK") ON CONFLICT ROLLBACK
);

-- ----------------------------
-- Table structure for "AUDIT_RECORD"
-- ----------------------------
DROP TABLE IF EXISTS "AUDIT_RECORD";
CREATE TABLE "AUDIT_RECORD" (
"ENVIRONMENT" TEXT(8) NOT NULL DEFAULT '-',
"USER" TEXT NOT NULL DEFAULT '-',
"TIMESTAMP" TEXT NOT NULL,
"CREATED_DT" REAL,
"SUCCESS" TEXT,
"PROJECT" TEXT NOT NULL,
"LABEL" TEXT,
"YEAR" INTEGER NOT NULL,
"MONTH" INTEGER NOT NULL,
"DAY" INTEGER NOT NULL,
"DAY_OF_MONTH" INTEGER NOT NULL,
"OWNER" TEXT,
"HOUR_OF_DAY" INTEGER,
"ACTION" TEXT NOT NULL,
PRIMARY KEY ("USER", "TIMESTAMP", "ENVIRONMENT")
);




Triggers for each of the counts as an after insert

CREATE TRIGGER "trigger_year_action_summary" AFTER INSERT ON "AUDIT_RECORD" FOR EACH ROW
BEGIN

INSERT INTO SUMMARIES ('VALUE','YEAR','MONTH','ENVIRONMENT','ACTION','USER','SUCCESS','HOUR_OF_DAY')
SELECT 0,new.YEAR,new.MONTH,new.ENVIRONMENT,new.ACTION,null,null,null
WHERE NOT EXISTS (
SELECT 1 FROM SUMMARIES WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null

);

UPDATE SUMMARIES
SET value = value + 1
WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null
;




select
c.id
,c.year as 'YEAR'
, c.environment as 'ENV'
, c.month as 'MONTH'
, c.value as 'total'
, x.value as 'builds'
, y.value as 'deploys'
, xs.value as 'bld_suc'
, xf.cvalue as 'bld_fail'
, ys.value as 'dly_suc'
, yf.cvalue as 'dly_fail'
from
summaries c
,(select year, month, value, environment from summaries where action = 'builds' and success is null ) x
,(select year, month, value, environment from summaries where action = 'deploy' and success is null ) y
,(select year, month, value, environment from summaries where action = 'builds' and success =0) xs
,(select year, month, value, environment, sum(value) as "cvalue" from summaries where action = 'builds' and success != 0 group by year, month, environment) xf
,(select year, month, value, environment from summaries where action = 'deploy' and success =0) ys
,(select year, month, value, environment, sum(value) as "cvalue" from summaries where action = 'deploy' and success != 0 group by year, month, environment) yf
where
c.year = 2010
and c.environment = 'PRD'
and c.action is null
and x.year = c.year
and x.environment = c.environment
and x.month = c.month
and y.year = c.year
and y.environment = c.environment
and y.month = c.month
and xs.year = c.year
and xs.environment = c.environment
and xs.month = c.month
and xf.year = c.year
and xf.environment = c.environment
and xf.month = c.month
and yf.year = c.year
and yf.environment = c.environment
and yf.month = c.month
and ys.year = c.year
and ys.environment = c.environment
and ys.month = c.month


SQLite Hibernate: Primary Keys and Foreign Keys on generated values

Here a little hint on defining the correct mapping for generated values and their relationships.

If the SQLiteDialect.class is used for the Hibernate dialect and the identity column string returns "integer".

When I generated the database table with

hibernate.hbm2ddl.auto=create

and then read it back with

hibernate.hbm2ddl.auto=validate

I ended up with corrupt foreign key relationships.


Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in class path resource [orm-layer.xml]: Invocation of init method failed; nested exception is org.hibernate.HibernateException: Wrong column type in APPLICATION for column APPLICATION_ID. Found: integer, expected: bigint
at Caused by: org.hibernate.HibernateException: Wrong column type in APPLICATION for column APPLICATION_ID. Found: integer, expected: bigint
at org.hibernate.mapping.Table.validateColumns(Table.java:284)
at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1130)
at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:139)
at org.hibernate.impl.SessionFactoryImpl.(SessionFactoryImpl.java:359)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1341)
at


Here the tip of my configuration: just make sure your generated values are "integer" by using the columnDefinition and both "create" and "validate" settings for hibernate.hbm2ddl.auto.

SQLite Primary Keys and Hibernate

 SQLite is a modern, compact and stable database. Using Hibernate on top of SQLite proved to be a challenge with Primary Keys.

Here the reason why the autoincrement does not work... even if we would specify it the table creation would mess you up with two primary key definitions. See the next pictures why and how...

So you need the following SQLiteDialect that is being found accross the web... here a copy.

/**
 * 
 */
package org.territory;

import java.sql.Types;

import org.hibernate.Hibernate;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;

/**
 * @author 751818
 * 
 */
public class SQLiteDialect extends Dialect {

 /**
  * 
  */
 public SQLiteDialect() {
  super();
  registerColumnType(Types.BIT, "integer");
  registerColumnType(Types.TINYINT, "tinyint");
  registerColumnType(Types.SMALLINT, "smallint");
  registerColumnType(Types.INTEGER, "integer");
  registerColumnType(Types.BIGINT, "bigint");
  registerColumnType(Types.FLOAT, "float");
  registerColumnType(Types.REAL, "real");
  registerColumnType(Types.DOUBLE, "double");
  registerColumnType(Types.NUMERIC, "numeric");
  registerColumnType(Types.DECIMAL, "decimal");
  registerColumnType(Types.CHAR, "char");
  registerColumnType(Types.VARCHAR, "varchar");
  registerColumnType(Types.LONGVARCHAR, "longvarchar");
  registerColumnType(Types.DATE, "date");
  registerColumnType(Types.TIME, "time");
  registerColumnType(Types.TIMESTAMP, "timestamp");
  registerColumnType(Types.BINARY, "blob");
  registerColumnType(Types.VARBINARY, "blob");
  registerColumnType(Types.LONGVARBINARY, "blob");
  // registerColumnType(Types.NULL, "null");
  registerColumnType(Types.BLOB, "blob");
  registerColumnType(Types.CLOB, "clob");
  registerColumnType(Types.BOOLEAN, "integer");

  registerFunction("concat", new VarArgsSQLFunction(Hibernate.STRING, "",
    "||", ""));
  registerFunction("mod", new SQLFunctionTemplate(Hibernate.INTEGER,
    "?1 % ?2"));
  registerFunction("substr", new StandardSQLFunction("substr",
    Hibernate.STRING));
  registerFunction("substring", new StandardSQLFunction("substr",
    Hibernate.STRING));
 }

 public boolean supportsIdentityColumns() {
  return true;
 }

 /*
  * public boolean supportsInsertSelectIdentity() { return true; // As
  * specify in NHibernate dialect }
  */

 public boolean hasDataTypeInIdentityColumn() {
  return false; // As specify in NHibernate dialect
 }

 /*
  * public String appendIdentitySelectToInsert(String insertString) { return
  * new StringBuffer(insertString.length()+30). // As specify in NHibernate
  * dialect append(insertString).
  * append("; ").append(getIdentitySelectString()). toString(); }
  */

 public String getIdentityColumnString() {
  return "INTEGER";
  // return "integer";
 }

 public String getIdentitySelectString() {
  return "select last_insert_rowid()";
 }

 public boolean supportsLimit() {
  return true;
 }

 public String getLimitString(String query, boolean hasOffset) {
  return new StringBuffer(query.length() + 20).append(query).append(
    hasOffset ? " limit ? offset ?" : " limit ?").toString();
 }

 public boolean supportsTemporaryTables() {
  return true;
 }

 public String getCreateTemporaryTableString() {
  return "create temporary table if not exists";
 }

 public boolean dropTemporaryTableAfterUse() {
  return false;
 }

 public boolean supportsCurrentTimestampSelection() {
  return true;
 }

 public boolean isCurrentTimestampSelectStringCallable() {
  return false;
 }

 public String getCurrentTimestampSelectString() {
  return "select current_timestamp";
 }

 public boolean supportsUnionAll() {
  return true;
 }

 public boolean hasAlterTable() {
  return false; // As specify in NHibernate dialect
 }

 public boolean dropConstraints() {
  return false;
 }

 public String getAddColumnString() {
  return "add column";
 }

 public String getForUpdateString() {
  return "";
 }

 public boolean supportsOuterJoinForUpdate() {
  return false;
 }

 public String getDropForeignKeyString() {
  throw new UnsupportedOperationException(
    "No drop foreign key syntax supported by SQLiteDialect");
 }

 public String getAddForeignKeyConstraintString(String constraintName,
   String[] foreignKey, String referencedTable, String[] primaryKey,
   boolean referencesPrimaryKey) {
  throw new UnsupportedOperationException(
    "No add foreign key syntax supported by SQLiteDialect");
 }

 public String getAddPrimaryKeyConstraintString(String constraintName) {
  throw new UnsupportedOperationException(
    "No add primary key syntax supported by SQLiteDialect");
 }

 public boolean supportsIfExistsBeforeTableName() {
  return true;
 }

 public boolean supportsCascadeDelete() {
  return false;
 }

 /*
  * (non-Javadoc)
  * 
  * @see org.hibernate.dialect.Dialect#getNativeIdentifierGeneratorClass()
  */
 @Override
 public Class getNativeIdentifierGeneratorClass() {
  // TODO Auto-generated method stub
  return super.getNativeIdentifierGeneratorClass();
 }
}

Next set the dialect of the Hibernate mapping...

Next set the sequence generate for the ID's that you want AUTOINCREMENT'ed.

And that should give you an incremented value of your id....