AppleScript & interactive shell-script sessions

I thought I should share the brief research I finished yesterday.

Being AppleScript (as the main language to drive apple-events in the mac) the mother of the automation since MacOS 7 days, I was still missing the path to automate interactive shell scripting sessions, such as ssh, telnet, mail, etc., without the need of scripting the Terminal app, which is ugly.

Yesterday I found info about “expect” (google for it and click the first search result if you use english as your main language). It’s kind of a pseudo-terminal which you can invoke just like bash or sh, and it accepts a serie of special commands, being “expect” the killer one.

I’m working in a top-secret project with gnuchess (an app which acts as interface for UCI chess engines => well, the app which in fact was used to determine how UCI chess engines should communicate with UIs) and I needed grabbing input from it. But gnuchess must be launched in interactive mode, etc. And AS’s “do shell script” doesn’t work like that.

So, I wrote my first “expect” script, which works like this:

(“sample.sh” invoked from AS «do shell script “cd xxx; ./sample.sh ‘file.pgn'”»)

#!/usr/bin/expect
# this is how you read passed arguments (index-based)
set pgnName [lindex $argv 0]

cd /path/to/gnuchess-6.0.2/src/;
# start listening
spawn ./gnuchess

expect “White (1) : “
send “pgnload $pgnName\n”

expect “White (1) : “
send “book on\n”

expect “White (1) : “
send “hard\n”

expect “White (1) : “
send “go\n”

expect “) : “
send “quit\n”

close

Basically:

• spawn: launches and starts listening a process.

• expect: listens the process to output the given string (such as “White (1) : “).

• send: upon that string being sent by the process, enter the given string (such as “book on”).

• close: that is.

I invite you to fire the Terminal and type “man expect”. You will find more options and ways to write more compact code.

Green fields in front of me 😉

This one and the new features in Adobe AIR will keep me busy for the next two years (appart from that damn mobile devices and the html5 headache everyone loves).

Be good.

MySQL remove duplicates (large database + fast)

In the end, this was for me the fastest method to remove dups from the big db I’m working on:

1. Duplicate the structure of the database (ie, “db”) to a new database (ie, “db2”, setting the fields you don’t want to be dups to UNIQUE.
2. Copy db to db2 using INSERT IGNORE:

INSERT IGNORE INTO
db2
(`xxx`,`yyy`,`zzz`)
SELECT `xxx`,`yyy`,`zzz`
FROM `db`

This reduced the amount of time to minutes, while any other method could take many hours, appart from creating large temporary files which could fill the disk holding the temp dir and finally abort the process.

If you don’t need in the end to have those UNIQUE fields (the indexes takes many HD), you should re-dump db2 to a new db without that UNIQUE fields => again some more minutes, but still worth against 12 hours of CPU/RAM intensive working (in a highly mysql responsive profile) with other kind of filtering methods, such as DISTINCT or GROUP BY.

Recover MySQL from files

This is how I recovered my databases from a HD crash using Mac OS X. These included both MyISAM and InnoDB tables.

1. Get the files, typically from a backup or booting into your machine/server in recovery mode, usually located @ /var/lib/mysql
2. Read (if you own this file) mysql_upgrade_info, so you see which mysql version created the files.
3. Install the same version in your machine. If needed, uninstall whatever exists (if you don’t need it, of course):

brew remove mysql

4. Install the closest mysql version to that you found in mysql_upgrade_info (or whatever you guess is the version). See the available formulas and do it:

brew search mysql
brew install mysql@5.5

5. I installed myself version 5.5, which is the one I needed.
6. Now locate the place where the local files reside, typically @ /usr/local/var/mysql
7. Zip this folder as a backup measure and remove all of its contents.
8. Move inside all the files and folders you got in step 1.
9. Launch mysql:

/usr/local/opt/mysql@5.5/bin/mysql.server start

10. Connect and mysqldump whatever you need.

Bypassing Cloudflare

I use to automate many tasks in the web (such as triggering news and sorting them according to my interests) and some times I hit with Cloudflare’s DDoS protection, I mean client-side, I guess they will have some real DDoS protection server-side, as the client-side is very easy to break.
They use to change their algorithms from time to time, but the basics are always the same:

1. curl the protected page and you will get an invisible form which auto-sends itself after making some client-side calculations. Currently this form is called challenge-form and has three hidden fields called jschl_vc, pass and jschl_answer.
2. Compute the javascript you find in the curl-ed page and send the form (the results of the calculations will be populated to the jschl_answer field). You can emulate these calculations in your favorite language (Python, AppleScript, PHP, whatever) or have a JS engine to execute the scripts. I’ve used node for this, which is available as a command-line tool you can install and execute easilly.
3. If everything is OK, you will have now two cookies: __cfduid and cf_clearance. Using that cookies, you can now surf freely the website.

The details may change from time to time, as Cloudflare updates their methods, but it’s been very similar across the years. Just take a look in your regular browser/developer tools and find the magic under the hoods.

Mac: sqlite3 command-line one-liner load extension

You are using the sqlite3 command-line binary and you need an extension, for example the Levenshtein algorithm which will allow you to find in a database text similar to other texts, let’s say you would like to consider “Jules Verne” and “Julio Verne” the same author.

You learn that you can load the spellfix extension which will allow you to use a function called editdist3, which is this algorithm (also called edit-distance, meaning the amount of changes needed in a string to look like another string).

Mac OS X’s built-in sqlite3 binary doesn’t allow you to load extensions (maybe because of security issues), so after some time googling, you do the following:

1. Install an alternate copy of sqlite3 through brew (this is a must-have utility if you are some kind of programmer):

   $ brew install sqlite

   This will install a new copy of the sqlite3 binary with the ability to load extensions enabled by default. You will keep your system’s sqlite3 unalthered, so you are safe, as you will be the only one using the “unsafe” sqlite3, installed in
   /usr/local/opt/sqlite3/bin/sqlite3.

2. Compile yourself the spellfix extension. You will download sqlite’s source code. I got mine from here: https://www.sqlite.org/src/, clicking here or here =>
   
   You may get the tarball if you feel better.
   Unzip the file and look for spellfix.c inside sqlite/ext/misc. Right? Now let’s compile it following official sqlite’s instructions:

   $ cd /path/to/sqlite/ext/misc

   $ gcc -g -fPIC -dynamiclib spellfix.c -o spellfix.dylib

   If you are compiling a different extension, just substitute the words in red (spellfix.c and spellfix.dylib) with the right ones. If you receive this kind of message:

   -bash: gcc: command not found

   Then you dont’ have installed Apple’s command-line tools for Xcode. Go and get them @ Apple’s developer website.

3. Okay. This was the easy part. Now you are ready to load and run your brand-new copy of sqlite3 with loading-extensions enabled, and your brand-new compiled extension. It doesn’t exist a man page for sqlite3 and you don’t realize the how-to if you happen to run sqlite3 -help in a Terminal window… Using the trial and error method for minutes or hours, depending on your skills or how spirited you are, you will find the syntax. This is the one-liner, as I will be running this code from within another utility which doesn’t let me interact with sqlite3. And this is the reason of this entire post:

   cd /path/to/dir/with/spellfix/; /usr/local/opt/sqlite3/bin/sqlite3 /path/to/database.db 'SELECT load_extension("spellfix.dylib")' 'SELECT title FROM books WHERE editdist3(author, "Jules Verne") < 450'

I colored some things:

/path/to/dir/with/spellfix/ You will run your command from within the directory containing spellfix.dylib. Most probably you could specify the full path to spellfix.dylib later in the load_extension command, but this could lead to issues depending on your OS (ie, Win), so I prefer it this way.

Now follows the full path to the new sqlite3 binary you installed, as the system’s built-in sqlite3 won’t do the job.

/path/to/database.db This is the full path to the database. This means that we will be running the load_extension command from within SQL, not from the binary itself. I didn’t find a way. I tried using the -cmd switch mixed with the dot-command syntax (.load extension) but didn’t work.

load_extension(“spellfix.dylib”) Now we run two separate SQL statements, quoted and space-separated. In the first one we load the extension. In the second one, our statements (ie, searching for something).

editdist3(author, “Verne, Jules”) And here is how we are using the function which is available only after loading the spellfix extension.

BTW, here is the link to the spellfix documentation, just in case you are interested in its capabilities => https://www.sqlite.org/spellfix1.html

JDownloader Mac memory issue + restart [fixed]

It seems that there is a memory problem. A restart of JDownloader is required to fix the problem. If even 2 restarts do not fix it, please visit our support chat.

Do you see this message oftenly? Try this:

1. Go to the menu JDownloader > About JDownloader. You will see somewhere something like: Memory: Usage: 40 MB – Allocated: 44 MB – Max: 64 MB. This means that JDownloader is being launched with a max heap of 64 MB. Increasing this limit may fix your memory issues.
2. Quit JDownloader.
3. Right-click JDownloader’s icon in the Finder, then choose “Show Package Contents” in the context menu.
4. Open the folder called “Contents”.
5. Open the file called “vmoptions.txt”. If this file doesn’t exist, create it yourself using a plain text editor.
6. Add the following:

-Xmx256m

This will increase the max memory to 256 MB. Some people add up to 512 or 1024 MB, but first let’s see if 256 is good enough.

Launch JDownloader, go again to the About JDownloader window and see if the Max field in the Memory section was correctly increased.

eBay endorses fraud

Steps to reproduce the issue:

1. Crash your graphics card and try to find a replacement in eBay => https://www.ebay.es/itm/382559592650?ViewItem=&item=382559592650&ssPageName=ADME:X:COCE:ES:3160

2. Contact a seller which lists such item as New, which according to https://www.ebay.com/pages/help/sell/contextual/condition_1.html means “A brand-new, unused, unopened, undamaged item in its original packaging (where packaging is applicable). Packaging should be the same as what is found in a retail store, unless the item is handmade or was packaged by the manufacturer in non-retail packaging, such as an unprinted box or plastic bag. See the seller’s listing for full details.”:

3. When you go to the details, you can read in a tiny bullet inside a long list: “Used! 90% New”.

4. Contact the seller and ask for a clarification. What does it mean 90% new???

OK, not so difficult to understand: 90% new means 90% new, like renewed (???), so it’s more than new. It’s new renewed. However, that doesn’t sound like brand-new, unused, unopened, undamaged.

However, this isn’t the part related to the apparent endorsement of fraud from the point of view of eBay. Just read the same message focusing on certain keywords:

Also, I’ve reviewed the contact/help pages on eBay, and there is no apparent decent way to contact and REPORT FRAUD. My only chance was calling to an international? phone number, after waiting for (estimate) 34 minutes. No mail, no easy way to report a FRAUDULENT item.

The programmers or the CEO @ eBay, though, seems to suggest that the default behaviour is asking the buyer to buy whatever. Endorsement messages, a mechanism to include a special offers (2% discount)… Very smart.

Insolvable and slow recaptchas fixed (jDownloader Mac)

Launching jDownloader captcha’s solver in a Chrome incognito window (easilly adaptable to other browser?). Look in Advanced prefs for “BrowseCaptchaSolver: Browser Commandline” and enter the following =>

[ "/Applications/Google Chrome.app/Contents/MacOS/Google Chrome", "-incognito", "%s" ]

This will fix the issue of insolvable and slow recaptchas (maybe some kind of conflict between your google id and the process of solving captchas).

The same should work without jDownloader: just solve your captcha in a incognito window.

Hints to manipulate Apple’s Photos programmatically

Today we have many tools to handle data in the cloud, but there is no tool in the market able to do many things you can think of, like having a centralized photo DB in your computer coming from different devices (ie, merging your iPhone photos with those of your wife’s).

So, some hints are coming… (just hints, no final code, just read and adapt to your needs/circumstances). These are some useful locations:

/Users/<USER>/Pictures/Photos Library.photoslibrary/database/photos.db

And these for shared albums:

/Users/<USER>/Library/Containers/com.apple.cloudphotosd/Data/Library/Application Support/com.apple.cloudphotosd/services/com.apple.photo.icloud.sharedstreams/coremediastream-state/38332866+6004658345420041391/Model.sqlite

/Users/<USER>/Library/Containers/com.apple.cloudphotosd/Data/Library/Application Support/com.apple.cloudphotosd/services/com.apple.photo.icloud.sharedstreams/com.apple.photo.icloud.sharedstreams-38332866.cloudphotoservicelibrary/database/photos.db

In the surrounding folders there are the original pictures and resampled versions (thumbnails and so on).

The databases are SQLite and you can manipulate them easilly. There are libraries in almost any programming language and you have a built-in sqlite3 command-line tool you can invoke from any scripting language, such as AppleScript, like this:

do shell script ¬
    "sqlite3 FILE.DB 'SELECT * FROM table;'"

This would work to get out binary data:

do shell script ¬
    "sqlite3 FILE.DB 'SELECT quote(blobfield) FROM table;'" & ¬
    " | cut -d \"'\" -f2 | xxd -r -p > /tmp/out.gz'"

Photos stores many things in binary plists, so the previous code will come in handy to extract that binary fields, which you can later uncompress and parse as regular XML data.

If you can’t read the DB files, don’t panic. Most probably they are locked (opened by another process). You can check it like this:

lsof | grep -e 'FILE.DB'

Most probably there are some sync services running and thus locking the databases. You can quit temporarily this process, do your stuff, and it will be relaunched after that. Ie, if the “offending” process is photolibraryd, you can:

kill -3 `ps xww | grep photolibraryd | grep -v grep`; sqlite3 FILE.DB 'SELECT * FROM table;'

More tips tomorrow!

HTML5 today: 1 code

After many years out of Flash and inside HTML5, I can dictate: nightmare.

It’s a world of possibilities but, regarding developers, a totally *ucking nightmare.

100% of customers think they need support for desktop computers and mobiles devices. This means a minimum of Chrome, IE, Firefox and Safari, in different environments (desktop, tablet, mobile) and OS’s (Win, Mac, iOS, Android, including Win 7 thru 10, Mac 10 thru 12, iOS 8 thru 11…), and usually as well the customer’s frontend app, which uses to be a Cordova-based app (also available for iOS, Android and desktop Win and Mac).

Regarding audio and video, you stick with mp3/ogg and mp4/ogv. Regarding programming, you rely mostly in general-use libraries such as jQuery. For the rest of functions, you create your own libraries, aimed to support around 40-50 different browsers/versions.

In the end, you spend in development a similar amount of time than you spent previously in Flash (mostly because Flash is still available for impossible tasks out of Flash, such as RAD —standing for Rapid Animation Development, instead of Application—, now with HTML5 output thanks to CreateJS).

But some times you spend around 100 thru 200% of the time with debugging tasks. The last time I had a bug in the customer’s iOS app. The code worked fine in the other 40-50 browsers, but not here. I had to re-make at home the app and simulate the customer’s code in order to replicate and debug the issue. This converted a simple 2-hours job into a complex 4-days job.

My advice to all my customers all this time is the following: force your users to use your own app. This way you develop and mantain 1 code.

Otherwise, take care of your problems. No! Your supported browser’s problems! The transition from IE10 to IE11 caused an issue which affected to almost 1000+ interactives to one of my customers. This customer had the luck to have my company as the responsible of such interactives, as we managed to resolve the issue quickly. However, this issue caused a bunch of negative comments in the different app stores. This is because my customer lets the browsers decide which work and which doesn’t, how and when.

Flash movies worked the same all around the browsers and operative systems: 1 code, 1 behavior.

This is just the first round. I wanna tell you more about this!