آموزش نحوه‌ی استفاده از Generator یا Sequence در Firebird

Generatorها که به آن‌ها Sequence هم گفته می‌شود، برای تولید اعداد یونیک و سریال در فایربرد مورد استفاده قرار می‌گیرند که استفاده‌های متعددی دارد. در اینجا صرفا در مورد نحوه استفاده از آن برای پیاده سازی فیلدهای AutoInc (که در اغلب RDBMSها وجود دارد) توضیح داده می‌شود. در مقایسه با سایر بانک‌های اطلاعاتی که دارای امکان AutoInc هستند، استفاده از Generator نیاز به کمی کار دارد. به عنوان مثال در بانک اطلاعاتی SQL Server هنگام تشکیل یک جدول می‌توان فیلد کلیدی آن را از نوع AutoInc تعریف کرد و همین کار کفایت می‌کند تا هنگام درج اطلاعات در جدول مقدار فیلد فوق به صورت خودکار مقداردهی شود و هر دفعه مقدار آن یکی افزایش یابد. ولی در فایر برد همان طور که گفته شود کمی کار می‌برد، در عوض انعطاف پذیری بیشتری نسبت به سایر بانک‌های اطلاعاتی دارد که در ادامه به آن اشاره خواهد شد.

در ابتدا جدولی با نام PERSONS را در نظر بگیرید که با دستور زیر تشکیل داده شده است:

CREATE TABLE PERSONS

(

   PER_ID INTEGER NOT NULL PRIMARY KEY,

   PER_LNAME VARCHAR(25) NOT NULL,

   PER_FNAME VARCHAR(20)

);

فیلد کلیدی این جدول با نام PER_ID و از نوع INTEGER می‌باشد. ما می‌خواهیم خاصیت AutoInc به آن بدهیم تا با درج رکوردهای جدید مقدار آن به صورت خودکار به ترتیب (1 و ۲ و ۳ و...) مقداردهی شود.

برای استفاده از Generator در فایر برد باید مراحل زیر را انجام دهید:

۱) تشکیل یک Generator برای فیلد مورد نظر:

ساده‌ترین روش برای تشکیل Generator استفاده از یک نرم‌افزار ISQL مناسب مانند FlameRobin (که به رایگان از اینترنت قابل دانلود است) می‌باشد. ابتدا برای Generator خود یک نام مناسب انتخاب نمایید، سپس آن را با دستور زیر تشکیل دهید:

CREATE GENERATOR <generator_name>;

به عنوان مثال دستور زیر یک Generator به نام GEN_PERSONS_ID می‌سازد:

CREATE GENERATOR GEN_PERSONS_ID;

تشکیل Generator به تنهایی کاری برای ما انجام نمی‌دهد و فعال کردن آن نیاز به کمی کدنویسی دارد.

2) تشکیل یک Trigger برای درج مقدار جدید Generator در فیلد Primary: قبل از پرداختن به این موضوع لازم است در ابتدا ببینیم چگونه می‌توان مقدار فعلی Generator را بدست آورد. به کمک تابع زیر می‌توان از مقدار فعلی Generator باخبر شد:

GEN_ID(<generator_name>, 0)

این تابع مقدار فعلی Generator را برمی‌گرداند (بدون آنکه مقدار آن را تغییر دهد). ولی اغلب ما می‌خواهیم پس از بدست آوردن مقدار فعلی، مقدار آن یکی افزایش یابد. برای این کار از تابع زیر می‌توان استفاده نمود:

GEN_ID(<generator_name>, 1)

این تابع ابتدا مقدار فعلی Generator را به ما می‌دهد و سپس مقدار آن را به اندازه‌ی مشخص شده که در این‌جا 1 تعیین شده افزایش می‌دهد. به جای عدد 1 می‌توان عدد دلخواه و مناسب را به آن داد.

