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 »

Star War Sex Parrot: Oct 2, 2003

Also friendly reminder for database system nerds that VLDB is next week and registration is free. The program page has videos of the talks already, so you only need to sign up if you want to ask questions in real-time I guess. The design of that page is loving awful though.

# ? Aug 28, 2020 17:03

Adbot: ADBOT LOVES YOU

# ? Jun 7, 2024 22:20

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Star War Sex Parrot posted:

That's cool because the academic system we're building aims for that. We compile physical plans to our DSL (a la MemSQL) and we can then either compile that using LLVM (a la HyPer 10 years ago, Postgres is adopting it for some queries now) to machine-native code for maximum performance, or we have an interpreter for that DSL for debugging. We're still working on getting the interpreter set up with a GDB stub, but in the meantime having the DSL alone is a nice way to see what operations are actually being performed at runtime. As usual the HyPer guys already built something similar for their new system.

Yeah, that�s neat. Academically speaking that seems pretty interesting but unless you can read the compiled code (which might make sense to step through) I can�t imagine that correlating lines of lower level code to lines of pre-compile sql would be possible for anything except simple queries.

Like I immediately think of nested views and a compiler taking advantage of a filtered column coming from a table a few layers deep in the views. Dunno.

I�m no academic on the matter though.

# ? Aug 28, 2020 23:31

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

here's a weird one: let's say for stupid reasons I have a database with a date column that is, unfortunately, named date

if i do this:

select distinct top 10 date from dbo.MyTable order by date desc;

it appears to work every time as expected

if i do this:

select distinct top 5 date from dbo.MyTable order by date desc;

sometimes it gives me the results i expect; other times, it will repeat entries and not even bother ordering them

wrapping the fieldnames in [] seems to have no effect - sometimes it works, sometimes it doesn't

for some reason i don't have SHOWPLAN permissions on this machine so i can't actually look at an execution plan, but i've never seen something like this before. anyone got any ideas? this isn't affecting anything, it's much more of a curiosity but i'm eager to know if someone has a rational explanation

e: i suspect it has something to do with the fact that in the second query, i'm retrieving less distinct records than exist, e.g. there are 8 distinct dates in the full dataset, and in the first query i am asking for 10 and thus getting all 8, whereas in the second query i'm only asking for 5 and then something freaks out??

# ? Aug 31, 2020 16:48

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

kumba posted:

here's a weird one: let's say for stupid reasons I have a database with a date column that is, unfortunately, named date

if i do this:

select distinct top 10 date from dbo.MyTable order by date desc;

it appears to work every time as expected

if i do this:

select distinct top 5 date from dbo.MyTable order by date desc;

sometimes it gives me the results i expect; other times, it will repeat entries and not even bother ordering them

wrapping the fieldnames in [] seems to have no effect - sometimes it works, sometimes it doesn't

for some reason i don't have SHOWPLAN permissions on this machine so i can't actually look at an execution plan, but i've never seen something like this before. anyone got any ideas? this isn't affecting anything, it's much more of a curiosity but i'm eager to know if someone has a rational explanation

e: i suspect it has something to do with the fact that in the second query, i'm retrieving less distinct records than exist, e.g. there are 8 distinct dates in the full dataset, and in the first query i am asking for 10 and thus getting all 8, whereas in the second query i'm only asking for 5 and then something freaks out??

Can you reproduce it using a temp table with data that I could grab and poke at in my local instance?

# ? Aug 31, 2020 17:33

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

It sounds like a bug. What version of SQL server is it? Can you reproduce it on a test machine you have full privileges on?

I tried to set it up on SQL Fiddle because they purportedly support SQL Server 2017, but it seems not to be working at the moment.

# ? Aug 31, 2020 20:11

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

