control substitution on

statement ok
CREATE EXTERNAL TABLE fasta_table STORED AS FASTA LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fasta/test.fasta';

query T
SELECT id, description, sequence FROM fasta_table;
----
a description ATCG
b description2 ATCG

query T
SELECT * FROM fasta_table WHERE id = 'a';
----
a description ATCG

statement ok
DROP TABLE fasta_table;

# Now do gzipped FASTA

statement ok
CREATE EXTERNAL TABLE fasta_table STORED AS FASTA LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fasta/test.fasta.gz' OPTIONS (compression 'gzip');

query T
SELECT id, description, sequence FROM fasta_table;
----
a description ATCG
b description2 ATCG

query T
SELECT COUNT(*) FROM fasta_table;
----
2

statement ok
DROP TABLE fasta_table;

# Now to ZSTD compressed FASTA

statement ok
CREATE EXTERNAL TABLE fasta_table STORED AS FASTA LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fasta/test.fasta.zst' OPTIONS (compression 'zstd');

query T
SELECT id, description, sequence FROM fasta_table;
----
a description ATCG
b description2 ATCG

statement ok
DROP TABLE fasta_table;

statement ok
CREATE EXTERNAL TABLE fasta_table STORED AS FASTA PARTITIONED BY (sample) LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fasta-partition/';

query T
SELECT id, description, sequence, sample FROM fasta_table ORDER BY id ASC, sample ASC;
----
a description ATCG 1
a description ATCG 2
b description2 ATCG 1
b description2 ATCG 2

query T
SELECT COUNT(*) FROM fasta_table WHERE sample = '4';
----
0

statement ok
DROP TABLE fasta_table;

query T
SELECT COUNT(*) FROM fasta_scan('$CARGO_MANIFEST_DIR/test-data/datasources/fasta/test.fasta');
----
2

query T
SELECT COUNT(*) FROM fasta_scan('$CARGO_MANIFEST_DIR/test-data/datasources/fasta-partition/');
----
4

query T
SELECT COUNT(*) FROM fasta_scan('$CARGO_MANIFEST_DIR/test-data/datasources/fasta/test.fasta.gz', 'gzip');
----
2


statement ok
CREATE EXTERNAL TABLE fna_table STORED AS FASTA OPTIONS ('fasta.file_extension' 'fna') LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fna/test.fna';

query T
SELECT COUNT(*) FROM fna_table;
----
2

statement ok
DROP TABLE fna_table;

statement ok
CREATE EXTERNAL TABLE fna_table STORED AS FASTA OPTIONS ('fasta.file_extension' 'fna', compression gzip) LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fna/test.fna.gz';

query T
SELECT COUNT(*) FROM fna_table;
----
2

statement ok
DROP TABLE fna_table;

statement ok
CREATE EXTERNAL TABLE fa_table STORED AS FASTA OPTIONS ('fasta.file_extension' 'fa', compression gzip) LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fa/test.fa.gz';

query T
SELECT COUNT(*) FROM fa_table;
----
2

statement ok
DROP TABLE fa_table;

statement ok
CREATE EXTERNAL TABLE exon_table STORED AS FASTA OPTIONS('fasta.sequence_data_type' 'integer_encode_dna') LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/fasta/test.fasta';

query T
SELECT id, description, sequence FROM exon_table;
----
a description [1, 4, 2, 3]
b description2 [1, 4, 2, 3]

statement ok
DROP TABLE exon_table;

statement ok
CREATE EXTERNAL TABLE exon_table STORED AS FASTA OPTIONS ('fasta.file_extension' 'faa', 'fasta.sequence_data_type' 'integer_encode_protein') LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/faa/test.faa';

query T
SELECT id, description, sequence FROM exon_table;
----
a description [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23]
b description2 [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23]

statement ok
DROP TABLE exon_table;

statement ok
CREATE EXTERNAL TABLE exon_table STORED AS FASTA OPTIONS ('fasta.file_extension' 'faa', 'fasta.sequence_data_type' 'integer_encode_protein') LOCATION '$CARGO_MANIFEST_DIR/test-data/datasources/faa/test.faa';

query T
SELECT id, description, sequence FROM exon_table;
----
a description [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23]
b description2 [1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23]

statement ok
DROP TABLE exon_table;