Category Archives: MySQL

MySQL. CloneDB

Posted on 07.09.2018 by Stanislav_Panteleev

Скачать утилиты для MySQL Пример использования

mysqldbcopy --source=root:masterkey@localhost:3306 --destination=root:masterkey@localhost:3306 sewworld_db:sewworld_db2

1	mysqldbcopy --source=root:masterkey@localhost:3306 --destination=root:masterkey@localhost:3306 sewworld_db:sewworld_db2

Базы, в которую клонируем не должно быть. Утилита сама ее создаст.

Posted in MySQL | Comments Off

MySQL. Triggers don’t fire on Cascade Delete

Posted on 28.01.2018 by Stanislav_Panteleev

This is fact. No workaround possible with MySQL Syntax. Only on client. The end.

Posted in MySQL | Comments Off

MySQL. Cравнение даты (datetime) через оператор BETWEEN

Posted on 06.01.2018 by Stanislav_Panteleev

Источник Оператор BETWEEN идеально подходит для сравнения диапазона между датами (datetime). Но тут есть подводные камни. Например, есть задача — выбрать данные из таблицы за некоторый промежуток времени (с ‘2008-08-14’ по ‘2008-08-23’). Рекомендации: 1. Выполняя любые сравнения, приводить все данные … Continue reading →

Posted in MySQL | Comments Off

MySQL.Example with Cursor

Posted on 06.12.2017 by Stanislav_Panteleev

from project

CREATE DEFINER=`root`@`localhost` FUNCTION `isCorrect_ManyCorrectAnswers`(aEid integer,aTid integer, aUid integer, aQid integer) RETURNS tinyint(1)
BEGIN
declare r tinyint(1) default(0);
declare answersID integer;
Declare done tinyint(1) default(0);
# is correct if all answers correct
# isCorrect  if at least one answer correct
declare isCorrectIfAllAnswersCorrect tinyint(1);
SELECT 
    isCorrectIfAllAnswersCorrect
FROM
    coffeetest_db.testquestions
WHERE
    id = aQid INTO isCorrectIfAllAnswersCorrect;
if isCorrectIfAllAnswersCorrect then 
begin
# check if all answers correct
DECLARE answersCursor CURSOR FOR 
SELECT 
iftable.testAnswers_id as respondentAnswer
FROM 
coffeetest_db.answersonquestions aq,
testQuestions tq,
ifoneormanycorrectanswers iftable
where 
aq.testQuestions_id=tq.id
and iftable.answersonquestions_id=aq.id
and aq.events_id=aEid
and aq.users_id=aUid
and aq.tests_id=aTid
and aq.testQuestions_id=aQid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;                         
  set r=1; # assume all variants where checked correct 
  Open answersCursor;  
  whileLoop:while done=false do    
  fetch answersCursor into answersId;
  # do something
  if isAnswerCorrect(aQid,answersId)=0 then
   begin
      set r=0;
      leave whileLoop;
   end;
   end if;
  end while;  
  Close answersCursor;

end;

else

begin
# check if at least one answer correct
DECLARE answersCursor CURSOR FOR 
SELECT 
iftable.testAnswers_id as respondentAnswer
FROM 
coffeetest_db.answersonquestions aq,
testQuestions tq,
ifoneormanycorrectanswers iftable
where 
aq.testQuestions_id=tq.id
and iftable.answersonquestions_id=aq.id
and aq.events_id=aEid
and aq.users_id=aUid
and aq.tests_id=aTid
and aq.testQuestions_id=aQid;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;                         
  Open answersCursor;
  whileLoop:while done=false do  
  fetch answersCursor into answersId;
  # do something
  if isAnswerCorrect(aQid,answersId)=1 then
   begin
      set r=1;
      leave whileLoop;
   end;
   end if;
  end while;  
  Close answersCursor;

end;

end if;

RETURN r;
END

CREATE DEFINER=`root`@`localhost` FUNCTION `isCorrect_ManyCorrectAnswers`(aEid integer,aTid integer, aUid integer, aQid integer) RETURNS tinyint(1)

BEGIN

declare r tinyint(1) default(0);

declare answersID integer;

Declare done tinyint(1) default(0);

