Authors CurseForge

So, LUA tables are used a lot in UIs. That much is certain. In the past years (has it been years already?) of programming addons several problems snuck up. Eventually they led me to the following idea: Make a relational database management system-library with REAL tables complete with indexes that speed up access, table definitions to force compliance, triggers that automate certain tasks and an implementation of SQL to control it all.

Tables are defined with 'CREATE TABLE', deleted with 'DROP TABLE'. The definitions are stored in the database.

Indices and triggers can be defined and dropped using their SQL commands.

Data manipulation occurs through SELECT, INSERT, UPDATE and DELETE statements which are checked against the table definitions.

I don't know how far I will be able to drive this (INNER/OUTER JOINS, subqueries, ...) but the further the better I think.

In the end I believe it will provide an abstraction layer that some db-heavy addons have been needing in order to reduce their complexity and hopefully even improve performance.

Even further improvements can see selective compression of certain tables that are not used for a while to reduce memory consumption?

AceRDBMS as name? Would it need to be stooled onto AceDB or would it be better to keep the data locked away from tampering?

Is the SQL implementation over the top (would the parsing kill me?) or would I be better off exposing a certain set of API-methods that perform the same thing? AceRDBMS:CreateTable(tabledefinition) ? AceRDBMS:Select(fieldvalue-pairs, table) (this would probably make joins impossible)?

No.. please don't! All the overhead that would need is wasted.

Mostly I came up with the idea because I was always trying to design my LUA-tables dictionary style for ultra-fast lookups. While this works fine you're screwed when you need to find "records" with a different qualifier. If you wanna maintain your speed you need to start keeping second dictionaries that end up overly complicating things.

I can see how an SQL-interpreter would be over the top, but some generic framework to take the load off the developer's hands when dealing with these table-issues should have it's place right? And being able to trigger off certain data-changes should simplify addondesign as well...

Off the top of my head I'm thinking of Auctioneer that has to maintain vast amounts of data while keeping it sane AND fast.

Firstly, "Lua" is not an acronym, please stop posting it in all caps. Secondly, SQL is good for SQL servers, not so much for flat file databases such as WoW's implementation of Lua.

I vote, nay.

Edit: This is just fetching the data from the DB. Never mind the complexity of maintaining links to tables (relational).

You'd have to parse the SQL command:

local fieldName, tableName, conditions = string.match(sql, "SELECT (.*) FROM (.*) WHERE (.*)")

Then, you'd have to parse the fieldName(s):

... = string.split(fieldName, ",")

Then you'd have to parse the tableName(s):

... = string.split(tableName, ",")

Then you'd have to parse the conditions (this is the hardest part):

... = string.split(condition, (some complex pattern to split the SQL-esque condition list))
... = string.split((returns from the above), (= or LIKE or < or > or <= or >= ...))

Wouldn't it be much easier and less laggy just to do this?:

if tableName[fieldName] == conditions then ... end

Well, thank you all for your input.

for postno, post in pairs(feedback) do
  respond(post);
end

You're all correct in the following points:
- Allara, Auctioneer DOES use a highly compressed, customized tableformat
- Allara, your own addon probably does as well
- Pastamancer, with dictionary style I mean:

BeneCast.SpellInfo = {
  ['Power Word: Shield'] = {
    spellid = 17,
    target = 'group',
  },
  ...
};

as opposed to

BeneCast.SpellInfo = {
  {
    name = 'Power Word: Shield',
    spellid = 17,
    target = 'group',
  },
  ...
};

The first way, finding a spell is very fast: spell = BeneCast.SpellInfo[spellname];
opposed to scanning through the table comparing the name-members because the index is a sequential number.

The trouble starts, assuming for a moment that the table gets enormous, when I'd routinely need a second value looked up very fast. In SQL the equivalent of this are indices. Secondary tables that only contain the lookup-values and a link to the records that contain that value. In Lua you'd have to maintain a table like:

BeneCast.SpellsByTarget = {
  self = { *linktospell1*, *linktospell2*, ... },
  group = { ... },
};

Now, BeneCast does not have "vast amounts of data" that need to be scanned quickly. I WAS thinking of other addons that have such storage needs.

