خلاصه اشتراکهای روز 1390/06/25
جناب محبی اون لینک مربوط به صفحه آبی مرگ ویندوز 8 بود
http://8.mshcdn.com/wp-content/uploads/2011/09/314,win8bsod.jpg
SQL Antipattern #2
بخش دوم : Naive Trees
فرض کنید یک وب سایت حرفهای خبری یا علمی-پژوهشی داریم که قابلیت دریافت نظرات کاربران را در مورد هر مطلب مندرج در سایت یا نظرات داده شده در مورد آن مطالب را دارا میباشد. یعنی هر کاربر علاوه بر توانایی اظهار نظر در مورد یک خبر یا مطلب باید بتواند پاسخ نظرات کاربران دیگر را نیز بدهد. اولین راه حلی که برای طراحی این مطلب در پایگاه داده به ذهن ما میرسد، ایجاد یک زنجیره با استفاده از کد sql زیر میباشد:
CREATE TABLE Comments ( comment_idSERIAL PRIMARY KEY, parent_idBIGINT UNSIGNED, comment TEXT NOT NULL, FOREIGN KEY (parent_id) REFERENCES Comments(comment_id) );
البته همان طور که پیداست بازیابی زنجیرهای از پاسخها در یک پرسوجوی sql کار سختی است. این نخها معمولا عمق نامحدودی دارند و برای به دست آوردن تمام نخهای یک زنجیره باید پرسوجوهای زیادی را اجرا نمود.
ایدهی دیگر میتواند بازیابی تمام نظرها و ذخیرهی آنها در حافظهی برنامه به صورت درخت باشد. ولی این روش برای ذخیره هزاران نظری که ممکن است در سایت ثبت شود و علاوه بر آن مقالات جدیدی که اضافه میشوند، تقریبا غیرعملی است.
1.2 هدف: ذخیره و ایجاد پرسوجو در سلسلهمراتب
وجود سلسله مراتب بین دادهها امری عادی محسوب میگردد. در ساختار دادهای درختی هر ورودی یک گره محسوب میگردد. یک گره ممکن است تعدادی فرزند و یک پدر داشته باشد. گره اول پدر ندارد، ریشه و گره فرزند که فرزند ندارد، برگ و گرهای دیگر، گرههای غیربرگ نامیده میشوند.
مثالهایی که از ساختار درختی دادهها وجود دارد شامل موارد زیر است:
Organizational chart: در این ساختار برای مثال در ارتباط بین کارمندان و مدیر، هر کارمند یک مدیر دارد که نشاندهندهی پدر یک کارمند در ساختار درختی است. هر مدیر هم یک کارمند محسوب میگردد.
Threaded discussion: در این ساختار برای مثال در سیستم نظردهی و پاسخها، ممکن است زنجیرهای از نظرات در پاسخ به نظرات دیگر استفاده گردد. در درخت، فرزندان یک گرهی نظر، پاسخهای آن گره هستند.
در این فصل ما از مثال ساختار دوم برای نشان دادن Antipattern و راه حل آن بهره میگیریم.
2.2 Antipattern : همیشه مبتنی بر یکی از والدین
راه حل ابتدایی و ناکارآمد
اضافه نمودن ستون parent_id . این ستون، به ستون نظر در همان جدول ارجاع داده میشود و شما میتوانید برای اجرای این رابطه از قید کلید خارجی استفاده نمایید. پرسوجویی که برای ساخت مثالی که ما در این بحث از آن استفاده میکنیم در ادامه آمده است:
CREATE TABLE Comments ( comment_idSERIAL PRIMARY KEY, parent_idBIGINT UNSIGNED, bug_idBIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_dateDATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (parent_id)REFERENCES Comments(comment_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY(author) REFERENCES Accounts(account_id) );
لیست مجاورت :
لیست مجاورت خود میتواند به عنوان یک antipattern در نظر گرفته شود. البته این مطلب از آنجایی نشأت میگیرد که این روش توسط بسیاری از توسعهدهندگان مورد استفاده قرار میگیرد ولی نتوانسته است به عنوان راه حل برای معمولترین وظیفهی خود، یعنی ایجاد پرسوجو بر روی کلیه فرزندان، باشد.
• با استفاده از پرسوجوی زیر میتوان فرزند بلافاصلهی یک "نظر" را برگرداند:
SELECT c1.*, c2.* FROM Comments c1 LEFT OUTER JOIN Comments c2 ON c2.parent_id = c1.comment_id;
ضعف پرسوجوی فوق این است که فقط میتواند دو سطح از درخت را برای شما برگرداند. در حالیکه خاصیت درخت این است که شما را قادر میسازد بدون هیچ گونه محدودیتی فرزندان و نوههای متعدد (سطوح بیشمار) برای درخت خود تعریف کنید. بنابراین به ازای هر سطح اضافه باید یک join به پرسجوی خود اضافه نمایید. برای مثال اگر پرسوجوی زیر میتواند درختی با چهار سطح برای شما برگرداند ولی نه بیش از آن:
SELECT c1.*, c2.*, c3.*, c4.* FROM Comments c1 -- 1st level LEFT OUTER JOIN Comments c2 ON c2.parent_id = c1.comment_id -- 2nd level LEFT OUTER JOIN Comments c3 ON c3.parent_id = c2.comment_id -- 3rd level LEFT OUTER JOIN Comments c4 ON c4.parent_id = c3.comment_id; -- 4th level
این پرسوجو به این دلیل که با اضافه شدن ستونهای دیگر، نوهها را سطوح عمیقتری برمیگرداند، پرسوجوی مناسبی نیست. در واقع استفاده از توابع تجمیعی ، مانند COUNT() مشکل میشود.
راه دیگر برای به دست آوردن ساختار یک زیردرخت از لیست مجاورت برای یک برنامه، این است که سطرهای مورد نظر خود را از مجموعه بازیابی نموده و سلسهمراتب مورد نظر را در حافظه بازیابی نماییم و از آن به عنوان درخت استفاده نماییم:
SELECT * FROM Comments WHERE bug_id = 1234;
INSERT INTO Comments (bug_id, parent_id, author, comment) VALUES (1234, 7, 'Kukla' , 'Thanks!' );
UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;
SELECT parent_id FROM Comments WHERE comment_id = 6; -- returns 4 UPDATE Comments SET parent_id = 4 WHERE parent_id = 6; DELETE FROM Comments WHERE comment_id = 6;
- چه تعداد سطح برای پشتیبانی در درخت نیاز خواهیم داشت؟
- من همیشه از کار با کدی که ساختار دادهی درختی را مدیریت میکند، میترسم
- من باید اسکریپتی را به طور دورهای اجرا نمایم تا سطرهای یتیم موجود در درخت را حذف کند.
WITH CommentTree (comment_id, bug_id, parent_id, author, comment, depth) AS ( SELECT *, 0 AS depth FROM Comments WHERE parent_id IS NULL UNION ALL SELECT c.*, ct.depth+1 AS depth FROM CommentTreect JOIN Comments c ON (ct.comment_id = c.parent_id) ) SELECT * FROM CommentTree WHERE bug_id = 1234;
SELECT * FROM Comments START WITH comment_id = 9876 CONNECT BY PRIOR parent_id = comment_id;
CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, path VARCHAR(1000), bug_id BIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (author) REFERENCES Accounts(account_id)
SELECT * FROM Comments AS c WHERE '1/4/6/7/' LIKE c.path || '%' ;
SELECT * FROM Comments AS c WHERE c.path LIKE '1/4/' || '%' ;
CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, nsleft INTEGER NOT NULL, nsright INTEGER NOT NULL, bug_id BIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id), FOREIGN KEY (author) REFERENCES Accounts(account_id) );
شمارهی سمت چپ یک گره از تمام شمارههای سمت چپ فرزندان آن گره کوچکتر و شمارهی سمت راست آن گره از تمام شمارههای سمت راست آن گره بزرگتر است. این شمارهها هیچ ارتباطی به comment_id مربوط به آن گره ندارند.
یک راه حل ساده برای تخصیص این شمارهها به گرهها این است که از سمت چپ یک گره آغاز میکنیم و اولین شماره را اختصاص میدهیم و به همین به گرهای سمت چپ فرزندان میآییم و شمارهها را به صورت افزایشی به سمت چپ آنها نیز اختصاص میدهیم. سپس در ادامه به سمت راست آخرین نود رفته و از آن جا به سمت بالا میآییم و به همین ترتیب به صورت بازگشتی تخصیص شمارهها را ادامه میدهیم.
با اختصتص شمارهها به هر گره، میتوان از آنها برای یافتن نیاکان و فرزندان آن گره بهره جست. برای مثال برای بازیابی گرهی 4 و فرزندان (نوههای) آن باید دنبال گرههایی باشیم که شمارههای آن گرهها بین nsleft و nsright گرهی شماره4 باشد:
SELECT c2.* FROM Comments AS c1 JOIN Comments as c2 ON c2.nsleft BETWEEN c1.nsleft AND c1.nsright WHERE c1.comment_id = 4;
SELECT c2.* FROM Comments AS c1 JOIN Comment AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright WHERE c1.comment_id = 6;
SELECT parent.* FROM Comment AS c JOIN Comment AS parent ON c.nsleft BETWEEN parent.nsleft AND parent.nsright LEFT OUTER JOIN Comment AS in_between ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright WHERE c.comment_id = 6 AND in_between.comment_id IS NULL;
-- make space for NS values 8 and 9 UPDATE Comment SET nsleft = CASE WHEN nsleft >= 8 THEN nsleft+2 ELSE nsleft END, nsright = nsright+2 WHERE nsright >= 7; -- create new child of comment #5, occupying NS values 8 and 9 INSERT INTO Comment (nsleft, nsright, author, comment) VALUES (8, 9, 'Fran' , 'Me too!' );
CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, bug_id BIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id), FOREIGN KEY (author) REFERENCES Accounts(account_id) ); CREATE TABLE TreePaths ( ancestor BIGINT UNSIGNED NOT NULL, descendant BIGINT UNSIGNED NOT NULL, PRIMARY KEY(ancestor, descendant), FOREIGN KEY (ancestor) REFERENCES Comments(comment_id), FOREIGN KEY (descendant) REFERENCES Comments(comment_id) );
به جای استفاده از جدول Comments برای ذخیرهی اطلاعات مربوط به یک درخت از جدول TreePath استفاده میکنیم. به ازای هر یک جفت گره در این درخت یک سطر در جدول ذخیره میشود که ارتباط پدر فرزندی را نمایش میدهد و الزاما نباید این دو پدر فرزند بلافصل باشد. همچنین یک سطر هم به ازای ارتباط هر گره با خودش به جدول اضافه میگردد.
پرسوجوهای بازیابی نیاکان و فرزندان (گرهها) از طریق جدول TreePaths سادهتر از روش مجموعههای تودرتو است. مثلا برای بازیابی فرزندان (نوههای) گرهی شمارهی 4، سطرهایی که نیاکان آنها 4 است را به دست میآوریم:
SELECT c.* FROM Comments AS c JOIN TreePaths AS t ON c.comment_id = t.descendant WHERE t.ancestor = 4;
SELECT c.* FROM Comments AS c JOIN TreePaths AS t ON c.comment_id = t.ancestor WHERE t.descendant = 6;
INSERT INTO TreePaths (ancestor, descendant) SELECT t.ancestor, 8 FROM TreePaths AS t WHERE t.descendant = 5 UNION ALL SELECT 8, 8;
DELETE FROM TreePaths WHERE descendant = 7;
DELETE FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = 4);
DELETE FROM TreePaths WHERE descendant IN (SELECT descendant FROM TreePaths WHERE ancestor = 6) AND ancestor IN (SELECT ancestor FROM TreePaths WHERE descendant = 6 AND ancestor != descendant);
INSERT INTO TreePaths (ancestor, descendant) SELECT supertree.ancestor, subtree.descendant FROM TreePaths AS supertree CROSS JOIN TreePaths AS subtree WHERE supertree.descendant = 3 AND subtree.ancestor = 6;
میتوان عملکرد Closure Table را برای ایجاد پرسوجو روی فرزندان و پدر بلافصل را آسانتر نیز نمود. اگر فیلد path_length را به جدول TreePaths اضافه نماییم این کار انجام میشود. path_length گرهای که به خودش ارجاع میشود، صفر است. path_length فرزند بلافصل هر گره 1، path_length نوهی آن 2 میباشد و به همین ترتیب path_lengthها را در هر سطر مقداردهی میکنیم. اکنون یا فتن فرزند گرهی شمارهی 4 آسانتر است:
SELECT * FROM TreePaths WHERE ancestor = 4 AND path_length = 1;
public void ConfigureServices(IServiceCollection services) { // DbContext Service services.AddDbContext<AppDbContext>(options => { options .UseSqlServer(appSettings.ConnectionStrings.MyConnectionString, sqlServerOptionsBuilder => { sqlServerOptionsBuilder.CommandTimeout((int)TimeSpan.FromMinutes(1).TotalSeconds); //Default is 30 seconds sqlServerOptionsBuilder.EnableRetryOnFailure(); sqlServerOptionsBuilder.MigrationsAssembly(typeof(AppDbContext).Assembly.FullName); }) //Tips .ConfigureWarnings(warning => warning.Throw(RelationalEventId .QueryPossibleExceptionWithAggregateOperatorWarning)); // Activate EF Second Level Cache options.AddInterceptors(new SecondLevelCacheInterceptor()); }); // register other services .... }
راه دوم روش استفاده از متدهای الحاقی است؛ طوریکه برای هر سرویس، یک متد الحاقی را تعریف کنیم و از آن، در این متد استفاده کنیم که حجم کدها را تا حد زیادی کم میکند. برای مثال ثبت سرویس بالا را میتوانیم در کلاس دیگری با نام DbContextServiceCollectionExtensions.cs ثبت کنیم:
public static class DbContextServiceCollectionExtensions { public static void AddDbContext(this IServiceCollection services) { services.AddDbContext<AppDbContext>(options => { options .UseSqlServer(appSettings.ConnectionStrings.MyConnectionString, sqlServerOptionsBuilder => { sqlServerOptionsBuilder.CommandTimeout((int)TimeSpan.FromMinutes(1).TotalSeconds); //Default is 30 seconds sqlServerOptionsBuilder.EnableRetryOnFailure(); sqlServerOptionsBuilder.MigrationsAssembly(typeof(AppDbContext).Assembly.FullName); }) //Tips .ConfigureWarnings(warning => warning.Throw(RelationalEventId .QueryPossibleExceptionWithAggregateOperatorWarning)); // Activate EF Second Level Cache options.AddInterceptors(new SecondLevelCacheInterceptor()); }); } }
public void ConfigureServices(IServiceCollection services) { // Add DbContext services.AddDbContext(); //.... Register other services }
public interface IServiceInstaller { void InstallServices(IServiceCollection services, AppSettings appSettings, Assembly startupProjectAssembly); }
public class DbContextInstaller : IServiceInstaller { public void InstallServices(IServiceCollection services, AppSettings appSettings, Assembly startupProjectAssembly) { services.AddDbContext<AppDbContext>(options => { options .UseSqlServer(appSettings.ConnectionStrings.MyConnectionString, sqlServerOptionsBuilder => { sqlServerOptionsBuilder.CommandTimeout((int)TimeSpan.FromMinutes(1).TotalSeconds); //Default is 30 seconds sqlServerOptionsBuilder.EnableRetryOnFailure(); sqlServerOptionsBuilder.MigrationsAssembly(typeof(AppDbContext).Assembly.FullName); }) //Tips .ConfigureWarnings(warning => warning.Throw(RelationalEventId .QueryPossibleExceptionWithAggregateOperatorWarning)); // Activate EF Second Level Cache options.AddInterceptors(new SecondLevelCacheInterceptor()); }); } }
public static class ServiceInstallerExtensions { public static void InstallServicesInAssemblies(this IServiceCollection services, AppSettings appSettings) { var startupProjectAssembly = Assembly.GetCallingAssembly(); var assemblies = new[] { startupProjectAssembly, Assembly.GetExecutingAssembly() }; var installers = assemblies.SelectMany(a => a.GetExportedTypes()) .Where(c => c.IsClass && !c.IsAbstract && c.IsPublic && typeof(IServiceInstaller).IsAssignableFrom(c)) .Select(Activator.CreateInstance).Cast<IServiceInstaller>().ToList(); installers.ForEach(i => i.InstallServices(services, appSettings, startupProjectAssembly)); } }
public void ConfigureServices(IServiceCollection services) { //* HttpContextAccessor // services.AddHttpContextAccessor(); //* Controllers services.AddControllers(options => { options.Filters.Add(new AuthorizeFilter()); }) .AddNewtonsoftJson(); //* Installers services.InstallServicesInAssemblies(_appSettings); }
public class RegisterServicesUsingAutoRegisterDiInstaller : IServiceInstaller { public void InstallServices(IServiceCollection services, AppSettings appSettings, Assembly startupProjectAssembly) { var dataAssembly = typeof(SomeRepository).Assembly; var serviceAssembly = typeof(SomeService).Assembly; var webFrameworkAssembly = Assembly.GetExecutingAssembly(); var startupAssembly = startupProjectAssembly; var assembliesToScan = new[] { dataAssembly, serviceAssembly, webFrameworkAssembly, startupAssembly }; #region Generic Type Dependencies services.AddScoped(typeof(IRepository<>), typeof(Repository<>)); #endregion #region Scoped Dependency Interface services.RegisterAssemblyPublicNonGenericClasses(assembliesToScan) .Where(c => c.GetInterfaces().Contains(typeof(IScopedDependency))) .AsPublicImplementedInterfaces(ServiceLifetime.Scoped); #endregion #region Singleton Dependency Interface services.RegisterAssemblyPublicNonGenericClasses(assembliesToScan) .Where(c => c.GetInterfaces().Contains(typeof(ISingletonDependency))) .AsPublicImplementedInterfaces(ServiceLifetime.Singleton); #endregion #region Transient Dependency Interface services.RegisterAssemblyPublicNonGenericClasses(assembliesToScan) .Where(c => c.GetInterfaces().Contains(typeof(ITransientDependency))) .AsPublicImplementedInterfaces(); // Default is Transient #endregion #region Register DIs By Name services.RegisterAssemblyPublicNonGenericClasses(dataAssembly) .Where(c => c.Name.EndsWith("Repository") && !c.GetInterfaces().Contains(typeof(ITransientDependency)) && !c.GetInterfaces().Contains(typeof(IScopedDependency)) && !c.GetInterfaces().Contains(typeof(ISingletonDependency))) .AsPublicImplementedInterfaces(ServiceLifetime.Scoped); services.RegisterAssemblyPublicNonGenericClasses(serviceAssembly) .Where(c => c.Name.EndsWith("Service") && !c.GetInterfaces().Contains(typeof(ITransientDependency)) && !c.GetInterfaces().Contains(typeof(IScopedDependency)) && !c.GetInterfaces().Contains(typeof(ISingletonDependency))) .AsPublicImplementedInterfaces(); #endregion } }