# is correct if all answers correct

# isCorrect if at least one answer correct

declare isCorrectIfAllAnswersCorrect tinyint(1);

SELECT

isCorrectIfAllAnswersCorrect

FROM

coffeetest_db.testquestions

WHERE

id = aQid INTO isCorrectIfAllAnswersCorrect;

if isCorrectIfAllAnswersCorrect then

begin

# check if all answers correct

DECLARE answersCursor CURSOR FOR

SELECT

iftable.testAnswers_id as respondentAnswer

FROM

coffeetest_db.answersonquestions aq,

testQuestions tq,

ifoneormanycorrectanswers iftable

where

aq.testQuestions_id=tq.id

and iftable.answersonquestions_id=aq.id

and aq.events_id=aEid

and aq.users_id=aUid

and aq.tests_id=aTid

and aq.testQuestions_id=aQid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

set r=1; # assume all variants where checked correct

Open answersCursor;

whileLoop:while done=false do

fetch answersCursor into answersId;

# do something

if isAnswerCorrect(aQid,answersId)=0 then

begin

set r=0;

leave whileLoop;

end;

end if;

end while;

Close answersCursor;

end;

else

begin

# check if at least one answer correct

DECLARE answersCursor CURSOR FOR

SELECT

iftable.testAnswers_id as respondentAnswer

FROM

coffeetest_db.answersonquestions aq,

testQuestions tq,

ifoneormanycorrectanswers iftable

where

aq.testQuestions_id=tq.id

and iftable.answersonquestions_id=aq.id

and aq.events_id=aEid

and aq.users_id=aUid

and aq.tests_id=aTid

and aq.testQuestions_id=aQid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

Open answersCursor;

whileLoop:while done=false do

fetch answersCursor into answersId;

# do something

if isAnswerCorrect(aQid,answersId)=1 then

begin

set r=1;

leave whileLoop;

end;

end if;

end while;

Close answersCursor;

end;

end if;

RETURN r;

END

Posted in MySQL | Comments Off

Delphi. VCL и не только. Делаем фильтр. Почти быстро и просто

Posted on 04.07.2017 by Stanislav_Panteleev

Выглядеть он будет у нас вот так… Можно добавлять бесконечное число параметров для фильтрования. Это начальная версия, я расскажу идею, там ещё много всего надо доработать, но как идея вполне рабочая.

Posted in Delphi, MySQL | Comments Off

MySQL. Добавляем хранимые процедуры и функции ( “рутину” ) в модель и физическую базу данных

Posted on 22.05.2017 by Stanislav_Panteleev

Добрый день, сегодня простая и интересная тема. Добавление хранимых процедур и функций, или как их называют в документации MySQL “рутин” в модель и физическую базу данных. Добавление хранимой функции в физическую базу данных Здесь всё просто. Открываем WorkBench Добавляем хранимую … Continue reading →

Posted in MySQL | Comments Off

Delphi.MySQL.Мои компоненты.TPSDBTreeView

Posted on 13.05.2017 by Stanislav_Panteleev

Вот что у нас получится. Дерево работает над базой MySQL. В примере 2 дерева, работающие над одной таблицей базы. 3-й ряд кнопок сам определяет какое дерево активно. Исходники прилагаю. Типовая таблица для дерева. Это минимальный набор полей. Всё остальное можно … Continue reading →

Posted in Delphi, MySQL, Компонентостроение | Comments Off

Delphi. MySQL. InsertSelect всех полей для реализации Copy Paste

Posted on 13.05.2017 by Stanislav_Panteleev

Делал для своего компонента TPSDBTreeView функцию “вырезать, копировать, вставить” на основе InsertSelect. Проблема в том, что если пытаться копировать все поля, скажем так

insert into users SELECT * FROM coffeetest_db.users;

1	insert into users SELECT * FROM coffeetest_db.users;

то получим

10:35:43	insert into users SELECT * FROM coffeetest_db.users	
Error Code: 1062. Duplicate entry '1025' for key 'PRIMARY'	0.016 sec

1 2	10:35:43 insert into users SELECT * FROM coffeetest_db.users Error Code: 1062. Duplicate entry '1025' for key 'PRIMARY' 0.016 sec

