OneScript SQL - работа с СУБД

Программирование - Инструментарий

Внешняя компонента, реализующая возможность работы с базами данных из OneScript (https://infostart.ru/public/548028/ , https://infostart.ru/public/327581/). Поддерживаются базы: sqlite, MS SQL Server, MySQL, PostgreSQL.

Для коннектора sqlite позволяет работать с базой im memory.

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

Реализована возможность выполнения запросов DDL и DML.

Если не подходит сборка sqlite, выложенная в release - следует скачать подходящую с официального сайта sqlite

Примечание: УстановитьПараметр принимает типы: Строка, Число, Дата, Булево

Исходные коды проекта размещены на github: https://github.com/ret-Phoenix/oscript-sql

Примеры использования можно найти в тестах: https://github.com/ret-Phoenix/oscript-sql/tree/master/tests

 

Установка

Варианты:

  • opm install sql
  • https://github.com/ret-Phoenix/oscript-sql/releases - скачать последний релиз: opm install <путь к скаченному файлу>
  • https://github.com/ret-Phoenix/oscript-sql/releases - скачать последний релиз, распаковать содержимое папки content в oscript/kibs/sql

 

Пример работы с sqlite in memory

#Использовать sql

Соединение = Новый Соединение();
Соединение.ТипСУБД = Соединение.ТипыСУБД.sqlite;
Соединение.ИмяБазы = ":memory:";
Соединение.Открыть();

Запрос = Новый Запрос();
Запрос.УстановитьСоединение(Соединение);
Запрос.Текст = "Create table users (id integer, name text)";
Запрос.ВыполнитьКоманду();

Запрос.Текст = "insert into users (id, name) values(1, @name)";
Запрос.УстановитьПараметр("name", "Сергей");
Запрос.ВыполнитьКоманду();

Запрос2 = Новый Запрос();
Запрос2.УстановитьСоединение(Соединение);
Запрос2.Текст = "select * from users where id = @id";
Запрос2.УстановитьПараметр("id", 1);
ТЗ = Запрос2.Выполнить().Выгрузить();

Для каждого Стр Из ТЗ Цикл
    Сообщить("Имя: " + Стр.Name + " (" + Стр.id + ")")
КонецЦикла;

Пример работы с MS SQL Server

#Использовать sql

Процедура Тест_Должен_ДолженИзменитьСтроки() Экспорт
	
	СтрокаСоединения = ПолучитьТекстИзФайла("fixtures\ms-sql-server-con-str.txt");

	Соединение = Новый Соединение();
	Соединение.ТипСУБД = Соединение.ТипыСУБД.MSSQLServer;
	Соединение.СтрокаСоединения = СтрокаСоединения;
	Соединение.Открыть();

	ЗапросВставка = Новый Запрос();
	ЗапросВставка.УстановитьСоединение(Соединение);
	ЗапросВставка.Текст = "Create table #users (id integer, name varchar(50))";
	ЗапросВставка.ВыполнитьКоманду();

	ЗапросВставка.Текст = "insert into #users (id, name) values(1, @name)";
	ЗапросВставка.УстановитьПараметр("name", "Сергей");
	ЗапросВставка.ВыполнитьКоманду();

	ЗапросВставка.Текст = "update #users set name = @name";
	ЗапросВставка.УстановитьПараметр("name", "Сергей Александрович");
	Результат = ЗапросВставка.ВыполнитьКоманду();

	Соединение.Закрыть();

	Ожидаем.Что(Результат).Равно(1);

КонецПроцедуры

Пример работы с MySQL

Процедура Тест_Должен_ПроверитьГенерациюСтрокиСоединения() Экспорт
	
	Соединение = Новый Соединение();
	Соединение.ТипСУБД = Соединение.ТипыСУБД.MySQL;
	Соединение.Сервер = "localhost";
	Соединение.ИмяПользователя = "root";
	Соединение.ИмяБазы = "test";
	Соединение.Пароль = "testpassword";
	Соединение.Порт = 3306;

	Попытка
		/// Заведомо известно падение при открытии.
		/// Строка соединения генерируется только при открытии
		Соединение.Открыть();
	Исключение
		Ожидаем.Что(Соединение.СтрокаСоединения).Равно("server=localhost;user=root;password=testpassword;database=test;port=3306;");
	КонецПопытки;

КонецПроцедуры

Процедура Тест_Должен_ДолженПолучитьВыборку() Экспорт

	Соединение = Новый Соединение();
	Соединение.ТипСУБД = Соединение.ТипыСУБД.MySQL;
	Соединение.СтрокаСоединения = мСтрокаСоединения;
	Соединение.Открыть();

	ЗапросВставка = Новый Запрос();
	ЗапросВставка.УстановитьСоединение(Соединение);

	ЗапросВставка.Текст = "DROP TABLE IF EXISTS users";
	ЗапросВставка.ВыполнитьКоманду();

	ЗапросВставка.Текст = "Create table users (id integer, name varchar(50))";
	ЗапросВставка.ВыполнитьКоманду();

	ЗапросВставка.Текст = "insert into users (name) values(@name)";
	ЗапросВставка.УстановитьПараметр("name", "Сергей");
	ЗапросВставка.ВыполнитьКоманду();

	ЗапросВставка.Параметры.Очистить();
	ЗапросВставка.Текст = "select * from users";
	ТЗ = ЗапросВставка.Выполнить().Выгрузить();

	Ожидаем.Что(ТЗ.Количество()).Равно(1);

	Соединение.Закрыть();

КонецПроцедуры

API

Соединение / Connection

Соединение с БД. Используется для указания источника данных объекта Запрос.

Свойства

ТипыСУБД / DBTypes

Доступ: Чтение

Тип значения: ТипСУБД

Типы поддерживаемых СУБД

ТипСУБД / DBType

Доступ: Чтение/Запись

Тип значения: ТипСУБД

Тип подключенной СУБД

Порт / Port

Доступ: Чтение/Запись

Тип значения: Число

Порт подключения

Сервер / Server

Доступ: Чтение/Запись

Тип значения: Строка

Имя или IP сервера

ИмяБазы / DbName

Доступ: Чтение/Запись

Тип значения: Строка

Имя базы, в случае с SQLITE - путь к базе

ИмяПользователя / Login

Доступ: Чтение/Запись

Тип значения: Строка

Пользователь под которым происходит подключение. Если СУБД MS SQL и пользователь не указан - используется Windows авторизация.

Пароль / Password

Доступ: Чтение/Запись

Тип значения: Строка

Пароль пользователя

Открыто / IsOpen

Доступ: Чтение

Тип значения: ConnectionState

Статус соединения с БД

СтрокаСоединения / ConnectionString

Доступ: Чтение/Запись

Тип значения: Строка

Подготовленная строка соединения. В случае sqlite аналог ИмяБазы

Методы

Открыть / Open()

Открыть соединение с БД

Возвращаемое значение

Булево

Закрыть / Close()

Закрыть соединение с БД

СоздатьЗапрос / CreateQuery()

Создать запрос с установленным соединением

Возвращаемое значение

Запрос

ТипСУБД / DBType

Тип поддерживаемой СУБД

Свойства

sqlite / sqlite

Доступ: Чтение

MSSQLServer / MSSQLServer

Доступ: Чтение

Запрос / Query

Предназначен для выполнения запросов к базе данных.

Свойства

Параметры / Parameters

Доступ: Чтение

Текст / Text

Доступ: Чтение/Запись

Тип значения: Строка

Содержит исходный текст выполняемого запроса.

Таймаут / Timeout

Доступ: Чтение/Запись

Тип значения: Число

Время в секундах, в течение которого должно происходить ожидание выполнения команды. По умолчанию используется значение 30 секунд.

Методы

Выполнить / Execute()

Выполняет запрос к базе данных.

Возвращаемое значение

РезультатЗапроса

ВыполнитьКоманду / ExecuteCommand()

Выполняет запрос на модификацию к базе данных.

Возвращаемое значение

Число - Число обработанных строк.

УстановитьПараметр / SetParameter()

Устанавливает параметр запроса. Параметры доступны для обращения в тексте запроса. С помощью этого метода можно передавать переменные в запрос, например, для использования в условиях запроса. ВАЖНО: В запросе имя параметра указывается с использованием '@'.

Пример:

Запрос.Текст = "select * from mytable where category_id = @category_id";
Запрос.УстановитьПараметр("category_id", 1);

Параметры

  • ParametrName: Строка - Имя параметра

  • ParametrValue: Произвольный - Значение параметра

УстановитьСоединение / SetConnection()

Установка соединения с БД.

Параметры

  • connector: Соединение - объект соединение с БД

РезультатЗапроса / QueryResult

Содержит результат выполнения запроса. Предназначен для хранения и обработки полученных данных.

Методы

Выгрузить / Unload()

Создает таблицу значений и копирует в нее все записи набора.

Возвращаемое значение

ТаблицаЗначений

Скачать файлы

Наименование Файл Версия Размер
OneScript работа с СУБД:
.ospx 1,29Mb
12.09.17
10
.ospx 1.0.6464.20384 1,29Mb 10 Скачать

См. также

Комментарии
1. Евгений Мартыненков (JohnyDeath) 290 12.09.17 17:07 Сейчас в теме
2. Павел Хорев (w22u) 30 12.09.17 17:35 Сейчас в теме
Благодарю за Вашу статью с примерами!
3. Кирилл Власов (neikist) 12.09.17 17:42 Сейчас в теме
Потом над этой компонентой появится ORM со справочниками, документами и т.д. Допилят какую нибудь IDE для работы с этим ORM и OneScript... Хм...
4. Сергей (ret-Phoenix) 271 12.09.17 17:49 Сейчас в теме
(3) VSCode уже многое умеет ;)
8. Павел Одинцов (Darklight) 13.09.17 10:02 Сейчас в теме
5. Алексей Лустин (lustin) 870 12.09.17 18:19 Сейчас в теме
(0) скачал файл - лови СтартМаню ;-)

P.S. Хотя хаб пакетов никто не отменял

(3) Oscript не про это, а например про вот такое https://github.com/arkuznetsov/cpdb
VladimirL; +1 Ответить
6. Александр Мороз (aarh) 13.09.17 09:14 Сейчас в теме
Подскажите, пожалуйста, вот тут:

Исключение
Ожидаем.Что(Соединение.СтрокаСоединения).Равно("server=localhost;user...
КонецПопытки;

Ожидаем - это имя общего модуля, а Что - это функция возвращающая объект содержащий поле, например, значение и метод Равно?
7. Сергей (ret-Phoenix) 271 13.09.17 09:33 Сейчас в теме
(6) это метод asserts (подсистема тестирования). Приведенный код выдран из тестов компоненты.
artbear; aarh; +2 Ответить
10. Артур Аюханов (artbear) 894 13.09.17 12:41 Сейчас в теме
(6) Это т.н. "текучие" ассерты.
Вот статья на эту тему "Юнит-тесты, BDD и сила текучих утверждений (fluent assertions) в 1С"
https://habrahabr.ru/post/260013/
9. Сергей (ret-Phoenix) 271 13.09.17 10:10 Сейчас в теме
"Кроссплатформенный редактор исходного кода" от MS. Основной инструмент разработки на OneScript.
11. Александр Мороз (aarh) 13.09.17 14:43 Сейчас в теме
Кажется что это можно реализовать проще, я у себя просто сделал функцию в глобальном модуле
Требуется(Условие, СообщениеОбОшибке, ВызватьИсключение = Истина), где в условие передается уже конкретное условие для проверки

Например:
Требуется(А>=10);

Тогда не надо думать как реализовать все эти методы равно, не равно , списке и прочее..
12. Артур Аюханов (artbear) 894 13.09.17 15:54 Сейчас в теме
(11) С помощью различных утверждений, а не одного единственного, можно получить множество разных плюсов
+ код читать и понимать проще
+ находить проверки проще
+ можно группировать проверки
+ и т.п. и т.д.

но и Ваш подход имеет право на жизнь.
с одним "НО" - 3й параметр "ВызватьИсключение" не нужен, нужно всегда выбрасывать исключение, если условие не выполнено.

Это один из принципов защитного программирования, код писать и сопровождать становится намного проще
Serg O.; Redokov; +2 Ответить
13. Антон Беляев (anto-belyaev) 6 21.09.17 13:52 Сейчас в теме
Здравствуйте, прошу прощения, а можно уточнить, как подключить данный файл?
Либо как из исходников github скомпилировать dll?
OneScript, насколько я понял, работает же только с dll/os
14. Сергей (ret-Phoenix) 271 21.09.17 16:31 Сейчас в теме
1. opm install <путь к файлу>
2. opm install sql
3. ospx - zip архив. Положить в oscript\lib\sql из content
15. Михаил Петухов (oldfornit) 21.09.17 17:22 Сейчас в теме
(14) и не забыть про директиву #Использовать sql
16. Михаил Петухов (oldfornit) 25.09.17 15:19 Сейчас в теме
Наткнулись на ошибку:

Результат = Запрос.Выполнить().Unload();

Внешнее исключение (System.MissingMethodException): Метод не найден: "ScriptEngine.HostedScript.Library.ValueTable.ValueTableColumn ScriptEngine.HostedScript.Library.ValueTable.ValueTableColumnCollection.Add(System.String, ScriptEngine.Machine.IValue, System.String)".}
17. Сергей (ret-Phoenix) 271 25.09.17 21:05 Сейчас в теме
(16) какая версия oscript и библиотеки?
18. Михаил Петухов (oldfornit) 26.09.17 08:43 Сейчас в теме
(17) sql - 1.0.6277.22275, onescript - 1.0.17.95
19. Сергей (ret-Phoenix) 271 26.09.17 09:32 Сейчас в теме
(18) у меня на этих же релизах работает нормально. Если проблема не ушла - можно перейти в личку.
20. Сергей Щепинов (Chip2003) 25 26.09.17 16:45 Сейчас в теме
(19) у меня та же ошибка 1.0.17 и sql-1.0.6277.22275

