[HN Gopher] SQLite has pretty limited builtin functions
___________________________________________________________________
SQLite has pretty limited builtin functions
Author : youngtaff
Score : 20 points
Date : 2022-08-22 21:42 UTC (1 hours ago)
(HTM) web link (datastation.multiprocess.io)
(TXT) w3m dump (datastation.multiprocess.io)
| bob1029 wrote:
| Indeed. The whole point is to extend it with application-specific
| functions since the database is typically hosted in-process.
|
| This is what we have done. We have a lot of domain-specific UDFs
| available in our SQLite scopes. Both scalar and aggregate types.
| Note that not all functions must be pure. You can quickly
| repurpose SQLite into a full-blown DSL by going down this path.
|
| See: https://www.sqlite.com/appfunc.html
|
| And: https://docs.microsoft.com/en-
| us/dotnet/standard/data/sqlite...
| srcreigh wrote:
| What language do you write your extensions in? what language is
| your codebase? and what are some non-deterministic UDFs you've
| written? Are they just randomness related or are there other
| types of non deterministic UDFs that you use?
| srgpqt wrote:
| If Sqlite is missing functionality for your particular use-case,
| there is probably an extension that adds it. If not, you can
| write your custom extension.
| tqi wrote:
| SQLite also has some weird (non-standard?) behavior when
| aggregating. If you forget to include a GROUP BY, SQLite will
| just pick a random value from the non-aggregate columns. For
| example:
|
| SELECT col1, COUNT(1) FROM table1;
|
| happily returns a single row of data.
|
| I struggle to understand why that is preferable to throwing an
| error?
| mikece wrote:
| The "lack of functions" is what allows SQLite to be what it is: a
| small, efficient SQL database that works on everything from
| mainframes to a watch. The fact it doesn't have every possible
| feature you want or need is not a deficiency of SQLite: if you
| need tons of features then, by definition, you're outside the
| niche of what SQLite was built to handle and you need a large,
| full-featured database server.
| Beltalowda wrote:
| SQLite isn't _that_ small, about 1.3M compiled as a shared
| library on my system, and the "amalgamation file" is 240k
| lines of code, or 8.2M (160k lines w/o comments). I don't think
| a bunch of functions will add _that_ much to it; it 's probably
| more about developer time, maintenance burden, etc.
| chasil wrote:
| If you are desperate for greater functionality, you can install
| the free Oracle XE database, then write whatever functions you
| want in pl/SQL.
|
| Oracle "external tables" will allow you to see your CSVs
| directly, without creating a physical table within the database
| or otherwise using resources. If you actually have SQLite
| databases, you can join them in Oracle directly with dg4odbc
| inserted into your listener.
|
| Note that the SQL/PSM standard is based on Oracle pl/SQL, and
| many databases implement it; SQLite doesn't.
|
| "SQL/PSM is derived, seemingly directly, from Oracle's PL/SQL.
| Oracle developed PL/SQL and released it in 1991, basing the
| language on the US Department of Defense's Ada programming
| language."
|
| https://en.m.wikipedia.org/wiki/SQL/PSM
| Beltalowda wrote:
| Why Oracle XE and not PostgreSQL? What advantages does Oracle
| XE have over it?
| chasil wrote:
| EnterpriseDB sells a version of Postgres with deep Oracle
| compatibility, but this emulation is not included in the free
| version. Many years ago, IBM bundled this code into db2, and
| it remains jointly developed as far as I know.
|
| The EnterpriseDB sales page for this product would be a good
| start on what's missing.
|
| https://www.enterprisedb.com/why-edb/leave-oracle-for-
| postgr...
| mhio wrote:
| At what point does the licensing kick in and the lawyers turn
| up on your door?
| mhio wrote:
| Does anyone have an idea of the performance impact of calling
| back and forth to Go from sqlite rather than C?
___________________________________________________________________
(page generated 2022-08-22 23:01 UTC)