Menu

Hand-creating new data sources

2007-10-01
2013-04-23
  • Jonathan Lin

    Jonathan Lin - 2007-10-01

    Since the Database Manger wasn't in yet whenI started playing with this, I decided to try to add in new datasource "by hand"...hopefully this runthrough of how I did would be helpful.

    For someone that came from MySQL, I found it helpful to have understood this relationship for PostgreSQL:

    A server ("once" in our case) can have many schemas. Each schema can have many tables. As noted in Spdenne_work's thread, there is currently support for a single database. This means each new application would need to be placed into the same database (it'd be easiest to just keep it as "once", since there are security information in that database, that radix uses). So...for the people who come from MySQL, there's an extra layer of catigorization between database and tables.

    I used phpPgAdmin to create a new schema for my application (http://phppgadmin.sourceforge.net/), a very straightforward program to use. Very handy for those of you who don't know (or want) SQL statements (which isn't actually all that hard to learn. I recommend W3Schools, where I first leanred SQL (http://www.w3schools.com/sql/default.asp)

    In the once/model folder, you should find some .met files. These are your METAMODEL files (we'll come back to these later), which is used to form Relationships between each table. By default, you should have a data folder here too. If you've been playing with the prebuilt "contact" application, the files in here might look familar. These .mdl files are you MODEL files, which is the o:r representation of your tables. We'll look at these files first.

    LAYOUT OF THE MODEL FILE (for example, open up one of the .mdl files and its corresponding table, such as models/data/contacts_person.mdl and the "person" table in PgSQL)
    NOTE: I've altered the spacing here in attempt to get all the comments in properly. I don't think it actually matters to o:r, wiether or not we have spacing in the mdl files...
    [model]                 // "model" is a o:r keyword, and describes the model (this file)
    type = sql                    
    name = contacts_person  // name of this model. MET files will refer to these names, so have
                            // a good naming convention in place, so you don't get confused
                            // now. the metamodel will refer to the models by the filename I believe
                            // but it's probably a really good to name the filename and the model name
                            // the same
    description = blah
    version = 1.0           // not sure if these really matter, so I left them
    format = 1.0

    [location]              // maps to the location of the datasource itself
    database = once         // note that you can only connect to a single table per model
    schema = contacts
    table = person
                            // After the first two mandatory sections, we start on the columns
    [Title]                 // "Title" is an alias for the column person.title, and is how o:r
    field = title           // will refer to this field in the application
    type = string           // the datatype. I've seen "integer", "string" and "date"
    calculated = false      // didn't have any calculated fields. didn't touch this at all
    [First_Name]
    field = firstname
    type = string
    calculated = false

    Notes about table design: o:r seems to be expecting the primary key as an autoincrementor with an alias name of "primary"...as well as another field aliased as "Owner_Organization" ... I have no idea what owner organization would be for, but I added a blank column into my table to satisfy these conditions. Just note that your actual columns don't necessarily need to be called these. Just the aliases in the model does.
    Notes about datatype: I haven't tried it extensively, but it seems all SQL integer types is mapped to "integer", all string types to "string", etc...I've only seen these three fields so far...is there a list somewhere, of the exact mappings and how o:r treats them?

    Alright. Now, depending on how your tables are designed, you may or may not need to deal with JOINs. No idea what a JOIN is? Read it on W3Schools (http://www.w3schools.com/sql/sql_join.asp) who explains it very well. Basically, if you want to bring in relationships between models, you do it in your METAMODEL files.

    LAYOUT OF THE METAMODEL FILE (example: use models/contacts.met)
    [metamodel]                       // just like "model" in the mdl file    
    name = contacts
    description = blah
    version = 1.0
    format = 1.0

    [address]                        // here, we're creating a relationship map called
    model1 = data/contacts_address   // "address", which has exactly one model in it, which is
    condition =                      // "contacts_address". In this case, we don't have
                                     // any relationships (so we're only interested in interacting
                                     // with the contents of "contact_address" when we called on
                                     // "address", so there is no "join = " statement

    [branch]
    model1 = data/contacts_levelstructure
    model2 = data/contacts_branch
    condition =
    join = "contacts_levelstructure" INNER JOIN "contacts_branch" ON ("contacts_levelstructure"."fk_branch" = "contacts_branch"."primary")
    // however, here we are involving two models (hence two tables, as each model can only represent a single table)
    // and it's related because "contacts_levelstructure"."fk_branch" = "contacts_branch"."primary" (if you have no idea what this means, I strongly
    // suggest you read the JOIN stuff on W3Schools...or look into database table relationships, one-to-one and one-to-many)

    Note: notice that, since we specify where the mdl file is, you can create folders to help organization
    Note2: we're always refering to things by their MODEL names, not table names. I forgot that when I was creating my metamodels, and it didn't work
    properly
    Note3: you can check if you created your metamodel correctly! Log into o:r and enter ADMIN mode. Right click the screen and go to
       Tools > Check Metamodel Integrety
    Then scroll down to your metamodel file...if it works, it'll give you a whole bunch of OKs. If it didn't, it'll just say something like "check failed" ... in that case, just go back and make sure your files are all created properly.

    If everything went over alright, you should now be able to use this metamodel and its related models in the Add Data Source... under the o:r editor

    Since I only got all these by playing around with files myself, I might not necessarily have understood everything correctly, but this is how I got mine to work thus far, so...hopefully it'll save some people some time here, until the Database Manager is out.

     
    • onceradix

      onceradix - 2007-10-02

      Jonathan

      Congratulations on making the first contribution to the once:radix project! This is great to see.

      We'll review your work and will incorporate your ideas in our documentation.

      Rob Napier

       

Log in to post a comment.