{Модуль <string> / Ошибка в строке: 16 / Внешнее исключение (System.MissingMethodException): Метод не найден: "ScriptEngine.HostedScript.Library.ValueTable.ValueTableColumn ScriptEngine.HostedScript.Library.ValueTable.ValueTableColumnCollection.Add(System.String, ScriptEngine.Machine.IValue, System.String)".}

Запрос.Текст = "SELECT Name FROM v8users";

Результат = Запрос.Выполнить();
ТЗ = Результат.Выгрузить();
21. Сергей (ret-Phoenix) 271 26.09.17 16:51 Сейчас в теме
Обновитесь с github, постараюсь сегодня залить в хаб последнюю версию
22. Сергей Щепинов (Chip2003) 25 27.09.17 13:46 Сейчас в теме
(21) Обновился до 1.0.6464.20384 с гитхаба - ошибка пропала (на http://hub.oscript.io/download/sql/ - старая версия с ошибкой)
23. Сергей (ret-Phoenix) 271 27.09.17 14:34 Сейчас в теме
Обновил версию на хабе oscript. теперь установка через OPM получает последнюю версию.
24. Сергей Щепинов (Chip2003) 25 04.10.17 13:45 Сейчас в теме
Внешнее
исключение (System.Data.SqlClient.SqlException): Истекло время ожидания (Timeout
). Время ожидания истекло до завершения операции или сервер не отвечает.
Резервное копирование или восстановление прервано.
Контекст базы данных изменен на "master".
10 проц. обработано.
20 проц. обработано.
30 проц. обработано.
40 проц. обработано.}

