Skip to content

Views

Jump to bottom
Siim Kinks edited this page Mar 21, 2017 · 3 revisions

SqliteMagic supports creating SQLite views. A view is a SQL statement stored in the database with an associated name. One can look at a view as a virtual read-only table.

Generally, there are two ways to create a view in SqliteMagic - using interfaces or AutoValue objects (for SqliteMagic there is no difference between the two).

To define a view just follow these steps:

  • Annotate interface or AutoValue object with @View annotation. By default lower cased class/interface name, where camel case is replaced with "_" character, is used to name the resulting SQL view. This can be changed through annotation value property.

  • Define a view query. A view query must be defined in a static field of type CompiledSelect and annotated with @ViewQuery annotation. Each view must have one query. See Querying Objects for more details on how to create queries.

  • Create methods for all the selected column and annotate them with @ViewColumn annotations. @ViewColumn#value must be fully qualified column name or alias in query. For example, if in a query Author table column title is selected then annotation must be @ViewColumn("author.title") or if that same column is given alias foo then annotation must be @ViewColumn("foo").

Example:

SQL SqliteMagic 
      
      
      
      
      
      
      



CREATE VIEW IF NOT EXISTS book_view AS
   SELECT book.title,
          length(book.title)
              AS 'book_title_len',
          author.first_name || " " ||
              author.last_name
                  AS 'author_full_name'
     FROM book
LEFT JOIN author
          ON book.author=author._id











import static com.siimkinks.sqlitemagic.AuthorTable.AUTHOR;
import static com.siimkinks.sqlitemagic.BookTable.BOOK;
import static com.siimkinks.sqlitemagic.Select.concat;
import static com.siimkinks.sqlitemagic.Select.length;
import static com.siimkinks.sqlitemagic.Select.val;

@View
public interface BookView {
  @ViewQuery
  CompiledSelect QUERY = Select
    .columns(
        BOOK.TITLE,
        length(BOOK.TITLE).as("book_title_len")
        concat(AUTHOR.FIRST_NAME,
               val(" "),
               AUTHOR.LAST_NAME)
              .as("author_full_name"))
    .from(BOOK)
    .queryDeep()
    .compile();

  @ViewColumn("author_full_name")
  String authorName();

  @ViewColumn("book.title")
  String bookTitle();
  
  @ViewColumn("book_title_len")
  long bookTitleLength();
}

Joins, complex column, SQL functions, aliases, etc are all supported in views.

Query Views

Query views the same way as normal tables:

SQL SqliteMagic 
   SELECT *
     FROM BOOK_VIEW
    WHERE BOOK_VIEW.TITLE LIKE '%Foo%' 
          AND BOOK_VIEW.BOOK_TITLE_LEN > 5
 ORDER BY BOOK_VIEW.AUTHOR_FULL_NAME ASC
    LIMIT 2
   OFFSET 1;

import static com.siimkinks.sqlitemagic.BookViewTable.BOOK_VIEW;

List<BookView> views = Select
  .from(BOOK_VIEW)
  .where(BOOK_VIEW.BOOK_TITLE.like("%Foo%")
    .and(BOOK_VIEW.BOOK_TITLE_LEN.greaterThan(5)))
  .orderBy(BOOK_VIEW.AUTHOR_FULL_NAME.asc())
  .limit(2)
  .offset(1)
  .execute();

Clone this wiki locally