PDA

View Full Version : This is why I love SQL



tofu
2005-Apr-11, 12:20 AM
SELECT case when PacketsIn is NULL then 0 else PacketsIn end as PacketsIn

not that anybody will care. I'm just polluting the board with a senseless post but since I'm working late on a Sunday night I just thought I'd post that. It's convoluted, but there's something beautiful about it. I think I'll go home now.

Maksutov
2005-Apr-11, 12:28 AM
SELECT case when PacketsIn is NULL then 0 else PacketsIn end as PacketsIn

not that anybody will care. I'm just polluting the board with a senseless post but since I'm working late on a Sunday night I just thought I'd post that. It's convoluted, but there's something beautiful about it. I think I'll go home now.
Nah, it's not convoluted, it's actually pretty well-structured, although some might query you about your language. :wink:

mickal555
2005-Apr-11, 03:28 AM
Bleah I hate SQL!


It's driving me nuts!

arghhhhhhhhhhhhhhh

Celestial Mechanic
2005-Apr-11, 04:11 AM
I write reasonably decent SQL and use it at work, although my privileges only extend to querying the database (which is just as well!)

I am now trying to install PostGreSQL 7.3.2 on my new main computer, which runs eComStation 1.2 (a latter-day distribution of OS/2). Things have not gone well. Many of the problems stem from the fact that this is a port of a UNIX program to OS/2, and so too many UNIXisms have been left in only to trip up the user. For example, there are scripts in OS/2 that act as wrappers around calls to the ksh program which then runs the realscripts that get things done, like initializing the database. Unfortunately that internal script keeps hanging up at the same spot. An alternate version of the script was provided on Hobbes that commented out a certain portion of the script in order to get it to work. This was known 4 years ago! Do you think that the people responsible for the port ever got the clue? No, so I had to go in and comment out the offending code in order to get the database created.

So I have gotten as far as creating a database (Yay!!!!) but now the server program claims it doesn't know which user to use. The default username is postgres, but somehow it doesn't know it or provide me with a means to enter it, because, well, this is OS/2 and OS/2 doesn't need no stinkin' "su" command!

mickal555
2005-Apr-11, 04:32 AM
well mine crashed for some reason.....

I have trouble intaslling SQL data bases...

Sigma_Orionis
2005-Apr-11, 01:28 PM
Porting Postgres outside of the Unix environment is rather hard since it does weird things with IPC. The Windoze port is plagued with such problems and I suspect that the OS/2 might have the same issues.

As for SQL what can I say, the lack of proper loops in Ansi-92 SQL (since it's non-procedural) is a pain in the behind, people complained so much that Oracle created an extension called PL-SQL (which includes "while do" Loops among other things) to be used in their stored procedures.

OTOH it's the closest thing to a standard when dealing with RDBMSs :-?

wedgebert
2005-Apr-11, 02:11 PM
If only T-SQL supported arrays...

I do have one big piece of advice for people with UPDATE/DELETE privileges: Don't forget that WHERE clause. Nothing liking having to e-mail the DBA saying "Hey, uh, when is the last update to the Foo table, I kind of deleted everything from it again..."

Sigma_Orionis
2005-Apr-11, 03:06 PM
If only T-SQL supported arrays...

I do have one big piece of advice for people with UPDATE/DELETE privileges: Don't forget that WHERE clause. Nothing liking having to e-mail the DBA saying "Hey, uh, when is the last update to the Foo table, I kind of deleted everything from it again..."

And pray that the DBA does NOT follow the BOFH guidelines, specially if your RDBMS has the Auto-commit option active........... :lol:

BTW Oracle's PL-SQL supports arrays (one dimensional IIRC) but they are a real loose cannon.

wedgebert
2005-Apr-11, 07:33 PM
Well, we're a Microsoft shop here. ASP and ASP.NET running on Windows 2000/2003 using VBScript and VB.NET with a SQL Server back end.

None of that Oracle stuff here. In fact, I'm a member of the I Hate Oracle Club (http://www.thedailywtf.com/ShowForum.aspx?ForumID=17)

http://www.thedailywtf.com/images/ihoc/Obstacle_hlf_2x.gif

Sigma_Orionis
2005-Apr-11, 08:01 PM
Nope I won't get suckered into the usual "my RDBMS is cooler than yours" trap, Unix Geek here, so I will not make any comments on MS :lol:

Nergal
2005-Apr-11, 08:04 PM
I do have one big piece of advice for people with UPDATE/DELETE privileges: Don't forget that WHERE clause. Nothing liking having to e-mail the DBA saying "Hey, uh, when is the last update to the Foo table, I kind of deleted everything from it again..."
This is exactly why my users only have UPDATE/DELETE privileges through pre-defined and validated ASP pages [-X

Of course none of my users even know what SQL is, so they obviously don't have much access beyond what I give them :wink:

Sigma_Orionis
2005-Apr-11, 08:07 PM
And to think that back in the late 80s/early 90s THE cool thing was to have your spreadsheet talk to your RDBMS via SQL, people actually thought that Upper Management was going to learn SQL to do ad-hoc reports......


How innocent were those days....... :o

Candy
2005-Apr-11, 08:17 PM
I use SQL at work. It's part of ATT's ICR. Pretty cool when I'm tracking down nuisance callers. :D

Sigma_Orionis
2005-Apr-11, 08:22 PM
Candy, you are not an Upper Management type :)

Any further comments on that particular segment of humanity could be considered offensive and an indirect Ad-Hominem so I will refrain on elaborating on THAT :D

Candy
2005-Apr-11, 08:29 PM
Candy, you are not an Upper Management type :)
Not yet! :wink:

