Hello Richard !
I decided to really look at the code now (before I looked at the diffs and
saw so many magic numbers "4").
I know that what I'm saying here not always I follow for n reasons but thanks
for giving me a chance to take a moment and reflect on this that do not seem
a problem when
Hello Richard !
It's hard to come up with names but as it is on
https://www.sqlite.org/src/info/1541607d458069f5 I think that's a good
improvement to make the code easier to follow and DRY.
This approach has at least 2 benefits:
1- Is easy to see what parts of the code depend on PTRMAP_OV
Hello Richard !
I just saw this commit https://www.sqlite.org/src/info/6a4cfc7ab62046eb and
noticed you've been using magic numbers would it be better to use a macro
instead ?
I think for other people (and maybe yourself) would be easier to see
something like "INFO_SIZE_ADJUST" (or any meanin
y the full text search executed
by fossil need be revised ?
Cheers !
?
> Mon Dec 28 2015 7:52:30 pm CET CET from "Olivier Mascia"
>Subject: Re: [sqlite] JSON1 Extension should appear on top level doc page
>
>
>>Le 28 d?c. 2015 ? 17:13, Domingo Alvarez Duarte
Hello !
I just needed to work with json but didn't remember all the json1 available
functions and it's parameters so I went to https://www.sqlite.org/docs.html
and did not found any mention to then, then I did a search
https://www.sqlite.org/search?q=json and again nothing about then I remember
Let's add another one:
- replace hard coded floating point type "double" by "sqlite3_double" to
allow user define alternatives like "_Decimal64" (see
https://github.com/mingodad/squilu/tree/master/SquiLu-ext sqlite3.c sqlite3.h
with "-DSQLITE_USE_DECIMAL")
Best whishes for 2016!
> Sun Dec
Hello !
I said once and I'll say again for some applications it would make sense to
use _Decimal64 (_Decimal32, _Decimal128) instead of floating points.
Even if it's done in software the performance is acceptable on most common
cases.
See a sqlite3.c/sqlite3.h modified to use "_Decimal64"
Hello !then
I did a modification to sqlite3 that basically define a new type
"sqlite_double" and use it instead of "double" (#define sqlite_double?
double) then I can redefine it to _Decimal64 (#define sqlite_double?
_Decimal64) this way with a modern C compiler we can have sqlite3 using
decimal
If I understood correctly when no transaction is specified an implicit
transaction is created so there is no point to create a transaction for only
one statement.
Cheers !
> Tue Dec 08 2015 5:51:35 am CET CET from "Simon Slavin"
> Subject: Re: [sqlite] NOP INSERT still writes to the
>DB/jour
This way we repeat the string everywhere then it's not DRY !
Cheers !
> Wed Nov 25 2015 11:38:50 am CET CET from "Simon Slavin"
> Subject: Re: [sqlite] Dont Repeat Yourself (DRY) and
>SQLite
>
> On 24 Nov 2015, at 11:24pm, Domingo Alvarez Duarte
> wrote
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'" ju
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:
>
&
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'
C
Hello !
I'm writing an application that uses the column declared/type to do some
formatting, it's working fine for tables and most views except when the
column is calculated like:
SELECT id, name, (weight / height) AS pseudo_mass? FROM people;
I can get the column type of "id" and "name" b
Hello !
This is a real simple bug fix but it seems that no one is caring about it !!!
Cheers !
> Sat Sep 12 2015 8:52:04 pm CEST CEST from "chris0e3"
>Subject: [sqlite] Bug in SQLite 3.8.11.1 source code
>
> Hello,
>
> I was just looking at updating to SQLite 3.8.11.1 when I spotted wha
Hello !
Thank you for all feedbacks to this problem !
I originally gave a wrong/difficult example, the original one that other gave
here use the equality operator instead of inequality.
___test-random.sql
create table a(a);
insert into a values (1),(2),(3);
select 'direct select ='
Hello !
Due the way sqlite manages it's source code (with fossil-scm) I propose to
anyone that has any extension/custom sqlite code fork this project on github:
https://github.com/mackyle/sqlite
And publish it there.
Cheers !
> Sun Sep 13 2015 6:42:27 am CEST CEST from "Charles Leifer
Hello !
Thank you for the good example and explanation !
Cheers !
> Sun Sep 13 2015 2:06:14 am CEST CEST from "James K. Lowden"
> Subject: Re: [sqlite] Third test of json and
>index expressions, now it works
>
> On Wed, 09 Sep 2015 09:56:12 +0200
> &qu
Hello !
Looking at the documentation of json* functions and after see the example
given for a query on a field mixed owith string and json_array:
___ SELECT name FROM user WHERE phone LIKE '704-%' UNION SELECT
user.nameFROM user, json_each(user.phone) WHERE json_valid(user.phone)
Hello !
I did some tests to see how the json* functions behave when we specify
collation to the column that stores it and it seems that the json* functions
ignore the collation completely, is that by design or is it a forgotten
implementation ?
___
CREATE TABLE a(j collate nocase);
INS
Hello !
That's what json_extract does !
json_extract('[2,3,4,5,6]', '$[3]'); --> returns 5;
Cheers !
> Fri Sep 11 2015 8:40:36 pm CEST CEST from "Nelson, Erik - 2"
> Subject: Re: [sqlite] Feedback request:
>JSON support in SQLite
>
> Richard Hipp wrote on Friday, September 11, 2015 11
Hello !
I'm looking at these new extensions/functions and noticed that from scripting
languages to be able to use sqlite3_value_subtype we'll need access to
sqlite3_context parameters by index, because from the scripting language once
we are called there is no easy way from the script to refer t
Hello !
I think that this can be of interest for some sqlite users
https://google.github.io/lovefield/ .
Cheers !
Hello !
This fix the example I gave on the other email it was "r <> r" but it's more
easy to see the problem with "r = r".
Nice explanation !
With your knowledge could you give your view about how evaluation of
calculated/function columns should be done to have correct results.
Like ano
Hello !
Nice explanation !
With your knowledge could you give your view about how evaluation of
calculated/function columns should be done to have correct results.
Like another example given on other thread:
_
CREATE TABLE a(b);
INSERT INTO a(b) VALUES(1),(2),(3);
SELEC
-----------
> On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
>
>>Hello !
>>
>> After seem several emails from a user asking about how to use sqlite3
>>through
>> shell scripts, I remember my experiences with sqlit
Hello !
It's nice that you shared this, probably if you also host/mirror the on
github you'll get an even broader audience and probably feedback and
enhancements.
I suggest to to fork this project https://github.com/mackyle/sqlite it's
almost daily updated with the https://www.sqlite.org and
Hello !
After seem several emails from a user asking about how to use sqlite3 through
shell scripts, I remember my experiences with sqlite3 but didn't mind to
report it, but now I think that it's worth mention it because it'll hurt
several users.
The bug/problem is that the sqlite3 command li
Hello !
Now I realize that in the function re_balanced I also need restore the
ReInput back in case of not matching:
___
static int re_balanced(
??? ReInput *p,
??? int cb, /* char that opens a balanced expression */
??? int ce? /* char that closes a balanced expression */
){
? int c =
Hello !
I'm trying to add a new option to ext/misc/regex.c it's "\p()" that's like
the LUA operator "%b"
%b/xy/, where /x/ and /y/ are two distinct characters; such item matches
strings that start with?/x/, end with?/y/, and where the /x/ and /y/ are
/balanced/. This means that, if one reads
Hello !
Sqlite like any other project has developed a lot of cross platform/utilities
code but most of it remain locked down to sqlite3 alone.
Example the latest json1 functions, vfs, sqlite3_printf, thread/lock
management functions, ..., would be nice if they could also be used by the
sqlite
Hello !
I forgot to include this topics on my wish list:
Sqldiff is an amazing tool added to sqlite, but it can be even more useful if
it's integrated on sqlite itself, right know sqldiff needs two databases to
operate, but if included in sqlite3 somehow it could generate the diffs on
the go
Hello !
Sqlite is really a very nice piece of software and it could even be better,
here is things to add to the wish list:
- Implement sql "CREATE FUNCTION" even if it is as simple of only allowing a
limited set of operations, refactoring the actual trigger implementation to
allow it be call
ndex expressions
>not allowed ?
>
> On 9/7/15, Domingo Alvarez Duarte wrote:
>
>>Hello !
>>
>> I'm converting a database from postgresql and when I tried to create this
>> index it fails:
>>
>> CREATE UNIQUE INDEX country_name_idx on countr
Hello !
I'm converting a database from postgresql and when I tried to create this
index it fails:
CREATE UNIQUE INDEX country_name_idx on country(lower(name));
Error: "non-deterministic functions prohibited in index expressions"
Can the lower function be non deterministic ?
I know th
Hello !
Here is the parser code for the "SQL PROCEDURE" anyone have any comment on it
?
_
/ The SQL PREPARE statement /
%ifndef SQLITE_OMIT_SQL_PREPARED
cmd ::= create_prepared.? {sqlite3SqlPreparedFinishParse(pParse,0, 0);}
cmd :
Hello !
I just cloned a fresh sqlite3 repository from
https://github.com/mackyle/sqlite and after executing "./configure
--disable-amalgamation; make" I'm get this error on ubuntu 14.04:
make: *** No rule to make target
`/home/mingo/dev/dadbiz++/third-party/sqlite3-git/manifest.uuid', needed
Hello !
That's awesome !
We can write cleaner queries with this.
Thank you so much !
Cheers !
> Sun Sep 06 2015 12:39:03 am CEST CEST from "Richard Hipp"
> Subject: Re: [sqlite] Second test of json and index
>expressions, not so good
>
> On 9/4/15,
ird test of json and index expressions, now it works
>
> On 2015-09-05 08:58 PM, Domingo Alvarez Duarte wrote:
>
>>Hello !
>>
>> Again your proposition doesn't stand up !
>>
> Again?
> That was my first post on the subject and it was in reply to
Hello !
It seems that you mix apples to oranges and try to compare then.
One thing is formal grammar/syntax and the other is
implementation/optimization/execution.
The fact that sqlite does things in one way now do not mean it's the
best/definitive way of implement things and that can be c
Hello !
Again your proposition doesn't stand up !
There is no "waste of cpu cycles" for work that is not done, I mean for the
ones that write queries for the machine instead to the humans, they will
continue to have the same results (less parsing steps, besides the parsing
normally accounts t
Hello !
Although your explanation is interesting it doesn't seems to be correct:
First of all there is no point to to do anything if the whole sql statement
is no correct.
I mean:
- If there is any reference to inexistent columns/functions/tables/views
- If any expression is not vali
egory as manifest typing, where a more liberal
> approach is an improvement?
>
>
> On Fri, Sep 4, 2015 at 9:35 PM, Richard Hipp wrote:
>
>
>>On 9/4/15, Domingo Alvarez Duarte wrote:
>>
>>
>>>Would be nice to sqlite be able to recognize aliases
Hello !
And here is the database dump for the example:
___
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, json_misc TEXT);
INSERT INTO "users" VALUES(1,'Domingo','{"color":"cyan", "has_eyes": true}');
CREATE TABLE "unql_users"(x);
>
> Em 04/09/2015 17:09, "Domingo Alvarez Duarte"
> escreveu:
>
>
>>Hello !
>>
>> This is my first test with json and index expressions !
>>
>> How to make usage of the expression index on queries ?
>>
>> Why is json_extra
Hello !
I did for one project a kind of sqlite3 extension using UNQL
(http://unql.sqlite.org/index.html/wiki?name=UnQL) it has some flaws (and I
did some bug fixes) like only performing linear scan on collections, but now
with index expressions support on sqlite3 it could be revived back with
in
alue_33' = json_extract(json, path);
COMMIT;
_____
> Sat Sep 05 2015 6:35:08 am CEST CEST from "Richard Hipp"
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
> On 9/4/15, Domingo Alvarez Duarte wrote:
>
>
>>Would
Cheers !
> Sat Sep 05 2015 11:16:26 am CEST CEST from "Simon Slavin"
> Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
> On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte
> wrote:
>
>
>>What about the warning messages t
e_value_33' =
json_extract(json, '$.value');
EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_view WHERE 'the_value_33' =
val;
EXPLAIN QUERY PLAN
WITH RECURSIVE above33 AS (SELECT * FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE json_extract(json,
Hello !
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> --
>
> ?
>
>
What about the warning messages to stderr through sqlite3 when opening
databases with invalid
Hello again !
I was trying to use the alias in the where clause hopping sqlite would be
smart enough to recognize it and use the index but that doesn't happen.
So I tried again repeating the expression instead of using the alias and now
sqlite recognize the index and run very fast.
Would b
Hello !
I was not been fair with my raw select against json_extract now I'm doing it
one by one and the difference with json_extract is basically none.
But I still could not manage to use the index expression to speedup the
query.
__Output
Time to insert??? 5000??? 0.03179??? re
Hello again !
Well it's slow partly because of the linear scan but there are not too many
records 5000.
I also tried with "indexed by" but:
___
sql = "select? json_extract(json, '$.value') AS val? from json_tbl indexed
by json_tbl_idx where val = ?;";
showPlan();
?
AN ERROR HA
Hello !
Here is the database dump with 50 records:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
INSERT INTO "json_tbl" VALUES(1,'{"id" : 1, "value" : "the_value_1"}');
INSERT INTO "json_tbl" VALUES(2,'{"id" : 2, "value" :
Hello !
This is my first test with json and index expressions !
How to make usage of the expression index on queries ?
Why is json_extract so slow ?
Cheers !
Output
Time to insert??? 5000??? 0.032227??? records by second = ???
155149
count=??? 4999
json=??? the_value_1
???
?
?
Example:
sqlite3 a_db_that_uses_malformed_sql.db
-- warning
-- use of single quotes on field names are not standard
-- the_index_table_has_the_problem
sqlite3>
?
Cheers !
?
> Fri Sep 04 2015 11:40:11 am CEST CEST from "Domingo Alvarez Duarte"
>
e] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 10:13 PM, Domingo Alvarez Duarte wrote:
>
>>Hello again !
>>
>> I looked at the documentaion again and realized that I was alread calling
>> sqlite3_close_v2 the I commented out all of th
Hello again !
I think that would be interesting to have functions similar to
sqlite3_next_stmt/sqlite3_finalize for extensions something like this:
sqlite3_next_extension / sqlite3_finalize_extension
?
Cheers !
> Fri Sep 04 2015 5:13:23 pm CEST CEST from "Domingo Alvare
a lot for your help !
> Fri Sep 04 2015 4:57:57 pm CEST CEST from "Domingo Alvarez Duarte"
> Subject: Re: [sqlite] SQLite3 trunk error with
>old database with fts3/4
>
> Hello !
>
> I did something similar to your sugestion (sqlite3_next_stmt(db, NULL))?
&
etect
it and skip it ?
Or any way to safely have a central point to do a cleanup ?
Cheers !
> Fri Sep 04 2015 4:44:02 pm CEST CEST from "Dan Kennedy"
> Subject: Re: [sqlite] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 09:29 PM, Domingo Alvarez Du
sql(statement));
??? sqlite3_finalize(statement);
??? count++;
??? }
?
> Fri Sep 04 2015 4:33:13 pm CEST CEST from "Dan Kennedy"
> Subject: Re: [sqlite] SQLite3 trunk error with old
>database with fts3/4
>
> On 09/04/2015 09:18 PM, Domingo Alvarez Duarte wrote:
>
_release:stmt:%s\n",
sqlite3_sql(statement));
??? sqlite3_finalize(statement);
??? count++;
??? }
??? if (count) return sq_throwerror(v, _SC("closing database with
%d statements not closed."), count);
?
> Fri Sep 04 2015 4:18:01 pm CEST CEST from &quo
k error with old
>database with fts3/4
>
> On 09/04/2015 07:35 PM, Domingo Alvarez Duarte wrote:
>
>>Hello !
>>
>> After fix the index issues using an old sqlite3 executable (the trunk
>>refuse
>> to work on indexes created with single quotes on field
fixed on trunk.
Cheers !
> Fri Sep 04 2015 3:40:18 pm CEST CEST from "Dominique Devienne"
> Subject: Re: [sqlite] Changes to create index on
>trunk
>is breaking old code
>
> On Fri, Sep 4, 2015 at 2:54 PM, Richard Hipp wrote:
>
>
>>On 9/4/1
Hello !
After fix the index issues using an old sqlite3 executable (the trunk refuse
to work on indexes created with single quotes on field names) I'm getting
ocasionaly memory errors when using fts3/4 searches, see error below:
free(): corrupted unsorted chunks: 0x7fa3a01073a0
?
Ch
Hello !
I'm testing sqlite3 trunk on existing code and noticed that the changes to
"create index" using expressions/functions is breaking on old databases.
It seems that because I used the field name single quotes now it's
considering it an expression.
On an old database I have the followi
Hello !
I was looking at this particular commit
https://www.sqlite.org/src/info/0ea6e5c9fc6b1dd1 then I realize the usage of
magic number through sqlite3 source code like the one bellow, it's not good
practice to avoid then ?
Cheers !
= case PragTyp_STATS: { static const char *azCol
Hello !
Also there is no way to link the callback to specific statement, I think that
an extra parameter with the whole sql statement would be needed and/or any
other way to link several calls to a statement.
Maybe something like: int sqlite3_set_authorizer(sqlite3*,int
(*xAuth)(void
the proposal
of aquameta.
?
Cheers !
> Sat Aug 29 2015 7:41:48 pm CEST CEST from Domingo Alvarez Duarte Subject:
>Aquameta Layer 0: meta - Writable System Catalog for PostgreSQL
>
>
>
>Hello !
>
>It would be nice to have something like this on sqlite too !
>
>http://blog.aquameta.com/2015/08/29/intro-meta/
>
>Cheers !
>
>
>
>
?
;Writable System Catalog for PostgreSQL
>
> On 2015-08-29 10:41 AM, Domingo Alvarez Duarte wrote:
>
>>It would be nice to have something like this on sqlite too !
>>
>> http://blog.aquameta.com/2015/08/29/intro-meta/
>>
> I won't argue that it is
Hello !
It would be nice to have something like this on sqlite too !
http://blog.aquameta.com/2015/08/29/intro-meta/
Cheers !
Now that we have solved the expression column reevaluation problem on the
"ORDER BY" clause what about the same problem on the "WHERE" clause ?
CREATE TABLE myTable (a INTEGER);
INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable;
SELECT
On this case:
select random(), random() from blah order by random()
?
Error ambiguous column "random()" near "order by".
Cheers !
?
> Thu Aug 27 2015 6:48:54 pm CEST CEST from "Scott Robison"
> Subject: Re: [sqlite] order by not working in
>combination with random()
>
> On Thu,
A very instructive post, could you give your opinion about what should be the
behavior for the "WHERE" clause ?
I meam if we have a function on the field definition and reference it on the
"WHERE" clause:
CREATE TABLE myTable (a INTEGER);
INSERT INTO myTable VALUES (1),(2),(3),(4),(5);
try to use the field names since the order of the
> field names COULD change.
>
>
> On Wed, Aug 26, 2015 at 10:12 AM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
>
>
>>Hello !
>>
>> Three possibilities:
>>
>>
I just saw this commit http://www.sqlite.org/src/info/c2f3bbad77850468 and
the same principle probably should apply to the where clause ?
SELECT rr FROM myView WHERE rr < 30 ORDER BY rr;
Cheers !
?
This assumption is a bit naive :
In SQLite, this cannot happen because queries execute infinitely fast
(as far as the built-in date/time functions are concerned).
There is different cpus with different processing power, load variations, io
access latency, ...
Cheers !
?
> Wed Aug 26
EST from "Simon Slavin"
> Subject: Re: [sqlite] Why sqlite show qualified
>column names when selecting from views ?
>
> On 26 Aug 2015, at 10:17am, Domingo Alvarez Duarte
> wrote:
>
>
>>But like it's already done on several other cases why not fix t
I tested this on postgresql and I get a correctly ordered list for "SELECT rr
FROM myView ORDER BY rr;"
So I also will say that the sqlite behavior of reevaluate columns function
calls on "order by" is a bug.
Cheers !
> Wed Aug 26 2015 9:50:48 am CEST CEST from "Simon Slavin"
> Subject: R
I just tested this on postgresql and I always get unqualified column names as
I would expect.
I remember this was a lot worst in previous sqlite versions and I needed to
play with two compile time/pragma flags and depending on that combination we
got some bizarre results.
I understand that th
I'm seeing this thread for a while and don't remember anyone mentioning the
sqlite3_backup_* api functions, isn't that what are you looking for ?
Cheers !
On Thu, May 29, 2014 at 2:25 PM, E.Pasma wrote:
> Op 29 mei 2014, om 11:59 heeft big stone het volgende geschreven:
>
> Hello,
>>
>> I wou
The parallel usage would be a bonus if sqlite with threads could in the
future be instructed to use more available cores on the system.
On Thu, May 29, 2014 at 10:46 AM, big stone wrote:
> Hi Domingo,
>
> Oracle is doing also this sort of things with there / * parallel */ comment
> keyword.
>
>
Here is a link to a document describing the "COMMENT" syntax
http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4009.htm#i2119719
On Thu, May 29, 2014 at 10:34 AM, Domingo Alvarez Duarte wrote:
> Hello all !
>
> Now that we are talking about comments in sql
Hello all !
Now that we are talking about comments in sql statements why not extend
sqlite to accept the "COMMENTS" keyword for fields and tables and then we
will have a standard way to store extra information about our sql schema.
Cheers !
On Thu, May 29, 2014 at 10:10 AM, Luuk wrote:
> On 2
You should provide a test case to get help from other people !
On Tue, Apr 8, 2014 at 11:28 AM, Ashok Pitambar wrote:
> Hi All,
>
> While executing query multiple times in a loop I encountered crash in
> function sqlite3MallocSize() which is called from sqlite3_free(). I see
> that
> an invalid
Hello !
I did a modification to the LUA regex code to allow using it without
LUA dependency so it can be used with sqlite as regex function.
It's very light and small thus a good candidate to be included on sqlite.
If you are interested on it you can download it here
http://code.google.com/p/lua
201 - 286 of 286 matches
Mail list logo
