[sqlite] INSERT DEFAULT literal-value
On 2015/11/24 9:20 PM, chromedout64 at yahoo.com wrote: > Thanks for the reply. It would be the case of specifying a default value > among other columns but not wishing to remove it from the inserted fields -- > not just because I'm lazy, but also to reuse that same INSERT statement for > other values. Thank you, I understand better now. While I still think it must be extremely trivial to implement in code (moreso than adding the feature), I at least understand your need. Your feature request has probably been noted by now and while I think it will be very trivial to implement, I can see a problem with some broken backward compatibility. Imagine someone has a database with a column named "default". Or consider this Statement: INSERT INTO t SELECT ID, Name, DEFAULT FROM x;
[sqlite] Dont Repeat Yourself (DRY) and SQLite
On 24 Nov 2015, at 7:09pm, Domingo Alvarez Duarte wrote: > one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use > a default here ? Include "DEFAULT 'tuple'" just like you would in PostgreSQL. Otherwise I'm with Igor. I don't see why you're using TRIGGERs and I don't see what problem you're having. Can you point out a specific section of your PostgreSQL code you can't translate into SQLite ? Simon.
[sqlite] INSERT DEFAULT literal-value
Backward compatibility is an understandable concern, but since "DEFAULT" is one of the 124 official SQLite keywords, you would expect it not to be used for user-defined objects or else properly quoted. http://www.sqlite.org/lang_keywords.html Additionally, adding the capability of using a "DEFAULT" literal-value satisfies the WWPD criteria. http://www.postgresql.org/docs/9.4/static/sql-insert.html
[sqlite] Dont Repeat Yourself (DRY) and SQLite
Actually we can not use "select"? on constraints ! Cheers ! > Tue Nov 24 2015 8:43:57 pm CET CET from "Igor Tandetnik" > Subject: Re: [sqlite] Dont Repeat Yourself (DRY) and >SQLite > > On 11/24/2015 2:09 PM, Domingo Alvarez Duarte wrote: > >>I'm trying to migrate a database from PostgreSQL to SQLite but could not >>find >> a way to do it and stay DRY, is it possible to do it ? >> > SQLite supports CHECK constraints, just like Postrgress. Why do you feel > you need triggers? > > >>Using literals for one_type is not an acceptable option ! >> > I'm not sure I understand. What else do you plan to use? > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
[sqlite] Query flattening for left joins involving subqueries on the right-hand side
Hi For a left join with a subquery on the right-hand side, that subquery doesn't seem to be flattened. This seems to work well with an inner join. I have attached a reprex. It creates two tables with $n rows and one ID column each (200k is enough to show substantial slowdown), and joins them with and without subqueries. The third example seems to create a suboptimal query plan and takes much longer than necessary to run. The output on my system is below the message. Thanks for your attention. Best regards Kirill 1|0|0|SCAN TABLE t1 0|0|0|SCAN SUBQUERY 1 1 real0m0.003s user0m0.000s sys0m0.000s 1|0|0|SCAN TABLE t1 0|0|0|SCAN SUBQUERY 1 1 real0m0.003s user0m0.000s sys0m0.000s 2|0|0|SCAN TABLE t2 1|0|0|SCAN TABLE t1 1|1|1|SEARCH SUBQUERY 2 AS zzz2 USING AUTOMATIC COVERING INDEX (a=?) 0|0|0|SCAN SUBQUERY 1 1 real0m0.277s user0m0.264s sys0m0.012s 1|0|0|SCAN TABLE t1 1|1|1|SEARCH TABLE t2 USING COVERING INDEX sqlite_autoindex_t2_1 (a=?) 0|0|0|SCAN SUBQUERY 1 1 real0m0.003s user0m0.000s sys0m0.000s
[sqlite] Dont Repeat Yourself (DRY) and SQLite
Hello !
I'm trying to migrate a database from PostgreSQL to SQLite but could not find
a way to do it and stay DRY, is it possible to do it ?
See example bellow.
Cheers !
==
Using literals for one_type is not an acceptable option !
one_type varchar NOT NULL DEFAULT 'tuple'
CHECK( (one_type IN('simple', 'tuple')) (NOT (one_type = 'simple' AND active
= FALSE)) )
== PostgreSQL
CREATE TYPE "mytype" AS ENUM ('simple', 'tuple');
CREATE TABLE "use_mytype"(
?? ?id SERIAL PRIMARY KEY,
?? ?one_type mytype NOT NULL DEFAULT 'tuple',
?? ?active BOOLEAN,
?? ?CONSTRAINT "one_type_and_active" CHECK(NOT (one_type = 'simple' AND
active = FALSE))
);
== SQLite
CREATE TABLE "mytype"(id INTEGER PRIMARY KEY, name VARCHAR);
insert into mytype(id, name) values (1,'simple'), (2,'tuple');
CREATE TABLE "use_mytype"(
?? ?id INTEGER PRIMARY KEY,
?? ?one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use
a default here ?
?? ?active BOOLEAN
);
CREATE TRIGGER "use_mytype_insert_trigger"
BEFORE INSERT ON "use_mytype"
BEGIN
?? ?SELECT RAISE(ROLLBACK, 'Not permited !')
?? ??? ?WHERE (NEW.one_type = 'simple' AND NEW.active = 0);
END;
-- NOT DRY (Repetition is the mother of bugs)
CREATE TRIGGER "use_mytype_update_trigger"
BEFORE UPDATE OF one_type ON "use_mytype"
BEGIN
?? ?SELECT RAISE(ROLLBACK, 'Not permited !')
?? ??? ?WHERE (NEW.one_type = 'simple' AND NEW.active = 0);
END;
==
[sqlite] INSERT DEFAULT literal-value
On 2015/11/23 11:00 PM, chromedout64 at yahoo.com wrote:
> Maybe there's a technical reason that this functionality wasn't added to
> SQLite. Does anyone know?
I am not sure exactly what you intend with this.
Do you mean to Insert into a table a row with the default values? In
that case you can use SQLite's:
"INSERT INTO t DEFAULT VALUES;
If you mean for any specific item to gain its default value upon
inserting (but not the other fields), you can simply omit it from the
Insert statement.
If you mean to Insert any other literal value, you can simply do so with
either specifying in the SQL or adding named hooks and binding them.
If you mean to specify the default value for a single column among other
columns but do not wish to remove it from the inserted fields (as a lazy
programmer would), then I believe you are stuck - you have to omit it
from the field list.
Do you mean perhaps to use a specified value during table-creation as
the default? I have used functions with success (see example sql below),
so a user-defined function can be pressed into service for some
interesting control. However, if you are going to be creating tables in
your code (I mean, why else would you need literals?), then you could
just use string substitution:
"CREATE TABLE t (Val TEXT DEFAULT ?1);" can - with equal amounts of
effort - simply be:
"CREATE TABLE t (Val TEXT DEFAULT " + MyVar + ");".
If none of the above, then I am sorry for not following what you
intended - maybe explain more?
Quick script demonstrating some of it:
(The date function can easily be substituted for any user-defined function)
-- Processing SQL in: E:\Documents\SQLiteAutoScript.sql
-- Using SQLite version 3.8.11.1 on SQLitespeed version 2.0.1.23
-- Script Items: 6 Parameter Count: 0
-- 2015-11-24 19:50:05.296 | [Info] Script Initialized,
Started executing...
--
CREATE TABLE t (
ID INTEGER PRIMARY KEY,
Val TEXT DEFAULT 'New Item',
Updated NUMERIC DEFAULT (datetime('now'))
);
INSERT INTO t DEFAULT VALUES;
INSERT INTO t (Val) VALUES ('Second Inserted Item');
SELECT * FROM t;
-- ID | Val| Updated
-- | -- | -
-- 1 | New Item | 2015-11-24 17:50:05
-- 2 | Second Inserted Item | 2015-11-24 17:50:05
DROP TABLE t;
-- Script Stats: Total Script Execution Time: 0d 00h 00m
and 00.030s
-- Total Script Query Time: 0d 00h 00m
and 00.014s
-- Total Database Rows Changed: 2
-- Total Virtual-Machine Steps: 210
-- Last executed Item Index:6
-- Last Script Error:
--
-- 2015-11-24 19:50:05.311 | [Success]Script Success.
-- 2015-11-24 19:50:05.311 | [Success]Transaction Rolled back.
[sqlite] INSERT DEFAULT literal-value
Doesn't look like the formatting worked on the last one.
Just an quick example using the sqlite3 CLI:db=/tmp/test.dbsqlite3 $db "create
table t(id integer primary key,ts text default
(datetime('localtime','now')));"for x in "'no_date'" "'invalid_date'" "NULL"
"CURRENT_TIMESTAMP" "DEFAULT"; dosqlite3 $db "insert into t(ts)
values($x);"donesqlite3 $db "select * from t;"
Error: near "DEFAULT": syntax error1|no_date2|invalid_date3|4|2015-11-24
19:06:26
[sqlite] INSERT DEFAULT literal-value
Thanks for the reply. It would be the case of specifying a default value among
other columns but not wishing to remove it from the inserted fields -- not just
because I'm lazy, but also to reuse that same INSERT statement for other values.
Just an quick example using the sqlite3 CLI:db=/tmp/test.dbsqlite3 $db "create
table t(id integer primary key,ts text default
(datetime('localtime','now')));"for x in "'no_date'" "'invalid_date'" "NULL"
"CURRENT_TIMESTAMP" "DEFAULT"; dosqlite3 $db "insert into t(ts)
values($x);"donesqlite3 $db "select * from t;"
Error: near "DEFAULT": syntax error
1|no_date
2|invalid_date
3|
4|2015-11-24 19:06:26
[sqlite] regular expression in check constraint?
On 11/24/2015 6:07 PM, Richard Hipp wrote: > On 11/24/15, James Hartley wrote: >> I would like to add a check constraint which determines if a string >> contains all digits, ie. >> >> sqlite> select zip_code from zip_codes where regexp(zip_code, >> '^[[:digit:]]+$'); >> >> However, this generates the error: >> >> Error: no such function: regexp > > Maybe this instead: > > SELECT * FROM zip_codes WHERE zip_code NOT GLOB '[^0-9]'; Another variation: WHERE ltrim(zip_code, '0123456789')='' -- Igor Tandetnik
[sqlite] regular expression in check constraint?
On 11/24/15, James Hartley wrote: > I would like to add a check constraint which determines if a string > contains all digits, ie. > > sqlite> select zip_code from zip_codes where regexp(zip_code, > '^[[:digit:]]+$'); > > However, this generates the error: > > Error: no such function: regexp Maybe this instead: SELECT * FROM zip_codes WHERE zip_code NOT GLOB '[^0-9]'; > > Searching through sqlite.org points that this function may not be included. > > So, is there any equivalent to this kind of functionality? I can check at > the application level beforehand, but there is some solace knowing that the > database can check the condition at the time of insertion too. > > Thanks! > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] regular expression in check constraint?
I would like to add a check constraint which determines if a string contains all digits, ie. sqlite> select zip_code from zip_codes where regexp(zip_code, '^[[:digit:]]+$'); However, this generates the error: Error: no such function: regexp Searching through sqlite.org points that this function may not be included. So, is there any equivalent to this kind of functionality? I can check at the application level beforehand, but there is some solace knowing that the database can check the condition at the time of insertion too. Thanks!
[sqlite] FTS5 prefix index documentation may be incorrect
The FTS5 prefix index documentation[1] seems to not be working. I've tried with SQLite 3.9.0 and 3.10.0 (2015-11-06) and both show the same error messages. Examples: sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3'); Error: malformed prefix=... directive sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3); Error: multiple prefix=... directives What does appear to work is using a comma: sqlite> CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3'); sqlite> .schema ft CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2,3'); 1: https://sqlite.org/fts5.html#section_4_2
[sqlite] Dont Repeat Yourself (DRY) and SQLite
How Postgres stores enum values is an implementation detail that should be ignored. You always use them using string syntax, that is proper. The SQL syntax for comparisons is the same =, <, > etc for all types, there is no distinct "string comparison". See http://www.postgresql.org/docs/9.4/static/datatype-enum.html . Do what Simon says. I don't see a problem here. -- Darren Duncan On 2015-11-24 3:24 PM, Domingo Alvarez Duarte wrote: > If we do that we'll be repeating the same string on every column and need a > string comparison, with postgres enum types or foreign keys it's an integer > and no repetition. > > Cheers ! >> Tue Nov 24 2015 11:01:35 pm CET CET from "Simon Slavin" >> Subject: Re: [sqlite] Dont Repeat Yourself (DRY) >> and >> SQLite >> >> On 24 Nov 2015, at 7:09pm, Domingo Alvarez Duarte >> wrote: >> >> >>> one_type INTEGER NOT NULL REFERENCES mytype(id) NOT NULL, --how to use >>> a default here ? >>> > >> Include "DEFAULT 'tuple'" just like you would in PostgreSQL. >> >> Otherwise I'm with Igor. I don't see why you're using TRIGGERs and I don't >> see what problem you're having. Can you point out a specific section of your >> PostgreSQL code you can't translate into SQLite ?
[sqlite] Dont Repeat Yourself (DRY) and SQLite
On 11/24/2015 2:58 PM, Domingo Alvarez Duarte wrote: > Actually we can not use "select" on constraints ! And that's a problem because... ? Your triggers don't refer to any other table either. Color me dense, but I utterly fail to grasp the nature of the difficulty. -- Igor Tandetnik
[sqlite] Dont Repeat Yourself (DRY) and SQLite
On 11/24/2015 2:09 PM, Domingo Alvarez Duarte wrote: > I'm trying to migrate a database from PostgreSQL to SQLite but could not find > a way to do it and stay DRY, is it possible to do it ? SQLite supports CHECK constraints, just like Postrgress. Why do you feel you need triggers? > Using literals for one_type is not an acceptable option ! I'm not sure I understand. What else do you plan to use? -- Igor Tandetnik
