I need some consultation. I've used Drupal for lightweight deployments (mostly personal blogs). But I've got a potential client who is interested in drupal for e-commerce. We're thinking through a couple of issues, and we'd like some advice: Our client uses MSSQL for their inventory, etc. Since drupal doesn't support anything but mysql and pgsql, and since their tables would be different from drupal's regardless, we're trying to work out the best way to handle integrating drupal into their existing infrastructure. We've considered writing a script to pull data from one database and use it to update the other. We've also considered hacking the relevant modules (Ubercart) to do this on demand. Does anyone have experience with this?
On Mon, 26 May 2008 11:00:52 -0400 "Christopher M. Jones" <cjones@partialflow.com> wrote:
I need some consultation. I've used Drupal for lightweight deployments (mostly personal blogs). But I've got a potential client who is interested in drupal for e-commerce. We're thinking through a couple of issues, and we'd like some advice:
Our client uses MSSQL for their inventory, etc. Since drupal doesn't support anything but mysql and pgsql, and since their tables would be different from drupal's regardless, we're trying to work out the best way to handle integrating drupal into their existing infrastructure. We've considered writing a script to pull data from one database and use it to update the other. We've also considered hacking the relevant modules (Ubercart) to do this on demand.
Does anyone have experience with this?
If you're planning to use Ubercart it may not be that easy to convince it to use 2 DB. If you don't need extreme customisation to the purchase process it may still be a "bargain". I don't know Ubercart so deeply to say if it is going to be easy to make it use 2 DB. Anyway I doubt it is going to be that easy. Depending on the version you're developing for there were some choices about how to make Drupal works with MSSQL. I remember there was a "big" patch and Siren (whose project page is not anymore available). I'd say that using MSSQL for all Drupal on the currently supported release is going to cause you more headache than the one it will avoid you. It could be different for D7. I've written an e-commerce engine inside Drupal. In its infancy I was using pgsql for CMS and for all the e-commerce processing and MSSQL for the catalogue. I wrote a couple of classes to overcome the lack of LIMIT in MSSQL and keep on using Drupal paging facilities. It was not a big work. Anyway it worked to a certain extent, you still have themed tables for displaying products, you can use the template system, FAPI... and you've a CMS. If you're planning to develop your own e-commerce software, put most of the business logic in php and rely just on what Drupal can offer, you'll have anyway to implement the consistency and locking logic by your own and having your catalogue on one DB and baskets, orders etc... on another may not make too much difference, since you won't exploit what a RDBMS can really offer. While things grew up I decided to a) import the catalogue in pgsql and b) put most of the business logic in the DB. On 2 similar boxes (2x Xeons roughly 5 years old with RAID1 and 4Gb RAM), and no tuning at all, one running MS SQL the other running pgsql, exporting and importing a catalogue of 600K articles and a tree of tens of thousands categories takes roughly 5 minutes including FTP transfer. If you can live with a 5 min "down" and miss referential integrity on items ids, it doesn't seem a big cost. Otherwise pgsql have a nice plug-in (dblink) that let you perform "cross DB" queries... and that's what I'm going to implement next as soon as I'll finish with other features that have precedence over a more elegant import procedure. I had to implement a quite unusual purchase process so I couldn't go the Ubercart route. If you've a more traditional purchase process, importing the catalogue into Ubercart may be the easiest, more reliable path and it will come with a lot of plug-ins, discount methods etc... you'll take years to develop by your own. Even if you were working with the same DB on the back-end an front-end you'll still have to move data around. If you don't have a very dynamic catalogue you could easily sync the 2 DB once a day... if eg. availability of items is critical to your business you may just pull on stock quantities on demand (you may be so lucky there is already a plug-in for Ubercart that just do that). Summing it up: Since I think most of Ubercart coherency checks and constraints are made in php on client side (DB client) it doesn't make too much difference if you're pulling part of the data directly from MSSQL "on demand" (look at FreeTDS and sybase_*[1]), but I think pulling them all is going to make things to tricky and hard to keep under control, anyway I'd pull data on demand just if I had a very dynamic catalogue that really need to be up-to-date. Building your own e-commerce engine if you don't need a really custom purchase process is not going to pay off, but if you have to... if you put your business logic and coherency checks in php you may find interesting and not too hard to put your feet in 2 DB inside Drupal, if you feel more comfortable in delegating locking and coherency checks to the DB... having to deal with 2 DB is going to be a PITA and even syncing a reasonably large catalogue is not going to cost you that much. [1] most Linux distro comes with sybase drivers for php but they miss precompiled mssql drivers. You could compile them but I didn't find the extra maintenance involved worth. Sybase drivers are generally up to the task. Here you could find something that may come useful if you plan to connect to MS SQL from a Linux box with PHP: http://www.webthatworks.it/d1/page/odbc,_freetds_and_microsoft_sql_(and_php) http://www.webthatworks.it/d1/page/nested_query,_connection_sharing_and_php_... http://www.webthatworks.it/d1/page/freetds_and_sqsh_debian -- Ivan Sergio Borgonovo http://www.webthatworks.it
The product catalog should remain fairly stable. It's mainly stock (price, availability, location) that needs to come from the MSSQL db. Prices and availability are managed on site using a custom VB app that talks to the MSSQL server. Quantities, pricing, and locations are constantly changing and need to be accurate to the second. I'd already thought of using an external php script to handle updating relevant fields in the Ubercart database from the MSSQL db. I'd also thought of adding calls to this script into Ubercart, to ensure that the drupal db is up to date every time a product is queried. I can see high performance costs in both of these options. Keep in mind, I've yet to really look at the Ubercart source, so I don't really know how carefully the data layer is separated from the logic, or even what pattern (active record? sql map?) is used. If there's good separation, then maybe I could modify things to pull just the pricing and availability data from the MSSQL server, and use the drupal db for the rest. Another tricky piece is going to be a 'sell cart' in addition to the traditional 'buy cart'. I foresee this requiring some hacking. Does anyone have initial thoughts on this? On Mon, 2008-05-26 at 20:13 +0200, Ivan Sergio Borgonovo wrote:
On Mon, 26 May 2008 11:00:52 -0400 "Christopher M. Jones" <cjones@partialflow.com> wrote:
I need some consultation. I've used Drupal for lightweight deployments (mostly personal blogs). But I've got a potential client who is interested in drupal for e-commerce. We're thinking through a couple of issues, and we'd like some advice:
Our client uses MSSQL for their inventory, etc. Since drupal doesn't support anything but mysql and pgsql, and since their tables would be different from drupal's regardless, we're trying to work out the best way to handle integrating drupal into their existing infrastructure. We've considered writing a script to pull data from one database and use it to update the other. We've also considered hacking the relevant modules (Ubercart) to do this on demand.
Does anyone have experience with this?
If you're planning to use Ubercart it may not be that easy to convince it to use 2 DB. If you don't need extreme customisation to the purchase process it may still be a "bargain". I don't know Ubercart so deeply to say if it is going to be easy to make it use 2 DB. Anyway I doubt it is going to be that easy.
Depending on the version you're developing for there were some choices about how to make Drupal works with MSSQL. I remember there was a "big" patch and Siren (whose project page is not anymore available). I'd say that using MSSQL for all Drupal on the currently supported release is going to cause you more headache than the one it will avoid you. It could be different for D7.
I've written an e-commerce engine inside Drupal. In its infancy I was using pgsql for CMS and for all the e-commerce processing and MSSQL for the catalogue. I wrote a couple of classes to overcome the lack of LIMIT in MSSQL and keep on using Drupal paging facilities. It was not a big work.
Anyway it worked to a certain extent, you still have themed tables for displaying products, you can use the template system, FAPI... and you've a CMS. If you're planning to develop your own e-commerce software, put most of the business logic in php and rely just on what Drupal can offer, you'll have anyway to implement the consistency and locking logic by your own and having your catalogue on one DB and baskets, orders etc... on another may not make too much difference, since you won't exploit what a RDBMS can really offer.
While things grew up I decided to a) import the catalogue in pgsql and b) put most of the business logic in the DB. On 2 similar boxes (2x Xeons roughly 5 years old with RAID1 and 4Gb RAM), and no tuning at all, one running MS SQL the other running pgsql, exporting and importing a catalogue of 600K articles and a tree of tens of thousands categories takes roughly 5 minutes including FTP transfer. If you can live with a 5 min "down" and miss referential integrity on items ids, it doesn't seem a big cost. Otherwise pgsql have a nice plug-in (dblink) that let you perform "cross DB" queries... and that's what I'm going to implement next as soon as I'll finish with other features that have precedence over a more elegant import procedure.
I had to implement a quite unusual purchase process so I couldn't go the Ubercart route. If you've a more traditional purchase process, importing the catalogue into Ubercart may be the easiest, more reliable path and it will come with a lot of plug-ins, discount methods etc... you'll take years to develop by your own.
Even if you were working with the same DB on the back-end an front-end you'll still have to move data around. If you don't have a very dynamic catalogue you could easily sync the 2 DB once a day... if eg. availability of items is critical to your business you may just pull on stock quantities on demand (you may be so lucky there is already a plug-in for Ubercart that just do that).
Summing it up:
Since I think most of Ubercart coherency checks and constraints are made in php on client side (DB client) it doesn't make too much difference if you're pulling part of the data directly from MSSQL "on demand" (look at FreeTDS and sybase_*[1]), but I think pulling them all is going to make things to tricky and hard to keep under control, anyway I'd pull data on demand just if I had a very dynamic catalogue that really need to be up-to-date.
Building your own e-commerce engine if you don't need a really custom purchase process is not going to pay off, but if you have to... if you put your business logic and coherency checks in php you may find interesting and not too hard to put your feet in 2 DB inside Drupal, if you feel more comfortable in delegating locking and coherency checks to the DB... having to deal with 2 DB is going to be a PITA and even syncing a reasonably large catalogue is not going to cost you that much.
[1] most Linux distro comes with sybase drivers for php but they miss precompiled mssql drivers. You could compile them but I didn't find the extra maintenance involved worth. Sybase drivers are generally up to the task. Here you could find something that may come useful if you plan to connect to MS SQL from a Linux box with PHP: http://www.webthatworks.it/d1/page/odbc,_freetds_and_microsoft_sql_(and_php) http://www.webthatworks.it/d1/page/nested_query,_connection_sharing_and_php_... http://www.webthatworks.it/d1/page/freetds_and_sqsh_debian
-- Ivan Sergio Borgonovo http://www.webthatworks.it
Hi on upgrading my site from 5.3 to 6.2 I found out the hard way that this was a big mistake. Currently images are not shown by the image module and by image gallery though the images are still there and can be displayed by using the html img tag. I browsed the forums but there isn't really a good solution for that, is there? the index.php on the start page displays a long list of error messages of the kind warning: Invalid argument supplied for foreach() in .../drupal-6.2/modules/cck/content.module on line 1096. I have browsed the forums and this seems to be a problem that, like with the images, many other people have, but where no clear solution exists? finally, magickally, the weighting for blocks administration seems to have gone. so where can I arrange my blocks in 6.2? Another mistake which was partly my fault, partly due to unclear instructions about how to upgrade was that I first disabled all non core modules and only then backuped the data base which seems to have destroyed tables for some modules such as biblio and all content is gone. as this seems to affect mostly biblio I wonder if there is a way to extract only this table from an older backup of the data base and insert it into the current db. any instructuions on how to would have to be command line version as my server hosts dont offer phpmyadmin I paste my status report below which looks fairly healthy given the problems which I encounter any help greatly appreciated thanks Armin Drupal6.2 Access to update.phpProtectedConfiguration fileProtected Cron maintenance tasksLast run 15 hours 16 min ago You can run cron manually. Database updatesUp to date Drupal core update statusUp to date File systemWritable (public download method) GD librarybundled (2.0.34 compatible) Image module directoriesExists (files/images) Image toolkitThe imagemagick toolkit is installed Module and theme update statusUp to date MySQL database4.1.22PHP5.2.6 PHP memory limit64M PHP register globalsDisabledUnicode library PHP Mbstring ExtensionUpdate notificationsEnabled Web serverApache/2 (lo-res) PHP/5.2.6 with Suhosin-Patch
Quoting Armin Medosch <armin@easynet.co.uk>:
Hi
on upgrading my site from 5.3 to 6.2 I found out the hard way that this was a big mistake.
Always, always, always, always upgrade a development site first.
Currently images are not shown by the image module and by image gallery though the images are still there and can be displayed by using the html img tag. I browsed the forums but there isn't really a good solution for that, is there?
I'm not familiar with these modules, are they DRUPAL-6 enabled? Have you tried emptying the tables with cache in the name?
the index.php on the start page displays a long list of error messages of the kind
warning: Invalid argument supplied for foreach() in .../drupal-6.2/modules/cck/content.module on line 1096.
A minor nit. You can turn off the display by setting the PHP variable display_errors to 0.
I have browsed the forums and this seems to be a problem that, like with the images, many other people have, but where no clear solution exists?
Sorry, I don't think I can give anything clearer.
finally, magickally, the weighting for blocks administration seems to have gone. so where can I arrange my blocks in 6.2?
IIRC this changed to a drag and drop function using javascript.
Another mistake which was partly my fault, partly due to unclear instructions about how to upgrade was that I first disabled all non core modules and only then backuped the data base which seems to have destroyed tables for some modules such as biblio and all content is gone. as this seems to affect mostly biblio I wonder if there is a way to extract only this table from an older backup of the data base and insert it into the current db. any instructuions on how to would have to be command line version as my server hosts dont offer phpmyadmin
You might want to open an issue against the documentation. Backup is the first step regardless. Also you should open an issue with biblio if all you did was to disable the module for the table to be removed. The table is supposed to remain until you choose the "Uninstall" tab.
I paste my status report below which looks fairly healthy given the problems which I encounter
The only thing I don't know is if you upgraded all your modules to the DRUPAL-6 versions. If not, they will not work. Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
On Tue, 2008-05-27 at 07:28 -0400, Earnie Boyd wrote:
Quoting Armin Medosch <armin@easynet.co.uk>:
Currently images are not shown by the image module and by image gallery though the images are still there and can be displayed by using the html img tag. I browsed the forums but there isn't really a good solution for that, is there?
I'm not familiar with these modules, are they DRUPAL-6 enabled? Have you tried emptying the tables with cache in the name?
these are the standard modules for working with images, the modules image, image gallery and image toolkit as well as image import are, I think drupal core optional. yes, they are drupal 6 sorry, i do not understand "empyting tables with cache in their name". is this something I can do in drupal? or mysql? i can only do mysql on command line there is no web based tool for that on my host. meanwhile another issue came up. two modules, poormanscron and simplenews, keep showing up in the modules list with a red button with cross. it says "This version is incompatible with the 6.2 version of Drupal core." yes but i would love to deinstall them and install the new versions but it does not let me. I rm the modules but they keep showing with this red buttons in the error list. how can i get rid of them and get the new versions apart from those 2 all modules have bene upgraded to new versions regards Armin
the index.php on the start page displays a long list of error messages of the kind
warning: Invalid argument supplied for foreach() in .../drupal-6.2/modules/cck/content.module on line 1096.
A minor nit. You can turn off the display by setting the PHP variable display_errors to 0.
this makes the messages go away but not the error?
IIRC this changed to a drag and drop function using javascript.
this was easy, thanks. sorry for not noticing something so stupid.
Another mistake which was partly my fault, partly due to unclear instructions about how to upgrade was that I first disabled all non core modules and only then backuped the data base which seems to have destroyed tables for some modules such as biblio and all content is gone. as this seems to affect mostly biblio I wonder if there is a way to extract only this table from an older backup of the data base and insert it into the current db. any instructuions on how to would have to be command line version as my server hosts dont offer phpmyadmin
You might want to open an issue against the documentation. Backup is the first step regardless. Also you should open an issue with biblio if all you did was to disable the module for the table to be removed. The table is supposed to remain until you choose the "Uninstall" tab.
I paste my status report below which looks fairly healthy given the problems which I encounter
The only thing I don't know is if you upgraded all your modules to the DRUPAL-6 versions. If not, they will not work.
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
On Wed, May 28, 2008 at 1:37 AM, Armin Medosch <armin@easynet.co.uk> wrote:
On Tue, 2008-05-27 at 07:28 -0400, Earnie Boyd wrote:
Quoting Armin Medosch <armin@easynet.co.uk>:
Currently images are not shown by the image module and by image gallery though the images are still there and can be displayed by using the html img tag. I browsed the forums but there isn't really a good solution for that, is there?
I'm not familiar with these modules, are they DRUPAL-6 enabled? Have you tried emptying the tables with cache in the name?
these are the standard modules for working with images, the modules image, image gallery and image toolkit as well as image import are, I think drupal core optional. yes, they are drupal 6
They are pretty "standard", ok, but not core. They are contributed modules and the Drupal 6 version of http://drupal.org/project/image is still in "alpha". Its issues queue does contain bug reports for serious updating issues. Some have reported that "solved" these issues by wiping out their galleries and starting over. So, it seems that your upgrade was a hasty move if your galleries were important. I you still have the backup of the database before updating you may want to go back and upgrade only to 5.7.
sorry, i do not understand "empyting tables with cache in their name". is this something I can do in drupal? or mysql? i can only do mysql on command line there is no web based tool for that on my host.
If you don't have phpmyadmin, then that meants running "TRUNCATE TABLE" on all tables containing "cache" in their names. But if you ran update.php after the update then that should have been mostly done.
meanwhile another issue came up. two modules, poormanscron and simplenews, keep showing up in the modules list with a red button with cross. it says "This version is incompatible with the 6.2 version of Drupal core." yes but i would love to deinstall them and install the new versions but it does not let me. I rm the modules but they keep showing with this red buttons in the error list. how can i get rid of them and get the new versions
Have you uploaded the 6.x versions and run update.php?
apart from those 2 all modules have bene upgraded to new versions regards Armin
the index.php on the start page displays a long list of error messages of the kind
warning: Invalid argument supplied for foreach() in .../drupal-6.2/modules/cck/content.module on line 1096.
A minor nit. You can turn off the display by setting the PHP variable display_errors to 0.
this makes the messages go away but not the error?
IIRC this changed to a drag and drop function using javascript.
this was easy, thanks. sorry for not noticing something so stupid.
Another mistake which was partly my fault, partly due to unclear instructions about how to upgrade was that I first disabled all non core modules and only then backuped the data base which seems to have destroyed tables for some modules such as biblio and all content is gone. as this seems to affect mostly biblio I wonder if there is a way to extract only this table from an older backup of the data base and insert it into the current db. any instructuions on how to would have to be command line version as my server hosts dont offer phpmyadmin
You might want to open an issue against the documentation. Backup is the first step regardless. Also you should open an issue with biblio if all you did was to disable the module for the table to be removed. The table is supposed to remain until you choose the "Uninstall" tab.
I paste my status report below which looks fairly healthy given the problems which I encounter
The only thing I don't know is if you upgraded all your modules to the DRUPAL-6 versions. If not, they will not work.
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
-- [ Drupal support list | http://lists.drupal.org/ ]
Some have reported that "solved" these issues by wiping out their galleries and starting over.
which I cant because images are from users
So, it seems that your upgrade was a hasty move if your galleries were important.
this is true, I am angry with myself. for my site also audio is important and audio mdule isn't ready for 6
I you still have the backup of the database before updating you may want to go back and upgrade only to 5.7.
Is this safe and are there instructions for that anywhere on the support pages?
If you don't have phpmyadmin, then that meants running "TRUNCATE TABLE" on all tables containing "cache" in their names. But if you ran update.php after the update then that should have been mostly done.
thanks
meanwhile another issue came up. two modules, poormanscron and simplenews, keep showing up in the modules list with a red button with cross. it says "This version is incompatible with the 6.2 version of Drupal core." yes but i would love to deinstall them and install the new versions but it does not let me. I rm the modules but they keep showing with this red buttons in the error list. how can i get rid of them and get the new versions
Have you uploaded the 6.x versions and run update.php?
yep best a.
On Wed, May 28, 2008 at 9:56 AM, Armin Medosch <armin@easynet.co.uk> wrote:
Some have reported that "solved" these issues by wiping out their galleries and starting over.
which I cant because images are from users
So, it seems that your upgrade was a hasty move if your galleries were important.
this is true, I am angry with myself. for my site also audio is important and audio mdule isn't ready for 6
I you still have the backup of the database before updating you may want to go back and upgrade only to 5.7.
Is this safe and are there instructions for that anywhere on the support pages?
Everything is always safe as long as you have your initial un-updated database backup and your user files/images. If you have kept all the files of your old installation you can just put them back, load your old database from the backup, and update settings.php about this. If you haven't kept all the files, you can - upload Drupal 5.7 - the latest proper releases of all your modules (not -dev) - your theme - your user files/images which should be at the same place relative to the site - load the database and update settings.php - and run update.php. (Even if you don't remember all the modules, you can look them up in the system table of the old database)
If you don't have phpmyadmin, then that meants running "TRUNCATE TABLE" on all tables containing "cache" in their names. But if you ran update.php after the update then that should have been mostly done.
thanks
meanwhile another issue came up. two modules, poormanscron and simplenews, keep showing up in the modules list with a red button with cross. it says "This version is incompatible with the 6.2 version of Drupal core." yes but i would love to deinstall them and install the new versions but it does not let me. I rm the modules but they keep showing with this red buttons in the error list. how can i get rid of them and get the new versions
I have tried poormanscron 6.x-1.0 and simplenews 6.x-1.0-beta1 (which are their latest proper releases) and I don't see this problem. Are these the versions you used?
Have you uploaded the 6.x versions and run update.php?
yep
best a.
-- [ Drupal support list | http://lists.drupal.org/ ]
If you have kept all the files of your old installation you can just put them back, load your old database from the backup, and update settings.php about this.
this sounds good, thanks. what is it that I have to tell settings.php about that? and I simply remove all the 6.2 shebang from the html document document root? and copy backk the old files?
I have tried poormanscron 6.x-1.0 and simplenews 6.x-1.0-beta1 (which are their latest proper releases) and I don't see this problem. Are these the versions you used?
yes. I think it is something else. some weird shit that happend during update. I have kept all error messages and have to go through those. the old versions are still listed as modules although they are not there. something in the database I suppose. thanks armin
On Wed, May 28, 2008 at 1:14 PM, Armin Medosch <armin@easynet.co.uk> wrote:
If you have kept all the files of your old installation you can just put them back, load your old database from the backup, and update settings.php about this.
this sounds good, thanks. what is it that I have to tell settings.php
Only if the database name or something else has changed.
about that? and I simply remove all the 6.2 shebang from the html document document root? and copy backk the old files?
If you upload the exact same versions of everything as before, then yes. There is nothing else except the database and the files (and their permissions).
I have tried poormanscron 6.x-1.0 and simplenews 6.x-1.0-beta1 (which are their latest proper releases) and I don't see this problem. Are these the versions you used?
yes. I think it is something else. some weird shit that happend during update. I have kept all error messages and have to go through those. the old versions are still listed as modules although they are not there. something in the database I suppose.
thanks armin
-- [ Drupal support list | http://lists.drupal.org/ ]
Armin Medosch wrote:
Currently images are not shown by the image module and by image gallery though the images are still there and can be displayed by using the html img tag. I browsed the forums but there isn't really a good solution for that, is there?
The upgrade for the image module is still a bit bumpy. There are more details here: http://drupal.org/node/257743 -Jonathan -- Jonathan Hedstrom OpenSourcery http://opensourcery.com Technology for Good
On Mon, 26 May 2008 18:06:21 -0400 "Christopher M. Jones" <cjones@partialflow.com> wrote:
The product catalog should remain fairly stable. It's mainly stock (price, availability, location) that needs to come from the MSSQL db. Prices and availability are managed on site using a custom VB app that talks to the MSSQL server. Quantities, pricing, and locations are constantly changing and need to be accurate to the second.
That looks tricky and that's exactly why I decided to put the business logic into the DB. Especially if you want to be sure that the order is going to be closed with quantities accurate to the second, you'll need some locking mechanism across 2 DB. Then if your DB are in different places latency problem won't make keeping the thing running easier.
Another tricky piece is going to be a 'sell cart' in addition to the traditional 'buy cart'. I foresee this requiring some hacking. Does anyone have initial thoughts on this?
It start to look as if you're going to implement your own e-commerce engine. In Ubercart products are nodes. That means they "should" reside in Drupal DB. If you need "accurate to the second" quantities you'll have to see how Ubercart closes the order and update the inventory. Its design may not be suited for "accurate to the second" inventory management or may just be hard to be converted to a multi DB system. One thing is "on demand" check of inventory status, another is closing an order coherently... or you may rethink your constraint and specification. The core of Ubercart cart "workflow" is actually workflow-ng module that doesn't look to use any lock on the DB, nor transactions, making it pretty hard to transform a basket into an order, saving the correct inventory quantities and updating them with an accuracy "to the second". Ubercart and workflow-ng are pretty impressive pieces of software... and may save you hundreds hours of programming if you're really not that concerned of "accurate to the second" quantities, pricing... and the probability and cost of getting into troubles is acceptable. Consider that the probability of getting into troubles is going to increase if you'll use 2 DB. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Very helpful. Thank you. In view of the fact that precisely what we're looking to avoid is the cost of creating a new engine, what about other solutions? We like Drupal. And if the tweaks we need were simple we'd love to contribute. But if that's just not realistic, then what about eCommerce? I steered clear of it, because reviews of it seemed a bit luke warm. And then there's Zen Cart, which I can't really get a good feeling for, since they seem to have removed their documentation so you have to buy their book. That doesn't give me a lot of confidence. Suggestions? On Tue, 2008-05-27 at 10:51 +0200, Ivan Sergio Borgonovo wrote:
On Mon, 26 May 2008 18:06:21 -0400 "Christopher M. Jones" <cjones@partialflow.com> wrote:
The product catalog should remain fairly stable. It's mainly stock (price, availability, location) that needs to come from the MSSQL db. Prices and availability are managed on site using a custom VB app that talks to the MSSQL server. Quantities, pricing, and locations are constantly changing and need to be accurate to the second.
That looks tricky and that's exactly why I decided to put the business logic into the DB. Especially if you want to be sure that the order is going to be closed with quantities accurate to the second, you'll need some locking mechanism across 2 DB. Then if your DB are in different places latency problem won't make keeping the thing running easier.
Another tricky piece is going to be a 'sell cart' in addition to the traditional 'buy cart'. I foresee this requiring some hacking. Does anyone have initial thoughts on this?
It start to look as if you're going to implement your own e-commerce engine. In Ubercart products are nodes. That means they "should" reside in Drupal DB. If you need "accurate to the second" quantities you'll have to see how Ubercart closes the order and update the inventory. Its design may not be suited for "accurate to the second" inventory management or may just be hard to be converted to a multi DB system.
One thing is "on demand" check of inventory status, another is closing an order coherently... or you may rethink your constraint and specification.
The core of Ubercart cart "workflow" is actually workflow-ng module that doesn't look to use any lock on the DB, nor transactions, making it pretty hard to transform a basket into an order, saving the correct inventory quantities and updating them with an accuracy "to the second".
Ubercart and workflow-ng are pretty impressive pieces of software... and may save you hundreds hours of programming if you're really not that concerned of "accurate to the second" quantities, pricing... and the probability and cost of getting into troubles is acceptable. Consider that the probability of getting into troubles is going to increase if you'll use 2 DB.
-- Ivan Sergio Borgonovo http://www.webthatworks.it
Quoting "Christopher M. Jones" <cjones@partialflow.com>:
Very helpful. Thank you. In view of the fact that precisely what we're looking to avoid is the cost of creating a new engine, what about other solutions? We like Drupal. And if the tweaks we need were simple we'd love to contribute. But if that's just not realistic, then what about eCommerce? I steered clear of it, because reviews of it seemed a bit luke warm. And then there's Zen Cart, which I can't really get a good feeling for, since they seem to have removed their documentation so you have to buy their book. That doesn't give me a lot of confidence.
Suggestions?
If it were me, I would create a Drupal DB engine for mssql for the version you wish to use. I would use Ubercart with changes for the product data you need. It is possible to have multiple DB within the settings.php file so the missing parts are the mssql support within the Drupal DB engines. Edison Wong has a SF project[1] where he is working on supporting more DB engines with Drupal and perhaps you can use his work for the mssql Drupal DB engine. [1] http://sourceforge.net/projects/siren/ Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
On Tuesday 27 May 2008, Earnie Boyd wrote:
Quoting "Christopher M. Jones" <cjones@partialflow.com>:
Very helpful. Thank you. In view of the fact that precisely what we're looking to avoid is the cost of creating a new engine, what about other solutions? We like Drupal. And if the tweaks we need were simple we'd love to contribute. But if that's just not realistic, then what about eCommerce? I steered clear of it, because reviews of it seemed a bit luke warm. And then there's Zen Cart, which I can't really get a good feeling for, since they seem to have removed their documentation so you have to buy their book. That doesn't give me a lot of confidence.
Suggestions?
If it were me, I would create a Drupal DB engine for mssql for the version you wish to use. I would use Ubercart with changes for the product data you need. It is possible to have multiple DB within the settings.php file so the missing parts are the mssql support within the Drupal DB engines. Edison Wong has a SF project[1] where he is working on supporting more DB engines with Drupal and perhaps you can use his work for the mssql Drupal DB engine.
[1] http://sourceforge.net/projects/siren/
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
Drupal currently cannot connect to more than one database type at a time; All databases must be either MySQL or PostgreSQL. While one could write an MS SQL driver for Drupal's current database implementation, there's no guarantee that it would work with the vast array of contrib modules out there; in fact, odds are against you. MS SQL, because AFAIK it doesn't require any additional field handling, is one of the few that are even possible to think about. You would have to move the entire Drupal system over to MS SQL, though. Siren is of no use in practice, because it makes far-reaching changes to the syntax of every single query, changes that no contrib module has or will ever implement. The database layer is being gutted and rewritten for Drupal 7 to address many of these issues; I know that won't help you now, but we are aware of them. :-) -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson
On Tuesday 27 May 2008, Larry Garfield wrote:
Drupal currently cannot connect to more than one database type at a time; All databases must be either MySQL or PostgreSQL. While one could write an MS SQL driver for Drupal's current database implementation, there's no guarantee that it would work with the vast array of contrib modules out there; in fact, odds are against you. MS SQL, because AFAIK it doesn't require any additional field handling, is one of the few that are even possible to think about. You would have to move the entire Drupal system over to MS SQL, though.
I forgot to mention, that limitation applies to Drupal's own database layer only. You can connect to MS SQL or anything else you want as long as you are not using Drupal's database layer for it; that could be a problem or not depending on your use case; (The node table is pretty much bound to only being useful via Drupal's database layer, but if you're doing extensive programming of your own you can point those database calls wherever. I recommend PDO as the tool to use for any extra-Drupal database access.) -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson
Quoting Larry Garfield <larry@garfieldtech.com>:
Drupal currently cannot connect to more than one database type at a time; All databases must be either MySQL or PostgreSQL. While one could write an MS SQL driver for Drupal's current database implementation, there's no guarantee that it would work with the vast array of contrib modules out there; in fact, odds are against you. MS SQL, because AFAIK it doesn't require any additional field handling, is one of the few that are even possible to think about. You would have to move the entire Drupal system over to MS SQL, though.
Well I suppose that the fact that I connect to multiple databases using Drupal makes your point somewhat mute but both databases were MySql though I see no reason why one couldn't be a different DB engine. You use an array of databases in the settings.php file to give the connections and you use db_set_active[1] before performing the queries to the second DB and then after to reset back to the default. There is nothing suggesting to me that the database connections must be the same.
Siren is of no use in practice, because it makes far-reaching changes to the syntax of every single query, changes that no contrib module has or will ever implement.
I only suggested that it could be used as a starting point for the MSSQL engine. I agree that it the changes are too far fetched to be used wholly.
The database layer is being gutted and rewritten for Drupal 7 to address many of these issues; I know that won't help you now, but we are aware of them. :-)
Yes, PDO will help greatly. [1] http://api.drupal.org/api/function/db_set_active/5 Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
On Tuesday 27 May 2008, Earnie Boyd wrote:
Quoting Larry Garfield <larry@garfieldtech.com>:
Drupal currently cannot connect to more than one database type at a time; All databases must be either MySQL or PostgreSQL. While one could write an MS SQL driver for Drupal's current database implementation, there's no guarantee that it would work with the vast array of contrib modules out there; in fact, odds are against you. MS SQL, because AFAIK it doesn't require any additional field handling, is one of the few that are even possible to think about. You would have to move the entire Drupal system over to MS SQL, though.
Well I suppose that the fact that I connect to multiple databases using Drupal makes your point somewhat mute but both databases were MySql though I see no reason why one couldn't be a different DB engine. You use an array of databases in the settings.php file to give the connections and you use db_set_active[1] before performing the queries to the second DB and then after to reset back to the default. There is nothing suggesting to me that the database connections must be the same.
Drupal loads either the database.mysql.inc file or the database.pgsql.inc file. Both files define the same function names. If you try to load both, you get a duplicate function name fatal error and your PHP process dies immediately. Two, three, or eight MySQL databases, no problem. 4 different PostgreSQL databases, great. One of each? Drupal will cry. :-) -- Larry Garfield AIM: LOLG42 larry@garfieldtech.com ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson
On Tue, 27 May 2008 08:39:22 -0400 "Christopher M. Jones" <cjones@partialflow.com> wrote:
Very helpful. Thank you. In view of the fact that precisely what we're looking to avoid is the cost of creating a new engine, what about other solutions? We like Drupal. And if the tweaks we need were simple we'd love to contribute. But if that's just not realistic, then what about eCommerce? I steered clear of it, because reviews of it seemed a bit luke warm. And then there's Zen Cart, which I can't really get a good feeling for, since they seem to have removed their documentation so you have to buy their book. That doesn't give me a lot of confidence.
At a first sight none of them actually reach that level of "consistency". I'd like to sit down and just see pieces of software gluing up by themselves and get the money for my contemplation of the Open Source Universe... <g> but since I had strict requirement on constrains on orders I had to sit down and write my own e-commerce engine and I used Drupal at where it is good at: CMS and community plumbing... plus the fact you get form validation and anti-tampering, theming... and PostgreSQL at where it is good at (coherency, transactions, fun to program). If your MS SQL box is in proximity of your web server (to avoid latency etc...) you may put the e-commerce logic in MS SQL and use Drupal with mysql or postgresql as the display layer. That's exactly what I did at the beginning. I think putting products in nodes and recycling taxonomy doesn't offer enough advantages compared to performance impact and it can't be tuned as much as a general e-commerce site require (at least... integrating nodes and products in a good way required more time than just writing a "product" hook). If you need full text search on the catalogue you'll end up using the one offered by the DB if you've a high traffic website. MS SQL have a pretty different logic for paging and I think adodb drivers are the ones that exploit it better. Zencart offer a basic CMS as other products do. I was looking for a more complete CMS. Without Drupal and PostgreSQL I wouldn't have been able to write such feature rich software in such a "short" time. You may: a) reconsider your requirements. Are they really so strict? If the traffic is not going to be so high etc..., the chances that someone will order the same products at an enough near time to cause inaccuracy may be negligible. One thing is displaying the available quantity at a certain time, another is closing an order trying to avoid "overbooking", another one is closing an order *being sure* you didn't "overbook". b) write your own solution. This is not going to be cheap and you'll have to consider Drupal release cycle and your development time and your "time to market". c) dig deeper in Ubercart/Zen/OsCommerce and prove I was wrong and they can actually be tuned to meet such strict requirements in a 2 DB scenario or they already do what you need. If you chose b) you could: - use mysql/postgresql as Drupal DB and MS SQL as the e-commerce engine DB. This is a feasible choice just if your MS SQL is in proximity with your web server. Consider also security concerns. You'll end up in exploiting some of the characteristics of the DB because you'll need transactions and locking etc... When you'll need more Drupal/e-commerce integration... porting your code from MS SQL to pgsql or mysql may take time. - write your sync logic and move everything in mysql/pgsql. Still, if you *really* need up to the second inventory and your data are on a MS SQL server you're going to write your own purchase logic to be sure that orders contains really available items. The most frequent scenario is... they have their 3rd party closed source accounting application on that MS SQL and this is not going to help. The really most frequent scenario is... they don't have such strict requirements and Ubercart + Drupal may be your best bet. -- Ivan Sergio Borgonovo http://www.webthatworks.it
Good morning, I would like to know if there is an aggregator flow afp and ats for drupal 5.7? Thx
I appreciate everyone's advice. You've been very helpful. I suspect that our spec probably is less rigid than what I made out. We'll know more as we find out more about what this client actually wants. Either way, I think we're still pushing for Drupal. There's just one last thing: what are we going to face as we look at implementing a sell cart? That is, in addition to the traditional buy cart, where customers save items they want to purchase from the company, we need a sell cart where customers place items they want to sell to the company for a set price. Is this going to be a quick hack, or not? On Wed, 2008-05-28 at 10:52 +0200, Ivan Sergio Borgonovo wrote:
On Tue, 27 May 2008 08:39:22 -0400 "Christopher M. Jones" <cjones@partialflow.com> wrote:
Very helpful. Thank you. In view of the fact that precisely what we're looking to avoid is the cost of creating a new engine, what about other solutions? We like Drupal. And if the tweaks we need were simple we'd love to contribute. But if that's just not realistic, then what about eCommerce? I steered clear of it, because reviews of it seemed a bit luke warm. And then there's Zen Cart, which I can't really get a good feeling for, since they seem to have removed their documentation so you have to buy their book. That doesn't give me a lot of confidence.
At a first sight none of them actually reach that level of "consistency". I'd like to sit down and just see pieces of software gluing up by themselves and get the money for my contemplation of the Open Source Universe... <g> but since I had strict requirement on constrains on orders I had to sit down and write my own e-commerce engine and I used Drupal at where it is good at: CMS and community plumbing... plus the fact you get form validation and anti-tampering, theming... and PostgreSQL at where it is good at (coherency, transactions, fun to program).
If your MS SQL box is in proximity of your web server (to avoid latency etc...) you may put the e-commerce logic in MS SQL and use Drupal with mysql or postgresql as the display layer. That's exactly what I did at the beginning. I think putting products in nodes and recycling taxonomy doesn't offer enough advantages compared to performance impact and it can't be tuned as much as a general e-commerce site require (at least... integrating nodes and products in a good way required more time than just writing a "product" hook). If you need full text search on the catalogue you'll end up using the one offered by the DB if you've a high traffic website. MS SQL have a pretty different logic for paging and I think adodb drivers are the ones that exploit it better.
Zencart offer a basic CMS as other products do. I was looking for a more complete CMS.
Without Drupal and PostgreSQL I wouldn't have been able to write such feature rich software in such a "short" time. You may: a) reconsider your requirements. Are they really so strict? If the traffic is not going to be so high etc..., the chances that someone will order the same products at an enough near time to cause inaccuracy may be negligible. One thing is displaying the available quantity at a certain time, another is closing an order trying to avoid "overbooking", another one is closing an order *being sure* you didn't "overbook". b) write your own solution. This is not going to be cheap and you'll have to consider Drupal release cycle and your development time and your "time to market". c) dig deeper in Ubercart/Zen/OsCommerce and prove I was wrong and they can actually be tuned to meet such strict requirements in a 2 DB scenario or they already do what you need.
If you chose b) you could: - use mysql/postgresql as Drupal DB and MS SQL as the e-commerce engine DB. This is a feasible choice just if your MS SQL is in proximity with your web server. Consider also security concerns. You'll end up in exploiting some of the characteristics of the DB because you'll need transactions and locking etc... When you'll need more Drupal/e-commerce integration... porting your code from MS SQL to pgsql or mysql may take time. - write your sync logic and move everything in mysql/pgsql.
Still, if you *really* need up to the second inventory and your data are on a MS SQL server you're going to write your own purchase logic to be sure that orders contains really available items. The most frequent scenario is... they have their 3rd party closed source accounting application on that MS SQL and this is not going to help.
The really most frequent scenario is... they don't have such strict requirements and Ubercart + Drupal may be your best bet.
-- Ivan Sergio Borgonovo http://www.webthatworks.it
Quoting "Christopher M. Jones" <cjones@partialflow.com>:
There's just one last thing: what are we going to face as we look at implementing a sell cart? That is, in addition to the traditional buy cart, where customers save items they want to purchase from the company, we need a sell cart where customers place items they want to sell to the company for a set price. Is this going to be a quick hack, or not?
Isn't your sell cart just allowing for inventory owned by a particular user and providing that user his own buy cart? Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
Depending on the requirements I would have thought webform module could go some way to solving this. psynaptic http://freestylesystems.co.uk http://api.freestylesystems.co.uk On 29 May 2008, at 18:53, Earnie Boyd wrote:
Quoting "Christopher M. Jones" <cjones@partialflow.com>:
There's just one last thing: what are we going to face as we look at implementing a sell cart? That is, in addition to the traditional buy cart, where customers save items they want to purchase from the company, we need a sell cart where customers place items they want to sell to the company for a set price. Is this going to be a quick hack, or not?
Isn't your sell cart just allowing for inventory owned by a particular user and providing that user his own buy cart?
Earnie -- http://for-my-kids.com/ -- http://give-me-an-offer.com/
-- [ Drupal support list | http://lists.drupal.org/ ]
participants (9)
-
Armin Medosch -
Christopher M. Jones -
Cog Rusty -
Da Costa Alcindo -
Earnie Boyd -
Ivan Sergio Borgonovo -
Jonathan Hedstrom -
Larry Garfield -
Richard Burford