Virtual Tables and Django Foreignkeys

I have been continuing with my virtual table implementation, though taking a short break to do some more integration on the Django side.

I have an ideas site that I use as a kind of wiki to keep track of various ideas.

# the default model I'm using from my virtual table integration
class FrontmatterModel(models.Model):
    inode = models.PositiveIntegerField(primary_key=True, editable=False)
    path = models.FilePathField(unique=True, editable=False)
    slug = models.SlugField()

    content = models.TextField()
    excerpt = models.TextField(blank=True)

    metadata = models.JSONField()

    class Meta:
        abstract = True

# Custom model for my idea object itself
# These also exist in the `metadata` json frontmatter, but putting them here
# makes them nicer to deal with from Django
class Idea(FrontmatterModel):
    title = models.CharField(max_length=128)

    status = models.ForeignKey(Status, on_delete=models.SET_NULL, null=True, db_column="dir")

    homepage = models.URLField(blank=True)
    repository = models.URLField(blank=True)
    focus = models.BooleanField(default=False)
    date = models.DateTimeField(default=timezone.now)

    class Meta:
        required_db_vendor = "markdowndb"
        managed = False

The model ultimately gets sent to the virtual table backend with a statement that looks like this

f"""
CREATE VIRTUAL TABLE IF NOT EXISTS temp.{app_model._meta.db_table}
USING {connection.vendor}(schema='{sql}', path='{model_path}');
"""

Where our schema sql looks something like this

CREATE TABLE "ideas_idea" (
	"inode" integer unsigned NOT NULL PRIMARY KEY CHECK ("inode" >= 0),
	"path" varchar(100) NOT NULL UNIQUE,
	"slug" varchar(50) NOT NULL,
	"content" text NOT NULL,
	"excerpt" text NOT NULL,
	"metadata" text NOT NULL CHECK (
		(
			JSON_VALID("metadata")
			OR "metadata" IS NULL
		)
	),
	"title" varchar(128) NOT NULL,
	"dir" varchar(50) NULL REFERENCES "ideas_status" ("slug") DEFERRABLE INITIALLY DEFERRED,
	"homepage" varchar(200) NOT NULL,
	"repository" varchar(200) NOT NULL,
	"focus" bool NOT NULL,
	"date" DATETIME NOT NULL
)

Now that I have my Ideas table reading from my markdown files, I wanted to create a Tag model that I could use as a filter.

class Tag(models.Model):
    name = models.SlugField(unique=True)
    slug = models.SlugField(unique=True)

    # Normally I would add this to my Ideas model but since it is managed=False
    # it get skipped over in migrations.
    idea_set = models.ManyToManyField(Idea, db_constraint=False)

Originally, I created this without db_constraint, which means Django generates foreign constraints on each field.

CREATE TABLE "ideas_tag_idea_set" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "tag_id" bigint NOT NULL REFERENCES "ideas_tag" ("id") DEFERRABLE INITIALLY DEFERRED,
    "idea_id" integer NOT NULL REFERENCES "ideas_idea" ("inode") DEFERRABLE INITIALLY DEFERRED
    )

This resulted in a error with some of my queries.

django.db.utils.OperationalError: no such table: main.ideas_idea

Since I am currently creating these in the temp. schema, the table is re-created for each new connection. Due to this, I need to ensure that even when using ForeignKey or ManyToMany fields, that Django does not automatically add constraints or they will fail.

Using db_constraints=False generates the following table

CREATE TABLE "ideas_tag_idea_set" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "tag_id" bigint NOT NULL,
    "idea_id" integer NOT NULL
    )

Instead of db_constraint I could use a through model for my idea_set though I would still need to add db_constraint to the idea_id field. This would let me check the constraints on the tag side, without worrying about errors on the idea side.

CREATE TABLE "ideas_tag_idea_set" (
    "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "tag_id" bigint NOT NULL REFERENCES "ideas_tag" ("id") DEFERRABLE INITIALLY DEFERRED,
    "idea_id" integer NOT NULL
    )

With my models in place, my Tag model is a kind of cache that I can update with a script similar to the following.

tags = collections.defaultdict(list)
for idea in models.Idea.objects.all():
    for tag in idea.metadata.get("tags", []):
        tags[tag].append(idea)

for slug in tags:
    tag, _created = models.Tag.objects.get_or_create(slug=slug, defaults={"name": slug})
    tag.idea_set.set(tags[slug])

In the future, I want to see if I can sort out the differences between main. and temp. schema for the virtual table so that foreign keys can be properly handled but this seems to be working for now.