Dumping all of the values into a temp table then doing the select distinct top 10 vs select distinct top 5 gives me the results I expect every time (or at least every time I've run it so far)

I did learn something: dbo.MyTable in this case isn't actually a table, it's a view (the team left out that detail when I was looking at this this morning)

I'm not exactly sure why it being a view would make any difference, but I'm guessing there's something weird going on behind the scenes when ordering by columns materialized in a view?? I dunno, I would still expect this to work but unfortunately I also can't see the underlying data nor the query that populates this view so I'm shooting a bit in the dark here :v:

# ? Aug 31, 2020 20:25

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Is the underlying data type actually date?

Are there unions involved in the view?

# ? Sep 1, 2020 05:40

Butterwagon: Mar 21, 2010; Lookit that stupid ass-hole!

I'm trying to store some data with "column expressions" into a SQLite database. Basically I've got a bunch of families of things, where each family is a table of different family options. Some of the columns have expressions like

code:

({COLUMN2} + {COLUMN3}) / (4 * {COLUMN4})

and so the "family table" would look like this:

code:

  COLUMN1    COLUMN2    COLUMN3    COLUMN4
---------  ---------  ---------  ---------
   0.25            1          2          3
   0.3125          2          3          4
   0.35            3          4          5

which corresponds to the abstract syntax tree

code:

(DIV
  (ADD
    (REF, COLUMN2),
    (REF, COLUMN3)
  ),
  (MUL
    (LITERAL, 4),
    (REF, COLUMN4)
  )
)

Database schema is something like this:

code:

CREATE TABLE families (
    familyKey INTEGER PRIMARY KEY NOT NULL,
    familyName TEXT NOT NULL
);

CREATE TABLE tableColumns(
    columnKey INTEGER PRIMARY KEY NOT NULL,
    familyKey INTEGER NOT NULL,
    columnName TEXT NOT NULL,
    expressionKey INTEGER,
    FOREIGN KEY (familyKey) REFERENCES families(familyKey),
    FOREIGN KEY (expressionKey) REFERENCES expressions(expressionKey)
);

CREATE TABLE expressions(
    expressionKey INTEGER PRIMARY KEY NOT NULL,
    parentExpressionKey INTEGER,
    literalValue FLOAT,
    columnKey INTEGER,
    operationName TEXT,
    FOREIGN KEY (parentExpressionKey) REFERENCES expressions(expressionKey),
    FOREIGN KEY (columnKey) REFERENCES tableColumns(columnKey),
    CHECK (
        (literalValue IS NOT NULL AND columnKey IS NULL AND operationName IS NULL)
        OR (literalValue IS NULL AND columnKey IS NOT NULL AND operationName IS NULL)
        OR  (literalValue IS NULL AND columnKey IS NULL AND operationName IS NOT NULL)
    )
);

CREATE TABLE tableRows(
    rowKey INTEGER PRIMARY KEY NOT NULL,
    familyKey INTEGER NOT NULL,
    rowNumber INTEGER NOT NULL,
    FOREIGN KEY (familyKey) REFERENCES families(familyKey),
    UNIQUE (familyKey, rowNumber)
);

CREATE TABLE tableData(
    dataKey INTEGER PRIMARY KEY NOT NULL,
    rowKey INTEGER NOT NULL,
    columnKey INTEGER NOT NULL,
    dataValue FLOAT,
    FOREIGN KEY (rowKey) REFERENCES tableRows(rowKey),
    FOREIGN KEY (columnKey) REFERENCES tableColumns(columnKey),
    UNIQUE(rowKey, columnKey)
);

So expressions would look something like this:

code:

[[tableColumns]]
  columnKey    familyKey  columnName    expressionKey
-----------  -----------  ------------  ---------------
          1            1  COLUMN1       1
          2            1  COLUMN2       NULL
          3            1  COLUMN3       NULL
          4            1  COLUMN4       NULL

[[expressions]]
  exressionKey  parentExpressionKey    literalValue    columnKey    operationName
--------------  ---------------------  --------------  -----------  ---------------
             1  NULL                   NULL            NULL         DIV
             2  1                      NULL            NULL         ADD
             3  2                      NULL            2            NULL
             4  2                      NULL            3            NULL
             5  1                      NULL            NULL         MUL
             6  5                      4               NULL         NULL
             7  5                      NULL            4            NULL

My problem is I don't have a way of actually evaluating the abstract syntax tree in SQLite for operations that take arbitrary numbers of arguments because I'd have to do grouping within a recursive CTE which isn't allowed. One example is addition. I'd like to do something like

code:

WITH RECURSIVE subExpressions (
    expressionKey,
    parentExpressionKey,
    value
) AS (
    SELECT
        expressionKey,
        parentExpressionKey,
        COALESCE(
            literalValue, 
            dataValue
        )
        FROM
            expressions
        LEFT JOIN
            tableColumns
        USING
             (columnKey)
        LEFT JOIN
            (SELECT * FROM tableRows WHERE rowNumber = 1)
        USING
            (familyKey)
        LEFT JOIN
            tableData
        USING
            (columnKey, rowKey)
    UNION ALL
    SELECT
        expressions.expressionKey,
        expressions.parentExpressionKey,
-- This is the thing that's not allowed - no group_concat in recursive CTE's
        SUM(subExpressions.value) OVER (PARTITION BY subExpressions.parentExpressionKey ORDER BY subExpressions.expressionKey)
    FROM
        subExpressions
    LEFT JOIN
         expressions
    ON
        subExpressions.parentExpressionKey = expressions.expressionKey
)
SELECT * FROM subExpressions

but it's not allowed. I feel like there must be a way to do grouping within a recursive CTE but I sure haven't figured out a way to do it.

# ? Sep 5, 2020 18:36

Tibalt: May 14, 2017; What, drawn, and talk of peace! I hate the word, As I hate hell, all Montagues, and thee

This the script I've been told to use by my director to get the correct data:

code:

select distinct a.market, a.patientid, a.run_date, b.adj_start_date, a.raf_a, a.rafactortype from tbl_mmr_temp a,
(select distinct a.market, a.patientid, a.run_date , max(a.adj_start_date) as adj_start_date from tbl_mmr_temp a,
(select market, patientid, max(run_date) as run_date from tbl_mmr_temp where year(adj_start_date) = '2020'  and raf_a <> '0.000' group by market, patientid) b
where  
       year(a.adj_start_date) = '2020' and
       a.market = b.market and a.patientid = b.patientid and a.run_date = b.run_date and raf_a <> '0.000'
group by a.market, a.patientid, a.run_date ) b
where
       a.market = b.market and a.patientid = b.patientid and a.run_date = b.run_date and a.adj_start_date = b.adj_start_date and raf_a <> '0.000'
union
select distinct a.market, a.patientid, a.run_date, b.adj_start_date, a.raf_a, a.rafactortype from tbl_MMR_all a,
(select distinct a.market, a.patientid, a.run_date , max(a.adj_start_date) as adj_start_date from tbl_MMR_all a,
(select market, patientid, max(run_date) as run_date from tbl_MMR_all where year(adj_start_date) = '2020' and raf_a <> 0.000 group by market, patientid) b
where  
       year(a.adj_start_date) = '2020' and
       a.market = b.market and a.patientid = b.patientid and a.run_date = b.run_date and a.raf_a <> 0.000
group by a.market, a.patientid, a.run_date ) b
where
       a.market = b.market and a.patientid = b.patientid and a.run_date = b.run_date and a.adj_start_date = b.adj_start_date and a.raf_a <> 0.000

I've been told not to change ANYTHING, to make sure I'm pulling the same data as him. I'm not asking for help, I just want to complain about it.

# ? Sep 10, 2020 16:50

abelwingnut: Dec 23, 2002

what on god's green earth :psyduck:

# ? Sep 10, 2020 18:19

kumba: Nov 8, 2003; I posted my food for USPOL Thanksgiving!

enjoy the ride; Lipstick Apathy

why change it? it works!! :downs:

# ? Sep 10, 2020 18:47

Ruggan: Feb 20, 2007; WHAT THAT SMELL LIKE?!

Nice formatting style

# ? Sep 10, 2020 20:00

Gatac: Apr 22, 2008; Fifty Cent's next biopic.

Let's see here: SELECT DISTINCT, nestled inline views, implicit join syntax, aggregation of aggregation, aliases both bland and reused repeatedly. And to top it all off, a set operation. A wild, wild ride in just over 1500 characters.

Have we found it? Is this the worst SQL query?

# ? Sep 10, 2020 20:18

Combat Pretzel: Jun 23, 2004; No, seriously... what kurds?!

I ran this through an formatting plugin. I have no idea what it does. Seems to UNION two the same queries? There's probably a minute difference between the two, but I don't see it. --edit: A different table in each query.

# ? Sep 10, 2020 20:33

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

Combat Pretzel posted:

I ran this through an formatting plugin. I have no idea what it does. Seems to UNION two the same queries? There's probably a minute difference between the two, but I don't see it. --edit: A different table in each query.

Lastjob had that all over the place because we had one generic 'registration' table for three types of reservation, all of which were often lumped together in the UI and reporting. At least they could often simply be turned into UNION ALLs for low-hanging performance gains.

# ? Sep 11, 2020 13:33

Combat Pretzel: Jun 23, 2004; No, seriously... what kurds?!

Yeah, uh, I'm only just about getting back into SQL after two decades of gently caress-all of it, but, can't you just create a view over all the tables and avoid this type of bullshit?

# ? Sep 11, 2020 19:02

Twerk from Home: Jan 17, 2009; This avatar brought to you by the 'save our dead gay forums' foundation.

How big is too big for Postgres indexes? I'm trying to drop into an application that's struggling that I know little about, and improve performance.

I've got a 15GB data, ~12.5 million row table with no relations. The hottest index is >2.5GB, and individual queries using that index are both slow (>1.5s) and driving high CPU usage. The problem query is definitely only doing an index scan with very low planning time, but execution time is high. The index is a 4 column multi-column index, we're querying on 3 of them in the right order so the index is being used. The columns that are being queried on are highly duplicated, and also text fields.

2 of the 3 columns we're querying with have a total of 6 distinct values each, so my immediate thinking is that we can reduce the index size by normalizing those out into their own table, so that instead of duplicated text making the index huge we just have numeric foreign keys. Any suggestions for different kinds of indexes that would perform better with such duplicated values in the index? It's just a normal B-Tree right now.

Edit: Actually, I would bet that putting the least-duplicated column first in the multi-column index helps... let me try that.

Twerk from Home fucked around with this message at 03:49 on Sep 15, 2020

# ? Sep 15, 2020 03:40

abelwingnut: Dec 23, 2002

sql server question

can one retrieve the history for a particular login? to be specific, i'd want to know all of the times the login logged on, what queries it ran, any ddl changes, dml changes, etc.

i don't think this is possible at all? i know sql server keeps like the last day's worth of queries for all logins in dm_exec_query_stats and dm_exec_sql_text, but beyond that...i don't think so. if i wanted to produce an audit trail, i think i would need to create an audit for that login. that would then save all of the login's actions to some file or table or something. or i could run a temporary profile on them, but that seems silly for what i'm being asked for.

there's not some grand history log or table tracking all changes as far as i know.

am i understanding this correctly?

# ? Sep 16, 2020 15:19

Pardot: Jul 25, 2001

Twerk from Home posted:

How big is too big for Postgres indexes?

Large indexes are fine as long as (and I�m assuming this is oltp) you have enough ram to keep cache hit rates at 99%

Twerk from Home posted:

The columns that are being queried on are highly duplicated

Look into gin or gist indexes. They are inverted so will be much smaller and more efficient for duplicated values. See this talk for details on how it works under the hood

https://youtu.be/2l-nCkPQVuQ

Twerk from Home posted:

and also text fields.

If you can get away with it, try the �C� collation. The downside is non ascii characters won�t sort to the expected place, but it might not matter (in almost all my apps almost never). Even when I have non ascii text, I�m not relying how Postgres sorts it, since it gets sorted again in my app or it just doesn�t matter for whatever reason. If this is the case here, and the collation is C, you can take advantage of the �abbreviated keys� optimization that went in a few releases ago.

# ? Sep 23, 2020 07:47

Twerk from Home: Jan 17, 2009; This avatar brought to you by the 'save our dead gay forums' foundation.

Pardot posted:

Large indexes are fine as long as (and I�m assuming this is oltp) you have enough ram to keep cache hit rates at 99%

Look into gin or gist indexes. They are inverted so will be much smaller and more efficient for duplicated values. See this talk for details on how it works under the hood

https://youtu.be/2l-nCkPQVuQ

If you can get away with it, try the �C� collation. The downside is non ascii characters won�t sort to the expected place, but it might not matter (in almost all my apps almost never). Even when I have non ascii text, I�m not relying how Postgres sorts it, since it gets sorted again in my app or it just doesn�t matter for whatever reason. If this is the case here, and the collation is C, you can take advantage of the �abbreviated keys� optimization that went in a few releases ago.

I really appreciate this, thank you. I'm going to start by hitting the docs about GIN indexes.

# ? Sep 23, 2020 14:45

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

Pardot posted:

Large indexes are fine as long as (and I�m assuming this is oltp) you have enough ram to keep cache hit rates at 99%

Lastjob: "SQL performance is worse but we're paying extra for the faster [smaller] RAM! How can this be?"

Me: *points at cache churn, disk read stats*

Lastjob: "But fast RAM!?"

# ? Sep 23, 2020 18:18

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

Are there any proper Hasura / PostgREST equivalents for SQLite or other embedded databases? By "proper" I mean that they don't just generate CRUD endpoints, but also eg. perform auth via SQL roles, expose webhooks, and in general can do most of what you'd expect from a full backend.

# ? Sep 23, 2020 23:32

spiritual bypass: Feb 19, 2008; Grimey Drawer

NihilCredo posted:

Are there any proper Hasura / PostgREST equivalents for SQLite or other embedded databases? By "proper" I mean that they don't just generate CRUD endpoints, but also eg. perform auth via SQL roles, expose webhooks, and in general can do most of what you'd expect from a full backend.

It's not as full-featured at PostgREST, but I've made a Go library that creates a similar HTTP API at runtime from either SQLite or MariaDB. It's capable of doing custom authentication and can tack on the proper user ID column to queries to control access. You could create your own webhooks by wrapping the HTTP handlers it produces in your own code before feeding them to the server.

# ? Sep 24, 2020 17:39

Secx: Mar 1, 2003; Hippopotamus retardus

Using SSMS, is there a shortcut/add-in that allows you to enclose values in single quotes for use in an IN clause?

Right now, I put my values in Excel and create this formula in column B: ="'" & A1 & "',". This results in:

code:

'1234',
'5678',
'9012',
[...]

I then copy/paste into SSMS:

code:

select foo from bar
where baz in (
'1234',
'5678',
'9012',
[...]
)

Trying to find a more efficient way to do this.

Edit: I found this on Stackoverflow: https://dba.stackexchange.com/questions/96371/applying-quotes-across-multiple-lines. But the Shift-Alt solution only seems to do this for one line at a time. Not practical if I have 1000+ lines. It also doesn't work if my values aren't all the same length.

Secx fucked around with this message at 16:18 on Sep 25, 2020

# ? Sep 25, 2020 16:08

redleader: Aug 18, 2005; Engage according to operational parameters

Secx posted:

Using SSMS, is there a shortcut/add-in that allows you to enclose values in single quotes for use in an IN clause?

Right now, I put my values in Excel and create this formula in column B: ="'" & A1 & "',". This results in:
code:
'1234',
'5678',
'9012',
[...]
I then copy/paste into SSMS:
code:
select foo from bar
where baz in (
'1234',
'5678',
'9012',
[...]
)
Trying to find a more efficient way to do this.

Edit: I found this on Stackoverflow: https://dba.stackexchange.com/questions/96371/applying-quotes-across-multiple-lines. But the Shift-Alt solution only seems to do this for one line at a time. Not practical if I have 1000+ lines. It also doesn't work if my values aren't all the same length.

i jam 'em into notepad++ and do a regex replace. something like find ^(.+)$, replace with '$1',

i've also done similar things to split up text into multiple columns for inserting into temp tables or w/e

# ? Sep 25, 2020 23:47

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

I use LINQPad and some extensions I wrote to mush the data into shape

# ? Sep 29, 2020 18:17

Combat Pretzel: Jun 23, 2004; No, seriously... what kurds?!

In DB2/DB400, when I want to use CTEs, do I need to end the whole query with a semicolon? I've been sending queries via ODBC without any ending semicolons since a while now, and everything's fine. Then I tried to get CTEs work, just for the dumb iSeries to puke out (to me) non-sensical error messages. Out of boredom and annoyance I'm googling about it at home, and they're all putting an ending semicolon in the examples. Is that the cause of my frustration?

(Then again, lots of posts have semicolons in simple stupid queries, so what the hell.)

# ? Oct 6, 2020 21:36

MockingQuantum: Jan 20, 2012

Hey thread, wondering if you can help me figure out a very broad and newb-ish question regarding SQL:

I'm pretty new to SQL (though I know enough to have figured out this is the SQL thread, so there's that) and still wrapping my head around how exactly it would get used in a real-world application. I work for a small business that's currently operating pretty slowly due to COVID, so I'm taking some time to look at our business processes and figuring out if there's a better way to set up some of our systems. I work for a company that rents audio equipment, so we've got a big, varied inventory of stuff that requires pretty constant tracking, and right now we use like three different systems to handle orders, inventory tracking, and invoicing. When I took over the day-to-day order management, we were still using a Lotus 1-2-3 spreadsheet to track inventory, and the thing was basically useless.

