Database

Re: Prevent users from playing with MySQL database

30-Jan-14 16:52

of course you use security and you give your application it's own identity. your app logs on using those creds.

Never underestimate the power of human stupidity
RAH

Eddy Vluggen30-Jan-14 23:30

Eddy Vluggen

30-Jan-14 23:30

Jassim Rahma wrote:
How can I make sure users in outlet won't mess with the database if any

Your average user doesn't know SQL; so there's no issue there.

You've asked this question before. He who owns the computer, is the local admin, and owns the database and everything in it. Resetting would be easy[^].

If you don't want them to peek in "your" database, then your only option is to not give them your database. Host it on your own machine, and put that machine there - or provide access using webservices.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Prevent users from playing with MySQL database

Jörgen Andersson30-Jan-14 23:31

Jörgen Andersson

30-Jan-14 23:31

You move all your SQL and DML over to Stored Procedures, add one user to the database that only has permissions to use these procedures and no permissions on any tables at all.
Now call these procedures from your application using this user. Make sure that the clients don't have access permissions to the database via the OS.

<edit>Eddy is of course right, there's no safe way to do it, you can only make it harder for the users.
Next step is to consider encryption of the tables, but the problem here is of course the same. If the database is on the machine, so are the encryption key.</edit>

Wrong is evil and must be defeated.
- Jeff Ello[^]

modified 31-Jan-14 4:38am.

Complex SQL Query for Inventory. Need Help

chichocojo29-Jan-14 13:35

chichocojo

29-Jan-14 13:35

Hi, I need some guidance on the following scenario:

I need to create a historic inventory of number of rooms sold per hotel.

A transactional table stores data in the following columns

IdHotel, CheckInDate, CheckOutDate, NoRooms

I need to generate a table with 365 records/days per year per hotel as follows:

hotel1, 01/Jan/2014, 1 room
hotel1,02/Jan/2014,2 rooms
hotel1,03/Jan/2014,2 rooms
hotel1,04/Jan/2014,0 rooms
.
.
.
.
.
.
hotel1, 01/Nov/2014, 1 room
hotel1, 02/Nov/2014, 1 room
hotel1, 03/Nov/2014, 1 room
.
.
.
.
.

I am a bit lost as to how to approach this problem for a solution.

I do not need the actual code. Just a hint on how to solve this problem.

Mycroft Holmes29-Jan-14 18:40

29-Jan-14 18:40

Select hotel,date,sum(no rooms )from table where datepart(yy,datefied)=2013 group by( hotel,date)

That should give you some ideas, get a book on tsql and work through it,you will find it invaluable .

Never underestimate the power of human stupidity
RAH

khun_panya29-Jan-14 20:01

khun_panya

29-Jan-14 20:01

I have some quite dirty solution

1. Create a dummy table of an int column stores 1...365
2. Select DATEADD 1/1/2014 with an int from table above. You'll get all "dates" in 2014.
3. Select COUNT on your transaction GROUP BY date from (2).
If you want to count how many rooms had been sold each day, you might need to count by matching CheckInDate.
If you want to count how many rooms were occupied each day, you might need to count by seeing if the "date" within CheckInDate & CheckOutDate.

King Fisher30-Jan-14 3:12

King Fisher

30-Jan-14 3:12

so,the hotel name is same ?

Richard Deeming30-Jan-14 3:24

Richard Deeming

30-Jan-14 3:24

chichocojo wrote:
I need to generate a table with 365 records/days per year per hotel as follows:

You do realise than some years have 366 days, right? Smile | :)

You can generate a list of all days in a given year by using a tally-table (or a common table expression which generates a tally table), along with the DateAdd function:

SQL

DECLARE <a href="/Members/Year">@Year</a>int = 2014;

-- Get 1st January in the specified year:
DECLARE @StartDate date = Convert(date, Convert(char(4), <a href="/Members/Year">@Year</a> + '0101');

WITH cte1 (N) As
(
    SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    -- 8 rows
),
cte2 (N) As
(
    SELECT TOP 366
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM
        cte1 As A
        CROSS JOIN cte1 As B
        CROSS JOIN cte1 As C
        -- 8 * 8 * 8 = 512 rows
),
cteDates (Value) As
(
    SELECT
        DateAdd(day, N, @StartDate)
    FROM
        cte2 -- 366 rows
    WHERE
        -- If this isn't a leap-year, we only need 355 rows:
        Year(DateAdd(day, N, @StartDate)) = <a href="/Members/Year">@Year</a>
)
SELECT
    Value
FROM 
    cteDates
ORDER BY
    Value
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

database projects

dexter.nmn29-Jan-14 2:12

dexter.nmn

29-Jan-14 2:12

I am using database project in my solution. I Need to know how to synchronize source(in sql server management studio) and target(in your solution) databases, in case when someone update the schema from Sql server IDE. Let me know if you need more clarity on the question.

Re: database projects

Mycroft Holmes29-Jan-14 18:33

Would an index make this query faster?

29-Jan-14 18:33

I have trouble with the whole idea that you the owner of the database do not control who and when the code is changed. You should be controlling who ans when someone or an application updates the code

Never underestimate the power of human stupidity
RAH

Optimizing a query

mrkeivan28-Jan-14 20:37

mrkeivan

28-Jan-14 20:37

Hi, please help me optimize this query

SQL

SELECT DISTINCT CC_STRINGENTEREXIT(ems.passdatetime,
      (Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
            where emsqi.emsinfoid = ems.emsinfoid)      
      ) as DetailEnterExit, 
      CC_DURATIONENTEREXIT(
      (Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
            where emsqi.emsinfoid = ems.emsinfoid)
      , ems.passdatetime) as DurationInZone
FROM EMSINFO ems
where
     trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS')) AND
     CC_GETNEXTENTER(ems.EMSINFOID, 
            (Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
            where emsqi.emsinfoid = ems.emsinfoid)
     , ems.PASSDATETIME) <> 0 and 
     CC_CheckForExit(ems.PASSDATETIME,
     (
          select ems2.PASSDATETIME from EMSINFO ems2 
          where ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,
            (Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
            where emsqi.emsinfoid = ems.emsinfoid)
          , ems.PASSDATETIME)
     ), 
            (Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
            where emsqi.emsinfoid = ems.emsinfoid)
     ) <> 0 
     and ems.masterplatenumber = '150444833' and rownum <100 ;

