SQL-file technology (EN/RU)

ARTICLES, BLOGS, FORUMS (EN/RU):

SQL-file technology, in English (at , subdomain, backward chronological order of the posts, EN):
SQL-File Technology for Transact-SQL:

SQL-File Technology for Transact-SQL (article at , developer's resource, EN):
Author: Sergei Y.Kitáev (Китаев С.Ю.);
Published: December 19, 2022;

SQL-file technology, in Russian (at , subdomain, backward chronological order of the posts, RU):
1) 2) 3) 4) 5)

SQL-file technology, preprocessor for T-SQL, Side-by-Side files etc., in Russian:
Title: Технология SQL-файл, препроцессор для T-SQL, бок-о-бок файлы и др;
Author: Китаев С.Ю. (Sergei Y.Kitáev);
Published: June 25, 2022;
/1,2/

SQL-file micro-technology is related to programming in Transact-SQL. SQL-file is a set of techniques which are intended to maintain special SQL-code project (a set of SQL-scripts) for programming at DB-level. Such SQL-project corresponds to a set of active DB-objects whose scripts are stored on base of traditional file/directory tree. SQL-file is represented in the first place by SQL-tools — lightweight command-line tools which provide effect of so-called SQL-code translation: from SQL-files to SQL-database (DB-objects).

See related topic (local page):

SQL-file micro-technology or “SQL-in-files” is a set of utilities, techniques and templates for translation of script files into objects in relational database, with using of corresponding advantages of file storage for SQL-code (directories/files), and also with elements of light SQL-preprocessing on base of well-known SQLCMD-tool (from Microsoft), with special addings and wrappers. There is possibility for performance of translation of whole tree of SQL-scripts in one go (as well as individually).

Key tool named $SqlTrans that is wrapping CMD-utility is effectively integrated with Far Manager (and not only with Far Manager) which is main instrument in SQL-file, providing effect of presence of lightweight alternative IDE. There is a way for attachment of import-file of SQL-preprocessor (on base of environment variables). Far Manager in this context plays role of original handicraft command console for performance of SQL-operations (in “manual mode”).

Present version of tools and libraries is intended for development in Microsoft SQL Server DBMS.

Main idea of SQL-file is to have a maintenance of internal active objects of SQL-server (SP, function, trigger etc.) with storage of correspongding scripts in files with possibility to edit the sources. Application/service, when it interoperates with database usually does it via so-called active SQL-constituent in DB — procedural API — SQL-subsystem (as part of the application in DB), which in any moment we can retranslate from files (in concord with corresponding application or service). Also SQL-file may be applied for operative deployment of dependent (one on another) table-structures in DB — from scripts (with foreign keys, indexes, triggers etc.).

In a base of SQL-file there is desire of elemination of some obstacles in SQL-programming and in usage of SQL as a first-class programming language. Obtainment of possibility of natural and unified location/application of source texts in file tree fills existing gap which is present (in topic of SQL-projects) in official instruments like SQL Server Management Studio and Visual Studio. Used in SQL-file organization of weak-dependent-from-IDE file project allows to look on SQL-files so as to separately compiled fragments of a big program, with attachement of necessary extension import-definitions (like header H-files in C-language), enhancing thereby insufficient original SQL-syntax. (For instance, extended keywords $(BEGIN_AUX) and $(END_AUX) together with application of key settings “set” solve a problem of error control, installing also corresponding “try-catch”-interceptor for main block.)

Above described idea is realized only partially in SQL-file. The tools are implemented in so-called handicraft style. However inspite of some lacks (for instance there is no code autocompletion in editor) and inspite of modest present realization form, proposed approach (in case of its proper application) allows to bring certain gain to somebody who deeply codes in Transact-SQL. However usage of SQL-file technology implies a presence of some fantasy in desire to have features that are absent in traditional SQL-language programming style.

SQL-file technology includes following (main and additional) components:

1) Command utilities: $SqlTrans, $SqlUplodad, $SqlToCsvExp (SqlToCsvExport), $SqlCon, $SQL;
2) SQLAUX scripting library (general purpose), with documental SQL header files;
3) Far Manager plugin — CmdCpAuto (DLL): auxiliary adding to file manager for automatic opening of CMD-batch in corresponding OEM or UTF-8 encoding (in Far editor);
4) Simple macro (.lua) and instructions (.txt) for integration of $SqlTrans with panels and editor of Far Manager (and not only with Far Manager);
5) Simple program examples of enhanced T-SQL;
6) Simple Web-application +DB (deployment): Extralight-ORM test legacy sample (En+Ru);
7) Cyrillic code collection (in Russian) named SQL_RU: examples or real usage of SQL-file (SP subsystems, creation of table structures, report selections, interactive operations etc.).

