text:
package io.crate.integrationtests;

import com.google.common.base.Joiner;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.ImmutableSet;
import io.crate.action.sql.SQLAction;
import io.crate.action.sql.SQLActionException;
import io.crate.action.sql.SQLRequest;
import io.crate.testing.TestingHelpers;
import org.elasticsearch.common.collect.MapBuilder;
import org.elasticsearch.test.ElasticsearchIntegrationTest;
import org.hamcrest.Matchers;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.ExpectedException;

import java.util.HashMap;
import java.util.Map;

import static org.hamcrest.Matchers.*;

@ElasticsearchIntegrationTest.ClusterScope(numDataNodes = 2)
public class InformationSchemaTest extends SQLTransportIntegrationTest {

    final static Joiner commaJoiner = Joiner.on(", ");

    @Rule
    public ExpectedException expectedException = ExpectedException.none();

    private void serviceSetup() {
        execute("create table t1 (col1 integer primary key, " +
                    "col2 string) clustered into 7 " +
                    "shards");
        execute("create table t2 (col1 integer primary key, " +
                    "col2 string) clustered into " +
                    "10 shards");
        execute(
            "create table t3 (col1 integer, col2 string) clustered into 5 shards with (number_of_replicas=8)");
        ensureYellow();
    }

    @Test
    public void testDefaultTables() throws Exception {
        execute("select * from information_schema.tables order by schema_name, table_name");
        assertEquals(16L, response.rowCount());

        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "NULL| NULL| strict| 0| 1| NULL| information_schema| NULL| columns\n" +
                "NULL| NULL| strict| 0| 1| NULL| information_schema| NULL| routines\n" +
                "NULL| NULL| strict| 0| 1| NULL| information_schema| NULL| schemata\n" +
                "NULL| NULL| strict| 0| 1| NULL| information_schema| NULL| table_constraints\n" +
                "NULL| NULL| strict| 0| 1| NULL| information_schema| NULL| table_partitions\n" +
                "NULL| NULL| strict| 0| 1| NULL| information_schema| NULL| tables\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| checks\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| cluster\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| jobs\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| jobs_log\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| nodes\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| operations\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| operations_log\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| repositories\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| shards\n" +
                "NULL| NULL| strict| 0| 1| NULL| sys| NULL| snapshots\n"));
    }

    @Test
    public void testSelectFromInformationSchema() throws Exception {
        execute("create table quotes (" +
                "id integer primary key, " +
                "quote string index off, " +
                "index quote_fulltext using fulltext(quote) with (analyzer='snowball')" +
                ") clustered by (id) into 3 shards with (number_of_replicas=10)");

        execute("select table_name, number_of_shards, number_of_replicas, clustered_by from " +
                "information_schema.tables " +
                "where table_name='quotes'");
        assertEquals(1L, response.rowCount());
        assertEquals("quotes", response.rows()[0][0]);
        assertEquals(3, response.rows()[0][1]);
        assertEquals("10", response.rows()[0][2]);
        assertEquals("id", response.rows()[0][3]);
        assertThat(response.duration(), greaterThanOrEqualTo(0L));

        execute("select * from information_schema.columns where table_name='quotes'");
        assertEquals(2L, response.rowCount());
        assertThat(response.duration(), greaterThanOrEqualTo(0L));

        execute("select * from information_schema.table_constraints where schema_name='doc' and table_name='quotes'");
        assertEquals(1L, response.rowCount());
        assertThat(response.duration(), greaterThanOrEqualTo(0L));

        execute("select * from information_schema.routines");
        assertEquals(115L, response.rowCount());
        assertThat(response.duration(), greaterThanOrEqualTo(0L));
    }

    @Test
    public void testSearchInformationSchemaTablesRefresh() throws Exception {
        serviceSetup();

        execute("select * from information_schema.tables");
        assertEquals(19L, response.rowCount());

        client().execute(SQLAction.INSTANCE,
            new SQLRequest("create table t4 (col1 integer, col2 string) with (number_of_replicas=0)")).actionGet();
        ensureGreen("t4");

        execute("select * from information_schema.tables");
        assertEquals(20L, response.rowCount());
    }

    @Test
    public void testSelectStarFromInformationSchemaTableWithOrderBy() throws Exception {
        execute("create table test (col1 integer primary key, col2 string) clustered into 5 shards");
        execute("create table foo (col1 integer primary key, " +
                "col2 string) clustered by(col1) into 3 shards");
        ensureGreen();
        execute("select * from INFORMATION_SCHEMA.Tables where schema_name='doc' order by table_name asc");
        assertEquals(2L, response.rowCount());
        assertEquals("doc", response.rows()[0][6]);
        assertEquals("foo", response.rows()[0][8]);
        assertEquals(3, response.rows()[0][4]); //
        assertEquals("1", response.rows()[0][3]);
        assertEquals("col1", response.rows()[0][1]);

        assertEquals("doc", response.rows()[1][6]);
        assertEquals("test", response.rows()[1][8]);
        assertEquals(5, response.rows()[1][4]);
        assertEquals("1", response.rows()[1][3]);
        assertEquals("col1", response.rows()[1][1]);
    }

    @Test
    public void testSelectStarFromInformationSchemaTableWithOrderByAndLimit() throws Exception {
        execute("create table test (col1 integer primary key, col2 string)");
        execute("create table foo (col1 integer primary key, col2 string) clustered into 3 shards");
        ensureGreen();
        execute("select schema_name, table_name, number_of_shards, number_of_replicas " +
                "from INFORMATION_SCHEMA.Tables where schema_name='doc' " +
                "order by table_name asc limit 1");
        assertEquals(1L, response.rowCount());
        assertEquals("doc", response.rows()[0][0]);
        assertEquals("foo", response.rows()[0][1]);
        assertEquals(3, response.rows()[0][2]);
        assertEquals("1", response.rows()[0][3]);
    }

    @Test
    public void testSelectStarFromInformationSchemaTableWithOrderByTwoColumnsAndLimit() throws Exception {
        execute("create table test (col1 integer primary key, col2 string) clustered into 1 shards");
        execute("create table foo (col1 integer primary key, col2 string) clustered into 3 shards");
        execute("create table bar (col1 integer primary key, col2 string) clustered into 3 shards");
        ensureGreen();
        execute("select table_name, number_of_shards from INFORMATION_SCHEMA.Tables where schema_name='doc' " +
                "order by number_of_shards desc, table_name asc limit 2");
        assertEquals(2L, response.rowCount());

        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "bar| 3\n" +
                "foo| 3\n"));
    }

    @Test
    public void testSelectStarFromInformationSchemaTableWithOrderByAndLimitOffset() throws Exception {
        execute("create table test (col1 integer primary key, col2 string) clustered into 5 shards");
        execute("create table foo (col1 integer primary key, col2 string) clustered into 3 shards");
        ensureGreen();
        execute("select * from INFORMATION_SCHEMA.Tables where schema_name='doc' order by table_name asc limit 1 offset 1");
        assertEquals(1L, response.rowCount());

        assertEquals("doc", response.rows()[0][6]); // schema_name
        assertEquals("test", response.rows()[0][8]); // table_name
        assertEquals(5, response.rows()[0][4]); // number_of_shards
        assertEquals("1", response.rows()[0][3]); // number_of_replicas
        assertEquals("col1", response.rows()[0][1]); // primary key
    }

    @Test
    public void testSelectFromInformationSchemaTable() throws Exception {
        execute("select TABLE_NAME from INFORMATION_SCHEMA.Tables where schema_name='doc'");
        assertEquals(0L, response.rowCount());

        execute("create table test (col1 integer primary key, col2 string) clustered into 5 shards");
        ensureGreen();

        execute("select table_name, number_of_shards, number_of_replicas, " +
                "clustered_by from INFORMATION_SCHEMA.Tables where schema_name='doc'");
        assertEquals(1L, response.rowCount());
        assertEquals("test", response.rows()[0][0]);
        assertEquals(5, response.rows()[0][1]);
        assertEquals("1", response.rows()[0][2]);
        assertEquals("col1", response.rows()[0][3]);
    }

    @Test
    public void testSelectBlobTablesFromInformationSchemaTable() throws Exception {
        execute("select TABLE_NAME from INFORMATION_SCHEMA.Tables where schema_name='blob'");
        assertEquals(0L, response.rowCount());

        execute("create blob table test clustered into 5 shards with (blobs_path='/tmp/blobs_path')");
        ensureGreen();

        execute("select table_name, number_of_shards, number_of_replicas, " +
                "clustered_by, blobs_path from INFORMATION_SCHEMA.Tables where schema_name='blob' ");
        assertEquals(1L, response.rowCount());
        assertEquals("test", response.rows()[0][0]);
        assertEquals(5, response.rows()[0][1]);
        assertEquals("1", response.rows()[0][2]);
        assertEquals("digest", response.rows()[0][3]);
        assertEquals("/tmp/blobs_path", response.rows()[0][4]);
    }

    @Test
    public void testSelectPartitionedTablesFromInformationSchemaTable() throws Exception {
        execute("create table test (id int primary key, name string) partitioned by (id)");
        execute("insert into test (id, name) values (1, 'Youri'), (2, 'Ruben')");
        ensureGreen();

        execute("select table_name, number_of_shards, number_of_replicas, " +
                "clustered_by, partitioned_by from INFORMATION_SCHEMA.Tables where schema_name = 'doc'");
        assertEquals(1L, response.rowCount());
        assertEquals("test", response.rows()[0][0]);
        assertEquals(5, response.rows()[0][1]);
        assertEquals("1", response.rows()[0][2]);
        assertEquals("id", response.rows()[0][3]);
        assertArrayEquals(new String[]{"id"}, (String[]) response.rows()[0][4]);
    }

    @Test
    public void testSelectStarFromInformationSchemaTable() throws Exception {
        execute("create table test (col1 integer, col2 string) clustered into 5 shards");
        ensureGreen();
        execute("select * from INFORMATION_SCHEMA.Tables where schema_name='doc'");
        assertEquals(1L, response.rowCount());
        assertEquals("doc", response.rows()[0][6]);
        assertEquals("test", response.rows()[0][8]);
        assertEquals(5, response.rows()[0][4]);
        assertEquals("1", response.rows()[0][3]);
        assertEquals("_id", response.rows()[0][1]);
    }

    @Test
    public void testSelectFromTableConstraints() throws Exception {

        execute("select * from INFORMATION_SCHEMA.table_constraints order by schema_name asc, table_name asc");
        assertEquals(10L, response.rowCount());
        assertThat(response.cols(),
                arrayContaining("constraint_name", "constraint_type", "schema_name", "table_name"));
        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "[schema_name, table_name, column_name]| PRIMARY_KEY| information_schema| columns\n" +
                "[schema_name]| PRIMARY_KEY| information_schema| schemata\n" +
                "[schema_name, table_name]| PRIMARY_KEY| information_schema| tables\n" +
                "[id]| PRIMARY_KEY| sys| checks\n" +
                "[id]| PRIMARY_KEY| sys| jobs\n" +
                "[id]| PRIMARY_KEY| sys| jobs_log\n" +
                "[id]| PRIMARY_KEY| sys| nodes\n" +
                "[name]| PRIMARY_KEY| sys| repositories\n" +
                "[schema_name, table_name, id, partition_ident]| PRIMARY_KEY| sys| shards\n" +
                "[name, repository]| PRIMARY_KEY| sys| snapshots\n"
        ));

        execute("create table test (col1 integer primary key, col2 string)");
        ensureGreen();
        execute("select constraint_type, constraint_name, " +
                "table_name from information_schema.table_constraints where schema_name='doc'");
        assertEquals(1L, response.rowCount());
        assertEquals("PRIMARY_KEY", response.rows()[0][0]);
        assertThat(commaJoiner.join((String[]) response.rows()[0][1]), is("col1"));
        assertEquals("test", response.rows()[0][2]);
    }

    @Test
    public void testRefreshTableConstraints() throws Exception {
        execute("create table test (col1 integer primary key, col2 string)");
        ensureGreen();
        execute("select table_name, constraint_name from INFORMATION_SCHEMA" +
                ".table_constraints where schema_name='doc'");
        assertEquals(1L, response.rowCount());
        assertEquals("test", response.rows()[0][0]);
        assertThat(commaJoiner.join((String[]) response.rows()[0][1]), is("col1"));

        execute("create table test2 (col1a string primary key, col2a timestamp)");
        ensureGreen();
        execute("select table_name, constraint_name from INFORMATION_SCHEMA.table_constraints where schema_name='doc' order by table_name asc");

        assertEquals(2L, response.rowCount());
        assertEquals("test2", response.rows()[1][0]);
        assertThat(commaJoiner.join((String[]) response.rows()[1][1]), is("col1a"));
    }

    @Test
    public void testSelectFromRoutines() throws Exception {
        String stmt1 = "CREATE ANALYZER myAnalyzer WITH (" +
                "  TOKENIZER whitespace," +
                "  TOKEN_FILTERS (" +
                "     myTokenFilter WITH (" +
                "      type='snowball'," +
                "      language='german'" +
                "    )," +
                "    kstem" +
                "  )" +
                ")";
        execute(stmt1);
        execute("CREATE ANALYZER myOtherAnalyzer extends german (" +
                "  stopwords=[?, ?, ?]" +
                ")", new Object[]{"der", "die", "das"});
        ensureGreen();
        execute("SELECT * from INFORMATION_SCHEMA.routines " +
                "where routine_name = 'myanalyzer' " +
                "or routine_name = 'myotheranalyzer' " +
                "and routine_type = 'ANALYZER' " +
                "order by routine_name asc");
        assertEquals(2L, response.rowCount());

        assertEquals("myanalyzer", response.rows()[0][0]);
        assertEquals("ANALYZER", response.rows()[0][1]);
        assertEquals("myotheranalyzer", response.rows()[1][0]);
        assertEquals("ANALYZER", response.rows()[1][1]);
        client().admin().cluster().prepareUpdateSettings()
                .setPersistentSettingsToRemove(
                    ImmutableSet.of("crate.analysis.custom.analyzer.myanalyzer",
                            "crate.analysis.custom.analyzer.myotheranalyzer",
                            "crate.analysis.custom.filter.myanalyzer_mytokenfilter"))
                .setTransientSettingsToRemove(
                        ImmutableSet.of("crate.analysis.custom.analyzer.myanalyzer",
                                "crate.analysis.custom.analyzer.myotheranalyzer",
                                "crate.analysis.custom.filter.myanalyzer_mytokenfilter"))
                .execute().actionGet();
    }

    @Test
    public void testSelectAnalyzersFromRoutines() throws Exception {
        execute("SELECT routine_name from INFORMATION_SCHEMA.routines WHERE " +
               "routine_type='ANALYZER' order by " +
                "routine_name desc limit 5");
        assertEquals(5L, response.rowCount());
        String[] analyzerNames = new String[response.rows().length];
        for (int i=0; i<response.rowCount(); i++) {
            analyzerNames[i] = (String)response.rows()[i][0];
        }
        assertEquals(
                "whitespace, turkish, thai, swedish, stop",
                Joiner.on(", ").join(analyzerNames)
        );
    }

    @Test
    public void testSelectTokenizersFromRoutines() throws Exception {
        execute("SELECT routine_name from INFORMATION_SCHEMA.routines WHERE " +
                "routine_type='TOKENIZER' order by " +
                "routine_name asc limit 5");
        assertEquals(5L, response.rowCount());
        String[] tokenizerNames = new String[response.rows().length];
        for (int i=0; i<response.rowCount(); i++) {
            tokenizerNames[i] = (String)response.rows()[i][0];
        }
        assertEquals(
                "classic, edgeNGram, edge_ngram, keyword, letter",
                Joiner.on(", ").join(tokenizerNames)
        );
    }

    @Test
    public void testSelectTokenFiltersFromRoutines() throws Exception {
        execute("SELECT routine_name from INFORMATION_SCHEMA.routines WHERE " +
                "routine_type='TOKEN_FILTER' order by " +
                "routine_name asc limit 5");
        assertEquals(5L, response.rowCount());
        String[] tokenFilterNames = new String[response.rows().length];
        for (int i=0; i<response.rowCount(); i++) {
            tokenFilterNames[i] = (String)response.rows()[i][0];
        }
        assertEquals(
                "apostrophe, arabic_normalization, arabic_stem, asciifolding, brazilian_stem",
                Joiner.on(", ").join(tokenFilterNames)
        );
    }

    @Test
    public void testSelectCharFiltersFromRoutines() throws Exception {
        execute("SELECT routine_name from INFORMATION_SCHEMA.routines WHERE " +
                "routine_type='CHAR_FILTER' order by " +
                "routine_name asc");
        assertEquals(4L, response.rowCount());
        String[] charFilterNames = new String[response.rows().length];
        for (int i=0; i<response.rowCount(); i++) {
            charFilterNames[i] = (String)response.rows()[i][0];
        }
        assertEquals(
                "htmlStrip, html_strip, mapping, pattern_replace",
                Joiner.on(", ").join(charFilterNames)
        );
    }

    @Test
    public void testTableConstraintsWithOrderBy() throws Exception {
        execute("create table test1 (col11 integer primary key, col12 float)");
        execute("create table test2 (col21 double primary key, col22 string)");
        execute("create table abc (col31 integer primary key, col32 string)");

        ensureGreen();

        execute("select table_name from INFORMATION_SCHEMA.table_constraints " +
                "where schema_name not in ('sys', 'information_schema')  " +
                "ORDER BY table_name");
        assertEquals(3L, response.rowCount());
        assertEquals("abc", response.rows()[0][0]);
        assertEquals("test1", response.rows()[1][0]);
        assertEquals("test2", response.rows()[2][0]);
    }

    @Test
    public void testDefaultColumns() throws Exception {
        execute("select * from information_schema.columns order by schema_name, table_name");
        assertEquals(333L, response.rowCount());
    }

    @Test
    public void testColumnsColumns() throws Exception {
        execute("select * from information_schema.columns where schema_name='information_schema' and table_name='columns' order by ordinal_position asc");
        assertThat(response.rowCount(), is(7L));
        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "column_name| string| NULL| false| 1| information_schema| columns\n" +
                "data_type| string| NULL| false| 2| information_schema| columns\n" +
                "generation_expression| string| NULL| false| 3| information_schema| columns\n" +
                "is_generated| boolean| NULL| false| 4| information_schema| columns\n" +
                "ordinal_position| short| NULL| false| 5| information_schema| columns\n" +
                "schema_name| string| NULL| false| 6| information_schema| columns\n" +
                "table_name| string| NULL| false| 7| information_schema| columns\n"));
    }

    @Test
    public void testSelectFromTableColumns() throws Exception {
        execute("create table test (col1 integer, col2 string index off, age integer)");
        ensureGreen();
        execute("select * from INFORMATION_SCHEMA.Columns where schema_name='doc'");
        assertEquals(3L, response.rowCount());
        assertEquals("age", response.rows()[0][0]);
        assertEquals("integer", response.rows()[0][1]);
        assertEquals(null, response.rows()[0][2]);
        assertEquals(false, response.rows()[0][3]);
        short expected = 1;
        assertEquals(expected, response.rows()[0][4]);
        assertEquals("doc", response.rows()[0][5]);
        assertEquals("test", response.rows()[0][6]);

        assertEquals("col1", response.rows()[1][0]);
        assertEquals("col2", response.rows()[2][0]);
    }

    @Test
    public void testSelectFromTableColumnsRefresh() throws Exception {
        execute("create table test (col1 integer, col2 string, age integer)");
        ensureGreen();
        execute("select table_name, column_name, " +
                "ordinal_position, data_type from INFORMATION_SCHEMA.Columns where schema_name='doc'");
        assertEquals(3L, response.rowCount());
        assertEquals("test", response.rows()[0][0]);

        execute("create table test2 (col1 integer, col2 string, age integer)");
        ensureGreen();
        execute("select table_name, column_name, " +
                "ordinal_position, data_type from INFORMATION_SCHEMA.Columns " +
                "where schema_name='doc' " +
                "order by table_name");

        assertEquals(6L, response.rowCount());
        assertEquals("test", response.rows()[0][0]);
        assertEquals("test2", response.rows()[4][0]);
    }

    @Test
    public void testSelectFromTableColumnsMultiField() throws Exception {
        execute("create table test (col1 string, col2 string," +
                "index col1_col2_ft using fulltext(col1, col2))");
        ensureGreen();
        execute("select table_name, column_name," +
                "ordinal_position, data_type from INFORMATION_SCHEMA.Columns where schema_name='doc'");
        assertEquals(2L, response.rowCount());

        assertEquals("test", response.rows()[0][0]);
        assertEquals("col1", response.rows()[0][1]);
        short expected = 1;
        assertEquals(expected, response.rows()[0][2]);
        assertEquals("string", response.rows()[0][3]);

        assertEquals("test", response.rows()[1][0]);
        assertEquals("col2", response.rows()[1][1]);
        expected = 2;
        assertEquals(expected, response.rows()[1][2]);
        assertEquals("string", response.rows()[1][3]);
    }

    /* TODO: enable when information_schema.indices is implemented
    @SuppressWarnings("unchecked")
    @Test
    public void testSelectFromTableIndices() throws Exception {
        execute("create table test (col1 string, col2 string, " +
                "col3 string index using fulltext, " +
                "col4 string index off, " +
                "index col1_col2_ft using fulltext(col1, col2) with(analyzer='english'))");
        ensureGreen();
        execute("select table_name, index_name, method, columns, properties " +
                "from INFORMATION_SCHEMA.Indices");
        assertEquals(4L, response.rowCount());

        assertEquals("test", response.rows()[0][0]);
        assertEquals("col1", response.rows()[0][1]);
        assertEquals("plain", response.rows()[0][2]);
        assertTrue(response.rows()[0][3] instanceof List);
        assertThat((List<String>) response.rows()[0][3], contains("col1"));
        assertEquals("", response.rows()[0][4]);

        assertEquals("test", response.rows()[1][0]);
        assertEquals("col2", response.rows()[1][1]);
        assertEquals("plain", response.rows()[1][2]);
        assertThat((List<String>) response.rows()[1][3], contains("col2"));
        assertEquals("", response.rows()[1][4]);

        assertEquals("test", response.rows()[2][0]);
        assertEquals("col1_col2_ft", response.rows()[2][1]);
        assertEquals("fulltext", response.rows()[2][2]);
        assertThat((List<String>) response.rows()[2][3], contains("col1", "col2"));
        assertEquals("analyzer=english", response.rows()[2][4]);

        assertEquals("test", response.rows()[3][0]);
        assertEquals("col3", response.rows()[3][1]);
        assertEquals("fulltext", response.rows()[3][2]);
        assertThat((List<String>) response.rows()[3][3], contains("col3"));
        assertEquals("analyzer=standard", response.rows()[3][4]);
    }*/

    @Test
    public void testGlobalAggregation() throws Exception {
        execute("select max(ordinal_position) from information_schema.columns");
        assertEquals(1, response.rowCount());

        short max_ordinal = 99;
        assertEquals(max_ordinal, response.rows()[0][0]);

        execute("create table t1 (id integer, col1 string)");
        ensureGreen();
        execute("select max(ordinal_position) from information_schema.columns where schema_name='doc'");
        assertEquals(1, response.rowCount());

        max_ordinal = 2;
        assertEquals(max_ordinal, response.rows()[0][0]);

    }

    @Test
    public void testGlobalAggregationMany() throws Exception {
        execute("create table t1 (id integer, col1 string) clustered into 10 shards with(number_of_replicas=14)");
        execute("create table t2 (id integer, col1 string) clustered into 5 shards with(number_of_replicas=7)");
        execute("create table t3 (id integer, col1 string) clustered into 3 shards with(number_of_replicas=2)");
        ensureYellow();
        execute("select min(number_of_shards), max(number_of_shards), avg(number_of_shards)," +
                "sum(number_of_shards) from information_schema.tables where schema_name='doc'");
        assertEquals(1, response.rowCount());

        assertEquals(3, response.rows()[0][0]);
        assertEquals(10, response.rows()[0][1]);
        assertEquals(6.0d, response.rows()[0][2]);
        assertEquals(18.0d, response.rows()[0][3]);
    }

    @Test
    public void testGlobalAggregationWithWhere() throws Exception {
        execute("create table t1 (id integer, col1 string) clustered into 10 shards with(number_of_replicas=14)");
        execute("create table t2 (id integer, col1 string) clustered into 5 shards with(number_of_replicas=7)");
        execute("create table t3 (id integer, col1 string) clustered into 3 shards with(number_of_replicas=2)");
        ensureYellow();
        execute("select min(number_of_shards), max(number_of_shards), avg(number_of_shards)," +
                "sum(number_of_shards) from information_schema.tables where schema_name='doc' and table_name != 't1'");
        assertEquals(1, response.rowCount());

        assertEquals(3, response.rows()[0][0]);
        assertEquals(5, response.rows()[0][1]);
        assertEquals(4.0d, response.rows()[0][2]);
        assertEquals(8.0d, response.rows()[0][3]);
    }

    @Test
    public void testGlobalAggregationWithAlias() throws Exception {
        execute("create table t1 (id integer, col1 string) clustered into 10 shards with(number_of_replicas=14)");
        execute("create table t2 (id integer, col1 string) clustered into 5 shards with(number_of_replicas=7)");
        execute("create table t3 (id integer, col1 string) clustered into 3 shards with(number_of_replicas=2)");
        ensureYellow();
        execute("select min(number_of_shards) as min_shards from information_schema.tables where table_name = 't1'");
        assertEquals(1, response.rowCount());

        assertEquals(10, response.rows()[0][0]);
    }

    @Test
    public void testGlobalCount() throws Exception {
        execute("create table t1 (id integer, col1 string) clustered into 10 shards with(number_of_replicas=14)");
        execute("create table t2 (id integer, col1 string) clustered into 5 shards with(number_of_replicas=7)");
        execute("create table t3 (id integer, col1 string) clustered into 3 shards with(number_of_replicas=2)");
        ensureYellow();
        execute("select count(*) from information_schema.tables");
        assertEquals(1, response.rowCount());
        assertEquals(19L, response.rows()[0][0]);
    }

    @Test
    public void testGlobalCountDistinct() throws Exception {
        execute("create table t3 (id integer, col1 string)");
        ensureGreen();
        execute("select count(distinct schema_name) from information_schema.tables order by count(distinct schema_name)");
        assertEquals(1, response.rowCount());
        assertEquals(3L, response.rows()[0][0]);
    }

    @Test
    public void selectDynamicObjectAddsSubColumn() throws Exception {
        execute("create table t4 (" +
                "  title string," +
                "  stuff object(dynamic) as (" +
                "    first_name string," +
                "    last_name string" +
                "  )" +
                ") with (number_of_replicas=0)");
        ensureGreen();
        execute("select column_name, ordinal_position from information_schema.columns where table_name='t4'");
        assertEquals(4, response.rowCount());
        assertEquals("stuff", response.rows()[0][0]);
        short ordinal_position = 1;
        assertEquals(ordinal_position++, response.rows()[0][1]);

        assertEquals("stuff['first_name']", response.rows()[1][0]);
        assertEquals(ordinal_position++, response.rows()[1][1]);

        assertEquals("stuff['last_name']", response.rows()[2][0]);
        assertEquals(ordinal_position++, response.rows()[2][1]);

        assertEquals("title", response.rows()[3][0]);
        assertEquals(ordinal_position, response.rows()[3][1]);

        execute("insert into t4 (stuff) values (?)", new Object[]{
                new HashMap<String, Object>() {{
                    put("first_name", "Douglas");
                    put("middle_name", "Noel");
                    put("last_name", "Adams");
                }}
        });
        execute("refresh table t4");

        waitForMappingUpdateOnAll("t4", "stuff.first_name", "stuff.middle_name", "stuff.last_name");
        execute("select column_name, ordinal_position from information_schema.columns where table_name='t4'");
        assertEquals(5, response.rowCount());

        assertEquals("stuff", response.rows()[0][0]);
        ordinal_position = 1;
        assertEquals(ordinal_position++, response.rows()[0][1]);

        assertEquals("stuff['first_name']", response.rows()[1][0]);
        assertEquals(ordinal_position++, response.rows()[1][1]);

        assertEquals("stuff['last_name']", response.rows()[2][0]);
        assertEquals(ordinal_position++, response.rows()[2][1]);

        assertEquals("stuff['middle_name']", response.rows()[3][0]);
        assertEquals(ordinal_position++, response.rows()[3][1]);

        assertEquals("title", response.rows()[4][0]);
        assertEquals(ordinal_position, response.rows()[4][1]);
    }

    @Test
    public void testAddColumnToIgnoredObject() throws Exception {
        execute("create table t4 (" +
                "  title string," +
                "  stuff object(ignored) as (" +
                "    first_name string," +
                "    last_name string" +
                "  )" +
                ")");
        ensureYellow();
        execute("select column_name, ordinal_position from information_schema.columns where table_name='t4'");
        assertEquals(4, response.rowCount());
        short ordinal_position = 1;
        assertEquals("stuff", response.rows()[0][0]);
        assertEquals(ordinal_position++, response.rows()[0][1]);

        assertEquals("stuff['first_name']", response.rows()[1][0]);
        assertEquals(ordinal_position++, response.rows()[1][1]);

        assertEquals("stuff['last_name']", response.rows()[2][0]);
        assertEquals(ordinal_position++, response.rows()[2][1]);

        assertEquals("title", response.rows()[3][0]);
        assertEquals(ordinal_position, response.rows()[3][1]);

        execute("insert into t4 (stuff) values (?)", new Object[]{
                new HashMap<String, Object>() {{
                    put("first_name", "Douglas");
                    put("middle_name", "Noel");
                    put("last_name", "Adams");
                }}
        });

        execute("select column_name, ordinal_position from information_schema.columns where table_name='t4'");
        assertEquals(4, response.rowCount());

        ordinal_position = 1;
        assertEquals("stuff", response.rows()[0][0]);
        assertEquals(ordinal_position++, response.rows()[0][1]);

        assertEquals("stuff['first_name']", response.rows()[1][0]);
        assertEquals(ordinal_position++, response.rows()[1][1]);

        assertEquals("stuff['last_name']", response.rows()[2][0]);
        assertEquals(ordinal_position++, response.rows()[2][1]);

        assertEquals("title", response.rows()[3][0]);
        assertEquals(ordinal_position, response.rows()[3][1]);
    }

    @Test
    public void testPartitionedBy() throws Exception {
        execute("create table my_table (id integer, name string) partitioned by (name)");
        execute("create table my_other_table (id integer, name string, content string) " +
                "partitioned by (name, content)");

        execute("select * from information_schema.tables " +
                "where schema_name = 'doc' order by table_name");

        String[] row1 = new String[] { "name", "content" };
        String[] row2 = new String[] { "name" };
        assertArrayEquals((String[]) response.rows()[0][5], row1);
        assertArrayEquals((String[]) response.rows()[1][5], row2);
    }

    @Test
    public void testTablePartitions() throws Exception {
        execute("create table my_table (par int, content string) " +
                "clustered into 5 shards " +
                "partitioned by (par)");
        execute("insert into my_table (par, content) values (1, 'content1')");
        execute("insert into my_table (par, content) values (1, 'content2')");
        execute("insert into my_table (par, content) values (2, 'content3')");
        execute("insert into my_table (par, content) values (2, 'content4')");
        execute("insert into my_table (par, content) values (2, 'content5')");
        execute("insert into my_table (par, content) values (3, 'content6')");
        ensureGreen();

        execute("select table_name, schema_name, partition_ident, values, number_of_shards, number_of_replicas "+
                "from information_schema.table_partitions order by table_name, partition_ident");
        assertEquals(3, response.rowCount());

        Object[] row1 = new Object[] { "my_table", "doc", "04132", ImmutableMap.of("par", 1), 5, "1"};
        Object[] row2 = new Object[] { "my_table", "doc", "04134", ImmutableMap.of("par", 2), 5, "1"};
        Object[] row3 = new Object[] { "my_table", "doc", "04136", ImmutableMap.of("par", 3), 5, "1"};

        assertArrayEquals(row1, response.rows()[0]);
        assertArrayEquals(row2, response.rows()[1]);
        assertArrayEquals(row3, response.rows()[2]);
    }

    @Test
    public void testDisableWriteOnSinglePartition() throws Exception {
        execute("create table my_table (par int, content string) " +
                "clustered into 5 shards " +
                "partitioned by (par)");
        execute("insert into my_table (par, content) values (1, 'content1'), " +
                                                            "(1, 'content2'), " +
                                                            "(2, 'content3'), " +
                                                            "(2, 'content4'), " +
                                                            "(2, 'content5'), " +
                                                            "(3, 'content6')");

        ensureGreen();
        execute("alter table my_table partition (par=1) set (\"blocks.write\"=true)");

        // update is expected to be executed without exception since this partition has no write block
        execute("update my_table set content=\'content42\' where par=2");
        refresh();
        // verifying update
        execute("select content from my_table where par=2");
        assertThat(response.rowCount(), is(3L));

        expectedException.expect(SQLActionException.class);
        expectedException.expectMessage("blocked by: [FORBIDDEN/8/index write (api)]");

        // trying to perform an update on a partition with a write block
        execute("update my_table set content=\'content42\' where par=1");

    }

    @Test
    public void testMultipleWritesWhenOnePartitionIsReadOnly() throws Exception {
        execute("create table my_table (par int, content string) " +
                "clustered into 5 shards " +
                "partitioned by (par)");
        execute("insert into my_table (par, content) values " +
                "(1, 'content2'), " +
                "(2, 'content3')");

        ensureGreen();
        execute("alter table my_table partition (par=1) set (\"blocks.write\"=true)");
        try {
            execute("insert into my_table (par, content) values (2, 'content42'), " +
                    "(2, 'content42'), " +
                    "(1, 'content2'), " +
                    "(3, 'content6')");

        } catch (SQLActionException e) {
            assertThat(e.getMessage(), is("blocked by: [FORBIDDEN/8/index write (api)];"));
        }
        refresh();
        execute("select * from my_table");
        assertThat(response.rowCount(), is(both(greaterThanOrEqualTo(2L)).and(lessThanOrEqualTo(5L))));
        //cleaning up
        execute("alter table my_table partition (par=1) set (\"blocks.write\"=false)");
    }

    @Test
    public void testPartitionedTableShardsAndReplicas() throws Exception {
        execute("create table parted (par byte, content string) " +
                "partitioned by (par) " +
                "clustered into 2 shards with (number_of_replicas=0)");
        ensureGreen();

        execute("select table_name, number_of_shards, number_of_replicas from information_schema.tables where table_name='parted'");
        assertThat(TestingHelpers.printedTable(response.rows()), is("parted| 2| 0\n"));

        execute("select * from information_schema.table_partitions where table_name='parted' order by table_name, partition_ident");
        assertThat(response.rowCount(), is(0L));

        execute("insert into parted (par, content) values (1, 'foo'), (3, 'baz')");
        execute("refresh table parted");
        ensureGreen();

        execute("select table_name, number_of_shards, number_of_replicas from information_schema.tables where table_name='parted'");
        assertThat(TestingHelpers.printedTable(response.rows()), is("parted| 2| 0\n"));

        execute("select table_name, partition_ident, values, number_of_shards, number_of_replicas " +
                "from information_schema.table_partitions where table_name='parted' order by table_name, partition_ident");
        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "parted| 04132| {par=1}| 2| 0\n" +
                "parted| 04136| {par=3}| 2| 0\n"));

        execute("alter table parted set (number_of_shards=6)");
        waitNoPendingTasksOnAll();

        execute("insert into parted (par, content) values (2, 'bar')");
        execute("refresh table parted");
        ensureGreen();

        execute("select table_name, number_of_shards, number_of_replicas from information_schema.tables where table_name='parted'");
        assertThat(TestingHelpers.printedTable(response.rows()), is("parted| 6| 0\n"));

        execute("select table_name, partition_ident, values, number_of_shards, number_of_replicas " +
                "from information_schema.table_partitions where table_name='parted' order by table_name, partition_ident");
        assertThat(response.rowCount(), is(3L));
        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "parted| 04132| {par=1}| 2| 0\n" +
                "parted| 04134| {par=2}| 6| 0\n" +
                "parted| 04136| {par=3}| 2| 0\n"));

        execute("update parted set new=true where par=1");
        refresh();
        waitNoPendingTasksOnAll();

        // ensure newer index metadata does not override settings in template
        execute("select table_name, number_of_shards, number_of_replicas from information_schema.tables where table_name='parted'");
        assertThat(TestingHelpers.printedTable(response.rows()), is("parted| 6| 0\n"));

        execute("select table_name, partition_ident, values, number_of_shards, number_of_replicas " +
                "from information_schema.table_partitions where table_name='parted' order by table_name, partition_ident");
        assertThat(response.rowCount(), is(3L));
        assertThat(TestingHelpers.printedTable(response.rows()), is(
                        "parted| 04132| {par=1}| 2| 0\n" +
                        "parted| 04134| {par=2}| 6| 0\n" +
                        "parted| 04136| {par=3}| 2| 0\n"));

        execute("delete from parted where par=2");
        waitNoPendingTasksOnAll();

        execute("select table_name, number_of_shards, number_of_replicas from information_schema.tables where table_name='parted'");
        assertThat(TestingHelpers.printedTable(response.rows()), is("parted| 6| 0\n"));
        waitNoPendingTasksOnAll();
        execute("select table_name, partition_ident, values, number_of_shards, number_of_replicas "+
                "from information_schema.table_partitions where table_name='parted' order by table_name, partition_ident");
        assertThat(response.rowCount(), is(2L));
        assertThat(TestingHelpers.printedTable(response.rows()), is(
                "parted| 04132| {par=1}| 2| 0\n" +
                "parted| 04136| {par=3}| 2| 0\n"));

    }

    @Test
    public void testTablePartitionsMultiCol() throws Exception {
        execute("create table my_table (par int, par_str string, content string) " +
                "clustered into 5 shards " +
                "partitioned by (par, par_str)");
        execute("insert into my_table (par, par_str, content) values (1, 'foo', 'content1')");
        execute("insert into my_table (par, par_str, content) values (1, 'bar', 'content2')");
        execute("insert into my_table (par, par_str, content) values (2, 'foo', 'content3')");
        execute("insert into my_table (par, par_str, content) values (2, 'bar', 'content4')");
        ensureGreen();
        refresh();
        execute("alter table my_table set (number_of_shards=4)");
        waitNoPendingTasksOnAll();
        execute("insert into my_table (par, par_str, content) values (2, 'asdf', 'content5')");
        ensureYellow();

        execute("select table_name, schema_name, partition_ident, values, number_of_shards, number_of_replicas " +
                "from information_schema.table_partitions order by table_name, partition_ident");
        assertEquals(5, response.rowCount());

        Object[] row1 = new Object[] { "my_table", "doc", "08132132c5p0", ImmutableMap.of("par", 1, "par_str", "bar"), 5, "1"};
        Object[] row2 = new Object[] { "my_table", "doc", "08132136dtng", ImmutableMap.of("par", 1, "par_str", "foo"), 5, "1"};
        Object[] row3 = new Object[] { "my_table", "doc", "08134132c5p0", ImmutableMap.of("par", 2, "par_str", "bar"), 5, "1"};
        Object[] row4 = new Object[] { "my_table", "doc", "08134136dtng", ImmutableMap.of("par", 2, "par_str", "foo"), 5, "1"};
        Object[] row5 = new Object[] { "my_table", "doc", "081341b1edi6c", ImmutableMap.of("par", 2, "par_str", "asdf"), 4, "1"};

        assertArrayEquals(row1, response.rows()[0]);
        assertArrayEquals(row2, response.rows()[1]);
        assertArrayEquals(row3, response.rows()[2]);
        assertArrayEquals(row4, response.rows()[3]);
        assertArrayEquals(row5, response.rows()[4]);
    }

    @Test
    public void testPartitionsNestedCol() throws Exception {
        execute("create table my_table (id int, metadata object as (date timestamp)) " +
                "clustered into 5 shards " +
                "partitioned by (metadata['date'])");
        ensureYellow();
        execute("insert into my_table (id, metadata) values (?, ?), (?, ?)",
                new Object[]{
                        1, new MapBuilder<String, Object>().put("date","1970-01-01").map(),
                        2, new MapBuilder<String, Object>().put("date", "2014-05-28").map()
                });
        refresh();

        execute("select table_name, partition_ident, values from information_schema.table_partitions order by table_name, partition_ident");
        assertEquals(2, response.rowCount());

        Object[] row1 = new Object[] { "my_table", "04130", ImmutableMap.of("metadata['date']", 0L) };
        Object[] row2 = new Object[] { "my_table", "04732d1g64p36d9i60o30c1g", ImmutableMap.of("metadata['date']", 1401235200000L) };

        assertArrayEquals(row1, response.rows()[0]);
        assertArrayEquals(row2, response.rows()[1]);
    }

    @Test
    public void testAnyInformationSchema() throws Exception {
        execute("create table any1 (id integer, date timestamp, names array(string)) partitioned by (date)");
        execute("create table any2 (id integer, num long, names array(string)) partitioned by (num)");
        ensureGreen();
        execute("select table_name from information_schema.tables where 'date' = ANY (partitioned_by)");
        assertThat(response.rowCount(), is(1L));
        assertThat((String) response.rows()[0][0], is("any1"));
    }

    @Test
    public void testDynamicObjectPartitionedTableInformationSchemaColumns() throws Exception {
        String stmtCreate = "create table data_points (" +
                "day string primary key," +
                "data object(dynamic)" +
                ") partitioned by (day)";
        execute(stmtCreate);

        String stmtInsert = "insert into data_points (day, data) values (?, ?)";
        Map<String, Object> obj = new HashMap<>();
        obj.put("somestringroute", "stringvalue");
        obj.put("somelongroute", 1338L);
        Object[] argsInsert = new Object[] {
                "20140520",
                obj
        };
        execute(stmtInsert, argsInsert);
        assertThat(response.rowCount(), is(1L));
        waitForMappingUpdateOnAll("data_points", "data.somestringroute");
        refresh();

        String stmtIsColumns = "select table_name, column_name, data_type " +
                "from information_schema.columns " +
                "where table_name = 'data_points' " +
                "order by column_name";
        execute(stmtIsColumns);
        assertThat(response.rowCount(), is(4L));

        String expected = "data_points| data| object\n" +
                "data_points| data['somelongroute']| long\n" +
                "data_points| data['somestringroute']| string\n" +
                "data_points| day| string\n";
        assertEquals(expected, TestingHelpers.printedTable(response.rows()));
    }

    @Test
    public void testRegexpMatch() throws Exception {
        serviceSetup();
        execute("create blob table blob_t1 with (number_of_replicas=0)");
        ensureYellow();
        execute("select distinct schema_name from information_schema.tables " +
                "where schema_name ~ '[a-z]+o[a-z]' order by schema_name");
        assertThat(response.rowCount(), is(2L));
        assertThat((String)response.rows()[0][0], is("blob"));
        assertThat((String) response.rows()[1][0], is("doc"));
    }

    @Test
    public void testSelectSchemata() throws Exception {
        execute("select * from information_schema.schemata order by schema_name asc");
        assertThat(response.rowCount(), is(4L));
        assertThat(TestingHelpers.getColumn(response.rows(), 0), is(Matchers.<Object>arrayContaining("blob", "doc", "information_schema", "sys")));

        execute("create table t1 (col string) with (number_of_replicas=0)");
        ensureGreen();

        execute("select * from information_schema.schemata order by schema_name asc");
        assertThat(response.rowCount(), is(4L));
        assertThat(TestingHelpers.getColumn(response.rows(), 0), is(Matchers.<Object>arrayContaining("blob", "doc", "information_schema", "sys")));
    }

    @Test
    public void testOrphanedPartition() throws Exception {
        prepareCreate(".partitioned.foo.04138").execute().get();
        execute("select table_name from information_schema.tables where schema_name='doc'");
        assertThat(response.rowCount(), is(0L));
    }

    @Test
    public void testSelectGeneratedColumnFromInformationSchemaColumns() throws Exception {
        execute("create table t (lastname string, firstname string, name as (lastname || '_' || firstname)) " +
                "with (number_of_replicas = 0)");
        execute("select column_name, is_generated, generation_expression from information_schema.columns where is_generated = true");
        assertThat(TestingHelpers.printedTable(response.rows()),
                is("name| true| concat(concat(lastname, '_'), firstname)\n"));
    }
}


