Add models and outline for a SQLAlchemy-based backend

4 files changed, 138 insertions, 3 deletions

diff --git a/.pre-commit-config.yaml b/.pre-commit-config.yaml
index cab8478..44da8c7 100644
--- a/.pre-commit-config.yaml
+++ b/.pre-commit-config.yaml
@@ -26,7 +26,10 @@ repos:
     hooks:
       - id: mypy
         files: requests_cache
-        additional_dependencies: [attrs, types-itsdangerous, types-requests, types-pyyaml, types-redis, types-ujson, types-urllib3]
+        additional_dependencies: [
+          attrs, sqlalchemy, types-itsdangerous, types-requests,
+          types-pyyaml, types-redis, types-ujson, types-urllib3
+        ]
   - repo: https://github.com/yunojuno/pre-commit-xenon
     rev: v0.1
     hooks:
diff --git a/pyproject.toml b/pyproject.toml
index ed0b4de..01dc4af 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -44,6 +44,8 @@ boto3                      = {optional=true, version=">=1.15"}
 botocore                   = {optional=true, version=">=1.18"}
 pymongo                    = {optional=true, version=">=3"}
 redis                      = {optional=true, version=">=3"}
+sqlalchemy                 = {optional=true, version=">=1.4"}
+psycopg2-binary            = {optional=true, version=">=2.8"}
 
 # Optional serialization dependencies
 bson                       = {optional=true, version=">=0.5"}
@@ -67,6 +69,7 @@ sphinxext-opengraph        = {optional=true, version=">=0.6"}
 
 [tool.poetry.extras]
 # Package extras for optional backend dependencies
+db       = ["sqlalchemy"]
 dynamodb = ["boto3", "botocore"]
 mongodb  = ["pymongo"]
 redis    = ["redis"]
@@ -77,8 +80,9 @@ json     = ["ujson"]  # Will optionally be used by JSON serializer for improved
 security = ["itsdangerous"]
 yaml     = ["pyyaml"]
 
