AI prompt in SQL command generating the queries with Microsoft Foundry AI

sql-foundryai.php

<?php

/** AI prompt in SQL command generating the queries with Microsoft Foundry AI
* Beware that this sends your whole database structure (not data) to Foundry AI.
* @link https://learn.microsoft.com/en-us/azure/foundry/concepts/foundry-models-overview
* @link https://www.adminer.org/plugins/#use
* @author Ted Martin, https://datafluent.ca
* @license https://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
* @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License, version 2 (one or other)
*/
class AdminerSqlFoundryAi extends Adminer\Plugin {
	private $apiKey;
	private $model;
	private $endpoint;
	private $schemas;
	/**
	* @param string $apiKey The default key is shared with all users and may run out of quota; get your own API key at: https://aistudio.google.com/apikey
	* @param string $model model from Microsoft Foundry AI	
	* @param string $endpoint endpoint from Microsoft Foundry AI
	* @param array $schemas array of schema DDL to include in the prompt. defaults to public
	*/
	function __construct($apiKey, $model , $endpoint , $schemas = ['public']) {
		$this->apiKey = $apiKey;
		$this->model = $model;
		$this->endpoint = $endpoint;
		$this->schemas = $schemas;
	}

	function headers() {
		if (isset($_POST["foundry"]) && !isset($_POST["query"])) {
			$prompt = "I have a " . Adminer\get_driver(Adminer\DRIVER) . " database with this structure:\n\n";
			
			// Determine schemas to use for prompt: allow overriding via POST, else use constructor defaults
			$schemasToUse = [];
			if (isset($_POST['schemas']) && trim($_POST['schemas']) !== '') {
				$parts = explode(',', $_POST['schemas']);
				foreach ($parts as $s) {
					$t = trim($s);
					if ($t !== '') {
						$schemasToUse[] = $t;
					}
				}
			}
			if (empty($schemasToUse)) {
				$schemasToUse = $this->schemas;
			}
			foreach ($schemasToUse as $schema){
				$prompt .= "Schema: ".$schema."\n\n";
				Adminer\set_schema($schema);
				foreach (Adminer\tables_list() as $table => $type) {
					$prompt .= Adminer\create_sql($table, false, "CREATE") . ";\n\n";
				}
			}
			$prompt .= "Prefer returning relevant columns including primary key.\n\n";
			$prompt .= "Include schema name when referencing table names (without quotes).\n\n";
			$prompt .= "Assign tables a friendly alias (e.g. first letter of table name).\n\n";

			// Build messages payload following Azure OpenAI chat format to align with curl example
			$prompt1 = $prompt;
			$prompt2 = "Give me this SQL query and nothing else:\n\n" . $_POST['foundry'] . "\n\n";
			// Determine model to use from POST input; fallback to constructor value or default
			$modelFromPost = isset($_POST['model']) ? $_POST['model'] : '';
			if (empty($modelFromPost)) {
				$modelFromPost = $this->model;
			}

			$messages = [
				["role" => "user", "content" => $prompt1],
				["role" => "user", "content" => $prompt2]
			];
			$payload = json_encode([
				"messages" => $messages,
				"max_completion_tokens" => 16384,
				"reasoning_effort" => "low",
				"model" => $modelFromPost
			]);

			$context = stream_context_create(array("http" => array(
				"method" => "POST",
				"header" => array("Content-Type: application/json", "Authorization: Bearer " . ($this->apiKey ?? "")),
				"content" => $payload,
				"ignore_errors" => true,
			)));
			$response = json_decode(file_get_contents($this->endpoint, false, $context));
			if (isset($response->error)) {
				echo "-- " . $response->error->message;
			} else {
				$text = '';
				if (isset($response->candidates) && is_array($response->candidates) && isset($response->candidates[0]->content->parts[0]->text)) {
					$text = $response->candidates[0]->content->parts[0]->text;
				} elseif (isset($response->choices) && is_array($response->choices)) {
					if (isset($response->choices[0]->text)) {
						$text = $response->choices[0]->text;
					} elseif (isset($response->choices[0]->message->content)) {
						$text = $response->choices[0]->message->content;
					}
				}
				$text = preg_replace('~(\n|^)```sql\n(.+)\n```(\n|$)~sU', "*/\n\n\\2\n\n/*", "/*\n$text*/\n");
				echo preg_replace('~/\*\s*\*/\n*~', '', $text);
			}
			exit;
		}
	}

