*** 46,86 ****
*: ALTER VIEW, ALTER TRIGGER, ALTER TABLE
! *: Schemas - See: http://www.postgresql.org/docs/8.1/static/ddl-schemas.html
!
! _::: The idea is that multiple users using the same database can cleanly
! separate their tables, views (stored procs, etc) by prefixing them with
! their login, so jack's jack.importantTable is distinct from jill's
! jill.importantTable. There are
! administrative benefits ('Jack left and we don't like his work; can we kill
! everything he did?' Ans: 'Yes, let
! me just
! drop his schema..', with aliases, jill.importantTable can be made available to
! everybody as
! 'importantTable', permissions can be hung off schemas). The common notation
! (jill.importantTable)
! would map to databasename.tablename in the current sqlite arrangement.
!
! _:::: This doesn't really make a lot of sense for an embedded database.
!
! _::::: I could use this. Im trying to use sqlite as a 'fake database'
! that i can use in testing suite. For sqlite to be a good 'fake' of
! something like Oracle it would help a lot if it had the ability to do stuff
! like 'select * from blah.PERSON'. In this case the 'blah' is the schema name.
! PERSON is the table name.
! Another example: 'select zipcode from blorg.ADDRESS'. blorg is the schema
! name, ADDRESS is the
! table name.
! right now it is giving 'no such database as blah' or 'no such database as
! blorg'. i try to 'create database blah' but of cousre that doesnt work either.
!
! _::::: at the very least, it could accept table names that have
! a '.' in them. this would fake schemas good enough for me. right now
! it doesnt seem to allow it.
!
! _:::::: You can fake this syntax if you split the "schemas" off into seperate
! files, then do an ATTACH
! DATABASE blorg.db AS blorg; SELECT zipcode FROM blorg.address;
*: TRUNCATE (MySQL, Postgresql and Oracle have it... but I dont know if this is
a standard command) -
--- 46,52 ----
*: ALTER VIEW, ALTER TRIGGER, ALTER TABLE
! *: Schemas - See: http://www.postgresql.org/docs/8.1/static/ddl-schemas.html -> UnsupportedSqlSchemas
*: TRUNCATE (MySQL, Postgresql and Oracle have it... but I dont know if this is
a standard command) -
***************
*** 92,160 ****
*: CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle)
*: UPDATE with a FROM clause (not sure if this is standard, Sybase and
! Microsoft have it).
!
! _:: Postgres also allows "UPDATE ... FROM ... ", BTW. (As does Ingres --CAU)
!
! _:: I was working on something where I really wanted to use this construct with
! SQLite, so I came up
! with the following hack:
!
! --
! -- SQLite does not allow "UPDATE ... FROM"
! -- but this is what it might look like
! --
! UPDATE
! t1
! SET
! measure = t2.measure
! FROM
! t2, t1
! WHERE
! t2.key = t1.key
! ;
!
! --
! -- emulating "UPDATE ... FROM" in SQLite
! --
! -- n.b.: it assumes a PRIMARY KEY !
! --
! -- the INSERT never succeeds because
! -- the JOIN restricts the SELECT to
! -- existing rows, forcing the REPLACE
! --
! INSERT OR REPLACE INTO
! t1( key, measure )
! SELECT
! t2.key, t2.measure
! FROM
! t2, t1
! WHERE
! t2.key = t1.key
! ;
!
! _:: Since that works, maybe SQLite could be made to support the "UPDATE ...
! FROM" construct directly,
! so we would not have to rely on conflict resolution to do essentially the same
! thing (not exactly the
! same, since REPLACE is DELETE and INSERT, but sometimes close enough). *<
! gifford hesketh::2004-
! Oct-26*
!
! _:: I've managed successfully to do this an alternative way, works in version
! 3.2.1 (--CAU:18-Aug
! -2005) ...
!
! --
! -- emulating "UPDATE ... FROM" in SQLite
! --
! --
! UPDATE
! t1
! SET
! measure = ( SELECT measure FROM t2 WHERE t2.key = t1.key )
! ;
!
*: Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN
(SELECT...)
--- 58,64 ----
*: CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle)
*: UPDATE with a FROM clause (not sure if this is standard, Sybase and
! Microsoft have it). -> UnsupportedSqlUpdateWithFrom
*: Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN
(SELECT...)
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|