Написал скрипт по бэкапу и восстановлению 6 баз по очереди.
4 базы нормально отрабатывает, а на 2-х вышибает с такой ошибкой.
В консоли SQL этот же скрипт нормально отрабатывает на этих базах.

процентов отработано всегда разное значение
иногда проходит до конца, а на восстановлении вышибает
Было что дошло до 100 и вышибло

Скрипт
25. Сергей (ret-Phoenix) 271 04.10.17 14:50 Сейчас в теме
В ошибке же написано: timeout
Установите его и все должно нормализоваться.
https://github.com/ret-Phoenix/oscript-sql/blob/master/docs/%D0%97%D0%B0%D0%BF%D1%80%D0%BE%D1%81.md#Таймаут--timeout
Если не ошибаюсь, 0 значит ждать пока не будет выполнено, без timeout.
Более детально можно посмотреть в MSDN, т.к. я реализовал обертку над родным .net connection.
26. Сергей Щепинов (Chip2003) 25 04.10.17 18:29 Сейчас в теме
(25) Спасибо, помогло. Единственное, что Таймаут смог установиться, только после метода УстановитьСоединение()

Еще вопрос. Когда пишу такого рода запрос:

Запрос.Текст = "
|ALT ER   DATABASE
|GO
|RESTORE DATABASE
|GO
|ALT ER   DATABASE...";