Что же делать в таком случае? Нашёл такой выход Вот пример из проекта … Continue reading →

Posted in MySQL, Без рубрики | Comments Off

MySQL. Вход по ролям на уровне базы

Posted on 07.05.2017 by Stanislav_Panteleev

Напишем простейшую функцию, которая позволит входить по ролям на уровне базы. Допустим у нас есть таблицы Следующая функция позволит осуществлять вход по ролям. На выходе мы получим название роли. И в клиенте можем загружать тот или иной кабинет.

CREATE DEFINER=`root`@`localhost` FUNCTION `getUserRole`(luser_name VARCHAR(45),lpassword VARCHAR(45)) RETURNS varchar(45) CHARSET utf8
BEGIN
 
-- defining local vars
DECLARE aid_user INT; 
DECLARE roleName VARCHAR(45); 
DECLARE apassword VARCHAR(45) default 0;
 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET aid_user = 0;
 
-- selecting for login and md5 password
select id,password into aid_user,apassword 
from users where login=luser_name and password=md5(lpassword);
 
-- check number of selected rows
SELECT FOUND_ROWS() as num_of_rows_found into @RowCount;
if @RowCount=0 then begin set aid_user = 0; set roleName='wrong login or password';end; 

else begin
select name into roleName 
from roles where id=aid_user;
end;

end if;
 
-- if md5(lpassword)<>apassword then set aid_user = 0;  end if;
-- if lpassword<>apassword then set aid_user = 0;  end if;
 
 
 
RETURN roleName;
 
END

CREATE DEFINER=`root`@`localhost` FUNCTION `getUserRole`(luser_name VARCHAR(45),lpassword VARCHAR(45)) RETURNS varchar(45) CHARSET utf8

BEGIN

-- defining local vars

DECLARE aid_user INT;

DECLARE roleName VARCHAR(45);

DECLARE apassword VARCHAR(45) default 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET aid_user = 0;

-- selecting for login and md5 password

select id,password into aid_user,apassword

from users where login=luser_name and password=md5(lpassword);

-- check number of selected rows

SELECT FOUND_ROWS() as num_of_rows_found into @RowCount;

if @RowCount=0 then begin set aid_user = 0; set roleName='wrong login or password';end;

else begin

select name into roleName

from roles where id=aid_user;

end;

end if;

-- if md5(lpassword)<>apassword then set aid_user = 0; end if;

-- if lpassword<>apassword then set aid_user = 0; end if;

RETURN roleName;

END

Тестируем

Posted in MySQL | Comments Off

MySQL. Пример авторизации на языке базы (хранимая функция). И вызов из Delphi

Posted on 06.05.2017 by Stanislav_Panteleev

Пример №1 Простая и надежная функция проверки логина, пароля, роли, с результатом tinyint(1)

CREATE DEFINER=`root`@`localhost` FUNCTION `loginpasswordrolecheck2`(ALogin VARCHAR(45), APassword VARCHAR(45), ARoleName VARCHAR(45)) RETURNS tinyint(1)
BEGIN

-- DECLARE foundRows INT; -- default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION return 0; 

-- if count=1 then OK
select count(*) from users 
where login=ALogin and password=APassword and roles_id=(select id from roles where name=ARoleName)
into @RowCount;

if @RowCount=1 then return 1; else return 0;  end if; 

 
END

CREATE DEFINER=`root`@`localhost` FUNCTION `loginpasswordrolecheck2`(ALogin VARCHAR(45), APassword VARCHAR(45), ARoleName VARCHAR(45)) RETURNS tinyint(1)

BEGIN

-- DECLARE foundRows INT; -- default 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION return 0;

-- if count=1 then OK

select count(*) from users

where login=ALogin and password=APassword and roles_id=(select id from roles where name=ARoleName)

into @RowCount;

if @RowCount=1 then return 1; else return 0; end if;

END

C результатом VarChar

CREATE DEFINER=`root`@`localhost` FUNCTION `loginpasswordrolecheck`(ALogin VARCHAR(45), APassword VARCHAR(45), ARoleName VARCHAR(45)) RETURNS varchar(45) CHARSET utf8
BEGIN

