A simple task to generate sql scripts

Published on and tagged with bake  cakephp  sql  task

To make it a bit easier to write the sql scripts for creating and dropping tables, I wrote a simple bake task. There isn’t much to say about it, so I show you just an example.

The following call:

php bake2.php sqlscripts users profiles profiles_users

creates app/config/sql/create.sql:

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT, 
  created DATETIME, 
  modified DATETIME, 
  PRIMARY KEY (id) 
); 

CREATE TABLE profiles (
  id INT(11) NOT NULL AUTO_INCREMENT, 
  created DATETIME, 
  modified DATETIME, 
  PRIMARY KEY (id) 
); 

CREATE TABLE profiles_users (
  profile_id INT(11) NOT NULL, 
  user_id INT(11) NOT NULL, 
  PRIMARY KEY (profile_id, user_id)
); 

and app/config/sql/drop.sql:

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS profiles;

DROP TABLE IF EXISTS profiles_users;

That’s it. You can find the code in the download section. Happy baking :)

11 comments baked

  • Dieter@be

    That’s nice!
    but on the other hand, maybe it’s time to evaluate our workflow and see if we can’t do it better.

    I mean, bakers current workflow is:
    1)set up a “structure”
    a)directly by creating tables, editing them, etc manually in a pogram like phpmyadmin
    b) by working with a program like database designer which is basically the same as option a) but a layer of abstraction higher.
    in either case, we end up with a database layout in our development environment.
    2)if we want our app in production, we need to get an sql export of our dev environment, and import it in the production environment. your task helps a bit here. so we are basically working with raw sql code that first gets exported, and then imported (hopefully) successfully.

    I’m thinking it might be better if we could just define our database layout in a way that is both abstract enough so we can juggle with it without becoming a pita, but also detailed enough so we can be sure the sql (and maybe php) generated will be exactly what we expected. I just have to think here at the orm-tool hibernate, that has xml files for each model that is already defined in java code, this means some works need to be done twice, but it works well. (in the current cake system, we also do work twice: create an sql layout + write php modes, which have some overlappings in the properties they define)

    I am in favor of a workflow that starts with xml files to generate php (only the structure, the memberfunctions should be left alone offcourse) and sql from that.
    Or maybe even better (?): we could even define all these details in the php models themselves. since we have to define FK and other details in there anyway. Like this we wouldn’t even need raw sql dumps, we could have cake tasks that would just know exactly what sql to generate, given the right php models.

  • cakebaker

    @Dieter@be: Thanks for your comment.

    First a correction: The task is useless in the scenario you described (sql export of dev environment and import in production environment). It is intended to be used for creating the skeleton of the table creation script for the dev environment.

    I am not sure if there should be some abstract level from which the database is generated. The only advantage of such an abstract level I see at the moment is when you want to support different databases. But I think most applications are built for just one database. So I think it is not worth the additional complexity.

    And concerning the workflow: for me it is more logical to start at the bottom layer and to move upwards, than to start on a middle layer and to create the bottom layer. If I start on a middle layer I am either restricted in what is possible (I possibly can’t use a nice feature specific to a database) or the middle layer is much more complex as it contains the complexity of both layers.

  • Dieter@be

    Well, you have some good points, if the task is intented to do just that, keeping the sql DDL in sync with the php cake models is just a small sacrifice for the dev. (no matter the solution, there is always a small sacrifice/compromise the dev has to make) I’m not going to forget my “all-DDL-in-php idea though ;-)

    On the other hand, it would be nice to have a task that is able to import/export the complete contents of a database.. By the time i start to use cake 1.2 i will maybe edit your script to give it the extra functionality. (if nobody did it before me) :-)

    Dieter

  • cakebaker

    @Dieter@be: Yeah, keep your “all-DDL-in-php” idea, experiment with it, and build a cool solution ;-)

  • bryn

    Anybody have any suggestions on how to make this work with Cake 1.2?

  • Bryn

    Anybody have any tips on how to make this work with Cake 1.2?

  • cakebaker

    @Bryn: I uploaded a new version to the downloads section. It is no longer a bake task but a cake shell script. The usage is now

    cake sqlscripts table1 table2...
    
  • Bryn

    Thanks it works great! (sorry about the double post previously)

  • cakebaker

    @Bryn: Cool to hear that :)

  • Matt Kaufman

    Thanks for this genius script…. This is the most useful thing for SQL in the world! :)

    Haven’t seen anyone do anything this simple and awesome for this. great thinking / solution.

  • cakebaker

    @Matt: You are welcome!

Bake a comment




(for code please use <code>...</code> [no escaping necessary])

© daniel hofstetter. Licensed under a Creative Commons License