0

I need to store some straight forward data rows (similar to the JSON objects that can be stored in MongoDB), where all the data associated with one object is found on the same row and not scattered over several database tables.

What is the best approach to do this in Drupal?

Notes:

  • I am considering storing them on database (MySQL/Postgres or MongoDB). I have no data yet (I am at the project planning stage).
  • I'll be using some API code (server side - does not need to have anything to do with Drupal) and I will be creating/updating the data via JSON post requests from a c# application. I need Drupal only to display the data.
  • @Pierre.Vriens I am considering storing them on database (MySQL/Postgres or MongoDB) .. I have no data yet, I am at the project planning stage. Thanks. – W.M. Jun 16 '16 at 16:02
  • Using some API code (server side - does not need to have anything to do with Drupal) I will be creating/updating the data via JSON post requests from a c# application. I need Drupal only to display the data. – W.M. Jun 16 '16 at 16:48
1

Have a look at the Forena module which can be used to display (= query, not update) data stored in databases external to Drupal. It comes with a full suite of Supported database connections ... such as MySQL, MS SQL, Oracle, Postgres or any PDO compliant variation.

For more details about Forena, 2 types of documentation are available:

The Sample reports and graphs are fully functional, such as the drill downs available on the Master Summary with drill down. To filter data (by the users), you'd typically use "report parameters", which you can see in the sample report "Users in cities". On top of that, using Forena "skins", you can also use the amazing "dataTables" (and its widgets, such as sorting by selected columns).

It also includes a UI for either creating your reports (the WYSIWYG Report Editor) and/or for creating your SQL queries (the WYSIWYG Query Builder).

Disclosure: I'm a co-maintainer of Forena.

  • I will read more about Forena later. Looks promising. I have one little question: would it be possible using Forena to filter and sort the data, I mean based on end user interaction? – W.M. Jun 16 '16 at 20:02
  • 1
    @W.M. : sure enough, check my updated answer (and the "users in cities", and experiment a bit with its parameters). – Pierre.Vriens Jun 16 '16 at 20:10
1

You could store your objects in the Drupal-DB in one table with just an id and a text field. Put your objects as JSON-Strings into the text field. De-serialize (json_decode) the data to display it in Drupal (build your own theme #type and twig-template or just put it into a #table).

You can access your table with the Drupal\Core\Database\Connection object just with straight forward SQL (query) or with PDO like (select) etc.

$stm = \Drupal::database()->select('my_simpletable', 't')
      ->fields('t', ['text'])
      ->condition('t.id', $needed_id)
      ->execute()->fetchAll(\PDO::FETCH_ASSOC);

You can create your simple table in the hook_schema() function of your module.install file. Like this:

$schema['my_simpletable'] = array(
      'description' => 'Table to json objects',
      'fields' => array(
          'id' => array(
              'type' => 'serial',
              'unsigned' => TRUE,
              'not null' => TRUE,
          ),
          'text' => array(
              'type' => 'text',
              'not null' => true,
              'size' => 'big',
              'description' => 'JSON string of objects.',
          )
       )
    )

That's all, no need for full blown entities here :-)

  • Thanks @Rainer Feike. I am using Drupal 7 and will continue to do so for a while now. However, I understand you approach and I do have the knowledge to apply it under Drupal 7. I think it would be better to use Postgres to store the data because it can work with JSON objects natively. – W.M. Jun 16 '16 at 19:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.