I'm just Middle Management now, hence, the reason for returning to school. 8)

magimix
2005-Apr-12, 08:49 AM
If only T-SQL supported arrays...


Oh, I don't know. You could shoe-horn your array needs into #temp tables and cursors? :lol:

I love working with T-SQL, mainly because it is more interesting than anything else in my job at the moment. Managed to kill lots of time recently writing a set of data archival routines for our CRM system - 10,414 lines of T-SQL and counting.

I only hope I don't have to spend much time debugging it!

worzel
2005-Apr-12, 09:06 AM
SELECT case when PacketsIn is NULL then 0 else PacketsIn end as PacketsIn

not that anybody will care. I'm just polluting the board with a senseless post but since I'm working late on a Sunday night I just thought I'd post that. It's convoluted, but there's something beautiful about it. I think I'll go home now.
What about SELECT ISNULL(Packetsln,0) AS Packetsln, that's how I would have done it in sybase. I hate NULLS! (three valued logic does not feel natural), and I hate SQL! (relational algebra is so elegant in comparison)

If only T-SQL supported arrays...
I do have one big piece of advice for people with UPDATE/DELETE privileges: Don't forget that WHERE clause. Nothing liking having to e-mail the DBA saying "Hey, uh, when is the last update to the Foo table, I kind of deleted everything from it again..."
My colleague did this the other day and asked me if I could restore a table for him. I never could find a nice way to back up tables indivdually but I restored the entire database on a different server up to the last transaction dump (crontabbed hourly), created a remote table pointing to the live server and selected from the restore into the remote - I was well chuffed - as was he :)

Nergal
2005-Apr-12, 12:30 PM
I NULLS! (three valued logic does not feel natural)
A-darn-men. I have to deal with a lot of tri-value (yes,no,n/a) logic elements. I it with a passion.

worzel
2005-Apr-12, 12:56 PM
I NULLS! (three valued logic does not feel natural)
A-darn-men. I have to deal with a lot of tri-value (yes,no,n/a) logic elements. I it with a passion.
How many logical operators do you use? :)

AstroSmurf
2005-Apr-12, 01:32 PM
I've got it worse - I'm dealing with an application where all attributes can hold lists of values (even though most have only 0-1 values). And we want to do searches based on the _entire_ attribute value. This means that I try to shoehorn set algebra into SQL. Not a pretty sight, especially for the 'identical' comparison.

So instead of a simple '=' / '<>' pair, I have to deal with:
* one of : one of the attribute values matches one of the search arguments. A set intersection test, so to speak.
* none of: ditto, but has to be implemented in a wildly different way.
* exactly like: All values in the argument have to be present, and none other. This one I had a very hard time getting to work efficiently. And I still haven't created an inverse operator for it.