Shop with the excellent First for Paper promo codes & offers at a discount price.
Save money shopping online at First for Paper. Get the latest First for Paper Voucher Codes to receive up to 15% off for April 2019. You can find 3 First for Paper Voucher Codes on our page for First for Paper now.


text:
UK aerospace: reaching new heights
Posted on 19 Sep 2016 by Jonny Williamson
With demand for new aeroplanes predicted to exceed 38,000 by 2035, Jonny Williamson explores how the nation's aerospace sector is ideally placed to take advantage of this £4.3 trillion opportunity.
It has become almost a cliché to extoll the virtues of the UK aerospace industry, so often are its numerous successes discussed and championed.
Currently the largest in Europe, and second globally only to the US, the impressive growth of the sector is testament to the work being carried out by companies the length and breadth of the nation.
Across almost every metric, numbers have soared. The sector currently contributes more than £31bn to the economy every year, with annual exports in excess of £27bn (nearly 90% of total output) – in 2013, those figures were £28bn and £25bn respectively.
The sector directly supports more than 128,000 skilled jobs, with a further 154,000 indirectly. Its productivity growth continues to vastly outpace the national average (30% versus just 2%), and employs increasing numbers of trainees and apprentices – 60% of aerospace companies reportedly now offer apprenticeships, compared to just 15% in 2009.
Future opportunities
It's predicted that air passenger numbers will rise by 80%, to 218 million by 2034. Accordingly, every one of the world's airlines – from flag carriers down to low-cost budget brands – is looking to replace and augment their in-service fleets with ever more efficient aircraft.
That's "great news", according to trade body, ADS, as it would allow UK aerospace to "continue to punch above its weight in supplying world-leading parts, technology, components and services."
As far as airlines – and arguably their passengers – are concerned, there are only a handful of pertinent questions asked of new aircraft: Is it stronger? Is it lighter? Is it quieter? Is it more environmentally friendly?
UK aerospace companies can – and indeed are – offering the answers to such questions, as ADS chief executive, Paul Everitt states; "At the forefront of this technological innovation is the UK's world-leading aerospace industry.
"From wings to engine, through to interiors and greater on board connectivity; the innovation, design and manufacture of new technology derived from UK industry will make a difference to the future of UK aviation."
Yet, for comparable innovation to continue, a strong, secure pipeline of associated research and development needs to be maintained.
CEO of the Aerospace Technology Institute (ATI), Gary Elliott, succinctly sums up the prevailing landscape in the organisation's recently published strategy update.
"The UK provides a very attractive environment for innovation, offering financial support, a growing Catapult network, excellent universities and research organisations, and access to high quality skills," he notes.
ADS: UK Aerospace Outlook Report 2016 – bit.ly/ADSoutlook
ATI: Technology Strategy & Portfolio Update 2016 – bit.ly/ATIstrategy
AGP: Industrial Strategy for UK Aerospace 2016 – bit.ly/AGPstrategy
"However, international competition to attract high-value aerospace work is formidable – from established and emerging economies.
"Other nations, including those with a similar share of the global market, continue to spend heavily on aerospace-related research and development, narrowing an advantage the UK has long enjoyed."
So how can the UK retain its globe-spanning status as an aerospace innovator and leader?
In a nutshell – collaboration and investment.
Announced as part of the Aerospace Growth Partnership's (AGP) industrial strategy for 2016, the UK Aerospace Supply Chain Competitiveness Charter aims to see Prime and Tier One companies commit to promoting structured continuous improvement programmes and share growth opportunities with suppliers.
According to the mission statement, the Charter is an attempt to break down the barriers to future productivity and growth by disseminating new technology and fostering a more robust culture of innovation – particularly within the SME supplier community.
Rolls-Royce is one of 11 companies to have signed the UK Aerospace Supply Chain Competitiveness Charter to date – image courtesy of Rolls-Royce.
To date, 11 companies have signed the agreement, including Airbus; Boeing; Bombardier; GE; GKN Aerospace; Leonardo Helicopters; Rolls Royce; Safran; Spirit; Thales, and UTC Aerospace Systems.
According to the AGP, poor supply chain management is a barrier to productivity and growth; "Improved mutual understanding and co-operation between suppliers and higher tier customers will break down that barrier."
The Charter aims to mean more than Prime and Tier One's making demands of their UK suppliers; "It also entails suppliers benefiting from the information, skills and technology usually available to customers alone.
"Improved inter-company relationships and communication will build commitment to continuous improvement and drive positive change throughout the chain."
Renewed investment
With aerospace firmly considered one of the jewels in UK manufacturing's crown, it was perhaps inevitable that it would catch the eye of those in Westminster. As such there has been a spate of government-backed initiatives over the past half-decade, ATI arguably being the most important.
Launched in 2013, the ATI is an independent collaboration between industry and government charged with leading the UK's aerospace technology strategy.
With £2bn of secured research and technology (R&T) investment, around half has already been put towards initiating almost 150 new projects exploring a wide range of technologies within four broad themes:
Aircraft of the future – strengthening the UK's whole-aircraft design and system integration capability
Smart, connected aircraft – developing UK advanced systems technologies to capture high-value opportunities
Aerostructures of the future – ensuring the UK is a global leader in the development of large complex structures, particularly wings
Propulsion of the future – advancing a new generation of more efficient propulsion technologies, particularly within large turbofan engines
According to the ATI; "Across all areas, there is a pressing need within the next five years to increase productivity and lower manufacturing costs."
To achieve that, and to maximise every pound at the ATI's disposal, it shouldn't just be R&T which should be the focus of future investment; skills are just as important – especially at the higher end.
High skills
There are numerous similarities between automotive and aerospace, so it's probably no great coincidence that the UK has become a world leader in both.
However, with a nationwide lack of highly skilled graduates and apprentices, rivalry between the two sectors to attract the top percentile is fierce – with those below each sector's Prime and Tier One companies particularly feeling the pinch.
As is true for manufacturing as a whole, closer collaboration between all major stakeholders – industry, academia and government – is necessary to ensure future engineers and innovators have the required skills, tools and mindset for the UK to compete in an increasingly competitive global market.