I now have a very hacky Excel spreadsheet for tracking our inventory, and the upside is that it not only tells me what quantities of equipment we have in the shop right now, but actually generates an availability calendar, and lets me plug in dates and item codes to see if something will be available in the future! Amazing! (I know it's not amazing, but the old spreadsheet literally couldn't do that, you just had to guess or figure it out yourself unassisted).

This is working, sort of, but I'd like a much more streamlined system that lets me enter a customer name, rental out date, return date, items, and quantities into an entry form, and have whatever program I'm working with automatically adjust inventory availability for that date range. I'd obviously like to be able to call up inventory availability at any time in the future to see what we can actually commit to future rentals, too.

Originally, I was going to try to build something in MS Access to do all of this, but I took the Codecademy SQL class on a whim and it makes a lot of sense to me. So should I go that route? The thing I haven't wrapped my head around is, how do my (non-coder, mostly tech literate) coworkers interact with this theoretical database? If I build the database, and use SQL to build the kinds of queries I need for regular use, do I use some sort of "end-user" application that refers to that database and presents it all in a clean, readable spreadsheet-style interface for them to refer to?

Basically I don't think I've totally wrapped my head around the relationship between SQL vs something like MySQL vs an "end-user" database program like Access, and so far all my newbish googling comes up with results that don't quite make the situation clearer to me.

