DreadCthulhu posted:

I think my question was mostly about building parameterized SQL queries procedurally in the application layer, sorry for the confusion.

Hard NOP Life posted:

#2 I don't personally believe in, I've yet to see a convincing argument.

ShoulderDaemon posted:

There's also the edge case where one of your columns may contain very large data, and you may not actually need it, in which case you should avoid selecting it because you're making the database do extra work to move that data around. But that's similarly uncommon and not very motivating for a general case.

Beelzebozo posted:

The biggest argument against select * is that someone could change the table schema behind your code's back. If you create a function that expects a certain number or order of columns it could break unexpectedly.

ShoulderDaemon posted:

It's important to explicitly list the columns you're selecting if you are using a SQL interface which just hands you back rows as simple arrays instead of as associative lists, and you consequently have to depend on the order that columns are returned in. Especially because some backup systems or replication systems may not preserve the natural order of columns.

That said, SQL interfaces which don't return the names of the columns are quite uncommon and very stupid and you'd be far better off just avoiding them entirely. Hell, even in the 90s it was hard to find a SQL client that forced you into such a dumb operating mode.

ShoulderDaemon posted:

There's also the edge case where one of your columns may contain very large data, and you may not actually need it, in which case you should avoid selecting it because you're making the database do extra work to move that data around. But that's similarly uncommon and not very motivating for a general case.

Biowarfare posted:

Not sure if this is the correct thread, but I'm looking for a database right now. It would consist of inserts (99%) and rarely any updates or deletes, with around 100 columns all storing an int type with anywhere from 0 to 15 characters. I need to be able to fit billions of rows in as little disk space as possible. Many of the columns would be zero, too.

Any ideas on what to do that preferably don't involve some kind of abomination of a separate deduped partition running or software suggestions? Postgres apparently compresses large data but I'm not too sure what large is and I'm reasonably sure that individual rows will never be past 1-2KB each uncompressed. No idea what MySQL does or what type (innodb? myisam?) to use.

Xae posted:

Are you looking to put 100 columns on one table?

Splinter posted:

Is there a website out there for practicing select queries? I'm giving my friend a crash course on writing select queries and it'd be nice if were a site out there that has some basic tables populated that could be queried through a web interface so he could get some practice. Does anything like this exist?

Also, anyone have any recommendations for either books or online courses (e.g. coursera, code academy) for learning basic SQL?

Splinter posted:

Is there a website out there for practicing select queries

a foolish pianist posted:

Is there a way to get the user_id and the counts for each week in a line?

Karthe posted:

I need some help optimizing the following query:

SQL code:

SELECT
kd2c.id as _id,
kd2c.literal as kanji
FROM
kd2_character as kd2c
JOIN krad_components as kcom ON kcom.kanji_fk = kd2c.id
WHERE kcom.radical_fk IN (1, 2, 3, etc...)
GROUP BY kd2c.id HAVING count(distinct kcom.radical_fk) = <number of integers in WHERE clause>
ORDER BY kd2c.freq IS NULL ASC, kd2c.freq, kd2c.id

