Corey Salzano – Page 2 – Web developer, music lover

Migrate Specific WordPress Posts or Attachments

Migrate Specific Posts or Attachments With a Third WordPress Install
Duplicate the Site and Delete Posts That Won’t Move
The Built-in Tools > Export Feature Preserves Post IDs

An easy way to move WordPress content from one site to another is with the built-in XML import and export scripts located under the Tools menu. This tool does not allow users to export only specific posts, attachments, or other content. If you want Posts and Media, you get them all. Here is how I worked around this limitation to migrate 700 specific attachments among thousands.

Use a Third WordPress Install

I duplicated the site to a third, throwaway instance of WordPress using exfil, deleted the attachments I was not interested in (so only the attachments I wanted to move remained), and exported all Media with Tools > Export.

The Hard Part is Deleting All The Posts That Won’t Move

Deleting a few thousand attachments from WordPress is not fast using PHP. Like me, you might imagine writing a small loop with a call to wp_delete_attachment() inside. This is painful, and I do not recommend. (My local Laravel Valet development environment skid to a stop, and after many valet restart to rescue my local server, I decided to move on.)

I probably could have used a database query like DELETE FROM wp_posts WHERE ID NOT IN ( 32281, 32279, ... ), but I opted for the assistance of wp-cli, the WordPress Command Line Interface. This giant command to delete attachments by providing all of their IDs works great and finishes in under one minute:

wp post delete 32281 32279 32263 32258 32248 32247 32241 32239 32238 32230 32222 32195 32152 32151 32150 32131 32130 32129 32102 32067 32066 32060 32059 32058 32057 32052 31977 31957 31954 31953 31919 31918 31889 31875 31868 31864 31863 31859 31843 31839 31838 31837 31836 31835 31817 31816 31815 31814 31813 31812 31810 31801 31799 31798 31777 31776 31775 31774 31764 31763 31751 31731 31730 31726 31725 31721 31720 31719 31714 31707 31706 31681 24802 31646 31631 31623 31612 31605 31603 31584 31578 31572 31571 31552 31544 31541 31531 31501 31498 31497 31480 31441 31439 31397 31395 31391 31390 31389 31388 31323 31322 31316 31289 31287 31249 31237 31236 31224 31222 31206 31183 31175 31172 31170 31168 31161 31160 31158 31156 31148 31120 31107 31098 31097 31096 31073 31037 31036 31035 31029 31033 31032 31031 31030 31026 31025 31024 31007 31003 30992 30991 30935 30930 30928 30925 30924 30918 30917 30913 30912 30911 30909 30907 30899 30880 30781 30756 30743 30742 30731 30674 30673 30643 30634 30631 30617 30596 30592 30591 30586 30580 30577 30572 30571 30552 30516 30515 30514 30496 30494 30493 30492 30490 30427 30393 30378 30351 30241 30218 30187 30186 30185 30184 30178 30177 30173 30171 30164 30096 30090 30084 30038 30037 30036 30035 30006 29979 29969 29968 29955 29954 29946 29945 29920 29913 29901 29898 29897 29896 29895 29894 29893 29892 29891 29890 29889 29888 29887 29886 29885 29884 29883 29881 29880 29878 29874 29799 29798 29797 29605 29604 29603 29602 29601 29600 29591 29493 29479 29446 29349 29279 29268 29263 29162 29163 29211 29207 29197 29161 29156 29145 29144 29143 29142 29141 29116 29115 29112 29110 29087 29082 29081 29079 29042 29002 29001 23161 28982 28971 28941 28940 28938 28937 28882 28836 28834 28833 28832 28831 28826 28825 28824 28823 28821 28813 28812 28805 28800 28779 28778 28777 28776 28775 28774 28773 28772 28771 28770 28767 28715 28712 28711 28709 28697 28696 28695 28694 28566 28559 28558 28557 28556 28553 28537 28536 28519 28518 28466 28441 28440 28439 28438 28422 28367 28366 28358 28357 28356 28290 28220 28219 28195 28194 28182 28181 28017 28168 28167 28162 28155 28152 28149 28146 28143 28129 28128 28059 28058 28057 28048 28046 28018 27965 27964 27963 27948 27947 27809 27783 27778 27773 27772 27771 27698 27697 27683 27681 27680 27659 27650 27597 27576 27556 27534 27528 27527 27526 27525 27523 27519 27518 27498 27437 27432 27428 27388 27375 27374 27373 27372 27371 27369 27316 27308 27302 27226 27203 27202 27201 27200 27199 27157 27152 27148 27133 27130 27128 27105 27103 27081 27066 27059 27045 27044 25537 24930 24929 25108 25088 25087 25086 25085 25084 25083 25054 24980 24979 24959 24924 24888 24810 24755 24744 24633 24604 24496 24495 24482 24398 24396 24376 24351 24339 24332 24312 24311 24284 24277 24275 24269 24237 24208 24145 24144 24137 24132 24128 24127 24126 24006 23997 23961 23957 23933 23922 23921 23919 23912 23910 23907 23906 23905 23904 23903 23902 23898 23842 23487 23486 23480 23445 23434 23433 23432 23400 23390 23325 23185 23158 23152 23150 23147 22989 22984 22983 22975 22974 22972 22970 22967 22966 22964 22962 22960 22958 22956 22930 22929 22928 22922 22916 22858 22851 22775 22774 22764 22760 22759 22756 22746 22745 22744 22740 22714 22710 22709 22706 22701 22699 22673 22652 22624 22621 22619 22611 22610 22608 22607 22594 22592 22005 21122 21098 21097 21096 21092 21090 21089 21088 20994 20993 20985 20948 20936 20920 20875 20872 20857 20786 20784 20773 20766 20759 20758 20757 20756 20753 20752 20751 20750 20749 20748 20747 20746 20735 20730 20728 20726 20725 20724 20723 20718 20698 20693 20681 20641 20640 20639 20631 20624 20611 20603 20593 20568 20567 20560 20559 20558 20557 20556 20555 20554 20550 20547 20546 20471 20462 20427 20394 20384 20342 20267 20253 20250 20235 20224 20211 20204 20201 20200 20195 20192 20183 20162 20158 20154 20149 20146 20140 20076 20075 20018 20002 19987 19976 19975 19974 19973 19972 19962 19957 19909 19906 19893 19866 19864 19790 19723 19722 19653 19652 19222 19210 19181 19176 19175 19174 19173 19070 19069 18985 18978 18973 18936 18792 18791 18784 18772 18771 18730 18728 18727 18726 18696 18666 18665 18655 18654 18653 18649 18633 18624 18621 18620 18619 18618 18612 18608 18604 18603 18601 18597 18595 18590 18580 18549 18540 18522 18521 18520 18519 18505 18491 18484 18478 18477 18465 18464 18457 18353 18307 18300 18252 18056 18036 17915 17914 17913 17897 17894 17891 17888 17885 17882 17874 17873 17870 17868 17865 17862 17857 17846 17845 17843 17842 17836 17835 17789 17765 17763 17689 17688 17681 17661 17643 17633 17602 17601 17501 17456 17444 17416 17410 17405 17382 17380 17371 17359 17356 17345 17344 17313 17312 17311 17310 17288 17283 17275 16999 16990 16989 16988 16987 16916 16914 16903 16874 16746 16743 16732 16723 16722 16721 16720 16674 16633 16625 16618 16590 16586 16571 16569 16424 16417 16416 16413 16385 16341 16320 16319 16318 16055 16018 16015 16011 15997 15995 15994 15992 15884 15688 15670 15629 15625 15619 15616 15612 15611 15609 15594 15590 15589 15585 15582 15580 15571 15564 15561 15544 15542 15540 15537 15499 15497 15490 15486 15485 15451 15441 15438 15395 15392 15387 15377 15369 15365 15363 15358 15351 15324 15323 15302 15282 15278 15277 15273 15272 15271 15270 15268 15267 15265 15264 15262 15259 15240 15221 15220 15219 15217 15216 15215 15214 15213 15212 15211 15210 15111 14928 14887 14878 14875 14871 14864 14862 14819 14774 14733 14722 14586 14528 14526 14525 14523 14522 14521 14520 14519 14518 14517 14516 14515 14514 14513 14512 14509 14501 14447 14434 14390 14388 14376 14315 14295 14294 14293 14270 14261 14260 14180 14173 14171 14038 14011 13931 13784 13751 13702 13691 13688 13686 13652 13605 13604 13603 13602 13569 13535 13515 13514 13513 13512 13505 13504 13503 13502 13501 13500 13498 13497 13496 13495 13494 13492 13491 13490 13485 13484 13483 13482 13479 13478 13477 13476 13475 13474 13462 13461 13382 13380 13378 13376 13325 13262 13258 13252 13251 13250 13246 13245 13242 13090 13059 13008 12930 12927 12921 12915 12913 12910 12837 12835 12825 12823 12821 12820 12819 12818 12817 12816 12815 12814 12812 12813 12810 12779 12771 12730 12722 12655 12652 12633 12632 12606 12605 12604 12596 12573 12571 12569 12568 12565 12564 12563 12562 12560 12550 12549 12548 11048 11046 11045 11044 11043 11042 11039 11038 11037 11036 11035 11034 11033 11032 11031 11030 11029 11028 11027 10997 10995 10990 10986 10982 10981 10980 10979 10970 10968 10967 10966 10912 10910 10904 10886 10878 10874 10871 10861 10851 10843 10841 10830 10814 10813 10808 10796 10795 10783 10751 10741 10737 10736 10735 10733 10625 10623 10622 10607 10603 10601 10598 10597 10586 10584 10583 10582 10581 10580 10579 10577 10576 10575 10574 10573 10572 10571 10570 10569 10568 10567 10566 10565 10564 10563 10562 10561 10560 10559 10558 10557 10556 10551 10550 10516 10515 10479 10416 10410 10407 10400 10399 10398 10347 10336 10335 10332 10325 10324 10319 10318 10317 10315 10310 10308 10306 10231 10229 10225 10206 10203 10202 10201 10200 10199 10194 10145 10135 10124 10116 10087 10086 10085 10061 10060 10041 10039 10038 10037 10036 10035 10033 10032 10031 10030 10028 10027 10025 10024 10018 10015 10013 10011 10005 10004 10003 10000 9997 9996 9994 9974 9835 9832 9831 9830 9829 9827 9822 9791 9789 9725 9723 9643 9637 9636 9634 9631 9630 9629 9628 9622 9579 9578 9577 9574 9564 9561 9524 9520 9519 9511 9473 9467 9456 9454 9450 9449 9447 9424 9423 9422 9421 9420 9405 9401 9400 9397 9332 9333 9331 9327 9325 9321 9317 9175 9174 9169 9168 9167 9152 9151 9150 9149 9148 9147 9146 9135 9134 9127 9114 9108 9104 9103 9096 9094 9093 9091 9086 9085 9084 9083 9082 9078 9076 9069 9068 9064 9054 9051 9049 9048 9047 9046 9045 9044 9029 8992 8990 8977 8975 8971 8966 8963 8962 8952 8951 8950 8949 8948 8947 8946 8945 8944 8943 8942 8941 8940 8939 8937 8935 8934 8932 8921 8917 8916 8913 8911 8907 8906 8899 8896 8895 8883 8864 8840 8821 8819 8818 8817 8811 8810 8809 8808 8807 8806 8805 8804 8803 8797 8795 8793 8759 8756 8750 8745 8739 8736 8733 8732 8731 8714 8708 8702 8697 8677 8676 8672 8671 8670 8666 8665 8664 8662 8653 8652 8645 8640 8639 8638 8636 8635 8634 8553 8483 8470 8460 8459 8451 8444 8438 8429 8428 8427 8426 8423 8422 8417 8416 8415 8414 8411 8410 8406 8402 8401 8399 8387 8367 8352 8306 8298 8297 8295 8290 8289 8260 8256 8255 8252 8244 8206 8195 8192 8191 8183 8182 8180 8179 8164 8133 8132 8130 8129 8126 8124 8121 8120 8117 8115 8114 8113 8112 8111 8106 8105 8104 8103 8102 7906 7903 7901 7879 7858 7818 7811 7810 7809 7808 7800 7793 7734 7689 7686 7685 7673 7672 7670 7666 7662 7649 7648 7647 7642 7639 7638 7636 7623 7621 7620 7601 7598 7593 7592 7589 7582 7580 7579 7578 7572 7566 7565 7564 7563 7561 7560 7557 7544 7542 7541 7534 7519 7518 7517 7515 7514 7506 7505 7503 7496 7492 7475 7471 7467 7460 7453 7435 7429 7427 7426 7421 7420 7414 7413 7410 7387 7385 7375 7366 7364 7359 7357 7355 7352 7349 7346 7342 7340 7335 7334 7331 7329 7326 7321 7319 7318 7313 7308 7307 7305 7301 7298 7291 7290 7289 7288 7278 7271 7268 7260 7253 7246 7232 7229 7228 7224 7222 7221 7220 7201 7198 7196 7195 7189 7188 7187 7185 7174 7172 7171 7168 7166 7162 7156 7152 7147 7140 7119 7116 7113 7112 7110 7109 7103 7098 7096 7094 7078 7077 7072 7071 7062 7052 7048 7038 7030 7029 7026 7025 7010 7004 7003 6998 6989 6986 6981 6972 6970 6969 6964 6953 6952 6946 6945 6943 6926 6921 6918 6915 6914 6910 6906 6898 6896 6894 6892 6893 6891 6890 6881 6879 6873 6865 6860 6848 6845 6844 6843 6842 6838 6835 6832 6827 6826 6825 6818 6817 6814 6812 6811 6810 6809 6802 6801 6800 6797 6792 6791 6790 6789 6788 6787 6785 6783 6780 6777 6776 6775 6772 6771 6761 6758 6756 6747 6746 6745 6742 6714 6703 6698 6697 6692 6690 6691 6688 6689 6686 6645 6638 6639 6637 6635 6632 6613 6607 6606 6603 6595 6592 6591 6590 6584 6568 6567 6566 6565 6543 6542 6541 6540 6539 6538 6537 6531 6530 6529 6528 6527 6526 6522 6519 6492 6488 6478 6477 6474 6473 6468 6456 6453 6452 6401 6397 6394 6393 6390 6391 6389 6383 6375 6374 6372 6373 6370 6371 6369 6367 6368 6366 6365 6363 6362 6361 6347 6314 6311 6309 6307 6306 6305 6304 6303 6302 6301 6300 6299 6296 6294 6274 6261 6251 6233 6231 6224 6222 6223 6221 6218 6216 6215 6214 6210 6206 6201 6200 6199 6198 6196 6195 6193 6191 6190 6188 6187 6185 6182 6174 6171 6166 6156 6139 6128 6043 6042 6041 6034 5963 5952 5928 5925 5922 5905 5904 5903 5902 5901 5892 5850 5847 5841 5835 5786 5784 5781 5777 5776 5773 5770 5766 5744 5741 5735 5719 5718 5713 5708 5707 5706 5705 5704 5701 5681 5680 5679 5678 5676 5675 5674 5673 5670 5669 5668 5667 5664 5663 5662 5632 5620 5618 5617 5613 5612 5608 5604 5523 5522 5520 5519 5518 5485 5473 5472 5471 5470 5469 5456 5451 5419 5416 5401 5400 5399 5398 5350 5348 5335 5334 5333 5325 5316 5315 5313 5311 5310 5302 5301 5298 5295 5293 5291 5227 5216 5213 5181 5153 5146 5145 5134 5123 5117 5054 5052 5051 5050 5048 5043 5042 5040 5039 5037 5033 5031 5026 5023 5021 5022 5020 5016 5010 5007 5002 5001 4995 4991 4987 4982 4967 4964 4955 4952 4951 4950 4949 4943 4940 4937 4927 4926 4919 4918 4900 4872 4866 4859 4858 4848 4847 4845 4820 4814 4812 4795 4789 4761 4742 4741 4740 4739 4738 4737 4732 4651 4610 4590 4575 4572 4571 4568 4565 4557 4514 4506 4486 4483 4479 4447 4442 4433 4428 4426 4420 4322 4321 4320 4319 4316 4315 4313 4312 4311 4307 4303 4298 4290 4288 4287 4286 4285 4275 4274 4273 4272 4270 4269 4267 4264 4263 4261 4260 4259 4258 4257 4255 4171 4106 4084 4081 4080 4079 4078 4077 4076 4075 4074 4073 4072 4071 4070 4063 4059 4054 4048 4047 4046 4045 4044 4043 4040 4039 4038 4037 4036 4035 4034 4033 4032 4031 4030 4029 4028 4027 4026 4025 4024 4023 4022 4021 4020 4019 4018 4017 4016 4015 4014 4013 4012 4011 4010 4009 4008 4007 4006 4005 4003 4002 4001 4000 3999 3998 3997 3996 3995 3994 3993 3992 3991 3990 3989 3964 3962 3960 3906 3905 3894 3892 3867 3863 3858 3854 3847 3845 3841 3836 3829 3813 3812 3664 3647 3636 3614 3610 3608 3582 3577 3573 3570 3554 3553 3551 3550 3549 3548 3547 3531 3378 3334 3321 3281 3272 3246 3244 3242 3203 3182 3162 3160 3159 3157 3081 3061 3057 3053 3052 3051 3046 3041 3010 3008 2996 2995 2994 2986 2979 2978 2977 2976 2975 2973 2971 2954 2950 2949 2945 2944 2943 2941 2932 2931 2922 2915 2906 2890 2888 2883 2872 2864 2862 2861 2860 2855 2841 2828 2826 2818 2781 2778 2777 2772 2771 2768 2766 2765 2764 2763 2762 2745 2736 2737 2735 2725 2719 2711 2697 2694 2552 2548 2547 2530 2470 2457 2444 2443 2442 2439 2436 2399 2354 2347 2311 2310 2268 2264 2261 2259 2242 2239 2238 2237 2231 2180 2177 2176 2175 2150 2144 2140 2126 2125 2124 2121 2119 2118 2117 2115 2112 2109 2107 2098 2097 2096 2095 2094 1826 1827 1825 1824 1823 1822 1821 1819 1817 1818 1816 1815 1814 1812 1813 1810 1809 1807 1808 1806 1805 1804 1803 1802 1801 1799 1505 1499 1498 1497 1496 1495 1493 1487 1486 1485 1484 1483 1482 1446 1108 1099 906 893 892 886 872 868 865 744 743 742 741 740 739 738 736 722 720 721 719 718 717 713 710 708 707 704 692 691 690 686 670 658 657 656 655 654 406 407 408 400 401 402 403 404 405 388 387 386 384 383 380 381 382 372 271 268 265 223 210 194 189 --force

The --force argument skips Trash and permanently deletes.

What Gave Birth to That Hideous Command

I wrote a bit of PHP to find the post IDs of attachments I wanted to migrate, and used the exclude argument in a get_posts() call to find the post IDs of all other attachments in the site. Then, I built the above wp-cli command in a string, and wrote it to a text file.

After deleting all the posts that should not be migrated, I was then ready to run Tools > Export for Media. The resulting XML file only contains attachments that remain after the massive deletion. The file also has URLs containing .test domains for my local environment, so I replaced those with the production domain. These URL changes mean the attachments will be pulled from the production site, not my local copy that’s only accessible from on my computer.

Provide the XML file to the WordPress importer at Tools > Import. Some servers will kill PHP processes that run longer than a specific amount of time. It’s OK to re-run the Import until it finishes. Because post IDs are preserved, no duplicate posts will result from running an XML import more than once.

August 13, 2020

Extract WordPress databases and files with exfil

I have written and released a bash script that extracts WordPress websites and updates their local copies in my computer. It’s called exfil, it’s free and open-source, and lives on Github at https://github.com/csalzano/exfil/.

To celebrate it’s release, I demoed exfil at the August 2020 burbswp.com virtual meetup, “What I’ve Learned Since Lockdowns Started”.

I plan to use this script for years because it saves time and is already better than the migration plugin I was using previously. Let me know if you try it or have a better script I could learn from.

August 12, 2020
Nginx Configuration Files for Laravel Valet

I switched to Laravel Valet for local WordPress development on my Macbook in December 2019. I love it. I presented at January’s WordPress Lancaster meetup about how and why I made the move.

This week, I’ve written some code that protects uploads to the WordPress Media Library. (Did you know that anyone can view uploads if they find the URLs? The Media Library uses only security by obscurity.) To code my solution, I’m using an .htaccess file to protect a subfolder in wp-content/uploads.

There is No .htaccess in Nginx

.htaccess files do not work in Laravel Valet because it uses the nginx web server instead of Apache. (And, Valet does not use a reverse proxy to allow Apache features to work while running the faster nginx.) nginx does not allow its configuration to be changed on-the-fly, and that’s one reason it’s faster than Apache.

To replicate the protected folder mechanism, I learned how to block access to a directory inside an nginx configuration file, but that introduced a new challenge: Where is the nginx configuration file? Most resources will identify usr/local/etc/nginx/nginx.conf as the configuration file, and while that’s correct, changing it and restarting Valet did not change the behavior of the folder.

Nginx Config File Location for Laravel Valet

Each site you create inside Valet gets its own configuration file at this location: ~/.config/valet/Nginx/sitename.test This is the file you should edit.

Restart Valet to Allow Configuration Changes to be Loaded

Nginx needs to be restarted in order for configuration changes to take effect, and I found it easy to use this command inside Terminal: valet restart

April 29, 2020
Customizing a Durgod Keyboard for macOS
I recently bought my first mechanical keyboard. The COVID-19 pandemic means I’m switching between my Macbook and a Windows machine throughout the week at the same desk, and while I was comfortable typing on the Macbook, this HP Spectre keyboard and I do not get along. The differences in shortcuts for copy and paste alone were causing me to make enough mistakes to know it was time to invest in a peripheral keyboard.

Alas, I’ve made the leap to a mechanical keyboard, a Durgod K320 in space grey with Cherry Silent Red switches (120$ shipped). Typically, I’m the kind of guy who buys whatever Wirecutter recommends, but their suggestions for mechanical keyboards are bad ones.

Features you Should Demand of a Mechanical Keyboard

the ability to remap any key
multiple profiles of key mappings

Wirecutter recommends the Varmilo VA87M and the Leopold FC750R. I couldn’t determine if these keyboards include software that makes it easy to remap keys while shopping. The Leopold’s keycaps don’t have legends for the multimedia controls of the function keys, so users will have to memorize the alternate roles of F5 through F12, print their own legend, or change the keycaps. I can’t support these accessibility shortcomings.

Durgod Zeus Engine Only Runs on Windows

The keyboard I bought has companion software to customize the key map and save multiple profiles. That software is called Durgod Zeus Engine, and it only runs on Windows. That might be a showstopper for some, but not I. The current version of Zeus Engine is 1.0.2.12, and it is much better easier to use compared to when I first bought this keyboard.

The changes I’ve made to make this keyboard macOS friendly are as follows:

Swap Left Ctrl and Left Windows keys (macOS’ command defaults to the Left Windows key instead of Alt, which is weird and why freedom to customize is important.)
Ctrl = 224
Windows = 17

Use the website https://keycode.info/ to reveal what your keys are actually doing.

How to Switch Between Profiles

Hold Fn and press F12. The M light will illuminate to confirm the alternate profile activation.
April 28, 2020
Show Attachments on The Events Calendar

Out of the box, The Events Calendar plugin for WordPress is amazing and a pleasure to work with as a developer. However, it does not show users Media Library uploads that are attached to event posts.

I made a plugin to bring this functionality to the Single Event view, it’s free and open-source, and you can download it here: https://github.com/csalzano/the-events-calendar-show-attachments

April 9, 2020
How to Delete Meta Fields with the WordPress REST API

As of this writing, it is not possible to delete a meta value using the WordPress REST API. Post meta updates must be communicated while inserting and updating post objects, and the only way to “remove” meta fields is to write blank values over their current values.

I wrote a free and open-source plugin to provide granular control over post and term meta fields. It lives on Github. The plugin is called Manipulate Meta with the WP API, and it extends the REST API to allow one request to retrieve, update, or delete a meta field independently from a post or term object.

The instructions are located in the repo’s README.md.

March 28, 2020
Azure External Tables: Please verify that the shards are accessible
Are you getting this error from Azure SQL Server? Login failed on ServerName.database.windows.net.DatabaseName Please verify that the shards are accessible and that the credential information affiliated with external data source ExternalDataSourceName is correct.

When I ran into this error while migrating a SQL Server instance to Azure and designing External Tables, it was because the user I specified as the IDENTITY during DATABASE SCOPED CREDENTIAL creation had a different password that the LOGIN of the same name in the Master database.

Say we create a user in the Master database like this:

CREATE LOGIN ExternalTableAdministrator WITH PASSWORD = '[email protected][I7L$~5j5UL';

Later, when creating a database-scoped credential (and specifying this user as the identity), make sure to use the same password:

CREATE DATABASE SCOPED CREDENTIAL [ExternalTableCred] WITH IDENTITY = 'ExternalTableAdministrator', SECRET = '[email protected][I7L$~5j5UL'; GO
November 27, 2019
How to fix “Computed columns are not supported with external tables for sharded data.”

This post was written the first day I encountered this error on Azure SQL Server because I could not find any web page that contained this error message.

If you’re getting the error, Computed columns are not supported with external tables for sharded data. while trying to create External Tables, remove the computed column definitions. Treat computed columns like normal columns and provide a data type. Read this: https://stackoverflow.com/a/43382300/338432

November 27, 2019

Solving a Confusing MigrationBlocker While Moving to Azure SQL Database

This week, I’m altering functions and stored procedures in a SQL Server 2008 database so that it can be migrated to Azure. The Data Migration Assistant does a great job of generating reports identifying MigrationBlockers, but one type of error was vague enough to confuse me for a few minutes. Here’s an example of that error:

    {
      "Recommendations": [
        {
          "ApplicableCompatibilityLevels": [
            "CompatLevel100",
            "CompatLevel110",
            "CompatLevel120",
            "CompatLevel130",
            "CompatLevel140"
          ],
          "ChangeCategory": "MigrationBlocker",
          "RuleId": "46010",
          "Title": "One or more objects contain statements that are not supported in Azure SQL Database [46010]",
          "Impact": "While assessing the schema on the source database, one or more syntax issues were found. Syntax issues on the source database indicate that some objects contain syntax that is unsupported in Azure SQL Database.",
          "ImpactDetail": "Function: [dbo].[GalleryXML] contains a statement that is not supported on Microsoft Azure SQL Database v12. The specific error is: Incorrect syntax near AS.",
          "Recommendation": "Note that some of these syntax issues may be reported in more detail as separate issues in this assessment.  Review the list of objects and issues reported, fix the syntax errors, and re-run assessment before migrating this database.",
          "MoreInfo": ""
        }
      ],
      "IsSelectedForMigration": true,
      "Eligibility": {
        "IsEligibleForMigration": true,
        "Explanation": "OK"
      },
      "ObjectName": "GalleryXML",
      "SchemaName": "dbo",
      "ObjectType": "UserDefinedFunction"
    },

The error text, “Incorrect syntax near AS,” was just not a strong clue as to what was wrong with this function. The purpose of the function is to create structured XML that can be de-serialized into an instance of an object in C#. I use PATH Mode to accomplish this, and it is one of my favorite maneuvers when operating in SQL Server/.Net. Here is an abbreviated version of the query to help you understand the required changes to continue using this method in an Azure database:

SELECT 
ID,

...

--Subquery for photos
(
	SELECT 
	ID,

	...

	FROM GalleryPhotosTbl 
	WHERE GalleryID = GalleryTbl.ID
	FOR XML PATH('GalleryPhoto'), TYPE
) AS Photos
FROM GalleryTbl
WHERE GalleryTbl.ID = @ID
FOR XML PATH('Gallery'), TYPE

The syntax that Azure doesn’t support is “AS Photos.” Here is the solution:

SELECT 
ID,

...

--Subquery for photos
(
	SELECT 
	ID,

	...

	FROM GalleryPhotosTbl 
	WHERE GalleryID = GalleryTbl.ID
	FOR XML PATH('GalleryPhoto'), root ('Photos'), TYPE
)
FROM GalleryTbl
WHERE GalleryTbl.ID = @ID
FOR XML PATH('Gallery'), TYPE

October 10, 2019

Homebrew Competition 2019

Beer in the streets raises $103k for local charities. 11×17. https://lititz.beer

August 21, 2019
Hiding Specific Terms When Creating or Editing Posts

The WordPress block editor uses the REST API to manipulate all information in the post object, so hiding terms from the category, tags, or a custom taxonomy meta box can be achieved by removing those terms from REST API responses.

One hook that makes this easy is the rest_{taxonomy}_collection_params filter.

Here is an example that shows how terms can be included in the editor only if they have a certain value for a term meta key. This isn’t the best code design because it uses a global variable to hold the taxonomy name, but that’s an artifact of my pulling this out of a larger class.

For my application, the terms I’m excluding are outdated and won’t be added to any new content on the site. This method is effective and seems low risk. If you are aware of any risks associated with excluding terms from REST API responses, please comment below.

August 12, 2019
WordPress Memes

This is the collection of all the WordPress memes I’ve created.

You Should Make Plugins

The West Coast Choppers meme is one of the greatest of all time. I made this for a presentation at the WordPress Lancaster meetup called How to Make a WordPress Plugin to help people get over the two biggest mental roadblocks to making their first plugin: “I don’t know PHP,” and “I don’t want to break my site.”

Is This a PHP File?

I made this for the same How to Make a WordPress Plugin presentation to help people understand that PHP start and end tags are what makes parts of a PHP file special, and there might be HTML or something else inside otherwise. This is slight meme abuse because the typical use of this image portrays our hero as naive.

But Her Editors

I made this to mock a sentence in Matt Mullenweg’s “New 5.0 Target Date” blog post that turned out to be false. I feel bad because this is trolling and a joke at his expense, but I did it.

July 19, 2019