# ? Oct 12, 2020 17:12

PhantomOfTheCopier: Aug 13, 2008; Pikabooze!

Access is a basic database and provides a forms interface, can be set up so users must go through the forms, etc. It's been a couple decades since I've touched it, so I don't rightly know how hard it is to build something, if it can be linked to a website form/interface, and how awful the learning curve is. As a benefit, it's probably the easiest choice for Excel import/export support.

Keep in mind that your users will refuse change. Better validation and reporting scripts in your existing spreadsheet might go a long way to improving the process without having to fight your peers while simultaneously learning an entirely new collection of technologies.

The primary difference you'll find between the spreadsheet and SQL land is that the latter does not just let you "randomly enter some text in the next column because we're giving the customer a pizza with their pickup". Everything has to be well defined (the "schema") and, in most cases, values must be appropriate or selected from a list of alternates. Of course unbounded comment fields work, but if your users start putting business decisions in there, it will be a mess.

One other quick note. SQL is not scheduling software. Because it's "general purpose", it does not tie you to specific solutions, so you have to choose a method to implement reservation uniqueness, calendar control, and so forth. MySQL data types will limit you here somewhat compared to PostgreSQL, for example, which supports range types and uniqueness constraints on timestamp ranges. Much depends on the granularity and options that you have for reservations, as well as your checkout workflows and modifications to existing reservations.

