Team Ai
Apppublic

Kalletlamadhav/sql-optimization-env

sourceHugging Faceupdated 6mo agoView on Hugging Face
0likes
environment.cpython-311.pyc72 linesDownload Raw Back to __pycache__
1�

2���i����ddlZddlZddlZddlZddlmZddlmZddlTddl	m3Z4ddlmZddl
mZdd	lmZdd5lmZed��ZGd�d
��ZdS)�N)�Path)�Optional�)�*)�RewardComposer)�HackDetector)�TaskRegistry)�CurriculumEngine)�
seed_databasez!data/fixtures/benchmark_seed42.dbc�d�eZdZd�Zddedefd�Zdedefd�Z	de6fd�Zdd	ed7efd�Z
dedefd
�ZdS)�SQLOptEnvironmentc�|�t��|_t��|_t	��|_t
��|_d|_d|_	tt�����j
j
dzdzdz|_	tdd���|j���r|j���t%t'|j��d��tdd���dS#t($r}td	|��d���Yd}~dSd}~wwxYw)8N�data�fixtureszbenchmark_seed42.dbu🚀 Rebuilding database...T��flush��u✅ Database ready with tablesu❌ Seeding failed: )r	�
task_registryr9�10curriculumr�reward_composerr�
hack_detector�_state�
_current_taskr�__file__�resolve�parent�_db_path�print�exists�unlinkr�str�	Exception)�self�es  �server\environment.py�__init__zSQLOptEnvironment.__init__s9��)�^�^���*�,�,���-�/�/���)�^�^���26���!����X���.�.�0�0�7�>��G�*�T�Wl�l��
�	:��/�t�<�<�<�<��}�#�#�%�%�
'��
�$�$�&�&�&��#�d�m�,�,�d�3�3�3��2�$�?�?�?�?�?�?���	:�	:�	:��,��,�,�D�9�9�9�9�9�9�9�9�9�9�����	:���s�A6D�11D;�D6�6D;N�task_id�returnc��|r0|j�|��}|�td|�d����n-|j���}t	j|��}t
d|j��d���||_|j	}tttj
����dd�|jd|j|g|jjd���|_|�|j��\}}}||_||_t-|jd|j|j|j||||�|j��||d	kr|jnd�12��S)NzTask 'z' not foundzSelected task: Tr�r)�13episode_id�current_task_id�current_step�	max_steps�curriculum_level�episode_rewards�total_episodes�14is_running�)r'�step_number�goal�15schema_ddl�
current_query�execution_plan�execution_time_ms�	row_count�db_statsr/�anti_pattern_hint)r�get_task_by_id�16ValueError�
get_all_tasks�random�choicerr'rr/�EnvironmentStater!�uuid�uuid4r.rr1r�17_run_query�18slow_query�original_time_ms�
original_plan�SQLOptObservationr5r6�
_get_db_stats�tables�hint)r#r'�task�pool�level�	orig_time�	orig_rows�	exec_plans        r%�resetzSQLOptEnvironment.reset.so���	'��%�4�4�W�=�=�D��|� �!>�'�!>�!>�!>�?�?�?���%�3�3�5�5�D��=��&�&�D�
�.���.�.�d�;�;�;�;�!����%��&��4�:�<�<�(�(��!��,� �L���n�"���?�9��	19�	20�	21���+/�/�/�$�/�*J�*J�'�	�9�i� )���&��� ��L�������/�$�'���'�'���4�4�"�+0�A�:�:�d�i�i�4�22�23�24�	25��actionc
���|jr|jjstd���|jxjdz
c_|j}|j�||��}	|�|j|j	��\}}}d}nB#t$r5}t|jd��dz}d}d}t|��}Yd}~nd}~wwxYw|j�|||j|||||���}	|jj|jjkp|	jdkp|du}26|27rOd|j_|j�|	j��|jj�|	j��t-|j|jj|j|j|j|r|n|j|||�|j��|jj|�	��}t=||	j|	|28d29|i���S)N�Call reset() firstrg�?r3�����)rMrUrP�opt_time�opt_rows�opt_plan�hack�query_errorgffffff�?F)r'r4r5r6r7r8r9r:r;r/�
error_messager\)�observation�reward�
reward_detail�done�info)rr2r>r-rr�detectrE�optimized_query�index_statementsr"�maxrGr!r�computer.�totalr�record_episoder0�appendrIr'r5r6rHrJrKr/�30StepResult)r#rUrMr\rYrZr[r]r$rarb�next_obss            r%�stepzSQLOptEnvironment.stepYs2���{�	3�$�+�"8�	3��1�2�2�2��� � �A�%� � ��!���!�(�(���6�6��		!�+/�?�?��&��(?�,�,�(�H�h���K�K���	!�	!�	!��4�0�#�6�6��:�H��H��H��a�&�&�K�K�K�K�K�K�����		!�����,�4�4����+�����#�5�	31�	32�
�
�K�$���(=�=�
$���4�'�
$��t�#�	
��	D�%*�D�K�"��O�*�*�=�+>�?�?�?��K�'�.�.�}�/B�C�C�C�$��L���0����� �0�'/�G�8�8�T�5G�&���'�'���4�4�!�[�9�%�33�34�35��� � �&�'���$��36�37�38�	39s�&B�40C�+B<�<Cc�<�|jstd���|jS)NrW)rr>)r#s r%�statezSQLOptEnvironment.state�s#���{�	3��1�2�2�2��{�rT�query�index_stmtsc	�t�tj|j��}tjd��}|�|��|���|�d��|�d�d��|r"|D]}	|�|���#Y�xYw|�d|�������}d�d�|D����}d|�	��v}d	|�	��vo|}	t|	rd41ndddd
|r|nd|	rdnd|���}42tj��}|�|�����}tj��|z43dz}
|���|
t|��|44fS)Nz:memory:zPRAGMA foreign_keys = ONc��ddzS)Nrr�rurTr%�<lambda>z.SQLOptEnvironment._run_query.<locals>.<lambda>�s45��a��c�rTi@BzEXPLAIN QUERY PLAN z | c3�4K�|]}t|��V��dS�N)r!)�.0�rs  r%�	<genexpr>z/SQLOptEnvironment._run_query.<locals>.<genexpr>�s(����9�9�!�s�1�v�v�9�9�9�9�9�9rTzUSING INDEX�SCANzFULL TABLE SCANz46INDEX SCANrgzConsider adding index)�	operation�
rows_examined�
rows_returned�
cost_estimate�using_index�missing_index_hint�explain_rawr)�sqlite3�connectr�backup�close�execute�set_progress_handler�fetchall�join�upper�
ExecutionPlan�time�perf_counter�len)r#rqrr�	disk_conn�mem_conn�stmt�	plan_rows�	plan_textr��is_full_scanrR�start�rows�elapseds              r%rEzSQLOptEnvironment._run_query�s����O�D�M�2�2�	��?�:�.�.��	����"�"�"����������3�4�4�4�	�%�%�k�k�7�;�;�;��	�#�
�
����$�$�T�*�*�*�*����D�����$�$�%B�5�%B�%B�C�C�L�L�N�N�	��J�J�9�9�y�9�9�9�9�9�	�#�y���'8�'8�8������!2�!2�2�F�;���!�+7�I�'�'�\����%0�:�	�	�d�:F�P�6�6�D�!�47�48�49�	��!�#�#������&�&�/�/�1�1���$�&�&��.�$�6����������D�	�	�9�,�,s�50B � B$rKc�4�tj|j��}i}|D]e}	|�d|�������d}d|i||<�;#t51$r}dt
|��i||<Yd}~�^d}~wwxYw|���|S)NzSELECT COUNT(*) FROM rr:�error)r�r�rr��fetchoner"r!r�)r#rK�conn�stats�table�countr$s       r%rJzSQLOptEnvironment._get_db_stats�s�����t�}�-�-�����	1�	1�E�
1����%D�U�%D�%D�E�E�N�N�P�P�QR�S�� +�U�3��e�����
1�
1�
1� '��Q���0��e�����������
1����	
�52�53�����s�7A�54B�#A<�<Brx)�__name__�55__module__�__qualname__r&r!rIrS�SQLOptActionrlrnrBrp�listrE�dictrJrurTr%r
r
s�������:�:�:�0)56�)57�S�)58�,=�)59�)60�)61�)62�V>63�<�>64�J�>65�>66�>67�>68�@�'�����--�--��--�$�--�--�--�--�^�D��T������rTr
)r@r�r�rC�pathlibr�typingr�modelsr`rrr�tasks.task_registryr	�curriculum.curriculum_enginer69�data.seed_databaser�DB_PATHr
rurTr%�<module>r�s���
�
�
�����������������������������"�"�"�"�"�"�'�'�'�'�'�'�,�,�,�,�,�,�9�9�9�9�9�9�,�,�,�,�,�,�70�$�2�713�723��F�F�F�F�F�F�F�F�F�FrT