tag:blogger.com,1999:blog-30455518384931912752024-02-20T06:39:52.703-08:00tiplicks!linux, shell, perl, php and mysql tips and tricksRoryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.comBlogger177125tag:blogger.com,1999:blog-3045551838493191275.post-35971878777623169542018-10-06T10:23:00.004-07:002018-10-06T10:23:50.645-07:00Formula for pricing buy X get X (buy 1 get 1 free) promospython code:
<pre class="brush:bash;light: true">
each_price = ( (current_price * quantity) - (current_price * (int(quantity / (buyX + getX) * getX) ) ) ) / quantity
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-36104903882332200562018-07-24T08:16:00.001-07:002018-07-24T08:16:12.607-07:00MySQL insert or update a random value from a set of given values<pre class="brush: bash; light: true">
UPDATE my_table SET my_field = (SELECT sub.code FROM (SELECT '01' AS code
UNION SELECT '02'
UNION SELECT '53'
UNION SELECT '54'
UNION SELECT '55'
UNION SELECT '56') as sub
ORDER BY RAND() LIMIT 1)
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-64411613219484248622017-12-18T08:37:00.000-08:002018-01-15T11:05:50.634-08:00MySQL search and replace unicode (hexidecimal) characters with HTML entitiesNot really pretty, but this will replace <a href="https://en.wikipedia.org/wiki/List_of_Unicode_characters">Latin-1 Supplement and Latin Extended-A characters</a> (plus permutations of quotes, dashes and apostrophes) with their corresponding HTML entities. NOTE: if you use this, copy the queries from the source code of this page, the browser is translating the entities. Query is operating on a column called DESCRIPTION.
<pre class="brush: bash;light: true">
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A0'),' ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A1'),'¡');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A2'),'¢');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A3'),'£');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A4'),'¤');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A5'),'¥');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A6'),'¦');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A7'),'§');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A8'),'¨');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00A9'),'©');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AA'),'ª');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AB'),'«');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AC'),'¬');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AD'),'­');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AE'),'®');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00AF'),'¯');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B0'),'°');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B1'),'±');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B2'),'²');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B3'),'³');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B4'),'´');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B5'),'µ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B6'),'¶');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B7'),'·');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B8'),'¸');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),'¹');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),'º');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BB'),'»');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BC'),'¼');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BD'),'½');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BE'),'¾');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BF'),'¿');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C0'),'À');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C1'),'Á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'Â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'Ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C4'),'Ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C5'),'Å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C6'),'Æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C7'),'Ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C8'),'È');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C9'),'É');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CA'),'Ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CB'),'Ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CC'),'Ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CD'),'Í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CE'),'Î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00CF'),'Ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D0'),'Ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D1'),'Ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D2'),'Ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D3'),'Ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D4'),'Ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D5'),'Õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D6'),'Ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D9'),'Ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DA'),'Ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DB'),'Û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DC'),'Ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DD'),'Ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DE'),'Þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00DF'),'ß');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E0'),'à');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E1'),'á');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'â');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E3'),'ã');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E4'),'ä');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E5'),'å');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E6'),'æ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E7'),'ç');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E8'),'è');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E9'),'é');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EA'),'ê');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EB'),'ë');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EC'),'ì');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00ED'),'í');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EE'),'î');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00EF'),'ï');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F0'),'ð');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F1'),'ñ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F2'),'ò');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F3'),'ó');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F4'),'ô');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F5'),'õ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F6'),'ö');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F7'),'÷');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F8'),'ø');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00F9'),'ù');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FA'),'ú');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FB'),'û');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FC'),'ü');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FD'),'ý');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00FE'),'þ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0100'),'Ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0101'),'ā');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0102'),'Ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0103'),'ă');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0104'),'Ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0105'),'ą');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0106'),'Ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0107'),'ć');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0108'),'Ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0109'),'ĉ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010A'),'Ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010B'),'ċ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010C'),'Č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010D'),'č');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010E'),'Ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'010F'),'ď');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0110'),'Đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0111'),'đ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0112'),'Ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0113'),'ē');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0114'),'&Ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0115'),'&ebreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0116'),'Ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0117'),'ė');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0118'),'Ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0119'),'ę');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011A'),'Ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011B'),'ě');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011C'),'Ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011D'),'ĝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011E'),'Ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'011F'),'ğ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0120'),'Ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0121'),'ġ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0122'),'Ģ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0124'),'Ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0125'),'ĥ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0126'),'Ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0127'),'ħ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0128'),'Ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0129'),'ĩ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012A'),'Ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012B'),'ī');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012C'),'&Ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012D'),'&ibreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012E'),'Į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'012F'),'į');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0130'),'İ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0131'),'ı');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0132'),'IJ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0133'),'ij');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0134'),'Ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0135'),'ĵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0136'),'Ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0137'),'ķ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0139'),'Ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013A'),'ĺ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013B'),'Ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013C'),'ļ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013D'),'Ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'013E'),'ľ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0141'),'Ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0142'),'ł');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0143'),'Ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0144'),'ń');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0145'),'Ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0146'),'ņ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0147'),'Ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0148'),'ň');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014A'),'Ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014B'),'ŋ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014C'),'Ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014D'),'ō');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014E'),'&Obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'014F'),'&obreve;');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0150'),'Ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0151'),'ő');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0152'),'Œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0153'),'œ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0154'),'Ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0155'),'ŕ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0156'),'Ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0157'),'ŗ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0158'),'Ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0159'),'ř');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015A'),'Ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015B'),'ś');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015C'),'Ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015D'),'ŝ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015E'),'Ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'015F'),'ş');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0160'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0161'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0162'),'Ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0163'),'ţ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0164'),'Ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0165'),'ť');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0166'),'Ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0167'),'ŧ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0168'),'Ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0169'),'ũ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016A'),'Ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016B'),'ū');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016C'),'Ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016D'),'ŭ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016E'),'Ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'016F'),'ů');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0170'),'Ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0171'),'ű');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0172'),'Ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0173'),'ų');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0174'),'Ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0175'),'ŵ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0176'),'Ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0177'),'ŷ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0178'),'Ÿ');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0179'),'Ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017A'),'ź');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017B'),'Ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017C'),'ż');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017D'),'Ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'017E'),'ž');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00D7'),'×');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0096'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0085'),'…');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009A'),'š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'008A'),'Š');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2013'),'—');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2014'),'–');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2015'),'&mdash');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009C'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'009D'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0093'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0094'),'"');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00E2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C3'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00C2'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0080'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0082'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0083'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0000'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0099'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00BA'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'00B9'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0098'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2018'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'201A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2019'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'2039'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'203A'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, char(x'0092'),''');
UPDATE products SET DESCRIPTION = replace(DESCRIPTION, '—','—');
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-58870080013727121592017-12-11T12:40:00.002-08:002017-12-11T12:40:09.435-08:00Find all non-ascii characters in MySQl column<pre class="brush:bash;light:true">
SELECT *
FROM products
WHERE DESCRIPTION <> CONVERT(DESCRIPTION USING ASCII)
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-34296876215849186152017-11-03T11:56:00.001-07:002017-11-03T11:56:29.153-07:00search and replace unicode characters for ASCII output in perlI don't know why you would EVER need this :P but just in case, this handles most of the open/close quote and double quote issues that arise when you need to output to ASCII
<pre class="brush:bash">
$string =~ s/[\x{00E2}|\x{00C3}|\x{00C2}]?[\x{0080}|\x{0082}|\x{0083}|\x{0000}]?[\x{0099}|\x{00BA}|\x{00B9}|\x{0098}]/'/ig;
$string =~ s/[\x{00E2}|\x{00C3}|\x{00C2}]?[\x{0080}|\x{0082}|\x{0083}|\x{0000}]?[\x{009C}|\x{009D}|\x{0093}|\x{0094}]/"/ig;
$string =~ s/\x{2018}/'/ig;
$string =~ s/\x{201A}/'/ig;
$string =~ s/\x{2019}/'/ig;
$string =~ s/\x{2039}/'/ig;
$string =~ s/\x{203A}/'/ig;
$string =~ s/\x{0092}/'/ig;
$string =~ s/\x{0093}/"/ig;
$string =~ s/\x{0094}/"/ig;
$string =~ s/\x{201C}/"/ig;
$string =~ s/\x{201D}/"/ig;
$string =~ s/\x{201E}/"/ig;
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com1tag:blogger.com,1999:blog-3045551838493191275.post-28996862483296140322017-08-05T08:42:00.002-07:002017-08-05T08:44:26.334-07:00Array: remove empty, remove whitespace out of each sliceFor dealing with arrays with IDs (that have been touched by humans somewhere along the way)
<pre class="brush: bash; light: true;">
@array = grep /\S/, @array; # remove empty
s/\s$// for @array; # remove whitespace out of each slot
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-78949131741745316122017-07-31T09:37:00.003-07:002017-08-09T09:42:13.652-07:00Ember.js stateful image loading componentNot sure how useful this is, but this uses javascript Image() and a promise to load an image. Allows you to set loading and error states.
<pre class="brush: javascript">
import Ember from 'ember';
const {
Component,
get,
set
} = Ember;
export default Component.extend({
isLoading: true,
isError: false,
/* JavaScript Image Object used to do the loading */
imageLoader: Ember.computed(function() {
return new Image();
},
getImage(url, _this) {
return new Ember.RSVP.Promise((resolve, reject) => {
let img = _this.get('imageLoader');
img.onload = function() {
resolve(url)
}
img.onerror = function() {
reject(url)
}
img.src = url
});
},
didInsertElement() {
this._super(...arguments);
let _this = this;
let src = get(_this, 'src');
_this.get('getImage')(src, _this).then(success => {
console.log("success", success);
set(_this, 'isLoading', false);
}).catch(error => {
console.log("fail", error);
set(_this, 'isLoading', false);
set(_this, 'isError', true);
})
},
willDestroyElement() {
this._super(...arguments);
let img = get(this, 'imageLoader');
if (img) {
img = img.onload = img.onerror = null;
this.set('imageLoader', null);
}
}
});
</pre>
Then in the template you could do something like:
<pre class="brush:html">
{{#if isLoading}}
<div class="loading">loading...</div>
{{else}}
{{#if isError}}
<img src="/path/to/missing_image.jpg" />
{{else}}
<img src="{{src}}" class="img-responsive" />
{{/if}}
{{/if}}
</pre>
If the component is called "product-render" you could use it in parent template like:
<pre class="brush:bash;light:true">
{{product-render src=myImage}}
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-47953101073953696592017-07-31T07:06:00.000-07:002017-07-31T07:06:04.171-07:00MySQL select records with timestamp in the previous day, no matter what time of day it is nowWHERE timestamp >= DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 24 HOUR) AND timestamp < DATE_ADD(DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 24 HOUR), INTERVAL 24 HOUR) Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-50909233756047757052017-07-02T14:45:00.001-07:002020-12-19T20:36:12.100-08:00Create Self-Signed Cert on OSX and tell the OS to trust itNot as easy as you might think. Let's say you wanted to create a self-signed cert for a local domain called "my.webtool"...
First create a file called v3.ext with these contents:
<pre class="brush:bash; light:true">
authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names
[alt_names]
DNS.1 = my.webtool
DNS.2 = localhost
DNS.3 = 127.0.0.1
</pre>
Then run these commands. This assumes you have openSSL installed:
<pre class="brush: bash; light: true">
openssl genrsa -des3 -passout pass:x -out my.webtool.pass.key 2048
openssl rsa -passin pass:x -in my.webtool.pass.key -out my.webtool.key
rm my.webtool.pass.key
openssl req -new -key my.webtool.key -out my.webtool.csr
openssl x509 -req -days 1000 -in my.webtool.csr -signkey my.webtool.key -out my.webtool.crt -extfile v3.ext
</pre>
Then install the .key and .crt files in whatever server you're running.
THEN you have to tell your system to trust the certificate by importing it into your keychain AND change the "trust" settings on it.
See <a href="http://www.accuweaver.com/2014/09/19/make-chrome-accept-a-self-signed-certificate-on-osx/">http://www.accuweaver.com/2014/09/19/make-chrome-accept-a-self-signed-certificate-on-osx/</a>
update! this appears to be a one-shot deal:
<pre class="brush: bash; light: true">
openssl req \
-newkey rsa:2048 \
-x509 \
-nodes \
-keyout yarr.mydev.com.key \
-new \
-out yarr.mydev.com.crt \
-subj /CN=yarr.mydev.com \
-reqexts SAN \
-extensions SAN \
-config <(cat /etc/ssl/openssl.cnf \
<(printf '[SAN]\nsubjectAltName=DNS:yarr.mydev.com,IP:192.168.56.101')) \
-sha256 \
-days 3650
</pre>
Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-38824440359668800032017-02-27T09:02:00.003-08:002017-02-27T09:02:35.069-08:00clean the apache cache nicely<pre class="brush:bash;light;true">
/usr/local/apache/bin/htcacheclean -vn -p/var/cache/apache -l500M
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com1tag:blogger.com,1999:blog-3045551838493191275.post-66742324645262801782017-02-27T09:02:00.001-08:002017-02-27T09:02:17.793-08:00Kinda rough regex for pattern matching and capturing apache log infothe IP is parsed into \2 \3 and \4
<pre class="brush:bash;light:true">
^(((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)) \- \- (\[.*?\]) "(.*?)" (\d+) (\d+) "(.*?)" "(.*?)"$
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-20491058534964891682017-02-27T09:01:00.002-08:002017-02-27T09:01:58.299-08:00Geocode a batch of addresses with perl and GoogleI am working with <a href="http://www.bjornblog.com/web/jquery-store-locator-plugin">this</a> store locator and needed a way to geocode 7k addresses, so I wrote this quick-n-dirty script. Google only lets you geocode 2500 addresses a day. The script will start where it left off, then quit when you hit the query limit. So it feasibly could be run on a cron until all your addresses are coded.
<pre class="brush:bash">
#!/usr/bin/perl
use strict;
use DBI;
use Google::GeoCoder::Smart;
my $geo = Google::GeoCoder::Smart->new();
my $host ="";
my $database ="";
my $user ="";
my $mysqlpassword ="";
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1});
my $ctr= 0;
my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";
my $sth = &db_query($sql);
while (my $ref = $sth->fetchrow_hashref) {
my ($num, $error, @results, $returntext) = $geo->geocode(
"address" => stripChars($ref->{'address'}),
"city" => stripChars($ref->{'city'}),
"state" => stripChars($ref->{'state'}),
"zip" => stripChars($ref->{'postal'})
);
my $lat;
my $lng;
eval {
$lat = $results[0]{geometry}{location}{lat};
};
eval {
$lng = $results[0]{geometry}{location}{lng};
};
print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
if ($error eq 'OVER_QUERY_LIMIT') {
last;
}
if (($lat) && ($lng)) {
$lat = $dbh->quote($lat);
$lng = $dbh->quote($lng);
my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
my $sth2 = &db_query($upd_q);
$sth2->finish();
}
$ctr++;
if (($ctr%10)==0) {
print "sleeping\n";
sleep 5;
}
}
$sth->finish();
undef ($sql);
undef ($sth);
$dbh->disconnect();
sub stripChars {
my($text) = @_;
$text =~ s/^\s*//; # strip out leading space if there is one
$text =~ s/\n/ /g; # strip carraige returns
$text =~ s/\t/ /g; # strip tabs
$text =~ s/\a/ /g; # strip carraige returns
$text =~ s/"/'/g; # strip quotes and replace with single quotes
$text =~ s/\s+/ /g; # strip repeating spaces and replace with one
$text =~ s/[^[:ascii:]]+//g;
return ($text);
} # end sub strip chars
sub db_query {
my ($query) = @_;
my $sth = $dbh->prepare("$query");
#print $query;
$sth->execute;
#print $dbh->err;
my $err = $dbh->err;
my $errstr = $dbh->errstr;
if ($err) { print "$err: $errstr on query $query"; }
return $sth;
} # end sub db_query
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-40497639128571501942016-08-13T08:20:00.002-07:002016-08-13T08:20:30.515-07:00grep mail.log for only sent emails<pre class="brush: bash; light:true">
tail -1000 /var/log/mail.log | grep -E -o "\bto=<[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}\b" | grep -v '[DOMAINS YOU WANT TO EXCLUDE]'
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-29615383007810793562016-08-13T08:19:00.001-07:002016-08-13T08:19:49.280-07:00regex to grab the last path of a url<pre class="brush:bash;light:true">
.*/([^/]+*)$
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-58708192728720852112016-07-30T08:16:00.003-07:002016-07-30T08:20:48.014-07:00Geocode thousands of addresses with perlI'm using <a href="http://www.bjornblog.com/web/jquery-store-locator-plugin/">this</a> jquery store locator and needed a way to geocode 7K addresses. Google only lets you code 2500 in a 24H hour period, so this will quit when the query limit is reached. When it runs again it will pick up where is left off. So it could theoretically be run on a cron until it's done.
<pre class="brush:perl">
#!/usr/bin/perl
use strict;
use DBI;
use Google::GeoCoder::Smart;
my $geo = Google::GeoCoder::Smart->new();
my $host ="";
my $database ="";
my $user ="";
my $mysqlpassword ="";
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1});
my $ctr= 0;
my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";
my $sth = &db_query($sql);
while (my $ref = $sth->fetchrow_hashref) {
my ($num, $error, @results, $returntext) = $geo->geocode(
"address" => stripChars($ref->{'address'}),
"city" => stripChars($ref->{'city'}),
"state" => stripChars($ref->{'state'}),
"zip" => stripChars($ref->{'postal'})
);
my $lat;
my $lng;
eval {
$lat = $results[0]{geometry}{location}{lat};
};
eval {
$lng = $results[0]{geometry}{location}{lng};
};
print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
if ($error eq 'OVER_QUERY_LIMIT') {
last;
}
if (($lat) && ($lng)) {
$lat = $dbh->quote($lat);
$lng = $dbh->quote($lng);
my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
my $sth2 = &db_query($upd_q);
$sth2->finish();
}
$ctr++;
if (($ctr%10)==0) {
print "sleeping\n";
sleep 5;
}
}
$sth->finish();
undef ($sql);
undef ($sth);
$dbh->disconnect();
sub stripChars {
my($text) = @_;
$text =~ s/^\s*//; # strip out leading space if there is one
$text =~ s/\n/ /g; # strip carraige returns
$text =~ s/\t/ /g; # strip tabs
$text =~ s/\a/ /g; # strip carraige returns
$text =~ s/"/'/g; # strip quotes and replace with single quotes
$text =~ s/\s+/ /g; # strip repeating spaces and replace with one
$text =~ s/[^[:ascii:]]+//g;
return ($text);
} # end sub strip chars
sub db_query {
my ($query) = @_;
my $sth = $dbh->prepare("$query");
#print $query;
$sth->execute;
#print $dbh->err;
my $err = $dbh->err;
my $errstr = $dbh->errstr;
if ($err) { print "$err: $errstr on query $query"; }
return $sth;
} # end sub db_query
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-20931059007175835242016-04-26T11:00:00.001-07:002016-04-26T11:00:17.476-07:00Add transparent PNG to a JPG using ImageMagick Convert<pre style="brush:bash;light:true">
convert shirt.jpg art.png -gravity center -composite -matte output.jpg
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com3tag:blogger.com,1999:blog-3045551838493191275.post-22650876898040273402016-03-22T20:18:00.001-07:002016-03-23T09:46:18.129-07:00recursively search and replace ambersands (or any character) in filenamesReplaces the ampersands with underscores in any file ending in .jpg (all subdirs)
<pre class="brush:bash;light:true">
find . -type f -iname "*.jpg" -exec rename 's/&/_/' {} \;
</pre>
Same thing, but on systems that don't have 'rename' (like OS X)
<pre class="brush:bash;light:true">
find . -name '*.jpg' -type f -exec bash -c 'mv "$1" "${1/\/\&/_/}"' -- {} \;
</pre>
Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-58924739262771091212016-03-11T23:23:00.002-08:002016-03-11T23:23:22.378-08:00wget a site for caching purposes (spider it)Sometimes you want to crawl a site so that it gets cached on its server. This will "spider" a site and not download content
<pre class="brush:bash;light:true">
wget -r -l6 --spider -D mysite.com http://mysite.com/
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com1tag:blogger.com,1999:blog-3045551838493191275.post-21210481209453042162015-09-23T11:27:00.000-07:002015-09-23T11:27:17.250-07:00MySQL query to compare two COUNT results to get a percentage<pre class="brush:bash;light:true">
SELECT
keyed.keyed_codes AS "Entered Codes",
totals.total_table AS "Total Codes",
ROUND(((keyed.keyed_codes/totals.total_table)*100),2) AS "Percent Keyed"
FROM
(SELECT count(*) AS keyed_source FROM `table` WHERE code RLIKE "^[1-9]" AND timestamp BETWEEN '20150801000000' AND '20150831000000') AS keyed,
(SELECT count(*) AS total_table FROM `table` WHERE timestamp BETWEEN '20150801000000' AND '20150831000000') AS totals
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-77201355245199612472015-01-17T16:27:00.004-08:002015-03-19T14:32:28.930-07:00Stop iTunes v12 from Expiring PodcastsIf you're a heavy podcast listener you've probably seen this iTunes message:<br />
<br />
"iTunes has stopped updating this podcast because you have not listened to any episodes recently. Would you like to resume updating this podcast?"<br />
<br />
iTunes stops downloading podcasts if you haven't listened to any of the last 5 downloaded episodes. I perhaps because bandwidth gets expensive? But it's annoying because sometimes you're traveling and not able to sync, or you occasionally listen to the podcast on Stitcher or some other podcatcher right on your phone. Doesn't mean you want them to stop downloading.<br />
<br />
This script <a href="http://dougscripts.com/itunes/scripts/ss.php?sp=updateexpiredpodcasts">http://dougscripts.com/itunes/scripts/ss.php?sp=updateexpiredpodcasts</a> used to work, but somewhere in iTunes version 11 they wised up to it and it stopped working. Now they are actually checking the downloaded tracks to see if any are played.<br />
<br />
So, I wrote a little script that gives 'em what they want. It plays the first unplayed episode in each subscribed podcast, and then back-tracks to the beginning of the podcast so that when you go to listen you don't know the difference. In order for a track to be identified as "played" it appears it needs to have been playing for at least 15 seconds. While the script is running it sets the volume to 0. But it will return the volume to what it was previously set at when it's done. So you can run this via launchd or cron once a day, in the middle of the night.<br />
<br />
Yes, it's kludgey. I can't even say for sure that it works, as I just wrote it. I borrowed some of Doug's code, so credits to him.<br />
<br />
<b>UPDATE</b>: This actually appears to work. I've been running it as a cron job late at night for a couple weeks and haven't had the "expired podcast" issue. Only weirdness I'm seeing is that the iTunes "messages" (little note that pops up to tell you when a new track is playing) sticks on the last track processed.<br />
<br />
<pre class="brush:bash;light:true">
--check if itunes is running. if not, activate it
set itunesOK to my itunes_is_running()
if itunesOK is false then
tell application "iTunes"
activate
end tell
end if
tell application "iTunes"
with timeout of 300 seconds
--see if there's a currently playing track so we can return to it when done
set playTrack to false
if player state is playing then
set aTrack to current track
stop
set playTrack to true
set myMessage to "Running the StopExpired script, will return to your track shortly"
set myReturn to my growlMessage(myMessage)
end if
--see what the volume is currently set at
set curVol to (get sound volume)
--turn the volume all the way down so what we're about to do is not audible
set sound volume to 0
set podcast_playlist to some playlist whose special kind is Podcasts
-- filter multiple names
set each_podcast to {}
try
set each_podcast to my ASCII_Sort(my get_discrete_list_of(get album of every track of podcast_playlist))
end try
--display the list (debugging only)
--set selectedVoice to {choose from list each_podcast}
-- loop thru each podcast
if each_podcast is not {} then
repeat with this_podcast in each_podcast
--create a list of unplayed episodes of within each podcast
set each_unplayed to {}
try
set each_unplayed to (every track of podcast_playlist whose album is this_podcast and unplayed is true)
end try
if each_unplayed is not {} then
set this_unplayed to (item 1 of each_unplayed)
--for debugging
set myMessage to this_podcast & ": " & name of this_unplayed as string
set myReturn to my growlMessage(myMessage)
--download the episode, if it's not already
try
set url_track to (get URL track of this_unplayed)
set myMessage to "DOWNLOADING " & name of this_unplayed & " URL: " & URL track as string
set myReturn to my growlMessage(myMessage)
download url_track
end try
try
play this_unplayed
delay 20
set player position to 0
stop this_unplayed
--back track this_unplayed
end try
--why is this necessary? I don't know. Sometimes the last track just keeps playing.
stop
end if
end repeat
end if
--update all the podcasts
updateAllPodcasts
--return the sound volume to previous level
set sound volume to curVol
set myMessage to "StopExpired script complete"
set myReturn to my growlMessage(myMessage)
--if there was a track already playing, return to it
if playTrack is true then
play aTrack
set playTrack to true
end if
end timeout
end tell
--if itunes was originally closed, then close it again
if itunesOK is false then
tell application "iTunes"
quit
end tell
end if
to get_discrete_list_of(list1)
script a
property list1ref : list1
end script
set list2 to {}
script b
property list2ref : list2
end script
repeat with i from 1 to length of list1
set this_item to item i of a's list1ref
considering case
if this_item is not "" and this_item is not in b's list2ref then set end of list2 to this_item
end considering
end repeat
return b's list2ref
end get_discrete_list_of
on ASCII_Sort(my_list)
set the index_list to {}
set the sorted_list to {}
repeat (the number of items in my_list) times
set the low_item to ""
repeat with i from 1 to (number of items in my_list)
if i is not in the index_list then
set this_item to item i of my_list as text
if the low_item is "" then
set the low_item to this_item
set the low_item_index to i
else if this_item comes before the low_item then
set the low_item to this_item
set the low_item_index to i
end if
end if
end repeat
set the end of sorted_list to the low_item
set the end of the index_list to the low_item_index
end repeat
return the sorted_list
end ASCII_Sort
--subroutine showing messages in growl (preferably)
--and if no growl, default dialog with timeout
to growlMessage(myMessage)
--show our output message
-- Check if Growl is running:
set isRunning to my growl_is_running()
--Only display growl notifications if Growl is running:
if isRunning = true then
tell application "GrowlHelperApp"
-- Make a list of all notification types:
set the allNotificationsList to ¬
{"Notification 1", "Notification 2"}
-- Make a list of the default enabled notifications:
set the enabledNotificationsList to ¬
{"Notification 1"}
-- Register the script with Growl
-- using either "icon of application"
-- or "icon of file":
register as application ¬
"StopExpired" all notifications allNotificationsList ¬
default notifications enabledNotificationsList ¬
icon of application "Script Editor"
-- Send a notification:
notify with name "Notification 1" title "StopExpired output" description myMessage application name "StopExpired"
end tell
else
display dialog myMessage giving up after 1
end if
end growlMessage
--sub checks if growl is running
on growl_is_running()
tell application "System Events" to return (exists process "GrowlHelperApp")
end growl_is_running
--subroutine checks if itunes is running
on itunes_is_running()
tell application "System Events" to return (exists process "iTunes")
end itunes_is_running
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com7tag:blogger.com,1999:blog-3045551838493191275.post-25181790622314444772014-09-19T12:28:00.000-07:002014-09-19T12:28:07.953-07:00Perl subroutine to fix when html entities get recursively applied<pre class="brush:perl">
sub enEntity {
my $string = shift;
if ($string =~ /&/) {
#print "string: $string\n";
}
if ($string =~ /&(amp;)+quot;/) { $string =~ s/&(amp;)+quot;/"/ig; }
if ($string =~ /&quot;/) { $string =~ s/&quot;/"/ig; }
if ($string =~ /"/) { $string =~ s/"/"/ig; }
if ($string =~ /&(amp;)+apos;/) { $string =~ s/&(amp;)+apos;/'/ig; }
if ($string =~ /&apos;/) { $string =~ s/&apos;/'/ig; }
if ($string =~ /'/) { $string =~ s/'/'/ig; }
if ($string =~ /&(amp;)+/) { $string =~ s/&(amp;)+/&/ig; }
if ($string =~ /&amp;/) { $string =~ s/&amp;/&/ig; }
if ($string =~ /&/) { $string =~ s/&/&/ig; }
#optional
#$string = unidecode($string);
return ($string);
} # end sub unescape html entities
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com4tag:blogger.com,1999:blog-3045551838493191275.post-70266605184613647042014-09-19T11:43:00.001-07:002014-09-19T11:43:07.040-07:00Convert raw (batch) mysql command-line output to CSV <pre class="brush:bash;light:true">
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-42881038318896390112013-05-16T09:58:00.000-07:002016-02-11T08:24:24.614-08:00This perl code recurses down thru a category hierarchy and indents.
You pass into it the category you want to drill into
<pre class="brush:perl;light:true">
# call the sub
&drillTree($code,1);
sub drillTree {
my ($root,$level) = @_;
my $tab = "";
for (my $i=1;$i<=$level;$i++) {
$tab .= "\t";
}
my $drill_q = "SELECT category_code,category_name FROM $categories WHERE category_parent = '$root'";
my $sth = &db_query($drill_q);
my $rc = $sth->rows;
if ($rc>0) {
$level++;
while (my $ref = $sth->fetchrow_arrayref) {
print "${tab}sub_code: $$ref[0] sub_name: $$ref[1]\n";
&drillTree($$ref[0],$level);
}
} else {
$level--;
}
}
</pre>
this one will print out the full path
<pre class="brush:perl;light:true">
# Launch the category drill-down sub
&drillTree($dbh,"100",0);
my @path = ();
$path[0] = "Products";
my @codes = ();
$codes[0] = "0";
sub drillTree {
my ($dbh,$root,$level) = @_;
my $tab = "";
for (my $i=1;$i<=$level;$i++) {
$tab .= "\t";
}
my $drill_q = "SELECT category_code,category_name FROM $categories WHERE category_parent = '$root'";
my $sth = &db_query($dbh,$drill_q);
my $rc = $sth->rows;
if ($rc>0) {
$level++;
while (my $ref = $sth->fetchrow_arrayref) {
$path[$level] = $$ref[1];
$codes[$level] = $$ref[0];
my $fullpath;
my $fullcodes;
for (my $i=1;$i<=$level;$i++) {
$fullpath .= $path[$i];
$fullcodes .= $codes[$i];
if ($i<$level) {
$fullpath .= "|";
$fullcodes .= ";";
}
}
$fullpath =~ s/®//g;
$fullpath =~ s/é/e/g;
print "level: $level ${tab}sub_code: $$ref[0] sub_name: $$ref[1] $fullpath $fullcodes\n";
&drillTree($dbh,$$ref[0],$level);
}
} else {
$level--;
}
} # end drillTree sub
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-64509357967894514882013-04-23T16:40:00.000-07:002013-04-24T10:05:40.399-07:00Generate random bytes<pre class="brush:bash;light:true">
find /var/ /usr /lib /srv -type f -print0 | xargs -0 cat > /dev/null
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com0tag:blogger.com,1999:blog-3045551838493191275.post-1777078459206615962013-03-15T11:14:00.001-07:002013-03-15T11:15:20.901-07:00Recursively delete backup files created by rsyncThese are mega annoying
<pre class="brush:bash;light:true">
/usr/bin/find ./ -name '*~' -exec rm '{}' \; -print -or -name ".*~" -exec rm -f {} \; -print
</pre>Roryhttp://www.blogger.com/profile/14054424218630129032noreply@blogger.com1