<commit_before><?php
require_once(dirname(__FILE__) . '/config/database.php');
global $drupalprefix;
global $config;

$table_o = $drupalprefix . 'organism';
$table_osy = $drupalprefix . 'organism_synonym';
$table_ol = $drupalprefix . 'organism_lang';
$table_oa = $drupalprefix . 'organism_artgroup';
$table_oas = $drupalprefix . 'organism_artgroup_subscription';

$web_db = pg_connect(
	'host=' . $config['naturvielfalt_dev']['host'] . ' port='
			. $config['naturvielfalt_dev']['port'] . ' dbname='
			. $config['naturvielfalt_dev']['name'] . ' user='
			. $config['naturvielfalt_dev']['user'] . ' password='
			. $config['naturvielfalt_dev']['password']);
$file = 'iPhone_DB.sql';

$sql_create_iphone_db_structur = '
	DROP TABLE IF EXISTS classification;
	CREATE TABLE classification (
	-- Artgroup id
		classification_id integer PRIMARY KEY,
	-- something classified? :P (NOT NULL removed, standart set to 1)
		class_level integer,
	-- parent artgroup id
		parent integer NOT NULL,
		name_de character varying(60),
		name_fr character varying(50),
		name_it character varying(50),
		name_en character varying(50),
		classification_type_id integer,
		"position" integer
	);

	DROP TABLE IF EXISTS observation;
	CREATE TABLE observation (
		ID INTEGER PRIMARY KEY AUTOINCREMENT,
		ORGANISM_ID INTEGER,
		ORGANISMGROUP_ID INTEGER,
		ORGANISM_NAME TEXT,
		ORGANISM_NAME_LAT TEXT,
		ORGANISM_FAMILY TEXT,
		AUTHOR TEXT,
		DATE TEXT,
		AMOUNT INTEGER,
		LOCATION_LAT REAL,
		LOCATION_LON REAL,
		ACCURACY INTEGER,
		COMMENT TEXT,
		IMAGE BLOB
	);

	DROP TABLE IF EXISTS organism;
	CREATE TABLE organism (
	-- entry id
	--	id bigint PRIMARY KEY,
	-- the original organsim id
		organism_id bigint,
	-- flora or group or something other, not used on the new system (NOT NULL removed)
		organism_type bigint,
	-- organism id
	--	organism_id bigint NOT NULL,
	-- artgroup?
		inventory_type_id bigint,
	-- translations
		name_de character varying(80),
		name_fr character varying(100),
		name_it character varying(80),
	-- original name
		name_sc character varying(100),
		name_rm character varying(80),
		name_en character varying(80)
	);

	DROP TABLE IF EXISTS classification_taxon;
	CREATE TABLE classification_taxon (
		classification_taxon_id INTEGER PRIMARY KEY,
	-- artgroup id
		classification_id INTEGER REFERENCES classification,
	-- organismus id
		taxon_id bigint INTEGER REFERENCES organism,
		display_level integer
	);

	CREATE INDEX idx_classification_idx1 ON classification(parent);
	CREATE INDEX idx_classification_idx2 ON classification(class_level);
	CREATE INDEX idx_classification_taxon_idx1 ON classification_taxon(classification_id);
	CREATE INDEX idx_classification_taxon_idx2 ON classification_taxon(taxon_id);';

$query_all_organism = "
	-- Alle Organismen, der richtige wissenschaftliche Namen und die Uebersetzung selektieren
	SELECT
		o.id organism_id,
		o.scientific_name name_sc,
		ol_de.name name_de,
		ol_fr.name name_fr,
		ol_it.name name_it,
		ol_rm.name name_rm,
		ol_en.name name_en

	FROM $table_o o
		LEFT JOIN $table_ol ol_de ON ol_de.languages_language='de' AND ol_de.organism_id=o.id
		LEFT JOIN $table_ol ol_fr ON ol_fr.languages_language='fe' AND ol_fr.organism_id=o.id
		LEFT JOIN $table_ol ol_it ON ol_it.languages_language='it' AND ol_it.organism_id=o.id
		LEFT JOIN $table_ol ol_rm ON ol_rm.languages_language='rm' AND ol_rm.organism_id=o.id
		LEFT JOIN $table_ol ol_en ON ol_en.languages_language='en' AND ol_en.organism_id=o.id

	-- Alle Synonyme hinzuholen
	UNION SELECT
		osy.organism_id organism_id,
		osy.name name_sc,
		ol_de.name name_de,
		ol_fr.name name_fr,
		ol_it.name name_it,
		ol_rm.name name_rm,
		ol_en.name name_en

	FROM $table_osy o
		LEFT JOIN $table_ol ol_de ON ol_de.languages_language='de' AND ol_de.organism_id=osy.organism_id<commit_msg>small typo<commit_after><?php
require_once(dirname(__FILE__) . '/config/database.php');
global $drupalprefix;
global $config;

