Last active
December 7, 2024 12:59
-
-
Save yammerjp/68dfa8ca6b2fc003618246554b2033d7 to your computer and use it in GitHub Desktop.
generate queries such as `ADD INDEX ...` from TypeScript sources
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| TEMPORARY_DIR="/tmp/gen-sql-index" | |
| set -e | |
| function message() | |
| { | |
| echo "$1" 1>&2 | |
| } | |
| if ! which curl > /dev/null; then | |
| message "need curl command" | |
| exit 1 | |
| fi | |
| if ! which npx > /dev/null; then | |
| message "need npx command" | |
| exit 1 | |
| fi | |
| if ! which tac > /dev/null; then | |
| message "need tac command" | |
| exit 1 | |
| fi | |
| if ! which gawk > /dev/null; then | |
| message "need gawk command" | |
| exit 1 | |
| fi | |
| if [ "$TARGET_DIR" == "" ]; then | |
| message "Usage: TARGET_DIR=./ $0" | |
| exit 1 | |
| fi | |
| message "reset $TEMPORARY_DIR" | |
| rm -rf "$TEMPORARY_DIR" | |
| mkdir -p "$TEMPORARY_DIR" | |
| # Download a script to extract valid strings as SQL in AST | |
| curl -sL https://cdn.jsdelivr.net/npm/@yamachu/sql-index-helper/dist/transforms/index.js > "$TEMPORARY_DIR/jscodeshift-sql-collector.js" | |
| # Extract valid strings as SQL in AST | |
| message "collect sql in $TARGET_DIR ..." | |
| npx --yes jscodeshift --dry -s -t "$TEMPORARY_DIR/jscodeshift-sql-collector.js" --extensions ts "$TARGET_DIR" > "$TEMPORARY_DIR/collected_sql.log" | |
| # Compose an ADD INDEX statement based on SQL | |
| message "build index queries to $TEMPORARY_DIR/index-queries.sql" | |
| npx @yamachu/sql-index-helper -f "$TEMPORARY_DIR/collected_sql.log" > "$TEMPORARY_DIR/index-queries.sql" | |
| # Extract only the longest composite index with matching column order to avoid duplicate indices | |
| message "filter index queries to $TEMPORARY_DIR/index-queries.unique.sql" | |
| cat "$TEMPORARY_DIR/index-queries.sql" | grep -v "^-- " | grep -v '^$'| sort | uniq | tac | gawk ' | |
| { | |
| query_string = $0; | |
| gsub("ALTER TABLE ", ""); | |
| gsub(" ADD INDEX [a-zA-Z0-9_]+ \\(", "."); | |
| gsub(", ", ","); | |
| sub("\\);", ""); | |
| query_target = sprintf(" %s", $0) | |
| queries[query_target] = query_string | |
| } | |
| END { | |
| for (i in queries) | |
| for (j in queries) | |
| if (i!=j && index(i, j) == 1) | |
| delete queries[j]; | |
| for(k in queries) | |
| print queries[k] | |
| } | |
| ' | sort | tac > "$TEMPORARY_DIR/index-queries.unique.sql" | |
| # Build queries such as DROP INDEX | |
| cat "$TEMPORARY_DIR/index-queries.unique.sql" | gawk '{gsub(" ADD INDEX ", " DROP INDEX "); gsub(" \\(.+\\);", ";"); print }' > "$TEMPORARY_DIR/index-queries.unique.drop.sql" | |
| # Show results | |
| message "result(add): $TEMPORARY_DIR/index-queries.unique.sql" | |
| message "result(drop): $TEMPORARY_DIR/index-queries.unique.drop.sql" | |
| message "" | |
| message "====result(add)=====" | |
| cat "$TEMPORARY_DIR/index-queries.unique.sql" |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Usage
TARGET_DIR=./ bash <(curl https://gist.githubusercontent.com/yammerjp/68dfa8ca6b2fc003618246554b2033d7/raw/05929a8a598cdc321181ba838fa3f02a8875de8c/gen-sql-index.sh -o - )ref: https://github.com/yamachu/sql-index-helper
ref: https://techblog.cartaholdings.co.jp/entry/2023/12/06/125911