[sqlite] If I got many to many relationship data first, how do I insert them to my table?

[邱朗]

"Another option is to create an "Unknown" customer, and link any new  orders to 
it. You can easily change that parent-id on the order later."This solution 
seems not work (especially in my case) because it is easy to have more than one 
unknown customer. Then I can't decide who orders what later.


The other solution (from stackoverflow) is to use "Deferred Foreign Key 
Constraints". That seems quite promising. 



Thanks,
Qiulang



At 2017-03-31 19:44:56, "R Smith"  wrote:
>
>On 2017/03/31 12:08 PM, 邱朗 wrote:
>> Say my mobile app has a customer table, a product table, and an order table 
>> to record who buys what, the order table basically has 2 foreign keys, 
>> customer_id & product_id.
>>
>> Now I got the order information first, within in it I can't find the 
>> customer information in my local sqlite table. As it turns out this is a new 
>> customer, whose information will come later from another thread/queue. To 
>> make things even worse the customer id I got from the order information is 
>> not the same one I used locally. My local customer id is INTEGER PRIMARY KEY 
>> (I do record that "true customer id" as another column and set index on it)
>>
>> So how I do record this order information? I can come up with some clumsy 
>> solution, e.g. if I can't find contact info, I insert a record for it first. 
>> Later after I get the real information for this customer, I update customer 
>> & order table. But I was wondering is there any "standard" way for the 
>> situation like this?
>> BTW, I did ask the same question at stackoverflow, but because I use sqlite 
>> (while all the data come from web storing in MySQL) I was wondering if 
>> sqlite has any specific solution for it.
>
>Your question would arise no matter which DB you use, so it's not really 
>an SQLite question - but - we're a fun bunch of people, and many here 
>would have run into the same problem, so you might find some answers.
>
>To start with, Your "clumsy" idea is not so clumsy, it is a practice 
>some people use. The flaw in that is when the customer actually existed 
>already, you just didn't know who it was, so now you end up with 2 
>records that mean the same customer with disjointed keys and Orders 
>connected to both.
>
>What we usually do is put any orders that are new in a table that looks 
>exactly like the orders table (only without any foreign key constraints 
>and such) and then move them to the main order table as soon as they are 
>confirmed and connected to a customer. This also helps if this is, for 
>instance, an online interface or help-desk type system and people can 
>make orders which they might still cancel or amend, etc.
>The biggest problem with this method is that for queries on "current 
>orders" you would need to join the output from the two tables - easy 
>enough to do, but if you already have millions of lines of code, it 
>could involve a lot of changes.
>
>Another option is to create an "Unknown" customer, and link any new 
>orders to it. You can easily change that parent-id on the order later.
>
>If however your problem boils down to you not being "sure" if you have 
>now the correct parent (at the point of insert) - we can't help you with 
>that. You have to either be sure, or use a temporary situation until you 
>are.
>
>Whatever the solution you find, at the point stuff gets inserted to a 
>permanent table with foreign key constraints, all the correct 
>constraint-related information/links has to be known.
>
>HTH - Cheers,
>Ryan
>
>___
>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] Incompatibility into configure.ac

[Scott Robison]

On Tue, Apr 4, 2017 at 9:52 PM, Jens Alfke  wrote:
>
>> On Apr 4, 2017, at 8:33 PM, Pavel Volkov  wrote:
>>
>> bash it's Bourne again shell. Not Bourne shell. Bash is more then POSIX
>> shell.
>
> Yes, that’s what they said. The Bourne shell is ‘sh’.
>
>> And you forgot the FreeBSD in your listing. As example. It does not use bash 
>> at all.
>
> It doesn’t come with bash installed (presumably as an aftereffect of the 
> ancient religious war between BSD and System V; tcsh was a BSD invention.) 
> But so what? You can easily install it through a package manager. If that 
> means SQLite has a dependency on it, that’s nothing awful; most software has 
> dependencies on other software, and package managers track those dependencies 
> very well.
>
> The issue here seems to be that some scripts in the SQLite source 
> distribution are _implicitly_ assuming that the default shell is bash, or 
> else that ‘sh’ is an alias of bash. The best fix, IMHO, would be to make 
> those scripts explicitly invoke bash, using a shebang or whatever.

I'm not as conversant with posix systems, but there isn't a standard
location for bash on systems where it is not the default shell. Does
sh not provide the necessary functionality with some other syntax?

>
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incompatibility into configure.ac

[Jens Alfke]

> On Apr 4, 2017, at 8:33 PM, Pavel Volkov  wrote:
> 
> bash it's Bourne again shell. Not Bourne shell. Bash is more then POSIX
> shell.

Yes, that’s what they said. The Bourne shell is ‘sh’.

> And you forgot the FreeBSD in your listing. As example. It does not use bash 
> at all.

It doesn’t come with bash installed (presumably as an aftereffect of the 
ancient religious war between BSD and System V; tcsh was a BSD invention.) But 
so what? You can easily install it through a package manager. If that means 
SQLite has a dependency on it, that’s nothing awful; most software has 
dependencies on other software, and package managers track those dependencies 
very well.

The issue here seems to be that some scripts in the SQLite source distribution 
are _implicitly_ assuming that the default shell is bash, or else that ‘sh’ is 
an alias of bash. The best fix, IMHO, would be to make those scripts explicitly 
invoke bash, using a shebang or whatever.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incompatibility into configure.ac