And that's just for the exact tests; then we have range tests, LIKE expressions and whatnot...

Fram
2005-Apr-12, 01:39 PM
I NULLS! (three valued logic does not feel natural)
A-darn-men. I have to deal with a lot of tri-value (yes,no,n/a) logic elements. I it with a passion.

Logical elements are the only ones where I can stand trivalue. I've gotten used to them when programming in Progress (True / False / ?), and I have no problem with them in SQL. I have much more problems with NULL in characters or numericals, as they ruin every comparison you try to make.

Candy
2005-Apr-12, 02:11 PM
Does anyone work with CTI? 8-[

magimix
2005-Apr-12, 02:47 PM
Does anybody have to work with databases that store stuff in a horrible format?

For example, here we use something called SupportCenter as our helpdesk system. Now, for historic reasons, the Supportcenter database doesn't use datetime columns for things like support-call creation dates. Rather it uses seperate Date and Time columns that are basically large integers, with an internal, Julian-esque format, meaning you have to convert the values into SQL format dates and times yourself.

For example:



create procedure MJLSupportCenterTimeToString @@Time int, @@STime varchar(21) output
as
begin
declare @hour1 int
declare @minute1 int
declare @second1 int

set @hour1 = (@@Time / 16777216)
set @minute1 = ((@@Time - (@hour1 * 16777216)) / 65536)
set @second1 = ((@@Time - (@hour1 * 16777216) - (@minute1 * 65536)) / 256)

set @@STime = ' ' + RTRIM(@hour1) + ':' + RTRIM(@minute1) + ':' + RTRIM(@second1)
end


CREATE PROCEDURE MJLSupportCenterDateToString @@JDATE int, @@TDATE varchar(64) OUTPUT
AS
begin
declare @day int
declare @month int
declare @year int
declare @tmonth varchar(20)

set @year = (@@JDATE / 65536)
set @month = ((@@JDATE - (@year * 65536)) / 256)
set @day = (@@JDATE) - (@year * 65536) - (@month * 256)

select @tmonth =
case @month
when 1 then 'JAN'
when 2 then 'FEB'
when 3 then 'MAR'
when 4 then 'APR'
when 5 then 'MAY'
when 6 then 'JUN'
when 7 then 'JUL'
when 8 then 'AUG'
when 9 then 'SEP'
when 10 then 'OCT'
when 11 then 'NOV'
when 12 then 'DEC'
END

set @@TDATE = RTRIM(@day) + '/' + RTRIM(@tmonth) + '/' + RTRIM(@year)
end


Anyway, I was bored and thought it would be fun to try and munge the conversion code into a select statement, to convert the Date and Time columns on the fly.

So *behold*! The rottenest, funkiest and flabbiest select statement I ever wrote that wasn't a stupidly long-winded set of multiple joins...



select callid_hdw, convert(datetime, convert(varchar(4), cast((calldat_hdw / 65536) as int)) + '/' + convert(varchar(2), cast(((calldat_hdw - (cast((calldat_hdw / 65536) as int) * 65536)) / 256) as int)) + '/' + convert(varchar(2), cast((calldat_hdw) - (cast((calldat_hdw / 65536) as int) * 65536) - (cast(((calldat_hdw - (cast((calldat_hdw / 65536) as int) * 65536)) / 256) as int) * 256) as int)) + ' ' + convert(varchar(2), cast(calltme_hdw / 16777216 as int)) + ':' + convert(varchar(2), cast(((calltme_hdw - (cast(calltme_hdw / 16777216 as int) * 16777216)) / 65536) as int)) + ':' + convert(varchar(2), cast(((calltme_hdw - (cast(calltme_hdw / 16777216 as int) * 16777216) - (cast(((calltme_hdw - (cast(calltme_hdw / 16777216 as int) * 16777216)) / 65536) as int) * 65536)) / 256) as int)), 102) as [datetime]
from call_hdw


I intentionally didn't try and format the statement, because hey, that's how wild I am :o

tofu
2005-Apr-12, 03:39 PM
I've got it worse - I'm dealing with an application where all attributes can hold lists of values

that's wrong. you should normalize that. I think that a lot of the problems that people have with SQL comes from improper database design.

Sigma_Orionis
2005-Apr-12, 04:07 PM
Now I remember why I quit Administering RDBMSs :lol: not having to deal with !"#"%$#%&$/&%(/& applications that for "historical" reasons have flawed designs

Sigma_Orionis
2005-Apr-12, 04:22 PM
Candy, you are not an Upper Management type :)
Not yet! :wink:

I'm just Middle Management now, hence, the reason for returning to school. 8)

Be careful, they will teach dreadful things like Emotional Intelligence and NeuroLinguistic Programming so you can treat your "collaborators" better (according to them). Beware also of the "Personal Growth Programs" (I mean who told those %$#&%$% instructors I wanted to grow? ) :lol:

tofu
2005-Apr-12, 07:16 PM
Hey, since we seem to have so many SQL lovers here, do you guys want to see what I think is a legitimate bug in MicroSoft SQLServer 2000? I haven't been able to test this on any other versions yet. The original query was very long, so I reduced it to the minimum necessary to recreate the problem - and I added a fictional scenario. I don't really work with a parking ticket database.

Anyway, here we go. A police department has a database that, among other things, records information about parking tickets given out by the officers. Here is the database and some sample data:



create table ParkingTickets (
TicketDate datetime,
IssuingOfficer int
)

insert into ParkingTickets values ('1/2/2005 12:31',1)
insert into ParkingTickets values ('1/2/2005 14:20',1)
insert into ParkingTickets values ('1/3/2005 12:05',1)
insert into ParkingTickets values ('1/3/2005 12:22',2)
insert into ParkingTickets values ('1/3/2005 14:01',2)
insert into ParkingTickets values ('1/4/2005 10:12',2)
insert into ParkingTickets values ('1/2/2005 12:10',3)
insert into ParkingTickets values ('1/4/2005 12:36',3)


So, as you can see, officer 1 gave out three tickets. Officer 2 gave out three, etc.

Now the problem: management wants a report containing the following information;
the Hour of Day,
the ID of the Issuing Officer,
the number of tickets issued by the officer during this hour during the week,
and the average number of tickets issued by all officers during this hour during the week

So, if the report is to cover 1/2/2005 through 1/5/2005, the report would look like this:


select
datepart(hh,TicketDate) as HourOfDay,
IssuingOfficer,
count(*) as TicketsIssuedByThisOfficer,
TotalTicketsForThisHour_AllOfficers = (
select convert(real, count(*))/3
from ParkingTickets
where datepart(hh,TicketDate) = datepart(hh,PT.TicketDate)
and TicketDate between '1/2/2005' and '1/5/2005'
)
from ParkingTickets PT
where TicketDate between '1/2/2005' and '1/5/2005'
group by IssuingOfficer, datepart(hh,TicketDate)
order by HourOfDay, IssuingOfficer

drop table ParkingTickets


if you'll run that in Query Analyzer, you'll see that it works just fine and produces the expected output. However, if you delete this line:
count(*) as TicketsIssuedByThisOfficer,

the query errors out. I can't see any reason why deleting that line would produce an error. I think it must be a bug.

magimix
2005-Apr-12, 08:06 PM
Hey, since we seem to have so many SQL lovers here, do you guys want to see what I think is a legitimate bug in MicroSoft SQLServer 2000? I haven't been able to test this on any other versions yet. The original query was very long, so I reduced it to the minimum necessary to recreate the problem - and I added a fictional scenario. I don't really work with a parking ticket database.
[snip]


Oh man, that's a good one. You can also get it do work or wig out depending on what you comment out. For example, remming out the count(*) yields the error, but additionally remming out the group by makes the error going away.

However, putting the group by back, but replacing datepart(hh,TicketDate) with TicketDate still nets you a working query (in that it runs, though doesn't quite do what you want :) )

Indeed, it subtly breaks in all sorts of ways. Nice find! Can't say I've run into this one before.

Edit: Nice. Googling the fairly unhelpful error message you get yields dark tales of all sorts of things breaking in unusual ways, often closely linked with the use of sub-queries and aggregate functions.

AstroSmurf
2005-Apr-13, 08:41 AM
I've got it worse - I'm dealing with an application where all attributes can hold lists of values
that's wrong. you should normalize that. I think that a lot of the problems that people have with SQL comes from improper database design.
The trouble is that the "can hold lists" requirement set on a higher level (application layer). And the database is fairly normalised (or as much as it can be) - no NULLs anywhere, but primary keys are, well, difficult to specify. Some tables have them, some don't. And the ones who don't can't have them unless I invent something just to make things look pretty - the keys won't ever be used.

PhantomWolf
2005-Apr-13, 09:03 AM
Bleah I hate SQL!


It's driving me nuts!

arghhhhhhhhhhhhhhh

I agree mickal.

When you have to write a query pulling fields out of five tables, several of which are interdependant on the data taken from other tables thus requiring multiple nested SELECTs, JOINs and UNIONs, then come backl and tell me how much you love SQL. Bleah!

tofu
2005-Apr-13, 03:13 PM
The trouble is that the "can hold lists" requirement set on a higher level (application layer).

hmm. I'm not sure I follow you. Anyway, don't take it the wrong way. I don't personally care what your database looks like. I'm just saying that it's not normalized.

If you had two tables, one to hold car salesmen and one to hold customers, you might be tempted to put a comma-delimited list of customerIDs in the salesmen table. It's an easy and direct way to say, "this salesman has worked with these customers." And 99.9% of the time, the question you'll be answering with that data will be, "show me this salesman's customers" which you would do easily like this:

select * from customers
where customerID in (select customerIDlist from salesmen where salesmanID = X)

what's not immediately obvious is that with a comma delimited list, you can't do the reverse. You cannot answer the question, "show me all the salesmen this customer has worked with." You can't do it unless of course you go to that extra step of keeping another comma-delimited list in the customer table. Here's why you can't do it. Show me all the salesmen that customer number 5 has worked with:

select * from salesmen where customers_served like '%5%'

See, that would return salesmen who have worked with customer 25, and salesmen who have worked with customer 50, and customer 155, etc. You can hack it to make it work. You can put a 0 on the beginning and the ending of the list and then do:

where customers_served like '%,5,%'

But a: that really is a hack. And b: the data still isn't normalized because one day, no matter how big you make that character field, some salesmen is going to have too many customers to fit in it. You'll save yourself infinite trouble (and if you're a perfectionist, you'll be able to sleep at night) if you just have one extra table with two fields, both of which are keys.

create table l_customer_salesmen (
salesmenID int
customerID int
)

alter table l_customer_salesmen add
constraint PK_l_customer_salesmen
primary key clustered (
salesmenID,
customerID
)

now you can answer both questions, and theoretically normalized data can answer any question that the data can answer. That's what normalization is about. It's about making sure that your implementation of a problem doesn't impose some arbitrary restriction - it's what they say in the unix world - "solve the problem, not the machine." You never want to have to say, "oh sorry, I can't do that because of the way the data's organized, or because of the way I implemented it."

magimix
2005-Apr-13, 04:35 PM
The trouble is that the "can hold lists" requirement set on a higher level (application layer).

hmm. I'm not sure I follow you. Anyway, don't take it the wrong way. I don't personally care what your database looks like. I'm just saying that it's not normalized.


I think AstroSmurf's point was that he (I'll assume AstroSmurf is a bloke for now) has no say whatsoever over the schema. That maybe the schema he works with is intimately tied to the runtime portion of whatever system he works with, and therefore cannot change the schema without basically breaking the runtime component (because the runtime will be expecting data in a form it is no longer getting).

One of the perils of closed source, you could perhaps argue, as we the system administrators have a limited capacity to alter and improve things.

Take the SupportCenter system I look after. I'd love to change the grotty int encoded time and date formats into a single datetime type, and for that matter properly normalise certain elements of the database. All I'd succeed in doing, however, is breaking things in a fairly serious, will-be-lynched-by-users way because I won't be able to change the runtime to handle the modifications I'll have made.

Of course, I could be wrong about what AstroSmurf meant, though I think in general the point stands :)

