001/*
002 * ============================================================================
003 *  Copyright © 2002-2024 by Thomas Thrien.
004 *  All Rights Reserved.
005 * ============================================================================
006 *  Licensed to the public under the agreements of the GNU Lesser General Public
007 *  License, version 3.0 (the "License"). You may obtain a copy of the License at
008 *
009 *       http://www.gnu.org/licenses/lgpl.html
010 *
011 *  Unless required by applicable law or agreed to in writing, software
012 *  distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
013 *  WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
014 *  License for the specific language governing permissions and limitations
015 *  under the License.
016 */
017
018package org.tquadrat.foundation.sql;
019
020import static java.lang.Character.isWhitespace;
021import static org.apiguardian.api.API.Status.STABLE;
022import static org.tquadrat.foundation.lang.CommonConstants.EMPTY_STRING;
023import static org.tquadrat.foundation.lang.Objects.nonNull;
024import static org.tquadrat.foundation.lang.Objects.requireNonNullArgument;
025import static org.tquadrat.foundation.lang.Objects.requireNotEmptyArgument;
026import static org.tquadrat.foundation.util.StringUtils.isEmptyOrBlank;
027import static org.tquadrat.foundation.util.StringUtils.splitString;
028
029import java.io.Serial;
030import java.io.Serializable;
031import java.sql.Connection;
032import java.sql.DatabaseMetaData;
033import java.sql.ResultSet;
034import java.sql.SQLException;
035import java.util.ArrayList;
036import java.util.Collection;
037import java.util.HashMap;
038import java.util.List;
039import java.util.Map;
040import java.util.Optional;
041import java.util.StringJoiner;
042import java.util.stream.Stream;
043import java.util.stream.StreamSupport;
044
045import org.apiguardian.api.API;
046import org.tquadrat.foundation.annotation.ClassVersion;
047import org.tquadrat.foundation.annotation.UtilityClass;
048import org.tquadrat.foundation.exception.EmptyArgumentException;
049import org.tquadrat.foundation.exception.NullArgumentException;
050import org.tquadrat.foundation.exception.PrivateConstructorForStaticClassCalledError;
051import org.tquadrat.foundation.exception.ValidationException;
052import org.tquadrat.foundation.lang.Objects;
053import org.tquadrat.foundation.sql.internal.ResultSetSpliterator;
054
055/**
056 *  <p>{@summary Several utilities for the work with databases that will be
057 *  accessed through plain JDBC.}
058 *
059 *  @version $Id: DatabaseUtils.java 1132 2024-05-08 23:11:24Z tquadrat $
060 *  @extauthor Thomas Thrien - thomas.thrien@tquadrat.org
061 *  @UMLGraph.link
062 *  @since 0.1.0
063 */
064@ClassVersion( sourceVersion = "$Id: DatabaseUtils.java 1132 2024-05-08 23:11:24Z tquadrat $" )
065@UtilityClass
066@API( status = STABLE, since = "0.1.0" )
067public final class DatabaseUtils
068{
069        /*---------------*\
070    ====** Inner Classes **====================================================
071        \*---------------*/
072    /**
073     *  <p>{@summary Instances of this class are used to return the status of
074     *  a call to
075     *  {@link DatabaseUtils#execute(Connection,String...)}.}</p>
076     *
077     *  @param  command The failed command.
078     *  @param  error   The exception that was thrown to indicate the failure.
079     *
080     *  @extauthor Thomas Thrien - thomas.thrien@tquadrat.org
081     *  @version $Id: DatabaseUtils.java 1132 2024-05-08 23:11:24Z tquadrat $
082     *  @since 0.0.1
083     *
084     *  @UMLGraph.link
085     */
086    @ClassVersion( sourceVersion = "$Id: DatabaseUtils.java 1132 2024-05-08 23:11:24Z tquadrat $" )
087    @API( status = STABLE, since = "0.0.1" )
088    public record ExecStatus( String command, SQLException error ) implements Serializable
089    {
090            /*------------------------*\
091        ====** Static Initialisations **=======================================
092            \*------------------------*/
093        /**
094         *  The serial version UID for objects of this class: {@value}.
095         *
096         *  @hidden
097         */
098        @Serial
099        private static final long serialVersionUID = 1L;
100
101            /*--------------*\
102        ====** Constructors **=================================================
103            \*--------------*/
104        /**
105         *  Creates a new {@code ExecStatus} instance.
106         *
107         *  @param  command The failed command.
108         *  @param  error   The exception for the failure.
109         */
110        public ExecStatus
111        {
112            requireNotEmptyArgument( command, "command" );
113            requireNonNullArgument( error, "error" );
114        }   //  ExecStatus()
115    }
116    //  record ExecStatus
117
118        /*--------------*\
119    ====** Constructors **=====================================================
120        \*--------------*/
121    /**
122     *  No instance allowed for this class.
123     */
124    private DatabaseUtils() { throw new PrivateConstructorForStaticClassCalledError( DatabaseUtils.class ); }
125
126        /*---------*\
127    ====** Methods **==========================================================
128        \*---------*/
129    /**
130     *  <p>{@summary Checks whether the table, that is specified by its
131     *  {@code catalog}, schema (through {@code schemaPattern}) and
132     *  {@code tableNamePattern}, exists.}</p>
133     *  <p>Where a pattern is allowed, the wildcards {@literal "%"} and
134     *  {@literal "_"} can be used.</p>
135     *  <p>This method works for any RDBMS.</p>
136     *
137     *  @param  connection  The connection to the database.
138     *  @param  catalog A catalog name; it must match the catalog name as it is
139     *      stored in the database. The empty String retrieves those tables
140     *      without a catalog, and {@code null} means that the catalog name
141     *      should not be used to narrow the search.
142     *  @param  schemaPattern   A schema name pattern; it must match the schema
143     *      name as it is stored in the database. The empty String retrieves
144     *      those tables without a schema, and {@code null} means that the
145     *      schema name should not be used to narrow the search.
146     *  @param  tableNamePattern    A table name pattern; it must match the
147     *      table name as it is stored in the database.
148     *  @param  tableTypes  A list of table types, which must be from the list
149     *      of table types returned from
150     *      {@link DatabaseMetaData#getTableTypes()},
151     *      to include. It can be omitted to return all types.
152     *  @return {@code true} if the specified table exists, {@code false}
153     *      otherwise.
154     *  @throws SQLException    A database access error occurred.
155     */
156    public static final boolean checkIfTableExists( final Connection connection, final String catalog, final String schemaPattern, final String tableNamePattern, final String... tableTypes ) throws SQLException
157    {
158        final var metaData = connection.getMetaData();
159        var retValue = false;
160        final var effectiveTypes = nonNull( tableTypes ) && tableTypes.length == 0 ? null : tableTypes;
161        try( final var resultSet = metaData.getTables( catalog, schemaPattern, tableNamePattern, effectiveTypes ) )
162        {
163            retValue = resultSet.next();
164        }
165
166        //---* Done *----------------------------------------------------------
167        return retValue;
168    }   //  checkIfTableExists()
169
170    /**
171     *  Dumps the given result set to a
172     *  {@link List}
173     *  of Strings.
174     *
175     *  @param  resultSet   The result set to dump.
176     *  @return The contents of the result set.
177     *  @throws SQLException    Something went wrong when reading the result
178     *      set.
179     *
180     *  @since 0.4.1
181     */
182    @API( status = STABLE, since = "0.4.1" )
183    public static final List<String> dumpResultSet( final ResultSet resultSet ) throws SQLException
184    {
185        final var metaData = requireNonNullArgument( resultSet, "resultSet" ).getMetaData();
186        final var columnCount = metaData.getColumnCount();
187        final List<String> retValue = new ArrayList<>();
188
189        while( resultSet.next() )
190        {
191            final var line = new StringJoiner( "|", "{", "}" );
192            line.setEmptyValue( "{}" );
193            for( var i = 0; i < columnCount; ++i )
194            {
195                final var label = metaData.getColumnLabel( i + 1 );
196                final var value = Objects.toString( resultSet.getObject( i + 1 ) );
197                line.add( resultSet.wasNull() ? "%s=NULL".formatted( label ) : "%s='%s'".formatted( label, value ) );
198            }
199            retValue.add( line.toString() );
200        }
201
202        //---* Done *----------------------------------------------------------
203        return retValue;
204    }   //  dumpResultSet()
205
206    /**
207     *  <p>{@summary Executes the given list of commands on the given database
208     *  connection.}</p>
209     *  <p>The commands should be DDL or DML commands, not queries. If all
210     *  commands were executed successfully, the method calls
211     *  {@link Connection#commit() commit()}
212     *  on the provided connection, otherwise a call to
213     *  {@link Connection#rollback() rollback()}
214     *  is issued. In case the connection is configured for
215     *  {@linkplain Connection#getAutoCommit() AutoCommit},
216     *  neither call will be made.</p>
217     *  <p>In case of an error, the return value is not
218     *  {@linkplain Optional#empty() empty}.</p>
219     *  <p>Empty commands and commands that will start with a hash
220     *  (&quot;{@code #}&quot;) will be ignored; this allows to process
221     *  script files without extensive reformatting.</p>
222     *
223     *  @param  connection  The connection to the database.
224     *  @param  commands    A list of SQL (DDL or DML) commands that will be
225     *      executed against the provided connection.
226     *  @return An instance of
227     *      {@link Optional}
228     *      that holds the execution status; will be.
229     *      {@linkplain Optional#empty() empty}
230     *      if all commands were successfully executed.
231     *
232     *  @see Connection#getAutoCommit()
233     *  @see Connection#setAutoCommit(boolean)
234     *  @see #parseSQLScript(CharSequence)
235     */
236    @API( status = STABLE, since = "0.0.1" )
237    public static final Optional<ExecStatus> execute( final Connection connection, final String... commands )
238    {
239        final var retValue = execute( connection, List.of( requireNonNullArgument( commands, "commands" ) ) );
240
241        //---* Done *----------------------------------------------------------
242        return retValue;
243    }   //  execute()
244
245    /**
246     *  <p>{@summary Executes the given list of commands on the given database
247     *  connection.}</p>
248     *  <p>The commands should be DDL or DML commands, not queries. If all
249     *  commands were executed successfully, the method calls
250     *  {@link Connection#commit() commit()}
251     *  on the provided connection, otherwise a call to
252     *  {@link Connection#rollback() rollback()}
253     *  is issued. In case the connection is configured for
254     *  {@linkplain Connection#getAutoCommit() AutoCommit},
255     *  neither call will be made.</p>
256     *  <p>In case of an error, the return value is not
257     *  {@linkplain Optional#empty() empty}.</p>
258     *  <p>Empty commands will be silently ignored.</p>
259     *
260     *  @param  connection  The connection to the database.
261     *  @param  commands    A list of SQL (DDL or DML) commands that will be
262     *      executed against the provided connection.
263     *  @return An instance of
264     *      {@link Optional}
265     *      that holds the execution status; will be.
266     *      {@linkplain Optional#empty() empty}
267     *      if all commands were successfully executed.
268     *
269     *  @see Connection#getAutoCommit()
270     *  @see Connection#setAutoCommit(boolean)
271     *  @see #parseSQLScript(CharSequence)
272     */
273    @API( status = STABLE, since = "0.1.0" )
274    public static final Optional<ExecStatus> execute( final Connection connection, final List<String> commands )
275    {
276        Optional<ExecStatus> retValue = Optional.empty();
277        var currentCommand = EMPTY_STRING;
278        try( final var statement = requireNonNullArgument( connection, "connection" ).createStatement() )
279        {
280            ExecLoop: for( final var sql : requireNonNullArgument( commands, "commands" ) )
281            {
282                if( isEmptyOrBlank( sql ) ) continue ExecLoop;
283                currentCommand = sql;
284                statement.execute( sql );
285            }   //  ExecLoop:
286
287            if( !connection.getAutoCommit() )
288            {
289                currentCommand = "commit;";
290                connection.commit();
291            }
292        }
293        catch( final SQLException e )
294        {
295            try
296            {
297                if( !connection.getAutoCommit() ) { connection.rollback(); }
298            }
299            catch( final SQLException eOnRollback )
300            {
301                e.addSuppressed( eOnRollback );
302            }
303            retValue = Optional.of( new ExecStatus( currentCommand, e ) );
304        }
305
306        //---* Done *----------------------------------------------------------
307        return retValue;
308    }   //  execute()
309
310    /**
311     *  <p>{@summary Parses the given SQL script.}</p>
312     *  <p>Basically, the method splits the provided String into the single
313     *  commands (they are separated by semicolon &quot;;&quot;), and returns
314     *  them as a
315     *  {@link List}
316     *  that can be used with
317     *  {@link #execute(Connection, List)}.</p>
318     *  <p>Text between &quot;--&quot; (two hyphens, &amp;#x002D) and the end
319     *  of the line will be seen as a comment and is ignored; same for comments
320     *  between &quot;&#x002F;*&quot; and &quot;*&#x002F;&quot;.
321     *
322     *  @param  script  The script.
323     *  @return The separated commands.
324     */
325    @SuppressWarnings( {"OverlyLongMethod", "OverlyComplexMethod"} )
326    @API( status = STABLE, since = "0.0.1" )
327    public static final List<String> parseSQLScript( final CharSequence script )
328    {
329        //---* Get the single commands *---------------------------------------
330        final var chars = requireNonNullArgument( script, "script" ).toString().toCharArray();
331        final var sourceLen = chars.length;
332        final var buffer = new char [sourceLen];
333        var targetLen = 0;
334        var isSingleQuoteString = false;
335        var isDoubleQuoteString = false;
336        var isBlockComment = false;
337        var isLineComment = false;
338        var lastChar = ' ';
339
340        ScanLoop: for( final var currentChar : chars )
341        {
342            if( (targetLen == 0) && isWhitespace( currentChar ) ) continue ScanLoop;
343
344            AnalyzeSwitch:
345            //noinspection EnhancedSwitchMigration
346            switch( currentChar )
347            {
348                case '-':
349                {
350                    if( isBlockComment || isLineComment ) break AnalyzeSwitch;
351                    if( isSingleQuoteString || isDoubleQuoteString )
352                    {
353                        buffer [targetLen++] = lastChar;
354                        break AnalyzeSwitch;
355                    }
356                    if( lastChar == currentChar )
357                    {
358                        isLineComment = true;
359                        lastChar = ' ';
360                        continue ScanLoop;
361                    }
362                    buffer [targetLen++] = lastChar;
363                    break AnalyzeSwitch;
364                }
365
366                case '\n':
367                {
368                    if( isBlockComment ) continue ScanLoop;
369                    if( isLineComment )
370                    {
371                        isLineComment = false;
372                        lastChar = ' ';
373                        continue ScanLoop;
374                    }
375                    if( isSingleQuoteString || isDoubleQuoteString )
376                    {
377                        buffer [targetLen++] = lastChar;
378                        lastChar = ' ';
379                        continue ScanLoop;
380                    }
381                    if( isWhitespace( lastChar ) )
382                    {
383                        continue ScanLoop;
384                    }
385                    if( lastChar == ';' )
386                    {
387                        buffer [targetLen++] = ';';
388                        break AnalyzeSwitch;
389                    }
390                    buffer [targetLen++] = lastChar;
391                    lastChar = ' ';
392                    continue ScanLoop;
393                }
394
395                case '\'':
396                {
397                    if( isBlockComment || isLineComment ) break AnalyzeSwitch;
398                    if( !isDoubleQuoteString )
399                    {
400                        if( !isSingleQuoteString )
401                        {
402                            isSingleQuoteString = true;
403                        }
404                        else if( lastChar != '\\' )
405                        {
406                            isSingleQuoteString = false;
407                        }
408                    }
409                    buffer [targetLen++] = lastChar;
410                    break AnalyzeSwitch;
411                }
412
413                case '"':
414                {
415                    if( isBlockComment || isLineComment ) break AnalyzeSwitch;
416                    if( !isSingleQuoteString )
417                    {
418                        if( !isDoubleQuoteString )
419                        {
420                            isDoubleQuoteString = true;
421                        }
422                        else if( lastChar != '\\' )
423                        {
424                            isDoubleQuoteString = false;
425                        }
426                    }
427                    buffer [targetLen++] = lastChar;
428                    break AnalyzeSwitch;
429                }
430
431                case '/':
432                {
433                    if( isBlockComment )
434                    {
435                        if( lastChar == '*' )
436                        {
437                            isBlockComment = false;
438                            lastChar = ' ';
439                            continue ScanLoop;
440                        }
441                        break AnalyzeSwitch;
442                    }
443                    if( isLineComment ) break AnalyzeSwitch;
444                    buffer [targetLen++] = lastChar;
445                    break AnalyzeSwitch;
446                }
447
448                case '*':
449                {
450                    if( isBlockComment || isLineComment ) break AnalyzeSwitch;
451                    if( isSingleQuoteString || isDoubleQuoteString )
452                    {
453                        buffer [targetLen++] = lastChar;
454                        break AnalyzeSwitch;
455                    }
456                    if( lastChar == '/' )
457                    {
458                        isBlockComment = true;
459                        lastChar = ' ';
460                        continue ScanLoop;
461                    }
462                    buffer [targetLen++] = lastChar;
463                    break AnalyzeSwitch;
464                }
465
466                default:
467                {
468                    if( isBlockComment || isLineComment ) break AnalyzeSwitch;
469                    if( isWhitespace( currentChar ) )
470                    {
471                        if( !isWhitespace( lastChar ) )
472                        {
473                            buffer [targetLen++] = lastChar;
474                        }
475                        break AnalyzeSwitch;
476                    }
477                    if( isSingleQuoteString || isDoubleQuoteString )
478                    {
479                        buffer [targetLen++] = lastChar;
480                        break AnalyzeSwitch;
481                    }
482                    if( currentChar == ';' )
483                    {
484                        buffer [targetLen++] = lastChar;
485                        buffer [targetLen++] = ';';
486                        lastChar = '\n';
487                        continue ScanLoop;
488                    }
489                    buffer [targetLen++] = lastChar;
490                    break AnalyzeSwitch;
491                }
492            }   //  AnalyzeSwitch:
493
494            lastChar = currentChar;
495        }   //  ScanLoop:
496
497        //---* Compose the return value *--------------------------------------
498        final List<String> retValue = targetLen > 1 ? List.of( splitString( new String( buffer, 1, targetLen ).trim(), '\n' ) ) : List.of();
499
500        //---* Done *----------------------------------------------------------
501        return retValue;
502    }   //   parseSQLScript()
503
504    /**
505     *  <p>{@summary This method checks whether the given connection is not
506     *  {@code null} and that it is open.} Otherwise it will throw a
507     *  {@link ValidationException}.</p>
508     *  <p>This method will test the method by calling
509     *  {@link Connection#isValid(int)}.</p>
510     *
511     *  @param  connection  The connection to check; can be {@code null}.
512     *  @param  name    The name of the argument; this is used for the error
513     *      message.
514     *  @param  validationTimeout   The validation timeout in seconds, with -1
515     *      for no validation (in this case, only
516     *      {@link Connection#isClosed()}
517     *      is called); a value of 0 means no timeout.
518     *  @return The value if the validation succeeds.
519     *  @throws ValidationException The connection is closed or otherwise not
520     *      valid.
521     *  @throws NullArgumentException   {@code name} or the connection is
522     *      {@code null}.
523     *  @throws EmptyArgumentException  {@code name} is the empty String.
524     *
525     *  @since 0.4.1
526     */
527    @API( status = STABLE, since = "0.4.1" )
528    public static final Connection requireValidConnectionArgument( final Connection connection, final String name, final int validationTimeout ) throws ValidationException
529    {
530        final var message = "The connection in argument '%s' is not valid".formatted( requireNotEmptyArgument( name, "name" ) );
531        requireNonNullArgument( connection, "connection" );
532
533        try
534        {
535            final var validationResult = (validationTimeout < 0) ? !connection.isClosed() : connection.isValid( validationTimeout );
536            if( !validationResult ) throw new ValidationException( message );
537        }
538        catch( final SQLException e )
539        {
540            throw new ValidationException( message, e );
541        }
542
543        //---* Done *----------------------------------------------------------
544        return connection;
545    }   //  requireValidConnectionArgument()
546
547    /**
548     *  Reads the records from the given
549     *  {@link ResultSet}
550     *  to a
551     *  {@link Map}
552     *  that uses the
553     *  {@linkplain java.sql.ResultSetMetaData#getColumnLabel(int) column labels}
554     *  as key and stores these to a
555     *  {@link List}.
556     *
557     *  @param  resultSet   The result set to dump.
558     *  @return The contents of the result set; maybe empty, but will never be
559     *      {@code null}.
560     *  @throws SQLException    Something went wrong when reading the result
561     *      set.
562     *
563     *  @since 0.4.1
564     */
565    @API( status = STABLE, since = "0.4.1" )
566    public static final List<Map<String,Object>> resultSetToMap( final ResultSet resultSet ) throws SQLException
567    {
568        final var metaData = requireNonNullArgument( resultSet, "resultSet" ).getMetaData();
569        final var columnCount = metaData.getColumnCount();
570        final var labels = new String [columnCount];
571        for( var i = 0; i < columnCount; ++i ) labels [i] = metaData.getColumnLabel( i + 1 );
572        final Collection<Map<String,Object>> buffer = new ArrayList<>();
573
574        while( resultSet.next() )
575        {
576            final Map<String,Object> record = new HashMap<>();
577            for( var i = 0; i < columnCount; ++i )
578            {
579                final var value = resultSet.getObject( i + 1 );
580                record.put( labels [i], resultSet.wasNull() ? null : value );
581            }
582            buffer.add( Map.copyOf( record ) );
583        }
584
585        final var retValue = List.copyOf( buffer );
586
587        //---* Done *----------------------------------------------------------
588        return retValue;
589    }   //  resultSetToMap()
590
591    /**
592     *  <p>{@summary Returns a
593     *  {@link Stream}
594     *  implementation for the given
595     *  {@link ResultSet}.}</p>
596     *  <p>Several operations on a {@code ResultSet} instance will not work
597     *  when called on the streamed instance.</p>
598     *  <p>When one of the operations that would move the cursor is called on
599     *  the {@code ResultSet} instance that is pushed into an operation on the
600     *  stream, an
601     *  {@link UnsupportedOperationException}
602     *  is thrown; this affects the methods</p>
603     *  <ul>
604     *      <li>{@link ResultSet#absolute(int)}</li>
605     *      <li>{@link ResultSet#afterLast()}</li>
606     *      <li>{@link ResultSet#beforeFirst()}</li>
607     *      <li>{@link ResultSet#first()}</li>
608     *      <li>{@link ResultSet#last()}</li>
609     *      <li>{@link ResultSet#moveToCurrentRow()}</li>
610     *      <li>{@link ResultSet#moveToInsertRow()}</li>
611     *      <li>{@link ResultSet#next()}</li>
612     *      <li>{@link ResultSet#previous()}</li>
613     *      <li>{@link ResultSet#relative(int)}</li>
614     *  </ul>
615     *  <p>as well as the following methods:</p>
616     *  <ul>
617     *      <li>{@link ResultSet#close()}</li>
618     *      <li>{@link ResultSet#deleteRow()}</li>
619     *      <li>{@link ResultSet#insertRow()}</li>
620     *      <li>{@link ResultSet#setFetchDirection(int)}</li>
621     *      <li>{@link ResultSet#setFetchSize(int)}</li>
622     *  </ul>
623     *  <p>A call to
624     *  {@link Stream#close()}
625     *  does not close the
626     *  {@link ResultSet}!</p>
627     *
628     *  @param  resultSet   The result set to stream on.
629     *  @return The stream.
630     */
631    @API( status = STABLE, since = "0.0.1" )
632    public static final Stream<ResultSet> stream( final ResultSet resultSet )
633    {
634        final var spliterator = new ResultSetSpliterator( resultSet );
635        final var retValue = StreamSupport.stream( spliterator, false );
636
637        //---* Done *----------------------------------------------------------
638        return retValue;
639    }   //  stream()
640}
641//  class DatabaseUtils
642
643/*
644 *  End of File
645 */