SQL-FILE INSTALLATION (STEPS)

Corresponding web-page (SQL-file installation | Handicraft-SDK):
https://handicraft.remelias.ru/sdk/sql_file_install.html

Installation of SQL-file environment is not simple nor very complex. The software is installed by hands, it is needed to work a little.
Download pages (this domain):
— https://handicraft.remelias.ru/ (Handicraft-CODE home: Handicraft Toolkit, Handicraft-SDK);
— https://handicraft.remelias.ru/sdk/handicraft_sdk.html (SDK central page: Handicraft-SDK).
Files for download (one of):
— "Handicraft_Toolkit_YYYYMMDDx.7z" (without password; recommended full download);
— "HandicraftSDK YYYY-MM-DDx.7z" (password: "qwerty"; SDK-only download).
(It is recommended to install complete "Handicraft Toolkit", compound package: "Handicraft-SDK", "BookRegistry app.", "TEST (DB).")

INSTALLATION STEPS, BASIC AND DATABASE (SQL-FILE) >>

List of the manipulations that are needed to install the SQL-file programming environment (STEPS 11+2):

----------------------------------------------------------------------------------------------------
*** BASIC STEPS (CLIENT ENVIRONMENT): ***

== 1."SDK downloading and extraction (from the archive)": ==

Download one of SDK packages from Handicraft-CODE public pages (see above).

Copy either "HandicraftSDK" folder or "Handicraft_Toolkit_YYYYMMDDx" folder (SDK + additional examples) to user's machine
(it is desirable to be situated not far from volume root). Path to the SDK must not to contain exclamation sign character '!';
the same is actual for the path(s) to user SQL-project(s)!


== 2."Making $-commands working (i.e. accessible through PATH variable)": ==

Register path to "CMD-utilities" folder — include it in "PATH" variable of system or user environment
(to make working the following utilities of the console mode: $SQLTRANS, $SQLUPLOAD, $SQLCON, $SQL, $SQLTOCSVEXP, $CONFIRMPAUSE, $TYPEINCOLOR and others).


== 3."Specifying SQLAUX_ROOT and SSMS_ExePath environment vars": ==

Specify in the system "SQLAUX_ROOT" variable (system / user environment) so that it refers to the following path:
"…\SQL\SQLAUX" (or similar location) for the sake of availability of "SQLAUX" scripting library.
Also it is very useful to define "SSMS_ExePath" variable (to something like the following):
"C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe".


== 4."Installation of CmdCpAuto plugin, from Utilities folder to Far Manager": ==

Copy "CmdCpAuto" plugin folder (it is for the Far Manager)
"…\HandicraftSDK\Utilities\Far plugins\CmdCpAuto plugin, build 100 (Far v3)\x64\CmdCpAuto" (or "…\x86\CmdCpAuto")
into "%FARHOME%\Plugins\" folder.
("Far Manager" folder is located under the "Program Files" folder in Windows OS.)


== 5."Installing elementary LUA-macros to Far Manager (from “CMD-utilities\Shell\Integration” folder)": ==

Copy "*.lua" files (all simple macros or selectively) to the Far Manager into "%FARPROFILE%\Macros\scripts\" folder.
The LUA-macros are taken from "…\CMD-utilities\Shell\Integration" folder.


== 6."Associating .SQL extension with SQL-translation command ($SQLTRANS), in Far Manager etc.": ==

Configure file association for ".sql" extension according to instructions from the "…\CMD-utilities\Shell\Integration" folder
(please read "*.txt" files).
You have to perform some other actions at this step in concordance with the instructions.
For instance — to make adoption of liked menu items to user system (Far Manager) as suggested in "FarMenu.ini" sample file
(located in "Integration" folder).
Also there you’ll find an information how to configure for SQL-translation (i.e. processing performed by $SQLTRANS-utility)
one of the best autonomous editors of program source text, it is about GUI-editor (see the "Integration" folder for details).

See following instructions and illustration:
— https://handicraft.remelias.ru/sdk/sql_file_install.html#sqltrans_integration_far_txt_image (first or two illustration pictures)
  or "HandicraftSDK\CMD-utilities\Shell\Integration\!Integration of $SqlTrans with FAR Manager.txt" (local file);
— https://handicraft.remelias.ru/sdk/sql_file_install.html#sqltrans_integration_far_image (the second picture).
At the second picture it is illustrated the way of configuration of SQL-script association (.SQL) in the Far Manager.