For further familiarization with SQL-file look content of “HandicraftSDK\SQL”.

See also illustrations (screenshot pictures + fragments of code):

– “HandicraftSDK\SQL\Extralight-ORM test legacy sample (En+Ru)\Illustration (screenshots, fragments)”;
– “HandicraftSDK\SQL\Use of SQL-infrastructure (legacy pictures)”.

For integration of $SqlTrans with another IDE (not Far Manager) see attentively instructions (in “D:\HANDICRAFT\HandicraftSDK\CMD-utilities\Shell\Integration”).
Also there is a possibility of partially restricted use (environment only) of SQL-file technology with SSMS (SQL-Server Management Studio).

SQLAUX scripting library is an inherent adding to SQL-file, a part of the technology. SQLAUX represents collection of helper objects in T-SQL: SP, functions, some tables. And simultaneously it provides a set of preprocessor definitions available for user SQL via environment variables (through “$(…)”-references). Such definitions are imported via attachment of library CMD from “HandicraftSDK\SQL\SQLAUX\Import”-folder. For familiarization with import techniques, please, examine source scripts and batches of SQLAUX and samples. (SQLAUX is normally included by $sql_settings.cmd from user project: call :CALL_SETTINGS_BATCH "!SQLAUX_ROOT!\Import\SQLAUX.cmd".) Being a SQL-file project — SQLAUX libray may be translated to DB in one go (by execution of translate_programmatics.cmd–batch).

SQLAUX library (its scripts and imports) together with base SQL-tools supply so-called enhanced SQL-language (with preprocessor and helpers).

Subfolders of “HandicraftSDK\SQL\SQLAUX”-folder:

– “API (headers)”: documental headers for SQL-objects and import definitions (“#….sql”, “….txt”);
– “Import”: SQLAUX import definitions (“*.cmd”);
– “Source”: library main sources (except those CMD which are in “Import”-folder);
– “Templates (dummies)”: pattern files for library extension (empty prototypes).

See source view and DB-object listing at the end of this page:

–
– .

SQL-file inherent samples (script-only):

1) “HandicraftSDK\SQL\Programming samples (SQL-file)\GUID-list generation”;
2) “HandicraftSDK\SQL\Programming samples (SQL-file)\Lists downloading (sys-info)”;
3) “HandicraftSDK\SQL\Programming samples (SQL-file)\Nested transaction test”;
4) “HandicraftSDK\SQL\Programming samples (SQL-file)\Simple script (Hello);
5) “HandicraftSDK\SQL\Programming samples (SQL-file)\Trigger test”;
6) “HandicraftSDK\SQL\Programming samples (SQL-file)\Uploading to DB (used buildings)”.

Additional SQL-samples in the SDK (apps and other):

– “HandicraftSDK\SQL\Extralight-ORM test legacy sample (En+Ru)”: simple Web-application +DB (deployment);
– “HandicraftSDK\Old-Legacy\SQL_RU (sample collection).7z”: Cyrillic code collection (in Russian) named SQL_RU.

SQL extra sample, in the Handicraft Toolkit (as a part of the BookRegistry app.):

– “BookRegistry\SQL (DB-modelling)”: SQL-project base (tables, app.invisible subqueries);
– “BookRegistry\SHARED\$ClientServer\!DB-queries (application SP)”: application queries, C#/SQL side-by-side.

Programmatics.AUX.txt:

=== LIST OF PROGRAMMATICS (SQLAUX LIBRARY): === Stored procedures ---------------------------------------------------------------------------------------------------- AUX:DropFunction AUX:DropFunctions AUX:DropProcedure AUX:DropProcedures AUX:DropTable AUX:DropTrigger AUX:DropTriggers AUX:DropType AUX:DropTypes AUX:DropView AUX:DropViews AUX:GrantExecOnProcedures AUX:InExternalTransaction.Verify AUX:ListProcesses AUX:RaiseBlockedProcedure AUX:RaiseBlockedScript AUX:RaiseDebugException AUX:RaiseError AUX:RaiseIncorrectProcedureCall AUX:RaiseNotImplemented AUX:RaiseNullParameter AUX:RaiseRollbackException AUX:RaiseUnexpectedInternalError AUX:RevokeExecOnProcedures AUX:SubstEmptyWithNull AUX:TrimEndingSpaces AUX:TrimSpaces AUX:TryMakeDateFromStr (28 rows affected) Scalar functions ---------------------------------------------------------------------------------------------------- AUX:ActiveLanguage AUX:BitInversion AUX:CompareDate.YMD AUX:CompareDates AUX:DateAsString AUX:DateAsString.Ext AUX:DateFormat AUX:DateTimeAsString AUX:DateTimeAsString.Ext AUX:DateValue.YMD AUX:DaysInMonth AUX:DaysInMonth.ByDate AUX:DefaultLanguageId AUX:EmptyAsNull AUX:EmptyGUID AUX:FormatByWidth.BigInt AUX:FormatByWidth.DecMoney AUX:FormatByWidth.DHP AUX:FormatByWidth.DMP AUX:FormatByWidth.Int AUX:FormatByWidth_2.DecMoney AUX:FormatGUID AUX:FormatNumByWidth AUX:HasSignificantSpaces AUX:IntRound.DecMoney AUX:IntRound.DHP AUX:IntRound.DMP AUX:IntRound.Float AUX:IntSign.BigInt AUX:IntSign.DecMoney AUX:IntSign.DHP AUX:IntSign.DMP AUX:IntSign.Float AUX:IntSign.Int AUX:IsDigit AUX:IsEmptyGUID AUX:IsInDefaultLangLocale AUX:IsInMainLanguageLocale AUX:IsInServerAdminRole AUX:IsPureDate AUX:IsSignificantGUID AUX:IsSupportedLanguage AUX:LocalizedText AUX:LocalizedText.X1 AUX:LocalizedText.X2 AUX:LocalLanguageName AUX:LogicalSign.BigInt AUX:LogicalSign.DateTime AUX:LogicalSign.DecMoney AUX:LogicalSign.DHP AUX:LogicalSign.DMP AUX:LogicalSign.Float AUX:LogicalSign.Int AUX:LogicalSign.Str AUX:MainLanguageId AUX:Maximum.BigInt AUX:Maximum.DecMoney AUX:Maximum.DHP AUX:Maximum.DMP AUX:Maximum.Float AUX:Maximum.Int AUX:Minimum.BigInt AUX:Minimum.DecMoney AUX:Minimum.DHP AUX:Minimum.DMP AUX:Minimum.Float AUX:Minimum.Int AUX:MonthName AUX:MonthName.Ext AUX:MonthNameWithYear AUX:MonthNameWithYear.Ext AUX:NegativeOrZero.BigInt AUX:NegativeOrZero.DecMoney AUX:NegativeOrZero.DHP AUX:NegativeOrZero.DMP AUX:NegativeOrZero.Float AUX:NegativeOrZero.Int AUX:OverrideLanguageWithMain AUX:PadEnd AUX:PadEnd.Ext AUX:PadStart AUX:PadStart.Ext AUX:PositiveOrZero.BigInt AUX:PositiveOrZero.DecMoney AUX:PositiveOrZero.DHP AUX:PositiveOrZero.DMP AUX:PositiveOrZero.Float AUX:PositiveOrZero.Int AUX:PureDate AUX:RoundDiv AUX:RoundDiv.Ext AUX:SpecialLanguage AUX:StartsFromSpace AUX:StartsFromSpace.Ext AUX:StrLength AUX:TechLanguage AUX:TechLocalText AUX:ThroughComma AUX:ThroughComma_3 AUX:ThroughComma_4 AUX:ThroughComma_5 AUX:ThroughCommaAndSpace AUX:ThroughCommaAndSpace_3 AUX:ThroughCommaAndSpace_4 AUX:ThroughCommaAndSpace_5 AUX:ThroughSeparator AUX:ThroughSeparator_3 AUX:ThroughSeparator_4 AUX:ThroughSeparator_5 AUX:TimeFormat AUX:ToRomanNumber AUX:ToRomanNumber.Ext AUX:ToSingleLine AUX:ToString.BigInt AUX:ToString.Date AUX:ToString.DateTime AUX:ToString.DecMoney AUX:ToString.DHP AUX:ToString.DMP AUX:ToString.Int AUX:ToString.Time AUX:ToString_2.DecMoney AUX:ToStringFS.BigInt AUX:ToStringFS.DecMoney AUX:ToStringFS.DHP AUX:ToStringFS.DMP AUX:ToStringFS.Int AUX:ToStringFS_2.DecMoney AUX:ToStringVS.DHP AUX:ToStringVS.DMP AUX:UseMainLanguageForFallback AUX:WeekDayName AUX:WeekDayName.Ext AUX:WeekDayNameById AUX:YMComparison AUX:YMComparison.ByDate AUX:ZeroLeftPadByWidthOfAbs (137 rows affected) Table functions ---------------------------------------------------------------------------------------------------- AUX:MonthList AUX:RomanNumbers AUX:WeekDayList (3 rows affected) Auxiliary tables ---------------------------------------------------------------------------------------------------- AUX::Month_list:En AUX::Month_list:Ru AUX::Roman_numbers AUX::WeekDay_list:En AUX::WeekDay_list:Ru (5 rows affected)