# ? Oct 13, 2020 08:40

DELETE CASCADE: Oct 25, 2017; i haven't washed my penis since i jerked it to a phtotograph of george w. bush in 2003

ms access is probably fine for you.

like potc said, access is basically a sql database combined with a forms interface that should be familiar to excel users, so basically you are getting the end-user application bundled with your database. you can probably buy something similar from oracle too, for $$$$$. but with popular open source databases like mysql and postgres, what you get is the backend database and that's it.

with an access database, the db is just a file on your personal computer, and you use the access application to... access it. with mysql or postgres, the db is a separate server that runs on another machine, and there is no built-in graphical interface. generally the way you build your end-user application with these databases is to hire some programmers. technically you could use a database client like pgadmin or phpmyadmin to interact with the database in a graphical way. however, those clients are really intended for database administrators and programmers, not end users.

# ? Oct 13, 2020 22:22

Bruegels Fuckbooks: Sep 14, 2004; Now, listen - I know the two of you are very different from each other in a lot of ways, but you have to understand that as far as Grandpa's concerned, you're both pieces of shit! Yeah. I can prove it mathematically.

DELETE CASCADE posted:

ms access is probably fine for you.

like potc said, access is basically a sql database combined with a forms interface that should be familiar to excel users, so basically you are getting the end-user application bundled with your database. you can probably buy something similar from oracle too, for $$$$$. but with popular open source databases like mysql and postgres, what you get is the backend database and that's it.

