how can codeigniter create a db schema from a model is there a plugin

[eluser]Randy Casburn[/eluser]
This is why I don't use humor in my //comments// heheh // because the next nerd would take it completely out of context!

I was the meany...I was implying I was the one that didn't like the guy.

It was all in fun :vampire:

[eluser]Colin Williams[/eluser]
Hahahaha... I'm the one who came off wrong. I knew what you were saying and I took the opportunity to self-deprecate myself as well!

Forums do stink.

[eluser]elrolfe[/eluser]
hi guys,
i recently set up a little tool using CI that allows me to describe my db-table in yml-files. the script takes these files and creates the db-tables using db-forge.
in order to work you need to set up routes, create yaml-files, load a library and use a controller. its very beta and not tested (it worked at first look...).
if youre interested i can post the files. but i have to say that im not the einstein of php-coding and wont have the time to support you. to make it worse: all comments are german

[eluser]Sally D[/eluser]
You guys really are convinced that I don't know my way around a sql prompt wow! Well I do understand sql and how to manually create a table pretty easily

@randy why did you say I don't want to learn sql when I watched over 10 hours of videos of how to construct sql statements on the web all ready so I am familiar with the create table sometable (count int, note text);

all I would have to do is loop through an object, xml or an array and echo this text to an open mysql connection and wallah a database table will be created for me



here is the paradigm that will start the table creation process. You will pass it an object, xml or array that you set up in a model. Then in your controller you would make a function called make_db and this functions argument will be fed a call to the method you made in your model that returns the schema array. From that array the make_db library class will build the db real easily. I can make this in a matter of an hour really straight forward and simple. Then I can start working development with out messing around with sql or another application I won't have to leave CI for anything.

and If I want to share a model with you all I would need to do is include a schema with my model and then if you had my make_db class you would have the table all set up with out the need to use another app like php my admin or something else

function make_db($this->Model_name->return_schema()){
// creation of table takes place here and show details of out put to user
}

@elrolfe I would love to see your class I understand php and I can read it well

I really like CI I don't know what I did to Randy to make him not like me. You don't even know me who I am or what I do?

And thanks for the Forge link that is just what I needed to make the ace go into the hole

[eluser]elrolfe[/eluser]
Hi Raymond,
youll need yayparser to get this working. i created a directory called structure in the application dir, that holds all yml-files.
i hope code-pasing works this way...
feel free to use it or to work on it. if you have any ideas about what to do better, please tell me

the library:

Code:

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

class ymlcrud
{
    /**
     * params
     * library-Parameter
     * @var object
     **/
    public $params;
    
    
    
    
    function __construct()
    {
        $this->CI = &get;_instance();
        
        $this->params->showProfiler     = true;
        $this->params->ymlDir             = base_url().'structure/';
        $this->params->ymlFileExtension = '.yml';
    }
    
    
    
    
    
    /**
     * loadYmlFileAsArray
     *
     * @return array
     **/
    public function loadYmlFileAsArray($fileBaseName)
    {
        return yayparser(utf8_decode(file_get_contents($this->params->ymlDir.$fileBaseName.$this->params->ymlFileExtension)));
    }
    
    
    
    

    
    /**
     * showProfiler
     *
     * @return bool
     **/
    public function showProfiler()
    {
        return $this->params->showProfiler;
    }
}
// END ymlcrud Class
?>

the controller

Code:

<?php  if (!defined('BASEPATH')) exit('No direct script access allowed');

/**
* generator
*
* @package ymlcrud
**/
class generator extends Controller {
    
    private  $table;
    private  $fields = array();
    
    
    
    
    /**
     * __construct
     *
     * @return void
     **/
    function __construct()
    {
        parent::Controller();
        log_message('debug', 'generator controller initialized');
        $this->load->dbforge();
    }
    
    
    
    
    /**
     * index
     * stellt uebersicht dar mit generator-links
     * @return bool
     **/
    function index($table='structure')
    {
        $data['yml'] = $this->ymlcrud->loadYmlFileAsArray($table);
        $this->load->view('generator_view', $data['yml']);        
    }
    
    
    
    
    /**
     * createTable
     * erstellt eine tabelle mit allen feldern. wenn es die tabelle mit dem namen gibt, wird zuvor ein backup ("altername_timestamp") angelegt
     * @return void
     * @param $table string die zu erstellende tabelle
     **/
    function createTable($table)
    {        
        $data['yml'] = $this->ymlcrud->loadYmlFileAsArray($table);
        
        
        if (in_array($table, $this->db->list_tables()))
        {
            $this->dbforge->rename_table( $data['yml']['table'],$data['yml']['table'].'_'.time());    
            $this->modifyTable($table);        
        }
        
        
        
        $this->dbforge->add_field('id');
        foreach ($data['yml']['fields'] as $name => $field)
        {
            
            switch ($field['type'])
            {
                #einfaches textfeld
                case 'textfield':
                    $this->dbforge->add_field(array($name => array('type' => 'VARCHAR','constraint' => '255', 'null' => 'TRUE')));
                    break;
                
                #textarea    
                case 'textarea':
                    $this->dbforge->add_field(array($name => array('type' => 'TEXT', 'null' => 'TRUE')));
                    break;
                #password
                case 'password':
                    $this->dbforge->add_field(array($name => array('type' => 'VARCHAR','constraint' => '255', 'null' => 'TRUE')));
                    break;
                
                #datumsfeld
                case 'date':
                    $this->dbforge->add_field(array($name => array('type' => 'DATE', 'null' => 'TRUE')));
                    break;
                
                #einfachauswahl per select
                case 'select_one':
                    $this->dbforge->add_field(array($name => array('type' => 'INT', 'constraint'=>'9', 'unsigned' => 'TRUE', 'default'=>'0')));
                    break;

                #mehrfachauswahl per checkboxen (als csv in textfeld speichern)
                case 'select_many':
                    $this->dbforge->add_field(array($name => array('type' => 'TEXT', 'null' => 'TRUE')));
                    break;
                
                case 'number':
                    $this->dbforge->add_field(array($name => array('type' => 'INT', 'constraint'=>'9', 'unsigned' => 'TRUE')));
                    break;
                
                // case 'float':
                //     $this->dbforge->add_field(array($name => array('type' => 'FLOAT', 'constraint'=>'9', 'unsigned' => 'TRUE')));
                //     break;
                
                #enum aus yml
                case 'radio':
                    $this->dbforge->add_field(array($name => array('type' => 'VARCHAR','constraint' => '255', 'null' => 'TRUE')));
                    break;
                
                
                default:
                    $this->dbforge->add_field(array($name => array('type' => 'VARCHAR','constraint' => '255', 'null' => 'TRUE')));
                    break;
            }    
        }

        $this->dbforge->create_table($data['yml']['table']);

        $this->load->view('generator_result_view', $data['yml']);
    }
    

}

