Calling Developers!
We are reenergizing our code contribution process! Learn More

Configure propel to create an index for every foreign-key automaticaly?

Options
U018XELUZS9
U018XELUZS9 Posts: 167 🧑🏻‍🚀 - Cadet
edited June 2023 in Propel ORM

Do you know if it's possible to configure propel to create an index for every foreign-key automatically when using Postgres?

Tagged:

Best Answer

  • U018XELUZS9
    U018XELUZS9 Posts: 167 🧑🏻‍🚀 - Cadet
    Answer ✓
    Options

    We found the IndexGenerator Module, which is exactly what we need. We wrapped that in a bash script and added that to our CI pipeline:

    #!/bin/bash

export APPLICATION_ENV=development

SCHEMA_DIR="src/Pyz/Zed/IndexGenerator/Persistence/Propel/Schema"
TMP_DIR="/tmp/check-schema-changes/$(date -u +"%Y-%m-%d-%H-%M-%S")/"

mkdir -p "$TMP_DIR"
cp -a "$SCHEMA_DIR/." "$TMP_DIR"
rm -rf src/Orm/Propel/MDE/Schema/*.xml
rm -rf $SCHEMA_DIR/*.xml
vendor/bin/console propel:schema:copy -q
vendor/bin/console propel:postgres-indexes:generate -q

DIFF_OUTPUT=$(diff "$SCHEMA_DIR" "$TMP_DIR")
rm -rf "$TMP_DIR"

if [ "$DIFF_OUTPUT" != "" ]; then
  echo "found missing indices, please generate them with: 'console propel:postgres-indexes:generate'"
  echo "you can find the generated files in '$SCHEMA_DIR'"
  echo "$DIFF_OUTPUT"
  exit 1
fi

echo "there are no missing indices, good job"

Answers

  • U018XELUZS9
    U018XELUZS9 Posts: 167 🧑🏻‍🚀 - Cadet
    Options

    Looks like this works already for MySQL: http://propelorm.org/blog/2012/03/05/don-t-do-this-at-home-4-add-indices-on-foreign-key-columns.html

  • U018XELUZS9
    U018XELUZS9 Posts: 167 🧑🏻‍🚀 - Cadet
    Options

    @UQK3ZPJEN do you know if this is supported by Propel 2?

  • U018XELUZS9
    U018XELUZS9 Posts: 167 🧑🏻‍🚀 - Cadet
    Answer ✓
    Options

    We found the IndexGenerator Module, which is exactly what we need. We wrapped that in a bash script and added that to our CI pipeline:

    #!/bin/bash

export APPLICATION_ENV=development

SCHEMA_DIR="src/Pyz/Zed/IndexGenerator/Persistence/Propel/Schema"
TMP_DIR="/tmp/check-schema-changes/$(date -u +"%Y-%m-%d-%H-%M-%S")/"

mkdir -p "$TMP_DIR"
cp -a "$SCHEMA_DIR/." "$TMP_DIR"
rm -rf src/Orm/Propel/MDE/Schema/*.xml
rm -rf $SCHEMA_DIR/*.xml
vendor/bin/console propel:schema:copy -q
vendor/bin/console propel:postgres-indexes:generate -q

DIFF_OUTPUT=$(diff "$SCHEMA_DIR" "$TMP_DIR")
rm -rf "$TMP_DIR"

if [ "$DIFF_OUTPUT" != "" ]; then
  echo "found missing indices, please generate them with: 'console propel:postgres-indexes:generate'"
  echo "you can find the generated files in '$SCHEMA_DIR'"
  echo "$DIFF_OUTPUT"
  exit 1
fi

echo "there are no missing indices, good job"

Categories