TSTool-Vol2-CommandReference

• Title: TSTool - CommandReference - 10.21.00
• Description: Command Reference for Version 10.21.00
• Decision Support - Doc Type: Software Documentation
• Date: 7/14/2013
• DSS Category: DMI Utilities
• Prepared By: Riverside Technology, Inc.

global output period. OutputEnd The date/time for the end of the output. Use the global output period. WriteMethod The approach for writing time series, one of: • Delete – delete the
time series records but do not write the time series (useful for testing and database maintenance) • DeleteInsert – delete time series records and then insert. Revision data are not
inserted into the database. • TrackRevisions – track revisions as per the logic described after this table. None – must be specified. ProtectedFlags Indicate the flag values for database
data records that should NOT be modified, when WriteMethod=TrackRevisions. Typically these records indicate that data have been validated and/or manually entered and automated processes
should not overwrite the values. Currently only one flag value can be specified; however, multiple values or patterns may be supported in the future. None – no data will be protected.
ComparePrecision The number of digits after the decimal used when comparing previous database values with values in the time series. This may be required where input/output operations
result in truncations or round-off. 4 RevisionDateTime The date/time for a new revision, if needed, one of: • CurrentToSecond syntax – see SetOuputPeriod() command for more information.
• ${Property} – property value string • YYYY-MM-DD hh:mm or similar date/time string Time from computer system clock at the time the command is run. RevisionUser User identifier for
revisions. Currently this is NOT taken from the RiversideDB user table. ${Property} notation can be used. User’s login from the operating system. RevisionComment Comment for revisions.
${Property} notation can be used. No comment. Revision information will be utilized only if configured in the database. The time series data table must contain a Revision_num column
and the Revision table must exist. The time series table layout information will indicate whether revision numbers are used. If WriteMethod=DeleteInsert and RevisionComment is provided,
then all old data will be deleted (all revisions) and a new revision will be added corresponding to all inserted records. This ensures that the database size does not grow quickly. If
RevisionComment is not specified, then a revision will not be added in the database (revision will be set to zero, which corresponds to the “original data” revision). The following figure
explains the logic when WriteMethod=TrackRevisions, which can be used when RiversideDB is configured with time series tables that use the Revision_num column. The following flow chart
focuses on the case where a data record to write to the database (consisting of a date/time, a value, and a flag) already exists for the date/time. This indicates that this data record
528
TSTool Documentation WriteRiversideDB() Command Command Reference – WriteRiversideDB() -5 previously has been written to the database. In this case, the existing data record in the database
should be overwritten with the new record unless the existing record is flagged as protected (in this example, manually adjusted with ProtectedFlags=M). In this case, the manually adjusted
value persists and can be overwritten only if the new data record also is flagged with M, indicating that an additional manual adjustment has occurred. The following figure illustrates
the logic performed for each value. However, several steps are performed in bulk to improve performance. Logic for Writing Time Series Values with Revisions (ProtectedFlags=“M”) Data
record(s) from TSTool time series DB record already exists? Yes – check for protected values Write new data to TS table, including data flags Revision enabled? Yes No DB record has flag
“M” (manual)? No – write record with new revision STOP Insert new revision in DB No – OK to write as Revision 1 Query DB for existing data record(s) DB record value and flag same as
time series? Yes – no change so don’t write Yes – protected so don’t write No – but might be protected 529
WriteRiversideDB() Command TSTool Documentation Command Reference – WriteRiversideDB() -6 Revisions to existing data records are stored in the Revision table in RiversideDB. Original
data records in the time series tables have a revision number of ‘1’, which refers to the revision number ‘1’ in the Revision table. Any time a new revision is needed due to changes
in a data value, a new entry is created in the Revision table, populated with the pertinent information (date/time of revision, user, and a comment) and the corresponding revision number
is assigned to the revised data record in the time series table. Revision numbers are not incremented for each data value but are incremented for the bulk database operation (similar
to a commit in content management systems). Care should be taken in using WriteRiversideDB() commands in order to minimize the number of revisions that are tracked. For example, rather
than relying on the default value for the RevisionDateTime command parameter, a better approach may be to define a property at the top of the command file using a SetProperty() command
and then refer to that property when specifying the RevisionDateTime property. This will ensure that multiple WriteRiversideDB() commands in a single command file utilize the same revision
information. If a data revision is detected based on the logic in the above figure, the corresponding revision will be searched for in the Revision table. If not found, then a new revision
record will be inserted into the Revision table and that information will be used in the time series data table. Time Series Table Revision Table MeasType_num Date_Time Val Revision_num
Quality_flag 1 2011-04-11 14:35 585.98 1 1 2011-04-11 14:50 586.02 1 1 2011-04-11 15:00 585.98 1 1 2011-04-11 15:10 585.98 1 1 2011-04-11 15:10 585.97 2 M 1 2011-04-11 15:15 585.99 1
1 2011-04-11 15:15 585.98 3 M 1 2011-04-11 15:15 585.97 4 M 1 2011-04-11 15:25 586 1 Revision_num Date_Time User Comment 1 NO REVISION 2 2011-04-11 17:00 JP Manual Change 3 2011-04-11
17:01 JP Manual Change 4 2011-04-11 18:01 JP Manual Change after review TSTool commands that read RiversideDB time series, such as the ReadRiversideDB() command, sort time series data
records by the Date_Time and revision number (Revision_num) prior to transferring the data into data objects. Consequently, the entry with the largest revision number for records at
the same sensor (MeasType_num) and time (Date_Time) is used as the valid data record because the record will be processed last. In the above example, this is the record with Revision_num
4 and a value of 585.97. This approach may result in performance degradation over time if many revisions are made to data values for the same date/time and consequently it may be appropriate
to remove or archive very old revisions as part of database maintenance. The trade-off between performance and the ability to track revisions may vary between systems and in general
there should be few revisions for the same data point because data loading will move forward through time without reloading the entire period. 530
Command Reference: WriteRiverWare() Write time series to a RiverWare format file Version 08.15.00, 2008-05-12 The WriteRiverWare() command writes one time series to the specified RiverWare
format file. See the RiverWare Input Type Appendix for more information about the file format. The following dialog is used to edit the command and illustrates the syntax of the command.
WriteRiverWare WriteRiverWare() Command Editor 531 Command Reference – WriteRiverWare() -1
WriteRiverWare() Command TSTool Documentation The command syntax is as follows: WriteRiverWare(Parameter=Value, …) Command Parameters Parameter Description Default TSList Indicate how
to determine the list of time series to process (only first one is written), one of: 􀂃 AllMatchingTSID – process time series that have identifiers matching the TSID parameter. 􀂃 AllTS
– process all the time series. 􀂃 SelectedTS – process the time series that are selected (see SelectTimeSeries()). None – must be specified. TSID Used if TSList=AllMatchingTSID to indicate
the time series identifier or alias for the time series to be filled. Specify * to match all time series or use a wildcard for one or more identifier parts. Required if TSList=AllMatchingTSID.
OutputFile The RiverWare file to write. The path to the file can be absolute or relative to the working directory. The Browse button can be used to select the file to write (if a relative
path is desired, delete the leading path after the select). None – must be specified. Units The data units to be output. Specify units that are recognized by RiverWare – the units are
not actually converted but the new units string is used in the output file. Use the units in the time series. Scale See the RiverWare Input Type Appendix. 1 Set_units See the RiverWare
Input Type Appendix. Set_units are not output. Set_scale See the RiverWare Input Type Appendix. Set_scale are not output. Precision The number of digits after the decimal to write. 4
A sample command file to write data from the State of Colorado’s HydroBase is as follows: # 08213500 -RIO GRANDE RIVER AT THIRTY MILE BRIDGE NEAR CREEDE 08213500.DWR.Streamflow.Month~HydroBase
WriteRiverWare(TSList=AllTS,OutputFile="08213500.Month.RiverWare",Precision=2) Command Reference – WriteRiverWare() -2 532
Command Reference: WriteStateCU() Write time series to a StateCU format file Version 08.16.04, 2008-09-04 The WriteStateCU() command writes time series to the specified StateCU frost
dates format file. Currently only the frost dates file can be written with this command. See the WriteStateMod() command to write StateMod format files (e.g., for the precipitation,
temperature, and diversion time series files used with the StateCU model). See the StateCU Input Type Appendix for more information about the file format. All time series matching the
data types FrostDateL28S, FrostDateL32S, FrostDateF32F, and FrostDateF28F will be written (all other time series will be ignored). Other StateCU files may be supported in the future.
See also the StateDMI software, which processes other StateCU files. The following dialog is used to edit the command and illustrates the syntax of the command. WriteStateCU WriteStateCU()
Command Editor 533 Command Reference – WriteStateCU() -1
WriteStateCU() Command TSTool Documentation The command syntax is as follows: WriteStateCU(Parameter=Value,…) Command Parameters Parameter Description Default OutputFile The StateCU
frost dates output file. The path to the file can be absolute or relative to the working directory. None – must be specified. OutputStart The date/time for the start of the output. Use
the global output period. OutputEnd The date/time for the end of the output. Use the global output period. A sample command file is as follows, using data from the State of Colorado’s
HydroBase database: # 0109 -AKRON 4 E 0109.NOAA.FrostDateL28S.Year~HydroBase 0109.NOAA.FrostDateL32S.Year~HydroBase 0109.NOAA.FrostDateF32F.Year~HydroBase 0109.NOAA.FrostDateF28F.Year~HydroBase
WriteStateCU(OutputFile="test.stm") Command Reference – WriteStateCU() -2 534
Command Reference: WriteStateMod() Write time series to a StateMod format file Version 08.15.00, 2008-05-12 The WriteStateMod() command writes the time series in memory to the specified
StateMod format file. See the StateMod Input Type Appendix for more information about the file format. It is expected that the time series have the same interval. The time series identifier
location part is written as the identifier, even if an alias is assigned to a time series. The following dialog is used to edit the command and illustrates the syntax of the command.
WriteStateMod WriteStateMod() Command Editor 535 Command Reference – WriteStateMod() -1
WriteStateMod() Command TSTool Documentation The command syntax is as follows: WriteStateMod(Parameter=Value,…) Command Parameters Parameter Description Default TSList Indicate how to
determine the list of time series to process, one of: 􀂃 AllMatchingTSID – process time series that have identifiers matching the TSID parameter. 􀂃 AllTS – process all the time series.
􀂃 SelectedTS – process the time series that are selected (see SelectTimeSeries()). None – must be specified. TSID Used if TSList=AllMatchingTSID to indicate the time series identifier
or alias for the time series to be filled. Specify * to match all time series or use a wildcard for one or more identifier parts. Required if TSList=AllMatchingTSID. OutputFile The StateMod
file to write. The path to the file can be absolute or relative to the working directory (command file location). None – must be specified. OutputStart The date/time for the start of
the output. Use the global output period. OutputEnd The date/time for the end of the output. Use the global output period. MissingValue The value to write for missing data. -999 Precision
The number of digits to use after the decimal point, for data values. A negative number indicates that if the formatted number is larger than the allowed output width, adjust the format
accordingly by truncating fractional digits. A special value of –2001 is equivalent to –2 and additionally NO decimal point will be printed for large values. The default output precision
if not specified is -2, which is then reset based on the data units (see the system\DATAUNIT file). A sample command file to process data from the State of Colorado’s HydroBase is as
follows: SetOutputPeriod(OutputStart="1950-01",OutputEnd="2002-12") # 08213500 -RIO GRANDE RIVER AT THIRTY MILE BRIDGE NEAR CREEDE 08213500.DWR.Streamflow.Month~HydroBase # 08217000
-RIO GRANDE AT WASON, BELOW CREEDE, CO. 08217000.USGS.Streamflow.Month~HydroBase WriteStateMod(TSList=AllTS,OutputFile="RioGrande.rih") Command Reference – WriteStateMod() -2 536
Command Reference: WriteSummary() Write time series to a summary format file Version 09.07.00, 2010-08-09 The WriteSummary() command writes time series to a summary report file, as text
or HMTL. The format of the file is a default for the data interval. The total/average column in reports (if output) is based on the units – a parameter may be added in the future to
allow more flexibility. The following dialog is used to edit the command and illustrates the syntax of the command. WriteSummary WriteSummary() Command Editor 537 Command Reference –
WriteSummary() -1
WriteSummary() Command TSTool Documentation The command syntax is as follows: WriteSummary(Parameter=Value,…) Command Parameters Parameter Description Default OutputFile The summary
file. The path to the file can be absolute or relative to the working directory (command file location). Specifying a filename with an “html” extension will result in HTML output, which
is colorcoded for missing values and has notes for flagged values. None – must be specified. TSList Indicates the list of time series to be processed, one of: • AllMatchingTSID – all
time series that match the TSID (single TSID or TSID with wildcards) will be processed. • AllTS – all time series before the command. • EnsembleID – all time series in the ensemble will
be processed. • FirstMatchingTSID – the first time series that matches the TSID (single TSID or TSID with wildcards) will be processed. • LastMatchingTSID – the last time series that
matches the TSID (single TSID or TSID with wildcards) will be processed. • SelectedTS – the time series are those selected with the SelectTimeSeries() command. AllTS TSID The time series
identifier or alias for the time series to be processed, using the * wildcard character to match multiple time series. Required if TSList=*TSID. EnsembleID The ensemble to be processed,
if processing an ensemble. Required if TSList= EnsembleID. OutputStart The date/time for the start of the output. Use the global output period. OutputEnd The date/time for the end of
the output. Use the global output period. OutputYearType The output year type, in particular for formatting monthly and daily time series. Calendar A sample command file to process data
from the State of Colorado’s HydroBase is as follows: SetOutputPeriod(OutputStart="1950-01",OutputEnd="2002-12") # 08213500 -RIO GRANDE RIVER AT THIRTY MILE BRIDGE NEAR CREEDE 08213500.DWR.Streamflow
.Month~HydroBase # 08217000 -RIO GRANDE AT WASON, BELOW CREEDE, CO. 08217000.USGS.Streamflow.Month~HydroBase WriteSummary(TSList=AllTS,OutputFile="RioGrandeStreamflow.txt",TSList="AllTS")
Command Reference – WriteSummary() -2 538
Command Reference – WriteTableToDataStore() -1 Command Reference: WriteTableToDataStore() Write a table to a datastore Version 10.18.00, 2013-03-03 This command is under development
and has the following limitations: • Although some error handling has been implemented, it is not very detailed. Improvements will be made in response to exercising the command functionality.
• Write statements are created for each row of the table being written. This is inefficient and slow. Improvements will be made in future updates. • Functionality has been tested mainly
with SQL Server. • Handling of date objects has not been tested. • Better handling of blank rows needs to be implemented. The WriteTableToDataStore() command processes each row in a
table and executes an SQL statement to insert the row into a database datastore. If database datastore support is not specifically provided by TSTool, a generic datastore can be used
(see the Generic Database Datastore appendix). This command cannot be used with web service datastores and use with Excel datastores has not been tested. This command is useful in particular
for bulk data loading such as for database initialization and when tight integration with TSTool is not required or has not been implemented. In the future additional command parameters
may be added to limit the rows that are being written and allow update functionality. General constraints on the query are as follows: • the table or views being written must be writeable
by the user specified for the database connection (some databases restrict direct access to data and require using stored procedures) • the table column names must match the database
table column names (in the future a command parameter may be added to allow column names to be mapped) • data types for table columns must closely match the database: o internally an
SQL statement is created in which data values are formatted as per the data type (e.g., strings are quoted); consequently column types must be appropriate to generate correct formatting
o the full precision of floating point numbers is passed to the database (formatting for display will not apply to values written to the database) o null values in the table will transfer
to null values in the database o date/time columns in the table will be represented as such in the database table; however, it may not be possible to limit the precision of the date/time
(i.e., hours, minutes, and seconds may be shown with default zero values in output) • the specified table columns are written (all are written by default) o primary keys in the database
table do not need to be specified (their values will be assigned automatically) o table columns that correspond to related tables in the datastore table need to be mapped using the DataStoreRelatedCo
lumnsMap command parameter An example of column mapping to a related table is as follows, using the notation Table.Column to fully identify columns: • the string TableID.DataType column
is in the input data 539
WriteTableToDataStore() Command TSTool Documentation Command Reference – WriteTableToDataStore() -2 • an integer database table TimeSeriesMeta.DataTypesID column is a foreign key to
DataTypes.DataTypesID, and DataTypes.Abbreviation is the string data type – in other words, the datastore column being written does not match the string data type, but uses a relationship
to match the integer data type in a separate table To handle this relationship: • Use the ColumnMap parameter to tell the command that the DataType column in input table maps to the
DataTypesID column in the datastore table: ColumnMap=”DataType:DataTypesID” • Use the DataStoreRelatedColumnsMap parameter to tell the command that the DataTypesID column should be looked
up the Abbreviation column, which is a second level of column mapping: DataStoreRelatedColumnsMap=”DataTypesID:Abbreviation” The following dialog is used to edit the command and illustrates
the syntax for the command, in this case writing a table to a datastore that was defined as a GenericDatabaseDataStore. WriteTableToDataStore WriteTableToDataStore() Command The command
syntax is as follows: WriteTableToDataStore(Parameter=Value,…) 540
TSTool Documentation WriteTableToDataStore() Command Command Reference – WriteTableToDataStore() -3 Command Parameters Parameter Description Default TableID Identifier for table to write.
None – must be specified. IncludeColumns The names of the table columns to write, separated by commas. All columns from TableID are written. ExcludeColumns The names of table columns
NOT to write, separated by commas. This will override IncludeColumns. All columns from TableID are written. DataStore The name of a database datastore to receive data. None – must be
specified. DataStoreTable The name of the database table or view to receive data. None – must be specified. ColumnMap Indicate which columns in TableID have different names in DataStoreTable,
using the syntax: ColumnName:DatastoreTableName, ColumnName:DatastoreTableName, … DatastoreTableName columns are assumed to match the column names in TableID DataStoreRelated ColumnsMap
Indicate datastore columns that need to match values in a related table in the datastore. For example, TableID may contain a column “Abbreviation” but the corresponding column in DataStoreTable
may refer to a related table using a foreign key relationship (matching integer column in both tables). It is expected that the related table will have only one primary key column, which
will be determined automatically. However, a column mapping must be provided to tell the command which DataStoreTable column should be matched with the related table. The syntax of the
parameter is: DataStoreTableCol1:RelatedTableCol1, DataStoreTableCol2:RelatedTableCol2, … The above assumes that foreign keys have been defined in the DataStoreTable columns. If the
database does not explicitly define a foreign key relationship in the database design, then specify the right side of the map as: RelatedTable1.RelatedCol1. DatastoreTableName columns
are assumed to match the column names in TableID, with no need to perform reference table value matching. WriteMode The method used to write data, recognizing the databases use insert
and update SQL statements, one of: • DeleteInsert – delete the data first and then insert (all values will need to be matched to delete) • Insert – insert the data with no InsertUpdate
541
WriteTableToDataStore() Command TSTool Documentation Command Reference – WriteTableToDataStore() -4 Parameter Description Default attempt to update if the insert fails • InsertUpdate
– try inserting the data first and if that fails try to update • Update – update the data with no attempt to insert if the update fails • UpdateInsert – try updating the data first and
if that fails try to insert This page is intentionally blank. 542
Command Reference – WriteTableToDelimitedFile() -1 Command Reference: WriteTableToDelimitedFile() Write a table to a delimited file Version 10.20.00, 2013-03-25 The WriteTableToDelimitedFile()
command writes a table to a delimited file. Currently only the comma is supported as the delimiter. This command is the analog to the ReadTableFromDelimitedFile() command. It can be
used to provide tabular data to other programs, such as spreadsheet programs and geographic information systems. The default is to write a standard file header using comment lines that
start with the # character. If available, column names will be written in double quotes as the first non-comment row. Formatting for cell values is limited and the default precision
of floating point numbers may include too many digits – this will be addressed in future updates. The following dialog is used to edit the command and illustrates the syntax for the
command. WriteTableToDelimitedFile WriteTableToDelimitedFile() Command Editor 543
WriteTableToDelimitedFile() Command TSTool Documentation Command Reference – WriteTableToDelimitedFile() -2 The command syntax is as follows: WriteTableToDelimitedFile(Parameter=Value,…)
Command Parameters Parameter Description Default TableID Identifier for the table to write. None – must be specified. OutputFile The name of the file to write, as an absolute path or
relative to the command file location. None – must be specified. WriteHeaderComments Indicates whether to write the header comments, True or False. Some programs, such as Esri’s ArcGIS
do not handle delimited files with comments. True AlwaysQuoteStrings Indicates whether values in string columns should always be surrounded by double quotes: • False – only quote strings
that contain the delimiter • True – always quote strings An example of using AlwaysQuoteStrings=True is to quote identifiers that have a leading zero (e.g., 01234567). Not quoting may
cause the values to be interpreted as integers when read from the delimited file. False NewlineReplacement The string to replace newlines in string values, necessary to prevent unexpected
line breaks in output rows. In order to handle newlines from various systems, the following patterns are replaced in sequence: • \r\n • \n • \r The following special parameter values
are recognized: • \t – replace newline with tab • \s – replace newline with space Do not replace newlines. 544
Command Reference: WriteTableToHTML() Write a table to an HTML file Version 09.10.01, 2010-12-07 The WriteTableToHTML() command writes a table to an HTML file. It can be used to publish
tables to the web. Table column names are output as the HTML table column headers. Formatting for cell values is based on the precision of the original table data. Default styles are
written at the top of the HTML. In the future the command may accept styles as input. The following dialog is used to edit the command and illustrates the syntax for the command. WriteTableToHTML
WriteTableToHTML() Command Editor 545 Command Reference – WriteTableToHTML() -1
WriteTableToHTML() Command TSTool Documentation The command syntax is as follows: WriteTableToHTML(Parameter=Value,…) Command Parameters Parameter Description Default TableID Identifier
for the table to write. None – must be specified. OutputFile The name of the file to write, as an absolute path or relative to the command file location. None – must be specified. Command
Reference – WriteTableToHTML() -2 546
Command Reference: WriteTimeSeriesProperty() Write a time series property to a file Version 08.16.03, 2008-08-18 This command is under development and is used primarily for software
testing. In particular one limitation is that the time series identifier is not included in output and therefore properties for multiple time series are not uniquely identified. The
WriteTimeSeriesProperty() command writes the value of a time series property to a file. This command should not be confused with the WriteProperty() command, which writes processor properties.
This is useful for testing whether properties are being set. It could also be used to pass information from TSTool to another program. The format of the output is: Property=”Value” Multi-line
properties will be contained within the quotes. The number of properties is limited at this time, as needed for testing software, but may be increased in the future. The format of output
may also change in the future. The following dialog is used to edit this command and illustrates the syntax of the command. WriteTimeSeriesProperty WriteTimeSeriesProperty() Command
Editor 547 Command Reference – WriteTimeSeriesProperty() -1
WriteTimeSeriesProperty() Command TSTool Documentation The command syntax is as follows: WriteTimeSeriesProperty(Parameter=Value,…) Command Parameters Parameter Description Default TSList
Indicates the list of time series to be processed, one of: • AllMatchingTSID – all time series that match the TSID (single TSID or TSID with wildcards) will be modified. • AllTS – all
time series before the command. • EnsembleID – all time series in the ensemble will be modified. • FirstMatchingTSID – the first time series that matches the TSID (single TSID or TSID
with wildcards) will be modified. • LastMatchingTSID – the last time series that matches the TSID (single TSID or TSID with wildcards) will be modified. • SelectedTS – the time series