Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

mapping complex structures to mysql tables

by niubi (Initiate)
on May 08, 2013 at 18:09 UTC ( #1032661=perlquestion: print w/ replies, xml ) Need Help??
niubi has asked for the wisdom of the Perl Monks concerning the following question:

Hi everybody,

I have a big perl website that has lots of configuration settings that are stored in lots of variables in a perl module (most of them are stored in several hash of hashes).

My boss wants to move all this configuration settings to a mysql database, the problem is that doing all this by hand is difficult and not recommended, as new settings are added/modified frequently (and also duplicated when a new section is added to the website).

I've suggested my boss to use XML, YAML or just plain Storable module to save all this settings to a text file, but he refuses these options, he doesn't want text files and want to store all these variables in MySQL.

My question is: is there anything (cpan module, etc) I can use to map these complex structures into MySQL tables and viceversa? (the most important goal for me is to easily read all the data from the tables to initialise all the settings variables I have)

Any help will be really appreciated. Thanks!

Comment on mapping complex structures to mysql tables
Re: mapping complex structures to mysql tables
by InfiniteSilence (Curate) on May 08, 2013 at 18:27 UTC

    I would add a method to my configuration module that exports a hash with configuration items as keys and the needed values as values. Then all you have to do is loop over the keys to create SQL statements you can run in the DB, like:

    INSERT INTO configs VALUES ('myKey','myValue');

    Celebrate Intellectual Diversity

      The problem with this is that I have hashes of hashes and also array of hashes. Also, the most important issue for me is reading all this information from the database tables to the original variables.
Re: mapping complex structures to mysql tables
by jnyman (Acolyte) on May 08, 2013 at 18:34 UTC
    Have you considered of storing the configuration variables as XML in the database? There also seems to be a mysql function called ExtractValue which you can use to select data with XPath.

      Have you considered of storing the configuration variables as XML in the database?

      Hmm, personally, I would be quite reluctant to this type of solution, because, in my experience, it is not very scalable.

      The very large company for which I work right now as a freelance consultant is using a large billing application running under Oracle. For various reasons which I ignore, it was decided years ago to store some configuration items as XML in a few database fields. No problem in principle, since this is relatively static data which can be loaded into memory every time the application is started.

      Except that these configuration XML objects grew over time. One of them is now about 75 MB and another one 30 or 40 MB, I don't remember exactly its size. This has become pretty much unmanageable for a number of purposes. For example, it has become practically impossible to just consult these with the available GUIs, SQL browsers and other tools available. It is not even possible to display the data and copy and paste it in a text editor or an XML browser.

      So, this idea of storing reference data into an XML object might have looked as a neat trick at the time the decision was taken, today, it has become a nightmare.

      In brief, the idea of storing reference data in an XML object within the database does not look right to me. Too many problems.

      My first idea for this problem would be that what is needed is some tool to parse the data structure (the HoH, HoA, etc.), or the files from where they are loaded, and translate that into an SQL data dictionary (I mean an SQL tables data structure reflecting somehow the current data structure). Once this is done, it should not be too difficult to load the data into a MySQL or other database. I do not know of any module to do that straight from the box, but combining existing CPAN modules might lead to a relatively light solution.

      An possible alternative solution would be a NoSQL database, together with one of the NoSQL DBI modules. The advantage is that a NoSQL data model (for what I know about it, i.e. quite little) has a structure usually more closely related with hashes and HoHs and the like (or JSON) than the SQL model, so that the translation from one to the other might be much easier.

        Yes, i can agree that XML is more like an easy fix and not scalable in that sense.

        Years ago, I stumbled into a very similar (legacy...) “kitchen-sink experience”and the basic problem turned out to be that all of the settings were being stored in one XML record/file.   The application would absolutely grind to a dead-stop.   But the actual solution turned out to be fairly simple:   carve off three of the top-level sections of the file (thus dividing the total into 200-odd subsections), and store each piece as an XML-formatted BLOB field keyed by a set of fields consisting of three top-level keys.

        Intuitively, I feel that the best overall solution to a problem like this one is to store the data in a BLOB (that is, a VARCHAR field of arbitrary size) in a “well-known format” ... JSON if you know you’re among friends, otherwise XML.   This compromise allows you to let the database handle storage, of all or a subset of the data, without condemning you to a hell of impossible-to-maintain code that tries to “wedge” arbitrary data into a rows-and-columns pure SQL format.   This is an extremely well-known and widely-used approach.

Re: mapping complex structures to mysql tables
by GrandFather (Cardinal) on May 09, 2013 at 01:39 UTC

    I use a simple key/value configuration table for site wide things such as webmaster email addresses. I find it hard to imagine site wide configuration data with a more complicated structure than key/value pairs that shouldn't be in a specific table of its own. Can you give examples of the sort of site wide configuration data that is problematic?

    If the configuration data only applies to part of the site it should be handled by the code that manages that part of the site and should either store the configuration in existing relevant tables, or should add configuration tables specific to the site area as required. It may be useful to write a base class that does most of the common heavy lifting for managing configuration data then provide site area specific derived classes as required.

    True laziness is hard work
      That's also what I do!
      Sorry, I can't provide any real life details about the data (I have signed a very tight NDA and the company I work for is very picky about that), but I can assure you that this configuration variables are needed and can't be simplified. Also, this settings define how a specific part of the website works and the number and types of settings vary dynamically, and each section could have different settings, that's why hardcoding is out of the question

      Currently, the site currently works as yout suggested solution (I have defined a class that sets all this configuration variables) but my boss wants the possibility that anyone can access and edit the settings without the need to know and/or modify any piece of perl code and he wants that information to be in MySQL.

        The significant part of my "solution" was:

        store the configuration in existing relevant tables, or should add configuration tables specific to the site area as required

        so if "the site currently works as yout suggested solution" then you have no problem.

        If "boss wants the possibility that anyone can access and edit the settings" that implies to me that you need to add some sort of administrative access configuration panel to your site. Allowing configuration by direct access to raw configuration data is a recipe for major disaster!

        True laziness is hard work
Re: mapping complex structures to mysql tables
by choroba (Abbot) on May 09, 2013 at 10:05 UTC
    This should show you how to store any complex structure into an SQL table (I used SQLite in the example). I chose a hash as the top structure:
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
      Thanks, this is a very nice piece of code that will be certainly useful as a starting point. I've also been pointed at DBIx class to implement this, I will look into this as well.

      Fantastic, that was just what I was looking for!

      I might suggest testing to see if the table exists first before creating it:

      if (do('SELECT 1 FROM t LIMIT 1')){ $db->do('CREATE TABLE t (key VARCHAR, value VARCHAR, ref INT)') }

      Also, some people may like comment out the "DROP TABLE" statement.

      Thanks for sharing your code.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1032661]
Approved by Happy-the-monk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2014-09-15 04:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (145 votes), past polls