redpajama_set_name: RedPajamaCommonCrawl


It can sometimes be difficult to think of something witty or wonderful to say when wishing family and friends a happy birthday. That's why we've put together a birthday message guide with 120 of the best wishes you can send.
Whether you're sending birthday wishes to your boss, close friend or colleague, you can use the guide to be cheeky, sentimental or even find a famous quote. We've even included a table full of nostalgia you can use as ammunition to remind them just how old they are!
If you're looking for a way to make a those Mother's Day roses, graduation flowers or birthday arrangements stay vibrant and blooming for longer, follow our in depth flower care guide.
Just like counting sheep to fall asleep, it seems everyone has a theory of how to keep their flowers lasting longer.
Sending flowers to loved ones is one of the most powerful ways to express your heart-felt emotions.Sending flowers that express your sentiments through their unique symbolism can take a special gift & make it a gift that will never be forgotten.
We have put together a collection of perennial flowers that are commonly found in our arrangements along with their meanings & associations to help your sentiments go further & truly wow your loved ones. We also asked 1000 people what they thought about flower meanings in the 21st century.
As well as popping flowers in a vase, you can also pop them in the oven! Edible flowers are becoming increasingly popular with our food from prettying up our cakes and bakes to pepping up salads, jellies and cordials.
But eating flowers isn't just something fancy chefs and The Great British Bake Off have revived - medieval monks were using Calendula in their cooking pots and the Victorians topped desserts with candied Violets, associating them with elegance. Carnations, Day Lily and Nasturtium petals were also thought to be good for you with body-cleansing properties as well as attractive garnishes, says GardenGuides.com.
We are proud to introduce our stunning new Band of Sisters range.
This year, Help for Heroes is celebrating its 10th birthday. We were delighted to help with the celebrations and create a stunning new collection of bouquets to support this great cause. We've been proud supporters of Help for Heroes for over two years donating 15% of the sale price from a number of gorgeous bouquets and plants to the charity.


