rsisco

# Create two stored functions that will be used
DELIMITER $$
CREATE FUNCTION split_string(stringToSplit VARCHAR(256), sign VARCHAR(12), position INT) RETURNS LONGTEXT
BEGIN
	# Return the requested section of the delimited string
	RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit, sign, position),LENGTH(SUBSTRING_INDEX(stringToSplit, sign, position - 1)) + 1), sign, '');
END$$

CREATE FUNCTION get_category_path(catId INT, storeId INT) RETURNS LONGTEXT
BEGIN

rsisco

mysql -u <user> -p <db> -h <host> -e "SELECT distinct case when value like '/%' then concat('.',value) else value end FROM catalog_product_entity_media_gallery" | awk '{print $1}' | xargs ls | grep "cannot access"

rsisco

#!/bin/sh
zip -qr foo.zip "$@" && unzip -aqo foo.zip && rm foo.zip

# Usage: fixascii [files or directories to convert]
# Taken from http://hints.macworld.com/article.php?story=20031018164326986

rsisco

# Select data needed for generating URL rewrites for M2
SELECT url AS old_uri, '' AS new_uri, category_id, product_id, sku FROM m1_url_export WHERE store_id = ?;

rsisco

###################################################################
# Table creation SQL
CREATE TABLE `google_analytics_export` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `page` varchar(255) DEFAULT NULL,
  `pageviews` int(11) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18180 DEFAULT CHARSET=latin1;

rsisco

# This script assumes that a table has been created (m1_url_export) containing the output generated by https://gist.github.com/rsisco/a14b3383b2ab9f6435139d107e428a43
# as well as a table (google_analytics_export) that contains page request paths and counts from Google Analytics:

# Select the path without the query string
SELECT SUBSTRING_INDEX(page, '?', 1) AS page, sum(pageviews) AS pageviews FROM google_analytics_export AS gae
LEFT JOIN m1_url_export AS m ON gae.page = m.url and gae.store_id = m.store_id
# Only include results with no M1 match
WHERE m.url IS NULL
# Exclude paths we don't need to know about to keep the list pared down
AND page NOT LIKE '/catalog/product/gallery/%'

rsisco

# Enabled product URLs for provided store ID
SELECT
	r.store_id,
	request_path, concat('/', request_path) AS url,
	concat('/', target_path) AS target_url,
	CASE 
		WHEN r.options IS NULL THEN 'internal'
		WHEN r.options = 'RP' THEN '301'
		WHEN r.options = 'R' THEN '302'
	END AS url_type,

rsisco

DELIMITER ;;
CREATE DEFINER=`magento_prod`@`%` FUNCTION `generate_unique_url_key`(target_value_id INT) RETURNS LONGTEXT CHARSET latin1
    READS SQL DATA
BEGIN
	DECLARE url_key_attr_id INT DEFAULT 124;
	DECLARE counter INT DEFAULT 1;
	DECLARE url_key LONGTEXT;
	DECLARE first_pass BOOLEAN DEFAULT TRUE;
	# Retrieve current URL key for provided value_id row
	SELECT `VALUE` INTO @current_url_key FROM catalog_product_entity_varchar WHERE value_id = target_value_id;

rsisco

# Create two stored functions that will be used
DELIMITER $$
CREATE FUNCTION split_string(stringToSplit VARCHAR(256), sign VARCHAR(12), position INT) RETURNS LONGTEXT
BEGIN
	# Return the requested section of the delimited string
	RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit, sign, position),LENGTH(SUBSTRING_INDEX(stringToSplit, sign, position - 1)) + 1), sign, '');
END$$

CREATE FUNCTION get_category_path(catId INT, storeId INT) RETURNS LONGTEXT
BEGIN