[sqlite] How to build a Windows DLL with cygwin
Greetings! I have been a Cygwin user since its infancy, and I have been looking for a way to build SQLite Windows DLL with Cygwin, and I just found how to do this. I know I can use MinGW, which is the way I was doing it, but if you have Cygwin, you can just follow this steps: 1. use your setup to get the basic MinGW Cygwin libraries and utilities Install mingw64-i686-gcc-core 7.4.0-1 Install mingw64-i686-gcc-g++ 7.4.0-1 or the 64 bit version, if you are going to build the 64b. 2. download the sqlite3 amalgation[1] 3. untar 4. run this command, jcabrera@elimelec ~/builds/sqlite/sqlite-snapshot-201905242258 $ i686-w64-mingw32-gcc -shared -static-libgcc sqlite3.c -o sqlite3.dll 5. jcabrera@elimelec ~/builds/sqlite/sqlite-snapshot-201905242258 $ ls -l sqlite3.dll -rwxr-xr-x 1 jcabrera None 1.1M Jun 10 22:38 sqlite3.dll* And that's it. Just thought I'd share this for the Cygwin users. Thanks. josé [1] https://sqlite.org/snapshot/sqlite-snapshot-201905242258.tar.gz ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected/undocumented REPLACE() behavior
With most functions, including replace(), if any of the arguments are null,
it returns null.
On Mon, Jun 10, 2019 at 4:29 PM Tony Papadimitriou wrote:
> Example:
>
> --- CUT ---
> create table t(s text);
> insert into t values ('1'),('null'),('3');
>
> .print 'BEFORE'
> select rowid,* from t;
> update t set s = replace(s,'null',null)
> --where s = 'null' --adding this works of course but that’s not my point
> ;
>
> .print 'AFTER'
> select rowid,* from t;
> --- CUT ---
>
> The documentation says: “The replace(X,Y,Z) function returns a string
> formed by substituting string Z for every occurrence of string Y in string
> X. The BINARY collating sequence is used for comparisons. If Y is an empty
> string then return X unchanged. If Z is not initially a string, it is cast
> to a UTF-8 string prior to processing.”
>
> “substituting string Z for every occurrence of string Y” implies that if
> there is no occurrence of string Y nothing should happen to the original
> string, right?
>
> Accordingly, my expectation is that either:
> 1. null will remain null as there is not really a string (even empty) that
> can truly represent it, or
> 2. we allow null to be converted to empty string so that the “returns a
> string” requirement can be satisfied.
>
> or, maybe
> 3. using null for the Z part gives an error.
>
> In either [1] or [2] above, however, if the target string (Y part) is not
> found, the result should be unaltered.
> The replacement seems to occur regardless of the target being found or not.
>
> ... and everything becomes null.
>
> Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with
> it also, and got the same unexpected behavior.
> From a quick look, I haven’t seen anything in their documentation on
> REPLACE to justify it either.
>
> I consider this behavior wrong, or (easier way out) the documentation
> should make a special note about null behaving the way it does.
>
> Thank you.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unexpected/undocumented REPLACE() behavior
Example:
--- CUT ---
create table t(s text);
insert into t values ('1'),('null'),('3');
.print 'BEFORE'
select rowid,* from t;
update t set s = replace(s,'null',null)
--where s = 'null' --adding this works of course but that’s not my point
;
.print 'AFTER'
select rowid,* from t;
--- CUT ---
The documentation says: “The replace(X,Y,Z) function returns a string formed by
substituting string Z for every occurrence of string Y in string X. The BINARY
collating sequence is used for comparisons. If Y is an empty string then return
X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior
to processing.”
“substituting string Z for every occurrence of string Y” implies that if there
is no occurrence of string Y nothing should happen to the original string,
right?
Accordingly, my expectation is that either:
1. null will remain null as there is not really a string (even empty) that can
truly represent it, or
2. we allow null to be converted to empty string so that the “returns a string”
requirement can be satisfied.
or, maybe
3. using null for the Z part gives an error.
In either [1] or [2] above, however, if the target string (Y part) is not
found, the result should be unaltered.
The replacement seems to occur regardless of the target being found or not.
... and everything becomes null.
Note: I know SQLite3 tries to mimic in some ways Postgres, so I tried with it
also, and got the same unexpected behavior.
From a quick look, I haven’t seen anything in their documentation on REPLACE to
justify it either.
I consider this behavior wrong, or (easier way out) the documentation should
make a special note about null behaving the way it does.
Thank you.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected parsing of an invalid CREATE TABLE statement
Forgiving, yes, but usually not /that/ forgiving. It's certainly caused some wasted time going down the wrong path trying to debug an issue. On Mon, Jun 10, 2019 at 3:39 PM Richard Hipp wrote: > On 6/10/19, Shawn Wagner wrote: > > Consider: > > > > CREATE TABLE a(id INTEGER PRIMARY KEY); > > CREATE TABLE b(id INTEGER PRIMARY KEY); > > CREATE TABLE c(id INTEGER PRIMARY KEY, a_id, b_id, > >FOREIGN KEY (a_id) REFERENCES a(id) > >FOREIGN KEY (b_id) REFERENCES b(id)); > > > > Note the lack of comma between the two foreign key constraints in the > > definition for table c. The syntax diagrams in the documentation indicate > > that the comma is mandatory, but not only does this not cause a parse > > error, but both of them are detected: > > > > The parser in SQL is very forgiving. Does this cause some kind of problem? > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexpected parsing of an invalid CREATE TABLE statement
On 6/10/19, Shawn Wagner wrote: > Consider: > > CREATE TABLE a(id INTEGER PRIMARY KEY); > CREATE TABLE b(id INTEGER PRIMARY KEY); > CREATE TABLE c(id INTEGER PRIMARY KEY, a_id, b_id, >FOREIGN KEY (a_id) REFERENCES a(id) >FOREIGN KEY (b_id) REFERENCES b(id)); > > Note the lack of comma between the two foreign key constraints in the > definition for table c. The syntax diagrams in the documentation indicate > that the comma is mandatory, but not only does this not cause a parse > error, but both of them are detected: > The parser in SQL is very forgiving. Does this cause some kind of problem? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unexpected parsing of an invalid CREATE TABLE statement
Consider: CREATE TABLE a(id INTEGER PRIMARY KEY); CREATE TABLE b(id INTEGER PRIMARY KEY); CREATE TABLE c(id INTEGER PRIMARY KEY, a_id, b_id, FOREIGN KEY (a_id) REFERENCES a(id) FOREIGN KEY (b_id) REFERENCES b(id)); Note the lack of comma between the two foreign key constraints in the definition for table c. The syntax diagrams in the documentation indicate that the comma is mandatory, but not only does this not cause a parse error, but both of them are detected: sqlite> PRAGMA foreign_key_list(c); id seq table fromto on_update on_delete match -- -- -- -- -- -- -- -- 0 0 b b_idid NO ACTION NO ACTION NONE 1 0 a a_idid NO ACTION NO ACTION NONE ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimising multiple group by clauses
Hello, > explain query plan select > prod, > per, > min(val) > from > (select >prod, >per, >mar, >sum(val) as val > from >data > group by >prod, >per, >mar) > group by > prod, > per > ; > QUERY PLAN > |--CO-ROUTINE 1 > | `--SCAN TABLE data USING INDEX sqlite_autoindex_data_1 > |--SCAN SUBQUERY 1 > `--USE TEMP B-TREE FOR GROUP BY I constructed an equivalent query that does not involve a temporary B-tree. I don't expect this to be useful for the real case. But it proves that speed can be improved. With 1000*100*10*1 rows in the data table, my timing was reduced from 0.859 to 0.635. E. Pasma explain query plan select prod, per, (select min(val) from (select sum(val) as val from data where (prod,per)=(v1.prod,v1.per) group by prod, per, mar)) from (select prod, per from data group by prod, per) v1 ; QUERY PLAN |--CO-ROUTINE 3 | `--SCAN TABLE data USING COVERING INDEX sqlite_autoindex_data_1 |--SCAN SUBQUERY 3 AS v1 `--CORRELATED SCALAR SUBQUERY 2 |--CO-ROUTINE 1 | `--SEARCH TABLE data USING INDEX sqlite_autoindex_data_1 (prod=? AND per=?) `--SEARCH SUBQUERY 1 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to insert the BLOB in database?
On 10-6-2019 05:08, Mark Halegua wrote: On Monday, June 10, 2019 03:46:02 AM Simon Slavin wrote: On 10 Jun 2019, at 3:44am, Mark Halegua wrote: I probably should figure this out, but in a GUI, how do I recover a graphic from the database? Programming. SQLite can't do it since it doesn't even understand that that sequence of octets is a graphics. How you do it in programming depends on your development environment and libraries. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users I'm using Python. What would the programming sequence be to display the graphic in that language? Mark mARK google for: python show picture from database sqlite https://stackoverflow.com/questions/30818728/retrieve-image-from-sqlite3-database-and-directly-display-on-kivy-window ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