king bed collection full size of bedroom beautiful king bedroom sets best price king size bed king bedroom furniture king size bed set walmart california king storage bed set.
white led light strings white battery powered led string lights fairy lights lights battery operated 50 warm white led string lights outdoor timer warm white led string lights outdoor.
how to vent a stove hood outside outdoor vent hood outdoor vent hood kitchen vent hood club inside decorations outside range hood vent stove hood fan filters gas stove vent hood height.
white desks with hutch and drawers white desk with hutch off white office desk hutch overstock shopping big white desk and hutch white desk with hutch white corner desk with hutch and drawers white de.
cheap tvs stands tv stands with fireplace small corner tv stand ikea.
rug tiles cheap how to install residential carpet tiles in home carpet tiles buy online india carpet tiles cheap melbourne.
diy metal garage kits metal garage kits carport garages metal with carport garage diy steel garage buildings diy steel garage kits uk.
rustic vanity base nestle a sculptural sink on salvaged wood and make a vanity cabinet rustic wood vanity base.


If you are looking to increase your Internet presence in People's Republic of China, a .ORG.CN domain name would be the best choice for you. Google has openly admitted that domains with country-code TLDs will show up higher in local search results. Plus, people have a tendency to click more frequently on sites with a local extension.
At Apache Website Hosting, you can purchase a .ORG.CN domain name for just $10.50 a year and enjoy an exclusive-quality domain name management service.
Apache Website Hosting's custom-made Domain Manager will enable you to control all your .ORG.CN domain names, and their WHOIS, Registrar-Lock, DNS, etcetera controls, in an outstandingly user-friendly way. The elaborate help tutorials will help you with every problem that you could come across. Of course, you can always rely on our qualified technical support team, who are at your disposal 24/7.
The Domain Manager is an essential part of Apache Website Hosting's avant-garde Web Hosting Control Panel, this means that when you in addition have a hosting account with us, you are able to control both your domain names as well as your sites from one and the same place!


