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 »

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

Subotai posted:

I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use.

In the vein of what var1ety posted:

datediff(s,'01-Jan-1970',getutcdate())

E:F,B

# ? Mar 18, 2009 21:44

Adbot: ADBOT LOVES YOU

# ? Jun 5, 2024 17:49

eHacked: Sep 30, 2003; CONGRATULATIONS!!! YOU ARE THE 6,127,436,218TH PERSON TO VIEW THIS USELESS POST. CLICK TO CLAIM YOUR PRIZE!!!

goddamnit nm!

eHacked fucked around with this message at 17:58 on Mar 19, 2009

# ? Mar 19, 2009 17:51

Morpheus: Apr 18, 2008; My favourite little monsters

This is a really easy one, but nigh unsearchable on Google: What does AS do in an Oracle command? For example:

code:

SELECT columnName AS otherName FROM table WHERE thing=value;

# ? Mar 19, 2009 21:03

var1ety: Jul 26, 2004

Morpheus posted:

This is a really easy one, but nigh unsearchable on Google: What does AS do in an Oracle command? For example:
code:
SELECT columnName AS otherName FROM table WHERE thing=value;

Renames columns.

You might be interested in Oracle's SQL reference document. Say one thing for Oracle - say that they produce very thorough and verbose documentation.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065646

# ? Mar 19, 2009 21:11

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Morpheus posted:

This is a really easy one, but nigh unsearchable on Google: What does AS do in an Oracle command? For example:
code:
SELECT columnName AS otherName FROM table WHERE thing=value;

