Hosting sqlite databases on static page servers

[Avinash L Varna]

Hi all,

I came across this nice article about a tool the author developed to use sqlite databases on static page hosters (such as Github pages, Netlify) instead of actual DB servers that require hosting and upkeep AND without downloading the entire sqlite db - https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/. Just thought I would share it here in case it sparks some ideas/applications. (My apologies if you've already seen it.)

E.g. Shreevatsa had previously converted the DCS data to an sqlite file that's about 753 MB. Using this technique, it may be possible to build web apps that use the DCS data without downloading the entire database each time, without using a SQL server.

Another application I can think of is to convert the stardict dictionaries into sqlite and use this technique to build an online dictionary that can run entirely in the browser, without requiring any backend database servers. Members of this group have had issues with some servers used in the past going offline or being repurposed, so this could be one solution.

(Many of these could indeed be built as simple server-side applications hosted on appspot or similar, but this is a nice client-side alternative.)

I would love to hear any other ideas/thoughts. I hope you will share them, and maybe someone will be inspired to build them.

Thanks

Avinash

[Irene Galstian]

Thank you! Will try out. 

Best wishes,

Irene

On 3 May 2021, at 5:26 pm, Avinash L Varna <avinas...@gmail.com> wrote:



--
You received this message because you are subscribed to the Google Groups "sanskrit-programmers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sanskrit-program...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sanskrit-programmers/CAALtx9YQzLm3J1MPt25ZtURYoviAx3p6zFiEOeFWy3wj_gxMvg%40mail.gmail.com.

[विश्वासो वासुकिजः (Vishvas Vasuki)]

On Mon, May 3, 2021 at 9:56 PM Avinash L Varna <avinas...@gmail.com> wrote:

Hi all,

I came across this nice article about a tool the author developed to use sqlite databases on static page hosters (such as Github pages, Netlify) instead of actual DB servers that require hosting and upkeep AND without downloading the entire sqlite db - https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/. Just thought I would share it here in case it sparks some ideas/applications. (My apologies if you've already seen it.)

Interesting! Random access read of files over HTTP (which I suppose is required for this work as described) शक्यम् इति नाज्ञासिषम्।

 

Another application I can think of is to convert the stardict dictionaries into sqlite and use this technique to build an online dictionary that can run entirely in the browser, without requiring any backend database servers. Members of this group have had issues with some servers used in the past going offline or being repurposed, so this could be one solution.

(Many of these could indeed be built as simple server-side applications hosted on appspot or similar, but this is a nice client-side alternative.)

I would love to hear any other ideas/thoughts. I hope you will share them, and maybe someone will be inspired to build them.

This very problem (which this http sqlite attempts to solve) has bothered me for long (eg. with https://github.com/sanskrit-coders/subhAShita-db-sanskrit ). I'd add another constraint for an ideal static database - you should be able to edit (atleast some of) the data as you would edit plain text files on github. With this, I get the hope that I could combine per-record data files (eg. 1 per subhAShita) with an sqlite database for indices/ search (perhaps with the latter being synced with CI [GithubActions] builds).

Thanks

Avinash

--
You received this message because you are subscribed to the Google Groups "sanskrit-programmers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sanskrit-program...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sanskrit-programmers/CAALtx9YQzLm3J1MPt25ZtURYoviAx3p6zFiEOeFWy3wj_gxMvg%40mail.gmail.com.

--

--
Vishvas /विश्वासः

[Shreevatsa R]

This is a really clever idea and good motivation, thanks Avinash for sharing!

The author is indeed hosting the 600+MB database on GitHub Pages, as 64 files of about 10 MB each: https://github.com/phiresky/world-development-indicators-sqlite/tree/20c1543/split-db

And the trick revolves around something as simple as xhr.setRequestHeader("Range", "bytes=" + from + "-" + to);

Really inspiring.

Both the use-cases you mentioned are great (in-browser usage of something like DCS, and of the stardict dictionaries). I hope someone builds them... For instance, I would use the dictionaries a lot more if they were accessible via a web browser instead of specific apps. (I've seen https://kosha.sanskrit.today/ which I suspect relies on the same Stardict dictionaries compiled by Vishvas and Dhaval and others, but they seem to inject artificial delays and limits etc and have some bugs, which I would be more inclined to report/investigate if the platform were free and open-source.)

Separately, I was also thinking recently about full-text search (e.g. searching for a certain word in a large text corpus), and looking up a few pre-existing options online, had got the impression that the best (in terms of smallest size of the index that needs to be downloaded) may be flexsearch (speed benchmark). But with this idea, we could use an SQLite index (FTS5), avoid downloading the entire index, and even do things like "NEAR", etc.

About Vishvas's idea editing the data, I guess it's out of scope of this idea (needs a non-static server), but an idea I've seen recently is to use GitHub servers in creative ways; in particular I saw some blogs hosted on static hosting (like GitHub Pages) that nevertheless incorporated a comments section using https://utteranc.es/ (which uses GitHub issue comments). So it may be possible to do something similar where edits (by a user who is logged in to their GitHub account) get posted as GitHub comments on some issue, and a bot looks at the GitHub issue and updates the db. (In the hypothetical case of wanting to show verses ranked by user likes—and no other kind of editing needed—one could do something even simpler, by having each one be a GitHub comment, so the user can just click thumbs up etc!) At some point it becomes easier to use something server-side; possibly something like Firebase may be a good option.

--

[उत्कर्ष राजपूत]

अपि॒ न Github API प्रयो॑क्तुं शक्यते स॒म्पाद॑नाय॒ यथात्र॒ निर्दि॑ष्टम्?

Can't the edit be done using the Github API as documented here ?

मंगलवार, 4 मई 2021 को 11:25:35 pm UTC+5:30 बजे shreevatsa ने लिखा: