alexfu / sqlitequerybuilder Goto Github PK

From discussion #12, it was decided that a separate CREATE builder was needed.

I would like to be able to do the following:

.from("table1")
.leftOuterJoin("table2").on(...)
.leftOuterJoin("table3").on(...)
.where(...)

As far as I understand it is not possible to chain multiple left outer join statements after each other (or any other non-regular join). Any plans to add it?

For example I can do:-
... .where(clause).and(clause).build();
But I can't do :-
where(clause).and(clause).and(clause).build();

Hi again!

Still this is the best library to easily make sqlite queries, without having a complete orm :).
I still find some things missing, I hope you can add these features. I added the queries I want to build below the list items.

• COUNT statement

"SELECT COUNT(*) " + "FROM " + MOVIE_TABLE + " " + "WHERE " + MOVIE_ID_TRAKT + "=?";

• Chain ORDER BY to a JOIN statement

"SELECT " + WATCHLIST_TABLE + ".*, " + "CASE WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_MOVIE + "' " + "THEN " + MOVIE_TABLE + "." + MOVIE_TITLE + " " + "ELSE " + SHOW_TABLE + "." + SHOW_TITLE + " " + "END AS watchlist_item_title, " + "CASE WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_SEASON + "' " + "THEN " + SEASON_TABLE + "." + SEASON_NUMBER + " " + "WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_EPISODE + "' " + "THEN " + EPISODE_TABLE + "." + EPISODE_SEASON + " " + "ELSE NULL " + "END AS season_number," + EPISODE_TABLE + "." + EPISODE_NUMBER + " AS episode_number " + "FROM " + WATCHLIST_TABLE + " " + "LEFT JOIN " + MOVIE_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_MOVIE_ID + "=" + MOVIE_TABLE + "." + MOVIE_ID_TRAKT + " " + "LEFT JOIN " + SHOW_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_SHOW_ID + "=" + SHOW_TABLE + "." + SHOW_ID_TRAKT + " " + "LEFT JOIN " + SEASON_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_SEASON_ID + "=" + SEASON_TABLE + "." + SEASON_ID_TRAKT + " " + "LEFT JOIN " + EPISODE_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_EPISODE_ID + "=" + EPISODE_TABLE + "." + EPISODE_ID_TRAKT + " " + "ORDER BY " + "CASE WHEN LOWER(SUBSTR(watchlist_item_title,1,4)) = 'the ' " + "THEN SUBSTR(watchlist_item_title,5) ELSE watchlist_item_title END ASC, " + "season_number ASC, episode_number ASC";

• Also I am kind of using prepared statements with '=?' but it would be nice, not to have to write this all the time. Though I would understand it if this is something that was not intended for the query builder.

Thanks in advance!

Add support for AUTOINCREMENT in ColumnConstraint.

if i do something like this
SQLiteQueryBuilder.create().table(Constants.TBL_RECENT).column(new Column("demo", ColumnType.TEXT)).column(new Column("demo1", ColumnType.INTEGER)).column(new Column("pk", ColumnType.INTEGER, ColumnConstraint.PRIMARY_KEY)).toString()
out put will be
CREATE TABLE recent(demo TEXT,demo1 INTEGER,pk INTEGER PRIMARY KEY)

but what if i want to do some thing like this
CREATE TABLE recent(demo TEXT,demo1 INTEGER,pk INTEGER PRIMARY KEY) AUTOINCREMENT

Hi,

I posted another issue #48 where you asked me to split my features in different issues, so this is the first one.

I wonder if you could find a way to easily make a select count statement like below :).

"SELECT COUNT(*) " + "FROM " + MOVIE_TABLE + " " + "WHERE " + MOVIE_ID_TRAKT + "=?";

Should be able to do something like....

String sql = SQLiteQueryBuilder
  .select("*")
  .from("users")
  .limit(10)
  .build()

Prevent silly mistakes by forcing users to only use valid method calls.

Is there also a lib for parsing results?

