3 Base class for producing SQL for topics and messages.
5 ------------------------------------------------------------------------------
6 This file is part of grepros - grep for ROS bag files and live topics.
7 Released under the BSD License.
12 ------------------------------------------------------------------------------
21 from ... common
import ConsolePrinter, ellipsize, ensure_namespace, import_item, merge_dicts
27 Base class for producing SQL for topics and messages.
29 Can load additional SQL dialects or additional options for existing dialects
30 from a YAML/JSON file.
34 DEFAULT_DIALECT =
"sqlite"
37 DEFAULT_ARGS = dict(META=
False, WRITE_OPTIONS={}, MATCH_WRAPPER=
None, VERBOSE=
False)
42 @param args arguments as namespace or dictionary, case-insensitive
43 @param args.write_options ```
44 {"dialect": SQL dialect if not default,
45 "nesting": true|false to created nested type tables}
47 @param kwargs any and all arguments as keyword overrides, case-insensitive
55 self.
_nesting = args.WRITE_OPTIONS.get(
"nesting")
60 Returns whether arguments are valid.
62 Verifies that "dialect-file" is valid and "dialect" contains supported value, if any.
68 """Returns whether "dialect-file" is valid in args.WRITE_OPTIONS."""
70 if self.
_args.WRITE_OPTIONS.get(
"dialect-file"):
71 filename = self.
_args.WRITE_OPTIONS[
"dialect-file"]
73 with open(filename)
as f:
74 dialects = yaml.safe_load(f.read())
75 if any(
not isinstance(v, dict)
for v
in dialects.values()):
76 raise Exception(
"Each dialect must be a dictionary.")
77 for opts
in dialects.values():
78 for k, v
in list(opts.get(
"adapters", {}).items()):
82 ConsolePrinter.error(
"Error loading adapter %r for %r "
83 "in SQL dialect file %r.", v, k, filename)
85 except Exception
as e:
87 ConsolePrinter.error(
"Error reading SQL dialect file %r: %s", filename, e)
90 for opts
in self.
DIALECTS.values()
if ok
else ():
91 for rostype
in list(opts.get(
"types", {})):
92 alias = api.get_type_alias(rostype)
94 opts[
"types"][alias] = opts[
"types"][rostype]
95 if alias
and rostype +
"[]" in opts[
"types"]:
96 opts[
"types"][alias +
"[]"] = opts[
"types"][rostype +
"[]"]
102 """Returns whether "dialect" is valid in args.WRITE_OPTIONS."""
104 if "dialect" in self.
_args.WRITE_OPTIONS \
105 and self.
_args.WRITE_OPTIONS[
"dialect"]
not in tuple(filter(bool, self.
DIALECTS)):
107 ConsolePrinter.error(
"Unknown dialect for SQL: %r. "
108 "Choose one of {%s}.",
109 self.
_args.WRITE_OPTIONS[
"dialect"],
110 "|".join(sorted(filter(bool, self.
DIALECTS))))
115 """Clears data structures."""
124 Returns full data dictionary for topic, including view name and SQL.
126 @param exclude_cols list of column names to exclude from view SELECT, if any
127 @return {"name": topic name, "type": message type name as "pkg/Cls",
128 "table_name": message type table name, "view_name": topic view name,
129 "md5": message type definition MD5 hash, "sql": "CREATE VIEW .."}
131 with api.TypeMeta.make(msg, topic)
as m:
132 typename, typehash, typekey = (m.typename, m.typehash, m.typekey)
134 table_name = self.
_types[typekey][
"table_name"]
135 pkgname, clsname = typename.split(
"/", 1)
136 nameargs = {
"topic": topic,
"type": typename,
"hash": typehash,
137 "package": pkgname,
"class": clsname}
140 sqlargs = dict(nameargs, view=
quote(view_name), table=
quote(table_name, force=
True),
141 topic=repr(topic), cols=
"*")
143 exclude_cols = [x[0]
if isinstance(x, (list, tuple))
else x
for x
in exclude_cols]
144 select_cols = [c
for c
in self.
_schema[typekey]
if c
not in exclude_cols]
145 sqlargs[
"cols"] =
", ".join(
quote(c)
for c
in select_cols)
148 return {
"name": topic,
"type": typename,
"md5": typehash,
149 "sql": sql,
"table_name": table_name,
"view_name": view_name}
154 Returns full data dictionary for message type, including table name and SQL.
156 @param rootmsg top message this message is nested under, if any
157 @param extra_cols additional table columns, as [(column name, column def)]
158 @return {"type": message type name as "pkg/Cls",
159 "table_name": message type table name,
160 "definition": message type definition,
161 "cols": [(column name, column type)],
162 "md5": message type definition MD5 hash, "sql": "CREATE TABLE .."}
164 rootmsg = rootmsg
or msg
165 with api.TypeMeta.make(msg, root=rootmsg)
as m:
166 typename, typehash = (m.typename, m.typehash)
170 for path, value, subtype
in api.iter_message_fields(msg, scalars=scalars):
172 cols += [(
".".join(path), coltype)]
173 cols.extend(extra_cols
or [])
175 namewidth = 2 + max(len(n)
for n, _
in cols)
176 coldefs = [
"%s %s" % (
quote(n).ljust(namewidth), t)
for n, t
in cols]
178 pkgname, clsname = typename.split(
"/", 1)
179 nameargs = {
"type": typename,
"hash": typehash,
"package": pkgname,
"class": clsname}
182 sqlargs = dict(nameargs, table=
quote(table_name), cols=
"\n %s\n" %
",\n ".join(coldefs))
184 return {
"type": typename,
"md5": typehash,
185 "definition": api.TypeMeta.make(msg).definition,
186 "table_name": table_name,
"cols": cols,
"sql": sql}
190 """Returns ("INSERT ..", [args]) for inserting into topics-table."""
192 topickey = api.TypeMeta.make(msg, topic).topickey
196 args = [tdata[k]
for k
in (
"name",
"type",
"md5",
"table_name",
"view_name")]
201 """Returns ("INSERT ..", [args]) for inserting into types-table."""
203 typekey = api.TypeMeta.make(msg).typekey
204 tdata = self.
_types[typekey]
207 args = [tdata[k]
for k
in (
"type",
"definition",
"md5",
"table_name")]
213 Returns ("INSERT ..", [args]) for inserting into message type table.
215 @param extra_cols list of additional table columns, as [(name, value)]
217 typekey = api.TypeMeta.make(msg, topic).typekey
218 table_name = self.
_types[typekey][
"table_name"]
219 sql, cols, args = self.
_sql_cache.get(table_name), [], []
222 for p, v, t
in api.iter_message_fields(msg, scalars=scalars):
223 if not sql: cols.append(
".".join(p))
225 args = tuple(args) + tuple(v
for _, v
in extra_cols)
229 if extra_cols: cols.extend(c
for c, _
in extra_cols)
230 sql =
"INSERT INTO %s (%s) VALUES (%s)" % \
231 (
quote(table_name),
", ".join(map(quote, cols)),
232 ", ".join([POSARG] * len(args)))
239 """Returns ("UPDATE ..", [args])."""
241 sql, args, sets, filters =
"UPDATE %s SET " %
quote(table), [], [], []
242 for lst, vals
in [(sets, values), (filters, where)]:
243 for k, v
in vals.items()
if isinstance(vals, dict)
else vals:
244 lst.append(
"%s = %s" % (
quote(k), POSARG))
246 sql +=
", ".join(sets) + (
" WHERE " if filters
else "") +
" AND ".join(filters)
252 Returns valid unique name for table/view.
254 @param args format arguments for table/view name template
257 existing = set(sum(([x[
"table_name"], x.get(
"view_name")]
259 for x
in dct.values()), []))
260 return self.
_make_name(
"entity", name, existing)
265 Returns a valid unique name for table/view/column.
267 Replaces invalid characters and constrains length.
268 If name already exists, appends counter like " (2)".
270 MAXLEN_ARG =
"maxlen_column" if "column" == category
else "maxlen_entity"
274 if not MAXLEN
and not INVALID_RGX:
return name
276 name1 = re.sub(INVALID_RGX, INVALID_REPL, name)
if INVALID_RGX
else name
279 while name2
in existing:
280 suffix =
" (%s)" % counter
281 name2 =
ellipsize(name1, MAXLEN - len(suffix)) + suffix
287 """Returns valid unique names for table columns."""
289 for name
in col_names:
290 result.append(self.
_make_name(
"column", name, result))
295 """Returns column value suitable for inserting to database."""
296 if not typename:
return value
299 if isinstance(v, (list, tuple)):
300 scalartype = api.scalar(typename)
301 if scalartype
in api.ROS_TIME_TYPES:
302 v = [self._convert_time(x)
for x
in v]
303 elif scalartype
not in api.ROS_BUILTIN_TYPES:
305 else: v = [api.message_to_dict(x)
for x
in v]
308 elif api.is_ros_time(v):
310 elif typename
not in api.ROS_BUILTIN_TYPES:
311 v = json.dumps(api.message_to_dict(v))
319 Returns column type for SQL.
321 @param fallback fallback typename to use for lookup if no mapping for typename
327 scalartype = api.scalar(typename)
328 timetype = api.get_ros_time_category(scalartype)
329 coltype = TYPES.get(typename)
or TYPES.get(api.canonical(typename, unbounded=
True))
331 if not coltype
and scalartype
in TYPES:
332 coltype = ARRAYTEMPLATE.format(type=TYPES[scalartype])
333 if not coltype
and timetype
in TYPES:
334 if typename != scalartype:
335 coltype = ARRAYTEMPLATE.format(type=TYPES[timetype])
337 coltype = TYPES[timetype]
338 if not coltype
and fallback:
341 coltype = DEFAULTTYPE
or quote(typename)
346 """Returns ROS value converted to dialect value."""
348 if not ADAPTERS:
return value
350 adapter, iterate = ADAPTERS.get(typename),
False
351 if not adapter
and isinstance(value, (list, tuple)):
352 adapter, iterate = ADAPTERS.get(api.scalar(typename)),
True
354 value = [adapter(x)
for x
in value]
if iterate
else adapter(value)
359 """Returns ROS time/duration value converted to dialect value."""
362 try: is_int = issubclass(adapter, int)
363 except Exception: is_int =
False
364 v = api.to_sec(value)
if is_int
else "%d.%09d" % api.to_sec_nsec(value)
367 result = api.to_decimal(value)
372 """Returns option for current SQL dialect, falling back to default dialect."""
381 "table_template":
"CREATE TABLE IF NOT EXISTS {table} ({cols});",
384 DROP VIEW IF EXISTS {view};
386 CREATE VIEW {view} AS
389 WHERE _topic = {topic};""",
390 "table_name_template":
"{type}",
391 "view_name_template":
"{topic}",
395 "arraytype_template":
"{type}[]",
398 "invalid_char_regex":
None,
399 "invalid_char_repl":
"__",
402 INSERT INTO topics (name, type, md5, table_name, view_name)
403 VALUES (%s, %s, %s, %s, %s);""",
405 INSERT INTO types (type, definition, md5, table_name)
406 VALUES (%s, %s, %s, %s);""",
413 CREATE TABLE IF NOT EXISTS messages (
414 id INTEGER PRIMARY KEY,
415 topic_id INTEGER NOT NULL,
416 timestamp INTEGER NOT NULL,
421 dt TIMESTAMP NOT NULL,
425 CREATE TABLE IF NOT EXISTS topics (
426 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
429 serialization_format TEXT DEFAULT "cdr",
430 offered_qos_profiles TEXT DEFAULT "",
433 table_name TEXT NOT NULL,
437 CREATE TABLE IF NOT EXISTS types (
438 id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
440 definition TEXT NOT NULL,
442 table_name TEXT NOT NULL,
446 CREATE INDEX IF NOT EXISTS timestamp_idx ON messages (timestamp ASC);
448 PRAGMA journal_mode = WAL;
450 "insert_message":
"""
451 INSERT INTO messages (topic_id, timestamp, data, topic, type, dt, yaml)
452 VALUES (:topic_id, :timestamp, :data, :topic, :type, :dt, :yaml)
458 "int8":
"SMALLINT",
"int16":
"SMALLINT",
"int32":
"INTEGER",
459 "uint8":
"SMALLINT",
"uint16":
"INTEGER",
"uint32":
"BIGINT",
460 "int64":
"BIGINT",
"uint64":
"BIGINT",
"bool":
"BOOLEAN",
461 "string":
"TEXT",
"wstring":
"TEXT",
"uint8[]":
"BYTEA",
462 "float32":
"REAL",
"float64":
"DOUBLE PRECISION",
464 "defaulttype":
"JSONB",
469 INSERT INTO topics (name, type, md5, table_name, view_name)
470 VALUES (%s, %s, %s, %s, %s)
473 INSERT INTO types (type, definition, md5, table_name)
474 VALUES (%s, %s, %s, %s)
477 CREATE TABLE IF NOT EXISTS topics (
478 id BIGSERIAL PRIMARY KEY,
482 table_name TEXT NOT NULL,
483 view_name TEXT NOT NULL
486 CREATE TABLE IF NOT EXISTS types (
487 id BIGSERIAL PRIMARY KEY,
489 definition TEXT NOT NULL,
491 table_name TEXT NOT NULL,
497 "table_template":
"CREATE TABLE IF NOT EXISTS {table} ({cols}) ENGINE = ENGINE;",
499 "int8":
"Int8",
"int16":
"Int16",
"int32":
"Int32",
500 "uint8":
"UInt8",
"uint16":
"UInt16",
"uint32":
"UInt32",
501 "int64":
"Int64",
"uint64":
"UInt64",
"bool":
"UInt8",
502 "float32":
"Float32",
"float64":
"Float64",
"uint8[]":
"String",
503 "string":
"String",
"wstring":
"String",
505 "defaulttype":
"String",
506 "arraytype_template":
"Array({type})",
514 "A",
"ABORT",
"ABS",
"ABSOLUTE",
"ACCESS",
"ACTION",
"ADA",
"ADD",
"ADMIN",
"AFTER",
515 "AGGREGATE",
"ALIAS",
"ALL",
"ALLOCATE",
"ALSO",
"ALTER",
"ALWAYS",
"ANALYSE",
"ANALYZE",
516 "AND",
"ANY",
"ARE",
"ARRAY",
"AS",
"ASC",
"ASENSITIVE",
"ASSERTION",
"ASSIGNMENT",
517 "ASYMMETRIC",
"AT",
"ATOMIC",
"ATTACH",
"ATTRIBUTE",
"ATTRIBUTES",
"AUDIT",
518 "AUTHORIZATION",
"AUTOINCREMENT",
"AUTO_INCREMENT",
"AVG",
"AVG_ROW_LENGTH",
"BACKUP",
519 "BACKWARD",
"BEFORE",
"BEGIN",
"BERNOULLI",
"BETWEEN",
"BIGINT",
"BINARY",
"BIT",
"BITVAR",
520 "BIT_LENGTH",
"BLOB",
"BOOL",
"BOOLEAN",
"BOTH",
"BREADTH",
"BREAK",
"BROWSE",
"BULK",
521 "BY",
"C",
"CACHE",
"CALL",
"CALLED",
"CARDINALITY",
"CASCADE",
"CASCADED",
"CASE",
"CAST",
522 "CATALOG",
"CATALOG_NAME",
"CEIL",
"CEILING",
"CHAIN",
"CHANGE",
"CHAR",
"CHARACTER",
523 "CHARACTERISTICS",
"CHARACTERS",
"CHARACTER_LENGTH",
"CHARACTER_SET_CATALOG",
524 "CHARACTER_SET_NAME",
"CHARACTER_SET_SCHEMA",
"CHAR_LENGTH",
"CHECK",
"CHECKED",
525 "CHECKPOINT",
"CHECKSUM",
"CLASS",
"CLASS_ORIGIN",
"CLOB",
"CLOSE",
"CLUSTER",
"CLUSTERED",
526 "COALESCE",
"COBOL",
"COLLATE",
"COLLATION",
"COLLATION_CATALOG",
"COLLATION_NAME",
527 "COLLATION_SCHEMA",
"COLLECT",
"COLUMN",
"COLUMNS",
"COLUMN_NAME",
"COMMAND_FUNCTION",
528 "COMMAND_FUNCTION_CODE",
"COMMENT",
"COMMIT",
"COMMITTED",
"COMPLETION",
"COMPRESS",
529 "COMPUTE",
"CONDITION",
"CONDITION_NUMBER",
"CONNECT",
"CONNECTION",
"CONNECTION_NAME",
530 "CONSTRAINT",
"CONSTRAINTS",
"CONSTRAINT_CATALOG",
"CONSTRAINT_NAME",
"CONSTRAINT_SCHEMA",
531 "CONSTRUCTOR",
"CONTAINS",
"CONTAINSTABLE",
"CONTINUE",
"CONVERSION",
"CONVERT",
"COPY",
532 "CORR",
"CORRESPONDING",
"COUNT",
"COVAR_POP",
"COVAR_SAMP",
"CREATE",
"CREATEDB",
533 "CREATEROLE",
"CREATEUSER",
"CROSS",
"CSV",
"CUBE",
"CUME_DIST",
"CURRENT",
"CURRENT_DATE",
534 "CURRENT_DEFAULT_TRANSFORM_GROUP",
"CURRENT_PATH",
"CURRENT_ROLE",
"CURRENT_TIME",
535 "CURRENT_TIMESTAMP",
"CURRENT_TRANSFORM_GROUP_FOR_TYPE",
"CURRENT_USER",
"CURSOR",
536 "CURSOR_NAME",
"CYCLE",
"DATA",
"DATABASE",
"DATABASES",
"DATE",
"DATETIME",
537 "DATETIME_INTERVAL_CODE",
"DATETIME_INTERVAL_PRECISION",
"DAY",
"DAYOFMONTH",
"DAYOFWEEK",
538 "DAYOFYEAR",
"DAY_HOUR",
"DAY_MICROSECOND",
"DAY_MINUTE",
"DAY_SECOND",
"DBCC",
539 "DEALLOCATE",
"DEC",
"DECIMAL",
"DECLARE",
"DEFAULT",
"DEFAULTS",
"DEFERRABLE",
"DEFERRED",
540 "DEFINED",
"DEFINER",
"DEGREE",
"DELAYED",
"DELAY_KEY_WRITE",
"DELETE",
"DELIMITER",
541 "DELIMITERS",
"DENSE_RANK",
"DENY",
"DEPTH",
"DEREF",
"DERIVED",
"DESC",
"DESCRIBE",
542 "DESCRIPTOR",
"DESTROY",
"DESTRUCTOR",
"DETACH",
"DETERMINISTIC",
"DIAGNOSTICS",
543 "DICTIONARY",
"DISABLE",
"DISCONNECT",
"DISK",
"DISPATCH",
"DISTINCT",
"DISTINCTROW",
544 "DISTRIBUTED",
"DIV",
"DO",
"DOMAIN",
"DOUBLE",
"DROP",
"DUAL",
"DUMMY",
"DUMP",
"DYNAMIC",
545 "DYNAMIC_FUNCTION",
"DYNAMIC_FUNCTION_CODE",
"EACH",
"ELEMENT",
"ELSE",
"ELSEIF",
"ENABLE",
546 "ENCLOSED",
"ENCODING",
"ENCRYPTED",
"END",
"END-EXEC",
"ENUM",
"EQUALS",
"ERRLVL",
547 "ESCAPE",
"ESCAPED",
"EVERY",
"EXCEPT",
"EXCEPTION",
"EXCLUDE",
"EXCLUDING",
"EXCLUSIVE",
548 "EXEC",
"EXECUTE",
"EXISTING",
"EXISTS",
"EXIT",
"EXP",
"EXPLAIN",
"EXTERNAL",
"EXTRACT",
549 "FALSE",
"FETCH",
"FIELDS",
"FILE",
"FILLFACTOR",
"FILTER",
"FINAL",
"FIRST",
"FLOAT",
550 "FLOAT4",
"FLOAT8",
"FLOOR",
"FLUSH",
"FOLLOWING",
"FOR",
"FORCE",
"FOREIGN",
"FORTRAN",
551 "FORWARD",
"FOUND",
"FREE",
"FREETEXT",
"FREETEXTTABLE",
"FREEZE",
"FROM",
"FULL",
552 "FULLTEXT",
"FUNCTION",
"FUSION",
"G",
"GENERAL",
"GENERATED",
"GET",
"GLOBAL",
"GO",
553 "GOTO",
"GRANT",
"GRANTED",
"GRANTS",
"GREATEST",
"GROUP",
"GROUPING",
"HANDLER",
"HAVING",
554 "HEADER",
"HEAP",
"HIERARCHY",
"HIGH_PRIORITY",
"HOLD",
"HOLDLOCK",
"HOST",
"HOSTS",
555 "HOUR",
"HOUR_MICROSECOND",
"HOUR_MINUTE",
"HOUR_SECOND",
"IDENTIFIED",
"IDENTITY",
556 "IDENTITYCOL",
"IDENTITY_INSERT",
"IF",
"IGNORE",
"ILIKE",
"IMMEDIATE",
"IMMUTABLE",
557 "IMPLEMENTATION",
"IMPLICIT",
"IN",
"INCLUDE",
"INCLUDING",
"INCREMENT",
"INDEX",
558 "INDICATOR",
"INFILE",
"INFIX",
"INHERIT",
"INHERITS",
"INITIAL",
"INITIALIZE",
559 "INITIALLY",
"INNER",
"INOUT",
"INPUT",
"INSENSITIVE",
"INSERT",
"INSERT_ID",
"INSTANCE",
560 "INSTANTIABLE",
"INSTEAD",
"INT",
"INT1",
"INT2",
"INT3",
"INT4",
"INT8",
"INTEGER",
561 "INTERSECT",
"INTERSECTION",
"INTERVAL",
"INTO",
"INVOKER",
"IS",
"ISAM",
"ISNULL",
562 "ISOLATION",
"ITERATE",
"JOIN",
"K",
"KEY",
"KEYS",
"KEY_MEMBER",
"KEY_TYPE",
"KILL",
563 "LANCOMPILER",
"LANGUAGE",
"LARGE",
"LAST",
"LAST_INSERT_ID",
"LATERAL",
"LEAD",
"LEADING",
564 "LEAST",
"LEAVE",
"LEFT",
"LENGTH",
"LESS",
"LEVEL",
"LIKE",
"LIMIT",
"LINENO",
"LINES",
565 "LISTEN",
"LN",
"LOAD",
"LOCAL",
"LOCALTIME",
"LOCALTIMESTAMP",
"LOCATION",
"LOCATOR",
566 "LOCK",
"LOGIN",
"LOGS",
"LONG",
"LONGBLOB",
"LONGTEXT",
"LOOP",
"LOWER",
"LOW_PRIORITY",
567 "M",
"MAP",
"MATCH",
"MATCHED",
"MAX",
"MAXEXTENTS",
"MAXVALUE",
"MAX_ROWS",
"MEDIUMBLOB",
568 "MEDIUMINT",
"MEDIUMTEXT",
"MEMBER",
"MERGE",
"MESSAGE_LENGTH",
"MESSAGE_OCTET_LENGTH",
569 "MESSAGE_TEXT",
"METHOD",
"MIDDLEINT",
"MIN",
"MINUS",
"MINUTE",
"MINUTE_MICROSECOND",
570 "MINUTE_SECOND",
"MINVALUE",
"MIN_ROWS",
"MLSLABEL",
"MOD",
"MODE",
"MODIFIES",
"MODIFY",
571 "MODULE",
"MONTH",
"MONTHNAME",
"MORE",
"MOVE",
"MULTISET",
"MUMPS",
"MYISAM",
"NAME",
572 "NAMES",
"NATIONAL",
"NATURAL",
"NCHAR",
"NCLOB",
"NESTING",
"NEW",
"NEXT",
"NO",
573 "NOAUDIT",
"NOCHECK",
"NOCOMPRESS",
"NOCREATEDB",
"NOCREATEROLE",
"NOCREATEUSER",
574 "NOINHERIT",
"NOLOGIN",
"NONCLUSTERED",
"NONE",
"NORMALIZE",
"NORMALIZED",
"NOSUPERUSER",
575 "NOT",
"NOTHING",
"NOTIFY",
"NOTNULL",
"NOWAIT",
"NO_WRITE_TO_BINLOG",
"NULL",
"NULLABLE",
576 "NULLIF",
"NULLS",
"NUMBER",
"NUMERIC",
"OBJECT",
"OCTETS",
"OCTET_LENGTH",
"OF",
"OFF",
577 "OFFLINE",
"OFFSET",
"OFFSETS",
"OIDS",
"OLD",
"ON",
"ONLINE",
"ONLY",
"OPEN",
578 "OPENDATASOURCE",
"OPENQUERY",
"OPENROWSET",
"OPENXML",
"OPERATION",
"OPERATOR",
579 "OPTIMIZE",
"OPTION",
"OPTIONALLY",
"OPTIONS",
"OR",
"ORDER",
"ORDERING",
"ORDINALITY",
580 "OTHERS",
"OUT",
"OUTER",
"OUTFILE",
"OUTPUT",
"OVER",
"OVERLAPS",
"OVERLAY",
"OVERRIDING",
581 "OWNER",
"PACK_KEYS",
"PAD",
"PARAMETER",
"PARAMETERS",
"PARAMETER_MODE",
"PARAMETER_NAME",
582 "PARAMETER_ORDINAL_POSITION",
"PARAMETER_SPECIFIC_CATALOG",
"PARAMETER_SPECIFIC_NAME",
583 "PARAMETER_SPECIFIC_SCHEMA",
"PARTIAL",
"PARTITION",
"PASCAL",
"PASSWORD",
"PATH",
584 "PCTFREE",
"PERCENT",
"PERCENTILE_CONT",
"PERCENTILE_DISC",
"PERCENT_RANK",
"PLACING",
585 "PLAN",
"PLI",
"POSITION",
"POSTFIX",
"POWER",
"PRAGMA",
"PRECEDING",
"PRECISION",
586 "PREFIX",
"PREORDER",
"PREPARE",
"PREPARED",
"PRESERVE",
"PRIMARY",
"PRINT",
"PRIOR",
587 "PRIVILEGES",
"PROC",
"PROCEDURAL",
"PROCEDURE",
"PROCESS",
"PROCESSLIST",
"PUBLIC",
588 "PURGE",
"QUOTE",
"RAID0",
"RAISE",
"RAISERROR",
"RANGE",
"RANK",
"RAW",
"READ",
"READS",
589 "READTEXT",
"REAL",
"RECHECK",
"RECONFIGURE",
"RECURSIVE",
"REF",
"REFERENCES",
590 "REFERENCING",
"REGEXP",
"REGR_AVGX",
"REGR_AVGY",
"REGR_COUNT",
"REGR_INTERCEPT",
591 "REGR_R2",
"REGR_SLOPE",
"REGR_SXX",
"REGR_SXY",
"REGR_SYY",
"REINDEX",
"RELATIVE",
592 "RELEASE",
"RELOAD",
"RENAME",
"REPEAT",
"REPEATABLE",
"REPLACE",
"REPLICATION",
"REQUIRE",
593 "RESET",
"RESIGNAL",
"RESOURCE",
"RESTART",
"RESTORE",
"RESTRICT",
"RESULT",
"RETURN",
594 "RETURNED_CARDINALITY",
"RETURNED_LENGTH",
"RETURNED_OCTET_LENGTH",
"RETURNED_SQLSTATE",
595 "RETURNS",
"REVOKE",
"RIGHT",
"RLIKE",
"ROLE",
"ROLLBACK",
"ROLLUP",
"ROUTINE",
596 "ROUTINE_CATALOG",
"ROUTINE_NAME",
"ROUTINE_SCHEMA",
"ROW",
"ROWCOUNT",
"ROWGUIDCOL",
597 "ROWID",
"ROWNUM",
"ROWS",
"ROW_COUNT",
"ROW_NUMBER",
"RULE",
"SAVE",
"SAVEPOINT",
"SCALE",
598 "SCHEMA",
"SCHEMAS",
"SCHEMA_NAME",
"SCOPE",
"SCOPE_CATALOG",
"SCOPE_NAME",
"SCOPE_SCHEMA",
599 "SCROLL",
"SEARCH",
"SECOND",
"SECOND_MICROSECOND",
"SECTION",
"SECURITY",
"SELECT",
600 "SELF",
"SENSITIVE",
"SEPARATOR",
"SEQUENCE",
"SERIALIZABLE",
"SERVER_NAME",
"SESSION",
601 "SESSION_USER",
"SET",
"SETOF",
"SETS",
"SETUSER",
"SHARE",
"SHOW",
"SHUTDOWN",
"SIGNAL",
602 "SIMILAR",
"SIMPLE",
"SIZE",
"SMALLINT",
"SOME",
"SONAME",
"SOURCE",
"SPACE",
"SPATIAL",
603 "SPECIFIC",
"SPECIFICTYPE",
"SPECIFIC_NAME",
"SQL",
"SQLCA",
"SQLCODE",
"SQLERROR",
604 "SQLEXCEPTION",
"SQLSTATE",
"SQLWARNING",
"SQL_BIG_RESULT",
"SQL_BIG_SELECTS",
605 "SQL_BIG_TABLES",
"SQL_CALC_FOUND_ROWS",
"SQL_LOG_OFF",
"SQL_LOG_UPDATE",
606 "SQL_LOW_PRIORITY_UPDATES",
"SQL_SELECT_LIMIT",
"SQL_SMALL_RESULT",
"SQL_WARNINGS",
"SQRT",
607 "SSL",
"STABLE",
"START",
"STARTING",
"STATE",
"STATEMENT",
"STATIC",
"STATISTICS",
608 "STATUS",
"STDDEV_POP",
"STDDEV_SAMP",
"STDIN",
"STDOUT",
"STORAGE",
"STRAIGHT_JOIN",
609 "STRICT",
"STRING",
"STRUCTURE",
"STYLE",
"SUBCLASS_ORIGIN",
"SUBLIST",
"SUBMULTISET",
610 "SUBSTRING",
"SUCCESSFUL",
"SUM",
"SUPERUSER",
"SYMMETRIC",
"SYNONYM",
"SYSDATE",
"SYSID",
611 "SYSTEM",
"SYSTEM_USER",
"TABLE",
"TABLES",
"TABLESAMPLE",
"TABLESPACE",
"TABLE_NAME",
612 "TEMP",
"TEMPLATE",
"TEMPORARY",
"TERMINATE",
"TERMINATED",
"TEXT",
"TEXTSIZE",
"THAN",
613 "THEN",
"TIES",
"TIME",
"TIMESTAMP",
"TIMEZONE_HOUR",
"TIMEZONE_MINUTE",
"TINYBLOB",
614 "TINYINT",
"TINYTEXT",
"TO",
"TOAST",
"TOP",
"TOP_LEVEL_COUNT",
"TRAILING",
"TRAN",
615 "TRANSACTION",
"TRANSACTIONS_COMMITTED",
"TRANSACTIONS_ROLLED_BACK",
"TRANSACTION_ACTIVE",
616 "TRANSFORM",
"TRANSFORMS",
"TRANSLATE",
"TRANSLATION",
"TREAT",
"TRIGGER",
617 "TRIGGER_CATALOG",
"TRIGGER_NAME",
"TRIGGER_SCHEMA",
"TRIM",
"TRUE",
"TRUNCATE",
"TRUSTED",
618 "TSEQUAL",
"TYPE",
"UESCAPE",
"UID",
"UNBOUNDED",
"UNCOMMITTED",
"UNDER",
"UNDO",
619 "UNENCRYPTED",
"UNION",
"UNIQUE",
"UNKNOWN",
"UNLISTEN",
"UNLOCK",
"UNNAMED",
"UNNEST",
620 "UNSIGNED",
"UNTIL",
"UPDATE",
"UPDATETEXT",
"UPPER",
"USAGE",
"USE",
"USER",
621 "USER_DEFINED_TYPE_CATALOG",
"USER_DEFINED_TYPE_CODE",
"USER_DEFINED_TYPE_NAME",
622 "USER_DEFINED_TYPE_SCHEMA",
"USING",
"UTC_DATE",
"UTC_TIME",
"UTC_TIMESTAMP",
"VACUUM",
623 "VALID",
"VALIDATE",
"VALIDATOR",
"VALUE",
"VALUES",
"VARBINARY",
"VARCHAR",
"VARCHAR2",
624 "VARCHARACTER",
"VARIABLE",
"VARIABLES",
"VARYING",
"VAR_POP",
"VAR_SAMP",
"VERBOSE",
625 "VIEW",
"VOLATILE",
"WAITFOR",
"WHEN",
"WHENEVER",
"WHERE",
"WHILE",
"WIDTH_BUCKET",
626 "WINDOW",
"WITH",
"WITHIN",
"WITHOUT",
"WORK",
"WRITE",
"WRITETEXT",
"X509",
"XOR",
"YEAR",
627 "YEAR_MONTH",
"ZEROFILL",
"ZONE",
633 Returns name in quotes and proper-escaped for SQL queries.
635 @param force quote even if name does not need quoting (starts with a letter,
636 contains only alphanumerics, and is not a reserved keyword)
639 if force
or result.upper()
in SqlMixin.KEYWORDS \
640 or re.search(
r"(^[\W\d])|(?=\W)", result, re.U):
641 result =
'"%s"' % result.replace(
'"',
'""')
645 __all__ = [
"SqlMixin",
"quote"]