Till Francke, till@comets.de; 21.11.2014

The development of WASA-SED is still in progress. Therefore, the structure of the database used to store input data for the model
may be subject to changes. This file is meant to document these changes. 
To check the version number of your current database file, open the data base, then table "db_version" and read the "version" field of the most recent (last) record in your database file.
If you need to update your db-file, backup your file first! Then, find the entry PRIOR to your current version number in the list below. Process all the following instructions of higher version version numbers, until you have reached the desired version number. 
Within each upgrade step, instructions may be given as SQL-STATEMENTS or as verbal instructions, starting with a #. Please process them in the order given in the upgrade step.


<ver_number>
	<upgrade_intructions>

ver. 1
	#If you cannot find a version number in your db-file, you are using a db-file version built before Feb, 2006.
	#Open format_ver1.mdb (downloadable from http://brandenburg.geoecology.uni-potsdam.de/projekte/sesam/reports.php)
	#and compare to your db-file manually. Copy and paste your data, where necessary.
	#<no SQL-instructions>

ver. 2	
	#computationally irrelevant, renamed stream_order to a_stream_order
	#add column description "(optionally automatically generated) stream order (1 at outlet basin)"
	INSERT INTO db_version VALUES (2, 2, "column stream_order renamed to a_stream_order", "", "subbasins", "computationally irrelevant", "17.7.2006");

ver. 3
	#computationally irrelevant
	#added views, used for checking output from prof_class. Export these views from the format_ver3.mdb template (right click on view and "export" to your db) or do this manually using the SQL-statements given below.
	#m_find_lus_having_tcs_with_negative_slope:
	#SELECT landscape_units.pid, landscape_units.descr, terrain_components.pid, r_lu_contains_tc.position, terrain_components.slope FROM terrain_components INNER JOIN (landscape_units INNER JOIN r_lu_contains_tc ON landscape_units.pid=r_lu_contains_tc.lu_id) ON terrain_components.pid=r_lu_contains_tc.tc_id WHERE (((terrain_components.slope)<=0));

	#m_find_tcs_of_irrelevant_lus:
	# SELECT landscape_units.pid, r_lu_contains_tc.lu_id FROM landscape_units LEFT JOIN r_lu_contains_tc ON landscape_units.pid=r_lu_contains_tc.lu_id WHERE r_lu_contains_tc.lu_id is NULL;
	#m_find_lus_not_in_any_subbasin:
	# SELECT landscape_units.pid, r_subbas_contains_lu.fraction FROM landscape_units LEFT JOIN r_subbas_contains_lu ON landscape_units.pid=r_subbas_contains_lu.lu_id WHERE r_subbas_contains_lu.lu_id is NULL;
	#m_find_lus_without_tcs
	# SELECT landscape_unit.pid, r_subbas_contains_lu.fraction FROM landscape_units INNER JOIN r_subbas_contains_lu ON landscape_units.pid=r_subbas_contains_lu.lu_id WHERE r_subbas_contains_lu.lu_id is NULL;
	INSERT INTO db_version VALUES (3, 3, "added views m_find_lus_having_tcs_with_negative_slope,m_find_tcs_of_irrelevant_lus,m_find_lus_not_in_any_subbasin,m_find_lus_without_tcs", "", "", "", "31.07.2006");
ver. 4
	#added columns lag_time and retention to table subbasins to allow generation of response.dat
	alter table subbasins add lag_time float,retention float
	#add column description "runoff lag time [d]" to table subbasins, column lag_time
	#add column description "runoff retention time [d]" to table subbasins, column retention
	INSERT INTO db_version VALUES (4, 4, "added columns lag_time and retention to table subbasins", "", "subbasins", "", "2.8.2006");
