Implementation of temporary table

[Ivan Smahin]

Hello All,

  Here is some points we want to discuss regarding temporary tables
  in Valentina.

  There are two different types of temporary tables - local and
  global ones.

  Global temporary table is the table which available for all users.
  Actually we support global temporary table at the moment - so it is
  not a matter of discussion.



  We are going to support local tables. But there are some problems.

  Local temporary table is the table which available for current user
  only. So it could be automatically dropped after user disconnect.
  Different users may create such tables with the same name without
  any problems. It would be even the name of existed permanent table.


  Oracle, Postgre create such table in the special temporary schema
  using sql-standard syntax: "CREATE LOCAL|GLOBAL TABLE..."
  MySQL do something like that to.

  Sybase, MS-SQL use the special names to mark the table as temporary
  - The name started with # - means local temporary table and ##
  means global one. ( CREATE TABLE #t1 (f1 int ) )


  Each user may create own temporary table "t1". Moreover - it could
  be global temporary or permanent table t1 also.

  So there are some local tmp tables named t1 and each one obviously
  marked with some userID to differ them.
  Usually, table name is not just t1 but something like t1_123456 -
  where 123456 is userID.

  Now you need the way to access such table. In case of Sybase or
  MS-SQL you write something like "SELECT * FROM #t1". Here is the
  information - we are trying to select records from local temporary
  table (because of #). So we can easily get one comparing current
  userID with userID placed inside table name and choose right one.

  In case of Oracle you will be required to specify the schema to
  distinguish the table which you want to get (it could be permanent
  t1 also).

  BTW - Postgre documentation says: we are ignoring GLOBAL TEMPORARY
  TABLE clause. They support only LOCAL TEMPORARY.
  And this is also from their documentation:
  "The standard’s definition of the behavior of temporary tables is widely ignored. "


  Valentina has no schemas - so we are not able to distinguish t1-
  persistent from t1-local (It seems MySQL is not able to do it too - so
  there are some "effects" having both t1 - permanent and local)

  We are going to

  1. Left "GLOBAL TEMPORARY TABLE" part unchanged. So - "CREATE
  TEMPORARY TABLE t1(..." produced some global temporary table named
  t1.

  2. Ignore "LOCAL TEMPORARY TABLE" clause like Postgre do for global
  one. And use Sybase approach for it - "CREATE TABLE #t1...".
  Because there is no easy and speedy way to allow having two tables - permanent and
  temporary both named t1 at the same time.
  SELECT * FROM t1 contains no info - which table you want to get.


  What do you think?


-- 
Best regards,
 Ivan                          mailto:ivan_smahin at paradigmasoft.com

_______________________________________________
Vdevelopers mailing list
Vdevelopers at paradigma.ukrcom.kherson.ua
http://192.168.2.1/mailman/listinfo/vdevelopers