Sqlite

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

I have been continuing my earlier virtual table prototype and it is slowly coming along. I uploaded a version to codeberg while I was debugging python packaging. I do not have it quite working right, but I think I will be using setuptools-rust for now (pr ).

This post is partly to help document what I have discovered over time so will not be complete code examples in every case. In the future I will be uploading the project to Codeberg and later to PyPi.

Most of my rust code is likely not great, so would appreciate a mentor!

Sqlite has support for virtual tables which can be used for external datasources. There is an example csv data source that allows you to query a csv file as if it was a regular table.

.load ./csv
CREATE VIRTUAL TABLE temp.csv USING csv(filename=FILENAME);
SELECT * FROM csv;

I do not really want to write C for this, but I found the rusqlite project which allows writing a loadable_extension that provides virtual tables. Using their vtablog example (slightly modified) I was able to test from sqlite with a rust module.