[Pavel Volkov]

Hello.
bash it's Bourne again shell. Not Bourne shell. Bash is more then POSIX
shell.
And you forgot the FreeBSD in your listing. As example. It does not use
bash at all.
Thank you.

On Apr 4, 2017 4:34 PM, "Gary R. Schmidt"  wrote:

> On 04/04/2017 23:20, Richard Hipp wrote:
>
>> On 4/4/17, Pavel Volkov  wrote:
>>
>>> The "+ =" operator works as you would expect in bash only.
>>> And it causes an error in the Bourne shell, for example.
>>>
>>
>> You have piqued my curiosity.  Who is still using Bourne shell instead
>> of the Bourne-again shell (bash)?  Bash has been with us now for like
>> three decades, right?
>>
>> Anyone who is supporting Solaris/AIX/HP-UX or older systems.
>
> Is SQLite going the way of "Sure it's portable, it works on Fedora Core
> *and* Ubuntu"???
>
> Cheers,
> GaryB-)
> ___
> 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] Vacuum results in larger database after running pragma integrity_check

[Richard Hipp]

On 4/4/17, Richard Hipp  wrote:
> On 4/4/17, Ben Newberg  wrote:
>> I've noticed with 3.18.0 that it's possible to make a database increase
>> in
>> size after running pragma integrity_check (which returns "ok") and then
>> running vacuum.
>
> I can now repro the behavior and have bisected to this check-in:
> https://www.sqlite.org/src/timeline?c=aa02bd
>
> Still do not understand how a (read-only) integrity_check might affect
> a subsequent VACUUM operation, however.

Dan has now checked in a fix for the problem on trunk.

   https://www.sqlite.org/src/timeline?c=e5bb7d

Because this problem never results in an incorrect answer (as far as
we can determine) only a sub-optimal vacuum, we are not calling it a
bug.

-- 
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

Re: [sqlite] Vacuum results in larger database after running pragma integrity_check

[Richard Hipp]

On 4/4/17, Ben Newberg  wrote:
> I've noticed with 3.18.0 that it's possible to make a database increase in
> size after running pragma integrity_check (which returns "ok") and then
> running vacuum.

I can now repro the behavior and have bisected to this check-in:
https://www.sqlite.org/src/timeline?c=aa02bd

Still do not understand how a (read-only) integrity_check might affect
a subsequent VACUUM operation, however.
-- 
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

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

[Stadin, Benjamin]

Hi Peter,

Wrapping the bbox was solely for the purpose of additional (and optional) 
performance. At the moment, this column would be optional and used instead of 
the geopackage geometry in some situations (e.g. improve rtree indexing by 
avoiding to create the bbox for every geometry). The Geopackage geometry 
currently contains an envelope, next to the srid and various otrher things: 
http://www.geopackage.org/spec/#gpb_spec

Some of this can be improved size-wise and made leaned more towards existing 
standards (e.g. pack the envelope also as WKB).

I’ve just learned about requirements imposed by some aspects of the standard 
where unfortunately the srid needs to be part of the geometry. But still, the 
overhead of the current struct is significant, as well as redundant info about 
srid at other places as well (gpkg_contents, again in gpkg_geometry_columns and 
in the geometry itself). I’m actually trying to create a proposal for a few 
breaking changes for a version the, though probably chances are low it’s ever 
considered.

Though this discussion belongs to another user group now.

Regards
Ben

Am 04.04.17, 17:05 schrieb "sqlite-users im Auftrag von Peter Aronson" 
:

If you're creating GeoPackages with the F.3 RTREE Spatial Indexes 
extension, you do not "wrap" a bounding box. You need to define 5 
functions from SQL/MM -- ST_MinX, ST_MaxX, ST_MinY, ST_MaxY and 
ST_IsEmpty -- that take a geometry blob as input and return (for the 
first four) a floating point number or an integer value of 0 or 1 
(ST_IsEmpty). Now it is possible you might want to create some sort of 
side cache so you don't have to parse the geometry blob four times, but 
that is an implementation issue. The exact text of the triggers is 
specified by the standard and is not optional if you are using the 
extension. And you certainly could not use standard WKB values in the 
geometry columns without completely violating the GeoPackage standard.

Peter

On 4/3/2017 9:26 AM, Stadin, Benjamin wrote:
> Hi,
>
> Is there a hook which allows to get notified as soon as it’s save to 
modify a db connection after (or as alternative to) sqlite3_update_hook was 
triggered?
>
> The background to this question is that I’m trying to prepare a proposal 
for Geopackage. And one of the questions is if it’s possible to avoid 
Geopackage using a wrapper object [1] which is used in combination with user 
defined functions to update the rtree index after a change to the geometry 
table occurred. This object is currently necessary because it wraps another 
object (an envelope / bbox) to be used by the user defined function when the 
SQL update trigger is called.
>
> So my idea is to register an update hook and do the update to the rtree 
index directly after the step(). So this could execute an arbitrary statement, 
or more concrete a statement which would read an envelope column from the 
geometry table where the update occurred and update the rtree table 
accordingly. This would allow those columns to be plain and widely adopted WKB 
fields instead of this Geopackage binary wrapper object.
>
> Regards
> Ben
>
> [1] http://www.geopackage.org/spec/#gpb_spec
>
>
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vacuum results in larger database after running pragma integrity_check

[Richard Hipp]

Can you email me the database that does this?