ver. 5
	#defined primary key for table horizon to improve data integrity
	ALTER TABLE horizons ADD PRIMARY KEY (soil_id,position)
	#if this statement produces an error, you have multiple horizons for one soil at one position ( change this) or you have to remove the attribute 'primary key' from column 'pid', table horizons first (manually)

	#add view/function, used for transferring the coarse_frac data from the topmost horizons to the table soil_veg_components
	#m_update_coarse_fraction_svc_from_horizon:
	#UPDATE soil_veg_components LEFT JOIN [select * from horizons where position=1]. AS hor ON soil_veg_components.soil_id=hor.soil_id SET soil_veg_components.coarse_frac = hor.coarse_frag;
	INSERT INTO db_version VALUES (5, 5, "defined primary key for table horizon; view/function m_update_coarse_fraction_svc_from_horizon", "", "horizon", "computationally irrelevant", "3.8.2006");
	
ver. 6
	#add columns c_musle_c and c_mannings_n to table vegetation to allow specification of both in vegetation table
	alter table vegetation add c_musle_c float,c_manning_n float;
	#add column description to vegetation.c_musle_c "(optional for erosion modelling) transferred to soil_veg_components if m_copy_c_factor is called"
	#add column description to vegetation.c_manning_n "(optional for erosion modelling) transferred to soil_veg_components if m_copy_mannings_n is called"

	#add view/function, used for transferring the c-factor/manning's n from table vegetation to table soil_veg_components
	#m_update_c_n_in_svc_from_vegetation:
	#UPDATE soil_veg_components LEFT JOIN vegetation ON soil_veg_components.veg_id=vegetation.pid SET soil_veg_components.musle_c = vegetation.c_musle_c, soil_veg_components.manning_n = vegetation.c_manning_n;
	INSERT INTO db_version VALUES (6, 6, "added c_musle_c,c_manning_n to vegetation; view/function m_update_c_n_in_svc_from_vegetation", "", "vegetation", "computationally irrelevant", "4.8.2006");

ver. 7	
	#computationally irrelevant, created table meta_info to store meta infomation
	create table meta_info( pid autoincrement not null primary key, mod_date date, user varchar(50), affected_tables varchar(255), affected_columns varchar(255), remarks varchar(255))
	#add column description "automatic counter, can be used as data-version number"
	#add column description "date of modification"
	#add column description "modified by"
	#add column description "modified tables"
	#add column description "modified columns"
	#add column description "remarks"
	INSERT INTO db_version VALUES (7, 7, "created table meta_info to store meta infomation", "meta_info", "", "computationally irrelevant", "7.8.2006");
	
ver. 8
	#computationally irrelevant, cleaned and renamed query collection
	#delete all queries/views from current database, copy all queries/views from ver. 8 template
	INSERT INTO db_version VALUES (8, 8, "cleaned and renamed query collection", "", "", "computationally irrelevant", "17.1.2007");

ver. 9
	#computationally irrelevant, clarified column description of tc-position
	#change column description of r_lu_contains_tc.position to "hillslope position of TC in LU (counting from hillslope bottom)"
	INSERT INTO db_version VALUES (9, 9, "clarified column descrition of tc-position", "", "r_lu_contains_tc", "computationally irrelevant", "08.05.2007");

ver. 10
	#computationally irrelevant, added multiple views for db clean-up
	#add views/functions manually or copy from version10-database template
	#add view/function: finding not-used SVCs
	#m_find_svcs_without_tcs:
	#SELECT r_tc_contains_svc.svc_id, terrain_components.pid FROM r_tc_contains_svc LEFT JOIN terrain_components ON r_tc_contains_svc.tc_id=terrain_components.pid WHERE terrain_components.pid IS NULL;
	#add view/function: delete not-used SVCs (1)
	#m_delete_svcs_without_tcs1
	#DELETE * FROM r_tc_contains_svc WHERE tc_id NOT IN(SELECT pid FROM terrain_components);
	#add view/function: delete not-used SVCs (2)
	#m_delete_svcs_without_tcs2
	#DELETE * FROM soil_veg_components WHERE pid NOT IN(SELECT svc_id FROM r_tc_contains_svc);
	#add view/function: finding not-used soils
	#m_find_soils_without_svcs
	#SELECT pid FROM soils WHERE pid not in (select soil_id from soil_veg_components);
	#add view/function: delete not-used soils
	#m_delete_soils_without_svcs
	#DELETE * FROM soils WHERE pid not in (select soil_id from soil_veg_components);
	#add view/function: finding not-used vegetation
	#m_find_vegetation_without_svc
	#SELECT pid FROM vegetation WHERE pid not in (select veg_id from soil_veg_components);
	#add view/function: delete not-used vegetation 
	#m_delete_vegetation_without_svc
	#DELETE * FROM vegetation WHERE pid not in (select veg_id from soil_veg_components);
	INSERT INTO db_version VALUES (10, 10, "added multiple views for db clean-up", "", "", "computationally irrelevant", "17.05.2007");
	