The query itself (regardless of the number of fk's in the WHERE clause) takes 0.04 seconds to run, which is a long time relative to all of my other queries that take around 0.0003 seconds. I ran an EXPLAIN QUERY PLAN against the above statement and received the following:

code:

# | selectid | order | from | detail 
1	0	0	0	SCAN TABLE kd2_character AS kd2c USING INTEGER PRIMARY KEY (~1000000 rows)
2	0	1	1	SEARCH TABLE krad_components AS kcom USING COVERING INDEX idx_krad_components (kanji_fk=? AND radical_fk=?) (~9 rows)
3	0	0	0	EXECUTE LIST SUBQUERY 1
4	0	0	0	USE TEMP B-TREE FOR ORDER BY

I'm pretty sure the script takes so long because of that initial SCAN TABLE. If that's the case, how can I get rid of it? I thought creating an index on kd2_characer.id would help things along, but
it didn't have any noticeable effect on execution time. Is there a better way to structure my GROUP BY, since it's probably the source of the SCAN?

Malcolm XML posted:

Can you up the verbosity on the explain query plan? My gut tells me that something in the order by or having (likely the distinct) is messing with things, but nothing really seems to stick out.

Malcolm XML posted:

Also, you have a group by but one of your output cols is not in an aggregate function so stricter DBMS like PostgreSQL will bitch at you since it's against the standard and undefined behavior.

Malcolm XML posted:

Fake edit : Suppose you dropped the having clause and removed the order by null asc bit : does it speed up? I want to say that the count distinct is expensive but that's just a hunch

Malcolm XML posted:

You could try building the result backwards, by first grouping the components by kanji fk , filtering out those that don't have a full set of radical_fk matching the integers, then joining that to the character table to get the literal.

a foolish pianist posted:

I'm trying to aggregate some data from a forum, and what I'd like to get out of my query is something like this:

code:

user_id1, postcount-week1, postcount-week2, postcount-week3,..., postcount-weekn
user_id2, postcount-week1, postcount-week2, postcount-week3,..., postcount-weekn
...

for each user. The database just contains a list of posts, with the user_id, the date and time, and the post.

For individual weeks, it's simple enough:

code:

SELECT user_id, COUNT(post_id)
FROM post_table
WHERE WEEK(post_date) = $whateverweek
GROUP BY user_id

Is there a way to get the user_id and the counts for each week in a line?

Karthe posted:

I ran ANALYZE on the database, then ran EXPLAIN QUERY PLAN again. Here's the updated output:

code:

# | selectid | order | from | detail 
1	0	0	0	SCAN TABLE kd2_character AS kd2c USING INTEGER PRIMARY KEY (~13108 rows)
2	0	1	1	SEARCH TABLE krad_components AS kcom USING COVERING INDEX idx_krad_components (kanji_fk=? AND radical_fk=?) (~2 rows)
3	0	0	0	EXECUTE LIST SUBQUERY 1
4	0	0	0	USE TEMP B-TREE FOR ORDER BY

Can you elaborate more on this? I'm using SQLite and haven't experienced any kickback in instances like this, so I'm not sure what it means when an output column isn't part of an aggregate function within the query.

I played around with the query and found out that dropping HAVING in the GROUP BY shaved off about 0.015 seconds. Unfortunately, the HAVING is necessary or else the number of results returned increases whenever I add additional numbers to the WHERE...IN clause.

I see where you're going with this. I'll try working from krad_components first and then joining to kd2_character.

Edit: It's the ORDER BY that's causing the slow down! I removed the kd2c.freq IS NULL ASC, kd2c.freq part of the query and execution time dropped to about 0.0004 seconds, in line with my other queries. I created an index on kd2_character.freq and kd2_character.id thinking it would take care of the USE TEMP B-TREE FOR ORDER BY, but that didn't help at all.

PlesantDilemma posted:

Does this query do what I think it does?

code:

SELECT shipid,
       codecount
FROM   codestable
GROUP  BY shipid
ORDER  BY codeid DESC;

This is MySQL and codeid is the autoincrement field. I think this gives me the most recent codecount for each shipid. Is that correct?

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html posted:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ...However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

FlyWhiteBoy posted:

Can someone recommend a book about SQL Security? I haven't found any clear favorites on Amazon or Google.

Scaramouche posted:

MySQL? MSSQL? The problem with SQL security is that:
- The majority of the problems are from the underlying presentation language (php, .net, etc.) each of which have their own quirks
- The SQL platforms by themselves are actually pretty secure, except when
- You write bad code and queries that allow them to be exploited (see point 1)

So a generic SQL Security book would be pretty hard to write, other than a couple pages of "how not to write injectable code". However, there are things specific to each platform, such as the permission escalation bug in older versions of MSSQL, that could stand to be documented specifically. But again, it would depend largely on what flavour of SQL, and what presentation language you are using.

BabyFur Denny posted:

Create a unique index (wordID, tagID) and do an INSERT IGNORE for any new tags. If the combination already exists in the database, it is silently dropped.

foundtomorrow posted:

aBagorn posted:

I have a where statement in a stored proc I'm writing (MS-SQL) that includes this:

SQL code:

dbA.dateCreated BETWEEN @pStartDate and @pEndDate

However, StartDate and EndDate could come in as null. I've never been great with handling null parameters in sql. What's the best way to go about?

Jethro posted:

SQL code:

dbA.dateCreated BETWEEN ISNULL(@pStartDate,dbA.dateCreated) and ISNULL(@pEndDate,dbA.dateCreated)

This assumes that it's OK if either or both are null, and that null means wide open.

DreadCthulhu posted:

I need some advice on how to better secure a DB that for now I still want direct Internet access to. I understand that in the real world you never put your db on an internet-facing machine and expect not to get hacked, but we're pretty scrappy and have to deal with this for now unless there's a very quick way of setting up rerouting someones pgAdmin connection through a gateway box. Or even better expose the data through some kind of admin dashboard instead of connecting directly etc etc.

Right now we're using passwords and IP whitelisting, but the problem is that whitelisting sucks when you have to directly connect to the DB from a different location (which we do a lot), and now you have to ssh into that machine and update its pg_hba.conf and make sure the IP is recorded in our configuration management repo. What's a step up from this? Is there a simpler way of still ensuring a bare minimum of security without whitelisting?

Jethro posted:

Great advice

A few questions:
How often do you need to see the results? You scrape in real-time (more or less), but do you also need the data in SQL to be updated in real-time, or does that only need to happen every few hours or once a day or whatever?
Is there any unique identifier either on the file itself or on the records in the file? In other words, is there a way to distinguish between loading the same file again versus a new file that just has all the same entries in it?

Great advice.

Salt Fish posted:

I would only allow access to the MySQL service on the local area network and then have my users VPN to the private network to connect to the service. Do you have a capable hardware firewall?

foundtomorrow posted:

Ok guys, Google has failed me on this question. This is for a side project.
I need some help on automating a process with maybe Triggers or Stored Procedures that involves importing the contents of a .csv every two minutes (with likely duplicates from one .csv to the next) into a SQL Server database (2008 or 2012 is fine).

I am scraping a website which has data on the current status of multiple websites (latency, down, slow, etc.).
I have the scraping part all set up with Python using Selenium and BeautifulSoup and RegEx. This Python script exports to a Pipe-Delimited Text file once every 2 minutes. There's less than 100 rows on each 2 minute export, so we aren't talking huge data here, at least.
I can easily import my text file into my SQL Server Database table like this:

code:

BULK
INSERT CSVTest
FROM 'c:\testing\output1.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

That works great. I also need to add a field which auto-populates on the current time when the row is inserted (I googled this, and I can do this as well).

This is the complicated part:
For each 2 minute update, there are both duplicate records which were imported previously, and also new records which need to be added.
One record can possibly be in every imported file for an entire hour (30 different records that were imported). This is fine, and I need this to happen, because I need to be able to calculate how long that record was actually showing on the website. In other words, if Record A shows up at 2PM, and then goes off the website at 3PM, I will have 30 entries in my Database table for that entry (one entry every 2 minutes). I need to be able to calculate that 1 hour time difference between the first entry and the last entry and then add that value to the first entry in a new field (Elapsed Time). There is another small complication. That exact same record could show up 30 minutes later, so I need to make sure my script knows that if the value was in the previous CSV import, but not in the current import, then that's when the Elapsed Time calculation should occur.

Sorry for the wall of text, but SQL Server Triggers or Stored Procedures are not familiar to me. I am comfortable with Python, Java, C#, but haven't done much SQL Server development.

I'm not looking for code to copy and paste, I just need some guidance on the workflow I should use and how I can detect when the imported record was in the previous update and not in the current update, so I know when to calculate the Elapsed Time.