Как сохранить таблицы в sql

Организация хранилища файлов внутри базы данных 1C+MS-SQL

Довольно часто в различных вариантах учетных задач возникает проблема размещения различной информации (файлов) с привязкой их к базе данных. Например, построение на базе 1С системы учета документооборота с хранением файлов в формате Word , Excel . Если организовывать такое хранилище в виде каталогов файлов, то очень велика вероятность нарушения работы системы в результате ряда факторов, вызванных, как правило, неумелыми, либо наоборот, «слишком умелыми» действиями пользователя. Другими словами система хранения файлов в каталоге базы 1С , ввиду своей общедоступности является существенной прорехой в безопасности. Файлы легко переименовать, перезаписать, испортить или удалить.

Таким образом, мы постепенно подходим к мысли хранения файлов непосредственно в базе данных MS SQL . Остается вопрос, каким образом всё это можно организовать? В своей статье, я постараюсь дать как можно более подробный ответ на этот вопрос. Все исследования будут происходить на платформе MS SQL 2000 , далее просто SQL .

Итак, первый вопрос на повестке дня – создание таблицы для хранения файлов. Как известно, SQL позволяет создавать таблицы, содержащие поля с типом IMAGE (в переводе на русский «образ»). В поле такого типа мы и будем хранить свой файл. Для обеспечения уникальности данных и возможности в дальнейшем получения доступа к файлу нам необходимо создать колонку с типом int , которая у нас будет иметь свойство « Identity ». Это свойство обеспечит нам автоматическую нумерацию строк таблицы средствами SQL . Первоначальный номер у нас будет выставлен в « 1 », приращение номера будет тоже идти по « 1 ».

Второй вопрос на повестке дня, – в какой базе создавать таблицу? Требуется ли для ее хранения отдельная база данных, или можно воспользоваться уже существующей базой данной 1С . Как показали опыты, 1С при операциях, затрагивающих базу данных, таких как выгрузка, загрузка, реструктуризация, работает только со «своими» таблицами не затрагивая те, которые отсутствуют в ее файле DDS . Это дает нам возможность поместить нашу таблицу непосредственно в базе данных 1С . При этом, однако, следует иметь в виду, что раз 1С не трогает «не свои» таблицы, то при выгрузке данных наша таблица выгружаться не будет. Поэтому, для обеспечения резервного копирования или переноса данных придется использовать средства SQL . С другой стороны, если мы помещаем нашу таблицу в отдельную базу данных, то получаем более гибкие возможности работы, в частности легче можно будет наладить репликацию данной таблицы, что немаловажно. В моем примере таблица будет расположена непосредственно в базе данных 1С .

Теперь нам остается создать необходимый скрипт. Сразу определимся, что работать с хранимыми в базе данных файлами мы будем с использованием технологии ADO . Отсюда вытекает необходимость работы в нормальном рабочем режиме только в разделенном режиме работы 1С -Предприятия, так как в монопольном соединение ADO не сможет получить доступ к базе данных.

Сделаем так, чтобы процедура проверки наличия необходимой таблицы в базе данных запускалась в из процедуры ПриНачалеРаботыСистемы () когда режим работы пользователя разделенный. Текст процедуры можно посмотреть здесь.

Теперь у нас есть в базе необходимая таблица. Следующая задача — научиться записывать в нее необходимые данные. Для этого мы воспользуемся такой возможностью SQL-сервера , как « BULK » операции. Это операции манипулирования большими объемами данных, точнее сказать это операции обмена данными между SQL-сервером и файлом определенной структуры, который может содержать как одну строку данных для SQL-сервера , так и большое их количество. Определение структуры данных, содержащихся в файле, происходит с помощью специального файла-формы.

Итак, мы определлись, что будем записывать файл в базу данных с помощью операции « BULK INSERT ». В процедуре записи файла в базу данных, мы должны выполнить следующие действия:

• получить последний номер строки и сохранить его в переменной;
• создать и заполнить содержимым файл формы;
• выполнить архивацию исходного файла для уменьшения его объема на сервере (не обязательно — у меня делается ввиду большого объема данных);
• создать файл исходных данных;
• выполнить запрос на размещение на сервере;
• получить последний номер строки и сравнить его с сохраненным ранее значением;
• если номер увеличился на «1» — значит операция прошла успешно, иначе — была ошибка.

Важное примечание: так как результирующий запрос будет выполнять сервер, необходимо, чтобы файлы, используемые сервером были доступны для него. Соответственно, при формировании путей к файлам мы должны «смотреть на них» со стороны сервера. То есть, получив запрос на размещение файла «C:myfile.doc» сервер будет искать его именно на своем диске, а не на компьютере-клиенте. Лучше всего сохранять файлы в сетевых папках доступных серверу и сразу указывать сетевые пути .

