Версия для печати темы

Нажмите сюда для просмотра этой темы в обычном формате

MyDC.ru _ Дополнительные библиотеки для PtokaX _ Мануал По Работе С Sqlite3

Автор: Setuper 23.6.2008, 14:06

Взаимодействие SQLite и Lua.

1. Подключение библиотеки PXSQLite3.dll

Существует несколько способов подключения, однако я рассмотрю один из этих способов.
Итак, для подключения библиотеки PXSQLite3.7z ( 174.07 килобайт ) : 102
(для PtokaX 0.4.*.*), прежде всего, помещаем эту библиотеку в рабочую папку PtokaX (в папку, где находится файл PtokaX.exe). После этого нам нужен файл sqlite3.lua ( 17.95 килобайт ) : 48
. Этот файл содержит основные таблицы и функции для вызова функций из библиотеки PXSQLite3.dll, а также в этом файле как раз таки идет подключение данной библиотеки:

Код

local init, error = package.loadlib("PXSQLite3", "luaopen_pxsqlite3")

Скопировав файл sqlite.lua приступим к написанию скрипта для работы с sqlite:

В папке scripts создаем файл test.lua и пишем код:

Код

require("путь_к_файлу_sqlite3.lua"..".sqlite3")

local db = sqlite3.open_memory() -- открываем "временную базу данных"

