Mysql lazarus
lazarus / 16-Databases

----------------------------------------------------------------------
Omirbay · 2016-10-06 05:39

Hello there!

I am new to server programming and beginner in terms of database
application.

The application that id like to build is comprised of two parts.

First, server application that has a mysql (*.sql) database in the
same directory as *.exe file.

Second, client application that connects to the server which has a
database. and does some manipulations on mysql database.

My question is that how can i create a server/client application which
work with mysql database.

At home, I have a denwer installed. I created a database with some
tables in it. I created an application which does some manipulation on
the database, such as adding and updating the table. I got the
database file by exporting that database on phpmyadmin page. Now I
need to have the program work over the net not in localhost. But to do
that, I have nothing in mind as im new to server/client field. Any
comment is welcome.

----------------------------------------------------------------------
Leledumbo · 2016-10-06 09:17

I wouldn't suggest creating custom protocol, so make a good use of
http. You can use either the shipped fpweb reading my tutorial or
brook if you prefer handcoding to drag-n-drop. There are also other
frameworks such as daraja (was: habari web components) built on top
another http library (fpweb & brook are built on top of fcl-web). The
server app is no different than regular database driven desktop
application, it's just the communication (same level as user
interface) that's different (http).

----------------------------------------------------------------------
Devstructor · 2016-10-06 15:11

Hi,

would it be perfect for you that the client could nearly directly work
on the database and execute multiple different queries, or should the
client kind of just ask the database for some information like the
current application version, or news, maybe a login into an user
account?

----------------------------------------------------------------------
Omirbay · 2016-10-07 04:23

Quote from: Leledumbo on October 06, 2016, 11:17:51 amI wouldn't
suggest creating custom protocol, so make a good use of http. You can
use either the shipped fpweb reading my tutorial or brook if you
prefer handcoding to drag-n-drop. There are also other frameworks such
as daraja (was: habari web components) built on top another http
library (fpweb & brook are built on top of fcl-web). The server app is
no different than regular database driven desktop application, it's
just the communication (same level as user interface) that's different
(http).

Hello there! I have read the tutorial about fpWeb you suggested and
followed some code written there. But still I don't have a clear
picture of what you mean by that.

A friend of mine suggested downloading mysql community server for
that. After installation, he said it would be possible to connect to
the computer the server installed pointing the ip address of that
specific computer. This morning, I downloaded and installed that
server but I could not import the database I exported from phpmyadmin.

----------------------------------------------------------------------
Omirbay · 2016-10-07 04:31

Quote from: Devstructor on October 06, 2016, 05:11:30 pmHi,

would it be perfect for you that the client could nearly directly work
on the database and execute multiple different queries, or should the
client kind of just ask the database for some information like the
current application version, or news, maybe a login into an user
account?

Hi there!

I am only going to allow clients to manipulate data directly following
the established connection. I think I will only be giving him buttons
or edits on the form to add or modify records, and as for the other
options such as deleting he will not be permitted to. You know, he
will not hopefully see the username and password that are written in
the object inspector after compilation nor do such queries that can
delete collected data. I am going to upload the code I wrote.

----------------------------------------------------------------------
Devstructor · 2016-10-07 08:45

Hi,

If I had to develop a solution, I would have considered these two
approaches:
Information
DB := Database - collection of Data representing your modeled aspect
of your world

DBMS := Database Management System - software system for the design,
creation, implementation and operation of one or more DBs. In this use
case it is really important that the DBMS supports features like a
detailed right system.

Solution 1

DB <----> DBMS <-----------------------------------> Client

I would prefer this situation whenever it's possible. Most of my
applications are working in an own network and establish tunnels to
other networks. The Client would directly access the DBMS and execute
queries like selects, updates and more. In my point of view this is
the best approach for LANs and many enterprise applications (people
are working together with the same data, connected in a LAN or using
tunnels...). The cool thing is that you could setup a detailed right
system. One solution could be that each user has an own account. This
works when you don't have to many users and you need to work together.
In this case you could assign custom rights to each individual user.

