001package ganymede.kernel.magic;
002/*-
003 * ##########################################################################
004 * Ganymede
005 * %%
006 * Copyright (C) 2021, 2022 Allen D. Ball
007 * %%
008 * Licensed under the Apache License, Version 2.0 (the "License");
009 * you may not use this file except in compliance with the License.
010 * You may obtain a copy of the License at
011 *
012 *      http://www.apache.org/licenses/LICENSE-2.0
013 *
014 * Unless required by applicable law or agreed to in writing, software
015 * distributed under the License is distributed on an "AS IS" BASIS,
016 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017 * See the License for the specific language governing permissions and
018 * limitations under the License.
019 * ##########################################################################
020 */
021import com.fasterxml.jackson.databind.JsonNode;
022import ball.annotation.ServiceProviderFor;
023import ganymede.notebook.AbstractMagic;
024import ganymede.notebook.Description;
025import ganymede.notebook.Magic;
026import java.util.Collections;
027import java.util.stream.Stream;
028import lombok.Data;
029import lombok.NoArgsConstructor;
030import lombok.ToString;
031import lombok.extern.log4j.Log4j2;
032import org.jooq.DSLContext;
033import org.jooq.exception.DataAccessException;
034import picocli.CommandLine.Command;
035import picocli.CommandLine.Option;
036import picocli.CommandLine.ParameterException;
037import picocli.CommandLine.Parameters;
038
039/**
040 * {@link SQL} {@link Magic}.
041 *
042 * @see ganymede.notebook.NotebookContext#sql
043 * @see DSLContext
044 *
045 * @author {@link.uri mailto:ball@hcf.dev Allen D. Ball}
046 */
047@ServiceProviderFor({ Magic.class })
048@Description("Execute code in SQL REPL")
049@NoArgsConstructor @ToString @Log4j2
050public class SQL extends AbstractMagic {
051    private DSLContext dsl = null;
052
053    @Override
054    public void execute(String line0, String code, JsonNode metadata) throws Exception {
055        try {
056            if (dsl != null && (! context.sql.values().contains(dsl))) {
057                dsl = null;
058            }
059
060            var argv = Magic.getCellMagicCommand(line0);
061            var arguments = new Arguments();
062
063            parse(argv, arguments);
064
065            if (arguments.getUrl() != null) {
066                dsl = arguments.dsl();
067            }
068
069            if (! code.isBlank()) {
070                if (dsl == null) {
071                    dsl = arguments.dsl();
072                }
073
074                context.sql.queries.clear();
075                context.sql.results.clear();
076
077                var queries = dsl.parser().parse(code);
078
079                Collections.addAll(context.sql.queries, queries.queries());
080
081                for (var query : queries) {
082                    var result = dsl.fetch(query.getSQL());
083
084                    context.sql.results.add(result);
085
086                    if (arguments.isPrint()) {
087                        context.print(result);
088                    }
089                }
090            } else {
091                if (! context.sql.isEmpty()) {
092                    context.sql.keySet().forEach(System.out::println);
093                } else {
094                    System.out.println("No JDBC connections have been established.");
095                }
096            }
097        } catch (ParameterException exception) {
098            System.err.println(exception.getMessage());
099            System.err.println();
100            exception.getCommandLine().usage(System.err);
101        } catch (DataAccessException exception) {
102            System.err.println(exception.getMessage());
103        } catch (Exception exception) {
104            exception.printStackTrace(System.err);
105        }
106    }
107
108    @Override
109    public String getUsage() { return getUsage(new Arguments()); }
110
111    @Command @Data
112    private class Arguments {
113        @Parameters(description = { "JDBC Connection URL" }, index = "0", arity = "0..1")
114        private String url = null;
115
116        @Parameters(description = { "JDBC Connection Username" }, index = "1", arity = "0..1", defaultValue = "root")
117        private String username = null;
118
119        @Parameters(description = { "JDBC Connection Password" }, index = "2", arity = "0..1")
120        private String password = null;
121
122        @Option(names = { "--no-print" }, negatable = true, description = { "Print query results.  true by default" })
123        private boolean print = true;
124
125        public DSLContext dsl() {
126            return context.sql.connect(getUrl(), getUsername(), getPassword());
127        }
128    }
129}