SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

Jethro posted:

code:

select *
  from UserPermissions up
       inner join Users u on u.UserNTLogin = up.userNTLogin
       inner join Users m on u.ManagerNTLogin = m.userNTLogin
 where not exists (select 0
                     from UserPermissions mp
                    where m.UserNTLogin = mp.userNTLogin
                          and mp.site = up.site
                          and mp.Application = up.Application
                          and mp.PermissionName = up.PermissionName)

Return all user permissions and users and their managers where there is no matching permission for the manager.

Great, thank you. Works rather well, just need to filter out users with full admin permission and I can start building the report. =)

# ? Oct 12, 2009 20:32

Adbot: ADBOT LOVES YOU

# ? Jun 3, 2024 20:58

benisntfunny: Dec 2, 2004; I'm Perfect.

Is there a better way to do this? tsql

I have entries being made into the database something like this...

code:

Order_Type   Customer_Name        Date
Shipment     Bob                  10/10/2009
Shipment     Bob                  10/11/2009
Pickup       Bob                  10/10/2009

I want to select the last unique order types for bob.

So my result is

code:

Order_Type   Customer_Name        Date
Shipment     Bob                  10/11/2009
Pickup       Bob                  10/10/2009

So here's more or less the code I've written (Which I absolutely hate and think it's dumb).

code:

DECLARE @TMP TABLE (
OrderType VARCHAR(100),
Date      DATETIME
)

INSERT INTO @TMP (OrderType,Date)
SELECT Order_Type,
       Date
FROM   OrdersTable
WHERE  Customer_Name = 'Bob'
DELETE @TMP
WHERE  Date IN (
                SELECT t1.Date 
                FROM @TMP t1
                INNER JOIN @TMP t2
                    ON t1.OrderType = t2.OrderType
                WHERE t1.Date < t2.Date
                )
SELECT *
FROM   OrdersTable
WHERE  Customer_Name = 'Bob'
       AND Date IN (
                    SELECT Date 
                    From   @TMP
                   )

My biggest problem, aside from looking stupid and having a .00000000005% of sometimes returning two records for the same Order_type is that I'm doing two hits at the table.

EDIT: This is dumb, can't I just do the exact same thing I'm doing in the delete but the opposite and handle it in the select?

EDIT2: No.

benisntfunny fucked around with this message at 23:54 on Oct 14, 2009

# ? Oct 14, 2009 23:32

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

code:

select order_type, customer_number, max( date ) from orderstable group by order_type, customer_number

?

Also, customer_number is Bob?

# ? Oct 14, 2009 23:41

benisntfunny: Dec 2, 2004; I'm Perfect.

ShoulderDaemon posted:

code:

select order_type, customer_number, max( date ) from orderstable group by order_type, customer_number

?

Also, customer_number is Bob?

it's an example... albiet since I gave a number as varchar you might wonder how good. MAX(DATE) didn't work for me. I also have quite a bit more columns being returned.

# ? Oct 14, 2009 23:43

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

benisntfunny posted:

it's an example... albiet since I gave a number as varchar you might wonder how good. MAX(DATE) didn't work for me. I also have quite a bit more columns being returned.

Max(Date) should do exactly what you want unless its not stored in the database as a date.

# ? Oct 14, 2009 23:57

benisntfunny: Dec 2, 2004; I'm Perfect.

Sprawl posted:

Max(Date) should do exactly what you want unless its not stored in the database as a date.

code:

SELECT PAT_CODE_ID,MAX(PAT_CREATE_DT) 
FROM PET_PERSON_ATTRIBUTE
INNER JOIN BUT_CODE
	ON PAT_CODE_ID = COD_CODE_ID
INNER JOIN BUT_CODE_TYPE
    ON COD_CODE_TYPE_ID = CTY_CODE_TYPE_ID
       AND CTY_STATIC_NM = 'EmailSent'
WHERE PAT_VARCHAR1_TXT = '063563961000015'
GROUP BY PAT_CODE_ID,PAT_CREATE_DT


PAT_CODE_ID	(No column name)
14036	2009-10-14 17:05:22.803
14036	2009-10-14 17:12:51.503
14036	2009-10-14 17:47:38.323
14037	2009-10-14 18:06:01.063
14037	2009-10-14 18:06:01.843
14037	2009-10-14 18:06:02.073

# ? Oct 15, 2009 00:00

ShoulderDaemon: Oct 9, 2003; support goon fund; Taco Defender

You probably don't want to group by the column you are trying to take the maximum of.

# ? Oct 15, 2009 00:05

benisntfunny: Dec 2, 2004; I'm Perfect.

ShoulderDaemon posted:

You probably don't want to group by the column you are trying to take the maximum of.

Probably shouldn't. You're right. THanks. I knew it was dumb.

EDIT: well actually it didn't work on my main select, but it works enough for me to do this a cleaner way.

benisntfunny fucked around with this message at 00:10 on Oct 15, 2009

# ? Oct 15, 2009 00:06

indulgenthipster: Mar 16, 2004; Make that a pour over

I think I just hit a bug with MySQL 5.0.51a.

In one of my larger queries, I have a section that is WHERE user_id IN (1,2,3,4).

This works fine for every combination, (3,4,5), (4), (5), but as soon as I try (1), it pulls up zero results. If I switch IN to =, it works.

I'll just avoid using that user_id for anybody, but has anyone heard of this happening?

# ? Oct 16, 2009 15:30

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

I need to make this faster (mysql):

code:

SELECT COUNT(DISTINCT users.id)
  FROM subs RIGHT JOIN users ON users.id = subs.userid
  WHERE subs.FromDate <= '20091016'
    AND subs.ToDate >= '20091016'
    AND users.deleted = 0;

The tables users and subs have indexes on id, userid, FromDate, and ToDate, but none of them get used according to explain. FromDate and ToDate are VARCHAR(8) and the ID columns are plain INTs. The dates come from PHP and can be pretty much any number, but will always be the same (it's meant to see who could log in on a single day).

Right now I'm testing on a database with 50k+ rows in each table and it takes about 2 seconds for the query to run, which is unacceptable.

It would be very difficult to change the column types or database structure, so don't bother telling me to unless it's the only possible way. Also, I didn't design this thing and I already know it's - lets say - not great.

Let me know if I need to provide more information.

# ? Oct 16, 2009 17:24

var1ety: Jul 26, 2004

Munkeymon posted:

I need to make this faster (mysql):
code:
SELECT COUNT(DISTINCT users.id)
  FROM subs RIGHT JOIN users ON users.id = subs.userid
  WHERE subs.FromDate <= '20091016'
    AND subs.ToDate >= '20091016'
    AND users.deleted = 0;
The tables users and subs have indexes on id, userid, FromDate, and ToDate, but none of them get used according to explain. FromDate and ToDate are VARCHAR(8) and the ID columns are plain INTs. The dates come from PHP and can be pretty much any number, but will always be the same (it's meant to see who could log in on a single day).

Right now I'm testing on a database with 50k+ rows in each table and it takes about 2 seconds for the query to run, which is unacceptable.

It would be very difficult to change the column types or database structure, so don't bother telling me to unless it's the only possible way. Also, I didn't design this thing and I already know it's - lets say - not great.

Let me know if I need to provide more information.

If subs and users are 1:1 you should get rid of the DISTINCT. Also, using an outer join here is inappropriate since you require rows in both to exist. If subs has users not in the users table you should normalize the users table.

code:

SELECT COUNT(*)
  FROM subs INNER JOIN users ON users.id = subs.userid
  WHERE subs.FromDate <= '20091016'
    AND subs.ToDate >= '20091016'
    AND users.deleted = 0;

Something lovely is probably happening with your varchar >= and <= operators. It's possible that your database would use the indexes if they were actual dates or numbers. With your dates as numbers or varchar, though, you are taking information away from the optimizer that it might be able to use to better optimize your statement (the knowledge that your columns are in fact dates, and subject to a limited set of valid entries).

Are subs that begin and end in the future actually possible? If not, you can take out the FromDate comparison, since you really only care if they are ending in the future. Also, you might be able to get better results by bounding your ToDate if you know that your subs can only end (say) at most 90 days from today (ToDate between '20091016' and '20091216').

# ? Oct 16, 2009 18:03

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

var1ety posted:

If subs and users are 1:1 you should get rid of the DISTINCT. Also, using an outer join here is inappropriate since you require rows in both to exist. If subs has users not in the users table you should normalize the users table.

We should do a lot of things we don't

It's 1:many, but we might orphan subs rows somewhere.

quote:

code:
SELECT COUNT(*)
  FROM subs INNER JOIN users ON users.id = subs.userid
  WHERE subs.FromDate <= '20091016'
    AND subs.ToDate >= '20091016'
    AND users.deleted = 0;
Something lovely is probably happening with your varchar >= and <= operators. It's possible that your database would use the indexes if they were actual dates or numbers. With your dates as numbers or varchar, though, you are taking information away from the optimizer that it might be able to use to better optimize your statement (the knowledge that your columns are in fact dates, and subject to a limited set of valid entries).

Are subs that begin and end in the future actually possible? If not, you can take out the FromDate comparison, since you really only care if they are ending in the future. Also, you might be able to get better results by bounding your ToDate if you know that your subs can only end (say) at most 90 days from today (ToDate between '20091016' and '20091216').

The inner join is about 20% faster if I remove distinct, so that's cool and thank you, but I'm concerned that the result would be wrong for one of our databases where users and subs aren't 1:1. I'll have to check that. It's much slower with distinct, however. ~7 seconds as opposed to ~2

Subscriptions can be in the future because this is for statistics reporting and it could examine any date. We have to support any date range on a subscription that anyone might want to type in. October 31, 1910 -> November 12th, 3045? Yeah, sure why not?

I think I've tried changing the columns to unsigned ints and had even worse results. I'll have to check that again, I guess. I'm pretty sure dates are out of the question because then I'd have to rewrite any query that looks at this table, right? Background: this being a 'traditional' PHP monstrosity, all database interaction is done via mysql_query with query strings constructed in-code.

Munkeymon fucked around with this message at 18:31 on Oct 16, 2009

# ? Oct 16, 2009 18:28

var1ety: Jul 26, 2004

Munkeymon posted:

The inner join is about 20% faster if I remove distinct, so that's cool and thank you, but I'm concerned that the result would be wrong for one of our databases where users and subs aren't 1:1. I'll have to check that. It's much slower with distinct, however. ~7 seconds as opposed to ~2

Cutting out unnecessary steps is the fastest way to get a job done. You should spend your time cleaning up the outliers and (if it makes sense in your situation) policing these using constraints so they cannot recur.

You could also consider staging the results into a table and refreshing daily (or intra-daily, or after inserts).

# ? Oct 16, 2009 18:44

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

var1ety posted:

Cutting out unnecessary steps is the fastest way to get a job done. You should spend your time cleaning up the outliers and (if it makes sense in your situation) policing these using constraints so they cannot recur.

You could also consider staging the results into a table and refreshing daily (or intra-daily, or after inserts).

The problem is that users:subs being 1:many is the correct behavior as the system was designed, but some databases are going to be 1:1 by chance because of the way they happen to be used, so I can't make that assumption or clean anything up.

# ? Oct 16, 2009 19:17

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Munkeymon posted:

We should do a lot of things we don't It's 1:many, but we might orphan subs rows somewhere.

The inner join is about 20% faster if I remove distinct, so that's cool and thank you, but I'm concerned that the result would be wrong for one of our databases where users and subs aren't 1:1. I'll have to check that. It's much slower with distinct, however. ~7 seconds as opposed to ~2

Subscriptions can be in the future because this is for statistics reporting and it could examine any date. We have to support any date range on a subscription that anyone might want to type in. October 31, 1910 -> November 12th, 3045? Yeah, sure why not?

I think I've tried changing the columns to unsigned ints and had even worse results. I'll have to check that again, I guess. I'm pretty sure dates are out of the question because then I'd have to rewrite any query that looks at this table, right? Background: this being a 'traditional' PHP monstrosity, all database interaction is done via mysql_query with query strings constructed in-code.

Well you could do some hackie things that i've done before to terrible web apps.

Create new columns called <name>2 and have them date values and then just change anything that would write the data into them to also update those columns without disturbing anything working on the old ones.

The inner join method will only make sure you have a record in both tables so if a users entry is deleted without wiping out its subs entry they subs won't show up in the list otherwise it will do everything the same as the right join.

You do have a combined index of the 2 date's as one index right?

Are these MYISAM or INNODB tables and are your index's btrees?

# ? Oct 16, 2009 19:29

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

Sprawl posted:

Well you could do some hackie things that i've done before to terrible web apps.

Create new columns called <name>2 and have them date values and then just change anything that would write the data into them to also update those columns without disturbing anything working on the old ones.

The inner join method will only make sure you have a record in both tables so if a users entry is deleted without wiping out its subs entry they subs won't show up in the list otherwise it will do everything the same as the right join.

You do have a combined index of the 2 date's as one index right?

Are these MYISAM or INNODB tables and are your index's btrees?

We never actually delete users or subs (except by hand if we have to for some reason). Making a combined index didn't do anything - it still won't use the index and I did try both (FromDate,ToDate) and (ToDate,FromDate). I suspect it simply can't use an index with any combination of less or greater than on a varchar column, but I can't find confirmation of this in the manual.

It's all MyISAM and they're all BTREES.

Edit: Making a copy of the subs table and turning the dates into ints actually slowed it down - wtf?

Edit 2: Is it unreasonable of me to expect it to be able to use an index to examine a column with greater or less than?

Munkeymon fucked around with this message at 20:39 on Oct 16, 2009

# ? Oct 16, 2009 20:07

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Munkeymon posted:

We never actually delete users or subs (except by hand if we have to for some reason). Making a combined index didn't do anything - it still won't use the index and I did try both (FromDate,ToDate) and (ToDate,FromDate). I suspect it simply can't use an index with any combination of less or greater than on a varchar column, but I can't find confirmation of this in the manual.

It's all MyISAM and they're all BTREES.

Edit: Making a copy of the subs table and turning the dates into ints actually slowed it down - wtf?

Edit 2: Is it unreasonable of me to expect it to be able to use an index to examine a column with greater or less than?

Can you try date value fields and maybe an InnoDB table i find they cache these kinds of results better.

# ? Oct 16, 2009 21:18

MoNsTeR: Jun 29, 2002

Bite the bullet and convert to actual date fields. If you don't, these problems will only get worse, and at the same time it will become harder to make the switch.

# ? Oct 16, 2009 21:27

LP0 ON FIRE: Jan 25, 2006; _{beep boop}

I'm trying to find a very conditional mysql insert command that checks for a duplicate key AND something else. Using php.

Usual statement goes something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=4, b=5, c=6;

I want something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY && DUPLICATE columnName($value) UPDATE a=4, b=5, c=6;

LP0 ON FIRE fucked around with this message at 22:19 on Oct 16, 2009

# ? Oct 16, 2009 22:16

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

MoNsTeR posted:

Bite the bullet and convert to actual date fields. If you don't, these problems will only get worse, and at the same time it will become harder to make the switch.

I tried date fields in MyISAM and it's still only about 25% faster than my base time. Thankfully it understands ISO dates so I didn't have to write anything to convert them. I'll give InnoDB a shot.

Edit: InnoDB doesn't make any difference :\ Is there really no way to make it use an index on these columns? I tried INT, CHAR, VARCHAR, and DATE.

Munkeymon fucked around with this message at 23:27 on Oct 16, 2009

# ? Oct 16, 2009 23:05

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Munkeymon posted:

I tried date fields in MyISAM and it's still only about 25% faster than my base time. Thankfully it understands ISO dates so I didn't have to write anything to convert them. I'll give InnoDB a shot.

Edit: InnoDB doesn't make any difference :\ Is there really no way to make it use an index on these columns? I tried INT, CHAR, VARCHAR, and DATE.

I have one setup that uses them on date field fine? there is a primary key set for the table right?

# ? Oct 17, 2009 01:05

var1ety: Jul 26, 2004

Munkeymon posted:

I tried date fields in MyISAM and it's still only about 25% faster than my base time. Thankfully it understands ISO dates so I didn't have to write anything to convert them. I'll give InnoDB a shot.

Edit: InnoDB doesn't make any difference :\ Is there really no way to make it use an index on these columns? I tried INT, CHAR, VARCHAR, and DATE.

Could just be your data distribution or the way you're ordering your date fields in your index. If the optimizer thinks you're going to be pulling out a ton of rows then it may think it is faster pulling the whole table.

Are you doing a composite index on (fromdate, todate)? If most of your entries have a fromdate <= today then it'll probably ignore the index. If only a small percentage have todate >= today then try indexing on that column first.

# ? Oct 17, 2009 06:08

Vince McMahon: Dec 18, 2003

awdio posted:

I'm trying to find a very conditional mysql insert command that checks for a duplicate key AND something else. Using php.

Usual statement goes something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=4, b=5, c=6;

I want something like this:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY && DUPLICATE columnName($value) UPDATE a=4, b=5, c=6;

Do you mean you want the two columns to have unique pairs? If so you can create a unique index on (column1, column2) and it should work. Not sure if I've understood your problem correctly though.

# ? Oct 17, 2009 11:46

LP0 ON FIRE: Jan 25, 2006; _{beep boop}

Vince McMahon posted:

Do you mean you want the two columns to have unique pairs? If so you can create a unique index on (column1, column2) and it should work. Not sure if I've understood your problem correctly though.

Sorry I wasn't so clear. Let me explain a slightly different situation, because I think it would be even more help. I would like one statement that inserts if two certain column values do not exist. If they do exist, do an update.

# ? Oct 17, 2009 15:10

Munkeymon: Aug 14, 2003; Motherfucker's got an
armor-piercing crowbar! Rigoddamndicu𝜆ous.

Sprawl posted:

I have one setup that uses them on date field fine? there is a primary key set for the table right?

Yeah, an autoincrementing ID column that I don't think gets used much if at all.

var1ety posted:

Could just be your data distribution or the way you're ordering your date fields in your index. If the optimizer thinks you're going to be pulling out a ton of rows then it may think it is faster pulling the whole table.

Are you doing a composite index on (fromdate, todate)? If most of your entries have a fromdate <= today then it'll probably ignore the index. If only a small percentage have todate >= today then try indexing on that column first.

It is possible for this dataset that it thinks pulling the whole table is faster since most of the subs are valid on this database. I think I know of a different one I can test that on.

I'm testing with 4 indexes on from, to, (from,to) and (to,from). It shouldn't be a problem to have them all on the same table should it? I'm not going to confuse the optimizer or anything am I? I'd just be happy to see it try to use one.

# ? Oct 19, 2009 15:22

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Munkeymon posted:

I'm testing with 4 indexes on from, to, (from,to) and (to,from). It shouldn't be a problem to have them all on the same table should it? I'm not going to confuse the optimizer or anything am I? I'd just be happy to see it try to use one.

As far as I know adding too many indexes never confuses it. It will always use at most one index, and it will (hopefully) choose the best one. Having redundant indexes just means you are taking up space.

# ? Oct 20, 2009 01:33

Fastbreak: Jul 4, 2002; Don't worry, I had ten bucks.

I am trying to query some XML in an XML column in a SQL2K8 DB. Here is the xml

code:

<promotionData>
  <field name="companyName">Company</field>
  <field name="addressLine1">Address</field>
  <field name="countryCode">Country</field>
  <field name="postalCode">Postal</field>
</promotionData>

So I do a select against the table and try to through a value function in there:

code:

SELECT TOP 1000 
       [promotionId]      
      ,[formData].value('(/promotionData/field[name="postalCode"])[1]', 'nvarchar(30)') as Postal
  FROM TABLE

But no matter what the Postal field comes back as null even though I know there is data in there. I am obviously not putting my xpath query in right, but I am not sure what I am doing wrong. Any advice?

Edit: I forgot the '@' symbol on the attribute name. Doh!

Fastbreak fucked around with this message at 17:03 on Oct 20, 2009

# ? Oct 20, 2009 16:16

almostkorean: Jul 9, 2001; eeeeeeeee

I'm trying to find rows with events inbetween the given event_start_time and event_end_time

code:

rst = stmt.executeQuery("select event_start_time, event_end_time " + 
		        "from " + eventTableName + " " +
		        "where event_start_time >= '"+ts.get(i)+"' and event_end_time <='"+ts.get(i+1)+"'");

ts is an ArrayString<Timestamp>, and when I output an element from ts it looks like:

code:

2009-10-13 21:00:00.0

Which I think would be fine? But when I run it I get this:

code:

java.sql.SQLException: ORA-01843: not a valid month

Can someone please help and tell me what the effff is wrong here?

# ? Oct 20, 2009 22:02

Sub Par: Jul 18, 2001; Dinosaur Gum

almostkorean posted:

Can someone please help and tell me what the effff is wrong here?

It's probably expecting the default format of 'DD-MON-YY'. Try using to_date(yourVariable,'YYYY-MM-DD HH24:MI:SS'). It is failing on my implementation (10g) because of the fractional seconds, so either remove that fraction or deal with it in the format mask in to_date(). I googled for a minute and found something about using FF for fractional seconds, but it's not working for me.

# ? Oct 20, 2009 22:19

var1ety: Jul 26, 2004

Sub Par posted:

It's probably expecting the default format of 'DD-MON-YY'. Try using to_date(yourVariable,'YYYY-MM-DD HH24:MI:SS'). It is failing on my implementation (10g) because of the fractional seconds, so either remove that fraction or deal with it in the format mask in to_date(). I googled for a minute and found something about using FF for fractional seconds, but it's not working for me.

Dates only have precision to the seconds position so the FF modifier does not work with to_date(). You can use it with to_timestamp().

OP - if your column is a date you should strip the fractional seconds then use to_date() and/or modify your NLS_DATE_FORMAT session variable. If your column is a timestamp you should use to_timestamp() and either set your NLS_TIMESTAMP_FORMAT, or specify it as an argument to to_timestamp().

code:

user@db> select 'foo' AS bar from dual where systimestamp > to_timestamp('2009-01-01 01:00:00.0');
select 'foo' AS bar from dual where systimestamp > to_timestamp('2009-01-01 01:00:00.0')
                                                                *
ERROR at line 1:
ORA-01843: not a valid month


user@db> alter session set NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';

Session altered.

user@db> select 'foo' AS bar from dual where systimestamp > to_timestamp('2009-01-01 01:00:00.0');

BAR
---
foo

user@db>

# ? Oct 20, 2009 22:39

almostkorean: Jul 9, 2001; eeeeeeeee

var1ety posted:

stuff

Nice! It worked, thank you very much

# ? Oct 21, 2009 01:08

wigga please: Nov 1, 2006; by mons all madden

Please be patient because I think I'm about to ask some appallingly dumb poo poo.

I'm trying to write a POS (Point of Sale, although atm Piece Of poo poo is more appropriate) application in C#/.NET using SQL Server as the database. I want to use as much ANSI SQL rather than MS stuff as I don't want to have a lot of work when I'll eventually change the db to MySQL or PostgreSQL.

Everything (suppliers, brands, products, categories, invoices,...) is stored over 20-something tables in a single database.

What I want to achieve is that different types of users with different rights can sign in to the application. For example, administrators have full read/write/create control, management has read/write on all tables, accounting can only create products and change tax rates...

My first question: how do I work with roles/logins/users/schemas? Can I keep a single login, create schemas for every kind of employee, and then create users in a role that uses these schemas (where the users would inherit their permissions from the role they are in)?

Second: on the login screen, the login/password are tested against a table "Users" in the main database with the following stored procedure:

code:

USE [POSDB]

      ALTER procedure [dbo].[ValidateUserLogin]

      @UserName varchar(30)

      , @Password varchar(30)

      as

      begin

      if exists (select * from Users as ut

      where ut.login = @UserName AND ut.password = @Password)

      select 1;

      else

      select 0;

      end

In my application I use:

code:

                cnc.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "ValidateUserLogin";

                cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = u.UserName;
                cmd.Parameters.Add("@Password", SqlDbType.VarChar, 30).Value = u.PassWord;

where the objest u is a class that defines a user (who also has a 'level' but that's for use inside the application for enabling/disabling boxes where the user doesn't have db access)

But the

code:

cnc

in question uses the standard POSDB login (remember there is only a single login atm).

So should I check logins/password under a readonly user, and, on a successful login, log that user off and log in with the credentials given by the user? Can I even specify a connection with a specific user rather than a login? Should I gtfo and go do webdesign?

Please help me SQL thread :cry:

# ? Oct 22, 2009 19:46

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

well if your using MSSQL it can integrate really nicely into the AD users and permissions and they you can set it to do lots of stuff but mysql/postgresql do things differently even from eachother so you will likely for future proofing purposes have to do this all outside of the sql level.

# ? Oct 22, 2009 19:56

wigga please: Nov 1, 2006; by mons all madden

Sprawl posted:

well if your using MSSQL it can integrate really nicely into the AD users and permissions and they you can set it to do lots of stuff but mysql/postgresql do things differently even from eachother so you will likely for future proofing purposes have to do this all outside of the sql level.

Well the thing is, even though it's much easier, I don't want to make AD a requirement to use the POS app and make users go through the trouble of buying/installing an AD server, but ultimately include MySQL, postgres, hell even SQLite (I am young and naive).
Also in the environment this is to be used, users switching would be too slow, most likely there would just be several instances of the app running under a single AD/regular user on a few computers where users share a pc.

# ? Oct 22, 2009 20:23

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

I'm sure a billion people are about to correct me, but I think the usual way to do this is to have user and role tables, rather than using the built in DB users and roles, especially if the app is web-based and/or you want to be as DB agnostic as possible. This way, all connection to the app is done using an administrative-type service account with the proper permissions. So, when someone connects, they log in to the app using their personal account information, but the connection between the app and the db uses the service account.

Also, don't store plain-text passwords.

# ? Oct 22, 2009 21:02

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Jethro posted:

I'm sure a billion people are about to correct me, but I think the usual way to do this is to have user and role tables, rather than using the built in DB users and roles, especially if the app is web-based and/or you want to be as DB agnostic as possible. This way, all connection to the app is done using an administrative-type service account with the proper permissions. So, when someone connects, they log in to the app using their personal account information, but the connection between the app and the db uses the service account.

Also, don't store plain-text passwords.

Yes this is pretty much was i was saying. You will have to setup your own roles and permissions for everything you want in your program that is totally decoupled from the sql user part of things.

# ? Oct 22, 2009 21:38

wigga please: Nov 1, 2006; by mons all madden

Jethro posted:

I'm sure a billion people are about to correct me, but I think the usual way to do this is to have user and role tables, rather than using the built in DB users and roles, especially if the app is web-based and/or you want to be as DB agnostic as possible. This way, all connection to the app is done using an administrative-type service account with the proper permissions. So, when someone connects, they log in to the app using their personal account information, but the connection between the app and the db uses the service account.

Also, don't store plain-text passwords.

So basically you mean disabling/enabling parts of my windows forms that a given user shouldn't have acces to when he loads a form? Or altering my data layer classes to use some sort of permissions system? I do have a "CurrentUser" statvar so this would be feasible.

# ? Oct 23, 2009 07:36

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

wigga please posted:

So basically you mean disabling/enabling parts of my windows forms that a given user shouldn't have acces to when he loads a form? Or altering my data layer classes to use some sort of permissions system? I do have a "CurrentUser" statvar so this would be feasible.

Yes pretty much what it looked like what you were trying to do was each user login have a sql user with a set of permissions that controlled what they could do in the application if you tied that in with their login info so its independent of your database it can do what you want.

# ? Oct 23, 2009 08:05

wigga please: Nov 1, 2006; by mons all madden

Sprawl posted:

Yes pretty much what it looked like what you were trying to do was each user login have a sql user with a set of permissions that controlled what they could do in the application if you tied that in with their login info so its independent of your database it can do what you want.

So what if I implement half of this and make 10 logins (9 + service login to fetch user), and make separate Connect methods in the application?

I now have:

code:

    public static class Connect
    {
        public static SqlConnection GetConnection()
        {
            SqlConnection cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
                + "Persist Security Info=True;User ID=POSUser;Password=******");
            return cnc;
        }
    }

and maybe could replace that with

code:

        public static SqlConnection GetConnectionForLevel()
        {
            SqlConnection cnc;
            switch (User.LoggedInUser.Level)
            {
                case 0:
                    cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
               + "Persist Security Info=True;User ID=POSAdmin;Password=********");
                break;
                case 1:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSOwnerr;Password=********");
                break;
                case 2:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSManager;Password=********");
                break;
                case 3:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSAccountant;Password=********");
                break;
                case 5:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSWarehouse;Password=********");
                break;
                case 6:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSSales;Password=********");
                break;
                case 7:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSService;Password=********");
                break;
                case 8:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSBasicSales;Password=********");
                break;
                default:
                cnc = new SqlConnection("Data Source=VISTA;Initial Catalog=POSDB;"
           + "Persist Security Info=True;User ID=POSGuest;Password=********");
                break;
            }

            return cnc;
        }

# ? Oct 23, 2009 09:10

Adbot: ADBOT LOVES YOU

# ? Jun 3, 2024 20:58

Dietrich: Sep 11, 2001

I have a stored procedure that I want to run about every 5 minutes on a SQL 2005 enterprise server.

I thought I could just create a schedule that would run it at that frequency, but that doesn't seem to exist- the lowest resolution is daily. So I thought I'd set it to run whenever the CPU's were idle, but I don't know how often that is, and it's important that this runs with a consistent frequency.

Am I attacking this the wrong way? How should I set this up so that it runs every 5 minutes?

EDIT:

HOLY poo poo I'M BLIND. Forget you read this.

# ? Oct 23, 2009 15:07

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »