Uploaded image for project: 'Solr'
  1. Solr
  2. SOLR-8502

Improve Solr JDBC Driver to support SQL Clients like DBVisualizer

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Implemented
    • Affects Version/s: 6.0
    • Fix Version/s: 6.0
    • Component/s: SolrJ
    • Labels:

      Description

      Currently when trying to connect to Solr with the JDBC driver with a SQL client the driver must implement more methods and metadata to allow connections. This JIRA is designed to act as an umbrella for the JDBC changes.

      An initial pass from a few months ago is here: https://github.com/risdenk/lucene-solr/tree/expand-jdbc. This needs to be broken up and create patches for the related sub tasks.

      1. aggregation-query.png
        990 kB
        Joel Bernstein
      2. connection-working!.png
        789 kB
        Joel Bernstein
      3. driver-setup.png
        1.22 MB
        Joel Bernstein
      4. connection-screen-shot.png
        1.14 MB
        Joel Bernstein
      5. screenshot-4.png
        1.46 MB
        Kevin Risden
      6. screenshot-3.png
        1.45 MB
        Kevin Risden
      7. screenshot-2.png
        1.42 MB
        Kevin Risden
      8. screenshot-1.png
        1.44 MB
        Kevin Risden

        Issue Links

        1.
        Implement org.apache.solr.client.solrj.io.sql.ConnectionImpl.getMetaData and getCatalog Sub-task Closed Unassigned
         
        2.
        Implement DatabaseMetaDataImpl.getDatabaseMajorVersion and DatabaseMetaDataImpl.getDatabaseMinorVersion() Sub-task Closed Unassigned
         
        3.
        Determine test strategy and add tests for JDBC driver metadata. Sub-task Closed Unassigned
         
        4.
        Implement DatabaseMetaDataImpl.getURL() Sub-task Closed Unassigned
         
        5.
        Implement ResultSetImpl.getMetaData() Sub-task Closed Unassigned
         
        6.
        Implement StatementImpl.getConnection Sub-task Closed Joel Bernstein
         
        7.
        Implement ResultSetImpl.getStatement Sub-task Closed Unassigned
         
        8.
        Implement StatementImpl.execute(String sql), StatementImpl.getResultSet(), and StatementImpl.getUpdateCount() Sub-task Closed Joel Bernstein
         
        9.
        Implement ConnectionImpl,StatementImpl,ResultSetImpl clearWarnings and getWarnings Sub-task Resolved Unassigned
         
        10.
        Implement ResultSetMetaDataImpl.getColumnCount() Sub-task Resolved Unassigned
         
        11.
        Implement minimal set of get* methods in ResultSetImpl for column names Sub-task Resolved Unassigned
         
        12.
        Implement minimal set of get* methods in ResultSetImpl for column indices Sub-task Resolved Unassigned
         
        13.
        Implement ResultSetMetaDataImpl getColumnType and getColumnTypeName Sub-task Resolved Unassigned
         
        14.
        Implement ResultSetMetaDataImpl getColumnLabel(int column) and getColumnDisplaySize(int column) Sub-task Resolved Unassigned
         
        15.
        Implement ConnectionImpl.isValid() and DatabaseMetaDataImpl.getConnection() Sub-task Resolved Joel Bernstein
         
        16.
        Implement ResultSetImpl.wasNull() Sub-task Resolved Joel Bernstein
         
        17.
        Implement StatementImpl.getMoreResults() Sub-task Resolved Unassigned
         
        18.
        Implement DatabaseMetaDataImpl.getCatalogs() Sub-task Resolved Unassigned
         
        19.
        Implement DatabaseMetaDataImpl.getSchemas() Sub-task Resolved Unassigned
         
        20.
        Throw UnsupportedOperationException for DatabaseMetaDataImpl.getTypeInfo() Sub-task Resolved Unassigned
         
        21.
        Add information about database product name, product version, driver name, and driver version. Sub-task Resolved Unassigned
         
        22.
        Implement ConnectionImpl setCatalog and setSchema Sub-task Resolved Unassigned
         

          Activity

          Hide
          risdenk Kevin Risden added a comment -

          Added SOLR-8659 with some notes on what can continue to be improved.

          Show
          risdenk Kevin Risden added a comment - Added SOLR-8659 with some notes on what can continue to be improved.
          Hide
          risdenk Kevin Risden added a comment -

          Joel Bernstein - Thanks for the quick reviews and getting these changes merged.

          There are some more improvements that can be made. Clicking around some of the areas in DBVisualizer can cause errors to pop up here and there. I found that more methods need to be implemented for SquirrelSQL to work. I'll create a new JIRA with subtasks to address these since this seemed to work well so far.

          Show
          risdenk Kevin Risden added a comment - Joel Bernstein - Thanks for the quick reviews and getting these changes merged. There are some more improvements that can be made. Clicking around some of the areas in DBVisualizer can cause errors to pop up here and there. I found that more methods need to be implemented for SquirrelSQL to work. I'll create a new JIRA with subtasks to address these since this seemed to work well so far.
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit c2eb76941eb604f6cdf7873cab9ea02175c69000 in lucene-solr's branch refs/heads/jira/lucene-5438-nrt-replication from jbernste
          [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=c2eb769 ]

          SOLR-8502: Improve Solr JDBC Driver to support SQL Clients like DBVisualizer

          Show
          jira-bot ASF subversion and git services added a comment - Commit c2eb76941eb604f6cdf7873cab9ea02175c69000 in lucene-solr's branch refs/heads/jira/lucene-5438-nrt-replication from jbernste [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=c2eb769 ] SOLR-8502 : Improve Solr JDBC Driver to support SQL Clients like DBVisualizer
          Hide
          joel.bernstein Joel Bernstein added a comment -

          Kevin Risden, great work on this ticket. This is a great feature for Solr 6.

          Show
          joel.bernstein Joel Bernstein added a comment - Kevin Risden , great work on this ticket. This is a great feature for Solr 6.
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit c2eb76941eb604f6cdf7873cab9ea02175c69000 in lucene-solr's branch refs/heads/master from jbernste
          [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=c2eb769 ]

          SOLR-8502: Improve Solr JDBC Driver to support SQL Clients like DBVisualizer

          Show
          jira-bot ASF subversion and git services added a comment - Commit c2eb76941eb604f6cdf7873cab9ea02175c69000 in lucene-solr's branch refs/heads/master from jbernste [ https://git-wip-us.apache.org/repos/asf?p=lucene-solr.git;h=c2eb769 ] SOLR-8502 : Improve Solr JDBC Driver to support SQL Clients like DBVisualizer
          Hide
          joel.bernstein Joel Bernstein added a comment -

          Added a screenshot of an aggregation query with aliases.

          Show
          joel.bernstein Joel Bernstein added a comment - Added a screenshot of an aggregation query with aliases.
          Hide
          joel.bernstein Joel Bernstein added a comment - - edited

          Attaching a screenshot with a working connection. All I did was restart DbVisualizer and the connection started working.

          I'll move on committing the remaining open tickets. I'll also create a new ticket to implement setMaxRows() on the Statement. This is the only error I see in the debug window and it will be extremely useful in ensuring users don't select entire result sets. The implementation will simply tack on a limit clause to the end of the SQL statement if it doesn't exist.

          After this work is done I'll close out this ticket and add SOLR-8502 to CHANGES.txt for 6.0.

          Show
          joel.bernstein Joel Bernstein added a comment - - edited Attaching a screenshot with a working connection. All I did was restart DbVisualizer and the connection started working. I'll move on committing the remaining open tickets. I'll also create a new ticket to implement setMaxRows() on the Statement. This is the only error I see in the debug window and it will be extremely useful in ensuring users don't select entire result sets. The implementation will simply tack on a limit clause to the end of the SQL statement if it doesn't exist. After this work is done I'll close out this ticket and add SOLR-8502 to CHANGES.txt for 6.0.
          Hide
          risdenk Kevin Risden added a comment -

          Joel Bernstein - I put together a step-by-step guide with screenshots to get DBVisualizer setup with Solr. It is attached to SOLR-8521.

          Show
          risdenk Kevin Risden added a comment - Joel Bernstein - I put together a step-by-step guide with screenshots to get DBVisualizer setup with Solr. It is attached to SOLR-8521 .
          Hide
          risdenk Kevin Risden added a comment -

          I've been meaning to put together a "getting started" guide. I'll use this as a chance to start one with some screenshots.

          Show
          risdenk Kevin Risden added a comment - I've been meaning to put together a "getting started" guide. I'll use this as a chance to start one with some screenshots.
          Hide
          joel.bernstein Joel Bernstein added a comment -

          It's running SolrCloud with an embedded ZK. Since I can connect with a command line client I've ruled out Solr being the issue. I suspect there is something off about my DbVisualizer setup. I'll will give it another try in the next couple of days.

          Can you post a screenshot similar to the driver-setup screenshot attached to the ticket? I'll compare my local setup to your working screenshot.

          Show
          joel.bernstein Joel Bernstein added a comment - It's running SolrCloud with an embedded ZK. Since I can connect with a command line client I've ruled out Solr being the issue. I suspect there is something off about my DbVisualizer setup. I'll will give it another try in the next couple of days. Can you post a screenshot similar to the driver-setup screenshot attached to the ticket? I'll compare my local setup to your working screenshot.
          Hide
          risdenk Kevin Risden added a comment -

          I downloaded the latest nightly build from https://builds.apache.org/job/Solr-Artifacts-trunk/lastSuccessfulBuild/artifact/solr/package/, extracted the tgz, ran ./bin/solr start -c -f, and connected with DBVisualizer no problem. I didn't have an issue trying to connect to Zookeeper. How did you run Solr?

          Show
          risdenk Kevin Risden added a comment - I downloaded the latest nightly build from https://builds.apache.org/job/Solr-Artifacts-trunk/lastSuccessfulBuild/artifact/solr/package/ , extracted the tgz, ran ./bin/solr start -c -f, and connected with DBVisualizer no problem. I didn't have an issue trying to connect to Zookeeper. How did you run Solr?
          Hide
          risdenk Kevin Risden added a comment -

          hmmmmm not sure why this is happening... I can try to reproduce later tonight. That error is straight from the Solr code and not from DBVisualizer.

          Show
          risdenk Kevin Risden added a comment - hmmmmm not sure why this is happening... I can try to reproduce later tonight. That error is straight from the Solr code and not from DBVisualizer.
          Hide
          joel.bernstein Joel Bernstein added a comment -

          This works fine from a command line java program:

          Connection con = DriverManager.getConnection("jdbc:solr://localhost:9983?collection=collection2");
           con.close();
           System.out.println("Done");
          
          
          Show
          joel.bernstein Joel Bernstein added a comment - This works fine from a command line java program: Connection con = DriverManager.getConnection( "jdbc:solr: //localhost:9983?collection=collection2" ); con.close(); System .out.println( "Done" );
          Hide
          joel.bernstein Joel Bernstein added a comment -

          Here is the debug error:

          2016-02-03 08:52:38.855 INFO 955 [ExecutorRunner-pool-3-thread-11 - G.ᅣᄋ] Exception while connecting Database Connection (1) (2)

              • Database Properties ****************************
                Alias: Database Connection (1) (2)
                Settings Format: URL
                URL: jdbc:solr://localhost:9983?collection=collection2
                Driver: solr
                Userid:
                Preferred Database Type: auto
                Current Database Type: generic
                Preferred Profile: auto
                Current Profile: null (not connected?)
              • Object Type Settings ---------------------------
                None
              • Overridden User Properties ---------------------
                generic.AutoCommit = false
                generic.ConnectionMode = Production
              • Overridden Driver Properties -------------------
                None
              • End Database Properties ************************
              • Driver Information *****************************
                Name: solr
                DefaultDriver: false
                DefaultClass: org.apache.solr.client.solrj.io.sql.DriverImpl
                Paths:
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solr-solrj-6.0.0-SNAPSHOT.jar
                Type: dynamic
                Class: org.apache.solr.client.solrj.io.sql.DriverImpl
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/commons-io-2.4.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/httpclient-4.4.1.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/httpcore-4.4.1.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/httpmime-4.4.1.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/jcl-over-slf4j-1.7.7.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/noggit-0.6.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/slf4j-api-1.7.7.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/stax2-api-3.1.4.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/woodstox-core-asl-4.4.1.jar
                Type: dynamic
                Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/zookeeper-3.4.6.jar
                Type: dynamic
              • End Driver Information *************************
              • Current Connection Message *********************
                Disconnected.
              • End Current Connection Message *****************

          2016-02-03 08:52:38.857 INFO 955 [ExecutorRunner-pool-3-thread-11 - G.ᅣᄋ] Exception while connecting Database Connection (1) (2)
          org.apache.solr.common.SolrException: java.util.concurrent.TimeoutException: Could not connect to ZooKeeper localhost:9983 within 10000 ms
          at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:182)
          at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:116)
          at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:106)
          at org.apache.solr.common.cloud.ZkStateReader.<init>(ZkStateReader.java:202)
          at org.apache.solr.client.solrj.impl.CloudSolrClient.connect(CloudSolrClient.java:467)
          at org.apache.solr.client.solrj.io.SolrClientCache.getCloudSolrClient(SolrClientCache.java:49)
          at org.apache.solr.client.solrj.io.sql.ConnectionImpl.<init>(ConnectionImpl.java:56)
          at org.apache.solr.client.solrj.io.sql.DriverImpl.connect(DriverImpl.java:75)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:497)
          at com.onseven.dbvis.h.B.D.ᅣチ(Z:1548)
          at com.onseven.dbvis.h.B.F$A.call(Z:1369)
          at java.util.concurrent.FutureTask.run(FutureTask.java:266)
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
          at java.lang.Thread.run(Thread.java:745)
          Caused by: java.util.concurrent.TimeoutException: Could not connect to ZooKeeper localhost:9983 within 10000 ms
          at org.apache.solr.common.cloud.ConnectionManager.waitForConnected(ConnectionManager.java:209)
          at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:174)
          ... 17 more

          Show
          joel.bernstein Joel Bernstein added a comment - Here is the debug error: 2016-02-03 08:52:38.855 INFO 955 [ExecutorRunner-pool-3-thread-11 - G.ᅣᄋ] Exception while connecting Database Connection (1) (2) Database Properties **************************** Alias: Database Connection (1) (2) Settings Format: URL URL: jdbc:solr://localhost:9983?collection=collection2 Driver: solr Userid: Preferred Database Type: auto Current Database Type: generic Preferred Profile: auto Current Profile: null (not connected?) Object Type Settings --------------------------- None Overridden User Properties --------------------- generic.AutoCommit = false generic.ConnectionMode = Production Overridden Driver Properties ------------------- None End Database Properties ************************ Driver Information ***************************** Name: solr DefaultDriver: false DefaultClass: org.apache.solr.client.solrj.io.sql.DriverImpl Paths: Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solr-solrj-6.0.0-SNAPSHOT.jar Type: dynamic Class: org.apache.solr.client.solrj.io.sql.DriverImpl Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/commons-io-2.4.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/httpclient-4.4.1.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/httpcore-4.4.1.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/httpmime-4.4.1.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/jcl-over-slf4j-1.7.7.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/noggit-0.6.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/slf4j-api-1.7.7.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/stax2-api-3.1.4.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/woodstox-core-asl-4.4.1.jar Type: dynamic Path: /Users/joelbernstein/solr-git/lucene-solr/solr/dist/solrj-lib/zookeeper-3.4.6.jar Type: dynamic End Driver Information ************************* Current Connection Message ********************* Disconnected. End Current Connection Message ***************** 2016-02-03 08:52:38.857 INFO 955 [ExecutorRunner-pool-3-thread-11 - G.ᅣᄋ] Exception while connecting Database Connection (1) (2) org.apache.solr.common.SolrException: java.util.concurrent.TimeoutException: Could not connect to ZooKeeper localhost:9983 within 10000 ms at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:182) at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:116) at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:106) at org.apache.solr.common.cloud.ZkStateReader.<init>(ZkStateReader.java:202) at org.apache.solr.client.solrj.impl.CloudSolrClient.connect(CloudSolrClient.java:467) at org.apache.solr.client.solrj.io.SolrClientCache.getCloudSolrClient(SolrClientCache.java:49) at org.apache.solr.client.solrj.io.sql.ConnectionImpl.<init>(ConnectionImpl.java:56) at org.apache.solr.client.solrj.io.sql.DriverImpl.connect(DriverImpl.java:75) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at com.onseven.dbvis.h.B.D.ᅣチ(Z:1548) at com.onseven.dbvis.h.B.F$A.call(Z:1369) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: java.util.concurrent.TimeoutException: Could not connect to ZooKeeper localhost:9983 within 10000 ms at org.apache.solr.common.cloud.ConnectionManager.waitForConnected(ConnectionManager.java:209) at org.apache.solr.common.cloud.SolrZkClient.<init>(SolrZkClient.java:174) ... 17 more
          Hide
          joel.bernstein Joel Bernstein added a comment -

          I attached a couple of screen shots of the driver setup and connection window.

          Show
          joel.bernstein Joel Bernstein added a comment - I attached a couple of screen shots of the driver setup and connection window.
          Hide
          risdenk Kevin Risden added a comment -

          As a side note, in DBVisualizer in the top menu there is a Tools dropdown that has "Debug Window...". If you open the Debug Window and under the Debug Log tab you can check both Debug DbVisualizer and Debug JDBC Drivers. That window will show low level errors and exceptions. The only error you should get is UnsupportedOperationException when getTypeInfo is called if you have SOLR-8507 added.

          Show
          risdenk Kevin Risden added a comment - As a side note, in DBVisualizer in the top menu there is a Tools dropdown that has "Debug Window...". If you open the Debug Window and under the Debug Log tab you can check both Debug DbVisualizer and Debug JDBC Drivers. That window will show low level errors and exceptions. The only error you should get is UnsupportedOperationException when getTypeInfo is called if you have SOLR-8507 added.
          Hide
          risdenk Kevin Risden added a comment -

          What does your connection string look like? Did you add all the jars from solrj-lib and the solrj jar to the user classpath when creating the Solr driver in DBVisualizer?

          Show
          risdenk Kevin Risden added a comment - What does your connection string look like? Did you add all the jars from solrj-lib and the solrj jar to the user classpath when creating the Solr driver in DBVisualizer?
          Hide
          joel.bernstein Joel Bernstein added a comment - - edited

          Kevin Risden, working on connecting with DbVisualizer but am currently stuck with following error:

          java.util.concurrent.TimeoutException: Could not connect to ZooKeeper localhost:9983 within 10000 ms

          I can make a JDBC connection using a command line client to that address though.

          Did you run into an issue like this?

          Show
          joel.bernstein Joel Bernstein added a comment - - edited Kevin Risden , working on connecting with DbVisualizer but am currently stuck with following error: java.util.concurrent.TimeoutException: Could not connect to ZooKeeper localhost:9983 within 10000 ms I can make a JDBC connection using a command line client to that address though. Did you run into an issue like this?
          Hide
          joel.bernstein Joel Bernstein added a comment -

          The screen shot looks good!

          I'll play around some with DbVisualizer in the next couple of days and commit SOLR-8507.

          Show
          joel.bernstein Joel Bernstein added a comment - The screen shot looks good! I'll play around some with DbVisualizer in the next couple of days and commit SOLR-8507 .
          Hide
          risdenk Kevin Risden added a comment -

          Including: SOLR-8508, SOLR-8510, SOLR-8631, and SOLR-8507

          DBVisualizer with screenshot-4.png

          JiSQL

          java -cp /opt/jisql/lib/jisql-2.0.11.jar:/opt/jisql/lib/jopt-simple-3.2.jar:/opt/solr/dist/solrj-lib/*:/opt/solr/dist/solr-solrj-6.0.0-SNAPSHOT.jar com.xigole.util.sql.Jisql -user test -password test -driver org.apache.solr.client.solrj.io.sql.DriverImpl -cstring 'jdbc:solr://docker.vm:9983?collection=test&aggregationMode=map_reduce' -c \; -query 'select fielda, fieldb, fieldc, fieldd_s, fielde_i from test limit 10;'
          
          SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
          SLF4J: Defaulting to no-operation (NOP) logger implementation
          SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
          SLF4J: Failed to load class "org.slf4j.impl.StaticMDCBinder".
          SLF4J: Defaulting to no-operation MDCAdapter implementation.
          SLF4J: See http://www.slf4j.org/codes.html#no_static_mdc_binder for further details.
          fielda | fieldb | fieldc | fieldd_s | fielde_i |
          -------+--------+--------+----------+----------|
              a1 |     b1 |      1 |       d1 |        1 |
              a2 |     b2 |      2 |       d1 |        2 |
              a1 |     b3 |      3 |     NULL |        1 |
              a1 |     b4 |      4 |       d2 |     NULL |
              a2 |     b2 |   NULL |       d2 |        2 |
          
          Show
          risdenk Kevin Risden added a comment - Including: SOLR-8508 , SOLR-8510 , SOLR-8631 , and SOLR-8507 DBVisualizer with screenshot-4.png JiSQL java -cp /opt/jisql/lib/jisql-2.0.11.jar:/opt/jisql/lib/jopt-simple-3.2.jar:/opt/solr/dist/solrj-lib/*:/opt/solr/dist/solr-solrj-6.0.0-SNAPSHOT.jar com.xigole.util.sql.Jisql -user test -password test -driver org.apache.solr.client.solrj.io.sql.DriverImpl -cstring 'jdbc:solr: //docker.vm:9983?collection=test&aggregationMode=map_reduce' -c \; -query 'select fielda, fieldb, fieldc, fieldd_s, fielde_i from test limit 10;' SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder" . SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http: //www.slf4j.org/codes.html#StaticLoggerBinder for further details. SLF4J: Failed to load class "org.slf4j.impl.StaticMDCBinder" . SLF4J: Defaulting to no-operation MDCAdapter implementation. SLF4J: See http: //www.slf4j.org/codes.html#no_static_mdc_binder for further details. fielda | fieldb | fieldc | fieldd_s | fielde_i | -------+--------+--------+----------+----------| a1 | b1 | 1 | d1 | 1 | a2 | b2 | 2 | d1 | 2 | a1 | b3 | 3 | NULL | 1 | a1 | b4 | 4 | d2 | NULL | a2 | b2 | NULL | d2 | 2 |
          Hide
          joel.bernstein Joel Bernstein added a comment -

          I think we can close out SOLR-8502 before finishing SOLR-8521. I think we should add a short page of documentation specifically for the JDBC Driver and we can include a section about connecting with DBVisualizer.

          Show
          joel.bernstein Joel Bernstein added a comment - I think we can close out SOLR-8502 before finishing SOLR-8521 . I think we should add a short page of documentation specifically for the JDBC Driver and we can include a section about connecting with DBVisualizer.
          Hide
          risdenk Kevin Risden added a comment -

          I think the following four tickets should be addressed. I outlined the reasons below:

          • SOLR-8507 - This is more about user experience with DBVisualizer. After connecting, DBVisualizer displays this info to the user. There is an error/warning if this info is not available so hurts user experience.
          • SOLR-8508 / SOLR-8510 - Both of these also seem to cause issues with DBVisualizer user experience. I haven't narrowed down what parts these are absolutely required for, but it would be nice to have them implemented to avoid the warnings that DBvisualizer shows.
          • SOLR-8521 - Without documentation, using this would not be obvious. Maybe this ticket can be addressed after SOLR-8502 is closed?
          Show
          risdenk Kevin Risden added a comment - I think the following four tickets should be addressed. I outlined the reasons below: SOLR-8507 - This is more about user experience with DBVisualizer. After connecting, DBVisualizer displays this info to the user. There is an error/warning if this info is not available so hurts user experience. SOLR-8508 / SOLR-8510 - Both of these also seem to cause issues with DBVisualizer user experience. I haven't narrowed down what parts these are absolutely required for, but it would be nice to have them implemented to avoid the warnings that DBvisualizer shows. SOLR-8521 - Without documentation, using this would not be obvious. Maybe this ticket can be addressed after SOLR-8502 is closed?
          Hide
          joel.bernstein Joel Bernstein added a comment - - edited

          Kevin Risden, do we have enough working in DBVisualizer with the latest commit to declare victory on this ticket. If we do, we can close it out and add it to the CHANGES.txt for 6.0. If we can connect, run an aggregation query and chart the result I think that's a great start.

          The remaining issues listed on this ticket we can tackle individually.

          Or do you feel like we need to tackle the calalog and schema issues before closing this out?

          Show
          joel.bernstein Joel Bernstein added a comment - - edited Kevin Risden , do we have enough working in DBVisualizer with the latest commit to declare victory on this ticket. If we do, we can close it out and add it to the CHANGES.txt for 6.0. If we can connect, run an aggregation query and chart the result I think that's a great start. The remaining issues listed on this ticket we can tackle individually. Or do you feel like we need to tackle the calalog and schema issues before closing this out?
          Hide
          risdenk Kevin Risden added a comment -

          Including SOLR-8520, SOLR-8574, SOLR-8603, and SOLR-8602:

          DBVisualizer with screenshot-3.png

          JiSQL

          java -cp /opt/jisql/lib/jisql-2.0.11.jar:/opt/jisql/lib/jopt-simple-3.2.jar:/opt/solr/dist/solrj-lib/*:/opt/solr/dist/solr-solrj-6.0.0-SNAPSHOT.jar com.xigole.util.sql.Jisql -user test -password test -driver org.apache.solr.client.solrj.io.sql.DriverImpl -cstring 'jdbc:solr://192.168.1.237:9983?collection=test&aggregationMode=map_reduce' -c \; -query 'select fielda, fieldb, fieldc, fieldd_s, fielde_i from test limit 10;'
          
          SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
          SLF4J: Defaulting to no-operation (NOP) logger implementation
          SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
          SLF4J: Failed to load class "org.slf4j.impl.StaticMDCBinder".
          SLF4J: Defaulting to no-operation MDCAdapter implementation.
          SLF4J: See http://www.slf4j.org/codes.html#no_static_mdc_binder for further details.
          fielda | fieldb | fieldc | fieldd_s | fielde_i |
          -------+--------+--------+----------+----------|
              a1 |     b1 |      1 |       d1 |        1 |
              a2 |     b2 |      2 |       d1 |        2 |
              a1 |     b3 |      3 |     NULL |        1 |
              a1 |     b4 |      4 |       d2 |     NULL |
              a2 |     b2 |   NULL |       d2 |        2 |
          
          Show
          risdenk Kevin Risden added a comment - Including SOLR-8520 , SOLR-8574 , SOLR-8603 , and SOLR-8602 : DBVisualizer with screenshot-3.png JiSQL java -cp /opt/jisql/lib/jisql-2.0.11.jar:/opt/jisql/lib/jopt-simple-3.2.jar:/opt/solr/dist/solrj-lib/*:/opt/solr/dist/solr-solrj-6.0.0-SNAPSHOT.jar com.xigole.util.sql.Jisql -user test -password test -driver org.apache.solr.client.solrj.io.sql.DriverImpl -cstring 'jdbc:solr: //192.168.1.237:9983?collection=test&aggregationMode=map_reduce' -c \; -query 'select fielda, fieldb, fieldc, fieldd_s, fielde_i from test limit 10;' SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder" . SLF4J: Defaulting to no-operation (NOP) logger implementation SLF4J: See http: //www.slf4j.org/codes.html#StaticLoggerBinder for further details. SLF4J: Failed to load class "org.slf4j.impl.StaticMDCBinder" . SLF4J: Defaulting to no-operation MDCAdapter implementation. SLF4J: See http: //www.slf4j.org/codes.html#no_static_mdc_binder for further details. fielda | fieldb | fieldc | fieldd_s | fielde_i | -------+--------+--------+----------+----------| a1 | b1 | 1 | d1 | 1 | a2 | b2 | 2 | d1 | 2 | a1 | b3 | 3 | NULL | 1 | a1 | b4 | 4 | d2 | NULL | a2 | b2 | NULL | d2 | 2 |
          Hide
          joel.bernstein Joel Bernstein added a comment -

          Ok, I'll work on these this afternoon.

          Show
          joel.bernstein Joel Bernstein added a comment - Ok, I'll work on these this afternoon.
          Hide
          risdenk Kevin Risden added a comment -

          This screenshot should be possible with the trunk build https://builds.apache.org/job/Solr-Artifacts-trunk/2778/

          Show
          risdenk Kevin Risden added a comment - This screenshot should be possible with the trunk build https://builds.apache.org/job/Solr-Artifacts-trunk/2778/
          Hide
          risdenk Kevin Risden added a comment -
          Show
          risdenk Kevin Risden added a comment - SOLR-8602 SOLR-8603
          Hide
          risdenk Kevin Risden added a comment -

          Joel Bernstein - Here are two more tickets ready for review:

          Show
          risdenk Kevin Risden added a comment - Joel Bernstein - Here are two more tickets ready for review: SOLR-8520 SOLR-8574
          Hide
          risdenk Kevin Risden added a comment -

          attached screenshot2.png to show DBVisualizer with printing proper types after SOLR-8518 was merged.

          Show
          risdenk Kevin Risden added a comment - attached screenshot2.png to show DBVisualizer with printing proper types after SOLR-8518 was merged.
          Hide
          joel.bernstein Joel Bernstein added a comment -

          This looks great!

          Show
          joel.bernstein Joel Bernstein added a comment - This looks great!
          Hide
          risdenk Kevin Risden added a comment - - edited

          Added screenshot-1.png to show that rough DBVisualizer is working. The following items are still "issues":

          • The columns are always returned as strings with getString since SOLR-8518 is not merged
          • ConnectionImpl.isValid() throws a warning that it is not implemented but assumes the connection is valid. SOLR-8574
          • Column names and width are guessed by dbvisualizer but can be improved with SOLR-8520
          • DBVisualizer cannot get info about the driver since some methods about versions/names aren't implemented. SOLR-8507
          • Catalog/schema info is not implemented so there is an error when trying to populate that info with DBVisualizer SOLR-8508 and SOLR-8510
          Show
          risdenk Kevin Risden added a comment - - edited Added screenshot-1.png to show that rough DBVisualizer is working. The following items are still "issues": The columns are always returned as strings with getString since SOLR-8518 is not merged ConnectionImpl.isValid() throws a warning that it is not implemented but assumes the connection is valid. SOLR-8574 Column names and width are guessed by dbvisualizer but can be improved with SOLR-8520 DBVisualizer cannot get info about the driver since some methods about versions/names aren't implemented. SOLR-8507 Catalog/schema info is not implemented so there is an error when trying to populate that info with DBVisualizer SOLR-8508 and SOLR-8510
          Hide
          risdenk Kevin Risden added a comment -

          SOLR-8518 has a patch available as well once SOLR-8512 is done.

          Show
          risdenk Kevin Risden added a comment - SOLR-8518 has a patch available as well once SOLR-8512 is done.
          Hide
          risdenk Kevin Risden added a comment -

          Joel Bernstein Here are some more tickets that are ready for review:

          They have to be merged in that order based on dependencies. There might be slight conflicts between them, but easily addressed.

          Show
          risdenk Kevin Risden added a comment - Joel Bernstein Here are some more tickets that are ready for review: SOLR-8519 SOLR-8517 SOLR-8512 They have to be merged in that order based on dependencies. There might be slight conflicts between them, but easily addressed.
          Hide
          joel.bernstein Joel Bernstein added a comment -

          Looks like you've got a number of tickets ready to go. Can you make another list of jiras. I should be able to review and comment over the weekend. Because of the git migration we'll have to start the commits next week. I'll make a push next week to try and get as much in before the 6.0 release.

          Show
          joel.bernstein Joel Bernstein added a comment - Looks like you've got a number of tickets ready to go. Can you make another list of jiras. I should be able to review and comment over the weekend. Because of the git migration we'll have to start the commits next week. I'll make a push next week to try and get as much in before the 6.0 release.
          Hide
          risdenk Kevin Risden added a comment -

          Joel Bernstein - Can you take a look at SOLR-8573 and SOLR-8519?

          Show
          risdenk Kevin Risden added a comment - Joel Bernstein - Can you take a look at SOLR-8573 and SOLR-8519 ?
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1725662 from Joel Bernstein in branch 'dev/trunk'
          [ https://svn.apache.org/r1725662 ]

          SOLR-8514, SOLR-8502: Implement StatementImpl.execute(String sql), StatementImpl.getResultSet(), and StatementImpl.getUpdateCount()

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1725662 from Joel Bernstein in branch 'dev/trunk' [ https://svn.apache.org/r1725662 ] SOLR-8514 , SOLR-8502 : Implement StatementImpl.execute(String sql), StatementImpl.getResultSet(), and StatementImpl.getUpdateCount()
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1725616 from Joel Bernstein in branch 'dev/trunk'
          [ https://svn.apache.org/r1725616 ]

          SOLR-8516, SOLR-8502: Implement ResultSetImpl.getStatement

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1725616 from Joel Bernstein in branch 'dev/trunk' [ https://svn.apache.org/r1725616 ] SOLR-8516 , SOLR-8502 : Implement ResultSetImpl.getStatement
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1725595 from Joel Bernstein in branch 'dev/trunk'
          [ https://svn.apache.org/r1725595 ]

          SOLR-8515, SOLR-8502: Implement StatementImpl.getConnection

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1725595 from Joel Bernstein in branch 'dev/trunk' [ https://svn.apache.org/r1725595 ] SOLR-8515 , SOLR-8502 : Implement StatementImpl.getConnection
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1724870 from Joel Bernstein in branch 'dev/trunk'
          [ https://svn.apache.org/r1724870 ]

          SOLR-8513,SOLR-8502: Implement ResultSetImpl.getMetaData()

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1724870 from Joel Bernstein in branch 'dev/trunk' [ https://svn.apache.org/r1724870 ] SOLR-8513 , SOLR-8502 : Implement ResultSetImpl.getMetaData()
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit 1724867 from Joel Bernstein in branch 'dev/trunk'
          [ https://svn.apache.org/r1724867 ]

          SOLR-8502: Implement DatabaseMetaDataImpl.getURL()

          Show
          jira-bot ASF subversion and git services added a comment - Commit 1724867 from Joel Bernstein in branch 'dev/trunk' [ https://svn.apache.org/r1724867 ] SOLR-8502 : Implement DatabaseMetaDataImpl.getURL()
          Hide
          risdenk Kevin Risden added a comment - - edited

          Here are some of the JIRAs are that are ready for review.

          Show
          risdenk Kevin Risden added a comment - - edited Here are some of the JIRAs are that are ready for review. SOLR-8503 SOLR-8507 SOLR-8509 SOLR-8511 SOLR-8513 SOLR-8514 SOLR-8515 SOLR-8516
          Hide
          joel.bernstein Joel Bernstein added a comment -

          It looks like the jira filter is private. You can also just list the jira's that are ready for review.

          This is a high priority for Solr 6. So I'll definitely work with you to get the code reviewed and ready to be committed.

          Show
          joel.bernstein Joel Bernstein added a comment - It looks like the jira filter is private. You can also just list the jira's that are ready for review. This is a high priority for Solr 6. So I'll definitely work with you to get the code reviewed and ready to be committed.
          Show
          risdenk Kevin Risden added a comment - The following filter can be used to look at the tickets that have patches, but are not committed/closed yet. https://issues.apache.org/jira/issues/?filter=12334493&jql=project%20%3D%20SOLR%20AND%20parent%20%3D%20SOLR-8502%20AND%20Flags%20%3D%20patch%20AND%20status%20not%20in%20(Fixed%2C%20Closed%2C%20Done%2C%20Invalid)%20and%20attachments%20is%20not%20EMPTY%20order%20by%20created%20ASC Joel Bernstein - Can you take a look at these when you get a chance?
          Hide
          risdenk Kevin Risden added a comment -

          Laid out an initial set of subtasks and an approximate order. Most of these are already done in https://github.com/risdenk/lucene-solr/tree/expand-jdbc so just need to spend the time breaking it out and adding tests.

          SOLR-8503 must be committed first since it adds a class DatabaseMetaDataImpl.

          Show
          risdenk Kevin Risden added a comment - Laid out an initial set of subtasks and an approximate order. Most of these are already done in https://github.com/risdenk/lucene-solr/tree/expand-jdbc so just need to spend the time breaking it out and adding tests. SOLR-8503 must be committed first since it adds a class DatabaseMetaDataImpl.
          Hide
          joel.bernstein Joel Bernstein added a comment -

          +1 this is an exciting ticket!

          Show
          joel.bernstein Joel Bernstein added a comment - +1 this is an exciting ticket!

            People

            • Assignee:
              Unassigned
              Reporter:
              risdenk Kevin Risden
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development