ver. 11
	#computationally irrelevant, added/revise multiple views for db clean-up
	#add views/functions manually or copy from template of this db-version
	#remove view/function m_find_tcs_of_irrelevant_lus
	#add view/function: find TCs not belonging to any LU
	#m_find_tcs_without_lus
	#SELECT landscape_units.pid, r_lu_contains_tc.lu_id FROM landscape_units LEFT JOIN r_lu_contains_tc ON landscape_units.pid=r_lu_contains_tc.lu_id WHERE r_lu_contains_tc.lu_id is NULL;
	#add view/function: find TCs without SVCs
	#m_find_tcs_without_svcs	
	#SELECT terrain_components.pid FROM terrain_components LEFT JOIN r_tc_contains_svc ON r_tc_contains_svc.tc_id=terrain_components.pid WHERE r_tc_contains_svc.tc_id IS NULL;
	INSERT INTO db_version
	VALUES (11, 11, "added/revised multiple views for db clean-up", "", "", "computationally irrelevant", "28.03.2008");
ver. 12
	#computationally irrelevant, added multiple views for consistency checks
	#add views/functions manually or copy from template of this db-version
	#add view/function: find referenced but unspecified LUs
	#m_find_unspecified_lus
	#SELECT DISTINCT r_subbas_contains_lu.lu_id FROM r_subbas_contains_lu LEFT JOIN landscape_units ON landscape_units.pid=r_subbas_contains_lu.lu_id WHERE landscape_units.pid is null;
    	#add view/function: find referenced but unspecified TCs
	#m_find_unspecified_tcs
	#SELECT DISTINCT r_lu_contains_tc.tc_id FROM r_lu_contains_tc LEFT JOIN terrain_components ON terrain_components.pid=r_lu_contains_tc.tc_id WHERE terrain_components.pid is null;
	#add view/function: find referenced but unspecified SVCs
	#m_find_unspecified_scvs
	#SELECT DISTINCT r_tc_contains_svc.svc_id FROM r_tc_contains_svc LEFT JOIN soil_veg_components ON soil_veg_components.pid=r_tc_contains_svc.svc_id WHERE soil_veg_components.pid is null;
	#add view/function: find referenced but unspecified soils
	#m_find_unspecified_soils
	#SELECT DISTINCT soil_id FROM soil_veg_components LEFT JOIN soils ON soil_veg_components.soil_id=soils.pid WHERE soils.pid is null;
	#add view/function: find referenced but unspecified vegetation
	#m_find_unspecified_vegetation
	#SELECT DISTINCT veg_id FROM soil_veg_components LEFT JOIN vegetation ON soil_veg_components.veg_id=vegetation.pid WHERE vegetation.pid is null;
	#add view/function: find soils without horizons
	#m_find_soils_without_horizons
	#SELECT DISTINCT soil_id FROM soils LEFT JOIN horizons ON horizons.soil_id=soils.pid WHERE horizons.soil_id is null;
	
	#add view/function: delete unneeded LUs
	#m_delete_lus_not_in_any_subbasin
	#DELETE * FROM landscape_units WHERE (landscape_units.pid IN (select * from m_find_lus_not_in_any_subbasin));
	#add view/function: delete unneeded TCs
	#m_delete_tcs_without_lus
	#DELETE * FROM terrain_components WHERE (terrain_components.pid IN (select * from m_find_tcs_without_lus));
	INSERT INTO db_version VALUES (12, 12, "added multiple views for consistency checks", "", "", "computationally irrelevant", "11.06.2008");
