6 ms·

Using Askgit – A SQL interface to your Git repository

176 points by pcr910303 6y ago

bravura 6y ago
What are good APIs to git, besides git itself? Something high level, ergonomic, and with write operations, not just querying?
- gru 6y ago
  libgit2[0] has a comprehensive API and bindings for dozens of languages. I also like go-git[1] - a pure Go implementation with idiomatic Go API and cool features like in-memory filesystem. [0] https://libgit2.org/ https://libgit2.org/ [1] https://github.com/go-git/go-git https://github.com/go-git/go-git
gigatexal 6y ago
I’m an absolute fanboy of all the efforts to bolt on SQL interfaces to things. I love this. Kudos to the creator.
- eitland 6y ago
  You are maybe aware of this already then but someone here might like to know that the Fossil DVCS is built on top of SQLite, by the same author (edit as pointed out by Gaelan:) as SQLite.
  - Gaelan 6y ago
    To be clear, the same author as SQLite, not Askgit.
    - gigatexal 6y ago
      I did not know that. Thank you!
    - eitland 6y ago
      Thanks for pointing out, I thought it was obvious from the context but I'm glad someone tells me when it is not.
- cube2222 6y ago
  If you like this, check out OctoSQL[0], it bolts on SQL on json, CSV, Excel and Parquet files. It also let's you join them with each other (and other databases). [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql
  - sirodoht 6y ago
    There is also xsv [0] for CSV SQLing [0] https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
    - gigatexal 6y ago
      For this and the above +1 to you both and thanks.
- tyingq 6y ago
  Sqlite has an api for "virtual tables" that provides a fairly easy way to bolt SQL on top of things. https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html I haven't used it directly, but I did use a Perl module that hooks into it, and that made it very easy to cobble together. https://metacpan.org/pod/release/SALVA/SQLite-VirtualTable-0.06/lib/SQLite/VirtualTable.pm https://metacpan.org/pod/release/SALVA/SQLite-VirtualTable-0...
  - thechao 6y ago
    The VTAB extension is new. The documentation is a bit thin compared to the rest of SQLite. (To be clear: the documentation is good; just not fantastically awesome like the rest of SQLite.) The main Achille's Heel of VTAB is wrapping your head around the indexing API: the basic idea is you need to tell the query planner which of a set of columns is best to work with. It is exceedingly unintuitive. This is compounded by the problem that the planner essentially refuses to do binary-search queries on more than one column of a VTAB. (Hell -- getting it to use any binary-search on any index is nearly impossible.) The result is that unless you're in a real bind, you should always translate your custom VTAB data into a reified table. This is all compounded by the fact that the slightest misstep in the callback API will corrupt/confuse/lockup SQLite (the in-memory representation -- your data is still safe). I'm glad VTAB exists, and I know it's just a side-effect of the compression support, but goddamn it's frustrating for it to be almost be awesome...
    - gigatexal 6y ago
      Bummer. Perhaps over time these pain points will be remedied.
andreypopp 6y ago
What would be nice is to add to this a way to plug language specific semantic analysers. Then it could be possible to do queries like "which commits did changes to a specific function" and so on.
- mattigames 6y ago
  I have always wondered why GitHub doesn't add such functionality, its a no-brainer how much it would help to have a way to right-click a function and see how has it changed through history, it could use an interface where you scroll through time (eg the most you scroll the older versions you see, until you reach when it was created); same thing for files.
  - oefrha 6y ago
    Presumably you’ve heard about git-blame, which is available on GitHub.
    - mattigames 6y ago
      Yeah, I find it of little use due being syntax agnostic (e.g can't tell me about the changes on a function because it doesn't know what a function is). If you are taking about me asking the same for "files" I meant being able to see all the versions of a file in a single page (maybe using "infinite scroll" in case there are too many)
      - deleted 6y ago
        [deleted]
  - sigstoat 6y ago
    maybe some day after their search develops even the faintest idea of what programming language lexemes look like.
- taspeotis 6y ago
  Visual Studio has a limited version of what you want: if you start with a function it can show you the revision history. https://docs.microsoft.com/en-us/visualstudio/ide/find-code-changes-and-other-history-with-codelens?view=vs-2019#find-changes-in-your-code https://docs.microsoft.com/en-us/visualstudio/ide/find-code-... I think Rider has it as well.