Текст процедуры глобального модуля осуществляющей размещение файла в таблице базы данных SQL можно посмотреть здесь.

Следующий вопрос, который нам предстоит решить — каким образом можно извлечь файл из базы данных. Тут дело обстоит сложнее. Придется воспользоваться утилитой BCP , входящей в состав поставки MS SQL сервера . Эта утилита служит для пакетного обмена данными с SQL сервером . В принципе, мы могли бы воспользоваться этой утилитой и для загрузки файла на сервер, но, думается, что, чем меньше будет мелькать дополнительных окошек, тем лучше. Принцип использования утилиты такой же как у команды « BULK INSERT », с тем различием, что нам нужно получить данные с сервера, а не загрузить на него.

Таки образом список действий, который нам надо выполнить становится намного короче:

• создать файл формы;
• выполнить утилиту BCP с параметрами;
• разархивировать полученный файл (если использовалось архивирование).

Функцию глобального модуля, служащую для извлечения файла с сервера можно посмотреть здесь.

PostgreSQL | Как сохранить данные одного столбца таблицы на ПК?

Как сделать SQL-дамп данных одного столбца таблицы PostgreSQL на ПК в отдельный файл?

У нас есть таблица с несколькими столбцами и несколькими записями. Мы хотим сохранить на компьютер данные только из одного столбца этой таблицы, чтобы у нас получился файл с расширением «.sql«.

Этот файл нам нужен для хранения наиболее важной части информации из таблицы. Такой файл не хранит индексы таблицы и так как он текстовый, то его можно будет легко сжать архиватором и передать куда-то ещё. Как это сделать?

Вкратце план действий такой:

1. Создаём новую таблицу на основании запроса выборки (через интерфейс pgAdmin4 или через терминальный клиент psql)
2. Переходим к терминал операционной системы, где установлен PostgreSQL
3. Используем команду сохранения — pg_dump
4. Копируем файл выгрузки в нужное место
5. Удаляем созданную ранее таблицу

SQL-запрос на получение только одного столбца таблицы PostgreSQL

Такой командой мы получаем все столбцы таблицы:

SELECT * FROM filter.t_baddlevel2;

В моём случае нужно использовать уточнение для столбца « baddname » (хранит плохие мусорные домены второго уровня сети интернет):

SELECT baddname FROM filter.t_baddlevel2;

Результат вызова в веб-интерфейсе pgAdmin4:

Сперва мы должны знать, что именно мы хотим получить. Делаем запрос на выборку данных и убеждаемся, что всё правильно. У нас получилось 36188 записей имён доменов второго уровня, которые нужны для фильтрации новых адресов для базы данных.

Создание новой таблицы на основании запроса SELECT в PostgreSQL

Команда SELECT по факту возвращает нам новую таблицу, пусть даже с одним столбцом. Это значит, что мы можем из нашего запроса создать отдельную таблицу.

CREATE TABLE filter.t_baddname_temp AS SELECT baddname FROM filter.t_baddlevel2;

При помощи команды CREATE TABLE AS мы создаём новую таблицу с именем « t_baddname_temp » в схеме данных « filter «.

Проверяем, что все данные перенеслись корректно.

SELECT * FROM filter.t_baddname_temp;

Если в новой таблице будут не те данные, то эту таблицу-клон можно будет просто удалить.

DROP TABLE filter.t_baddname_temp;

Использование консольной утилиты pg_dump для сохранения данных новой таблицы из PostgreSQL на компьютер в операционную систему — в файл

К этому моменту у нас уже есть нужная таблица для сохранения. Теперь мы должны подключиться к удалённому хосту на котором обслуживается PostgreSQL. Для этого нужно знать логин и пароль для входа в ОС, а также логин и пароль пользователя, от имени которого будет выполняться сохранение данных из СУБД.

В общем нужно знать две учётные записи.

В терминале операционной системы (например, Debian) под правильным пользователем нужно ввести команду:

pg_dump -t 'filter.t_baddname_temp' sdb > filter.t_baddlevel2-2023-01-19.sql

После выполнения, файл будет сохранён в директорию « /var/lib/postgresql «.

Если вы захотите сменить путь сохранения, то имейте ввиду, что вашему пользователю PostgreSQL может быть отказано в доступе для сохранения в нужный вам путь в ОС. Болванка файла сможет появиться, но он будет пустой.

Просмотр файла для проверки сохранения

Открываем наш файл в любом текстовом редакторе и смотрим.

Начальная часть файла будет содержать важную для PostgreSQL информацию о состоянии таблицы. После этого последуют сами данные:

Теперь можно удалить временную таблицу.

Информационные ссылки

Официальный сайт PostgreSQL — https://www.postgresql.org

Графический веб-интерфейс pgAdmin для управления СУБД PostgreSQL — https://www.pgadmin.org

Синтаксис SQL-команды SELECT на русском языке — https://postgrespro.ru/docs/postgresql/15/sql-select

Синтаксис SQL-команды COPY на русском языке — https://postgrespro.ru/docs/postgresql/15/sql-copy

Операционная система Debian — https://www.debian.org

Вам также может понравиться

Опубликовано 21.12.2022

PostgreSQL | Как получить предыдущее значение существующего идентификатора?

Как получить предыдущую запись таблицы по предполагаемому первичному ключу? Суть проблемы заключается в том, что при использовании генераторов последовательностей SEQUENCE, можно получать […]

Опубликовано 13.07.2023

PostgreSQL | Как подменять NULL-значение при выборке данных через SELECT?

NULL-значения, которые база данных отдаёт на клиент могут не представлять никакой ценности для конечного пользователя. В большинстве случаев от них нужно избавляться. […]

Опубликовано 18.10.2022

PostgreSQL | Как получить одну случайную запись из таблицы?

Решение № 1 — Через сортировку всей таблицы и функцию RANDOM() в PostgreSQL Это самый простой в понимании вариант, но самый медленный. Команда […]

Опубликовано 01.10.2022

PostgreSQL | Как получить первые два символа из строки данных?

В PostgreSQL есть специальные встроенные строковые функции, при помощи которых можно работать со строковыми типами данных, например с типом text. Нужна для […]

Экспорт данных в базу данных SQL с помощью хранимой процедуры

Мне нужно записывать данные в базу данных SQL с использованием хранимой процедуры, поскольку мне нужно выполнять дополнительные проверки перед записью данных.

Что такое хранимая процедура:

Хранимая процедура (stored procedure или stored proc) — это функция на сервере, которая обеспечивает особый доступ для приложений к базе данных SQL. Такие процедуры выполняются и обрабатывают данные на стороне сервера.

Когда может потребовать хранимая процедура:

• Обработка данных на стороне сервера.
• Запись данных в несколько таблиц.
• Добавление первичных или дополнительных ключей к записываемым данным.
• Обновление или запись данных по дополнительным условиям.

Требования:

• Advanced OPC Data Logger Professional, Enterprise, или пробная версия;
• SQL Database Professional

Подразумевается что:

У вас уже настроен парсер и подготовлены переменные парсера для экспорта.

Для этого примера переменные парсера взяты из предыдущего примера.

Также вы можете просмотреть другие примеры парсинга данных (разные типы парсеров)

Решение:

Замечание: Вам потребуются знания языка программирования SQL для вашей базы данных, поскольку хранимая процедура это код программы для сервера.

Будем считать, что у вас есть доступ к базе данных и права для создания объектов в ней. Прежде всего, создайте таблицу в базе данных для сохранения входящих данных. Выберите существующую базу данных, или создайте новую для тестирования. Самый простой способ — использовать инструменты разработчика, предоставляемые производителем базы:

• SQL Server Management Studio для MSSQL.
• MySQL Workbench для MySQL.
• pgAdmin для PostgreSQL.

Обратитесь к документации на соответствующий игструмент для дополнительной информации.

Мы подготовили пример таблицы (рис. 1). Это прототип. Вы можете изменить его по своим требованиям или использовать вашу существующую таблицу.

MS SQL Server

