Questions tagged [database-agnostic]
This tag specifies that the question is independent of any particular database and the sql should work in any rdbms.
100
questions
1841
votes
27
answers
3.4m
views
Insert into ... values ( SELECT ... FROM ... )
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the ...
- 41.9k
167
votes
4
answers
163k
views
Database cluster and load balancing
What is database clustering? If you allow the same database to be on 2 different servers how do they keep the data between synchronized. And how does this differ from load balancing from a database ...
- 13.6k
102
votes
6
answers
280k
views
Function that creates a timestamp in c#
I was wondering, is there a way to create a timestamp in c# from a datetime?
I need a millisecond precision value that also works in Compact Framework(saying that since DateTime.ToBinary() does not ...
- 13.6k
37
votes
8
answers
34k
views
When to use database views and when not?
This question is about database views, not materialized-views.
Pros:
Query simplification.
Avoid repeat the same joins on multiples queries.
Avoid magic numbers.
Cons:
Hiding real queries (may be ...
- 36.2k
37
votes
7
answers
20k
views
Should many to many tables have a primary key?
If I have two objects that have a many-to-many relationship, I would typically model them in my database schema with a many-to-many table to relate the two. But should that many-to-many table (or "...
- 10k
24
votes
6
answers
21k
views
Does "group by" automatically guarantee "order by"?
Does "group by" clause automatically guarantee that the results will be ordered by that key? In other words, is it enough to write:
select *
from table
group by a, b, c
or does one have to write
...
- 58.5k
22
votes
4
answers
59k
views
Difference between Restrictions.like and .ilike in Hibernate Criteria API
Hibernate's Criteria API has Restrictions.ilike function which has the following contract:
A case-insensitive "like", similar to Postgres ilike operator
That's cool. But the same class also has ...
- 26.6k
20
votes
8
answers
9k
views
Best Schema to represent NCAA Basketball Bracket
What is the best database schema to represent an NCAA mens basketball bracket? Here is a link if you aren't familiar: http://www.cbssports.com/collegebasketball/mayhem/brackets/viewable_men
I can see ...
- 13.7k
18
votes
9
answers
37k
views
What data type is recommended for ID columns?
I realize this question is very likely to have been asked before, but I've searched around a little among questions on StackOverflow, and I didn't really find an answer to mine, so here goes. If you ...
- 59.5k
18
votes
2
answers
22k
views
How to get fully materialized query from querydsl
I am trying to use querydsl for building dynamic queries for dynamic schemas. I am trying to get just the query instead of having to actually execute it.
So far I have faced two issues:
- The schema....
- 2,147
16
votes
6
answers
8k
views
Fast Relational method of storing tree data (for instance threaded comments on articles)
I have a cms which stores comments against articles. These comments can be both threaded and non threaded. Although technically they are the same just with the reply column left blank when it's not ...
- 3,529
14
votes
4
answers
8k
views
Creating indexes with descending key columns with Liquibase
I am wondering if there is a generic way to create an "ordered index" with liquibase. Something that will produce these kind of SQL statement:
CREATE INDEX idx_name ON my_table (m_column DESC)
I ...
- 7,183
10
votes
2
answers
2k
views
DB agnostic SQL for CURRENT_TIMESTAMP
I'm currently using Oracle, but most of the SQL is db agnostic. One exception is CURRENT_TIMESTAMP which fails in MSSQL. Is is possible to move to a more portable syntax for this too?
- 3,849
8
votes
6
answers
1k
views
Select the maximum value for each (Col1,Col2)
Lets assume the following table:
Name SubName Message Time
USA MA M1 1
USA NY M2 2
USA WA ...
- 1,395
8
votes
5
answers
4k
views
How do you write your applications to be database independent?
My boss asks me to write only ANSI SQL to make it database independent. But I learned that it is not that easy as no database fully ANSI SQL compatible. SQL code can rarely be ported between database ...
- 24.7k
8
votes
2
answers
739
views
How can this SQL query code be broken/exploited by user input? [duplicate]
Possible Duplicate:
Can I protect against SQL Injection by escaping single-quote and surrounding user input with single-quotes?
We have a legacy app that doesn't do queries using positional ...
- 12.3k
7
votes
5
answers
928
views
Is NOW() a stable function?
If I do
INSERT INTO table1 (datetime1, datetime2) VALUES (NOW(),NOW())
Will the two fields always be identical in both columns?
Ditto for
INSERT INTO table1 (datetime1, datetime2) VALUES (NOW()...
- 75.1k
7
votes
4
answers
532
views
Implementing a Flexible Relationship in a RDBMS -- What really are the tradeoffs?
I have a bunch of products with a bunch of different possible attributes for each product. E.g. Product A has a name, size, color, shape. Product B has a name, calories, sugar, etc. One way to solve ...
- 4,259
7
votes
1
answer
4k
views
Distributed primary key - UUID, simple auto increment or custom sequential values?
I know this type of question has been asked before, but I could not find one that compared the options I have in mind. So I am going to post them here, please post links if there are duplicates.
...
- 981
6
votes
3
answers
7k
views
How to construct query in querydsl without domain classes
While looking for java libraries to build queries in a database agnostic way I came across many including iciql, querydsl, jooq, joist, hibernate etc.
I wanted something that does not require ...
- 2,147
6
votes
4
answers
2k
views
Is converting database string enums to integers worth it?
There are two ways to store enum types in database: as a string or as an integer.
Saving the enumeration ( sex = {male,female}, account_type = {regular,pro,admin}, etc. ) as strings makes things more ...
- 12.1k
6
votes
3
answers
1k
views
Standard database neutral XSD to describe a relational database schema
Is anyone aware of a vendor neutral XSD to describe a relational database schema? Our system needs to grab information about the structure of a database:
Tables
Columns and types
Primary and ...
- 347
5
votes
3
answers
648
views
Database neutral .NET application
Many product developers want to write a .NET application which will work seamlessly with any popular RDBMS like SQL server, oracle, DB2 , MySql. If we use the Data application block it dynamically ...
- 2,525
5
votes
2
answers
857
views
Difference between JOIN...ON...JOIN...ON vs JOIN...JOIN...ON...ON
What, if any, is the difference between the following?
FROM table1
LEFT JOIN table2 ON table1.key = table2.key
LEFT JOIN table3 ON table2.key = table3.key
and:
FROM table1
LEFT JOIN table2
LEFT ...
- 622
5
votes
1
answer
3k
views
Changing timezone on an existing Django project
Like an idiot, I completely overlooked the timezone setting when I first built an application that collects datetime data.
It wasn't an issue then because all I was doing was "time-since" style ...
- 238k
5
votes
0
answers
726
views
How to write a DB-agnostic .NET Core app?
I'm struggling to port a console app from .NET Framework 4.6.2 to .NET Core 2.0. The app needs to connect to a database whose platform is not known at compile time. In the full framework this was ...
- 9,746
4
votes
4
answers
1k
views
Best way to enforce inter-table constraints inside database
I looking for the best way to check for inter-table constraints an step forward of foreing keys. For instance, to check if a date child record value is between a range date on two parent rows columns. ...
- 36.2k
4
votes
2
answers
8k
views
What's a good tool to run SQL scripts against any database?
Currently I have a custom tool which generates vanilla-SQL (only using standard SQL), from our Data-models that sets up database schemas and initial data for our new databases, and do version patches, ...
- 69.3k
3
votes
6
answers
2k
views
Primary Keys - Native, Sequence, or GUID keys?
In reading this and this and then reading this (which references the other two ironically) I find myself wondering just how big the discussion of this topic is? I am a SQL Server guy and so I tend to ...
- 10.2k
3
votes
4
answers
315
views
SELECT DISTINCT on a field not appearing in recordset?
I want to do a SELECT DISTINCT guid, ..., but I don't want guid appearing in the recordset. How do I do this?
- 17.7k
3
votes
3
answers
364
views
Agnostic Connection Handlers in .NET
I'm developing a simple database acessor for a system, but i need to support 2 databases, PostgreSQL and Oracle (XXg);
A example would be like this (already working for pgsql)
... bunch of ...
- 3,484
3
votes
2
answers
603
views
Database design for tags and URL
I'm building simple application for myself in JSP which stores URL for me and finds it based on tags. For which i want to design a database. I'm limited with my knowledge of SQL. But still i want to ...
- 183
3
votes
3
answers
342
views
ANSI sql for comparison with string and non-string
Let's say I have any one of the following expression:
SELECT
DATE '2014-01-01' < '2014-02-01',
DATE '2014-01-01' < '321',
9 < '10',
9 < 'a'
Is there a recommendation or ...
- 108k
3
votes
3
answers
331
views
How to create portable inserts from SQL Server?
Now it generates inserts like
INSERT [Bla] ([id], [description], [name], [version])
VALUES (CAST(1 AS Numeric(19, 0)), convert(t...
It's very SQL Server specific. I would like to create a script ...
- 63.4k
3
votes
4
answers
618
views
Is it possible to search for dates as strings in a database-agnostic way?
I have a Ruby on Rails application with a PostgreSQL database; several tables have created_at and updated_at timestamp attributes. When displayed, those dates are formatted in the user's locale; for ...
- 2,941
3
votes
3
answers
310
views
Where are specific collations used in a modern utf application
I was wondering if collation is still a big issue when working with databases without legacy data.
For example, for something like BigQuery or Snowflake the character encoding is utf-8. BigQuery ...
- 108k
3
votes
3
answers
231
views
How to group the records according to the range in SQL
Hi I would like to create a SQL to group the records according to the range
For example, suppose I have
Number Time Price
100 20100810 10.0
100 20100812 15.0
...
- 25.5k
3
votes
1
answer
3k
views
CakePHP Multiple Nested Joins
I have an App in which several of the models are linked by hasMany/belongsTo associations. So for instance, A hasMany B, B hasMany C, C hasMany D, and D hasMany E. Also, E belongs to D, D belongs to ...
user35288
2
votes
2
answers
109
views
SQL: Group by on value1 and "not" value1?
I have a table with something like the following:
OrderNo, OrderType
1, Type1
2, Type1
3, Type2
4, Type3
5, Type4
Doing a "group by" on OrderType is easy but is there a way to do a "group by" to get ...
- 2,613
2
votes
4
answers
2k
views
Database independence
We are in the early stages of design of a big business application that will have multiple modules. One of the requirements is that the application should be database independent, it should support ...
- 531
2
votes
4
answers
2k
views
Database Agnostic Application
The database for one the application that I am working on is not confirmed yet by the business.
Best guess is Oracle and DB2.
What I've heard is initially the project will go live with DB2 V9 and ...
- 1,721
2
votes
2
answers
209
views
Why is NULL handled differently in aggregate and scalar functions?
Let's take the following two queries:
select greatest(1, val) from unnest([null]) as val
# null
And:
select sum(val) from unnest([1, null]) as val
# 1
My question is why is null handled differently ...
- 108k
2
votes
1
answer
1k
views
Slick generic AND driver agnostic
Basically what I want to achieve is a combination of:
Slick 3.0.0 database agnostism
and
Slick 3 reusable generic repository
I tried a lot, actually, but I can't get this to work at all.
abstract ...
user4063815
2
votes
2
answers
3k
views
Magento installer setup agnostic insert
I got through this tutorial regarding Magento Setup Resources.
At some point I saw that it supports more database backends, but in the mentioned tutorial is described only the way you can create ...
- 4,846
2
votes
4
answers
96
views
Index on a table you must fully scan anyway? (MySQL)
I'm pretty stumped here.
I have 2 tables and I'm left joining the first (around 500k records) with the second (around 2.2 million records) in order to find out which records are in the first and not ...
- 1,448
2
votes
1
answer
1k
views
jOOQ: Database-agnostic way of disabling constraints
I'd like to know if there's a way to disable database constraints in jOOQ for different databases in an agnostic way. For example, disabling foreign key constraints in MySQL and PostgreSQL differs ...
- 381
2
votes
1
answer
220
views
Relational vs NoSql: Which database should I use for this kind of app? [closed]
I am about to build an application which does analytics on student results. Ghe data that needs to be stored looks like this:
Course: name
Semester: 1st,2nd etc
Student: name
Subject1: marks
Subject2:...
- 33.8k
2
votes
3
answers
518
views
SQL : built-in way to detect gaps in a arbitrary series of dates?
I have a set of tables that feed into a historical reports. It's taking a while to generate the report now, and I want to create cache tables that will store the compiled data that then feed into the ...
- 17.7k
2
votes
5
answers
432
views
How do I normalise this database design?
I am creating a rowing reporting and statistics system for a client where I have a structure at the moment similar to the following:
-------------------------------------------------------------------...
- 1,794
2
votes
2
answers
275
views
optimize query with column in where clause
I have an sql query which fetch the first N rows in a table which is designed as a low-level queue.
select top N * from my_table where status = 0 order by date asc
The intention behind this query is ...
- 10.2k