db:exec[[ -- выполнение sql запроса
    CREATE TABLE test (id INTEGER PRIMARY KEY, content); -- создание таблицы test с первичным ключом id типа integer и некой переменной content

    INSERT INTO test VALUES (NULL, 'Hello World'); -- вставка в таблицу test данных
    INSERT INTO test VALUES (NULL, 'Hello Lua');
    INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

for row in db:rows("SELECT * FROM test") do
    Core.SendToAll(row.id.." -> "..row.content)
end

db:close() -- закрытие базы данных.

2. Открытие и закрытие базы данных.

Код

db = sqlite3.open("filename") --открытие файла базы данных "filename" (обычно файл с расширением "*.db3" - database sqlite3)

Код

db = sqlite3.open_memory() --открытие временной базы данных в оперативной памяти

Код

db:close() --метод закрытия базы данных

3. Выполнение sql запросов

Код

db:exec( "Запрос в двойных кавычках" ) --однострочный запрос
db:exec[[ Запросы без кавычек ]] --многострочный запрос

4. Обработка запросов, связанных с SELECT.

Код

-- Возвращает row как индексированный, целочисленный массив
for row in db:irows("SELECT * FROM test") do
    Core.SendToAll(row[1].." - "..row[2])
end

-- Возвращает row как индексированный массив с индексами равными названиям столбцов таблицы
for row in db:rows("SELECT * FROM test") do
    Core.SendToAll(row.id.." - "..row.data)
end

-- Возвращает данные каждого столбца отдельно
for id, data in db:cols("SELECT * FROM test") do
    Core.SendToAll(id.." - "..data)
end

5. Обработка однострочных или одноколоночных запросов.

Код

row = db:first_irow("SELECT count(*) FROM test")
Core.SendToAll(row[1])

row = db:first_row("SELECT count(*) AS count FROM test")
Core.SendToAll(row.count)

count = db:first_cols("SELECT count(*) FROM test")
Core.SendToAll(count)

6. Выполнение предзапросов.
Если нам необходимо несколько раз использовать один и тот же запрос, мы можем получить этот запрос у базы данных единожды. Это оптимизирует работу с базой данных:

Код

local stmt = db:prepare("SELECT * FROM test")

for row in stmt:irows() do
    Core.SendToAll(row[1].." - "..row[2])
end

for row in stmt:rows() do
    Core.SendToAll(row.id.." - "..row.data)
end

for id, data in stmt:cols() do
    Core.SendToAll(id.." - "..data)
end

Хочу заметить, что на самом деле та же самая функция db:rows(query) выполняется через предзапрос db:prepare(query):rows().

Также, используя данную конструкцию предзапросов, можно осуществлять проверку на правильность запроса.

Пример:

Код

function CheckSql(sRequest)
return db:prepare(sRequest) and true
end

Функция возвращает true в случае правильности запроса и nil в противном случае.

7. Использование анонимных параметров.
Функция stmt:bind(параметры) устанавливает анонимные параметры (? - обозначение анонимного параметра).

Код

local stmt = db:prepare[[
    INSERT INTO test VALUES (?, ?);
    INSERT INTO test VALUES (?, ?)
]]

function insert(id1, data1, id2, data2)
    stmt:bind(id1, data1, id2, data2)
    stmt:exec()
end

insert(1, "Hello World",   2, "Hello Lua")
insert(3, "Hello Sqlite3", 4, "Hello User")

stmt = db:prepare("SELECT data FROM test WHERE test.id = ?")

function get_data(id)
    stmt:bind(id)
    return stmt:first_cols()
end

Core.SendToAll(get_data(1))
Core.SendToAll(get_data(2))
Core.SendToAll(get_data(3))
Core.SendToAll(get_data(4))

8. Именованные параметры.

Код

db:exec("CREATE TABLE person_name (id, name)")
db:exec("CREATE TABLE person_email (id, email)")
db:exec("CREATE TABLE person_address (id, address)")

-- метка-заполнители '$' и ':' не в sql утверждениях выставляются или не выставляются по желанию программиста
local parameter_names = {":id", "$name", "address", "email"}

-- в sql утверждениях выставлять метка-заполнители необходимо обязательно!
local stmt = db:prepare(parameter_names, [[
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
]])

function insert(id, name, address, email)
    stmt:bind(id, name, address, email)
    stmt:exec()
end

insert(1, "Michael", "Germany", "mroth@nessie.de")
insert(2, "John",    "USA",     "john@usa.org")
insert(3, "Hans",    "France",  "hans@france.com")

9. Автоматические именованные параметры.

Код

local stmt = db:prepare[[
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
]]

function insert(id, name, address, email)
    stmt:bind(id, name, email, address)
    stmt:exec()
end

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

10. Параметры, передаваемые по средствам таблиц или метатаблиц.

Код

stmt = db:prepare[[
    INSERT INTO person_name VALUES (:id, :name);
    INSERT INTO person_email VALUES (:id, :email);
    INSERT INTO person_address VALUES (:id, :address);
]]

--первый способ
function insert(id, name, address, email)
    args = {}
    args.id = id
    args.name = name
    args.address = address
    args.email = args.email
    stmt:bind(args)
    stmt:exec()
end

--второй способ (упрощенный)
function insert2(id, name, address, email)
    stmt:bind{id=id, name=name, address=address, email=email}
    stmt:exec()
end

11. Запрос всех параметров.

Код

local stmt = db:prepare[[
    BEGIN TRANSACTION;
        INSERT INTO person_name VALUES (:id, :name);
        INSERT INTO person_email VALUES (:id, :email);
        INSERT INTO person_address VALUES (:id, :address);
    COMMIT
]]

local names = stmt:parameter_names()  -- занесение всех параметров в таблицу names

Core.SendToAll(table.getn(names))      -- "4"
Core.SendToAll(names[1])               -- "id"
Core.SendToAll(names[2])               -- "name"
Core.SendToAll(names[3])               -- "email"
Core.SendToAll(names[4])               -- "address"

Параметры упорядочиваются по мере их введения.

12. Функции, определяемые пользователем.

В SQLite3 вы можете определить свои собственные функции. Функцию написанную на Lua можно будет использовать в синтаксисе sql.

Код

function sql_add_ten(a)
return a + 10
end

db:set_function("add_ten", 1, sql_add_ten) --lua функции sql_add_ten мы даем в sql имя: add_ten (1 - число аргументов)

for id, add_ten, data in db:cols("SELECT id, add_ten(id), data FROM test") do
Core.SendToAll(id.." - "..add_ten.." - "..data)
end

13. Определение функции с произвольным числом аргументов.

Код

db:set_function("my_max", -1, math.max) -- (-1 - обозначение произвольного числа аргументов)

local max1 = db:first_cols("SELECT my_max(17, 7)")
local max2 = db:first_cols("SELECT my_max(1, 2, 3, 4, 5)")

Core.SendToAll(max1..", "..max2) -- 17, 5

14. Задержка в выполнении sql кода.

Код

-- Открываем базу данных
db = sqlite3.open("filename")

-- параметр - время (в милисекундах), после которого функция, ожидающая ответ на запрос, сообщит что он не был выполнен
db:set_busy_timeout(2 * 1000)

-- Выполнение sql кода
db:exec(...)

15. Установка обработчика.
Можно установить обработчика, который бы выполнял некий код в то время когда база данных занята.
Обработчик возвращает значения, если SQL пытается продолжить начатую трансакцию и трансакция была прервана с ошибкой.
Возвращается 0, false или nil для прерыания трансакции и некое значение для повторного обращения к базе данных.

Код

-- Открываем базу данных
db = sqlite3.open("filename")

-- Делаем 10 попыток обратиться к "занятой" (или недоступной) базе данных
function my_busy_handler(attempts_made)
    if attempts_made < 10 then
        return true
    else
        return false
    end
end

-- Установка обработчика
db:set_busy_handler(my_busy_handler)

-- Выполнение sql кода
db:exec(...)

16. Трассирующий обработчик.
Трассирующий обработчик нужен для отслеживания выполнения sql кода.
Выполняется каждый раз, когда выполняется функция db:exec() или db:prepare(). Обработчик получает один аргумент со строкой, которая была откомпилированна.

Код

function mytrace(sql_string)
Core.SendToAll("Sqlite3:"..sql_string)
end

db:set_trace_handler(mytrace)

http://mydc.ru/r/?http://sb-news.net/sqlite.php?page=21

Полезные линки.

http://mydc.ru/r/?http://sqlite.org/
http://mydc.ru/r/?http://sb-news.net/sqlite.php
http://mydc.ru/r/?http://www.freesource.info/wiki/SQLite
http://mydc.ru/r/?http://sqlitebrowser.sourceforge.net/
http://mydc.ru/r/?http://sqliteadmin.orbmu2k.de/

Автор: Setuper 27.8.2008, 11:03

Полезные функции sqlite:

Код

--функция проверяет правильность запроса
function CheckSql(sRequest)
  return db:prepare(sRequest) and true
end

--функция возвращает не пустую таблицу после запроса типа SELECT
function DoSelect(request)
    local t,stmt={},db:prepare(request)
    if stmt and stmt:rows() then
        for row in stmt:rows() do
            table.insert(t,row)
        end
        if next(t) then return t end
    end
end

--функция проверяет правильность и выполнимость запроса SELECT
function CheckSelect(request)
    local stmt=db:prepare(request)
    if stmt and stmt:first_row() then return true end
end

--функция возвращает однострочный запрос как таблицу
function DoFirstSelect(request)
    local stmt=db:prepare(request)
    if stmt and stmt:first_row() then
        return stmt:first_row()
    end
end

--функция выполняет любой запрос sqlite
function DoSql(request)
    local stmt=db:prepare(request)
    if stmt then
        stmt:exec()
        return true
    end
end

Автор: Nickolya 10.10.2008, 21:40

Немного подняли шума на форуме птоки, уже становится понятнее почему что как, кому интересно - http://mydc.ru/r/?http://board.ptokax.ch/index.php?topic=7456.0.

Автор: Wariner 10.10.2008, 22:27

Колюх, а можешь рассказать в двух словах что тебе отвечали?( твои сообщения я практически понял ибо не такой сложный англ )

Автор: Nickolya 11.10.2008, 0:05

Вот в 2 словах, основные моменты переводил полностью, но лучше читайте исходную переписку, мало ли чего я не так перевел или недописал

> Setuper: Я не могу создать больше 1 таблицы в этом скрипте!
> Я: да, такая же ***ня и у меня
> Я: вопрос еще актуален, может кто что сделать?
> какой-то zigzagkms: мне нужна либа для MySQL
> ATAG: всё работает на nix'е
> Я: да, там всё нормально, но на винде не пашет, такие-то и такие-то проблемы. И еще, почему птока не использует стандартныую библиотеку lua.dll, с ней бы мы могли использовать все расширения без изменений?
> Snooze: пользуйте таблицы, они работают быстрее
> PPK: нет стандартной lua.dll, луа растпространяется как исходный текст, все распространемые библиотеки не являются стандартными. Самая популярная - на луафордже, но она не может использоваться для птоки по многим причинам (меняются имена библиотек, не совместимы с 32-битным копилятором птоки, плохие настройки компилятора для 64-битной версии, что делает невозможным использование с 64-битной птокой...)
Еще причина почему изменено - в pxlua изменено управление памятью и исправлены некоторые баги (для примера, креш при делении на 0)
> Я: спасибо за объяснения, можешь объяснить как компилировать либы на 2 апи, с 1 вроде всё нормально, а именно момент с "name mangling".
> PPK (издевается, цк): апи птоки ничего не делает с библиотеками. _ было в начале функций т.к. они использовали "C calling convention", и это было изменено когда CrazyGuy сообщил что pxlua быстрее пашет с "fastcall calling convention" (using cpu registers for passing function parameters и на тестовом скрипте показала на 33% меньше времени цпу для завершения).
Вы можете использовать любой мой исходник чтобы посмотреть как компилировать библиотеки, 1 штуку вам надо поменять, и это "calling convention" в настройках компилятора.

Автор: Wariner 13.10.2008, 16:04

2Setuper: установил по твоему описанию Borland С++ Builder 6 но по посту выше следует поменять в опции calling convention параметр c C на fastcall, а там такого нет! Есть С, Pascal, Regicter(J) и Standard call. Это в этой версии нет такой опции или я что то не понял?

Автор: Setuper 13.10.2008, 16:35

2Wariner: Выше описана настройка компилятора для компиляции dll библиотеки для sqlite!

Опять же выше я полагаю описана настройка компилятора vs, потому как существуют исходники под vs)))))

