Documentation task: How Data relates to Table Wizard

Indeed, there is overlap between table wizard and data, but there are also differences. These differences stem largely from the environments in which TW and Data are maturing: while TW roots are in migration, Data's are in dynamically allocating storage space + providing CRUD on table data.

When starting on the predecessor of Data (Flatstore, there have been 2 versions of it). I had a very close look at table wizard (I was after the dynamic views integration :) - but it became very quickly clear that I was more after a simple dynamic table based data API rather than a table analyzer and integrator.

Here is a breakdown of the most important commonalities and differences that I am aware of (I invite everybody to add or correct).

- Table allocation API: Data has an API to create, update and delete tables, TW doesn't.
- Adding existing tables: TW can manage existing tables, create Schema API information and generate views integration for it. Data can do this only with its own tables. However, Data uses very similar approaches to generate schema API information and views integration for its tables. Adding existing tables would be a rather small change. Both modules rely heavily on schema module for some of this functionality.
- Relate tables: Both modules have an API for relating tables (Data has also a UI for it, don't know about TW).
- Data API: Data has an API for creating, updating and deleting records in data tables. I am looking into strengthening this API. TW does not have such an API and I'm not sure whether it should/is planning to do so.
- Import: TW has import data functionality, Data itself does not have any. Data relies on FeedAPI for importing data (see FeedAPI Data module).
- Data can export table definitions CTools-style, I'm working on a install() update() infrastructure ATM so that we can use Data better as API for other contrib modules.
- Data allows additional meta data per fields (e. g. for defining human readable labels).
- TW analyzes table content, Data doesn't at all.
- There are some other features that I don't see in TW: Configure which views handlers to pick for which fields, search integration, relating data records to nodes (working on that one), manage differences between schema info and table. Some of these features may fit into TW's concept, others may not. I don't know.

To summarize: yes, there is a lot of similarity/overlap, but I feel these two modules aim to address very different use cases. I also use data very decidedly to explore new ways of managing content in Drupal and to that end it is a lot easier to have full independence from similar modules with a different focus. That said, I am very interested to explore ways on how to merge efforts or learn from each other.

I hope this was helpful.

Could you add this to the project page? That'd certainly help people (like me) who are confused by this. :)

Webchick: good call: http://drupal.org/project/data

Interesting. When I first got to Drupal my initial questions about integrating third party database assets were met with "can't be done." Then, after hearing a very helpful presentation at BADCamp in Berkeley, I discovered the Node-Import module, and shortly after, Table Wizard, Migrate, and Data. Now I know it can be done. Or can it?

My ideal scenario would be to import a local CSV (or whatever) file into the MySQL db of Drupal and then display it in Views without first pouring it into nodes. When the imported data gets poured into nodes, it mixes with other nodes, and it becomes harder to manage and update, like mixing steel ball bearings with marbles, chocolate balls, rabbit pellets, etc. Much cleaner and faster to sidestep the node container with all its complications. If I could pour all the data into one node, that would be OK, but creating thousands of nodes, each containing individual data records (table rows) that neither need to be nor should be managed individually, seems inelegant and wasteful.

Table Wizard lets me import a CSV file into a new MySQL table, and lets me see that this table exists, what its fields are, and how many records it has. The import implementation is excellent; imported data automatically overwrites data in an existing table of the same name, which is what I want, because the offline data changes frequently and needs to be pushed up to the site so as to refresh the display. One button does it. Kudos.

But there my applause stops. The new table is visible in Schema. But it doesn't show up in Migrate. It doesn't even show up in Data. And Views can't see it either. The new table is like an outcast, an orphan, a leper that nobody wants to handle. So, unless I've missed a button or a form somewhere (and the UI could use a bit of pulling together), TW is a bridge to nowhere.

Migrate is no help here. Migrate assumes that you've already created a View that defines the data you want to operate on, which presupposes that you've already poured the external data into nodes or other predefined Drupal content vessels -- just what was to be avoided. And surprisingly, Migrate can't see the new data table that TW created, even though Migrate depends on TW.

Data lets you create and design a new data table, and it even has a promising option to relate the table to "None" instead of Nodes etc., but this selection again hides the table from Views and leaves it in the dark. Data doesn't let you import external data into your new table. Like Migrate, Data doesn't see the new table that TW created. So, for my particular needs, it's not a fit.

Bottom line: there's no way to do anything with imported data in Views (or any other module?) unless you first pour it into nodes (or another even less useful predefined content holder). And for that purpose, the Node Import module is the most direct and comprehensive pathway. It uploads a CSV file, it maps the CSV fields to the node fields you've set up in CCK, and pours the imported data into those fields. You can then go to town with Views and do what needs to be done. It works and I'm not complaining. But:

It would be ideal to have a new Drupal content type called "Data" that does away with the cumbersome "Title" and "Body" fields and all the useless node hangers-on like comments, revisions, authored by, input filter, ad nauseam. And it would be ideal if the architects could tweak the wonderful Views query builder to see this new content type and work its magic on it.

manicolaus:

Feeds+Data support CSV import, node relations, views:

1) install views, data, data_ui
2) Use feeds for importing into data (for an example, take a look at Managing News: http://managingnews.com/download)
3) Start building views