[eluser]elrolfe[/eluser]
an this is a sample stripped-downyml-file... you can put config/params in there too.

Code:

table: people
label: People

fields:    
    name:
        label: name
        type: textfield
        validators:
            - required    
    email:
        label: E-Mail-Adresse
        type: textfield
        validators:
            - email
            - required
    
    password:
        label: Password
        type: password
  
    description:
        label: description
        type: textarea
        params:
            -editor
            
    birthday:
        label: Your Birthday
        type: date
        validators:
            - required
            - valid_date
        options:
            -readonly
            
    fav_food:
        label: Your Favourite Food
        type: select_one
        fTable: food
        fTableId:id
        fTableLabel: name
        fTableSort: essen.name DESC
        
    friends:
        label: Your Friends
        type: select_many
        fTable: people
        fTableId: id
        fTableLabel: name
        fTableSort: name DESC

[eluser]Sally D[/eluser]
@elrolfe Thanks I will examine your work very carefully like a shaman examines a vision

I will come up with a nice db utiliy that gets a design and builds the db so we can build a depository for db designs for CI and just use them in our models

also if you can send a design template in your models it will make collaboration between two people easier

[eluser]Sally D[/eluser]
Hi all this is what my brain came up with I don't know if it's logical or not that is why I am posting it here.

I did not want to mess around with yaml cause I don't have pear library's installed on my machine to make it work since its not native on php 5 yet, so I thought of the next easiest thing INI files

for each table in your db you would create an ini file the name must match the name of a table in a db and the result array much match the array the $this->dbforge->add_fields().

main controller

Code:

<?php
class Blog extends Controller {
    function Blog()
    {
        parent::Controller();    
        $this->load->model('Blog');
        $this->load->library('db_utils');
    }
    function index()
    {
        $this->load->view('welcome_message');
    }
    function set_up_db(){
        $table_names=$this->db_utils->get_tables();
        $this->Blog->make_tables($table_names);
    }
}
?>

model

Code:

<?php
class Blog extends Model {
    function Blog() {
        parent::Model();
        $this->load->library('db_util');
    }
    
    // this function takes and array of db tables names you
    // want to create. The names of the table corresponds to
    // names of ini files created for each table
    // array('blog','comments')
    // would mean you would need a blog.ini and comments.ini
    // that would describe the data type for each field in the table
    function make_tables($tables_array) {
        $this->db_util($tables_array);
        
    }
}
?>

db util library

Code:

<?php if(!defined('BASEPATH')) exit('No Direct access');
class Db_util {
    function Db_util()
    {
        $CI=& get_instance();
        $this->load->dbforge();
    }
    function get_tables(){
        // array of files in the ini dir
        $files = array();
        // path to db ini files
        $path=base_url()."/db_ini";
        $handle=opendir($path);
        chdir($handle);
        while ($file !== false) {
            $file=readDir($handle);
            $files[] = $file;
        }
        $temp = preg_grep("/ini$/", $files);
        $table_names=array();
        foreach($temp as $val) {
            $x=substr($val,-4,4);
            $table_names[]=$x;
        }
        return $table_names;
    }
    function make_tables($array){
        foreach($array as $value) {
            $ini_url=base_path()."/db_ini/$value.ini";
            $fields = parse_ini_file($ini_url, true);
            $this->dbforge->add_field($fields);
            $this->dbforge->create_table($value);
        }
    }
}
?>

you would have to make an ini file for each table in the database like this and then store them in a db_ini Directory

; database ini
; this is a database schema for that works with the
; codeigniter forge db class
;
;


[blog_id]
type=int
contraint=5
unsigned=true
auto_increment=true

[blog_title]
type=varchar
contraint=100

[blog_author]
type=varchar
constraint=100
defualt=me
[blog_description]
type=text
null=true

I had this revalation in my sleep so I don't know if it's logical to make an ini file for each table in a database or not

any way I got a job interview at 10 am and then it's off to myrtle beach, sc for a vacation I will play around with this there

I hope you can tell me if this approach is logical or not

[eluser]elrolfe[/eluser]
Hello again,

at first look I would say we go the same way here. you chose ini, i preferred yml. either way we have table-schemes which need to be interpreted.
so far so good, the next thing to do is changing the structure of the tables as you sometimes have to do in mid-project without deleting data.

btw im off to vacation for 10 days now. have a good time.
lets talk about it again when were both back.

[eluser]m4rw3r[/eluser]
Nice to see that my parser helped

I think this is also one thing I'll have to add to IgnitedRecord, maybe as a side module (which probably remind of RoR's db migrate).
But I will probably wait for the improved db abstraction in the db forge class before I start on my project.