Get all 19 SVART1 releases available on Bandcamp and save 35%.
Includes unlimited streaming via the free Bandcamp app, plus high-quality downloads of SVR, OBRAZY STAREHO SVETA (extract from Live set with Natalia Bocco at May Mask 2018), ICH VERACHTE DEN TOD, ICH LACHE MICH ZU TODE, BELET ILI (III SH act), 2006-2009 SVART1 AMBIENT EARLY WORKS (digital only), AD EXTREMAS (Live "Ex Chaos" at Chiesa di San Saturnino, for "Music in Touch", Cagliari 2009), PRAHA (Live set Prague, 2012), ARDAT LILI (II SH act), and 11 more. , and , .
Svart1 is Raimondo Gaviano, who works in the fields of Ambient, Dark Ambient, Industrial, Noise, live-performance and visuals.


Thank you for contacting Frontier Horizons.
We are currently reviewing your request and a team member will be in contact with you shortly.
In the meantime, for further updates regarding Frontier Horizons' Global Operating Platform, follow us on Twitter @FrontierRisk.


text:
Trump Booed, Impeachment Banners Hung At World Series
Submitted by Luke on Mon, 10/28/2019 - 01:39
Video compiled from many sources on Twitter 1 min 15 sec
On the 27th of October, Donald Trump thought he could turn the 5th game of the World Series (Wash Nationals vs Houston Astros) into a campaign rally for himself. Instead, the whole ballpark erupted into a roaring chorus of boos as soon as he was shown on the jumbotron from his fancy suite. This escalated to chants of "lock him up!" and two separate banners calling for impeachment were set up.
Trump had to sneak in after the first pitch and slink away before the last out, supposedly for traffic reasons but this also made it much harder for fans to do things like say, protesting his motorcade on the way in and out. Such protests have occurred at every State of the Union Address Trump has given, but not with tens of thousands of angry baseball fans in attendance.
Baseball fans tend to be older white men, whom Trump no doubt thought was a favorable demographic. Not in Washington DC it seems! All the rich developers who built the stadium with 100% tax money and a whopping side order of eminent domain certainly didn't expect to see a real estate thug like themselves take such a crushing defeat in their project. As for the fans and DC residents, it looks like they hit this one right out of the ballpark!


