view release on metacpan or  search on metacpan

examples/amazon_mws.sql  view on Meta::CPAN

DROP TABLE IF EXISTS amazon_mws_feeds;
DROP TABLE IF EXISTS amazon_mws_products;

CREATE TABLE amazon_mws_jobs (
      amws_job_id VARCHAR(64) NOT NULL,
      shop_id VARCHAR(64) NOT NULL,
      task VARCHAR(64) NOT NULL,
      -- if complete one or those has to be set.
      aborted BOOLEAN NOT NULL DEFAULT FALSE,
      success BOOLEAN NOT NULL DEFAULT FALSE,
      last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (amws_job_id, shop_id)
); 

CREATE TABLE amazon_mws_feeds (
       amws_feed_pk SERIAL NOT NULL PRIMARY KEY,
       -- not strictly needed, but helpful anyway
       shop_id VARCHAR(64) NOT NULL,
       amws_job_id VARCHAR(64) NOT NULL REFERENCES amazon_mws_jobs(amws_job_id),
       feed_id VARCHAR(64) UNIQUE, -- populated when we get the id
       feed_name VARCHAR(255) NOT NULL,
       feed_file VARCHAR(255) NOT NULL UNIQUE,
       processing_complete BOOLEAN NOT NULL DEFAULT FALSE,
       aborted BOOLEAN NOT NULL DEFAULT FALSE,
       success BOOLEAN NOT NULL DEFAULT FALSE,
       errors TEXT,
       notes  TEXT,
       last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- table to keep track of the uploaded items

CREATE TABLE amazon_mws_products (
       -- don't enforce the sku format
       sku VARCHAR(255) NOT NULL,
       shop_id VARCHAR(64) NOT NULL,
       -- given that we just test for equality, don't enforce a type.
       -- So an epoch will do just fine, as it would be a random date,
       -- as long as the script sends consistent data
       timestamp_string VARCHAR(255) NOT NULL DEFAULT '0',
       status VARCHAR(32),
       -- this can be null
       amws_job_id VARCHAR(64) REFERENCES amazon_mws_jobs(amws_job_id),
       error_code integer NOT NULL DEFAULT '0',
       error_msg TEXT,
       listed_date DATETIME,
       -- our update
       last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       PRIMARY KEY (sku, shop_id)
);

CREATE TABLE amazon_mws_orders (
       shop_id         VARCHAR(64) NOT NULL,
       amazon_order_id VARCHAR(64) NOT NULL,
       shop_order_id   VARCHAR(64) NOT NULL,
       amws_job_id     VARCHAR(64) REFERENCES amazon_mws_jobs(amws_job_id),
       error_code INTEGER NOT NULL DEFAULT 0,
       error_msg TEXT,
       confirmed BOOLEAN NOT NULL DEFAULT FALSE,
       last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       PRIMARY KEY (shop_id, amazon_order_id)
);

ALTER TABLE amazon_mws_orders ADD COLUMN shipping_confirmation_job_id VARCHAR(64) REFERENCES amazon_mws_jobs(amws_job_id);
ALTER TABLE amazon_mws_orders ADD COLUMN shipping_confirmation_ok BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE amazon_mws_orders ADD COLUMN shipping_confirmation_error TEXT;
ALTER TABLE amazon_mws_jobs   ADD COLUMN job_started_epoch INTEGER;
ALTER TABLE amazon_mws_jobs   ADD COLUMN status VARCHAR(255);

-- 2015-01-29

t/job-selection.t  view on Meta::CPAN

my $uploader = Amazon::MWS::Uploader->new(%constructor);
ok ($uploader->dbh);
my $create_table =<<'SQL';
CREATE TABLE amazon_mws_jobs (
      amws_job_id VARCHAR(64) NOT NULL,
      shop_id VARCHAR(64) NOT NULL,
      task VARCHAR(64) NOT NULL,
      -- if complete one or those has to be set.
      aborted BOOLEAN NOT NULL DEFAULT FALSE,
      success BOOLEAN NOT NULL DEFAULT FALSE,
      last_updated TIMESTAMP,
      job_started_epoch INTEGER,
      status VARCHAR(255),
      PRIMARY KEY (amws_job_id, shop_id)
); 
SQL
my $create_p_table = <<'SQL';
CREATE TABLE amazon_mws_products (
       -- don't enforce the sku format
       sku VARCHAR(255) NOT NULL,
       shop_id VARCHAR(64) NOT NULL,

t/job-selection.t  view on Meta::CPAN

       -- So an epoch will do just fine, as it would be a random date,
       -- as long as the script sends consistent data
       timestamp_string VARCHAR(255) NOT NULL DEFAULT '0',
       status VARCHAR(32),
       -- this can be null
       amws_job_id VARCHAR(64) REFERENCES amazon_mws_jobs(amws_job_id),
       error_code integer NOT NULL DEFAULT '0',
       error_msg TEXT,
       listed_date DATETIME,
       -- our update
       last_updated TIMESTAMP,
       PRIMARY KEY (sku, shop_id)
);
SQL
$uploader->dbh->do('DROP TABLE IF EXISTS amazon_mws_jobs');
$uploader->dbh->do($create_table) or die;
$uploader->dbh->do('DROP TABLE IF EXISTS amazon_mws_products');
$uploader->dbh->do($create_p_table) or die;

my $dbh = $uploader->dbh;


my @populate = get_sample_records('t/jobs.txt');
my $pop_sth = $dbh->prepare("INSERT INTO amazon_mws_jobs (amws_job_id, shop_id, task, aborted, success, last_updated, job_started_epoch, status) values (?, ?, ?, 0, 0, ?, ?, NULL)");
$dbh->begin_work;
foreach my $sample (@populate) {
    $pop_sth->execute(@$sample);
}
$dbh->commit;

sub get_sample_records {
    my $file = shift;
    my @records;
    open (my $fh, '<', $file) or die "Cannot open $file $!";