Пишет ошибка около GO
Обошел разделением на части. А можно все-таки одним написать?
27. Сергей (ret-Phoenix) 271 05.10.17 03:00 Сейчас в теме
(26) Как-то думаю возможно, Надо разбираться (копаться в доке от MS)
28. Any Body (AnyBody) 06.10.17 14:47 Сейчас в теме
У меня не срабатывает "УстановитьПараметр()"
ОТЛАДКА - установка: имябд=debug_ЛД_Бух_Р2
ОТЛАДКА - ПараметрЫ ЗАПРОСА
ОТЛАДКА - имябд=debug_ЛД_Бух_Р2
ОТЛАДКА - Текст запроса:
DBCC CHECKDB(N'@имябд') WITH NO_INFOMSGS

ОТЛАДКА - Тип СУБД:1

При выполнении ругается на @имябд
Что делать?
29. Any Body (AnyBody) 06.10.17 14:49 Сейчас в теме
(28)код:
Для каждого Параметр Из этотЗапрос.ПараметрыЗапроса Цикл
Запрос.УстановитьПараметр(Параметр.Ключ, Параметр.Значение);
Лог.Отладка("установка: "+Параметр.Ключ+ "="+ Параметр.Значение);
КонецЦикла;
Лог.Отладка("ПараметрЫ ЗАПРОСА");
Для каждого Параметр Из Запрос.Параметры Цикл
Лог.Отладка(Параметр.Ключ+ "="+ Параметр.Значение);
КонецЦикла;
Лог.Отладка("Текст запроса:"+Запрос.Текст);
32. Сергей (ret-Phoenix) 271 10.10.17 23:25 Сейчас в теме
(28) DBCC CHECKDB(@имябд) WITH NO_INFOMSGS - так пробовали?
30. Дмитрий Синичников (DmitrySinichnikov) 103 10.10.17 18:17 Сейчас в теме
Помогите плиз, чквствую туплю где то...
Не понимаю как подключить вашу библиотеку. Где dll?
Скачал исходники, там нет dll, скачал ospx, ток что с ним делать дальше понятия не имею. У гугла уже спрашивал. С OneScript начал работать недавно.
31. Виктория Дорохина (vikad) 72 10.10.17 19:14 Сейчас в теме
opm install sql

в командной строке
А потом в заголовке скрипта
#Использовать sql
33. Дмитрий Синичников (DmitrySinichnikov) 103 18.10.17 10:05 Сейчас в теме
(31) Спасибо)

П.С. Способы установки проглядел...
Оставьте свое сообщение