-# All optional packages combined, for demo/evaluation purposes
-all      = ["boto3", "botocore", "itsdangerous", "pymongo", "pyyaml", "redis", "ujson"]
+# All optional packages combined, for testing/evaluation purposes
+all      = ["boto3", "botocore", "itsdangerous", "psycopg2-binary", "pymongo", "pyyaml", "redis",
+            "sqlalchemy", "ujson"]
 
 # Documentation
 docs     = ["furo", "linkify-it-py", "myst-parser", "sphinx", "sphinx-autodoc-typehints",
diff --git a/requests_cache/backends/__init__.py b/requests_cache/backends/__init__.py
index d916ab8..6ea9c08 100644
--- a/requests_cache/backends/__init__.py
+++ b/requests_cache/backends/__init__.py
@@ -15,6 +15,11 @@ logger = getLogger(__name__)
 
 # Import all backend classes for which dependencies are installed
 try:
+    from .db import DBCache, DBStorage
+except ImportError as e:
+    DBCache = DBStorage = get_placeholder_class(e)  # type: ignore
+
+try:
     from .dynamodb import DynamoDbCache, DynamoDbDict
 except ImportError as e:
     DynamoDbCache = DynamoDbDict = get_placeholder_class(e)  # type: ignore
@@ -46,6 +51,7 @@ except ImportError as e:
 
 
 BACKEND_CLASSES = {
+    'db': DBCache,
     'dynamodb': DynamoDbCache,
     'filesystem': FileCache,
     'gridfs': GridFSCache,
diff --git a/requests_cache/backends/db.py b/requests_cache/backends/db.py
new file mode 100644
index 0000000..a6d7ef4
--- /dev/null
+++ b/requests_cache/backends/db.py
@@ -0,0 +1,122 @@
+"""Generic relational database backend that works with any dialect supported by SQLAlchemy"""
+import json
+
+from sqlalchemy import Column, DateTime, Integer, LargeBinary, String
+from sqlalchemy.engine import Engine
+from sqlalchemy.orm import declarative_base
+
+from ..models import CachedRequest, CachedResponse
+from . import BaseCache, BaseStorage
+
+Base = declarative_base()
+
+
+# TODO: Benchmark read & write with ORM model vs. creating a CachedResponse from raw SQL query
+class SQLResponse(Base):
+    """Database model based on :py:class:`.CachedResponse`. Instead of full serialization, this maps
+    request attributes to database columns. The corresponding table is generated based on this model.
+    """
+
+    __tablename__ = 'response'
+
+    key = Column(String, primary_key=True)
+    cookies = Column(String)
+    content = Column(LargeBinary)
+    created_at = Column(DateTime, nullable=False, index=True)
+    elapsed = Column(Integer)
+    expires = Column(DateTime, index=True)
+    encoding = Column(String)
+    headers = Column(String)
+    reason = Column(String)
+    request_body = Column(LargeBinary)
+    request_cookies = Column(String)
+    request_headers = Column(String)
+    request_method = Column(String, nullable=False)
+    request_url = Column(String, nullable=False)
+    status_code = Column(Integer, nullable=False)
+    url = Column(String, nullable=False, index=True)
+
+    @classmethod
+    def from_cached_response(cls, response: CachedResponse):
+        """Convert from db model into CachedResponse (to emulate the original response)"""
+        return cls(
+            key=response.cache_key,
+            cookies=json.dumps(response.cookies),
+            content=response.content,
+            created_at=response.created_at,
+            elapsed=response.elapsed,
+            expires=response.expires,
+            encoding=response.encoding,
+            headers=json.dumps(response.headers),
+            reason=response.reason,
+            request_body=response.request.body,
+            request_cookies=json.dumps(response.request.cookies),
+            request_headers=json.dumps(response.request.headers),
+            request_method=response.request.method,
+            request_url=response.request.url,
+            status_code=response.status_code,
+            url=response.url,
+        )
+
+    def to_cached_response(self) -> CachedResponse:
+        """Convert from CachedResponse to db model (so SA can handle dialect-specific types, etc.)"""
+        request = CachedRequest(
+            body=self.request_body,
+            cookies=json.loads(self.request_cookies) if self.request_cookies else None,
+            headers=json.loads(self.request_headers) if self.request_headers else None,
+            method=self.request_method,
+            url=self.request_url,
+        )
+        obj = CachedResponse(
+            cookies=json.loads(self.cookies) if self.cookies else None,
+            content=self.content,
+            created_at=self.created_at,
+            elapsed=self.elapsed,
+            expires=self.expires,
+            encoding=self.encoding,
+            headers=json.loads(self.headers) if self.headers else None,
+            reason=self.reason,
+            request=request,
+            status_code=self.status_code,
+            url=self.url,
+        )
+        obj.cache_key = self.key  # Can't be set in init
+        return obj
+
+
+class SQLRedirect(Base):
+    __tablename__ = 'redirect'
+    redirect_key = Column(String, primary_key=True)
+    response_key = Column(String, index=True)
+
+
+class DbCache(BaseCache):
+    def __init__(self, engine: Engine, **kwargs):
+        super().__init__(**kwargs)
+        self.redirects = DbStorage(engine, model=SQLResponse, **kwargs)
+        self.responses = DbStorage(engine, model=SQLRedirect, **kwargs)
+
+
+class DbStorage(BaseStorage):
+    def __init__(self, engine: Engine, model, **kwargs):
+        super().__init__(no_serializer=True, **kwargs)
+        self.engine = engine
+        self.model = model
+
+    def __getitem__(self, key):
+        pass
+
+    def __setitem__(self, key, value):
+        pass
+
+    def __delitem__(self, key):
+        pass
+
+    def __iter__(self):
+        pass
+
+    def __len__(self):
+        pass
+
+    def clear(self):
+        pass