Repository: deepset-ai/haystack
Pull request: Add support for MySQL database
This PR adds makes the `SQLDocumentStore` more generic to work with MySQL database.

Resolves #543.

Problem statement:
FAISSDocumentStore + mysql 5.7
Hi guys,

Hope you are all well !

I tried to setup a mysql connector to the FAISSDocumentStore but it triggers the following error:

```sh
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 2904, in visit_create_table
    create_column, first_pk=column.primary_key and not first_pk
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 352, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 2937, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/dialects/mysql/base.py", line 1716, in get_column_specification
    column.type, type_expression=column
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 402, in process
    return type_._compiler_dispatch(self, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 3425, in visit_string
    return self.visit_VARCHAR(type_, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/dialects/mysql/base.py", line 2203, in visit_VARCHAR
    "VARCHAR requires a length on dialect %s" % self.dialect.name
sqlalchemy.exc.CompileError: VARCHAR requires a length on dialect mysql

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "train-faiss.py", line 185, in <module>
    service()
  File "/usr/local/lib/python3.7/dist-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.7/dist-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.7/dist-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "train-faiss.py", line 182, in service
    train_model(data_dir, host, port, index_name, index_reset, username, password)
  File "train-faiss.py", line 91, in train_model
    document_store = FAISSDocumentStore(sql_url=sql_url)
  File "/usr/local/lib/python3.7/dist-packages/haystack/document_store/faiss.py", line 76, in __init__
    super().__init__(url=sql_url)
  File "/usr/local/lib/python3.7/dist-packages/haystack/document_store/sql.py", line 62, in __init__
    ORMBase.metadata.create_all(engine)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/schema.py", line 4554, in create_all
    ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", line 2095, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", line 1656, in _run_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 787, in visit_metadata
    _is_metadata_operation=True,
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/visitors.py", line 145, in traverse_single
    return meth(obj, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 832, in visit_table
    include_foreign_key_constraints,  # noqa
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", line 1011, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/engine/base.py", line 1066, in _execute_ddl
    else None,
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/elements.py", line 481, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 322, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 352, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/visitors.py", line 96, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/sql/compiler.py", line 2918, in visit_create_table
    from_=ce,
  File "/usr/local/lib/python3.7/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
sqlalchemy.exc.CompileError: (in table 'document', column 'id'): VARCHAR requires a length on dialect mysql
```

Is it possible to sort it out ?

Cheers,
X

Hints from issue discussion:

Commit message: Revert change to UUID type for IDs

Patch to review:
diff --git a/haystack/document_store/sql.py b/haystack/document_store/sql.py
index a2746f8747..931d2b974a 100644
--- a/haystack/document_store/sql.py
+++ b/haystack/document_store/sql.py
@@ -1,7 +1,7 @@
 from typing import Any, Dict, Union, List, Optional
 from uuid import uuid4
 
-from sqlalchemy import create_engine, Column, Integer, String, DateTime, func, ForeignKey, Boolean
+from sqlalchemy import create_engine, Column, Integer, String, DateTime, func, ForeignKey, Boolean, Text
 from sqlalchemy.ext.declarative import declarative_base
 from sqlalchemy.orm import relationship, sessionmaker
 from sqlalchemy.sql import case
@@ -16,7 +16,7 @@
 class ORMBase(Base):
     __abstract__ = True
 
-    id = Column(String, default=lambda: str(uuid4()), primary_key=True)
+    id = Column(String(100), default=lambda: str(uuid4()), primary_key=True)
     created = Column(DateTime, server_default=func.now())
     updated = Column(DateTime, server_default=func.now(), server_onupdate=func.now())
 
@@ -24,19 +24,20 @@ class ORMBase(Base):
 class DocumentORM(ORMBase):
     __tablename__ = "document"
 
-    text = Column(String, nullable=False)
-    index = Column(String, nullable=False)
-    vector_id = Column(String, unique=True, nullable=True)
+    text = Column(Text, nullable=False)
+    index = Column(String(100), nullable=False)
+    vector_id = Column(String(100), unique=True, nullable=True)
 
     # speeds up queries for get_documents_by_vector_ids() by having a single query that returns joined metadata
     meta = relationship("MetaORM", backref="Document", lazy="joined")
 
+
 class MetaORM(ORMBase):
     __tablename__ = "meta"
 
-    name = Column(String, index=True)
-    value = Column(String, index=True)
-    document_id = Column(String, ForeignKey("document.id", ondelete="CASCADE"), nullable=False)
+    name = Column(String(100), index=True)
+    value = Column(String(100), index=True)
+    document_id = Column(String(100), ForeignKey("document.id", ondelete="CASCADE"), nullable=False)
 
     documents = relationship(DocumentORM, backref="Meta")
 
@@ -44,14 +45,14 @@ class MetaORM(ORMBase):
 class LabelORM(ORMBase):
     __tablename__ = "label"
 
-    document_id = Column(String, ForeignKey("document.id", ondelete="CASCADE"), nullable=False)
-    index = Column(String, nullable=False)
+    document_id = Column(String(100), ForeignKey("document.id", ondelete="CASCADE"), nullable=False)
+    index = Column(String(100), nullable=False)
     no_answer = Column(Boolean, nullable=False)
-    origin = Column(String, nullable=False)
-    question = Column(String, nullable=False)
+    origin = Column(String(100), nullable=False)
+    question = Column(Text, nullable=False)
     is_correct_answer = Column(Boolean, nullable=False)
     is_correct_document = Column(Boolean, nullable=False)
-    answer = Column(String, nullable=False)
+    answer = Column(Text, nullable=False)
     offset_start_in_doc = Column(Integer, nullable=False)
     model_id = Column(Integer, nullable=True)
 


Code review comments:
diff_hunk: @@ -16,42 +16,43 @@
 class ORMBase(Base):
     __abstract__ = True
 
-    id = Column(String, default=lambda: str(uuid4()), primary_key=True)
+    id = Column(String(100), default=lambda: str(uuid4()), primary_key=True)
     created = Column(DateTime, server_default=func.now())
     updated = Column(DateTime, server_default=func.now(), server_onupdate=func.now())
 
 
 class DocumentORM(ORMBase):
     __tablename__ = "document"
 
-    text = Column(String, nullable=False)
-    index = Column(String, nullable=False)
-    vector_id = Column(String, unique=True, nullable=True)
+    text = Column(Text, nullable=False)
+    index = Column(String(100), nullable=False)
+    vector_id = Column(String(100), unique=True, nullable=True)
 
     # speeds up queries for get_documents_by_vector_ids() by having a single query that returns joined metadata
     meta = relationship("MetaORM", backref="Document", lazy="joined")
 
+
 class MetaORM(ORMBase):
     __tablename__ = "meta"
 
-    name = Column(String, index=True)
-    value = Column(String, index=True)
-    document_id = Column(String, ForeignKey("document.id", ondelete="CASCADE"), nullable=False)
+    name = Column(String(100), index=True)
+    value = Column(String(100), index=True)
path: haystack/document_store/sql.py
text: @user1:
Title of the doc is stored as value of `meta.name` key and there is possibility that it can breach 100 char limit. And if `Text` is used then need to consider how Text filed is indexed. I feel high value for this would be good ie 1024 or 512.

@author:
Well spotted @user1! I switched the `value` field to `Text`.