you see that
1-

SQL

Select emsqi.PLATENUMBER from EMSQUIRKINFO emsqi
            where emsqi.emsinfoid = ems.emsinfoid

has been repeated multiple time, how do I declare it once and use it as many times as I want.

2-

SQL

CC_GETNEXTENTER()

has also been repeated, and in the functions I have a similar Issue, how can I get the value of the function once and use it as many times as I want through the query?

Thanx in advance

Re: Optimizing a query

Jörgen Andersson29-Jan-14 2:44

Jörgen Andersson

29-Jan-14 2:44

1. You use a join. Something like this: Untested!

SQL

SELECT  DISTINCT
        CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit, 
        CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
FROM    EMSINFO ems JOIN EMSQUIRKINFO emsqi
    ON  emsqi.emsinfoid = ems.emsinfoid
WHERE   trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND     CC_GETNEXTENTER(ems.EMSINFOID, emsqi.PLATENUMBER, ems.PASSDATETIME) <> 0
AND     CC_CheckForExit(ems.PASSDATETIME, (
            SELECT  ems2.PASSDATETIME from EMSINFO ems2
            WHERE   ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME)
             ),emsqi.PLATENUMBER) <> 0 
AND     ems.masterplatenumber = '150444833' and rownum <100 ;

2. Use a CTE, Something like this: Still untested!

SQL

WITH bla AS (
    SELECT  CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit, 
            CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
            CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME) GetNextEnter
    FROM    EMSINFO ems JOIN EMSQUIRKINFO emsqi
        ON  emsqi.emsinfoid = ems.emsinfoid
    WHERE   trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
    AND     ems.masterplatenumber = '150444833'
    )
SELECT  DISTINCT
        DetailEnterExit,
        DurationInZone
WHERE   GetNextEnter <> 0
AND     CC_CheckForExit(
            ems.PASSDATETIME, 
                (
                SELECT  ems2.PASSDATETIME from EMSINFO ems2
                WHERE   ems2.EMSINFOID = GetNextEnter
                )
                ,emsqi.PLATENUMBER) <> 0 
AND     rownum <100
;

Now, keep in mind that I don't have a clue what your functions do, so my suggestions is only to show you the methodology, It's up to you to see if it gives the right result.
But you want as much of the filtering in the CTE as possible to keep the amount of data down.

Wrong is evil and must be defeated.
- Jeff Ello[^]

khun_panya27-Jan-14 19:42

khun_panya

27-Jan-14 19:42

select cs.customer_name, st.staff_name
from Customer cs
inner join Staff st on cs.city_id=st.city_id

(just a sample query, dont ask for its purpose)

I'm quite sure that we need an index on st.city_id to speed things up.
But would an index on cs.city_id make any different?
Because it looks like we have to scan entire Customer table anyway.

Mycroft Holmes27-Jan-14 22:00

27-Jan-14 22:00

Assuming you are using SQL Server, turn on the Profiler in SSMS and run the query, it will then recommend an index if required.

Never underestimate the power of human stupidity
RAH

Shameel27-Jan-14 23:43

Shameel

27-Jan-14 23:43

khun_panya wrote:
But would an index on cs.city_id make any different?

Most probably it will. The query optimizer decides whether to use an index or not depending on various criteria. If the number of rows in the table is very less, it might do a table scan rather than an index seek. But this is purely left to the optimizer to decide.

khun_panya wrote:
Because it looks like we have to scan entire Customer table anyway.

What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.

khun_panya29-Jan-14 7:23

khun_panya

29-Jan-14 7:23

Shameel wrote:
khun_panya wrote:
Because it looks like we have to scan entire Customer table anyway.
What makes you think so? If execution plan shows a table scan, then either an index does not exist or the number of rows is very less to warrant an index seek.

I have not checked for real execution plan. But above query do select with "no filter". So it would have to iterate all the rows in table. That's why I doubt that an index helps in this situation.

GuyThiebaut28-Jan-14 4:32

GuyThiebaut

28-Jan-14 4:32

There is a good chance that an index would speed things up.
The advice given so far is sound, however.. indexes are a bit of an art and like all arts you have to experiment occasionally.

Two areas that affect indexes are the number of rows and the frequency of updates and inserts.
For this reason you need to understand some of the theory behind indexes and also be willing to experiment with adding indexes to see what happens and learn from that.

I don't have any links but I would suggest giving yourself a good hour to google and read up on indexes as it will stand you in good stead.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens