Table prefix is missing

[g4b0]

Hi all,

What do you think about adding a table prefix option to SilverStripe? Actually there is only the opportunity to set the database prefix, but in some circumstances it's useful to assign a prefix to each single table. 

g4b0

[Nicolaas Thiemen Francken - Sunny Side Up]

I think there is a danger that adding lots of these "nice to haves" slows down the core.

I would recommend to use several db-es if you want to mix non SS tables with SS tables.

--
You received this message because you are subscribed to the Google Groups "SilverStripe Core Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to silverstripe-d...@googlegroups.com.
To post to this group, send email to silverst...@googlegroups.com.
Visit this group at http://groups.google.com/group/silverstripe-dev.
For more options, visit https://groups.google.com/groups/opt_out.

--
Nicolaas Thiemen Francken  

Sunny Side Up Ltd  {
  Skype: atyourservicehour
  Phone: +64 4 889 2773 

  Emergencies: 0221697577
  n...@sunnysideup.co.nz
  http://www.sunnysideup.co.nz

}

[Mark Guinn]

I agree Nicolaas. In theory, wouldn't that be doable as a module? I'm on holiday and don't have my laptop handy, but off the top of my head I think you could override the table name via an extension on DataObject and/or augmenting the SQL ala the Versioned extension. 

I imagine you'll have a hell of a time with modules though - any time someone uses DB::query or dips too deeply into SQL, joins etc. For all that, you may have the same problem in core as well. 

[g4b0]

Slowness it's not a big deal, since string concatenation is fast in PHP. The problem may be the massive usage of DB::query in core and modules that will make refactoring painful, as stated by Mark.

Off course I can manage my project in a different way, but it would be nice to add some "nice to haves" to SS just to have some more flexibility. Moreover I'm not the first one searching for table prefix, it's an old issue (probably because all the major CMS out there implement it): 

http://www.silverstripe.org/archive/show/1540

http://www.silverstripe.org/archive/show/3907

http://www.silverstripe.org/migrating-a-site-to-silverstripe/show/4710

http://www.silverstripe.org/general-questions/show/9023

g4b0

[Nicolaas Thiemen Francken - Sunny Side Up]

On 6 October 2013 21:26, g4b0 <gabriele...@gmail.com> wrote:

Slowness it's not a big deal, since string concatenation is fast in PHP. The problem may be the massive usage of DB::query in core and modules that will make refactoring painful, as stated by Mark.

People tend to use DB::query to speed up SS... 

Speed is a big deal and even tiny calculations / concatenations, etc...  add up....

What is difficult about using two databases?  Can we make that easier?

[g4b0]

We need to share some tables between two different software, SilverStripe on one hand and a custom CRM on the other hand. The idea is to have CRM writing directly into SS tables, in order to automatically populate the frontend.

We can achieve the same results by introducing an API layer or some sort of sync cronjob, but it will be easier to directly write into the DB.

g4b0

[Will Morgan]

I see table prefixes as a feature being used due to limitations on number of databases on shared hosting environments.

> We need to share some tables between two different software, SilverStripe on one hand and a custom CRM on the other hand. The idea is to have CRM writing directly into SS tables, in order to automatically populate the frontend.

Forgive me if I'm wrong, but it sounds like what you really need is multiple database connections, which in SilverStripe is doable but not elegant.

[Nicolaas Thiemen Francken - Sunny Side Up]

It is very easy to setup a second DB connection.  Could your custom CRM not connect to its own database AND the SS install?  That would solve your problem without any major hassles. 

--

You received this message because you are subscribed to the Google Groups "SilverStripe Core Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to silverstripe-d...@googlegroups.com.
To post to this group, send email to silverst...@googlegroups.com.
Visit this group at http://groups.google.com/group/silverstripe-dev.
For more options, visit https://groups.google.com/groups/opt_out.

[g4b0]

Since we could not change CRM behavior we have tried walking on a non conventional path: we had exported some views from CRM database to the SS DB, letting them appear like DO tables (correct name, added ID, ClassName, Created and LastEdited column). Doing so SS views CRM objects like its own DataObjects, and it can work with them. Since the views are simple table select without joins we can also write through them.

Here you are an example:

###########################################

############### CRM DB:

###########################################

CREATE TABLE IF NOT EXISTS `software` (

  `ss_id` int(11) NOT NULL AUTO_INCREMENT,

  `ClassName` enum('DoSoftware') NOT NULL,

  `Created` datetime NOT NULL,

  `LastEdited` datetime NOT NULL,

  `id` varchar(20) NOT NULL DEFAULT '',

  `id_linea` varchar(10) DEFAULT NULL,

  [.. snip ..]

  PRIMARY KEY (`ss_id`),

  UNIQUE KEY `id` (`id`),

  KEY `id_linea` (`id_linea`),

  KEY `id_linea_2` (`id_linea`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=66 ;

ALTER TABLE `software`

  ADD CONSTRAINT `software_ibfk_1` FOREIGN KEY (`id_linea`) REFERENCES `software_linee` (`id`) ON UPDATE CASCADE;

###########################################

############### SS DB:

###########################################

CREATE VIEW DoSoftware AS (

  select ss_id as ID, 

  ClassName, 

  Created, 

  LastEdited,  

  id as ec_id, 

  id_linea, 

  [.. snip ..] 

  from `crm`.`software`

);

###########################################

############### SS CODE:

###########################################

class DoSoftware extends DataObject {

private static $db = array(

'ec_id' => 'Varchar(20)',

'id_linea' => 'Varchar(10)',

[.. snip ..]

);

private static $has_many = array (

'Files' => 'File',

'Demos' => 'File'

);

}

We had some issue with the original foreign key that can not be translated to SS has_one/has_many relationship (software_linee has also been translated into SS DataObject), but we handled them by hand. Everything seems working, what do  you think about that?

g4b0