I'm looking for a script or application that tidies up and reformats SQL queries. I've found some good online SQL formatters (see here) , as well as some downloadable commercial applications. I'm cautious about using an online service as I don't want to risk copies of these queries being stored somewhere they might be compromised, if on...
Hello, How can I best set up my PHP (LAMP) development environment so that I have development, staging and production servers. One-"click" deployment to any of those, as well as one-click rollback to any revision. Rollback should also rollback the database schema and data to how it was when that source code was current. Right now I'v...
I have three related tables "A(id, val)", "B(id, val)", and a link table with a value "AB(aid, bid, val)" I am querying against B to bring back A values, for example: SELECT A.* FROM A INNER JOIN AB ON A.id = AB.aid INNER JOIN B ON AB.bid = B.id WHERE B.val = 'foo'; Every A has many B's and every B has many A's. And the catch that ...
I'm implementing an audit log on a database, so everything has a CreatedAt and a RemovedAt column. Now I want to be able to list all revisions of an object graph but the best way I can think of for this is to use unions. I need to get every unique CreatedAt and RemovedAt id. If I'm getting a list of countries with provinces the union lo...
In a database I am designing I have implemented profile based object level security. Each user can view, edit, insert, update database tables according to the profiles (roles) he is a member of. Now there is a need to implement "External Users" who can view only the relevant records and edit some of them (but not the bulk of the database...
Hi. I have some products that belongs to the some category. Each category can have different properties. For example, category cars has properties color, power, ... category pets have properties weight, age, ... Number of categories is about 10-15. Number of properties in each category is 3-15. Number of products is very big. M...
Hi All, I'm in the process of designing a small website and was curious when an XML file can/should be substituted for a database table. There are some cases where I think using a database table may be overkill and was just wondering if anyone else has come across making this decision. Thanks! ...
I'm familiar with Sybase / SQL server, where I can create a temp. table like this: SELECT * INTO #temp FROM tab1 , tab2 WHERE tab1.key = tab2.fkey SELECT * FROM #temp WHERE field1 = 'value' #temp only exists for the duration of this session, and can only be seen by me. I would like to do a similar thing in Ora...
I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:...
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: +----+-------------+-------+--------+---------------+------------+---------+-------------------------------+------+---------------------------------+...
I need help with realizing quite complex business logic which operates on many tables and executes quite a few SQL commands. However I want to be sure that the data will not be left in incosistent state and to this moment I don't see the solution which would not require nested transactions. I wrote a simple pseudo-code which illustrates ...
I've tried Red Gate's SQL Prompt and like it, but cannot afford it at the moment. I miss the good code completion that this tool provides. Are there any good free tools out there that have SQL code completion (table and column names, etc)? I've tried TOAD, but it was quite buggy. It would crash, and would also leave connections open ...
Can anyone give me a complete list of string manipulation function in Microsoft SQL Server (2000 or 2005)? (I don't need a lecture about doing all my string processing in the presentation layer. And, I don't need a list of MySQL string functions.) Thanks! ...
I'm running Oracle 10g and have columns with Type_Name TIMESTAMP(6) WITH TIME ZONE When inflated into java classes they come out as oracle.sql.TIMESTAMPTZ But DbUnit can't handle converting Oracle specific classes to Strings for writing to XML. I'm wondering if there's any easy way for me to convert (say, in my SELECT statement so...
Having difficulty articulating this correlated subquery. I have two tables fictitious tables, foo and bar. foo has two fields of foo_id and total_count. bar has two fields, seconds and id. I need to aggregate the seconds in bar for each individual id and update the total_count in foo. id is a foreign key in bar for foo_id. I've tried ...
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 ] ) ...
Hi to all, I'm trying to get a query working that takes the values (sometimes just the first part of a string) from a form control. The problem I have is that it only returns records when the full string is typed in. i.e. in the surname box, I should be able to type gr, and it brings up green grey graham but at present it's not bring...
Is there a way to merge two primary keys into one and then cascade update all affected relationships? Here's the scenario: Customers (idCustomer int PK, Company varchar(50), etc) CustomerContacts (idCustomerContact int PK, idCustomer int FK, Name varchar(50), etc) CustomerNotes (idCustomerNote int PK, idCustomer int FK, Note Text, et...
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...
Suppose I have a bunch of varchar(6000) fields in a table and want to change those to text fields. What are the ramifications of the stored procedures whose arguments are of type varchar(6000). Does each stored procedure also need those argument data types changed? ...