Qore SqlUtil Module Reference  1.2
 All Classes Namespaces Functions Variables Groups Pages
SqlUtil.qm.dox.h
1 // -*- mode: c++; indent-tabs-mode: nil -*-
2 // @file SqlUtil.qm Qore user module for working with SQL data
3 
4 /* SqlUtil.qm Copyright (C) 2013 - 2014 Qore Technologies, sro
5 
6  Permission is hereby granted, free of charge, to any person obtaining a
7  copy of this software and associated documentation files (the "Software"),
8  to deal in the Software without restriction, including without limitation
9  the rights to use, copy, modify, merge, publish, distribute, sublicense,
10  and/or sell copies of the Software, and to permit persons to whom the
11  Software is furnished to do so, subject to the following conditions:
12 
13  The above copyright notice and this permission notice shall be included in
14  all copies or substantial portions of the Software.
15 
16  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
17  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
18  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
19  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
20  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
21  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
22  DEALINGS IN THE SOFTWARE.
23 */
24 
25 // this module requires Qore 0.8.11 or better
26 
27 // requires the Util module
28 
29 // don't use "$" signs for variables and class members, assume local variable scope
30 
31 // require type definitions everywhere
32 
33 // enable all warnings
34 
35 
36 // version history is included below in the docs
37  ...
296  @endcode
297  @warning Oracle: using different comments in the same SQL can lead to new optimizer statement hard parsing.
298 
299  @anchor select_option_hint
300  @par Select Option "hint"
301  In database query operations, various SQL implementations use hints as additions to the SQL standard that instruct the database engine on how to execute the query. For example, a hint may tell the engine to use as little memory as possible (even if the query will run slowly), or to use or not to use an index (even if the query optimizer would decide otherwise).
302 
303  <b>Hint Example:</b>
304  @code
305 $table.selectRows( ("hint" : "full(t1)") );
306  @endcode
307  will produce select statement like this:
308  @code
309 select /*+ full(a) */ ...
310  @endcode
311  The string is taken as is and it's up to user to handle correct aliases in join functions etc.
312  @note Hints are platform dependent. Curently only Oracle and some versions of PostgreSQL hints are supported in Sqlutil module.
313  @warning Use hints only when you know what you are doing.
314 
315  @anchor select_option_columns
316  @par Select Option "columns"
317  <b>Columns Example:</b>
318  @code
319 my list $columns = ("id", "name", "started", cop_as("warnings", "warning_count"), cop_as("errors", "error_count"));
320 my *list $rows = $table.selectRows(("columns": $columns, "where": ("type": "user")));
321  @endcode
322  By default, all columns are returned from a query; to limit the columns returned, or to perform column operations on the columns returned, use the \c "columns" option of the @ref select_option_hash "select option hash". \n\n
323  This option takes a list, each element of the list can be one of the following.\n\n
324  <b>A Simple String Giving a Column Name</b>\n
325  ex: \c "name"
326  @code
327 my *list $rows = $table.selectRows(("columns": ("id", "name", "started")));
328  @endcode \n
329  <b>A String in Dot Notation</b>\n
330  This format is for use with @ref select_option_join "joins"; ex: \c "q.name"
331  @code
332 my *list $rows = $table.selectRows(("columns": ("table.id", "t2.customer_name"), "join": join_inner($table2, "t2", ("id": "altid"))));
333  @endcode \n
334  <b>A Column Operation Specified by a Column Operator Function</b>\n
335  ex: <tt>cop_as("column_name", "column_alias")</tt> \n
336  See @ref sql_cop_funcs "column operator function" for more information on column operator functions
337  @code
338 my *list $rows = $table.selectRows(("columns": ("id", cop_as("warnings", "warning_count"), cop_as("errors", "error_count"))));
339  @endcode
340  For @ref sql_cop_funcs "column operator functions" taking a column name, either a string name or a name in dot notation is acceptable\n\n
341  <b>The Value \c "*", Meaning All Columns</b>\n
342  ex: \c "*"
343  @code
344 my *list $rows = $table.selectRows(("columns": "*"));
345  @endcode
346  This is the default if no \c "columns" key is included in the @ref select_option_hash "select option hash" \n\n
347  <b>An \c "*" in Dot Notation</b>\n
348  ex: \c "q.*"
349  @code
350 my *list $rows = $table.selectRows(("columns": ("table.id", "t2.*"), "join": join_inner($table2, "t2", ("id": "altid"))));
351  @endcode
352 
353  @anchor select_option_where
354  @par Select Option "where"
355  <b>Where Example:</b>
356  @code
357 my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
358  @endcode
359  The hash value assigned to this key describes how the \c "where" clause in the query is built. Because the \c "where" clause logic is common to many SQL methods, this topic is covered in a separate section. See @ref where_clauses for a detailed description of the value of this key.
360 
361  @anchor select_option_orderby
362  @par Select Option "orderby"
363  <b>Orderby Example:</b>
364  @code
365 my *list $rows = $table.selectRows(("where": ("account_type": "CUSTOMER"), "orderby": "created_date"));
366  @endcode
367  This option is a list of the following values:
368  - a simple string giving a column name; ex: \c "name"
369  - a string giving a table or table alias and a column name in dot notation (for use with @ref select_option_join "joins"); ex: \c "q.name"
370  @note
371  - By using the @ref select_option_offset "offset option" the results will be automatically ordered according to the primary key of the table
372 
373  @anchor select_option_desc
374  @par Select Option "desc"
375  <b>Desc Example:</b>
376  @code
377 my *list $rows = $table.selectRows(("where": ("account_type": "CUSTOMER"), "orderby": "created_date", "desc": True));
378  @endcode
379  If the value of this key is @ref Qore::True "True" and results are ordered (either due to the @ref select_option_orderby "orderby option" or due to implicit ordering by the primary key due to the use of the @ref select_option_offset "offset option"), then results will be sorted in descending order.\n\n
380  Otherwise, ordered results are returned in ascending order by default.
381 
382  @anchor select_option_limit
383  @par Select Option "limit"
384  <b>Limit Example:</b>
385  @code
386 my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
387  @endcode
388  This option will limit the number of rows returned.
389  @note
390  - This option is required if the @ref select_option_offset "offset option" is non-zero
391  - If this option is present and an @ref select_option_orderby "orderby option" is also present, then at least a subset of the @ref select_option_orderby "orderby" columns must correspond to a unique key of the table or an exception is raised
392 
393  @anchor select_option_offset
394  @par Select Option "offset"
395  <b>Offset Example:</b>
396  @code
397 my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
398  @endcode
399  This option specifies the row number offset for the rows returned where the first row is at offset zero.
400  @note
401  - If this option is present, then either an @ref select_option_orderby "orderby option" must be present of which at least a subset of the @ref select_option_orderby "orderby" columns must correspond to a unique key of the table, or, if no @ref select_option_orderby "orderby option" is used, then the table must have a primary key which is used for the ordering instead.
402  - Additionally, this option requires the presence of the @ref select_option_limit "limit option", or an exception will be thrown.
403  @see @ref sql_paging
404 
405  @anchor select_option_join
406  @par Select Option "join"
407  <b>Join Example:</b>
408  @code
409 my *list $rows = $table.selectRows(("columns": ("name", "version", "id", cop_as("st.value", "source"), cop_as("st.value", "offset")),
410  "join": join_left($function_instance_tags, "st", NOTHING, ("st.tag": "_source"))
411  + join_left($function_instance_tags, "lt", NOTHING, ("st.tag": "_offset"))));
412  @endcode
413  To join multiple tables in a single query, use the \c "join" option. The \c "join" hash key should be assigned to a join description hash as returned by one of the @ref sql_jop_funcs or combined join description hash created by concatenating such values (see an example of this above).
414  @note the join columns do not need to be specified in the case that a foreign key in one table exists to the primary key of the other table; in this case this information is assumed for the join automatically
415 
416  @see @ref joins for more examples
417 
418  @anchor select_option_groupby
419  @par Select Option "groupby"
420  <b>Groupby Example:</b>
421  @code
422 my *list $rows = $table.selectRows(("columns": (cop_as(cop_max("service_type"), "type"), cop_count()), "groupby": "service_type"));
423  @endcode
424  The \c "groupby" option allows for aggregate SQL column operator functions to be used (ex: @ref cop_max(), cop_min()) in select statements.
425  The \c "groupby" hash key should be assigned to a list of column specifiers or a single column specifier. Column specifies for the \c "groupby"
426  key are strings giving column names, optionally in dot notation.
427 
428  @anchor select_option_having
429  @par Select Option "having"
430  <b>Having Example:</b>
431  @code
432 my *list $rows = $table.selectRows(("columns": (cop_as(cop_max("service_type"), "type"), cop_count()), "groupby": "service_type", "having": ("service_type": (COP_COUNT, op_ge(100)))));
433  @endcode
434  The \c "having" option allows for query results with aggregate SQL column operator functions to be filtered by user-defined criteria.
435  The \c "having" hash key should be assigned to a hash where each key is a column specifier (optionally in dot notation) and the values are lists with two elements; the first element must be a @ref sql_cops "column operator code", and the second element is a column operator description normally provided by using a @ref sql_cop_funcs "column operator function" as in the above example.
436 
437  @anchor select_option_superquery
438  @par Select Option "superquery"
439  <b>Superquery Example:</b>
440  @code
441 my *list $rows = $table.selectRows("columns": ("serviceid", "service_methodid", cop_as(cop_over(cop_max("service_methodid"), "serviceid"), "max_methodid")), "superquery": ("columns": ("serviceid", "service_methodid"), "where": ("max_methodid": op_ceq("service_methodid"))));
442  @endcode
443  The \c "superquery" option allows for the rest of the query arguments to define a subquery where as the hash arguments assigned to the \c "superquery" key define the select made from the subquery. In the example above, the \c "OVER" sql windowing function is used and then rows matching the \c "max_methodid)" result value are selected.\n\n
444  The above example results in an SQL command equivalent to the following:
445  @code
446 my *list $rows = $table.vselectRows("select serviceid,service_methodid from (select serviceid,service_methodid,max(service_methodid) over (partition by serviceid) as max_methodid from schema.service_methods) subquery where max_methodid = service_methodid");
447  @endcode
448  @note that MySQL does not support SQL windowing functions so the above example would fail on MySQL.
449 
450  @anchor select_option_forupdate
451  @par Select Option "forupdate"
452  <b>For Update Example:</b>
453  @code
454 on_success $ds.commit();
455 on_error $ds.rollback();
456 
457 my *list $rows = $table.selectRows("columns": ("serviceid", "service_methodid"), "forupdate": True);
458  @endcode
459  \n The \c "forupdate" option allows for the rows selected to be locked for updating; to release the locks, call commit() or rollback() on the underlying datasource object.
460  The above example results in an SQL commit equivalent to the following:
461  @code
462 my *list $rows = $table.vselectRows("select serviceid,service_methodid from schema.service_methods for update");
463  @endcode
464 
465  @subsection sql_paging Select With Paging
466 
467  There is support for paging query results in the following methods:
468  - @ref SqlUtil::Table::getRowIterator()
469  - @ref SqlUtil::Table::getSelectSql()
470  - @ref SqlUtil::Table::select()
471  - @ref SqlUtil::Table::selectRows()
472 
473  @note the above list also applies to the corresponding @ref SqlUtil::AbstractTable methods
474 
475  Each of these methods takes a @ref select_option_hash "select option hash argument" that allows the \c "limit" and \c "offset" options to be specified to specify the data window for the results.
476 
477  If the \c "offset" options is used, then an \c "orderby" option is required which must match some unique constraint or unique index on the table to guarantee the order of results, unless the table has a primary key, in which case the primary key will be used by default if no \c "orderby" option is supplied.
478 
479  @par Example:
480  Select 100 rows starting at row 200 (the table's primary key will be used for the \c "orderby" option by default): \n
481  @code
482 my *list $rows = $table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
483  @endcode
484  As an illustration of the different SQL that is generated for different database types; for the above query, here is the SQL generated for Oracle:
485  @code
486 $ds.vselectRows("select * from (select /*+ first_rows(100) */ a.*, rownum rnum from (select * from schema.table where type = %v order by type) a where rownum <= %v) where rnum > %v", ("user", 300, 200));
487  @endcode
488  And for PostgreSQL:
489  @code
490 $ds.vselectRows("select * from public.table where type = %v order by type limit %v offset %v", ("user", 100, 200));
491  @endcode
492 
493  @subsection check_matching_rows Check For At Least One Matching Row
494 
495  Use the @ref SqlUtil::Table::findSingle() method to find at least one matching row:
496  @code
497 my *hash $h = $table.findSingle(("account_type": "CUSTOMER"));
498 if ($h)
499  printf("found 1 customer row: %y\n", $l[0]);
500  @endcode
501 
502  Also it's possible to use the \c "limit" option to make an efficient check for at least one matching row as in the following example (which is functionally equivalent to the previous example):
503  @code
504 my *hash $h = $table.selectRow(("where": ("account_type": "CUSTOMER"), "limit": 1));
505 if ($h)
506  printf("found 1 customer row: %y\n", $l[0]);
507  @endcode
508 
509  @section inserting_data Inserting Data into the Database
510 
511  The following methods can be used to insert data into the database:
512  - @ref SqlUtil::Table::insert(): inserts a single row into a table and commits the transaction
513  - @ref SqlUtil::Table::insertNoCommit(): inserts a single row into a table without committing the transaction
514  - @ref SqlUtil::Table::insertFromSelect(): inserts data in a table based on a select statement created from the @ref select_option_hash "select option hash" argument and commits the transaction
515  - @ref SqlUtil::Table::insertFromSelectNoCommit(): inserts data in a table based on a select statement created from the @ref select_option_hash "select option hash" argument and without committing the transaction
516 
517  @see @ref sql_upsert for information about upserting or merging data
518 
519  @subsection inserting_data_explicitly Inserting Data Explicitly
520 
521  @par Example:
522  @code
523 $table.insert(("id": $id, "name": $name, "created": now_us()));
524  @endcode
525 
526  Data can be explicitly inserted into the database with immediate values with @ref SqlUtil::Table::insert() and @ref SqlUtil::Table::insertNoCommit() as in the above example.
527 
528  @subsection inserting_data_from_select Inserting Data From a Select Statement
529 
530  @par Example:
531  @code
532 my int $rows = $table.insertFromSelect(("id", "name", "created"), $source_table, (("columns": ("cid", "fullname", "created"), "where": ("type": "CUSTOMER"))));
533  @endcode
534 
535  Data can be inserted into the database based on the results of a select statement with @ref SqlUtil::Table::insertFromSelect() and @ref SqlUtil::Table::insertFromSelectNoCommit() as in the above example.
536 
537  The example above would generate a %Qore SQL command like the following:
538  @code
539 return $ds.vexec("insert into schema.table (id,name,created) select cid,fullname,created from schema.source_table where type = %v", ("CUSTOMER"));
540  @endcode
541 
542  The return value of these methods is the number of rows inserted. See @ref select_option_hash "select option hash" for more information about how to form the select criteria in these methods.
543 
544  @subsection inserting_data_from_iterator Inserting Data from an Iterator Source
545 
546  To insert data from an iterator source (such as an @ref Qore::SQL::SQLStatement object), call @ref SqlUtil::Table::insertFromIterator() or @ref SqlUtil::Table::insertFromIteratorNoCommit() as in the following example:
547 
548  @par Example:
549  @code
550 # get the rows to be inserted
551 my list $l = get_table_rows();
552 # insert the data and commit after every 5000 rows
553 $table.insertFromIterator($l.iterator(), ("commit_block": 5000));
554  @endcode
555 
556  The iterator given to the @ref SqlUtil::Table::insertFromIterator() or @ref SqlUtil::Table::insertFromIteratorNoCommit() methods can be any iterator whose @ref Qore::AbstractIterator::getValue() "getValue()" method returns a @ref hash_type "hash".
557 
558  @note the @ref SqlUtil::AbstractTable::InsertOptions "insert option" \c "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache
559 
560  @section updating_data Updating Data
561 
562  The following methods can be used to update data:
563  - @ref SqlUtil::Table::update(): updates a single row and commits the transaction
564  - @ref SqlUtil::Table::updateNoCommit(): updates a single row and does not commit the transaction
565 
566  @par Example:
567  @code
568 my int $rows_updated = t.update(("permission_type": uop_append("-migrated", uop_lower())));
569  @endcode
570 
571  The example above generates a %Qore SQL command like the following on Oracle and PostgreSQL for example:
572  @code
573 return $ds.vexec("update schema.table set permission_type = lower(permission_type) || '-migrated');
574  @endcode
575  And the following on MySQL:
576  @code
577 return $ds.vexec("update schema.table set permission_type = concat(lower(permission_type), '-migrated'));
578  @endcode
579 
580  @section deleting_data Deleting Data
581 
582  The following methods can be used to dekete data:
583  - @ref SqlUtil::Table::del(): updates the table based on a @ref where_clauses "where clause" and commits the transaction
584  - @ref SqlUtil::Table::delNoCommit(): updates the table based on a @ref where_clauses "where clause" and does not commit the transaction
585  - @ref SqlUtil::Table::truncate(): truncates the table and commits the transaction releasing the transaction lock on the underlying datasource object
586  - @ref SqlUtil::Table::truncateNoCommit(): truncates the table and does not commit the transaction
587 
588  @par Example:
589  @code
590 my int $dcnt = $table.del(("record_type": "OLD-CUSTOMER"));
591  @endcode
592 
593  The above example would generate a %Qore SQL command like the following:
594  @code
595 return $ds.vexec("delete from schema.table where record_type = %v", ("OLD-CUSTOMER"));
596  @endcode
597 
598  The @ref SqlUtil::Table::del() and @ref SqlUtil::Table::delNoCommit() methods can be used to delete data from the database.
599 
600  See @ref where_clauses for information about specifying the criteria for the rows to be deleted.
601 
602  @section joins Joining Tables
603 
604  Joining tables is made by providing a join specification to the @ref select_option_join "join select option" in
605  a @ref select_option_hash "select option hash" as in the following example:
606  @code
607 my *list $rows = $table.selectRows(("columns": ("table.id", "t2.customer_name"), "join": join_inner($table2, "t2", ("id": "altid"))));
608  @endcode
609  In the above example, \a table is joined with \a table2 on <tt>table.id = table2.altid</tt>.
610 
611  Joins on multiple tables are performed by combining the results of @ref sql_op_funcs "join functions" with the @ref plus_operator "+ operator"
612  as follows:
613  @code
614 my *list $rows = $table.selectRows(("join": join_inner($table2, "t2", ("id": "altid")) + join_inner($table3, "t3")));
615  @endcode
616  In the above example, \a table is joined with \a table2 on <tt>table.id = table2.altid</tt> and with \a table3 on an
617  automatically detected primary key to foreign key relationship between the two tables.
618 
619  Joins are by default made with the primary table; to join with another join table, then give the alias for the table as the first
620  argument to the @ref sql_op_funcs "join function" as in the following example:
621  @code
622 my *list $rows = $table.selectRows(("join": join_inner($table2, "t2", ("id": "altid")) + join_inner("t2", $table3, "t3")));
623  @endcode
624  In the above example, \a table is joined with \a table2 on <tt>table.id = table2.altid</tt> and \a table2 (aliased as \c t2) is joined
625  with \a table3 (aliased as \c t3) on an automatically detected primary key to foreign key relationship between the two tables.
626 
627  @see @ref select_option_join "join select option"
628 
629  @section where_clauses Where Clauses
630 
631  Several methods accept a hash of conditions to build a \c "where" clause to restrict the rows that are operated on or returned; for example:
632  - @ref SqlUtil::Table::del()
633  - @ref SqlUtil::Table::delNoCommit()
634  - @ref SqlUtil::Table::findAll()
635  - @ref SqlUtil::Table::findSingle()
636  - @ref SqlUtil::Table::getRowIterator()
637  - @ref SqlUtil::Table::getSelectSql()
638  - @ref SqlUtil::Table::insertFromSelect()
639  - @ref SqlUtil::Table::insertFromSelectNoCommit()
640  - @ref SqlUtil::Table::select()
641  - @ref SqlUtil::Table::selectRow()
642  - @ref SqlUtil::Table::selectRows()
643  - @ref SqlUtil::Table::update()
644  - @ref SqlUtil::Table::updateNoCommit()
645  - @ref SqlUtil::Table::upsertFromSelect()
646  - @ref SqlUtil::Table::upsertFromSelectNoCommit()
647 
648  @note the above list also applies to the corresponding @ref SqlUtil::AbstractTable methods
649 
650  The where clause or condition hash is made of keys signifying the column names, and either a direct value meaning that the column value has to match exactly, or SQL operators can be given by using the appropriate operator function as the key value. Each member of the where hash translates to an expression that is combined with \c "AND" in the SQL query; to combine expressions with \c "OR", then use a list of @ref select_option_hash "select option hashes", which will combine each @ref select_option_hash "select option hash" with \c "OR" as in @ref where_list "this example".
651 
652  The where condition hash has the following format:
653  - each key gives a column name or a table/alias with column name in dot notation
654  - the values are either direct values, meaning that the equality operator (\c "=") is used, or a @ref sql_op_funcs "SQL operator function" for operators in the where clause
655 
656  @note To reference a column more than once in a where clause, prefix the column specification with a unique number and a colon as in the following example: @code my hash $w = ("0:created": op_ge($mindate), "1:created": op_lt($maxdate)); @endcode
657 
658  See @ref sql_op_funcs for a list of operator functions.
659 
660  @par Where Hash Example:
661  @code
662 my hash $w = (
663  "name": "Smith",
664  "account_type": op_like("%CUSTOMER%"),
665  "id": op_ge(500),
666 );
667  @endcode \n
668  The preceding example results in a where clause equivalent to: \c "name = 'Smith' and type like '%CUSTOMER%' and id >= 500", except
669  that bind by value is used, so, if used in a context like the following:
670  @code
671 my Table $t($ds, "table");
672 my *hash $qh = $t.select(("where": $w));
673  @endcode \n
674  the complete query would look instead as follows:
675  @code
676 $ds.vselect("select * from table where name = %v and account_type like %v and id >= %v", ("Smith", "%CUSTOMER%", 500));
677  @endcode
678 
679  @anchor where_list
680  @par Where List Example:
681  @code
682 my hash $w1 = (
683  "name": "Smith",
684  "account_type": op_like("%CUSTOMER%"),
685  "id": op_ge(500),
686 );
687 my hash $w2 = (
688  "name": "Jones",
689  "account_type": op_like("%VENDOR%"),
690  "id": op_ge(2500),
691 );
692 my Table $t($ds, "table");
693 my *hash $qh = $t.select(("where": ($w1, $w2)));
694  @endcode \n
695  the complete query would look instead as follows:
696  @code
697 $ds.vselect("select * from table where (name = %v and account_type like %v and id >= %v) or (name = %v and account_type like %v and id >= %v)", ("Smith", "%CUSTOMER%", 500, "Jones", "%VENDOR%", 2500));
698  @endcode
699 
700  @par Code Examples:
701  Find a single row in the table where the \c "permission_type" column is a value between \c "US" and \c "UX":\n
702  @code
703 my *hash $row = $table.findSingle(("permission_type": op_between("US", "UX")));
704  @endcode
705  resulting in an internal SQL command that looks as follows (depending on the database):
706  @code
707 my *hash $row = $ds.vselectRow("select * from table where permission_type between %v and %v limit %v", ("US", "UX", 1));
708  @endcode \n
709  Delete all rows in the table where the \c "name" column is like \c "%Smith%":\n
710  @code
711 my int $row_count = $table.del(("name": op_like("%Smith%")));
712  @endcode
713  resulting in an internal SQL command that looks as follows:
714  @code
715 $ds.vexec("delete from table where name like %v", ("%Smith%"));
716  @endcode \n
717  Find all rows where \c "id" is greater than \c 100 and \c "created" is after \c 2013-03-01:\n
718  @code
719 my *list $rows = $table.findAll(("id": op_gt(100), "created": op_gt(2013-03-01)));
720  @endcode
721  resulting in an internal SQL command that looks as follows:
722  @code
723 $ds.vexec("select * from table where id > %v and created > %v", (100, 2013-03-01));
724  @endcode \n
725 
726  @section sql_upsert Upserting or Merging Data
727 
728  This module offers a high-level api for "upserting" or merging data from one table into another table through the following methods:
729  - @ref SqlUtil::Table::upsert()
730  - @ref SqlUtil::Table::upsertNoCommit()
731  - @ref SqlUtil::Table::getUpsertClosure()
732  - @ref SqlUtil::Table::getUpsertClosureWithValidation()
733  - @ref SqlUtil::Table::upsertFromIterator()
734  - @ref SqlUtil::Table::upsertFromIteratorNoCommit()
735  - @ref SqlUtil::Table::upsertFromSelect()
736  - @ref SqlUtil::Table::upsertFromSelectNoCommit()
737 
738  @subsection sql_upsert_single Upsert a Single Row
739 
740  @par Example:
741  @code
742 $table.upsert(("id": $id, "name": $name, "account_type": $account_type));
743  @endcode
744 
745  To upsert or merge a single row in the database, call @ref SqlUtil::Table::upsert() or @ref SqlUtil::Table::upsertNoCommit() with the
746  single row to be upserted or merged as a hash as in the preceding example.
747 
748  @subsection sql_upsert_many Upserting Many Rows Using An Upsert Closure
749 
750  To upsert or merge many rows by using an upsert closure, call @ref SqlUtil::Table::getUpsertClosure() or @ref SqlUtil::Table::getUpsertClosureWithValidation() and provide an example row as an argument to acquire a closure that will be executed on the rest of the rows as in the following example.
751 
752  @par Simple Example:
753  @code
754 # get the rows to be inserted
755 my list $l = get_table_rows();
756 
757 if ($l) {
758  my code $upsert = $table.getUpsertClosure($l[0]);
759 
760  on_success $ds.commit();
761  on_error $ds.rollback();
762 
763  # loop through the reference data rows
764  map $upsert($1), $l;
765 }
766  @endcode
767 
768  @par Complex Example With Callbacks:
769  @code
770 # set the upsert strategy depending on the use case
771 my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
772 
773 # hash summarizing changes
774 my hash $sh;
775 
776 # get the rows to be inserted
777 my list $l = get_table_rows();
778 
779 if ($l) {
780  # get the upsert closure to use based on the first row to be inserted
781  my code $upsert = $table.getUpsertClosure($l[0], $upsert_strategy);
782 
783  on_success $ds.commit();
784  on_error $ds.rollback();
785 
786  # loop through the reference data rows
787  foreach my hash $h in ($l) {
788  my int $code = $upsert($h);
789  if ($code == AbstractTable::UR_Unchanged)
790  continue;
791 
792  my string $change = AbstractTable::UpsertResultMap{$code};
793  ++$sh{$change};
794 
795  if (!$verbose) {
796  printf(".");
797  flush();
798  }
799  else if ($verbose > 1)
800  printf("*** reference data %s: %y: %s\n", $table.getName(), $h, $change);
801  }
802 
803  # show table summary
804  if ($sh)
805  printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
806  else
807  printf("*** reference data %s: OK\n", $table.getName());
808 }
809  @endcode
810 
811  @subsection sql_upsert_from_iterator Upserting Many Rows from an Iterator Source
812 
813  To upsert or merge many rows from an iterator source (such as an @ref Qore::SQL::SQLStatement object), call @ref SqlUtil::Table::upsertFromIterator() or @ref SqlUtil::Table::upsertFromIteratorNoCommit() as in the following example:
814 
815  @par Simple Example:
816  @code
817 # get the rows to be inserted
818 my list $l = get_table_rows();
819 $table.upsertFromIterator($l.iterator());
820  @endcode
821 
822  @par Complex Example With Callbacks:
823  @code
824 # set the upsert strategy depending on the use case
825 my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
826 
827 # get the rows to be inserted
828 my list $l = get_table_rows();
829 
830 my code $callback = sub (string $table_name, hash $row, int $result) {
831  if ($result == AbstractTable::UR_Unchanged)
832  return;
833  my string $change = AbstractTable::UpsertResultMap{$result};
834  if ($verbose)
835  printf("*** reference data %s: %y: %s\n", $table_name, $row, $change);
836 };
837 
838 my hash $sh = $table.upsertFromIterator($l.iterator(), $upsert_strategy, False, ("info_callback": $callback, "commit_block": 5000));
839 if ($sh)
840  printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
841 else
842  printf("*** reference data %s: OK\n", $table.getName());
843  @endcode
844 
845  The iterator given to the @ref SqlUtil::Table::upsertFromIterator() or @ref SqlUtil::Table::upsertFromIteratorNoCommit() methods can be any iterator whose @ref Qore::AbstractIterator::getValue() "getValue()" method returns a @ref hash_type "hash".
846 
847  @note the @ref SqlUtil::AbstractTable::UpsertOptions "upsert option" \c "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache
848 
849  @subsection sql_upsert_from_select Upserting Many Rows from a Select Statement
850 
851  To upsert or merge many rows from a select statement, use @ref SqlUtil::Table::upsertFromSelect() or @ref SqlUtil::Table::upsertFromSelectNoCommit() as in the following example:
852 
853  @par Simple Example:
854  @code
855 $table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")));
856  @endcode
857 
858  @par Complex Example With Callbacks:
859  @code
860 # set the upsert strategy depending on the use case
861 my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
862 
863 my code $callback = sub (string $table_name, hash $row, int $result) {
864  if ($result == AbstractTable::UR_Unchanged)
865  return;
866  my string $change = AbstractTable::UpsertResultMap{$result};
867  if ($verbose)
868  printf("*** reference data %s: %y: %s\n", $table_name, $row, $change);
869 };
870 
871 my hash $sh = $table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")), $upsert_strategy, False, ("info_callback": $callback, "commit_block": 5000));
872 if ($sh)
873  printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
874 else
875  printf("*** reference data %s: OK\n", $table.getName());
876  @endcode
877 
878  The source table does not have to be in the same database or even of the same database type (ie you can upsert to and from any database type supported by SqlUtil).
879 
880  @note the @ref SqlUtil::AbstractTable::UpsertOptions "upsert option" \c "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache
881 
882  @subsection sql_upsert_with_delete Upserting Many Rows and Deleting Unwanted Rows
883 
884  Call any of the batch upsert methods with @ref SqlUtil::AbstractTable::UpsertOptions "upsert option" \c delete_others set to @ref Qore::True "True" to perform a batch upsert / merge operation on a table, and then scan the table and delete any unwanted rows. If there are no rows to be deleted, these calls have very similar performance to the batch upsert method calls without any deletions, however, if there are rows to be deleted, then the entire source table must be iterated to compare each row to valid data to delete the rows that do not belong. Therefore for large tables this can be an expensive operation.
885 
886  The only difference in the following examples and the preceding ones is that @ref SqlUtil::AbstractTable::UpsertOptions "upsert option" \c delete_others is @ref Qore::True "True" in these examples.
887 
888  @par Simple Example:
889  @code
890 # get the rows to be inserted
891 my list $l = get_table_rows();
892 $table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")), AbstractTable::UpsertAuto, ("delete_others": True, "commit_block": 5000));
893  @endcode
894 
895  @par Complex Example With Callbacks:
896  @code
897 # set the upsert strategy depending on the use case
898 my int $upsert_strategy = $verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
899 
900 # get the rows to be inserted
901 my list $l = get_table_rows();
902 
903 my code $callback = sub (string $table_name, hash $row, int $result) {
904  if ($result == AbstractTable::UR_Unchanged)
905  return;
906  my string $change = AbstractTable::UpsertResultMap{$result};
907  if ($verbose)
908  printf("*** reference data %s: %y: %s\n", $table_name, $row, $change);
909 };
910 
911 my hash $sh = $table.upsertFromSelect($table2, ("where": ("account_type": "CUSTOMER")), $upsert_strategy, ("delete_others": True, "info_callback": $callback, "commit_block": 5000));
912 if ($sh)
913  printf("*** reference data %s: %s\n", $table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), $sh.pairIterator())));
914 else
915  printf("*** reference data %s: OK\n", $table.getName());
916  @endcode
917 
918  @note the @ref SqlUtil::AbstractTable::UpsertOptions "upsert option" \c "commit_block" can be used to insert a large amount of data in pieces in order to avoid overwhelming the database server's rollback cache
919 
920  @subsection sql_upsert_strategies Upsert Strategies
921  The approach used is based on one of the following strategies (see @ref upsert_options):
922  - @ref SqlUtil::AbstractTable::UpsertAuto "AbstractTable::UpsertAuto": if the target table is empty, then @ref SqlUtil::AbstractTable::UpsertInsertFirst is used, otherwise @ref SqlUtil::AbstractTable::UpsertUpdateFirst is used
923  - @ref SqlUtil::AbstractTable::UpsertInsertFirst "AbstractTable::UpsertInsertFirst": first an insert will be attempted, if it fails due to a duplicate key, then an update will be made; this strategy should be used if more inserts will be made than updates
924  - @ref SqlUtil::AbstractTable::UpsertUpdateFirst "AbstractTable::UpsertUpdateFirst": first an update will be attempted, if it fails due to missing data, then an insert is performed; this strategy should be used if more updates will be made then inserts
925  - @ref SqlUtil::AbstractTable::UpsertSelectFirst "AbstractTable::UpsertSelectFirst": first a select is made on the unique key, if the data to be updated is equal, nothing is done and @ref upsert_results "upsert result" @ref SqlUtil::AbstractTable::UR_Unchanged is returned
926  - @ref SqlUtil::AbstractTable::UpsertInsertOnly "AbstractTable::UpsertInsertOnly": insert if the row doesn't exist, otherwise do nothing and @ref upsert_results "upsert result" @ref SqlUtil::AbstractTable::UR_Unchanged is returned
927 
928  @note @ref SqlUtil::AbstractTable::UpsertSelectFirst "AbstractTable::UpsertSelectFirst" is the only upsert strategy that can return @ref SqlUtil::AbstractTable::UR_Updated; the @ref SqlUtil::AbstractTable::UpsertSelectFirst "AbstractTable::UpsertSelectFirst" strategy should be used when verbose reporting is required, particularly if it's necessary to report the actual number of changed rows.
929 */
1609 namespace SqlUtil {
1611 
1612  /* @defgroup DBFeaturesConstants DB Features Constants
1613  These constants can be used as a lookup values in AbstractDatabase::features() method.
1614  */
1616 
1619  const DB_FUNCTIONS = "functions";
1621  const DB_MVIEWS = "materialized views";
1623  const DB_PACKAGES = "packages";
1625  const DB_PROCEDURES = "procedures";
1627  const DB_SEQUENCES = "sequences";
1629  const DB_TABLES = "tables";
1631  const DB_TYPES = "named types";
1633  const DB_VIEWS = "views";
1635 
1636  /* @defgroup SqlTypeConstants SQL Type Constants
1637  These constants can be used for the \c "qore_type" values when creating columns to specify additional SQL column types
1638  */
1640  const VARCHAR = "string";
1642 
1644  const NUMERIC = "number";
1645 
1647  const CHAR = "char";
1648 
1650  const BLOB = "blob";
1651 
1653  const CLOB = "clob";
1655 
1660  const SZ_NONE = 0;
1662 
1664  const SZ_MAND = 1;
1665 
1667  const SZ_OPT = 2;
1668 
1670  const SZ_NUM = 3;
1672 
1677 
1680  const COP_AS = "as";
1681 
1683 
1685  const COP_PREPEND = "prepend";
1686 
1688 
1690  const COP_APPEND = "append";
1691 
1693 
1695  const COP_VALUE = "value";
1696 
1698 
1700  const COP_UPPER = "upper";
1701 
1703 
1705  const COP_LOWER = "lower";
1706 
1708 
1710  const COP_DISTINCT = "distinct";
1711 
1713 
1715  const COP_MIN = "min";
1716 
1718 
1720  const COP_MAX = "max";
1721 
1723 
1725  const COP_AVG = "avg";
1726 
1728 
1730  const COP_COUNT = "count";
1731 
1733 
1735  const COP_OVER = "over";
1736 
1738 
1740  const COP_MINUS = "minus";
1741 
1743 
1745  const COP_PLUS = "plus";
1746 
1748 
1750  const COP_DIVIDE = "divide";
1751 
1753 
1755  const COP_MULTIPLY = "multiply";
1756 
1758 
1760  const COP_YEAR = "year";
1761 
1763 
1765  const COP_YEAR_MONTH = "year_month";
1766 
1768 
1770  const COP_YEAR_DAY = "year_day";
1771 
1773 
1775  const COP_YEAR_HOUR = "year_hour";
1776 
1778 
1780  const COP_SEQ = "seq";
1781 
1783  const DefaultCopMap = (
1784  COP_AS: (
1785  "arg": Type::String,
1786  "code": string (string cve, string arg) {
1787  return sprintf("%s as %s", cve, arg);
1788  },
1789  ),
1790  COP_PREPEND: (
1791  "arg": Type::String,
1792  "sqlvalue": True,
1793  "code": string (string cve, string arg) {
1794  return sprintf("%s||%s", arg, cve);
1795  },
1796  ),
1797  COP_APPEND: (
1798  "arg": Type::String,
1799  "sqlvalue": True,
1800  "code": string (string cve, string arg) {
1801  return sprintf("%s||%s", cve, arg);
1802  },
1803  ),
1804  COP_VALUE: (
1805  "sqlvalue": True,
1806  "nocolumn": True,
1807  "code": string (*string cve, any arg) {
1808  return arg;
1809  },
1810  ),
1811  COP_UPPER: (
1812  "code": string (string cve, any arg) {
1813  return sprintf("upper(%s)", cve);
1814  },
1815  ),
1816  COP_LOWER: (
1817  "code": string (string cve, any arg) {
1818  return sprintf("lower(%s)", cve);
1819  },
1820  ),
1821  COP_DISTINCT: (
1822  "code": string (string cve, any arg) {
1823  return sprintf("distinct %s", cve);
1824  },
1825  ),
1826  COP_MIN: (
1827  "code": string (string cve, any arg) {
1828  return sprintf("min(%s)", cve);
1829  },
1830  "group": True,
1831  ),
1832  COP_MAX: (
1833  "code": string (string cve, any arg) {
1834  return sprintf("max(%s)", cve);
1835  },
1836  "group": True,
1837  ),
1838  COP_AVG: (
1839  "code": string (string cve, any arg) {
1840  return sprintf("avg(%s)", cve);
1841  },
1842  "group": True,
1843  ),
1844  COP_COUNT: (
1845  "nocolumn": True,
1846  "code": string (*string cve, any arg) {
1847  return sprintf("count(%s)", cve ? cve : "1");
1848  },
1849  ),
1850  COP_MINUS: (
1851  "argcolumn": True,
1852  "code": string (string arg1, string arg2) {
1853  return sprintf("%s - %s", arg1, arg2);
1854  },
1855  ),
1856  COP_PLUS: (
1857  "argcolumn": True,
1858  "code": string (string arg1, string arg2) {
1859  return sprintf("%s + %s", arg1, arg2);
1860  },
1861  ),
1862  COP_DIVIDE: (
1863  "argcolumn": True,
1864  "code": string (string arg1, string arg2) {
1865  return sprintf("%s / %s", arg1, arg2);
1866  },
1867  ),
1868  COP_MULTIPLY: (
1869  "argcolumn": True,
1870  "code": string (string arg1, string arg2) {
1871  return sprintf("%s * %s", arg1, arg2);
1872  },
1873  ),
1874  );
1876 
1893 
1902  hash make_cop(string cop, any column, any arg);
1903 
1904 
1906 
1916  hash cop_as(any column, string arg);
1917 
1918 
1920 
1930  hash cop_prepend(any column, string arg);
1931 
1932 
1934 
1944  hash cop_append(any column, string arg);
1945 
1946 
1948 
1957  hash cop_value(any arg);
1958 
1959 
1961 
1970  hash cop_upper(any column);
1971 
1972 
1974 
1983  hash cop_lower(any column);
1984 
1985 
1987 
1996  hash cop_distinct(any column);
1997 
1998 
2000 
2009  hash cop_min(any column);
2010 
2011 
2013 
2022  hash cop_max(any column);
2023 
2024 
2026 
2035  hash cop_avg(any column);
2036 
2037 
2039 
2046  hash cop_count(string column = "");
2047 
2048 
2050 
2057  hash cop_over(any column, string arg);
2058 
2059 
2061 
2072  hash cop_minus(any column1, any column2);
2073 
2074 
2076 
2087  hash cop_plus(any column1, any column2);
2088 
2089 
2091 
2102  hash cop_divide(any column1, any column2);
2103 
2104 
2106 
2117  hash cop_multiply(any column1, any column2);
2118 
2119 
2121 
2130  hash cop_year(any column);
2131 
2132 
2134 
2143  hash cop_year_month(any column);
2144 
2145 
2147 
2156  hash cop_year_day(any column);
2157 
2158 
2160 
2169  hash cop_year_hour(any column);
2170 
2171 
2173 
2182  hash cop_seq(string seq);
2183 
2185 
2189  const DefaultUopMap = (
2191  COP_PREPEND: True,
2192  COP_APPEND: True,
2193  COP_UPPER: True,
2194  COP_LOWER: True,
2195  COP_SEQ: True,
2196  );
2198 
2214 
2223  hash make_uop(string uop, any arg, *hash nest);
2224 
2225 
2227 
2237  hash uop_prepend(string arg, *hash nest);
2238 
2239 
2241 
2251  hash uop_append(string arg, *hash nest);
2252 
2253 
2255 
2264  hash uop_upper(*hash nest);
2265 
2266 
2268 
2277  hash uop_lower(*hash nest);
2278 
2279 
2281 
2290  hash uop_seq(string seq);
2291 
2293 
2300 
2303  const JOP_INNER = "inner";
2304 
2306 
2308  const JOP_LEFT = "left";
2309 
2311 
2313  const JOP_RIGHT = "right";
2314 
2316  const JopMap = (
2317  JOP_INNER: "inner",
2318  JOP_LEFT: "left outer",
2319  JOP_RIGHT: "right outer",
2320  );
2322 
2332 
2336  hash make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash opt);
2337 
2338 
2340 
2359  hash join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
2360 
2361 
2363 
2382  hash join_inner(Table table, *string alias, *hash jcols, *hash cond, *hash opt);
2383 
2384 
2386 
2406  hash join_inner(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
2407 
2408 
2410 
2430  hash join_inner(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt);
2431 
2432 
2434 
2453  hash join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
2454 
2455 
2457 
2476  hash join_left(Table table, *string alias, *hash jcols, *hash cond, *hash opt);
2477 
2478 
2480 
2500  hash join_left(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
2501 
2502 
2504 
2524  hash join_left(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt);
2525 
2526 
2528 
2547  hash join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
2548 
2549 
2551 
2570  hash join_right(Table table, *string alias, *hash jcols, *hash cond, *hash opt);
2571 
2572 
2574 
2594  hash join_right(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
2595 
2596 
2598 
2618  hash join_right(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt);
2619 
2621 
2626 
2629  const OP_LIKE = "like";
2630 
2632 
2634  const OP_LT = "<";
2635 
2637 
2639  const OP_LE = "<=";
2640 
2642 
2644  const OP_GT = ">";
2645 
2647 
2649  const OP_GE = ">=";
2650 
2652 
2654  const OP_NE = "!=";
2655 
2657 
2659  const OP_EQ = "=";
2660 
2662 
2664  const OP_CLT = "C<";
2665 
2667 
2669  const OP_CLE = "C<=";
2670 
2672 
2674  const OP_CGT = "C>";
2675 
2677 
2679  const OP_CGE = "C>=";
2680 
2682 
2684  const OP_CNE = "C!=";
2685 
2687 
2689  const OP_CEQ = "C=";
2690 
2692 
2694  const OP_BETWEEN = "between";
2695 
2697 
2699  const OP_IN = "in";
2700 
2702 
2704  const OP_NOT = "not";
2705 
2707  const DefaultOpMap = (
2708  OP_LIKE: (
2709  "code": string (object t, string cn, any arg, reference args) {
2710  args += arg;
2711  return sprintf("%s like %v", cn);
2712  },
2713  ),
2714  OP_LT: (
2715  "code": string (object t, string cn, any arg, reference args) {
2716  args += arg;
2717  return sprintf("%s < %v", cn);
2718  },
2719  ),
2720  OP_LE: (
2721  "code": string (object t, string cn, any arg, reference args) {
2722  args += arg;
2723  return sprintf("%s <= %v", cn);
2724  },
2725  ),
2726  OP_GT: (
2727  "code": string (object t, string cn, any arg, reference args) {
2728  args += arg;
2729  return sprintf("%s > %v", cn);
2730  },
2731  ),
2732  OP_GE: (
2733  "code": string (object t, string cn, any arg, reference args) {
2734  args += arg;
2735  return sprintf("%s >= %v", cn);
2736  },
2737  ),
2738  OP_NE: (
2739  "code": string (object t, string cn, any arg, reference args) {
2740  if (arg === NULL || !exists arg)
2741  return sprintf("%s is not null", cn);
2742  args += arg;
2743  return sprintf("%s != %v", cn);
2744  },
2745  ),
2746  OP_EQ: (
2747  "code": string (object t, string cn, any arg, reference args) {
2748  if (arg === NULL || !exists arg)
2749  return sprintf("%s is null", cn);
2750  args += arg;
2751  return sprintf("%s = %v", cn);
2752  },
2753  ),
2754  OP_BETWEEN: (
2755  "code": string (object t, string cn, any arg, reference args) {
2756  args += arg[0];
2757  args += arg[1];
2758  return sprintf("%s between %v and %v", cn);
2759  },
2760  ),
2761  OP_IN: (
2762  "code": string (object t, string cn, any arg, reference args) {
2763  *string ins = (foldl $1 + "," + $2, (map t.getSqlValue($1), arg));
2764  return ins ? sprintf("%s in (%s)", cn, ins) : "1 != 1";
2765  },
2766  ),
2767  OP_NOT: (
2768  "recursive": True,
2769  "code": string (object t, string cn, any arg, reference args) {
2770  return sprintf("not (%s)", cn);
2771  },
2772  ),
2773  OP_CLT: (
2774  "argcolumn": True,
2775  "code": string (object t, string cn, any arg, reference args) {
2776  return sprintf("%s < %s", cn, arg);
2777  },
2778  ),
2779  OP_CLE: (
2780  "argcolumn": True,
2781  "code": string (object t, string cn, any arg, reference args) {
2782  return sprintf("%s <= %s", cn, arg);
2783  },
2784  ),
2785  OP_CGT: (
2786  "argcolumn": True,
2787  "code": string (object t, string cn, any arg, reference args) {
2788  return sprintf("%s > %s", cn, arg);
2789  },
2790  ),
2791  OP_CGE: (
2792  "argcolumn": True,
2793  "code": string (object t, string cn, any arg, reference args) {
2794  return sprintf("%s >= %s", cn, arg);
2795  },
2796  ),
2797  OP_CNE: (
2798  "argcolumn": True,
2799  "code": string (object t, string cn, any arg, reference args) {
2800  return sprintf("%s != %s", cn, arg);
2801  },
2802  ),
2803  OP_CEQ: (
2804  "argcolumn": True,
2805  "code": string (object t, string cn, string arg, reference args) {
2806  return sprintf("%s = %s", cn, arg);
2807  },
2808  ),
2809  );
2811 
2832  hash make_op(string op, any arg);
2834 
2835 
2837 
2846  hash op_like(string str);
2847 
2848 
2850 
2861  hash op_lt(any arg);
2862 
2863 
2865 
2876  hash op_le(any arg);
2877 
2878 
2880 
2891  hash op_gt(any arg);
2892 
2893 
2895 
2906  hash op_ge(any arg);
2907 
2908 
2910 
2921  hash op_ne(any arg);
2922 
2923 
2925 
2936  hash op_eq(any arg);
2937 
2938 
2940 
2952  hash op_between(any l, any r);
2953 
2954 
2956 
2963  hash op_in();
2964 
2965 
2967 
2976  hash op_in(list args);
2977 
2978 
2980 
2987  hash op_not(hash arg);
2988 
2989 
2991 
3002  hash op_clt(string arg);
3003 
3004 
3006 
3017  hash op_cle(string arg);
3018 
3019 
3021 
3032  hash op_cgt(string arg);
3033 
3034 
3036 
3047  hash op_cge(string arg);
3048 
3049 
3051 
3062  hash op_cne(string arg);
3063 
3064 
3066 
3077  hash op_ceq(string arg);
3078 
3080 
3087 
3090  const IOP_SEQ = "seq";
3091 
3093  const DefaultIopMap = hash();
3095 
3101 
3109  hash make_iop(string iop, any arg);
3110 
3111 
3113 
3122  hash iop_seq(string arg);
3123 
3125 
3128 
3129 public:
3130 private:
3131 
3132 public:
3133 
3134  private :
3135  *hash h;
3136 
3137 public:
3138 
3140  constructor(*hash nh);
3141 
3142 
3145 
3146 
3148 
3163  any memberGate(string k);
3164 
3165 
3167 
3173  clear();
3174 
3175 
3177  abstract any take(string k);
3178 
3180  renameKey(string old_name, string new_name);
3181 
3182 
3184  *hash getHash();
3185 
3186 
3188 
3197  bool matchKeys(hash h1);
3198 
3199 
3201 
3210  bool matchKeys(list l);
3211 
3212 
3214 
3224 
3225 
3227 
3236  bool partialMatchKeys(hash h1);
3237 
3238 
3240 
3249  bool partialMatchKeys(list l);
3250 
3251 
3253 
3263 
3264 
3266 
3275  bool val();
3276 
3277 
3279 
3286  list keys();
3287 
3288 
3290 
3297  list values();
3298 
3299 
3301 
3309 
3310 
3312 
3320 
3321 
3323 
3331 
3332 
3334  bool empty();
3335 
3336 
3338 
3345  int size();
3346 
3347 
3349 
3358  bool hasKey(string k);
3359 
3360 
3362 
3371  bool hasKeyValue(string k);
3372 
3373 
3375 
3384  *string firstKey();
3385 
3386 
3388 
3397  *string lastKey();
3398 
3399 
3401  abstract string getElementName();
3402  };
3403 
3406 
3407 public:
3408 private:
3409 
3410 public:
3411 
3412  private :
3413  softlist l;
3414 
3415 public:
3416 
3418  constructor(softlist nl);
3419 
3420 
3422 
3433  abstract any get(softint i);
3434 
3436  add(any val);
3437 
3438 
3440  any take(int i);
3441 
3442 
3444  list getList();
3445 
3446 
3448 
3457  bool val();
3458 
3459 
3461 
3469 
3470 
3472  bool empty();
3473 
3474 
3476 
3483  int size();
3484 
3485 
3487  abstract string getElementName();
3488 
3489  private checkIndex(int i);
3490 
3491  };
3492 
3495 
3496 public:
3498  constructor();
3499 
3500 
3502  constructor(AbstractDatasource ds, hash tables, *hash opt);
3503 
3504 
3506  constructor(AbstractDatasource ds);
3507 
3508 
3510  add(string k, Table val);
3511 
3512 
3514  add(string k, AbstractTable val);
3515 
3516 
3518  add(Table val);
3519 
3520 
3522  add(AbstractTable val);
3523 
3524 
3526  AbstractTable take(string k);
3527 
3528 
3530  populate(AbstractDatasource ds, hash tables, *hash opt);
3531 
3532 
3534  populate(AbstractDatasource ds);
3535 
3536 
3538 
3552  *list getDropAllForeignConstraintsOnTableSql(string name, *hash opt);
3553 
3554 
3556 
3571  AbstractTable memberGate(string k);
3572 
3573 
3575  string getElementName();
3576 
3577 
3579  *AbstractTable getIfExists(AbstractDatasource ds, string name);
3580 
3581 
3583  AbstractTable get(AbstractDatasource ds, string name);
3584 
3585 
3587 
3602  *string getRenameTableIfExistsSql(string old_name, string new_name, *hash opts);
3603 
3604 
3606 
3617  bool tableRenamed(string old_name, string new_name, string old_sql_name);
3618 
3619 
3620  private tableRenamedIntern(string old_name, string new_name, string oldsn);
3621 
3622 
3624 
3639  *string getDropConstraintIfExistsSql(string tname, string cname, *hash opts);
3640 
3641 
3642  list getCreateList();
3643 
3644 
3645  Qore::AbstractIterator createIterator();
3646 
3647 
3649 
3657  list getDropList();
3658 
3659 
3661 
3669 
3670 
3671  private getDependencies(reference tdh, reference sdh, *reference th);
3672 
3673  };
3674 
3677 
3678 public:
3679  constructor(*hash c);
3680 
3681 
3683  add(string k, AbstractColumn val);
3684 
3685 
3687  AbstractColumn take(string k);
3688 
3689 
3691 
3706  AbstractColumn memberGate(string k);
3707 
3708 
3710  Columns subset(softlist l);
3711 
3712 
3714  string getElementName();
3715 
3716 
3718  bool equal(Columns cols);
3719 
3720  };
3721 
3724 
3725 public:
3726  public :
3728  string name;
3729 
3731  string native_type;
3732 
3734  *string qore_type;
3735 
3737  int size;
3738 
3740  bool nullable;
3741 
3743  *string def_val;
3744 
3746  *string comment;
3747 
3748 public:
3749 
3750  constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string c);
3751 
3752 
3754  string getNativeTypeString();
3755 
3756 
3758  string getCreateSql(AbstractTable t);
3759 
3760 
3762 
3771  abstract list getAddColumnSql(AbstractTable t);
3772 
3774  string getDropSql(string table_name);
3775 
3776 
3778 
3791 
3792 
3794 
3804  abstract string getRenameSql(AbstractTable t, string new_name);
3805 
3807  bool equal(AbstractColumn c);
3808 
3809 
3811  private abstract bool equalImpl(AbstractColumn c);
3812 
3814 
3826  private abstract list getModifySqlImpl(AbstractTable t, AbstractColumn c, *hash opt);
3827  };
3828 
3831 
3832 public:
3833  public :
3835  int scale;
3836 
3837 public:
3838 
3839  constructor(softint n_scale = 0);
3840 
3841 
3843  string getNativeTypeString(string native_type, int precision);
3844 
3845  };
3846 
3849 
3850 public:
3851  constructor(*hash c);
3852 
3853 
3855  add(string k, AbstractIndex val);
3856 
3857 
3860 
3861 
3863  AbstractIndex take(string k);
3864 
3865 
3867 
3882  AbstractIndex memberGate(string k);
3883 
3884 
3885  string getElementName();
3886 
3887  };
3888 
3891 
3892 public:
3893  public :
3895  string name;
3896 
3898  bool unique;
3899 
3902 
3903 public:
3904 
3905  private :
3908 
3909 public:
3910 
3912  constructor(string n, bool u, hash c);
3913 
3914 
3916  string getName();
3917 
3918 
3920  bool hasColumn(string cname);
3921 
3922 
3924  abstract string getCreateSql(string table_name, *hash opt);
3925 
3927  string getDropSql(string table_name);
3928 
3929 
3931  bool equal(AbstractIndex ix);
3932 
3933 
3935  bool equalExceptName(AbstractIndex ix);
3936 
3937 
3939  abstract bool equalImpl(AbstractIndex ix);
3940 
3942  abstract string getRenameSql(string table_name, string new_name);
3943 
3946 
3947 
3950 
3951 
3954 
3955 
3957  list getRecreateSql(AbstractDatasource ds, string table_name, *hash opt);
3958 
3959  };
3960 
3963 
3964 public:
3965  constructor(*hash c);
3966 
3967 
3969  add(string k, AbstractConstraint val);
3970 
3971 
3973  AbstractConstraint take(string k);
3974 
3975 
3978 
3979 
3981 
3996  AbstractConstraint memberGate(string k);
3997 
3998 
3999  string getElementName();
4000 
4001  };
4002 
4005 
4006 public:
4007 private:
4008 
4009 public:
4010 
4011  private :
4013  string name;
4014 
4015 public:
4016 
4018  constructor(string n);
4019 
4020 
4022  string getName();
4023 
4024 
4026  rename(string n);
4027 
4028 
4030  abstract string getCreateSql(string table_name, *hash opt);
4031 
4033  string getDropSql(string table_name);
4034 
4035 
4037  abstract list getRenameSql(string table_name, string new_name);
4038 
4040  string getDisableSql(string table_name);
4041 
4042 
4044  string getEnableSql(string table_name, *hash opt);
4045 
4046 
4048  bool equal(AbstractConstraint c);
4049 
4050 
4052  private abstract bool equalImpl(AbstractConstraint c);
4053 
4055  abstract bool setIndexBase(string ix);
4056 
4058  abstract clearIndex();
4059 
4061  bool hasColumn(string cname);
4062 
4063  };
4064 
4067 
4068 public:
4069  public :
4071  string src;
4072 
4073 public:
4074 
4076  constructor(string n, string n_src);
4077 
4078 
4080  private bool equalImpl(AbstractConstraint c);
4081 
4082 
4084  bool setIndexBase(string ix);
4085 
4086 
4088  clearIndex();
4089 
4090  };
4091 
4094 
4095 public:
4096  private :
4099 
4101  *string index;
4102 
4103 public:
4104 
4106  constructor(string n, *hash c, *string n_index);
4107 
4108 
4110  abstract string getCreateSql(string table_name, *hash opts);
4111 
4113  private bool equalImpl(AbstractConstraint c);
4114 
4115 
4117 
4122 
4123 
4125  hash getDisableReenableSql(AbstractDatasource ds, string table_name, *hash opts);
4126 
4127 
4129  findMatchingIndex(*Indexes indexes);
4130 
4131 
4133 
4144 
4145 
4147  removeSourceConstraint(string tname, list cols);
4148 
4149 
4151  renameSourceConstraintTable(string old_name, string new_name);
4152 
4153 
4155  bool hasColumn(string cname);
4156 
4157 
4159  *string getIndex();
4160 
4161  };
4162 
4165 
4166 public:
4167  constructor();
4168 
4169 
4170  constructor(string n, *hash c);
4171 
4172  };
4173 
4176 
4177 public:
4178  constructor(*hash c);
4179 
4180 
4182  add(string k, AbstractForeignConstraint val);
4183 
4184 
4186  AbstractForeignConstraint take(string k);
4187 
4188 
4191 
4192 
4194 
4210 
4211 
4213  *hash findConstraintOn(string table, softlist cols);
4214 
4215 
4217  string getElementName();
4218 
4219  };
4220 
4223 
4224 public:
4225  public :
4227  string table;
4228 
4231 
4232 public:
4233 
4235  constructor(string t, Columns c);
4236 
4237 
4239  bool equal(ForeignConstraintTarget targ);
4240 
4241  };
4242 
4245 
4246 public:
4247  public :
4250 
4253 
4254 public:
4255 
4256  constructor(string n, Columns c, ForeignConstraintTarget t);
4257 
4258 
4260  private bool equalImpl(AbstractConstraint con);
4261 
4262 
4264  bool hasColumn(string cname);
4265 
4266 
4268  bool setIndexBase(string ix);
4269 
4270 
4272  clearIndex();
4273 
4274  };
4275 
4278 
4279 public:
4280  public :
4282  string name;
4283 
4286 
4289 
4292 
4293 public:
4294 
4296  constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_max);
4297 
4298 
4300  abstract string getCreateSql(*hash opt);
4301 
4303  string getDropSql();
4304 
4305 
4307  abstract string getRenameSql(string new_name);
4308  };
4309 
4312 
4313 public:
4314  public :
4315  // ! potential object schema
4316  *string schema;
4317 
4319  string name;
4320 
4322  string src;
4323 
4326 
4327 public:
4328 
4330  constructor(string n_name, string n_src);
4331 
4332 
4334  abstract string getCreateSql(*hash opt);
4335 
4337  string getDropSql();
4338 
4339 
4341  abstract softlist getRenameSql(string new_name);
4342 
4343  };
4344 
4347 
4348 public:
4349  public :
4351  string name;
4352 
4354  string type;
4355 
4357  string src;
4358 
4359 public:
4360 
4362 
4366  constructor(string n, string n_type, string n_src);
4367 
4368 
4370  string getType();
4371 
4372 
4373  // FIXME: not appropriate for pgsql triggers for example
4375  string getDropSql();
4376 
4377 
4379  bool equal(AbstractFunctionBase t);
4380 
4381 
4383  private abstract bool equalImpl(AbstractFunctionBase t);
4384  };
4385 
4388 
4389 public:
4391 
4395  constructor(string n, string n_type, string n_src);
4396 
4397 
4399  abstract list getCreateSql(*hash opt);
4400 
4402  abstract list getRenameSql(string new_name);
4403 
4405  setName(string new_name);
4406 
4407  };
4408 
4411 
4412 public:
4413  constructor(*hash c);
4414 
4415 
4417  add(string k, AbstractFunction val);
4418 
4419 
4421  AbstractFunction take(string k);
4422 
4423 
4425 
4440  AbstractFunction memberGate(string k);
4441 
4442 
4443  string getElementName();
4444 
4445  };
4446 
4449 
4450 public:
4452  constructor(string n, string n_src);
4453 
4454 
4456  abstract list getCreateSql(string table_name, *hash opt);
4457 
4459  abstract list getRenameSql(string table_name, string new_name);
4460 
4462  abstract list getDropSql(string table_name);
4463  };
4464 
4467 
4468 public:
4469  constructor(*hash c);
4470 
4471 
4473  add(string k, AbstractTrigger val);
4474 
4475 
4477  AbstractTrigger take(string k);
4478 
4479 
4481 
4496  AbstractTrigger memberGate(string k);
4497 
4498 
4499  string getElementName();
4500 
4501  };
4502 
4504 
4514  class Database {
4515 
4516 public:
4517  private :
4520 
4521 public:
4522 
4524 
4535  constructor(AbstractDatasource ds, *hash opts);
4536 
4537 
4539 
4549  constructor(string ds, *hash opts);
4550 
4551 
4553 
4571  constructor(hash ds, *hash opts);
4572 
4573 
4575 
4586  any tryExec(string sql);
4587 
4588 
4590 
4600  any tryExecArgs(string sql, *softlist args);
4601 
4602 
4604 
4615  any tryExecRaw(string sql);
4616 
4617 
4619 
4633  list getAlignSql(hash schema_hash, *hash opt, *Tables table_cache);
4634 
4635 
4637 
4650  list getDropSchemaSql(hash schema_hash, *hash opt);
4651 
4652 
4655 
4656 
4658  any methodGate(string meth);
4659 
4660 
4662 
4676  AbstractSequence makeSequence(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
4677 
4678 
4680 
4693  AbstractTable makeTable(string name, hash desc, *hash opts);
4694 
4695 
4697 
4708  AbstractFunction makeFunction(string name, string src, *hash opt);
4709 
4710 
4712 
4723  AbstractFunction makeProcedure(string name, string src, *hash opt);
4724 
4725 
4727 
4739  bool dropFunctionIfExists(string name, *hash opt);
4740 
4741 
4743 
4755  bool dropProcedureIfExists(string name, *hash opt);
4756 
4757 
4759 
4771  bool dropSequenceIfExists(string name, *hash opt);
4772 
4773 
4775 
4787  bool dropTableIfExists(string name, *hash opt);
4788 
4789 
4791 
4804 
4805 
4807 
4820 
4821 
4823 
4832  *AbstractTable getTable(string name);
4833 
4834 
4836 
4845  *AbstractSequence getSequence(string name);
4846 
4847 
4849 
4860  *AbstractFunction getFunction(string name);
4861 
4862 
4864 
4875  *AbstractFunction getProcedure(string name);
4876 
4877 
4879 
4888  *AbstractView getView(string name);
4889 
4890 
4892 
4901  int getNextSequenceValue(string name);
4902 
4903 
4905 
4914  string getSqlFromList(list l);
4915 
4916 
4918  bool supportsSequences();
4919 
4920 
4922  list listTables();
4923 
4924 
4927 
4928 
4930  list listFunctions();
4931 
4932 
4935 
4936 
4938  list listProcedures();
4939 
4940 
4943 
4944 
4946  list listSequences();
4947 
4948 
4951 
4952 
4954  list listViews();
4955 
4956 
4959 
4960  };
4961 
4964 
4965 public:
4966  private :
4968  AbstractDatasource ds;
4970  string dsdesc;
4972  Mutex l();
4975 
4976 public:
4977 
4979 
4984  private constructor(AbstractDatasource nds, *hash nopts);
4985 
4986 
4987  static string makeDatasourceDesc(AbstractDatasource ds);
4988 
4989  private validateOptionsIntern(string err, hash ropt, reference opt, string tag);
4990 
4991 
4992 
4993 private:
4994  static validateOptionIntern(string err, string type, reference opt, string k, string tag);
4995 public:
4996 
4997 
4999  private validateHashKeysForWhitespaces(any node);
5000 
5001 
5004 
5005 
5007  string getDriverName();
5008 
5009 
5011  string getDatasourceDesc();
5012 
5013  };
5014 
5017 
5018 public:
5019  public :
5021 
5025  "native_case": Type::Boolean,
5026  );
5027 
5029 
5032  const CacheOptions = (
5033  "table_cache": "Tables",
5034  );
5035 
5037 
5043  "info_callback": "code",
5044  "sql_callback": "code",
5045  "sql_callback_executed": Type::Boolean,
5046  );
5047 
5056  const AC_Unchanged = 0;
5058 
5060  const AC_Create = 1;
5061 
5063  const AC_Drop = 2;
5064 
5066  const AC_Rename = 3;
5067 
5069  const AC_Modify = 4;
5070 
5072  const AC_Truncate = 5;
5073 
5075  const AC_Add = 6;
5076 
5078  const AC_Recreate = 7;
5079 
5081  const AC_Insert = 8;
5082 
5084  const AC_Update = 9;
5085 
5087  const AC_Delete = 10;
5088 
5090  const AC_NotFound = 11;
5092 
5094  const ActionMap = (
5095  AC_Unchanged: "unchanged",
5096  AC_Create: "create",
5097  AC_Drop: "drop",
5098  AC_Rename: "rename",
5099  AC_Modify: "modify",
5100  AC_Truncate: "truncate",
5101  AC_Add: "add",
5102  AC_Recreate: "recreate",
5103  AC_Insert: "insert",
5104  AC_Update: "update",
5105  AC_Delete: "delete",
5106  AC_NotFound: "not found",
5107  );
5108 
5110  const ActionDescMap = (
5111  "unchanged": AC_Unchanged,
5112  "create": AC_Create,
5113  "drop": AC_Drop,
5114  "rename": AC_Rename,
5115  "modify": AC_Modify,
5116  "truncate": AC_Truncate,
5117  "add": AC_Add,
5118  "recreate": AC_Recreate,
5119  "insert": AC_Insert,
5120  "update": AC_Update,
5121  "delete": AC_Delete,
5122  "not found": AC_NotFound,
5123  );
5124 
5127  AC_Unchanged: ".",
5128  AC_Create: "C",
5129  AC_Drop: "D",
5130  AC_Rename: "N",
5131  AC_Modify: "M",
5132  AC_Truncate: "T",
5133  AC_Add: "A",
5134  AC_Recreate: "R",
5135  AC_Insert: "I",
5136  AC_Update: "U",
5137  AC_Delete: "X",
5138  AC_NotFound: ".",
5139  );
5140 
5142 
5149  "replace": Type::Boolean,
5150  "table_cache": "Tables",
5151  "data_tablespace": Type::String,
5152  "index_tablespace": Type::String,
5153  );
5154 
5156 
5159 
5161 
5164 
5166 
5179  "tables": Type::Hash,
5180  "table_map": Type::Hash,
5181 
5182  "sequences": Type::Hash,
5183  "sequence_map": Type::Hash,
5184 
5185  "functions": Type::Hash,
5186  "function_map": Type::Hash,
5187 
5188  "procedures": Type::Hash,
5189  "procedure_map": Type::Hash,
5190 
5191  //"views": Type::Hash,
5192  //"view_map": Type::Hash,
5193  );
5194 
5196 
5202  "start": Type::Int,
5203  "increment": Type::Int,
5204  "end": Type::Int,
5205  );
5206 
5207 public:
5208 
5209  private :
5212 
5213 public:
5214 
5216 
5221  private constructor(AbstractDatasource nds, *hash nopts);
5222 
5223 
5225  list features();
5226 
5227 
5228  static doOkCallback(*hash opt, int ac, string type, string name, *string table, *string info);
5229 
5230  static *string doCallback(*hash opt, *string sql, int ac, string type, string name, *string table, *string new_name, *string info);
5231 
5232  static list doCallback(*hash opt, list sql, int ac, string type, string name, *string table, *string new_name, *string info);
5233 
5234 /*
5235  static *string doCallback(*hash opt, *string sql, string fmt) {
5236  if (!sql)
5237  return;
5238  if (opt.info_callback)
5239  opt.info_callback(vsprintf(fmt, argv));
5240  if (opt.sql_callback)
5241  opt.sql_callback(sql);
5242  return sql;
5243  }
5244 
5245  static list doCallback(*hash opt, list sql, string fmt) {
5246  if (sql) {
5247  if (opt.info_callback)
5248  opt.info_callback(vsprintf(fmt, argv));
5249  if (opt.sql_callback)
5250  map opt.sql_callback($1), sql;
5251  }
5252  return sql;
5253  }
5254 */
5255 
5257 
5268  any tryExec(string sql);
5269 
5270 
5272 
5282  any tryExecArgs(string sql, *softlist args);
5283 
5284 
5286 
5297  any tryExecRaw(string sql);
5298 
5299 
5301 
5315  list getAlignSql(hash schema_hash, *hash opt, *Tables table_cache);
5316 
5317 
5319 
5332  list getDropSchemaSql(hash schema_hash, *hash opt);
5333 
5334 
5335  private list dropSqlUnlocked(string type, hash schema_hash, code get, code make, *hash opt, string make_arg_type);
5336 
5337 
5338  private list alignCodeUnlocked(string type, hash schema_hash, code get, code make, *hash opt, string make_arg_type);
5339 
5340 
5342 
5356  AbstractSequence makeSequence(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
5357 
5358 
5359  AbstractSequence makeSequenceFromDescription(string name, *hash sh, *hash opts);
5360 
5361 
5363 
5376  AbstractTable makeTable(string name, hash desc, *hash opts);
5377 
5378 
5380 
5391  AbstractFunction makeFunction(string name, string src, *hash opts);
5392 
5393 
5395 
5406  AbstractFunction makeProcedure(string name, string src, *hash opt);
5407 
5408 
5410 
5422  bool dropFunctionIfExists(string name, *hash opt);
5423 
5424 
5426 
5438  bool dropProcedureIfExists(string name, *hash opt);
5439 
5440 
5442 
5454  bool dropSequenceIfExists(string name, *hash opt);
5455 
5456 
5458 
5470  bool dropViewIfExists(string name, *hash opt);
5471 
5472 
5474 
5486  bool dropTableIfExists(string name, *hash opt);
5487 
5488 
5490 
5502  *string getDropFunctionSqlIfExists(string name, *hash opt);
5503 
5504 
5506 
5518  *string getDropProcedureSqlIfExists(string name, *hash opt);
5519 
5520 
5522 
5534  *string getDropSequenceSqlIfExists(string name, *hash opt);
5535 
5536 
5538 
5550  *list getDropTableSqlIfExists(string name, *hash opt);
5551 
5552 
5553  doDropSql(*softlist l, string type, string name, *hash opt);
5554 
5555 
5556  bool doDrop(*softlist l, string type, string name, *hash opt);
5557 
5558 
5560 
5573 
5574 
5576 
5589 
5590 
5592 
5601  *AbstractTable getTable(string name);
5602 
5603 
5605 
5614  *AbstractSequence getSequence(string name);
5615 
5616 
5618 
5629  *AbstractFunction getFunction(string name);
5630 
5631 
5633 
5644  *AbstractFunction getProcedure(string name);
5645 
5646 
5648 
5657  *AbstractView getView(string name);
5658 
5659 
5661 
5670  int getNextSequenceValue(string name);
5671 
5672 
5674 
5683  string getSqlFromList(list l);
5684 
5685 
5687  bool supportsSequences();
5688 
5689 
5691  bool supportsTypes();
5692 
5693 
5695  bool supportsPackages();
5696 
5697 
5699  list listTables();
5700 
5701 
5704 
5705 
5707  list listFunctions();
5708 
5709 
5712 
5713 
5715  list listProcedures();
5716 
5717 
5720 
5721 
5723  list listSequences();
5724 
5725 
5728 
5729 
5731  list listViews();
5732 
5733 
5736 
5737 
5738  private validateOptionsIntern(string err, hash ropt, reference opt);
5739 
5740 
5741  private validateOptionsIntern(string err, hash ropt, reference opt, string tag);
5742 
5743 
5744  static AbstractDatabase getDatabase(AbstractDatasource nds, *hash opts);
5745 
5746  static AbstractDatabase getDatabase(string dsstr, *hash opts);
5747 
5748  static AbstractDatabase getDatabase(hash dsh, *hash opts);
5749 
5750  static checkDriverOptions(reference h, string drv);
5751 
5753  private hash getDatabaseOptions();
5754 
5755 
5757  private hash getCallbackOptions();
5758 
5759 
5761  private hash getCreationOptions();
5762 
5763 
5765  private hash getCacheOptions();
5766 
5767 
5769  private hash getAlignSchemaOptions();
5770 
5771 
5773  private hash getDropSchemaOptions();
5774 
5775 
5778 
5779 
5782 
5783 
5785  private any tryExecArgsImpl(string sql, *softlist args);
5786 
5787 
5789  private any tryExecRawImpl(string sql);
5790 
5791 
5792  private abstract string getCreateSqlImpl(list l);
5793  private abstract list getAlignSqlImpl(hash schema_hash, *hash opt);
5794  private abstract list getDropSchemaSqlImpl(hash schema_hash, *hash opt);
5795 
5796  private abstract *AbstractSequence getSequenceImpl(string name);
5797  private abstract *AbstractFunction getFunctionImpl(string name);
5798  private abstract *AbstractFunction getProcedureImpl(string name);
5799  private abstract *AbstractView getViewImpl(string name);
5800 
5801  private abstract AbstractSequence makeSequenceImpl(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
5802  private abstract AbstractFunction makeFunctionImpl(string name, string src, *hash opts);
5803  private abstract AbstractFunction makeProcedureImpl(string name, string src, *hash opts);
5804 
5805  private abstract list featuresImpl();
5806  private abstract list listTablesImpl();
5807  private abstract list listFunctionsImpl();
5808  private abstract list listProceduresImpl();
5809  private abstract list listSequencesImpl();
5810  private abstract list listViewsImpl();
5811 
5813  private abstract int getNextSequenceValueImpl(string name);
5814 
5816  private abstract bool supportsSequencesImpl();
5817  private abstract bool supportsPackagesImpl();
5818  private abstract bool supportsTypesImpl();
5819  };
5820 
5822 
5832  class Table {
5833 
5834 public:
5835  private :
5838 
5839 public:
5840 
5842 
5854  constructor(AbstractDatasource ds, string name, *hash opts);
5855 
5856 
5858 
5870  constructor(string ds, string name, *hash opts);
5871 
5872 
5874 
5894  constructor(hash ds, string name, *hash opts);
5895 
5896 
5898 
5906  constructor(AbstractDatasource ds, hash desc, string name, *hash opts);
5907 
5908 
5910 
5921  setDatasource(AbstractDatasource nds);
5922 
5923 
5925  commit();
5926 
5927 
5929  rollback();
5930 
5931 
5933  string getName();
5934 
5935 
5938 
5939 
5942 
5943 
5945  any methodGate(string meth);
5946 
5947 
5949 
5953  bool checkExistence();
5954 
5955 
5957 
5966  bool inDb();
5967 
5968 
5970 
5976  setupTable(hash desc, *hash opt);
5977 
5978 
5980 
5988 
5989 
5991 
6002  drop(*hash opt);
6003 
6004 
6006 
6017  dropNoCommit(*hash opt);
6018 
6019 
6021 
6032  any tryExec(string sql);
6033 
6034 
6036 
6046  any tryExecArgs(string sql, *softlist args);
6047 
6048 
6050 
6061  any tryExecRaw(string sql);
6062 
6063 
6065 
6072  truncate();
6073 
6074 
6076 
6083  truncateNoCommit();
6084 
6085 
6087 
6102  string getTruncateSql(*hash opt);
6103 
6104 
6106 
6117  create(*hash opt);
6118 
6119 
6121 
6130  createNoCommit(*hash opt);
6131 
6132 
6134 
6145  rename(string new_name, *reference sql, *Tables table_cache);
6146 
6147 
6149 
6160  bool emptyData();
6161 
6162 
6164 
6173  bool empty();
6174 
6175 
6177 
6201  AbstractColumn addColumn(string cname, hash opt, bool nullable = True, *reference lsql);
6202 
6203 
6205 
6233  list getAddColumnSql(string cname, hash copt, bool nullable = True, *hash opt);
6234 
6235 
6237 
6263  AbstractColumn modifyColumn(string cname, hash opt, bool nullable = True, *reference lsql);
6264 
6265 
6267 
6293  list getModifyColumnSql(string cname, hash copt, bool nullable = True, *hash opt);
6294 
6295 
6297 
6313  AbstractColumn renameColumn(string old_name, string new_name, reference sql);
6314 
6315 
6317 
6335  string getRenameColumnSql(string old_name, string new_name, *hash opt);
6336 
6337 
6339 
6360  AbstractPrimaryKey addPrimaryKey(string cname, softlist cols, *hash opt, *reference sql);
6361 
6362 
6364 
6386  string getAddPrimaryKeySql(string pkname, softlist cols, *hash pkopt, *hash opt);
6387 
6388 
6390 
6408  AbstractPrimaryKey dropPrimaryKey(*reference lsql);
6409 
6410 
6412 
6432 
6433 
6435 
6458  AbstractUniqueConstraint addUniqueConstraint(string cname, softlist cols, *hash opt, *reference sql);
6459 
6460 
6462 
6482  string getAddUniqueConstraintSql(string cname, softlist cols, *hash ukopt, *hash opt);
6483 
6484 
6486 
6508  AbstractIndex addIndex(string iname, bool unique, softlist cols, *hash opt, *reference sql);
6509 
6510 
6512 
6533  string getAddIndexSql(string iname, bool unique, softlist cols, *hash ixopt, *hash opt);
6534 
6535 
6537 
6549  AbstractIndex renameIndex(string old_name, string new_name, reference sql);
6550 
6551 
6553 
6572  string getDropIndexSql(string iname, *hash opt);
6573 
6574 
6576 
6595  string getDropConstraintSql(string cname, *hash opt);
6596 
6597 
6599 
6618  *string getDropConstraintIfExistsSql(string cname, *hash opt, *reference cref);
6619 
6620 
6622 
6640  AbstractIndex dropIndex(string iname, *reference sql);
6641 
6642 
6644 
6667  AbstractForeignConstraint addForeignConstraint(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference sql);
6668 
6669 
6671 
6693  string getAddForeignConstraintSql(string cname, softlist cols, string table, *softlist tcols, *hash fkopt, *hash opt);
6694 
6695 
6697 
6715  AbstractForeignConstraint dropForeignConstraint(string cname, *reference sql);
6716 
6717 
6719 
6735 
6736 
6738 
6758  AbstractCheckConstraint addCheckConstraint(string cname, string src, *hash opt, *reference sql);
6759 
6760 
6762 
6784  string getAddCheckConstraintSql(string cname, string src, *hash copt, *hash opt);
6785 
6786 
6788 
6800  AbstractConstraint renameConstraint(string old_name, string new_name, reference lsql);
6801 
6802 
6804 
6822  AbstractConstraint dropConstraint(string cname, *reference sql);
6823 
6824 
6826 
6846  AbstractTrigger addTrigger(string tname, string src, *hash opt, *reference lsql);
6847 
6848 
6850 
6872  list getAddTriggerSql(string tname, string src, *hash topt, *hash opt);
6873 
6874 
6876 
6894  AbstractTrigger dropTrigger(string tname, *reference sql);
6895 
6896 
6898 
6917  list getDropTriggerSql(string tname, *hash opt);
6918 
6919 
6921 
6932  string getSqlValue(any v);
6933 
6934 
6936 
6952  AbstractColumn dropColumn(string cname, *reference lsql);
6953 
6954 
6956 
6975  list getDropColumnSql(string cname, *hash opt);
6976 
6977 
6979 
6989  insert(hash row, *reference sql);
6990 
6991 
6993 
7003  insertNoCommit(hash row, *reference sql);
7004 
7005 
7007 
7017  insert(hash row, *hash opt);
7018 
7019 
7021 
7031  insertNoCommit(hash row, *hash opt);
7032 
7033 
7035 
7053  int insertFromSelect(list cols, AbstractTable source, *hash sh, *reference sql);
7054 
7055 
7057 
7075  int insertFromSelect(list cols, Table source, *hash sh, *reference sql);
7076 
7077 
7079 
7097  int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *reference sql);
7098 
7099 
7101 
7119  int insertFromSelectNoCommit(list cols, Table source, *hash sh, *reference sql);
7120 
7121 
7123 
7141  int insertFromSelect(list cols, AbstractTable source, *hash sh, *hash opt);
7142 
7143 
7145 
7163  int insertFromSelect(list cols, Table source, *hash sh, *hash opt);
7164 
7165 
7167 
7185  int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *hash opt);
7186 
7187 
7189 
7207  int insertFromSelectNoCommit(list cols, Table source, *hash sh, *hash opt);
7208 
7209 
7211 
7230 
7231 
7233 
7252 
7253 
7255 
7275  int upsert(hash row, int upsert_strategy = AbstractTable::UpsertAuto);
7276 
7277 
7279 
7299  int upsertNoCommit(hash row, int upsert_strategy = AbstractTable::UpsertAuto);
7300 
7301 
7303 
7328  code getUpsertClosure(hash example_row, int upsert_strategy = AbstractTable::UpsertAuto);
7329 
7330 
7332 
7357  code getUpsertClosureWithValidation(hash example_row, int upsert_strategy = AbstractTable::UpsertAuto);
7358 
7359 
7361 
7391 
7392 
7394 
7424 
7425 
7427 
7464  *hash upsertFromSelect(AbstractTable src, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
7465 
7466 
7468 
7507  *hash upsertFromSelectNoCommit(AbstractTable src, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
7508 
7509 
7511 
7548  *hash upsertFromSelect(Table src, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
7549 
7550 
7552 
7591  *hash upsertFromSelectNoCommit(Table src, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
7592 
7593 
7595 
7606  softint rowCount();
7607 
7608 
7610 
7627  Qore::SQL::SQLStatement getRowIterator(*hash sh, *reference sql);
7628 
7629 
7631 
7649 
7650 
7652 
7670  *hash selectRow(*hash sh, *reference sql);
7671 
7672 
7674 
7691  *list selectRows(*hash sh, *reference sql);
7692 
7693 
7695 
7712  *hash select(*hash sh, *reference sql);
7713 
7714 
7716 
7734  *hash selectRow(*hash sh, *hash opt);
7735 
7736 
7738 
7755  *list selectRows(*hash sh, *hash opt);
7756 
7757 
7759 
7776  *hash select(*hash sh, *hash opt);
7777 
7778 
7780 
7798  string getSelectSql(*hash sh, *reference args);
7799 
7800 
7802 
7816  int del(*hash cond, *reference sql);
7817 
7818 
7820 
7834  int delNoCommit(*hash cond, *reference sql);
7835 
7836 
7838 
7854  int update(hash set, *hash cond, *reference sql);
7855 
7856 
7858 
7874  int updateNoCommit(hash set, *hash cond, *reference sql);
7875 
7876 
7878 
7892  int del(*hash cond, *hash opt);
7893 
7894 
7896 
7910  int delNoCommit(*hash cond, *hash opt);
7911 
7912 
7914 
7930  int update(hash set, *hash cond, *hash opt);
7931 
7932 
7934 
7950  int updateNoCommit(hash set, *hash cond, *hash opt);
7951 
7952 
7954 
7963  string getSqlFromList(list l);
7964 
7965 
7967 
7985  string getRenameSql(string new_name, *hash opt);
7986 
7987 
7989 
8000  string getCreateSqlString(*hash opt);
8001 
8002 
8004 
8013  list getCreateSql(*hash opt);
8014 
8015 
8017 
8028  string getCreateTableSql(*hash opt);
8029 
8030 
8032 
8043  *list getCreateIndexesSql(*hash opt);
8044 
8045 
8047 
8058  *string getCreatePrimaryKeySql(*hash opt);
8059 
8060 
8062 
8074 
8075 
8077 
8091 
8092 
8094 
8105  *list getCreateMiscSql(*hash opt);
8106 
8107 
8109 
8121 
8122 
8124 
8137  list getAlignSql(AbstractTable table, *hash opt);
8138 
8139 
8141 
8154  list getAlignSql(Table table, *hash opt);
8155 
8156 
8158 
8171  string getAlignSqlString(AbstractTable table, *hash opt);
8172 
8173 
8175 
8188  string getAlignSqlString(Table table, *hash opt);
8189 
8190 
8192 
8203  *hash find(any id);
8204 
8205 
8207 
8218  *list find(list ids);
8219 
8220 
8222 
8233  *hash find(hash row);
8234 
8235 
8237 
8252  *hash findSingle(*hash cond);
8253 
8254 
8256 
8269  *list findAll(*hash cond);
8270 
8271 
8273 
8280  cache(*hash opts);
8281 
8282 
8284 
8290  clear();
8291 
8292 
8294 
8301  Columns describe();
8302 
8303 
8305 
8315 
8316 
8318 
8328 
8329 
8331 
8340  Indexes getIndexes();
8341 
8342 
8344 
8354 
8355 
8358 
8359 
8362 
8363 
8365  string getDriverName();
8366 
8367  };
8368 
8371 
8372 public:
8373  public :
8375 
8379  const TableOptions = (
8380  "native_case": Type::Boolean,
8381  "table_cache": "Tables",
8382  );
8383 
8385 
8389  const IndexOptions = (
8390  "index_tablespace": Type::String,
8391  "replace": Type::Boolean,
8392  );
8393 
8395 
8398 
8400  const CacheOptions = (
8401  "table_cache": "Tables",
8402  );
8403 
8405 
8409  "table_cache": "Tables",
8410  );
8411 
8413 
8416 
8418 
8431  const SelectOptions = (
8432  "comment": Type::String,
8433  "hint": Type::String,
8434  "columns": Type::NothingType,
8435  "where": "hash/list",
8436  "orderby": "softstringhashlist",
8437  "desc": Type::Boolean,
8438  "limit": Type::Int,
8439  "offset": Type::Int,
8440  "join": Type::Hash,
8441  "groupby": "softstringhashlist",
8442  "having": Type::Hash,
8443  "superquery": Type::Hash,
8444  "forupdate": Type::Boolean,
8445  );
8446 
8449  "indexes": True,
8450  "foreign_constraints": True,
8451  "triggers": True,
8452  );
8453 
8455 
8459  "omit": "softstringlist",
8460  );
8461 
8463 
8471  "column_map": Type::Hash,
8472  "index_map": Type::Hash,
8473  "constraint_map": Type::Hash,
8474  "trigger_map": Type::Hash,
8475  "db_table_cache": "Tables",
8476  );
8477 
8479 
8491  "columns": Type::Hash,
8492  "primary_key": Type::Hash,
8493  "indexes": Type::Hash,
8494  "triggers": Type::Hash,
8495  "foreign_constraints": Type::Hash,
8496  "unique_constraints": Type::Hash,
8497  //"check_constraints": Type::Hash,
8498  "table_cache": "Tables",
8499  );
8500 
8502 
8514  "qore_type": Type::String,
8515  "native_type": Type::String,
8516  "size": Type::Int,
8517  "scale": Type::Int,
8518  "default_value": Type::NothingType,
8519  "comment": Type::String,
8520  );
8521 
8523 
8527  "notnull": Type::Boolean,
8528  );
8529 
8531  const ColumnOptions = hash();
8532 
8534 
8538  "sqlarg_callback": "code",
8539  );
8540 
8542 
8547  const UpsertOptions = (
8548  "info_callback": "code",
8549  "commit_block": Type::Int,
8550  "delete_others": Type::Boolean,
8551  );
8552 
8554 
8559  "info_callback": "code",
8560  "commit_block": Type::Int,
8561  );
8562 
8577 
8584 
8586 
8592 
8594 
8601 
8603 
8607  const UpsertAuto = 4;
8608 
8610 
8614  const UpsertInsertOnly = 5;
8615 
8617 
8620  UpsertInsertFirst: "UpsertInsertFirst",
8621  UpsertUpdateFirst: "UpsertUpdateFirst",
8622  UpsertSelectFirst: "UpsertSelectFirst",
8623  UpsertAuto: "UpsertAuto",
8624  UpsertInsertOnly: "UpsertInsertOnly",
8625  );
8626 
8628 
8631  "UpsertInsertFirst": UpsertInsertFirst,
8632  "UpsertUpdateFirst": UpsertUpdateFirst,
8633  "UpsertSelectFirst": UpsertSelectFirst,
8634  "UpsertAuto": UpsertAuto,
8635  "UpsertInsertOnly": UpsertInsertOnly,
8636  );
8638 
8643  const UR_Inserted = 1;
8645 
8647  const UR_Verified = 2;
8648 
8650  const UR_Updated = 3;
8651 
8653  const UR_Unchanged = 4;
8654 
8656  const UR_Deleted = 5;
8658 
8660 
8663  UR_Inserted: "inserted",
8664  UR_Verified: "verified",
8665  UR_Updated: "updated",
8666  UR_Unchanged: "unchanged",
8667  UR_Deleted: "deleted",
8668  );
8669 
8671 
8674  "inserted": UR_Inserted,
8675  "verified": UR_Verified,
8676  "updated": UR_Updated,
8677  "unchanged": UR_Unchanged,
8678  "deleted": UR_Deleted,
8679  );
8680 
8683  UR_Inserted: "I",
8684  UR_Verified: "V",
8685  UR_Updated: "U",
8686  UR_Unchanged: ".",
8687  UR_Deleted: "X",
8688  );
8689 
8690 public:
8691 
8692  private :
8694  string name;
8710  bool inDb = False;
8712  bool manual = False;
8713 
8714 public:
8715 
8717 
8723  private constructor(AbstractDatasource nds, string nname, *hash nopts);
8724 
8725 
8727  copy(AbstractTable old);
8728 
8729 
8731 
8742  setDatasource(AbstractDatasource nds);
8743 
8744 
8745  private doTableOptions(*hash nopts);
8746 
8747 
8749  commit();
8750 
8751 
8753  rollback();
8754 
8755 
8757 
8766  bool inDb();
8767 
8768 
8770 
8778 
8779 
8781 
8792  drop(*hash opt);
8793 
8794 
8796 
8807  any tryExec(string sql);
8808 
8809 
8811 
8821  any tryExecArgs(string sql, *softlist args);
8822 
8823 
8825 
8836  any tryExecRaw(string sql);
8837 
8838 
8840 
8851  dropNoCommit(*hash opt);
8852 
8853 
8855 
8865  softlist getDropSql(*hash opt);
8866 
8867 
8869 
8876  truncate();
8877 
8878 
8880 
8887  truncateNoCommit();
8888 
8889 
8891 
8906  string getTruncateSql(*hash opt);
8907 
8908 
8910 
8919  create(*hash opt);
8920 
8921 
8923 
8934  createNoCommit(*hash opt);
8935 
8936 
8938 
8949  rename(string new_name, *reference sql, *Tables table_cache);
8950 
8951 
8952  private doRenameIntern(string new_name, *Tables table_cache);
8953 
8954 
8956 
8967  bool emptyData();
8968 
8969 
8971 
8980  bool empty();
8981 
8982 
8983  private bool emptyUnlocked();
8984 
8985 
8987 
8993  setupTable(hash desc, *hash opt);
8994 
8995 
8997 
9020  AbstractColumn addColumn(string cname, hash opt, bool nullable = True, *reference lsql);
9021 
9022 
9024 
9052  list getAddColumnSql(string cname, hash copt, bool nullable = True, *hash opt);
9053 
9054 
9055  private AbstractColumn addColumnUnlocked(string cname, hash opt, bool nullable = True, *reference lsql, bool do_exec = True, bool modify_table = True);
9056 
9057 
9058  private addColumnToTableUnlocked(AbstractColumn c);
9059 
9060 
9062 
9086  AbstractColumn modifyColumn(string cname, hash opt, bool nullable = True, *reference lsql);
9087 
9088 
9090 
9116  list getModifyColumnSql(string cname, hash copt, bool nullable = True, *hash opt);
9117 
9118 
9120 
9137  AbstractColumn renameColumn(string old_name, string new_name, reference sql);
9138 
9139 
9141 
9159  string getRenameColumnSql(string old_name, string new_name, *hash opt);
9160 
9161 
9162  private AbstractColumn renameColumnIntern(AbstractColumn c, string new_name);
9163 
9164 
9165  private validateOptionsIntern(string err, hash ropt, reference opt);
9166 
9167 
9168  private validateOptionsIntern(string err, hash ropt, reference opt, string tag);
9169 
9170 
9171  private execSql(softlist lsql);
9172 
9173 
9175 
9195  AbstractPrimaryKey addPrimaryKey(string pkname, softlist cols, *hash opt, *reference sql);
9196 
9197 
9199 
9221  string getAddPrimaryKeySql(string pkname, softlist cols, *hash pkopt, *hash opt);
9222 
9223 
9224  private setPrimaryKeyUnlocked(AbstractPrimaryKey pk);
9225 
9226 
9227  private AbstractPrimaryKey addPrimaryKeyUnlocked(string pkname, softlist cols, *hash opt, *reference sql);
9228 
9229 
9230  private AbstractPrimaryKey addPrimaryKeyUnlockedIntern(string pkname, softlist cols, *hash opt, *reference sql);
9231 
9232 
9234 
9251 
9252 
9253  private list getDropAllConstraintsAndIndexesOnColumnSqlUnlocked(string cname, *hash opt);
9254 
9255 
9257 
9277 
9278 
9280 
9298  AbstractPrimaryKey dropPrimaryKey(*reference lsql);
9299 
9300 
9302 
9323  AbstractUniqueConstraint addUniqueConstraint(string cname, softlist cols, *hash opt, *reference sql);
9324 
9325 
9327 
9347  string getAddUniqueConstraintSql(string cname, softlist cols, *hash ukopt, *hash opt);
9348 
9349 
9350  private AbstractUniqueConstraint addUniqueConstraintUnlocked(string cname, softlist cols, *hash opt, *reference sql);
9351 
9352 
9353  private AbstractUniqueConstraint addUniqueConstraintUnlockedIntern(string cname, softlist cols, *hash opt, *reference sql);
9354 
9355 
9357 
9378  AbstractIndex addIndex(string iname, bool unique, softlist cols, *hash opt, *reference sql);
9379 
9380 
9382 
9403  string getAddIndexSql(string iname, bool unique, softlist cols, *hash ixopt, *hash opt);
9404 
9405 
9406  private AbstractIndex addIndexUnlocked(string iname, bool unique, softlist cols, *hash opt, *reference sql);
9407 
9408 
9409  private AbstractIndex addIndexUnlockedIntern(string iname, bool unique, softlist cols, *hash opt, *reference sql);
9410 
9411 
9413 
9425  AbstractIndex renameIndex(string old_name, string new_name, reference sql);
9426 
9427 
9429 
9447  AbstractIndex dropIndex(string iname, *reference sql);
9448 
9449 
9451 
9470  string getDropIndexSql(string iname, *hash opt);
9471 
9472 
9474 
9496  AbstractForeignConstraint addForeignConstraint(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference sql);
9497 
9498 
9500 
9522  string getAddForeignConstraintSql(string cname, softlist cols, string table, *softlist tcols, *hash fkopt, *hash opt);
9523 
9524 
9525  private Columns getReferencedTableColumnsUnlocked(string table, *Tables cache, string err = "FOREIGN-CONSTRAINT-ERROR");
9526 
9527 
9528  private AbstractForeignConstraint addForeignConstraintUnlocked(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference sql);
9529 
9530 
9531  private AbstractForeignConstraint addForeignConstraintUnlockedIntern(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference sql);
9532 
9533 
9535 
9553  AbstractForeignConstraint dropForeignConstraint(string cname, *reference sql);
9554 
9555 
9557 
9573 
9574 
9576 
9596  AbstractCheckConstraint addCheckConstraint(string cname, string src, *hash opt, *reference sql);
9597 
9598 
9600 
9622  string getAddCheckConstraintSql(string cname, string src, *hash copt, *hash opt);
9623 
9624 
9625  private AbstractCheckConstraint addCheckConstraintUnlocked(string cname, string src, *hash opt, *reference sql);
9626 
9627 
9628  private AbstractCheckConstraint addCheckConstraintUnlockedIntern(string cname, string src, *hash opt, *reference sql);
9629 
9630 
9632 
9644  AbstractConstraint renameConstraint(string old_name, string new_name, reference lsql);
9645 
9646 
9648 
9667  string getDropConstraintSql(string cname, *hash opt);
9668 
9669 
9671 
9690  *string getDropConstraintIfExistsSql(string cname, *hash opt, *reference cref);
9691 
9692 
9693  private AbstractConstraint findDropConstraintUnlocked(string cname, reference rmv);
9694 
9695 
9697 
9715  AbstractConstraint dropConstraint(string cname, *reference sql);
9716 
9717 
9719 
9739  AbstractTrigger addTrigger(string tname, string src, *hash opt, *reference lsql);
9740 
9741 
9743 
9765  list getAddTriggerSql(string tname, string src, *hash topt, *hash opt);
9766 
9767 
9768  private AbstractTrigger addTriggerUnlocked(string tname, string src, *hash opt, *reference lsql);
9769 
9770 
9771  private AbstractTrigger addTriggerUnlockedIntern(string tname, string src, *hash opt, *reference lsql);
9772 
9773 
9775 
9793  AbstractTrigger dropTrigger(string tname, *reference sql);
9794 
9795 
9797 
9816  list getDropTriggerSql(string tname, *hash opt);
9817 
9818 
9819  private getAllConstraintsUnlocked(*hash opt);
9820 
9821 
9822  private checkUniqueConstraintName(string err, string cname);
9823 
9824 
9825  private checkUniqueConstraintNameValidateOptions(string err, string cname, hash ropt, reference opt);
9826 
9827 
9829  private validateColumnOptions(string cname, reference opt, bool nullable);
9830 
9831 
9833 
9851  AbstractColumn dropColumn(string cname, *reference lsql);
9852 
9853 
9855 
9874  list getDropColumnSql(string cname, *hash opt);
9875 
9876 
9878 
9888  insert(hash row, *reference sql);
9889 
9890 
9892 
9902  insertNoCommit(hash row, *reference sql);
9903 
9904 
9905  private insertNoCommitIntern(hash row, *reference sql, *hash opt);
9906 
9907 
9908  private hash getPlaceholdersAndValues(hash row);
9909 
9910 
9912 
9922  insert(hash row, *hash opt);
9923 
9924 
9926 
9936  insertNoCommit(hash row, *hash opt);
9937 
9938 
9940 
9958  int insertFromSelect(list cols, AbstractTable source, *hash sh, *reference sql);
9959 
9960 
9962 
9980  int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *reference sql);
9981 
9982 
9983  private int insertFromSelectNoCommitIntern(list cols, AbstractTable source, *hash sh, *reference sql, *hash opt);
9984 
9985 
9987 
10005  int insertFromSelect(list cols, AbstractTable source, *hash sh, *hash opt);
10006 
10007 
10009 
10027  int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *hash opt);
10028 
10029 
10031 
10050 
10051 
10053 
10072 
10073 
10074  private int insertFromIteratorNoCommitIntern(Qore::AbstractIterator i, *hash opt);
10075 
10076 
10078 
10093  int upsert(hash row, int upsert_strategy = UpsertAuto);
10094 
10095 
10097 
10112  int upsertNoCommit(hash row, int upsert_strategy = UpsertAuto);
10113 
10114 
10116 
10136  code getUpsertClosure(hash example_row, int upsert_strategy = UpsertAuto);
10137 
10138 
10140 
10160  code getUpsertClosureWithValidation(hash example_row, int upsert_strategy = UpsertAuto);
10161 
10162 
10164 
10197 
10198 
10200 
10233 
10234 
10235  private *hash upsertFromIteratorNoCommitIntern(Qore::AbstractIterator i, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
10236 
10237 
10238  private *hash doDeleteOthersIntern(hash pkh, *hash opt);
10239 
10240 
10242 
10280  *hash upsertFromSelect(AbstractTable t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
10281 
10282 
10284 
10324  *hash upsertFromSelectNoCommit(AbstractTable t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
10325 
10326 
10328 
10366  *hash upsertFromSelect(Table t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
10367 
10368 
10370 
10410  *hash upsertFromSelectNoCommit(Table t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
10411 
10412 
10414 
10425  softint rowCount();
10426 
10427 
10429 
10446  Qore::SQL::SQLStatement getRowIterator(*hash sh, *reference sql);
10447 
10448 
10449  private Qore::SQL::SQLStatement getRowIteratorIntern(*hash sh, *reference sql, *hash opt);
10450 
10451 
10453 
10471 
10472 
10474 
10492  *hash selectRow(*hash sh, *reference sql);
10493 
10494 
10496 
10513  *list selectRows(*hash sh, *reference sql);
10514 
10515 
10517 
10534  *hash select(*hash sh, *reference sql);
10535 
10536 
10538 
10556  *hash selectRow(*hash sh, *hash opt);
10557 
10558 
10560 
10577  *list selectRows(*hash sh, *hash opt);
10578 
10579 
10581 
10598  *hash select(*hash sh, *hash opt);
10599 
10600 
10602 
10620  string getSelectSql(*hash sh, *reference args);
10621 
10622 
10623  *AbstractUniqueConstraint matchAnyUnique(list cols);
10624 
10625 
10626  string getSelectSqlIntern(*hash qh, reference args);
10627 
10628 
10629  string getSelectSqlUnlocked(*hash qh, reference args);
10630 
10631 
10632  string getSelectSqlUnlockedIntern(*hash qh, string from, reference args, *hash ch);
10633 
10634 
10635  private doForUpdate(reference sql);
10636 
10637 
10638  private string getSelectSqlName(*hash qh);
10639 
10640 
10641  private string getColumnExpressionIntern(any cvc, *hash jch, bool join, *hash ch);
10642 
10643 
10644  private string doColumnOperatorIntern(hash cvc, *hash jch, bool join, *hash ch);
10645 
10646 
10647  private string doColumnOperatorIntern(any cop, any arg, *string cve, hash cm, *hash jch, bool join, *hash ch);
10648 
10649 
10650  private string getColumnNameIntern(string cv, *hash jch, bool join, *hash ch);
10651 
10652 
10653  private getSelectWhereSqlUnlocked(reference sql, reference args, *hash qh, *hash jch, bool join = False, *hash ch);
10654 
10655 
10656  private *string getWhereClause(*hash cond, reference args, *string cprefix, *hash jch, bool join = False);
10657 
10658 
10659  private *string getWhereClause(list cond, reference args, *string cprefix, *hash jch, bool join = False);
10660 
10661 
10662  private *string getWhereClauseUnlocked(list cond, reference args, *string cprefix, *hash jch, bool join = False, *hash pch);
10663 
10664 
10665  private *string getWhereClauseUnlocked(*hash cond, reference args, *string cprefix, *hash jch, bool join = False, *hash pch);
10666 
10667 
10668  private *list getWhereClauseIntern(*hash cond, reference args, *string cprefix, *hash jch, bool join = False, *hash ch);
10669 
10670 
10671  private string doWhereExpressionIntern(string cn, any we, reference args, *hash jch, bool join = False, *hash ch);
10672 
10673 
10674  private list getOrderByListUnlocked(*hash qh, *hash jch, *hash ch);
10675 
10676 
10677  private doSelectOrderBySqlUnlocked(reference sql, reference args, *hash qh, *hash jch, *hash ch);
10678 
10679 
10681 
10695  int del(*hash cond, *reference sql);
10696 
10697 
10699 
10713  int delNoCommit(*hash cond, *reference sql);
10714 
10715 
10716  private int delNoCommitIntern(*hash cond, *reference sql, *hash opt);
10717 
10718 
10720 
10736  int update(hash set, *hash cond, *reference sql);
10737 
10738 
10740 
10756  int updateNoCommit(hash set, *hash cond, *reference sql);
10757 
10758 
10759  private int updateNoCommitIntern(hash set, *hash cond, *reference sql, *hash opt);
10760 
10761 
10763 
10777  int del(*hash cond, *hash opt);
10778 
10779 
10781 
10795  int delNoCommit(*hash cond, *hash opt);
10796 
10797 
10799 
10815  int update(hash set, *hash cond, *hash opt);
10816 
10817 
10819 
10835  int updateNoCommit(hash set, *hash cond, *hash opt);
10836 
10837 
10838  private string getUpdateExpression(string col, hash uh);
10839 
10840 
10841  private bool emptyDataUnlocked();
10842 
10843 
10844  private code getUpsertClosureUnlocked(hash row, int upsert_strategy = UpsertAuto);
10845 
10846 
10847  private code getUpsertInsertFirst(Columns cols, hash example_row);
10848 
10849 
10850  private code getUpsertUpdateFirst(Columns cols, hash example_row);
10851 
10852 
10853  private code getUpsertSelectFirst(Columns cols, hash example_row);
10854 
10855 
10856  private code getUpsertInsertOnly(Columns cols, hash example_row);
10857 
10858 
10859  private Columns getUpsertColumns(reference csrc);
10860 
10861 
10862  private string getUpsertSelectSql(hash row, Columns cols, reference updc);
10863 
10864 
10865  private string getUpsertInsertSql(hash row);
10866 
10867 
10868  private string getUpsertUpdateSql(hash row, Columns cols, reference updc);
10869 
10870 
10871  private softbool tryUpdate(string sql, hash row, Columns cols, list updc);
10872 
10873 
10874  private checkValue(string cname, string argname, reference val, string type);
10875 
10876 
10878 
10887  string getSqlFromList(list l);
10888 
10889 
10891 
10902  string getSqlValue(any v);
10903 
10904 
10906  string getName();
10907 
10908 
10910 
10917  cache(*hash opts);
10918 
10919 
10921 
10926  clear();
10927 
10928 
10930 
10937  Columns describe();
10938 
10939 
10941 
10950 
10951 
10953 
10963 
10964 
10965  *AbstractUniqueConstraint findUniqueConstraintUnlocked(string name);
10966 
10967 
10969 
10978  Indexes getIndexes();
10979 
10980 
10983 
10984 
10987 
10988 
10990 
11000 
11001 
11003 
11021  string getRenameSql(string new_name, *hash opt);
11022 
11023 
11025 
11034  string getCreateSqlString(*hash opt);
11035 
11036 
11038 
11047  list getCreateSql(*hash opt);
11048 
11049 
11051 
11062  string getCreateTableSql(*hash opt);
11063 
11064 
11066 
11070  bool checkExistence();
11071 
11072 
11073  private *hash getCheckOmissionOptions(*softlist ol, string err);
11074 
11075 
11077 
11093 
11094 
11095  private list getAlignSqlUnlocked(AbstractTable t, *hash opt);
11096 
11097 
11098  private renameIndexUnlocked(AbstractIndex ix, string new_name);
11099 
11100 
11102 
11115  string getAlignSqlString(AbstractTable t, *hash opt);
11116 
11117 
11119 
11131  *list getCreateIndexesSql(*hash opt, bool cache = True);
11132 
11133 
11135 
11147  *string getCreatePrimaryKeySql(*hash opt, bool cache = True);
11148 
11149 
11151 
11164 
11165 
11167 
11181  *list getCreateConstraintsSql(*hash opt, bool cache = True);
11182 
11183 
11185 
11197  *list getCreateMiscSql(*hash opt, bool cache = True);
11198 
11199 
11201 
11215  *list getCreateTriggersSql(*hash opt, bool cache = True);
11216 
11217 
11219 
11226  *hash find(any id);
11227 
11228 
11230 
11241  *list find(list ids);
11242 
11243 
11244  private string getPrimaryKeyColumn();
11245 
11246 
11248 
11261  *hash find(hash row);
11262 
11263 
11265 
11278  *hash findSingle(*hash cond);
11279 
11280 
11282 
11295  *list findAll(*hash cond);
11296 
11297 
11299  string getSqlName();
11300 
11301 
11303 
11305  private hash getTableOptions();
11306 
11307 
11309 
11312 
11313 
11315 
11317  private hash getConstraintOptions();
11318 
11319 
11321 
11323  private hash getCacheOptions();
11324 
11325 
11327 
11329  private hash getTableCreationOptions();
11330 
11331 
11333 
11335  private hash getAlignTableOptions();
11336 
11337 
11339 
11342 
11343 
11345 
11347  private hash getColumnOptions();
11348 
11349 
11351 
11353  private hash getColumnDescOptions();
11354 
11355 
11357 
11359  private hash getTableColumnDescOptions();
11360 
11361 
11363 
11365  private hash getIndexOptions();
11366 
11367 
11369 
11371  private hash getTriggerOptions();
11372 
11373 
11375 
11377  private hash getSelectOptions();
11378 
11379 
11381 
11383  private hash getUpsertOptions();
11384 
11385 
11387 
11389  private hash getInsertOptions();
11390 
11391 
11393 
11395  private hash getSqlDataCallbackOptions();
11396 
11397 
11399 
11401  private hash getWhereOperatorMap();
11402 
11403 
11405 
11407  private hash getColumnOperatorMap();
11408 
11409 
11411 
11413  private hash getInsertOperatorMap();
11414 
11415 
11417 
11419  private hash getUpdateOperatorMap();
11420 
11421 
11422  private string getCreateTableSqlUnlocked(*hash opt);
11423 
11424 
11425  private *list getCreateIndexesSqlUnlocked(*hash opt, bool cache = True);
11426 
11427 
11428  private *string getCreatePrimaryKeySqlUnlocked(*hash opt, bool cache = True);
11429 
11430 
11431  private *list getCreateConstraintsSqlUnlocked(*hash opt, bool cache = True);
11432 
11433 
11434  private *list getCreateForeignConstraintsSqlUnlocked(*hash opt, bool cache = True);
11435 
11436 
11437  private *list getCreateMiscSqlUnlocked(*hash opt, bool cache = True);
11438 
11439 
11440  private *list getCreateTriggersSqlUnlocked(*hash opt, bool cache = True);
11441 
11442 
11443  private list getCreateSqlUnlocked(*hash opt, bool cache = True);
11444 
11445 
11446  private cacheUnlocked(*hash opt);
11447 
11448 
11449  private any execData(*hash opt, string sql, *list args);
11450 
11451 
11452  private execData(SQLStatement stmt, *hash opt, *list args);
11453 
11454 
11455  static AbstractTable getTable(AbstractDatasource nds, string nname, *hash opts);
11456 
11457  static AbstractTable getTable(string dsstr, string nname, *hash opts);
11458 
11459  static AbstractTable getTable(hash dsh, string nname, *hash opts);
11460 
11461  private getColumnsUnlocked();
11462 
11463 
11464  private getPrimaryKeyUnlocked();
11465 
11466 
11467  // also loads primary key and constraints (for unique constraints)
11468  private getIndexesUnlocked();
11469 
11470 
11471  private getForeignConstraintsUnlocked(*hash opt);
11472 
11473 
11474  private addSourceConstraint(string table_name, AbstractForeignConstraint fk);
11475 
11476 
11477  private getConstraintsUnlocked();
11478 
11479 
11480  private getTriggersUnlocked();
11481 
11482 
11483  private softlist getDropSqlImpl();
11484 
11485 
11486  private string getTruncateSqlImpl();
11487 
11488 
11490  private any tryExecArgsImpl(string sql, *softlist args);
11491 
11492 
11494  private any tryExecRawImpl(string sql);
11495 
11496 
11498  private clearImpl();
11499 
11500 
11501  private preSetupTableImpl(reference desc, *hash opt);
11502 
11503 
11504  private abstract bool emptyImpl();
11505 
11507  private abstract *string getSqlValueImpl(any v);
11508 
11510 
11513  private abstract bool checkExistenceImpl();
11514 
11516  private abstract bool supportsTablespacesImpl();
11517 
11519  private abstract bool constraintsLinkedToIndexesImpl();
11520 
11522  private abstract bool uniqueIndexCreatesConstraintImpl();
11523 
11524  private abstract setupTableImpl(hash desc, *hash opt);
11525 
11526  private abstract Columns describeImpl();
11527  private abstract AbstractPrimaryKey getPrimaryKeyImpl();
11528  private abstract Indexes getIndexesImpl();
11529  private abstract ForeignConstraints getForeignConstraintsImpl(*hash opt);
11530  private abstract Constraints getConstraintsImpl();
11531  private abstract Triggers getTriggersImpl();
11532 
11533  private abstract string getCreateTableSqlImpl(*hash opt);
11534  private abstract *list getCreateMiscSqlImpl(*hash opt, bool cache);
11535  private abstract string getCreateSqlImpl(list l);
11536  private abstract string getRenameSqlImpl(string new_name);
11537  private abstract *list getAlignSqlImpl(AbstractTable t, *hash opt);
11538 
11539  private abstract AbstractColumn addColumnImpl(string cname, hash opt, bool nullable = True);
11540  private abstract AbstractPrimaryKey addPrimaryKeyImpl(string cname, hash ch, *hash opt);
11541  private abstract AbstractIndex addIndexImpl(string iname, bool enabled, hash ch, *hash opt);
11542  private abstract AbstractForeignConstraint addForeignConstraintImpl(string cname, hash ch, string table, hash tch, *hash opt);
11543  private abstract AbstractCheckConstraint addCheckConstraintImpl(string cname, string src, *hash opt);
11544  private abstract AbstractUniqueConstraint addUniqueConstraintImpl(string cname, hash ch, *hash opt);
11545 
11546  private abstract AbstractTrigger addTriggerImpl(string tname, string src, *hash opt);
11547 
11549  private abstract bool tryInsertImpl(string sql, hash row);
11550 
11552  private abstract hash getQoreTypeMapImpl();
11553 
11555  private abstract hash getTypeMapImpl();
11556 
11558  private abstract doSelectOrderByWithOffsetSqlUnlockedImpl(reference sql, reference args, *hash qh, *hash jch, *hash ch);
11559 
11561  private abstract doSelectLimitOnlyUnlockedImpl(reference sql, reference args, *hash qh);
11562 
11564  private abstract copyImpl(AbstractTable old);
11565  };
11566 
11567 /*
11568  public class Sqlite3Table inherits AbstractTable {
11569 
11570  public {
11571  const Sqlite3TypeMap = (
11572  "INTEGER": ("qore": "integer",),
11573  "NUMERIC": ("qore": "number",),
11574  "TEXT": ("qore": "string",),
11575  "BLOB": ("qore": "binary",),
11576  "NONE": ("qore": "any",),
11577  "REAL": ("qore": "float",),
11578  );
11579 
11580  const QoreTypeMap = (
11581  "integer": "INTEGER",
11582  "float": "NUMBER",
11583  "number": "NUMBER",
11584  "string": "VARCHAR2",
11585  #"date": "TIMESTAMP",
11586  "binary": "BLOB",
11587  );
11588  }
11589 
11590  constructor(AbstractDatasource nds, string nname, *hash opts) : AbstractTable(nds, nname, opts) {
11591  }
11592 
11593  private hash describeImpl() {
11594  hash rv;
11595 
11596  # NOTE: sqlite3's pragmas cannot use %v binding
11597 
11598  # table info - PK is part of the table description
11599  hash tableInfo = ds.select("pragma table_info(%s)", name);
11600  context(tableInfo) {
11601  rv.columns{%name} = (
11602  "native_type" : %type,
11603  "qore_type" : Sqlite3TypeMap{%type}.qore,
11604  "size" : NOTHING,
11605  "nullable" : %notnull == 1 ? NOTHING : "YES",
11606  );
11607  if (%pk)
11608  rv.primary_key{%name} = True;
11609  }
11610 
11611  # get index description
11612  hash indexes = ds.select("pragma index_list(%s)", name);
11613  context(indexes) {
11614  rv.indexes{%name}.unique = %unique == 0 ? False : True;
11615  hash indexColumns = ds.select("pragma index_info(%s)", %name);
11616  string columnName = %name;
11617  context (indexColumns) {
11618  rv.indexes{columnName}.columns{%name} = True;
11619  }
11620  }
11621 
11622  # TODO/FIXME: FKs
11623 
11624  return rv;
11625  }
11626  }
11627 */
11628 }
11629 
abstract string getRenameSql(string table_name, string new_name)
returns a string that can be used to rename the index in the database
const ActionMap
maps from action codes to action descriptions
Definition: SqlUtil.qm.dox.h:5094
list getAlignSql(AbstractTable table, *hash opt)
accepts an AbstractTable argument and returns a list of SQL strings required to align the structure a...
string name
the name of the constraint
Definition: SqlUtil.qm.dox.h:4013
string getRenameColumnSql(string old_name, string new_name, *hash opt)
gets an SQL string that can be used to rename an existing column in the table
private validateHashKeysForWhitespaces(any node)
Check input node for all hash keys - if it contains a key with whitespace in the beginning or at the ...
constructor(string n_name, number n_start=1, number n_increment=1, *softnumber n_max)
creates the object from the arguments
int insertFromSelect(list cols, AbstractTable source, *hash sh, *reference sql)
inserts rows into a table based on a select statement from another table (which must be using the sam...
hash cop_avg(any column)
returns a hash for the "avg" operator; returns average column values
string getElementName()
returns "column" since this object stores column objects
softlist getDropSql(*hash opt)
returns the sql required to drop the table; reimplement in subclasses if necessary ...
bool hasColumn(string cname)
returns True if the constraint references the named column
constructor()
creates an empty object
*string index
the index supporting the constraint
Definition: SqlUtil.qm.dox.h:4101
AbstractIndex addIndex(string iname, bool unique, softlist cols, *hash opt, *reference sql)
adds an index to the table; if the table is already known to be in the database, then it is added in ...
createNoCommit(*hash opt)
creates the table with all associated properties (indexes, constraints, etc) without any transaction ...
const SelectOptions
default select options
Definition: SqlUtil.qm.dox.h:8431
string getDropSql()
returns a string that can be used to drop the function from the database
Constraints getConstraints()
returns a Constraints object describing the non-foreign constraints on the table
private hash getCacheOptions()
returns the cache options for this driver
*hash find(any id)
finds a row in the table with the given primary key value; if no row matches the primary key value pa...
string name
the name of the sequence
Definition: SqlUtil.qm.dox.h:4282
hash uop_lower(*hash nest)
returns a hash for the "lower" operator with the given argument; returns a column value in lower case...
bool setIndexBase(string ix)
returns True if the object supports an index property and is set, False if not
const COP_SEQ
to return the value of a sequence
Definition: SqlUtil.qm.dox.h:1780
Qore::AbstractIterator pairIterator()
Returns a HashPairIterator object for the contained hash.
*AbstractFunction getProcedure(string name)
returns an AbstractFunction argument for the given stored procedure name or NOTHING if the stored pro...
const Hash
const UpsertAuto
Upsert option: if the target table is empty, use UpsertInsertFirst, otherwise use UpsertUpdateFirst...
Definition: SqlUtil.qm.dox.h:8607
string getTruncateSql(*hash opt)
gets the SQL that can be used to truncate the table
list getAlignProcedureSql(AbstractFunction f, *hash opt)
returns a list of SQL strings that can be used to update a stored procedure in the database to the st...
string getAddIndexSql(string iname, bool unique, softlist cols, *hash ixopt, *hash opt)
returns an SQL string that can be used to add an index to the table
const String
Qore::SQL::AbstractDatasource getDatasource()
gets the underlying AbstractDatasource
hash op_cge(string arg)
returns a hash for the ">=" operator with the given argument for use in where clauses when comparing ...
const DefaultIopMap
a hash of default insert operator descriptions
Definition: SqlUtil.qm.dox.h:3093
int updateNoCommit(hash set, *hash cond, *reference sql)
updates rows in the table matching an optional condition and returns the count of rows updated; no tr...
private hash getTriggerOptions()
returns the trigger options for this driver
abstract container class that throws an exception if an unknown key is accessed
Definition: SqlUtil.qm.dox.h:3405
*list getCreateTriggersSql(*hash opt)
returns a list of SQL strings that could be used to create triggers on the table or NOTHING if there ...
bool inDb()
returns True if the table has been read from or created in the database, False if not ...
const TableOmissionOptions
alignment omission options
Definition: SqlUtil.qm.dox.h:8448
string getDropSql()
returns a string that can be used to drop the sequence from the database
list getDropSchemaSql(hash schema_hash, *hash opt)
accepts a hash argument describing a database schema and returns a list of SQL strings that can be us...
copy(AbstractHashContainer old)
creates a "deep copy" of the object
hash cop_append(any column, string arg)
returns a hash for the "append" operator with the given argument
hash uop_append(string arg, *hash nest)
returns a hash for the "append" or concatenate operator with the given argument
string sprintf(string fmt,...)
constructor(*hash nh)
creates the object with the hash argument passed
Qore::ListIterator iterator()
Returns a ListIterator object for the contained list.
private hash getDatabaseOptions()
override in subclasses to return driver-specific options
const AC_Unchanged
used when an existing object matches the template and no changes are made
Definition: SqlUtil.qm.dox.h:5057
string getElementName()
must return the name of the contained element
bool equal(ForeignConstraintTarget targ)
returns True if the argument is equal to the current object, False if not
AbstractColumn modifyColumn(string cname, hash opt, bool nullable=True, *reference lsql)
modifies an existing column in the table; if the table is already known to be in the database...
bool dropProcedureIfExists(string name, *hash opt)
drops the given procedure if it exists; returns True if the procedure was dropped, False if not
const OP_IN
the SQL "in" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2699
bool checkExistence()
returns True if the table exists in the database, False if not
*hash getHash()
returns the hash contained by this object
the base abstract class for the table implementation
Definition: SqlUtil.qm.dox.h:8370
const DefaultCopMap
a hash of default column operator descriptions
Definition: SqlUtil.qm.dox.h:1783
bool equal(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
any tryExec(string sql)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
any methodGate(string meth)
executes a method on the contained AbstractTable object
hash iop_seq(string arg)
returns a hash for retrieving the value of the given sequence in insert queries
bool equalExceptName(AbstractIndex ix)
returns True if the argument is equal to the current index with the exception of the name...
const VARCHAR
specifies a VARCHAR column (equivalent to Qore::Type::String)
Definition: SqlUtil.qm.dox.h:1641
AbstractFunction memberGate(string k)
returns the AbstractFunction object corresponding to the key given or throws a KEY-ERROR exception ...
*AbstractUniqueConstraint findUniqueConstraint(string name)
returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary...
hash uop_upper(*hash nest)
returns a hash for the "upper" operator with the given argument; returns a column value in upper case...
list getAlignFunctionSql(AbstractFunction f, *hash opt)
returns a list of SQL strings that can be used to update a function in the database to the function d...
const ForeignConstraintOptions
default foreign constraint options
Definition: SqlUtil.qm.dox.h:8408
string getAddPrimaryKeySql(string pkname, softlist cols, *hash pkopt, *hash opt)
returns the SQL that can be used to add a primary key to the table
int delNoCommit(*hash cond, *reference sql)
deletes rows in the table matching the condition and returns the count of rows deleted; no transactio...
const ColumnOptions
Column options; this is currently empty and can be extended in database-specific modules.
Definition: SqlUtil.qm.dox.h:8531
the table container class stores a collection of tables in a schema
Definition: SqlUtil.qm.dox.h:3494
*list getCreateForeignConstraintsSql(*hash opt)
returns a list of SQL strings that could be used to create foreign constraints on the table or NOTHIN...
Qore::AbstractIterator getUniqueConstraintIterator()
returns an iterator for all unique constraints on the table (including the primary key if any) ...
constructor(string n)
creates the object and sets its name
bool updatable
Flag showing if is the view updatable with DML commands.
Definition: SqlUtil.qm.dox.h:4325
private hash getCreationOptions()
override in subclasses to return driver-specific options
drop(*hash opt)
drops the table from the database; releases the transaction lock after dropping the table ...
*string getDropConstraintIfExistsSql(string cname, *hash opt, *reference cref)
gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns NOT...
private bool equalImpl(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
string getAddPrimaryKeySql(string pkname, softlist cols, *hash pkopt, *hash opt)
returns the SQL that can be used to add a primary key to the table
hash op_between(any l, any r)
returns a hash for the "between" operator with the given arguments, neither of which can be NULL or N...
abstract string getCreateSql(*hash opt)
returns a string that can be used to create the view in the database
const UpsertResultLetterMap
maps upsert result codes to single letter symbols
Definition: SqlUtil.qm.dox.h:8682
const UpsertStrategyMap
hash mapping upsert strategy codes to a text description
Definition: SqlUtil.qm.dox.h:8619
hash make_iop(string iop, any arg)
returns a hash with _iop and arg keys
list getAlignSql(hash schema_hash, *hash opt, *Tables table_cache)
accepts a hash argument describing a database schema and returns a list of SQL strings that can be us...
date now_us()
AbstractIndex take(string k)
removes the given key from the contained hash and returns the value
int update(hash set, *hash cond, *reference sql)
updates rows in the table matching an optional condition and returns the count of rows updated; the t...
abstract string getCreateSql(*hash opt)
returns a string that can be used to create the sequence in the database
abstract list getRenameSql(string table_name, string new_name)
returns a string that can be used to rename the trigger in the database
const DropSchemaOptions
default generic drop schema options
Definition: SqlUtil.qm.dox.h:5163
string getDropIndexSql(string iname, *hash opt)
gets the SQL that can be used to drop an index from the table
hash uop_prepend(string arg, *hash nest)
returns a hash for the "prepend" operator with the given argument
string getCreateTableSql(*hash opt)
returns an SQL string that could be used to create the basic table structure without indexes and cons...
const SchemaDescriptionOptions
default generic schema description keys
Definition: SqlUtil.qm.dox.h:5178
const UR_Inserted
row was inserted
Definition: SqlUtil.qm.dox.h:8644
constructor(string t, Columns c)
creates the object and sets the target table name and the target columns
constructor(string n_name, string n_src)
creates the object from the arguments
string getDriverName()
returns the database driver name
hash op_in()
returns a hash for the "in" operator with all arguments passed to the function; for use in where clau...
cache(*hash opts)
reads in all attributes of the table from the database
string printf(string fmt,...)
const OP_BETWEEN
the SQL "between" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2694
*AbstractView getView(string name)
returns an AbstractView argument for the given view name or NOTHING if the view cannot be found ...
int upsert(hash row, int upsert_strategy=UpsertAuto)
update or insert the data in the table according to the hash argument; the table must have a unique k...
abstract any take(string k)
removes the given key from the contained hash and returns the value
const DatabaseOptions
database options
Definition: SqlUtil.qm.dox.h:5024
string getDisableSql(string table_name)
returns a string that can be used to temporarily disable the constraint from the database; if disabli...
private hash getIndexOptions()
returns the index options for this driver
the abstract base class for index information
Definition: SqlUtil.qm.dox.h:3890
*AbstractUniqueConstraint findEqualUniqueConstraint(AbstractUniqueConstraint uk)
finds a unique constraint with the same columns as the unique constraint passed
hash sourceConstraints
a hash of ForeignConstraintSources, keyed by table name, the value is a hash of foreign constraints k...
Definition: SqlUtil.qm.dox.h:4098
*AbstractUniqueConstraint getSupportingConstraint()
returns the supporting constraint, if any
Qore::ListIterator procedureIterator()
returns an iterator listing the string procedure names in the database
*hash upsertFromSelectNoCommit(AbstractTable t, *hash sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given foreign table and select option hash into the curre...
Triggers getTriggers()
returns an object of class Triggers describing the triggers on the table
add(string k, AbstractColumn val)
adds the given value to the hash with the given key name
const COP_OVER
the SQL "over" clause
Definition: SqlUtil.qm.dox.h:1735
foreign constraint container class that throws an exception if an unknown constraint is accessed ...
Definition: SqlUtil.qm.dox.h:4175
const UpsertResultDescriptionMap
hash mapping upsert descriptions to codes
Definition: SqlUtil.qm.dox.h:8673
Columns columns
an object of class Columns giving the source table that make up the constraint
Definition: SqlUtil.qm.dox.h:4249
const UR_Deleted
row was deleted (only possible with batch upsert methods such as Table::upsertFromIterator() where up...
Definition: SqlUtil.qm.dox.h:8656
list listTables()
returns a list of string table names in the database
bool dropProcedureIfExists(string name, *hash opt)
drops the given procedure if it exists; returns True if the procedure was dropped, False if not
hash op_ne(any arg)
returns a hash for the "!=" or "<>" operator with the given argument for use in where clauses when co...
bool dropFunctionIfExists(string name, *hash opt)
drops the given function if it exists; returns True if the function was dropped, False if not ...
constructor(string n, *hash c, *string n_index)
creates the object from the name an a hash of column information
abstract private bool uniqueIndexCreatesConstraintImpl()
returns True if the database automatically creates a unique constraint when a unique index is created...
hash make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash opt)
returns a hash keyed with the table name assigned to a hash with jop, table, jcols, cond, alias, and ta keys
clearIndex()
clears any index base for the constraint
AbstractForeignConstraint removeForeignConstraint(string cname)
removes the named foreign constraint from the table; no SQL is executed in any case, only the named foreign constraint is removed from the table definition
bool hasColumn(string cname)
returns True if the constraint references the named column
const OP_NE
the SQL not equals operator (!= or <>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2654
AbstractIndex dropIndex(string iname, *reference sql)
drops the given index from the table; if the table is known to be in the database already...
AbstractColumn dropColumn(string cname, *reference lsql)
drops a column from the table
list listSequences()
returns a list of string sequence names in the database
setSupportingConstraint()
clears the supporting constraint
hash cop_as(any column, string arg)
returns a hash for the "as" operator with the given argument
private hash getSchemaDescriptionOptions()
override in subclasses to return driver-specific options
string getType()
returns the type of object
any methodGate(string meth)
executes a method on the contained AbstractDatabase object
string getAddCheckConstraintSql(string cname, string src, *hash copt, *hash opt)
returns an SQL string that can be used to add a check constraint to the table
abstract softlist getRenameSql(string new_name)
returns a list with command(s) that can be used to rename the view in the database ...
hash cop_year_month(any column)
returns a hash for the "year_month" operator with the given argument
list getAlignSql(AbstractTable t, *hash opt)
accepts an AbstractTable argument and returns a list of SQL strings required to align the structure a...
string getDropSql(string table_name)
returns a string that can be used to drop the constraint from the database
list getDropTriggerSql(string tname, *hash opt)
returns SQL that can be used to drop the given trigger from the table
private constructor(AbstractDatasource nds, string nname, *hash nopts)
creates the object; private constructor
truncate()
truncates all the table data; releases the transaction lock after executing
string name
the name of the object
Definition: SqlUtil.qm.dox.h:4351
string getAlignSqlString(AbstractTable t, *hash opt)
accepts an AbstractTable argument and returns an SQL string that could be executed to align the struc...
AbstractColumn dropColumn(string cname, *reference lsql)
drops a column from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally
a class describing a foreign constraint target
Definition: SqlUtil.qm.dox.h:4222
string getAlignSqlString(AbstractTable table, *hash opt)
accepts an AbstractTable argument and returns an SQL string that could be executed to align the struc...
*list getCreateIndexesSql(*hash opt, bool cache=True)
returns a list of SQL strings that could be used to create indexes on the table or NOTHING if there a...
*string getDropFunctionSqlIfExists(string name, *hash opt)
returns the SQL require to drop the given function if it exists or NOTHING if the named function does...
*string qore_type
the equivalent qore type name of the column if known
Definition: SqlUtil.qm.dox.h:3734
add(string k, AbstractConstraint val)
adds the given value to the hash with the given key name
hash op_gt(any arg)
returns a hash for the ">" operator with the given argument for use in where clauses when comparing c...
Constraints getConstraints()
returns a Constraints object describing non-foreign constraints on the table
the base class to use to extend AbstractColumn to implement numeric columns
Definition: SqlUtil.qm.dox.h:3830
AbstractIndex renameIndex(string old_name, string new_name, reference sql)
renames an existing index; if the table is already known to be in the database, then the changes are ...
list getRecreateSql(AbstractDatasource ds, string table_name, *hash opt)
returns a list of strings to drop and recreate the current index; if there are dependent constraints...
const AC_Create
used when a new object is created
Definition: SqlUtil.qm.dox.h:5060
add(string k, AbstractFunction val)
adds the given value to the hash with the given key name
const True
string getAddUniqueConstraintSql(string cname, softlist cols, *hash ukopt, *hash opt)
returns an SQL string that can be used to add a unique constraint to the table
AbstractTrigger memberGate(string k)
returns the AbstractTrigger object corresponding to the key given or throws a KEY-ERROR exception ...
list listViews()
returns a list of string view names in the database
softint rowCount()
returns the number of rows in the table
*string getCreatePrimaryKeySql(*hash opt)
returns an SQL string that could be used to create the primary key on the table
abstract private hash getQoreTypeMapImpl()
returns the qore type -> column type map
list keys()
Returns a list of key names of the contained hash.
AbstractForeignConstraint addForeignConstraint(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference sql)
adds a foreign constraint to the table; if the table is already known to be in the database...
const JopMap
a hash of valid join operators
Definition: SqlUtil.qm.dox.h:2316
hash make_op(string op, any arg)
returns a hash with op and arg keys
const SZ_MAND
the data type takes a mandatory size parameter
Definition: SqlUtil.qm.dox.h:1664
const OP_GT
the SQL greater than operator (>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2644
const CHAR
specifies a CHAR column
Definition: SqlUtil.qm.dox.h:1647
hash op_clt(string arg)
returns a hash for the "<" operator with the given argument for use in where clauses when comparing t...
const COP_AVG
to return the average value
Definition: SqlUtil.qm.dox.h:1725
const DB_SEQUENCES
Feature: sequences.
Definition: SqlUtil.qm.dox.h:1627
const DB_MVIEWS
Feature: materialized views/snapshots.
Definition: SqlUtil.qm.dox.h:1621
int del(*hash cond, *reference sql)
deletes rows in the table matching the condition and returns the count of rows deleted; the transacti...
list getAddTriggerSql(string tname, string src, *hash topt, *hash opt)
returns a list of SQL strings that can be used to add a trigger to the table
private clearImpl()
clears any driver-specific table information
const JOP_LEFT
for left outer joins
Definition: SqlUtil.qm.dox.h:2308
base class for sequences
Definition: SqlUtil.qm.dox.h:4277
*list getCreateTriggersSql(*hash opt, bool cache=True)
returns a list of SQL strings that could be used to create triggers on the table or NOTHING if there ...
list listFunctions()
returns a list of string function names in the database
hash op_cgt(string arg)
returns a hash for the ">" operator with the given argument for use in where clauses when comparing t...
private hash getColumnOperatorMap()
returns the column operator map for this object
string getElementName()
must return the name of the contained element
add(any val)
adds the given value to the list
*AbstractSequence getSequence(string name)
returns an AbstractSequence argument for the given sequence name or NOTHING if the sequence cannot be...
abstract string getElementName()
must return the name of the contained element
int updateNoCommit(hash set, *hash cond, *reference sql)
updates rows in the table matching an optional condition and returns the count of rows updated; no tr...
Columns subset(softlist l)
returns a subset of the current columns according to the list argument
nothing flush()
bool nullable
True if the column can hold a NULL value, False if not
Definition: SqlUtil.qm.dox.h:3740
hash cop_min(any column)
returns a hash for the "min" operator; returns minimum column values
AbstractPrimaryKey addPrimaryKey(string pkname, softlist cols, *hash opt, *reference sql)
adds a primary key to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
const UpsertOptions
default upsert option keys
Definition: SqlUtil.qm.dox.h:8547
number number(softnumber n)
const COP_DISTINCT
to return distinct values
Definition: SqlUtil.qm.dox.h:1710
const COP_YEAR_HOUR
to return a date value with year to hextern information
Definition: SqlUtil.qm.dox.h:1775
const CacheOptions
default cache options
Definition: SqlUtil.qm.dox.h:8400
AbstractFunction makeFunction(string name, string src, *hash opts)
creates a database-specific AbstractFunction object corresponding to the arguments ...
Columns describe()
returns an object of class Columns describing the table
list getAddTriggerSql(string tname, string src, *hash topt, *hash opt)
returns a list of SQL strings that can be used to add a trigger to the table
*AbstractTable getTable(string name)
returns an AbstractTable argument for the given table name or NOTHING if the table cannot be found ...
*list getDropAllForeignConstraintsOnTableSql(string name, *hash opt)
returns a list of SQL strings that can be used to drop all the foreign constraints on a particular ta...
int update(hash set, *hash cond, *reference sql)
updates rows in the table matching an optional condition and returns the count of rows updated; the t...
ForeignConstraints foreignConstraints
foreign constraints description
Definition: SqlUtil.qm.dox.h:8702
bool native_case
native case option
Definition: SqlUtil.qm.dox.h:8708
*AbstractUniqueConstraint constraint
the AbstractUniqueConstraint that this index supports, if any
Definition: SqlUtil.qm.dox.h:3907
string getTruncateSql(*hash opt)
gets the SQL that can be used to truncate the table
AbstractConstraint renameConstraint(string old_name, string new_name, reference lsql)
renames an existing constraint; this can be any constraint on the table, a primary key...
list getDropAllConstraintsAndIndexesOnColumnSql(string cname, *hash opt)
gets a list of SQL strings to drop all constraints and indexes with the given column name; if the col...
private hash getUpdateOperatorMap()
returns the update operator map for this object
bool dropSequenceIfExists(string name, *hash opt)
drops the given sequence if it exists; returns True if the sequence was dropped, False if not ...
Qore::SQL::SQLStatement getRowIterator(*hash sh, *reference sql)
returns an SQLStatement object that will iterate the results of a select statement matching the argum...
string getSelectSql(*hash sh, *reference args)
returns the SQL string to be executed corresponding to the argument hash with an output parameter for...
list getDropPrimaryKeySql(*hash opt)
gets a list of SQL strings that can be used to drop the primary key from the table ...
AbstractFunction take(string k)
removes the given key from the contained hash and returns the value
hash op_cne(string arg)
returns a hash for the "!=" or "<>" operator with the given argument for use in where clauses when co...
create(*hash opt)
creates the table in the database; releases the transaction lock after creating the table ...
AbstractTrigger addTrigger(string tname, string src, *hash opt, *reference lsql)
adds a trigger to the table; if the table is already known to be in the database, then it is added in...
const List
base class for abstract SqlUtil classes
Definition: SqlUtil.qm.dox.h:4963
const CacheOptions
generic cache options
Definition: SqlUtil.qm.dox.h:5032
const UpsertUpdateFirst
Upsert option: update first, if the update fails, then insert.
Definition: SqlUtil.qm.dox.h:8591
private constructor(AbstractDatasource nds, *hash nopts)
creates the object; private constructor
abstract private bool tryInsertImpl(string sql, hash row)
tries to insert a row, if there is a duplicate key, then it returns False, if successful, returns True
int del(*hash cond, *reference sql)
deletes rows in the table matching the condition and returns the count of rows deleted; the transacti...
list getAddColumnSql(string cname, hash copt, bool nullable=True, *hash opt)
returns a list of SQL strings that can be use to add a column to the table
const NULL
const AC_Modify
used when an object is modified in place
Definition: SqlUtil.qm.dox.h:5069
bool equal(Columns cols)
returns True if the argument has the same columns in the same order as the current object...
*hash upsertFromSelectNoCommit(AbstractTable src, *hash sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given foreign table and select option hash into the curre...
create(*hash opt)
creates the table in the database; releases the transaction lock after creating the table ...
const OP_CNE
the SQL not equals operator (!= or <>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2684
any memberGate(string k)
returns the value of the given key in the contained hash if it exists, otherwise throws a KEY-ERROR e...
represents a database; this class embeds an AbstractDatabase object that is created automatically in ...
Definition: SqlUtil.qm.dox.h:4514
int insertFromIteratorNoCommit(Qore::AbstractIterator i, *hash opt)
this method inserts data from the given iterator argument (whose getValue() method must return a hash...
abstract private doSelectLimitOnlyUnlockedImpl(reference sql, reference args, *hash qh)
processes a string for use in SQL select statements when there is a "limit" argument, but no "orderby" or "offset" arguments
constructor(AbstractDatasource ds, string name, *hash opts)
creates the Table object
private hash getSqlDataCallbackOptions()
returns the sql data operation callback options for this driver
const OP_CGT
the SQL greater than operator (>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2674
bool dropSequenceIfExists(string name, *hash opt)
drops the given sequence if it exists; returns True if the sequence was dropped, False if not ...
the base class for foreign key constraint information
Definition: SqlUtil.qm.dox.h:4244
hash op_like(string str)
returns a hash for the "like" operator with the given argument for use in where clauses ...
ForeignConstraintTarget target
a ForeignConstraintTarget object to describe the target table and columns
Definition: SqlUtil.qm.dox.h:4252
*AbstractFunction getFunction(string name)
returns an AbstractFunction argument for the given function name or NOTHING if the function cannot be...
const False
bool setIndexBase(string ix)
returns True if the object supports an index property and is set, False if not
constructor(string n, string n_src)
creates the object and sets its name and the check clause source
AbstractConstraint memberGate(string k)
returns the AbstractConstraint object corresponding to the key given or throws a KEY-ERROR exception ...
Mutex l()
mutex for atomic actions
abstract private copyImpl(AbstractTable old)
db-specific copy actions
string getAddUniqueConstraintSql(string cname, softlist cols, *hash ukopt, *hash opt)
returns an SQL string that can be used to add a unique constraint to the table
const NothingType
hash op_lt(any arg)
returns a hash for the "<" operator with the given argument for use in where clauses when comparing c...
abstract string getCreateSql(string table_name, *hash opts)
returns a string that can be used to create the constraint in the database
hash op_le(any arg)
returns a hash for the "<=" operator with the given argument for use in where clauses when comparing ...
*AbstractView getView(string name)
returns an AbstractView argument for the given view name or NOTHING if the view
AbstractCheckConstraint addCheckConstraint(string cname, string src, *hash opt, *reference sql)
adds a check constraint to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
int scale
the scale for numeric columns
Definition: SqlUtil.qm.dox.h:3835
any tryExec(string sql)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
abstract class for check constraints
Definition: SqlUtil.qm.dox.h:4066
AbstractConstraint renameConstraint(string old_name, string new_name, reference lsql)
renames an existing constraint; this can be any constraint on the table, a primary key...
bool emptyData()
returns True if the table has no data rows, False if not
Qore::ListIterator sequenceIterator()
returns an iterator listing the string sequence names in the database
const AC_Drop
used when an object is dropped
Definition: SqlUtil.qm.dox.h:5063
bool equal(AbstractFunctionBase t)
returns True if the argument is equal to the current object, False if not
constructor(string n, bool u, hash c)
creates the object from the name, a unique flag, and a hash of column information ...
const COP_COUNT
to return the row count
Definition: SqlUtil.qm.dox.h:1730
createNoCommit(*hash opt)
creates the table with all associated properties (indexes, constraints, etc) without any transaction ...
list list(...)
list getCreateSql(*hash opt)
returns a list of SQL strings that could be used to create the table and all known properties of the ...
int getNextSequenceValue(string name)
returns the next value in the given sequence
const OP_CEQ
the SQL equals operator (=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2689
abstract container class that throws an exception if an unknown key is accessed
Definition: SqlUtil.qm.dox.h:3127
any tryExecRaw(string sql)
executes some SQL so that if an error occurs the current transaction state is not lost ...
bool empty()
returns True if the table has no definitions, False if not
hash cop_distinct(any column)
returns a hash for the "distinct" operator with the given argument; returns distinct column values ...
Qore::ListIterator sequenceIterator()
returns an iterator listing the string sequence names in the database
const ActionLetterMap
maps from action codes to action letter codes
Definition: SqlUtil.qm.dox.h:5126
string name
the table's name
Definition: SqlUtil.qm.dox.h:8694
abstract private bool supportsTablespacesImpl()
returns True if the database support tablespaces
bool supportsPackages()
returns True if the database supports packages
string getAddIndexSql(string iname, bool unique, softlist cols, *hash ixopt, *hash opt)
returns an SQL string that can be used to add an index to the table
trigger container class that throws an exception if an unknown trigger is accessed ...
Definition: SqlUtil.qm.dox.h:4466
renameSourceConstraintTable(string old_name, string new_name)
renames a table in a source constraint
AbstractTrigger dropTrigger(string tname, *reference sql)
drops the given trigger from the table; if the table is known to be in the database already...
const DefaultUopMap
a hash of valid update operators
Definition: SqlUtil.qm.dox.h:2190
int index(softstring str, softstring substr, softint pos=0)
string src
the source of the check clause
Definition: SqlUtil.qm.dox.h:4071
string getDatasourceDesc()
returns a descriptive string for the datasource
Indexes indexes
index descriptions
Definition: SqlUtil.qm.dox.h:8700
*string getDropSequenceSqlIfExists(string name, *hash opt)
returns the SQL require to drop the given sequence if it exists or NOTHING if the named sequence does...
string getSqlFromList(list l)
returns an SQL string corresponding to the list of commands in the argument
private hash getDropSchemaOptions()
override in subclasses to return driver-specific options
const DB_PACKAGES
Feature: packages.
Definition: SqlUtil.qm.dox.h:1623
AbstractColumn take(string k)
removes the given key from the contained hash and returns the value
*hash upsertFromSelect(AbstractTable src, *hash sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given foreign table and select option hash into the curre...
list listProcedures()
returns a list of string procedure names in the database
const COP_UPPER
to return column value in upper case
Definition: SqlUtil.qm.dox.h:1700
Qore::ListIterator functionIterator()
returns an iterator listing the string function names in the database
const COP_PLUS
the SQL "minus" operator
Definition: SqlUtil.qm.dox.h:1745
private hash getSelectOptions()
returns the select options for this driver
const Boolean
abstract private bool equalImpl(AbstractFunctionBase t)
returns True if the argument is equal to the current object, False if not
hash cop_minus(any column1, any column2)
returns a hash for the "-" operator with the given arguments
cache(*hash opts)
reads in all attributes of the table from the database
const UR_Verified
row was updated unconditionally (not returned with UpsertSelectFirst)
Definition: SqlUtil.qm.dox.h:8647
abstract private bool supportsSequencesImpl()
returns True if the database supports sequences
clear()
purges the current table definition
const OP_GE
the SQL greater than or equals operator (>=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2649
index container class that throws an exception if an unknown index is accessed
Definition: SqlUtil.qm.dox.h:3848
private validateColumnOptions(string cname, reference opt, bool nullable)
validates column options
const SZ_NUM
the data type is numeric so takes an optional precision and scale
Definition: SqlUtil.qm.dox.h:1670
abstract private bool constraintsLinkedToIndexesImpl()
returns True if the database links constraints to indexes (ie dropping the constraint drops the index...
Qore::ListIterator tableIterator()
returns an iterator listing the string table names in the database
AbstractUniqueConstraint addUniqueConstraint(string cname, softlist cols, *hash opt, *reference sql)
adds a unique constraint to the table; if the table is known to be in the database already...
const TableOptions
table options
Definition: SqlUtil.qm.dox.h:8379
any tryExecArgs(string sql, *softlist args)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
const AC_Rename
used when an object is renamed
Definition: SqlUtil.qm.dox.h:5066
Indexes getIndexes()
returns an object of class Indexes describing the indexes on the table
abstract bool equalImpl(AbstractIndex ix)
returns True if the argument is equal to the current index, False if not
private hash getTableColumnDescOptions()
returns the table column description options for this driver
*hash upsertFromSelect(AbstractTable t, *hash sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given foreign table and select option hash into the curre...
list getAlignSql(hash schema_hash, *hash opt, *Tables table_cache)
accepts a hash argument describing a database schema and returns a list of SQL strings that can be us...
ForeignConstraints getForeignConstraints(*hash opt)
returns a ForeignConstraints object describing the foreign constraints that the table has on other ta...
string getRenameSql(string new_name, *hash opt)
returns an SQL string that could be used to rename the table in the database
any tryExecArgs(string sql, *softlist args)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
any tryExecArgs(string sql, *softlist args)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
*string def_val
default value for column
Definition: SqlUtil.qm.dox.h:3743
int insertFromIterator(Qore::AbstractIterator i, *hash opt)
this method inserts data from the given iterator argument (whose getValue() method must return a hash...
string getCreateSql(AbstractTable t)
returns an sql string that can be used to add the column to a table
AbstractPrimaryKey primaryKey
primary key description
Definition: SqlUtil.qm.dox.h:8698
any tryExecRaw(string sql)
executes some SQL so that if an error occurs the current transaction state is not lost ...
insertNoCommit(hash row, *reference sql)
inserts a row into the table without any transaction management; a transaction will be in progress af...
AbstractPrimaryKey addPrimaryKey(string cname, softlist cols, *hash opt, *reference sql)
adds a primary key to the table; if the table already exists, then it is added in the database also i...
hash cop_plus(any column1, any column2)
returns a hash for the "+" operator with the given arguments
string getAddCheckConstraintSql(string cname, string src, *hash copt, *hash opt)
returns an SQL string that can be used to add a check constraint to the table
bool manual
manual edits
Definition: SqlUtil.qm.dox.h:8712
const AlignTableOptions
table alignment options
Definition: SqlUtil.qm.dox.h:8470
AbstractPrimaryKey getPrimaryKey()
returns an object of class AbstractPrimaryKey describing the primary key of the table ...
*AbstractIndex findEqual(AbstractIndex ix)
find an index with columns equal to the index passed
bool emptyData()
returns True if the table has no data rows, False if not
bool tableRenamed(string old_name, string new_name, string old_sql_name)
updates table names and internal references for renamed tables
removeSourceConstraint(string tname, list cols)
removes a source constraint
any tryExecRaw(string sql)
executes some SQL so that if an error occurs the current transaction state is not lost ...
abstract string getCreateSql(string table_name, *hash opt)
returns a string that can be used to create the constraint in the database
AbstractColumn addColumn(string cname, hash opt, bool nullable=True, *reference lsql)
adds a column to the table; if the table already exists, then it is added in the database also immedi...
int upsertNoCommit(hash row, int upsert_strategy=AbstractTable::UpsertAuto)
update or insert the data in the table according to the hash argument; the table must have a unique k...
*list getCreateForeignConstraintsSql(*hash opt, bool cache=True)
returns a list of SQL strings that could be used to create foreign constraints on the table or NOTHIN...
string getNativeTypeString(string native_type, int precision)
returns the string describing the native type that can be used in SQL (for example to add the column ...
hash getDisableReenableSql(AbstractDatasource ds, string table_name, *hash opts)
returns lists of SQL strings to disable this constraint plus any dependent constraints and another li...
hash cop_lower(any column)
returns a hash for the "lower" operator with the given argument; returns a column value in lower case...
Triggers triggers
trigger descriptions
Definition: SqlUtil.qm.dox.h:8706
*string getRenameTableIfExistsSql(string old_name, string new_name, *hash opts)
returns an SQL string that can be used to rename the given table if it exists and the target does not...
Columns describe()
returns an object of class Columns describing the Table
const DB_PROCEDURES
Feature: procedures.
Definition: SqlUtil.qm.dox.h:1625
const CreationOptions
default generic creation options
Definition: SqlUtil.qm.dox.h:5148
abstract string getRenameSql(AbstractTable t, string new_name)
returns a string that can be used to rename the column
AbstractTable memberGate(string k)
returns the AbstractTable object corresponding to the key given or throws a KEY-ERROR exception ...
const OP_EQ
the SQL equals operator (=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2659
string getEnableSql(string table_name, *hash opt)
returns a string that can be used to enable the constraint in the database; if disabling constraints ...
*AbstractSequence getSequence(string name)
returns an AbstractSequence argument for the given sequence name or NOTHING if the sequence cannot be...
list getDropColumnSql(string cname, *hash opt)
returns the SQL that can be used to drop a column from the table
AbstractPrimaryKey getPrimaryKey()
returns an object of class AbstractPrimaryKey describing the primary key of the table ...
bool dropViewIfExists(string name, *hash opt)
drops the given view if it exists; returns True if the view was dropped, False if not ...
bool exists(...)
AbstractForeignConstraint take(string k)
removes the given key from the contained hash and returns the value
abstract private bool equalImpl(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
abstract string getElementName()
must return the name of the contained element
*string comment
comment on the column
Definition: SqlUtil.qm.dox.h:3746
Qore::AbstractIterator dropIterator()
returns an iterator for a list of cached table names in the order that can be used to drop the tables...
Qore::AbstractIterator getSourceConstraintIterator()
returns an iterator through all known source foreign constraints on the current table ...
list getAlignFunctionSql(AbstractFunction f, *hash opt)
returns a list of SQL strings that can be used to update a function in the database to the function d...
AbstractIndex dropIndex(string iname, *reference sql)
drops the index from the table; if the table is known to be in the database already, then it is also dropped from the database immediately; otherwise it is only removed internally
string getName()
returns the name of the table
code getUpsertClosure(hash example_row, int upsert_strategy=AbstractTable::UpsertAuto)
returns a closure that can be executed given a hash argument representing a single row that will be u...
bool val()
Returns False if the contained list is empty, True if not.
string getSelectSql(*hash sh, *reference args)
returns the SQL string to be executed corresponding to the argument hash with an output parameter for...
string src
the source code
Definition: SqlUtil.qm.dox.h:4322
bool unique
True if the index is a unique index, False if not
Definition: SqlUtil.qm.dox.h:3898
string getDropIndexSql(string iname, *hash opt)
gets the SQL that can be used to drop an index from the table
list getModifySql(AbstractTable t, AbstractColumn c, *hash opt)
returns a list of sql strings that can be used to modify the column to the new definition; if the col...
hash cop_over(any column, string arg)
returns a hash for the "over" clause
Qore::SQL::AbstractDatasource getDatasource()
gets the underlying AbstractDatasource
const AC_Insert
used when data is inserted in a table
Definition: SqlUtil.qm.dox.h:5081
any tryExec(string sql)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
add(string k, Table val)
adds the given value to the hash with the given key name
Qore::ListIterator viewIterator()
returns an iterator listing the string view names in the database
constructor(AbstractDatasource ds, *hash opts)
creates the Database object
string getNativeTypeString()
returns the string describing the native type that can be used in SQL (for example to add the colunn ...
Qore::AbstractIterator iterator()
Returns a HashIterator object for the contained hash.
private any tryExecRawImpl(string sql)
tries to execute a command so that if an error occurs the current transaction status is not lost ...
Qore::ListIterator tableIterator()
returns an iterator listing the string table names in the database
string getAddForeignConstraintSql(string cname, softlist cols, string table, *softlist tcols, *hash fkopt, *hash opt)
returns an SQL string that can be used to add a foreign constraint to the table
list getCreateSql(*hash opt)
returns a list of SQL strings that could be used to create the table and all known properties of the ...
string getSqlName()
returns the name of the table to be used in SQL (with a possible qualifiers for schema, etc)
abstract list getAddColumnSql(AbstractTable t)
returns a list of sql strings that can be used to add the column to an existing table ...
const COP_YEAR_MONTH
to return a date value with year to month information
Definition: SqlUtil.qm.dox.h:1765
string src
the source of the object
Definition: SqlUtil.qm.dox.h:4357
private bool equalImpl(AbstractConstraint c)
returns True if the argument is equal to the current object, False if not
string getElementName()
must return the name of the contained element
private hash getForeignConstraintOptions()
return the foreign constraint options for this driver
const COP_MAX
to return the maximum value
Definition: SqlUtil.qm.dox.h:1720
AbstractForeignConstraint dropForeignConstraint(string cname, *reference sql)
drops a foreign constraint from the table; if the table is known to be in the database already...
insert(hash row, *reference sql)
inserts a row into the table; the transaction is committed if successful, if an error occurs...
const COP_AS
to rename a column on output
Definition: SqlUtil.qm.dox.h:1680
string getDriverName()
returns the database driver name
hash cop_divide(any column1, any column2)
returns a hash for the "/" operator with the given arguments
hash op_ge(any arg)
returns a hash for the ">=" operator with the given argument for use in where clauses when comparing ...
abstract list getRenameSql(string table_name, string new_name)
returns a list of SQL strings that can be used to rename the constraint in the database ...
hash cop_seq(string seq)
returns a hash for the "seq" operator with the given argument giving the sequence name whose value sh...
Columns columns
column description object
Definition: SqlUtil.qm.dox.h:8696
int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *reference sql)
inserts rows into a table based on a select statement from another table (which must be using the sam...
private constructor(AbstractDatasource nds, *hash nopts)
creates the object; private constructor
*AbstractFunction getFunction(string name)
returns an AbstractFunction argument for the given function name or NOTHING if the function cannot be...
const DB_VIEWS
Feature: views.
Definition: SqlUtil.qm.dox.h:1633
hash op_not(hash arg)
returns a hash for the "not" operator; for use in where clauses
Qore::ListIterator procedureIterator()
returns an iterator listing the string procedure names in the database
AbstractForeignConstraint addForeignConstraint(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference sql)
adds an foreign constraint to the table; if the table is already known to be in the database...
const OP_LE
the SQL less than or equals (<=) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2639
private hash getCacheOptions()
override in subclasses to return driver-specific options
hash join_inner(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for standard inner joins with the given arguments for use when joining with a table ot...
const BLOB
specifies a large variable-length binary column (ie BLOB or BYTEA, etc)
Definition: SqlUtil.qm.dox.h:1650
string name
the name of the index
Definition: SqlUtil.qm.dox.h:3895
int insertFromIterator(Qore::AbstractIterator i, *hash opt)
this method inserts data from the given iterator argument (whose getValue() method must return a hash...
string type(any arg)
hash cop_value(any arg)
returns a hash for the "value" operator with the given argument
AbstractConstraint dropConstraint(string cname, *reference sql)
drops a constraint from the table; this can be any constraint on the table, a primary key...
*string getCreatePrimaryKeySql(*hash opt, bool cache=True)
returns an SQL string that could be used to create the primary key on the table
Columns columns
columns in the target table
Definition: SqlUtil.qm.dox.h:4230
drop(*hash opt)
drops the table from the database; releases the transaction lock after dropping the table ...
const OP_CLE
the SQL less than or equals (<=) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2669
hash join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for left outer joins with the given arguments
AbstractColumn addColumn(string cname, hash opt, bool nullable=True, *reference lsql)
adds a column to the table; if the table is already known to be in the database, then it is added in ...
AbstractColumn modifyColumn(string cname, hash opt, bool nullable=True, *reference lsql)
modifies an existing column in the table; if the table already exists, then the changes are effected ...
const DB_TABLES
Feature: tables.
Definition: SqlUtil.qm.dox.h:1629
list getAlignProcedureSql(AbstractFunction f, *hash opt)
returns a list of SQL strings that can be used to update a stored procedure in the database to the st...
string getElementName()
must return the name of the contained element
*hash select(*hash sh, *reference sql)
returns a hash of lists representing the columns and rows in the table that match the argument hahs ...
const CLOB
specifies a large variable-length character column (ie CLOB or TEXT, etc)
Definition: SqlUtil.qm.dox.h:1653
string getSqlFromList(list l)
returns an SQL string corresponding to the list of commands in the argument
bool supportsTypes()
returns True if the database supports named types
const OP_NOT
the SQL "not" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2704
AbstractColumn renameColumn(string old_name, string new_name, reference sql)
renames an existing column; if the table already exists, then the changes are effected in the databas...
string getRenameSql(string new_name, *hash opt)
returns an SQL string that could be used to rename the table in the database
clear()
purges the contained data
Columns columns
an object of class Columns representing the columns in the index
Definition: SqlUtil.qm.dox.h:3901
*list getCreateConstraintsSql(*hash opt)
returns a list of SQL strings that could be used to create non-foreign constraints on the table or NO...
AbstractForeignConstraint memberGate(string k)
returns the AbstractForeignConstraint object corresponding to the key given or throws a KEY-ERROR exc...
const SqlDataCallbackOptions
generic SQL data operation callbacks
Definition: SqlUtil.qm.dox.h:8537
base class for function or objects with code
Definition: SqlUtil.qm.dox.h:4346
const DB_TYPES
Feature: named types.
Definition: SqlUtil.qm.dox.h:1631
addSourceConstraint(string tname, AbstractForeignConstraint fk)
adds a foreign constraint source to the unique constraint
string getRenameColumnSql(string old_name, string new_name, *hash opt)
gets an SQL string that can be used to rename an existing column in the table
AbstractTable makeTable(string name, hash desc, *hash opts)
creates a database-specific AbstractTable object corresponding to the arguments
bool empty()
returns True if the container is empty, False if not
copy(AbstractTable old)
copies the object
private hash getInsertOptions()
returns the insert options for this driver
Qore::ListIterator viewIterator()
returns an iterator listing the string view names in the database
setDatasource(AbstractDatasource nds)
changes the datasource for the table; if the inDb flag is True, then it is set to False by calling th...
AbstractFunction makeFunction(string name, string src, *hash opt)
creates a database-specific AbstractFunction object corresponding to the arguments ...
const UpsertResultMap
hash mapping upsert results to a description
Definition: SqlUtil.qm.dox.h:8662
findMatchingIndex(*Indexes indexes)
find an index that matches the constraint and marks both objects as related
private any tryExecArgsImpl(string sql, *softlist args)
tries to execute a command so that if an error occurs the current transaction status is not lost ...
string name
the name of the column
Definition: SqlUtil.qm.dox.h:3728
bool empty()
returns True if the container is empty, False if not
bool supportsSequences()
returns True if the database supports sequences
private hash getAlignSchemaOptions()
override in subclasses to return driver-specific options
string string(softstring str)
private hash getConstraintOptions()
returns the constraint options for this driver
AbstractDatasource ds
the connection to the database server
Definition: SqlUtil.qm.dox.h:4968
string getName()
returns the constraint name
insert(hash row, *reference sql)
inserts a row into the table; the transaction is committed if successful, if an error occurs...
*list getCreateMiscSql(*hash opt)
returns a list of SQL strings that could be used to create other table attributes (such as comments...
abstract private list getModifySqlImpl(AbstractTable t, AbstractColumn c, *hash opt)
returns a list of sql strings that can be used to modify the column to the new definition; if the col...
string getName()
returns the index name
base class for functions
Definition: SqlUtil.qm.dox.h:4387
private hash getTableDescriptionHashOptions()
returns the table description hash options for this driver
string getCreateSqlString(*hash opt)
returns an SQL string that could be used to create the table and all known properties of the table ...
const COP_MULTIPLY
the SQL "multiply" operator
Definition: SqlUtil.qm.dox.h:1755
AbstractUniqueConstraint addUniqueConstraint(string cname, softlist cols, *hash opt, *reference sql)
adds a unique constraint to the table; if the table is known to be in the database already...
list listSequences()
returns a list of string sequence names in the database
Constraints constraints
constraint descriptions
Definition: SqlUtil.qm.dox.h:8704
const COP_PREPEND
to prepend a string to a column on output
Definition: SqlUtil.qm.dox.h:1685
list getDropSchemaSql(hash schema_hash, *hash opt)
accepts a hash argument describing a database schema and returns a list of SQL strings that can be us...
hash cop_count(string column="")
returns a hash for the "count" operator; returns row counts
string getDropConstraintSql(string cname, *hash opt)
gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the ...
string getSqlFromList(list l)
returns an SQL string corresponding to the list of commands in the argument
list listFunctions()
returns a list of string function names in the database
bool partialMatchKeys(hash h1)
returns True if the hash argument has at least the same keys (in any order, can have more keys)...
AbstractSequence makeSequence(string name, number start=1, number increment=1, *softnumber end, *hash opts)
creates a database-specific AbstractSequence object corresponding to the arguments ...
const AC_Recreate
used when an object is recreated (usually dropped and recreated in place)
Definition: SqlUtil.qm.dox.h:5078
*string getIndex()
returns the name of the associated index, if any
abstract clearIndex()
clears any index base for the constraint
base class for views
Definition: SqlUtil.qm.dox.h:4311
bool dropTableIfExists(string name, *hash opt)
drops the given table if it exists; returns True if the table was dropped, False if not ...
bool hasKey(string k)
Returns True if the key exists in the contained hash (may or may not be assigned a value)...
bool equal(AbstractIndex ix)
returns True if the argument is equal to the current index, False if not
abstract list getRenameSql(string new_name)
returns a string that can be used to rename the function in the database
*hash opts
option hash
Definition: SqlUtil.qm.dox.h:4974
string getCreateSqlString(*hash opt)
returns an SQL string that could be used to create the table and all known properties of the table ...
AbstractTable getTable()
returns the AbstractTable object contained by this object
bool native_case
native case option
Definition: SqlUtil.qm.dox.h:5211
*list getCreateConstraintsSql(*hash opt, bool cache=True)
returns a list of SQL strings that could be used to create non-foreign constraints on the table or NO...
list getAddColumnSql(string cname, hash copt, bool nullable=True, *hash opt)
returns a list of SQL strings that can be use to add a column to the table
bool checkExistence()
returns True if the table exists in the database, False if not
AbstractFunction makeProcedure(string name, string src, *hash opt)
creates a database-specific AbstractFunction object for a stored procedure corresponding to the argum...
renameKey(string old_name, string new_name)
renames the given key; maintains the key order
*hash selectRow(*hash sh, *reference sql)
returns a hash representing the row in the table that matches the argument hash; if more than one row...
list getModifyColumnSql(string cname, hash copt, bool nullable=True, *hash opt)
gets a list of SQL strings that can be used to modify an existing column in the table ...
private hash getTableCreationOptions()
returns the table creation options for this driver
const InsertOptions
default insert option keys
Definition: SqlUtil.qm.dox.h:8558
Qore::SQL::AbstractDatasource getDatasource()
gets the underlying AbstractDatasource
AbstractTrigger take(string k)
removes the given key from the contained hash and returns the value
abstract private bool equalImpl(AbstractColumn c)
returns True if the argument is equal to the current object, False if not
any tryExecRaw(string sql)
executes some SQL so that if an error occurs the current transaction state is not lost ...
*hash upsertFromIterator(Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given iterator argument (whose getValue() method must ret...
insertNoCommit(hash row, *reference sql)
inserts a row into the table without any transaction management; a transaction will be in progress af...
const TriggerOptions
default trigger options
Definition: SqlUtil.qm.dox.h:8415
*list findAll(*hash cond)
finds all rows in the table with the given column values; a list of hashes is returned representing t...
hash cop_prepend(any column, string arg)
returns a hash for the "prepend" operator with the given argument
AbstractForeignConstraint dropForeignConstraint(string cname, *reference sql)
drops the foreign constraint from the table; if the table is known to be in the database already...
string type
the type of object
Definition: SqlUtil.qm.dox.h:4354
setupTable(hash desc, *hash opt)
creates the object from a table description hash
const Int
AbstractIndex renameIndex(string old_name, string new_name, reference sql)
renames an existing index; if the table is already known to be in the database, then the changes are ...
abstract private doSelectOrderByWithOffsetSqlUnlockedImpl(reference sql, reference args, *hash qh, *hash jch, *hash ch)
processes a string for use in SQL select statements when there is an "order by" and "offset" argument...
*list findAll(*hash cond)
finds all rows in the table with the given column values; a list of hashes is returned representing t...
const COP_YEAR
to return a date value with year information only
Definition: SqlUtil.qm.dox.h:1760
constructor(softlist nl)
creates the object with the list argument passed
bool hasColumn(string cname)
returns True if the constraint references the named column
Triggers getTriggers()
returns an object of class Triggers describing the triggers on the table
const SequenceDescriptionOptions
default generic sequence description keys
Definition: SqlUtil.qm.dox.h:5201
AbstractCheckConstraint addCheckConstraint(string cname, string src, *hash opt, *reference sql)
adds a check constraint to the table; if the table is already known to be in the database, then it is added in the database also immediately; otherwise it is only added internally and can be created when create() is called for example
abstract list getCreateSql(string table_name, *hash opt)
returns a string that can be used to create the trigger in the database
*list getCreateIndexesSql(*hash opt)
returns a list of SQL strings that could be used to create indexes on the table or NOTHING if there a...
softint rowCount()
returns the number of rows in the table
rollback()
rolls back the current transaction on the underlying Qore::SQL::AbstractDatasource ...
string getElementName()
returns "table" since this object stores AbstractTable objects
list getDropPrimaryKeySql(*hash opt)
gets a list of SQL strings that can be used to drop the primary key from the table ...
abstract private hash getTypeMapImpl()
returns the type name -> type description hash
string getDropSql()
returns a string that can be used to drop the view from the database
dropNoCommit(*hash opt)
drops the table from the database without any transaction management
string getElementName()
returns "foreign constraint" for the type of object encapsulated
int size
the size of the column
Definition: SqlUtil.qm.dox.h:3737
AbstractColumn renameColumn(string old_name, string new_name, reference sql)
renames an existing column; if the table is already known to be in the database, then the changes are...
string getSqlFromList(list l)
returns an SQL string corresponding to the list of commands in the argument
bool dropTableIfExists(string name, *hash opt)
drops the given table if it exists; returns True if the table was dropped, False if not ...
list listProcedures()
returns a list of string procedure names in the database
list getModifyColumnSql(string cname, hash copt, bool nullable=True, *hash opt)
gets a list of SQL strings that can be used to modify an existing column in the table ...
string getSqlValue(any v)
returns a string for use in SQL queries representing the DB-specific value of the argument ...
int size()
Returns the number of keys in the contained hash.
private any tryExecRawImpl(string sql)
tries to execute a command so that if an error occurs the current transaction status is not lost ...
Qore::AbstractIterator keyIterator()
Returns a HashKeyIterator object for the contained hash.
private hash getWhereOperatorMap()
returns the "where" operator map for this object
*hash upsertFromIteratorNoCommit(Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given iterator argument (whose getValue() method must ret...
any tryExecArgs(string sql, *softlist args)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
abstract string getRenameSql(string new_name)
returns a string that can be used to rename the sequence in the database
string native_type
the native type name of the column
Definition: SqlUtil.qm.dox.h:3731
const COP_VALUE
to append a constant value to use as an output column value
Definition: SqlUtil.qm.dox.h:1695
truncateNoCommit()
truncates all the table data without any transaction management
column container class that throws an exception if an unknown column is accessed
Definition: SqlUtil.qm.dox.h:3676
the base class for triggers
Definition: SqlUtil.qm.dox.h:4448
hash join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for right outer joins with the given arguments
hash op_eq(any arg)
returns a hash for the "=" operator with the given argument for use in where clauses when comparing c...
private hash getAlignTableOptions()
returns the align table options for this driver
int insertFromSelect(list cols, AbstractTable source, *hash sh, *reference sql)
inserts rows into a table based on a select statement from another table (which must be using the sam...
const OP_LT
the SQL less than (<) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2634
string dsdesc
datasource description
Definition: SqlUtil.qm.dox.h:4970
*hash findSingle(*hash cond)
finds a single row in the table that match the row condition passed; multiple rows may match...
setDatasource(AbstractDatasource nds)
changes the datasource for the table; if the inDb flag is True, then it is set to False by calling th...
bool empty()
returns True if the table has no definitions, False if not
const AlignSchemaOptions
default generic schema description / alignment options
Definition: SqlUtil.qm.dox.h:5158
*hash findConstraintOn(string table, softlist cols)
returns either a hash of AbstractColumn information or NOTHING if no foreign constraint can be found ...
add(string k, AbstractForeignConstraint val)
adds the given value to the hash with the given key name
function container class that throws an exception if an unknown function is accessed ...
Definition: SqlUtil.qm.dox.h:4410
Indexes getIndexes()
returns an object of class Indexes describing the indexes on the table
const UR_Unchanged
row was unchanged (only possible with UpsertSelectFirst)
Definition: SqlUtil.qm.dox.h:8653
AbstractPrimaryKey dropPrimaryKey(*reference lsql)
drops the primary key from the table; if the table is known to be in the database already...
rename(string new_name, *reference sql, *Tables table_cache)
renames the table; if the table already exists in the database, then the changes are effected in the ...
const UR_Updated
row was updated because it was different (only possible with UpsertSelectFirst)
Definition: SqlUtil.qm.dox.h:8650
int getNextSequenceValue(string name)
returns the next value in the given sequence
AbstractDatabase db
the embedded AbstractDatabase object that actually provides the functionality for this class ...
Definition: SqlUtil.qm.dox.h:4519
the base class for column information
Definition: SqlUtil.qm.dox.h:3723
truncate()
truncates all the table data; releases the transaction lock after executing
bool hasColumn(string cname)
returns True if the constraint references the named column
const UpsertInsertOnly
Upsert option: insert if the row does not exist, otherwise ignore.
Definition: SqlUtil.qm.dox.h:8614
setName(string new_name)
sets the new name of the object
hash join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for standard inner joins with the given arguments
AbstractPrimaryKey dropPrimaryKey(*reference lsql)
drops the primary key from the table; if the table is known to be in the database already...
add(string k, AbstractTrigger val)
adds the given value to the hash with the given key name
const OP_CGE
the SQL greater than or equals operator (>=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2679
const AC_Truncate
used when a table is truncated
Definition: SqlUtil.qm.dox.h:5072
hash op_cle(string arg)
returns a hash for the "<=" operator with the given argument for use in where clauses when comparing ...
string getName()
returns the name of the table
represents a database table; this class embeds an AbstractTable object that is created automatically ...
Definition: SqlUtil.qm.dox.h:5832
const IndexOptions
default index options
Definition: SqlUtil.qm.dox.h:8389
private hash getColumnOptions()
returns the column options for this driver
list values()
Returns a list of values of the contained hash.
AbstractConstraint dropConstraint(string cname, *reference sql)
drops a constraint from the table; this can be any constraint on the table, a primary key...
*hash find(any id)
finds a row in the table with the given primary key value; if no row matches the primary key value pa...
bool equal(AbstractColumn c)
returns True if the argument is equal to the current object, False if not
rename(string new_name, *reference sql, *Tables table_cache)
renames the table; if the table is already known to be in the database in the database, then the changes are effected in the database also immediately; otherwise it is only updated internally
AbstractTable take(string k)
removes the given key from the contained hash and returns the value
hash make_cop(string cop, any column, any arg)
returns a hash with cop, column, and arg keys
const COP_MIN
to return the minimum value
Definition: SqlUtil.qm.dox.h:1715
const UpsertSelectFirst
Upsert option: select first, if the row is unchanged, do nothing, if it doesn't exist, insert, otherwise update.
Definition: SqlUtil.qm.dox.h:8600
bool dropFunctionIfExists(string name, *hash opt)
drops the given function if it exists; returns True if the function was dropped, False if not ...
const COP_YEAR_DAY
to return a date value with year to day information
Definition: SqlUtil.qm.dox.h:1770
dropNoCommit(*hash opt)
drops the table from the database without any transaction management
code getUpsertClosureWithValidation(hash example_row, int upsert_strategy=AbstractTable::UpsertAuto)
returns a closure that can be executed given a hash argument representing a single row that will be u...
const Closure
setupTable(hash desc, *hash opt)
creates the object from a table description hash
add(string k, AbstractIndex val)
adds the given value to the hash with the given key name
AbstractFunction makeProcedure(string name, string src, *hash opt)
creates a database-specific AbstractFunction object for a stored procedure corresponding to the argum...
const UpsertInsertFirst
Upsert option: insert first, if the insert fails, then update.
Definition: SqlUtil.qm.dox.h:8583
private hash getCallbackOptions()
override in subclasses to return driver-specific options
*hash selectRow(*hash sh, *reference sql)
returns a hash representing the row in the table that matches the argument hash; if more than one row...
AbstractIndex addIndex(string iname, bool unique, softlist cols, *hash opt, *reference sql)
adds an index to the table; if the table already exists, then it is added in the database also immedi...
string name
the name of the sequence
Definition: SqlUtil.qm.dox.h:4319
*hash findSingle(*hash cond)
finds a single row in the table that match the row condition passed; multiple rows may match...
constructor(string n, string n_src)
creates the object and sets its name and the trigger source
list features()
See DBFeaturesConstants.
*number max
the ending number
Definition: SqlUtil.qm.dox.h:4291
abstract private bool checkExistenceImpl()
returns True if the table exists in the DB, False if not
hash cop_upper(any column)
returns a hash for the "upper" operator with the given argument; returns a column value in upper case...
code getUpsertClosure(hash example_row, int upsert_strategy=UpsertAuto)
returns a closure that can be executed given a hash argument representing a single row that will be u...
bool val()
Returns False if the contained hash has no keys, True if it does.
string getAddForeignConstraintSql(string cname, softlist cols, string table, *softlist tcols, *hash fkopt, *hash opt)
returns an SQL string that can be used to add a foreign constraint to the table
*list selectRows(*hash sh, *reference sql)
returns a list of hashes representing the rows in the table that match the argument hash ...
list getList()
returns the list contained by this object
Qore::SQL::SQLStatement getRowIterator(*hash sh, *reference sql)
returns an SQLStatement object that will iterate the results of a select statement matching the argum...
const ColumnDescOptions
Column description options.
Definition: SqlUtil.qm.dox.h:8513
number increment
the increment
Definition: SqlUtil.qm.dox.h:4288
*string getDropConstraintIfExistsSql(string cname, *hash opt, *reference cref)
gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns NOT...
AbstractColumn memberGate(string k)
returns the AbstractColumn object corresponding to the key given or throws a KEY-ERROR exception ...
bool matchKeys(hash h1)
returns True if the hash argument has the same keys (in any order), False if not
list listViews()
returns a list of string view names in the database
*string getDropProcedureSqlIfExists(string name, *hash opt)
returns the SQL require to drop the given procedure if it exists or NOTHING if the named procedure do...
bool inDb()
returns True if the table has been read from or created in the database, False if not ...
const OP_LIKE
the SQL "like" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2629
rollback()
rolls back the current transaction on the underlying Qore::SQL::AbstractDatasource ...
const CallbackOptions
generic callback options
Definition: SqlUtil.qm.dox.h:5042
const TableDescriptionHashOptions
Table description options.
Definition: SqlUtil.qm.dox.h:8490
const SZ_NONE
the data type does not take a size parameter
Definition: SqlUtil.qm.dox.h:1661
AbstractIndex memberGate(string k)
returns the AbstractIndex object corresponding to the key given or throws a KEY-ERROR exception ...
int size()
Returns the number of elements in the contained list.
constructor(string n, string n_type, string n_src)
creates the object from the arguments passed
list getDropList()
returns a list of cached table names in the order that can be used to drop the tables, taking into account foreign constraint dependencies
AbstractTrigger addTrigger(string tname, string src, *hash opt, *reference lsql)
adds a trigger to the table; if the table is already known to be in the database, then it is added in...
const ActionDescMap
maps from action descriptions to action codes
Definition: SqlUtil.qm.dox.h:5110
AbstractTable t
the embedded AbstractTable object that actually provides the functionality for this class ...
Definition: SqlUtil.qm.dox.h:5837
code getUpsertClosureWithValidation(hash example_row, int upsert_strategy=UpsertAuto)
returns a closure that can be executed given a hash argument representing a single row that will be u...
abstract private *string getSqlValueImpl(any v)
returns a string for use in SQL queries representing the DB-specific value of the argument; returns N...
const OP_CLT
the SQL less than (<) operator for use in Where Clauses when comparing two columns ...
Definition: SqlUtil.qm.dox.h:2664
const COP_MINUS
the SQL "minus" operator
Definition: SqlUtil.qm.dox.h:1740
*AbstractFunction getProcedure(string name)
returns an AbstractFunction argument for the given stored procedure name or NOTHING if the stored pro...
clear()
purges the current table definition
constructor(string n, string n_type, string n_src)
creates the object from the arguments passed
clearIndex()
clears any index base for the constraint
string table
the name of the target table
Definition: SqlUtil.qm.dox.h:4227
hash hash(object obj)
commit()
commits the current transaction on the underlying Qore::SQL::AbstractDatasource
abstract private int getNextSequenceValueImpl(string name)
returns the next value in the given sequence
commit()
commits the current transaction on the underlying Qore::SQL::AbstractDatasource
private hash getColumnDescOptions()
returns the column description options for this driver
*AbstractUniqueConstraint findUniqueConstraint(string name)
returns the given AbstractUniqueConstraint object if defined for the table (also includes the primary...
const TableCreationOptions
table creation options
Definition: SqlUtil.qm.dox.h:8458
hash cop_year_day(any column)
returns a hash for the "year_day" operator with the given argument
const AC_Delete
used when data is deleted in a table
Definition: SqlUtil.qm.dox.h:5087
Qore::ListIterator functionIterator()
returns an iterator listing the string function names in the database
list listTables()
returns a list of string table names in the database
*list getCreateMiscSql(*hash opt, bool cache=True)
returns a list of SQL strings that could be used to create other table attributes (such as comments...
abstract string getCreateSql(string table_name, *hash opt)
returns a string that can be used to create the index in the database
const JOP_RIGHT
for right outer joins
Definition: SqlUtil.qm.dox.h:2313
AbstractTrigger dropTrigger(string tname, *reference sql)
drops the given trigger from the table; if the table is known to be in the database already...
hash make_uop(string uop, any arg, *hash nest)
returns a hash with uop, arg, and nest keys
const SZ_OPT
the data type takes an optional size parameter
Definition: SqlUtil.qm.dox.h:1667
const AC_Update
used when data is updated in a table
Definition: SqlUtil.qm.dox.h:5084
int insertFromIteratorNoCommit(Qore::AbstractIterator i, *hash opt)
this method inserts data from the given iterator argument (whose getValue() method must return a hash...
private hash getInsertOperatorMap()
returns the insert operator map for this object
*string getDropConstraintIfExistsSql(string tname, string cname, *hash opts)
returns an SQL string that can be used to drop an existing constraint on a table, if the table is not...
AbstractSequence makeSequence(string name, number start=1, number increment=1, *softnumber end, *hash opts)
creates a database-specific AbstractSequence object corresponding to the arguments ...
hash cop_year(any column)
returns a hash for the "year" operator with the given argument
number start
the starting number
Definition: SqlUtil.qm.dox.h:4285
const IOP_SEQ
for using the value of a sequence
Definition: SqlUtil.qm.dox.h:3090
const AC_NotFound
used when dropping object but the object is not present
Definition: SqlUtil.qm.dox.h:5090
list getDropTriggerSql(string tname, *hash opt)
returns SQL that can be used to drop the given trigger from the table
populate(AbstractDatasource ds, hash tables, *hash opt)
populates the object from a hash description
the base abstract class for the database implementation
Definition: SqlUtil.qm.dox.h:5016
int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *reference sql)
inserts rows into a table based on a select statement from another table (which must be using the sam...
private hash getTableOptions()
returns the table options for this driver
*list getDropTableSqlIfExists(string name, *hash opt)
returns the SQL require to drop the given table if it exists or NOTHING if the named table does not e...
int upsert(hash row, int upsert_strategy=AbstractTable::UpsertAuto)
update or insert the data in the table according to the hash argument; the table must have a unique k...
Qore::AbstractIterator getUniqueConstraintIterator()
returns an iterator for all unique constraints on the table (including the primary key if any) ...
any take(int i)
removes the given element from the contained list and returns the value
hash cop_multiply(any column1, any column2)
returns a hash for the "*" operator with the given arguments
*hash select(*hash sh, *reference sql)
returns a hash of lists representing the columns and rows in the table that match the argument hahs ...
const AC_Add
used when an element is added to an existing object
Definition: SqlUtil.qm.dox.h:5075
truncateNoCommit()
truncates all the table data without any transaction management
string getCreateTableSql(*hash opt)
returns an SQL string that could be used to create the basic table structure without indexes and cons...
string getDropSql(string table_name)
returns a string that can be used to drop the index from the database
string getDropConstraintSql(string cname, *hash opt)
gets the SQL that can be used to drop a constraint from the table; this can be any constraint on the ...
*AbstractForeignConstraint findEqual(AbstractForeignConstraint fk)
find an index with columns equal to the index passed
ForeignConstraints getForeignConstraints(*hash opt)
returns a ForeignConstraints object describing the foreign constraints that the table has on other ta...
represents a primary key
Definition: SqlUtil.qm.dox.h:4164
const JOP_INNER
for standard inner joins
Definition: SqlUtil.qm.dox.h:2303
const COP_LOWER
to return column value in lower case
Definition: SqlUtil.qm.dox.h:1705
*AbstractTable getTable(string name)
returns an AbstractTable argument for the given table name or NOTHING if the table cannot be found ...
private hash getSequenceDescriptionOptions()
override in subclasses to return driver-specific options
private any tryExecArgsImpl(string sql, *softlist args)
tries to execute a command so that if an error occurs the current transaction status is not lost ...
list getDropColumnSql(string cname, *hash opt)
returns the SQL that can be used to drop a column from the table
const COP_DIVIDE
the SQL "divide" operator
Definition: SqlUtil.qm.dox.h:1750
string join(string str,...)
const DB_FUNCTIONS
Features constants.
Definition: SqlUtil.qm.dox.h:1619
const NUMERIC
specifies a numeric column (equivalent to Qore::Type::Number)
Definition: SqlUtil.qm.dox.h:1644
abstract list getCreateSql(*hash opt)
returns a list of SQL strings that can be used to create the function in the database ...
abstract bool setIndexBase(string ix)
returns True if the object supports an index property and is set, False if not
hash op_ceq(string arg)
returns a hash for the "=" operator with the given argument for use in where clauses when comparing t...
*list selectRows(*hash sh, *reference sql)
returns a list of hashes representing the rows in the table that match the argument hash ...
string getDropSql(string table_name)
returns a string that can be used to drop the column from the table
const UpsertStrategyDescriptionMap
hash mapping upsert strategy descriptions to upsert strategy codes
Definition: SqlUtil.qm.dox.h:8630
*string lastKey()
Returns the last key name in the contained hash or NOTHING if the contained hash has no keys...
int delNoCommit(*hash cond, *reference sql)
deletes rows in the table matching the condition and returns the count of rows deleted; no transactio...
private bool equalImpl(AbstractConstraint con)
returns True if the argument is equal to the current object, False if not
AbstractConstraint take(string k)
removes the given key from the contained hash and returns the value
*string firstKey()
Returns the first key name in the contained hash or NOTHING if the contained hash has no keys...
*hash upsertFromIterator(Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given iterator argument (whose getValue() method must ret...
bool supportsSequences()
returns True if the database supports sequences
*hash upsertFromIteratorNoCommit(Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
this method upserts or merges data from the given iterator argument (whose getValue() method must ret...
any tryExec(string sql)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
hash cop_year_hour(any column)
returns a hash for the "year_hour" operator with the given argument
bool hasKeyValue(string k)
Returns True if the key exists in the contained hash and is assigned a value, False if not...
const ConstraintOptions
default constraint options
Definition: SqlUtil.qm.dox.h:8397
*AbstractTable getIfExists(AbstractDatasource ds, string name)
gets a table from the database or from the cache if already cached; if the table does not exist...
const DefaultOpMap
a hash of valid operators for use in Where Clauses
Definition: SqlUtil.qm.dox.h:2707
represents a unique column constraint
Definition: SqlUtil.qm.dox.h:4093
rename(string n)
renames the constraint
private hash getUpsertOptions()
returns the upsert options for this driver
AbstractTable makeTable(string name, hash desc, *hash opts)
creates a database-specific AbstractTable object corresponding to the arguments
const AdditionalColumnDescOptions
additional column description keys valid when describing columns in a table description hash ...
Definition: SqlUtil.qm.dox.h:8526
hash cop_max(any column)
returns a hash for the "max" operator; returns maximum column values
abstract base class for constraints
Definition: SqlUtil.qm.dox.h:4004
string getSqlValue(any v)
returns a string for use in SQL queries representing the DB-specific value of the argument ...
hash uop_seq(string seq)
returns a hash for the "seq" operator with the given argument giving the sequence name whose value sh...
AbstractForeignConstraint removeForeignConstraint(string cname)
removes the named foreign constraint from the table; no SQL is executed in any case, only the named foreign constraint is removed from the table definition
int upsertNoCommit(hash row, int upsert_strategy=UpsertAuto)
update or insert the data in the table according to the hash argument; the table must have a unique k...
constraint container class that throws an exception if an unknown constraint is accessed ...
Definition: SqlUtil.qm.dox.h:3962
const COP_APPEND
to append a string to a column on output
Definition: SqlUtil.qm.dox.h:1690