0

Unfortunately, all of my RDMS experience comes from using my company's proprietary database technology, and I'm struggling to find analogues that transfer my knowledge to PHP/MySQL practices.

In my company, we define "data dictionary" configurations that define tables, fields, keys, and such:

orders_Dct.cfg
trades_Dct.cfg
profiles_Dct.cfg

One such configuration file might look like this:

table
{
    name   ORDERS
    fields (ORDER_ID, ORDER_VS, PRICE, QUANTITY)

    key
    {
        name ORDERS_BY_ID_VS
        sort (ORDER_ID, ORDER_VS)
    }
}

field
{
    name ORDER_ID
    type CHARARRAY
    size 8
}

field
{
    name ORDER_VS
    type I32
}

field
{
    name PRICE
    type F64
}

field
{
    name QUANTITY
    type F64
}

Additionally, we define "static data", e.g. profiles_Static.cfg:

table
{
    name PROFILES

    entry
    {
        USER_ID    0
        USER_NAME  SYSTEM
        SUPER_USER Y
    }

    entry
    {
        USER_ID    1
        USER_NAME  TEST
        SUPER_USER N
    }
}

We then run commands such as Install, Remap, and LoadStatic to update the database based on these configuration files.

Now, I can write scripts of course, perhaps even replicating the way my company does things, but I think there must be some standard way to do this in PHP/MySQL. I wonder if I'm searching with the wrong terminology in mind, because based on terms like "mysql", "database", "dictionary", "configuration", and "rollout" I found only this question about a utility to create a database dictionary, which, at least on first glance, doesn't seem like what I'm looking for. I would like a simple, configuration-file-based approach to setting up database tables, fields, keys, and static data. Would someone point in me in the right direction?

If there is no such "standard" practice, then there must be a good reason, and I'm open to adapting to a different way of doing things. But it definitely seems silly to have to write a custom script for this purpose!

Thanks in advance.

Community
  • 1
  • 1
Andrew Cheong
  • 29,362
  • 15
  • 90
  • 145
  • 1
    I think you might be better of by describing what you want to achieve, not what you are currently doing. For instance, you want to create a database for a fresh install of something? Maybe with some default data in it? Or do you want to have an update-path for releasing a new develepment branch? Or is it a backup strategy thing? – Nanne Oct 25 '12 at 07:16
  • @Nanne - More for practice than for release, but I'm creating a site template from scratch. (Yes, I know about Ruby, Django, etc. but I wanted to try "reinventing the wheel" anyway.) The site template will always require certain tables, fields, and keys, and I'd like to be able to load in user data, product data, etc. from files I can hand-edit. – Andrew Cheong Oct 25 '12 at 07:24
  • A good keyword to google for is "database migrations"... – deceze Oct 25 '12 at 07:26
  • @Nanne - For example, say I realize I need to add fields, ``ORDER_ENTERED``, ``ORDER_EXPIRY``. I don't want to just PHPMyAdmin the change, because I may want to push this change to 10 different sites. I'd like to edit one configuration file, and run something on each site that would reflect the changes. I guess if I went with a script, I can edit the script, that's the thing--this has to be common enough that the "script" already exists--but perhaps I'm mistaken... – Andrew Cheong Oct 25 '12 at 07:28

1 Answers1

3

You're in luck! There's an entire language devoted to describing the creation of database tables and the insertion of data into them. It also supports the modification of existing tables.

This language is called.... SQL.

There's also a command, mysqldump, which can generate SQL from an active DB.

Amber
  • 507,862
  • 82
  • 626
  • 550
  • I appreciate the honesty, and I'll reconsider whether I really need more than just the basics, but I guess I thought there'd be a sort of utility or method that removes much of the redundancy of SQL. (Hm, or maybe my problem has more to do with readability...) – Andrew Cheong Oct 25 '12 at 07:33
  • 2
    SQL isn't actually all that redundant, and readability tends to be more a matter of formatting. Properly indented SQL is generally pretty easy to read. – Amber Oct 25 '12 at 07:34
  • Huh. After more thought, you're totally right. The answer was in front of me this whole time. My company's RDMS doesn't have a query language; everything is accessed through a C++ or Tcl API. Having gotten used to this, it didn't even occur to me that the ``*.cfg`` files I'm looking for, are pretty much translatable to ``*.sql`` files! Thanks! – Andrew Cheong Oct 25 '12 at 07:40