with an access database, the db is just a file on your personal computer, and you use the access application to... access it. with mysql or postgres, the db is a separate server that runs on another machine, and there is no built-in graphical interface. generally the way you build your end-user application with these databases is to hire some programmers. technically you could use a database client like pgadmin or phpmyadmin to interact with the database in a graphical way. however, those clients are really intended for database administrators and programmers, not end users.

To be honest, access has enough of a learning curve that I'm not sure how much better off you'd be with access over just installing a database like mysql or postgres and using those front end tools like phpmyadmin or pgadmin. I wouldn't say the UIs for pgadmin/phpmyadmin are very good, but access is opaque and using access doesn't translate to any other program.

# ? Oct 14, 2020 17:01

Just-In-Timeberlake: Aug 18, 2003

Bruegels Fuckbooks posted:

To be honest, access has enough of a learning curve that I'm not sure how much better off you'd be with access over just installing a database like mysql or postgres and using those front end tools like phpmyadmin or pgadmin. I wouldn't say the UIs for pgadmin/phpmyadmin are very good, but access is opaque and using access doesn't translate to any other program.

Nonsense, if they use Access then the next person to deal with it gets to curse their name for all eternity

# ? Oct 14, 2020 18:11

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

The main thing that separates Access from client-server model RDBMSes as I understand it, is that Access isn't designed for multiple people to interact with it at a time. If the DB is only going to be used by the business's employees (one at a time, ideally sitting at a specific computer) then Access is probably just fine. If you're going to have multiple users interacting with it or multiple programs reading/writing then Access is probably not a good idea.

