SELECTqueries (column projection, value predicates, sorting).
SELECTqueries (joins, aggregators, group by).
In short, you will learn why and how to use Lovefield in your next real-world data-rich application, and you will gain a clear understanding of the functionality Lovefield provides.
StockAnalyzerskeleton code (instructions on downloading available at “Step 0”).
Basic familiarity with the concepts of relational databases and the relational data model is required to follow the exercises of this codelab. Familiarity with SQL (Structured Query Language) is not required, but is beneficial, especially when it comes to advanced topics like indexing and query optimization.
The application user interface has four components:
App users can search for “Stocks” or “Sectors” and specify a time window. The result graph updates as users examine different stocks/sectors/time-windows. Hovering over or touching the graph exposes additional information about a specific date.
All Lovefield-related code resides in lovefield_service.js, within a class called
LovefieldService. Everything else is already implemented and is not the focus of this codelab.
All HTML code.
All CSS code.
Logic for drawing graphs.
Logic related to fetching real-world stock market data from the network, using the Google Finance API.
AngularJS controller for interacting with all UI components related to building database queries.
Class used by the rest of the application to make calls to Lovefield's API.
This class is partially implemented. You will implement the remaining parts in this codelab.
The application calls the
LovefieldService to fulfill its data-related needs. The following methods are unimplemented or only partially implemented. Each of them demonstrates a different part of Lovefield’s API, and you will implement them in this codelab.
Builds the database schema.
Inserts data fetched from the network to the database.
Retrieves the list of stocks for the “Stocks” search mode.
Retrieves the list of sectors for the “Sectors” search mode.
Retrieves a given company’s performance, within a given time window.
Retrieves a given industry sector’s performance, within a given time window.
Registers an observer for the “stock closing prices” query.
Registers an observer for the “sector closing prices” query.
One of Lovefield’s nicest features is its SQL-like API. Developers who are already familiar with SQL will find learning Lovefield APIs straightforward. To make it even easier, we're including the equivalent SQL queries for all steps in this codelab.
Download the zip file from Github.
python -m SimpleHTTPServer.
Start the codelab from step1 below by adding your code inside
src/step1/lovefield_service.js. You can find the starting code for each step in the respective sub-folder,
src/step2 … etc.
In this step you are going to define the database schema used for holding the data for the Stock Analyzer application. The database schema describes the structure of the relational database. More specifically it describes the following:
The API entry point for defining the “schema” is
lf.schema.create(), which in our case contains the two tables shown below.
This table stores historical data about stocks. Each record refers to a given stock on a given day. For example:
No primary key exists on the
This table stores information about each company. For example:
Note that the “
Stock” field is the primary key of the
StockInfo table, which means that it is also unique.
buildSchema() method on
LovefieldService (search for
LovefieldService.prototype.buildSchema_) inside the
lovefield_service.js file. If you get stuck, you can take a look at Lovefield’s “Quick Start” doc for some help.
lf.schema.create()(need to pass two parameters to this call).
createTable(‘TableName’), once for each table, to get a table builder instance and start calling methods on this instance to add columns to your table.
If you are encountering problems finishing this step, take a look at the complete answer here.
Once you've built the schema, reload the app and verify that the database has been created using the developer tools in your browser.
For Chrome, navigate to Resources > IndexedDB. You should see a database called “stocks” with two empty tables, as shown in the following screenshot.
For Firefox, you first need to enable the storage inspector by checking the "Storage" checkbox in developer tools settings, see more instructions here.
Let’s take a look at the following code snippet which resides in
connectOptions parameter specifies what kind of backing storage to use for persisting the data. In this codelab we will use IndexedDB (which is also the default value if
connectOptions is omitted), so that the data is persisted locally and it becomes inspectable by Chrome/Firefox’s developer tools.
In this step you will implement the
LovefieldService#insertData() method. This method is being called by the
FetcherService class (already implemented) which grabs the data by making network requests to the Google Finance API. The method receives two Array parameters holding the raw data for each table. The fields for both sets of raw data match the schema we've defined. Take a look at the Lovefield API for inserting rows on the query builder docs , and populate both tables. You'll need to call
insertOrReplace(), as the latter requires a table to have a primary key, which is not the case for the
The raw rows are already converted to Lovefield rows within
insertData() method, by calling the
createRow() method (specifically
Reload the app and verify in the developer tools that both tables have been correctly populated. If the schema you created at the previous step is incorrect (for example missing columns, or wrong types) it will not be detected until in later steps (where spurious errors will be thrown).
Verify that the
HistoricalData table is populated correctly. Open the developer tools and inspect a random record from the
HistoricalData table. It should look as it does in the following screenshot. Make sure that all the columns are present and that they have the correct types (also ensure that there are no undefined/null values).
Now verify the same for the
StockInfo table by comparing with the following screenshot.
If the tables are not populated correctly, you will have to:
Next, you will implement SELECT queries and start populating the app’s user interface.
In this step you are going to populate the sectors/stock drop-down list depending on the current “search mode”.
You can find the list of stocks in the
LovefieldService#getStockList() method by writing a select query that retrieves all rows from the
StockInfo table and only keeps the
Stock column. Review the SELECT Query Builder docs for information on writing SELECT queries, and review the filters docs for information on selecting only the columns you're interested in.
To verify that your query works, reload the app, choose Stocks as the search mode, and click on the “Select Stocks” drop-down menu. It should be populated with stock codes as shown in the following screenshot.
LovefieldService#getSectorList() method by writing a query that retrieves all (distinct) industry sectors. This query is similar to the previous one as all the information resides in a single column of the
StockInfo table. But you have to use an aggregation function to ensure that each industry sector is included only once in the result.
Once done, verify that the query works by switching to “Sectors” search mode and clicking on the drop-down menu. It should be populated with three industry sectors as shown in the following screenshot.
In this step you are going to write a query to retrieve closing values for a given stock within a given time window sorted by date in ascending order. The information resides in the
Date columns. Go ahead and implement
LovefieldService#getStockClosingPrices(). This method receives three parameters.
end indicate the time window of interest,
stock indicates the stock to be analyzed. You will need to use all three parameters to properly build the query. Make sure to review the information on how to construct predicates.
Once done, verify that the query works. Reload the app, select “Stock” search mode, and then select any stock from the drop down menu. Select various time windows and the graph should update.
Voila! You can now start researching your next stock market moves!
In this step you are going to write a query to retrieve average closing values for a given industry sector within a given time window. The information resides in two tables, which means that the query will have to join these two tables. The association between stocks and industry sectors resides in the
StockInfo table, while the closing values for each stock resides in the
HistoricalData table. Go ahead and implement
LovefieldService#getSectorClosingPrices(). Create a query that returns the average closing value for each date, taking into account the closing values of all stocks that belong in the chosen sector, sorted by date in ascending order.
Once done, verify that the query works. Reload the app, choose “Sectors” search mode, and select any sector from the drop-down menu. The graph should update accordingly.
It is very common that a query you wrote needs to be debugged because it either
explain() method allows the developer to get an insider look on how the query engine executes a given query, by returning the execution plan in a human readable format. The execution plan is a detailed list of steps that the database engine will perform in order to calculate the answer of a given query.
For example, let’s examine the query performed by
getStockClosingPrices() which was implemented in the previous step and see whether we can improve its execution plan. In order to do so, store the query in a local variable and print out the result of the
explain() method before executing it.
As a hint, you might update the query like this:
Reload the app, perform a stock search and observe the output in the console. It should look similar to the following.
The execution plan is basically a tree data structure. Each node in the tree is an operation. The plan is executed from the bottom up. In our example the following operations will take place.
table_access: The entire
HistoricalDatatable is brought into memory.
select: Rows that don’t correspond to the GOOG stock are filtered out.
select: Rows that don’t fall within the specified time window are filtered out.
order_by: Remaining rows are sorted based on the
project: Columns that are not requested by the user are filtered out. In our case we requested all columns (by passing no params to
select()) and therefore no columns are dropped.
The execution plan described above can be improved (in terms of performance), by eliminating the need for a full table scan of the
HistoricalData table. This can be done by modifying the schema to create an index on the
HistoricalData#Stock column. Go ahead and modify the
LovefieldService#buildSchema_() method by adding the proposed index. If you get stuck, you can take a look at Lovefield’s “Quick Start” for an example schema definition that uses an index.
Once done, reload the app and re-examine the execution plan. It should now look as follows.
Notice how there is no
table_access operation anymore and the first
select is also missing. Instead, an
index_range_scan operation has shown up. The database engine is utilizing the index we just added to retrieve only those
HistoricalData rows that correspond to the GOOG stock, eliminating the need for a full table scan followed by a filtering of rows that don’t correspond to the given stock. Optimizations like this one can make a huge difference on a real-world application.
Parameterized queries are queries where placeholders are used for some values. Before such a query can be executed all placeholders must be assigned a value. Most modern database engines support this feature, although it might be advertised using a different name (for example “prepared statements” or “query templates”).
The advantage of this approach is that under the covers the database engine will generate an execution plan only once and reuse it many times. The overhead of generating an execution plan can be significant for queries that are executed multiple times.
In our application there are two queries that are executed multiple times with different values each time and therefore they could be parameterized. Specifically the queries that are executed in methods
getSectorClosingPrices() are receiving three parameters each.
Go ahead and
Once done reload the app and ensure that both stock and sector search modes still work.
A query observer is a function that gets called whenever the results of a given query are modified. Lovefield provides a very powerful DB observing mechanism via the db.observe() method. In short, any
SELECT query can be observed, such that every time the results of that query are modified, observers get notified with a detailed list of the changes that happened. The information that is passed to observers is inspired by the Array.observe() API. The two most common scenarios triggering observers are listed below.
The database is modified by
DELETE queries. For example consider the following snippet
It is worth noting that the observer is triggered only if the results of the observed query are modified. Also the
changes object passed to the callback includes a detailed list of changes (additions/deletions).
Query observers can be combined with parameterizable queries as shown below.
In the above example, even though no modification happened to the database, the observer gets triggered because a parameter of the observed query was bound to a different value (which causes the result of this query to be changed).
In our example application we are already using two parameterized queries (implemented at step 6), but the app is currently not using observers to update the UI. In this exercise the app will be modified to use DB observers to update the UI as the user is making different selections in the available drop down lists. The instructions below will guide you through this process. Note that there will be no UI noticeable change by completing this exercise, just the underlying mechanism that updates the UI will be modified.
For this exercise two files need to be modified,
query_builder_controller.js, such that it registers two observers (use the methods implemented at the previous instruction). Within each observer, call
query_builder_controller.js. You will notice that it calls
LovefieldServiceclass and once the results are available it explicitly makes a call to update the UI. This is no longer necessary since our observers will perform this task, therefore delete both occurrences of promise callbacks
startObserving_()method in the constructor of
QueryBuilderController. Place this call right before the call to
Reload the app and start interacting with the UI. Everything should be working as before, only this time query observers are utilized to update the UI.
Your sample web application is now successfully utilizing Lovefield as its data layer.
INSERTqueries, explicit/implicit transactions.
SELECTqueries, specifying column filtering, simple and composite predicates, joining, grouping, sorting.
There are more useful Lovefield features that were not covered in this codelab.
SKIPto retrieve only a subset of the results.
IndexedDbas the backing store (useful for testing, but also for cases where data persistence is not desired).
UPDATEqueries (updating specific columns while leaving other columns unaffected).
If you would like to find out more about Lovefield please see the full developer documentation. You can post questions/comments at our public discussion forum and file bugs at our issue tracker.