<commit_before><database name="home8">

	<table name="places">
		<column name="id" type="uuid" not-null="yes" />
		<column name="name" type="varchar" size="250" />

		<primary-key ref="id" />
	</table>

	<table name="gateways">
		<column name="id" type="int64" not-null="yes" />
		<column name="name" type="varchar" size="250" />
		<column name="place_id" type="uuid" />
		<column name="altitude" type="double" />
		<column name="latitude" type="double" />
		<column name="longitude" type="double" />

		<primary-key ref="id" />
		<foreign-key table="places" local="place_id" remote="id" />
	</table>

	<table name="locations">
		<column name="id" type="uuid" not-null="yes" />
		<column name="name" type="varchar" size="250" />
		<column name="place_id" type="uuid" />

		<primary-key ref="id" />
		<foreign-key table="places" local="place_id" remote="id" />
	</table>

	<table name="users">
		<column name="id" type="uuid" not-null="yes" />
		<column name="first_name" type="varchar" size="250" not-null="yes" />
		<column name="last_name" type="varchar" size="250" not-null="yes" />

		<primary-key ref="id" />
	</table>

	<table name="identities">
		<column name="id" type="uuid" not-null="yes" />
		<column name="email" type="varchar" size="250" not-null="yes" />

		<primary-key ref="id" />
		<unique name="unique_identity" ref="email" />
	</table>

	<table name="verified_identities">
		<column name="id" type="uuid" not-null="yes" />
		<column name="identity_id" type="uuid" not-null="yes" />
		<column name="user_id" type="uuid" not-null="yes" />
		<column name="provider" type="varchar" size="250" not-null="yes" />
		<column name="picture" type="varchar" size="250" />
		<column name="access_token" type="varchar" size="250" />

		<primary-key ref="id" />
		<foreign-key table="identities" local="identity_id" remote="id" />
		<foreign-key table="users" local="user_id" remote="id" />

		<unique name="unique_verified_identity">
			<ref>identity_id</ref>
			<ref>provider</ref>
		</unique>
	</table>

	<table name="roles_in_place">
		<column name="id" type="uuid" not-null="yes" />
		<column name="place_id" type="uuid" not-null="yes" />
		<column name="identity_id" type="uuid" not-null="yes" />
		<column name="level" type="smallint" not-null="yes" />
		<column name="created" type="bigint" not-null="yes" />

		<primary-key ref="id" />
		<foreign-key table="places" local="place_id" remote="id" />
		<foreign-key table="identities" local="identity_id" remote="id" />

		<check name="check_valid_level" expression="level &gt;= 0 AND level &lt;= 100" />
	</table>

	<table name="devices">
		<column name="id" type="int64" not-null="yes" />
		<column name="gateway_id" type="int64" not-null="yes" />
		<column name="location_id" type="uuid" />
		<column name="name" type="varchar" size="250" not-null="yes" />
		<column name="type" type="smallint" not-null="yes" />
		<column name="refresh" type="integer" default="10" not-null="yes" />
		<column name="battery" type="smallint" />
		<column name="signal" type="smallint" />
		<column name="first_seen" type="bigint" not-null="yes" />
		<column name="last_seen" type="bigint" not-null="yes" />
		<column name="active_since" type="bigint" />

		<primary-key>
			<ref>id</ref>
			<ref>gateway_id</ref>
		</primary-key>

		<foreign-key table="gateways" local="gateway_id" remote="id" />
		<foreign-key table="locations" local="location_id" remote="id" />

		<check name="check_id_positive" expression="id &gt;= 0" />
		<check name="check_seen_valid" expression="first_seen &lt;= last_seen" />
		<check name="check_active_valid"
			expression="active_since IS NULL OR first_seen &lt;= active_since" />
		<check name="check_battery_valid"
			expression="battery IS NULL OR (battery &gt;= 0 AND battery &lt;= 100)" />
		<check name="check_singal_valid"
			expression="signal IS NULL OR (signal &gt;= 0 AND signal &lt;= 100)" />
	</table>