If you think you will eventually want to add (for example) a website that customers can use to order or book stuff, then skip Access and go straight to a grown-up database.

# ? Oct 15, 2020 11:04

NihilCredo: Jun 6, 2011; iram omni possibili modo preme:
plus una illa te diffamabit, quam multæ virtutes commendabunt

It sounds like you're looking for AirTable or one of the many similar products, more than a pure database.

# ? Oct 15, 2020 11:25

Knot My President!: Jan 10, 2005

Crossposting:

Is there some kind of magical galaxy brain way to do SQL that I'm missing? I learned to do SQL a certain way and I'm at a loss as to why I got rejected after a technical take-home. The thing is that I know the answers are correct because I uploaded the tables into BigQuery to test all of my answers there.

I generally use table aliases / common table expressions in order to avoid using subqueries, and I avoid using self joins to filter data since they're generally harder aesthetically to read (unless there's a parent-child relationship in the same table). My syntax and methods are "highest ranked answer on Stack Overflow". After inheriting a ton of bad queries from the senior analyst, I went through and organized everything in such a way so anyone from top level down could make sense of my code even if they had no idea how to write SQL, including documentation that explained what everything did. But how I functionally write code on a job is apparently different from what a recruiter wants in an interview so I'm at a loss here :shrug:

I'm in my final rounds with another job I am excited for but I want to cover my bases nonetheless. Any ideas?