ver. 13
	#computationally relevant, when used: added queries for estimating the groundwater parameter frgw_delay
	#add views/functions manually or copy from template of this db-version
	#add view/function: compute average slope of LUs
	#aux_d_lu_slope
	#SELECT lu_id, Sum(r_lu_contains_tc.fraction*slope/100) AS slope_lu FROM r_lu_contains_tc INNER JOIN terrain_components ON r_lu_contains_tc.tc_id=terrain_components.pid GROUP BY lu_id;
	#add view/function: compute rocky fraction of TCs based on soils with coarse_frag=1
	#aux_d_tc_frac_rocky
	#SELECT r_tc_contains_svc.tc_id, sum(abs(r_tc_contains_svc.fraction*(horizons.position=1 And horizons.coarse_frag=1))) AS frac_rocky_tc FROM (r_tc_contains_svc INNER JOIN soil_veg_components ON r_tc_contains_svc.svc_id=soil_veg_components.pid) INNER JOIN horizons ON soil_veg_components.soil_id=horizons.soil_id GROUP BY tc_id;
	#add view/function: compute rocky fraction of LUs based on those of the TCs 
	#aux_d_lu_frac_rocky
	#SELECT landscape_units.pid AS lu_id, Sum(r_lu_contains_tc.fraction*aux_d_tc_frac_rocky.frac_rocky_tc) AS frac_rocky FROM (landscape_units LEFT JOIN r_lu_contains_tc ON landscape_units.pid=r_lu_contains_tc.lu_id) LEFT JOIN aux_d_tc_frac_rocky ON r_lu_contains_tc.tc_id=aux_d_tc_frac_rocky.tc_id GROUP BY landscape_units.pid;
	#add view/function: summarize subfactors and compute proxy for the parameter frgw_delay
	#m_d0_show_proxy_frgw_delay
	#SELECT landscape_units.pid AS lu_id, landscape_units.lenght, aux_d_lu_frac_rocky.frac_rocky, aux_d_lu_slope.slope_lu, lenght*(1-frac_rocky)/slope_lu AS proxy_frgw_delay FROM (landscape_units INNER JOIN aux_d_lu_frac_rocky ON landscape_units.pid=aux_d_lu_frac_rocky.lu_id) INNER JOIN aux_d_lu_slope ON aux_d_lu_frac_rocky.lu_id=aux_d_lu_slope.lu_id;	
	#add view/function: copy m_d0_show_proxy_frgw_delay to temp_table (clumsy, but necessary, sorry)
	#m_d1_make_temp_table
	#SELECT (select avg(proxy_frgw_delay) from m_d0_show_proxy_frgw_delay AS avg_proxy) AS avg_proxy, * INTO temp_table FROM m_d0_show_proxy_frgw_delay;
	#add view/function: use estimates generated in m_d0_show_proxy_frgw_delay and transfer them to table landscape_units
	#m_d2_set_proxy_gw_delay
	#UPDATE landscape_units LEFT JOIN temp_table ON landscape_units.pid=temp_table.lu_id SET landscape_units.frgw_delay = proxy_frgw_delay/avg_proxy*mean_gw_delay;
	#change type of max_suction in vegetation to double

	MS Access: ALTER TABLE vegetation ALTER COLUMN max_suction double
	MySQL:     alter table vegetation modify max_suction,max_suction double
	INSERT INTO db_version VALUES (13, 13, "added views to estimate frgw_delay, changed type of max_suction in vegetation to double", "", "", "relevant, when used", "20.08.2008 16:02:14");

