هناك نقاش حاد بين المبرمجين حول مخاطر وفوائد الإجراءات المخزنة في قواعد البيانات. اليوم سنبتعد عنهم ونفعل مرة أخرى ما لا يصدق في ظروف مستحيلة.
يحاول المطورون اليوم تجنب بناء منطق الأعمال في قواعد البيانات كلما أمكن ذلك. ومع ذلك ، هناك متحمسون يتحدون أنفسهم ويخلقون ، على سبيل المثال ، أداة تطابق التبادل ، وفي بعض الأحيان تقوم شركات بأكملها بنقل جانب الخادم إلى إجراءات مخزنة في قاعدة البيانات. يجادل مؤلفو هذه المشاريع أنه يمكنك فعل أي شيء في قواعد البيانات إذا كنت تريد ذلك.
هنا أذكر قسرا "معركة البحر" على BGP . هل من الممكن جعل هذه اللعبة في SQL؟ للإجابة على هذا السؤال ، سنستخدم خدمات PostgreSQL 12 بالإضافة إلى PLpgSQL. بالنسبة لأولئك الذين لا يطيقون الانتظار للنظر "تحت غطاء محرك السيارة" ، رابط إلى المستودع .
تتطلب لعبة المعركة البحرية مساهمة مستمرة من المستخدم طوال اللعبة. أسهل طريقة للتفاعل مع مستخدم قاعدة البيانات هي عميل سطر الأوامر.
إدخال بيانات
يعد الحصول على البيانات من المستخدم أصعب مهمة في هذا المشروع. أسهل طريقة من وجهة نظر التطوير هي مطالبة المستخدم بكتابة استعلامات SQL صحيحة لإدخال المعلومات الضرورية في جدول مُعد خصيصًا. هذه الطريقة بطيئة نسبيًا وتتطلب من المستخدم تكرار الطلب مرارًا وتكرارًا. أود أن أكون قادرًا على استرداد البيانات دون كتابة استعلام SQL.
تقترح PostgreSQL استخدام COPY… من STDIN لحفظ البيانات من الإدخال القياسي إلى جدول. لكن هذا الحل له عيبان.
أولاً ، لا يمكن تقييد مشغل COPY بمقدار المعلومات التي تم تحميلها. ينتهي بيان COPY فقط عندما يتلقى علامة نهاية الملف. وبالتالي ، سيتعين على المستخدم أيضًا الدخول إلى EOF للإشارة إلى اكتمال إدخال المعلومات.
ثانيًا ، لا توجد ملفات stdin و stdout في الإجراءات والوظائف المخزنة. تتوفر تدفقات الإدخال والإخراج القياسية عند تنفيذ استعلامات SQL العادية من خلال العميل ، ولكن الحلقات غير متوفرة هناك. وبالتالي ، لا يمكنك تشغيل اللعبة بأمر SQL واحد. كان من الممكن أن تكون هذه نهاية القصة ، لكن تم العثور على حل ماكر.
PostgreSQL لديها القدرة على تسجيل الدخولجميع الطلبات ، بما في ذلك الطلبات غير الصحيحة. علاوة على ذلك ، يمكن أن يكون التسجيل بتنسيق CSV ، ويمكن لمشغل COPY العمل بهذا التنسيق. لنقم بتهيئة التسجيل في ملف التكوين postgresql.conf:
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql.log'
log_min_error_statement = error
log_statement = 'all'سيقوم ملف postgresql.csv الآن بتسجيل جميع استعلامات SQL التي يتم تنفيذها في PostgreSQL. توضح الوثائق ، في قسم استخدام إخراج سجل تنسيق CSV ، طريقة لتحميل سجلات csv مع تمكين التدوير. نحن مهتمون بتحميل السجلات بفاصل زمني من ثانية واحدة.
نظرًا لأنه من غير العملي تدوير السجلات كل ثانية ، سنقوم بتحميل ملف السجل مرارًا وتكرارًا ، مع إضافة السجلات إلى الجدول. سيعمل الحل المباشر من مشغل COPY واحد فقط في المرة الأولى ، ثم سيعرض خطأ بسبب تعارضات المفتاح الأساسي. تم حل هذه المشكلة باستخدام جدول مرحلي وعبارة ON CONFLICT DO NOTHING .
تحميل السجلات في جدول
CREATE TEMP TABLE tmp_table ON COMMIT DROP
AS SELECT * FROM postgres_log WITH NO DATA;
COPY tmp_table FROM '/var/lib/postgresql/data/pg_log/postgresql.csv' WITH csv;
INSERT INTO postgres_log
SELECT * FROM tmp_table WHERE query is not null AND command_tag = 'idle' ON CONFLICT DO NOTHING;يمكنك أيضًا إضافة عامل تصفية عند ترحيل البيانات من جدول مؤقت إلى postgres_log ، مما يقلل من كمية المعلومات غير الضرورية في جدول السجل. نظرًا لأننا لا نخطط لتلقي استعلامات SQL صحيحة من المستخدم ، يمكننا تقييد أنفسنا بالاستعلامات حيث يوجد نص استعلام وعلامة الأمر خاملة.
لسوء الحظ ، لا تحتوي PostgreSQL على برنامج جدولة يدير روتينًا وفقًا لجدول زمني. نظرًا لوجود المشكلة في جزء "الخادم" من اللعبة ، يمكن حلها عن طريق كتابة نص برمجي يستدعي الإجراء المخزن لتحميل السجلات كل ثانية.
ستظهر الآن أي سلسلة أدخلها المستخدم ليست استعلام SQL صالحًا في الجدول postgres_log. على الرغم من أن هذه الطريقة تتطلب فاصلًا إلزاميًا للفاصلة المنقوطة ، إلا أنها أسهل بكثير من إرسال EOF.
سيلاحظ القارئ اليقظ أنه أثناء تنفيذ إجراء أو وظيفة مخزنة ، لن يقوم عميل سطر الأوامر بمعالجة الأوامر وسيكون صحيحًا تمامًا. لكي يعمل هذا الحل ، يلزم وجود عميلين: "شاشة" و "لوحة مفاتيح".
"لإقران" لوحة المفاتيح ، تُنشئ الشاشة تسلسلاً عشوائيًا شبه عشوائي للأحرف التي يجب إدخالها في عميل لوحة المفاتيح. تحدد "الشاشة" لوحة المفاتيح عن طريق المعرف الفريد لجلسة العميل (session_id) ثم تختار من جدول السجل فقط الأسطر التي تحتوي على معرف الجلسة المطلوب.
من السهل أن ترى أن إخراج لوحة مفاتيح العميل غير مفيد ، وأن الإدخال إلى شاشة العميل يقتصر على استدعاء إجراء واحد. لسهولة الاستخدام ، يمكنك إرسال "الشاشة" إلى الخلفية ، وإطفاء إخراج "لوحة المفاتيح":
psql <<<'select keyboard_init()' & psql >/dev/null 2>&1لدينا الآن القدرة على إدخال المعلومات من المدخلات القياسية في قاعدة البيانات واستخدام الإجراءات المخزنة.
حلقة اللعبة
تنقسم اللعبة بشروط إلى المراحل التالية:
- واجهة عميل الشاشة مع عميل لوحة المفاتيح ؛
- إنشاء لوبي أو الاتصال بواحدة قائمة ؛
- وضع السفن
- الجزء النشط من اللعبة.
تتكون اللعبة من خمسة طاولات:
- عرض مرئي للميدان ، جدولين ؛
- قائمة السفن وحالتها ، جدولين ؛
- قائمة الأحداث في اللعبة.
أثناء إنشاء اللوبي ، يقوم اللاعب A ، الخادم ، بإنشاء جميع الجداول وملءها بالقيم الأولية. لإتاحة إمكانية لعب عدة ألعاب على التوازي ، تحتوي جميع الطاولات الموجودة في العنوان على معرف ردهة مكون من عشرة أرقام ، يتم إنشاؤه بشكل شبه عشوائي في بداية اللعبة.
إن تطوير منطق اللعبة بشكل عام مشابه جدًا للتطوير في لغات البرمجة التقليدية ، ويختلف غالبًا في بناء الجملة وعدم وجود مكتبة للتنسيق الجيد. بالنسبة للإخراج ، يتم استخدام عامل RAISE ، والذي يعرض لـ psql رسالة ببادئة مستوى السجل. لن تتمكن من التخلص منه ، لكن هذا لا يتعارض مع اللعبة.
هناك أيضًا اختلافات في التصميم ، وهي تجعل الدماغ يغلي.
وقت الالتزام
يتم تشغيل كل منطق اللعبة بواسطة شاشة العميل ، أي يتم تنفيذ إجراء واحد من البداية إلى النهاية. علاوة على ذلك ، لمعاملة واحدة ، إذا لم يتم تحديد عامل التشغيل COMMIT صراحة.
هذا يعني أن الجداول الجديدة والبيانات الجديدة في الجداول الحالية لن تتغير للاعب الثاني حتى تكتمل المعاملة. علاوة على ذلك ، عند العمل مع الوقت ، من المهم أن تتذكر أن وظيفة now () ترجع الوقت الحالي في الوقت الذي بدأت فيه المعاملة .
إجراء الالتزام ليس سهلاً كما يبدو. يسمح لهم فقط في الإجراءات . ستؤدي محاولة الالتزام بمعاملة ما في إحدى الوظائف إلى حدوث خطأ ، نظرًا لأنها تعمل ضمن معاملة خارج الوظيفة.
تشغيل اللعبة
لا نوصي بتشغيل هذه اللعبة في بيئة حقيقية. لحسن الحظ ، من الممكن نشر قاعدة بيانات باللعبة بسرعة وسهولة. في المستودع ، يمكنك العثور على Dockerfile الذي سيبني صورة باستخدام PostgreSQL 12.4 والتهيئة اللازمة. بناء الصورة وتشغيلها:
docker build -t sql-battleships .
docker run -p 5432:5432 sql-battleshipsالاتصال بقاعدة البيانات في الصورة:
psql -U postgres <<<'call screen_loop()' & psql -U postgresلاحظ أن PostgreSQL في الحاوية تستخدم سياسة مصادقة الثقة ، أي أنها تسمح بجميع الاتصالات بدون كلمة مرور. لا تنس فصل الحاوية بعد الانتهاء من جميع الألعاب!
خاتمة
غالبًا ما يؤدي استخدام الأدوات الخاصة لأغراض أخرى إلى ردود فعل سلبية من المتخصصين. ومع ذلك ، فإن حل المهام التي لا معنى لها ولكنها مثيرة للاهتمام تدرب على التفكير الجانبي وتسمح لك باستكشاف الأداة من وجهات نظر مختلفة بحثًا عن حل مناسب.
اليوم أكدنا مرة أخرى أنه يمكنك كتابة أي شيء تريده في SQL إذا كنت تريد ذلك. ومع ذلك ، نوصي باستخدام الأدوات في الإنتاج للغرض المقصود منها ، والقيام بمثل هذه المتعة حصريًا مثل المشاريع المنزلية الصغيرة.