Would it be possible to create a feature to create foreign keys to other tables?

In each builder, we should have some sort of argument check, like this.

Errors should be caught and thrown as early as possible. If, for example, these checks were done right before the build() function was called, it would be extremely hard to debug since the stack trace would only point to the toString()/build() line and not where the actual error happened.

Currently statements are built by using a series of append operations on StringBuilder. This is not exactly ideal since it is very vulnerable to bugs and mistakes. Look into a better, safer method.

Basically:

• (natural) left outer join
• cross join

For the API we can use the same approach like JOOQ uses:

leftOuterJoin("secondtable")
naturalJoin("table")
naturalLeftOuterJoin("table")
crossJoin("secondtable")

I can create a PR for this if you want, i'll be happy to contribute.

Because it's the right thing to do.

Hi,

It would also be nice if I can mark a select query as a prepared statement so that I do not have to write "=?" everytime. I understand it though if this is a feature that is out of scope in this library.

We should consider changing some classes scope from public to package private.
This avoids exposing a lot of internal classes that aren't meant to be used outside of the API.

Some examples:

• JoinType
• StringUtils

The only troublesome part is that we need to move the tests accordingly to have access to the classes like .StringUtilsTest.java -> .utils.StringUtilsTest.java.

Add support for default values in create table statements.

Replace usage of JUnits assertion statements with AssertJ. AssertJ offers a more fluent and easy to understand API.

Instead of validating unit tests against a hard-coded string, use a real SQLite instance to run statements to validate them.

Adds "TEMP/TEMPORARY" as an optional clause to CREATE TABLE statement.

String sql = SQLiteQueryBuilder
  .createTable("myTable")
  .temp()
  .column(column)
  .toString();

Add "IF NOT EXISTS" as part of the CREATE TABLE statement as an optional condition. Please refer the link here.

is there any support for insert and Update statements?

Hi,

I am executing the very complex query at the end of this issue :P. The only problem with it is that I cannot chain multiple left joins. But also I cannot chain an order by to these join statements.

It would be nice if you could build this feature for me :).

"SELECT " + WATCHLIST_TABLE + ".*, " + "CASE WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_MOVIE + "' " + "THEN " + MOVIE_TABLE + "." + MOVIE_TITLE + " " + "ELSE " + SHOW_TABLE + "." + SHOW_TITLE + " " + "END AS watchlist_item_title, " + "CASE WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_SEASON + "' " + "THEN " + SEASON_TABLE + "." + SEASON_NUMBER + " " + "WHEN " + WATCHLIST_TABLE + "." + WATCHLIST_TYPE + "='" + WatchlistItem.TYPE_EPISODE + "' " + "THEN " + EPISODE_TABLE + "." + EPISODE_SEASON + " " + "ELSE NULL " + "END AS season_number," + EPISODE_TABLE + "." + EPISODE_NUMBER + " AS episode_number " + "FROM " + WATCHLIST_TABLE + " " + "LEFT JOIN " + MOVIE_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_MOVIE_ID + "=" + MOVIE_TABLE + "." + MOVIE_ID_TRAKT + " " + "LEFT JOIN " + SHOW_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_SHOW_ID + "=" + SHOW_TABLE + "." + SHOW_ID_TRAKT + " " + "LEFT JOIN " + SEASON_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_SEASON_ID + "=" + SEASON_TABLE + "." + SEASON_ID_TRAKT + " " + "LEFT JOIN " + EPISODE_TABLE + " " + "ON " + WATCHLIST_TABLE + "." + WATCHLIST_EPISODE_ID + "=" + EPISODE_TABLE + "." + EPISODE_ID_TRAKT + " " + "ORDER BY " + "CASE WHEN LOWER(SUBSTR(watchlist_item_title,1,4)) = 'the ' " + "THEN SUBSTR(watchlist_item_title,5) ELSE watchlist_item_title END ASC, " + "season_number ASC, episode_number ASC";

As more people contribute, we should all follow the same coding style in order to stay consistent.