ver. 14
	#additional option for specifying seasonality in C-factor
	#rename column: 
	alter table vegetation change c_musle_c float,c_musle_c1 float
	alter table vegetation add c_musle_c2 float,c_musle_c3 float,c_musle_c4 float
	#add column description "(optional for erosion modelling) C-factor seasonality (leave empty for invariant value), transferred to soil_veg_components if m_copy_c_factor is called" to columns c_musle_c(2-4); (rearrange column order, if desired)
	
	#rename column: 
	alter table soil_veg_components change musle_c float,musle_c1 float
	alter table soil_veg_components add musle_c2 float,musle_c3 float,musle_c4 float
	#add column description "MUSLE C-factor (seasonality, leave empty for invariant value)" to columns musle_c(2-4); (rearrange column order, if desired)
	
	#add view/function: copy C-factors from table vegetation to table soil_veg_components
	#m_copy_c_factor
	#UPDATE soil_veg_components INNER JOIN vegetation ON vegetation.pid=soil_veg_components.veg_id SET musle_c1 = c_musle_c1, musle_c2 = c_musle_c2, musle_c3 = c_musle_c3, musle_c4 = c_musle_c4;

	INSERT INTO db_version VALUES (14, 14, "additional option for specifying seasonality in C-factor", "", "soil_veg_components,vegetation", "relevant, when used", "26.09.2008 11:18:14");

ver. 15
	#add optional columns for beta_fac and SDR to terrain_components:
	alter table terrain_components add beta_fac float, sdr float
	#add column description "(optional for erosion modelling) ratio of rill/interrill erosion (computation of the L-factor see Renard et al., 1997, pp.101)" to column beta_fac
	#add column description "(optional for erosion modelling) sediment delivery ratio on TC-scale" to column sdr

	#add optional columns for SDR to landscape_units:
	alter table landscape_units add sdr_lu float
	#add column description "(optional for erosion modelling) sediment delivery ratio on TC-scale" to column sdr_lu
	#modify description of column gw_flag to  "Flag for landscape unit [0: no groundwater, 1: with groundwater]"
	
	INSERT INTO db_version VALUES (15, 15, "optional columns for beta_fac and SDR ", "", "terrain_components, landscape_units", "relevant, when used", NOW());
	INSERT INTO meta_info ( mod_date, [user], affected_tables, affected_columns, remarks ) VALUES (NOW(), "yourname", "terrain_components, landscape_units", "beta_fac, sdr_lu, sdr", "updated to dbversion 15");

