gwenn/sqlite-dialect

Hibernate dialect for SQLite

DEPRECATED for Hibernate >= 6 See HHH-10668 and hibernate-community-dialects

Hibernate dialect for SQLite inspired from the NHibernate one.

First posted on 14 Mar 23:08 2008

sqlite-dialect's Issues

Wrong name of JdbcExceptionHelper

First of all thx for creating this file, after searching for a while I finally find this dialect.
But this line seems have something wrong. Please check it!
The name should be JdbcExceptionHelper, but, maybe I am wrong.

FLOAT and DOUBLE commented out in Dialect

Hi,

first of all: Thank you for this dialect.

I use this one:

<dependency>
            <groupId>com.github.gwenn</groupId>
            <artifactId>sqlite-dialect</artifactId>
            <version>0.1.0</version>
</dependency>

And when I look at the code of the SQLiteDialect class, I see:

public SQLiteDialect() {
		registerColumnType( Types.BIT, "boolean" );
		//registerColumnType(Types.FLOAT, "float");
		//registerColumnType(Types.DOUBLE, "double");
		registerColumnType( Types.DECIMAL, "decimal" );
		registerColumnType( Types.CHAR, "char" );
		registerColumnType( Types.LONGVARCHAR, "longvarchar" );
		registerColumnType( Types.TIMESTAMP, "datetime" );
		registerColumnType( Types.BINARY, "blob" );
		registerColumnType( Types.VARBINARY, "blob" );
		registerColumnType( Types.LONGVARBINARY, "blob" );

Plus, the comment says that this dialect is appropriate for Hibernate 3

/**
 * An SQL dialect for SQLite 3.
 */
public class SQLiteDialect extends Dialect {
	private final UniqueDelegate uniqueDelegate;

Could you tell why this is the case?

Thank you very much.
Kind regards.
Christian

Use LocalDate type

I know SQLite does not have a storage class set aside for storing dates and/or times and stores them as TEXT but Is it possible with the dialect to convert Sqlite TEXT dates to Java's LocalDate format?

If you have any insight on this, please let me know.
Thanks!

Update dependencies to support org.hibernate.orm/hibernate-core/6.1.5.Final

Actually it's not compatible with latest hibernate core, pls do a proper update :)

Some guide for hibernate and spring boot

Hi there,

is there a how to for using this with spring boot w/ hibernate?

greetings,
Antonio

automatically integrate into hibernate dialect detection

See https://github.com/pms1/sqlite-dialect

License?

Please add a license file for this project, thanks.

AutoIncrement failing

I have a table named collections in SQLite database with id as autoincrement and I’m using Spring Boot + Hibernate to connect to SQLite database. I’ve found that insert to collections table is failing with following error

What could be the reason for this?

Error Message

org.springframework.dao.DataIntegrityViolationException: A different object with the same identifier value
was already associated with the session : [com.product.cache.engine.entity.Collection#1624314]; nested 
exception is javax.persistence.EntityExistsException: A different object with the same identifier value was 
already associated with the session : [com.product.cache.engine.entity.Collection#1624314]

Entity class

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonIgnoreProperties(ignoreUnknown = true)
@ToString(doNotUseGetters = true)
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Builder
@Entity
@Table(name = TableConstants.COLLECTIONS)
public class Collection {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = TableConstants.ENTITY_ID)
  private Long id;

  @Column(name = TableConstants.PRODUCT_ID)
  @JsonProperty(value = TableConstants.PRODUCT_ID)
  private Long productId;

  @Column(name = TableConstants.COLLECTION_ID)
  @JsonProperty(value = TableConstants.COLLECTION_ID)
  private Long collectionId;

  @Column(name = TableConstants.BOT_REF)
  @JsonProperty(value = TableConstants.BOT_REF)
  private Integer botRef;

  @Column(name = TableConstants.SHOP_DOMAIN)
  @JsonProperty(value = TableConstants.SHOP_DOMAIN)
  private String shopDomain;

  @Column(name = TableConstants.DESCRIPTION)
  @JsonProperty(value = TableConstants.DESCRIPTION)
  private String description;

  @Column(name = TableConstants.HANDLE)
  @JsonProperty(value = TableConstants.HANDLE)
  private String handle;

  @Column(name = TableConstants.TITLE)
  @JsonProperty(value = TableConstants.TITLE)
  private String title;

  @Column(name = TableConstants.IMAGE_URL)
  @JsonProperty(value = TableConstants.IMAGE_URL)
  private String imageUrl;

  @Column(name = TableConstants.UPDATED_AT)
  @JsonProperty(value = TableConstants.UPDATED_AT)
  @JsonFormat(shape= JsonFormat.Shape.STRING, pattern="yyyy-MM-dd HH:mm:ss")
  private Timestamp updatedAt;
}

gwenn SQLite-dialect & xerial jdbc-driver versions

implementation("org.xerial:sqlite-jdbc:3.36.0.3")
implementation("com.github.gwenn:sqlite-dialect:0.1.0")

A few questions about this dialect implementation

Hi,

First of all, let me say thanks for working on and publishing this project, it has suited me well and I'm grateful for the time you spent on it and freeing it up. I'm starting to use this dialect implementation in more specific cases and started to study its implementation. I'm willing to contribute some fine-tuning, but I have some questions about it first. Please be so kind to answer them to me:

1. First of all, a general remark. I've dug around to find some similar projects and this is an overview of projects (active or not) I've found, for future reference:

• (this one) https://github.com/gwenn/sqlite-dialect
• https://github.com/ZsoltFabok/sqlite-dialect
• https://github.com/kemitix/sqlite-dialect
• https://github.com/autocompaste/AutoComPaste/blob/master/ACP/src/acp/store/dialect/SQLiteDialect.java

It looks like this project is the only one that sports a decent and recent implementation (eg. it's the only one with an IdentityColumnSupport implementation), so it's safe to say this SQLite dialect implementation is the one.

2. I'm not very familiar with Hibernate dialect implementations, but when comparing eg. the kemitix one with this one, it strikes me the list of column types that are registered is a lot thinner. Is there any reason for this?

3. It looks like the SQLiteDialectIdentityColumnSupport class is a bit rusty and stale. For instance, the hasDataTypeInIdentityColumn() method refers to the NHibernate dialect, but it doesn't really say why it returns false. Would it be possible to elaborate a bit on why these methods returns the values they do?

4. Also in that class, the getIdentityColumnString() method always returns integer, but eg. my implementation relies on LONGs (BIGINT) as row identifier. Is there any reason other identity types are not implemented here?

5. I encounter a bug when Hibernate wants to close the connection with the SQLite backend if it's not used for a while, saying that it couldn't close the connection properly. I don't have the full stacktrace here at hand anymore, but is this a known issue? I'll send in the stacktrace here.

Thanks a lot for answering!

b.

maven artifact release?

afaik, the only .jar available online is the one from kemitix's fork.

Unfortunately, I think that jar is not compatible with recent versions of Hibernate (5.0.2 here), and it fails with:

ClassNotFoundException: org.hibernate.exception.TemplatedViolatedConstraintNameExtracter

I've seen that this version has some commits for Hibernate 5 compatibility, and that TemplatedViolatedConstraintNameExtracter is mentioned. It'd be nice if this dialect would be packaged up.

UNIQUE not working

Hello! I'm using your great Dialect to integrate SQLite into Spring, but it seems that UNIQUE constraints do not work.

This is my code:

import javax.persistence.*;

@Entity
@Table(indexes = {
        @Index(name = "insert_time_index", columnList = "insert_time", unique = true),
        @Index(name = "start_time_index", columnList = "start_time"),
        @Index(name = "expired_time_index", columnList = "expired_time")
})
public class Tag {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    private String tag;
    private Long insert_time, start_time, expired_time;
    private Boolean done = false;
    private String next_token = "";

}

It's weird that if I remove the unique property everything works as expected.

I've also tried the uniqueConstraints = @UniqueConstraint(name = "insert_time_index", columnNames = {"insert_time"}) annotation, but with no success.

Am I doing something wrong?

The `import org.hibernate.util` cannot be resolved

org.hibernate.util.JDBCExceptionReporter is removed from Hibernate 4.3

Can't download jar

Hello. I can't donwload jar from direct link:
https://mvnrepository.com/artifact/org.hibernate.dialect/sqlite-dialect/0.1.0
https://scm.umiacs.umd.edu/maven/adapt/content/repositories/releases/org/hibernate/dialect/sqlite-dialect/0.1.0/sqlite-dialect-0.1.0.jar

Cannot alter an existing table

Hello. First of all, I want to say thank you for your efforts in maintaining SQLite dialect for Hibernate. I like this database and I find quite disappointing that Hibernate doesn't support SQLlite dialect officially (yet).

Recently I've found behavior when Hibernate can't alter an existing table in SQLite database (using your dialect) with Hibernate property setGenerateDdl = true.
For example I have Message entity with some fields. Hibernate is able to create database if it doesn't exist. But if I change Message POJO and add new field (for example private int intValue), Hibernate throws exception:
[WARN] GenerationTarget encountered exception accepting command : Unable to execute command [alter table Message add column intValue integer not null]
org.hibernate.tool.schema.spi.CommandAcceptanceException: Unable to execute command [alter table Message add column intValue integer not null]
Caused by: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (Cannot add a NOT NULL column with default value NULL)
(full trace: http://pastebin.com/kYKDyemD )

Only if I delete database file, Hibernate is able to work with the new field. I tried different data types, but the problem is the same - Hibernate can't alter tables in SQLite database.
Thanks.

Fix getCastTypeName

http://sqlite.org/lang_expr.html#castexpr

unique key Error.

JPA

@entity
@getter
@Setter
@NoArgsConstructor
public class MenuEntity implements Serializable {

@Id
@GeneratedValue()
private Long id;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="parent_id")
private MenuEntity parent;

private String name;

@Column( nullable = false )
private int listOrder;

@Column( nullable = false , **unique = true**)
private String menuCode;

@OneToMany(mappedBy = "menuEntity")
private List<MenuItemEntity> menuItemList = new ArrayList<>();

....
}

P6spy SQL query excute ->
alter table menu_entity add constraint UK_lk5c2lk5kyawukpw7lvotbivg unique (menu_code);

Error ->
Error executing DDL "alter table menu_entity add constraint UK_lk5c2lk5kyawukpw7lvotbivg unique (menu_code)"

I know that, SQLite does not support "alter table ~~~ add constraint " ,
SQlite query should be " CREATE INDEX ~~~~ "

Also, I tried @table ( constraint ~~ ) , It was same problem.

It may be different Sqlite from Other SQL query.
So, do you fix that problem on Dialect ?

Thanks in advance!

Support for Hibernate 6

Hello,

I was wondering if there are plans to support hibernate 6 as well and if so, can you say approximately when?

Thanks & Greetings
Steven

org.hibernate.MappingException: No Dialect mapping for JDBC type: 0

Hibernate sometimes can't distinguish the right JDBC type for a column type and asks the used dialect to handle java.sql.Types.NULL.

The solution is to let it handle Types.NULL:

public class SQLiteDialect extends Dialect {
    public SQLiteDialect() {
        super();
        ...
        registerColumnType(Types.NULL, "null");
        registerHibernateType(Types.NULL, "null");
    }
}

Reference, example and solution here:
thekasem/hibernate-sqlite#3

@Lob error

when @lob column value is null, find method error:
.m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.orm.jpa.JpaSystemException: Unable to access lob stream; nested exception is org.hibernate.HibernateException: Unable to access lob stream]
when the column has value, it's working.

commit transaction

in a Rest API I called:

<JpaRepository>.deleteAll();
<JpaRepository>.flush();
<JpaRepository>.saveAndFlush(<Entity>)

But that gives me: database is locked

That's because the transactions aren't committed to the database, until the end of the Rest API the deleteAll isn't executed on the database and the resource isn't released.