== 7."In SQL Server Management Studio — switching SQLCMD-mode on": ==

Inside SQL Server Management Studio (SSMS IDE) you have to switch on the following important setting:
Main menu >> "Tools" :: "Options" :: "Query Execution" :: "SQL Server" :: "General" ::
"By default, open new queries in SQLCMD mode" (switch on the checkbox).


== 8."Adjustment and fine tuning of Far Manager appearance by shortcut correction (.LNK)": ==

It is desirable to achieve precise accurate adjustment of Far Manager window by fitting in into the work area of your desktop.
Well fitting console does not have vertical (and horizontal) scroll bar when its window is maximized.
Good-looking pseudographic frames in Far Manager panels (composed by line drawing characters) are normally without gaps.
(An insignificant clearance in text graphics is possible for output from CMD batches / utilities.)
It is effective to "play" with parameters of the program shortcut (".LNK" binary file configured by Windows Explorer) to reach successful view.

Recommended font family name is "Consolas", for the sake of compatibility with Unicode, good-looking appearance on high DPI display etc.
(Further there are settings acting on the author’s computer.)
"Font" :: "Size": 18, not bold (this is for 1920×1080 display resolution combined with scale value 125%).
"Layout" :: "Screen Buffer Size": 158×43. "Wrap text output on resize": ON.
"Layout" :: "Window Size": 158×43. "Layout" :: "Window Position": -4×-4. "Let system position window": ON.
(Some of your parameters will probably be another.)

Also for startup from anywhere by hotkey, define the following for your Far Manager shortcut (.LNK) located on your desktop:
"Shortcut" :: "Shortcut key" :: "Ctrl + Alt + F12" (or similar, for "Far Manager 3 x64.lnk").


== 9."Making MSSQL Command Line Utilities runnable from command-line (SQLCMD, BCP)": ==

It is important and this is implied that MSSQL client utilities are installed in the system (SQLCMD, BCP)
and the corresponding location is referenced by the PATH environment variable, as its part.
For instance: "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn"
(one of the PATH components delimited from the others by column sign ';').


== 10."Configuring Windows Console a bit simpler, switching off QuickEdit parameter in system registry": ==

More over, it is suggested to neutralize in the system possibility of quick text selection in the console (by the mouse pointer).
It is needed to avoid unintentional making of such selection, that in its turn causes undesirable cessation of program text output to the console.

In Windows Registry (via reg-file or manually by the Registry Editor):

[HKEY_CURRENT_USER\Console]
"QuickEdit"=dword:00000000

Auxiliary reg-file: "…\HandicraftSDK\Utilities\System Configuration\Console\QuickEdit=0.HKCU.reg".
"…\HandicraftSDK\Utilities\System Configuration" folder contains a group of registry setting helpers.


== 11."Possible problems with locale and system date-time format": ==

WARNING!

SQL-file was tested under the following system locales (default code pages for single-byte apps):
1. English (USA), i.e. CP-1252 (Latin alphabet) / OEM: CP437;
2. Russian (Russia), i.e. CP-1251 (Cyrillic script) / OEM: CP866.
For other system languages (default locales) SQL-translation date-time stamp may be formed with some mistakes, probably in its date part.
(Sometimes it may be non-critical, because time order inside the day is then preserved.)

For locales different from the two above, following environment variables may be helpful:
YYYY_START_IN_DATE=<Zero-based position> (optional)
MM_START_IN_DATE=<Zero-based position>
DD_START_IN_DATE=<Zero-based position>

Zero-based positions have to be specified through the variables, according to corresponding user locale, concerning the DATE environment
variable (CMD). Also they are needed to account any nonstandard settings of date-time format in your system, concerning the DATE variable.
(This problem is connected to limitations of legacy CMD-processor, which is used for implementation of $SQLTRANS command.)


(A restart of the Far Manager will be required to reflect the changes of the above steps!)

----------------------------------------------------------------------------------------------------
*** DATABASE STEPS (SQLAUX LIBRARY IMPORT): ***