Firearms regulations in St. Johns County are outdated and may be illegal.
For decades, the county has issued licenses based on standards set before 1977, Commissioner Mark Miner told board members and county staff last week. The rules say someone 18 or older can apply for a concealed weapons license.
They also say applicants must have good moral character, that if a license is issued it's good for two years and that the issuance of the license is conditional until a $100 bond is paid to the state. Also, under the law, applicants can be approved if they committed a felony at least two years prior, because their civil rights have been restored. The applicant also must not have been committed to a mental institution.
State law states that any existing and future county, city, town or municipal ordinances regulating firearms are null and void. That's why St. Johns County attorneys are reviewing the ordinances and are set tentatively to report back to the commission on Tuesday, April 19, said Karen Pan, county spokeswoman.
When the County Commission votes to revise or repeal the law, it intends to fall in line with the state laws. The state law allows for licenses to be valid throughout the state for seven years from the date of issuance. Licensees must carry the license along with valid identification when in possession of a concealed weapon. They must be a resident and citizen of the United States or a permanent resident alien.
Call Shakaya at (904) 359-6319.


text:
Marcos Jesús Morales Sosa es un actor, cantante, músico y humorista español nacido en Las Palmas de Gran Canaria, conocido por su nombre artístico de Chifo en Los Trilocos de La 2, programa en que apareció desde su creación hasta su desaparición, entre 1999 y 2004.

Comienzos y llegada a la fama

Es más conocido con el nombre de Chifo al ser uno de los protagonistas principales del famoso programa de Televisión Española Los Trilocos.

Nace en Las Palmas de Gran Canaria y desde la corta edad de cuatro años se dedica al mundo del espectáculo, concretamente en sus facetas musicales y de interpretación.

Desde 1976 se da a conocer en todo el archipiélago canario, recorriendo las Islas Canarias y teniendo la oportunidad de realizar diversas grabaciones discográficas con el género de la música mexicana, concretamente dos: una, a la edad de ocho años, con una de las canciones más famosas de la época (La Mochila Azul); y la segunda a la edad de once años, un poco más tarde, con canciones producidas para el cantante por algunos autores de la tierra mexicana.

A los diecinueve años, y esperando que el cambio de voz de niño a hombre fuera el correcto, graba su tercer disco, al que tituló Sentimientos. En esta ocasión intercaló música mexicana, bolero ranchero y balada. En esta ocasión se preparó musicalmente cursando estudios musicales en el Conservatorio de Las Palmas, estudiando el instrumento de piano y todo lo que conllevaba el mundo de la música profesional.

En el año 1994 da el salto definitivo a la Península y recala en Madrid, recorriénde la geografía del país. Tuvo la oportunidad de realizar también giras de largas temporadas en otros países como Portugal, Turquía, Grecia e Inglaterra como solista, incorporándose más tarde como solista masculino en el grupo mariachi Los Charros de España.

A lo largo de su carrera profesional ha compartido escenario con muchos intérpretes del panorama nacional, como por ejemplo Parchís, Tito y Tita, Botones, Martes y Trece (cuando lo componía el trío), José Luis Moreno, Bertín Osborne, Emmanuel, María del Monte, Mari Carmen y sus Muñecos, Peret, María Vidal, Mariano Mariano, Kimbo, Manolo de Vega, Concha Velasco, Chavela Vargas, José Luis López Vázquez o Ketama.

Realizó numerosas apariciones televisivas en algunos programas, como los de Laura Valenzuela, María Teresa Campos, Jesús Hermida e Irma Soriano. Incluso participó en películas protagonizadas por Loles León y Salma Hayek.

Proyectos Discográficos.

En el año 1993, lanza al mercado un CD titulado Sentimientos.
Disco dedicado al género de la canción mexicana y bolero, con canciones populares de México, y creaciones hasta entonces inéditas compuestas por autores y compositores de Canarias, incluyendo dos temas, cuya composición musical, fue creada por el propio Marcos Jesús, cuyos nombres son Sentimientos y Brindis por tu adiós.

En el año 2017, lanza un disco titulado Recordando  Gaby, Fofó y Miliki, Los Payasos de la Tele. Disco dedicado al género infantil con canciones típicas de Los Payasos de la Tele; ya disponible en todas las plataformas digitales.

 Trilocos

En 1999, Emilio Aragón Bermúdez Miliki le ficha para la pequeña pantalla de La 2 de Televisión Española y actúa así en uno de los programas infantiles de más éxito de la generación, los Trilocos, junto a Alfonso Aragón Fofi, José Manuel Guisado Mané, Íñigo Aldekoa Metáfora y, más tarde y sustituyendo al desaparecido Fofi, Juan Carlos Martín en el papel de Nico.

Realizan 180 episodios en un total de dos temporadas en TVE 2, haciendo un programa que en poco tiempo se hizo con la audiencia de los niños y los mayores, ya que el formato recordaba mucho al de Los payasos de la tele.

Más tarde, lanzan un proyecto discográfico con las canciones del programa, creando éxitos musicales que marcarían una generación como Nos vamos de pi, Mi canción, Que sí, que no, Toca el botón'', entre otras.

Actualidad
Hoy por hoy realiza una gira por toda España representando y dirigiendo diferentes musicales como Peter Pan o La Bella y la Bestia sin perder la esencia del programa Trilocos con una presentación general del programa, además de compaginar con el mundo de la música ejerciendo como cantante, productor musical, compositor, arreglista e intérprete de piano.

Enlaces externos
 Página oficial de Chifo

Actores de televisión de España
Actores de la provincia de Las Palmas
Nacidos en Las Palmas de Gran Canaria

redpajama_set_name: RedPajamaWikipedia


Fuller Center for Housing of Houston, MS located in Chickasaw County in the northeastern corner of the State of Mississippi. They are a rural agricultural community with a population of about 20,000 with more than 16.8% of families and 20% of the population below the poverty line. As a former Habitat for Humanity affiliate-Fuller Center for Housing of Houston, MS has been working to improve the housing needs of their community for some time. They are focused on homes that need repair along with building affordable homes in the service area of Chickasaw County.


