Repository: crate/crate-python

Issue:
SQLAlchemy/DDL: Support `server_default` column definition option
### Introduction
CrateDB can use functions to generate server-side default values on columns. For example, the `NOW()` function can be used to auto-generate timestamp values.
```sql
crash <<EOL
    DROP TABLE IF EXISTS testdrive;
    CREATE TABLE IF NOT EXISTS "doc"."testdrive" (
      "timestamp" TIMESTAMP DEFAULT NOW() NOT NULL,
      "value" FLOAT
    );
    INSERT INTO testdrive (value) VALUES (42.42);
    INSERT INTO testdrive (value) VALUES (42.42);
    REFRESH TABLE testdrive;
    SELECT * FROM testdrive;
EOL
```

### Objective

It would be sweet if we could make the CrateDB SQLAlchemy dialect support this feature appropriately.


### Proposal

I think the right way is to implement the official SQLAlchemy column definition option `server_default`.
```python
>>> import sqlalchemy as sa
>>> sa.Column("timestamp", sa.DateTime, server_default=sa.text("NOW()"))
```
-- https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column.params.server_default

### Example
The [collab/sa-column-server-default](https://github.com/crate/crate-python/compare/collab/sa-column-server-default) branch has a corresponding example program.


Discussion:
JanLikar: > Other than a suggestion about reStructuredText syntax, and maybe a slight naming change on the test methods, I think it will be good to go.
I applied the fixes and squashed the commits. To be honest, I've been using Markdown so much lately I didn't even realize this is rst, thanks for spotting the issue.

Patch:
diff --git a/CHANGES.txt b/CHANGES.txt
index 0a7196f..f8d0f54 100644
--- a/CHANGES.txt
+++ b/CHANGES.txt
@@ -16,6 +16,7 @@ Unreleased
   SQLAlchemy 2.0 by adding the new ``insert_returning`` and ``update_returning`` flags
   in the CrateDB dialect.
 
+- SQLAlchemy DDL: Allow setting ``server_default`` on columns to enable server-generated defaults.
 
 2023/03/30 0.31.0
 =================
diff --git a/docs/sqlalchemy.rst b/docs/sqlalchemy.rst
index b16f2ab..2c1a747 100644
--- a/docs/sqlalchemy.rst
+++ b/docs/sqlalchemy.rst
@@ -206,6 +206,7 @@ system <sa:orm_declarative_mapping>`:
     ...     name_ft = sa.Column(sa.String)
     ...     quote_ft = sa.Column(sa.String)
     ...     even_more_details = sa.Column(sa.String, crate_columnstore=False)
+    ...     created_at = sa.Column(sa.DateTime, server_default=sa.func.now())
     ...
     ...     __mapper_args__ = {
     ...         'exclude_properties': ['name_ft', 'quote_ft']
@@ -221,13 +222,14 @@ In this example, we:
 - Use standard SQLAlchemy types for the ``id``, ``name``, and ``quote`` columns
 - Use ``nullable=False`` to define a ``NOT NULL`` constraint
 - Disable indexing of the ``name`` column using ``crate_index=False``
-- Disable the columnstore of the ``even_more_details`` column using ``crate_columnstore=False``
 - Define a computed column ``name_normalized`` (based on ``name``) that
   translates into a generated column
 - Use the `Object`_ extension type for the ``details`` column
 - Use the `ObjectArray`_ extension type for the ``more_details`` column
 - Set up the ``name_ft`` and ``quote_ft`` fulltext indexes, but exclude them from
   the mapping (so SQLAlchemy doesn't try to update them as if they were columns)
+- Disable the columnstore of the ``even_more_details`` column using ``crate_columnstore=False``
+- Add a ``created_at`` column whose default value is set by CrateDB's ``now()`` function.
 
 .. TIP::
 
diff --git a/src/crate/client/sqlalchemy/compiler.py b/src/crate/client/sqlalchemy/compiler.py
index efa88d1..3ac6fa5 100644
--- a/src/crate/client/sqlalchemy/compiler.py
+++ b/src/crate/client/sqlalchemy/compiler.py
@@ -108,7 +108,10 @@ class CrateDDLCompiler(compiler.DDLCompiler):
     def get_column_specification(self, column, **kwargs):
         colspec = self.preparer.format_column(column) + " " + \
             self.dialect.type_compiler.process(column.type)
-        # TODO: once supported add default here
+
+        default = self.get_column_default_string(column)
+        if default is not None:
+            colspec += " DEFAULT " + default
 
         if column.computed is not None:
             colspec += " " + self.process(column.computed)


Test patch:
diff --git a/src/crate/client/sqlalchemy/tests/create_table_test.py b/src/crate/client/sqlalchemy/tests/create_table_test.py
index f876655..b7fb9b8 100644
--- a/src/crate/client/sqlalchemy/tests/create_table_test.py
+++ b/src/crate/client/sqlalchemy/tests/create_table_test.py
@@ -259,3 +259,55 @@ class SqlAlchemyCreateTableTest(TestCase):
 
         with self.assertRaises(sa.exc.CompileError):
             self.Base.metadata.create_all(bind=self.engine)
+
+    def test_column_server_default_text_func(self):
+        class DummyTable(self.Base):
+            __tablename__ = 't'
+            pk = sa.Column(sa.String, primary_key=True)
+            a = sa.Column(sa.DateTime, server_default=sa.text("now()"))
+
+        self.Base.metadata.create_all(bind=self.engine)
+        fake_cursor.execute.assert_called_with(
+            ('\nCREATE TABLE t (\n\t'
+             'pk STRING NOT NULL, \n\t'
+             'a TIMESTAMP DEFAULT now(), \n\t'
+             'PRIMARY KEY (pk)\n)\n\n'), ())
+
+    def test_column_server_default_string(self):
+        class DummyTable(self.Base):
+            __tablename__ = 't'
+            pk = sa.Column(sa.String, primary_key=True)
+            a = sa.Column(sa.String, server_default="Zaphod")
+
+        self.Base.metadata.create_all(bind=self.engine)
+        fake_cursor.execute.assert_called_with(
+            ('\nCREATE TABLE t (\n\t'
+             'pk STRING NOT NULL, \n\t'
+             'a STRING DEFAULT \'Zaphod\', \n\t'
+             'PRIMARY KEY (pk)\n)\n\n'), ())
+
+    def test_column_server_default_func(self):
+        class DummyTable(self.Base):
+            __tablename__ = 't'
+            pk = sa.Column(sa.String, primary_key=True)
+            a = sa.Column(sa.DateTime, server_default=sa.func.now())
+
+        self.Base.metadata.create_all(bind=self.engine)
+        fake_cursor.execute.assert_called_with(
+            ('\nCREATE TABLE t (\n\t'
+             'pk STRING NOT NULL, \n\t'
+             'a TIMESTAMP DEFAULT now(), \n\t'
+             'PRIMARY KEY (pk)\n)\n\n'), ())
+
+    def test_column_server_default_text_constant(self):
+        class DummyTable(self.Base):
+            __tablename__ = 't'
+            pk = sa.Column(sa.String, primary_key=True)
+            answer = sa.Column(sa.Integer, server_default=sa.text("42"))
+
+        self.Base.metadata.create_all(bind=self.engine)
+        fake_cursor.execute.assert_called_with(
+            ('\nCREATE TABLE t (\n\t'
+             'pk STRING NOT NULL, \n\t'
+             'answer INT DEFAULT 42, \n\t'
+             'PRIMARY KEY (pk)\n)\n\n'), ())