I have a query which is starting to cause some concern in my application. I'm trying to understand this EXPLAIN statement better to understand where indexes are potentially missing: +----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+...
Is there any performance impact or any kind of issues? The reason I am doing this is that we are doing some synchronization between two set of DBs with similar tables and we want to avoid duplicate PK errors when synchronizing data. ...
I am developing RoR application that works with legacy database and uses ActiveScaffold plugin for fancy CRUD interface. However one of the tables of my legacy db has composite primary key. I tried using Composite Keys plugin to handle it, but it seems to have conflicts with ACtiveScaffold: I get the following error: ActionView::Templa...
I am creating a desktop app in Delphi and plan to use an embedded database. I've started the project using SQlite3 with the DISQLite3 library. It works but documentation seems a bit light. I recently found Firebird (yes I've been out of Windows for a while) and it seems to have some compelling features and support. What are some pros ...
How can REVOKE operations on a table be audited in Oracle? Grants can be audited with... AUDIT GRANT ON *schema.table*; Both grants and revokes on system privileges and rolls can be audited with... AUDIT SYSTEM GRANT; Neither of these statements will audit object level revokes. My database is 10g. I am interested in auditing rev...
I've created a trigger that resembles the following: delimiter // CREATE TRIGGER update_total_seconds_on_phone AFTER INSERT ON cdr FOR EACH ROW BEGIN IF NEW.billsec > 0 AND NEW.userfield <> NULL THEN UPDATE foo SET total_seconds = total_seconds + NEW.billsec WHERE phone_id = NEW.userfield; END IF; END;// It seems to go through okay...
I'm looking for a database of commonly installed Windows software. At minimum I need the name of the software and the executable name, but it'd also be nice to have the publisher and the common installation path, etc. Basically, I'd like to be able to query it to find all the software by Adobe and the associated executable name, etc. B...
Which Oracle database role will allow a user to select from a table in another schema without specifying the schema identifier? i.e., as user A- Grant select on A.table to user B; B can then- "Select * from table" without specifying the 'A'. One of our databases allows this, the other returns a 'table or view does not exist' error. ...
What is MySql equivalent of the Nz Function in MS Access? Is Nz a SQL standard? In Access, the Nz function lets you return a value when a variant is null. Source The syntax for the Nz function is: Nz ( variant, [ value_if_null ] ) ...
I know that Open Office Database uses a java database backend. Does anyone have any insight on how this compares to the Jet Database Engine? Also is the query designer/reporting nearly as robust as MS Access? ...
Which industry-class database has the most unique features? (with "unique" meaning that no other RDBMS has them) I think my choice here is Oracle 11g: Flashback query (you can estract data as it was a moment in the past) ASM - automatic storage management Native code compilation of stored procedures Audit features (tracing everything,...
Suppose you have two models, User and City, joined by a third model CityPermission: class CityPermission < ActiveRecord::Base belongs_to :city belongs_to :user end class City < ActiveRecord::Base has_many :city_permissions has_many :users, :through => :city_permissions end class User < ActiveRecord::Base has_many :city_permi...
I'm using Cocoon and want to store the jobs and triggers for the quartz scheduler in the database so they are persisted. I can see where I need to make the change in cocoon.xconf but I can't find much on how to configure the datasource etc. How do I configure this to use our existing (postgres) database? ...
We have just 'migrated' an SQL Server 2005 database from DEVEL into TEST. Somehow during the migration process the DB was changed from case insensitive to sensitive - so most SQL queries broke spectacularly. What I would like to know, is - are there any clear benefits to having a case sensitive schema? NOTE: By this I mean table nam...
Hi, I'm using a decimal column to store money values on a database, and today I was wondering what precision and scale to use. Since supposedly char columns of a fixed width are more efficient, I was thinking the same could be true for decimal columns. Is it? And what precision and scale should I use? I was thinking precision 24/8. Is...
I'm building a mobile application in VB.NET (compact framework), and I'm wondering what the best way to approach the potential offline interactions on the device. Basically, the devices have cellular and 802.11, but may still be offline (where there's poor reception, etc). A driver will scan boxes as they leave his truck, and I want to u...
I have a database in MySQL that I am currently developing. I have a copy of this database on my development machine which I modify as fast as I develop and a copy on a test server. My question is: Is there a way to compare the two instances of the database to see if there was any changes? Its not a real problem to simply re-deploy the ...
Hello, I have to add a coupon table to my db. There are 3 types of coupons : percentage, amount or 2 for 1. So far I've come up with a coupon table that contains these 3 fields. If there's a percentage value not set to null then it's this kind of coupon. I feel it's not the proper way to do it. Should I create a CouponType table and h...
I have a SQL Server 2005 sp2 box where tempdb has either 8 data files or 5 data files dependingo n where you look. DBCC showfilestates and sys.database_files (both queried in tempdb) show 8 data files (tempdev - tempdev8), however when I query sys.master_files (in master db, which is also what the GUI uses), I only see 5 (tempdev, tempd...
Suppose a database table has a column "Name" which is defined as key for the table. Usual name values will be "Bill", "Elizabeth", "Bob", "Alice". Lookups on the table will be done by the name key as well. Does hashing the values optimize the operations in any way? i.e. entering each name as some hashed value of the name (suppose MD5 - ...