Исходники с моими описаниями прилагаю (7zip файл), может найдутся знатоки программирования dll библиотек)))))

SQLite.7z ( 1.63 мегабайт ) : 34

Автор: Wariner 26.2.2009, 14:04

хм а почему не работает код:

Код

db = sqlite3.open(Core.GetPtokaXPath().."scripts/Protector/Logs/MainChat.db3")

т.е. не работает часть отвечающая за универсальность((((

+ как проверить наличие таблицы в бд? использовать проверку на правильность запроса?

Автор: Setuper 26.2.2009, 14:30

Всё работает! Проверку на существование таблицы делать не нужно, так как запрос CREATE TABLE будет выполняться только в случае отсутствия таблицы, не вызывая при этом ошибок.
Вот простейший пример по всем твоим вопросам:

Код

require"sqlite3"

local db = sqlite3.open(Core.GetPtokaXPath().."scripts/test.db3")

db:exec[[
  CREATE TABLE test (id INTEGER PRIMARY KEY, content);
  CREATE TABLE test1 (id INTEGER PRIMARY KEY, content);

  INSERT INTO test VALUES (NULL, 'Hello World');
  INSERT INTO test VALUES (NULL, 'Hello Lua');
  INSERT INTO test VALUES (NULL, 'Hello Sqlite3')
]]

function OnStartup()
  local t = DoSelect("SELECT * FROM test")
  for i,v in pairs(t) do
    for j,w in pairs(v) do
      Core.SendToAll(i.." - "..j.." - "..tostring(w))
    end
  end
end

function OnExit()
  db:close()
end

Функции для обработки запросов (в частности DoSelect) я выкладывал: http://mydc.ru/ipb.html?s=&showtopic=70&view=findpost&p=1995 Невнимательно просматриваешь тему!

Автор: Wariner 26.2.2009, 14:51

хм дело оказалось в путях. Толи из за того что присутствовали русские названия, толи слишком длинные и с пробелами.....

+ какие символы нельзя использовать в названии таблиц? не нашёл об этом инфы. Тестовым методом определил что нельзя только из цифр нельзя точки и тд

Автор: Setuper 26.2.2009, 15:06

Вообще говоря в sqlite можно писать так:

Код

CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, content);

однако данная конструкция не поддерживается в скомпилированной библиотеке (возможно из-за старой версии). Однако эта конструкция выполняется по умолчанию, хотя по синтаксису sqlite не должна выполняться по умолчанию, но тем лучше (не надо постоянно писать эти слова IF NOT EXISTS).

Правило формирования имени таблице такое же как и для любого ключевого слова в языке lua. Имя может состоять из комбинации букв, цифр и знака нижнего подчёркивания, при этом имя не должно начинять с цифры или с фразы sqlite_ - эта фраза зарезервирована под глобальные таблицы sqlite.

Возможно чуть позже я размещу тут на форуме подробный синтаксис команд sqlite, по причине того, что синтаксис на русском языке стал недоступен, из-за удаления его с одного из сайтов, ссылку на который я приводил выше.

Автор: Setuper 6.3.2009, 23:14

В соответствии с lua 5.1 поправлен метод bind в файле sqlite3.lua

sqlite3.lua ( 17.95 килобайт ) : 15

Автор: Wariner 6.3.2009, 23:20

а по русски
что это дало?

Автор: Setuper 6.3.2009, 23:40

Это дало выполнимость пунктов с 7 по 10 в мануале (в начале этой темы). До этого эти пунткы на работали на языке lua 5.1.
Работа проверяется опытным путём (по приведённым примерам).

Это связано с изменением принципа извлечения переменного числа параметров http://mydc.ru/r/?http://www.lua.ru/doc/7.1.html