Relating data records to nodes _does_ expose them on node based views. OTOH, you have to add a relationship to the display to create the join between the node and the data table.

Also: see how you can select which views handlers to use on a given field when you edit a data table.

And Views can't see it either. The new table is like an outcast, an orphan, a leper that nobody wants to handle. So, unless I've missed a button or a form somewhere (and the UI could use a bit of pulling together), TW is a bridge to nowhere.

manicolaus, this webchick's tutorial shows how to use Table Wizard to expose your imported database tables as Views: http://www.lullabot.com/articles/drupal-data-imports-migrate-and-table-w...

Keeping track of this thread...

Very interesting possibilities here, trying to figure out how to document and map an empty set of database tables -- about 400 of them, with a total of about 80k fields. Want multiple comment fields for each field, to point at places where corresponding definitions might live...

Subscribing

sub

@alex_b

Adding existing tables: TW can manage existing tables, create Schema API information and generate views integration for it. Data can do this only with its own tables. However, Data uses very similar approaches to generate schema API information and views integration for its tables. Adding existing tables would be a rather small change.

This last sentence ... "Adding existing tables would be a rather small change" ... I'm looking for this possibility urgently. I gonna look into the code and readme and hope to find how-to do this.

If possible, could you give me a push?

Feature request: Please create some kind of option that admins can switch on or off to include existing tables.

Thanks in advance
FreeFox

Just a quick question that I have not seen directly addressed. I apologize if I have missed it somewhere. We have used table wizard to incorporate data from at least two additional databases into our client sites. IOW the Drupal database is available and fully under the control of the Drupal installation. We have additional databases managed by proprietary client-server applications which are being used to provide online service for our clients to their customers.

We use in settings.php 3 in the vicinity of the lines 87 through 94 to give the Drupal system access to those two "extra" databases. Similar to the code shown here...

$db_url = 'mysql://username:password@localhost/databasename';

I was able to accomplish this easily in table wizard. Is it possible to choose a database using data module with views?

As an addition to #1:

Import: TW has import data functionality, Data itself does not have any. Data relies on FeedAPI for importing data (see FeedAPI Data module).

They both have some kind of 'import functionality', but they're actually opposite; TW's is actually 'export' functionality, if you will.

Data enables functionality (through Feeds) to import external data into its managed tables.
TW enables functionality (through Migrate) to import data from its managed tables, into Drupal standard entities (nodes, users, taxonomy, ...). That is, the 'external data' are the TW-managed tables.

---

TW displays data in tables, through views. (Just like Data.) It also has some analysis functions on that data - and it can work with different databases.
For the rest, it does not 'manage' its data. If you want to do anything else besides view the data (or import it into common Drupal entities)... you need the Data module.

From the announcement on the project page, it looks like TW will be phased out and Data will take over.

(And it's going to be interesting to see how Migrate & Feeds will evolve and overlap. But that's another issue :-) )

@ Cperg / #16:
This does not work. (Yet, I guess? #739516: Cannot not access tables outside the default DB?)

I just clicked my way through a few screens. The Schema module has a 'settings' tab where you can specify the database connection which Schema uses for some operations.
After setting this to a different connection, Data seems to be able to 'adopt' a table (schema) from that different database.
But viewing that table's data (or altering its schema) still won't work.

roderik,

Thanks for verifying my concerns. "Interesting" is one word we could use for sure. "Frustrating" is another ;)

Having the ability to work with more than the one underlying database in Drupal was a major consideration for our company's choice in selecting Drupal in the first place. We will take a look at the state of the data module in a few weeks and see what we can do to upgrade the code for our use. Of course we would be more than happy to return our development or findings to the project.

enlightening thread, thanks all!

Interesting discussion, thanks.

Our project has quite a simple structure now but as the schema of the data we're managing gets more complex we'll definitely need a good way of showing various views (small v) of the data in one node - otherwise we will end up with billions of nodes...

I'm hoping by the time we get there Drupal 7 will have thrown some more light on how to do it, and meanwhile, am cobbling something together with Data and Views 2 (big v.)

subscribing...

This was major helpful for me, hope it can help others http://fuzion.co.nz/content/getting-your-csv-data-drupal-view-using-feed...

Tim

All,

Thanks for the write up. We have been evaluating both of these modules and it is not clear if Table Wizard will be deprecated. Data appears to be largely stagnant and Table Wizard is seeking a new maintainer. Does anyone have any insight into the best path forward to use when accessing external data?

