MySQL Plugin

Description

Import values from a MySQL database.

Using this plugin you can synchronise your qtstalker charts with quote data you keep in a MySQL database. The plugin does not depend on specific table or field names. Instead, you configure it by specifying the SQL query it should execute.

Main Dialog

When you start the MySQL Plugin, this dialog window appears:

Once you have configured the plugin (see below), you'll usually just press the button at the top left, which performs the update.

This is what all four buttons do:

• Update starts the quote update. All charts in Stocks/MySQL will be updated.
• Cancel interrupts a running update.
• Add Symbols brings up a dialog in which you can add new symbols to be imported from MySQL.
• Edit Settings brings up the Settings Dialog (see below).

The other control on the page is the Full Reload check box.

• Full Reload. By default, the MySQL plugin imports only those quotes which are more recent than the latest quote already present in Qtstalker. If you tick this box, the plugin will reload all quotes for all stocks. This takes a lot longer, but you'll need it if you add or modify quotes in the MySQL database which either had been imported already, or whose date is earlier than the latest quote present.

Settings Dialog

Use the Settings Dialog to configure how to connect to the MySQL database and what statement to issue to retrieve quotes.

• Database - Name of the MySQL database to connect to.
• Host - Name of the machine hosting the MySQL database
• Username - The username for connecting to the MySQL database. This MySQL user must have SELECT rights on the appropriate table(s).
• Password - The password for connecting to the MySQL database. Note: this password will be stored in plaintext on your machine. For the security-aware, the best thing to do is to define a 'qtstalker' user on the database, whose rights are restricted to doing SELECTs on the quote tables. This user could do without a password.
• SQL Query - The query string to use (see below for explanation)

Note: You need to specify these settings only once. They are remembered between uses of the plugin.

The SQL Query

In order to keep this plugin maximally flexible, you must provide the SQL query to extract quotes for a specific symbol from the database.

The query you specify can be arbitrarily complex, as long as it is a single SQL SELECT statement which meets the following requirements:

• The names of the fields returned do not matter, but their order does. The order must be date, open, high, low, close, volume. (Note: the plugin currently handles only Stocks, so it won't import OI.)
• The statement must return quotes for one single symbol. Use the placeholder $SYMBOL$ whereever the actual symbol name must be substituted in the statement.
• Use the placeholder $LASTDAY$ in a WHERE clause if you want to use the incremental update feature (see discussion of the Full Reload above, and the examples below). The substring $LASTDAY$ will be replaced with the date of the last quote present in the qtstalker datafile for the symbol being updated. (If the Full Reload checkbox is checked, it will be replaced with a prehistoric date.)

Here is an example for someone using one table per symbol, table name corresponding to symbol name.

   SELECT day,open,high,low,close,volume
     FROM $SYMBOL$
     WHERE day > '$LASTDAY$'
     ORDER BY day

Here is another example, for someone using a single table for all quotes for all symbols.

   SELECT day,open,high,low,close,volume
     FROM Quotes
     WHERE symbol = '$SYMBOL$' AND day > '$LASTDAY$'
     ORDER BY day

One more example, which looks like my set-up. I have a Quotes table and a Symbols table, which both have a foreign key (stockId) referring to a shared primary key in a Stocks table.

   SELECT day,open,high,low,close,volume
     FROM Symbols JOIN Quotes USING (stockId)
     WHERE symbol = '$SYMBOL$' AND day > '$LASTDAY$'
     ORDER BY day

Technical Requirements

This plugin requires the mysql client libraries on the machine it is running on. Check if you have /usr/lib/libmysqlclient.so.*.