text:
Algonquin: Should You Catch the Falling Knife?
Higher interest rates are making Algonquin Power & Utilities (TSX:AQN) an increasingly riskier investment. Investors saw their position lose 42% year to date. Utility stocks are mostly bought or held for their predictability and stability. Algonquin stock was anything but predictable and stable.
Since September, it has been a falling knife. Needless to say, it's a speculative investment now. On one hand, investors must be careful about catching falling knives that could hurt their portfolios, as such stocks could be value traps. On the other hand, if AQN stock turns out to be a deep-value stock, it could deliver outsized returns for investors that jump in now and can withstand the high volatility.
Why AQN stock fell off a cliff
Initially, the utility stock had been weaker than its peers because of its higher debt levels. Though its S&P credit rating is investment grade at BBB, it's lower than its larger peers like Fortis's credit rating of A-. Any excessive levels of debt is seen as negative in today's higher interest rate environment.
Furthermore, AQN stock tumbled more than 30% in a matter of two trading days. The fall occurred right after it reported its third-quarter (Q3) results. For the quarter, the company reported 26% higher in revenue but a 27% drop in adjusted earnings per share (EPS) to US$0.11.
This was below expectations and management lowered its 2022 EPS estimate to US$0.66-$0.69. This implies an extended 2022 payout ratio of about 107% based on the midpoint of the estimate. So, there's an elevated concern of a dividend cut.
Essentially, there are multiple factors that have increased the risk of a dividend cut, such as higher interest expenses and lower earnings. And again, the company has tonnes of debt on its balance sheet. Its debt-to-equity ratio is 1.98 times.
How big could the dividend cut be?
Yesterday, AQN stock closed at $10.61 per share and yields almost 9.1%.
Given Algonquin's balance sheet with higher debt levels, it would serve the company well to reduce its dividend to help pay off higher-interest debt. A healthier payout ratio could be 60%, which would imply a payout of US$0.405 per share based on the midpoint of management's 2022 EPS guidance. If so, it would be a dividend cut of 44%. Based on the recent closing price, it would imply a forward yield of 3.8%.
The Foolish investor takeaway: Should you catch the falling knife?
Analysts have a consensus 12-month price target of US$14 per share on the stock, which implies a discount of 43%. The market is pricing in a dividend cut.
In multiple cases, stocks have jumped after dividend cut announcements. For a more sustainable turnaround, the company would need to demonstrate improved business performance, which can come from better execution or an improved macro environment.
AQN's third-quarter financial report stated, "Given the challenging macroeconomic environment, which is expected to continue into 2023, the company is evaluating its longer-term targets and financial expectations." Investors should look for more details (and likely a dividend cut) in the Investor and Analyst Day that's expected to occur in early 2023.
Higher-risk investors might consider AQN as a total-return investment. Just ensure proper asset allocation to diversify risk in your portfolio. Additionally, it would be more prudent to wait for some consolidation in the stock before buying (more) shares.
The post Algonquin: Should You Catch the Falling Knife? appeared first on The Motley Fool Canada.
Should You Invest $1,000 In Algonquin Power and Utilities?
Before you consider Algonquin Power and Utilities, you'll want to hear this.
Our market-beating analyst team just revealed what they believe are the 5 best stocks for investors to buy in November 2022 … and Algonquin Power and Utilities wasn't on the list.
The online investing service they've run for nearly a decade, Motley Fool Stock Advisor Canada, is beating the TSX by 15 percentage points. And right now, they think there are 5 stocks that are better buys.
See the 5 Stocks
* Returns as of 11/4/22
setButtonColorDefaults("#5FA85D", 'background', '#5FA85D');
setButtonColorDefaults("#43A24A", 'border-color', '#43A24A');
setButtonColorDefaults("#fff", 'color', '#fff');
Algonquin Power Stock: 3 Things to Know About the Crash
Worried About Algonquin? This TSX Utility Stock Looks Far More Attractive
TSX Today: What to Watch for in Stocks on Tuesday, November 15
TFSA: Invest $50,000 and Get $290-$340/Month in Passive Income
Why This Energy Giant Looks Like a Major Bargain
Fool contributor Kay Ng has a position in Algonquin. The Motley Fool has no position in any of the stocks mentioned. The Motley Fool has a disclosure policy.
This Semiconductor ETF Might Signal a Chip Recovery
3 High-Yield Dividend Stocks to Make You Forget about Inflation


This stylish warm grey Vertical blind features a blackout coating which creates a dim out effect. A dim out effect means rooms are darkened but not made completely dark because of the light seepage at the edge of the louvres, and at the top and bottom of the blind. It's perfect for controlling glare in conservatories or sunny spaces, and will also help to block the light in a bedroom. The contemporary shade means it's brilliantly versatile and at home in a whole host of different settings.


text:
Vivien Leigh Leaked Nude Video
Laurence Olivier and Vivien Leigh
She became immortalized as Scarlet O'Hara in the 1939 Civil War romance film, Gone with the Wind. Her other notable films include Fire Over England, A Streetcar Named Desire, and Tovarich. She studied at the Royal Academy of Dramatic Art in London. She suffered from a devastating bipolar disorder. She was married to Laurence Olivier from 1940 to 1960. She starred with Marlon Brando in the movie A Streetcar Named Desire.
Age: Jul 7, 1967 (age 53)
Birthday: November 5, 1913
Born: India
Other: Judy Reyes, Kat Deluna, Keri Hilson, Maria Sansone, Olivia Newton-John, Parker-McKenna Posey, Sarah Wynter, Zendaya Coleman, Angela Saunders, Assunta De Rossi, Chelsea Chanel Dudley, Elissa, Freja Beha Erichsen, Jasmine Curtis Smith, Jennifer Lamiraqui, Juliette Lamboley,
Topless. Gets Tapped for Shoots.
Topless. On The Beach.
Topless. Strips off in the desert.
Naked. Bares Topless Breasts.
Vivien Leigh l iː born Vivian Mary Hartley and styled as Lady Olivier after 1947 5 November 1913 – 8 July 1967) was a British stage and film actress. She won two Academy Awards for Best Actress, for her iconic performances as Scarlett O&aposHara in Gone with the Wind (1939) and Blanche DuBois in the film version of A Streetcar Named Desire (1951 a role she had also played on stage in). Here are the leak nude photos of actress Vivien Leigh that were among the second wave of leaks from the celebrity iCloud hacking scandal. Vivien Leigh nude photos are a treasure for everyone&aposs eyes to see. Vivien Leigh hacked iCloud pictures.
Vivien Leigh is famous for beating 1400 other actresses to play Scarlett O'Hara in Gone With The Wind. But Leigh's own life, which was filled with dramatic highs and lows, was as colorful and. From its adulterous beginnings to the final years of cold indifference, the relationship between Laurence Olivier and Vivien Leigh was one of the great love stories of the 20th Century.
The Hollywood love story of Vivien Leigh and Laurence Olivier is remembered today as one filled with passion, romance, betrayal, and heartbreak.
14. Vivien Leigh's Gone With the Wind Oscar was sold at auction fetching $510,000 in 1993, at that time, the highest amount ever paid for an Academy Award. It's now part of the James Tumblin Collection.
"Vivien would get along fine for a few weeks, a few months — be perfectly normal and friendly and involved in her activities," an old schoolmate of Leigh's said. "Then, suddenly, a.
Vivien Leigh Sexy Clip
And now that very same homestead has come onto the market – 80 years after the movie starring Clark Gable and Vivien Leigh was released to packed houses.
Vivien Leigh was a tiny, frail actress from the Himalaya mountains who won enduring fame for her fiery film performance as Scarlett O'Hara in "Gone With the Wind." The two-time Academy Award-winning . It carried Clark Gable and Vivien Leigh in a parade prior to the 1939 premiere of "Gone With The Wind," then later, Bob Hope, Doris Day and then-Georgia Gov. Jimmy Carter.
In her personal planner, Vivien Leigh documented the demise of her first marriage with five simple words.
The remarkable archive of over 800 images includes behind-the-scene snaps of the lead actors Clark Gable and Vivien Leigh unwinding between takes. MARIETTA, Ga.
From Vivien Leigh's signature frizzy curls in Gone With The Wind to Twiggy's pixie crop and a retro take on wet-look hair, these are the vintage hairstyles to be inspired by this summer. Playing two of the greatest female roles ever offered in film — Scarlett O'Hara in "Gone with the Wind" and Blanche DuBois in "A Streetcar Named Desire" — has guaranteed actress Vivien Leigh a place .
It carried Clark Gable and Vivien Leigh in a parade prior to the 1939 premiere of "Gone With The Wind," then later, Bob Hope, Doris Day and then-Georgia Gov. Jimmy Carter.
Vivien Leigh and Laurence Olivier got married there, and John Huston finished his script for The African Queen during his stay.


text:
DR. LORENZ
We are pleased to announce the development of several new group treatment programs. These were developed recently for the following populations, and adapted from previously offered successful evidence based and researched programs:
First responders and allied professionals.
Front-line healthcare workers and medical personnel (currently suffering from COVID related burn-out and mental health issues).
Patients with chronic and treatment resistant types of depression.
Patients with chronic and treatment resistant anxiety disorders.
Patients with chronic medical conditions such as chronic pain conditions and syndromes.
Depending on the group participant population, different priorities and goals may be set; triaging will be part of the process to start.
Prerequisites for acceptance into the programs and participation in any of the currently announced group and individually structured programs for anyone interested in these treatment programs are as follows:
The interested individual needs to have or did recently participate in individual treatment and has a treatment provider or therapist and is willing to commit to individual support, so that the group programs are supported by an individual psychologist or other treatment provider, who will support the process on an individual basis. Collaboration between treatment providers is also a prerequisite, for the benefit of the group participant, and for prevention of any adverse reactions to the program.
The group treatment programs are specifically developed based on previously offered group treatment programs which are evidence-based and have been part of a double-blind process research protocol in the past. All programs are 10-week closed coping skills group programs which implement an integrative approach between coping skills and more recently updated protocols and treatment approaches, which predominantly will use hypnotic process. These are offered in combination with well-known therapeutic process-based approaches.
Psycho-education for the development of resilience and coping skills will be one recognized goal, and is offered in combination with the development of a focus on positive visions for future goals, the enhancement and development of new and stronger resilience factors and establishment quality of life.
Learn more about our programs here.
JOURNEY WITHOUT MEDICINE
A New Structured Group Treatment Program
This will be a six-week structured group program with a maximum of five participants, which will lead participants through inspired ways of determining needs for change, defining goals and arriving at purpose. This will be offered to participants who have already completed sufficient treatment sessions to enable them to move forward and defining any new searches for goals and purpose in their lives. The program is "closed", meaning that it is a program that builds from one session to the other and cannot be started in the middle of the program.
Individuals who have completed the Coping Skills Programs also offered in my practice and described on my website, may wish to take part in this smaller group program, where a more defined and focused journey to create "sanctuary", purpose and life goals can be accomplished.
Group formats are helpful where companionship with others enhances the treatment goals. The main focus method will be guided by hypnotic approaches. This is based on the premise that alternative and sometimes unconscious processes can be much more efficient and helpful to achieve a better outcome than remaining with purely conscious processes, such as didactic and cognitive-behavioural approaches alone.
REFERRALS
When needs are identified that cannot be addressed in our practice, we refer to other practitioners experienced in the requested areas.
You may contact us via telephone or via a contact form. Please do not include an personal or private medical information in the contact form.
A brief intake process is completed over the telephone or via email.
© 2022 Dr. Annette Lorenz. All Rights Reserved. | Tel: 905-338-2195 | Fax: 905-338-2508 | Contact us