Steve

looking for the same functionality mentioned on post #25. Now that Drupal 7 is out, we need to start thinking about a future migration. Since we heavily use Table Wizard to access our external data and tw will be deprecated, what module can we use to replace that functionality?

For a functionality like mentioned on #25 you can try with this module http://drupal.org/project/feeds_sql

does it works with Views? if I use feeds_sql to access the external db, can I see the tables from the external db when adding new views?

Yes,You have to install feeds and feeds_sql and then create a "Feed SQL Importer" (to understand how it works, it's easier override the standart "Feed Importer" or trying to create one).
Finally you only have to create the view including the corresponding fields.

could you please post the steps to follow up to make it work for exposing tables from an external db? I don't need to import all the data from the external db onto drupal, just to use the connection to create the views.

sub

I have read everything I can find on this subject but my comprehension (or lack thereof) of some of the terminology has kept me from deciphering an answer to my issue, to whit:

I have 4 tables in my Drupal MySQL DB that are created and maintained by another source (an applet). I would like to be able to see and use these tables in Views to create links, list and analyze contents, etc. and, ideally, be able to write to them as well.

I don't have a sense on which module is the way to go, Data or Table Wizard. Our data is already in the Drupal DB, so getting it in there isn't an issue. Which one will expose the tables to Views in the most efficient and simplest manner? As must be evident from my post, simple is important. Very important.

Does the deprecation of TW alone answer my question?

Thanks for any guidance.

1) tw allows you to expose the tables to views, it does not creates nodes, and you can not write back to the db. It only works for Drupal 6 but it is easier to use.

2) feeds_sql allows you to import the data to nodes, to create views, you can not write back to the DB and it only works for Drupal 7.

3) #7 explains how to do it with the Data module. I could not make it works with an external DB. In your particular case, you are using the same Drupal DB for your tables, that should be easier to manipulate.

You can try out all of the modules to see which one works better for your needs. The part that you are not going to achieve is writing back to the DB. You might need to write a custom module for that.

> The part that you are not going to achieve is writing back to the DB. You might need to write a custom module for that.

This is now doable in the D7 version of Data -- data table rows can be treated as entities.

Hi joachim, what about external database's tables that are not within the same drupal db? are we going to be able to expose external databases using the data module?

If people work on the patch, sure! See #864184: Describe and use external database tables using Data..

Thanks for the help. Turns out nothing will do what I need it to on this D6 site. I have yet to fiddle around with this on a D7 site.

Has anyone had luck with this?
I am the same, dont want to use nodes, its too slow to import to, tried using the data module but it doesnt work as advertised.

Any ideas? Csv to single table to views/search/solr indexing etc?

Given how much this issue has drifted -- what do you mean by 'this'? Is there another issue that is more specific to your problem?

I may resort to direct PHP coding. It's what its made for. That and possibly doing any serious data manipulation using outside programs (and programming). But again I'd be back at PHP coding, as it is geared to do anything you want.

subscribe

we decided to use the Views XML Backend module, to create our custom views, with data from an external non-drupal database. This solution eliminates the need of writing custom PHP code. We don't need to import the external data onto drupal, just to display the data and create custom views with that external data.

We have taken to switching to a second database located within the same server. I am sure it would be the same if the server were different. We build choices for the data within the module and then step into the database using those variables to locate the existing records from a pre-built database-view. There are different database-views for some specific requests. The module seems to be working great so far. The resulting information points to a report for download by the user.

The external database we are using is from a Windows Client Server application we developed to track and control field work for engineer/state required inspections. As the reports come in they attach those reports as PDFs to the records and we record those paths to the local network server.

That server is set up to mirror those files to a private cloud server where they are stored and updated from the local server on a periodic basis. We translate those paths stored in the database local server to reflect the folder environment in the cloud server. The links work great but of course there is no update made to the local database.

The database is also replicated to the same server as the Drupal database. The records are within minutes the same on the local database server as on the private cloud server with a master/slave arrangement. If the data needed to be changed at the Drupal side we would have to implement a master master replication.

I am assured that this can be accomplished with additional code to provide an update form for the secondary database. All of this is hand coded so its not a simple "open the module point it at this and go" ... We have not found any other way that gets us to our immediate need for accessing a database outside of the primary Drupal database.

This is an informative issue to keep open. Moving it along.

This could maybe get copied to a documentation page?

Yes, suitable for a documentation page.

Created a placeholder documentation handbook page, pointing to this issue, and renamed this issue slightly to differenciate between the two:

"How Data relates to Table Wizard"
https://www.drupal.org/node/2406919

Before adding content from here to that page, I would like some confirmations as of how correct/updated the information here is right now.

Please help review and comment on any incorrect information in this issue, so we can put the desired parts in the handbook.