Provide sqlite via a different URL

[cmungall]

Currently semsql deposits the databases in an s3 bucket, and we advertise the bucket URL. We should also provide a vendor neutral URL such that we can easily swap different providers.

[github-actions]

Summary

Confirmed in the code: the S3 URL is hardcoded in download_obo_sqlite():

# src/semsql/builder/builder.py:96
url = f"https://s3.amazonaws.com/bbop-sqlite/{db}.gz"

semsql download <ontology> (CLI: src/semsql/builder/cli.py:110) always goes straight to s3.amazonaws.com/bbop-sqlite, and that URL is also baked into downstream consumers (notably OAK/pystow, per #43). There's no indirection layer at all right now, so swapping providers means a coordinated update across every client that has this URL hardcoded — exactly the problem this issue is flagging.

This duplicates/extends #43

#43 "Create w3ids for sqlite downloads" (opened 2022-07-06, still open) proposed exactly this: mint a PURL at w3id.org/semsql/ so OAK and other consumers stop hardcoding s3.amazonaws.com/bbop-sqlite. I checked — https://w3id.org/semsql/ currently 404s, so that proposal was never implemented. #110 and #43 are the same underlying ask (vendor-neutral indirection), just framed from different angles four years apart. Worth merging/cross-referencing so effort isn't duplicated.

From the #43 discussion, @jamesaoverton raised concerns (maintenance, format/compression, versioning of ontologies vs. schema/software) that are still relevant design questions here. @cmungall's reply there is a useful design note:

• No guarantee specific versionIRIs stay available — currently versioned by import date only.
• Plan was to move to compressed-only distribution (referenced cthoyt/pystow#45).

Other related issues for context

• Use https://s3.amazonaws.com/bbop-sqlite/hp.db.gz as an example S3 download, not https://s3.amazonaws.com/bbop-sqlite/hp.db #79 (closed) — canonicalized the advertised download path to .db.gz rather than .db, and noted the S3 bucket listing itself (https://s3.amazonaws.com/bbop-sqlite) doubles as an informal manifest.
• Update ontology builds in S3 bucket #76 (closed) — a report of stale builds (EFO) surfaced that there's no way to query "what version is actually behind this URL" without opening the db and checking owl:versionIRI — relevant if a redirect/manifest layer is built, since it could also expose version metadata.
• Include PyOBO products #45 — discusses adding PyOBO/obo-db-ingest products distributed the same way via S3; any vendor-neutral URL scheme should be designed to cover non-OBO-Foundry ontologies too, not just the OBO set.

Concrete options for the neutral URL layer

1. Revive Create w3ids for sqlite downloads #43's w3id.org plan. w3id.org (github.com/perma-id/w3id.org) is a community-run PURL redirector already widely used in the OBO/semantic web space — adding semsql/sqlite/{ontology}.db.gz → https://s3.amazonaws.com/bbop-sqlite/{ontology}.db.gz is a small PR to that repo's .htaccess-style config and costs nothing to maintain. This is the lowest-effort path and directly closes both issues.
2. Piggyback on OBO's existing PURL infra (purl.obolibrary.org), since it's already the trusted indirection layer for OBO ontology IRIs and has established governance — could add a /sqlite/ or /semsql/ path there instead of standing up a new namespace.
3. Either way, the code fix is small: parameterize the base URL in download_obo_sqlite() (src/semsql/builder/builder.py:87-104) instead of hardcoding s3.amazonaws.com/bbop-sqlite, defaulting to the new PURL. That alone decouples the client from any specific storage vendor even before a redirect target is finalized.

Suggested next step

@dragon-ai-agent please parameterize the base download URL in download_obo_sqlite() (src/semsql/builder/builder.py) into a constant/config (e.g. SEMSQL_DOWNLOAD_BASE_URL, defaulting to the current https://s3.amazonaws.com/bbop-sqlite value), and thread it through so it can be overridden via env var or CLI option. Do not change default runtime behavior. This is prep work for switching to a vendor-neutral URL (see INCATools/semantic-sql#110 and #43) once that redirect target exists.

Separately, reviving the w3id.org/semsql/ registration proposed in #43 (a PR to github.com/perma-id/w3id.org) would need to happen outside this repo, and is probably the actual unblocking step here.

[justaddcoffee]

To reduce s3 charges, cloudfront might be an option
https://chatgpt.com/share/e/6a4687a1-5f14-832c-aade-1072c0972fc3

[cmungall]

cc @kltm

[kltm]

After a discussion with @caufieldjh , we now have https://semanticsql.berkeleybop.io/{db}.gz functioning.

This provides a neutral (neutral enough for the moment anyways) location that is layered behind AWS CloudFront and Cloudflare. We can now take traffic at this location and it will significantly reduce (but not eliminate) S3 bucket egress charges.

[caufieldjh]

Turns out there is some weirdness: Cloudflare blocks the Python-urllib/* User-Agent, returning a 403. That's what pystow uses by default, which worked without issue when accessing the S3 directly. Let me find the cleanest fix — check whether the requests UA works and how pystow lets us override. We can use requests as the backend instead and still use pystow for caching.

[kltm]

@caufieldjh that is a slightly odd error. If you think it might be from the CDN layers (rather than something funny with that client), let me know and I can poke around a little.

[caufieldjh]

It is slightly odd, because based on this line in pystow:
https://github.com/cthoyt/pystow/blob/621274825562fc625c887b67b0dbc2b0db8769e4/src/pystow/utils/download.py#L197
it should be setting the User-Agent to f"pystow v{VERSION}" rather than the urllib default.
But pystow can use requests too, and that works as expected (i.e., we can set the User-Agent to something other than pystow), so not really an issue.