burstmode 6y ago
If adding a SQL interface makes a system easier to access, that says a lot about how convoluted the original interface is... Next logical step : a kernel level SQL interpreter integrated in systemd.
- arankine 6y ago
  There is OSQuery (https://osquery.io/ https://osquery.io/)!
- jarym 6y ago
  Actually I think it says a lot about how expressive SQL is. I get why a lot of developers take swipes at SQL - it is a bit different to other languages. But you cannot beat it for what it does. Another ‘great’ language is XSLT - so far I’ve seen nothing else that comes close for transforming data. It’s just a shame it’s so closely tied to XML which has understandably fallen out of favour
- teget 6y ago
  As arankine noted there is the platform independent osquery. There is also SQL for WMI [1], which predates osquery, I believe. [1]: https://docs.microsoft.com/en-us/windows/win32/wmisdk/querying-with-wql https://docs.microsoft.com/en-us/windows/win32/wmisdk/queryi...
- rco8786 6y ago
  I’m not sure there’s much truth to your statement. There’s nothing inherently complex about git’s internal data structure nor does putting a SQL interface on something indicate a level of complexity.
- cocktailpeanuts 6y ago
  Most people only need to know git commit, git push, git pull, and just a handful of commands. It is by design that something like this is not included into the original interface. It would be. terrible design.
shoo 6y ago
It'd be interesting to see some examples of queries that operate on the graph structure of the repo. mercurial offers a language where you can build expressions to select revsets: E.g. > Changesets mentioning "bug" or "issue" that are not in a tagged release: hg log -r "(keyword(bug) or keyword(issue)) and not ancestors(tag())" https://hg.mozilla.org/mozilla-central/help/revsets https://hg.mozilla.org/mozilla-central/help/revsets
danielbigham 6y ago
This is really nice. I experimented converting natural language to git log commands this week (https://twitter.com/danielbigham/status/1294461750251839489 https://twitter.com/danielbigham/status/1294461750251839489), but mapping natural language to git sql might be better and more flexible in some cases.

176 points by pcr910303 6y ago

What are good APIs to git, besides git itself? Something high level, ergonomic, and with write operations, not just querying?

gru 6y ago
libgit2[0] has a comprehensive API and bindings for dozens of languages. I also like go-git[1] - a pure Go implementation with idiomatic Go API and cool features like in-memory filesystem. [0] https://libgit2.org/ https://libgit2.org/ [1] https://github.com/go-git/go-git https://github.com/go-git/go-git

gigatexal 6y ago

I’m an absolute fanboy of all the efforts to bolt on SQL interfaces to things. I love this. Kudos to the creator.

eitland 6y ago
You are maybe aware of this already then but someone here might like to know that the Fossil DVCS is built on top of SQLite, by the same author (edit as pointed out by Gaelan:) as SQLite.
- Gaelan 6y ago
  To be clear, the same author as SQLite, not Askgit.
  - gigatexal 6y ago
    I did not know that. Thank you!
  - eitland 6y ago
    Thanks for pointing out, I thought it was obvious from the context but I'm glad someone tells me when it is not.
cube2222 6y ago
If you like this, check out OctoSQL[0], it bolts on SQL on json, CSV, Excel and Parquet files. It also let's you join them with each other (and other databases). [0]:https://github.com/cube2222/octosql https://github.com/cube2222/octosql
- sirodoht 6y ago
  There is also xsv [0] for CSV SQLing [0] https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv
  - gigatexal 6y ago
    For this and the above +1 to you both and thanks.
tyingq 6y ago
Sqlite has an api for "virtual tables" that provides a fairly easy way to bolt SQL on top of things. https://www.sqlite.org/vtab.html https://www.sqlite.org/vtab.html I haven't used it directly, but I did use a Perl module that hooks into it, and that made it very easy to cobble together. https://metacpan.org/pod/release/SALVA/SQLite-VirtualTable-0.06/lib/SQLite/VirtualTable.pm https://metacpan.org/pod/release/SALVA/SQLite-VirtualTable-0...
- thechao 6y ago
  The VTAB extension is new. The documentation is a bit thin compared to the rest of SQLite. (To be clear: the documentation is good; just not fantastically awesome like the rest of SQLite.) The main Achille's Heel of VTAB is wrapping your head around the indexing API: the basic idea is you need to tell the query planner which of a set of columns is best to work with. It is exceedingly unintuitive. This is compounded by the problem that the planner essentially refuses to do binary-search queries on more than one column of a VTAB. (Hell -- getting it to use any binary-search on any index is nearly impossible.) The result is that unless you're in a real bind, you should always translate your custom VTAB data into a reified table. This is all compounded by the fact that the slightest misstep in the callback API will corrupt/confuse/lockup SQLite (the in-memory representation -- your data is still safe). I'm glad VTAB exists, and I know it's just a side-effect of the compression support, but goddamn it's frustrating for it to be almost be awesome...
  - gigatexal 6y ago
    Bummer. Perhaps over time these pain points will be remedied.

andreypopp 6y ago

What would be nice is to add to this a way to plug language specific semantic analysers. Then it could be possible to do queries like "which commits did changes to a specific function" and so on.

mattigames 6y ago
I have always wondered why GitHub doesn't add such functionality, its a no-brainer how much it would help to have a way to right-click a function and see how has it changed through history, it could use an interface where you scroll through time (eg the most you scroll the older versions you see, until you reach when it was created); same thing for files.
- oefrha 6y ago
  Presumably you’ve heard about git-blame, which is available on GitHub.
  - mattigames 6y ago
    Yeah, I find it of little use due being syntax agnostic (e.g can't tell me about the changes on a function because it doesn't know what a function is). If you are taking about me asking the same for "files" I meant being able to see all the versions of a file in a single page (maybe using "infinite scroll" in case there are too many)
    - deleted 6y ago
      [deleted]
- sigstoat 6y ago
  maybe some day after their search develops even the faintest idea of what programming language lexemes look like.
taspeotis 6y ago
Visual Studio has a limited version of what you want: if you start with a function it can show you the revision history. https://docs.microsoft.com/en-us/visualstudio/ide/find-code-changes-and-other-history-with-codelens?view=vs-2019#find-changes-in-your-code https://docs.microsoft.com/en-us/visualstudio/ide/find-code-... I think Rider has it as well.

burstmode 6y ago

If adding a SQL interface makes a system easier to access, that says a lot about how convoluted the original interface is... Next logical step : a kernel level SQL interpreter integrated in systemd.

arankine 6y ago
There is OSQuery (https://osquery.io/ https://osquery.io/)!
jarym 6y ago
Actually I think it says a lot about how expressive SQL is. I get why a lot of developers take swipes at SQL - it is a bit different to other languages. But you cannot beat it for what it does. Another ‘great’ language is XSLT - so far I’ve seen nothing else that comes close for transforming data. It’s just a shame it’s so closely tied to XML which has understandably fallen out of favour
teget 6y ago
As arankine noted there is the platform independent osquery. There is also SQL for WMI [1], which predates osquery, I believe. [1]: https://docs.microsoft.com/en-us/windows/win32/wmisdk/querying-with-wql https://docs.microsoft.com/en-us/windows/win32/wmisdk/queryi...
rco8786 6y ago
I’m not sure there’s much truth to your statement. There’s nothing inherently complex about git’s internal data structure nor does putting a SQL interface on something indicate a level of complexity.
cocktailpeanuts 6y ago
Most people only need to know git commit, git push, git pull, and just a handful of commands. It is by design that something like this is not included into the original interface. It would be. terrible design.

shoo 6y ago

It'd be interesting to see some examples of queries that operate on the graph structure of the repo. mercurial offers a language where you can build expressions to select revsets: E.g. > Changesets mentioning "bug" or "issue" that are not in a tagged release: hg log -r "(keyword(bug) or keyword(issue)) and not ancestors(tag())" https://hg.mozilla.org/mozilla-central/help/revsets https://hg.mozilla.org/mozilla-central/help/revsets

danielbigham 6y ago

This is really nice. I experimented converting natural language to git log commands this week (https://twitter.com/danielbigham/status/1294461750251839489 https://twitter.com/danielbigham/status/1294461750251839489), but mapping natural language to git sql might be better and more flexible in some cases.