The Altai Spa is suitable for installation on the central heating system or with an immersion heater for summer use when the central heating is turned off. With its single or double tube options, the Altai Spa can meet all bathroom-heating requirements. It is also available as an oil-filled electric radiator. Pegs and towel ring accessories are available in different colours, including metallic.


I think the best way to deal with it is to make the triggering mechanism capable of applying a lot of force quickly. Perhaps an electromagnet would be appropriate. I suspect it's also quite important to minimize the mass of the moving linkage pieces. Another possible solution is to place the pivot of the fixed arm very close to the flywheel and use a cam to drive the flipper arm. With proper cam shape, it would be possible to smooth out the force profile a bit and avoid loading the mechanism until the linkage is fully engaged.
I like electromagnets I've been trying to use them in my bots for years. The best-working friction roller clutch I had on Magneato used electromagnets to capture the mechanism and springs that spring it into engagement when I turned them off. It was a little tricky to get right but drammatically reduced the firing reset time. Pretty complicated though..
I built a new beetleweight and brought it to the Robojackets 20th Anniversary Competition yesterday. Its name is Impulse Response and it's an undercutter with inspiration taken from Silent Spring. I'm really happy with this design and it was pretty cheap and easy to make.
It's been a while since I've done any combat robotics stuff, but it's good to be back.
Super happy to see you back at it. I thought Event Horizon was awesome in 2016 and since you ended up going to GT I kinda got excited (BSME, BSEE 2013 woot woot!).
I dig the name too, although it sounds like yall called it Event Horizon on the stream anyway, haha.
I remember being impressed by Event Horizon by then so its good to see you make new cool things! Hopefully you can go to Franklin in October or some other insect event in the summer and we can see more of your stuff! The Arduino was also a nice touch to mix the channels. I know Fingertech also has the tinyMixer if you ever need to shrink your footprint, but otherwise excellent work!
It looks like we both got some inspiration from Silent Spring and wound up with some strikingly similar bots.
Maybe if all 3 of us make it to Franklin this year we can have a battle of the 3D printed undercutters!


text:
Tris[4-(diethylamino)phenyl]methylium acetate
Regulatory process names 2 CAS names 1 IUPAC names 5 Other identifiers 1
Mol. formula:
Danger! According to the classification provided by companies to ECHA in REACH registrations this substance is toxic if swallowed, is very toxic to aquatic life, is very toxic to aquatic life with long lasting effects, causes severe skin burns and eye damage, causes damage to organs through prolonged or repeated exposure, causes serious eye damage, may cause an allergic skin reaction and causes skin irritation.
This substance is registered under the REACH Regulation and is manufactured in and / or imported to the European Economic Area, at ≥ 100 to < 1 000 tonnes per annum.
This substance is used in articles, by professional workers (widespread uses), in formulation or re-packing, at industrial sites and in manufacturing.
Release to the environment of this substance can occur from industrial use: industrial abrasion processing with low release rate (e.g. cutting of textile, cutting, machining or grinding of metal).
Other release to the environment of this substance is likely to occur from: indoor use in long-life materials with low release rate (e.g. flooring, furniture, toys, construction materials, curtains, foot-wear, leather products, paper and cardboard products, electronic equipment) and outdoor use in long-life materials with low release rate (e.g. metal, wooden and plastic construction and building materials).
This substance can be found in products with material based on: leather (e.g. gloves, shoes, purses, furniture), paper (e.g. tissues, feminine hygiene products, nappies, books, magazines, wallpaper) and fabrics, textiles and apparel (e.g. clothing, mattress, curtains or carpets, textile toys).
This substance is used in the following products: textile treatment products and dyes.
This substance is used for the manufacture of: textile, leather or fur.
Other release to the environment of this substance is likely to occur from: indoor use.
This substance is used in the following products: paper chemicals and dyes, leather treatment products, inks and toners, polymers and textile treatment products and dyes.
Release to the environment of this substance can occur from industrial use: formulation of mixtures.
This substance is used in the following products: paper chemicals and dyes, inks and toners, leather treatment products, textile treatment products and dyes and polymers.
This substance is used in the following areas: printing and recorded media reproduction.
This substance is used for the manufacture of: pulp, paper and paper products, textile, leather or fur, wood and wood products and plastic products.
Release to the environment of this substance can occur from industrial use: in the production of articles.
Properties of concern Properties of concern
The 'Properties of concern' section shows ECHA-assigned graphical indicators for certain substance properties that are regarded as being of relevance or importance to human health and/or the environment based on the information provided to the Agency.
Properties of concern are calculated at four "levels" of certainty:
"Recognised" - meaning that the concern is indicated in an official source. Recognised concerns are illustrated with a dark red icon. Sources for these are either a Harmonised C&L (CLP Regulation Annex VI) or in the Candidate list of substances of very high concern for authorisation (REACH).
"Potential" - again comes from official sources only. Potential concerns are illustrated with a light red icon. For (C), (M), and (R) it means that the concern is suspected in a Harmonised C&L (CLP Regulation Annex VI), as Carc. 2, Muta. 2, or Repr. 2. For (PBT) and (ED) Potential means that the concern is under assessment in the PBT or ED assessment list, and the outcome indicates a potential ED. There are no potential (Ss) or (Sr)s.
"Broad agreement" - comes from data submitted by industry to ECHA, and indicates that the data submitted is aligned, with >= 50% of the data submitters providing the same concern. Broad agreement concerns are illustratated with a solid outlined circle icon.
"Minority position" - comes from data submitted by industry to ECHA, and indicates that the data submitted is not aligned. > 5% and < 50% of the data submitters have provided the concerns indicated at this "level". Minority position concerns are illustrated with a greyed out circle icon.
The following properties of concern are calculated:
Carcinogenic (C) – Recognised carcinogen: comes from a harmonised C&L classifying the substance as Carc. 1A or 1B and/or an entry in the Candidate list. Potential carcinogen: comes from a harmonised C&L classifying the substance as a suspected carcinogen Carc.2. Broad agreement: comes from industry data where a majority of data submitters agree the substance is carcinogenic. Minority position: comes from industry data where a minority of data submitters indicate the substance is carcinogenic. More information about carcinogenicity here.
Mutagenic (M) – Recognised mutagen: comes from a harmonised C&L classifying the substance as Muta. 1A or 1B and/or an entry in the Candidate list. Potential mutagen: comes from a harmonised C&L classifying the substance as a suspected mutagen Muta. 2. Broad agreement: comes from industry data where a majority of data submitters agree the substance is mutagenic. Minority position: comes from industry data where a minority of data submitters indicate the substance is mutagenic. More information about mutagenicity here.
Toxic to Reproduction (R) – Recognised as toxic to reproduction: comes from a harmonised C&L classifying the substance as Carc. 1A or 1B and/or an entry in the Candidate list. Potentially toxic to reproduction: comes from a harmonised C&L classifying the substance as suspected toxic to reproduction Repr. 2. Broad agreement: comes from industry data where a majority of data submitters agree the substance is toxic to reproduction. Minority position: comes from industry data where a minority of data submitters indicate the substance is toxic to reproduction. More information about reproductive toxicity here.
Skin sensitising (Ss) – Recognised skin sensitiser: comes from a harmonised C&L classifying the substance as Skin Sens. 1, 1A, or 1B. Broad agreement: comes from industry data where a majority of data submitters agree the substance is a skin sensitiser. Minority position: comes from industry data where a minority of data submitters indicate the substance is skin sensitising. More information about skin sensitiser here.
Respiratory sensitising (Sr) – Recognised respiratory sensitiser: comes from a harmonised C&L classifying the substance as Resp. Sens. 1, 1A, or 1B and/or an entry in the Candidate list. Broad agreement: comes from industry data where a majority of data submitters agree the substance is a respiratory sensitiser. Minority position: comes from industry data where a minority of data submitters indicate the substance is a respiratory sensitiser. More information about respiratory sensitiser here.
PBT – Recognised Persistent, bioaccumulative and toxic (PBT) (or vPvB): comes from an entry in the Candidate list. Potential PBT: is shown for substances under assessment, and comes from an entry in the PBT assessment list. Broad agreement: comes from industry data where a majority of data submitters agree the substance is PBT. Minority position: comes from industry data where a minority of data submitters indicate the substance is PBT. More information about persistent, bioaccumulative and toxic substances here.
ED – Recognised Endocrine Disruptor (ED): comes from an entry in the Candidate list. Potential ED: is shown for substances under assessment, and comes from an entry in the ED assessment list, if the outcome is not final and "Not ED". At this time there is no industry submitted data taken into account to display an ED property of concern. More information about endocrine disrupting substances here.
The substance properties displayed in this section are derived from Harmonised classification and labelling (CLH) data, entries in the Candidate list of substances of very high concern for authorisation, the PBT assessment list, the ED assessment list, REACH registered dossier data and from notifications made under CLP. A prioritisation hierarchy means that data is taken from harmonised C&L data and regulatory lists first, then REACH registrations and finally from CLP notifications.
Impurities or additives: When a specific critical property is calculated from industry data and where the majority of data submitters have indicated that the property relates to cases containing impurities and/or additives, then the respective critical property icon is modified with an asterisk (*).
A majority of data submitters agree this substance is Skin sensitising
Details of dossier compliance checks and testing proposal evaluation.
PACT - Public activities Co-ordination Tool
Public (risk management) activities co-ordination table
Tool showing an overview of substances in various key regulatory processes that authorities are working on.
Testing proposals - previous consultation
Methylium, tris[4-(diethylamino)phenyl]-, acetate
2-methyl-1,3-dihydro-[1,2,4]triazolo[1,2-a]pyridazine-5,8-dione
Basic Violet 4
EC Inventory, C&L Inventory, Registration dossier, Pre-Registration process, Other