CREATE TABLE dbo.[DATA] ( [ID] decimal(10,0) IDENTITY(1,1) NOT NULL, [DATE_TIME_STAMP] datetime NULL, [DATA_SOURCE_NAME] nvarchar(32) NULL, [DEVICE_ID] nvarchar(32) NULL, [V1] real NULL, [V2] real NULL, [V3] real NULL, CONSTRAINT [PK_DATA] PRIMARY KEY ([ID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 

MySQL

CREATE TABLE `DATA` ( `ID` int(11) NOT NULL auto_increment, `DATE_TIME_STAMP` DATETIME NULL, `DATA_SOURCE_NAME` VARCHAR(32) NULL, `DEVICE_ID` VARCHAR(32) NULL, `V1` DOUBLE NULL, `V2` DOUBLE NULL, `V3` DOUBLE NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB; 

PostgreSQL

CREATE TABLE "DATA" ( "ID" SERIAL, "DATE_TIME_STAMP" timestamp DEFAULT NULL, "DATA_SOURCE_NAME" varchar(32) DEFAULT NULL, "DEVICE_ID" varchar(32) DEFAULT NULL, "V1" real DEFAULT NULL, "V2" real DEFAULT NULL, "V3" real DEFAULT NULL, PRIMARY KEY (ID) ); 

Подготовьте хранимую процедуру в базе данных и выдайте права «Execute» для пользователя. Следующий пример демонстрирует хранимую процедуру, которая проверяет входящие данные и записывает их в таблицу.

MS SQL Server

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'data_insert') DROP PROCEDURE data_insert GO CREATE PROCEDURE [dbo].[data_insert] @DATE_TIME_STAMP datetime, @DATA_SOURCE_NAME nvarchar(32), @DEVICE_ID nvarchar(32), @V1 float, @V2 float, @V3 float WITH EXECUTE AS OWNER AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF (@V1 > 100) OR (@V2 > 100) OR (@V3 > 100) BEGIN RETURN; END; UPDATE DATA SET DATE_TIME_STAMP = @DATE_TIME_STAMP, V1 = @V1, V2 = @V2, V3 = @V3 WHERE DATA_SOURCE_NAME = @DATA_SOURCE_NAME AND DEVICE_ID = @DEVICE_ID; END; GO

MySQL

DROP PROCEDURE IF EXISTS `data_insert`; CREATE PROCEDURE `data_insert`( IN `DATE_TIME_STAMP` DATETIME, IN `DATA_SOURCE_NAME` VARCHAR(32), IN `DEVICE_ID` VARCHAR(32), IN `V1` DOUBLE, IN `V2` DOUBLE, IN `V3` DOUBLE) BEGIN SET @DATE_TIME_STAMP = DATE_TIME_STAMP; SET @DATA_SOURCE_NAME = DATA_SOURCE_NAME; SET @DEVICE_ID = DEVICE_ID; SET @V1 = V1; SET @V2 = V2; SET @V3 = V3; IF (@V1  

CREATE OR REPLACE FUNCTION data_insert( IN DATE_TIME_STAMP_1 timestamp, IN DATA_SOURCE_NAME_1 varchar(32), IN DEVICE_ID_1 varchar(32), IN V1_1 real, IN V2_1 real, IN V3_1 real) RETURNS void SECURITY DEFINER AS $$ BEGIN IF (V1_1 > 100) OR (V2_1 > 100) OR (V3_1 > 100) THEN RETURN; END IF; UPDATE DATA SET DATE_TIME_STAMP = DATE_TIME_STAMP_1, V1 = V1_1, V2 = V2_1, V3 = V3_1 WHERE DATA_SOURCE_NAME = DATA_SOURCE_NAME_1 AND DEVICE_ID = DEVICE_ID_1; END; $$ LANGUAGE plpgsql; -- split line 

exec data_insert :TIMESTAMP, :DS_NAME, :DEVICE_ID, :V1, :V2, :V3

call data_insert(:timestamp, :ds_name, :device_id, :v1, :v2, :v3);

select data_insert(:timestamp, :ds_name, :device_id, :v1, :v2, :v3);

CREATE PROCEDURE sp_Test AS SELECT * FROM (VALUES (1, 'Компьютер', 500), (2, 'Принтер', 300), (3, 'Монитор', 300) ) AS TmpTable (ProductId, ProductName, Summa) GO EXEC sp_Test

--Создаем временную таблицу CREATE TABLE #TmpTable (ProductId INT, ProductName VARCHAR (30), Summa MONEY); --Осуществляем вставку INSERT INTO #TmpTable EXEC sp_Test --Проверяем, т.е. делаем выборку из таблицы SELECT * FROM #TmpTable --Удаляем временную таблицу DROP TABLE #TmpTable

--Создаем связанный сервер EXEC sp_addlinkedserver @server = N'CurrentServer', @srvproduct = N'', @provider = N'SQLOLEDB', @datasrc = N'TESTSERVER\SQLEXPRESS' GO --Выполняем запрос с сохранением данных во временную таблицу SELECT * INTO #TmpTable FROM OPENQUERY(CurrentServer, 'SET FMTONLY OFF EXEC TestBase.dbo.sp_Test') --Проверяем полученные данные SELECT * FROM #TmpTable --Удаляем временную таблицу DROP TABLE #TmpTable

--Включаем параметр Ad Hoc Distributed Queries EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE GO --Выполняем запрос с сохранением данных во временную таблицу SELECT * INTO #TmpTable FROM OPENROWSET('SQLOLEDB', 'Server=TESTSERVER\SQLEXPRESS;Trusted_Connection=Yes;', 'SET FMTONLY OFF EXEC TestBase.dbo.sp_Test') --Проверяем полученные данные SELECT * FROM #TmpTable --Удаляем временную таблицу DROP TABLE #TmpTable