Mote/MapTool: Thoughts on a DB choice

[JML]

My thoughts on a DB choice obviously became a thread hacking in Wolph's Mote wish list. So I take it here on it's own.

To summarize, I perfectly understand SQL is the easiest way for the development team to give access to a data base system to the framework coders, but I'm wondering if it suits best those framework coders needs, taking into account they're not supposed to be professional coders.

[aliasmask]

For me, I won't really know until I use it. My biggest concerns with db choice is how it deals with concurrency issues and speed. But even the worse one out there would probably be powerful enough to handle a one GM and six player environment. One thing I would want is to be able to create "alias" tables (not sure that's the right term). Basically, it's a table of other tables. So, if you change one of the tables, it changes the data in the other table. This is usually needed to speed up queries of common searches that stretch across several tables.

[JML]

It's a problem in the real world because newer programmers often treat databases as "dump in data, don't worry about it" because it takes so much data for performance to become a problem.

Surely frameworks won't need terabytes

But beginners will surely create a table, and then two others, etc. until it becomes a mess At this point they're going to say: "I need to tidy this up". My question is: "is SQL appropriate to handle this kind of behaviour ?"

You guys have got years of professional experience. They don't…

[JML]

AM, your concurrency concerns seem perfectly sound. But as you and Dracones pointed out, MapTool's frameworks' technical needs will surely be quite low. This could open up opportunities.

On the other hand, frameworks' DB will probably not have hundreds of tables, and reordering them may not be as much a problem as I fear it could be.

Even if I'm still wondering how easy merging frameworks and tools' DB would be…

[RPTroll]

I think I've read H2 is their choice. I've worked with it and it's as functional as it needs to be.

It would be nice if they explored the noSQL option as well. My (limited) understanding of them leads me to believe they would be a good fit, especially if you go with the "no only sql" no sql.

[Enoch]

I'm pretty newbish and the inclusion of a SQL database really excites me to work on MapTool. JSONS and storing stuff on tokens was a wall to what little programming knowledge I had. I had some vague ideas about storing stuff on tables as well, but never looked into it. I already have two projects buzzing through my head to try out with MOTE and both will make extensive use of the database feature. That with the new macro editing window will really help this newb out.

As for deciding which DB to use, I don't know enough to comment on how good H2 is, but I will say that a SQL based DB is definitely the way to go. Not for any intrinsic features of SQL, but the fact that its everywhere. My intro to databases class in community college taught two things. Database structure and SQL statements. Access still uses SQL statements, and there are a lot of people using Access that have no clue how to program anything beyond some basic VBA stuff.

[Bone White]

JSONS and storing stuff on tokens was a wall to what little programming knowledge I had.

So tokens would be databases and the jsons would be the tables. All databases use the same principle, the only difference is the wording and application, not the structure.

[JML]

Access still uses SQL statements, and there are a lot of people using Access that have no clue how to program anything beyond some basic VBA stuff.

Good example. Anyone else having this kind of experience who cares to share ?

[Dracones]

I guess my question would be "what would be better"?

Personally I think SQL is much easier than working with JSON as that gets pretty complex when you start to mess with multiple levels. If you go with anything else for data storage you're getting into Not Invented Here syndrome and 2 years out as more and more features get added to please the power users it'll become a complete and utter mess.

The Maptool macro language is mess because of that, which is probably one reason 2.0 is aiming towards Javascript.

Standard languages like SQL and Javascript have decades of testing, debugging, and design behind them and for new users there are a gazallion tutorials, videos and books to read on the subject. And for the people writing Mote and 2.0, the systems already plug into Java's architecture so there isn't a lot of work and they'll have years of testing already behind them on other app platforms.

So... it's standardized, less of a mess than what we currently have, gobs of tutorials already out there and has already been proven to work.

[Jagged]

Does there really need to be a choice? Can't you just specify a driver and use whatever?

I do web development for a living (slight lie, I do very little coding these days and have a small team of more skilled developers ) and while we currently use Oracle for all our production instances, the databases my developers install on their local machines is many, varied and seems to change with the wind/latest fashion.

My point being, that virtually all databases support SQL, is the MOTE development going to do anything that ties itself closely with a particular database flavour? Remember, there is a big difference between "These are the databases we support" and "These are the ones it works on"

[RPTroll]

I suspect coding the database in interface gets a lot easier if you limit yourself to one but ideally they won't do that. A clean separation is preferred in most architectures these days but it still comes down to the programming interface. That will need to remain the same or the frameworks will be fragile.

[Jagged]

Database access is still going to go through Java. I guess they will only want to distribute one database driver, but that should be in its own jar and should be configurable. Then as long as you avoid any favour specific query functions, people should be able to swap easily enough.

Coincidentally enough, the job I am doing today is test migrating one of the large applications we use (packaged product not in-house development) to a different database in the hopes of achieving some performance improvements

[JML]

Two thoughts by now:

1. Not making a choice, that is, giving choice between different DB looks like a very bad idea if you hope for easy frameworks and plugins interaction How will framework A interact with plugin B if they don't use the same DB ? And who will want to learn x number of query languages to be able to tweak those.
    
2. I'm not against SQL per se. I'm just aware other options exist and wondering what the pros and cons are. This being said, it seems to me that the only experienced users who shared their perspective are SQL professionals. I'd like to get at least one from someone who worked with something else for a more balanced view. "I worked with xxx and it's a real PITA because yyy" is ok with me as long as the "worked with" and "because" parts are there
   To rephrase this: it's not a comparison if you only look at one side

[Jagged]

What exactly do you mean by not SQL? Did you have any databases in mind?

There are other database systems that do not use the relational database model to store data, but they are less used and generally aimed at specialist high performance functions. People tend to refer to those as "NoSQL" databases, which actually stands for "Not Only SQL" because those databases still understand your typical "Select * from TableName where TableName.ColumnName=X" type command.

Or do you mean specific flavours of database? Like MySql, MariaDB, SkySQL, Postgres, Ingress, H2, Oracle etc etc?

Whatever flavour of database the MOTE team decide to use, I would expect the task of swapping the underlying database technology to be relatively simple. As I said above, its usually no more than supplying the correct driver and database location. There will be varying levels of abstraction within the Java code and I find it hard to imagine Maptool would require any functions that would tie it to any particular database flavour. Ultimately if the MOTE developers don't use a database that understands SQL, they will have chosen something rather obscure and will have drastically limited any hosting choices that might be available.

TL;DR - SQL is an industry standard understood by almost every database technology, to use anything else would severely limit your options.

[Jagged]

@JML

Coincidentally enough, the job I am doing today is test migrating one of the large applications we use (packaged product not in-house development) to a different database in the hopes of achieving some performance improvements

In the example above, the application is a very large and complex application that contains a desktop-like application, a web portal and a host of public web services. It has a reasonably complex data structure and a large amount of data. Our production environment is Oracle but we have just created a test version running Microsoft SQLServer. Here we had a more complex task, as we had to migrate data, rather than start from a clean slate, but that was achieved quite simply using public available tools. Then we just installed another version of the application on a tomcat server and changed the database url. Job done*


* well there was some other stuff that needed doing but that was to do with changing the application location, rather than the database technology