ver. 16
	#alter queries:
	#m_delete_lus_not_in_any_subbasin
	#DELETE * FROM landscape_units WHERE (landscape_units.pid IN (select pid from m_find_lus_not_in_any_subbasin));

	#m_find_svcs_without_tcs
	SELECT soil_veg_components.pid FROM soil_veg_components LEFT JOIN r_tc_contains_svc ON r_tc_contains_svc.svc_id=soil_veg_components.pid WHERE r_tc_contains_svc.tc_id IS NULL;

	#m_delete_svcs_without_tcs2
	DELETE * FROM soil_veg_components WHERE pid NOT IN(SELECT svc_id FROM r_tc_contains_svc);

	#m_copy_musle_K_from_soils_2_svc
	UPDATE soil_veg_components LEFT JOIN vegetation ON soil_veg_components.veg_id=vegetation.pid SET soil_veg_components.manning_n = vegetation.c_manning_n, musle_c1 = c_musle_c1, musle_c2 = c_musle_c2, musle_c3 = c_musle_c3, musle_c4 = c_musle_c4;
	
	#m_update_coarse_fraction_svc_from_horizon
	UPDATE soil_veg_components LEFT JOIN (SELECT * FROM horizons WHERE position=1)  AS hor ON soil_veg_components.soil_id=hor.soil_id SET soil_veg_components.coarse_frac = 100*hor.coarse_frag;
	
	#create queries:
	#m_f0_check_fractions_tcs_in_lus
	#SELECT lu_id, sum(fraction) AS summ  INTO temp_table FROM terrain_components INNER JOIN r_lu_contains_tc ON r_lu_contains_tc.tc_id=terrain_components.pid GROUP BY lu_id ORDER BY lu_id;

	#m_f1_normalise_fractions_tcs_in_lus
	#UPDATE r_lu_contains_tc LEFT JOIN temp_table ON r_lu_contains_tc.lu_id=temp_table.lu_id SET fraction = fraction/summ;
	
	#m_delete_unspecified_svcs
	DELETE * FROM r_tc_contains_svc WHERE svc_id in (select * from m_find_unspecified_svcs);
	
	#delete queries
	n_check_sum_tcs_in_lus
	m_copy_c_factor
	
	INSERT INTO db_version VALUES (16, 16, "corrected some queries ", "", "-", "", NOW());
	INSERT INTO meta_info ( mod_date, [user], affected_tables, affected_columns, remarks ) VALUES (NOW(), "yourname", "queries", "", "updated to dbversion 16");


	
	new:
	#m_find_particle_not_in_any_soil
	SELECT r_soil_contains_particles.soil_id
	FROM r_soil_contains_particles LEFT JOIN soils ON r_soil_contains_particles.soil_id=soils.pid
	WHERE soils.pid is NULL;

	#m_delete_particle_not_in_any_soil
	DELETE *
	FROM r_soil_contains_particles 
	WHERE r_soil_contains_particles.soil_id IN (select soil_id from m_find_particle_not_in_any_soil)

	#m_delete_tcs_without_lus2
	DELETE * FROM r_tc_contains_svc
	WHERE (r_tc_contains_svc.tc_id NOT IN (select pid as tc_id from terrain_components));

	
	alter
	#m_find_lus_without_tcs
	SELECT landscape_units.pid, r_lu_contains_tc.lu_id
	FROM landscape_units LEFT JOIN r_lu_contains_tc ON landscape_units.pid=r_lu_contains_tc.lu_id
	WHERE r_lu_contains_tc.lu_id is NULL;

	#m_find_tcs_without_lus
	SELECT terrain_components.pid FROM terrain_components WHERE pid NOT IN(SELECT DISTINCT tc_id as pid from r_lu_contains_tc);

ver. 17
	create table rainy_seasons(id autoincrement not null primary key, subbas_id not null integer, veg_id not null integer, yearm not null integer, node1 integer, node2 integer, node3 integer, node4 integer)
	#add column description "row counter"
	#add column description "foreign key to subbas (use -1 as wildcard)"
	#add column description "foreign key vegetation (use -1 as wildcard)"
	#add column description "year (use -1 as wildcard)"
	#add column description "1. node in cycle (as DOY/julian day)"
	#add column description "2. node in cycle (as DOY/julian day)"
	#add column description "3. node in cycle (as DOY/julian day)"
	#add column description "4. node in cycle (as DOY/julian day)"

	create table x_seasons(id autoincrement not null primary key, parameter varchar(20), subbas_id not null integer, veg_id not null integer, yearm not null integer, node1 integer, node2 integer, node3 integer, node4 integer)
	#add column description "row counter"
	#add column description "K: K-factor, C: C-factor, P: P-factor, coarse: coarse-fraction, n : Manning's n"
	#add column description "foreign key to subbas (use -1 as wildcard)"
	#add column description "foreign key vegetation (use -1 as wildcard)"
	#add column description "year (use -1 as wildcard)"
	#add column description "1. node in cycle (as DOY/julian day)"
	#add column description "2. node in cycle (as DOY/julian day)"
	#add column description "3. node in cycle (as DOY/julian day)"
	#add column description "4. node in cycle (as DOY/julian day)"

	add new views:
	#m_g0_svcs_with_same_veg_id	
	SELECT x_seasons.svc_id, soil_veg_components_1.pid
	FROM (x_seasons LEFT JOIN soil_veg_components ON x_seasons.svc_id=soil_veg_components.pid) LEFT JOIN soil_veg_components AS soil_veg_components_1 ON soil_veg_components.veg_id=soil_veg_components_1.veg_id;

	#m_g1_complete_x_seasons
	SELECT min(x_seasons.id) AS rid, x_seasons.parameter, x_seasons.subbas_id, [m_g0_svcs_with_same_veg_id].pid AS svc_id, x_seasons.yearm, x_seasons.node1, x_seasons.node2, x_seasons.node3, x_seasons.node4 INTO temp_table
	FROM x_seasons INNER JOIN m_g0_svcs_with_same_veg_id ON x_seasons.svc_id=[m_g0_svcs_with_same_veg_id].svc_id
	GROUP BY x_seasons.parameter, x_seasons.subbas_id, [m_g0_svcs_with_same_veg_id].pid, x_seasons.yearm, x_seasons.node1, x_seasons.node2, x_seasons.node3, x_seasons.node4
	ORDER BY rid;

	#m_g2_replace_m1
	UPDATE temp_table SET svc_id = -1 WHERE svc_id IS NULL;

	#m_g3_empty_x_seasons
	DELETE * FROM x_seasons;

	#m_g4_copy_temp_table
	INSERT INTO x_seasons
	SELECT parameter AS parameter, subbas_id AS subbas_id, svc_id AS svc_id, yearm AS yearm, node1 AS node1, node2 AS node2, node3 AS node3, node4 AS node4
	FROM temp_table ORDER BY rid;

	INSERT INTO db_version VALUES (17, 17, "added seasonality tables", "rainy_seasons, x_seasons", "", "", "20.11.2014 15:31:35");

