YottaDB Document Database with SQL via Octo

[Paul Simon]

Hi,

I’ve built a document database on YottaDB that uses Octo to enable SQL queries over document data, and I think its brill.

Why?
Mostly MUMPS: The core is written in MUMPS, keeping it lean and efficient.
No vendor lock-in: You own your data and stack.
Minimal code: Achieve powerful functionality without writing a mountain of code.

I’ve also made sure to credit all the contributors and projects this builds upon in the GitHub README

Before sharing this more broadly, I’d love to get thoughts from the MUMPS community - see below.

Thanks,
-- Paul Simon.
LIFETIME SOFTWARE SOLUTIONS.

SQL + NoSQL Without Compromise: The Best of Both Worlds

I built an open-source document database that actually lets you:
✅ Store raw JSON - No schema headaches, no migrations
✅ Query with real SQL (JOINs, WHERE, GROUP BY - the works)
✅ Scale predictably - YottaDB's globals handle heavy loads gracefully

Why this works when others struggle:
✔ No indexing gymnastics - Queries stay fast without constant tuning
✔ Runs anywhere - Cloud, laptop, or even a Raspberry Pi
✔ Open core - No vendor lock-in, no surprise licensing

The tradeoffs? (Because nothing's perfect)
• You'll need basic Linux skills to set it up
• Complex analytical queries can be optimized/simplified using SQL functions
• Documentation is solid but not hand-holding

Perfect when you need:

Rapid prototyping without schema constraints

SQL access for reporting/analytics (with function-based optimizations where needed)

A lightweight alternative to bloated database servers

See for yourself: https://github.com/psimon2/doc-mdb

Need help implementing this?
I offer consulting through Lifetime Software Solutions - from setup assistance to writing optimized SQL functions for your specific use case.

Built with YottaDB's rock-solid engine and their new Octo SQL layer - "because sometimes the right solution isn't the most hyped one".

[David Whitten]

What is your experience with SQL, and how did using Octo reinforce that experience or act in conflict with it ?

Dave Whitten

713-870-3834

--
--
http://groups.google.com/group/Hardhats
To unsubscribe, send email to Hardhats+unsubscribe@googlegroups.com

---
You received this message because you are subscribed to the Google Groups "Hardhats" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hardhats+unsubscribe@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/hardhats/9695d5b8-0020-41cd-92eb-0c8e88b7887bn%40googlegroups.com.

[Paul Simon]

Hi David,

Hope you're doing OK.

I've attached some pretty complex MS-SQL procs/functions to show you the kind of things Octo wouldn't be able to run straight away - just to give you some context.

From my experience with MS-SQL:

That CROSS APPLY operator is really handy, especially when you need to:

Call table-valued functions for each row

Handle complex correlated operations that would otherwise need nasty subqueries

Example:

sql
SELECT * from [db_lookup].[gpp].[ncmp_gms_data] x
cross apply (
    select TT.*
    FROM [test01].[dbo].[PlaceAtEventTime_V2_5] (x.patient_id, x.dom) as TT
) as TC
But here's the thing - when I used CROSS APPLY with functions in MS SQL Server, it ran painfully slow. I literally spent days trying to speed it up by adding secondary indexes, with limited success!

In the end, I gave up and wrote some Python code instead - and it ran way faster than trying to do everything in SQL Server. You can see the approach I took in this paper: https://ijpds.org/article/view/2379/5032

(I even tried implementing SQL functions using cursors at first, but that was still too slow.)

Now about Octo - it supports SQL-92 which gives you good basic SQL functionality out of the box (though no cursors):
https://docs.yottadb.com/Octo/intro.html

What's really great about Octo is that once you get the hang of mapping globals to SQL tables and writing SQL queries, it saves you from all those painful $order loops!

Check out this example of embedding SQL in MUMPS routines (similar to ISC COS):
https://github.com/psimon2/doc-mdb/blob/main/mumps/NORNU.m
Look at the MAKEDEAD^NORNU routine.

Here's how you can run SQL from MUMPS:

set sql="SELECT AGEV(CAST(docid AS VARCHAR)) AS age, COUNT(*) AS record_count, SUMV2('test-123', COUNT(*)) AS total FROM xasum WHERE type = 'nor' GROUP BY age;"
S *X=$$execute^%ydbguiOcto(sql,360000)
(X will contain your result set)

So to answer your question - there's definitely a compromise approach that works:

Octo is fast because its built on MUMPS.

If Octo's SQL can handle your needs directly - great!

If not - learn a bit of MUMPS and write a function (like that AGEV function I wrote to calculate patient ages)

If you don't know MUMPS - write a shim function in MUMPS that calls Python instead

And if you need Python to access MUMPS globals - use the yottadb call-in library

Here's some example code for the Python call-in approach:
https://github.com/psimon2/doc-mdb/blob/main/python/mumps_srv_v5.py

Hope that helps answer your question!

Thanks,
-- Paul.

--
--
http://groups.google.com/group/Hardhats
To unsubscribe, send email to Hardhats+u...@googlegroups.com

---
You received this message because you are subscribed to the Google Groups "Hardhats" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hardhats+u...@googlegroups.com.

[Paul Simon]

Hi David,

Also, some other Octo-related stuff I missed earlier...

Rocto lets Java devs work with YottaDB (I’ve attached some example code). Since there's no proper Java call-in for YottaDB, Rocto fills that gap. You can do the same thing in Python too if you don't want to use the call-in lib.

There are AI models that turn natural language into SQL - I've been playing with Ollama, but it's a bit inconsistent. Ask it the same question twice, and you might get different answers. Then again, humans do that too!

But imagine training a model on your Octo queries - then you could just ask it questions in plain English (or whatever) and it'd fetch data from a Mumps/YottaDB database.

Thanks,
-- Paul.