[1.3b56] Database features

[neofax]

I would like to have the ability similar to tables, but more than just two fields per row. The reason is, I would like to input say a weapons table with data like so:

Name die Group Capabilities Cost
Longsword 1d8 Light Blade Versatile, Off-hand 10 GP
Falchion...

This would allow someone to write a macro like if(and(table.Weapons.Name="Longsword", equip.Weapon.Off-Hand="True"), 1d8, 0) or some such.

[mccord42]

Can't this type of thing already be done with a JSON Object? Have you looked at the JSON Functions?

[RPTroll]

Yeah but a database would be a static store vs something you have to load everytime and it would be separate from any particular token. The token-as-datastore concept sorta blows.

[Azhrei]

The token-as-datastore concept sorta blows.

Yeah, but so does embedding a database engine inside of a mapping tool.

There is hope that with a JavaScript implementation a plugin could be written that allows database access. Installing the plugin would create a global object (perhaps called "DBEngine"?) and the JavaScript code could then use that object to connect and query databases. The database could be on the local system or elsewhere on a network...

[Phergus]

Plenty of lightweight database engines that can be used with Java.

[Thanlis]

I wouldn't be cranky if there wasn't ever a database engine, but I would be pretty pleased to see one.

[Craig]

Having a database would be really handy, great for things like SRD etc.
But I am not sure if it is best to embed the database in maptool or let maptool access an external database.

From a flexibility point of view an external database is best idea.
From a end user ease point of view an embedded database is best idea (just as everything is currently contained in the campaign file).



Now having said that.

Speaking as a database admin this scares me, especially when someone who doesn't really know about database design -- and I include the majority of developers that write programs to access databases in this, trust me experience more than backs this up -- design the structure and have no one to optimize their queries for them.

I can just see it now the numerous posts along the lines of.

Most likely
Its broken my following statement
<insert statement with 15 table Cartesian join>


Second most likely
Sub query of a huge table within a in/not in.

Followed by
Changing in/not in to not exists but there is no index on the table.


Or my favorite
My mass update of this table runs really really slow, I can't understand why I added 24 indexes so the where clause should be using an index.



So when you have a lot of people who don't know SQL writing statements and designing their tables you are going to have lots of problems to support.

[Thanlis]

So when you have a lot of people who don't know SQL writing statements and designing their tables you are going to have lots of problems to support.

I wouldn't embed a relational database. Something like CouchDB, maybe? Or just do a simple table DB without relational features using SQLite as an engine... but I wouldn't expose the relational stuff, that way lies madness.

Look at me! I'm an armchair designer.

[Azhrei]

but I wouldn't expose the relational stuff, that way lies madness.

Agreed. And therein lies the problem. Exactly what features does MapTool expose? If they are not the full relational features (such as joins) where is the value in using a database?

I agree with Craig that a general purpose engine with a general purpose interface is a Bad Idea(tm). If the database only had in it certain predefined tables, then it might be possible to provide a subset of queries that are both useful and optimized. (I'm not saying this as though I know this to be the case; I'm making the argument that it might be this way.) Example tables might include tokens, images, cards (looking forward towards card/deck support), players, and a bunch of stuff that now is handled through campaign settings (light, sight, repository, states, token property sets).

[Thanlis]

but I wouldn't expose the relational stuff, that way lies madness.

Agreed. And therein lies the problem. Exactly what features does MapTool expose? If they are not the full relational features (such as joins) where is the value in using a database?

Fair question! I can think of easy use cases for D&D, but I recognize that one game system does not make for a case for full support.

[plothos]

Seems to me having the current table framework such that you could call an entry by a label rather than a table row number would be pretty useful, and pretty broadly.

There are numerous other ways to handle what that would give you, but it'd be nice to have.

[Craig]

Well if you do decide to allow database storage then an embedded database (and a relational one not something like couchDB) gives you a lot of advantages.

You can store all your data in there, be it tables or tokens. Sure to macros and other parts of map tool it may look like the data is stored on the token but in reality its stored in a db table (or db tables). The same goes for MapTool tables, this allows you to easily add ways to search through all tokens for ones that contain certain properties or even properties set to a certain value. It should also go without mentioning that databases are very good at storing data