حال به بحث اصلی یعنی ساختن یک Trigger برمی‌گردیم. به طور خلاصه می‌توان گفت Trigger مشابه Event در برنامه‌نویسی است و مانند آن روالی است که به هنگام رویداد خاصی فراخوانی می‌شود. یکی از رویدادهایی که در فایربرد قابل کدنویسی است BEFORE INSERT می‌باشد. در صورتی که ما برای این رویداد کدنویسی بکنیم، هنگام درج رکورد جدید کد فوق فراخوانی خواهد شد. کد زیر نمونه بسیار ساده‌ای از کد مورد نیاز ما می‌باشد. نام Trigger را می‌توانید به دلخواه انتخاب کنید ولی بهتر است از یک نام با مسما که معرف هدف آن باشد استفاده کنید. در این‌جا نام آن را PERSONS_BI گذاشته‌ایم که BI مخفف BOFORE INSERT می‌باشد:

SET TERM ^;

CREATE TRIGGER PERSONS_BI FOR PERSONS

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

   NEW.PER_ID = GEN_ID(GEN_PERSONS_ID, 1);

END^

SET TERM ;^

در قسمت بدنه این روال (خط شماره ۶) مقدار جدید فیلد PER_ID با مقدار برگشتی تابع GEN_ID مقداردهی می‌شود. از این به بعد هر گاه رکورد جدیدی به جدول PERSONS اضافه کنیم فیلد PER_ID به صورت خودبخود به مقدار مناسب مقداردهی می‌شود. در دستور INSERT زیر هیچ مقداری برای فیلد PER_ID مشخص نشده ولی دستور بدون مشکل اجراء می‌شود. در صورتی که اگر Trigger فوق ساخته نشده بود دستور با مشکل مواجه می‌شد چون فیلد PER_ID از نوع NOT NULL تعریف شده و حتما باید دارای مقدار باشد. در این‌جا مقداردهی به صورت خوکار صورت پذیرفته است.

INSERT INTO PERSONS

(PER_LNAME, PER_FNAME)

VALUES ('Akbari', 'Reza')

اما در این روال یک مشکل وجود دارد و آن این است که اگر بخواهیم خودمان مقدار فیلد PER_ID را در دستور INSERT تعیین کنیم امکان‌پذیر نمی‌باشد:

INSERT INTO PERSONS

(PER_ID, PER_LNAME, PER_FNAME)

VALUES (5, 'Akbari', 'Reza')

در دستور فوق مقدار 5 که برای فیلد PER_ID تعیین شده نادیده گرفته خواهد شد و باز هم مقدار آن به صورت خودکار توسط Generator تعیین خواهد شد. برای برطرف کردن این مشکل باید در Trigger خود تغییراتی بدهیم تا فقط در صورتی که مقداری برای PER_ID مشخص نشده باشد از Generator کمک بگیرد. در زیر یک نمونه کامل از Trigger فوق آورده شده که می‌توانید از آن به عنوان یک قالب برای تمام حالات مشابه استفاده کنید:

SET TERM ^;

CREATE TRIGGER PERSONS_BI FOR PERSONS

ACTIVE BEFORE INSERT POSITION 0

AS

DECLARE VARIABLE tmp DECIMAL(18,0);

BEGIN

   IF (NEW.PER_ID IS NULL OR NEW.PER_ID = 0) THEN

      NEW.PER_ID = GEN_ID(GEN_PERSONS_ID, 1);

   ELSE

   BEGIN

      tmp = GEN_ID(GEN_PERSONS_ID, 0);

      if (tmp < NEW.PER_ID) THEN

         tmp = GEN_ID(GEN_PERSONS_ID, NEW.PER_ID - tmp);

   END

END^

SET TERM ;^

در ابتدای بحث گفته شد شیوه‌ای که فایربرد برای این کار انتخاب کرده انعطاف‌پذیری بیشتری نسبت به سایر بانک‌های اطلاعاتی دارد (البته تا جایی که من می‌دانم). فرض کنید در یک محیط برنامه‌نویسی مانند C++‎‎‎‎‎‎‎‎‎Builder بخواهیم رکورد جدیدی به جدول PERSONS اضافه کنیم (فرض کنید قبلا یک Connection برای بانک اطلاعاتی خود ساخته‌ایم و نام آن را AdoCon گذاشته‌ایم):