	function sqlPrintAfter() {
		echo "<div style='background: rgb(100 100 100 / 20%);padding: 5px;border-radius: 5px;margin-bottom: 5px;width: 96%;'>\n";	
		echo "<div><span style='vertical-align:top;width:75px;display:inline-block;'>Prompt:</span><textarea name='foundry' rows='5' cols='50' placeholder='" . $this->lang('Ask Foundry AI') . "'>" . Adminer\h($_POST["foundry"]) . "</textarea></div>\n";
		// Add input for delimited list of schemas (from constructor) to customize included schemas
		// Prefer the posted schemas if provided; otherwise, use the object's default schemas
		$schemasValue = (isset($_POST['schemas']) && trim($_POST['schemas']) !== '') ? $_POST['schemas'] : (isset($this->schemas) && is_array($this->schemas) ? implode(',', $this->schemas) : (string)$this->schemas);
		$schemasValueEscaped = htmlspecialchars($schemasValue, ENT_QUOTES, 'UTF-8');
		echo "<div><span style='width:75px;display:inline-block;'>Schemas:</span><input type=\"text\" id=\"schemasInput\" name=\"schemas\" value=\"$schemasValueEscaped\" size=\"40\" /> </div>\n";
		// Added UI control for overriding the AI model
		// Use POST['model'] if provided; else fall back to constructor value
		$modelValueRaw = $_POST['model'] ?? $this->model;
		$modelValue = htmlspecialchars($modelValueRaw, ENT_QUOTES, 'UTF-8');
		echo "<div><span style='width:75px;display:inline-block;'>Model:</span><input type=\"text\" id=\"modelInput\" name=\"model\" value=\"$modelValue\" size=\"20\" placeholder=\"$modelValue\" />";
		echo "<input type='button' id='foundryButton' value='Generate SQL'></div>\n";
		echo "</div>\n";
		?>
		<script <?php echo Adminer\nonce(); ?>>
const foundryText = qsl('textarea');
const foundryButton = document.getElementById('foundryButton');

function setSqlareaValue(value) {
	const sqlarea = qs('textarea.sqlarea');
	sqlarea.value = value;
	sqlarea.onchange && sqlarea.onchange();
}

foundryButton.onclick = () => {
	setSqlareaValue('-- <?php echo $this->lang('Just a sec...'); ?>');
		ajax(
			'',
			req => setSqlareaValue(req.responseText),
			'foundry=' + encodeURIComponent(foundryText.value) + '&model=' + encodeURIComponent(document.getElementById('modelInput').value)
		);
};

foundryText.onfocus = event => {
	alterClass(findDefaultSubmit(foundryText), 'default');
	alterClass(foundryButton, 'default', true);
	event.stopImmediatePropagation();
};

foundryText.onblur = () => {
	alterClass(foundryButton, 'default');
};

foundryText.onkeydown = event => {
	if (isCtrl(event) && (event.keyCode == 13 || event.keyCode == 10)) {
		foundryButton.onclick();
		event.stopPropagation();
	}
};
</script>
<?php
	}

	protected $translations = array(
		'cs' => array(
			'' => 'Generování SQL příkazů pomocí umělé AI Foundry',
			'Ask Foundry AI' => 'Zeptat se Foundry AI',
			'Just a sec...' => 'Chviličku...',
		),
		'pl' => array(
			'Ask Foundry AI' => 'Zapytaj Foundry AI',
			'Just a sec...' => 'Chwileczkę...',
		),
		'de' => array(
			'' => 'KI-Eingabeaufforderung im SQL-Befehl zur Erstellung der Abfragen mit Foundry AI',
			'Ask Foundry AI' => 'Foundry fragen',
			'Just a sec...' => 'Einen Moment...',
		),
		'ja' => array(
			'' => 'Foundry AI を用いて SQL 文を生成',
			'Ask Foundry AI' => 'Foundry に聞く',
			'Just a sec...' => 'しばらくお待ち下さい...',
		),
		'fr' => array(
			'' => 'Génération de requêtes SQL avec Foundry AI',
			'Ask Foundry AI' => 'Demander Foundry AI',
			'Just a sec...' => 'Un instant...',
		),
		'es' => array(
			'' => 'Generando consultas SQL con Foundry AI',
			'Ask Foundry AI' => 'Preguntar a Foundry AI',
			'Just a sec...' => 'Un momento...',
		),
	);
}

In addition to your prompt, allows textbox to choose custom model name (hosted on Microsoft Foundry AI), and specify which schemas DDL to includes (comma separated).
Screenshot (with dark mode)
.
In your adminer-plugins.php, setup:

<?php // adminer-plugins.php
return array(
    new AdminerSqlFoundryAi("YourApiKey","gpt-5-nano","https://resourcename.services.ai.azure.com/openai/v1/chat/completions",['schema1','schema2','schema3']),
    new AdminerDarkSwitcher()
);