On 4/4/17, Ben Newberg  wrote:
> I've noticed with 3.18.0 that it's possible to make a database increase in
> size after running pragma integrity_check (which returns "ok") and then
> running vacuum.
>
> Alternatively, vacuuming without running pragma integrity_check first keeps
> the database the same size as before.
>
> The page size on the database in question is 1024. The database starts with
> no pages in the freelist. I can't reproduce this with 3.17.0.
>
> SQLite version 3.18.0 2017-03-28 18:48:43
> Enter ".help" for usage hints.
>
> --1) Before the vacuum. Database page count = 3077, and the freelist page
> count is 0:
> sqlite> .dbinfo
> database page size:  1024
> write format:1
> read format: 1
> reserved bytes:  0
> file change counter: 52
> database page count: 3077
> freelist page count: 0
> schema cookie:   19
> schema format:   4
> default cache size:  0
> autovacuum top root: 0
> incremental vacuum:  0
> text encoding:   1 (utf8)
> user version:0
> application id:  0
> software version:3008010
> number of tables:2
> number of indexes:   1
> number of triggers:  0
> number of views: 0
> schema size: 309
>
> --2) Running a vacuum (without pragma integrity_check) results in the same
> size of database: 3077 page count and 0 freelist page count:
> sqlite> vacuum;
> sqlite> .dbinfo
> database page size:  1024
> write format:1
> read format: 1
> reserved bytes:  0
> file change counter: 53
> database page count: 3077
> freelist page count: 0
> schema cookie:   20
> schema format:   4
> default cache size:  0
> autovacuum top root: 0
> incremental vacuum:  0
> text encoding:   1 (utf8)
> user version:0
> application id:  0
> software version:3018000
> number of tables:2
> number of indexes:   1
> number of triggers:  0
> number of views: 0
> schema size: 309
>
> --3) Now running pragma integrity_check which returns "ok", and then
> vacuuming. This increases the database page count to 3236:
> sqlite> pragma integrity_check;
> ok
> sqlite> vacuum;
> sqlite> .dbinfo
> database page size:  1024
> write format:1
> read format: 1
> reserved bytes:  0
> file change counter: 54
> database page count: 3236
> freelist page count: 0
> schema cookie:   21
> schema format:   4
> default cache size:  0
> autovacuum top root: 0
> incremental vacuum:  0
> text encoding:   1 (utf8)
> user version:0
> application id:  0
> software version:3018000
> number of tables:2
> number of indexes:   1
> number of triggers:  0
> number of views: 0
> schema size: 309
> sqlite>
>
> Here is the full schema:
> CREATE TABLE bids_list (report_id INTEGER, price_id INTEGER, premium_id
> INTEGER, period_inactive INTEGER DEFAULT (0) CHECK (period_inactive IN (0,
> 1)));
> CREATE TABLE bids_dates (report_id integer primary key, date text, current
> integer check (current in (0, 1)));
> CREATE INDEX idx_price_id ON bids_list (price_id);
> /* No STAT tables available */
>
> Is the pragma fixing something in the index perhaps?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] Vacuum results in larger database after running pragma integrity_check

[Ben Newberg]

I've noticed with 3.18.0 that it's possible to make a database increase in
size after running pragma integrity_check (which returns "ok") and then
running vacuum.

Alternatively, vacuuming without running pragma integrity_check first keeps
the database the same size as before.

The page size on the database in question is 1024. The database starts with
no pages in the freelist. I can't reproduce this with 3.17.0.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.

--1) Before the vacuum. Database page count = 3077, and the freelist page
count is 0:
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 52
database page count: 3077
freelist page count: 0
schema cookie:   19
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3008010
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309

--2) Running a vacuum (without pragma integrity_check) results in the same
size of database: 3077 page count and 0 freelist page count:
sqlite> vacuum;
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 53
database page count: 3077
freelist page count: 0
schema cookie:   20
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3018000
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309

--3) Now running pragma integrity_check which returns "ok", and then
vacuuming. This increases the database page count to 3236:
sqlite> pragma integrity_check;
ok
sqlite> vacuum;
sqlite> .dbinfo
database page size:  1024
write format:1
read format: 1
reserved bytes:  0
file change counter: 54
database page count: 3236
freelist page count: 0
schema cookie:   21
schema format:   4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:   1 (utf8)
user version:0
application id:  0
software version:3018000
number of tables:2
number of indexes:   1
number of triggers:  0
number of views: 0
schema size: 309
sqlite>

Here is the full schema:
CREATE TABLE bids_list (report_id INTEGER, price_id INTEGER, premium_id
INTEGER, period_inactive INTEGER DEFAULT (0) CHECK (period_inactive IN (0,
1)));
CREATE TABLE bids_dates (report_id integer primary key, date text, current
integer check (current in (0, 1)));
CREATE INDEX idx_price_id ON bids_list (price_id);
/* No STAT tables available */

Is the pragma fixing something in the index perhaps?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

[Peter Aronson]

If you're creating GeoPackages with the F.3 RTREE Spatial Indexes 
extension, you do not "wrap" a bounding box. You need to define 5 
functions from SQL/MM -- ST_MinX, ST_MaxX, ST_MinY, ST_MaxY and 
ST_IsEmpty -- that take a geometry blob as input and return (for the 
first four) a floating point number or an integer value of 0 or 1 
(ST_IsEmpty). Now it is possible you might want to create some sort of 
side cache so you don't have to parse the geometry blob four times, but 
that is an implementation issue. The exact text of the triggers is 
specified by the standard and is not optional if you are using the 
extension. And you certainly could not use standard WKB values in the 
geometry columns without completely violating the GeoPackage standard.