Specifically, in a totally non-technical way, it assigns a column name to the value. The value is the value of the expression "columnName" (or it could be something more complex like "columnName + 1". Most people ignore this since for simple expressions, the column gets autonamed with the column used in the expression. But if you use two column names to calculate a new value, the column won't actually be named anything, which is okay for certain operations. For others, you absolutely do need a name for the colum.

# ? Mar 20, 2009 03:43

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

How can you speed up a query such as the following? (MySQL)

code:

SELECT vote.articleId, SUM(vote.value) AS votes
FROM vote
WHERE vote.timestamp > FROM_UNIXTIME(1205996595)
GROUP BY vote.articleId
ORDER BY votes DESC
LIMIT 0 , 30

# ? Mar 20, 2009 08:42

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Add a clustered index

articleId

And an index

timestamp, value

And then make sure that this is actually works. (Sample runtime before, and then after.)

# ? Mar 20, 2009 21:06

Victor: Jun 18, 2004

~~I dunno about MySQL, but with MSSQL, clustered indexes must be unique. vote.articleId is clearly not unique.~~

The above is wrong -- I must have skimmed the CREATE INDEX documentation and miscomprehended the "unique".

Victor fucked around with this message at 02:57 on Mar 21, 2009

# ? Mar 21, 2009 00:00

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Victor posted:

clustered indexes must be unique

False.

# ? Mar 21, 2009 02:06

Bad Titty Puker: Nov 3, 2007; Soiled Meat

I'd like to humbly interject that it's hard to give good advice without a minimum of:

*DDL, including keys and indexes

*A query execution plan.

Sample data would also be nice to have.

# ? Mar 21, 2009 03:10

Rocko Bonaparte: Mar 12, 2002; Every day is Friday!

I have a MySQL administrative question for Linux. I keep getting bad rows in a table I'm using for a toy app I'm running, but I seem to notice new problems that require a repair after booting up. I assume then that I'm improperly shutting down my system with mysql install. Generally I just run 'sudo halt' and wash my hands of it. Should I be shutting down in another manner to elegantly terminate SQL and make sure it's happy before halting the system?

# ? Mar 22, 2009 06:56

DTMTCM: Feb 18, 2005; I misclicked post icon so I have this account now.

So, let's say I have a table of customers and each customer needs to get an order number that is only unique to that customer (customerID=1 would have customerOrderID=1,2,3..etc while customerID=2 would have customerOrderID=1,2,3,... in the same table). I'd imagine determining the next customerOrdeID would just using MAX() (or COUNT()) on the column and incrementing it by 1, but what would be easiest way to do this to prevent concurrency problems?

I was thinking making this a stored procedure and using a transaction, but if I did this, what isolation level would be sufficient (SQL Server 2005)?

# ? Mar 22, 2009 18:50

kaniff: Feb 27, 2004; oh word?

Rocko Bonaparte posted:

I have a MySQL administrative question for Linux. I keep getting bad rows in a table I'm using for a toy app I'm running, but I seem to notice new problems that require a repair after booting up. I assume then that I'm improperly shutting down my system with mysql install. Generally I just run 'sudo halt' and wash my hands of it. Should I be shutting down in another manner to elegantly terminate SQL and make sure it's happy before halting the system?

This can vary by distribution, but most SystemV style distributions execute a series of shutdown scripts at the runlevel 0 and 6 for (halt and reboot respectively), they are located in /etc/rc.d/, usually.

The most common problem is the lack of an entry in either 0, 6, or both.

# ? Mar 22, 2009 19:36

Roundboy: Oct 21, 2008

This type of problem comes up enough for me, and I am getting sick of all the manual work it takes to get the format I need.

I am looking at a list of matches made by our system, and i specifically want to see where there are cases where multiple ID's match to a single secondary unique ID:

code:

CUSTOMER_ID     CLIENT_ID
H123            CL_122
H334            CL_122
H345            CL_998
H991            CL_998
H222            CL_812
H112            CL_812
H003            CL_812

I think the above is straightforward. But what I need to do is break out all the different customer_id's out by client_id like :

code:

CLIENT_ID     CUS_1     CUS_2     CUS_3
CL_122        H123      H334
CL_998        H345      H991
CL_812        H222      H112      H003

I have tried using rank() over (partition by client_id order by customer_id) as rank to get a unique position number for each customer_id, but it is still a manual process to extract all rank1 ids as ID1, all rank 2 ids as ID2, etc .. Made more complex by some having 3,4, even 5 ids connected to one client_id.

i have tried various forms of joins, etc, but it just ends up with me doing a LOT of temp tables with the ranks seperated, and then just a few joins, and then one big union, and then distinct across the whole thing.

There has got to be a better way.. but my goggle and oracle foo is failing me.. Ideas?

# ? Mar 25, 2009 18:36

var1ety: Jul 26, 2004

Roundboy posted:

This type of problem comes up enough for me, and I am getting sick of all the manual work it takes to get the format I need.

If you have Oracle 11 you can use the pivot operator. If you have pre-11 you can synthesize a result using a pattern following

code:

select client_id,
       max(case when rn = 1 then customer_id) AS cus_1,
       max(case when rn = 2 then customer_id) AS cus_2,
       max(case when rn = 3 then customer_id) AS cus_3
  from (select client_id,
               customer_id,
               row_number() OVER(partition by client_id order by customer_id) AS rn
          from table)
 group by client_id
 order by 1

You need to decide when you are building your query how many customer_ids you will allow per client_id if you are using this method.

# ? Mar 25, 2009 18:49

Roundboy: Oct 21, 2008

var1ety posted:

If you have Oracle 11 you can use the pivot operator. If you have pre-11 you can synthesize a result using a pattern following
code:
select client_id,
       max(case when rn = 1 then customer_id) AS cus_1,
       max(case when rn = 2 then customer_id) AS cus_2,
       max(case when rn = 3 then customer_id) AS cus_3
  from (select client_id,
               customer_id,
               row_number() OVER(partition by client_id order by customer_id) AS rn
          from table)
 group by client_id
 order by 1
You need to decide when you are building your query how many customer_ids you will allow per client_id if you are using this method.

This worked quite well (but you are missing 'end' in those case statements. i determined that I only need a max of '4' rownums, but the majority is just 1&2.

Thanks, I don't know why this was so complex to figure out.

# ? Mar 25, 2009 19:47

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

Looking to reduce my app to a single query, but I have one difficulty. Say I have a table "users":

code:

USER | ID
---------
Fred | 1
Joe  | 2
Hank | 3

and another, "status":

code:

ID | STATUS
-----------
1  | 1
1  | 22
2  | 3
3  | 1

What I want to do is nearly "select users.USER, users.ID from users, status where status=1" except I don't want any users who have a status not equal to 1 anywhere in the "status" table. So what I really want as output for this example is just Hank, 3.

It's an Oracle 9 database, if that matters.

# ? Mar 25, 2009 22:48

Roundboy: Oct 21, 2008

FeloniousDrunk posted:

Looking to reduce my app to a single query, but I have one difficulty. Say I have a table "users":
code:
USER | ID
---------
Fred | 1
Joe  | 2
Hank | 3
and another, "status":
code:
ID | STATUS
-----------
1  | 1
1  | 22
2  | 3
3  | 1
What I want to do is nearly "select users.USER, users.ID from users, status where status=1" except I don't want any users who have a status not equal to 1 anywhere in the "status" table. So what I really want as output for this example is just Hank, 3.

It's an Oracle 9 database, if that matters.

select a.user,a.id,b.status
from USER a
inner join STATUS b on a.ID = b.ID and b.status = '1'

But, I can see that there are TWO users with a status of '1', Fred and Hank .. what other limiters do you have ? The above will return all users with a status of 1

# ? Mar 26, 2009 00:56

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

Roundboy posted:

select a.user,a.id,b.status
from USER a
inner join STATUS b on a.ID = b.ID and b.status = '1'

But, I can see that there are TWO users with a status of '1', Fred and Hank .. what other limiters do you have ? The above will return all users with a status of 1

Yeah, I think I need a subquery somehow to kick out the unwanted users. I'm messing around with things like:

code:

select a.user,a.id,b.status, 
(select b.id foo_id, max(b.status) foo_stat from status b group by b.id) foo
     from USER a
     inner join STATUS b on a.ID = b.ID and b.status = '1'
     where foo.foo_id = a.ID and foo.foo_stat=1

but not having much luck. The real query is a lot bigger and the data set is pretty huge.

# ? Mar 26, 2009 01:13

Bad Titty Puker: Nov 3, 2007; Soiled Meat

FeloniousDrunk posted:

Looking to reduce my app to a single query, but I have one difficulty. Say I have a table "users":
code:
USER | ID
---------
Fred | 1
Joe  | 2
Hank | 3
and another, "status":
code:
ID | STATUS
-----------
1  | 1
1  | 22
2  | 3
3  | 1
What I want to do is nearly "select users.USER, users.ID from users, status where status=1" except I don't want any users who have a status not equal to 1 anywhere in the "status" table. So what I really want as output for this example is just Hank, 3.

It's an Oracle 9 database, if that matters.

Something like this (untested)?

code:

SELECT users.USER, users.ID 
FROM Users
WHERE EXISTS 
  (SELECT * FROM status 
   WHERE status.ID = Users.ID 
   AND status.Status = 1 
   AND NOT EXISTS 
    (SELECT * FROM status s2 WHERE s2.ID = status.ID AND s2.status <> 1))

# ? Mar 26, 2009 01:50

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

camels posted:

Something like this (untested)?

code:

SELECT users.USER, users.ID 
FROM Users
WHERE EXISTS 
  (SELECT * FROM status 
   WHERE status.ID = Users.ID 
   AND status.Status = 1 
   AND NOT EXISTS 
    (SELECT * FROM status s2 WHERE s2.ID = status.ID AND s2.status <> 1))

This looks reasonable, though I think maybe it will be more efficient if I turn it inside-out. Though maybe not: are subqueries executed before the outside query, or is there magic involved?

I might as well share the real situation. Here's the main thing:

code:

     	select
              	ITEM.PERM_LOCATION,
                ITEM.CREATE_DATE,
                BIB_MASTER.BIB_ID,
                BIB_MASTER.LIBRARY_ID,
                BIB_DATA.RECORD_SEGMENT,
                ITEM.ITEM_ID
        from
            	ITEM, MFHD_ITEM, BIB_MFHD, BIB_MASTER, BIB_DATA
        where
             	ITEM.ITEM_ID > :itemid
                and ITEM.CREATE_DATE > to_date(:extractdate,'YYYYMMDD')
                and ITEM.CREATE_DATE <= to_date(:untildate,'YYYYMMDD')
                and ITEM.COPY_NUMBER = 1
                and MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID
                and BIB_MFHD.MFHD_ID = MFHD_ITEM.MFHD_ID
                and BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID
                and BIB_DATA.BIB_ID = BIB_MASTER.BIB_ID
                and ITEM.ITEM_TYPE_ID not in(1,3,11,12,19,38)

over the results of which I'm iterating

code:

select MAX(I.ITEM_STATUS) M from ITEM_STATUS I WHERE I.ITEM_ID=:itemid

and rejecting everything where M <> 1. In each initial query I can expect around 3000 results. Obviously doing 3000 queries is bad. But if the subquery is run over the whole 4 million item database, that takes a lot of time too. So it is better to put the main query as I have it in a subquery and then pass stuff to the outside query?

# ? Mar 26, 2009 06:02

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Well, as a general rule a set-based query is going to outperform a row-at-a-time solution. If you write the row-based query as a PL/SQL script - I don't know if you can see the explain plan for the whole thing, but if you can, you can compare it to the plan for the single-query approach. Ideally, you can write it both ways and run a trace, and compare reads and CPU.

The explain plan for the single-query approach will answer your questions about how the database engine puts it together. If you don't mind posting DDL and especially indexes, along w/ the test results, it sounds interesting. I'm guessing that you will get a scan on "ITEM" (not to be annoying, but that is a p. bad name for a table) because of this:

code:

and ITEM.ITEM_TYPE_ID not in(1,3,11,12,19,38)

Also, you have some leeway as to how to write the single-query: for example, a joined subquery, a subquery with EXISTS in the main WHERE clause, or nested SELECT statements.

# ? Mar 26, 2009 09:26

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

camels posted:

Helpful things

OK, you definitely have it over me in geek points. I don't know the first thing about PL/SQL or what a DDL is, really I'm just a web developer dealing with a monstrous integrated library system database ("ITEM" was not my decision -- there's more cryptic stuff than that in the 403 friggin' tables). I do have this "TOAD" thing that certainly looks like it should give me what you ask, though, if I only knew where to look. Heck, it might even have a query optimizer.

Anyhow, this is what I came up with this morning:

code:

select
        sq.pl PERM_LOCATION,
        sq.cd CREATE_DATE,
        sq.bi BIB_ID,
        sq.li LIBRARY_ID,
        sq.rs RECORD_SEGMENT,
        sq.id ITEM_ID
from
        ITEM_STATUS,
        (select
         ITEM.PERM_LOCATION pl,
         ITEM.CREATE_DATE cd,
         BIB_MASTER.BIB_ID bi,
         BIB_MASTER.LIBRARY_ID li,
         BIB_DATA.RECORD_SEGMENT rs,
         ITEM.ITEM_ID id
        from
         ITEM, MFHD_ITEM, BIB_MFHD, BIB_MASTER, BIB_DATA
        where
         ITEM.ITEM_ID > :itemid
         and ITEM.CREATE_DATE > to_date(:extractdate,'YYYYMMDD')
         and ITEM.CREATE_DATE <= to_date(:untildate,'YYYYMMDD')
         and ITEM.COPY_NUMBER = 1
         and MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID
         and BIB_MFHD.MFHD_ID = MFHD_ITEM.MFHD_ID
         and BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID
         and BIB_DATA.BIB_ID = BIB_MASTER.BIB_ID
         and ITEM.ITEM_TYPE_ID not in(1,3,11,12,19,38)
        ) sq
where
        sq.id=ITEM_STATUS.ITEM_ID
        and ITEM_STATUS.ITEM_STATUS = 1
        and not exists 
         (select * from ITEM_STATUS s where s.ITEM_ID=sq.id and s.ITEM_STATUS <> 1)

To add to the fun, the result set maxes out the allocated memory on the web server if I run it over a full year of data, so it's split into 10-day chunks therefore I repeat this query 37 times. Also, this is happening over a remote connection (well, over a university LAN).

Results (times are cumulative):

code:

2727 raw records, 2.905s
2854 raw records, 6.847s
3356 raw records, 10.425s
2626 raw records, 13.411s
5209 raw records, 18.296s
[.. 32 more omitted ...]
113516 raw records in 160.506s

For comparison, I tried varying the chunk size --
20 days: 113512 raw records in 159.596s
5 days: 113509 raw records in 171.720s

I'm assuming the variation in number of records returned is because it's a live database. If I recall correctly the previous looping approach took about 4 minutes, so this is an improvement. I still feel it could be better though...

# ? Mar 26, 2009 18:08

Bad Titty Puker: Nov 3, 2007; Soiled Meat

DDL is the CREATE TABLE statements for the tables, including any indexes and foreign keys. PL/SQL is just Oracle's proprietary brand of SQL; I speculated that you could write your looping query in it and get trace results that way. In Oracle the command EXPLAIN PLAN FOR (sql statement) will show the query plan that the optimizer came up with. btw TOAD is awesome, I actually used to work for the company that makes it (Quest Software) but on a different product that only did SQL tuning. I haven't played with it for over ten years, but I think it has some handy ways to do some of the above things.

Anyway, something tells me that the DDL would fill several reams of paper though. Here's something -- and I am not sure that it is equivalent to your query -- but it's worth a shot. Can you combine the outer query into the subquery, something like this:

code:

      select
         ITEM.PERM_LOCATION pl,
         ITEM.CREATE_DATE cd,
         BIB_MASTER.BIB_ID bi,
         BIB_MASTER.LIBRARY_ID li,
         BIB_DATA.RECORD_SEGMENT rs,
         ITEM.ITEM_ID id
        from
         ITEM, MFHD_ITEM, BIB_MFHD, BIB_MASTER, BIB_DATA, ITEM_STATUS
        where
         ITEM.ITEM_ID > :itemid
         and ITEM.CREATE_DATE > to_date(:extractdate,'YYYYMMDD')
         and ITEM.CREATE_DATE <= to_date(:untildate,'YYYYMMDD')
         and ITEM.COPY_NUMBER = 1
         and MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID
         and BIB_MFHD.MFHD_ID = MFHD_ITEM.MFHD_ID
         and BIB_MASTER.BIB_ID = BIB_MFHD.BIB_ID
         and BIB_DATA.BIB_ID = BIB_MASTER.BIB_ID
         and ITEM.ITEM_TYPE_ID not in(1,3,11,12,19,38)
         and ITEM_STATUS.ITEM_ID = ITEM.ITEM_ID
         and ITEM_STATUS.ITEM_STATUS = 1
         and not exists 
         (select * from ITEM_STATUS s where s.ITEM_ID=ITEM.ITEM_ID
           and s.ITEM_STATUS <> 1)

# ? Mar 26, 2009 18:55

var1ety: Jul 26, 2004

FeloniousDrunk posted:

This looks reasonable, though I think maybe it will be more efficient if I turn it inside-out. Though maybe not: are subqueries executed before the outside query, or is there magic involved?

I might as well share the real situation. Here's the main thing:

The best approach to take is going to depend on your item distribution and the indexes you have available (and whether or not you can create new ones).

I would try starting by retrieving all the items which match your "has status = 1 and no other status" qualifier, and then pare this list down in successive filter operations.

code:

select id
  from (select id from item_status where status = 1) i
 where not exists (select null
          from item_status
         where id = i.id
           and status > 1)

var1ety fucked around with this message at 19:13 on Mar 26, 2009

# ? Mar 26, 2009 19:02

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

camels posted:

DDL is the CREATE TABLE statements for the tables, including any indexes and foreign keys. PL/SQL is just Oracle's proprietary brand of SQL; I speculated that you could write your looping query in it and get trace results that way. In Oracle the command EXPLAIN PLAN FOR (sql statement) will show the query plan that the optimizer came up with. btw TOAD is awesome, I actually used to work for the company that makes it (Quest Software) but on a different product that only did SQL tuning. I haven't played with it for over ten years, but I think it has some handy ways to do some of the above things.

Anyway, something tells me that the DDL would fill several reams of paper though. Here's something -- and I am not sure that it is equivalent to your query -- but it's worth a shot. Can you combine the outer query into the subquery, something like this:
code:
...

Ooh, 113465 raw records in 178.945s for 20-day chunks. Pity, because yours looks like it should be more efficient. Maybe it's just server load; I'll keep yours for now. It only runs twice a day, so three minutes should be OK.

I poked the TOAD and it doesn't seem to be able to improve on yours ("best alternative execution plan" is blank).

# ? Mar 26, 2009 19:54

Ciaphas: Nov 20, 2005; > BEWARE, COWARD

This is a horribly vague question and I apologize for that, but I'm not a database programmer and our DB guys are out until Tuesday so I'm uncertain what to google for. (I'm a C++ programmer who doesn't know a whole hell of a lot about databases.)

Is it as insane as I think it is to want to store SQL queries as a string in a database table? I'm in a situation where I may need to look up an appropriate query for certain situations, rather than hardcoding it into my program. So just a table with two columns, an index and a varchar or whatever containing the query I want, if that makes sense.

# ? Mar 26, 2009 23:13

No Safe Word: Feb 26, 2005

Ledneh posted:

This is a horribly vague question and I apologize for that, but I'm not a database programmer and our DB guys are out until Tuesday so I'm uncertain what to google for. (I'm a C++ programmer who doesn't know a whole hell of a lot about databases.)

Is it as insane as I think it is to want to store SQL queries as a string in a database table? I'm in a situation where I may need to look up an appropriate query for certain situations, rather than hardcoding it into my program. So just a table with two columns, an index and a varchar or whatever containing the query I want, if that makes sense.

Generally you want to just create stored procedures to do the things you want. Why would you need to "look up an appropriate query"? If they were well-named stored procedures, you'd just choose the right one.

# ? Mar 26, 2009 23:17

Ciaphas: Nov 20, 2005; > BEWARE, COWARD

No Safe Word posted:

Generally you want to just create stored procedures to do the things you want. Why would you need to "look up an appropriate query"? If they were well-named stored procedures, you'd just choose the right one.

It's the choosing the right one part that's... well, normally I'd just hardcode in the query to use for a given situation in my program. But the Powers That Be have raised the question "Why don't we use a DB lookup to determine the correct stored procedure instead of hardcoding it, so we can extend the program without having to change or recompile it?", and I'm trying to provide an answer either way. And they have a point, because there are a LOT of "given situations" that would take me an aeon to describe in text. But anyway.

My first thought was "ok, just make a table of IDs to SQL queries" (or I guess stored procedures). My second thought was "wait, that's loving stupid." My third thought was "I don't know dick about databases :smith:

" and so I came here.

That make any sense?

# ? Mar 26, 2009 23:26

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Ledneh posted:

"Why don't we use a DB lookup to determine the correct stored procedure instead of hardcoding it, so we can extend the program without having to change or recompile it?"

Because that's retarded. You cannot "look up in a database" what query you want to run for an arbitrary number of situations. If you find that your product matches a certain type of database (and it probably does) like OLTP/ORM access, then you can probably offload your poo poo on to a framework.

Sounds like you (collectively) are in over your heads and need someone to start specializing in DB ish.

# ? Mar 27, 2009 00:10

chocojosh: Jun 9, 2007; D00D.

Ledneh posted:

"Why don't we use a DB lookup to determine the correct stored procedure instead of hardcoding it, so we can extend the program without having to change or recompile it?"

My understanding is that you want to be able to dynamically change the SQL used in a DB lookup.

If you have a fixed set of values then you can use a lookup scheme as you're thinking of. Of course, each time you want a new query you're going to have to add it to the database. In my opinion this solution is relatively simple but there isn't much to gain except for avoiding compiles.

If however you want to dynamically build some SQL for different situations then that isn't so hard. You need to figure out how you're going to represent your SQL in a format that doesn't require compilation and that can be read in dynamically (say as an external XML file). You could either simply type in the SQL into an XML file, or you can use some other type of representation that can be converted to SQL. Then, you can read in the SQL and execute it (lookup prepared statements). This has more to gain in my opinion because you can create completely new queries without having to go into the DB and make stored procedures.

# ? Mar 27, 2009 12:02

illamint: Jun 26, 2005; According to The Oxford English Dictionary, the word "snapshot" was originally a hunting term.

I've got a solution I'm pondering in MySQL. I've got a page in PHP that's displaying a list of rows from a MySQL database, which have a one-to-many relationship to other tables in the database (it's actually a list of college courses, which can have multiple crosslistings, multiple professors, etc. associated with them). What I'm trying to figure out is what's the most efficient way to pull those relations into the view (i.e., print out that course XXX123 is taught by professors A and B and is crosslisted as YYY456, for each course XXX123 in a list of many). I can do it pretty easily with a LEFT JOIN and GROUP_CONCAT, but, say, if I want to add links to a page based on their primary keys, it gets ugly, but I think I can still do this (hackishly) with GROUP_CONCAT. The only other possibility I can think of is running another query for each individual item, but I'd rather hack about with GROUP_CONCAT since the overhead of calling that many queries definitely isn't worth it. Then again though, I can't see any other way than the two I've outlined. Any thoughts?

# ? Mar 27, 2009 18:19

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I don't understand. Do two LEFT JOINs?

# ? Mar 27, 2009 19:03

illamint: Jun 26, 2005; According to The Oxford English Dictionary, the word "snapshot" was originally a hunting term.

Triple Tech posted:

I don't understand. Do two LEFT JOINs?

It's not that I can't get the relation the way I want it, it's just that actually displaying what I want feels like a kludge. Like, in my output, I want to list each of the courses, and if they have crosslistings or multiple professors (each of which consists as one row in a separate table), I want to print them, individually, on each course. With a LEFT JOIN I can have it display something like "Cross-listed as XXX123, YYY456" via the use of GROUP_CONCAT(xl_d.`abbreviation`, COALESCE(xl.`new_course_number`, c.`number`) SEPARATOR ', ') AS `crosslistings`, but if I wanted to link XXX123 and YYY456, say, to their individual course pages, I feel like I'd either have to do something nasty with the GROUP_CONCAT clause like adding in either Smarty tags to link to the primary key or a link itself, and throwing display stuff into my query like that is something I'd rather avoid, but I guess there's no other way to get, say, a PHP array of crosslistings I can iterate over for each course in the list without doing a completely separate MySQL query for each individual item on the page, which is a lot of overhead for something fairly minor.

# ? Mar 27, 2009 19:15

Potassium Problems: Sep 28, 2001

I have a Contacts table which has fields for ID, [ForeignKey], Name, Title, Phone, EMail & a boolean IsPrimary, which (duh) signals that this is the contact to notify first if need be. The FK is One-To-Many with the Contacts table being the many, so is there a way to set up an index to only allow 1 IsPrimary to be set to true for a foreign key?

edit: MSSQL 2005

Potassium Problems fucked around with this message at 19:58 on Mar 27, 2009

# ? Mar 27, 2009 19:54

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I don't understand your example. Foreign key to what? You only mention one object, a Contact.

An index is something you add to a table to make reading from it faster. An exception case is a uniqueness constraint, which is implemented by a unique index. Conflated in SQL Server, but still two different concepts. I can't think of a query off the top of my head, but you can definitely enforce a "constraint" with a trigger, with which there are less limits on query complexity.

# ? Mar 27, 2009 20:34

Potassium Problems: Sep 28, 2001

The primary table is Clients, with the unique ID for the Clients table being the FK in the Contacts table. I tried a unique constraint, but it ended up only allowing 2 Contacts per Client; one with IsPrimary set to true, and one with IsPrimary set to false. I'm thinking it might be easier to enforce this in my data layer, but I'll look into a constraint with a trigger.

# ? Mar 27, 2009 21:07

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

My web application allows people to play a board game against each other over the internet in a play-by-email type of format. There are two tables we are concerned with, Game and User. There are five different colours or "seats" for players in the game: Red, Yellow, Green, Purple, and White. A row in the Game table stores information on who is playing in each game as follows: there are five columns PlayerR, PlayerY, PlayerG, PlayerP and PlayerW which store the user ID of the player in that seat, or are null if that seat is unfilled. So for example we might have

code:

PlayerR   PlayerY   PlayerG   PlayerP   PlayerW
34        NULL      2         789       NULL

When somebody accesses the page for a game (to view the game's current status), or one of the players submits a move in the game, it is necessary to fetch certain information about the game's players along with information about the game (the players' usernames, whether they are male or female, the values they have set for certain preferences, etc). So the query I use at present looks something like the following:

code:

SELECT Game.*,
       PlayerR.Name AS NameR, PlayerR.IsAdmin AS IsAdminR, (other stuff),
       PlayerY.Name AS NameY, PlayerY.IsAdmin AS IsAdminY, (other stuff),
       PlayerG.Name AS NameG, PlayerG.IsAdmin AS IsAdminG, (other stuff),
       PlayerP.Name AS NameP, PlayerP.IsAdmin AS IsAdminP, (other stuff),
       PlayerW.Name AS NameW, PlayerW.IsAdmin AS IsAdminW, (other stuff)
       FROM Game LEFT JOIN User AS UserR ON Game.PlayerR = UserR.UserID
                 LEFT JOIN User AS UserY ON Game.PlayerY = UserY.UserID
                 LEFT JOIN User AS UserG ON Game.PlayerG = UserG.UserID
                 LEFT JOIN User AS UserP ON Game.PlayerP = UserP.UserID
                 LEFT JOIN User AS UserW ON Game.PlayerW = UserW.UserID
       WHERE Game.GameID = (some number)

PHP then goes through a process of putting the returned information into arrays, e.g. $Name will be an array of 5 whose elements are the NameR, NameY, NameG, NameP and NameW returned in the query results.

My question is, is this method of using five LEFT JOINs makedly inefficient and is there a much better way of doing it?

Using MySQL.

# ? Mar 28, 2009 12:54

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Stack the user ids vertically in the user id/game id xref table.

code:

game_id  player_id  player_color
123      34         R
123      2          G
123      789        P

Cluster index by game_id, player_id. Then the join to the player table is only a single join.

# ? Mar 28, 2009 13:28

Adbot: ADBOT LOVES YOU

# ? Jun 5, 2024 17:49

TagUrIt: Jan 24, 2007; Freaking Awesome

I'm toying around with writing something to visually represent a lot of data, and I figure a database might be a good way to do the actual storage of the data. Having never working with databases before, though, I have a few questions:

1. I'm going to need to use Foreign Keys to represent the links between different sets of data (students with schools, for example). However, there are at least a few relations in my data that could need multiple FKs in the same field (i.e. having 2 co-valedictorians). Is there a way to have one column have an arbitrary number of FKs in it? If not, do you have any suggestions for dealing with stuff like this?

2. Is there a way to make a "Last Updated" field for every row that updates anytime something in that row is changed? Rows get updated individually and periodically; it'd be nice if I could find which entries are potentially the most outdated.

3. Do you guys have any preferred gui editors/admin things? I was poking around with OpenOffice Database, but I couldn't figure out how to do FKs, which is probably due to my lack of sleep.

# ? Mar 29, 2009 09:09

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

«‹›303 »