Drop all tables from a Drupal MySQL database

Handy command to mass-drop all tables:

$ drush sql-dump | grep DROP | drush sql-cli

Leave a Comment

Filed under Code, Database

How to evaluate a new CMS or web development platform

Question from a long-time client and friend, who runs a web agency: “I’m getting a demo of SalesForce’s new SiteForce CMS next week with a client in the SF Bay Area. In order to evaluate it properly, what questions do we need answers to? ┬áCan you please put together a list for me?

When evaluating a new CMS with no particular project or client in mind, the questions that come to mind are:

  1. Is it easy to use, can I hand it off to a non-technical client and have them update it themselves, or will it require a developer to maintain (or more like, how much can the client do themselves before they call us)
  2. What platform does it run on? (Hosted/SaaS, PHP, Ruby, etc)
  3. What functionality is available out of the box, is it focused on a particular use case or market (eCommerce, landing pages, car dealers, web applications, etc)? Are there things I can’t use the platform for?
  4. Does it provide basic CMS functionality and user authentication out of the box – how much time to spin up a new project? Will I need to do a lot of duplicate work to get started with each new project?
  5. What’s the licensing model?
  6. Will it add fixed costs to my project? Can I resell work I produce on/for this platform?
  7. What’s a rough time and materials estimate for creating a simple 5-page custom CMS-driven brochure website on this platform? What’s a rough estimate for creating a complex eCommerce or web application project? How does it compare to other platforms?
  8. Is it extendable? How can I add functionality if I need to? Is there an API and is it well documented?
  9. Are there architectural constraints on scale – this can work both ways (eg. is it too simple or slow for a large complex site, or is it too large and cumbersome for a small project)
  10. Can I export my data from the CMS?
  11. Can I import data from third parties? (even if that’s just being able to write a script to do raw SQL queries)
  12. Is there a community of developers and users who are innovating and building useful functionality or attractive themes?
  13. Is it white-label or does it present it’s own brand? Will that brand help or hinder the project (ie. WordPress is trusted and known for being easy, most clients recognize the name and have a positive association with it)
  14. Why is it better than building my own CMS?
  15. Why is it better than using competing CMS products? (Usually compare against WordPress for low-end and Drupal for mid/high-end)
  16. How will this help my clients achieve their business objectives?
  17. What provisions does it provide for SEO?
  18. Can I easily add a custom theme? Do I have full control over every aspect of the output if I want to, or are there certain parts that I cannot change?
  19. Can I add custom data structures (content types)? If I can, am I able to expose them to the plugin architecture or am I building a standalone system inside the CMS?
  20. What provisions does it provide for spam protection?
  21. What provisions does it provide for authorization/permissions? Can I have multiple levels of user with different permissions? Content workflow?
  22. Does it have a security release history with a track record of fast responses, or a dedicated security team?
  23. Is there a portfolio of high-quality sites built on the platform, or is it untested in the real world?
  24. Is it easy to find other people who can work on the platform? Will it be easy to find someone to take over the project, either as a subcontractor, or if the client wants to use a different developer, or wants to hire a full-time employee to work on the site.
  25. What’s the scalability like? At what traffic levels do I need to start caring about scalability/performance? Does it have any provisions for increasing performance inside the software? (eg. caching or aggregation). Are there smart people working on this issue?
  26. How can I run more than one site on this platform? Can I maintain a shared codebase or run multiple sites from one instance, or would each install need to be maintained separately?

Add a comment if you think I missed anything important.

1 Comment

Filed under Business

How to set up a free Paypal account to accept credit cards on your Drupal Commerce / Ubercart Shopping Cart website

Question from a colleague: “I have both Drupal 6 sites (Ubercart) and Drupal 7 (Ubercart or Drupal Commerce) sites that need shopping cart integration. Do I need to have the PayPal $30 a month merchant account to integrate using the API? Or can I integrate with the “free” PayPal account?

There’s two (or more) ways to integrate Paypal – Website Payments Standard and Website Payments Pro. The simpler option takes the user via Paypal to process the payment and is free to implement. The “white label” version where the user never leaves your site is paid.

Used Paypal with UC a few times and it’s always been easy to implement and hassle free. The Payments Standard does allow paying with a credit card but users can get confused and the conversion rate drops because they assume they need a Paypal account to pay (they do not, but the option to pay using a CC with no Paypal account is a bit hard to see).

