text:
package com.linecorp.armeria.server.thrift;

import static com.google.common.collect.ImmutableList.toImmutableList;
import static com.linecorp.armeria.common.util.Functions.voidFunction;
import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.fail;

import java.nio.ByteBuffer;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Stream;

import javax.annotation.Nonnull;

import org.apache.thrift.TApplicationException;
import org.apache.thrift.protocol.TProtocol;
import org.apache.thrift.transport.TMemoryBuffer;
import org.apache.thrift.transport.TMemoryInputTransport;
import org.apache.thrift.transport.TTransportException;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.extension.ExtensionContext;
import org.junit.jupiter.api.extension.RegisterExtension;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
import org.junit.jupiter.params.provider.ArgumentsProvider;
import org.junit.jupiter.params.provider.ArgumentsSource;

import com.linecorp.armeria.common.HttpData;
import com.linecorp.armeria.common.HttpMethod;
import com.linecorp.armeria.common.HttpRequest;
import com.linecorp.armeria.common.HttpRequestWriter;
import com.linecorp.armeria.common.HttpResponse;
import com.linecorp.armeria.common.SerializationFormat;
import com.linecorp.armeria.common.thrift.ThriftSerializationFormats;
import com.linecorp.armeria.common.thrift.text.ChildRpcDebugService;
import com.linecorp.armeria.common.thrift.text.Response;
import com.linecorp.armeria.common.util.CompletionActions;
import com.linecorp.armeria.common.util.Exceptions;
import com.linecorp.armeria.server.ServiceRequestContext;
import com.linecorp.armeria.service.test.thrift.main.BinaryService;
import com.linecorp.armeria.service.test.thrift.main.DevNullService;
import com.linecorp.armeria.service.test.thrift.main.FileService;
import com.linecorp.armeria.service.test.thrift.main.FileServiceException;
import com.linecorp.armeria.service.test.thrift.main.HelloService;
import com.linecorp.armeria.service.test.thrift.main.Name;
import com.linecorp.armeria.service.test.thrift.main.NameService;
import com.linecorp.armeria.service.test.thrift.main.NameSortService;
import com.linecorp.armeria.service.test.thrift.main.OnewayHelloService;
import com.linecorp.armeria.testing.junit5.common.EventLoopExtension;

import io.netty.util.concurrent.ImmediateEventExecutor;

/**
 * Tests {@link ThriftCallService} and {@link THttpService}.
 *

 * The test methods have the following naming convention:
 *
 *
TestType_ServiceName_MethodName[_AdditionalInfo]()

 *
 * .. where each field has the following meaning:
 *

 *
{@code TestType}
 *

 *
{@code Sync} tests an invocation of synchronous service. i.e. {@code *.Iface}

 *
{@code Async} tests an invocation of asynchronous service. i.e. {@code *.AsyncIface}

 *
{@code Identity} tests if the results of synchronous and asynchronous operations are identical
 *             at protocol level.

 *
{@code MultipleInheritance} tests the case where a service implementation implements
 *             multiple interfaces.

 *

 *
{@code ServiceName} - the class name of the service being tested

 *
{@code MethodName} - the name of the method in the service being tested

 *
(Optional) {@code AdditionalInfo} - specified when a service method has more than one test case

 *

 *

 */
class ThriftServiceTest {

    private static final Name NAME_A = new Name("a", "a", "a");
    private static final Name NAME_B = new Name("b", "b", "b");
    private static final Name NAME_C = new Name("c", "c", "c");

    private static final String FOO = "foo";
    private static final String BAR = "bar";
    private static final String BAZ = "baz";

    @RegisterExtension
    static final EventLoopExtension eventLoop = new EventLoopExtension();

    private TMemoryInputTransport in;
    private TMemoryBuffer out;

    private CompletableFuture<HttpData> promise;
    private CompletableFuture<HttpData> promise2;

    @BeforeEach
    void before() throws TTransportException {
        in = new TMemoryInputTransport();
        out = new TMemoryBuffer(128);

        promise = new CompletableFuture<>();
        promise2 = new CompletableFuture<>();
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_HelloService_hello(SerializationFormat defaultSerializationFormat) throws Exception {
        final HelloService.Client client = new HelloService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (HelloService.Iface) name -> "Hello, " + name + '!', defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_hello()).isEqualTo("Hello, foo!");
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_HelloService_hello(SerializationFormat defaultSerializationFormat) throws Exception {
        final HelloService.Client client = new HelloService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (HelloService.AsyncIface) (name, resultHandler) ->
                        resultHandler.onComplete("Hello, " + name + '!'), defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_hello()).isEqualTo("Hello, foo!");
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_HelloService_hello_with_null(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final HelloService.Client client = new HelloService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(null);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (HelloService.Iface) name -> String.valueOf(name != null), defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_hello()).isEqualTo("false");
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_HelloService_hello_with_null(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final HelloService.Client client = new HelloService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(null);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (HelloService.AsyncIface) (name, resultHandler) ->
                        resultHandler.onComplete(String.valueOf(name != null)), defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_hello()).isEqualTo("false");
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testIdentity_HelloService_hello(SerializationFormat defaultSerializationFormat) throws Exception {
        final HelloService.Client client = new HelloService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService syncService = THttpService.of(
                (HelloService.Iface) name -> "Hello, " + name + '!', defaultSerializationFormat);

        final THttpService asyncService = THttpService.of(
                (HelloService.AsyncIface) (name, resultHandler) ->
                        resultHandler.onComplete("Hello, " + name + '!'), defaultSerializationFormat);

        invokeTwice(syncService, asyncService);

        assertThat(promise.get()).isEqualTo(promise2.get());
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_OnewayHelloService_hello(SerializationFormat defaultSerializationFormat) throws Exception {
        final AtomicReference<String> actualName = new AtomicReference<>();

        final OnewayHelloService.Client client =
                new OnewayHelloService.Client.Factory().getClient(
                        inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (OnewayHelloService.Iface) actualName::set, defaultSerializationFormat);

        invoke(service);

        assertThat(promise.get().isEmpty()).isTrue();
        assertThat(actualName.get()).isEqualTo(FOO);
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_OnewayHelloService_hello(SerializationFormat defaultSerializationFormat) throws Exception {
        final AtomicReference<String> actualName = new AtomicReference<>();

        final OnewayHelloService.Client client =
                new OnewayHelloService.Client.Factory().getClient(
                        inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_hello(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of((OnewayHelloService.AsyncIface) (name, resultHandler) -> {
            actualName.set(name);
            resultHandler.onComplete(null);
        }, defaultSerializationFormat);

        invoke(service);

        assertThat(promise.get().isEmpty()).isTrue();
        assertThat(actualName.get()).isEqualTo(FOO);
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_DevNullService_consume(SerializationFormat defaultSerializationFormat) throws Exception {
        final AtomicReference<String> consumed = new AtomicReference<>();

        final DevNullService.Client client = new DevNullService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_consume(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (DevNullService.Iface) consumed::set, defaultSerializationFormat);

        invoke(service);

        assertThat(consumed.get()).isEqualTo(FOO);

        client.recv_consume();
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_DevNullService_consume(SerializationFormat defaultSerializationFormat) throws Exception {
        final AtomicReference<String> consumed = new AtomicReference<>();

        final DevNullService.Client client = new DevNullService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_consume("bar");
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of((DevNullService.AsyncIface) (value, resultHandler) -> {
            consumed.set(value);
            resultHandler.onComplete(null);
        }, defaultSerializationFormat);

        invoke(service);

        assertThat(consumed.get()).isEqualTo("bar");

        client.recv_consume();
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testIdentity_DevNullService_consume(SerializationFormat defaultSerializationFormat) throws Exception {
        final DevNullService.Client client = new DevNullService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_consume(FOO);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService syncService = THttpService.of((DevNullService.Iface) value -> {
            // NOOP
        }, defaultSerializationFormat);

        final THttpService asyncService = THttpService.of(
                (DevNullService.AsyncIface) (value, resultHandler) ->
                        resultHandler.onComplete(null), defaultSerializationFormat);

        invokeTwice(syncService, asyncService);

        assertThat(promise.get()).isEqualTo(promise2.get());
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_FileService_create_reply(SerializationFormat defaultSerializationFormat) throws Exception {
        final FileService.Client client = new FileService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_create(BAR);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of((FileService.Iface) path -> {
            throw newFileServiceException();
        }, defaultSerializationFormat);

        invoke(service);

        try {
            client.recv_create();
            fail(FileServiceException.class.getSimpleName() + " not raised.");
        } catch (FileServiceException ignored) {
            // Expected
        }
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_FileService_create_reply(SerializationFormat defaultSerializationFormat) throws Exception {
        final FileService.Client client = new FileService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_create(BAR);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (FileService.AsyncIface) (path, resultHandler) ->
                        resultHandler.onError(newFileServiceException()), defaultSerializationFormat);

        invoke(service);

        try {
            client.recv_create();
            fail(FileServiceException.class.getSimpleName() + " not raised.");
        } catch (FileServiceException ignored) {
            // Expected
        }
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testIdentity_FileService_create_reply(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final FileService.Client client = new FileService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_create(BAR);
        assertThat(out.length()).isGreaterThan(0);

        final THttpService syncService = THttpService.of((FileService.Iface) path -> {
            throw newFileServiceException();
        }, defaultSerializationFormat);

        final THttpService asyncService =
                THttpService.builder()
                            .addService(
                                    (FileService.AsyncIface) (path, resultHandler) ->
                                            resultHandler.onError(newFileServiceException()))
                            .defaultSerializationFormat(defaultSerializationFormat)
                            .build();

        invokeTwice(syncService, asyncService);

        assertThat(promise.get()).isEqualTo(promise2.get());
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_FileService_create_exception(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final FileService.Client client = new FileService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_create(BAZ);
        assertThat(out.length()).isGreaterThan(0);

        final RuntimeException exception = Exceptions.clearTrace(new RuntimeException());
        final THttpService service = THttpService.of((FileService.Iface) path -> {
            throw exception;
        }, defaultSerializationFormat);

        invoke(service);

        try {
            client.recv_create();
            fail(TApplicationException.class.getSimpleName() + " not raised.");
        } catch (TApplicationException e) {
            assertThat(e.getType()).isEqualTo(TApplicationException.INTERNAL_ERROR);
            assertThat(e.getMessage()).contains(exception.toString());
        }
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_FileService_create_exception(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final FileService.Client client = new FileService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_create(BAZ);
        assertThat(out.length()).isGreaterThan(0);

        final RuntimeException exception = Exceptions.clearTrace(new RuntimeException());
        final THttpService service = THttpService.of(
                (FileService.AsyncIface) (path, resultHandler) ->
                        resultHandler.onError(exception), defaultSerializationFormat);

        invoke(service);

        try {
            client.recv_create();
            fail(TApplicationException.class.getSimpleName() + " not raised.");
        } catch (TApplicationException e) {
            assertThat(e.getType()).isEqualTo(TApplicationException.INTERNAL_ERROR);
            assertThat(e.getMessage()).contains(exception.toString());
        }
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testIdentity_FileService_create_exception(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final FileService.Client client = new FileService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_create(BAZ);
        assertThat(out.length()).isGreaterThan(0);

        final RuntimeException exception = Exceptions.clearTrace(new RuntimeException());
        final THttpService syncService = THttpService.of((FileService.Iface) path -> {
            throw exception;
        }, defaultSerializationFormat);

        final THttpService asyncService = THttpService.of(
                (FileService.AsyncIface) (path, resultHandler) ->
                        resultHandler.onError(exception), defaultSerializationFormat);

        invokeTwice(syncService, asyncService);

        assertThat(promise.get()).isEqualTo(promise2.get());
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_NameService_removeMiddle(SerializationFormat defaultSerializationFormat) throws Exception {
        final NameService.Client client = new NameService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_removeMiddle(new Name(BAZ, BAR, FOO));
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (NameService.Iface) name -> new Name(name.first, null, name.last), defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_removeMiddle()).isEqualTo(new Name(BAZ, null, FOO));
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_NameService_removeMiddle(SerializationFormat defaultSerializationFormat) throws Exception {
        final NameService.Client client = new NameService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_removeMiddle(new Name(BAZ, BAR, FOO));
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of(
                (NameService.AsyncIface) (name, resultHandler) ->
                        resultHandler.onComplete(new Name(name.first, null, name.last)),
                defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_removeMiddle()).isEqualTo(new Name(BAZ, null, FOO));
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testIdentity_NameService_removeMiddle(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final NameService.Client client = new NameService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_removeMiddle(new Name(FOO, BAZ, BAR));
        assertThat(out.length()).isGreaterThan(0);

        final THttpService syncService = THttpService.of(
                (NameService.Iface) name -> new Name(name.first, null, name.last), defaultSerializationFormat);

        final THttpService asyncService = THttpService.of(
                (NameService.AsyncIface) (name, resultHandler) ->
                        resultHandler.onComplete(new Name(name.first, null, name.last)),
                defaultSerializationFormat);

        invokeTwice(syncService, asyncService);

        assertThat(promise.get()).isEqualTo(promise2.get());
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_NameSortService_sort(SerializationFormat defaultSerializationFormat) throws Exception {
        final NameSortService.Client client = new NameSortService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_sort(Arrays.asList(NAME_C, NAME_B, NAME_A));
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of((NameSortService.Iface) names -> {
            final ArrayList<Name> sorted = new ArrayList<>(names);
            Collections.sort(sorted);
            return sorted;
        }, defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_sort()).containsExactly(NAME_A, NAME_B, NAME_C);
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testAsync_NameSortService_sort(SerializationFormat defaultSerializationFormat) throws Exception {
        final NameSortService.Client client = new NameSortService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_sort(Arrays.asList(NAME_C, NAME_B, NAME_A));
        assertThat(out.length()).isGreaterThan(0);

        final THttpService service = THttpService.of((NameSortService.AsyncIface) (names, resultHandler) -> {
            final ArrayList<Name> sorted = new ArrayList<>(names);
            Collections.sort(sorted);
            resultHandler.onComplete(sorted);
        }, defaultSerializationFormat);

        invoke(service);

        assertThat(client.recv_sort()).containsExactly(NAME_A, NAME_B, NAME_C);
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testIdentity_NameSortService_sort(SerializationFormat defaultSerializationFormat) throws Exception {
        final NameSortService.Client client = new NameSortService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_sort(Arrays.asList(NAME_C, NAME_B, NAME_A));
        assertThat(out.length()).isGreaterThan(0);

        final THttpService syncService = THttpService.of((NameSortService.Iface) names -> {
            final ArrayList<Name> sorted = new ArrayList<>(names);
            Collections.sort(sorted);
            return sorted;
        }, defaultSerializationFormat);

        final THttpService asyncService =
                THttpService.of((NameSortService.AsyncIface) (names, resultHandler) -> {
                    final ArrayList<Name> sorted = new ArrayList<>(names);
                    Collections.sort(sorted);
                    resultHandler.onComplete(sorted);
                }, defaultSerializationFormat);

        invokeTwice(syncService, asyncService);

        assertThat(promise.get()).isEqualTo(promise2.get());
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testBinary(SerializationFormat defaultSerializationFormat) throws Exception {
        final BinaryService.Client client = new BinaryService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client.send_process(ByteBuffer.wrap(new byte[] { 1, 2 }));

        final THttpService service = THttpService.of((BinaryService.Iface) data -> {
            final ByteBuffer result = ByteBuffer.allocate(data.remaining());
            for (int i = data.position(), j = 0; i < data.limit(); i++, j++) {
                result.put(j, (byte) (data.get(i) + 1));
            }
            return result;
        }, defaultSerializationFormat);

        invoke(service);

        final ByteBuffer result = client.recv_process();

        // Convert the result into a Byte[] for more comprehensive comparison.
        final List<Byte> out = new ArrayList<>();
        for (int i = result.position(); i < result.limit(); i++) {
            out.add(result.get(i));
        }

        assertThat(out).contains((byte) 2, (byte) 3);
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testMultipleInheritance(SerializationFormat defaultSerializationFormat) throws Exception {
        final NameService.Client client1 = new NameService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client1.send_removeMiddle(new Name(BAZ, BAR, FOO));
        assertThat(out.length()).isGreaterThan(0);

        final HttpData req1 = HttpData.wrap(out.getArray(), 0, out.length());

        out = new TMemoryBuffer(128);
        final TProtocol outProto = outProto(defaultSerializationFormat);

        final NameSortService.Client client2 =
                new NameSortService.Client.Factory().getClient(
                        inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        client2.send_sort(Arrays.asList(NAME_C, NAME_B, NAME_A));
        assertThat(out.length()).isGreaterThan(0);

        final HttpData req2 = HttpData.wrap(out.getArray(), 0, out.length());

        final THttpService service = THttpService.of(
                (UberNameService) (names, callback) -> callback.onComplete(
                        names.stream().sorted().collect(toImmutableList())),
                defaultSerializationFormat);

        invoke0(service, req1, promise);
        invoke0(service, req2, promise2);

        final HttpData res1 = promise.get();
        final HttpData res2 = promise2.get();

        in.reset(res1.array());
        assertThat(client1.recv_removeMiddle()).isEqualTo(new Name(BAZ, null, FOO));

        in.reset(res2.array());
        assertThat(client2.recv_sort()).containsExactly(NAME_A, NAME_B, NAME_C);
    }

    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testServiceInheritance(SerializationFormat defaultSerializationFormat) {
        // This should not throw an exception
        THttpService.of((ChildRpcDebugService.Iface)(a1, a2, details) -> new Response("asdf"));
    }

    /**
     * This tests when multiple thrift service implementations, with same service method are added under
     * default("") service name then the thrift service that is added first should handle the request.
     * And also makes sure that adding a duplicate service does not break other services.
     */
    @ParameterizedTest
    @ArgumentsSource(SerializationFormatProvider.class)
    void testSync_HelloService_hello_WhenSameMethodMultipleTimes(SerializationFormat defaultSerializationFormat)
            throws Exception {
        final HelloService.Client helloClient = new HelloService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        helloClient.send_hello(FOO);
        assertThat(out.length()).isGreaterThan(0);
        final HttpData req1 = HttpData.wrap(out.getArray(), 0, out.length());

        out = new TMemoryBuffer(128);
        final NameService.Client nameClient = new NameService.Client.Factory().getClient(
                inProto(defaultSerializationFormat), outProto(defaultSerializationFormat));
        nameClient.send_removeMiddle(new Name(BAZ, BAR, FOO));
        assertThat(out.length()).isGreaterThan(0);
        final HttpData req2 = HttpData.wrap(out.getArray(), 0, out.length());

        final THttpService service =
                THttpService.builder()
                            .addService((HelloService.Iface) name -> "Hello, " + name + '!')
                            // Duplicate service with same method name
                            .addService((HelloService.Iface) name -> "Hello (again) " + name + '!')
                            .addService((NameService.Iface) name -> new Name(name.first, null, name.last))
                            .defaultSerializationFormat(defaultSerializationFormat)
                            .build();

        invoke0(service, req1, promise);
        invoke0(service, req2, promise2);

        final HttpData res1 = promise.get();
        final HttpData res2 = promise2.get();

        in.reset(res1.array());
        assertThat(helloClient.recv_hello()).isEqualTo("Hello, foo!");

        in.reset(res2.array());
        assertThat(nameClient.recv_removeMiddle()).isEqualTo(new Name(BAZ, null, FOO));
    }

    // NB: By making this interface functional, we can use lambda expression to implement
    //     NameSortService.AsyncIface.sort(). By using lambda expression, we can omit the parameter type
    //     declarations (i.e. no AsyncMethodCallback<List<Name>>). By omitting the parameter type declaration,
    //     We can compile it with both Thrift 0.9 (which uses raw type for AsyncMethodCallback) and
    //     0.10 (which uses a concrete type parameter for AsyncMethodCallback.)
    @FunctionalInterface
    private interface UberNameService extends NameService.Iface, NameSortService.AsyncIface {
        @Override
        default Name removeMiddle(Name name) {
            return new Name(name.first, null, name.last);
        }
    }

    private TProtocol inProto(SerializationFormat defaultSerializationFormat) {
        return ThriftSerializationFormats.protocolFactory(defaultSerializationFormat).getProtocol(in);
    }

    private TProtocol outProto(SerializationFormat defaultSerializationFormat) {
        return ThriftSerializationFormats.protocolFactory(defaultSerializationFormat).getProtocol(out);
    }

    private static class SerializationFormatProvider implements ArgumentsProvider {
        @Override
        public Stream<? extends Arguments> provideArguments(ExtensionContext context) throws Exception {
            return ThriftSerializationFormats.values().stream().map(Arguments::of);
        }
    }

    private void invoke(THttpService service) throws Exception {
        invoke0(service, HttpData.wrap(out.getArray(), 0, out.length()), promise);

        final HttpData res = promise.get();
        in.reset(res.array());
    }

    private void invokeTwice(THttpService service1, THttpService service2) throws Exception {
        final HttpData content = HttpData.wrap(out.getArray(), 0, out.length());
        invoke0(service1, content, promise);
        invoke0(service2, content, promise2);
    }

    private static void invoke0(THttpService service, HttpData content,
                                CompletableFuture<HttpData> promise) throws Exception {
        final HttpRequestWriter req = HttpRequest.streaming(HttpMethod.POST, "/");
        req.write(content);
        req.close();

        final ServiceRequestContext ctx =
                ServiceRequestContext.builder(req)
                                     .eventLoop(eventLoop.get())
                                     .serverConfigurator(builder -> {
                                         builder.blockingTaskExecutor(ImmediateEventExecutor.INSTANCE, false);
                                         builder.verboseResponses(true);
                                     })
                                     .build();

        final HttpResponse res = service.serve(ctx, req);
        res.aggregate().handle(voidFunction((aReq, cause) -> {
            if (cause == null) {
                if (aReq.status().code() == 200) {
                    promise.complete(aReq.content());
                } else {
                    promise.completeExceptionally(
                            new AssertionError(aReq.status() + ", " + aReq.contentUtf8()));
                }
            } else {
                promise.completeExceptionally(cause);
            }
        })).exceptionally(CompletionActions::log);
    }

    @Nonnull
    private static FileServiceException newFileServiceException() {
        // Remove the stack trace so we do not pollute the build log.
        return Exceptions.clearTrace(new FileServiceException());
    }
}


text:
layout: page-fullwidth-empty
title: "Janko Cizel -- Research"
header:
     image_fullwidth: "grey_banner_large.jpg"
     caption: This is a caption for the header image with link
     caption_url: https://unsplash.com/
title: "Research"
subheadline: "Portfolio"
teaser: ""
permalink: "/research/"
---

This is my project 1

I am testing whether this works. If it does, great! If not, I'll figure
	out sth else.This paper studies the information content of bank accounting
	fundamental data in the prediction of bank distress using an international
	sample of banks from 15 Western European countries and the U.S. during the
	financial crisis of 2007-12.  The analysis of our bank distress models
	reveals a substantial cross-country variation in the ability of accounting
	fundamentals to discriminate between distressed and non-distressed banks
	within countries. We show that the association between accounting
	fundamentals and bank distress is attenuated in jurisdictions with
	relatively lax bank disclosure laws and their implementation. Accounting
	ratios, whose information value is the most sensitive to the quality of
	regulatory disclosure include regulatory capital ratios, loan loss
	provisions, and unreserved loan losses. The evidence in this paper supports
	the oft-voiced concern that excessive flexibility in financial reporting
	undermines the ability of accounting signals to accurately capture the
	underlying financial health of banks. Obliqueness of the distressed s
	accounting signals makes such information less useful for investors and
	regulators, and thus has negative regulatory implications.

<a class="button tiny radius" href="test"

This is my project 1

I am testing whether this works. If it does, great! If not, I'll figure
	out sth else.This paper studies the information content of bank accounting
	fundamental data in the prediction of bank distress using an international
	sample of banks from 15 Western European countries and the U.S. during the
	financial crisis of 2007-12.  The analysis of our bank distress models
	reveals a substantial cross-country variation in the ability of accounting
	fundamentals to discriminate between distressed and non-distressed banks
	within countries. We show that the association between accounting
	fundamentals and bank distress is attenuated in jurisdictions with
	relatively lax bank disclosure laws and their implementation. Accounting
	ratios, whose information value is the most sensitive to the quality of
	regulatory disclosure include regulatory capital ratios, loan loss
	provisions, and unreserved loan losses. The evidence in this paper supports
	the oft-voiced concern that excessive flexibility in financial reporting
	undermines the ability of accounting signals to accurately capture the
	underlying financial health of banks. Obliqueness of the distressed s
	accounting signals makes such information less useful for investors and
	regulators, and thus has negative regulatory implications.

<a class="button tiny radius" href="test"

This is my project 1

I am testing whether this works. If it does, great! If not, I'll figure
	out sth else.This paper studies the information content of bank accounting
	fundamental data in the prediction of bank distress using an international
	sample of banks from 15 Western European countries and the U.S. during the
	financial crisis of 2007-12.  The analysis of our bank distress models
	reveals a substantial cross-country variation in the ability of accounting
	fundamentals to discriminate between distressed and non-distressed banks
	within countries. We show that the association between accounting
	fundamentals and bank distress is attenuated in jurisdictions with
	relatively lax bank disclosure laws and their implementation. Accounting
	ratios, whose information value is the most sensitive to the quality of
	regulatory disclosure include regulatory capital ratios, loan loss
	provisions, and unreserved loan losses. The evidence in this paper supports
	the oft-voiced concern that excessive flexibility in financial reporting
	undermines the ability of accounting signals to accurately capture the
	underlying financial health of banks. Obliqueness of the distressed s
	accounting signals makes such information less useful for investors and
	regulators, and thus has negative regulatory implications.

<a class="button tiny radius" href=""

redpajama_set_name: RedPajamaGithub


text:
myexperiment-java-client
========================


text:
The [Tideways XHProf Extension](https://github.com/tideways/php-xhprof-extension) is a well-maintained fork of the XHProf code and works with either the [XHProf](https://www.drupal.org/project/xhprof) or [Tideways](https://www.drupal.org/project/tideways) Drupal modules to profile page views. Results can be viewed with these modules or the native XHProf UI.

Note that the Tideways project [recently split](https://tideways.com/profiler/blog/releasing-new-tideways-xhprof-extension) into a proprietary extension and a stand-alone fork of XHProf. DrupalVM (and this document) specifically uses the latter (the "Tideways XHProf Extension").

To use Tideways XHProf make sure `tideways` is in the list of `installed_extras`.

**Note**: You should only enable one code profiler at a time—e.g. when using [Blackfire](blackfire.md), disable [XHProf](xhprof.md), [Tideways](tideways.md) and [XDebug](xdebug.md).

### Profiling with the XHProf Drupal Module

To enable profiling of Drupal pages using the Tideways PHP Extension, follow the directions for [configuring the XHProf and the XHProf module](xhprof.md#xhprof-module), but choose the _Tideways_ extension under the _Profiling settings_ section.

### Profiling with the Tideways module

Instructions for profiling with the Tideways module through the [tideways.io](https://tideways.io) service will be added after the following issue is resolved: [Can't install latest dev release on 8.x](https://www.drupal.org/node/2843481).

For a list of available role variables, see the [`geerlingguy.php-tideways` Ansible role's README](https://github.com/geerlingguy/ansible-role-php-tideways#readme).

### Viewing results

As with the XHProf extension, you can view callgraphs (and a listing of all stored runs) using Drupal VM's own XHProf UI installation by visiting [http://xhprof.drupalvm.test/](http://xhprof.drupalvm.test) and clicking on the relevant run, then clicking the _[View Full Callgraph]_ link.

Note that if you have overridden DrupalVM's [default set of Apache vHosts](https://github.com/geerlingguy/drupal-vm/blob/master/default.config.yml), you will need to specifically add a vHost for the XHProf dashboard.


text:
package controllers

import (
	"github.com/lyhopq/firefly/app/models"
	"github.com/lyhopq/firefly/app/routes"

	"github.com/revel/revel"
)

type Book struct {
	Application
}

func (c *Book) Index(page int) revel.Result {
	title := "最近上架"
	subActive := "latestShelf"

	books, rows := models.GetBooks(c.q, page, "", "", "id")
	pagination := models.GetPagination(page, rows, routes.Book.Index(page))

	return c.Render(title, subActive, books, pagination)
}

func (c *Book) MostRead(page int) revel.Result {
	title := "最多借阅"
	subActive := "mostRead"

	books, rows := models.GetBooks(c.q, page, "", "", "readed")
	pagination := models.GetPagination(page, rows, routes.Book.Index(page))

	c.Render(title, subActive, books, pagination)
	return c.RenderTemplate("book/Index.html")
}

func (c *Book) MostComment(page int) revel.Result {
	title := "最多评论"
	subActive := "mostComment"

	books, rows := models.GetBooks(c.q, page, "", "", "Commented")
	pagination := models.GetPagination(page, rows, routes.Book.Index(page))

	c.Render(title, subActive, books, pagination)

	return c.RenderTemplate("book/Index.html")
}

func (c *Book) Show(id int64) revel.Result {
	book := models.FindBookById(c.q, id)

	c.updateBookInfo(book)

	subActive := "intro"
	return c.Render(book, subActive)
}

func (c *Book) Borrow(id int64) revel.Result {
	book := models.FindBookById(c.q, id)

	c.updateBookInfo(book)

	borrows := models.FindBorrowsByBookId(c.q, id)

	subActive := "borrow"
	c.Render(book, subActive, borrows)
	return c.RenderTemplate("book/Show.html")
}

func (c *Book) updateBookInfo(book *models.Book) revel.Result {
	if book.Id == 0 {
		return c.NotFound("书籍不存在")
	}

	book.AddHited(c.q)
	user := c.connected()
	if user != nil {
		collect := models.FindCollect(c.q, user.Id, book.Id)
		if collect != nil {
			book.SetCollected(true)
		} else {
			book.SetCollected(false)
		}

		status := models.BorrowStatus(c.q, user.Id, book.Id)
		book.SetBorrow(status)
	}

	return nil
}

func (c *Book) Collect(id int64) revel.Result {
	user := c.connected()
	var signin bool
	if user != nil {
		signin = true
		models.AddCollect(c.q, user.Id, id)

		book := models.FindBookById(c.q, id)
		book.AddCollect(c.q)
	}

	return c.RenderJSON(signin)
}

func (c *Book) UnCollect(id int64) revel.Result {
	user := c.connected()
	var ok bool
	if user != nil {
		ok = true
		models.RemoveCollect(c.q, user.Id, id)

		book := models.FindBookById(c.q, id)
		book.SubCollect(c.q)
	}

	return c.RenderJSON(ok)
}

type Ret struct {
	Ok    bool
	Count int
}

func (c *Book) Booking(id int64) revel.Result {
	user := c.connected()
	var ret Ret
	if user != nil {
		status := models.BorrowStatus(c.q, user.Id, id)
		if status != models.BOOK && status != models.OWN {
			ret.Ok, ret.Count = models.AddBooking(c.q, user.Id, id)
		}

	}

	return c.RenderJSON(ret)
}

func (c *Book) UnBooking(id int64) revel.Result {
	user := c.connected()
	var ret Ret
	if user != nil {
		ret.Ok, ret.Count = models.RemoveBooking(c.q, user.Id, id)
	}

	return c.RenderJSON(ret)
}

func (c *Book) Intro(id int64) revel.Result {
	book := models.FindBookById(c.q, id)
	return c.RenderJSON(book.Introduction)
}


text:
ACCEPTED

#### According to
The Catalogue of Life, 3rd January 2011

#### Published in
Taxon 33:442. 1984

#### Original name
Erianthus ecklonii Nees

### Remarks
null


text:
/**
 * Handle messaging between pasteboard and the document via jschannel
 * @param {Function} handler
 * @constructor
 */
function Communications (handler) {
  var $this = this;

  /**
   * Initializer
   */
  function init () {
    $this.handler = handler;
    $this.channel = Communications.createChannel();
    $this.channel.bind('addClip', handler);
  }

  init()

}

/**
 * Creates a jschannel with parent frame
 * @return {Object}
 */
Communications.createChannel = function () {
  return Channel.build({
      window : window.parent
    , origin : '*'
    , scope : 'pasteboard'
  });
};


text:
DROP DATABASE IF EXISTS DISTRIBUTOR;
CREATE DATABASE DISTRIBUTOR;
USE DISTRIBUTOR;

/*Why ON DELETE CASCADE? 'cause if you want to change something, you use UPDATE, not delete and re-insert*/

/*TABLES*/

CREATE TABLE locations
(
	idLocation INTEGER NOT NULL AUTO_INCREMENT,
	country VARCHAR(50) NOT NULL,
	region VARCHAR(50) NOT NULL,
	province VARCHAR(50) NOT NULL,
	PRIMARY KEY(idLocation),
	UNIQUE(province)
);

CREATE TABLE workers
(
	idWorker INTEGER NOT NULL AUTO_INCREMENT,
	lastname VARCHAR(50) NOT NULL,
	name VARCHAR(50) NOT NULL,
	email VARCHAR(50),
	dateOfBirth DATE NOT NULL,
	city VARCHAR(50) NOT NULL,
	zipCode VARCHAR(50) NOT NULL,
	address VARCHAR(50) NOT NULL,
	idLocation INTEGER NOT NULL,
	PRIMARY KEY(idWorker),
	FOREIGN KEY(idLocation) REFERENCES locations(idLocation) ON DELETE CASCADE,
	UNIQUE(lastname,name)
);

CREATE TABLE phoneNumbers
(
	idPhone INTEGER NOT NULL AUTO_INCREMENT,
	phone VARCHAR(50) NOT NULL,
	idWorker INTEGER NOT NULL,
	PRIMARY KEY(idPhone),
	FOREIGN KEY(idWorker) REFERENCES workers(idWorker) ON DELETE CASCADE,
	UNIQUE(phone)
);

CREATE TABLE periodicities
(
	idPeriodicity INTEGER NOT NULL AUTO_INCREMENT,
	periodicity VARCHAR(50) NOT NULL,
	PRIMARY KEY(idPeriodicity),
	UNIQUE(periodicity)
);

CREATE TABLE magazines
(
	idMag INTEGER NOT NULL AUTO_INCREMENT,
	title VARCHAR(50) NOT NULL,
	idPeriodicity INTEGER NOT NULL,
	idOwner INTEGER NOT NULL,
	PRIMARY KEY(idMag),
	FOREIGN KEY(idOwner) REFERENCES workers(idWorker) ON DELETE CASCADE,
	FOREIGN KEY(idPeriodicity) REFERENCES periodicities(idPeriodicity) ON DELETE CASCADE,
	UNIQUE(title)
);

CREATE TABLE magRelases
(
	idMagRelase INTEGER NOT NULL AUTO_INCREMENT,
	idMagazine INTEGER NOT NULL,
	magNumber INTEGER NOT NULL,
	dateRelase DATE NOT NULL,
	nameRelase VARCHAR(50),
	priceToPublic NUMERIC(5,2) NOT NULL,
	percentToNS INTEGER NOT NULL,
	PRIMARY KEY(idMagRelase),
	FOREIGN KEY(idMagazine) REFERENCES magazines(idMag) ON DELETE CASCADE,
	UNIQUE(idMagazine,magNumber)
);

CREATE TABLE newsStands
(
	idNewsStand INTEGER NOT NULL AUTO_INCREMENT,
	businessName VARCHAR(50) NOT NULL,
	piva VARCHAR(50) NOT NULL,
	city VARCHAR(50) NOT NULL,
	zipCode VARCHAR(50) NOT NULL,
	address VARCHAR(50) NOT NULL,
	idLocation INTEGER NOT NULL,
	NewsStandPhoneN VARCHAR(50) NOT NULL,
	idOwner INTEGER NOT NULL,
	PRIMARY KEY(idNewsStand,piva),
	FOREIGN KEY(idLocation) REFERENCES locations(idLocation) ON DELETE CASCADE,
	FOREIGN KEY(idOwner) REFERENCES workers(idWorker) ON DELETE CASCADE,
	UNIQUE(businessName)
);

CREATE TABLE soldCopies
(
	idSoldCopies INTEGER NOT NULL AUTO_INCREMENT,
	nCopiesDelivered INTEGER,
	nCopiesReturned INTEGER,
	areInvoiced BOOLEAN,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	_dateInvoice DATE,
	PRIMARY KEY(idSoldCopies),
	FOREIGN KEY(idMagRelase) REFERENCES magRelases(idMagRelase) ON DELETE CASCADE,
	FOREIGN KEY(idNewsStand) REFERENCES newsStands(idNewsStand) ON DELETE CASCADE
);

CREATE TABLE jobs
(
	idJob INTEGER NOT NULL AUTO_INCREMENT,
	jobName VARCHAR(50),
	_date DATE NOT NULL,
	PRIMARY KEY(idJob),
	UNIQUE(jobName,_date)
);

CREATE TABLE tasks
(
	idTask INTEGER NOT NULL AUTO_INCREMENT,
	taskName VARCHAR(50),
	nCopies INTEGER NOT NULL,
	typeTask ENUM ("deliver","returner") NOT NULL,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	idWorker INTEGER NOT NULL,
	idJob INTEGER NOT NULL,
	PRIMARY KEY(idTask),
	FOREIGN KEY(idNewsStand) REFERENCES newsStands(idNewsStand) ON DELETE CASCADE,
	FOREIGN KEY(idWorker) REFERENCES workers(idWorker) ON DELETE CASCADE,
	FOREIGN KEY(idJob) REFERENCES jobs(idJob) ON DELETE CASCADE,
	FOREIGN KEY(idMagRelase) REFERENCES magRelases(idMagRelase) ON DELETE CASCADE,
	UNIQUE(taskName,idJob)
);

/*END TABLES*/

/*TRIGGER*/
DELIMITER $$

/*
idTask INTEGER NOT NULL AUTO_INCREMENT,
	taskName VARCHAR(50),
	nCopies INTEGER NOT NULL,
	typeTask ENUM ("deliver","returner") NOT NULL,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	idWorker INTEGER NOT NULL,
	idJob INTEGER NOT NULL,
*/

/*
idSoldCopies INTEGER NOT NULL AUTO_INCREMENT,
	nCopiesDelivered INTEGER,
	nCopiesReturned INTEGER,
	areInvoiced BOOLEAN,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	_dateInvoice DATE,
*/

CREATE FUNCTION soldCopyExist
(
	_idMagRelase INTEGER,
	_idNewsStand INTEGER
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT soldCopies.idSoldCopies FROM soldCopies WHERE UPPER(soldCopies.idMagRelase) = UPPER(_idMagRelase) AND UPPER(soldCopies.idNewsStand)=UPPER(_idNewsStand) INTO _id;
	RETURN _id;
END $$

CREATE TRIGGER TRG_UPDATE_SOLDCOPIES_ON_INSERT BEFORE INSERT ON tasks FOR EACH ROW
BEGIN

END $$

CREATE TRIGGER TRG_UPDATE_SOLDCOPIES_ON_UPDATE BEFORE UPDATE ON tasks FOR EACH ROW /*This is different from the after insert above: in this we need to set null the value in the old row if the task update its newsstand*/
BEGIN

END $$

DELIMITER ;
/*END TRIGGER*/

/*INSERT*/

INSERT INTO locations VALUES (1,"Italy","Trentino","Trento"),(2,"Germany","Geneva","Geneva");

INSERT INTO workers VALUES (1,"Sparrow","jack","sparrowj@domain.com","1980-02-01","Vernier","1214","Chemin De-Sales 3",2),(2,"Doe","John","doej@domain.com","1979-03-10","Arco","38062","Via Castello 1",1),(3,"White","Walter Hartwell","whitewh@domain.com","1979-10-11","Arco","38062","via chiesa 2",1),(4,"House","Gregory","houseg@domain.com","1962-11-16","Arco","38062","Via Segantini 1",1),(5,"Goodman","Saul","saulg@domain.com","1992-10-05","Arco","38062","via italia5",1);

INSERT INTO phoneNumbers VALUES (1,"0464-510001",3),(2,"0464-510002",2),(3,"004122-123652",1),(4,"0464-511919",4),(7,"0464-510005",5);

INSERT INTO periodicities VALUES (1,"daily"),(2,"monthly");

INSERT INTO magazines VALUES (1,"La Busa",2,4),(2,"rivista2",2,1);

INSERT INTO magRelases VALUES (1,1,53,"2016-04-01","April number",2,50),(2,1,54,"2016-05-01","May number",2,50),(3,2,1,"2016-01-01","Jan number",2,50),(4,2,2,"2016-02-01","Feb number",2,50);

INSERT INTO newsStands VALUES (1,"tabacchino arco","piva000001","Arco","38062","Via Mantova 1",1,"0464-510003",2),(2,"news stand genevas","piva000002","Geneva","38062","Chemin De-Sales 3",2,"0464-510004",3);

INSERT INTO jobs VALUES (1,"Consegna numero aprile","2016-04-02"),(2,"Consegna numero maggio","2016-05-01"),(3,"june job","2016-06-01");

INSERT INTO tasks VALUES (1,"deliver may copies",35,"deliver",2,1,5,2),(2,"deliver may copies again",10,"deliver",2,2,5,2),(3,"deliver april copies",10,"deliver",1,1,5,1),(4,"get april copies back",8,"returner",1,1,5,2),(5,"get may copies back",5,"returner",2,2,5,3);

/*END INSERT*/
/*
UPDATE soldCopies SET soldCopies.areInvoiced=1, soldCopies._dateInvoice=CURDATE() WHERE soldCopies.idMagRelase=2 AND soldCopies.idNewsStand=2;
*/
/*PROCEDURES*/
DELIMITER $$

CREATE PROCEDURE showtaskByType(_taskType VARCHAR(50))
BEGIN
	SELECT workers.lastname, workers.name, newsStands.businessName,newsStands.city, newsStands.address, tasks.nCopies, tasks.typeTask, tasks.taskName, jobs.idJob, jobs.jobName, magRelases.magNumber AS mag_number FROM tasks JOIN workers ON tasks.idWorker=workers.idWorker JOIN newsStands ON
tasks.idNewsStand=newsStands.idNewsStand JOIN locations ON locations.idLocation=newsStands.idLocation JOIN jobs ON jobs.idJob=tasks.idJob JOIN magRelases ON magRelases.idMagRelase=tasks.idMagRelase where tasks.typeTask=_taskType;

END $$

/*
taskName VARCHAR(50),
	nCopies INTEGER NOT NULL,
	typeTask ENUM ("deliver","returner") NOT NULL,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	idWorker INTEGER NOT NULL,
	idJob INTEGER NOT NULL,
*/

CREATE PROCEDURE showAllTasks()
BEGIN
	SELECT tasks.taskName, tasks.nCopies, tasks.typeTask, magRelases.magNumber AS mag_number,magazines.title, newsStands.businessName,newsStands.city, newsStands.address AS newsStandAddress, workers.lastname, workers.name, jobs.jobName, jobs._date AS jobDate
	FROM tasks
	JOIN workers ON tasks.idWorker=workers.idWorker
	JOIN newsStands ON tasks.idNewsStand=newsStands.idNewsStand
	JOIN locations ON locations.idLocation=newsStands.idLocation
	JOIN jobs ON jobs.idJob=tasks.idJob
	JOIN magRelases ON magRelases.idMagRelase=tasks.idMagRelase
	JOIN magazines ON magRelases.idMagazine=magazines.idMag
	ORDER BY(tasks.idTask);

END $$

CREATE PROCEDURE showSoldCopies()
BEGIN
	CALL populateSoldCopies();

	SELECT soldCopies.nCopiesDelivered,soldCopies.nCopiesReturned, IF(soldCopies.areInvoiced=1,"true","false") AS areInvoiced,
	magRelases.magNumber,magRelases.nameRelase,newsStands.businessName,newsStands.address,newsStands.city,workers.lastname,workers.name,
	soldCopies._dateInvoice AS invoiceDate
	FROM soldCopies
	JOIN newsStands ON soldCopies.idNewsStand=newsStands.idNewsStand
	JOIN workers ON workers.idWorker=newsStands.idOwner
	JOIN magRelases ON soldCopies.idMagRelase=magRelases.idMagRelase
	ORDER BY(soldCopies.idSoldCopies);
END $$

CREATE PROCEDURE showSoldCopiesInvoiced(_invoiced INTEGER) /*1 to show invoiced copies, 0 to show not invoiced copies*/
BEGIN

	CALL populateSoldCopies();

	SELECT soldCopies.nCopiesDelivered,soldCopies.nCopiesReturned, IF(soldCopies.areInvoiced=1,"true","false") AS areInvoiced,
	magRelases.magNumber,magRelases.nameRelase,newsStands.businessName,newsStands.address,newsStands.city,workers.lastname,workers.name,
	soldCopies._dateInvoice AS invoiceDate
	FROM soldCopies
	JOIN newsStands ON soldCopies.idNewsStand=newsStands.idNewsStand
	JOIN workers ON workers.idWorker=newsStands.idOwner
	JOIN magRelases ON soldCopies.idMagRelase=magRelases.idMagRelase
	 WHERE soldCopies.areInvoiced=_invoiced
	 ORDER BY(soldCopies.idSoldCopies);
END $$

/*
idTask INTEGER NOT NULL AUTO_INCREMENT,
	taskName VARCHAR(50),
	nCopies INTEGER NOT NULL,
	typeTask ENUM ("deliver","returner") NOT NULL,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	idWorker INTEGER NOT NULL,
	idJob INTEGER NOT NULL,
*/
/*
idSoldCopies INTEGER NOT NULL AUTO_INCREMENT,
	nCopiesDelivered INTEGER,
	nCopiesReturned INTEGER,
	areInvoiced BOOLEAN,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	_dateInvoice DATE,
*/

CREATE PROCEDURE populateSoldCopies()
BEGIN
	DECLARE n INT DEFAULT 0;
	DECLARE i INT DEFAULT 0;
	DECLARE _typeTask VARCHAR(20);
	DECLARE _idSold INTEGER;
	DECLARE _nCopies INTEGER;
	DECLARE _idMagRelase INTEGER;
	DECLARE _idNewsStand INTEGER;

	DELETE FROM soldCopies;

	SELECT COUNT(*) FROM tasks INTO n;
	SET i=1;
	WHILE i<=n DO

		SELECT tasks.typeTask,tasks.nCopies,tasks.idMagRelase,tasks.idNewsStand FROM tasks WHERE tasks.idTask=i INTO _typeTask,_nCopies,_idMagRelase,_idNewsStand;

		SELECT soldCopyExist(_idMagRelase,_idNewsStand) INTO _idSold;

		IF (_idSold > 0)THEN /*soldCopies already exists*/
			IF (_typeTask="returner") THEN
				UPDATE soldCopies SET soldCopies.nCopiesReturned=(SELECT SUM(_nCopies+soldCopies.nCopiesReturned)) WHERE soldCopies.idSoldCopies=_idSold; /*set nCopiesReturned and nSoldCopies, summing the new value and the old value*/
			END IF;

			IF (_typeTask="deliver") THEN
				UPDATE soldCopies SET soldCopies.nCopiesDelivered=(SELECT SUM(_nCopies+soldCopies.nCopiesDelivered)) WHERE soldCopies.idSoldCopies=_idSold; /*set nCopiesReturned and nSoldCopies, summing the new value and the old value*/
			END IF;
		END IF;

		IF NULLIF(_idSold, '') IS NULL THEN

			IF (_typeTask="returner") THEN  /*sold copies not exists yet, I'll create it:*/
				INSERT INTO soldCopies VALUES (NULL,0,_nCopies,FALSE,_idMagRelase,_idNewsStand, NULL);
			END IF;
			IF (_typeTask="deliver") THEN
				INSERT INTO soldCopies VALUES (NULL,_nCopies,0,FALSE,_idMagRelase,_idNewsStand, NULL);
			END IF;
		END IF;

		SET i = i + 1;
	END WHILE;
END $$

CREATE PROCEDURE allProvince()
BEGIN
	SELECT DISTINCT locations.province FROM locations ORDER BY(locations.province);
END $$

CREATE PROCEDURE workerPhoneNumbers()
BEGIN
	SELECT workers.lastname,workers.name, phoneNumbers.phone FROM workers JOIN phoneNumbers ON workers.idWorker=phoneNumbers.idWorker;
END $$

CREATE PROCEDURE allOwners()
BEGIN
	SELECT DISTINCT workers.lastname, workers.name FROM workers ORDER BY(workers.lastname);
END $$

CREATE PROCEDURE allWorkers()
BEGIN
	SELECT DISTINCT workers.lastname, workers.name,workers.dateOfBirth,workers.city,workers.zipCode,workers.address,locations.province FROM workers JOIN locations ON workers.idLocation=locations.idLocation ORDER BY(workers.lastname);
END $$

CREATE PROCEDURE test()
BEGIN
DECLARE _x INTEGER;
SELECT locations.idLocation FROM locations WHERE idLocation=1 into _x;
	IF(_x = NULL) THEN SELECT "hello"; END IF;

END $$

CREATE PROCEDURE workersPhoneNumber()
BEGIN
	SELECT  phoneNumbers.phone, workers.lastname, workers.name FROM workers JOIN phoneNumbers ON workers.idWorker=phoneNumbers.idWorker ORDER BY(workers.lastname);
END $$

CREATE PROCEDURE allMagazines()
BEGIN
	SELECT magazines.title, periodicities.periodicity, workers.lastname AS lastnameOwner,workers.name AS nameOwner FROM magazines JOIN periodicities ON magazines.idPeriodicity=periodicities.idPeriodicity JOIN workers ON magazines.idOwner=workers.idWorker;
END $$

CREATE PROCEDURE allPeriods()
BEGIN
	SELECT periodicities.periodicity FROM periodicities;
END $$

CREATE PROCEDURE allMagazinesName()
BEGIN
	SELECT magazines.title FROM magazines;
END $$

CREATE PROCEDURE allMagRelases()
BEGIN
	SELECT magazines.title,magRelases.magNumber,magRelases.dateRelase,magRelases.nameRelase,magRelases.priceToPublic, magRelases.percentToNS FROM magRelases JOIN magazines ON magRelases.idMagazine=magazines.idMag;
END $$

CREATE PROCEDURE allLocations()
BEGIN
	SELECT locations.country,locations.region,locations.province FROM locations;
END $$

CREATE PROCEDURE allJobs()
BEGIN
	SELECT jobs.jobName,jobs._date AS JobDate FROM jobs;
END $$

CREATE PROCEDURE allNewsstands()
BEGIN
	/*SELECT newsStands.businessName,newsStands.piva,newsStands.zipCode,newsStands.address,newsStands.NewsStandPhoneN,locations.country,locations.region,locations.province,newsStands.city,workers.lastname,workers.name
	FROM newsStands JOIN locations ON newsStands.idLocation=locations.idLocation JOIN workers ON newsStands.idOwner=workers.idWorker;*/
	SELECT * from newsStands;
END $$

/*
CREATE FUNCTION jobExist
(
	_jobName VARCHAR(50),
	_jobDate VARCHAR(10)
)
*/

CREATE PROCEDURE tasksByJob(
	_jobName VARCHAR(50),
	_jobDate VARCHAR(10)
)
BEGIN
	DECLARE _idJob INTEGER;

	SELECT jobExist(_jobName,_jobDate) INTO _idJob;
	SELECT IFNULL(_idJob, -1) INTO _idJob; /*if job does not exists, return -1*/

	SELECT tasks.taskName,tasks.nCopies, tasks.typeTask,tasks.taskName, magRelases.magNumber,magazines.title,newsStands.businessName,newsStands.address,workers.lastname,workers.name,jobs.jobName,jobs._date
	FROM tasks
	JOIN magRelases ON tasks.idMagRelase=magRelases.idMagRelase
	JOIN magazines ON magRelases.idMagazine=magazines.idMag
	JOIN newsStands ON tasks.idNewsStand=newsStands.idNewsStand
	JOIN workers ON workers.idWorker=tasks.idWorker
	JOIN jobs ON jobs.idJob=tasks.idJob WHERE jobs.idJob=_idJob;
END $$

CREATE PROCEDURE relaseNumbersByMagName
(
	_magName VARCHAR(50)
)
BEGIN
	SELECT magRelases.magNumber FROM magRelases JOIN magazines ON magRelases.idMagazine=magazines.idMag AND magazines.title=_magName;
END $$

CREATE PROCEDURE allJobsByDate
(
	_JobDate VARCHAR(10)
)
BEGIN
	SELECT jobs.jobName FROM jobs WHERE jobs._date = _jobDate;
END $$

CREATE PROCEDURE allBusinessName()
BEGIN
	SELECT newsStands.businessName FROM newsStands;
END $$

CREATE PROCEDURE allTaskType()
BEGIN
	SELECT "deliver", "returner";
END $$

CREATE PROCEDURE allJobsDate()
BEGIN
	SELECT jobs._date FROM jobs ORDER BY(jobs._date);
END $$

DELIMITER ;
/*END PROCEDURES*/

/*FUNCTIONS*/

DELIMITER $$

CREATE FUNCTION checkLogIn()
RETURNS INTEGER /*1: you are logged in if you can get 1*/
BEGIN
	RETURN 1;
END $$

CREATE FUNCTION hello()
RETURNS VARCHAR(512)
BEGIN
	DECLARE _txt VARCHAR(512);
	SELECT "welcome to DistriButor MySQL Server!\nPay attention: each access and each operation are stored.\nGood stay" INTO _txt; /*the message the user of dbinterface will see just after logged in*/
	RETURN _txt;
END $$

CREATE FUNCTION locationExist
(
	_province VARCHAR(50)
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT locations.idLocation FROM locations WHERE UPPER(locations.province) = UPPER(_province) INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertLocation
(
	_country VARCHAR(50),
	_region VARCHAR(50),
	_province VARCHAR(50),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2:empty or null fields, 3: updated, 4:deleted*/
BEGIN
	DECLARE _idOwn INTEGER;

	IF(_type=2) THEN
		DELETE FROM locations WHERE idLocation=_id;
		RETURN 4;
	END IF;

	IF NULLIF(_country, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_region, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_province, '') IS NULL THEN RETURN 2; END IF;

	IF(_type=1)
	THEN
		UPDATE locations SET locations.country=_country, locations.region=_region,locations.province=_province WHERE locations.idLocation=_id;
		RETURN 3;
	END IF;
	IF(_type=0) THEN

		IF (SELECT locationExist(_province) > 0) THEN RETURN 0; END IF; /*already exist*/

		INSERT INTO locations VALUES (NULL,_country,_region,_province);

		RETURN 1;
	END IF;
END $$

CREATE FUNCTION phoneExist
(
	_phoneN VARCHAR(50)
)
RETURNS INTEGER /*0: already exist, 1:not exist*/
BEGIN
	DECLARE _id INTEGER;

	SELECT phoneNumbers.idPhone FROM phoneNumbers WHERE phoneNumbers.phone = _phoneN INTO _id;

	RETURN _id;
END $$

CREATE FUNCTION insertPhoneNumber
(
	_phoneN VARCHAR(50),
	_lastnameOwner VARCHAR(50),
	_nameOwner VARCHAR(50),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_idPhone INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2: owner doesn't exists, 3:empty or null fields, 4:updated, 5:deleted*/
BEGIN
	DECLARE _idOwn INTEGER;

	IF(_type=2) THEN
		DELETE FROM phoneNumbers WHERE phoneNumbers.idPhone=_idPhone;
		RETURN 5;
	END IF;

	IF NULLIF(_phoneN, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_lastnameOwner, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_nameOwner, '') IS NULL THEN RETURN 3; END IF;

	SELECT workerExist(_lastnameOwner,_nameOwner) INTO _idOwn;

	IF(_type=1) THEN
		UPDATE phoneNumbers SET phoneNumbers.phone = _phoneN, phoneNumbers.idWorker = _idOwn WHERE phoneNumbers.idPhone=_idPhone;
		RETURN 4;
	END IF;

	IF(_type=0) THEN
		IF (SELECT phoneExist(_phoneN) > 0) THEN RETURN 0; END IF; /*already exist*/

		SELECT workerExist(_lastnameOwner,_nameOwner) INTO _idOwn;
		SELECT IFNULL(_idOwn, -1) INTO _idOwn; /*if owner does not exists, return -1*/
		if(_idOwn = -1) THEN
			RETURN 2;
		END IF;

		IF(_idOwn > 0) THEN

			INSERT INTO phoneNumbers VALUES (NULL, _phoneN, _idOwn);
			RETURN 1;
		END IF;
	END IF;
END $$

CREATE FUNCTION workerExist
(
	_lastname VARCHAR(50),
	_name VARCHAR(50)
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT workers.idWorker FROM workers WHERE workers.lastname = _lastname AND workers.name = _name INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertWorker
(
	_lastname VARCHAR(50),
	_name VARCHAR(50),
	_email VARCHAR(50),
	_dateOfBirth VARCHAR(10),
	_province VARCHAR(50),
	_city VARCHAR(50),
	_zipCode VARCHAR(50),
	_address VARCHAR(50),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2: province doesn't exists, 3: empty or null fields, 4:updated, 5:deleted*/
BEGIN
	DECLARE _idloc INTEGER;

	IF(_type=2) THEN
		DELETE FROM workers WHERE workers.idWorker=_id;
		RETURN 5;
	END IF;

	IF NULLIF(_lastname, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_name, '') IS NULL THEN RETURN 3; END IF;
	/*mail not listed beacuse mail is not NOT NULL marked*/
	IF NULLIF(_dateOfBirth, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_province, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_city, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_zipCode, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_address, '') IS NULL THEN RETURN 3; END IF;

	SELECT locationExist(_province) INTO _idloc;

	IF(_type=1) THEN
		UPDATE workers SET workers.lastname=_lastname,workers.name=_name,workers.email=_email,workers.dateOfBirth=_dateOfBirth,workers.city=_city,workers.zipCode=_zipCode,workers.address=_address,workers.idLocation=_idloc WHERE workers.idWorker=_id;
		RETURN 4;
	END IF;

	IF(_type=0) THEN
		IF (SELECT workerExist(_lastname,_name) > 0) THEN
			RETURN 0;
		END IF;

		SELECT IFNULL(_idloc,-1) INTO _idloc; /*if exists is null, return -1*/

		IF(_idloc = -1 ) THEN
			RETURN 2;
		END IF;

		IF (_idloc > 0) THEN
			INSERT INTO workers VALUES (NULL, _lastname,_name,_email,_dateOfBirth,_city,_zipCode,_address,_idloc);
			RETURN 1;
		END IF;
	END IF;

END $$

CREATE FUNCTION newsStandExist
(
	_businessName VARCHAR(50)
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT newsStands.idNewsStand FROM newsStands WHERE newsStands.businessName = _businessName INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertNewsStand
(
	_businessName VARCHAR(50),
	_piva VARCHAR(50),
	_city VARCHAR(50),
	_zipCode VARCHAR(10),
	_address VARCHAR(50),
	_province VARCHAR(50),
	_newsstandPhone VARCHAR(50),
	_lastnameOwner VARCHAR(50),
	_nameOwner VARCHAR(50),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exist, 2: province doesn't exist, 3: owner doesn't exist, 4: empty or null fields, 5:updated, 6:deleted*/
BEGIN
	DECLARE _idloc INTEGER;
	DECLARE _idOwn INTEGER;

	IF(_type=2) THEN
		DELETE FROM newsStands WHERE newsStands.idNewsStand=_id;
		RETURN 6;
	END IF;

	IF NULLIF(_businessName, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_piva, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_city, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_zipCode, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_address, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_province, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_newsstandPhone, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_lastnameOwner, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_nameOwner, '') IS NULL THEN RETURN 4; END IF;

	SELECT locationExist(_province) INTO _idloc;
	SELECT IFNULL(_idloc, -1) INTO _idloc; /*if province does not exists, return -1*/
	IF(_idloc = -1 ) THEN
		RETURN 2;
	END IF;

	SELECT workerExist(_lastnameOwner,_nameOwner) INTO _idOwn;
	SELECT IFNULL(_idOwn, -1) INTO _idOwn; /*if owner does not exists, return -1*/
	if(_idOwn = -1) THEN
		RETURN 3;
	END IF;

	IF(_type=1) THEN
		UPDATE newsStands SET newsStands.businessName=_businessName,newsStands.piva=_piva,newsStands.city=_city,newsStands.zipCode=_zipCode,newsStands.address=_address,newsStands.idLocation=_idloc,newsStands.NewsStandPhoneN=_newsstandPhone,newsStands.idOwner=_idOwn WHERE newsStands.idNewsStand=_id;
		RETURN 5;
	END IF;

	IF(_type=0) THEN
		IF (SELECT newsStandExist(_businessName) > 0) THEN
			RETURN 0;
		END IF;

		IF (_idloc > 0 AND _idOwn > 0) THEN
			INSERT INTO newsStands VALUES (NULL,_businessName,_piva,_city,_zipCode,_address,_idloc,_newsstandPhone,_idOwn);
			RETURN 1;
		END IF;
	END IF;

END $$

CREATE FUNCTION magazineExist
(
	_title VARCHAR(50)
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT magazines.idMag FROM magazines WHERE magazines.title = _title INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertMagazine
(
	_title VARCHAR(50),
	_periodicity VARCHAR(50),
	_lastnameOwner VARCHAR(50),
	_nameOwner VARCHAR(50),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2: owner does not exist, 3:periodicity does not exist, 4:empty or null fields, 5:updated, 6: deleted*/
BEGIN
	DECLARE _idOwn INTEGER;
	DECLARE _idPeriod INTEGER;

	IF(_type=2) THEN
		DELETE FROM magazines WHERE magazines.idMag=_id;
		RETURN 6;
	END IF;

	IF NULLIF(_title, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_periodicity, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_lastnameOwner, '') IS NULL THEN RETURN 4; END IF;
	IF NULLIF(_nameOwner, '') IS NULL THEN RETURN 4; END IF;

	SELECT workerExist(_lastnameOwner,_nameOwner) INTO _idOwn;
	SELECT IFNULL(_idOwn, -1) INTO _idOwn; /*if owner does not exists, return -1*/
	if(_idOwn = -1) THEN
		RETURN 2;
	END IF;

	SELECT periodExist(_periodicity) INTO _idPeriod;
	SELECT IFNULL(_idPeriod, -1) INTO _idPeriod; /*if owner does not exists, return -1*/
	if(_idPeriod = -1) THEN
		RETURN 3;
	END IF;

	IF(_type=1) THEN
		UPDATE magazines SET magazines.title=_title,magazines.idPeriodicity=_idPeriod,magazines.idOwner=_idOwn WHERE magazines.idMag=_id;
		RETURN 5;
	END IF;

	IF(_type=0) THEN
		IF (SELECT magazineExist(_title) > 0) THEN
			RETURN 0;
		END IF;
		INSERT INTO magazines VALUES (NULL,_title,_idPeriod,_idOwn);

		RETURN 1;
	END IF;
END $$

CREATE FUNCTION periodExist
(
	_period VARCHAR(50)
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT periodicities.idPeriodicity FROM periodicities WHERE UPPER(periodicities.periodicity)=UPPER(_period) INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertPeriod
(
	_period VARCHAR(50),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2:empty or null fields, 3: updated, 4: deleted*/
BEGIN

	IF(_type=2) THEN
		DELETE FROM periodicities WHERE periodicities.idPeriodicity=_id;
		RETURN 4;
	END IF;

	IF NULLIF(_period, '') IS NULL THEN RETURN 2; END IF;

	IF(_type=1) THEN
		UPDATE periodicities SET periodicities.periodicity=_period WHERE periodicities.idPeriodicity=_id;
		RETURN 3;
	END IF;

	IF(_type=0) THEN
		IF (SELECT periodExist(_period) > 0) THEN
			RETURN 0;
		END IF;

		INSERT INTO periodicities VALUES (NULL,_period);

		RETURN 1;
	END IF;
END $$

CREATE FUNCTION magRelaseExist
(
	_idMag INTEGER,
	_magNumber INTEGER
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT magRelases.idMagRelase FROM magRelases WHERE UPPER(magRelases.idMagazine)=UPPER(_idMag) AND UPPER(magRelases.magNumber) = UPPER(_magNumber) INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertMagRelase
(
	_magName VARCHAR(50),
	_magNumber INTEGER,
	_dateRelase VARCHAR(10),
	_nameRelase VARCHAR(50),
	_priceToPublic NUMERIC(5,2),
	_percentToNS INTEGER,
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2:magazine does not exist, 3:empty or null fields, 4: updated, 5: deleted*/
BEGIN
	DECLARE _idMag INTEGER;

	IF(_type=2) THEN
		DELETE FROM magRelases WHERE magRelases.idMagRelase=_id;
		RETURN 5;
	END IF;

	IF NULLIF(_magName, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_magNumber, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_dateRelase, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_nameRelase, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_priceToPublic, '') IS NULL THEN RETURN 3; END IF;
	IF NULLIF(_percentToNS, '') IS NULL THEN RETURN 3; END IF;

	SELECT magazineExist(_magName) INTO _idMag;
	SELECT IFNULL(_idMag, -1) INTO _idMag; /*if magazine does not exists, return -1*/
	if(_idMag = -1) THEN
		RETURN 2;
	END IF;

	IF(_type=1) THEN
		UPDATE magRelases SET magRelases.idMagazine=_idMag,magRelases.magNumber=_magNumber,magRelases.dateRelase=_dateRelase,magRelases.nameRelase=_nameRelase,magRelases.priceToPublic=_priceToPublic,magRelases.percentToNS=_percentToNS WHERE magRelases.idMagRelase=_id;
		RETURN 4;
	END IF;

	IF(_type=0) THEN
		IF (SELECT magRelaseExist(_idMag,_magNumber) > 0) THEN
			RETURN 0;
		END IF;

		INSERT INTO magRelases VALUES (NULL,_idMag,_magNumber,_dateRelase, _nameRelase,_priceToPublic,_percentToNS);

		RETURN 1;
	END IF;
END $$

/*
idJob INTEGER NOT NULL AUTO_INCREMENT,
	jobName VARCHAR(50),
	_date DATE NOT NULL,
	PRIMARY KEY(idJob)
*/

CREATE FUNCTION jobExist
(
	_jobName VARCHAR(50),
	_jobDate VARCHAR(10)
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT jobs.idJob FROM jobs WHERE UPPER(jobs.jobName)=UPPER(_jobName) AND UPPER(jobs._date) = UPPER(_jobDate) INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertJob
(
	_jobName VARCHAR(50),
	_jobDate VARCHAR(10),
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2:empty or null fields, 3: updated, 4: deleted*/
BEGIN

	IF(_type=2) THEN
		DELETE FROM jobs WHERE jobs.idJob=_id;
		RETURN 4;
	END IF;

	IF NULLIF(_jobName, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_jobDate, '') IS NULL THEN RETURN 2; END IF;

	IF(_type=1) THEN
		UPDATE jobs SET jobs.jobName=_jobName, jobs._date=_jobDate WHERE jobs.idJob=_id;
		RETURN 3;
	END IF;

	IF(_type=0) THEN
		IF (SELECT jobExist(_jobName,_jobDate)>0) THEN
			RETURN 0;
		END IF;

		INSERT INTO jobs VALUES (NULL, _jobName,_jobDate);

		RETURN 1;
	END IF;
END $$

CREATE FUNCTION howManyJobs
(

)
RETURNS INTEGER /*return the amount of jobs*/
BEGIN
	DECLARE _nJobs INTEGER;
	SELECT COUNT(jobs.idJob) FROM jobs INTO _nJobs;
	RETURN _nJobs;
END $$

/*
idTask INTEGER NOT NULL AUTO_INCREMENT,
	taskName VARCHAR(50),
	nCopies INTEGER NOT NULL,
	typeTask ENUM ("deliver","returner") NOT NULL,
	idMagRelase INTEGER NOT NULL,
	idNewsStand INTEGER NOT NULL,
	idWorker INTEGER NOT NULL,
	idJob INTEGER NOT NULL,

idMagRelase INTEGER NOT NULL AUTO_INCREMENT,
	idMagazine INTEGER NOT NULL,
	magNumber INTEGER NOT NULL,
	dateRelase DATE NOT NULL,
	nameRelase VARCHAR(50),
	priceToPublic NUMERIC(5,2) NOT NULL,
	percentToNS INTEGER NOT NULL,
*/

CREATE FUNCTION taskExist
(
	_taskName VARCHAR(50),
	_idJob INTEGER,
	_idNS INTEGER
)
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	SELECT tasks.idTask FROM tasks WHERE UPPER(tasks.taskName)=UPPER(_taskName) AND UPPER(tasks.idJob)=UPPER(_idJob) AND UPPER(tasks.idNewsStand)=UPPER(_idNS)INTO _id;
	RETURN _id;
END $$

CREATE FUNCTION insertTask
(
	_taskName VARCHAR(50),
	_nCopies INTEGER,
	_typeTask VARCHAR(8),
	_magTitle VARCHAR(50), 	/*For MagRelase*/
	_magNumber INTEGER, 	/*For MagRelase*/
	_nsBusinessName VARCHAR(50), /*For NewsStands*/
	_lastname VARCHAR(50), 	/*For workers*/
	_name VARCHAR(50),		/*For workers*/
	_jobName VARCHAR(50), 	/*For jobs*/
	_jobDate VARCHAR(10),	/*For jobs*/
	_type INTEGER,  /*0: insert,1:update,2:delete*/
	_id INTEGER
)
RETURNS INTEGER /*1: success, 0: already exists, 2:empty or null fields, 3:magRelase does not exist, 4:worker does not exist, 5:job does not exist, 6:newsstand does not exist, 7:magazine does not exist, 8: updated, 9:deleted*/
BEGIN
	DECLARE _idMag INTEGER;
	DECLARE _idWorker INTEGER;
	DECLARE _idJob INTEGER;
	DECLARE _idNS INTEGER;

	IF(_type=2) THEN
		DELETE FROM tasks WHERE tasks.idTask=_id;
		RETURN 9;
	END IF;

	IF NULLIF(_taskName, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_nCopies, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_typeTask, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_magTitle, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_magNumber, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_nsBusinessName, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_lastname, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_name, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_jobName, '') IS NULL THEN RETURN 2; END IF;
	IF NULLIF(_jobDate, '') IS NULL THEN RETURN 2; END IF;

	/*get the magRelases id*/
	SELECT magazineExist(_magTitle)	INTO _idMag;
	SELECT IFNULL(_idMag, -1) INTO _idMag; /*if magazine does not exists, return 7*/
	if(_idMag = -1) THEN
		RETURN 7;
	END IF;
	SELECT magRelaseExist(_idMag,_magNumber) INTO _idMag;
	SELECT IFNULL(_idMag, -1) INTO _idMag; /*if magRelase does not exists, return 3*/
	if(_idMag = -1) THEN
		RETURN 3;
	END IF;

	/*get the worker id*/
	SELECT workerExist(_lastname, _name) INTO _idWorker;
	SELECT IFNULL(_idWorker, -1) INTO _idWorker; /*if owner does not exists, return 4*/
	if(_idWorker = -1) THEN
		RETURN 4;
	END IF;

	/*get the job id*/
	SELECT jobExist(_jobName, _jobDate) INTO _idJob;
	SELECT IFNULL(_idJob, -1) INTO _idJob; /*if owner does not exists, return 5*/
	if(_idJob = -1) THEN
		RETURN 5;
	END IF;

	/*get the newsstand id*/
	SELECT newsStandExist(_nsBusinessName) INTO _idNS;
	SELECT IFNULL(_idNS, -1) INTO _idNS; /*if magazine does not exists, return 6*/
	if(_idNS = -1) THEN
		RETURN 6;
	END IF;

	IF(_type=1) THEN
		UPDATE tasks SET tasks.taskName=_taskName,tasks.nCopies=_nCopies,tasks.typeTask=_typeTask,tasks.idMagRelase=_idMag,tasks.idNewsStand=_idNS,tasks.idWorker=_idWorker,tasks.idJob=_idJob WHERE tasks.idTask=_id;
		RETURN 8;
	END IF;

	IF(_type=0) THEN
		IF (SELECT taskExist(_taskName,_idJob,_idNS) > 0) THEN
			RETURN 0;
		END IF;

		INSERT INTO tasks VALUES (NULL,_taskName,_nCopies,_typeTask,_idMag,_idNS,_idWorker,_idJob);

		RETURN 1;
	END IF;
END $$

CREATE FUNCTION updateSoldCopies()
RETURNS INTEGER
BEGIN
	DECLARE _id INTEGER;
	/*FILL THIS SOON!! (TO AVOID THE BUG IN THE TRIGGERS)*/
	RETURN _id;
END $$

DELIMITER ;

/*END FUNCTIONS*/

/*USERS*/
CREATE USER 'guest'@'%' IDENTIFIED BY 'guest';

GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.showtaskByType TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.showSoldCopies TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allProvince TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allOwners TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.workerPhoneNumbers TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allWorkers TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.workersPhoneNumber TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allMagazines TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allPeriods TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allMagazinesName TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allMagRelases TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allLocations TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.tasksByJob TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.relaseNumbersByMagName TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allJobsByDate TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allBusinessName TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allTaskType TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allJobsDate TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.showAllTasks TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allJobs TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.allNewsstands TO 'guest'@'%';
GRANT EXECUTE ON PROCEDURE DISTRIBUTOR.showSoldCopiesInvoiced TO 'guest'@'%';

GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertLocation TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertPhoneNumber TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertWorker TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertNewsStand TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.checkLogIn TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertMagazine TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertPeriod TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertMagRelase TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertJob TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.howManyJobs TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.insertTask TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.locationExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.phoneExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.workerExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.newsStandExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.magazineExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.periodExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.magRelaseExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.jobExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.taskExist TO 'guest'@'%';
GRANT EXECUTE ON FUNCTION DISTRIBUTOR.hello TO 'guest'@'%';
/*END USERS*/


text:
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<artifactId>maven-jaxb2-plugin-tests-po-scd</artifactId>
	<parent>
		<groupId>org.jvnet.jaxb2.maven2</groupId>
		<artifactId>maven-jaxb2-plugin-tests</artifactId>
		<version>2.3.6-SNAPSHOT</version>
	</parent>
	<packaging>jar</packaging>
	<name>Maven JAXB 2.x Plugin Tests [po-scd]</name>
	<dependencies>
		<dependency>
			<groupId>org.glassfish.jaxb</groupId>
			<artifactId>jaxb-runtime</artifactId>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.jvnet.jaxb2.maven2</groupId>
				<artifactId>maven-jaxb2-plugin</artifactId>
				<configuration>
					<extension>true</extension>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>


text:
package com.google.cloud.gkehub.v1alpha2.samples;

// [START gkehub_v1alpha2_generated_GkeHub_GenerateConnectManifest_sync]
import com.google.cloud.gkehub.v1alpha2.GenerateConnectManifestRequest;
import com.google.cloud.gkehub.v1alpha2.GenerateConnectManifestResponse;
import com.google.cloud.gkehub.v1alpha2.GkeHubClient;
import com.google.protobuf.ByteString;

public class SyncGenerateConnectManifest {

  public static void main(String[] args) throws Exception {
    syncGenerateConnectManifest();
  }

  public static void syncGenerateConnectManifest() throws Exception {
    // This snippet has been automatically generated and should be regarded as a code template only.
    // It will require modifications to work:
    // - It may require correct/in-range values for request initialization.
    // - It may require specifying regional endpoints when creating the service client as shown in
    // https://cloud.google.com/java/docs/setup#configure_endpoints_for_the_client_library
    try (GkeHubClient gkeHubClient = GkeHubClient.create()) {
      GenerateConnectManifestRequest request =
          GenerateConnectManifestRequest.newBuilder()
              .setName("name3373707")
              .setNamespace("namespace1252218203")
              .setProxy(ByteString.EMPTY)
              .setVersion("version351608024")
              .setIsUpgrade(true)
              .setRegistry("registry-690212803")
              .setImagePullSecretContent(ByteString.EMPTY)
              .build();
      GenerateConnectManifestResponse response = gkeHubClient.generateConnectManifest(request);
    }
  }
}
// [END gkehub_v1alpha2_generated_GkeHub_GenerateConnectManifest_sync]


text:
package uk.org.rbc1b.roms.controller.report;

import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import uk.org.rbc1b.roms.controller.ResourceNotFoundException;
import uk.org.rbc1b.roms.db.report.FixedReport;
import uk.org.rbc1b.roms.db.report.ReportDao;
import au.com.bytecode.opencsv.CSVWriter;
import com.google.common.net.MediaType;

/**
 * Handle data reports.
 */
@Controller
@RequestMapping("/reports")
public class ReportsController {
    @Autowired
    private ReportDao reportDao;
    @Autowired
    private DataSource dataSource;

    /**
     * Return the list of active reports.
     * @param model model
     * @return view name id is found
     */
    @RequestMapping(value = "fixed", method = RequestMethod.GET)
    @PreAuthorize("hasPermission('REPORT', 'READ')")
    public String showFixedReports(ModelMap model) {
        List<FixedReport> reports = reportDao.findFixedReports();

        List<FixedReportModel> reportModels = new ArrayList<FixedReportModel>(reports.size());
        for (FixedReport report : reports) {
            reportModels.add(createFixedReportModel(report));
        }

        model.addAttribute("reports", reportModels);
        return "reports/fixed/list";
    }

    /**
     * Run a fixed report, returning the data in html format.
     * @param reportId report id
     * @param model model
     * @return view name
     */
    @RequestMapping(value = "fixed/{reportId}/html", method = RequestMethod.GET)
    @PreAuthorize("hasPermission('REPORT', 'READ')")
    public String runHtmlReport(@PathVariable Integer reportId, ModelMap model) {

        FixedReport fixedReport = reportDao.findFixedReport(reportId);
        if (fixedReport == null) {
            throw new ResourceNotFoundException("No fixed report #" + reportId);
        }
        model.addAttribute("report", createFixedReportModel(fixedReport));
        try {
            ReportResults reportResults = extractResults(fixedReport.getQuery());
            model.addAttribute("columnNames", reportResults.columnNames);
            model.addAttribute("resultRows", reportResults.resultRows);
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to extract report data. Message: [" + e.getMessage() + "]", e);
        }

        return "reports/fixed/show-html";
    }

    /**
     * Run a fixed report, returning the data in a downloadable csv format.
     * @param reportId report id
     * @param response servlet response to output the csv data to directly
     * @throws IOException on failure to write to output stream
     */
    @RequestMapping(value = "fixed/{reportId}/csv", method = RequestMethod.GET)
    @PreAuthorize("hasPermission('REPORT', 'READ')")
    public void downloadCsvReport(@PathVariable Integer reportId, HttpServletResponse response) throws IOException {

        FixedReport fixedReport = reportDao.findFixedReport(reportId);
        if (fixedReport == null) {
            throw new ResourceNotFoundException("No fixed report #" + reportId);
        }

        ReportResults reportResults;
        try {
            reportResults = extractResults(fixedReport.getQuery());
        } catch (SQLException e) {
            throw new IllegalStateException("Failed to extract report data. Message: [" + e.getMessage() + "]", e);
        }

        String[] headers = reportResults.columnNames.toArray(new String[reportResults.columnNames.size()]);

        List<String[]> records = new ArrayList<String[]>();
        for (List<String> reportRow : reportResults.resultRows) {
            records.add(reportRow.toArray(new String[reportRow.size()]));
        }

        String fileName = "edifice-report-" + fixedReport.getName().replace(" ", "-").toLowerCase() + ".csv";

        response.setContentType(MediaType.CSV_UTF_8.toString());
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

        OutputStream output = response.getOutputStream();
        CSVWriter writer = new CSVWriter(new OutputStreamWriter(output), '\u0009');

        writer.writeNext(headers);
        writer.writeAll(records);

        writer.close();

    }

    private FixedReportModel createFixedReportModel(FixedReport report) {
        FixedReportModel reportModel = new FixedReportModel();
        reportModel.setName(report.getName());
        reportModel.setDescription(report.getDescription());
        reportModel.setCsvUri("/reports/fixed/" + report.getFixedReportId() + "/csv");
        reportModel.setHtmlUri("/reports/fixed/" + report.getFixedReportId() + "/html");
        return reportModel;
    }

    private ReportResults extractResults(String sql) throws SQLException {
        Connection con = DataSourceUtils.getConnection(dataSource);
        Statement s = con.createStatement();

        ResultSet rs = s.executeQuery(sql);

        ReportResults reportResults = new ReportResults();

        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();

        List<Integer> columnTypeIds = new ArrayList<Integer>();
        for (int i = 0; i < columnCount; i++) {
            columnTypeIds.add(rsmd.getColumnType(i + 1));
        }

        reportResults.columnNames = new ArrayList<String>();
        for (int i = 0; i < columnCount; i++) {
            reportResults.columnNames.add(rsmd.getColumnLabel(i + 1));
        }

        reportResults.resultRows = new ArrayList<List<String>>();
        while (rs.next()) {
            List<String> resultRow = new ArrayList<String>();

            for (int i = 0; i < columnCount; i++) {
                Integer columnTypeId = columnTypeIds.get(i);
                if (columnTypeId.intValue() == Types.BOOLEAN || columnTypeId.intValue() == Types.BIT) {
                    resultRow.add(Boolean.valueOf(rs.getBoolean(i + 1)).toString());
                } else {
                    resultRow.add(rs.getString(i + 1));
                }
            }

            reportResults.resultRows.add(resultRow);
        }

        return reportResults;
    }

    private static final class ReportResults {

        private List<String> columnNames;
        private List<List<String>> resultRows;
    }
}


text:
package org.wildfly.extras.creaper.commands.auditlog;

import java.io.IOException;
import java.util.concurrent.TimeoutException;
import org.jboss.arquillian.junit.Arquillian;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.wildfly.extras.creaper.core.CommandFailedException;
import org.wildfly.extras.creaper.core.ManagementClient;
import org.wildfly.extras.creaper.core.ServerVersion;
import org.wildfly.extras.creaper.core.online.CliException;
import org.wildfly.extras.creaper.core.online.ModelNodeResult;
import org.wildfly.extras.creaper.core.online.OnlineManagementClient;
import org.wildfly.extras.creaper.core.online.OnlineOptions;
import org.wildfly.extras.creaper.core.online.operations.Address;
import org.wildfly.extras.creaper.core.online.operations.OperationException;
import org.wildfly.extras.creaper.core.online.operations.Operations;
import org.wildfly.extras.creaper.core.online.operations.admin.Administration;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

@RunWith(Arquillian.class)
public class AddAuditLogTcpSyslogHandlerOnlineTest {
    private static final String HANDLER_NAME = "creaper-tcp-handler";
    private static final Address TEST_HANDLER_ADDRESS = Address.coreService("management")
            .and("access", "audit")
            .and("syslog-handler", HANDLER_NAME);
    private static final Address TEST_HANDLER_PROTOCOL_ADDRESS = Address.coreService("management")
            .and("access", "audit")
            .and("syslog-handler", HANDLER_NAME)
            .and("protocol", "tcp");

    private OnlineManagementClient client;
    private Operations ops;
    private Administration administration;

    private boolean reconnectTimeoutSupported;

    @Before
    public void connect() throws IOException {
        client = ManagementClient.online(OnlineOptions.standalone().localDefault().build());
        ops = new Operations(client);
        administration = new Administration(client);

        reconnectTimeoutSupported = true;
        if (client.version().lessThan(ServerVersion.VERSION_1_7_0)
                || client.version().inRange(ServerVersion.VERSION_2_0_0, ServerVersion.VERSION_2_2_0)) {
            reconnectTimeoutSupported = false;
        }
    }

    @After
    public void cleanup() throws IOException, CliException, OperationException, TimeoutException, InterruptedException {
        try {
            ops.removeIfExists(TEST_HANDLER_ADDRESS);
            administration.reloadIfRequired();
        } finally {
            client.close();
        }
    }

    @Test
    public void addTcpHandler() throws Exception {
        AddAuditLogSyslogHandler.TcpBuilder addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .appName("application-name")
                .facility(SyslogFacilityType.NETWORK_NEWS)
                .formatter("json-formatter")
                .maxFailureCount(20)
                .maxLength(2048)
                .syslogFormat(SyslogFormatType.RFC5424)
                .truncate(false)
                .messageTransfer(MessageTransferType.OCTET_COUNTING)
                .port(9898)
                .host("127.0.0.1");
        if (reconnectTimeoutSupported) {
            addTcpSyslogHandler.reconnectTimeout(-1);
        }
        client.apply(addTcpSyslogHandler.build());

        assertTrue("The TCP syslog handler should be created", ops.exists(TEST_HANDLER_ADDRESS));

        checkAttribute(TEST_HANDLER_ADDRESS, "app-name", "application-name");
        checkAttribute(TEST_HANDLER_ADDRESS, "facility", "NETWORK_NEWS");
        checkAttribute(TEST_HANDLER_ADDRESS, "formatter", "json-formatter");
        checkAttribute(TEST_HANDLER_ADDRESS, "max-failure-count", "20");
        checkAttribute(TEST_HANDLER_ADDRESS, "max-length", "2048");
        checkAttribute(TEST_HANDLER_ADDRESS, "syslog-format", "RFC5424");
        checkAttribute(TEST_HANDLER_ADDRESS, "truncate", "false");
        checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "host", "127.0.0.1");
        checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "port", "9898");
        checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "message-transfer", "OCTET_COUNTING");
        if (reconnectTimeoutSupported) {
            checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "reconnect-timeout", "-1");
        }
    }

    @Test
    public void overrideExistingTcpHandler() throws Exception {
        AddAuditLogSyslogHandler addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .formatter("json-formatter")
                .build();
        client.apply(addTcpSyslogHandler);
        assertTrue("The TCP syslog handler should be created", ops.exists(TEST_HANDLER_ADDRESS));

        AddAuditLogSyslogHandler.TcpBuilder addTcpSyslogHandler2 = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .appName("appName")
                .facility(SyslogFacilityType.SYSLOGD)
                .formatter("json-formatter")
                .maxFailureCount(15)
                .maxLength(1024)
                .syslogFormat(SyslogFormatType.RFC3164)
                .truncate(true)
                .host("localhost")
                .port(9898)
                .messageTransfer(MessageTransferType.NON_TRANSPARENT_FRAMING)
                .replaceExisting();
        if (reconnectTimeoutSupported) {
            addTcpSyslogHandler2.reconnectTimeout(-1);
        }
        client.apply(addTcpSyslogHandler2.build());

        assertTrue("The TCP syslog handler should be created", ops.exists(TEST_HANDLER_ADDRESS));

        checkAttribute(TEST_HANDLER_ADDRESS, "app-name", "appName");
        checkAttribute(TEST_HANDLER_ADDRESS, "facility", "SYSLOGD");
        checkAttribute(TEST_HANDLER_ADDRESS, "formatter", "json-formatter");
        checkAttribute(TEST_HANDLER_ADDRESS, "max-failure-count", "15");
        checkAttribute(TEST_HANDLER_ADDRESS, "max-length", "1024");
        checkAttribute(TEST_HANDLER_ADDRESS, "syslog-format", "RFC3164");
        checkAttribute(TEST_HANDLER_ADDRESS, "truncate", "true");
        checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "host", "localhost");
        checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "port", "9898");
        checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "message-transfer", "NON_TRANSPARENT_FRAMING");
        if (reconnectTimeoutSupported) {
            checkAttribute(TEST_HANDLER_PROTOCOL_ADDRESS, "reconnect-timeout", "-1");
        }
    }

    @Test(expected = CommandFailedException.class)
    public void overrideExistingTcpHandler_notAllowed() throws Exception {
        AddAuditLogSyslogHandler addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .formatter("json-formatter")
                .build();
        client.apply(addTcpSyslogHandler);
        assertTrue("The TCP syslog handler should be created", ops.exists(TEST_HANDLER_ADDRESS));

        AddAuditLogSyslogHandler addTcpSyslogHandler2 = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .appName("application-name")
                .formatter("json-formatter")
                .build();
        client.apply(addTcpSyslogHandler2);

        fail("File handler creaper-tcp-handler already exists, an exception should be thrown");
    }

    @Test(expected = IllegalArgumentException.class)
    public void addTcpHandler_nullName() throws Exception {
        AddAuditLogSyslogHandler addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder(null)
                .formatter("json-formatter")
                .build();
        client.apply(addTcpSyslogHandler);
    }

    @Test(expected = IllegalArgumentException.class)
    public void addTcpHandler_emptyName() throws Exception {
        AddAuditLogSyslogHandler addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder("")
                .formatter("json-formatter")
                .build();
        client.apply(addTcpSyslogHandler);
    }

    @Test(expected = IllegalArgumentException.class)
    public void addTcpHandler_nullFormatter() throws Exception {
        AddAuditLogSyslogHandler addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .formatter(null)
                .build();
        client.apply(addTcpSyslogHandler);
    }

    @Test(expected = IllegalArgumentException.class)
    public void addTcpHandler_emptyFormatter() throws Exception {
        AddAuditLogSyslogHandler addTcpSyslogHandler = new AddAuditLogSyslogHandler.TcpBuilder(HANDLER_NAME)
                .formatter("")
                .build();
        client.apply(addTcpSyslogHandler);
    }

    private void checkAttribute(Address address, String attribute, String expectedValue) throws IOException {
        ModelNodeResult readAttribute = ops.readAttribute(address, attribute);
        readAttribute.assertSuccess("Read operation for " + attribute + " failed");
        assertEquals("Read operation for " + attribute + " return wrong value", expectedValue,
                readAttribute.stringValue());
    }
}