ver 18
	alter table landscape_units change lenght float,length float
	alter table soil_veg_components add `special_area`, INT DEFAULT 0 COMMENT 'Flag for special areas: 1: water, 2: impervious, 0: ordinary SVC.'
	alter table horizons change depth float,thickness float
	
	add new views:
	#m_c1.1_remove_water_svcs
	DELETE r_tc_contains_svc FROM r_tc_contains_svc LEFT JOIN soil_veg_components ON (r_tc_contains_svc.svc_id=soil_veg_components.pid) WHERE special_area=1;
	
	#aux_d_tc_frac_rocky
	SELECT r_tc_contains_svc.tc_id, sum(abs(r_tc_contains_svc.fraction*(horizons.coarse_frag=1))) AS frac_coarse_soil,
	sum(abs(r_tc_contains_svc.fraction*(soil_veg_components.special_area=2))) AS frac_impervious_tc FROM (r_tc_contains_svc INNER JOIN soil_veg_components ON r_tc_contains_svc.svc_id=soil_veg_components.pid) 
	INNER JOIN horizons ON soil_veg_components.soil_id=horizons.soil_id WHERE horizons.position=1 GROUP BY tc_id;

	#m_c1_2_write_frac_rocky_2_TCs 
	UPDATE terrain_components LEFT JOIN aux_d_tc_frac_rocky	ON terrain_components.pid=aux_d_tc_frac_rocky.tc_id
	SET frac_rocky=frac_coarse_soil+frac_impervious_tc;

	#m_c1_3_remove_impervious_svcs 
	DELETE r_tc_contains_svc.*
	FROM (r_tc_contains_svc INNER JOIN soil_veg_components ON r_tc_contains_svc.svc_id=soil_veg_components.pid) INNER JOIN horizons ON soil_veg_components.soil_id=horizons.soil_id
	WHERE (horizons.position=1 AND horizons.coarse_frag=1) OR soil_veg_components.special_area=2;

	INSERT INTO db_version VALUES (18, 18, 'added column special_area to soil_veg_components; added queries to include impervious areas as frac_rocky in terrain_components and remove impervious SVC; renamed columns', '', 'soil_veg_components, terrain_components, r_tc_contains_svc,landscape_units, horizons', 'relevant, when used', '2015-01-19 00:00:00')
	