View source for PHREEQC Database Utility

[[Category: PHREEQC]]
{{BETA}}
{{Navigation | [[Models]] |[[Models#Thermodynamic Calculation Engines|TCE Models]]| [[PHREEQC Overview|PHREEQC]]}}
{{PHREEQC Header Tabs | currentpage = 2}}

----
{{TOC}}
== Applicability ==
This utility is for adding a list of species into a new SysCAD.93.db3 file for use with [[PHREEQC Overview|PHREEQC]] TCE.  
*It will use data from existing databases (in SysCAD database format), examples of such are Default.93.db3, SysCAD.93.db3 from user projects (these can be renamed for better identification).
*It will NOT extract data from PHREEQC databases.
*It will add species to the new database based on the DAT files selected.  It will include species that are listed in the DAT file as well as species that "may" form given the list of ions present.
*If a species is not found in the existing database, it will add the species with minimum data (Long Name, Short Name, compound, definition, phase, occurrence), but will leave all the other properties blank.  User will need to check and fill in data from other sources after the database is created.

== General Information ==

Utility file name: '''<big>SysCADPHREEQCUtils.exe</big>'''

*This utility can be used to create a species database using data from PHREEQC DAT file (database file).  PHREEQC software and database files are available online for download at: https://www.usgs.gov/software/phreeqc-version-3

*The utility can be placed in any folder, but the folder must meet the following requirements:[[File:PhreeqcFolderStructure.png|frame|PHREEQC Utility Folder Structure]]
*#There must be an existing '''SysCAD.93.db3''' file inside the folder to serve as a template (for species table layout).  
*#*CAUTION: depending on user selections, this file may be stripped down to contain only water and steam, then new data will be added based on the PHREEQC model species list.  Stripping of the file is optional.
*#*Please use a “copy” of the actual SysCAD.93.db3 file to avoid destroying user data.
*#There must be a subfolder, “'''DB3Files'''”, containing the following files: 
*#*May contain the '''Default.93.DB3''' file (copy from '''SysCADxxx\BaseFiles''' folder).  
*#*May contain user’s own database files, formatted in SysCAD DB file format.  
*#*Any database files stored in the DB3Files subfolder will be used to populate the SysCAD.93.DB3 file.
*#*when more than one database is present, there may be multiple entries for the same species, if a species has multiple sets of data, user must choose which set of data to add to the SysCAD.93.DB3 file.
*#*as an alternative to the above, user can specify a preferred database.  In this case, the preferred database will be used whenever possible, with alternate databases used elsewhere and placeholder species created where no data is available.  Selecting a preferred database will avoid choosing data for each case where multiple options are available.
*#*The databases in this folder will be used to build a list of options presented to the user by the utility.
*#There must be a subfolder, “'''DatabaseFiles'''”: 
*#*Users must place copies of each DAT file that they wish to include in the generation of the SysCAD database file. 
*#*There is an option to select one or all of the contained DAT files in the generation of the database.
{{Error_And_Message_Logs_Utility}}
*#''IonList.txt'', the ion list can be created by the utility, see item "Config.txt" below.
*#:[[File:TPS_IonList_AqSol.png]]
*#<b>Optional:</b> User can place a ''Replacements.txt'' file in the same folder as the executable.  This is a tab-delimited file that contains replacement strings.  If not found, it is simply not used.
*#:<b>''Replacements.txt''</b> <br>[[File:TPS_Replacements_File.png]]
*#*If the above file were used, any PHREEQC species containing the string Ice would be replaced with H2O in the creation of SysCAD species.
*#<b>Optional:</b> User can place a ''Config.txt'' file in the same folder as the executable.  This is a formatted file that contains configuration settings.  If not found, it is simply not used.
{{TPS_Utility_Config|IncludeIonList=true}}

== How to use the Utility ==

The utility has two functions:
# assisting with the creation of IonList.txt. 
# creating the SysCAD.93.db3 file. 

===Creating the Database === 

#Run '''SysCADPHREEQCUtils.exe'''
#The utility will ask for selection of a preferred database.  Select the corresponding preferred database, or -1 if none is preferred.  If -1 is selected, the user will manually select the data for each species where more than one option exists (see later step).
#Select the DAT file for which you wish to create a database.  Select -1 to create files which incorporate all of the DAT files in the DatabaseFiles folder.
#Where a suitable species does not exist in the available SysCAD database source files, a placeholder species will be created with NO thermodynamic data.
#Upon completion, the utility will show a summary, confirming the database was created successfully.  Press any key to close the utility.
#:[[Image:PHREEQCUtil1.png|PHREEQC Utility - Create SysCAD.93.db3]]

:NOTES:
:*If the species data does not exist in the reference SysCAD databases, then a place holder species with <b> no thermodynamic or physical properties data </b> will be used.  When this happens, any project using this database will log appropriate warnings on load, look out for these species when [[#Checking the Created SysCAD.93.db3|Checking the Created SysCAD.93.db3]] 
:*:In the above screenshot, we have 62 solid species using default (placeholder) data.  In other words, 62 solid species have no thermodynamic data or physical properties data, and will use SysCAD default values when used in a project.
:*While running the utility, if you have NOT selected a preferred database (i.e. used -1 as selection) and multiple data is found for a species, you will be prompted to select one of the options:
:*:[[Image:Select_Species_PHREEQC.png]]
:*:In the above example, the utility has found 2 sets of data for “NaCl” in the available databases, we have selected data set 2 by typing in the "2".
:*:If a Cp range was specified using MIN_TEMP_CP and MAX_TEMP_CP, the utility may be able to select from multiple databases based upon maximum Cp range coverage.  In this case, the prompt above would not be shown.
:*User can use <Ctrl+Z> to quit the utility at any time.

=== Checking the Created SysCAD.93.db3 ===

Check the SysCAD.93.db3 file before copying it to your actual project's cfgfile folder.  You can use any SQLite DB reader, such as [https://sqlitebrowser.org/ DB Browser for SQLite].  View the ''SpeciesData'' table to see the data. Species for which no data was found in the reference databases will appear as having each of the definition columns filled, but all of the property columns blank.

[[Image:PlaceholderSpecies DBBrowser.png|1200px]]

In the above example, NaCl*2H2O is using placeholder data.

=== Copy the files to the CfgFiles folder ===

Generally speaking, user only need to use this Utility to generate the SysCAD.93.db3 file at the start of a new PHREEQC project group.  Once the database has been created, the '''SysCAD.93.db3''' file, as well as the associated PHREEQC database files used to generate them, should be copied to the PHREEQC SysCAD project group\CfgFiles sub-folder.  For SysCAD with PHREEQC flowsheets using multiple PHREEQC database files, it is required to create the database using all of the PHREEQC database files at the same time when using this utility.  

=== Updating the SysCAD Configuration ===

#Edit the SysCAD configuration
##Open SysCAD without a project open.  Go to Edit->Project Configuration
##Edit the new configuration.  Press "Next" on the Step 1 of 2 dialog.
##In the Step 2 of 2 dialog, go to the species tab, then add the required species to the project.
##Go to the TCEs tab
##Update the configuration for PHREEQC. See [[TCE_Configuration|TCE Configuration Help Page]] for more information.

NOTES:
To select ALL species for the project:
*Select the first species in the middle column, hold shift, and select the last species in that column.  This will select ALL species in the database.
*Add all species to the project by pressing the "Add Species>>" button.

=== Creating a Test Project ===

You should now have a working SysCAD configuration file.  The next step is to create a project and review the species properties.
#Create a new SysCAD project and select the newly created configuration.  See [[Introductory Tutorial - Creating a SysCAD Project]] for more details.
#Once the project is open, review the species properties
##Go to Species->View Properties
##This brings up a window showing all data used for all species.  More information on this window is here: [[Species Properties ($SDB)]].  It is recommended to carefully review this to ensure it is correct.

== Troubleshooting ==

{| class="wikitable"
|-
! Error !! Description !! Possible cause / suggested solution.
|-
| rowspan=3|Utility Startup Errors || Missing SysCAD Template species database (SysCAD.93.db3) || The SysCAD.93.db3 file is missing from the Utility folder.  This file is required as a starting point.  User may copy any valid SysCAD.93.db3 file from the SysCADxxx folder here.   
|-
| No SysCAD reference species database found in folder DB3Files || The "DB3Files" folder is missing or empty.  Please check the folder name and or make sure it contains at least one SysCAD database, such as the Default.93.db3
|-
| No Model files found in folder DatabaseFiles || The "DatabaseFiles" folder is missing or empty.  Please check the folder name and or make sure it contains at least one PHREEQC database file with extension .DAT.
|-
|}
{{TCE_Utility_Recommended_Procedure}}