Flextrine Tutorial – CRUD in a simple Flex 4 address book: Creating the database schema

Introduction
Setting up the server
Creating the entities
Creating the database schema
Loading the entities
Creating new entities
Deleting entities
Updating entities
Conclusion

Once we have defined the entities in the entities directory of the Flextrine server component it is very simple to create the appropriate database schema.

Creating the database schema

Browse to http://localhost/flextrinemanager (or whatever URL you pointed to the manager directory).  You should see a screen like this:

Flextrine Manager 1

Enter the URL of the Flextrine server side component (http://localhost/contacts) in the FLEXTRINE PROJECT URL input and Click to refresh.  The Flextrine manager will examine your project and update the manager to show the entities and database.  Now click on Create schema to automatically generate the appropriate tables in the database.  The manager should now look like this:

Flextrine Manager 2

Under the hood Doctrine 2 has parsed the annotations in ContactGroup and Contact and has determined the appropriate tables and columns that will be required to persist the objects.  Although you will never need to edit the schema manually, the following SQL is what was actually executed when you clicked CREATE SCHEMA.  Notice that Doctrine enforces referential integrity through MySQL constraints so that, for example, you cannot delete a ContactGroup which has any Contacts referring to it.

   1: CREATE TABLE  `contact`.`contactgroup` (

   2:   `id` int(11) NOT NULL AUTO_INCREMENT,

   3:   `name` varchar(100) NOT NULL,

   4:   PRIMARY KEY (`id`)

   5: );

   6:

   7: CREATE TABLE  `contact`.`contact` (

   8:   `id` int(11) NOT NULL AUTO_INCREMENT,

   9:   `name` varchar(80) NOT NULL,

  10:   `telephoneNumber` varchar(50) DEFAULT NULL,

  11:   `birthday` date DEFAULT NULL,

  12:   `contactGroup_id` int(11) DEFAULT NULL,

  13:   PRIMARY KEY (`id`),

  14:   KEY `contactGroup_id` (`contactGroup_id`),

  15:   CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`contactGroup_id`) REFERENCES `contactgroup` (`id`)

  16: );

Now that the schema has been created you can use UPDATE SCHEMA whenever you add/remove entities or attributes within entities to update the schema without losing any data that is already in the database.  This gives you a very efficient workflow as your entities expand over the lifetime of a project.

We now have everything we need in the Flextrine server side component and in the database.  Its time to start writing some Flex code!

9 comments

  1. Hello Dave,
    Thank you for your very very nice tool !

    I had compiled php 5.3 on my server instead of 5.2 :(, just to make it working but when clicking “create schema”, it says “could not find driver”, but can’t find more infos about this error. Perhaps you help me to figure out ?

    Thank’s again !

    Anthony

  2. Hey Antony,

    It sounds like you are missing the required drivers to use mysql with pdo, which are required for Flextrine (well, really Doctrine 2 DBAL) to work. Ensure that you have the pdo and mysql extensions enabled, or that you have compiled PHP with support for these modules.

    Let me know if you get it sorted!

    Dave

  3. Hi, I hava a problem with creating tables. I made everything in your tutorial but when I write in FLEXTRINE PROJECT URL my project url and click refresh I see PERSISTENT ENTITIES (voContact and voContactGroup), but when I press CREATE SCHEMA no tables are created in database but flextrine manager shows me that ‘Schema created successfully’ and I never see some PERSISTENT ENTITIES for this project 🙁 even I click refresh.

    Can you help me please.

  4. Hey Andrew,

    This is a known bug in Flextrine 0.7 – if you upgrade to the latest version (or use the SVN trunk) this should work properly.

    Dave

  5. hmm I really wanted to get going on this but I failed the installation…something to do with
    $flextrineIncludePath = “C:xampphtdocsflextrineweblib”;
    As i get Warning: require_once(Doctrine/Common/ClassLoader.php) [function.require-once]: failed to open stream: No such file or directory in C:xampphtdocscontactswebbootstrap.php on line 33
    when looking at:
    http://localhost/flextrinemanager/webroot/index.php
    no idea where i went wrong as ive re-read the instuctions many times.
    any ideas?
    Murray

  6. Hey Murray,

    Are you sure that you have installed Doctrine 2 and added it to your include path (or copied it into the Flextrine lib directory)?

    Cheers,

    Dave

Leave a Reply

Your email address will not be published. Required fields are marked *