# ? Oct 21, 2020 01:34

Moonwolf: Jun 29, 2004; Flee from th' terrifyin' evil of "NHS"!

Knot My President! posted:

Crossposting:

Is there some kind of magical galaxy brain way to do SQL that I'm missing? I learned to do SQL a certain way and I'm at a loss as to why I got rejected after a technical take-home. The thing is that I know the answers are correct because I uploaded the tables into BigQuery to test all of my answers there.

I generally use table aliases / common table expressions in order to avoid using subqueries, and I avoid using self joins to filter data since they're generally harder aesthetically to read (unless there's a parent-child relationship in the same table). My syntax and methods are "highest ranked answer on Stack Overflow". After inheriting a ton of bad queries from the senior analyst, I went through and organized everything in such a way so anyone from top level down could make sense of my code even if they had no idea how to write SQL, including documentation that explained what everything did. But how I functionally write code on a job is apparently different from what a recruiter wants in an interview so I'm at a loss here

I'm in my final rounds with another job I am excited for but I want to cover my bases nonetheless. Any ideas?

If this is re: your post in poo poo that pisses me off, the hirers were insane. Doubly so if you were on new Postgres, because they've taken the pattern matching feature that Oracle put in the spec and implemented it, so there's a really performant way to do trend finding now rather than hideous joins.

Edit: Also table aliases are free, they're just letting you shorthand a name, just like renaming columns in a result set.

# ? Oct 21, 2020 08:06

Malcolm XML: Aug 8, 2009; I always knew it would end like ｔｈｉｓ．

Wait they implemented match recognize??? Holy poo poo

It's literally regex for everything

# ? Oct 21, 2020 14:45

GeorgieMordor: Jan 23, 2015

Is there an obvious way I'm missing to export a Postgres table with JSONB columns as a nested JSON object? I'm exporting now using several methods and they all dump that column out as an escaped JSON string.

# ? Oct 21, 2020 16:31

Empress Brosephine: Mar 31, 2012; by Jeffrey of YOSPOS

Hey all I have a similar question to the user above and I'm not sure if this is the best place for the question but:

I have a client that wants to be able to have a handful (20?) of their employees have some kind of UI to enter in certain data, which then hits a database which can be reported on with charts. The "catch" is some of that data needs to be exported to be used within a WordPress/Javascript site to display as basically time availability etc.

I'm guessing the answer is to do a postgres database which I could then access via Javascript (right?), but is there a good reporting tool that will grab the data and display it for them that is friendly to use?

I barely know SQL and have a basic understanding of Javascript, but I have until January to learn and deploy this. That said if any of you know of any "all in one" solutions I don't mind using something like that I. E. Access. I guess the most important thing is that it allows people to log in and submit data through a form or profile and then for it to have data be reportable and exposed for display.

Thanks goons

# ? Oct 21, 2020 18:14

Adbot: ADBOT LOVES YOU

# ? Jun 7, 2024 22:20

Dancer: May 23, 2011

I am not very aware of it myself, but GQL sounds like it eoukjd be appropriate for that.

# ? Oct 21, 2020 18:17

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

«‹›303 »