- egingell, I am moving away from the SQL-parsing that's true. Even though it's quite a powerful language... it'd generate FAR too much overhead for it's benefit. Instead I am considering an API-based approach. Like AceRDBMS:Select(table, { field1, field2, ... }, condition, indexid_to_use); (with optional arguments off course).

Remember though, the powerful thing about SQL (which I'd like to mirror very much in some way) is the 'anything goes' style of setting up conditions and return values. Things like (code == 'stance' or type == target) as opposed to just searching for one fieldmatch at a time.

Joins are usually what makes my DB-hart go faster as well. Being able to link two tables together so you don't have to spread your data all over the place. So say, being able to replace table with a 'join-definition' would be a possibility as well.


In the end it's like egingell's sig says:
"Ace == Libraries to help addon devs make addons."

Will existing addondevs that have developed their own tight 'n fast format jump up, toss their work aside and flock to this library? I really doubt it unless they've programmed themselves into a corner, decide they "don't need this trouble" and want an out-of-the-box "good, but not optimized to their addon specifically" solution.

Likewise, the next addondev that would like to make a "big-data" addon but doesn't because of the pains that might go into managing the database can be pulled over the line anyway if he sees AceRDBMS and goes "ow, that's nice, let's run with it anyway".

As a comparison:
Do we NEED AceConfigDialog-3.0 ? No, ppl were making GUI's before it and they'll still make 'em if AceConfigDialog stops being developed. But me, myself, I've always been crap at designing a GUI and AceConfigDialog (actually, it was Waterfall at first) was the library that made me go, "well, might as well put one in anyway". The fact that it was just one line of code added made it all the sweeter :p.

Remember why SQL exists. It's not to make things faster or use less space. It exists to enforce a standard that multiple applications can use to access the same data.

It also made a lot more sense back in the day when business apps were written in-house, and not shipped as readymade packages in the tens of thousands from large vendors.

These days, I really only want to barf over all the projects using SQL databases where even loading up a flat file and re-writing all of it would do just as well, AND be faster.


As an example, in an in-house job a while ago, I wrote a Bugzilla-like web application that used a flat text file database + an index file at the side. It did all the relations that bugzilla did, PLUS a whole heck of a lot more (work registered in multiple subcomponents per ticket, multiple assignees). Fulltext searches (which simply consisted of running grep over the textfile) over a set of 100k tickets were... hold on now... OVER TWENTY TIMES FASTER than in Bugzilla, which handed the job off to MySQL.

Oh and did I mention that it used only a fraction of the RAM compared to bugzilla+mysql?

Oh and that changes to the DB format were a no-brainer, and didn't require you to ship large SQL scripts that modified people's databases, and bound to fail 90% of the time when people tried to skip an upgrade?

Rule of thumb: specialized is always faster and more efficient than generic.


Having said that, this was my schpiel on SQL. I won't shoot someone that writes an RDBMS library for their own use. But as a core dev of Ace3, I _can_ say this: Ace3 will never incoroporate an RDBMS library. Ace3 is meant to be tools interesting to a vast majority of addons, not a select few.

Mikk, drop the SQL-thing. X-nay on the SQL-nay ... ok?

If I EVER decide to write this it'll be API-based, not text-based. And as far as incorperating it into Ace3... we'll see how popular it is *grin*.

It may be that NOW not many devs seem interested. But once it's out, ppl (being inherently lazy) are bound to start using it. Even in addons that don't REALLY need it.

Quote from Allara

Or would it be QL-Snay?

My piglatin is a little rusty...

Actually, since SQL is an acronym, I think it would be SQL-ay. :)

Another thing: transactional support!

It totally bites to corrupt your data because of a bug in the midst of a half-completed change.

Changes are held in a buffer until the transaction is closed. Changing data keeps track of the timestamp the record was modified from. When a transaction closes timestamps are checked and the changes are executed. Conversely, when executed 'with lock' (not warlock :p), other transactions cannot change a record.

Lol, imagine a 'your activity has been chosen as the victim in order to resolve a deadlock'-error ingame. DB-Programmers'd cry in terror.

(Apologies for keeping this going, the geek in me cannot resist. It's like those Linux fans that insist on running their favorite OS on everything with a CPU)