Qore SqlUtil Module Reference  1.4
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 - 2017 Qore Technologies, s.r.o.
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.13 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  ...
380  @endcode
381  @warning Oracle: using different comments in the same SQL can lead to new optimizer statement hard parsing.
382 
383  @subsubsection select_option_hint Select Option "hint"
384  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).
385 
386  <b>Hint Example:</b>
387  @code{.py}
388 table.selectRows(("hint": "full(t1)"));
389  @endcode
390  will produce select statement like this:
391  @code{.py}
392 select /*+ full(a) */ ...
393  @endcode
394  The string is taken as-is and it's up to the caller to handle correct aliases in join functions etc.
395  @note Hints are platform dependent. Curently only Oracle and some versions of PostgreSQL hints are supported in Sqlutil module.
396  @warning Use hints only when you know what you are doing.
397 
398  @subsubsection select_option_columns Select Option "columns"
399  <b>Columns Example:</b>
400  @code{.py}
401 list columns = (
402  "id", "name", "started",
403  cop_as("warnings", "warning_count"),
404  cop_as("errors", "error_count"),
405 );
406 *list rows = table.selectRows(("columns": columns, "where": ("type": "user")));
407  @endcode
408  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
409  This option takes a list, each element of the list can be one of the following.\n\n
410  <b>A Simple String Giving a Column Name</b>\n
411  ex: \c "name"
412  @code{.py}
413 *list rows = table.selectRows(("columns": ("id", "name", "started")));
414  @endcode \n
415  <b>A String in Dot Notation</b>\n
416  This format is for use with @ref select_option_join "joins"; ex: \c "q.name"
417  @code{.py}
418 hash sh = (
419  "columns": ("t1.id", "t2.customer_name"),
420  "join": join_inner(table2, "t2", ("id": "altid"))),
421  "alias": "t1",
422 );
423 *list rows = table.selectRows(sh);
424  @endcode \n
425  <b>A Column Operation Specified by a Column Operator Function</b>\n
426  ex: <tt>cop_as("column_name", "column_alias")</tt> \n
427  See @ref sql_cop_funcs "column operator function" for more information on column operator functions
428  @code{.py}
429 list columns = (
430  "id",
431  cop_as("warnings", "warning_count"),
432  cop_as("errors", "error_count"),
433 );
434 *list rows = table.selectRows(("columns": columns));
435  @endcode
436  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
437  <b>The Value \c "*", Meaning All Columns</b>\n
438  ex: \c "*"
439  @code{.py}
440 *list rows = table.selectRows(("columns": "*"));
441  @endcode
442  This is the default if no \c "columns" key is included in the @ref select_option_hash "select option hash" \n\n
443  <b>An \c "*" in Dot Notation</b>\n
444  ex: \c "q.*"
445  @code{.py}
446 hash sh = (
447  "columns": ("table.id", "t2.*"),
448  "join": join_inner(table2, "t2", ("id": "altid")),
449 );
450 *list rows = table.selectRows(sh);
451  @endcode
452 
453  @subsubsection select_option_where Select Option "where"
454  <b>Where Example:</b>
455  @code{.py}
456 *list rows = table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
457  @endcode
458  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.
459 
460  @subsubsection select_option_orderby Select Option "orderby"
461  <b>Orderby Example:</b>
462  @code{.py}
463 hash sh = (
464  "where": (
465  "account_type": "CUSTOMER",
466  ),
467  "orderby": "created_date",
468 );
469 *list rows = table.selectRows(sh);
470  @endcode
471  This option is a list of the following values:
472  - a simple string giving a column name; ex: \c "name"
473  - a simple string with a column name preceded by a \c "-" sign; ex: \c "-name", meaning that that column should be sorted in descending order
474  - 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"
475  - a positive integer giving the column number for the ordering
476  @note
477  - By using the @ref select_option_offset "offset option" the results will be automatically ordered according to the primary key of the table
478 
479  @subsubsection select_option_desc Select Option "desc"
480  <b>Desc Example:</b>
481  @code{.py}
482 hash sh = (
483  "where": (
484  "account_type": "CUSTOMER",
485  ),
486  "orderby": "created_date",
487  "desc": True,
488 );
489 *list rows = table.selectRows(sh);
490  @endcode
491  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
492  Otherwise, ordered results are returned in ascending order by default.
493 
494  @note per-column descending options can be given by prepending a \c "-" character to the column name in the @ref select_option_orderby "orderby option list"
495 
496  @subsubsection select_option_limit Select Option "limit"
497  <b>Limit Example:</b>
498  @code{.py}
499 hash sh = (
500  "where": ("type": "user"),
501  "limit": 100,
502  "offset": 200
503 );
504 *list rows = table.selectRows(sh);
505  @endcode
506  This option will limit the number of rows returned.
507  @note
508  - This option is required if the @ref select_option_offset "offset option" is non-zero
509  - 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
510 
511  @subsubsection select_option_offset Select Option "offset"
512  <b>Offset Example:</b>
513  @code{.py}
514 hash sh = (
515  "where": ("type": "user"),
516  "limit": 100,
517  "offset": 200
518 );
519 *list rows = table.selectRows(sh);
520  @endcode
521  This option specifies the row number offset for the rows returned where the first row is at offset zero.
522  @note
523  - 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.
524  - Additionally, this option requires the presence of the @ref select_option_limit "limit option", or an exception will be thrown.
525  @see @ref sql_paging
526 
527  @subsubsection select_option_join Select Option "join"
528  <b>Join Example:</b>
529  @code{.py}
530 hash sh = (
531  "columns": (
532  "name", "version", "id",
533  cop_as("st.value", "source"),
534  cop_as("st.value", "offset"),
535  ),
536  "join": join_left(function_instance_tags, "st", NOTHING, ("st.tag": "_source"))
537  + join_left(function_instance_tags, "lt", NOTHING, ("st.tag": "_offset")),
538 );
539 *list rows = table.selectRows(sh);
540  @endcode
541  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).
542  @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
543 
544  @see @ref joins for more examples
545 
546  @subsubsection select_option_groupby Select Option "groupby"
547  <b>Groupby Example:</b>
548  @code{.py}
549 hash sh = (
550  "columns": (
551  cop_as(cop_max("service_type"), "type"),
552  cop_count(),
553  ),
554  "groupby": "service_type",
555 );
556 *list rows = table.selectRows(sh);
557  @endcode
558  The \c "groupby" option allows for aggregate SQL column operator functions to be used (ex: @ref cop_max(), cop_min()) in select statements.
559  The \c "groupby" hash key should be assigned to a list of column specifiers or a single column specifier. Column specifiers for the \c "groupby"
560  key are strings giving column names, optionally in dot notation or positive column numbers.
561 
562  @subsubsection select_option_having Select Option "having"
563  <b>Having Example:</b>
564  @code{.py}
565 hash sh = (
566  "columns": (
567  cop_as(cop_max("service_type"), "type"),
568  cop_count(),
569  ),
570  "groupby": "service_type",
571  "having": (
572  "service_type": (COP_COUNT, op_ge(100)),
573  ),
574 );
575 *list rows = table.selectRows(sh);
576  @endcode
577  The \c "having" option allows for query results with aggregate SQL column operator functions to be filtered by user-defined criteria.
578  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.
579 
580  @subsubsection select_option_superquery Select Option "superquery"
581  <b>Superquery Example:</b>
582  @code{.py}
583 hash sh = (
584  "columns": (
585  "serviceid", "service_methodid",
586  cop_as(cop_over(cop_max("service_methodid"), "serviceid"), "max_methodid"),
587  ),
588  "superquery": (
589  "columns": ("serviceid", "service_methodid"),
590  "where": ("max_methodid": op_ceq("service_methodid")),
591  ),
592 );
593 *list rows = table.selectRows(sh);
594  @endcode
595  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
596  The above example results in an SQL command equivalent to the following:
597  @code{.py}
598 *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");
599  @endcode
600  @note that MySQL does not support SQL windowing functions so the above example would fail on MySQL.
601 
602  @subsubsection select_option_forupdate Select Option "forupdate"
603  <b>For Update Example:</b>
604  @code{.py}
605 on_success ds.commit();
606 on_error ds.rollback();
607 
608 hash sh = (
609  "columns": ("serviceid", "service_methodid"),
610  "forupdate": True,
611 )
612 *list rows = table.selectRows(sh);
613  @endcode
614  \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.
615  The above example results in an SQL commit equivalent to the following:
616  @code{.py}
617 *list rows = table.vselectRows("select serviceid,service_methodid from schema.service_methods for update");
618  @endcode
619 
620  @subsection sql_paging Select With Paging
621 
622  There is support for paging query results in the following methods:
623  - @ref SqlUtil::AbstractTable::getRowIterator()
624  - @ref SqlUtil::AbstractTable::getSelectSql()
625  - @ref SqlUtil::AbstractTable::select()
626  - @ref SqlUtil::AbstractTable::selectRows()
627 
628  @note the above list also applies to the corresponding @ref SqlUtil::AbstractTable methods
629 
630  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.
631 
632  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.
633 
634  @par Example:
635  Select 100 rows starting at row 200 (the table's primary key will be used for the \c "orderby" option by default): \n
636  @code{.py}
637 *list rows = table.selectRows(("where": ("type": "user"), "limit": 100, "offset": 200));
638  @endcode
639  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:
640  @code{.py}
641 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));
642  @endcode
643  And for PostgreSQL:
644  @code{.py}
645 ds.vselectRows("select * from public.table where type = %v order by type limit %v offset %v", ("user", 100, 200));
646  @endcode
647 
648  @subsection check_matching_rows Check For At Least One Matching Row
649 
650  Use the @ref SqlUtil::AbstractTable::findSingle() method to find at least one matching row:
651  @code{.py}
652 *hash h = table.findSingle(("account_type": "CUSTOMER"));
653 if (h)
654  printf("found 1 customer row: %y\n", l[0]);
655  @endcode
656 
657  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):
658  @code{.py}
659 *hash h = table.selectRow(("where": ("account_type": "CUSTOMER"), "limit": 1));
660 if (h)
661  printf("found 1 customer row: %y\n", l[0]);
662  @endcode
663 
664  @section inserting_data Inserting Data into the Database
665 
666  The following methods can be used to insert data into the database:
667  - @ref SqlUtil::AbstractTable::insert(): inserts a single row into a table without committing the transaction
668  - @ref SqlUtil::AbstractTable::insertCommit(): inserts a single row into a table and commits the transaction
669  - @ref SqlUtil::AbstractTable::insertFromSelect(): 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
670  - @ref SqlUtil::AbstractTable::insertFromSelectCommit(): 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
671 
672  @see @ref sql_upsert for information about upserting or merging data
673 
674  @subsection inserting_data_explicitly Inserting Data Explicitly
675 
676  @par Example:
677  @code{.py}
678 table.insert(("id": id, "name": name, "created": now_us()));
679  @endcode
680 
681  Data can be explicitly inserted into the database with immediate values with @ref SqlUtil::AbstractTable::insert() and @ref SqlUtil::AbstractTable::insertCommit() as in the above example.
682 
683  Additionally, instead of giving a literal value to be inserted, @ref sql_iop_funcs can be used to insert values based on SQL operations used directly in the insert statement.
684 
685  @subsection inserting_data_from_select Inserting Data From a Select Statement
686 
687  @par Example:
688  @code{.py}
689 int rows = table.insertFromSelect(("id", "name", "created"), source_table, (("columns": ("cid", "fullname", "created"), "where": ("type": "CUSTOMER"))));
690  @endcode
691 
692  Data can be inserted into the database based on the results of a select statement with @ref SqlUtil::AbstractTable::insertFromSelect() and @ref SqlUtil::AbstractTable::insertFromSelectCommit() as in the above example.
693 
694  The example above would generate a %Qore SQL command like the following:
695  @code{.py}
696 return ds.vexec("insert into schema.table (id,name,created) select cid,fullname,created from schema.source_table where type = %v", ("CUSTOMER"));
697  @endcode
698 
699  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.
700 
701  @subsection inserting_data_from_iterator Inserting Data from an Iterator Source
702 
703  To insert data from an iterator source (such as an @ref Qore::SQL::SQLStatement object), call @ref SqlUtil::AbstractTable::insertFromIterator() or @ref SqlUtil::AbstractTable::insertFromIteratorCommit() as in the following example:
704 
705  @par Example:
706  @code{.py}
707 # get the rows to be inserted
708 list l = get_table_rows();
709 # insert the data and commit after every 5000 rows
710 table.insertFromIterator(l.iterator(), ("commit_block": 5000));
711  @endcode
712 
713  The iterator given to the @ref SqlUtil::AbstractTable::insertFromIterator() or @ref SqlUtil::AbstractTable::insertFromIteratorCommit() methods can be any iterator whose @ref Qore::AbstractIterator::getValue() "getValue()" method returns a @ref hash_type "hash".
714 
715  @note the @ref SqlUtil::AbstractTable::InsertFromIteratorOptions "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
716 
717  @section updating_data Updating Data
718 
719  The following methods can be used to update data:
720  - @ref SqlUtil::AbstractTable::update(): updates a single row and does not commit the transaction
721  - @ref SqlUtil::AbstractTable::updateCommit(): updates a single row and commits the transaction
722 
723  @par Example:
724  @code{.py}
725 int rows_updated = t.update(("permission_type": uop_append("-migrated", uop_lower())));
726  @endcode
727 
728  The example above generates a %Qore SQL command like the following on Oracle and PostgreSQL for example:
729  @code{.py}
730 return ds.vexec("update schema.table set permission_type = lower(permission_type) || '-migrated');
731  @endcode
732  And the following on MySQL:
733  @code{.py}
734 return ds.vexec("update schema.table set permission_type = concat(lower(permission_type), '-migrated'));
735  @endcode
736 
737  @section deleting_data Deleting Data
738 
739  The following methods can be used to dekete data:
740  - @ref SqlUtil::AbstractTable::del(): updates the table based on a @ref where_clauses "where clause" and does not commit the transaction
741  - @ref SqlUtil::AbstractTable::delCommit(): updates the table based on a @ref where_clauses "where clause" and commits the transaction
742  - @ref SqlUtil::AbstractTable::truncate(): truncates the table and does not commit the transaction
743  - @ref SqlUtil::AbstractTable::truncateCommit(): truncates the table and commits the transaction releasing the transaction lock on the underlying datasource object
744 
745  @par Example:
746  @code{.py}
747 int dcnt = table.del(("record_type": "OLD-CUSTOMER"));
748  @endcode
749 
750  The above example would generate a %Qore SQL command like the following:
751  @code{.py}
752 return ds.vexec("delete from schema.table where record_type = %v", ("OLD-CUSTOMER"));
753  @endcode
754 
755  The @ref SqlUtil::AbstractTable::del() and @ref SqlUtil::AbstractTable::delCommit() methods can be used to delete data from the database.
756 
757  See @ref where_clauses for information about specifying the criteria for the rows to be deleted.
758 
759  @section joins Joining Tables
760 
761  Joining tables is made by providing a join specification to the @ref select_option_join "join select option" in
762  a @ref select_option_hash "select option hash" as in the following example:
763  @code{.py}
764 *list rows = table.selectRows(("columns": ("table.id", "t2.customer_name"), "join": join_inner(table2, "t2", ("id": "altid"))));
765  @endcode
766  In the above example, \a table is joined with \a table2 on <tt>table.id = table2.altid</tt>.
767 
768  Joins on multiple tables are performed by combining the results of @ref sql_jop_funcs "join functions" with the @ref plus_operator "+ operator"
769  as follows:
770  @code{.py}
771 *list rows = table.selectRows(("join": join_inner(table2, "t2", ("id": "altid")) + join_inner(table3, "t3")));
772  @endcode
773  In the above example, \a table is joined with \a table2 on <tt>table.id = table2.altid</tt> and with \a table3 on an
774  automatically detected primary key to foreign key relationship between the two tables.
775 
776  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
777  argument to the @ref sql_jop_funcs "join function" as in the following example:
778  @code{.py}
779 *list rows = table.selectRows(("join": join_inner(table2, "t2", ("id": "altid")) + join_inner("t2", table3, "t3")));
780  @endcode
781  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
782  with \a table3 (aliased as \c t3) on an automatically detected primary key to foreign key relationship between the two tables.
783 
784  @see @ref select_option_join "join select option"
785 
786  @section where_clauses Where Clauses
787 
788  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:
804 
805  @note the above list also applies to the corresponding @ref SqlUtil::AbstractTable methods
806 
807  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", there are two options:
808  - use the @ref SqlUtil::wop_or() function to combine where expressions with the \c "or" operator
809  - 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".
810 
811  The where condition hash has the following format:
812  - each key gives a column name or a table/alias with column name in dot notation
813  - 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
814 
815  @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{.py} hash w = ("0:created": op_ge(mindate), "1:created": op_lt(maxdate)); @endcode
816 
817  See @ref sql_op_funcs for a list of operator functions.
818 
819  @par Where Hash Example:
820  @code{.py}
821 hash w = (
822  "name": "Smith",
823  "account_type": op_like("%CUSTOMER%"),
824  "id": op_ge(500),
825 );
826  @endcode \n
827  The preceding example results in a where clause equivalent to: \c "name = 'Smith' and type like '%CUSTOMER%' and id >= 500", except
828  that bind by value is used, so, if used in a context like the following:
829  @code{.py}
830 Table t(ds, "table");
831 *hash qh = t.select(("where": w));
832  @endcode \n
833  the complete query would look instead as follows:
834  @code{.py}
835 ds.vselect("select * from table where name = %v and account_type like %v and id >= %v", ("Smith", "%CUSTOMER%", 500));
836  @endcode
837 
838  @anchor where_list
839  @par Where List Example:
840  @code{.py}
841 hash w1 = (
842  "name": "Smith",
843  "account_type": op_like("%CUSTOMER%"),
844  "id": op_ge(500),
845 );
846 hash w2 = (
847  "name": "Jones",
848  "account_type": op_like("%VENDOR%"),
849  "id": op_ge(2500),
850 );
851 Table t(ds, "table");
852 *hash qh = t.select(("where": (w1, w2)));
853  @endcode \n
854  the complete query would look instead as follows:
855  @code{.py}
856 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));
857  @endcode
858 
859  @par Code Examples:
860  Find a single row in the table where the \c "permission_type" column is a value between \c "US" and \c "UX":\n
861  @code{.py}
862 *hash row = table.findSingle(("permission_type": op_between("US", "UX")));
863  @endcode
864  resulting in an internal SQL command that looks as follows (depending on the database):
865  @code{.py}
866 *hash row = ds.vselectRow("select * from table where permission_type between %v and %v limit %v", ("US", "UX", 1));
867  @endcode \n
868  Delete all rows in the table where the \c "name" column is like \c "%Smith%":\n
869  @code{.py}
870 int row_count = table.del(("name": op_like("%Smith%")));
871  @endcode
872  resulting in an internal SQL command that looks as follows:
873  @code{.py}
874 ds.vexec("delete from table where name like %v", ("%Smith%"));
875  @endcode \n
876  Find all rows where \c "id" is greater than \c 100 and \c "created" is after \c 2013-03-01:\n
877  @code{.py}
878 *list rows = table.findAll(("id": op_gt(100), "created": op_gt(2013-03-01)));
879  @endcode
880  resulting in an internal SQL command that looks as follows:
881  @code{.py}
882 ds.vexec("select * from table where id > %v and created > %v", (100, 2013-03-01));
883  @endcode \n
884 
885  @section sql_upsert Upserting or Merging Data
886 
887  This module offers a high-level api for "upserting" or merging data from one table into another table through the following methods:
896 
897  @subsection sql_upsert_single Upsert a Single Row
898 
899  @par Example:
900  @code{.py}
901 table.upsert(("id": id, "name": name, "account_type": account_type));
902  @endcode
903 
904  To upsert or merge a single row in the database, call @ref SqlUtil::AbstractTable::upsert() or @ref SqlUtil::AbstractTable::upsertCommit() with the
905  single row to be upserted or merged as a hash as in the preceding example.
906 
907  @subsection sql_upsert_many Upserting Many Rows Using An Upsert Closure
908 
909  To upsert or merge many rows by using an upsert closure, call @ref SqlUtil::AbstractTable::getUpsertClosure() or @ref SqlUtil::AbstractTable::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.
910 
911  @par Simple Example:
912  @code{.py}
913 # get the rows to be inserted
914 list l = get_table_rows();
915 
916 if (l) {
917  code upsert = table.getUpsertClosure(l[0]);
918 
919  on_success ds.commit();
920  on_error ds.rollback();
921 
922  # loop through the reference data rows
923  map upsert($1), l;
924 }
925  @endcode
926 
927  @par Complex Example With Callbacks:
928  @code{.py}
929 # set the upsert strategy depending on the use case
930 int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
931 
932 # hash summarizing changes
933 hash sh;
934 
935 # get the rows to be inserted
936 list l = get_table_rows();
937 
938 if (l) {
939  # get the upsert closure to use based on the first row to be inserted
940  code upsert = table.getUpsertClosure(l[0], upsert_strategy);
941 
942  on_success ds.commit();
943  on_error ds.rollback();
944 
945  # loop through the reference data rows
946  foreach hash h in (l) {
947  int code = upsert(h);
948  if (code == AbstractTable::UR_Unchanged)
949  continue;
950 
951  string change = AbstractTable::UpsertResultMap{code};
952  ++sh{change};
953 
954  if (!verbose) {
955  printf(".");
956  flush();
957  }
958  else if (verbose > 1)
959  printf("*** reference data %s: %y: %s\n", table.getName(), h, change);
960  }
961 
962  # show table summary
963  if (sh)
964  printf("*** reference data %s: %s\n", table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), sh.pairIterator())));
965  else
966  printf("*** reference data %s: OK\n", table.getName());
967 }
968  @endcode
969 
970  @subsection sql_upsert_from_iterator Upserting Many Rows from an Iterator Source
971 
972  To upsert or merge many rows from an iterator source (such as an @ref Qore::SQL::SQLStatement object), call @ref SqlUtil::AbstractTable::upsertFromIterator() or @ref SqlUtil::AbstractTable::upsertFromIteratorCommit() as in the following example:
973 
974  @par Simple Example:
975  @code{.py}
976 # get the rows to be inserted
977 list l = get_table_rows();
978 table.upsertFromIterator(l.iterator());
979  @endcode
980 
981  @par Complex Example With Callbacks:
982  @code{.py}
983 # set the upsert strategy depending on the use case
984 int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
985 
986 # get the rows to be inserted
987 list l = get_table_rows();
988 
989 code callback = sub (string table_name, hash row, int result) {
990  if (result == AbstractTable::UR_Unchanged)
991  return;
992  string change = AbstractTable::UpsertResultMap{result};
993  if (verbose)
994  printf("*** reference data %s: %y: %s\n", table_name, row, change);
995 };
996 
997 hash sh = table.upsertFromIterator(l.iterator(), upsert_strategy, False, ("info_callback": callback, "commit_block": 5000));
998 if (sh)
999  printf("*** reference data %s: %s\n", table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), sh.pairIterator())));
1000 else
1001  printf("*** reference data %s: OK\n", table.getName());
1002  @endcode
1003 
1004  The iterator given to the @ref SqlUtil::AbstractTable::upsertFromIterator() or @ref SqlUtil::AbstractTable::upsertFromIteratorCommit() methods can be any iterator whose @ref Qore::AbstractIterator::getValue() "getValue()" method returns a @ref hash_type "hash".
1005 
1006  @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
1007 
1008  @subsection sql_upsert_from_select Upserting Many Rows from a Select Statement
1009 
1010  To upsert or merge many rows from a select statement, use @ref SqlUtil::AbstractTable::upsertFromSelect() or @ref SqlUtil::AbstractTable::upsertFromSelectCommit() as in the following example:
1011 
1012  @par Simple Example:
1013  @code{.py}
1014 table.upsertFromSelect(table2, ("where": ("account_type": "CUSTOMER")));
1015  @endcode
1016 
1017  @par Complex Example With Callbacks:
1018  @code{.py}
1019 # set the upsert strategy depending on the use case
1020 int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
1021 
1022 code callback = sub (string table_name, hash row, int result) {
1023  if (result == AbstractTable::UR_Unchanged)
1024  return;
1025  string change = AbstractTable::UpsertResultMap{result};
1026  if (verbose)
1027  printf("*** reference data %s: %y: %s\n", table_name, row, change);
1028 };
1029 
1030 hash sh = table.upsertFromSelect(table2, ("where": ("account_type": "CUSTOMER")), upsert_strategy, False, ("info_callback": callback, "commit_block": 5000));
1031 if (sh)
1032  printf("*** reference data %s: %s\n", table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), sh.pairIterator())));
1033 else
1034  printf("*** reference data %s: OK\n", table.getName());
1035  @endcode
1036 
1037  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).
1038 
1039  @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
1040 
1041  @subsection sql_upsert_with_delete Upserting Many Rows and Deleting Unwanted Rows
1042 
1043  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.
1044 
1045  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.
1046 
1047  @par Simple Example:
1048  @code{.py}
1049 # get the rows to be inserted
1050 list l = get_table_rows();
1051 table.upsertFromSelect(table2, ("where": ("account_type": "CUSTOMER")), AbstractTable::UpsertAuto, ("delete_others": True, "commit_block": 5000));
1052  @endcode
1053 
1054  @par Complex Example With Callbacks:
1055  @code{.py}
1056 # set the upsert strategy depending on the use case
1057 int upsert_strategy = verbose ? AbstractTable::UpsertSelectFirst : AbstractTable::UpsertAuto;
1058 
1059 # get the rows to be inserted
1060 list l = get_table_rows();
1061 
1062 code callback = sub (string table_name, hash row, int result) {
1063  if (result == AbstractTable::UR_Unchanged)
1064  return;
1065  string change = AbstractTable::UpsertResultMap{result};
1066  if (verbose)
1067  printf("*** reference data %s: %y: %s\n", table_name, row, change);
1068 };
1069 
1070 hash sh = table.upsertFromSelect(table2, ("where": ("account_type": "CUSTOMER")), upsert_strategy, ("delete_others": True, "info_callback": callback, "commit_block": 5000));
1071 if (sh)
1072  printf("*** reference data %s: %s\n", table.getName(), (foldl $1 + ", " + $2, (map sprintf("%s: %d", $1.key, $1.value), sh.pairIterator())));
1073 else
1074  printf("*** reference data %s: OK\n", table.getName());
1075  @endcode
1076 
1077  @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
1078 
1079  @subsection sql_upsert_strategies Upsert Strategies
1080  The approach used is based on one of the following strategies (see @ref upsert_options):
1081  - @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; note that if a driver-specific optimized version of the upsert operation is implemented, this strategy will normally result in the best performance
1082  - @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
1083  - @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
1084  - @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
1085  - @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
1086  - @ref SqlUtil::AbstractTable::UpsertUpdateOnly "AbstractTable::UpsertUpdateOnly": update if the row exists, otherwise do nothing and @ref upsert_results "upsert result" @ref SqlUtil::AbstractTable::UR_Unchanged is returned
1087 
1088  @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.
1089 */
2072 namespace SqlUtil {
2075  public struct GenericColumnInfo {
2077  string qore_type;
2079  string native_type;
2081  softint size;
2083  softint scale;
2087  bool default_value_native = False;
2089  *string comment;
2091  bool notnull = False;
2093  hash<string, hash> driver;
2096  };
2097 
2099  public struct OperatorInfo {
2100  string op;
2101  any arg;
2102  };
2103 
2105  public struct ColumnOperatorInfo {
2106  string cop;
2107  any column;
2108  any arg;
2109  };
2110 
2112  public struct InsertOperatorInfo {
2113  string _iop;
2114  any arg;
2115  };
2116 
2118  public struct UpdateOperatorInfo {
2119  string uop;
2120  any arg;
2122  };
2123 
2125  public struct JoinOperatorInfo {
2126  string jop;
2127  any table;
2128  *string alias;
2131  *string ta;
2133  };
2134 
2135  /* @defgroup DBFeaturesConstants DB Features Constants
2136  These constants can be used as a lookup values in AbstractDatabase::features() method.
2137  */
2139 
2142  const DB_FUNCTIONS = "functions";
2144  const DB_MVIEWS = "materialized views";
2146  const DB_PACKAGES = "packages";
2148  const DB_PROCEDURES = "procedures";
2150  const DB_SEQUENCES = "sequences";
2152  const DB_TABLES = "tables";
2154  const DB_TYPES = "named types";
2156  const DB_VIEWS = "views";
2158  const DB_SYNONYMS = "synonyms";
2160 
2161  /* @defgroup SqlTypeConstants SQL Type Constants
2162  These constants can be used for the \c "qore_type" values when creating columns to specify additional SQL column types
2163  */
2165  const VARCHAR = "string";
2167 
2169  const NUMERIC = "number";
2170 
2172  const CHAR = "char";
2173 
2175  const BLOB = "blob";
2176 
2178  const CLOB = "clob";
2180 
2185  const SZ_NONE = 0;
2187 
2189  const SZ_MAND = 1;
2190 
2192  const SZ_OPT = 2;
2193 
2195  const SZ_NUM = 3;
2197 
2202 
2205  const COP_AS = "as";
2206 
2208 
2210  const COP_CAST = "cast";
2211 
2213 
2215  const COP_PREPEND = "prepend";
2216 
2218 
2220  const COP_APPEND = "append";
2221 
2223 
2225  const COP_VALUE = "value";
2226 
2228 
2230  const COP_UPPER = "upper";
2231 
2233 
2235  const COP_LOWER = "lower";
2236 
2238 
2240  const COP_DISTINCT = "distinct";
2241 
2243 
2245  const COP_MIN = "min";
2246 
2248 
2250  const COP_MAX = "max";
2251 
2253 
2255  const COP_AVG = "avg";
2256 
2258 
2260  const COP_SUM = "sum";
2261 
2263 
2265  const COP_COUNT = "count";
2266 
2268 
2270  const COP_OVER = "over";
2271 
2273 
2275  const COP_MINUS = "minus";
2276 
2278 
2280  const COP_PLUS = "plus";
2281 
2283 
2285  const COP_DIVIDE = "divide";
2286 
2288 
2290  const COP_MULTIPLY = "multiply";
2291 
2293 
2295  const COP_YEAR = "year";
2296 
2298 
2300  const COP_YEAR_MONTH = "year_month";
2301 
2303 
2305  const COP_YEAR_DAY = "year_day";
2306 
2308 
2310  const COP_YEAR_HOUR = "year_hour";
2311 
2313 
2315  const COP_SEQ = "seq";
2316 
2318 
2320  const COP_SEQ_CURRVAL = "seq_currval";
2321 
2323 
2325  const COP_COALESCE = "coalesce";
2326 
2328 
2330  const COP_SUBSTR = "substr";
2331 
2333 
2337  const COP_LENGTH = "length";
2338 
2340 
2346  const COP_TRUNC_DATE = "truncate_date";
2347 
2349 
2353  const COP_CUME_DIST = "cume_dist";
2354 
2356 
2360  const COP_DENSE_RANK = "dense_rank";
2361 
2363 
2367  const COP_FIRST_VALUE = "first_value";
2368 
2370 
2374  const COP_LAST_VALUE = "last_value";
2375 
2377 
2381  const COP_NTILE = "ntile";
2382 
2384 
2388  const COP_PERCENT_RANK = "percent_rank";
2389 
2391 
2395  const COP_RANK = "rank";
2396 
2398 
2402  const COP_ROW_NUMBER = "row_number";
2403 
2404 
2406  const DefaultCopMap = (
2407  COP_AS: (
2408  "arg": Type::String,
2409  "withalias": True,
2410  "code": string (string cve, string arg, reference<hash> psch) {
2411  psch{arg} = cve;
2412  return sprintf("%s as %s", cve, arg);
2413  },
2414  ),
2415  COP_PREPEND: (
2416  "arg": Type::String,
2417  "sqlvalue": True,
2418  "code": string (string cve, string arg) {
2419  return sprintf("%s || %s", arg, cve);
2420  },
2421  ),
2422  COP_APPEND: (
2423  "arg": Type::String,
2424  "sqlvalue": True,
2425  "code": string (string cve, string arg) {
2426  return sprintf("%s || %s", cve, arg);
2427  },
2428  ),
2429  COP_VALUE: (
2430  "sqlvalue": True,
2431  "nocolumn": True,
2432  "code": string (*string cve, auto arg) {
2433  return arg;
2434  },
2435  ),
2436  COP_UPPER: (
2437  "code": string (string cve, auto arg) {
2438  return sprintf("upper(%s)", cve);
2439  },
2440  ),
2441  COP_LOWER: (
2442  "code": string (string cve, auto arg) {
2443  return sprintf("lower(%s)", cve);
2444  },
2445  ),
2446  COP_DISTINCT: (
2447  "code": string (string cve, auto arg) {
2448  return sprintf("distinct %s", cve);
2449  },
2450  ),
2451  COP_MIN: (
2452  "code": string (string cve, auto arg) {
2453  return sprintf("min(%s)", cve);
2454  },
2455  "group": True,
2456  ),
2457  COP_MAX: (
2458  "code": string (string cve, auto arg) {
2459  return sprintf("max(%s)", cve);
2460  },
2461  "group": True,
2462  ),
2463  COP_AVG: (
2464  "code": string (string cve, auto arg) {
2465  return sprintf("avg(%s)", cve);
2466  },
2467  "group": True,
2468  ),
2469  COP_SUM: (
2470  "code": string (string cve, auto arg) {
2471  return sprintf("sum(%s)", cve);
2472  },
2473  "group": True,
2474  ),
2475  COP_COUNT: (
2476  "nocolumn": True,
2477  "code": string (*string cve, auto arg) {
2478  return sprintf("count(%s)", cve ? cve : "*");
2479  },
2480  ),
2481  COP_MINUS: (
2482  "argcolumn": True,
2483  "code": string (string arg1, string arg2) {
2484  return sprintf("%s - %s", arg1, arg2);
2485  },
2486  ),
2487  COP_PLUS: (
2488  "argcolumn": True,
2489  "code": string (string arg1, string arg2) {
2490  return sprintf("%s + %s", arg1, arg2);
2491  },
2492  ),
2493  COP_DIVIDE: (
2494  "argcolumn": True,
2495  "code": string (string arg1, string arg2) {
2496  return sprintf("%s / %s", arg1, arg2);
2497  },
2498  ),
2499  COP_MULTIPLY: (
2500  "argcolumn": True,
2501  "code": string (string arg1, string arg2) {
2502  return sprintf("%s * %s", arg1, arg2);
2503  },
2504  ),
2505  COP_SEQ: (
2506  "nocolumn": True,
2507  "code": string (*string cve, hash arg) {
2508  throw "SEQUENCE-ERROR", sprintf("cannot select sequence %y because this database does not support sequences", arg.seq);
2509  }
2510  ),
2511  COP_SEQ_CURRVAL: (
2512  "nocolumn": True,
2513  "code": string (*string cve, hash arg) {
2514  throw "SEQUENCE-ERROR", sprintf("cannot select the current value of sequence %y because this database does not support sequences", arg.seq);
2515  }
2516  ),
2517  COP_COALESCE: (
2518  "columnargs": True,
2519  "code": string (*string cve, hash arg) {
2520  return sprintf("coalesce(%s)", (foldl $1 + "," + $2, arg.args));
2521  }
2522  ),
2523  COP_SUBSTR: (
2524  "code": string (string cve, list args) {
2525  if (!exists args[1])
2526  return sprintf("substring(%s from %d)", cve, args[0]);
2527  return sprintf("substring(%s from %d for %d)", cve, args[0], args[1]);
2528  },
2529  ),
2530  COP_LENGTH: (
2531  "code": string (string cve, auto arg) {
2532  return sprintf("length(%s)", cve);
2533  },
2534  ),
2535  COP_OVER: (
2536  "columnargs" : True,
2537  "columnargs_ignore_nothings" : True,
2538  "code": string (*string cve, hash args)
2539  {
2540  *string partitionby = args.args[0];
2541  *string orderby = args.args[1];
2542  if (!exists partitionby && exists orderby);
2543 
2544  string sql = cve + " over (";
2545  if (exists partitionby)
2546  sql += sprintf("partition by %s", partitionby); // TODO/FIXME: sql injection!
2547  if (exists orderby)
2548  sql += sprintf(" order by %s", orderby); // TODO/FIXME: sql injection!
2549  sql += ")";
2550  return sql;
2551  },
2552  ),
2553  COP_CUME_DIST: (
2554  "nocolumn": True,
2555  "code": string (*string cve, any arg) {
2556  return "cume_dist()";
2557  },
2558  ),
2559  COP_DENSE_RANK: (
2560  "nocolumn": True,
2561  "code": string (*string cve, any arg) {
2562  return "dense_rank()";
2563  },
2564  ),
2565  COP_FIRST_VALUE: (
2566  "code": string (string cve) {
2567  return sprintf("first_value(%s)", cve);
2568  },
2569  ),
2570  COP_LAST_VALUE: (
2571  "code": string (string cve) {
2572  return sprintf("last_value(%s)", cve);
2573  },
2574  ),
2575  COP_NTILE: (
2576  "sqlvalue": True,
2577  "nocolumn": True,
2578  "code": string (*string cve, any arg) {
2579  return sprintf("ntile(%d)", arg);
2580  },
2581  ),
2582  COP_PERCENT_RANK: (
2583  "nocolumn": True,
2584  "code": string (*string cve, any arg) {
2585  return "percent_rank()";
2586  },
2587  ),
2588  COP_RANK: (
2589  "nocolumn": True,
2590  "code": string (*string cve, any arg) {
2591  return "rank()";
2592  },
2593  ),
2594  COP_ROW_NUMBER: (
2595  "nocolumn": True,
2596  "code": string (*string cve, any arg) {
2597  return "row_number()";
2598  },
2599  ),
2600  );
2602 
2644 
2653  hash<ColumnOperatorInfo> make_cop(string cop, auto column, auto arg);
2654 
2655 
2657 
2669  hash<ColumnOperatorInfo> cop_as(auto column, string arg);
2670 
2671 
2673 
2687  hash<ColumnOperatorInfo> cop_cast(auto column, string arg, auto arg1, auto arg2);
2688 
2689 
2691 
2701  hash<ColumnOperatorInfo> cop_prepend(auto column, string arg);
2702 
2703 
2705 
2715  hash<ColumnOperatorInfo> cop_append(auto column, string arg);
2716 
2717 
2719 
2847  hash<ColumnOperatorInfo> cop_value(auto arg);
2848 
2849 
2851 
2860  hash<ColumnOperatorInfo> cop_upper(auto column);
2861 
2862 
2864 
2873  hash<ColumnOperatorInfo> cop_lower(auto column);
2874 
2875 
2877 
2886  hash<ColumnOperatorInfo> cop_distinct(auto column);
2887 
2888 
2890 
2899  hash<ColumnOperatorInfo> cop_min(auto column);
2900 
2901 
2903 
2912  hash<ColumnOperatorInfo> cop_max(auto column);
2913 
2914 
2916 
2925  hash<ColumnOperatorInfo> cop_avg(auto column);
2926 
2927 
2929 
2938  hash<ColumnOperatorInfo> cop_sum(auto column);
2939 
2940 
2942 
2949  hash<ColumnOperatorInfo> cop_count(auto column = "");
2950 
2951 
2953 
2960  hash<ColumnOperatorInfo> cop_over(auto column, *string partitionby, *string orderby);
2961 
2962 
2964 
2974  hash<ColumnOperatorInfo> cop_minus(auto column1, auto column2);
2975 
2976 
2978 
2988  hash<ColumnOperatorInfo> cop_plus(auto column1, auto column2);
2989 
2990 
2992 
3002  hash<ColumnOperatorInfo> cop_divide(auto column1, auto column2);
3003 
3004 
3006 
3016  hash<ColumnOperatorInfo> cop_multiply(auto column1, auto column2);
3017 
3018 
3020 
3029  hash<ColumnOperatorInfo> cop_year(auto column);
3030 
3031 
3033 
3042  hash<ColumnOperatorInfo> cop_year_month(auto column);
3043 
3044 
3046 
3055  hash<ColumnOperatorInfo> cop_year_day(auto column);
3056 
3057 
3059 
3068  hash<ColumnOperatorInfo> cop_year_hour(auto column);
3069 
3070 
3072 
3082  hash<ColumnOperatorInfo> cop_seq(string seq, *string as);
3083 
3084 
3086 
3096  hash<ColumnOperatorInfo> cop_seq_currval(string seq, *string as);
3097 
3098 
3100 
3112  hash<ColumnOperatorInfo> cop_coalesce(auto col1, auto col2);
3113 
3114 
3116 
3127  hash<ColumnOperatorInfo> cop_substr(auto column, int start, *int count);
3128 
3129 
3131 
3142  hash<ColumnOperatorInfo> cop_length(auto column);
3143 
3144 
3146 
3160  hash<ColumnOperatorInfo> cop_trunc_date(auto column, string mask);
3161 
3162 
3163 
3165 
3193  hash<ColumnOperatorInfo> cop_cume_dist();
3194 
3195 
3197 
3225  hash<ColumnOperatorInfo> cop_dense_rank();
3226 
3227 
3229 
3257  hash<ColumnOperatorInfo> cop_first_value(any column);
3258 
3259 
3261 
3289  hash<ColumnOperatorInfo> cop_last_value(any column);
3290 
3291 
3293 
3323  hash<ColumnOperatorInfo> cop_ntile(int value);
3324 
3325 
3327 
3355  hash<ColumnOperatorInfo> cop_percent_rank();
3356 
3357 
3359 
3387  hash<ColumnOperatorInfo> cop_rank();
3388 
3389 
3391 
3419  hash<ColumnOperatorInfo> cop_row_number();
3420 
3421 
3423 
3474  const DT_YEAR = "Y";
3476 
3478  const DT_MONTH = "M";
3479 
3481  const DT_DAY = "D";
3482 
3484  const DT_HOUR = "H";
3485 
3487  const DT_MINUTE = "m";
3488 
3490  const DT_SECOND = "S";
3491 
3492  // let's simulate and enum here'
3493  const DT_ALL_VALUES = ( DT_YEAR, DT_MONTH, DT_DAY, DT_HOUR, DT_MINUTE, DT_SECOND );
3495 
3500  const DefaultUopMap = (
3502  COP_PREPEND: True,
3503  COP_APPEND: True,
3504  COP_UPPER: True,
3505  COP_LOWER: True,
3506  COP_MINUS: (
3507  "sqlvalue": True,
3508  "code": string (string cve, auto arg) {
3509  return sprintf("%s - %s", cve, arg);
3510  },
3511  ),
3512  COP_PLUS: (
3513  "sqlvalue": True,
3514  "code": string (string cve, auto arg) {
3515  return sprintf("%s + %s", cve, arg);
3516  },
3517  ),
3518  COP_DIVIDE: (
3519  "sqlvalue": True,
3520  "code": string (string cve, auto arg) {
3521  return sprintf("%s / %s", cve, arg);
3522  },
3523  ),
3524  COP_MULTIPLY: (
3525  "sqlvalue": True,
3526  "code": string (string cve, auto arg) {
3527  return sprintf("%s * %s", cve, arg);
3528  },
3529  ),
3530  COP_SUBSTR: True,
3531  COP_SEQ: (
3532  "nocolumn": True,
3533  "code": string (*string cve, string arg) {
3534  throw "SEQUENCE-ERROR", sprintf("cannot select sequence %y because this database does not support sequences", arg);
3535  }
3536  ),
3537  COP_SEQ_CURRVAL: (
3538  "nocolumn": True,
3539  "code": string (*string cve, string arg) {
3540  throw "SEQUENCE-ERROR", sprintf("cannot select the current value of sequence %y because this database does not support sequences", arg);
3541  }
3542  ),
3543  COP_COALESCE: (
3544  "columnargs": True,
3545  "code": string (*string cve, softlist args) {
3546  return sprintf("coalesce(%s)", (foldl $1 + "," + $2, args));
3547  }
3548  ),
3549  );
3551 
3573 
3582  hash<UpdateOperatorInfo> make_uop(string uop, auto arg, *hash<UpdateOperatorInfo> nest);
3583 
3584 
3586 
3596  hash<UpdateOperatorInfo> uop_prepend(string arg, *hash<UpdateOperatorInfo> nest);
3597 
3598 
3600 
3610  hash<UpdateOperatorInfo> uop_append(string arg, *hash<UpdateOperatorInfo> nest);
3611 
3612 
3614 
3623  hash<UpdateOperatorInfo> uop_upper(*hash<UpdateOperatorInfo> nest);
3624 
3625 
3627 
3636  hash<UpdateOperatorInfo> uop_lower(*hash<UpdateOperatorInfo> nest);
3637 
3638 
3640 
3651  hash<UpdateOperatorInfo> uop_substr(int start, *int count, *hash<UpdateOperatorInfo> nest);
3652 
3653 
3655 
3665  hash<UpdateOperatorInfo> uop_plus(auto arg, *hash<UpdateOperatorInfo> nest);
3666 
3667 
3669 
3679  hash<UpdateOperatorInfo> uop_minus(auto arg, *hash<UpdateOperatorInfo> nest);
3680 
3681 
3683 
3693  hash<UpdateOperatorInfo> uop_multiply(auto arg, *hash<UpdateOperatorInfo> nest);
3694 
3695 
3697 
3707  hash<UpdateOperatorInfo> uop_divide(auto arg, *hash<UpdateOperatorInfo> nest);
3708 
3709 
3711 
3720  hash<UpdateOperatorInfo> uop_seq(string seq);
3721 
3722 
3724 
3733  hash<UpdateOperatorInfo> uop_seq_currval(string seq);
3734 
3736 
3743 
3746  const JOP_INNER = "inner";
3747 
3749 
3751  const JOP_LEFT = "left";
3752 
3754 
3756  const JOP_RIGHT = "right";
3757 
3759  const JopMap = (
3760  JOP_INNER: "inner",
3761  JOP_LEFT: "left outer",
3762  JOP_RIGHT: "right outer",
3763  );
3765 
3775 
3779  hash<string, hash<JoinOperatorInfo>> make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash opt);
3780 
3781 
3783 
3787  hash<string, hash<JoinOperatorInfo>> make_jop(string jop, string table_name, *string alias, *hash jcols, *hash cond, *string ta, *hash opt);
3788 
3789 
3791 
3810  hash<string, hash<JoinOperatorInfo>> join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
3811 
3812 
3814 
3833  hash<string, hash<JoinOperatorInfo>> join_inner(Table table, *string alias, *hash jcols, *hash cond, *hash opt);
3834 
3835 
3837 
3856  hash<string, hash<JoinOperatorInfo>> join_inner(string table_name, *string alias, *hash jcols, *hash cond, *hash opt);
3857 
3858 
3860 
3882  hash<string, hash<JoinOperatorInfo>> join_inner(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
3883 
3884 
3886 
3906  hash<string, hash<JoinOperatorInfo>> join_inner(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt);
3907 
3908 
3910 
3932  hash<string, hash<JoinOperatorInfo>> join_inner_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash opt);
3933 
3934 
3936 
3955  hash<string, hash<JoinOperatorInfo>> join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
3956 
3957 
3959 
3978  hash<string, hash<JoinOperatorInfo>> join_left(Table table, *string alias, *hash jcols, *hash cond, *hash opt);
3979 
3980 
3982 
4003  hash<string, hash<JoinOperatorInfo>> join_left(string table_name, *string alias, *hash jcols, *hash cond, *hash opt);
4004 
4005 
4007 
4027  hash<string, hash<JoinOperatorInfo>> join_left(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
4028 
4029 
4031 
4051  hash<string, hash<JoinOperatorInfo>> join_left(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt);
4052 
4053 
4055 
4077  hash<string, hash<JoinOperatorInfo>> join_left_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash opt);
4078 
4079 
4081 
4100  hash<string, hash<JoinOperatorInfo>> join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
4101 
4102 
4104 
4123  hash<string, hash<JoinOperatorInfo>> join_right(Table table, *string alias, *hash jcols, *hash cond, *hash opt);
4124 
4125 
4127 
4148  hash<string, hash<JoinOperatorInfo>> join_right(string table_name, *string alias, *hash jcols, *hash cond, *hash opt);
4149 
4150 
4152 
4172  hash<string, hash<JoinOperatorInfo>> join_right(string ta, AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt);
4173 
4174 
4176 
4196  hash<string, hash<JoinOperatorInfo>> join_right(string ta, Table table, *string alias, *hash jcols, *hash cond, *hash opt);
4197 
4198 
4200 
4222  hash<string, hash<JoinOperatorInfo>> join_right_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash opt);
4223 
4225 
4230 
4233  const OP_LIKE = "like";
4234 
4236 
4238  const OP_LT = "<";
4239 
4241 
4243  const OP_LE = "<=";
4244 
4246 
4248  const OP_GT = ">";
4249 
4251 
4253  const OP_GE = ">=";
4254 
4256 
4258  const OP_NE = "!=";
4259 
4261 
4263  const OP_EQ = "=";
4264 
4266 
4268  const OP_CLT = "C<";
4269 
4271 
4273  const OP_CLE = "C<=";
4274 
4276 
4278  const OP_CGT = "C>";
4279 
4281 
4283  const OP_CGE = "C>=";
4284 
4286 
4288  const OP_CNE = "C!=";
4289 
4291 
4293  const OP_CEQ = "C=";
4294 
4296 
4298  const OP_BETWEEN = "between";
4299 
4301 
4303  const OP_IN = "in";
4304 
4306 
4308  const OP_NOT = "not";
4309 
4311 
4313  const OP_SUBSTR = "substr";
4314 
4316  const OP_OR = "or";
4317 
4319  const DefaultOpMap = (
4320  OP_LIKE: (
4321  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4322  args += arg;
4323  return sprintf("%s like %v", cn);
4324  },
4325  ),
4326  OP_LT: (
4327  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4328  args += arg;
4329  return sprintf("%s < %v", cn);
4330  },
4331  ),
4332  OP_LE: (
4333  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4334  args += arg;
4335  return sprintf("%s <= %v", cn);
4336  },
4337  ),
4338  OP_GT: (
4339  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4340  args += arg;
4341  return sprintf("%s > %v", cn);
4342  },
4343  ),
4344  OP_GE: (
4345  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4346  args += arg;
4347  return sprintf("%s >= %v", cn);
4348  },
4349  ),
4350  OP_NE: (
4351  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4352  if (arg === NULL || !exists arg)
4353  return sprintf("%s is not null", cn);
4354  args += arg;
4355  return sprintf("(%s != %v or %s is null)", cn, cn);
4356  },
4357  ),
4358  OP_EQ: (
4359  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4360  if (arg === NULL || !exists arg)
4361  return sprintf("%s is null", cn);
4362  args += arg;
4363  return sprintf("%s = %v", cn);
4364  },
4365  ),
4366  OP_BETWEEN: (
4367  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4368  args += arg[0];
4369  args += arg[1];
4370  return sprintf("%s between %v and %v", cn);
4371  },
4372  ),
4373  OP_IN: (
4374  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4375  *string ins = (foldl $1 + "," + $2, (map t.getSqlValue($1), arg));
4376  return exists ins ? sprintf("%s in (%s)", cn, ins) : "1 != 1";
4377  },
4378  ),
4379  OP_NOT: (
4380  "recursive": True,
4381  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4382  return sprintf("not (%s)", cn);
4383  },
4384  ),
4385  OP_CLT: (
4386  "argcolumn": True,
4387  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4388  return sprintf("%s < %s", cn, arg);
4389  },
4390  ),
4391  OP_CLE: (
4392  "argcolumn": True,
4393  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4394  return sprintf("%s <= %s", cn, arg);
4395  },
4396  ),
4397  OP_CGT: (
4398  "argcolumn": True,
4399  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4400  return sprintf("%s > %s", cn, arg);
4401  },
4402  ),
4403  OP_CGE: (
4404  "argcolumn": True,
4405  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4406  return sprintf("%s >= %s", cn, arg);
4407  },
4408  ),
4409  OP_CNE: (
4410  "argcolumn": True,
4411  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4412  return sprintf("%s != %s", cn, arg);
4413  },
4414  ),
4415  OP_CEQ: (
4416  "argcolumn": True,
4417  "code": string (object t, string cn, string arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4418  return sprintf("%s = %s", cn, arg);
4419  },
4420  ),
4421  OP_SUBSTR: (
4422  "code": string (object t, string cn, auto arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4423  args += arg[0]; // start
4424  if (!exists arg[1]);
4425 
4426  args += arg[1]; // count
4427  args += arg[2]; // text
4428  return sprintf("substring(%s from %v for %v) = %v", cn);
4429  },
4430  ),
4431  OP_OR: (
4432  "code": string (object t, string cn, list arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch) {
4433  return t.getOrClause(arg, \args, jch, join, ch, psch);
4434  },
4435  ),
4436  );
4438 
4461  hash<OperatorInfo> make_op(string op, auto arg);
4463 
4464 
4466 
4475  hash<OperatorInfo> op_like(string str);
4476 
4477 
4479 
4490  hash<OperatorInfo> op_lt(auto arg);
4491 
4492 
4494 
4505  hash<OperatorInfo> op_le(auto arg);
4506 
4507 
4509 
4520  hash<OperatorInfo> op_gt(auto arg);
4521 
4522 
4524 
4535  hash<OperatorInfo> op_ge(auto arg);
4536 
4537 
4539 
4552  hash<OperatorInfo> op_ne(auto arg);
4553 
4554 
4556 
4569  hash<OperatorInfo> op_eq(auto arg);
4570 
4571 
4573 
4585  hash<OperatorInfo> op_between(auto l, auto r);
4586 
4587 
4589 
4598  hash<OperatorInfo> op_in();
4599 
4600 
4602 
4613  hash<OperatorInfo> op_in(list args);
4614 
4615 
4617 
4624  hash<OperatorInfo> op_not(hash arg);
4625 
4626 
4628 
4639  hash<OperatorInfo> op_clt(string arg);
4640 
4641 
4643 
4654  hash<OperatorInfo> op_cle(string arg);
4655 
4656 
4658 
4669  hash<OperatorInfo> op_cgt(string arg);
4670 
4671 
4673 
4684  hash<OperatorInfo> op_cge(string arg);
4685 
4686 
4688 
4699  hash<OperatorInfo> op_cne(string arg);
4700 
4701 
4703 
4714  hash<OperatorInfo> op_ceq(string arg);
4715 
4716 
4718 
4729  hash<OperatorInfo> op_substr(int start, *int count, string text);
4730 
4731 
4733 
4743  hash<OperatorInfo> op_substr(int start, string text);
4744 
4745 
4747 
4764  hash<string, hash<OperatorInfo>> wop_or(hash h1, hash h2);
4765 
4767 
4774 
4777  const IOP_SEQ = "seq";
4778 
4780 
4782  const IOP_SEQ_CURRVAL = "seq_currval";
4783 
4785  const DefaultIopMap = {};
4787 
4793 
4801  hash<InsertOperatorInfo> make_iop(string iop, auto arg);
4802 
4803 
4805 
4814  hash<InsertOperatorInfo> iop_seq(string arg);
4815 
4816 
4818 
4827  hash<InsertOperatorInfo> iop_seq_currval(string arg);
4828 
4830 
4832  const SqlUtilDrivers = (
4833  "oracle",
4834  "pgsql",
4835  "mysql",
4836  "freetds",
4837  "sybase",
4838  );
4839 
4842 
4843 public:
4844 private:
4845 
4846 public:
4847 
4848  private :
4849  *hash h;
4850 
4851 public:
4852 
4854  constructor(*hash nh);
4855 
4856 
4859 
4860 
4863 
4864 
4866 
4881  auto memberGate(string k);
4882 
4883 
4885 
4891  clear();
4892 
4893 
4895  abstract auto take(string k);
4896 
4898  renameKey(string old_name, string new_name);
4899 
4900 
4902  *hash getHash();
4903 
4904 
4906 
4915  bool matchKeys(hash h1);
4916 
4917 
4919 
4928  bool matchKeys(list l);
4929 
4930 
4932 
4941  bool matchKeys(AbstractHashContainer c);
4942 
4943 
4945 
4954  bool partialMatchKeys(hash h1);
4955 
4956 
4958 
4967  bool partialMatchKeys(list l);
4968 
4969 
4971 
4980  bool partialMatchKeys(AbstractHashContainer c);
4981 
4982 
4984 
4993  bool val();
4994 
4995 
4997 
5004  list<string> keys();
5005 
5006 
5008 
5015  list values();
5016 
5017 
5019 
5026  Qore::AbstractIterator iterator();
5027 
5028 
5030 
5037  Qore::AbstractIterator keyIterator();
5038 
5039 
5041 
5048  Qore::AbstractIterator pairIterator();
5049 
5050 
5052  bool empty();
5053 
5054 
5056 
5063  int size();
5064 
5065 
5067 
5076  bool hasKey(string k);
5077 
5078 
5080 
5089  bool hasKeyValue(string k);
5090 
5091 
5093 
5102  *string firstKey();
5103 
5104 
5106 
5115  *string lastKey();
5116 
5117 
5119  abstract string getElementName();
5120  };
5121 
5124 
5125 public:
5126 private:
5127 
5128 public:
5129 
5130  private :
5131  softlist l;
5132 
5133 public:
5134 
5136  constructor(softlist nl);
5137 
5138 
5140 
5151  abstract auto get(softint i);
5152 
5154  add(auto val);
5155 
5156 
5158  auto take(int i);
5159 
5160 
5162  list getList();
5163 
5164 
5166 
5175  bool val();
5176 
5177 
5179 
5186  Qore::ListIterator iterator();
5187 
5188 
5190  bool empty();
5191 
5192 
5194 
5201  int size();
5202 
5203 
5205  abstract string getElementName();
5206 
5207 
5208 private:
5209  checkIndex(int i);
5210 public:
5211 
5212  };
5213 
5216 
5217 public:
5219  constructor();
5220 
5221 
5223  constructor(AbstractDatasource ds, hash tables, *hash opt);
5224 
5225 
5227  constructor(AbstractDatasource ds);
5228 
5229 
5231  add(string k, Table val);
5232 
5233 
5235  add(string k, AbstractTable val);
5236 
5237 
5239  add(Table val);
5240 
5241 
5243  add(AbstractTable val);
5244 
5245 
5247  AbstractTable take(string k);
5248 
5249 
5251  populate(AbstractDatasource ds, hash tables, *hash opt);
5252 
5253 
5255  populate(AbstractDatasource ds);
5256 
5257 
5259 
5273  *list getDropAllForeignConstraintsOnTableSql(string name, *hash opt);
5274 
5275 
5277 
5292  AbstractTable memberGate(string k);
5293 
5294 
5296  string getElementName();
5297 
5298 
5300  *AbstractTable getIfExists(AbstractDatasource ds, string name);
5301 
5302 
5304  AbstractTable get(AbstractDatasource ds, string name);
5305 
5306 
5308 
5323  *string getRenameTableIfExistsSql(string old_name, string new_name, *hash opts);
5324 
5325 
5327 
5338  bool tableRenamed(string old_name, string new_name, string old_sql_name);
5339 
5340 
5341 
5342 private:
5343  tableRenamedIntern(string old_name, string new_name, string oldsn);
5344 public:
5345 
5346 
5348 
5363  *string getDropConstraintIfExistsSql(string tname, string cname, *hash opts);
5364 
5365 
5366  list getCreateList();
5367 
5368 
5369  Qore::AbstractIterator createIterator();
5370 
5371 
5373 
5381  list getDropList();
5382 
5383 
5385 
5392  Qore::AbstractIterator dropIterator();
5393 
5394 
5395 
5396 private:
5397  getDependencies(reference<hash> tdh, reference<hash> sdh, *reference<hash> th);
5398 public:
5399 
5400  };
5401 
5404 
5405 public:
5407  constructor(*hash c) ;
5408 
5409 
5411  constructor(Columns old) ;
5412 
5413 
5415  add(string k, AbstractColumn val);
5416 
5417 
5419  AbstractColumn take(string k);
5420 
5421 
5423 
5438  AbstractColumn memberGate(string k);
5439 
5440 
5442  Columns subset(softlist l);
5443 
5444 
5446  string getElementName();
5447 
5448 
5450  bool equal(Columns cols);
5451 
5452  };
5453 
5456 
5457 public:
5458  public :
5460  string name;
5461 
5463  string native_type;
5464 
5466  *string qore_type;
5467 
5469  int size;
5470 
5472  bool nullable;
5473 
5475  *string def_val;
5476 
5478  *string comment;
5479 
5480 public:
5481 
5482  constructor(string n, string nt, *string qt, int sz, bool nul, *string dv, *string c);
5483 
5484 
5486  string getNativeTypeString();
5487 
5488 
5490  string getCreateSql(AbstractTable t);
5491 
5492 
5494 
5503  abstract list getAddColumnSql(AbstractTable t);
5504 
5506  string getDropSql(string table_name);
5507 
5508 
5510 
5523  list getModifySql(AbstractTable t, AbstractColumn c, *hash opt);
5524 
5525 
5527 
5537  abstract string getRenameSql(AbstractTable t, string new_name);
5538 
5540  bool equal(AbstractColumn c);
5541 
5542 
5544 
5545 private:
5546  abstract bool equalImpl(AbstractColumn c);
5547 public:
5548 
5550 
5564 private:
5565  abstract list getModifySqlImpl(AbstractTable t, AbstractColumn c, *hash opt);
5566 public:
5567  };
5568 
5571 
5572 public:
5573  public :
5575  int scale;
5576 
5577 public:
5578 
5579  constructor(softint n_scale = 0);
5580 
5581 
5583  string getNativeTypeString(string native_type, int precision);
5584 
5585  };
5586 
5589 
5590 public:
5591  constructor(*hash c) ;
5592 
5593 
5595  add(string k, AbstractIndex val);
5596 
5597 
5599  *AbstractIndex findEqual(AbstractIndex ix);
5600 
5601 
5603  AbstractIndex take(string k);
5604 
5605 
5607  *AbstractIndex tryTake(string k);
5608 
5609 
5611 
5626  AbstractIndex memberGate(string k);
5627 
5628 
5629  string getElementName();
5630 
5631  };
5632 
5635 
5636 public:
5637  public :
5639  string name;
5640 
5642  bool unique;
5643 
5646 
5647 public:
5648 
5649  private :
5652 
5653 public:
5654 
5656  constructor(string n, bool u, hash c);
5657 
5658 
5660  string getName();
5661 
5662 
5664  bool hasColumn(string cname);
5665 
5666 
5668  abstract string getCreateSql(string table_name, *hash opt);
5669 
5671  string getDropSql(string table_name);
5672 
5673 
5675  bool equal(AbstractIndex ix);
5676 
5677 
5679  bool equalExceptName(AbstractIndex ix);
5680 
5681 
5683  abstract bool equalImpl(AbstractIndex ix);
5684 
5686  abstract string getRenameSql(string table_name, string new_name);
5687 
5689  setSupportingConstraint(AbstractColumnSupportingConstraint c);
5690 
5691 
5693  setSupportingConstraint();
5694 
5695 
5697  *AbstractColumnSupportingConstraint getSupportingConstraint();
5698 
5699 
5701  list getRecreateSql(AbstractDatasource ds, string table_name, *hash opt);
5702 
5703  };
5704 
5707 
5708 public:
5709  constructor(*hash c) ;
5710 
5711 
5713  add(string k, AbstractConstraint val);
5714 
5715 
5717  AbstractConstraint take(string k);
5718 
5719 
5721  *AbstractUniqueConstraint findEqualUniqueConstraint(AbstractUniqueConstraint uk);
5722 
5723 
5725 
5740  AbstractConstraint memberGate(string k);
5741 
5742 
5743  string getElementName();
5744 
5745  };
5746 
5749 
5750 public:
5751 private:
5752 
5753 public:
5754 
5755  private :
5757  string name;
5758 
5759 public:
5760 
5762  constructor(string n);
5763 
5764 
5766  string getName();
5767 
5768 
5770  rename(string n);
5771 
5772 
5774  abstract string getCreateSql(string table_name, *hash opt);
5775 
5777  string getDropSql(string table_name);
5778 
5779 
5781  abstract list getRenameSql(string table_name, string new_name);
5782 
5784  string getDisableSql(string table_name);
5785 
5786 
5788  string getEnableSql(string table_name, *hash opt);
5789 
5790 
5792  bool equal(AbstractConstraint c);
5793 
5794 
5796 
5797 private:
5798  abstract bool equalImpl(AbstractConstraint c);
5799 public:
5800 
5802  abstract bool setIndexBase(string ix);
5803 
5805  abstract clearIndex();
5806 
5808  bool hasColumn(string cname);
5809 
5810  };
5811 
5814 
5815 public:
5816  public :
5818  string src;
5819 
5820 public:
5821 
5823  constructor(string n, string n_src) ;
5824 
5825 
5827 
5828 private:
5829  bool equalImpl(AbstractConstraint c);
5830 public:
5831 
5832 
5834  bool setIndexBase(string ix);
5835 
5836 
5838  clearIndex();
5839 
5840  };
5841 
5844 
5845 public:
5846  private :
5849 
5851  *string index;
5852 
5853 public:
5854 
5856  constructor(string n, *hash c, *string n_index) ;
5857 
5858 
5860  constructor(string n, Columns c, *string n_index) ;
5861 
5862 
5864 
5868  Qore::AbstractIterator getSourceConstraintIterator();
5869 
5870 
5872  hash getDisableReenableSql(AbstractDatasource ds, string table_name, *hash opts);
5873 
5874 
5876  findMatchingIndex(*Indexes indexes);
5877 
5878 
5880 
5890  addSourceConstraint(string tname, AbstractForeignConstraint fk);
5891 
5892 
5894  removeSourceConstraint(string tname, list cols);
5895 
5896 
5898  renameSourceConstraintTable(string old_name, string new_name);
5899 
5900 
5902  bool hasColumn(string cname);
5903 
5904 
5906  *string getIndex();
5907 
5908 
5910 
5911 private:
5912  bool equalImpl(AbstractConstraint c);
5913 public:
5914 
5915 
5917  bool setIndexBase(string ix);
5918 
5919 
5921  clearIndex();
5922 
5923 
5925  abstract string getCreateSql(string table_name, *hash opts);
5926  };
5927 
5930 
5931 public:
5933  constructor(string n, *hash c, *string n_index) ;
5934 
5935  };
5936 
5939 
5940 public:
5941  constructor() ;
5942 
5943 
5944  constructor(string n, *hash c) ;
5945 
5946  };
5947 
5950 
5951 public:
5952  constructor(*hash c) ;
5953 
5954 
5956  add(string k, AbstractForeignConstraint val);
5957 
5958 
5960  AbstractForeignConstraint take(string k);
5961 
5962 
5965 
5966 
5968 
5983  AbstractForeignConstraint memberGate(string k);
5984 
5985 
5987  *hash findConstraintOn(string table, softlist cols);
5988 
5989 
5991  string getElementName();
5992 
5993  };
5994 
5997 
5998 public:
5999  public :
6001  string table;
6002 
6005 
6006 public:
6007 
6009  constructor(string t, Columns c);
6010 
6011 
6013  bool equal(ForeignConstraintTarget targ);
6014 
6015  };
6016 
6019 
6020 public:
6021  public :
6024 
6025 public:
6026 
6027  constructor(string n, Columns c, ForeignConstraintTarget t) ;
6028 
6029 
6031 
6032 private:
6033  bool equalImpl(AbstractConstraint con);
6034 public:
6035 
6036  };
6037 
6040 
6041 public:
6042  public :
6044  string name;
6045 
6048 
6051 
6054 
6055 public:
6056 
6058  constructor(string n_name, number n_start = 1, number n_increment = 1, *softnumber n_max);
6059 
6060 
6062  abstract string getCreateSql(*hash opt);
6063 
6065 
6067  string getDropSql(*hash opt);
6068 
6069 
6071 
6074  abstract softlist getRenameSql(string new_name, *hash opt);
6075  };
6076 
6079 
6080 public:
6081  public :
6082  // ! potential object schema
6083  *string schema;
6084 
6086  string name;
6087 
6089  string src;
6090 
6093 
6094 public:
6095 
6097  constructor(string n_name, string n_src);
6098 
6099 
6101  abstract string getCreateSql(*hash opt);
6102 
6104 
6106  string getDropSql(*hash opt);
6107 
6108 
6110 
6113  abstract softlist getRenameSql(string new_name, *hash opt);
6114  };
6115 
6118 
6119 public:
6120  public :
6122  string name;
6123 
6125  string type;
6126 
6128  string src;
6129 
6130 public:
6131 
6133 
6137  constructor(string n, string n_type, string n_src);
6138 
6139 
6141  string getType();
6142 
6143 
6145 
6147  string getDropSql(*hash opt);
6148 
6149 
6151  bool equal(AbstractFunctionBase t);
6152 
6153 
6155 
6156 private:
6157  abstract bool equalImpl(AbstractFunctionBase t);
6158 public:
6159  };
6160 
6163 
6164 public:
6166 
6170  constructor(string n, string n_type, string n_src) ;
6171 
6172 
6174  abstract list getCreateSql(*hash opt);
6175 
6177 
6180  abstract softlist getRenameSql(string new_name, *hash opt);
6181 
6183  setName(string new_name);
6184 
6185  };
6186 
6189 
6190 public:
6191  constructor(*hash c) ;
6192 
6193 
6195  add(string k, AbstractFunction val);
6196 
6197 
6199  AbstractFunction take(string k);
6200 
6201 
6203 
6218  AbstractFunction memberGate(string k);
6219 
6220 
6221  string getElementName();
6222 
6223  };
6224 
6227 
6228 public:
6230  constructor(string n, string n_src) ;
6231 
6232 
6234  abstract list getCreateSql(string table_name, *hash opt);
6235 
6237  abstract softlist getRenameSql(string table_name, string new_name);
6238 
6240  abstract list getDropSql(string table_name);
6241  };
6242 
6245 
6246 public:
6247  constructor(*hash c) ;
6248 
6249 
6251  add(string k, AbstractTrigger val);
6252 
6253 
6255  AbstractTrigger take(string k);
6256 
6257 
6259 
6274  AbstractTrigger memberGate(string k);
6275 
6276 
6277  string getElementName();
6278 
6279  };
6280 
6282 
6292  class Database {
6293 
6294 public:
6295  private :
6298 
6299 public:
6300 
6302 
6313  constructor(AbstractDatasource ds, *hash opts);
6314 
6315 
6317 
6327  constructor(string ds, *hash opts);
6328 
6329 
6331 
6350 
6351 
6353  SqlUtil::AbstractDatabase getDatabase();
6354 
6355 
6357  any methodGate(string meth);
6358 
6359  };
6360 
6363 
6364 public:
6365  private :
6367  AbstractDatasource ds;
6369  string dsdesc;
6371  Mutex l();
6374 
6375 public:
6376 
6378 
6384 private:
6385  constructor(AbstractDatasource nds, *hash nopts);
6386 public:
6387 
6388 
6389  static string makeDatasourceDesc(AbstractDatasource ds);
6390 
6391 
6392 private:
6393  validateOptionsIntern(string err, hash ropt, reference<hash> opt, string tag);
6394 public:
6395 
6396 
6397 
6398 private:
6399  static validateOptionIntern(string err, string type, reference opt, string k, string tag);
6400 public:
6401 
6402 
6404 
6405 private:
6406  validateHashKeysForWhitespaces(auto node);
6407 public:
6408 
6409 
6412 
6413 
6415  string getDriverName();
6416 
6417 
6419  string getDatasourceDesc();
6420 
6421  };
6422 
6425 
6426 public:
6427  public :
6429 
6432  const DatabaseOptions = (
6433  "native_case": Type::Boolean,
6434  );
6435 
6437 
6440  const CacheOptions = (
6441  "table_cache": "Tables",
6442  );
6443 
6445 
6450  const CallbackOptions = (
6451  "info_callback": "code",
6452  "sql_callback": "code",
6453  "sql_callback_executed": Type::Boolean,
6454  );
6455 
6464  const AC_Unchanged = 0;
6466 
6468  const AC_Create = 1;
6469 
6471  const AC_Drop = 2;
6472 
6474  const AC_Rename = 3;
6475 
6477  const AC_Modify = 4;
6478 
6480  const AC_Truncate = 5;
6481 
6483  const AC_Add = 6;
6484 
6486  const AC_Recreate = 7;
6487 
6489  const AC_Insert = 8;
6490 
6492  const AC_Update = 9;
6493 
6495  const AC_Delete = 10;
6496 
6498  const AC_NotFound = 11;
6500 
6502  const ActionMap = (
6503  AC_Unchanged: "unchanged",
6504  AC_Create: "create",
6505  AC_Drop: "drop",
6506  AC_Rename: "rename",
6507  AC_Modify: "modify",
6508  AC_Truncate: "truncate",
6509  AC_Add: "add",
6510  AC_Recreate: "recreate",
6511  AC_Insert: "insert",
6512  AC_Update: "update",
6513  AC_Delete: "delete",
6514  AC_NotFound: "not found",
6515  );
6516 
6518  const ActionDescMap = (
6519  "unchanged": AC_Unchanged,
6520  "create": AC_Create,
6521  "drop": AC_Drop,
6522  "rename": AC_Rename,
6523  "modify": AC_Modify,
6524  "truncate": AC_Truncate,
6525  "add": AC_Add,
6526  "recreate": AC_Recreate,
6527  "insert": AC_Insert,
6528  "update": AC_Update,
6529  "delete": AC_Delete,
6530  "not found": AC_NotFound,
6531  );
6532 
6534  const ActionLetterMap = (
6535  AC_Unchanged: ".",
6536  AC_Create: "C",
6537  AC_Drop: "D",
6538  AC_Rename: "N",
6539  AC_Modify: "M",
6540  AC_Truncate: "T",
6541  AC_Add: "A",
6542  AC_Recreate: "R",
6543  AC_Insert: "I",
6544  AC_Update: "U",
6545  AC_Delete: "X",
6546  AC_NotFound: ".",
6547  );
6548 
6550 
6556  const CreationOptions = CallbackOptions + (
6557  "replace": Type::Boolean,
6558  "table_cache": "Tables",
6559  "data_tablespace": Type::String,
6560  "index_tablespace": Type::String,
6561  );
6562 
6564 
6567  const AlignSchemaOptions = CreationOptions + (
6568  "force": Type::Boolean,
6569  );
6570 
6572 
6575  const DropSchemaOptions = CallbackOptions + (
6576  "force": Type::Boolean,
6577  );
6578 
6580 
6592  const SchemaDescriptionOptions = (
6593  "tables": Type::Hash,
6594  "table_map": Type::Hash,
6595 
6596  "sequences": Type::Hash,
6597  "sequence_map": Type::Hash,
6598 
6599  "functions": Type::Hash,
6600  "function_map": Type::Hash,
6601 
6602  "procedures": Type::Hash,
6603  "procedure_map": Type::Hash,
6604 
6605  //"views": Type::Hash,
6606  //"view_map": Type::Hash,
6607  );
6608 
6610 
6615  const SequenceDescriptionOptions = (
6616  "start": Type::Int,
6617  "increment": Type::Int,
6618  "end": Type::Int,
6619  );
6620 
6622  const ComputeStatisticsOptions = (
6623  "tables" : "softstringlist",
6624  );
6625 
6627  const ReclaimSpaceOptions = (
6628  "tables" : "softstringlist",
6629  );
6630 
6631 public:
6632 
6633  private :
6636 
6637 public:
6638 
6640 
6646 private:
6647  constructor(AbstractDatasource nds, *hash nopts) ;
6648 public:
6649 
6650 
6652  list features();
6653 
6654 
6655  static doOkCallback(*hash opt, int ac, string type, string name, *string table, *string info);
6656 
6657  static *string doCallback(*hash opt, *string sql, int ac, string type, string name, *string table, *string new_name, *string info);
6658 
6659  static list doCallback(*hash opt, list sql, int ac, string type, string name, *string table, *string new_name, *string info);
6660 
6661 /*
6662  static *string doCallback(*hash opt, *string sql, string fmt) {
6663  if (!sql)
6664  return;
6665  if (opt.info_callback)
6666  opt.info_callback(vsprintf(fmt, argv));
6667  if (opt.sql_callback)
6668  opt.sql_callback(sql);
6669  return sql;
6670  }
6671 
6672  static list doCallback(*hash opt, list sql, string fmt) {
6673  if (sql) {
6674  if (opt.info_callback)
6675  opt.info_callback(vsprintf(fmt, argv));
6676  if (opt.sql_callback)
6677  map opt.sql_callback($1), sql;
6678  }
6679  return sql;
6680  }
6681 */
6682 
6684 
6695  any tryExec(string sql);
6696 
6697 
6699 
6709  any tryExecArgs(string sql, *softlist args);
6710 
6711 
6713 
6724  any tryExecRaw(string sql);
6725 
6726 
6728 
6742  list getAlignSql(hash schema_hash, *hash opt, *Tables table_cache);
6743 
6744 
6746 
6759  list getDropSchemaSql(hash schema_hash, *hash opt);
6760 
6761 
6762 
6763 private:
6764  list dropSqlUnlocked(string type, hash schema_hash, code get, code make, *hash opt, string make_arg_type);
6765 public:
6766 
6767 
6768 
6769 private:
6770  list alignCodeUnlocked(string type, hash schema_hash, code get, code make, *hash opt, string make_arg_type);
6771 public:
6772 
6773 
6775 
6789  AbstractSequence makeSequence(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
6790 
6791 
6792  AbstractSequence makeSequenceFromDescription(string name, *hash sh, *hash opts);
6793 
6794 
6796 
6809  AbstractTable makeTable(string name, hash desc, *hash opts);
6810 
6811 
6813 
6824  AbstractFunction makeFunction(string name, string src, *hash opts);
6825 
6826 
6828 
6839  AbstractFunction makeProcedure(string name, string src, *hash opt);
6840 
6841 
6843 
6855  bool dropFunctionIfExists(string name, *hash opt);
6856 
6857 
6859 
6871  bool dropProcedureIfExists(string name, *hash opt);
6872 
6873 
6875 
6887  bool dropSequenceIfExists(string name, *hash opt);
6888 
6889 
6891 
6903  bool dropViewIfExists(string name, *hash opt);
6904 
6905 
6907 
6919  bool dropTableIfExists(string name, *hash opt);
6920 
6921 
6923 
6935  *string getDropFunctionSqlIfExists(string name, *hash opt);
6936 
6937 
6939 
6951  *string getDropProcedureSqlIfExists(string name, *hash opt);
6952 
6953 
6955 
6967  *string getDropSequenceSqlIfExists(string name, *hash opt);
6968 
6969 
6971 
6983  *list getDropTableSqlIfExists(string name, *hash opt);
6984 
6985 
6986  doDropSql(*softlist l, string type, string name, *hash opt);
6987 
6988 
6989  bool doDrop(*softlist l, string type, string name, *hash opt);
6990 
6991 
6993 
7005  list getAlignFunctionSql(AbstractFunction f, *hash opt);
7006 
7007 
7009 
7021  list getAlignProcedureSql(AbstractFunction f, *hash opt);
7022 
7023 
7025 
7034  *AbstractTable getTable(string name);
7035 
7036 
7038 
7047  *AbstractSequence getSequence(string name);
7048 
7049 
7051 
7062  *AbstractFunction getFunction(string name);
7063 
7064 
7066 
7077  *AbstractFunction getProcedure(string name);
7078 
7079 
7081 
7090  *AbstractView getView(string name);
7091 
7092 
7094 
7103  int getNextSequenceValue(string name);
7104 
7105 
7107 
7116  int getCurrentSequenceValue(string name);
7117 
7118 
7120 
7129  string getSqlFromList(list l);
7130 
7131 
7133  bool supportsSequences();
7134 
7135 
7137  bool supportsTypes();
7138 
7139 
7141  bool supportsPackages();
7142 
7143 
7145  list listTables();
7146 
7147 
7149  Qore::ListIterator tableIterator();
7150 
7151 
7153  list listFunctions();
7154 
7155 
7157  Qore::ListIterator functionIterator();
7158 
7159 
7161  list listProcedures();
7162 
7163 
7165  Qore::ListIterator procedureIterator();
7166 
7167 
7169  list listSequences();
7170 
7171 
7173  Qore::ListIterator sequenceIterator();
7174 
7175 
7177  list listViews();
7178 
7179 
7181  Qore::ListIterator viewIterator();
7182 
7183 
7185 
7195  bool rebuildIndex(string name, *hash options);
7196 
7197 
7199 
7207  bool rebuildIndex(AbstractIndex index, *hash options);
7208 
7209 
7211 
7218  computeStatistics(*hash options);
7219 
7220 
7222 
7229  reclaimSpace(*hash options);
7230 
7231 
7232 
7233 private:
7234  validateOptionsIntern(string err, hash ropt, reference<hash> opt);
7235 public:
7236 
7237 
7238 
7239 private:
7240  validateOptionsIntern(string err, hash ropt, reference<hash> opt, string tag);
7241 public:
7242 
7243 
7244  static AbstractDatabase getDatabase(AbstractDatasource nds, *hash opts);
7245 
7246  static AbstractDatabase getDatabase(string dsstr, *hash opts);
7247 
7248  static AbstractDatabase getDatabase(hash dsh, *hash opts);
7249 
7250  static checkDriverOptions(reference<hash> h, string drv);
7251 
7253 
7254 private:
7255  hash getDatabaseOptions();
7256 public:
7257 
7258 
7260 
7261 private:
7262  hash getCallbackOptions();
7263 public:
7264 
7265 
7267 
7268 private:
7269  hash getCreationOptions();
7270 public:
7271 
7272 
7274 
7275 private:
7277 public:
7278 
7279 
7281 
7282 private:
7283  hash getAlignSchemaOptions();
7284 public:
7285 
7286 
7288 
7289 private:
7290  hash getDropSchemaOptions();
7291 public:
7292 
7293 
7295 
7296 private:
7297  hash getSchemaDescriptionOptions();
7298 public:
7299 
7300 
7302 
7303 private:
7304  hash getSequenceDescriptionOptions();
7305 public:
7306 
7307 
7309 
7310 private:
7311  hash getRebuildIndexOptions();
7312 public:
7313 
7314 
7316 
7317 private:
7318  hash getComputeStatisticsOptions();
7319 public:
7320 
7321 
7323 
7324 private:
7325  hash getReclaimSpaceOptions();
7326 public:
7327 
7328 
7330 
7331 private:
7332  auto tryExecArgsImpl(string sql, *softlist args);
7333 public:
7334 
7335 
7337 
7338 private:
7339  auto tryExecRawImpl(string sql);
7340 public:
7341 
7342 
7343 
7344 private:
7345  abstract string getCreateSqlImpl(list l);
7346 public:
7347 
7348 private:
7349  abstract list getAlignSqlImpl(hash schema_hash, *hash opt);
7350 public:
7351 
7352 private:
7353  abstract list getDropSchemaSqlImpl(hash schema_hash, *hash opt);
7354 public:
7355 
7356 
7357 private:
7358  abstract *AbstractSequence getSequenceImpl(string name);
7359 public:
7360 
7361 private:
7362  abstract *AbstractFunction getFunctionImpl(string name);
7363 public:
7364 
7365 private:
7366  abstract *AbstractFunction getProcedureImpl(string name);
7367 public:
7368 
7369 private:
7370  abstract *AbstractView getViewImpl(string name);
7371 public:
7372 
7373 
7374 private:
7375  abstract AbstractSequence makeSequenceImpl(string name, number start = 1, number increment = 1, *softnumber end, *hash opts);
7376 public:
7377 
7378 private:
7379  abstract AbstractFunction makeFunctionImpl(string name, string src, *hash opts);
7380 public:
7381 
7382 private:
7383  abstract AbstractFunction makeProcedureImpl(string name, string src, *hash opts);
7384 public:
7385 
7386 
7387 private:
7388  abstract list featuresImpl();
7389 public:
7390 
7391 private:
7392  abstract list listTablesImpl();
7393 public:
7394 
7395 private:
7396  abstract list listFunctionsImpl();
7397 public:
7398 
7399 private:
7400  abstract list listProceduresImpl();
7401 public:
7402 
7403 private:
7404  abstract list listSequencesImpl();
7405 public:
7406 
7407 private:
7408  abstract list listViewsImpl();
7409 public:
7410 
7412 
7413 private:
7414  abstract int getNextSequenceValueImpl(string name);
7415 public:
7417 
7418 private:
7419  abstract int getCurrentSequenceValueImpl(string name);
7420 public:
7421 
7423 
7424 private:
7425  abstract bool supportsSequencesImpl();
7426 public:
7427 
7428 private:
7429  abstract bool supportsPackagesImpl();
7430 public:
7431 
7432 private:
7433  abstract bool supportsTypesImpl();
7434 public:
7435 
7436 
7437 private:
7438  abstract bool rebuildIndexImpl(string name, *hash options);
7439 public:
7440 
7441 private:
7442  abstract computeStatisticsImpl(*hash options);
7443 public:
7444 
7445 private:
7446  abstract reclaimSpaceImpl(*hash options);
7447 public:
7448  };
7449 
7451 
7467  class Table {
7468 
7469 public:
7470  private :
7473 
7474 public:
7475 
7477 
7489  constructor(AbstractDatasource ds, string name, *hash opts);
7490 
7491 
7493 
7505  constructor(string ds, string name, *hash opts);
7506 
7507 
7509 
7529  constructor(hash ds, string name, *hash opts);
7530 
7531 
7533 
7541  constructor(AbstractDatasource ds, hash desc, string name, *hash opts);
7542 
7543 
7545  AbstractTable getTable();
7546 
7547 
7549 
7551  any methodGate(string meth);
7552 
7553 
7554  }; // class Table
7555 
7557 
7560 
7561 public:
7562  public :
7564 
7568  const TableOptions = (
7569  "native_case": Type::Boolean,
7570  "table_cache": "Tables",
7571  );
7572 
7574 
7578  const IndexOptions = (
7579  "index_tablespace": Type::String,
7580  "replace": Type::Boolean,
7581  );
7582 
7584 
7587 
7589  const CacheOptions = (
7590  "table_cache": "Tables",
7591  );
7592 
7594 
7598  "table_cache": "Tables",
7599  );
7600 
7602 
7605 
7607 
7609  const SelectOptions = (
7610  "alias": Type::String,
7611  "comment": Type::String,
7612  "hint": Type::String,
7613  "columns": Type::NothingType,
7614  "where": "hash/list",
7615  "orderby": "softstringinthashlist",
7616  "desc": Type::Boolean,
7617  "limit": Type::Int,
7618  "offset": Type::Int,
7619  "join": Type::Hash,
7620  "groupby": "softstringinthashlist",
7621  "having": Type::Hash,
7622  "superquery": Type::Hash,
7623  "forupdate": Type::Boolean,
7624  );
7625 
7628  "indexes": True,
7629  "foreign_constraints": True,
7630  "triggers": True,
7631  );
7632 
7634 
7638  "omit": "softstringlist",
7639  );
7640 
7642 
7651  "column_map": Type::Hash,
7652  "index_map": Type::Hash,
7653  "constraint_map": Type::Hash,
7654  "trigger_map": Type::Hash,
7655  "db_table_cache": "Tables",
7656  "force": Type::Boolean,
7657  );
7658 
7660 
7672  "columns": Type::Hash,
7673  "primary_key": Type::Hash,
7674  "indexes": Type::Hash,
7675  "triggers": Type::Hash,
7676  "foreign_constraints": Type::Hash,
7677  "unique_constraints": Type::Hash,
7678  //"check_constraints": Type::Hash,
7679  "table_cache": "Tables",
7680  );
7681 
7683 
7696  "qore_type": Type::String,
7697  "native_type": Type::String,
7698  "size": Type::Int,
7699  "scale": Type::Int,
7700  "default_value": Type::NothingType,
7701  "default_value_native": Type::Boolean,
7702  "comment": Type::String,
7703  "notnull": Type::Boolean,
7704  "driver": Type::Hash,
7705  );
7706 
7708 
7712  "notnull": Type::Boolean,
7713  );
7714 
7716  const ColumnOptions = {};
7717 
7719 
7724  "sqlarg_callback": "code",
7725  "tablecode": "code",
7726  );
7727 
7729 
7739  "returning": "stringhashlist",
7740  );
7741 
7743 
7749  const UpsertOptions = (
7750  "info_callback": "code",
7751  "commit_block": Type::Int,
7752  "delete_others": Type::Boolean,
7753  "omit_update": "softstringlist",
7754  );
7755 
7757 
7762  "info_callback": "code",
7763  "commit_block": Type::Int,
7764  );
7765 
7781 
7788 
7790 
7796 
7798 
7805 
7807 
7811  const UpsertAuto = 4;
7812 
7814 
7818  const UpsertInsertOnly = 5;
7819 
7821 
7825  const UpsertUpdateOnly = 6;
7826 
7828 
7831  UpsertInsertFirst: "UpsertInsertFirst",
7832  UpsertUpdateFirst: "UpsertUpdateFirst",
7833  UpsertSelectFirst: "UpsertSelectFirst",
7834  UpsertAuto: "UpsertAuto",
7835  UpsertInsertOnly: "UpsertInsertOnly",
7836  UpsertUpdateOnly: "UpsertUpdateOnly",
7837  );
7838 
7840 
7843  "UpsertInsertFirst": UpsertInsertFirst,
7844  "UpsertUpdateFirst": UpsertUpdateFirst,
7845  "UpsertSelectFirst": UpsertSelectFirst,
7846  "UpsertAuto": UpsertAuto,
7847  "UpsertInsertOnly": UpsertInsertOnly,
7848  "UpsertUpdateOnly": UpsertUpdateOnly,
7849  );
7851 
7856  const UR_Inserted = 1;
7858 
7860  const UR_Verified = 2;
7861 
7863  const UR_Updated = 3;
7864 
7866  const UR_Unchanged = 4;
7867 
7869  const UR_Deleted = 5;
7871 
7873 
7876  UR_Inserted: "inserted",
7877  UR_Verified: "verified",
7878  UR_Updated: "updated",
7879  UR_Unchanged: "unchanged",
7880  UR_Deleted: "deleted",
7881  );
7882 
7884 
7887  "inserted": UR_Inserted,
7888  "verified": UR_Verified,
7889  "updated": UR_Updated,
7890  "unchanged": UR_Unchanged,
7891  "deleted": UR_Deleted,
7892  );
7893 
7896  UR_Inserted: "I",
7897  UR_Verified: "V",
7898  UR_Updated: "U",
7899  UR_Unchanged: ".",
7900  UR_Deleted: "X",
7901  );
7902 
7903 public:
7904 
7905  private :
7907  string name;
7923  bool inDb = False;
7925  bool manual = False;
7926 
7927 public:
7928 
7930 
7937 private:
7938  constructor(AbstractDatasource nds, string nname, *hash nopts) ;
7939 public:
7940 
7941 
7943  copy(AbstractTable old);
7944 
7945 
7947 
7958  setDatasource(AbstractDatasource nds);
7959 
7960 
7961 
7962 private:
7963  doTableOptions(*hash nopts);
7964 public:
7965 
7966 
7968  commit();
7969 
7970 
7972  rollback();
7973 
7974 
7976 
7985  bool inDb();
7986 
7987 
7989 
7997 
7998 
8000 
8011  dropCommit(*hash opt);
8012 
8013 
8015 
8026  drop(*hash opt);
8027 
8028 
8030  deprecated dropNoCommit(*hash opt);
8031 
8033 
8044  auto tryExec(string sql);
8045 
8046 
8048 
8058  auto tryExecArgs(string sql, *softlist args);
8059 
8060 
8062 
8073  auto tryExecRaw(string sql);
8074 
8075 
8077 
8088  softlist getDropSql(*hash opt);
8089 
8090 
8092 
8099  truncateCommit();
8100 
8101 
8103 
8110  truncate();
8111 
8112 
8114  deprecated truncateNoCommit();
8115 
8117 
8132  string getTruncateSql(*hash opt);
8133 
8134 
8136 
8145  createCommit(*hash opt);
8146 
8147 
8149 
8160  create(*hash opt);
8161 
8162 
8164  deprecated createNoCommit(*hash opt);
8165 
8167 
8178  rename(string new_name, *reference<string> sql, *Tables table_cache);
8179 
8180 
8181 
8182 private:
8183  doRenameIntern(string new_name, *Tables table_cache);
8184 public:
8185 
8186 
8188 
8199  bool emptyData();
8200 
8201 
8203 
8212  bool empty();
8213 
8214 
8215 
8216 private:
8217  bool emptyUnlocked();
8218 public:
8219 
8220 
8222 
8228  setupTable(hash desc, *hash opt);
8229 
8230 
8232 
8256  AbstractColumn addColumn(string cname, hash opt, bool nullable = True, *reference lsql);
8257 
8258 
8260 
8289  list getAddColumnSql(string cname, hash copt, bool nullable = True, *hash opt);
8290 
8291 
8292 
8293 private:
8294  AbstractColumn addColumnUnlocked(string cname, hash opt, bool nullable = True, *reference lsql, bool do_exec = True, bool modify_table = True);
8295 public:
8296 
8297 
8298 
8299 private:
8300  addColumnToTableUnlocked(AbstractColumn c);
8301 public:
8302 
8303 
8305 
8330  AbstractColumn modifyColumn(string cname, hash opt, bool nullable = True, *reference lsql);
8331 
8332 
8334 
8361  list getModifyColumnSql(string cname, hash copt, bool nullable = True, *hash opt);
8362 
8363 
8365 
8382  AbstractColumn renameColumn(string old_name, string new_name, reference<string> sql);
8383 
8384 
8386 
8404  string getRenameColumnSql(string old_name, string new_name, *hash opt);
8405 
8406 
8407 
8408 private:
8409  AbstractColumn renameColumnIntern(AbstractColumn c, string new_name);
8410 public:
8411 
8412 
8413 
8414 private:
8415  validateOptionsIntern(string err, hash ropt, reference<hash> opt);
8416 public:
8417 
8418 
8419 
8420 private:
8421  validateOptionsIntern(string err, hash ropt, reference<hash> opt, string tag);
8422 public:
8423 
8424 
8425 
8426 private:
8427  execSql(softlist lsql);
8428 public:
8429 
8430 
8432 
8452  AbstractPrimaryKey addPrimaryKey(string pkname, softlist cols, *hash opt, *reference<string> sql);
8453 
8454 
8456 
8478  string getAddPrimaryKeySql(string pkname, softlist cols, *hash pkopt, *hash opt);
8479 
8480 
8481 
8482 private:
8483  setPrimaryKeyUnlocked(AbstractPrimaryKey pk);
8484 public:
8485 
8486 
8487 
8488 private:
8489  AbstractPrimaryKey addPrimaryKeyUnlocked(string pkname, softlist cols, *hash opt, *reference<string> sql);
8490 public:
8491 
8492 
8493 
8494 private:
8495  AbstractPrimaryKey addPrimaryKeyUnlockedIntern(string pkname, softlist cols, *hash opt, *reference<string> sql);
8496 public:
8497 
8498 
8500 
8517 
8518 
8519 
8520 private:
8521  list getDropAllConstraintsAndIndexesOnColumnSqlUnlocked(string cname, *hash opt);
8522 public:
8523 
8524 
8526 
8546 
8547 
8549 
8567  AbstractPrimaryKey dropPrimaryKey(*reference lsql);
8568 
8569 
8571 
8592  AbstractUniqueConstraint addUniqueConstraint(string cname, softlist cols, *hash opt, *reference<string> sql);
8593 
8594 
8596 
8616  string getAddUniqueConstraintSql(string cname, softlist cols, *hash ukopt, *hash opt);
8617 
8618 
8619 
8620 private:
8621  AbstractUniqueConstraint addUniqueConstraintUnlocked(string cname, softlist cols, *hash opt, *reference<string> sql);
8622 public:
8623 
8624 
8625 
8626 private:
8627  AbstractUniqueConstraint addUniqueConstraintUnlockedIntern(string cname, softlist cols, *hash opt, *reference<string> sql);
8628 public:
8629 
8630 
8632 
8653  AbstractIndex addIndex(string iname, bool unique, softlist cols, *hash opt, *reference<string> sql);
8654 
8655 
8657 
8678  string getAddIndexSql(string iname, bool unique, softlist cols, *hash ixopt, *hash opt);
8679 
8680 
8681 
8682 private:
8683  AbstractIndex addIndexUnlocked(string iname, bool unique, softlist cols, *hash opt, *reference<string> sql);
8684 public:
8685 
8686 
8687 
8688 private:
8689  AbstractIndex addIndexUnlockedIntern(string iname, bool unique, softlist cols, *hash opt, *reference<string> sql);
8690 public:
8691 
8692 
8694 
8706  AbstractIndex renameIndex(string old_name, string new_name, reference<string> sql);
8707 
8708 
8710 
8728  AbstractIndex dropIndex(string iname, *reference<string> sql);
8729 
8730 
8732 
8751  string getDropIndexSql(string iname, *hash opt);
8752 
8753 
8755 
8777  AbstractForeignConstraint addForeignConstraint(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference<string> sql);
8778 
8779 
8781 
8803  string getAddForeignConstraintSql(string cname, softlist cols, string table, *softlist tcols, *hash fkopt, *hash opt);
8804 
8805 
8806 
8807 private:
8808  Columns getReferencedTableColumnsUnlocked(string table, *Tables cache, string err = "FOREIGN-CONSTRAINT-ERROR");
8809 public:
8810 
8811 
8812 
8813 private:
8814  AbstractForeignConstraint addForeignConstraintUnlocked(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference<string> sql);
8815 public:
8816 
8817 
8818 
8819 private:
8820  AbstractForeignConstraint addForeignConstraintUnlockedIntern(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference<string> sql);
8821 public:
8822 
8823 
8825 
8843  AbstractForeignConstraint dropForeignConstraint(string cname, *reference<string> sql);
8844 
8845 
8847 
8863 
8864 
8866 
8886  AbstractCheckConstraint addCheckConstraint(string cname, string src, *hash opt, *reference<string> sql);
8887 
8888 
8890 
8912  string getAddCheckConstraintSql(string cname, string src, *hash copt, *hash opt);
8913 
8914 
8915 
8916 private:
8917  AbstractCheckConstraint addCheckConstraintUnlocked(string cname, string src, *hash opt, *reference<string> sql);
8918 public:
8919 
8920 
8921 
8922 private:
8923  AbstractCheckConstraint addCheckConstraintUnlockedIntern(string cname, string src, *hash opt, *reference<string> sql);
8924 public:
8925 
8926 
8928 
8940  AbstractConstraint renameConstraint(string old_name, string new_name, reference lsql);
8941 
8942 
8944 
8963  string getDropConstraintSql(string cname, *hash opt);
8964 
8965 
8967 
8986  *string getDropConstraintIfExistsSql(string cname, *hash opt, *reference<AbstractConstraint> cref);
8987 
8988 
8989 
8990 private:
8991  AbstractConstraint findDropConstraintUnlocked(string cname, reference<code> rmv);
8992 public:
8993 
8994 
8996 
9014  AbstractConstraint dropConstraint(string cname, *reference<string> sql);
9015 
9016 
9018 
9038  AbstractTrigger addTrigger(string tname, string src, *hash opt, *reference lsql);
9039 
9040 
9042 
9064  list getAddTriggerSql(string tname, string src, *hash topt, *hash opt);
9065 
9066 
9067 
9068 private:
9069  AbstractTrigger addTriggerUnlocked(string tname, string src, *hash opt, *reference lsql);
9070 public:
9071 
9072 
9073 
9074 private:
9075  AbstractTrigger addTriggerUnlockedIntern(string tname, string src, *hash opt, *reference lsql);
9076 public:
9077 
9078 
9080 
9098  AbstractTrigger dropTrigger(string tname, *reference<string> sql);
9099 
9100 
9102 
9121  list getDropTriggerSql(string tname, *hash opt);
9122 
9123 
9124 
9125 private:
9126  getAllConstraintsUnlocked(*hash opt);
9127 public:
9128 
9129 
9130 
9131 private:
9132  checkUniqueConstraintName(string err, string cname);
9133 public:
9134 
9135 
9136 
9137 private:
9138  checkUniqueConstraintNameValidateOptions(string err, string cname, hash ropt, reference<hash> opt);
9139 public:
9140 
9141 
9143 
9144 private:
9145  validateColumnOptions(string cname, reference<hash> opt, bool nullable);
9146 public:
9147 
9148 
9150 
9168  AbstractColumn dropColumn(string cname, *reference lsql);
9169 
9170 
9172 
9191  list getDropColumnSql(string cname, *hash opt);
9192 
9193 
9195 
9208  *hash insertCommit(hash row);
9209 
9210 
9212  *hash insertCommit(hash row, reference<string> sql);
9213 
9214 
9216  *hash insertCommit(hash row, hash opt);
9217 
9218 
9220  *hash insertCommit(hash row, reference<string> sql, hash opt);
9221 
9222 
9224 
9238  *hash insert(hash row);
9239 
9240 
9242  *hash insert(hash row, reference<string> sql);
9243 
9244 
9246  *hash insert(hash row, hash opt);
9247 
9248 
9250  *hash insert(hash row, reference<string> sql, hash opt);
9251 
9252 
9254  deprecated *hash insertNoCommit(hash row, *reference<string> sql, *hash opt);
9255 
9257  deprecated *hash insertNoCommit(hash row, hash opt);
9258 
9259 
9260 private:
9261  *hash insertIntern(hash row, *reference<string> sql, *hash opt);
9262 public:
9263 
9264 
9265 
9266 private:
9267  hash getPlaceholdersAndValues(hash row);
9268 public:
9269 
9270 
9272 
9275  bool hasReturning();
9276 
9277 
9279 
9298  int insertFromSelectCommit(list cols, AbstractTable source, hash sh, reference<string> sql, hash opt);
9299 
9300 
9302  int insertFromSelectCommit(list cols, AbstractTable source);
9303 
9304 
9306  int insertFromSelectCommit(list cols, AbstractTable source, hash sh);
9307 
9308 
9310  int insertFromSelectCommit(list cols, AbstractTable source, hash sh, reference<string> sql);
9311 
9312 
9314  int insertFromSelectCommit(list cols, AbstractTable source, hash sh, hash opt);
9315 
9316 
9318 
9337  int insertFromSelect(list cols, AbstractTable source, hash sh, reference<string> sql, hash opt);
9338 
9339 
9341  int insertFromSelect(list cols, AbstractTable source);
9342 
9343 
9345  int insertFromSelect(list cols, AbstractTable source, hash sh);
9346 
9347 
9349  int insertFromSelect(list cols, AbstractTable source, hash sh, reference<string> sql);
9350 
9351 
9353  int insertFromSelect(list cols, AbstractTable source, hash sh, hash opt);
9354 
9355 
9357  deprecated int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *reference<string> sql, *hash opt);
9358 
9359 
9360 private:
9361  int insertFromSelectIntern(list cols, AbstractTable source, *hash sh, *reference<string> sql, *hash opt);
9362 public:
9363 
9364 
9366 
9385 
9386 
9388 
9407 
9408 
9410  deprecated int insertFromIteratorNoCommit(Qore::AbstractIterator i, *hash opt);
9411 
9412 
9413 private:
9414  int insertFromIteratorIntern(Qore::AbstractIterator i, *hash opt);
9415 public:
9416 
9417 
9419 
9435  int upsertCommit(hash row, int upsert_strategy = UpsertAuto, *hash opt);
9436 
9437 
9439 
9455  int upsert(hash row, int upsert_strategy = UpsertAuto, *hash opt);
9456 
9457 
9459  deprecated int upsertNoCommit(hash row, int upsert_strategy = UpsertAuto);
9460 
9462 
9483  code getUpsertClosure(hash row, int upsert_strategy = UpsertAuto, *hash opt);
9484 
9485 
9487 
9514  code getBulkUpsertClosure(hash example_row, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9515 
9516 
9518 
9539  code getUpsertClosureWithValidation(hash example_row, int upsert_strategy = UpsertAuto, *hash opt);
9540 
9541 
9543 
9576 
9577 
9579 
9612 
9613 
9615  deprecated *hash upsertFromIteratorNoCommit(Qore::AbstractIterator i, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9616 
9617 
9618 private:
9619  *hash upsertFromIteratorIntern(Qore::AbstractIterator i, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9620 public:
9621 
9622 
9623 
9624 private:
9625  *hash doDeleteOthersIntern(hash pkh, *hash opt);
9626 public:
9627 
9628 
9630 
9668  *hash upsertFromSelectCommit(AbstractTable t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9669 
9670 
9672  *hash upsertFromSelectCommit(Table t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9673 
9674 
9676 
9716  *hash upsertFromSelect(AbstractTable t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9717 
9718 
9720  deprecated *hash upsertFromSelectNoCommit(AbstractTable t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9721 
9723  deprecated *hash upsertFromSelect(Table t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9724 
9726  deprecated *hash upsertFromSelectNoCommit(Table t, *hash sh, int upsert_strategy = AbstractTable::UpsertAuto, *hash opt);
9727 
9729 
9740  softint rowCount();
9741 
9742 
9744 
9762  Qore::SQL::SQLStatement getRowIterator(*hash sh, *reference<string> sql, *hash opt);
9763 
9764 
9765 
9766 private:
9767  Qore::SQL::SQLStatement getRowIteratorIntern(*hash sh, *reference<string> sql, *hash opt);
9768 public:
9769 
9770 
9772 
9790 
9791 
9793 
9812  *hash selectRow(*hash sh, *reference<string> sql, *hash opt);
9813 
9814 
9816 
9834  *list selectRows(*hash sh, *reference<string> sql, *hash opt);
9835 
9836 
9838 
9856  *hash select(*hash sh, *reference<string> sql, *hash opt);
9857 
9858 
9860 
9878  *hash selectRow(*hash sh, *hash opt);
9879 
9880 
9882 
9899  *list selectRows(*hash sh, *hash opt);
9900 
9901 
9903 
9920  *hash select(*hash sh, *hash opt);
9921 
9922 
9924 
9942  string getSelectSql(*hash sh, *reference<list> args);
9943 
9944 
9945  *AbstractUniqueConstraint matchAnyUnique(list cols);
9946 
9947 
9948  string getSelectSqlIntern(*hash qh, reference<list> args, *hash opt);
9949 
9950 
9951  string getSelectSqlUnlocked(*hash qh, reference<list> args, *hash opt);
9952 
9953 
9954  // column & table information must be retrieved before calling this function
9955  string getSelectSqlUnlockedIntern(*hash qh, string from, reference<list> args, *hash ch, *hash opt);
9956 
9957 
9958 
9959 private:
9960  string getFromIntern(string from, *hash qh);
9961 public:
9962 
9963 
9964 
9965 private:
9966  list getGroupByListUnlocked(hash qh, *hash jch, *hash ch, *hash psch, list coll);
9967 public:
9968 
9969 
9970 
9971 private:
9972  list getOrderByListUnlocked(hash qh, *hash jch, *hash ch, *hash psch, list coll);
9973 public:
9974 
9975 
9976 
9977 private:
9978  list getGroupOrderByListUnlocked(string key, hash qh, *hash jch, *hash ch, *hash psch, list coll);
9979 public:
9980 
9981 
9982 
9983 private:
9984  doForUpdate(reference<string> sql);
9985 public:
9986 
9987 
9988 
9989 private:
9990  string getSelectSqlName(*hash qh);
9991 public:
9992 
9993 
9994 
9995 private:
9996  string getColumnExpressionIntern(auto cvc, *hash jch, bool join, *hash ch, *hash psch);
9997 public:
9998 
9999 
10000 
10001 private:
10002  string doColumnOperatorIntern(hash cvc, *hash jch, bool join, *hash ch, *hash psch, *reference psch_ref);
10003 public:
10004 
10005 
10006 
10007 private:
10008  string doColumnOperatorIntern(auto cop, auto arg, *string cve, hash cm, *hash jch, bool join, *hash ch, *hash psch, *reference psch_ref);
10009 public:
10010 
10011 
10012 
10013 private:
10014  string getColumnNameIntern(string cv, *hash jch, bool join, *hash ch, *hash psch);
10015 public:
10016 
10017 
10019 
10020 private:
10021  bool asteriskRequiresPrefix();
10022 public:
10023 
10024 
10025 
10026 private:
10027  getSelectWhereSqlUnlocked(reference<string> sql, reference<list> args, *hash qh, *hash jch, bool join = False, *hash ch, *hash psch);
10028 public:
10029 
10030 
10031 
10032 private:
10033  *string getWhereClause(*hash cond, reference<list> args, *string cprefix, *hash jch, bool join = False);
10034 public:
10035 
10036 
10037 
10038 private:
10039  *string getWhereClause(list cond, reference<list> args, *string cprefix, *hash jch, bool join = False);
10040 public:
10041 
10042 
10043 
10044 private:
10045  *string getWhereClauseUnlocked(list cond, reference<list> args, *string cprefix, *hash jch, bool join = False, *hash pch, *hash psch);
10046 public:
10047 
10048 
10049 
10050 private:
10051  *string getWhereClauseUnlocked(*hash cond, reference<list> args, *string cprefix, *hash jch, bool join = False, *hash pch, *hash psch);
10052 public:
10053 
10054 
10055 
10056 private:
10057  *list getWhereClauseIntern(*hash cond, reference<list> args, *string cprefix, *hash jch, bool join = False, *hash ch, *hash psch);
10058 public:
10059 
10060 
10061 
10062 private:
10063  string doWhereExpressionIntern(string cn, auto we, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch);
10064 public:
10065 
10066 
10067  string getOrClause(list arglist, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch);
10068 
10069 
10070  string getOrClause(hash arg, reference<list> args, *hash jch, bool join = False, *hash ch, *hash psch);
10071 
10072 
10073 
10074 private:
10075  doSelectOrderBySqlUnlocked(reference<string> sql, reference<list> args, *hash qh, *hash jch, *hash ch, *hash psch, list coll);
10076 public:
10077 
10078 
10080 
10095  int delCommit(hash cond, reference<string> sql, hash opt);
10096 
10097 
10099  int delCommit(hash cond, hash opt);
10100 
10101 
10103  int delCommit(hash cond, reference<string> sql);
10104 
10105 
10107  int delCommit(hash cond);
10108 
10109 
10111  int delCommit();
10112 
10113 
10115 
10130  int del(hash cond, reference<string> sql, hash opt);
10131 
10132 
10134  int del(hash cond, hash opt);
10135 
10136 
10138  int del(hash cond, reference<string> sql);
10139 
10140 
10142  int del(hash cond);
10143 
10144 
10146  int del();
10147 
10148 
10150  deprecated int delNoCommit(*hash cond, *reference<string> sql);
10151 
10152 
10153 private:
10154  int delIntern(*hash cond, *reference<string> sql, *hash opt);
10155 public:
10156 
10157 
10159 
10176  int updateCommit(hash set, hash cond, reference<string> sql, hash opt);
10177 
10178 
10180  int updateCommit(hash set, hash cond, reference<string> sql);
10181 
10182 
10184  int updateCommit(hash set, hash cond, hash opt);
10185 
10186 
10188  int updateCommit(hash set, hash cond);
10189 
10190 
10192  int updateCommit(hash set);
10193 
10194 
10196 
10213  int update(hash set, hash cond, reference<string> sql, hash opt);
10214 
10215 
10217  int update(hash set, hash cond, reference<string> sql);
10218 
10219 
10221  int update(hash set, hash cond, hash opt);
10222 
10223 
10225  int update(hash set, hash cond);
10226 
10227 
10229  int update(hash set);
10230 
10231 
10233  deprecated int updateNoCommit(hash set, *hash cond, *reference<string> sql);
10234 
10236  deprecated int updateNoCommit(hash set, *hash cond, *hash opt);
10237 
10238 
10239 private:
10240  int updateIntern(hash set, *hash cond, *reference<string> sql, *hash opt);
10241 public:
10242 
10243 
10244 
10245 private:
10246  string getUpdateExpression(string col, hash<UpdateOperatorInfo> uh);
10247 public:
10248 
10249 
10250 
10251 private:
10252  bool emptyDataIntern();
10253 public:
10254 
10255 
10256 
10257 private:
10258  Columns checkUpsertRow(hash row, reference<int> upsert_strategy);
10259 public:
10260 
10261 
10262 
10263 private:
10264  code getUpsertInsertFirst(Columns cols, hash example_row, *hash opt);
10265 public:
10266 
10267 
10268 
10269 private:
10270  code getUpsertUpdateFirst(Columns cols, hash example_row, *hash opt);
10271 public:
10272 
10273 
10274 
10275 private:
10276  code getUpsertSelectFirst(Columns cols, hash example_row, *hash opt);
10277 public:
10278 
10279 
10280 
10281 private:
10282  code getUpsertInsertOnly(Columns cols, hash example_row, *hash opt);
10283 public:
10284 
10285 
10286 
10287 private:
10288  code getUpsertUpdateOnly(Columns cols, hash example_row, *hash opt);
10289 public:
10290 
10291 
10292 
10293 private:
10294  Columns getUpsertColumns(reference csrc);
10295 public:
10296 
10297 
10298 
10299 private:
10300  string getUpsertSelectSql(hash row, Columns cols, reference<list<string>> updc);
10301 public:
10302 
10303 
10304 
10305 private:
10306  string getUpsertInsertSql(hash row);
10307 public:
10308 
10309 
10310 
10311 private:
10312  string getUpsertUpdateSql(hash row, Columns cols, reference updc, *hash opt);
10313 public:
10314 
10315 
10316 
10317 private:
10318  softbool tryUpdate(string sql, hash row, Columns cols, list updc);
10319 public:
10320 
10321 
10322 
10323 private:
10324  checkValue(string cname, string argname, reference val, string type);
10325 public:
10326 
10327 
10329 
10338  string getSqlFromList(list l);
10339 
10340 
10342 
10353  string getSqlValue(auto v);
10354 
10355 
10357  string getName();
10358 
10359 
10361 
10368  cache(*hash opts);
10369 
10370 
10372 
10377  clear();
10378 
10379 
10381 
10388  Columns describe();
10389 
10390 
10392 
10401 
10402 
10404 
10414 
10415 
10416  *AbstractUniqueConstraint findUniqueConstraintUnlocked(string name);
10417 
10418 
10420 
10429  Indexes getIndexes();
10430 
10431 
10434 
10435 
10438 
10439 
10441 
10451 
10452 
10454 
10472  string getRenameSql(string new_name, *hash opt);
10473 
10474 
10476 
10485  string getCreateSqlString(*hash opt);
10486 
10487 
10489 
10498  list getCreateSql(*hash opt);
10499 
10500 
10502 
10513  string getCreateTableSql(*hash opt);
10514 
10515 
10517 
10521  bool checkExistence();
10522 
10523 
10524 
10525 private:
10526  *hash getCheckOmissionOptions(*softlist ol, string err);
10527 public:
10528 
10529 
10531 
10547 
10548 
10549 
10550 private:
10551  list getAlignSqlUnlocked(AbstractTable t, *hash opt);
10552 public:
10553 
10554 
10555 
10556 private:
10557  *AbstractColumnSupportingConstraint getSupportingConstraint(string ixname);
10558 public:
10559 
10560 
10561 
10562 private:
10563  renameIndexUnlocked(AbstractIndex ix, string new_name);
10564 public:
10565 
10566 
10568 
10581  string getAlignSqlString(AbstractTable t, *hash opt);
10582 
10583 
10585 
10597  *list getCreateIndexesSql(*hash opt, bool cache = True);
10598 
10599 
10601 
10613  *string getCreatePrimaryKeySql(*hash opt, bool cache = True);
10614 
10615 
10617 
10629  *list getCreateForeignConstraintsSql(*hash opt, bool cache = True);
10630 
10631 
10633 
10647  *list getCreateConstraintsSql(*hash opt, bool cache = True);
10648 
10649 
10651 
10663  *list getCreateMiscSql(*hash opt, bool cache = True);
10664 
10665 
10667 
10681  *list getCreateTriggersSql(*hash opt, bool cache = True);
10682 
10683 
10685 
10692  *hash find(auto id);
10693 
10694 
10696 
10707  *list find(list ids);
10708 
10709 
10710 
10711 private:
10712  string getPrimaryKeyColumn();
10713 public:
10714 
10715 
10717 
10730  *hash find(hash row);
10731 
10732 
10734 
10747  *hash findSingle(*hash cond);
10748 
10749 
10751 
10764  *list findAll(*hash cond);
10765 
10766 
10768 
10772  string getDesc();
10773 
10774 
10776  string getBaseType();
10777 
10778 
10780  string getSqlName();
10781 
10782 
10784  string getColumnSqlName(string col);
10785 
10786 
10788  list getColumnSqlNames(softlist cols);
10789 
10790 
10792 
10794  bool bindEmptyStringsAsNull();
10795 
10796 
10798  abstract bool hasArrayBind();
10799 
10801 
10804 private:
10806 public:
10807 
10808 
10810 
10813 private:
10815 public:
10816 
10817 
10819 
10822 private:
10824 public:
10825 
10826 
10828 
10831 private:
10833 public:
10834 
10835 
10837 
10840 private:
10842 public:
10843 
10844 
10846 
10849 private:
10851 public:
10852 
10853 
10855 
10858 private:
10860 public:
10861 
10862 
10864 
10867 private:
10869 public:
10870 
10871 
10873 
10876 private:
10878 public:
10879 
10880 
10882 
10885 private:
10887 public:
10888 
10889 
10891 
10894 private:
10896 public:
10897 
10898 
10900 
10903 private:
10905 public:
10906 
10907 
10909 
10912 private:
10914 public:
10915 
10916 
10918 
10921 private:
10923 public:
10924 
10925 
10927 
10930 private:
10932 public:
10933 
10934 
10936 
10939 private:
10941 public:
10942 
10943 
10945 
10948 private:
10950 public:
10951 
10952 
10954 
10957 private:
10959 public:
10960 
10961 
10963 
10966 private:
10968 public:
10969 
10970 
10972 
10975 private:
10977 public:
10978 
10979 
10981 
10984 private:
10986 public:
10987 
10988 
10990 
10993 private:
10995 public:
10996 
10997 
10999 
11002 private:
11004 public:
11005 
11006 
11007 
11008 private:
11009  string getCreateTableSqlUnlocked(*hash opt);
11010 public:
11011 
11012 
11013 
11014 private:
11015  *list getCreateIndexesSqlUnlocked(*hash opt, bool cache = True);
11016 public:
11017 
11018 
11019 
11020 private:
11021  *string getCreatePrimaryKeySqlUnlocked(*hash opt, bool cache = True);
11022 public:
11023 
11024 
11025 
11026 private:
11027  *list getCreateConstraintsSqlUnlocked(*hash opt, bool cache = True);
11028 public:
11029 
11030 
11031 
11032 private:
11033  *list getCreateForeignConstraintsSqlUnlocked(*hash opt, bool cache = True);
11034 public:
11035 
11036 
11037 
11038 private:
11039  *list getCreateMiscSqlUnlocked(*hash opt, bool cache = True);
11040 public:
11041 
11042 
11043 
11044 private:
11045  *list getCreateTriggersSqlUnlocked(*hash opt, bool cache = True);
11046 public:
11047 
11048 
11049 
11050 private:
11051  list getCreateSqlUnlocked(*hash opt, bool cache = True);
11052 public:
11053 
11054 
11055 
11056 private:
11057  cacheUnlocked(*hash opt);
11058 public:
11059 
11060 
11061 
11062 private:
11063  auto execData(*hash opt, string sql, *list args);
11064 public:
11065 
11066 
11067 
11068 private:
11069  execData(SQLStatement stmt, *hash opt, *list args);
11070 public:
11071 
11072 
11073  static AbstractTable getTable(AbstractDatasource nds, string nname, *hash opts);
11074 
11075  static AbstractTable getTable(string dsstr, string nname, *hash opts);
11076 
11077  static AbstractTable getTable(hash dsh, string nname, *hash opts);
11078 
11079 
11080 private:
11081  getColumnsUnlocked();
11082 public:
11083 
11084 
11085 
11086 private:
11087  getPrimaryKeyUnlocked();
11088 public:
11089 
11090 
11091  // also loads primary key and constraints (for unique constraints)
11092 
11093 private:
11094  getIndexesUnlocked();
11095 public:
11096 
11097 
11098 
11099 private:
11100  getForeignConstraintsUnlocked(*hash opt);
11101 public:
11102 
11103 
11104 
11105 private:
11106  addSourceConstraint(string table_name, AbstractForeignConstraint fk);
11107 public:
11108 
11109 
11110 
11111 private:
11112  getConstraintsUnlocked();
11113 public:
11114 
11115 
11116 
11117 private:
11118  getTriggersUnlocked();
11119 public:
11120 
11121 
11123 
11124 private:
11125  bool hasReturningImpl();
11126 public:
11127 
11128 
11129 
11130 private:
11131  softlist getDropSqlImpl();
11132 public:
11133 
11134 
11135 
11136 private:
11137  string getTruncateSqlImpl();
11138 public:
11139 
11140 
11142 
11143 private:
11144  auto tryExecArgsImpl(string sql, *softlist args);
11145 public:
11146 
11147 
11149 
11150 private:
11151  auto tryExecRawImpl(string sql);
11152 public:
11153 
11154 
11156 
11157 private:
11158  clearImpl();
11159 public:
11160 
11161 
11162 
11163 private:
11164  preSetupTableImpl(reference desc, *hash opt);
11165 public:
11166 
11167 
11168 
11169 private:
11170  abstract *hash doReturningImpl(hash opt, reference<string> sql, list args);
11171 public:
11172 
11173 
11174 private:
11175  abstract bool emptyImpl();
11176 public:
11177 
11179 
11180 private:
11181  abstract *string getSqlValueImpl(auto v);
11182 public:
11183 
11185 
11189 private:
11190  abstract bool checkExistenceImpl();
11191 public:
11192 
11194 
11195 private:
11196  abstract bool supportsTablespacesImpl();
11197 public:
11198 
11200 
11201 private:
11202  abstract bool constraintsLinkedToIndexesImpl();
11203 public:
11204 
11206 
11207 private:
11208  abstract bool uniqueIndexCreatesConstraintImpl();
11209 public:
11210 
11211 
11212 private:
11213  abstract setupTableImpl(hash desc, *hash opt);
11214 public:
11215 
11216 
11217 private:
11218  abstract Columns describeImpl();
11219 public:
11220 
11221 private:
11222  abstract AbstractPrimaryKey getPrimaryKeyImpl();
11223 public:
11224 
11225 private:
11226  abstract Indexes getIndexesImpl();
11227 public:
11228 
11229 private:
11230  abstract ForeignConstraints getForeignConstraintsImpl(*hash opt);
11231 public:
11232 
11233 private:
11234  abstract Constraints getConstraintsImpl();
11235 public:
11236 
11237 private:
11238  abstract Triggers getTriggersImpl();
11239 public:
11240 
11241 
11242 private:
11243  abstract string getCreateTableSqlImpl(*hash opt);
11244 public:
11245 
11246 private:
11247  abstract *list getCreateMiscSqlImpl(*hash opt, bool cache);
11248 public:
11249 
11250 private:
11251  abstract string getCreateSqlImpl(list l);
11252 public:
11253 
11254 private:
11255  abstract string getRenameSqlImpl(string new_name);
11256 public:
11257 
11258 private:
11259  abstract *list getAlignSqlImpl(AbstractTable t, *hash opt);
11260 public:
11261 
11262 
11263 private:
11264  abstract AbstractColumn addColumnImpl(string cname, hash opt, bool nullable = True);
11265 public:
11266 
11267 private:
11268  abstract AbstractPrimaryKey addPrimaryKeyImpl(string cname, hash ch, *hash opt);
11269 public:
11270 
11271 private:
11272  abstract AbstractIndex addIndexImpl(string iname, bool enabled, hash ch, *hash opt);
11273 public:
11274 
11275 private:
11276  abstract AbstractForeignConstraint addForeignConstraintImpl(string cname, hash ch, string table, hash tch, *hash opt);
11277 public:
11278 
11279 private:
11280  abstract AbstractCheckConstraint addCheckConstraintImpl(string cname, string src, *hash opt);
11281 public:
11282 
11283 private:
11284  abstract AbstractUniqueConstraint addUniqueConstraintImpl(string cname, hash ch, *hash opt);
11285 public:
11286 
11287 
11288 private:
11289  abstract AbstractTrigger addTriggerImpl(string tname, string src, *hash opt);
11290 public:
11291 
11293 
11294 private:
11295  abstract bool tryInsertImpl(string sql, hash row);
11296 public:
11297 
11299 
11300 private:
11301  abstract hash getQoreTypeMapImpl();
11302 public:
11303 
11305 
11306 private:
11307  abstract hash getTypeMapImpl();
11308 public:
11309 
11311 
11312 private:
11313  abstract doSelectOrderByWithOffsetSqlUnlockedImpl(reference<string> sql, reference<list> args, *hash qh, *hash jch, *hash ch, *hash psch, list coll);
11314 public:
11315 
11317 
11318 private:
11319  abstract doSelectLimitOnlyUnlockedImpl(reference<string> sql, reference<list> args, *hash qh);
11320 public:
11321 
11323 
11324 private:
11325  abstract copyImpl(AbstractTable old);
11326 public:
11327  };
11328 }
string name
the name of the constraint
Definition: SqlUtil.qm.dox.h:5757
hash< ColumnOperatorInfo > cop_first_value(any column)
Analytic/window function: value evaluated at the row that is the first row of the window frame...
softlist getDropSql(*hash opt)
returns the sql required to drop the table; reimplement in subclasses if necessary ...
const SelectOptions
default possible select options; can be extended by driver-specific modules
Definition: SqlUtil.qm.dox.h:7609
Constraints getConstraints()
returns a Constraints object describing the non-foreign constraints on the table
string name
the name of the sequence
Definition: SqlUtil.qm.dox.h:6044
const COP_SEQ
to return the next value of a sequence
Definition: SqlUtil.qm.dox.h:2315
any arg
optional argument
Definition: SqlUtil.qm.dox.h:2120
const Hash
any column
column sopecifier, may be a string or a complex hash
Definition: SqlUtil.qm.dox.h:2107
const UpsertAuto
Upsert option: if the target table is empty, use UpsertInsertFirst, otherwise use UpsertUpdateFirst...
Definition: SqlUtil.qm.dox.h:7811
const String
Qore::SQL::AbstractDatasource getDatasource()
gets the underlying AbstractDatasource
const DefaultIopMap
a hash of default insert operator descriptions (currently empty, all operators are driver-dependent) ...
Definition: SqlUtil.qm.dox.h:4785
abstract container class that throws an exception if an unknown key is accessed
Definition: SqlUtil.qm.dox.h:5123
hash< ColumnOperatorInfo > cop_append(auto column, string arg)
returns a ColumnOperatorInfo hash for the "append" operator with the given argument ...
const TableOmissionOptions
alignment omission options
Definition: SqlUtil.qm.dox.h:7627
string native_type
the native database column type; if both native_type and qore_type are given then native_type is used...
Definition: SqlUtil.qm.dox.h:2079
rename(string new_name, *reference< string > 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
string cop
the column operator string code
Definition: SqlUtil.qm.dox.h:2106
string sprintf(string fmt,...)
deprecated truncateNoCommit()
A legacy warpper for truncate()
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 hasReturningImpl()
returns True if the current database driver supports the "returning" clause in insert statements...
int delCommit()
SqlUtil::AbstractTable::delCommit() variant
const OP_IN
the SQL "in" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4303
deprecated createNoCommit(*hash opt)
A legacy wrapper for create()
hash getInsertOptions()
returns the insert options for this driver
bool checkExistence()
returns True if the table exists in the database, False if not
the base abstract class for the table implementation
Definition: SqlUtil.qm.dox.h:7559
const DefaultCopMap
a hash of default column operator descriptions
Definition: SqlUtil.qm.dox.h:2406
bool hasReturning()
returns True if the current database driver supports the "returning" clause in insert statements...
hash< ColumnOperatorInfo > cop_cast(auto column, string arg, auto arg1, auto arg2)
returns a ColumnOperatorInfo hash for the "cast" operator with the given argument(s) ...
const VARCHAR
specifies a VARCHAR column (equivalent to Qore::Type::String)
Definition: SqlUtil.qm.dox.h:2166
const ForeignConstraintOptions
default foreign constraint options
Definition: SqlUtil.qm.dox.h:7597
const COP_FIRST_VALUE
Analytic (window) function: FIRST_VALUE.
Definition: SqlUtil.qm.dox.h:2367
hash< ColumnOperatorInfo > cop_multiply(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "*" operator with the given arguments
*hash insertCommit(hash row)
inserts a row into the table; the transaction is committed if successful, if an error occurs...
const ColumnOptions
Column options; this is currently empty and can be extended in database-specific modules.
Definition: SqlUtil.qm.dox.h:7716
auto tryExecArgsImpl(string sql, *softlist args)
tries to execute a command so that if an error occurs the current transaction status is not lost ...
the table container class stores a collection of tables in a schema
Definition: SqlUtil.qm.dox.h:5215
validateColumnOptions(string cname, reference< hash > opt, bool nullable)
validates column options
Qore::AbstractIterator getUniqueConstraintIterator()
returns an iterator for all unique constraints on the table (including the primary key if any) ...
bool updatable
Flag showing if is the view updatable with DML commands.
Definition: SqlUtil.qm.dox.h:6092
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 getIndexOptions()
returns the index options for this driver
const UpsertResultLetterMap
maps upsert result codes to single letter symbols
Definition: SqlUtil.qm.dox.h:7895
const UpsertStrategyMap
hash mapping upsert strategy codes to a text description
Definition: SqlUtil.qm.dox.h:7830
dropCommit(*hash opt)
drops the table from the database; releases the transaction lock after dropping the table ...
insert operator info hash as returned by all insert operator functions
Definition: SqlUtil.qm.dox.h:2112
const UR_Inserted
row was inserted
Definition: SqlUtil.qm.dox.h:7857
hash< OperatorInfo > op_ne(auto arg)
returns an OperatorInfo hash for the "!=" or "<>" operator with the given argument for use in where c...
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:4298
string getSqlValue(auto v)
returns a string for use in SQL queries representing the DB-specific value of the argument ...
*AbstractColumnSupportingConstraint constraint
the AbstractColumnSupportingConstraint that this index supports, if any
Definition: SqlUtil.qm.dox.h:5651
deprecated int delNoCommit(*hash cond, *reference< string > sql)
A legacy SqlUtil::AbstractTable::del() wrapper.
*hash upsertFromSelectCommit(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...
hash< ColumnOperatorInfo > make_cop(string cop, auto column, auto arg)
returns a ColumnOperatorInfo hash
any arg
optional argument
Definition: SqlUtil.qm.dox.h:2114
the abstract base class for index information
Definition: SqlUtil.qm.dox.h:5634
const OP_OR
to combine SQL expressions with "or" for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4316
Triggers getTriggers()
returns an object of class Triggers describing the triggers on the table
const COP_OVER
the SQL "over" clause
Definition: SqlUtil.qm.dox.h:2270
hash< ColumnOperatorInfo > cop_as(auto column, string arg)
returns a ColumnOperatorInfo hash for the "as" operator with the given argument
auto tryExec(string sql)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
foreign constraint container class that throws an exception if an unknown constraint is accessed ...
Definition: SqlUtil.qm.dox.h:5949
const UpsertResultDescriptionMap
hash mapping upsert descriptions to codes
Definition: SqlUtil.qm.dox.h:7886
hash< ColumnOperatorInfo > cop_minus(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "-" operator with the given arguments
const UR_Deleted
row was deleted (only possible with batch upsert methods such as AbstractTable::upsertFromIterator() ...
Definition: SqlUtil.qm.dox.h:7869
int insertFromIteratorCommit(Qore::AbstractIterator i, *hash opt)
this method inserts data from the given iterator argument (whose getValue() method must return a hash...
const InsertFromIteratorOptions
default insert option keys
Definition: SqlUtil.qm.dox.h:7761
hash< ColumnOperatorInfo > cop_last_value(any column)
Analytic/window function: value evaluated at the row that is the last row of the window frame...
const COP_DENSE_RANK
Analytic (window) function: DENSE_RANK.
Definition: SqlUtil.qm.dox.h:2360
deprecated int upsertNoCommit(hash row, int upsert_strategy=UpsertAuto)
A legacy SqlUtil::AbstractTable::upsert() wrapper.
const OP_NE
the SQL not equals operator (!= or <>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4258
AbstractColumn dropColumn(string cname, *reference lsql)
drops a column from the table
hash< ColumnOperatorInfo > cop_rank()
Analytic/window function: rank of the current row with gaps.
deprecated *hash insertNoCommit(hash row, *reference< string > sql, *hash opt)
A legacy wrapper for SqlUtil::AbstractTable::insert()
constructor(AbstractDatasource nds, string nname, *hash nopts)
creates the object; private constructor
hash< InsertOperatorInfo > iop_seq_currval(string arg)
returns an InsertOperatorInfo hash for retrieving the current value of the given sequence in insert q...
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
const COP_SUM
to return the sum value
Definition: SqlUtil.qm.dox.h:2260
list getAlignSql(AbstractTable t, *hash opt)
accepts an AbstractTable argument and returns a list of SQL strings required to align the structure a...
truncateCommit()
truncates all the table data; releases the transaction lock after executing
string name
the name of the object
Definition: SqlUtil.qm.dox.h:6122
string getAlignSqlString(AbstractTable t, *hash opt)
accepts an AbstractTable argument and returns an SQL string that could be executed to align the struc...
a class describing a foreign constraint target
Definition: SqlUtil.qm.dox.h:5996
hash< ColumnOperatorInfo > cop_length(auto column)
returns a ColumnOperatorInfo hash for the "len" operator with the given argument; returns the length ...
*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 qore_type
the equivalent qore type name of the column if known
Definition: SqlUtil.qm.dox.h:5466
const COP_SEQ_CURRVAL
to return the last value of a sequence issued in the same session
Definition: SqlUtil.qm.dox.h:2320
the base class to use to extend AbstractColumn to implement numeric columns
Definition: SqlUtil.qm.dox.h:5570
int upsert(hash row, int upsert_strategy=UpsertAuto, *hash opt)
update or insert the data in the table according to the hash argument; the table must have a unique k...
const True
hash< InsertOperatorInfo > make_iop(string iop, auto arg)
returns an InsertOperatorInfo hash
const JopMap
a hash of valid join operators
Definition: SqlUtil.qm.dox.h:3759
hash getTableCreationOptions()
returns the table creation options for this driver
const SZ_MAND
the data type takes a mandatory size parameter
Definition: SqlUtil.qm.dox.h:2189
const OP_GT
the SQL greater than operator (>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4248
const CHAR
specifies a CHAR column
Definition: SqlUtil.qm.dox.h:2172
const COP_AVG
to return the average value
Definition: SqlUtil.qm.dox.h:2255
const DB_SEQUENCES
Feature: sequences.
Definition: SqlUtil.qm.dox.h:2150
const DB_MVIEWS
Feature: materialized views / snapshots.
Definition: SqlUtil.qm.dox.h:2144
const JOP_LEFT
for left outer joins
Definition: SqlUtil.qm.dox.h:3751
base class for sequences
Definition: SqlUtil.qm.dox.h:6039
*hash upsertFromIteratorCommit(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...
*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 ...
const COP_LAST_VALUE
Analytic (window) function: LAST_VALUE.
Definition: SqlUtil.qm.dox.h:2374
hash< OperatorInfo > op_clt(string arg)
returns an OperatorInfo hash for the "<" operator with the given argument for use in where clauses wh...
nothing flush()
bool nullable
True if the column can hold a NULL value, False if not
Definition: SqlUtil.qm.dox.h:5472
abstract hash getQoreTypeMapImpl()
returns the qore type -> column type map
any arg
optional argument
Definition: SqlUtil.qm.dox.h:2101
const UpsertOptions
default upsert option keys
Definition: SqlUtil.qm.dox.h:7749
number number(softnumber n)
const COP_DISTINCT
to return distinct values
Definition: SqlUtil.qm.dox.h:2240
hash< OperatorInfo > op_cge(string arg)
returns an OperatorInfo hash for the ">=" operator with the given argument for use in where clauses w...
hash getTriggerOptions()
returns the trigger options for this driver
const COP_YEAR_HOUR
to return a date value with year to hextern information
Definition: SqlUtil.qm.dox.h:2310
const CacheOptions
default cache options
Definition: SqlUtil.qm.dox.h:7589
*hash opt
optional join options (for example, to specify a partition for the join if supported) ...
Definition: SqlUtil.qm.dox.h:2132
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
ForeignConstraints foreignConstraints
foreign constraints description
Definition: SqlUtil.qm.dox.h:7915
bool native_case
native case option
Definition: SqlUtil.qm.dox.h:7921
*list selectRows(*hash sh, *reference< string > sql, *hash opt)
returns a list of hashes representing the rows in the table that match the argument hash ...
hash< UpdateOperatorInfo > uop_multiply(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "*" operator with the given arguments
string getTruncateSql(*hash opt)
gets the SQL that can be used to truncate the table
hash< ColumnOperatorInfo > cop_plus(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "+" operator with the given arguments
AbstractConstraint dropConstraint(string cname, *reference< string > sql)
drops a constraint from the table; this can be any constraint on the table, a primary key...
hash< ColumnOperatorInfo > cop_value(auto arg)
returns a ColumnOperatorInfo hash for the "value" (literal) operator with the given argument ...
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...
any table
the table to join with (either an AbstractTable object or a string table name)
Definition: SqlUtil.qm.dox.h:2127
hash< UpdateOperatorInfo > uop_substr(int start, *int count, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "substr" operator with the given arguments; returns a subs...
list getDropPrimaryKeySql(*hash opt)
gets a list of SQL strings that can be used to drop the primary key from the table ...
string jop
the join operator string code
Definition: SqlUtil.qm.dox.h:2126
const IOP_SEQ_CURRVAL
for using the last value of a sequence issued in the current session
Definition: SqlUtil.qm.dox.h:4782
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:6362
abstract doSelectOrderByWithOffsetSqlUnlockedImpl(reference< string > sql, reference< list > args, *hash qh, *hash jch, *hash ch, *hash psch, list coll)
processes a string for use in SQL select statements when there is an "order by" and "offset" argument...
hash getAlignTableOptions()
returns the align table options for this driver
const UpsertUpdateFirst
Upsert option: update first, if the update fails, then insert.
Definition: SqlUtil.qm.dox.h:7795
Qore::SQL::SQLStatement getRowIterator(*hash sh, *reference< string > sql, *hash opt)
returns an SQLStatement object that will iterate the results of a select statement matching the argum...
hash< ColumnOperatorInfo > cop_year_day(auto column)
returns a ColumnOperatorInfo hash for the "year_day" operator with the given argument ...
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
string getColumnSqlName(string col)
returns the column name for use in SQL strings; subclasses can return a special string in case the co...
const NULL
create(*hash opt)
creates the table with all associated properties (indexes, constraints, etc) without any transaction ...
const OP_CNE
the SQL not equals operator (!= or <>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4288
auto tryExecRawImpl(string sql)
tries to execute a command so that if an error occurs the current transaction status is not lost ...
hash< UpdateOperatorInfo > uop_prepend(string arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "prepend" operator with the given argument ...
represents a database; this class embeds an AbstractDatabase object that is created automatically in ...
Definition: SqlUtil.qm.dox.h:6292
const SqlUtilDrivers
known drivers
Definition: SqlUtil.qm.dox.h:4832
const OP_CGT
the SQL greater than operator (>) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4278
the base class for foreign key constraint information
Definition: SqlUtil.qm.dox.h:6018
ForeignConstraintTarget target
a ForeignConstraintTarget object to describe the target table and columns
Definition: SqlUtil.qm.dox.h:6023
hash< OperatorInfo > op_le(auto arg)
returns an OperatorInfo hash for the "<=" operator with the given argument for use in where clauses w...
deprecated *hash upsertFromSelectNoCommit(AbstractTable t, *hash sh, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
A legacy SqlUtil::AbstractTable::upsertFromSelect() wrapper.
const False
Mutex l()
mutex for atomic actions
deprecated dropNoCommit(*hash opt)
A legacy wrapper for drop()
int insertFromSelect(list cols, AbstractTable source, hash sh, reference< string > sql, hash opt)
inserts rows into a table based on a select statement from another table (which must be using the sam...
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
AbstractIndex renameIndex(string old_name, string new_name, reference< string > sql)
renames an existing index; if the table is already known to be in the database, then the changes are ...
hash< string, hash< JoinOperatorInfo > > join_right_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for right outer joins with the given arguments for use when joining with a table other...
int scale
the scale for numeric columns
Definition: SqlUtil.qm.dox.h:5575
abstract hash getTypeMapImpl()
returns the type name -> type description hash
hash< string, hash< JoinOperatorInfo > > join_inner(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for standard inner joins with the given arguments
code getUpsertClosureWithValidation(hash example_row, int upsert_strategy=UpsertAuto, *hash opt)
returns a closure that can be executed given a hash argument representing a single row that will be u...
abstract class for check constraints
Definition: SqlUtil.qm.dox.h:5813
hash< UpdateOperatorInfo > uop_divide(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "/" operator with the given arguments
const COP_COUNT
to return the row count
Definition: SqlUtil.qm.dox.h:2265
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 ...
const OP_CEQ
the SQL equals operator (=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4293
const COP_NTILE
Analytic (window) function: NTILE.
Definition: SqlUtil.qm.dox.h:2381
hash< UpdateOperatorInfo > uop_plus(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "+" operator with the given arguments
softint scale
for numeric data types, this value gives the scale
Definition: SqlUtil.qm.dox.h:2083
abstract container class that throws an exception if an unknown key is accessed
Definition: SqlUtil.qm.dox.h:4841
hash< ColumnOperatorInfo > cop_year_hour(auto column)
returns a ColumnOperatorInfo hash for the "year_hour" operator with the given argument ...
join operator info hash as returned by all join operator functions
Definition: SqlUtil.qm.dox.h:2125
any default_value
the default value for the column
Definition: SqlUtil.qm.dox.h:2085
the SqlUtil namespace contains all the objects in the SqlUtil module
Definition: SqlUtil.qm.dox.h:2073
bool empty()
returns True if the table has no definitions, False if not
string name
the table&#39;s name
Definition: SqlUtil.qm.dox.h:7907
hash< OperatorInfo > op_cne(string arg)
returns an OperatorInfo hash for the "!=" or "<>" operator with the given argument for use in where c...
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:6244
int updateCommit(hash set, hash cond, reference< string > sql, hash opt)
updates rows in the table matching an optional condition and returns the count of rows updated; the t...
any arg
optional argument
Definition: SqlUtil.qm.dox.h:2108
string getSelectSql(*hash sh, *reference< list > args)
returns the SQL string to be executed corresponding to the argument hash with an output parameter for...
int index(softstring str, softstring substr, softint pos=0)
string src
the source of the check clause
Definition: SqlUtil.qm.dox.h:5818
string getDatasourceDesc()
returns a descriptive string for the datasource
AbstractIndex addIndex(string iname, bool unique, softlist cols, *hash opt, *reference< string > sql)
adds an index to the table; if the table is already known to be in the database, then it is added in ...
Indexes indexes
index descriptions
Definition: SqlUtil.qm.dox.h:7913
const DT_DAY
Format unit: day.
Definition: SqlUtil.qm.dox.h:3481
hash< OperatorInfo > op_cle(string arg)
returns an OperatorInfo hash for the "<=" operator with the given argument for use in where clauses w...
const DB_PACKAGES
Feature: packages.
Definition: SqlUtil.qm.dox.h:2146
hash< ColumnOperatorInfo > cop_avg(auto column)
returns a ColumnOperatorInfo hash for the "avg" operator; returns average column values ...
const COP_UPPER
to return column value in upper case
Definition: SqlUtil.qm.dox.h:2230
hash< string, hash< JoinOperatorInfo > > join_right(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for right outer joins with the given arguments
hash getColumnDescOptions()
returns the column description options for this driver
deprecated *hash upsertFromIteratorNoCommit(Qore::AbstractIterator i, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
A legacy SqlUtik::AbstractTable::upsertFromIterator() wrapper.
code getUpsertClosure(hash row, int upsert_strategy=UpsertAuto, *hash opt)
returns a closure that can be executed given a hash argument representing a single row that will be u...
clearImpl()
clears any driver-specific table information
*hash cond
additional conditions for the join clause for the table argument; see Where Clauses for more informat...
Definition: SqlUtil.qm.dox.h:2130
const COP_PLUS
the SQL "plus" operator
Definition: SqlUtil.qm.dox.h:2280
const Boolean
const UR_Verified
row was updated unconditionally (not returned with UpsertSelectFirst)
Definition: SqlUtil.qm.dox.h:7860
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:4253
index container class that throws an exception if an unknown index is accessed
Definition: SqlUtil.qm.dox.h:5588
const SZ_NUM
the data type is numeric so takes an optional precision and scale
Definition: SqlUtil.qm.dox.h:2195
hash< OperatorInfo > op_between(auto l, auto r)
returns an OperatorInfo hash for the "between" operator with the given arguments, neither of which ca...
const TableOptions
table options
Definition: SqlUtil.qm.dox.h:7568
*bool auto_increment
True for DBs that support an auto-increment column
Definition: SqlUtil.qm.dox.h:2095
*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...
ForeignConstraints getForeignConstraints(*hash opt)
returns a ForeignConstraints object describing the foreign constraints that the table has on other ta...
const DB_SYNONYMS
Feature: synonyms.
Definition: SqlUtil.qm.dox.h:2158
*string def_val
default value for column
Definition: SqlUtil.qm.dox.h:5475
int insertFromIterator(Qore::AbstractIterator i, *hash opt)
this method inserts data from the given iterator argument (whose getValue() method must return a hash...
AbstractPrimaryKey primaryKey
primary key description
Definition: SqlUtil.qm.dox.h:7911
hash< UpdateOperatorInfo > uop_upper(*hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "upper" operator with the given argument; returns a column...
const COP_COALESCE
to return the first non-null argument in the list
Definition: SqlUtil.qm.dox.h:2325
string op
the operator string code
Definition: SqlUtil.qm.dox.h:2100
bool manual
manual edits
Definition: SqlUtil.qm.dox.h:7925
const AlignTableOptions
table alignment options
Definition: SqlUtil.qm.dox.h:7650
AbstractPrimaryKey getPrimaryKey()
returns an object of class AbstractPrimaryKey describing the primary key of the table ...
bool emptyData()
returns True if the table has no data rows, False if not
string getBaseType()
returns the base type of the underlying object (normally "table", some DB-specific implementations ma...
hash< ColumnOperatorInfo > cop_min(auto column)
returns a ColumnOperatorInfo hash for the "min" operator; returns minimum column values ...
*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...
abstract bool hasArrayBind()
returns True if the underlying DB driver supports bulk DML operations
hash< OperatorInfo > op_ge(auto arg)
returns an OperatorInfo hash for the ">=" operator with the given argument for use in where clauses w...
Triggers triggers
trigger descriptions
Definition: SqlUtil.qm.dox.h:7919
string getDesc()
returns a descriptive string of the datasource (without the password) and the table name (with a poss...
abstract *string getSqlValueImpl(auto v)
returns a string for use in SQL queries representing the DB-specific value of the argument; returns N...
const DB_PROCEDURES
Feature: procedures.
Definition: SqlUtil.qm.dox.h:2148
const CreationOptions
default generic creation options
Definition: SqlUtil.qm.dox.h:6556
const OP_EQ
the SQL equals operator (=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4263
validateHashKeysForWhitespaces(auto node)
Check input node for all hash keys - if it contains a key with whitespace in the beginning or at the ...
list getDropColumnSql(string cname, *hash opt)
returns the SQL that can be used to drop a column from the table
bool exists(...)
generic column description hash in schema descriptions
Definition: SqlUtil.qm.dox.h:2075
int del()
SqlUtil::AbstractTable::del() variant
*string comment
comment on the column
Definition: SqlUtil.qm.dox.h:5478
code getBulkUpsertClosure(hash example_row, int upsert_strategy=AbstractTable::UpsertAuto, *hash opt)
returns a closure that can be executed given a hash argument representing either a single row or a se...
hash< ColumnOperatorInfo > cop_coalesce(auto col1, auto col2)
returns a ColumnOperatorInfo hash for the "coalesce" operator with the given column arguments; the fi...
string src
the source code
Definition: SqlUtil.qm.dox.h:6089
bool unique
True if the index is a unique index, False if not
Definition: SqlUtil.qm.dox.h:5642
string getDropIndexSql(string iname, *hash opt)
gets the SQL that can be used to drop an index from the table
hash< ColumnOperatorInfo > cop_trunc_date(auto column, string mask)
Truncates a date column or value regarding the given mask. The resulting value remains Qore::date (no...
hash< ColumnOperatorInfo > cop_dense_rank()
Analytic/window function: rank of the current row without gaps.
hash< OperatorInfo > op_eq(auto arg)
returns an OperatorInfo hash for the "=" operator with the given argument for use in where clauses wh...
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
string getSqlName()
returns the name of the table to be used in SQL (with a possible qualifier for schema, etc)
*hash find(auto id)
finds a row in the table with the given primary key value; if no row matches the primary key value pa...
const COP_YEAR_MONTH
to return a date value with year to month information
Definition: SqlUtil.qm.dox.h:2300
string src
the source of the object
Definition: SqlUtil.qm.dox.h:6128
AbstractPrimaryKey addPrimaryKey(string pkname, softlist cols, *hash opt, *reference< string > 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 COP_MAX
to return the maximum value
Definition: SqlUtil.qm.dox.h:2250
hash< OperatorInfo > op_cgt(string arg)
returns an OperatorInfo hash for the ">" operator with the given argument for use in where clauses wh...
const COP_LENGTH
to get the length of a text field
Definition: SqlUtil.qm.dox.h:2337
auto tryExecArgs(string sql, *softlist args)
executes some SQL with optional arguments so that if an error occurs the current transaction state is...
const COP_AS
to rename a column on output
Definition: SqlUtil.qm.dox.h:2205
string getDriverName()
returns the database driver name
hash< string, hash< JoinOperatorInfo > > join_left_alias(string ta, string table_name, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for left outer joins with the given arguments for use when joining with a table other ...
Columns columns
column description object
Definition: SqlUtil.qm.dox.h:7909
*string getDropConstraintIfExistsSql(string cname, *hash opt, *reference< AbstractConstraint > cref)
gets the SQL that can be used to drop a constraint from the table if it exists, otherwise returns NOT...
const DB_VIEWS
Feature: views.
Definition: SqlUtil.qm.dox.h:2156
hash< ColumnOperatorInfo > cop_max(auto column)
returns a ColumnOperatorInfo hash for the "max" operator; returns maximum column values ...
AbstractTrigger dropTrigger(string tname, *reference< string > sql)
drops the given trigger from the table; if the table is known to be in the database already...
const OP_LE
the SQL less than or equals (<=) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4243
const BLOB
specifies a large variable-length binary column (ie BLOB or BYTEA, etc)
Definition: SqlUtil.qm.dox.h:2175
string name
the name of the index
Definition: SqlUtil.qm.dox.h:5639
string _iop
the insert operator string code
Definition: SqlUtil.qm.dox.h:2113
*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:6004
string uop
the update operator string code
Definition: SqlUtil.qm.dox.h:2119
drop(*hash opt)
drops the table from the database without any transaction management
const OP_CLE
the SQL less than or equals (<=) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4273
the API for a constraint with columns
Definition: SqlUtil.qm.dox.h:5843
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 ...
const DB_TABLES
Feature: tables.
Definition: SqlUtil.qm.dox.h:2152
abstract bool uniqueIndexCreatesConstraintImpl()
returns True if the database automatically creates a unique constraint when a unique index is created...
hash< UpdateOperatorInfo > uop_append(string arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "append" or concatenate operator with the given argument ...
createCommit(*hash opt)
creates the table in the database; releases the transaction lock after creating the table ...
hash< UpdateOperatorInfo > uop_seq_currval(string seq)
returns an UpdateOperatorInfo hash for the "seq" operator with the given argument giving the sequence...
hash< ColumnOperatorInfo > cop_ntile(int value)
Analytic/window function: integer ranging from 1 to the argument value, dividing the partition as equ...
const CLOB
specifies a large variable-length character column (ie CLOB or TEXT, etc)
Definition: SqlUtil.qm.dox.h:2178
const OP_NOT
the SQL "not" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4308
string getRenameSql(string new_name, *hash opt)
returns an SQL string that could be used to rename the table in the database
Columns columns
an object of class Columns representing the columns in the index
Definition: SqlUtil.qm.dox.h:5645
const SqlDataCallbackOptions
generic SQL data operation callbacks
Definition: SqlUtil.qm.dox.h:7723
base class for function or objects with code
Definition: SqlUtil.qm.dox.h:6117
const DB_TYPES
Feature: named types.
Definition: SqlUtil.qm.dox.h:2154
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
update operator info hash as returned by all update operator functions
Definition: SqlUtil.qm.dox.h:2118
hash< string, hash< JoinOperatorInfo > > make_jop(string jop, AbstractTable table, *string alias, *hash jcols, *hash cond, *string ta, *hash opt)
returns hash keyed with the table name assigned to a JoinOperatorInfo hash
const COP_CUME_DIST
Analytic (window) function: CUME_DIST.
Definition: SqlUtil.qm.dox.h:2353
copy(AbstractTable old)
copies the object
hash< ColumnOperatorInfo > cop_lower(auto column)
returns a ColumnOperatorInfo hash for the "lower" operator with the given argument; returns a column ...
hash< ColumnOperatorInfo > cop_distinct(auto column)
returns a ColumnOperatorInfo hash for the "distinct" operator with the given argument; returns distin...
hash getColumnOptions()
returns the column options for this driver
bool asteriskRequiresPrefix()
returns True if the database requires a wildcard "*" to be prefixed with the table name when it appea...
const UpsertResultMap
hash mapping upsert results to a description
Definition: SqlUtil.qm.dox.h:7875
string name
the name of the column
Definition: SqlUtil.qm.dox.h:5460
bool bindEmptyStringsAsNull()
returns True if the DB treats empty strings as NULL, False if not; by default this method returns Fal...
hash< ColumnOperatorInfo > cop_year(auto column)
returns a ColumnOperatorInfo hash for the "year" operator with the given argument ...
AbstractDatasource ds
the connection to the database server
Definition: SqlUtil.qm.dox.h:6367
hash< UpdateOperatorInfo > uop_lower(*hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "lower" operator with the given argument; returns a column...
bool same(list l)
hash< string, hash< OperatorInfo > > wop_or(hash h1, hash h2)
returns an OperatorInfo hash with a fake "_OR_" column name; the list of arguments to the function is...
hash getRawUpdateOperatorMap()
returns the raw (default) update operator map for this object
base class for functions
Definition: SqlUtil.qm.dox.h:6162
auto tryExecRaw(string sql)
executes some SQL so that if an error occurs the current transaction state is not lost ...
abstract copyImpl(AbstractTable old)
db-specific copy actions
const COP_MULTIPLY
the SQL "multiply" operator
Definition: SqlUtil.qm.dox.h:2290
column operator info hash as returned by all column operator functions
Definition: SqlUtil.qm.dox.h:2105
hash< ColumnOperatorInfo > cop_count(auto column="")
returns a ColumnOperatorInfo hash for the "count" operator; returns row counts
abstract 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
Constraints constraints
constraint descriptions
Definition: SqlUtil.qm.dox.h:7917
hash< ColumnOperatorInfo > cop_cume_dist()
Analytic/window function: relative rank of the current row.
const COP_PREPEND
to prepend a string to a column on output
Definition: SqlUtil.qm.dox.h:2215
hash getInsertFromIteratorOptions()
returns the insert from iterator options for this driver
string getSqlFromList(list l)
returns an SQL string corresponding to the list of commands in the argument
string type(auto arg)
const COP_TRUNC_DATE
to return the date with truncated value
Definition: SqlUtil.qm.dox.h:2346
*hash insert(hash row)
inserts a row into the table without any transaction management; a transaction will be in progress af...
base class for views
Definition: SqlUtil.qm.dox.h:6078
deprecated int insertFromSelectNoCommit(list cols, AbstractTable source, *hash sh, *reference< string > sql, *hash opt)
A legacy SqlUtil::AbstractTable::insertFromSelect() wrapper.
hash< OperatorInfo > op_like(string str)
returns an OperatorInfo hash for the "like" operator with the given argument for use in where clauses...
*hash opts
option hash
Definition: SqlUtil.qm.dox.h:6373
string getCreateSqlString(*hash opt)
returns an SQL string that could be used to create the table and all known properties of the table ...
abstract bool constraintsLinkedToIndexesImpl()
returns True if the database links constraints to indexes (ie dropping the constraint drops the index...
*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...
*hash select(*hash sh, *reference< string > sql, *hash opt)
returns a hash of lists representing the columns and rows in the table that match the argument hahs ...
AbstractForeignConstraint addForeignConstraint(string cname, softlist cols, string table, *softlist tcols, *hash opt, *reference< string > sql)
adds a foreign constraint to the table; if the table is already known to be in the database...
const InsertOptions
generic SQL insert options
Definition: SqlUtil.qm.dox.h:7738
const TriggerOptions
default trigger options
Definition: SqlUtil.qm.dox.h:7604
*list findAll(*hash cond)
finds all rows in the table with the given column values; a list of hashes is returned representing t...
hash< string, hash< JoinOperatorInfo > > join_left(AbstractTable table, *string alias, *hash jcols, *hash cond, *hash opt)
returns a hash for left outer joins with the given arguments
string type
the type of object
Definition: SqlUtil.qm.dox.h:6125
deprecated int updateNoCommit(hash set, *hash cond, *reference< string > sql)
A legacy SqlUtil::AbstractTable::update() wrapper.
hash< ColumnOperatorInfo > cop_seq(string seq, *string as)
returns a ColumnOperatorInfo hash for the "seq" operator with the given argument giving the sequence ...
const Int
const COP_YEAR
to return a date value with year information only
Definition: SqlUtil.qm.dox.h:2295
hash< string, hash< JoinOperatorInfo > > join_inner_alias(string ta, string table_name, *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...
string string(softstring str, *string enc)
hash getWhereOperatorMap()
returns the "where" operator map for this object
int insertFromSelectCommit(list cols, AbstractTable source, hash sh, reference< string > sql, hash opt)
inserts rows into a table based on a select statement from another table (which must be using the sam...
softint rowCount()
returns the number of rows in the table
rollback()
rolls back the current transaction on the underlying Qore::SQL::AbstractDatasource ...
hash getForeignConstraintOptions()
return the foreign constraint options for this driver
int size
the size of the column
Definition: SqlUtil.qm.dox.h:5469
hash< string, hash > driver
this key can optionally contain a hash keyed by driver name which contains a hash of values that will...
Definition: SqlUtil.qm.dox.h:2093
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 ...
hash getConstraintOptions()
returns the constraint options for this driver
hash getUpsertOptions()
returns the upsert options for this driver
string native_type
the native type name of the column
Definition: SqlUtil.qm.dox.h:5463
const COP_VALUE
to append a constant value (SQL Literal) to use as an output column value
Definition: SqlUtil.qm.dox.h:2225
hash< UpdateOperatorInfo > uop_seq(string seq)
returns an UpdateOperatorInfo hash for the "seq" operator with the given argument giving the sequence...
const UpsertUpdateOnly
Upsert option: update if the row exists, otherwise ignore.
Definition: SqlUtil.qm.dox.h:7825
column container class that throws an exception if an unknown column is accessed
Definition: SqlUtil.qm.dox.h:5403
the base class for triggers
Definition: SqlUtil.qm.dox.h:6226
*string ta
optional table name or alias of the other table to join with when not joining with the primary table ...
Definition: SqlUtil.qm.dox.h:2131
*hash getPseudoColumnHash()
returns a hash of valid pseudocolumns
AbstractCheckConstraint addCheckConstraint(string cname, string src, *hash opt, *reference< string > 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
const OP_LT
the SQL less than (<) operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4238
string dsdesc
datasource description
Definition: SqlUtil.qm.dox.h:6369
setDatasource(AbstractDatasource nds)
changes the datasource for the table; if the inDb flag is True, then it is set to False by calling th...
*hash findSingle(*hash cond)
finds a single row in the table that match the row condition passed; multiple rows may match...
hash< ColumnOperatorInfo > cop_sum(auto column)
returns a ColumnOperatorInfo hash for the "sum" operator; returns the total sum of a numeric column...
function container class that throws an exception if an unknown function is accessed ...
Definition: SqlUtil.qm.dox.h:6188
Indexes getIndexes()
returns an object of class Indexes describing the indexes on the table
const UR_Unchanged
row was unchanged (only possible with UpsertSelectFirst, UpsertInsertOnly, and UpsertUpdateOnly) ...
Definition: SqlUtil.qm.dox.h:7866
AbstractPrimaryKey dropPrimaryKey(*reference lsql)
drops the primary key from the table; if the table is known to be in the database already...
const UR_Updated
row was updated because it was different (only possible with UpsertSelectFirst)
Definition: SqlUtil.qm.dox.h:7863
AbstractDatabase db
the embedded AbstractDatabase object that actually provides the functionality for this class ...
Definition: SqlUtil.qm.dox.h:6297
the base class for column information
Definition: SqlUtil.qm.dox.h:5455
truncate()
truncates all the table data without any transaction management
const COP_CAST
to convert column value into another datatype
Definition: SqlUtil.qm.dox.h:2210
hash< OperatorInfo > op_in()
returns an OperatorInfo hash for the "in" operator with all arguments passed to the function; for use...
const UpsertInsertOnly
Upsert option: insert if the row does not exist, otherwise ignore.
Definition: SqlUtil.qm.dox.h:7818
const OP_CGE
the SQL greater than or equals operator (>=) for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4283
const DT_MINUTE
Format unit: minute.
Definition: SqlUtil.qm.dox.h:3487
hash< ColumnOperatorInfo > cop_seq_currval(string seq, *string as)
returns a ColumnOperatorInfo hash for the "seq_currval" operator with the given argument giving the s...
const DT_HOUR
Format unit: hour.
Definition: SqlUtil.qm.dox.h:3484
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:7467
const IndexOptions
default index options
Definition: SqlUtil.qm.dox.h:7578
*string comment
an optional comment for the column
Definition: SqlUtil.qm.dox.h:2089
AbstractIndex dropIndex(string iname, *reference< string > sql)
drops the given index from the table; if the table is known to be in the database already...
AbstractUniqueConstraint addUniqueConstraint(string cname, softlist cols, *hash opt, *reference< string > sql)
adds a unique constraint to the table; if the table is known to be in the database already...
hash< ColumnOperatorInfo > cop_divide(auto column1, auto column2)
returns a ColumnOperatorInfo hash for the "/" operator with the given arguments
const DefaultUopMap
a hash of valid update operators
Definition: SqlUtil.qm.dox.h:3501
const COP_MIN
to return the minimum value
Definition: SqlUtil.qm.dox.h:2245
const UpsertSelectFirst
Upsert option: select first, if the row is unchanged, do nothing, if it doesn&#39;t exist, insert, otherwise update.
Definition: SqlUtil.qm.dox.h:7804
AbstractForeignConstraint dropForeignConstraint(string cname, *reference< string > sql)
drops a foreign constraint from the table; if the table is known to be in the database already...
const COP_YEAR_DAY
to return a date value with year to day information
Definition: SqlUtil.qm.dox.h:2305
hash< OperatorInfo > op_gt(auto arg)
returns an OperatorInfo hash for the ">" operator with the given argument for use in where clauses wh...
const Closure
setupTable(hash desc, *hash opt)
creates the object from a table description hash
const UpsertInsertFirst
Upsert option: insert first, if the insert fails, then update.
Definition: SqlUtil.qm.dox.h:7787
const DT_MONTH
Format unit: month.
Definition: SqlUtil.qm.dox.h:3478
string name
the name of the sequence
Definition: SqlUtil.qm.dox.h:6086
hash getTableColumnDescOptions()
returns the table column description options for this driver
const COP_PERCENT_RANK
Analytic (window) function: PERCENT_RANK.
Definition: SqlUtil.qm.dox.h:2388
abstract doSelectLimitOnlyUnlockedImpl(reference< string > sql, reference< list > args, *hash qh)
processes a string for use in SQL select statements when there is a "limit" argument, but no "orderby" or "offset" arguments
*hash jcols
the columns to use for the join, the keys will be columns in the source table and the values are colu...
Definition: SqlUtil.qm.dox.h:2129
*number max
the ending number
Definition: SqlUtil.qm.dox.h:6053
hash getUpdateOperatorMap()
returns the update operator map for this object
const COP_RANK
Analytic (window) function: RANK.
Definition: SqlUtil.qm.dox.h:2395
hash< OperatorInfo > op_not(hash arg)
returns an OperatorInfo hash for the "not" operator; for use in where clauses
const ColumnDescOptions
Column description options.
Definition: SqlUtil.qm.dox.h:7695
number increment
the increment
Definition: SqlUtil.qm.dox.h:6050
hash getTableOptions()
returns the table options for this driver
hash< ColumnOperatorInfo > cop_row_number()
Analytic/window function: number of the current row within its partition, counting from 1...
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:4233
hash< OperatorInfo > op_lt(auto arg)
returns an OperatorInfo hash for the "<" operator with the given argument for use in where clauses wh...
hash< OperatorInfo > make_op(string op, auto arg)
returns an OperatorInfo hash
hash< OperatorInfo > op_substr(int start, *int count, string text)
returns an OperatorInfo hash for the "substr" operator with the given arguments; for use in where cla...
const TableDescriptionHashOptions
Table description options.
Definition: SqlUtil.qm.dox.h:7671
const SZ_NONE
the data type does not take a size parameter
Definition: SqlUtil.qm.dox.h:2186
hash< UpdateOperatorInfo > uop_minus(auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash for the "-" operator with the given arguments
AbstractColumn renameColumn(string old_name, string new_name, reference< string > sql)
renames an existing column; if the table is already known to be in the database, then the changes are...
AbstractTable t
the embedded AbstractTable object that actually provides the functionality for this class ...
Definition: SqlUtil.qm.dox.h:7472
hash getColumnOperatorMap()
returns the column operator map for this object
hash< ColumnOperatorInfo > cop_upper(auto column)
returns a ColumnOperatorInfo hash for the "upper" operator with the given argument; returns a column ...
const OP_CLT
the SQL less than (<) operator for use in Where Clauses when comparing two columns ...
Definition: SqlUtil.qm.dox.h:4268
const COP_MINUS
the SQL "minus" operator
Definition: SqlUtil.qm.dox.h:2275
hash< ColumnOperatorInfo > cop_percent_rank()
Analytic/window function: relative rank of the current row.
hash getTableDescriptionHashOptions()
returns the table description hash options for this driver
string table
the name of the target table
Definition: SqlUtil.qm.dox.h:6001
hash hash(object obj)
commit()
commits the current transaction on the underlying Qore::SQL::AbstractDatasource
*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:7637
hash< ColumnOperatorInfo > cop_over(auto column, *string partitionby, *string orderby)
returns a ColumnOperatorInfo hash for the "over" clause
hash< ColumnOperatorInfo > cop_year_month(auto column)
returns a ColumnOperatorInfo hash for the "year_month" operator with the given argument ...
*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...
*string alias
optional alias for table in the query
Definition: SqlUtil.qm.dox.h:2128
const COP_SUBSTR
to extract a substring from a column
Definition: SqlUtil.qm.dox.h:2330
hash getSelectOptions()
returns the select options for this driver
hash getCacheOptions()
returns the cache options for this driver
const JOP_RIGHT
for right outer joins
Definition: SqlUtil.qm.dox.h:3756
list getColumnSqlNames(softlist cols)
returns a list of column names for use in SQL strings; subclasses can process the argument list in ca...
const OP_SUBSTR
the SQL "substr" operator for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4313
*hash selectRow(*hash sh, *reference< string > sql, *hash opt)
returns a hash representing the row in the table that matches the argument hash; if more than one row...
int upsertCommit(hash row, int upsert_strategy=UpsertAuto, *hash opt)
update or insert the data in the table according to the hash argument; the table must have a unique k...
hash< ColumnOperatorInfo > cop_substr(auto column, int start, *int count)
returns a ColumnOperatorInfo hash for the "substr" operator with the given arguments; returns a subst...
hash getSqlDataCallbackOptions()
returns the sql data operation callback options for this driver
const SZ_OPT
the data type takes an optional size parameter
Definition: SqlUtil.qm.dox.h:2192
hash< InsertOperatorInfo > iop_seq(string arg)
returns an InsertOperatorInfo hash for retrieving the value of the given sequence in insert queries ...
abstract bool checkExistenceImpl()
returns True if the table exists in the DB, False if not
softint size
for data types requiring a size component, the size; for numeric columns this represents the precisio...
Definition: SqlUtil.qm.dox.h:2081
int update(hash set, hash cond, reference< string > sql, hash opt)
updates rows in the table matching an optional condition and returns the count of rows updated; no tr...
SQL operator info hash as returned by all operator functions.
Definition: SqlUtil.qm.dox.h:2099
number start
the starting number
Definition: SqlUtil.qm.dox.h:6047
const IOP_SEQ
for using the value of a sequence
Definition: SqlUtil.qm.dox.h:4777
list getDropTriggerSql(string tname, *hash opt)
returns SQL that can be used to drop the given trigger from the table
the base abstract class for the database implementation
Definition: SqlUtil.qm.dox.h:6424
const COP_ROW_NUMBER
Analytic (window) function: ROW_NUMBER.
Definition: SqlUtil.qm.dox.h:2402
string getCreateTableSql(*hash opt)
returns an SQL string that could be used to create the basic table structure without indexes and cons...
const DT_YEAR
Format unit: year.
Definition: SqlUtil.qm.dox.h:3475
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 index
the index supporting the constraint
Definition: SqlUtil.qm.dox.h:5851
represents a primary key
Definition: SqlUtil.qm.dox.h:5938
const JOP_INNER
for standard inner joins
Definition: SqlUtil.qm.dox.h:3746
const COP_LOWER
to return column value in lower case
Definition: SqlUtil.qm.dox.h:2235
hash< ColumnOperatorInfo > cop_prepend(auto column, string arg)
returns a ColumnOperatorInfo hash for the "prepend" operator with the given argument ...
*hash nest
option nested operation hash
Definition: SqlUtil.qm.dox.h:2121
const COP_DIVIDE
the SQL "divide" operator
Definition: SqlUtil.qm.dox.h:2285
string join(string str,...)
const DB_FUNCTIONS
Features constants.
Definition: SqlUtil.qm.dox.h:2142
const NUMERIC
specifies a numeric column (equivalent to Qore::Type::Number)
Definition: SqlUtil.qm.dox.h:2169
*hash sourceConstraints
a hash of ForeignConstraintSources, keyed by table name, the value is a hash of foreign constraints k...
Definition: SqlUtil.qm.dox.h:5848
const UpsertStrategyDescriptionMap
hash mapping upsert strategy descriptions to upsert strategy codes
Definition: SqlUtil.qm.dox.h:7842
deprecated int insertFromIteratorNoCommit(Qore::AbstractIterator i, *hash opt)
A legacy SqlUtil::AbstractTable::insertFromIterator() wrapper.
*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...
string qore_type
a qore type string that will be converted to a native DB type with some default conversion ...
Definition: SqlUtil.qm.dox.h:2077
const ConstraintOptions
default constraint options
Definition: SqlUtil.qm.dox.h:7586
hash< UpdateOperatorInfo > make_uop(string uop, auto arg, *hash< UpdateOperatorInfo > nest)
returns an UpdateOperatorInfo hash
const DefaultOpMap
a hash of valid operators for use in Where Clauses
Definition: SqlUtil.qm.dox.h:4319
represents a unique column constraint
Definition: SqlUtil.qm.dox.h:5929
const AdditionalColumnDescOptions
additional column description keys valid when describing columns in a table description hash ...
Definition: SqlUtil.qm.dox.h:7711
hash< OperatorInfo > op_ceq(string arg)
returns an OperatorInfo hash for the "=" operator with the given argument for use in where clauses wh...
hash getInsertOperatorMap()
returns the insert operator map for this object
abstract base class for constraints
Definition: SqlUtil.qm.dox.h:5748
const DT_SECOND
Format unit: hour.
Definition: SqlUtil.qm.dox.h:3490
abstract bool supportsTablespacesImpl()
returns True if the database support tablespaces
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
constraint container class that throws an exception if an unknown constraint is accessed ...
Definition: SqlUtil.qm.dox.h:5706
const COP_APPEND
to append a string to a column on output
Definition: SqlUtil.qm.dox.h:2220