In this case opening up the database to JavaScript macros while it makes me cringe a lot is probably something you can't get away with not doing since its so useful. There is always something you are going to want to be able to store, player notes, character inventory, GM Campaign notes, GM encounters, you name it, just look at the variety of information people are storing in JSON objects on tokens. If you go for a non relational DB then you are going to have the same sort of thing happening where people are storing JSON objects inside fields in your non relational DB.

But this of course comes with a host of issues that need to be solved.

• Campaigns are currently contained with in a single file making them easy to distribute and share.
  I guess this could be solved with file->export campaign which generates a self contained file, and file->import (I will let you guess what this one would do ).
• Do you stick with an embedded only DB or allow GM to override to a new DB.
  I can see real advantages to allowing external DB. It makes it easier to write other tools that can access the data for whatever reason, even a website for your campaign or play by email game.
• But the biggest issue to solve his how you propagate the data
  Essentially any data that would have to be retrieved via an SQL statement would have to be sent to the MT server which would run the request on your behalf and return the data. For token properties and such these can obviously easily be cached, but this still adds an extra layer of complexity to things.

[jay]

CT & IT already have a relational database built into them. I've been able to hide the fact there is a database from most of the game related code so far without too much difficulty. I don't allow SQL access to it right now, so it hides most of that complexity from the user.

It wouldn't be all that hard to add to MT. The only hard part would be to give distributed access to it. The database that I use is H2 and it has a TCP server built into it. The only difficulty I see here is that another port would have to be opened up for the database when running.

[Azhrei]

In this case opening up the database to JavaScript macros while it makes me cringe a lot is probably something you can't get away with not doing since its so useful.

With proper planning it might not be so bad. A single JavaScript object that abstracts the db interface would hide implementation details.

While I don't particularly like the idea of modeling a JavaScript object on the ODBC/JDBC DataSource interface (is that the right one? it has the executeQuery(), execute(), and other methods?) because it's not a good object model, it would certainly be the most recognizable for most programmers.

But this of course comes with a host of issues that need to be solved.

Heh, you can say that again! (Not that you need to, really. )

1. Campaigns are currently contained with in a single file making them easy to distribute and share.
   I guess this could be solved with file->export campaign which generates a self contained file, and file->import (I will let you guess what this one would do ).
2. Do you stick with an embedded only DB or allow GM to override to a new DB.
   I can see real advantages to allowing external DB. It makes it easier to write other tools that can access the data for whatever reason, even a website for your campaign or play by email game.
3. But the biggest issue to solve his how you propagate the data
   Essentially any data that would have to be retrieved via an SQL statement would have to be sent to the MT server which would run the request on your behalf and return the data. For token properties and such these can obviously easily be cached, but this still adds an extra layer of complexity to things.

Instead of an import/export paradigm, just create a directory where the tables are stored and add some code to the Save Campaign feature so that the directory contents are added to the campaign as well. The biggest issue in my p.o.v. will be schema changes to the database! It's extremely annoying to have to support a normal application with a limited release cycle when the schema changes, but the desired one-build-a-week release schedule for MapTool would be onerous!

If the database was stored in a directory under .maptool (for example) then using the database from an external tool might not be too tough. For example, MySQL uses a directory structure for each "database" with a "table" being a single file (or set of files, depending on version). It should be possible to tell MySQL to use a different directory and then no additional setup/conversion should be necessary. I can hope/wish/dream that other database engines would make it as easy...

Propagating data could be done by tunneling on top of the existing link between MapTool-connected machines. Perhaps usurping the database engine's replication facilities (if it has them) and allowing the individual machines to update themselves without MapTool being involved. I.e. MapTool connects to the engine and all data from the engine is sent over MapTool's Hessian link to clients. This keeps a single port open and the replication happens transparently.

Another option would be to provide a web-based database service and charge $5/mo for use of the service. This puts the data on a well-connected server with high outbound bandwidth availability. The local client could cache information locally with timestamps used to determine whether to go back to the web.