github.com/dolthub/go-mysql-server@v0.18.0/BACKEND.md (about)

     1  # Custom backend integration guide
     2  
     3  This is the guide for creating a new backend to query with
     4  **go-mysql-server**.
     5  
     6  ## Core interfaces
     7  
     8  To create your own data source implementation you need to implement
     9  the following interfaces:
    10  
    11  - `sql.DatabaseProvider`. This interface allows the engine to find
    12    available databases. You can also unlock addtional functionality by
    13    optionally implementing additional interfaces:
    14    - `sql.MutableDatabaseProvider` to support creating and dropping
    15      databases.
    16    - `sql.CollatedDatabaseProvider` to support database-level
    17      collations.
    18  - `sql.Database`. These are returned by your
    19    `sql.DatabaseProvider`. The main job of `sql.Database` is to provide
    20    tables from your data source. You can also implement other
    21    interfaces on your database to unlock additional functionality:
    22    - `sql.TableCreator` to support creating new tables
    23    - `sql.TableDropper` to support dropping  tables
    24    - `sql.TableRenamer` to support renaming tables
    25    - `sql.ViewCreator` to support creating persisted views on your tables
    26    - `sql.ViewDropper` to support dropping persisted views
    27  - `sql.Table`. This interface will provide rows of values from your
    28    data source. You can also implement other interfaces on your table
    29    to unlock additional functionality:
    30    - `sql.InsertableTable` to allow your data source to be updated with
    31      `INSERT` statements.
    32    - `sql.UpdateableTable` to allow your data source to be updated with
    33      `UPDATE` statements.
    34    - `sql.DeletableTable` to allow your data source to be updated with
    35      `DELETE` statements.
    36    - `sql.ReplaceableTable` to allow your data source to be updated with
    37      `REPLACE` statements.
    38    - `sql.AlterableTable` to allow your data source to have its schema
    39      modified by adding, dropping, and altering columns.
    40    - `sql.IndexedTable` to declare your table's native indexes to speed
    41      up query execution.
    42    - `sql.IndexAlterableTable` to accept the creation of new native
    43      indexes.
    44    - `sql.ForeignKeyAlterableTable` to signal your support of foreign
    45      key constraints in your table's schema and data.
    46    - `sql.ProjectedTable` to return rows that only contain a subset of
    47      the columns in the table. This can make query execution faster.
    48    - `sql.FilteredTable` to filter the rows returned by your table to
    49      those matching a given expression. This can make query execution
    50      faster (if your table implementation can filter rows more
    51      efficiently than checking an expression on every row in a table).
    52      
    53  This is not a complete list, but should be enough to get you started
    54  on a full backend implementation. For an example of implementing these
    55  interfaces, see the `memory` package.
    56  
    57  ## Sessions and transactions
    58  
    59  Many backend implementations will be able to re-use the
    60  `sql.BaseSession` object for sessioned access to databases. This
    61  should be the case for all read-only database implementations.
    62  However, some backends may need to store session information
    63  particular to that backend, e.g. open data files that have yet to be
    64  written. Such integrators should implement their own `sql.Session`
    65  implementation, and probably should embed `sql.BaseSession` in it to
    66  make that easier.
    67  
    68  Backends that want transactional semantics for their queries must also
    69  implement `sql.TransactionSession` in their session object and provide
    70  a corresponding `sql.Transaction` implementation. The details of doing
    71  so are necessarily very specific to a particular backend and are
    72  beyond the scope of this guide.
    73  
    74  ## Native indexes
    75  
    76  Tables can declare that they support native indexes. The `memory`
    77  package contains an example of this behavior, but please note that it
    78  is only for example purposes and doesn't actually make queries faster
    79  (although we could change this in the future).
    80  
    81  Integrators should implement the `sql.IndexedTable` interface to
    82  declare which indexes their tables support and provide a means of
    83  returning a subset of the rows. The job of your `sql.Index`
    84  implementation is to accept or reject combinations of `sql.Range`
    85  expressions that it can support, which will be used by the engine to
    86  construct a `sql.IndexLookup` struct to provide to your
    87  `sql.IndexedTable` implementation.
    88  
    89  ## Custom index driver implementation
    90  
    91  Index drivers are separate backends for storing and querying indexes,
    92  without the need for a table to store and query its own native
    93  indexes. To implement a custom index driver you need to implement a
    94  few things:
    95  
    96  - `sql.IndexDriver` interface, which will be the driver itself. Not
    97    that your driver must return an unique ID in the `ID` method. This
    98    ID is unique for your driver and should not clash with any other
    99    registered driver. It's the driver's responsibility to be fault
   100    tolerant and be able to automatically detect and recover from
   101    corruption in indexes.
   102  - `sql.Index` interface, returned by your driver when an index is
   103    loaded or created.
   104  - `sql.IndexValueIter` interface, which will be returned by your
   105    `sql.IndexLookup` and should return the values of the index.
   106  - Don't forget to register the index driver in your `sql.Context`
   107    using `context.RegisterIndexDriver(mydriver)` to be able to use it.
   108  
   109  To create indexes using your custom index driver you need to use
   110  extension syntax `USING driverid` on the index creation statement. For
   111  example:
   112  
   113  ```sql
   114  CREATE INDEX foo ON table USING driverid (col1, col2)
   115  ```
   116  
   117  **go-mysql-server** does not provide a production index driver
   118  implementation. We previously provided a pilosa implementation, but
   119  removed it due to the difficulty of supporting it on all platforms
   120  (pilosa doesn't work on Windows).
   121  
   122  You can see an example of a driver implementation in the memory
   123  package.
   124  
   125  ## Testing your backend implementation
   126  
   127  **go-mysql-server** provides a suite of engine tests that you can use
   128  to validate that your implementation works as expected. See the
   129  `enginetest` package for details and examples.
   130  
   131  It's also possible and encouraged to write engine tests that are
   132  specific to your backend. This is especially important when
   133  implementing transactions, which the in-memory backend doesn't
   134  support.
   135