== DB-1."Creating SQLAUX DB-objects in a target database (in developer’s environment)": ==
[[
Performing this step implies that SQL-file program environment is installed, that is: above described basic steps
(all or those that are required for you) have been done, and you have convinced that $SQLTRANS-command is accessible
and SQL-translations really do work.

In Far Manager or another file management program (Windows Explorer etc.) open (pass into) directory with SQLAUX scripting library sources:
"HandicraftSDK\SQL\SQLAUX\Source". Look into "#programmatics.txt" file, there are numerous records there, which correspond to SQLAUX-library
last compilation at author’s machine (to local DB). Open "$sql_settings.cmd" (predefined settings file) in embedded Far Manager editor
(normally by F4-key). It is very recommended your OEM encoding have to be switched on ("|OEM  |" in editor top line),
it is done automatically if "CmdCpAuto"-plugin is installed.
Correct following two lines with requisites addressing your target DB:

>> set $sql_server=(local)
>> set $sql_database=TEST

You should write here your appropriate values or leave as is, if you are really targeted local database named "TEST".
Also there is a way to relay SQL-password to $SQLTRANS. This is done through assignment to "$sql_password_var" property,
which is a “name of environment variable (container) for SQL-password”
(see https://handicraft.remelias.ru/sdk/sql_tools.html#sqltrans_syntax_print_img, invoke it without parameters).

Try to execute "list_programmatics.sql" (this requires configured association for .SQL estension),
it should display empty contents for your database ("Stored procedures", "Scalar functions", "Table functions", "Auxiliary tables").
In Far Manager, "<Ctrl>+<PageDown>" combination is recommended for this quick report (if LUA-macros for shell are installed),
in order to see SQL-translation result in standalone console with vertical scroll-bar
(HOWEVER NO PRELIMINARY SAFETY CONFIRMATION IS PROVIDED FOR SQL-EXECUTION IN THIS WAY).
Then try "list_programmatics.cmd" command. This have only to recreate your "#programmatics.txt" (with empty lists of DB-objects).
Then proceed to SQLAUX DB-objects translation (re-creation).

SQLAUX library full re-translation is performed by "translate_programmatics.cmd" complex command.
In case of success it creates all the objects in target database ("AUX:…", "AUX::…") and produces corresponding full listings
in "#programmatics.txt" report. Also (as specified in "$sql_settings.cmd": "rem goto SKIP_SOURCE_GENERATION" etc.),
the compilation is configured to write into $GENERATED and $OUTPUT subfolders to save result of strong preprocessing,
that is switched on in these settings (not so as by default).

As opposed to library creation, "!drop_programmatics.cmd"-command is intended to destroy all SQLAUX objects in target database,
with accompanying deletion of "#programmatics.txt" report.
]]

== DB-1x."Importing SQLAUX scripting library via “sqlaux_library.sql” (single script)": ==
[[
This step is an alternative to the previous one ("DB-1"), it doesn’t require deployed SQL-file environment (basic steps above)
at computer (clinet of SQL Server), for advanced programming in parameterized Transact-SQL with files.
(It is not reasonable to require SQL-file environment there, to where developer do not has direct access, or by some other reasons.
Nevertheless DB-objects which are produced with help of SQL-file technology may to function somehow, in such remote DB.)

Launch SQL Server Management Studio. Connect to corresponding SQL-server. Then create new query tab, select target database in it
(for SQLAUX library import). Find and open "HandicraftSDK\SQL\SQLAUX\Source\$OUTPUT\sqlaux_library.sql" file, load it into the query.
It is not needed to have SQLCMD-mode on in this case. However it is admissible to have this setting active
("By default, open new queries in SQLCMD mode"), because SQLAUX programmatics are not readonly:
"sqlfile_readonly_aux = 0 -- allow 3rd party translation (to be able correct the objects from SSMS)"
(it is set to 0 in SQLAUX "$sql_settings.cmd", so no "$(SQLFILE_READONLY)"-insertion is made into SQLAUX queries and scripts).

Simply execute this SQLAUX import script, whose content is just loaded (from corresponding "sqlaux_library.sql").
This will destroy and then create all library DB-objects ("AUX:…", "AUX::…"), in database you’ve selected as target.

As opposed to library creation, "00_drop_programmatics.sql"-script in "…\SQLAUX\Source\$OUTPUT"-folder is intended
to destroy all SQLAUX objects in target database.
(Other SQLAUX re-creation scripts from this folder are: "01_auxiliary_tables.sql", "02_functions.sql", "03_procedures.sql".)
]]
----------------------------------------------------------------------------------------------------

See also: https://handicraft.remelias.ru/sdk/sql_file_install.html (SQL-file installation | Handicraft-SDK)

— — —