</database>
<commit_msg>schema: define views roles_in_gateway and legacy_gateways<commit_after><database name="home8">

	<table name="places">
		<column name="id" type="uuid" not-null="yes" />
		<column name="name" type="varchar" size="250" />

		<primary-key ref="id" />
	</table>

	<table name="gateways">
		<column name="id" type="int64" not-null="yes" />
		<column name="name" type="varchar" size="250" />
		<column name="place_id" type="uuid" />
		<column name="altitude" type="double" />
		<column name="latitude" type="double" />
		<column name="longitude" type="double" />

		<primary-key ref="id" />
		<foreign-key table="places" local="place_id" remote="id" />
	</table>

	<view name="users_of_gateway" base="gateways">
		<map ref="id" as="gateway_id" />
		<join remote="roles_in_place" on-base="place_id" on-remote="place_id">
			<map ref="id" as="role_id" />
			<map ref="identity_id" as="identity_id" />
			<map ref="created" as="created" />
			<map ref="level" as="level" />
		</join>
		<join remote="verified_identities" with-base="roles_in_place"
				on-base="identity_id" on-remote="identity_id">
			<map ref="user_id" as="user_id" />
		</join>
	</view>

	<view name="legacy_gateways" base="gateways">
		<map ref="id" as="id" />
		<map ref="name" as="name" />
		<map ref="place_id" as="place_id" />
		<map ref="altitude" as="altitude" />
		<map ref="latitude" as="latitude" />
		<map ref="longitude" as="longitude" />

		<sub-query as="roles_count">
			<sql>SELECT COUNT(*) FROM roles_in_place WHERE
				roles_in_place.place_id = gateways.place_id</sql>
		</sub-query>

		<sub-query as="devices_count">
			<sql>SELECT COUNT(*) FROM devices WHERE
				devices.gateway_id = gateways.id
				AND
				devices.active_since IS NOT NULL</sql>
		</sub-query>

		<sub-query as="owner_id">
			<sql>SELECT user_id FROM verified_identities
				JOIN roles_in_place ON
					roles_in_place.identity_id = verified_identities.identity_id
				WHERE
					roles_in_place.level &lt;= 10
					AND
					roles_in_place.place_id = gateways.place_id
				ORDER BY
					roles_in_place.created ASC
				LIMIT 1</sql>
		</sub-query>
	</view>

	<table name="locations">
		<column name="id" type="uuid" not-null="yes" />
		<column name="name" type="varchar" size="250" />
		<column name="place_id" type="uuid" />

		<primary-key ref="id" />
		<foreign-key table="places" local="place_id" remote="id" />
	</table>

	<table name="users">
		<column name="id" type="uuid" not-null="yes" />
		<column name="first_name" type="varchar" size="250" not-null="yes" />
		<column name="last_name" type="varchar" size="250" not-null="yes" />

		<primary-key ref="id" />
	</table>

	<table name="identities">
		<column name="id" type="uuid" not-null="yes" />
		<column name="email" type="varchar" size="250" not-null="yes" />

		<primary-key ref="id" />
		<unique name="unique_identity" ref="email" />
	</table>

	<table name="verified_identities">
		<column name="id" type="uuid" not-null="yes" />
		<column name="identity_id" type="uuid" not-null="yes" />
		<column name="user_id" type="uuid" not-null="yes" />
		<column name="provider" type="varchar" size="250" not-null="yes" />
		<column name="picture" type="varchar" size="250" />
		<column name="access_token" type="varchar" size="250" />

		<primary-key ref="id" />
		<foreign-key table="identities" local="identity_id" remote="id" />
		<foreign-key table="users" local="user_id" remote="id" />

		<unique name="unique_verified_identity">
			<ref>identity_id</ref>
			<ref>provider</ref>
		</unique>
	</table>

	<table name="roles_in_place">
		<column name="id" type="uuid" not-null="yes" />
		<column name="place_id" type="uuid" not-null="yes" />
		<column name="identity_id" type="uuid" not-null="yes" />
		<column name="level" type="smallint" not-null="yes" />
		<column name="created" type="bigint" not-null="yes" />

		<primary-key ref="id" />
		<foreign-key table="places" local="place_id" remote="id" />
		<foreign-key table="identities" local="identity_id" remote="id" />

		<check name="check_valid_level" expression="level &gt;= 0 AND level &lt;= 100" />
	</table>

	<table name="devices">
		<column name="id" type="int64" not-null="yes" />
		<column name="gateway_id" type="int64" not-null="yes" />
		<column name="location_id" type="uuid" />
		<column name="name" type="varchar" size="250" not-null="yes" />
		<column name="type" type="smallint" not-null="yes" />
		<column name="refresh" type="integer" default="10" not-null="yes" />
		<column name="battery" type="smallint" />
		<column name="signal" type="smallint" />
		<column name="first_seen" type="bigint" not-null="yes" />
		<column name="last_seen" type="bigint" not-null="yes" />
		<column name="active_since" type="bigint" />

		<primary-key>
			<ref>id</ref>
			<ref>gateway_id</ref>
		</primary-key>

		<foreign-key table="gateways" local="gateway_id" remote="id" />
		<foreign-key table="locations" local="location_id" remote="id" />

		<check name="check_id_positive" expression="id &gt;= 0" />
		<check name="check_seen_valid" expression="first_seen &lt;= last_seen" />
		<check name="check_active_valid"
			expression="active_since IS NULL OR first_seen &lt;= active_since" />
		<check name="check_battery_valid"
			expression="battery IS NULL OR (battery &gt;= 0 AND battery &lt;= 100)" />
		<check name="check_singal_valid"
			expression="signal IS NULL OR (signal &gt;= 0 AND signal &lt;= 100)" />
	</table>

</database>

{'fraction_non_alphanumeric': 0.2029389681433015, 'fraction_numerical': 0.011476992384425615, 'mean_word_length': 4.831144465290807, 'pattern_counts': {'":': 0, '<': 215, '<?xml version=': 0, '>': 215, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4003082', 'n_tokens_mistral': 3745, 'n_tokens_neox': 3373, 'n_words': 686}