SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database results and meta data

    How do people pass their meta data along with database results? (ie field type, length, default values, etc). I can think of a couple of ways:

    1. Arrays
    2. Object, with $data property and $meta property
    3. Object, with $data property that contains an array of meta information and values for each field

    Also, if a query returns more than one row, do you give each result object its own meta data, or have a single array of meta data and another array containing the results?

    Thanks,

    Cory

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    why are you trying to pass the meta data? if you're talking about a backup and restore type procedure, create a SQL dump using an appropriate tool, like mysqldump or phpmyadmin.

  3. #3
    SitePoint Addict Wildhoney's Avatar
    Join Date
    Apr 2006
    Location
    Nottingham
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm as confused as the person above me. Your description, I feel, is somewhat ambiguous. Could you please elaborate.
    TalkPHP.com - The Friendly PHP Community

    Watch Reaper Online - Watch Chuck Online

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by allspiritseve View Post
    How do people pass their meta data along with database results? (ie field type, length, default values, etc).
    with the CREATE TABLE statement

    as mentioned, this is produced by the dump

    or, you can alternatively generate it with SHOW CREATE TABLE tablename
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am talking about when data is displayed from the database. Some people use a DESCRIBE query, others (like me) keep this data in a class that extends from an abstract table gateway. This way, in a template, I can output the results without the template knowing what columns are in the result. If its a text field, display a text box, if its a longtext, display a textarea, etc.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you're assuming that your fields ALWAYS have a 1-to-1 relationship with the controls on your forms. for me, it's usually only 50% of the fields, so i don't have experience in this field.

    if you're a PHP developer, i suggest asking in the PHP Application Design subforum.

  7. #7
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, well do you hand-code all of your templates then? I'm not concerned with where the meta data comes from, but some sort of structural data has to be passed with the results to a template, unless you have explicitely coded every template by hand for that specific resultset. It is hard to believe that this process wouldn't be automated at least a little bit.

  8. #8
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Obviously I have not been explaining my question correctly. Let's try this again.
    * * *

    For those of you who use templates: How do your templates know what to display?

  9. #9
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If I were to do it, I'd have a mysql table holding table, field and type columns (and an auto incrementing primary key, of course).

    That way you can call to the table, check how to display it and so on. You could also specify that field to be invisible. Of course, you would need functions to handle different types of inputs.

    But I do it manually - mainly because all my code is custom-made, so there would be no need to allow it to adapt to major changes.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  10. #10
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is essentially what I am doing, though my system is a bit more complex. So complex, actually, that its a bit over my head how to properly implement it, so I'm looking for a simple model to extrapolate from-- however, it appears everyone in this forum creates all their templates manually as well, so it seems I may be out of luck.

    Quote Originally Posted by arkinstall View Post
    If I were to do it, I'd have a mysql table holding table, field and type columns (and an auto incrementing primary key, of course).

    That way you can call to the table, check how to display it and so on. You could also specify that field to be invisible. Of course, you would need functions to handle different types of inputs.

    But I do it manually - mainly because all my code is custom-made, so there would be no need to allow it to adapt to major changes.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you could read the information_schema views to figure out what the table looks like
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm...

    I was asked to help out here so reading the posts the only thing that I would ask is why do you feel you need to automate your template creation in regards that your tabular structure would be based on your database schema?

    Even if in your tabular structure you have a number of columns to be displayed based on your schema, I imagine you would have other tabular columns - in your template structure - that are not even related to your schema it's self...

    In some case, you couldn't even display all columns either, so what which do you disregard? I can only see that you have two options in this area,

    First, take a look at Ruby on Rails, and try to develop something which will generate your templates during a build process, or... Build the template structure as is dynamically based on your schema.

    The second option would be easier to develop but you'd take a hit on performance, more so if you've not cached your schema data. But I'm left thinking is it really so much trouble to manually maintain your templates, though I don't know your particular situation.

  13. #13
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are two ways, the view (the template) could know how to represent a value from the model (a dataset). Either the information is embedded within the view or you use some sort of reflection to determine it. There are pros and cons of each pattern. In the first case, you would get some duplicate code, while in the latter case, you would get a tight coupling between view and model.

    You can of course make a hybrid solution, where you default to use reflection, but allow a manual override. While this sounds like the best of both worlds, it presents a problem of higher complexity; This is a more intangible problem, so it may not be as easy to see, but it's still there. There's of course the forth option, which is code generation. This may look like a variation of (1), but it's really just a static-time variant of (2). I'd rule this approach out because it's way over-complicated and inflexible.

    As longneck said, 1:1 mapping isn't something you can rely on, outside of hello world, so in actuality, you only have the choice between hardcoding (1) and default-to-reflection (3). I have been using the latter approach, but now, I generally prefer the minimalistic approach. Which is better, is a matter of temper and depends a lot on the application you're building, so if you have a good feeling for it, go ahead. Just keep in mind, that it may not fit all cases. For example, you might want to go for default-to-reflection on a backend interface, and strictly hardcoded for the frontend, if you're building a CMS style application.

    Regarding implementation of the reflection approach, it's important that you make an effort to reduce the coupling. You're crossing layers, which can potentially be compromising your applications design. Use aggregation, instead of inheritance and avoid concrete class dependencies; You can use factories or dependency injection for this. If you like interfaces, this is a really good place to throw them into the mix.

    I would suggest, that you keep the meta-data together with the dataset (the row), rather than the gateway, although the row may simply have a link back to the gateway to get this information on-demand. You should probably let the row be an object, which implements ArrayAccess, so it can pass as a regular associative array. You could then have the view use a helper to introspect a row and return meta data from it. This is similar to a design, I've used once.

  14. #14
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston View Post
    why do you feel you need to automate your template creation in regards that your tabular structure would be based on your database schema?
    Well, a couple of reasons... First off, this is a CMS in which I have a content structure similar to what ezpublish uses, that basically holds content in virtual tables and the content structure. The idea is to allow content types to be created quickly through an admin interface and instantly have CRUD operations available-- CRUD operations that appear as if they are dedicated to that content type, but really use the same template as any other content type-- just changing based on the meta data. I'm also doing it because it's enjoyable, and it is really fun to add new content types, say, a blog entry, and see the form automatically construct itself without any hard-coded knowledge of the specific type it is displaying.

    Quote Originally Posted by Dr Livingston View Post
    Even if in your tabular structure you have a number of columns to be displayed based on your schema, I imagine you would have other tabular columns - in your template structure - that are not even related to your schema it's self...
    Maybe later on, definitely on the front end... but on the back end, for managing content types, everything is related to the schema.

    Quote Originally Posted by Dr Livingston View Post
    I'm left thinking is it really so much trouble to manually maintain your templates, though I don't know your particular situation.
    Probably not, but its a nice thought that I could install a client's website with custom content that can be changed on demand in less than an hour, without having to mess around with changing actual db schema or modifying code to include the new fields. That leaves me to concentrate on the design of the site, and any additional functionality they may want, instead of messing around with the little things.

    Quote Originally Posted by kyberfabrikken View Post
    In the first case, you would get some duplicate code, while in the latter case, you would get a tight coupling between view and model.
    In the application as a whole, maybe, but if the view is only concerned with CRUD operations, does the tight coupling really matter? Maybe on the front-end, where content is meant to be viewed in various forms, as opposed to managed, the view won't have as much need to stem straight from schema.

    Quote Originally Posted by kyberfabrikken View Post
    I would suggest, that you keep the meta-data together with the dataset (the row), rather than the gateway, although the row may simply have a link back to the gateway to get this information on-demand. You should probably let the row be an object, which implements ArrayAccess, so it can pass as a regular associative array. You could then have the view use a helper to introspect a row and return meta data from it. This is similar to a design, I've used once.
    This is very helpful, as I'm much more concerned with implementation of the meta-data, rather than where it comes from.

    Thank you, everyone, for your responses. I guess I am attempting something that nobody else really does, or cares about, and I'm not really sure whether that means its just too complicated, or that's redundant and useless, but either way I guess I'm on my own.

  15. #15
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Why not generate the form when you create the tables, instead of generating on the fly?
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  16. #16
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I am meddling with something somewhat similar for a simple ajaxified framework concept. I am working at a simple level and using a single table CRUD class, Not wanting to go the ORM or Metabase (xml config) route, I am just reading the values from an ini file for each table.

    PHP Code:
    //example People model ini file:
    //people.boot.ini - config options for the table `people`

    [fields]
    // field name, type for PDO
    person_id INT 
    person_name 
    STR 
    address 
    STR 
    I don't think it would be onerous to add a form field type:

    PHP Code:
    [fields]
    person_id =INT 
    person_name 
    =STR
    address 
    STR 

    [forms]
    // person_id -they cant set that
    person_name text,required
    address 
    textarea 


    //etc 
    I just haven't got round to linking the views to the ini file yet, but I think that's how I will be doing it.

    Eventually I can see how I could make a wizard-like front end that would create the basis of this file from the meta data, as you say, but then with a gui that allows my input to dictate field types for forms etc.

    I'd be interested to hear what you think...

  17. #17
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    I just haven't got round to linking the views to the ini file yet, but I think that's how I will be doing it.

    Eventually I can see how I could make a wizard-like front end that would create the basis of this file from the meta data, as you say, but then with a gui that allows my input to dictate field types for forms etc.

    I'd be interested to hear what you think...
    Hmm... it is similar, except if I'm reading your post right, your gui is for editing forms while leaving schema alone, whereas in my system editing the schema is editing the form. I haven't quite understood yet the downside of generating a form based on ini files/meta data (aside from a small performance loss) but here's to finding out the hard way, I guess...

  18. #18
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)

    yes, interesting thoughts ...

    Quote Originally Posted by allspiritseve View Post
    Hmm... it is similar, except if I'm reading your post right, your gui is for editing forms while leaving schema alone, whereas in my system editing the schema is editing the form. I haven't quite understood yet the downside of generating a form based on ini files/meta data (aside from a small performance loss) but here's to finding out the hard way, I guess...
    I think you have raised a good point there. My wizard/gui thing could actually write the db schema too, gotta be some code in PhpMyAdmin I could look at for starters.

    I just haven't thought that far ahead yet.

    Just so we are clear, this is what I understand, correct me if I am wrong...

    Your scenario:
    ---------------------------------
    edit this by hand
    |
    V
    db schema ---> extract meta data -> create forms automatically
    ---------------------------------

    My imaginary scenario (now I have spoken to you ):
    ---------------------------------
    GUI wizard --> db schema
    |
    V
    ini file <--- View extracts form description
    <--- Model extracts type for PDO insertions, checks a valid field name
    ---------------------------------

    I think I mindful of not ending up with a db schema with table names like
    `name_required` because eventually when you get into these automated generators some of the real value lies in where/how data can be "somewhat" validated ( well, at least cleansed before being inserted ).

    Whereas all I truthfully have at the moment is:
    ----------------------------------
    ini file <--- Model extracts type for PDO insertions ensures valid field name
    ---------------------------------

    Plugging that into a generic query generator does all my updates/extracts on a SINGLE table, no matter what, but only on the fields specified in the ini file.

    Thanks for giving me the ideas though ...

  19. #19
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The benefit of using reflection is, that you can draw on information, which is already there. What you're proposing, is to use configuration to describe all three layers of the form. I'm a bit sceptical about using configuration, because it makes for very restrictive applications. The typical benefit of configuration is to allow non-programmers to manipulate the application. In this case, the one going to use it, is most likely going to be the same programmer, who is writing the application, and so you might as well create a syntax within the boundaries of the language (in PHP), instead of creating your own syntax.

    Reflection or configuration, this reminds me of naked objects. You might want to look further into that, for some inspiration?

  20. #20
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Naked Objects. How interesting (especially as I work on the fringes of Government, local govt in fact).

    Quote Originally Posted by NakedObjectsOpenSourceSite
    Naked Objects is an open source Java-based application development platform. It’s called Naked Objects because all you need to develop are your domain objects - the Naked Objects platform auto-creates an object-oriented user interface (giving you the choice of different styles) and the underlying database (using Hibernate)
    I wonder if its been ported to PHP, though I don't think that's where I am going exactly.

    What I do find I have in common with the description on the wiki link you made is that I sometimes I just find MVC a layer one too many - and as many have suggested in the past on the PAD forum, if the problem doesn't need three layers, then morph two of the layers. Having fallen across Fowlers PresentationModel I thought it would be a fun project to link that idea with Ajax for simple crud applications where behaviour in the view can be controlled from that one place, the PresentationModel.

    In fact the amount of richness you can add to a view is pretty amazing ( by manipulating cookies client side and server side ).

    I should mention though that this idea is for Admin areas, where I know users have JS on, are logged in etc.

    Yes, you are correct, I would be the one configuring the ini files, so a wizard is just a nicety. I will just see if the idea works and saves me some time first though - but the idea of Admin users adding fields has some interesting possibilities ...
    Last edited by Cups; Dec 10, 2007 at 12:08.

  21. #21
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cups- I don't know if what my system does is really hand-inputting the schema. It is done through a GUI interface.

    Kyber- I don't know if I like the auto-generation idea presented by Naked Objects. I don't really want forms generated by a script. I just see that information that is already there, and want to use it. I can see how that results in tight coupling of layers, but maybe my boundaries are just different? I have clearly delineated controllers, I have controllers that are aware of their views, I have a model that is aware of neither, and I have templates that expect data, and expect information about that data, but have no idea what the specific implementation of that data is. Isn't that decent enough encapsulation that you could rule out calling it a one-layer architecture?

    longneck- I was thinking, would you be able to demonstrate a sample db schema you use, and a form that goes with it? I am curious to see how it differs from my implementation, and whether I can figure out a way that my system could do something similar without hacking it.

  22. #22
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think that regardless of your chosen implementation, you will have to touch the form to edit it manually at some point in time. It seems like what you are looking for is scaffolding, which I am surprised no one has mentioned so far. Ruby on Rails was mentioned, as it does have a pretty nice scaffolding script that will generate CRUD forms for you based on the database schema. But the point of that scaffold generation is that it's a good starting point. In every Rails app tutorial or book you will ever read, you are always guided through the process of editing and touching up the form that Rails generated. I think finding that "holy grail" of form generators will be very hard to achieve simply because of the human factor. A generated form isn't likely to be the most user-friendly one, so at some point you're going to have to do some manual editing anyways.

  23. #23
    Spirit Coder allspiritseve's Avatar
    Join Date
    Dec 2002
    Location
    Ann Arbor, MI (USA)
    Posts
    648
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly. I'm not trying to automate everything, I just want to use it as a starting point.

  24. #24
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, scaffolding is a good way to go. You can have view definitions for modules written in simple style, something as:

    Code:
    articles.views.ini
    [default view]
    title=text
    intro=area
    maintext=area
    rating=option(super,good,bad)
    categories=select(art_cat,id) // for dynamic queries
    From this INI you can easily generate template.

  25. #25
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sceptical towards code generation. If you change the generated code, and then later add a field, you'll overwrite your changes.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •