Guide

User Manual: Pdf

Open the PDF directly: View PDF .
Page Count: 5

Download
Open PDF In Browser	View PDF

Time Series Database Interface (TSdbi)

May 9, 2017

1

TSdbi Functions

In R, the functions in this package are made available with
> library("TSdbi")
The TSdbi package provides the common parts of an interface to time series
databases. To use this package it is necessary to also have one of the several
extension packages which provide provide the interface to the underlying source.
A complete vignette, illustrating the general functionality of all TSdbi extension
packages is provided in the vignette with package TSdata. Installing that package requires that most of the TS* packages are available, which will not be necessary for most users, so it may be easier to get the pdf version of the vignette from
CRAN at http://cran.r-project.org/web/packages/TSdata/index.html.

2

SQL setup

The documentation below is intended for an administrator who needs to set
up an SQL version of a database for local use. For many users this will not
be necessary, as extensions other than the SQL ones will not require this setup
(and the SQL ones setup a test database automatically).
The TSdbi interface works with some databases that are not SQL, but for
SQL databases the instructions below provide details for setting up the backend
database server tables.

3

Administration: Database Table Setup

The instructions to build SQL tables using R are given in the file CreateTables.TSsql distributed in TSdbi/inst/TSsql/ and for simple examples such as
illustrated in the database specific packages it is adequate to simply
source(system.file(”TSsql/CreateTables.TSsql”, package=”TSdbi”)).
Below the plain SQL instruction are shown. In a few places MySQL specific
commands are used, but the equivalent for other SQL variants should be fairly

1

Table 1: Data Tables
Table
Meta
A
Q
M
S
W
D
B
U
I
T

Contents
meta data and index to series data tables
annual data
quarterly data
monthly data
semiannual data
weekly data
daily data
business data
minutely data
irregular data with a date
irregular data with a date and time

clear to someone familiar with the SQL variant. The plain SQL instruction
below can be executed in a standalone client, such as mysql, which might be
convenient when bulk loading data. (Example makefiles for bulk loading data
might eventually be available from the author.)
The database tables are shown in the Table below. The Meta table is used for
storing meta data about series, such as a description and longer documentation,
and also includes an indication of what table the series data is stored in. To
retrieve series it is not necessary to know which table the series is in, since this
can be found on the Meta table. Putting data on the database may require
specifying the table, if it cannot be determined from the R representation of the
series.
In addition, there will be tables ”vintages” and ”panels” if those features are
used. The tables can be set up with the following commands. (Please note
that this documentation is not automatically maintained, and could become
out-of-date. The instructions in the file TSsql/CreateTables.TSsql are tested
automatically, and thus guaranteed to be current.)
DROP TABLE IF EXISTS Meta;
create table Meta (
id
VARCHAR(40) NOT NULL,
tbl
CHAR(1),
refperiod
VARCHAR(10) default NULL,
description
TEXT,
documentation
TEXT,
PRIMARY KEY (id)
);
DROP TABLE IF EXISTS A;
2

create table A (
id
VARCHAR(40),
year
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS B;
create table B (
id
VARCHAR(40),
date
DATE,
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS D;
create table D (
id
VARCHAR(40),
date
DATE,
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS M;
create table M (
id
VARCHAR(40),
year
INT,
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS U;
create table U (
id
VARCHAR(40),
date
DATETIME,
tz
VARCHAR(4), #not tested
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS Q;

3

create table Q (
id
VARCHAR(40),
year
INT,
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS S;
create table S (
id
VARCHAR(40),
year
INT,
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS W;
create table W (
id
VARCHAR(40),
date
DATE,
period
INT,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS I;
create table I (
id
VARCHAR(40),
date
DATE,
v
double DEFAULT NULL
);
DROP TABLE IF EXISTS T;
create table T (
id
VARCHAR(40),
date DATETIME,
v
double DEFAULT
);

NULL

Indexes can be generated as follows. (It may be quicker to load data before
generating indices.)
CREATE INDEX Metaindex_tbl ON Meta (tbl);
4

CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE
CREATE

INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX
INDEX

Aindex_id
Aindex_year
Bindex_id
Bindex_date
Bindex_period
Dindex_id
Dindex_date
Dindex_period
Mindex_id
Mindex_year
Mindex_period
Uindex_id
Uindex_date
Uindex_period
Qindex_id
Qindex_year
Qindex_period
Sindex_id
Sindex_year
Sindex_period
Windex_id
Windex_date
Windex_period
Iindex_id
Iindex_date

ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON
ON

A
A
B
B
B
D
D
D
M
M
M
U
U
U
Q
Q
Q
S
S
S
W
W
W
I
I

(id);
(year);
(id);
(date);
(period);
(id);
(date);
(period);
(id);
(year);
(period);
(id);
(date);
(period);
(id);
(year);
(period);
(id);
(year);
(period);
(id);
(date);
(period);
(id);
(date);

CREATE INDEX Tindex_id
ON T (id);
CREATE INDEX Tindex_date ON T (date);
In MySQL you can check table information (eg. table A ) with
describe A;
This is generic sql way to get table information but it requires read privileges
on INFORMATION SCHEMA.Columns which the user may not have. (And
SQLite does not seem to support this at all.)
SELECT COLUMN_NAME, COLUMN_DEFAULT, COLLATION_NAME, DATA_TYPE,
CHARACTER_SET_NAME, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.Columns WHERE table_name='A' ;
In mysql data might typically be loaded into a table with command like
LOAD DATA LOCAL INFILE 'A.csv' INTO TABLE A FIELDS TERMINATED BY ',';
Of course, the corresponding Meta table entries also need to be made.

5



---

Source Exif Data:

File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 5
Producer                        : pdfTeX-1.40.14
Creator                         : TeX
Create Date                     : 2017:05:09 11:34:22-04:00
Modify Date                     : 2017:05:09 11:34:22-04:00
Trapped                         : False
PTEX Fullbanner                 : This is pdfTeX, Version 3.1415926-2.5-1.40.14 (TeX Live 2013/Debian) kpathsea version 6.1.1

EXIF Metadata provided by EXIF.tools