TADOQuery* loQuery = new TADOQuery(Application);

try // finally

{

   loQuery->Connection = AdoCon;

   loQuery->SQL->Clear();

   loQuery->SQL->Add(L"INSERT INTO PERSONS");

   loQuery->SQL->Add(L"(PER_LNAME, PER_FNAME)");

   loQuery->SQL->Add(L"VALUES ('Mohseni', 'Saiid')");

   loQuery->ExecSQL();

}

__finally

{

   loQuery->Free();

}

این قطعه کد با موفقیت اجراء می‌شود و رکورد جدید به جدول اضافه می‌شود و برای فیلد PER_ID نیز به صورت خودکار مقدار مناسبی با استفاده از Generator انتخاب می‌شود. حال اگر ما به مقداری که به فیلد PER_ID اختصاص داده شده در برنامه‌یمان نیاز داشتیم چکار باید بکنیم؟
این مشکل هنگام استفاده از بانک اطلاعاتی SQL Server که دارای خاصیت AutoInc برای فیلدهای Primary می‌باشد برای من همیشه وجود داشته و راه حلی برای آن سراغ ندارم (البته زیاد در باره‌ی SQL Server مطالعه نکرده‌ام). اگر از دوستان در این زمینه می‌توانند راهنمایی بکنند بسیار ممنون می‌شوم.
در بانک اطلاعاتی MySQL تابعی به همین منظور وجود دارد به نام mysql_insert_id که بعد از دستور INSERT می‌توان آن را فراخوانی کرد تا مقدار اختصاص داده شده به Primary Key را برگرداند.
اما در فایربرد: برای این منظور باید روش دیگری انتخاب کنیم. با توجه به انعطاف‌پذیری فایربرد در این زمینه، ما می‌توانیم قبل از درج کردن رکورد جدید، ابتدا مقدار کنونی Generator را بدست آوریم و سپس از آن در دستور INSERT استفاده کنیم و در ادامه نیز آن را مورد استفاده قرار دهیم. در زیر قطعه کد تکمیل شده آورده شده است:

TADOQuery* loQuery = new TADOQuery(Application);

try // finally

{

   // Get current value of generator:

   loQuery->Connection = AdoCon;

   loQuery->SQL->Clear();

   loQuery->SQL->Add(L"SELECT GEN_ID(GEN_PERSONS_ID, 1) NewId");

   loQuery->SQL->Add(L"FROM RDB$DATABASE");

   loQuery->Open();

   __int64 liPerId = loQuery->FieldByName(L"NewId")->AsInteger;

   loQuery->Close();



   // Insert new data:

   loQuery->Connection = AdoCon;

   loQuery->SQL->Clear();

   loQuery->SQL->Add(L"INSERT INTO PERSONS");

   loQuery->SQL->Add(L"(PER_ID, PER_LNAME, PER_FNAME)");

   loQuery->SQL->Add(L"VALUES (" + IntToStr(liPerId) + L", 'Mohseni', 'Saiid')");

   loQuery->ExecSQL();



   // Now: We know the value of PER_ID in the inserted record: liPerId

   // ...

}

__finally

{

   loQuery->Free();

}

RDB$DATABASE نام یکی از جداول سیستمی فایربرد می‌باشد. فکر می‌کنم کد فوق به قدر کافی گویا هست، ولی در صورت نیاز بپرسید تا توضیح دهم.

نکته: برای تست قطعه کدهای فوق از Firebird 2.5.2 Security Update 1 و FlameRobin 0.9.2.1851 و C++‎‎‎‎Builder XE استفاده شده و بدون خطا اجراء شد.
موفق و پیروز باشید.