Create and manage content directly from the database.
Hi, I am creating a booking management application and the bookings are largely generated and managed from a MS Access front end working directly on a MySQL database.. Now we need to integrate these bookings into the Drupal website so that users can add or update bookings for themselves.. I have created a content type with the required fields and now need to work out how to create content directly with SQL from MS Access.. Has anyone ever created content this way or have any idea which tables need records created/updated?? Unfortunately the system needs to be able to be updated from the Drupal site and from the MS Access application so imports and feeds won't work.. Thanks for any ideas or help..
Standard stuff, see http://drupal.org/documentation for a good bit of information. You just need to create a hook_menu implementation that gives a page callback to a custom form displays the data and whose submit action modifies the data. Earnie. On Thu, Jun 7, 2012 at 4:46 AM, Wipe_Out <wipe_out@users.sourceforge.net> wrote:
Hi,
I am creating a booking management application and the bookings are largely generated and managed from a MS Access front end working directly on a MySQL database.. Now we need to integrate these bookings into the Drupal website so that users can add or update bookings for themselves..
I have created a content type with the required fields and now need to work out how to create content directly with SQL from MS Access..
Has anyone ever created content this way or have any idea which tables need records created/updated??
Unfortunately the system needs to be able to be updated from the Drupal site and from the MS Access application so imports and feeds won't work..
Thanks for any ideas or help..
-- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd
Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal. Dave -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Thursday, June 07, 2012 6:35 AM To: support@drupal.org Subject: Re: [support] Create and manage content directly from the database. Standard stuff, see http://drupal.org/documentation for a good bit of information. You just need to create a hook_menu implementation that gives a page callback to a custom form displays the data and whose submit action modifies the data. Earnie. On Thu, Jun 7, 2012 at 4:46 AM, Wipe_Out <wipe_out@users.sourceforge.net> wrote:
Hi,
I am creating a booking management application and the bookings are largely generated and managed from a MS Access front end working directly on a MySQL database.. Now we need to integrate these bookings into the Drupal website so that users can add or update bookings for themselves..
I have created a content type with the required fields and now need to work out how to create content directly with SQL from MS Access..
Has anyone ever created content this way or have any idea which tables need records created/updated??
Unfortunately the system needs to be able to be updated from the Drupal site and from the MS Access application so imports and feeds won't work..
Thanks for any ideas or help..
-- [ Drupal support list | http://lists.drupal.org/ ]
-- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ]
On Thu, Jun 7, 2012 at 11:22 AM, Metzler, David <metzlerd@evergreen.edu> wrote:
Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.
He is asking how to update the tables created by the MSACCESS tool in an external MySql DB. You can connect to the external DB with Drupal and use Drupal to display and update the data. You don't need to create Drupal nodes to do that. -- Earnie -- https://sites.google.com/site/earnieboyd
On 7 June 2012 17:24, Earnie Boyd <earnie@users.sourceforge.net> wrote:
On Thu, Jun 7, 2012 at 11:22 AM, Metzler, David <metzlerd@evergreen.edu> wrote:
Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.
He is asking how to update the tables created by the MSACCESS tool in an external MySql DB. You can connect to the external DB with Drupal and use Drupal to display and update the data. You don't need to create Drupal nodes to do that.
@Ernie - That was where we started but since we are not the worlds greatest programmers we are looking to switch it the other way around and get MS Access to work on the Drupal tables.. This way we can use "Views", "Rules", "References" and all the other available Drupal functionality as well..
If you are not the world's greatest programmers, I'd strongly recommend you abandon this approach. It will not be straightforward. Go ahead and look at the revisions tables and see that there are large text fields that contain data serialized by php's serialize function, and that's just this tip of the iceberg in terms of complexity you will encounter. You are basically facing rewriting drupal and some of PHP in Access! This is not simple stuff. Also Earnie is right that you be failing to fire the required hooks if you operate on the database tables directly, so the features that you talk about in drupal will not really be available to you. I would strongly recommend give the other approach more serious consideration. If you run into trouble there you are much more likely to get meaningful and helpful support from the drupal community on this endeavor. Dave . ________________________________ From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Wipe_Out Sent: Thursday, June 07, 2012 9:57 AM To: support@drupal.org Subject: Re: [support] Create and manage content directly from the database. On 7 June 2012 17:24, Earnie Boyd <earnie@users.sourceforge.net> wrote: On Thu, Jun 7, 2012 at 11:22 AM, Metzler, David <metzlerd@evergreen.edu> wrote:
Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.
He is asking how to update the tables created by the MSACCESS tool in an external MySql DB. You can connect to the external DB with Drupal and use Drupal to display and update the data. You don't need to create Drupal nodes to do that. @Ernie - That was where we started but since we are not the worlds greatest programmers we are looking to switch it the other way around and get MS Access to work on the Drupal tables.. This way we can use "Views", "Rules", "References" and all the other available Drupal functionality as well..
Hi Dave and Ernie, Maybe a bit more explanation will help.. We have a home baked MS access billing application that we use to generate and manage the bookings and bill based on those bookings.. The administration in managing all the bookings is getting to be too much so we want our customers to be able to manage and edit their bookings through our website.. This needs to be a two way process so bookings can still be managed through the MS Access side but can also be managed through the web interface.. It seemed easiest to create a new content type with all the required fields and references through Drupal and then get MS access to manage the same data tables in the mysql database.. The would mean creating, updating and deleting bookings nodes directly in the database from MS access..
From your comments it sounds like you are saying that its not as straight forward as that to manage Drupal's data.. I thought that by simply updating the "node" table along with all the "field" and "revision" tables that we could add or edit booking nodes from the backend while durpal managed it from the front..
Is this not the case?? @Dave - Fonena looks interesting and might solve the next part of the system I will be trying to create which is to show the users their billing data that is being generated by the MS Access application, obviously here a report/view is all that's needed because they don't need the ability to change the data.. Thanks.. On 7 June 2012 16:22, Metzler, David <metzlerd@evergreen.edu> wrote:
Unless I misunderstand this question he's asking about how to create nodes in a mysql database using MS access as the development tool. The nodes he's asking to create are CCK crafted nodes in drupal.
Dave
On Thu, Jun 7, 2012 at 12:46 PM, Wipe_Out wrote:
Hi Dave and Ernie,
Maybe a bit more explanation will help..
We have a home baked MS access billing application that we use to generate and manage the bookings and bill based on those bookings.. The administration in managing all the bookings is getting to be too much so we want our customers to be able to manage and edit their bookings through our website..
This needs to be a two way process so bookings can still be managed through the MS Access side but can also be managed through the web interface..
It seemed easiest to create a new content type with all the required fields and references through Drupal and then get MS access to manage the same data tables in the mysql database.. The would mean creating, updating and deleting bookings nodes directly in the database from MS access..
From your comments it sounds like you are saying that its not as straight forward as that to manage Drupal's data.. I thought that by simply updating the "node" table along with all the "field" and "revision" tables that we could add or edit booking nodes from the backend while durpal managed it from the front..
Is this not the case??
You don't want to update the node tables directly. You would miss out on the hook implementations. You need to create a node object and use node_save() to do it. You could manage this by creating a custom module to push and pull the changes to an external DB for the ms access entries via a hook_cron implementation. There may be a module already to do this but I don't know. -- Earnie -- https://sites.google.com/site/earnieboyd
On Thu, Jun 7, 2012 at 1:00 PM, Earnie Boyd <earnie@users.sourceforge.net> wrote:
On Thu, Jun 7, 2012 at 12:46 PM, Wipe_Out wrote:
Hi Dave and Ernie,
Maybe a bit more explanation will help..
We have a home baked MS access billing application that we use to generate and manage the bookings and bill based on those bookings.. The administration in managing all the bookings is getting to be too much so we want our customers to be able to manage and edit their bookings through our website..
This needs to be a two way process so bookings can still be managed through the MS Access side but can also be managed through the web interface..
It seemed easiest to create a new content type with all the required fields and references through Drupal and then get MS access to manage the same data tables in the mysql database.. The would mean creating, updating and deleting bookings nodes directly in the database from MS access..
From your comments it sounds like you are saying that its not as straight forward as that to manage Drupal's data.. I thought that by simply updating the "node" table along with all the "field" and "revision" tables that we could add or edit booking nodes from the backend while durpal managed it from the front..
Is this not the case??
You don't want to update the node tables directly. You would miss out on the hook implementations. You need to create a node object and use node_save() to do it. You could manage this by creating a custom module to push and pull the changes to an external DB for the ms
or separate table, it doesn't have to be an external DB.
access entries via a hook_cron implementation. There may be a module already to do this but I don't know.
-- Earnie -- https://sites.google.com/site/earnieboyd
You don't want to update the node tables directly. You would miss out on the hook implementations. You need to create a node object and use node_save() to do it. You could manage this by creating a custom module to push and pull the changes to an external DB for the ms access entries via a hook_cron implementation. There may be a module already to do this but I don't know.
What are the hook implementations you mention specifically?? If the data tables are all updated correctly will drupal not see it as a node? As mentioned we aren't programmers so this seemed like the easier option.. My concern with trying to write something to keep two sets of data "syncronised" is managing the whole syncronisation process and issues with changes happening on both sides causing a "split brain" scenario.. If both access interfaces are editing the same data this is reduced.. No different to why MySQL replication is not often attempted as master-master because the sync is not easy to do reliably..
On Thu, Jun 7, 2012 at 1:23 PM, Wipe_Out <wipe_out@users.sourceforge.net> wrote:
You don't want to update the node tables directly. You would miss out on the hook implementations. You need to create a node object and use node_save() to do it. You could manage this by creating a custom module to push and pull the changes to an external DB for the ms access entries via a hook_cron implementation. There may be a module already to do this but I don't know.
What are the hook implementations you mention specifically??
I can only point you to the technical documentation. http://api.drupal.org/api/drupal/includes%21module.inc/group/hooks/7
If the data tables are all updated correctly will drupal not see it as a node?
I doubt that you'll be able to update the tables correctly but not all that is Drupal will not know that you created the node so "correctly" is suspect to begin with.
As mentioned we aren't programmers so this seemed like the easier option..
Then please hire someone to do it. See consulting@drupal.org and http://groups.drupal.org/jobs for posting an opportunity.
My concern with trying to write something to keep two sets of data "syncronised" is managing the whole syncronisation process and issues with changes happening on both sides causing a "split brain" scenario.. If both access interfaces are editing the same data this is reduced.. No different to why MySQL replication is not often attempted as master-master because the sync is not easy to do reliably..
But the syncing scenario is the only reliable way to do it, IMO. Or, teach the administrators to use the Drupal interface to do their changes instead of MS Access. -- Earnie -- https://sites.google.com/site/earnieboyd
I am not suggesting you do data synchronization, but that you implement code that modifies the same mysql tables modified by access, OUTSIDE of the drupal node schema. You could write a custom module to do this. Yes you will need to learn the drupal forms api, but that is small compared to the access code you will need to write to get the fields in the node and revisions tables consistent. IF you are not programmers, what you are suggesting is not technically feasible anyway, as you will have to do quite a bit of programming to get the data in the node tables correct. It's not something you can just point an access form at and expect it to work. I agree with Earnie, the best advice is to hire a developer/consultant to work through this with/for you. Or to convince your Access users to modify the data in drupal instead of the access. Good luck, Dave -----Original Message----- From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Earnie Boyd Sent: Thursday, June 07, 2012 10:38 AM To: support@drupal.org Subject: Re: [support] Create and manage content directly from the database. On Thu, Jun 7, 2012 at 1:23 PM, Wipe_Out <wipe_out@users.sourceforge.net> wrote:
You don't want to update the node tables directly. You would miss out on the hook implementations. You need to create a node object and use node_save() to do it. You could manage this by creating a custom module to push and pull the changes to an external DB for the ms access entries via a hook_cron implementation. There may be a module already to do this but I don't know.
What are the hook implementations you mention specifically??
I can only point you to the technical documentation. http://api.drupal.org/api/drupal/includes%21module.inc/group/hooks/7
If the data tables are all updated correctly will drupal not see it as a node?
I doubt that you'll be able to update the tables correctly but not all that is Drupal will not know that you created the node so "correctly" is suspect to begin with.
As mentioned we aren't programmers so this seemed like the easier option..
Then please hire someone to do it. See consulting@drupal.org and http://groups.drupal.org/jobs for posting an opportunity.
My concern with trying to write something to keep two sets of data "syncronised" is managing the whole syncronisation process and issues with changes happening on both sides causing a "split brain" scenario.. If both access interfaces are editing the same data this is reduced.. No different to why MySQL replication is not often attempted as master-master because the sync is not easy to do reliably..
But the syncing scenario is the only reliable way to do it, IMO. Or, teach the administrators to use the Drupal interface to do their changes instead of MS Access. -- Earnie -- https://sites.google.com/site/earnieboyd -- [ Drupal support list | http://lists.drupal.org/ ]
On 7 June 2012 18:43, Metzler, David <metzlerd@evergreen.edu> wrote:
I agree with Earnie, the best advice is to hire a developer/consultant to work through this with/for you. Or to convince your Access users to modify the data in drupal instead of the access.
Ok, working on that theory would this be a more feasible/logical approach based on our level of code programming ability.. 1. In order to create the bookings we generate a CSV from MS Access and use something like the "Feeds" module to import it.. This should handle the node creation for bulk generated bookings right?? 2. Users then make changes to the bookings through Drupal rather than access which solves that aspect.. 3. For billing purposes I assume its not a problem to query the data from the Drupal tables from access.. This leaves one potential issue.. In the booking there is a "Date" field for booked time and a second "Date" field for actual time (both fields part of the same node so would be created when its generated).. We have an external interface used for clocking actual start and end times which would need to update the values of the actual time field.. Would it be a problem to only UPDATE these DateTime values from an interface outside of Drupal?? Thanks..
That sounds more feasible. Note that drupal date and time fields are likely unix timestamps, so you my run into troubles converting from access times to date times, but provided you can get that date math to work out, it seems plausible. ________________________________ From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Wipe_Out Sent: Thursday, June 07, 2012 11:17 AM To: support@drupal.org Subject: Re: [support] Create and manage content directly from the database. Ok, working on that theory would this be a more feasible/logical approach based on our level of code programming ability.. 1. In order to create the bookings we generate a CSV from MS Access and use something like the "Feeds" module to import it.. This should handle the node creation for bulk generated bookings right?? 2. Users then make changes to the bookings through Drupal rather than access which solves that aspect.. 3. For billing purposes I assume its not a problem to query the data from the Drupal tables from access.. This leaves one potential issue.. In the booking there is a "Date" field for booked time and a second "Date" field for actual time (both fields part of the same node so would be created when its generated).. We have an external interface used for clocking actual start and end times which would need to update the values of the actual time field.. Would it be a problem to only UPDATE these DateTime values from an interface outside of Drupal?? Thanks..
On 7 June 2012 21:42, Metzler, David <metzlerd@evergreen.edu> wrote:
**
That sounds more feasible. Note that drupal date and time fields are likely unix timestamps, so you my run into troubles converting from access times to date times, but provided you can get that date math to work out, it seems plausible. ****
** **
Thanks for your input David.. The "Date" module saves as a Datetime field so the format shouldn't be a problem.. Also the "clocking" interface is php based and the plan is to change the query to update the correct table.. Thanks again..
On Thu, Jun 7, 2012 at 4:55 PM, Wipe_Out <wipe_out@users.sourceforge.net> wrote:
The "Date" module saves as a Datetime field so the format shouldn't be a problem.. Also the "clocking" interface is php based and the plan is to change the query to update the correct table..
But the Drupal dates are an int field. You'll need to convert the integer time into the Datetime format and vice versa. See http://us.php.net/time for information on what Drupal stores. -- Earnie -- https://sites.google.com/site/earnieboyd
But the Drupal dates are an int field. You'll need to convert the integer time into the Datetime format and vice versa. See http://us.php.net/time for information on what Drupal stores.
Hi Earnie The Date module creates the fields as "datetime"..
From phpMyAdmin `field_booking_actual_value` datetime DEFAULT NULL, `field_booking_actual_value2` datetime DEFAULT NULL,
Today I have been looking at the node_save and drupal_execute options for creating nodes programatically.. It doesn't look too difficult (famous last words) so may be a solution to working with the drupal data and creating or updating nodes from our clocking interface.. The MSAccess issue is resolved now by using a feed to import the bookings from a CSV..
You might consider using a project I created called Forena. It's report writing software and can report directly against tables in drupal or other databases. In this fashion you could display the booking data directly without having to try and create nodes from an MS access database. It would probably be easier to write a custom drupal module to update the booking table structures than to get MS access to create nodes in drupal. The primary problem is that you'd have to be rewriting much of the cck logic in Access which seems like a lot of work, not no mention some data structures in drupal are serialized php objects, so there would be a lot of parsing code that would need to be written for this. There are no non-php apis. Creating php code to update a booking structure in drupal on the other hand seems really straightforward to me. I do this kind of development work in drupal all the time. ________________________________ From: support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Wipe_Out Sent: Thursday, June 07, 2012 1:47 AM To: support@drupal.org Subject: [support] Create and manage content directly from the database. Hi, I am creating a booking management application and the bookings are largely generated and managed from a MS Access front end working directly on a MySQL database.. Now we need to integrate these bookings into the Drupal website so that users can add or update bookings for themselves.. I have created a content type with the required fields and now need to work out how to create content directly with SQL from MS Access.. Has anyone ever created content this way or have any idea which tables need records created/updated?? Unfortunately the system needs to be able to be updated from the Drupal site and from the MS Access application so imports and feeds won't work.. Thanks for any ideas or help..
participants (3)
-
Earnie Boyd -
Metzler, David -
Wipe_Out