. July 2006 * * These database fields are from: * https://www.paypal.com/en_US/pdf/PP_OrderManagement_IntegrationGuide.pdf * See also: * https://www.paypal.com/IntegrationCenter/ic_ipn-pdt-variable-reference.html * https://www.paypal.com/uk/cgi-bin/webscr?cmd=p/acc/ipn-subscriptions-outside */ // TODO: Once I've been running lm_paypal for a while work out which // ipn fields are unnecessary and remove them. function lm_paypal_install() { drupal_install_schema('lm_paypal'); } function lm_paypal_uninstall() { drupal_uninstall_schema('lm_paypal'); } function lm_paypal_schema() { $schema['lm_paypal_ipns'] = array( 'fields' => array( 'id' => array('type' => 'serial', 'not null' => TRUE, 'description' => 'My ID for received IPNs'), 'timestamp' => array('type' => 'int', 'not null' => TRUE, 'unsigned' => TRUE, 'description' => 'unix time when ipn arrived'), 'txn_id' => array('type' => 'varchar', 'length' => 20, 'not null' => TRUE, 'default' => '', 'description' => 'Paypal generated transaction id'), 'processed' => array('type' => 'int', 'size' => 'tiny', 'default' => 0, 'description' => 'set if processed'), 'test_ipn' => array('type' => 'int', 'size' => 'tiny', 'description' => 'only set by sandbox to 1'), 'verify_sign' => array('type' => 'varchar', 'length' => 120), # Website Payments Standard, Website Payments Pro, and Refund Information # SOME FIELDS OMITTED 'parent_txn_id' => array('type' => 'varchar', 'length' => 20, 'description' => 'In the case of a refund, reversal, or canceled reversal, this variable contains the txn_id of the original transaction, while txn_id contains a new ID for the new transaction.'), 'payment_date' => array('type' => 'varchar', 'length' => 20, 'description' => 'Time/Date stamp generated by PayPal , in the following format: HH:MM:SS DD Mmm YY, YYYY PST'), 'payment_status' => array('type' => 'varchar', 'length' => 20, 'description' => 'Canceled_Reversal,Completed,Denied,Expired,Failed,In-Progress,Partially-Refunded,Pending,Processed,Refunded,Reversed,Voided'), 'payment_type' => array('type' => 'varchar', 'length' => 20, 'description' => 'echeck,instant'), 'pending_reason' => array('type' => 'varchar', 'length' => 20, 'description' => 'address,authorization,echeck,intl,multicurrency,unilateral,upgrade,verify,other'), 'reason_code' => array('type' => 'varchar', 'length' => 20, 'description' => 'chargeback,guarantee,buyer-complaint,refund,other'), # Buyer Information 'payer_email' => array('type' => 'varchar', 'length' => 127, 'description' => 'Customers primary email address. Use this email to provide any credits.'), 'first_name' => array('type' => 'varchar', 'length' => 64, 'description' => 'Customers first name'), 'last_name' => array('type' => 'varchar', 'length' => 64, 'description' => 'Customers last name'), 'payer_business_name' => array('type' => 'varchar', 'length' => 127, 'description' => 'Customers company name, if customer represents a business'), 'address_name' => array('type' => 'varchar', 'length' => 128, 'description' => 'Name used with address (included when the customer provides a Gift Address)'), 'address_street' => array('type' => 'varchar', 'length' => 200, 'description' => 'Customers street address'), 'address_city' => array('type' => 'varchar', 'length' => 40, 'description' => 'City of customers address'), 'address_zip' => array('type' => 'varchar', 'length' => 20, 'description' => 'Zip code of customers address.'), 'address_country' => array('type' => 'varchar', 'length' => 64, 'description' => 'Country of customers address.'), 'address_country_code' => array('type' => 'varchar', 'length' => 2, 'description' => 'Two-character ISO 3166 country code'), 'address_state' => array('type' => 'varchar', 'length' => 40, 'description' => 'State of customers address'), 'address_status' => array('type' => 'int', 'size' => 'tiny', 'description' => 'Customer provided a confirmed/unconfirmed address.'), 'payer_id' => array('type' => 'varchar', 'length' => 13, 'description' => 'Unique customer ID.'), 'payer_status' => array('type' => 'varchar', 'length' => 40, 'description' => 'Customer has a Verified|Unverified PayPal account.'), 'residence_country' => array('type' => 'varchar', 'length' => 2, 'description' => 'Two-character ISO 3166 country code'), # Business Information 'business' => array('type' => 'varchar', 'length' => 127, 'description' => 'Email address or account ID of the payment recipient (that is, the merchant). Equivalent to the values of receiver_email (if payment is sent to primary account) and business set in the Website Payment HTML. NOTE: The value of this variable is normalized to lowercase characters.'), 'item_name' => array('type' => 'varchar', 'length' => 127, 'description' => 'Item name as passed by you, the merchant. Or, if not passed by you, as entered by your customer. If this is a shopping cart transaction, PayPal will append the number of the item (e.g., item_name1, item_name2).'), 'quantity' => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0), 'shipping' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2), 'item_number' => array('type' => 'varchar', 'length' => 127, 'description' => 'Pass-through variable for you to track purchases. It will get passed back to you at the completion of the payment. If omitted, no variable will be passed back to you.'), 'receiver_email' => array('type' => 'varchar', 'length' => 127, 'description' => 'Primary email address of the payment recipient (that is, the merchant). If the payment is sent to a non-primary email address on your PayPal account, the receiver_email is still your primary email. NOTE: The value of this variable is normalized to lowercase characters.'), 'receiver_id' => array('type' => 'varchar', 'length' => 13, 'description' => 'Unique account ID of the payment recipient (i.e., the merchant). This is the same as the recipient\'s referral ID.'), # Advanced and Custom Information 'custom' => array('type' => 'varchar', 'length' => 255, 'description' => 'Custom value as passed by you, the merchant. These are passthrough variables that are never presented to your customer'), 'invoice' => array('type' => 'varchar', 'length' => 127, 'description' => 'Passthrough variable you can use to identify your Invoice Number for this purchase. If omittted, no variable is passed back.'), 'memo' => array('type' => 'varchar', 'length' => 255, 'description' => 'Memo as entered by your customer in PayPal Website Payments note field.'), 'option_name1' => array('type' => 'varchar', 'length' => 64, 'description' => 'Option 1 name as requested by you.'), 'option_name2' => array('type' => 'varchar', 'length' => 64, 'description' => 'Option 2 name as requested by you.'), 'option_selection1' => array('type' => 'varchar', 'length' => 200, 'description' => 'Option 1 choice as entered by your customer.'), 'option_selection2' => array('type' => 'varchar', 'length' => 200, 'description' => 'Option 2 choice as entered by your customer.'), 'tax' => array('type' => 'varchar', 'length' => 20, 'description' => 'Amount of tax charged on payment.'), # SOME FIELDS OMITTED 'mc_currency' => array('type' => 'varchar', 'length' => 3, 'description' => 'Three character currency code'), # SOME FIELDS OMITTED 'payment_fee' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'USD transaction fee associated with the payment. payment_gross minus payment_fee equals the amount deposited into the receiver email account. Is empty for non-USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee. NOTE: This is a deprecated field. Use mc_fee instead.'), 'payment_gross' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Full USD amount of the customer\'s payment, before transaction fee is subtracted. Will be empty for non-USD payments. This is a legacy field replaced by mc_gross. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.'), 'mc_fee' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Transaction fee associated with the payment. mc_gross minus mc_fee equals the amount deposited into the receiver_email account. Equivalent to payment_fee for USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction fee.'), 'mc_gross' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => ' Full amount of the customer\'s payment, before transaction fee is subtracted. Equivalent to payment_gross for USD payments. If this amount is negative, it signifies a refund or reversal, and either of those payment statuses can be for the full or partial amount of the original transaction.'), 'settle_amount' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Amount that is deposited into the accounts primary balance after a currency conversion from automatic conversion (through your Payment Receiving Preferences) or manual conversion (through manually accepting a payment).'), 'settle_currency' => array('type' => 'varchar', 'length' => 3, 'description' => 'Three character currency code.'), 'exchange_rate' => array('type' => 'numeric', 'precision' => 12, 'scale' => 6, 'description' => 'Exchange rate used if a currency conversion occurred.'), # Auctions - NOT SUPPORTED # Mass Payment - NOT SUPPORTED # Subscriptions Variables 'txn_type' => array('type' => 'varchar', 'length' => 20, 'description' => 'subscr_signup, subscr_cancel, subscr_failed, subscr_payment, subscr_eot, subscr_modify'), 'subscr_date' => array('type' => 'varchar', 'length' => 28, 'description' => 'Start date or cancellation date depending'), 'subscr_effective' => array('type' => 'varchar', 'length' => 28, 'description' => 'Date when the subscription modification will be effective'), 'period1' => array('type' => 'varchar', 'length' => 20, 'description' => 'Trial subscription interval in days, weeks, months, years (example: a 4 day interval is period1: 4 D).'), 'period2' => array('type' => 'varchar', 'length' => 20, 'description' => 'Trial subscription interval in days, weeks, months, or years.'), 'period3' => array('type' => 'varchar', 'length' => 20, 'description' => 'Regular subscription interval in days, weeks, months, or years.'), 'amount1' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Amount of payment for trial period 1 for USD payments; otherwise blank (optional).'), 'amount2' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'A mount of payment for trial period 2 for USD payments; otherwise blank (optional).'), 'amount3' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Amount of payment for regular subscription period for USD payments; otherwise blank.'), 'mc_amount1' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Amount of payment for trial period 1, regardless of currency (optional).'), 'mc_amount2' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Amount of payment for trial period 2, regardless of currency (optional).'), 'mc_amount3' => array('type' => 'numeric', 'precision' => 10, 'scale' => 2, 'description' => 'Amount of payment for regular subscription period, regardless of currency.'), 'recurring' => array('type' => 'int', 'size' => 'tiny', 'description' => 'Indicates whether regular rate recurs (1 is yes, blank is no).'), 'reattempt' => array('type' => 'int', 'size' => 'tiny', 'description' => 'Indicates whether reattempts should occur upon payment failures (1 is yes, blank is no).'), 'retry_at' => array('type' => 'varchar', 'length' => 28, 'description' => 'Date PayPal will retry a failed subscription payment.'), 'recur_times' => array('type' => 'int', 'description' => 'The number of payment installments that will occur at the regular rate.'), 'subscr_id' => array('type' => 'varchar', 'length' => 20, 'description' => 'ID generated by PayPal for the subscriber.'), ), 'indexes' => array( 'txn_id' => array('txn_id'), ), 'primary key' => array('id'), ); return $schema; }