-- DECLARE foundRows INT; -- default 0;
-- DECLARE roleName varchar(45); -- default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION return 0; 

-- if count=1 then OK
select count(*) from users 
where login=ALogin and password=APassword and roles_id=(select id from roles where name=ARoleName)
into @RowCount;

if @RowCount=1 then return '1'; else return '0';  end if; 

 
END

CREATE DEFINER=`root`@`localhost` FUNCTION `loginpasswordrolecheck`(ALogin VARCHAR(45), APassword VARCHAR(45), ARoleName VARCHAR(45)) RETURNS varchar(45) CHARSET utf8

BEGIN

-- DECLARE foundRows INT; -- default 0;

-- DECLARE roleName varchar(45); -- default 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION return 0;

-- if count=1 then OK

select count(*) from users

where login=ALogin and password=APassword and roles_id=(select id from roles where name=ARoleName)

into @RowCount;

if @RowCount=1 then return '1'; else return '0'; end if;

END

Вызов из Delphi

CREATE DEFINER=`root`@`localhost` FUNCTION `loginpasswordrolecheck`(ALogin VARCHAR(45), APassword VARCHAR(45), ARoleName VARCHAR(45)) RETURNS varchar(45) CHARSET utf8
BEGIN

-- DECLARE foundRows INT; -- default 0;
-- DECLARE roleName varchar(45); -- default 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION return 0; 

-- if count=1 then OK
select count(*) from users 
where login=ALogin and password=APassword and roles_id=(select id from roles where name=ARoleName)
into @RowCount;

if @RowCount=1 then return '1'; else return '0';  end if; 

 
END

CREATE DEFINER=`root`@`localhost` FUNCTION `loginpasswordrolecheck`(ALogin VARCHAR(45), APassword VARCHAR(45), ARoleName VARCHAR(45)) RETURNS varchar(45) CHARSET utf8

BEGIN

-- DECLARE foundRows INT; -- default 0;

-- DECLARE roleName varchar(45); -- default 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION return 0;

-- if count=1 then OK

select count(*) from users

where login=ALogin and password=APassword and roles_id=(select id from roles where name=ARoleName)

into @RowCount;

if @RowCount=1 then return '1'; else return '0'; end if;

END

Вызов из Delphi

procedure TMain.bHelloClick(Sender: TObject);
var 
  result:string;
begin
  with FDStoredProc do begin
    Connection:=FDConnection;
    //coffeetest_db.loginPasswordRoleCheck
    //StoredProcName:='loginpasswordrolecheck';
    StoredProcName:='loginpasswordrolecheck2';
    Prepare;
    Params[0].Value := 'admin1';
    Params[1].Value := 'admin';
    Params[2].Value := 'admin';
    Result := ExecFunc;
    ShowMessage(result);
  end;
end;

procedure TMain.bHelloClick(Sender: TObject);

var

result:string;

begin

with FDStoredProc do begin

Connection:=FDConnection;

//coffeetest_db.loginPasswordRoleCheck

//StoredProcName:='loginpasswordrolecheck';

StoredProcName:='loginpasswordrolecheck2';

Prepare;

Params[0].Value := 'admin1';

Params[1].Value := 'admin';

Params[2].Value := 'admin';

Result := ExecFunc;

ShowMessage(result);

end;

Пример №2 Вот функция в MySQL, которая проверяет пару логин пароль и при обнаружении … Continue reading →

Posted in Delphi, MySQL | Comments Off

Category Archives: MySQL

MySQL. CloneDB

MySQL. Triggers don’t fire on Cascade Delete

MySQL. Cравнение даты (datetime) через оператор BETWEEN

MySQL.Example with Cursor

Delphi. VCL и не только. Делаем фильтр. Почти быстро и просто

MySQL. Добавляем хранимые процедуры и функции ( “рутину” ) в модель и физическую базу данных

Delphi.MySQL.Мои компоненты.TPSDBTreeView

Delphi. MySQL. InsertSelect всех полей для реализации Copy Paste

MySQL. Вход по ролям на уровне базы

MySQL. Пример авторизации на языке базы (хранимая функция). И вызов из Delphi

MY PROJECTS

Recent Posts

Categories