If you don't want to give them individual accounts, you could also
create one "public" account. This account would not have any rights to
even take a look into tables. So everybody using this account could
not perform something like a select * from USERS or update ACCOUNT set
... The public account would only be allowed to execute one or a hand
full of stored procedures (SPs). The idea is that they can only
execute these and the procedures have rights to tables and more. For
instance in Firebird (a DBMS) it's possible to set permissions to SPs.
SPs can also return values. Let me give you an example:

Each client can only execute (simple example) the SP USERLOGIN. This
SP needs some params like the username maybe a password hash, or
whatever you need. So all users can only run this SP and can't see any
tables or records. The SP USERLOGIN has own rights and is allowed to
take a look in tables like USER or ACCOUNT. Maybe this SP will also be
allowed to manipulate data and set something like a last login
timestamp.

In this case you would need a client library to connect to the DBMS.
Again I'm using Firebird and this powerful and open source DBMS
features a embedded client library, so your users could run your
application from an usb stick without the need to install a DBMS or
DBMS client. It's really simple to establish a connection using the
TIBConnection component and the TSQLDBLibraryLoader for a custom or
specified client library (fbclient.dll for windows).

Solution 2

DB <----> DBMS <-------Custom-Server App------> Client

This second solution has a custom server in between the DBMS and the
client. Like @Leledumbo mentioned this application will receive the
commands from the client and coordinate the access to the DBMS.

Commands could be quite something different:
a) Custom protocol commands like (in understandable words): log me in,
give me the news, what is the current application version

b) SQLs like: select RSESSION, ACCOUNTNAME from
USERLOGIN(:MyUser,:MyPass)

(a) would be your own protocol. You could use existing stuff like HTTP
and send your command as document. The server could return the needed
data as CSV, XML, or whatever you prefer. You could also develop your
own protocols using simple TCP-Components like TidTCPServer (from
Indy). Components like TidHTTP (also from indy) could also use HTTPs
for an encrypted connection.

(b) would just be a gateway. Maybe you don't want to open the DBMS
port to the public, or you have multiple maybe even critical DBs on
the server. For one project I could not use a DB connection and I
developed something like this. I developed a HTTP-Server which
received SQL request and executed these with a db connection. The DB
account was transfers using HTTP-Auth, so the SQLs were executed with
the correct user and the corresponding permissions. The server
answered in a specified format like csv. I didn't needed a SSL
encryption, because the whole system was just used in a LAN.

Last words
I don't know your application, but I guess I would focus on solution 1
or solution 2a. It depends on the network (LAN vs WAN), your users
(employees vs everybody in the internet) and the use case (working
together vs just receiving news).

It could also be a challenge for a DB and server beginner to develop
this custom app server. You may be vulnerable or users could shut down
your system by attacking (DDOS) the custom app server.

QuoteYou know, he will not hopefully see the username and password
that are written in the object inspector after compilation nor do such
queries that can delete collected data. Well that is not so easy. The
object inspector just displays the password with a mask. Of course the
computer knows what you typed in and this information will be stored
in your memory. It's really easy to run the final application, do a
memory dumb and search for the password. Of course it would be great
when your system is so secure that you could give everybody an account
and nobody could do something bad (take a look at the permissions of
your DBMS).

Greetings
Miguel

----------------------------------------------------------------------
Omirbay · 2016-10-07 10:28

Here is the code

----------------------------------------------------------------------
Omirbay · 2016-10-07 10:34

Quote from: Devstructor on October 07, 2016, 10:45:14 amHi,

If I had to develop a solution, I would have considered these two
approaches:
Information
DB := Database - collection of Data representing your modeled aspect
of your world

DBMS := Database Management System - software system for the design,
creation, implementation and operation of one or more DBs. In this use
case it is really important that the DBMS supports features like a
detailed right system.

Solution 1

DB <----> DBMS <-----------------------------------> Client

I would prefer this situation whenever it's possible. Most of my
applications are working in an own network and establish tunnels to
other networks. The Client would directly access the DBMS and execute
queries like selects, updates and more. In my point of view this is
the best approach for LANs and many enterprise applications (people
are working together with the same data, connected in a LAN or using
tunnels...). The cool thing is that you could setup a detailed right
system. One solution could be that each user has an own account. This
works when you don't have to many users and you need to work together.
In this case you could assign custom rights to each individual user.