$table_o = $drupalprefix . 'organism';
$table_osy = $drupalprefix . 'organism_synonym';
$table_ol = $drupalprefix . 'organism_lang';
$table_oa = $drupalprefix . 'organism_artgroup';
$table_oas = $drupalprefix . 'organism_artgroup_subscription';

$web_db = pg_connect(
	'host=' . $config['naturvielfalt_dev']['host'] . ' port='
			. $config['naturvielfalt_dev']['port'] . ' dbname='
			. $config['naturvielfalt_dev']['name'] . ' user='
			. $config['naturvielfalt_dev']['user'] . ' password='
			. $config['naturvielfalt_dev']['password']);
$file = 'iPhone_DB.sql';

$sql_create_iphone_db_structur = '
	DROP TABLE IF EXISTS classification;
	CREATE TABLE classification (
	-- Artgroup id
		classification_id integer PRIMARY KEY,
	-- something classified? :P (NOT NULL removed, standart set to 1)
		class_level integer,
	-- parent artgroup id
		parent integer NOT NULL,
		name_de character varying(60),
		name_fr character varying(50),
		name_it character varying(50),
		name_en character varying(50),
		classification_type_id integer,
		"position" integer
	);

	DROP TABLE IF EXISTS observation;
	CREATE TABLE observation (
		ID INTEGER PRIMARY KEY AUTOINCREMENT,
		ORGANISM_ID INTEGER,
		ORGANISMGROUP_ID INTEGER,
		ORGANISM_NAME TEXT,
		ORGANISM_NAME_LAT TEXT,
		ORGANISM_FAMILY TEXT,
		AUTHOR TEXT,
		DATE TEXT,
		AMOUNT INTEGER,
		LOCATION_LAT REAL,
		LOCATION_LON REAL,
		ACCURACY INTEGER,
		COMMENT TEXT,
		IMAGE BLOB
	);

	DROP TABLE IF EXISTS organism;
	CREATE TABLE organism (
	-- entry id
	--	id bigint PRIMARY KEY,
	-- the original organsim id
		organism_id bigint,
	-- flora or group or something other, not used on the new system (NOT NULL removed)
		organism_type bigint,
	-- organism id
	--	organism_id bigint NOT NULL,
	-- artgroup?
		inventory_type_id bigint,
	-- translations
		name_de character varying(80),
		name_fr character varying(100),
		name_it character varying(80),
	-- original name
		name_sc character varying(100),
		name_rm character varying(80),
		name_en character varying(80)
	);

	DROP TABLE IF EXISTS classification_taxon;
	CREATE TABLE classification_taxon (
		classification_taxon_id INTEGER PRIMARY KEY,
	-- artgroup id
		classification_id INTEGER REFERENCES classification,
	-- organismus id
		taxon_id bigint INTEGER REFERENCES organism,
		display_level integer
	);

	CREATE INDEX idx_classification_idx1 ON classification(parent);
	CREATE INDEX idx_classification_idx2 ON classification(class_level);
	CREATE INDEX idx_classification_taxon_idx1 ON classification_taxon(classification_id);
	CREATE INDEX idx_classification_taxon_idx2 ON classification_taxon(taxon_id);';

$query_all_organism = "
	-- Alle Organismen, der richtige wissenschaftliche Namen und die Uebersetzung selektieren
	SELECT
		o.id organism_id,
		o.scientific_name name_sc,
		ol_de.name name_de,
		ol_fr.name name_fr,
		ol_it.name name_it,
		ol_rm.name name_rm,
		ol_en.name name_en

	FROM $table_o o
		LEFT JOIN $table_ol ol_de ON ol_de.languages_language='de' AND ol_de.organism_id=o.id
		LEFT JOIN $table_ol ol_fr ON ol_fr.languages_language='fe' AND ol_fr.organism_id=o.id
		LEFT JOIN $table_ol ol_it ON ol_it.languages_language='it' AND ol_it.organism_id=o.id
		LEFT JOIN $table_ol ol_rm ON ol_rm.languages_language='rm' AND ol_rm.organism_id=o.id
		LEFT JOIN $table_ol ol_en ON ol_en.languages_language='en' AND ol_en.organism_id=o.id

	-- Alle Synonyme hinzuholen
	UNION SELECT
		osy.organism_id organism_id,
		osy.name name_sc,
		ol_de.name name_de,
		ol_fr.name name_fr,
		ol_it.name name_it,
		ol_rm.name name_rm,
		ol_en.name name_en

	FROM $table_osy osy
		LEFT JOIN $table_ol ol_de ON ol_de.languages_language='de' AND ol_de.organism_id=osy.organism_id
{'fraction_non_alphanumeric': 0.09072978303747535, 'fraction_numerical': 0.007100591715976331, 'mean_word_length': 4.87789799072643, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 3, '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': '3739894', 'n_tokens_mistral': 2972, 'n_tokens_neox': 2683, 'n_words': 676}