6 Upload data

The database serves as a central repository for all data regarding solid wood, wood based materials, coating materials and adhesives of Institute of Wood Technology and Renewable Materials. Therefore new data needs to get uploaded to it after you made sure it is sanity checked. The upcoming sections explain the various constraints (“input validation”) when uploading data and go through the process of uploading.

6.1 Allowed values

A good database is very restrictive to ensure no wrong data (data in wrong units, typos etc.) gets into it. It does input validation through constraints and data types.

6.1.1 Data types and NULL

You can see the structure of a table by clicking on the table name on the left panel inside DB Admin as you did here. Beside Column you can see the allowed data type for each column at Type. Also you see whether you have to provide a value at a certain column of whether its allowed to leave the field empty - the latter is marked with NULL.

6.1.2 Foreign keys

Foreign keys are references to other table’s primary keys. Therefore its only allowed to enter primary key values of this other table. If a table of the schemas accum or const is linked, then the only existing column is the primary key.

In the table below, showing the table structure, you find information about the table’s foreign keys. Source is indicating the column name, Target lists the table the foreign key is linking to. You can click on those links to find out the allowed values (pressing CTRL while clicking opens a new tab).

6.1.3 Additional constraints

To prevent users from inserting values in wrong units there are additional constraints. You can find these in schema meta in the table measurement_parameter. Click on Select data to get to the data where you find the intended unit of the resp. parameter and an lower_limit as well as an upper_limit. If the measurement_value exceeds these two limits, you will encounter an error. If you are sure your measured value as well as the unit is correct, but is outside the intended range, please contact the database administrator.

6.2 Where to upload?

Most of the time you will upload data in the schemas solid, wood_based as well as adhesive. For most parameters you can upload in the measurement table. But for some parameters your table needs more arguments. This is why the concept of subtypes was introduced in section 5.2.12. Before uploading data to the table measurement be sure there is no subtype existing for the parameter you want to add. In case there is - enter your data there, it will also be available in measurement.

6.3 How to upload?

6.3.1 Single Item

You can enter data to the database by clicking on New item after accessing a table and insert values at least at all fields where no default value of NULL is suggested.

6.4 Batch upload

Most of the time you won’t just enter a single entry manually, but have a bunch of new measurements you want to insert into the database. Go to the table where you want to upload data and click on Select data to see the table’s data as described in 5.2.3. To be sure that you upload data in the right structure its a good starting point to download some lines (set the limit to a small number) as described in section 5.2.10 and use this file to fill it with new data for later upload.

You can also use a TSV or CSV you built yourself, you then either

  • have to provide a file without headers but correct number as well as correct order of columns or
  • have a file with headers (you then don’t have to provide all columns, but at minimum all not marked with NULL at the structure overview) - the headers have to exactly match the column names in the database.

Go to the very end of the table and upload your file:

You are allowed to provide files in the following formats:

  • TSV for tabstopp separated value
  • CSV, for comma separated value using a comma to separate the values and a dot as decimal point
  • CSV; for comma separated value (German version) using a semicolon to separate the values and a comma as decimal separator

If a red bar appears at the top of your page, something went wrong. Please refer to the error message to find out the reason, make a change and try again (in the example error message below, my intended entry “some_institution” to the column institution of the table person is not available in the primary keys of the table institution - it violates the foreign key constraint).

If you see a green bar your data was successfully added to the database.

6.5 Save binary files within the database (data type bytea resp. BLOBs)

In some cases you want to attach binary files (such as images, PDFs or other) to entries in the database (e.g. attach a project’s report to the project entry, save the measurement file with the stress–strain curve to a measurement etc.). You find these columns in DB Admin indicated as data type bytea in the table structure. Once you found the row where you want to add a binary file click on edit left of it just as described in section 5.2.11. Click on Browse to select a file which gets uploaded (server can be busy for a while!) after you saved your change. The procedure is the same for new entries.

If you also want to download or view binary data or upload files more frequently (or the upload takes very long resp. you encounter a timeout), please get yourself comfortable using SQL Workbench.

Please make sure you just upload files with reasonable size - consider compressing images and pdfs before uploading.

6.6 Upload checklist

Before uploading any data make sure you checked the following points:

  • You “sanity checked” your data and are absolutely sure that your data are valid and without flaws (no uninterpretable outliers, problems with specimens, problems with measurement setup). The column is_valid should be set TRUE for all uploaded data and is only there to “flag” potentially erroneous rows by administrators.
  • The unit of the measurement is as stated in schema meta, table measurement_parameter, column unit for the respective parameter.
  • The values of your measurements are in between lower_limit and upper_limit (which you find in the same table as unit).
  • You entered data for at least all columns not accepting NULL (see the table structure as described here).
  • All columns with foreign keys only show values you find as primary keys in the referenced tables.
  • Your measurement procedure used shows no difference (in any single detail!!) to the measurement_procedure you added as a foreign key in your file to upload. If so, contact a database administrator to add a new measurement_procedure!
  • All inserted strings (i.e. project speciemen identifiers etc.) are without any preceding or following unintentional whitespaces.
  • Your uploaded file is not too big (as rule of thump: is smaller than 5000 rows) - otherwise better use a direct database connection as described in section 7.