worzel
2005-Apr-14, 01:20 AM
I also got the impression that AstroSmurf fully understood that the data he had to work with was not even in first normal form and that he was not happy about it.

The key, the whole key, and nothing but the key, so help me Codd.

AstroSmurf
2005-Apr-14, 08:42 AM
Disclaimer: I haven't studied databases formally since high school, so my knowledge is not so much rusty as has returned to the ore state.

What I'm working with is an object repository that's gone mad and robbed the steroid shop. For reasons to do with my sins in a past career, I get to handle the translation of objects-and-classes to some sort of SQL. The trouble isn't so much the database scheme - it's fairly normalised apart from the lack of reasonable choices for primary key. The objects (called Task Object Instances) have a table of their own, and the attribute values are stored in a few separate tables, one for each type the values can have (though I've considered splitting them into one per attribute, that'd generate some 150-odd tables and I don't foresee any huge performance improvement).

So to find a salesman by his name, for instance, I'd look into a cache with the class id for 'Salesman' (eg 45) and the key id for 'name' (eg 236) and then find him with:

select toi_id from t_toi, t_value_lstring l0
where t_toi.toc_id = 45 and l0.toi_id = t_toi.toi_id and
l0.key_id = 236 and l0.value = 'John Doe'

And to get all the values in the 'handlers' attribute for an object, you'd lookup the key id and ask for:

select value from t_value_toiref t0 where t0.toi_id = 12345 and key_id = 228 order by serial

So far so good. The trouble is that some attributes allow 0-many values for a toi. And sometimes we need to find e.g a 'Case' where the 'handlers' are John, Jack and Jill and no others (specified by toi id numbers, typically), combined with something else. Then you get a beauty like:

select distinct toi_id from t_toi, t_value_lstring l0
where t_toi.toc_id = 22 and l0.toi_id = t_toi.toi_id and
l0.key_id = 254 and l0.value in ('New','Started','Assigned') and
not exists( select NULL from
(select value from t_value_toiref tuple where tuple.toi_id = t_toi.toi_id and tuple.key_id = 228) T
full outer join
(select 123 as v union all select 234 as v union all select 345 as v) V
on T.value = V.v
where T.value is NULL or V.v is NULL)

Fortunately, I only have to maintain the logic that writes this SQL, not type it out myself. But it took a while to get this to work, especially if you want some sort of efficiency.

My gripe was that a simple thing like 'is equal to XXX' is almost invariably met by a response of 'how do you mean equal? Is it enough if one of the values in XXX matches, or do all of them have to? And are other values allowed?' .... etc.

Among other amusing consequences, it's perfectly possible to have an attribute contain values so that value == 22, value != 22, value < 22 and value > 22 are all true simultaneously.
Makes things rather interesting...

worzel
2005-Apr-14, 09:36 AM
For reasons to do with my sins in a past career, I get to handle the translation of objects-and-classes to some sort of SQL. The trouble isn't so much the database scheme - it's fairly normalised apart from the lack of reasonable choices for primary key.
Is the schema under your control? Because with multivalued attributes it isn't even in first normal form.


Among other amusing consequences, it's perfectly possible to have an attribute contain values so that value == 22, value != 22, value < 22 and value > 22 are all true simultaneously.
As tofu said earlier, you'd do yourself a big favour if you normalized the schema. By working with multivalued attributes you're manually doing things that the database is supposed to do for you, like seeing if any value is 22, or if all values are less than 22, for instance.

Enzp
2005-Apr-14, 09:55 AM
Wow, this is great. I read all this and found it absolutely opaque. I have no idea what you are talking about. DOn't try to explain, I always find it interesting to bump into my ignorance. I feel really old now. Obviously computer related, I get that far. Data Bases in high school? When I was in high school, Univac was a big deal. Computers used vacuum tubes. I know about vacuum tubes though. carry on.

worzel
2005-Apr-14, 10:05 AM
Wow, this is great. I read all this and found it absolutely opaque. I have no idea what you are talking about. DOn't try to explain, I always find it interesting to bump into my ignorance. I feel really old now. Obviously computer related, I get that far. Data Bases in high school? When I was in high school, Univac was a big deal. Computers used vacuum tubes. I know about vacuum tubes though. carry on.
I was at my high school when they got their first computer. By the time I left they had a whole classroom full and were even thinking about getting a Winchester Fixed Disk! What I found interesting 10 years later at uni was that all this database stuff was worked out by Codd around the time I was born.

AstroSmurf
2005-Apr-14, 12:00 PM
Among other amusing consequences, it's perfectly possible to have an attribute contain values so that value == 22, value != 22, value < 22 and value > 22 are all true simultaneously.
As tofu said earlier, you'd do yourself a big favour if you normalized the schema. By working with multivalued attributes you're manually doing things that the database is supposed to do for you, like seeing if any value is 22, or if all values are less than 22, for instance.
Note that attribute is not the same thing as field. Attribute is a concept on a higher level than the database scheme. The values in an attribute are stored as database table rows, but the concepts aren't interchangeable. So if the 'handlers' attribute of object 123 has value (1,2,4,42) that would be stored as four rows in t_value_int with a one-to-many relation from the row in t_toi with toi_id = 123.

How would you set up a multivalued field in normal circumstances? Seems to me you'd be using a separate table with a one-to-many relation anyhow, which is what I'm doing already.

The high school thing - that'd have been around 1991, and I went to a place with a special focus on computer technology. Didn't much appreciate being taught database fundamentals at the time, though I'd definitely appreciate a refresher these days.

[edited for clarity]

worzel
2005-Apr-14, 01:19 PM
Note that attribute is not the same thing as field. Attribute is a concept on a higher level than the database scheme. The values in an attribute are stored as database table rows, but the concepts aren't interchangeable. So if the 'handlers' attribute of object 123 has value (1,2,4,42) that would be stored as four rows in t_value_int with a one-to-many relation from the row in t_toi with toi_id = 123.

How would you set up a multivalued field in normal circumstances? Seems to me you'd be using a separate table with a one-to-many relation anyhow, which is what I'm doing already.
My mistake. So the problem you're talking about is how to uniformaly handle single and mutli-valued attributes at a higher level.


The high school thing - that'd have been around 1991, and I went to a place with a special focus on computer technology. Didn't much appreciate being taught database fundamentals at the time, though I'd definitely appreciate a refresher these days.
Database fundamentals? All we did at high school was turtle graphics in Logo, binary and hex numbers, and logic gates :-?

Sigma_Orionis
2005-Apr-15, 12:08 AM
Wow, this is great. I read all this and found it absolutely opaque. I have no idea what you are talking about. DOn't try to explain, I always find it interesting to bump into my ignorance. I feel really old now. Obviously computer related, I get that far. Data Bases in high school? When I was in high school, Univac was a big deal. Computers used vacuum tubes. I know about vacuum tubes though. carry on.

Don't worry, back in 1978, when my father bought his first Microcomputer (NOT A PC!) which was a TRS-80 (and since it was down here in Low-Tech-Land, he must have been among the 5 first people to have a computer AT home in this country) most people thought that he was either an eccentric or a Rocket Scientist........ Nowadays any cellphone has lots more transistors (in a matter of speaking) in it than a Microcomputer of those days (and have better games too :lol:)

Celestial Mechanic
2005-Apr-15, 04:07 AM
[Snip!]So I have gotten as far as creating a database (Yay!!!!) but now the server program claims it doesn't know which user to use. The default username is postgres, but somehow it doesn't know it or provide me with a means to enter it, because, well, this is OS/2 and OS/2 doesn't need no stinkin' "su" command!
I had a few free minutes tonight for experimentation and ulimately found the incantation that would enter the username of "postgres", and it should be straight-forward to create an object in the PostGres folder to call up the database. On to the next step in the tutorial!

I'm curious. How many of you use some sort of database capable of being queried with SQL at home, and what for? I'm not sure yet what uses I might find for this, but I'm open to suggestions.

PeterFab
2005-Apr-15, 04:58 PM
I'm curious. How many of you use some sort of database capable of being queried with SQL at home, and what for? I'm not sure yet what uses I might find for this, but I'm open to suggestions.

I use Borland's Interbase for keeping track of my comic book collection, 12000+ issues.

I'm still writing the actual application, but I've started filling it with data.

Candy
2005-Apr-15, 05:04 PM
I use Borland's Interbase for keeping track of my comic book collection, 12000+ issues. :o

Sigma_Orionis
2005-Apr-15, 06:13 PM
I once installed Oracle 6 on an SCO Box I set up at home to play with it :D, frankly I never have had any need for databases for personal use