Peter

On 4/3/2017 9:26 AM, Stadin, Benjamin wrote:

Hi,

Is there a hook which allows to get notified as soon as it’s save to modify a 
db connection after (or as alternative to) sqlite3_update_hook was triggered?

The background to this question is that I’m trying to prepare a proposal for 
Geopackage. And one of the questions is if it’s possible to avoid Geopackage 
using a wrapper object [1] which is used in combination with user defined 
functions to update the rtree index after a change to the geometry table 
occurred. This object is currently necessary because it wraps another object 
(an envelope / bbox) to be used by the user defined function when the SQL 
update trigger is called.

So my idea is to register an update hook and do the update to the rtree index 
directly after the step(). So this could execute an arbitrary statement, or 
more concrete a statement which would read an envelope column from the geometry 
table where the update occurred and update the rtree table accordingly. This 
would allow those columns to be plain and widely adopted WKB fields instead of 
this Geopackage binary wrapper object.

Regards
Ben

[1] http://www.geopackage.org/spec/#gpb_spec


___
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] All versions compatible "very big" estimatedCost (Virtual tables)

[Max Vlasov]

On Tue, Mar 28, 2017 at 11:26 AM, Max Vlasov  wrote:

>
>
> So, what is the maximum reasonable value of estimatedCost that will not
> turn sqlite into possible overflow errors while telling at the same time
> that I consider some variant very, very expensive? Or maybe changing cheap
> from 1 to 0 will do the trick?
>
>
Hi again, replying to myself since I  noticed a more straightforward case
explaining estimatedCost peculiarities

Sqlite 3.17.0

My comma-list virtual table implementation (vtcommalist) reported the table
structure as
  CREATE TABLE [xxx] ([CommaList] TEXT HIDDEN, [Value] TEXT)
Basically it is only functional when CommaList is provided so it can
produce Value in this case based on coming CommaList. Hidden column here to
allow table-valued functions syntax.

Database:
The virtual table
  create virtual table [cmlist] Using VtCommaList
a simple test table with lists data
  create table  [lists] ([list] TEXT)
populated with 1 rows
  insert into lists (list) values ('1, 2, 3, 4')

The query in question:
  Select distinct trim(value) From lists, cmlist(list)

Sqlite asks for index evaluation twice:
1. Suggesting constraint for CommaList field only, my code reported
"cheap"  (value 1)
2. Suggesting no constraint at all, my code reported "expensive" (value
10)

But Sqlite still went for full-scan in this case (second choice) so I had
to report error leading to "SQL Logic error"
Only when I increased "expensive" to 10*10 = 100, Sqlite
accepted my cheap/expensive ratio as worth considering with following
filter with commalist field constraint.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Charles Leifer]

I hate to be critical of Dr Hipp, but this commit stinks. Even if using the
sqlite3 shell isn't the blessed way of producing a backup, I'm sure a lot
of folks prefer it to the online backup API. It's this simple:

"echo .dump | sqlite3 my_db.db | gzip > my_db.sql.gz"

That's beautiful.

And now it doesn't work.

What a horrible shock it was to discover that my backups were all of a
sudden failing. I had no idea why until I checked the mailing list, which I
luckily happen to be subscribed to, otherwise I'd still be puzzling over
what happened.

Please consider reverting this, or hiding this behavior behind a flag.

On Tue, Apr 4, 2017 at 1:55 AM, Clemens Ladisch  wrote:

> James K. Lowden wrote:
> >>> Why not use vis(3) instead?
> >>
> >> Because vis() is a nonstandard function that is not available
> >> everywhere,
> >
> > "everywhere" is a high standard, but vis is freely available and
> > included or packaged with almost anything not Windows.
>
> It is not included in the distribution that I happen to use.
>
> >> and in any case it does not support SQL.
> >
> > Entirely irrelevant, as you surely know.
>
> What?!?  The .dump output _is_ SQL, and is intended to be read again
> by SQLite, so whatever escaping mechanism it uses must be supported by
> SQLite itself.
>
> > To me, the most objectionable aspect of using char() is that the SQL is
> > munged.  I see no reason to modify the user's text and introduce
> > further SQL interpretation.
>
> The INSERT statements _already_ are interpreted; that's the whole point
> of generating them in the first place.
>
>
> Regards,
> Clemens
> ___
> 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] column alignment of views and tables;

[Paul Sanderson]

oops

you need to select the string length from the column width

select substring('', 1,  16 - length(printf("%2.f",
price))) || printf("%2.f", price) from prices


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 4 April 2017 at 15:07, Paul Sanderson 
wrote:

> Oops would need to subtract the string length from the column width you
> want - but hopefully you get the idea :)
>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786 <+44%201326%20572786>
> http://sandersonforensics.com/forum/content.php?195-SQLite-
> Forensic-Toolkit -Forensic Toolkit for SQLite
> email from a work address for a fully functional demo licence
>
> On 4 April 2017 at 15:05, Paul Sanderson 
> wrote:
>
>> Just shooting out so no time to test. But could you try something like
>>
>> select substring('', 1,  length(printf("%2.f", price)))
>> || printf("%2.f", price) from prices
>>
>>
>>
>>
>> Paul
>> www.sandersonforensics.com
>> skype: r3scue193
>> twitter: @sandersonforens
>> Tel +44 (0)1326 572786 <+44%201326%20572786>
>> http://sandersonforensics.com/forum/content.php?195-SQLite-F
>> orensic-Toolkit -Forensic Toolkit for SQLite
>> email from a work address for a fully functional demo licence
>>
>> On 4 April 2017 at 14:47, Hans M. van der Meer 
>> wrote:
>>
>>> Simon, thanks.
>>> Now at last, I know how to continue.
>>>
>>> 2017-04-04 15:03 GMT+02:00 Simon Slavin :
>>>
>>> >
>>> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer 
>>> > wrote:
>>> >
>>> > > I am building a simple bookkeeping.
>>> > > With PHP and SQLite i now have tables and views with columns for
>>> values
>>> > and
>>> > > prices: not nicely aligned because decimal values are aligned
>>> different
>>> > > from values that are interpreted as integers.
>>> >
>>> > Numeric values stored in a SQLite database should be stored as numbers.
>>> > This allows you to do calculations on them.  They should not have
>>> alignment
>>> > because they should not be text.
>>> >
>>> > > I like to create reports in which the column of prices and
>>> > > *values are aligned to the right and all figures with two decimals
>>> behind
>>> > > the decimal point.*
>>> > > I can not find a solution, so the question is how to achieve this
>>> >
>>> > SQLite is a database system.  It is used for storing and retrieving
>>> > information, not formatting it for people.  When presenting your
>>> numbers to
>>> > people, do your formatting in PHP using sprintf() or vprintf().
>>> >
>>> > http://php.net/manual/en/function.sprintf.php
>>> > http://php.net/manual/en/function.vprintf.php
>>> >
>>> > See example #5 for sprintf() for "padding" which a word relating to
>>> > alignment.
>>> >
>>> > Simon.
>>> > ___
>>> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column alignment of views and tables;

[Paul Sanderson]

Just shooting out so no time to test. But could you try something like

select substring('', 1,  length(printf("%2.f", price))) ||
printf("%2.f", price) from prices




Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 4 April 2017 at 14:47, Hans M. van der Meer  wrote:

> Simon, thanks.
> Now at last, I know how to continue.
>
> 2017-04-04 15:03 GMT+02:00 Simon Slavin :
>
> >
> > On 4 Apr 2017, at 11:25am, Hans M. van der Meer 
> > wrote:
> >
> > > I am building a simple bookkeeping.
> > > With PHP and SQLite i now have tables and views with columns for values
> > and
> > > prices: not nicely aligned because decimal values are aligned different
> > > from values that are interpreted as integers.
> >
> > Numeric values stored in a SQLite database should be stored as numbers.
> > This allows you to do calculations on them.  They should not have
> alignment
> > because they should not be text.
> >
> > > I like to create reports in which the column of prices and
> > > *values are aligned to the right and all figures with two decimals
> behind
> > > the decimal point.*
> > > I can not find a solution, so the question is how to achieve this
> >
> > SQLite is a database system.  It is used for storing and retrieving
> > information, not formatting it for people.  When presenting your numbers
> to
> > people, do your formatting in PHP using sprintf() or vprintf().
> >
> > http://php.net/manual/en/function.sprintf.php
> > http://php.net/manual/en/function.vprintf.php
> >
> > See example #5 for sprintf() for "padding" which a word relating to
> > alignment.
> >
> > Simon.
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column alignment of views and tables;

[Dominique Devienne]

On Tue, Apr 4, 2017 at 3:47 PM, Hans M. van der Meer 
wrote:

> Simon, thanks.
> Now at last, I know how to continue.
>

But SQLite itself ships with a portable version of printf too.

So you can convert your integer or real typed columns into text typed ones
for display, directly in SQL, as an alternative to doing it in PHP. --DD

https://sqlite.org/lang_corefunc.html#printf


> 2017-04-04 15:03 GMT+02:00 Simon Slavin :
> > SQLite is a database system.  It is used for storing and retrieving
> > information, not formatting it for people.  When presenting your numbers
> to
> > people, do your formatting in PHP using sprintf() or vprintf().
> >
> > http://php.net/manual/en/function.sprintf.php
> > http://php.net/manual/en/function.vprintf.php
> >
> > See example #5 for sprintf() for "padding" which a word relating
> to alignment.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] column alignment of views and tables;

[Hans M. van der Meer]

Simon, thanks.
Now at last, I know how to continue.

2017-04-04 15:03 GMT+02:00 Simon Slavin :

>
> On 4 Apr 2017, at 11:25am, Hans M. van der Meer 
> wrote:
>
> > I am building a simple bookkeeping.
> > With PHP and SQLite i now have tables and views with columns for values
> and
> > prices: not nicely aligned because decimal values are aligned different
> > from values that are interpreted as integers.
>
> Numeric values stored in a SQLite database should be stored as numbers.
> This allows you to do calculations on them.  They should not have alignment
> because they should not be text.
>
> > I like to create reports in which the column of prices and
> > *values are aligned to the right and all figures with two decimals behind
> > the decimal point.*
> > I can not find a solution, so the question is how to achieve this
>
> SQLite is a database system.  It is used for storing and retrieving
> information, not formatting it for people.  When presenting your numbers to
> people, do your formatting in PHP using sprintf() or vprintf().
>
> http://php.net/manual/en/function.sprintf.php
> http://php.net/manual/en/function.vprintf.php
>
> See example #5 for sprintf() for "padding" which a word relating to
> alignment.
>
> Simon.
> ___
> 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] Incompatibility into configure.ac

[Gary R. Schmidt]

On 04/04/2017 23:20, Richard Hipp wrote:

On 4/4/17, Pavel Volkov  wrote:

The "+ =" operator works as you would expect in bash only.
And it causes an error in the Bourne shell, for example.


You have piqued my curiosity.  Who is still using Bourne shell instead
of the Bourne-again shell (bash)?  Bash has been with us now for like
three decades, right?


Anyone who is supporting Solaris/AIX/HP-UX or older systems.

Is SQLite going the way of "Sure it's portable, it works on Fedora Core 
*and* Ubuntu"???


Cheers,
GaryB-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incompatibility into configure.ac

[Richard Hipp]

On 4/4/17, Pavel Volkov  wrote:
> The "+ =" operator works as you would expect in bash only.
> And it causes an error in the Bourne shell, for example.

You have piqued my curiosity.  Who is still using Bourne shell instead
of the Bourne-again shell (bash)?  Bash has been with us now for like
three decades, right?

-- 
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

Re: [sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

[Richard Hipp]

On 4/3/17, Stadin, Benjamin  wrote:
> Hi,
>
> Is there a hook which allows to get notified as soon as it’s save to modify
> a db connection after (or as alternative to) sqlite3_update_hook was
> triggered?

There is no such callback built into SQLite.  But you can add one by
putting a wrapper around sqlite3_step().

-- 
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

Re: [sqlite] column alignment of views and tables;

[Simon Slavin]

On 4 Apr 2017, at 11:25am, Hans M. van der Meer  wrote:

> I am building a simple bookkeeping.
> With PHP and SQLite i now have tables and views with columns for values and
> prices: not nicely aligned because decimal values are aligned different
> from values that are interpreted as integers.

Numeric values stored in a SQLite database should be stored as numbers.  This 
allows you to do calculations on them.  They should not have alignment because 
they should not be text.

> I like to create reports in which the column of prices and
> *values are aligned to the right and all figures with two decimals behind
> the decimal point.*
> I can not find a solution, so the question is how to achieve this

SQLite is a database system.  It is used for storing and retrieving 
information, not formatting it for people.  When presenting your numbers to 
people, do your formatting in PHP using sprintf() or vprintf().

http://php.net/manual/en/function.sprintf.php
http://php.net/manual/en/function.vprintf.php

See example #5 for sprintf() for "padding" which a word relating to alignment.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] recursive clause

[Cem Dayanik (Ibtech-Software Infrastructure)]

Hello there,

I am trying to find a memory leak with MemoScope.
It is using sqllite.
Memoscope has some issue to find the problem so I am actually querying the data.

CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress INTEGER)
CREATE TABLE Instances (TypeId INTEGER, Address INTEGER)
CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count INT, 
TotalSize INTEGER)

What I need is, start with an address and add all reference addresses to the 
list -with increasing level- unless it is already there.

Any insights?

Not working with:
and refbyaddress not in (select address from allreferences)

select * from (
WITH RECURSIVE
  allreferences(address, level) AS (
  values(435582892, 0)
  union
SELECT refbyaddress, allreferences.level+1 FROM instancereferences, 
allreferences
   WHERE instancereferences.instanceaddress=allreferences.address and 
allreferences.level < 1 and refbyaddress not in (select address from 
allreferences)
  )
SELECT* FROM allreferences ) r, instances ins , types t where r.n = ins.address 
and ins.typeid=t.id




Bu e-posta'n?n i?erdi?i bilgiler (ekleri dahil olmak ?zere) gizlidir. Onay?m?z 
olmaks?z?n ???nc? ki?ilere a?iklanamaz. Bu mesaj?n g?nderilmek istendi?i ki?i 
de?ilseniz, l?tfen mesaj? sisteminizden derhal siliniz. IBTech A.?. bu mesaj?n 
i?erdi?i bilgilerin do?rulu?u veya eksiksiz oldu?u konusunda bir garanti 
vermemektedir. Bu nedenle bilgilerin ne ?ekilde olursa olsun i?eri?inden, 
iletilmesinden, al?nmas?ndan, saklanmas?ndan sorumlu de?ildir. Bu mesaj?n 
i?eri?i yazar?na ait olup, IBTech A.?.'nin g?r??lerini i?ermeyebilir.

The information contained in this e-mail (including any attachments)is 
confidential. It must not be disclosed to any person without our authority. If 
you are not the intended recipient, please delete it from your system 
immediately. IBTech A.S. makes no warranty as to the accuracy or completeness 
of any information contained in this message and hereby excludes any liability 
of any kind for the information contained therein or for the information 
transmission, reception, storage or use of such in any way whatsoever. Any 
opinions expressed in this message are those of the author and may not 
necessarily reflect the opinions of IBTech A.S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_exec() on prepared/bound statement

[Labar, Ken]

Hello all,

* Short story:
To support better security through binding, we would like to request a new 
C/C++ API function similar to sqlite3_exec(), however it will take a prepared 
statement instead of an sql string.

* Long story:
We finally allowed a user string input directly into our database (embbeded C, 
ARM7, UCOS-II port).
We immediately found SQL injection errors in testing.
To solve sql injection we used sqlite3_preapre_v2() with sqlite3_bind_text().
When looking to refactor the code, it was easier to create the following 
function directly in sqlite3.c.
It appears this function could be called from sqlite3_exec()  to save code 
space and avoid duplication.

We feel this would make sense in the mainline code, so we are offering it here, 
hopefully it might be useful to others:

#define ADD_SQLITE3_EXEC_PREPARED 1
#if ADD_SQLITE3_EXEC_PREPARED
/*
** Execute a prepared sqlite3 statement. Borrowed from SQLITE_API int 
sqlite3_exec().
** Return one of the SQLITE_ success/failure
** codes.  Also write an error message into memory obtained from
** malloc() and make *pzErrMsg point to that message.
**
** If the SQL is a query, then for each row in the query result
** the xCallback() function is called.  pArg becomes the first
** argument to xCallback().  If xCallback=NULL then no callback
** is invoked, even for queries.
*/
// @note: added for the ViperFish project by klabar 3/24/2017
SQLITE_API int sqlite3_exec_prepared(
  sqlite3 *db,/* The database on which the SQL executes */
  sqlite3_stmt *pStmt,/* The prepared SQL to be executed */
  sqlite3_callback xCallback, /* Invoke this callback routine */
  void *pArg, /* First argument to xCallback() */
  char **pzErrMsg /* Write error messages here */
){
  int rc = SQLITE_OK; /* Return code */
  const char *zLeftover;  /* Tail of unprocessed SQL */
  char **azCols = 0;  /* Names of result columns */
  int callbackIsInit; /* True if callback data is initialized */

  if( !sqlite3SafetyCheckOk(db) ) return SQLITE_MISUSE_BKPT;

  sqlite3_mutex_enter(db->mutex);
  sqlite3Error(db, SQLITE_OK, 0);

  while( rc==SQLITE_OK && pStmt ){
int nCol;
char **azVals = 0;

callbackIsInit = 0;
nCol = sqlite3_column_count(pStmt);

while( 1 ){
  int i;
  rc = sqlite3_step(pStmt);

  /* Invoke the callback function if required */
  if( xCallback && (SQLITE_ROW==rc ||
  (SQLITE_DONE==rc && !callbackIsInit
   && db->flags_NullCallback)) ){
if( !callbackIsInit ){
  azCols = sqlite3DbMallocZero(db, 2*nCol*sizeof(const char*) + 1);
  if( azCols==0 ){
goto exec_prep_out;
  }
  for(i=0; imallocFailed = 1;
  goto exec_prep_out;
}
  }
}
if( xCallback(pArg, nCol, azVals, azCols) ){
  rc = SQLITE_ABORT;
  sqlite3VdbeFinalize((Vdbe *)pStmt);
  pStmt = 0;
  sqlite3Error(db, SQLITE_ABORT, 0);
  goto exec_prep_out;
}
  }

  if( rc!=SQLITE_ROW ){
rc = sqlite3VdbeFinalize((Vdbe *)pStmt);
pStmt = 0;
break;
  }
}

sqlite3DbFree(db, azCols);
azCols = 0;
  }

exec_prep_out:
  if( pStmt ) sqlite3VdbeFinalize((Vdbe *)pStmt);
  sqlite3DbFree(db, azCols);

  rc = sqlite3ApiExit(db, rc);
  if( rc!=SQLITE_OK && ALWAYS(rc==sqlite3_errcode(db)) && pzErrMsg ){
int nErrMsg = 1 + sqlite3Strlen30(sqlite3_errmsg(db));
*pzErrMsg = sqlite3Malloc(nErrMsg);
if( *pzErrMsg ){
  memcpy(*pzErrMsg, sqlite3_errmsg(db), nErrMsg);
}else{
  rc = SQLITE_NOMEM;
  sqlite3Error(db, SQLITE_NOMEM, 0);
}
  }else if( pzErrMsg ){
*pzErrMsg = 0;
  }

  assert( (rc>errMask)==rc );
  sqlite3_mutex_leave(db->mutex);
  return rc;
}
#endif //ADD_SQLITEUTIL_EXEC_PREPARED


Here is an example of how we used it:
/**
   \brief
   \return rc // the SQLite return code
**/
int HLFF_DB_logfile_get_byLognameSource(char *logname, int source, void* 
relay_args)
{
int rc;
sqlite3_stmt *pStmt;


if (!logname) { return(-1); }
if (!logname[0]) { return(-1); }

//build the query
Str_Copy(
sql,
"SELECT id, filename, logname, size, "
"source, start_time, stop_time,"
" modified_timestamp FROM `logfile` WHERE logname=?1 

[sqlite] Get notified as soon as it's save to modify a db after sqlite3_update_hook() was triggered

[Stadin, Benjamin]

Hi,

Is there a hook which allows to get notified as soon as it’s save to modify a 
db connection after (or as alternative to) sqlite3_update_hook was triggered?

The background to this question is that I’m trying to prepare a proposal for 
Geopackage. And one of the questions is if it’s possible to avoid Geopackage 
using a wrapper object [1] which is used in combination with user defined 
functions to update the rtree index after a change to the geometry table 
occurred. This object is currently necessary because it wraps another object 
(an envelope / bbox) to be used by the user defined function when the SQL 
update trigger is called.

So my idea is to register an update hook and do the update to the rtree index 
directly after the step(). So this could execute an arbitrary statement, or 
more concrete a statement which would read an envelope column from the geometry 
table where the update occurred and update the rtree table accordingly. This 
would allow those columns to be plain and widely adopted WKB fields instead of 
this Geopackage binary wrapper object.

Regards
Ben

[1] http://www.geopackage.org/spec/#gpb_spec


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] column alignment of views and tables;

[Hans M. van der Meer]

Hi users,

I am building a simple bookkeeping.
With PHP and SQLite i now have tables and views with columns for values and
prices: not nicely aligned because decimal values are aligned different
from values that are interpreted as integers.

I like to create reports in which the column of prices and
*values are aligned to the right and all figures with two decimals behind
the decimal point.*
I can not find a solution, so the question is how to achieve this

Hans
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Incompatibility into configure.ac

[Pavel Volkov]

Hello.
Please, replace the "+ =" operators in the configure.ac file with a
more compatible way of combining strings.
The "+ =" operator works as you would expect in bash only.
And it causes an error in the Bourne shell, for example.
Please, see this patch:

--- configure.ac.orig   2017-04-03 12:16:00 UTC
+++ configure.ac
@@ -596,7 +596,7 @@ AC_ARG_ENABLE(memsys5,
   [enable_memsys5=yes],[enable_memsys5=no])
 AC_MSG_CHECKING([whether to support MEMSYS5])
 if test "${enable_memsys5}" = "yes"; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_MEMSYS5"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_MEMSYS5"
   AC_MSG_RESULT([yes])
 else
   AC_MSG_RESULT([no])
@@ -606,7 +606,7 @@ AC_ARG_ENABLE(memsys3,
   [enable_memsys3=yes],[enable_memsys3=no])
 AC_MSG_CHECKING([whether to support MEMSYS3])
 if test "${enable_memsys3}" = "yes" -a "${enable_memsys5}" = "no"; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_MEMSYS3"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_MEMSYS3"
   AC_MSG_RESULT([yes])
 else
   AC_MSG_RESULT([no])
@@ -618,20 +618,20 @@ AC_ARG_ENABLE(fts3, AC_HELP_STRING([--en
   [Enable the FTS3 extension]),
   [enable_fts3=yes],[enable_fts3=no])
 if test "${enable_fts3}" = "yes" ; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_FTS3"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_FTS3"
 fi
 AC_ARG_ENABLE(fts4, AC_HELP_STRING([--enable-fts4],
   [Enable the FTS4 extension]),
   [enable_fts4=yes],[enable_fts4=no])
 if test "${enable_fts4}" = "yes" ; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_FTS4"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_FTS4"
   AC_SEARCH_LIBS([log],[m])
 fi
 AC_ARG_ENABLE(fts5, AC_HELP_STRING([--enable-fts5],
   [Enable the FTS5 extension]),
   [enable_fts5=yes],[enable_fts5=no])
 if test "${enable_fts5}" = "yes" ; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_FTS5"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_FTS5"
   AC_SEARCH_LIBS([log],[m])
 fi

@@ -641,7 +641,7 @@ AC_ARG_ENABLE(json1, AC_HELP_STRING([--e
   [Enable the JSON1 extension]),
   [enable_json1=yes],[enable_json1=no])
 if test "${enable_json1}" = "yes" ; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_JSON1"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_JSON1"
 fi

 #
@@ -650,7 +650,7 @@ AC_ARG_ENABLE(rtree, AC_HELP_STRING([--e
   [Enable the RTREE extension]),
   [enable_rtree=yes],[enable_rtree=no])
 if test "${enable_rtree}" = "yes" ; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_RTREE"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_RTREE"
 fi

 #
@@ -659,8 +659,8 @@ AC_ARG_ENABLE(session, AC_HELP_STRING([-
   [Enable the SESSION extension]),
   [enable_session=yes],[enable_session=no])
 if test "${enable_session}" = "yes" ; then
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_SESSION"
-  OPT_FEATURE_FLAGS+=" -DSQLITE_ENABLE_PREUPDATE_HOOK"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_SESSION"
+  OPT_FEATURE_FLAGS="${OPT_FEATURE_FLAGS} -DSQLITE_ENABLE_PREUPDATE_HOOK"
 fi

 #

Thanks.
With regards, Pavel.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Clemens Ladisch]

James K. Lowden wrote:
>>> Why not use vis(3) instead?
>>
>> Because vis() is a nonstandard function that is not available
>> everywhere,
>
> "everywhere" is a high standard, but vis is freely available and
> included or packaged with almost anything not Windows.

It is not included in the distribution that I happen to use.

>> and in any case it does not support SQL.
>
> Entirely irrelevant, as you surely know.

What?!?  The .dump output _is_ SQL, and is intended to be read again
by SQLite, so whatever escaping mechanism it uses must be supported by
SQLite itself.

> To me, the most objectionable aspect of using char() is that the SQL is
> munged.  I see no reason to modify the user's text and introduce
> further SQL interpretation.

The INSERT statements _already_ are interpreted; that's the whole point
of generating them in the first place.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

[Clemens Ladisch]

Charles Leifer wrote:
> This bit me... I fat-fingered a command and deleted my database. I had a
> backup dump taken earlier in the day. Go to restore it and all of a sudden
> this error starts cropping up.
>
> What to do?

From the link the in the first post:

  sed -e "s/'||char(10)||'/\\n/g" < with_char.sql > with_newlines.sql

(Same for char(13) → \r, if you have MS-DOS or Mac line endings.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users