[nekalv]

Hi everyone :)

I'm would like to contribute with this snippet of code to use Grocery CRUD with sql server using sqlsrv driver for CodeIgniter.

Source: https://github.com/scoumbourdis/grocery-crud-databases

<?php
class grocery_crud_model_SQLSRV extends grocery_CRUD_Generic_Model {
  /**
   * Get field types from basic table 
   * @return array list of field with meta-data from database
   */
  function get_field_types_basic_table()
  {
        $db_field_types = array();
        //thanks to marc_s for this nice query
        $show_colums = "SELECT 
                            c.name 'field',
                            t.name 'type',
                            c.max_length 'max_length',
                            c.precision ,
                            c.scale ,
                            c.is_nullable,
                            ISNULL(i.is_primary_key, 0) 'primary_key'
                        FROM    
                            sys.columns c
                        INNER JOIN 
                            sys.types t ON c.system_type_id = t.system_type_id
                        LEFT OUTER JOIN 
                            sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        LEFT OUTER JOIN 
                            sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                        WHERE
                            c.object_id = OBJECT_ID(?)
                        AND t.name <> 'sysname'";

        $rows_metadata = $this->db->query($show_colums, array($this->table_name));

        foreach ($rows_metadata->result() as $db_field_type) {
            
            $db_field_types[$db_field_type->field]['db_max_length'] = $db_field_type->max_length;
            $db_field_types[$db_field_type->field]['db_type']       = $db_field_type->type;
            $db_field_types[$db_field_type->field]['db_null']       = ($db_field_type->is_nullable == 1) ? true : false;
            $db_field_types[$db_field_type->field]['primary_key']   = $db_field_type->primary_key;
            $db_field_types[$db_field_type->field]['name']          = $db_field_type->field;
            $db_field_types[$db_field_type->field]['db_extra']      = $this->check_db_extra($db_field_type);
        }
        
        $results = $this->get_field_types($this->table_name);

        foreach($results as $num => $row)
        {
            $row = (array)$row;
            $results[$num] = (object)( array_merge($row, $db_field_types[$row['name']])  );
            $results[$num]->type = $results[$num]->db_type; 
        }
        
        return $results;
    }

    /**
     * Check id field is identity and assign extra properties to it
     * @param  object $db_field_type field meta-data
     * @return string extra property
     */
    public function check_db_extra($db_field_type)
    {   
        $extra = '';
        return ($db_field_type->primary_key === 1) 
                ? $extra = 'auto_increment'
                : $extra = '';
    }
   
}

Use the controller on this source and tested, hope you can give me some feedback, I'm very new at this :).

PD: sorry for my bad English,

[davidoster]

Hello and welcome to the forums [member=nekalv].

Thank you for your contribution.

Why don't you try to push it to github?

 

Anyway we will let [member=web-johnny] know about this. Thanks for the contribution again.

[nekalv]

HI davidoster... i'm a total newbie and don't know how to use github :/

Actually i found a bug in 1-n relation this afternoon and add a small fix to the snippet...

<?php
class grocery_crud_model_SQLSRV extends grocery_CRUD_Generic_Model {
  /**
   * Get field types from basic table 
   * @return array list of field with meta-data from database
   */
  function get_field_types_basic_table()
  {
        $db_field_types = array();
        //thanks to marc_s for this nice query
        $show_colums = "SELECT 
                            c.name 'field',
                            t.name 'type',
                            c.max_length 'max_length',
                            c.precision ,
                            c.scale ,
                            c.is_nullable,
                            ISNULL(i.is_primary_key, 0) 'primary_key'
                        FROM    
                            sys.columns c
                        INNER JOIN 
                            sys.types t ON c.system_type_id = t.system_type_id
                        LEFT OUTER JOIN 
                            sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        LEFT OUTER JOIN 
                            sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                        WHERE
                            c.object_id = OBJECT_ID(?)
                        AND t.name <> 'sysname'";

        $rows_metadata = $this->db->query($show_colums, array($this->table_name));

        foreach ($rows_metadata->result() as $db_field_type) {
            
            $db_field_types[$db_field_type->field]['db_max_length'] = $db_field_type->max_length;
            $db_field_types[$db_field_type->field]['db_type']       = $db_field_type->type;
            $db_field_types[$db_field_type->field]['db_null']       = ($db_field_type->is_nullable == 1) ? true : false;
            $db_field_types[$db_field_type->field]['primary_key']   = $db_field_type->primary_key;
            $db_field_types[$db_field_type->field]['name']          = $db_field_type->field;
            $db_field_types[$db_field_type->field]['db_extra']      = $this->check_db_extra($db_field_type);
        }
        
        $results = $this->get_field_types($this->table_name);

        foreach($results as $num => $row)
        {
            $row = (array)$row;
            $results[$num] = (object)( array_merge($row, $db_field_types[$row['name']])  );
            $results[$num]->type = $results[$num]->db_type; 
        }
        
        return $results;
    }

    /**
     * Check id field is identity and assign extra properties to it
     * @param  object $db_field_type field meta-data
     * @return string extra property
     */
    public function check_db_extra($db_field_type)
    {   
        $extra = '';
        return ($db_field_type->primary_key === 1) 
                ? $extra = 'auto_increment'
                : $extra = '';
    }

    function get_primary_key($table_name = null)
    {

        if($table_name == null)
        {
            if(isset($this->primary_keys[$this->table_name]))
            {
                return $this->primary_keys[$this->table_name];
            }
            
            if(empty($this->primary_key))
            {
                
                $fields = $this->get_field_types_basic_table();

                foreach($fields as $field)
                {
                    if($field->primary_key == 1)
                    {
                        return $field->name;
                    }
                }

                return false;
            }
            else
            {
                return $this->primary_key;
            }
        }
        else
        {
            
            if(isset($this->primary_keys[$table_name]))
            {
                return $this->primary_keys[$table_name];
            }

            $fields = $this->get_field_types_basic_table($table_name);
            
            foreach($fields as $field)
            {
                if($field->primary_key == 1)
                {
                    return $field->name;
                }
            }

            return false;
        }

    }
}

And i try to use n-n relation but there not such a thing like group_concat on sql server :( 

[davidoster]

It's ok [member=nekalv].

Well about group_concat... google it, this might be useful, http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005

[nekalv]

Thank you! davidoster! i will check out this! at first in the morning! :) 

[ezyb123]

Thank you for this code - super helpful!

 

The paging doesn't work - is there any chance to assist? When paging it keeps appending the new set to the first set.

 

 

Also - I kept getting an error about primary keys, but my work around is to set it manually in the controller:

 

    $crud = $this->new_crud();

    $crud->set_primary_key('idfield','tablename');

 

Thanks again!