If you don't want to give them individual accounts, you could also
create one "public" account. This account would not have any rights to
even take a look into tables. So everybody using this account could
not perform something like a select * from USERS or update ACCOUNT set
... The public account would only be allowed to execute one or a hand
full of stored procedures (SPs). The idea is that they can only
execute these and the procedures have rights to tables and more. For
instance in Firebird (a DBMS) it's possible to set permissions to SPs.
SPs can also return values. Let me give you an example:

Each client can only execute (simple example) the SP USERLOGIN. This
SP needs some params like the username maybe a password hash, or
whatever you need. So all users can only run this SP and can't see any
tables or records. The SP USERLOGIN has own rights and is allowed to
take a look in tables like USER or ACCOUNT. Maybe this SP will also be
allowed to manipulate data and set something like a last login
timestamp.

In this case you would need a client library to connect to the DBMS.
Again I'm using Firebird and this powerful and open source DBMS
features a embedded client library, so your users could run your
application from an usb stick without the need to install a DBMS or
DBMS client. It's really simple to establish a connection using the
TIBConnection component and the TSQLDBLibraryLoader for a custom or
specified client library (fbclient.dll for windows).

Solution 2

DB <----> DBMS <-------Custom-Server App------> Client

This second solution has a custom server in between the DBMS and the
client. Like @Leledumbo mentioned this application will receive the
commands from the client and coordinate the access to the DBMS.

Commands could be quite something different:
a) Custom protocol commands like (in understandable words): log me in,
give me the news, what is the current application version

b) SQLs like: select RSESSION, ACCOUNTNAME from
USERLOGIN(:MyUser,:MyPass)

(a) would be your own protocol. You could use existing stuff like HTTP
and send your command as document. The server could return the needed
data as CSV, XML, or whatever you prefer. You could also develop your
own protocols using simple TCP-Components like TidTCPServer (from
Indy). Components like TidHTTP (also from indy) could also use HTTPs
for an encrypted connection.

(b) would just be a gateway. Maybe you don't want to open the DBMS
port to the public, or you have multiple maybe even critical DBs on
the server. For one project I could not use a DB connection and I
developed something like this. I developed a HTTP-Server which
received SQL request and executed these with a db connection. The DB
account was transfers using HTTP-Auth, so the SQLs were executed with
the correct user and the corresponding permissions. The server
answered in a specified format like csv. I didn't needed a SSL
encryption, because the whole system was just used in a LAN.

Last words
I don't know your application, but I guess I would focus on solution 1
or solution 2a. It depends on the network (LAN vs WAN), your users
(employees vs everybody in the internet) and the use case (working
together vs just receiving news).

It could also be a challenge for a DB and server beginner to develop
this custom app server. You may be vulnerable or users could shut down
your system by attacking (DDOS) the custom app server.

QuoteYou know, he will not hopefully see the username and password
that are written in the object inspector after compilation nor do such
queries that can delete collected data. Well that is not so easy. The
object inspector just displays the password with a mask. Of course the
computer knows what you typed in and this information will be stored
in your memory. It's really easy to run the final application, do a
memory dumb and search for the password. Of course it would be great
when your system is so secure that you could give everybody an account
and nobody could do something bad (take a look at the permissions of
your DBMS).

Greetings
Miguel 

Hi Miguel!

I appreciate your reply. I will leave here my comment.

Best regards,
Omirbay

----------------------------------------------------------------------
Devstructor · 2016-10-07 16:41

Quotehas a mysql (*.sql) database in the same directory as *.exe file.
Btw: A sql file is not a database. It's a script, for instance a dump
containing all needed SQLs to get the current version of the database.
Open it and check whether it's full of create table... and insert
into..

I recommend to check out our DBMS (MySQL, Firebird, or whatever you
like to use). There are difference in concepts and features. Pick the
best one for your use case. I don't want to do advertisement, but
Firebird is a really powerful DBMS and I would use it every time when
it's possible. But gather your own information and pick what you want.
After this step check out how to connect to your DBMS:

http://wiki.freepascal.org/SQLdb_Package
http://wiki.freepascal.org/SqlDBHowto

Greetings
Miguel