If you expect a lot of people checking out who do not have Paypal or don’t understand the system (ie. non-technical users), and a 5-10% drop in conversions is greater than the monthly price of Payments Pro, then it’s worth upgrading to the Pro version. Also, if you’re going to be doing mid-$x,000+ a month in transactions then compare the cost of Paypal to the cost of a full authorize.net merchant account, as there’s a point where Paypal’s high fees make it cheaper to go with a traditional merchant account.

Original discussion (that people requested I write up as a blog post): http://groups.drupal.org/node/165544

Leave a Comment

Filed under Business, eCommerce

Urgent, Urgenter, Urgentest: Prioritizing Support Issues

Helped a client work through some issues with their support ticket workflow for a large enterprise Drupal website. Too many issues were being defined as high priority or urgent – making it difficult to spot what was a real emergency. We came up with these guidelines for priority assignment:

  1. Low – “Would be nice if” items, no immediate need to do this, work on it if you are out of other things to do/time is left in the budget. eg. Improving features that are already working well, back-end functions like non-essential module upgrades, improving processes that already work well, applying silver lining to clouds.
  2. Medium – Should do this item, if everything else in higher priorities is taken care of. Pre-planned upgrades with a schedule. Should not need to do any dual-entry (testing on development and immediately deploying on production) for this, can be migrated as part of a normal release cycle. Should be estimated prior to start and not go over budget. eg. Feature additions, upgrades, etc. Most day-to-day operations should go here.
  3. High – Must do this, during regular working hours, (optionally) to a defined timeframe. May require a hotfix to production. eg. New product page updates or contact phone numbers, landing pages/blog posts to respond to time-sensitive crisis.
  4. Urgent – Stop work on other tickets at lower priorities and focus all attention on this. It must be fixed as soon as possible, even if that means working late, going over hours or delaying other tasks until this is done. Will probably require a hotfix to production once a solution has been identified and tested on development. eg. Bugs that are preventing products from being accessed from certain views, broken download links to legally required documentation.
  5. Immediate – Wake people up in the middle of the night if you need to, this is a serious issue that is causing business to be affected or leaving the company unable to function. eg. Contact form is not routing mail, serious server crash, data loss, building literally on fire.

Depending on the organization, Urgent and Immediate issues may also trigger a page to senior management and require providing a status updates to them before the issue can be signed off as completed.

Leave a Comment

Filed under Project Management

Set a MySQL column to random numbers, 1-line query

Useful query to seed a database table with random numbers. I used this for testing a support desk application:

UPDATE support_ticket SET priority = CAST((RAND() * 3)+1 AS UNSIGNED);

Where “3″ is the highest number in your range. By default, numbers range from 0-3, but in our case we needed to set the numbers from 1-4, so we set our max as 3 and add one at the end.

1 Comment

Filed under Database

How to retrieve last MySQL INSERT ID from Drupal 7 db_merge()

Drupal 7′s DBTNG database abstraction layer introduces the db_merge() command which replaces the if($id) { db_update() } else { db_insert() } construct. Very useful.

One issue I ran into is retrieving the last MySQL insert ID. db_insert() returns the ID as expected, but db_merge() only returns a STATUS_INSERT or STATUS_UPDATE integer value, which isn’t much good for anything.

Reading through Drupal 7′s source code, modules/simpletest/tests/database_test.test contained an elegant solution where we run a SELECT query using the known values to pull the row back out of the database, where the ID is correctly set.

$result = db_merge('test_people')
 ->key(array('job' => 'Presenter'))
 ->fields(array(
 'age' => 31,
 'name' => 'Tiffany',
 ))
 ->execute();
 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', \
             array(':job' => 'Presenter'))->fetch();
 $last_insert_id = $person->id; // Core test does not actually include an ID,
                                // but this is how it could work

3 Comments

Filed under Code

Easiest way to fix MySQL UTF8 to Latin1 character set encoding issues.

MySQL originally used the latin1 character set by default which stored characters in a 2-byte sequence. In recent versions it defaults to UTF-8 to be friendlier to international users.

When migrating MySQL databases, occasionally you’ll see odd characters appear on the new system. For example, a simple quote mark may be replaced by 4-5 characters of junk symbols.

This happens when MySQL is trying to display characters using a different character set to the one they are stored in. To fix, we need to make sure the database is marked as latin1 when we export it from the old system, and then re-encode it into UTF-8 when importing it into it’s new home.

Export:

mysqldump -u $user -p --opt --quote-names --skip-set-charset \
--default-character-set=latin1 $dbname > dump.sql

Import:

mysql -u $user -p --default-character-set=utf8 $dbname < dump.sql

7 Comments

Filed under Code