ORACLE - tables
Hi, After entering code to create a new table in SQL ORACLE, would the changes be saved if I was to log out of the SQL Session? ...
ddl
Is there a tool to convert DBIx::Class Schema (or just SQL) to XSD
I am using perl DBIx::Class ORM and would like to be able to dynamically generate an XSD either from the DBIX::Class schema or directly from the SQL DDL. I know it would probably be better that we start with XSD and generate our SQL DDL from the XSD but we are not in a position to do that yet. thanks, Tom ...
What database user permissions are needed?
FYI: SQL Server 2005 I have a database user account (user_web) that has the ability to connect to and run queries and stored procedures in my database. Specifically, I have given the user the db_datareader and db_datawriter roles as well as granted them execute permission on the specific stored procedures it needs to be able to run. In...
Versioning SQL Server DDL code
I'd like to have all DB DDL code under CVS. We are using Subversion for our .NET code. But all Database code remains still unversioned. All we know how important DB logic can be. I've googled but I've found only few (expensive tools) I believe there exist other (cheaper) solution(s) I'm wonder your approach. How do you implemente...
Column type of 'set' and Rails
I am building a database of online offers. Each offer can be viewed in one or more countries, for instance an advertiser may be interested in reaching offers in the US & Canada. The list of countries we are covering is about 50 long with each country identified with an ISO standard two letter acronym like US, CA, GB, FR, DE, etc. I cou...
How to auto-redefine view when underlying table changes (new column)?
We've got a view that's defined like this CREATE VIEW aView as SELECT * from aTable Where <bunch of conditions>; The "value" of the view is in the where-condition, so it is okay to use a Select * in this case. When a new column is added to the underlying table, we have to redefine the view with a CREATE OR REPLACE FORCE VIEW aView...
Oracle Data Versioning/Partitioning Strategies/Best Practices
Hi there, not sure if the subject entirely conveys what I'm trying to achieve, but let me explain: We are building an application that uses Oracle as storage backend. Each year, last years dataset will be "Archived", and a new instance created and populated from scratch. What are the options to do this within the same schema? Keep ve...
Can I detect the version of a table's DDL in Oracle?
In Informix, I can do a select from the systables table, and can investigate its version column to see what numeric version a given table has. This column is incremented with every DDL statement that affects the given table. This means I have the ability to see whether a table's structure has changed since the last time I connected. I...
Why sql-script isn't executed?
CREATE TABLE PERMISSIONS( ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(255) NOT NULL, UNIQUE(ID) ) CREATE TABLE ROLES( ID BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR(255) ) I want to run this in MySql. When I try to execute separately each create-query everything works fine but they don't work together. I thought that separ...
Postgres Command Line tool for Import/Exporting data/ddl
For Postgres is there any sort of command line utilities that allow a database to be "dumped to a file" and that allow that same database dump to be imported? I know this can be done through PGAdmin, but I need to be able to do this on the cmd line. ...
SQL Server DDL script to append (or drop) the same set of columns for every table in a database?
How would one write a sql server DDL script that can: For each table in database: add column CreatedBy add column CreateDate add column UpdatedBy add column UpdatedDate Also, if the particular column already exists on the table, just skip that column. Also, the reverse of it, for each table, drop those 4 columns. ...
How do I get column datatype in Oracle with PL-SQL with low privileges?
I have read only access to a few tables in an Oracle database. I need to get schema information on some of the columns but i'm having trouble doing so. I'd like to use something analogous to MS SQL's sp_help. I see the table i'm interested in listed in this query. SELECT * FROM ALL_TABLES When I run this query, Oracle "tells me table ...
Oracle DDL in autonomous transaction
I need to execute a bunch of (up to ~1000000) sql statements on an Oracle database. These statements should result in a referentially consistent state at the end, and all the statements should be rolled back if an error occurs. These statements do not come in a referential order. So if foreign key constraints are enabled, one of the stat...
Fluent NHibernate Automapping DDL is missing foreign key reference column
I've created the following classes and use the automap functionality of fluent to automatically generate my database: public interface IBaseClass { int Id { get; set; } DateTime CreatedOn { get; set; } } public interface IApplicant : IBaseClass { string Firstname { get; set; } string Lastnam...
How should I migrate DDL changes from one environment to the next?
I make DDL changes using SQL Developer's GUI. Problem is, I need to apply those same changes to the test environment. I'm wondering how others handle this issue. Currently I'm having to manually write ALTER statements to bring the test environment into alignment with the development environment, but this is prone to error (doing the s...
SQL Server Conditional Foreign Key Constraints
I'm having trouble figuring out how to create a foreign key constraint. My data model is fixed and out of my control, it looks like this: CREATE TABLE Enquiry (Enquiry_Ref INTEGER PRIMARY KEY CLUSTERED, Join_Ref INTEGER, EnquiryDate, EnquiryType...) CREATE TABLE Contact (Contact_Ref INTEGER PRIMARY KEY CLUSTERED, Surname, Forenam...
guarantee child records either in one table or another, but not both?
I have a table with two child tables. For each record in the parent table, I want one and only one record in one of the child tables -- not one in each, not none. How to I define that? Here's the backstory. Feel free to criticize this implementation, but please answer the question above, because this isn't the only time I've encountered...
How do I run a script on/in a mysql database?
I have created a MySQL database using MySQL Workbench. It has about 20 tables. I cannot figure out how to run scripts on the database. Basically, I want to make a database creation script which will allow me to create my database on any other MySQL server. ...
How to increase mysql table comments length?
Seems like max table comments length in mysql is only 60 characters. I'm developing an aplicacion in php symfony, which automatically generates sql ddl sentences, and in many cases those comments are far beyond 60 characters. Is there some way to increase that limit? thanks a lot ...
Is there a declarative language for data definitions?
Reading about WPF and thinking about my application's data store at the same time led me to wonder if there are any languages or tools that allow you to define relational data in a declarative way? A shallow Google search suggests no such thing exists. Yet it seems so obviously useful. The kind of tool I have in mind would declaratively...