Mote/MapTool: Thoughts on a DB choice

If you have an idea for a new feature, please discuss it in the main MapTool forum first, then post a summary of the discussion here. Use the first Sticky as a template.

Moderators: dorpond, trevor, Azhrei, giliath, jay, Mr.Ice

User avatar
JML
Dragon
Posts: 515
Joined: Mon May 31, 2010 7:03 am
Location: Blagnac, France

Mote/MapTool: Thoughts on a DB choice

Post by 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.

User avatar
aliasmask
Deity
Posts: 8667
Joined: Tue Nov 10, 2009 6:11 pm
Location: Bay Area

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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.

User avatar
JML
Dragon
Posts: 515
Joined: Mon May 31, 2010 7:03 am
Location: Blagnac, France

Re: Mote/MapTool: Thoughts on a DB choice

Post by JML »

Dracones wrote: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 :wink:

But beginners will surely create a table, and then two others, etc. until it becomes a mess :lol: 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…

User avatar
JML
Dragon
Posts: 515
Joined: Mon May 31, 2010 7:03 am
Location: Blagnac, France

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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…

User avatar
RPTroll
TheBard
Posts: 3148
Joined: Tue Mar 21, 2006 7:26 pm
Location: Austin, Tx
Contact:

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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.
ImageImage ImageImageImageImage
Support RPTools by shopping
Image
Image

Enoch
Kobold
Posts: 5
Joined: Fri Mar 29, 2013 7:24 am

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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.

User avatar
Bone White
Great Wyrm
Posts: 1124
Joined: Tue Aug 23, 2011 11:41 am
Location: Cornwall, UK

Re: Mote/MapTool: Thoughts on a DB choice

Post by Bone White »

Enoch wrote: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.

User avatar
JML
Dragon
Posts: 515
Joined: Mon May 31, 2010 7:03 am
Location: Blagnac, France

Re: Mote/MapTool: Thoughts on a DB choice

Post by JML »

Enoch wrote: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
Cave Troll
Posts: 68
Joined: Mon Nov 14, 2011 8:04 pm
Location: Fort Pierce, FL

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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.

User avatar
Jagged
Great Wyrm
Posts: 1306
Joined: Mon Sep 15, 2008 9:27 am
Location: Bristol, UK

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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" :)

User avatar
RPTroll
TheBard
Posts: 3148
Joined: Tue Mar 21, 2006 7:26 pm
Location: Austin, Tx
Contact:

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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.
ImageImage ImageImageImageImage
Support RPTools by shopping
Image
Image

User avatar
Jagged
Great Wyrm
Posts: 1306
Joined: Mon Sep 15, 2008 9:27 am
Location: Bristol, UK

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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 :)

User avatar
JML
Dragon
Posts: 515
Joined: Mon May 31, 2010 7:03 am
Location: Blagnac, France

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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 :wink:
    To rephrase this: it's not a comparison if you only look at one side :)

User avatar
Jagged
Great Wyrm
Posts: 1306
Joined: Mon Sep 15, 2008 9:27 am
Location: Bristol, UK

Re: Mote/MapTool: Thoughts on a DB choice

Post by 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.

User avatar
Jagged
Great Wyrm
Posts: 1306
Joined: Mon Sep 15, 2008 9:27 am
Location: Bristol, UK

Re: Mote/MapTool: Thoughts on a DB choice

Post by Jagged »

@JML

Jagged wrote: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 ;)

Post Reply

Return to “Feature Requests”