Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Stephen Chrzanowski]

I've read a bunch of the posts here, and quite honestly, I'm kind of
looking forward to having Fossil send me notes that messages have been
added, then, if I want to reply, I just log into the site and do the
reply.  I'm not 100% sure about the anon sending to Fossil, if that's a
thing I thought I read somewhere, but at least my email address doesn't end
up in a public forum and find myself signed up to a dating site.  .. well..
at least until the mailing list is shut down.

On Thu, Mar 12, 2020 at 4:18 PM Richard Hipp  wrote:

> I have set up an on-line forum as a replacement for this mailing list:
>
> https://sqlite.org/forum
> https://www.sqlite.org/forum/forumpost/a6a27d79ac
>
> Please consider subscribing to the new Forum.  The intent is that the
> forum will eventually replace this mailing list.
>
> The Forum is powered by Fossil.  It has been in active use in the
> Fossil community for a couple of years, and has worked well.  See the
> second link above for more information.
>
> --
> 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] DRH interview on why/how SQLite succeeded

[Stephen Chrzanowski]

I just finished listening to this.  Really cool.

Thanks for ALL of your hard work SQLite team.  I appreciate it sincerely.


On Wed, Feb 19, 2020 at 12:39 PM Simon Slavin  wrote:

> 
>
> Podcast / transcription of DRH interview:
>
> " We talked to Richard about the history of SQLite, where it came from,
> why it succeeded as a database, how its development is sustainably funded
> and also how it’s the most widely deployed database engine in the world. "
>
> Not a formal treatment, but you can piece together what's said and get a
> lot of information on how SQLite developed into what it is today, who is in
> the development team, and how it is funded through the SQLite Consortium.
> Plus random anecdotes about computing over the last 40 years.
> ___
> 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] New word to replace "serverless"

[Stephen Chrzanowski]

I'd stick with "serverless".  The marketing teams that make "serverless"
mean that websites don't run with "servers" are I-D-TEN-Ts.  It's a fad
phrase that'll go away eventually.  I understand marketing, and its
purpose, but, in this case, they're pushing it.


On Mon, Jan 27, 2020 at 9:31 PM sub sk79  wrote:

> Also maybe Slipstreamed?
>
> -Neal
>
> On Monday, January 27, 2020, sub sk79  wrote:
>
> > How about Seamless, Integrated or Baked-in?
> >
> > -Neal
> >
> > On Monday, January 27, 2020, Warren Young  wrote:
> >
> >> On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
> >> >
> >> > "serverless" has become a popular buzz-word that
> >> > means "managed by my hosting provider rather than by me.”
> >>
> >> “Serverless” it a screwy buzzword anyway, because of course there’s
> still
> >> a server under its new meaning.
> >>
> >> My vote?  Keep using the term.  We were here first.
> >>
> >> This is an ancient problem.  It is why is any serious dictionary the
> >> count of definitions considerably exceeds the count of headwords.  These
> >> new kids?  “serverless, sense 2.”
> >> ___
> >> 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] SQLite version 3.30.0 in about two weeks.

[Stephen Chrzanowski]

"Still says" due to 3_30_0.html is in a draft, I would assume.

On Thu, Sep 26, 2019 at 3:31 PM Olivier Mascia  wrote:

> > Le 26 sept. 2019 à 20:26, Richard Hipp  a écrit :
> >
> > Our plan is to release SQLite version 3.30.0 in about two weeks - on
> > or about 2019-10-10.  Please review the change log
> >
> >https://www.sqlite.org/draft/releaselog/3_30_0.html
> >
> > And perhaps download, build, and test the latest snapshot.  Please let
> > us know if you encounter any problems or concerns.
>
> Just reading for now.  https://www.sqlite.org/draft/releaselog/3_30_0.html
> says:
>
> • Enhance the RBU extension to support indexes on expressions.
>
> While https://www.sqlite.org/draft/rbu.html still says:
>
> • The target database may not contain indexes on expressions.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
> ___
> 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] General Amalgamation vs Your Own

[Stephen Chrzanowski]

Thanks for all this info.

No, I have not made a single change to the amalgamation that has been
provided by sqlite.org.  The most I've done is read, line by line, through
the first meg or so of source code, as well as setup an environment where I
specifically can build sqlite3.dll for fun.  The thought was entirely for
theoretical practices, and only came up when I noticed a few threads
suggesting that they're making changes to the code (Be it to test theory or
to enact live in their libraries), then my thoughts wandered to their code
vs future SQLite code enhancements or bug fixes, and how getting the two
different sets of code to work with each other going forward.

On Sat, Sep 21, 2019 at 10:41 PM Warren Young  wrote:

> On Sep 21, 2019, at 11:29 AM, Stephen Chrzanowski 
> wrote:
> >
> > How does one have their own code base for SQLite, with their own customer
> > logic or functionality or whatever, then, have updates provided by the
> > SQLite team implemented in when updates and such are provided?
>
> What kind of code are you talking about?
>
> With custom functions and loadable extensions, you don’t modify the SQLite
> core at all:
>
> https://sqlite.org/c3ref/create_function.html
> https://sqlite.org/loadext.html
>
> The latter allows a lot of flexibility, changing collation sequences,
> adding VFSes, etc.
>
> If you’re modifying the SQLite core, then why?  Maybe it isn’t actually
> required that you do so, and we could show you a better way.  Or, maybe
> SQLite extensions could be, ah, *extended* to give you the API you’d need
> to do this from the outside, without modifying the core.
>
> > I'm assuming Fossil would handle this kind of process with merging and
> such?
>
> I would not recommend using private branches, even though drh offered it,
> because it’s not a commonly-used feature of Fossil, so it’s not as
> well-tested and well-supported as Fossil’s mainstream usage code paths.  If
> you ever run into trouble using private branches in Fossil, you’re more
> likely to get “crickets” as a response than with similar trouble within the
> well-used features of Fossil.
>
> Even when private branches are the right answer, they exist for a
> different use case than what you’ve got going here: you have check-in
> rights on the parent repo you cloned from, but you don’t want all local
> check-ins to sync up to the parent repo.  I don’t see any user with
> check-ins on the SQLite code repo that looks like your name or your Gmail
> user name, so I’m assuming you *don’t* have check-in rights on the SQLite
> code repo.  Therefore, private branches don’t really apply to your case.
>
> I’d suggest one of two other alternatives:
>
> 1. As with private branches, keep your SQLite changes in a clone of the
> main SQLite repo, but set the “autosync” setting to “pullonly” and check
> your local changes in on a normal branch.  Since you have no check-in
> ability on the main SQLite repo and autosync is set to not push local
> changes, you’re set.
>
> The main thing you have to watch out for is that if you ever *do* get
> check-in rights on the main SQLite repo, that you never use that same
> forked repo with that account, since the first sync will push all of your
> historical changes up to SQLite.
>
> All branches in Fossil are effectively private when you don’t have
> check-in capability.  All the autosync setting does is prevent Fossil from
> trying things you know will always fail.
>
>
> 2. Keep your local mods in a separate repository — possibly managed by a
> non-Fossil [D]VCS — and periodically merge changes in from SQLite.  This
> arrangement is usually called a “vendor branch:”
>
> http://svnbook.red-bean.com/en/1.7/svn.advanced.vendorbr.html
>
> That document gives it in terms of Subversion, but it works in any decent
> [D]VCS.
>
> When using two different [D]VCSes, you do the “merge” in part with copy
> commands:
>
> $ cd ~/path/to/sqlite/checkout
> $ fossil update
> $ make -j11 sqlite3.c   # optional
> $ cd ~/path/to/private/repo
> $ git checkout sqlite-vendor-branch
> $ cp ~/path/to/sqlite/checkout/src/* 3rd-party/sqlite
> $ git commit -a
> $ git checkout master, merge, etc.
>
> You could then script that process locally, so that you give only a single
> command, like “tools/update-sqlite”.
>
> > What is the workflow on making sure the amalgamation source doesn't
> modify
> > local changes?
>
> Vendor branches.  You want to do that even with my option #1 above or with
> drh’s offering of private branches.  In the latter case, SQLite “trunk” is
> the vendor branch, and your branch off of that is your project’s effective
> “trunk”.
>
> Keep in mind tha

[sqlite] General Amalgamation vs Your Own

[Stephen Chrzanowski]

With a post that someone made here, and the thought that I've been running
in my head over the past while...

How does one have their own code base for SQLite, with their own customer
logic or functionality or whatever, then, have updates provided by the
SQLite team implemented in when updates and such are provided?

I'm assuming Fossil would handle this kind of process with merging and such?

What is the workflow on making sure the amalgamation source doesn't modify
local changes?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Odd behaviour with JulianDay

[Stephen Chrzanowski]

I was just writing the reply to my own emails to say I found a work around
when your email showed up.

I kind of walked around this issue by re-looking at how I was inserting the
data.  I was using the DateTime type in Delphi which counts days up since
Jan 1, 1970.  So what I did is just change my insert statement to reformat
the datetime I want to insert into a string, then have SQLite handle the
conversions as needed.



On Sat, Sep 7, 2019 at 4:43 PM Keith Medcalf  wrote:

>
> You can convert a float unixepoch the same way as anything else, you just
> need to tell strftime (or its overloaded aliases julianday and datetime)
> that it is a 'unixepoch' since by default it thinks floating point means
> julianday and integer means unixepoch.
>
> julianday(value, 'unixepoch') will give you the floating point julianday
> corresponding with the "value" relative to the unixepoch.  Similarly
> datetime(value, 'unixepoch') will get you the iso8601 text (though only to
> a precision of a second).
>
> Getting a floating point unixepoch using the builtin functions is messy
> since strftime('%s') only returns whole seconds (select strftime('%s') -
> strftime('%S') + stftime('%f')) will get you the unixepoch offset in
> floating point corresponding to 'now'.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users  On
> Behalf
> >Of Stephen Chrzanowski
> >Sent: Saturday, 7 September, 2019 14:12
> >To: sqlite-users 
> >Subject: Re: [sqlite] Odd behaviour with JulianDay
> >
> >I think I see it.  This is the schema for the table:
> >CREATE TABLE [EventEntry](
> >  [EventID] INTEGER PRIMARY KEY AUTOINCREMENT,
> >  [IPAddress] CHAR,
> >  [Node] INTEGER DEFAULT 0,
> >  [NodeOpened] DATETIME,
> >  [NodeClosed] DATETIME);
> >
> >When I run a select * from EventEntry I'm seeing the 'float' since
> >UnixEpoch, so, 43711.819791667 as an example.  So I'm comparing oranges to
> >apples.
> >
> >Now I just need to figure out how to compare apples to apples when using
> >'now'.
> >
> >On Sat, Sep 7, 2019 at 4:01 PM Stephen Chrzanowski 
> >wrote:
> >
> >> I'm creating a new database to keep track of time difference between
> >> logins and logoffs for a particular system.
> >>
> >> I have the following query:
> >> select  NodeOpened,
> >> JulianDay(NodeOpened),
> >> JulianDay('now')
> >> from EventEntry
> >> where NodeClosed is null
> >>
> >> The results are:
> >> NodeOpenedJulianDay(NodeOpened) JulianDay('now')
> >> 2019-09-03 19:29:15.000   43711.8119791667  2458734.32840103
> >> 2019-09-03 19:52:24.000   43711.828056  2458734.32840103
> >> 2019-09-03 20:08:54.000   43711.8395138889  2458734.32840103
> >>
> >> Reading the Wiki on Julian Day (That the SQLite DateTime formats
> >provides)
> >> I understand why JulianDay is such a large number (Counting days back
> >from
> >> the BC era), but I'm not understanding why the NodeOpened is such a
> small
> >> number and 'now' is such a huge number?
> >>
> >>
> >___
> >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] Odd behaviour with JulianDay

[Stephen Chrzanowski]

I think I see it.  This is the schema for the table:
CREATE TABLE [EventEntry](
  [EventID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [IPAddress] CHAR,
  [Node] INTEGER DEFAULT 0,
  [NodeOpened] DATETIME,
  [NodeClosed] DATETIME);

When I run a select * from EventEntry I'm seeing the 'float' since
UnixEpoch, so, 43711.819791667 as an example.  So I'm comparing oranges to
apples.

Now I just need to figure out how to compare apples to apples when using
'now'.

On Sat, Sep 7, 2019 at 4:01 PM Stephen Chrzanowski 
wrote:

> I'm creating a new database to keep track of time difference between
> logins and logoffs for a particular system.
>
> I have the following query:
> select  NodeOpened,
> JulianDay(NodeOpened),
> JulianDay('now')
> from EventEntry
> where NodeClosed is null
>
> The results are:
> NodeOpenedJulianDay(NodeOpened) JulianDay('now')
> 2019-09-03 19:29:15.000   43711.8119791667  2458734.32840103
> 2019-09-03 19:52:24.000   43711.828056  2458734.32840103
> 2019-09-03 20:08:54.000   43711.8395138889  2458734.32840103
>
> Reading the Wiki on Julian Day (That the SQLite DateTime formats provides)
> I understand why JulianDay is such a large number (Counting days back from
> the BC era), but I'm not understanding why the NodeOpened is such a small
> number and 'now' is such a huge number?
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Odd behaviour with JulianDay

[Stephen Chrzanowski]

I'm creating a new database to keep track of time difference between logins
and logoffs for a particular system.

I have the following query:
select  NodeOpened,
JulianDay(NodeOpened),
JulianDay('now')
from EventEntry
where NodeClosed is null

The results are:
NodeOpenedJulianDay(NodeOpened) JulianDay('now')
2019-09-03 19:29:15.000   43711.8119791667  2458734.32840103
2019-09-03 19:52:24.000   43711.828056  2458734.32840103
2019-09-03 20:08:54.000   43711.8395138889  2458734.32840103

Reading the Wiki on Julian Day (That the SQLite DateTime formats provides)
I understand why JulianDay is such a large number (Counting days back from
the BC era), but I'm not understanding why the NodeOpened is such a small
number and 'now' is such a huge number?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] lockfile but using db table row instead of file

[Stephen Chrzanowski]

What Keith said, plus, SQLite does not do row locking, period.  It's a full
file lock when required.  SQLite has some embedded functionality to deal
with locking things like this, and your program will  need to be written to
be aware of how to deal with locking.

If you're attempting to fix race conditions, then you need to look at how
your programs are working with each other, either by communicating with
each other through FIFO files (If linux) or through DDE (Dynamic Data
Exchange) if Windows, or rely on the OS and SQLite provisions already out
there.  I've done some LIGHT work with DDE back in the Delphi 5 days, and
from what I remember, it was pretty interesting.

Work with the tools you have, and don't go reinvent the wheel.  (Unless the
purpose is to reinvent the wheel just for fun.)

On Wed, Sep 4, 2019 at 6:40 AM Peng Yu  wrote:

> The command line program `lockfile` locks based on files. I wants to do the
> same thing but based on a row in a table. For example (this is just one
> example feature, but all other features should also be preserved), if a
> given row is there, the process will hang unless some other process delete
> the given row. Multiple processes can compete for the same row. Because
> program like this should deal with concurrency, I am not clear how to make
> it bug free. Does anybody has a ready-to-use solution? Thanks.
> --
> Regards,
> Peng
> ___
> 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] Backward compatibility vs. new features (was: Re: dates, times and R)

[Stephen Chrzanowski]

Yep, I agree, to which is where I pointed out that you'd need the
additional information for that store location.  But that's all you'd need,
and only in that one location.  Your UI (Or whatever specialized report
generation) would have to do the math from the UTC time, and convert it
accordingly to whatever the stores location TZ info is, then present the
appropriate information.  So even if you are in BC, you can look at NFLD
info and get the NFLD local times.

On Wed, Aug 14, 2019 at 12:24 PM Warren Young  wrote:

> On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski 
> wrote:
> >
> > On Tue, Aug 13, 2019 at 7:30 PM J Decker  wrote:
> >
> >>> Why are you storing the timezone? You display the TZ of the user who
> is,
> >>> later, viewing the data. And that user could be anywhere.
> >>
> >> Because the actual time on the clock on the wall matters.
> >>
> > The time on the clock is irrelevant, completely and totally, due to
> > daylight saving.
>
> I agree with your reply as far as it goes, and I agree that times stored
> in the DB should be in UTC or some similar format.
>
> (By that I mean we don’t need to get into UTC vs UT1 vs whatever other
> “universal” time standard you prefer.  Just pick one and stick to it for
> all users of that DB.)
>
> However, it *can* be helpful to store the time zone or the local UTC
> offset alongside the universal timestamp in the DB so you can translate the
> timestamp to local time in calculations.  Without it, you can’t ask the DB
> questions like, “Give me all records that occurred between 9am and 10am
> local time,” or “Across all stores, what are our busiest hours?”
>
> The timestamp in UTC lets you ask different questions, such as “Give me
> the 10 most recent matching records,” which breaks if you store timestamps
> as local time, and your DB holds values from multiple time zones.
> ___
> 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] Backward compatibility vs. new features (was: Re: dates, times and R)

[Stephen Chrzanowski]

On Tue, Aug 13, 2019 at 7:30 PM J Decker  wrote:

>
> > Why are you storing the timezone? You display the TZ of the user who is,
> > later, viewing the data. And that user could be anywhere.
>
> Because the actual time on the clock on the wall matters.
> I want to know cashiers that are making transactions outside of 9am-5pm
> (for instance).  But depending on where they are that time is different.
> I also want to know the time continuously according to the linear time that
> they happened in.
>
>
The time on the clock is irrelevant, completely and totally, due to
daylight saving.  Moving the clock back and forth twice a year causes a
chunk of time to go missing or a chunk of time to be duplicated.  The clock
is a User Interface problem, not a data storage problem.  When you want to
know that locations actual time, you store that stores location with their
TZ information, and your UI presents that information to you.

Because UTC does not move forward or backwards (Occasionally we add a
second due to the rotation of the earth slowing down*, but we NEVER go
backwards), anything time related in a database NEEDS to be stored in
UTC/Zulu/GMT-0 time (Or whatever you want to call it).  This is THE base
time.  Sure, you COULD store the location,  but, if you're in British
Columbia, looking at a stores transactions in New Found Land, (Or say
California versus New York), what time do you believe?

I work for a company that literally has servers all around the world, which
means Time Zones ARE a thing for us.  We set every single machine we deploy
to UTC.  If there were any times to be displayed in "local" time, it's
handled at the application/presentation layer, not the database layer.

*https://www.quora.com/Why-is-Earths-rotation-slowing-down -- First hit on
Google.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: import MySQL dumps in CLI

[Stephen Chrzanowski]

The BIGGEST problem I had with importing data from MySQL to SQLite is the
table definitions.

If you do two dumps, one specifically for table definitions, the other for
the actual data to be imported, you could get a script to handle the table
definition file to make it conform to what SQLite can use, and the raw data
is sitting there ready to be imported.  Most of the work is going to be
against the table definitions as MySQL dumps information about what MySQL
engine needs to be used, while SQLite has no such necessity and breaks.

The work to be done would be to do a multi-pass "string replacement".
Remove the text that is superficial to SQLite that MySQL requires, like the
engine used by MySQL.  Remove anything that's MySQL language specific in
regards to table definitions, and wipe them or swap them for a generic TEXT
or NUMERIC definition.  Etc.  If you run into problems with the import,
it'd be easy enough modify the script and rerun the job.

I learned in my venture that the #! 00 numbers represent to the MySQL
engine that the 00 is a version number that must be met by the
importing engine for the command to be executed.  So (off the cuff) if the
export was done on MySQL 1.2, and the importer is MySQL 1.1, any line that
has #! 010200 {some command} would not execute on the 1.1 version.  Some of
these statements still must be executed for SQLite to behave as expected,
so you can't just blindly remove all #! lines.


On Wed, Aug 7, 2019 at 12:13 PM Thomas Kurz  wrote:

> Dear SQLite team,
>
> I suppose I am not the only one having to convert between MySQL/MariaDB
> and SQLite databases every now and then. I know there are converters for
> MySQL dumps but none of any I have ever tried did work nearly reliable.
>
> So my suggestion would be to add an import feature to the CLI that allows
> to directly import MySQL/MariaDB dumps into an SQLite database keeping as
> many information as possible. As SQLite already has a complete SQL parser I
> expect much better results than with existing converters.
>
> Kind regards,
> Thomas
>
> ___
> 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] Proposal: SQLite on DNA

[Stephen Chrzanowski]

Do we really want to start going down this path?  Like... we already have
parents who won't allow their kids to get flu shots.  Will we need to give
our PCs the same kind of shots?!

On Thu, Jul 25, 2019 at 11:31 AM Chris Brody  wrote:

> > > May I humbly suggest that the development team look into porting to a
> new platform:
> >
> > > <
> https://www.knowstuff.org/2019/07/forget-silicon-sql-on-dna-is-the-next-frontier-for-databases/
> >
>
> +1 (+100)
>
> > As in Viral File System?
>
> Yes (haha)
> ___
> 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] Ionic SQLite get error: "Cannot read property 'executeSql' of undefined"

[Stephen Chrzanowski]

I don't know a single thing about Ionic other than how to spell it, but
what I do know is that there are two types of SQL calls that do different
things, and depending on the wrapper or interface, you might be looking at
a different command or implementation.

With SQL, one style of execution is is a command, where you go and tell the
database to do something, like, create a table, delete a table, delete a
row, etc.
Another is a Query, in which you expect to get an actual result back.

In Delphi/Pascal, these calls are PROCEDURES versus FUNCTIONS, or in
C/C++/C#, VOID versus INT (As an example)

The command executeSql (to me) sounds like the Command/Procedure where you
aren't looking for a result.  In my SQL wrapper, I have a procedure to call
that commands SQL to do something (ExecSQL) and another to return rows
(GetTable).  I cannot assign a variable with ExecSQL.

In this example ( https://ionicframework.com/docs/native/sqlite ) I see
that their example shows that of being a Procedure.

On Thu, Jul 25, 2019 at 9:05 AM Sebastien Capdeville <
capdeville.sebast...@gmail.com> wrote:

> Hello,
> I have an error with 'executeSql'  on a SQLite database within a ionic 4
> application and I can't find any solution about it, I must do something
> wrong and I would like to be helped about it.
>
> *Context:*
> My application is a CRM application (*Customer Relationship Management*).
>
> *Database:*
> My database is contained in the file : "CRMApp" which is a sqlite file with
> only one table : 'client'.
>
> *Problem:*
> I'm trying to retrieve ADRESSE$RAISON_SOCIALE column from the table client
> with the following service : "testdbservice" and then I would like to show
> the data that is retrieved in a list on the page : "accueil.page.html".
> And it keeps showing me the error message linked to this mail.
>
> I have attached the app.module.ts for any informations about all the things
> that I installed with npm.
>
> I have been searching this for over two weeks now, I hope someone can help
> me and sort it out as it is an important project for me.
>
> Have a nice day,
>
> Seb
> ___
> 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] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[Stephen Chrzanowski]

Actually, I take that back...

sqlite> select * from TheTable;
1|Test
2|Test2
3|TestNull1
4|TestNull2
5|TestNull3

So the inserting of NULL in a primary key (Single instance maybe?) will
insert the new rowid.

Try running a SELECT against your table and see what kind of results you're
obtaining.


On Thu, Jul 18, 2019 at 1:13 PM Stephen Chrzanowski 
wrote:

> NULL is a special thing.  It's never considered unique.
>
> SQLite version 3.20.0 2017-08-01 13:24:15
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
> sqlite> insert into TheTable (1,"Test");
> Error: near "1": syntax error
> sqlite> insert into TheTable values (1,"Test");
> sqlite> insert into TheTable values (2,"Test2");
> sqlite> insert into TheTable values (null,"TestNull1");
> sqlite> insert into TheTable values (null,"TestNull2");
> sqlite> insert into TheTable values (null,"TestNull3");
> sqlite> insert into TheTable values (2,"Test2.1");
> Error: UNIQUE constraint failed: TheTable.ID
> sqlite> select count(*) from TheTable;
> 5
> sqlite>
>
>
> On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon 
> wrote:
>
>> Hello,
>>
>> I have created a table.
>>
>> CREATE TABLE "sales" (
>> "client"TEXT,
>> "salesman"  TEXT,
>> "revenue"   REAL,
>> PRIMARY KEY("client","salesman")
>> );
>>
>>
>> I can run the query below mutliple times without any error :
>>
>> INSERT INTO sales ("client", "salesman", "revenue")
>> VALUES ('C1', NULL, 10.0);
>>
>>
>> Have I missed something in the CREATE instruction ?
>> Is this a normal behaviour ?
>>
>> I have tried to read https://sqlite.org/lang_createtable.html#constraints
>> and https://www.sqlite.org/nulls.html but I don't really have found why
>> I can insert 2 records that have the same primary key.
>>
>> SQLite version 3.27.2 2019-02-25 16:06:06
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> .headers on
>> sqlite> .mode column
>> sqlite> .nullvalue null
>> sqlite>
>> sqlite> CREATE TABLE "sales" (
>>...> "client"TEXT,
>>...> "salesman"TEXT,
>>...> "revenue"REAL,
>>...> PRIMARY KEY("client","salesman")
>>...> );
>> sqlite>
>> sqlite>
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', NULL, 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> sqlite>
>> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>>...> VALUES ('C1', '', 10.0);
>> Error: UNIQUE constraint failed: sales.client, sales.salesman
>> sqlite>
>> sqlite> SELECT * FROM sales;
>> client  salesmanrevenue
>> --  --  --
>> C1  null10.0
>> C1  null10.0
>> C1  10.0
>> sqlite>
>>
>> Thanks in advance for your insights.
>>
>> Alex
>> ___
>> 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] I can insert multiple rows with the same primary key when one of the value of the PK is NULL ...

[Stephen Chrzanowski]

NULL is a special thing.  It's never considered unique.

SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table TheTable (ID Integer, Info Text, PRIMARY KEY (ID));
sqlite> insert into TheTable (1,"Test");
Error: near "1": syntax error
sqlite> insert into TheTable values (1,"Test");
sqlite> insert into TheTable values (2,"Test2");
sqlite> insert into TheTable values (null,"TestNull1");
sqlite> insert into TheTable values (null,"TestNull2");
sqlite> insert into TheTable values (null,"TestNull3");
sqlite> insert into TheTable values (2,"Test2.1");
Error: UNIQUE constraint failed: TheTable.ID
sqlite> select count(*) from TheTable;
5
sqlite>


On Thu, Jul 18, 2019 at 12:02 PM Alexandre Billon  wrote:

> Hello,
>
> I have created a table.
>
> CREATE TABLE "sales" (
> "client"TEXT,
> "salesman"  TEXT,
> "revenue"   REAL,
> PRIMARY KEY("client","salesman")
> );
>
>
> I can run the query below mutliple times without any error :
>
> INSERT INTO sales ("client", "salesman", "revenue")
> VALUES ('C1', NULL, 10.0);
>
>
> Have I missed something in the CREATE instruction ?
> Is this a normal behaviour ?
>
> I have tried to read https://sqlite.org/lang_createtable.html#constraints
> and https://www.sqlite.org/nulls.html but I don't really have found why I
> can insert 2 records that have the same primary key.
>
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .headers on
> sqlite> .mode column
> sqlite> .nullvalue null
> sqlite>
> sqlite> CREATE TABLE "sales" (
>...> "client"TEXT,
>...> "salesman"TEXT,
>...> "revenue"REAL,
>...> PRIMARY KEY("client","salesman")
>...> );
> sqlite>
> sqlite>
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', NULL, 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', NULL, 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', '', 10.0);
> sqlite>
> sqlite> INSERT INTO sales ("client", "salesman", "revenue")
>...> VALUES ('C1', '', 10.0);
> Error: UNIQUE constraint failed: sales.client, sales.salesman
> sqlite>
> sqlite> SELECT * FROM sales;
> client  salesmanrevenue
> --  --  --
> C1  null10.0
> C1  null10.0
> C1  10.0
> sqlite>
>
> Thanks in advance for your insights.
>
> Alex
> ___
> 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] Grammar police

[Stephen Chrzanowski]

"an historical oversight" feels dirty to me, mostly because it's an
incomplete sentence and can be understood in different ways.  It's a
"point", or answer to a question.

In my verbage, "historical" begins with an H (Note here "AN H", not "A H",
because when saying "H", it starts with a vowel sound).  Its the same as
you wouldn't say "I hear an hissing noise", or "I just learned an History
Lesson".  Words prefixed with the sound "HISS" should be prefixed with "A"
in my mind, unless the H is silent, like Honor or Honest.

However, that said, it'd also probably depend on what the sentence is going
to be describing.  "I went to a historical event" leaves a bad taste in my
mouth, while "I went to an historical event" feels better, because I think
the E in Event carries over to the "AN", or, it could be a past vs present
meaning of the sentence.  But if I say "I've just learned a historical
lesson", the "L" sound in Lesson doesn't carry over correctly to "AN".

Let me be clear I'm not saying you're wrong or right, just that, in my head
and my syntax when I write sentences, anything that begins with H would end
up having an "A" prefix.  I don't use the word Historical or History all
that often, so I can't say how I've written it out in the past.

That said, the point of the sentence is presented whether "AN" or "A" is
used, in my opinion.  This is going down the lines of (Dare I say) is it
ESS-QUE-EL or "SEEK-WIL".

In my case, if I were to read that entire paragraph, I probably wouldn't
even blink on "... an historical oversight ..." or "... a historical
oversight ...".

On Fri, Jul 12, 2019 at 12:16 PM Jose Isaias Cabrera 
wrote:

>
> Warren Young, on Thursday, July 11, 2019 03:13 PM, wrote...
> >
> > On Jul 11, 2019, at 10:41 AM, Richard Hipp, on
> > >
> > > Here in the Southeastern US (specifically in Charlotte, NC) we really
> > > do say "an historical oversight".  If you said "a historical
> > > oversight", people would look at you funny.
>
> "an historical oversight" is the correct English syntax, by the way. ;-)
> ___
> 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] the sqlite3 documentation would be pretty good if it wasn't tragic...

[Stephen Chrzanowski]

I've always been perturbed by the term "Real World Examples".  Because...
well.. Everything is different from one building to the next.  How I make
my SQL calls is going to be different than the company across the street.
Heck, even the guy sitting across from me will come up with a different way
to get to the same answer.

"Real world" examples are really hard to come by considering that there are
soo so many "real world" types of information that needs to be asked for.
There is no "standard" query to get the same answer.  How I pull data out
of SQLite (Or any SQL DBMS) is going to be different than how Dr Hipp, how
Ryan, how Simon, or how any other regular user posts on here.  There's
thousands, if not MILLIONS types of queries to get the exact same answer
for a very specific required answer.  For instance, there's nearly an
infinite number of ways to get the answer "42" out of a database, but a
"real world" example doesn't exist because it can be done way too many ways.

The best way to figure out SQL is to dive in, and ask lots of questions.
If it breaks, find out another way.  Post your query here, demonstrate what
you've done to try and solve the answer to why you're getting the wrong
answer, and explain what the answer should be.  Learning anything is a
process, and SQL is absolutely no exception to that rule.  Or law for that
matter.

I've asked a few questions here that probably didn't make sense to anyone
else in this forum, but were crystal clear in my head.  Google, Ask,
Metacrawler, Yahoo, and yes, even Bing, will get you a part of the way
there, but none of those search engines will provide you a link to a single
site that says "To get to that answer, you must go this route, there's no
other alternative". (Ok, other than for syntax differences, you HAVE to go
a route, but...)

**Note:
SQL has a "standard" but no one really follows it to the "T".  You'll find
different quirks between different engines and their languages.  So a
particular query may work on one engine or multiple engines, but, that same
query may not work on one.  Generally, though [ select 1+1; ] should return
2 on ALL SQL engines.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug in sqlite3??

[Stephen Chrzanowski]

 Let's not forget that the size of the database is going to grow above and
beyond the "number of rows" due to pages that aren't reserved for your
data, such index pages, etc.

On Tue, May 28, 2019 at 1:49 PM Jens Alfke  wrote:

>
>
> > On May 26, 2019, at 7:21 PM, John Brigham  wrote:
> >
> > The size of the file reflects the number of rows that should be
> present.  And furthermore, the size of the file nicely reflects the number
> of days. […]  Trust me when I say that this file is way to big for the
> number of rows.
>
> SQLite files can contain free space after rows are deleted. (The free
> space will eventually be reused for new data, or it can be reclaimed using
> the VACUUM pragma.) So the size of the file does not necessarily reflect
> the amount of data it currently contains.
>
> Try opening a copy of the database with the `sqlite3` CLI tool and
> entering “PRAGMA vacuum;”. Then exit and look at the file size.
>
> —Jens
> ___
> 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] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

[Stephen Chrzanowski]

2019 WISH LIST
---
- A list of libraries that are known to be successfully added to SQLite.
- Have a base instance, select some check-boxes, press SUBMIT, something
somewhere somehow generates your .DLL, .SO, etc. so that you have
everything you want built in
- A million dollars
- More Pepsi [Check]
- No one to tell me that the first and second option are not feasible.
It's just a wish list. ;)

On Thu, May 23, 2019 at 6:28 PM R Smith  wrote:

>
> And to be clear, I don't think the Math lib would specifically be bloat,
> though it's an unneeded piece of added weight for my needs, but I will
> accept it happily if it makes most other people happy. But then, if we
> start with the math lib, what is next? Sure enough someone will come up
> with a very valid next bit to be added, and a next. I understand that
> Richard's decision on inclusion is not beyond reproach, but whose would be?
>
> Put another way, let's say we do add some libraries automatically,
> pushing the weight up a nice bit, but they are not the libs you wanted,
> would you be happy and consoled? If not, how do you expect anyone else
> to be happy with your choice being the chosen implemented? Would you
> then rather go back to how it was before?
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Affinity Performance - Non Issue?

[Stephen Chrzanowski]

Hey all;

I've seen it grumbled about before about giving a field a property of CHAR,
and have seen the correction that it should be TEXT.  I understand that
SQLite doesn't really "care" what the contents of the field is, but, just
out of curiosity, is there a kind of performance hit on using CHAR versus
TEXT or INT versus INTEGER?

Other than translating from one database engine to another, and with the
fact that SQLite doesn't care what the affinity is (Other than for internal
workings), I can't seem to think that there'd be a big hit since it needs
to determine what that value is supposed to be when reporting back to the
application.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] seeking information on the throughput requirement using Sqlite

[Stephen Chrzanowski]

All of that is going to ENTIRELY depend on your hardware, and how fast it's
going to allow the electrical pulses and actual processing of the different
chips on your mobo, and the kind of data you're going to be adding,
requesting, and removing.  There's no clear-cut answer with the information
provided.

You're going to get throttled if this sits in a VM, bar none.  If you have
other machines that are running on the host, you've got a few layers in the
stack between your VM and the metal that needs to handle your data, as well
as other data in the other VMs.  Mind you, if you have a VM sitting solo on
your host, you'll be darn near 1:1 for Metal:VM speeds, but it isn't
exactly 1:1.
You're going to get throttled if you have other processes taking disk IO
and CPU.
You're going to get throttled at your database design if you don't include
indexes.
You're going to get throttled at the volume of data you're adding and
deleting (It'll be faster to delete as SQLite just marks space as being
available and overwrites existing data, but then you could be looking at
fragmentation not only in the database, but, the disk as well)
The type of file system you're using can also be a throttle point.

The physical database size doesn't really come into play, unless you're
talking billions of index records that needs to be dealt with per call.
Actual data is returned to your application one row at a time, not as a
full chunk of data for your entire request.  On any query, SQLite will
reference indexes heavily (If available, and if it makes sense), and
doesn't touch/look@ anything it doesn't need.

The more hardware you throw at it, the faster you're going to get, but on
top of that, ultimately, you're limited by the speed to which the
electricity flows through the tiny little wires and connections.


On Fri, May 3, 2019 at 10:02 AM Zhu, Liang [AUTOSOL/ASSY/US] <
liang@emerson.com> wrote:

> Hi Sqlite experts,
>
> In my current project, we have the need to inserting and deleting data to
> and from the database between 1 and 10 ms for 24/7.   I am seeking the
> clarification on the following questions
>
>
>   1.  what is the throughput requirements are possible using Sqlite API?,
> in other words, what is the max  speed can my inserting and deleting
> operation be?
>   2.  When we inserting and deleting data at the speed mentioned above,
> what kind database maintenance do we need to do to maintain the performance
> of the database?
>   3.  How is constant deleting and insert effect the database performance?
>
>
> Thank you,
>
> Liang Zhu | Lead Software Engineer | Branson Ultrasonics
> Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA
> T (203) 796-2235 | F (203) 796-0380
> liang@emerson.com
>
> The information contained in this message is confidential or protected by
> law. If you are not the intended recipient, please contact the sender and
> delete this message. Any unauthorized copying of this message or
> unauthorized distribution of the information contained herein is prohibited.
>
> ___
> 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] Page Size question

[Stephen Chrzanowski]

When I get my act together and get un-lazy about it, I want to take a large
set of "small" files and put them into a SQLite database blobs in a table,
then have my app run some kind of CRC check
(CRC32/MD5SUM/SHA1/custom/whatever - Haven't decided on a CRC or just
byte-for byte comparisons) to identify byte exact duplicate files.  I'm not
so much concerned about the physical file size of the database file and how
the page size can cross 4k sized cluster boundaries on a disk drive, but a
possible optimization on the page size within the database for reading and
writing the data from and to the database file.

ALL of the files are going to be a multiple of 174,848 bytes.  So that,
plus that CRC check, plus an integer based auto-inc'd field, I'm wondering
if there is going to be any optimization internally from SQLite by making
the page size, say, 180k? (I know, there'll be unused bytes in the page,
but, that's a low priority thing -- I'll tighten up that page size and
re-import data when I get more to a "production" state)

My app is going to be on Windows, and I'm not concerned about constraints
of memory or CPUs or anything of the sort.  I'm just thinking about reading
in and out of the database as a POSSIBLE early (Not exactly premature I
believe) optimization.  I figure that if SQLite is going to read in the
174,848 byte blob, it'd be quicker to read in that one page file into
memory rather than take multiple stabs across a fragmented file, of course,
having the full understanding that fragmentation will happen at the file
system level.

These raw blobs are going to be sitting in a table by themselves, with a
separate many-to-one table sitting elsewhere for other nefarious reasons.
I know that this table, plus any others, are going to end up sitting in the
180k sized pages, but beyond that, is there any practical gain going to
180k instead of the default 4k?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use cases for sqlite3_value_frombind()?

[Stephen Chrzanowski]

So it becomes the responsibility of the application to acknowledge when
binding is allowed or not.  Got it.  Clear now.  Thanks.

I was thinking small in this case.  My own app, I know what's being fed, I
already bind most things, but I absolutely can see the use of this.  Time
to see if I can add this particular function to my wrapper... .. one of
these days.

On Wed, Apr 17, 2019 at 2:22 PM Simon Slavin  wrote:

> On 17 Apr 2019, at 6:37pm, Stephen Chrzanowski 
> wrote:
>
> > What measures the trustworthiness?  At what point would the running
> > application be notified that the statement was bound or injection avenue?
>
> You can include parameters as text in your SQL command:
>
> UPDATE invoices SET toBePaid="1.23" WHERE customerId="7524"
>
> If someone is attacking your server using SQL injection on a whole
> statement, that's what they'd do.  And sqlite3_value_frombind() would
> return FALSE.  Of course, to detect this the application does need to call
> sqlite3_value_frombind() on each parameter it cares about.
> ___
> 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] Use cases for sqlite3_value_frombind()?

[Stephen Chrzanowski]

What measures the trustworthiness?  At what point would the running
application be notified that the statement was bound or injection avenue?

On Wed, Apr 17, 2019 at 12:40 PM Richard Hipp  wrote:

> On 4/17/19, Jens Alfke  wrote:
> > The new sqlite3_value_frombind() function sounds intriguing — "True if
> value
> > originated from a bound parameter
> >  — but I’m drawing a blank thinking of use cases for it. Optimizations?
> > Security? What was the rationale for adding it?
>
> This facilities additional security measures.  If a value comes from a
> bind, then (at least in most systems) that means it did not come from
> an SQL injection from an attacker, and hence the value is more
> trustworthy.
> --
> 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] Help with loading .DAT files

[Stephen Chrzanowski]

.DAT files can be anything.  If you can just use sqlite3.exe to open the
.DAT and do proper queries on it (IE: sqlite3.exe yourfile.dat), then it's
a proper SQLite3 database, so then you SHOULD be able to use the Attach
command.  Otherwise, you need to change the .DAT contents to something else
that can be understood by IMPORT and OPEN.


On Mon, Apr 15, 2019 at 12:25 PM Pablo Boswell (US - ASR) <
pablo.bosw...@pwc.com> wrote:

> I am trying to use Command Line Interface (CLI) sqlite3.exe to import .DAT
> files to an in-memory SQLite database.  I cannot get the following commands
> to load anything reasonable (the engine always decides to load the data as
> a single TEXT column with a column name of "sqlite3 data"):
>
> - .ATTACH
> - .IMPORT
> - .OPEN
>
> What am I doing wrong?
>
> --
> *Pablo Boswell*
>
> __
> The information transmitted, including any attachments, is intended only
> for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited, and all liability arising therefrom is disclaimed. If you
> received this in error, please contact the sender and delete the material
> from any computer. PricewaterhouseCoopers LLP is a Delaware limited
> liability partnership.  This communication may come from
> PricewaterhouseCoopers LLP or one of its subsidiaries.
> ___
> 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] Remove row to insert new one on a full database

[Stephen Chrzanowski]

This almost sounds like "Full" is a software limitation, in that your
application is specifying that "Full" means you can only have "X" number of
rows.

If you're looking to remove data, I'd suggest that you find some way to
isolate the oldest record, either by a row identifier (Like an ID field
that's using auto-increment) or a date/time stamp (Assigned by
current_timestamp).

Also, your logic is backwards in your pseudo-code.  You should check the
status of the database before you do any kind of insert. The reason is, if
you insert into an already full database, then you're database is over-full
at that point.  Also, your pseudo-code has two conditions to look at...  Do
this forever, and repeat while status is full.  Not to mention, if your
database is messed up and nothing can be inserted even though the table is
empty, you've introduced a lockup.

What I think you're looking more for is:

while (dbStatus() == full) {
  remove_one_row_from_db();
}
result=insert_1_row_to_db();
if (result != resOK) {
  die("uhh.. Problem with the database?");
}


On Thu, Apr 4, 2019 at 6:53 AM Arthur Blondel 
wrote:

> Hello
>
> When I try to insert new data to a full SQLite database, I need to remove
> much more than really needed. I'm doing the following:
>
> while(1) {
> do {
> status = insert_1_row_to_db();
> if (status == full) {
> remove_one_row_from_db();
> }
> } while (status == full);}
>
> The inserted data has always the same size. When the database is full,
> removing only one row is enough to insert the new one. But after a while, I
> need to remove 30, 40 and even more the 100 rows to be able to insert one
> new row. Is it the correct behavior of SQLite? Is there a way to remove
> only what is needed and no more? Thanks
> ___
> 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] Database backup with writers present?

[Stephen Chrzanowski]

Does write blocking still come into play when using "vaccum into", or are
writes put into the WAL file?  I know that with the regular backup API, any
writes to the DB restarts the backup process, but I hadn't thought about
putting either into a transaction.  (I'm an I-D-TEN-T, I know)

On Wed, Feb 27, 2019 at 10:20 AM Richard Hipp  wrote:

>
> I assume you are using WAL mode?  If not, you should be.  Just making
> that one change might solve your problem.
>
> In WAL mode, run the entire backup inside a transaction.  Or use the
> new VACUUM INTO feature to make your backup.
>
> --
> 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] MySQL Dumps into SQLite

[Stephen Chrzanowski]

Ah yes.  I forgot about that.  I don't use WITHOUT ROWID typically, so it
didn't enter the thought process.


On Mon, Feb 11, 2019 at 2:29 PM J. King  wrote:

> On February 11, 2019 2:19:27 PM EST, Stephen Chrzanowski <
> pontia...@gmail.com> wrote:
> >This is a dump from a MySQL table I created a few years ago.  I'm not
> >moving this particular database into SQLite, but, from what I learned
> >today
> >about MySQL dumps and the commenting system, I was kind of interested
> >on
> >how SQLite would handle the rest of the following statement:
> >
> >CREATE TABLE `Clusters` (
> >  `ClusterID` int(11) NOT NULL auto_increment,
> >  `ClusterName` varchar(50) NOT NULL default '',
> >  `Description` text NOT NULL,
> >  `GroupID` int(11) NOT NULL default '0',
> >  `ClusterOrder` int(11) NOT NULL default '255',
> >  PRIMARY KEY  (`ClusterID`)
> >) ENGINE=InnoDB DEFAULT CHARSET=ascii;
> >
> >Obviously, SQLite doesn't know what ENGINE is, or anything of the sort
> >(Actually, this statement didn't work due to the auto_increment
> >keyword.
> >Once I removed it, I got to the meat and potatoes of this question)
> >
> >The version of SQLite I'm using on this ancient machine is 3.5.9.  I
> >cannot
> >upgrade it, as the OS is long out of support, isn't used by anyone
> >other
> >than our staff for a jump point to other servers, and there's so much
> >legacy crap on this box that upgrading would break anything that is
> >running
> >on it.  I know SQLite3 is mostly backwards compatible, but I'm not
> >going to
> >go playing with a production machine. ;)
> >
> >Could there be an inch of movement for the future so that in this
> >particular example, SQLite would ignore everything between that final
> >closing bracket and the semi-colon?  Obviously a lost cause on this
> >particular host, but, thinking about the future and all...
> >
> >(FWIW, I just tried on 3.20.0 and the ENGINE thing is still a "broken"
> >thing)
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> For what it's worth it's not possible to ignore anything after the closing
> bracket: SQLite itself uses this space to declare WITHOUT ROWID tables.
> --
> J. King
> ___
> 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] MySQL Dumps into SQLite

[Stephen Chrzanowski]

This is a dump from a MySQL table I created a few years ago.  I'm not
moving this particular database into SQLite, but, from what I learned today
about MySQL dumps and the commenting system, I was kind of interested on
how SQLite would handle the rest of the following statement:

CREATE TABLE `Clusters` (
  `ClusterID` int(11) NOT NULL auto_increment,
  `ClusterName` varchar(50) NOT NULL default '',
  `Description` text NOT NULL,
  `GroupID` int(11) NOT NULL default '0',
  `ClusterOrder` int(11) NOT NULL default '255',
  PRIMARY KEY  (`ClusterID`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;

Obviously, SQLite doesn't know what ENGINE is, or anything of the sort
(Actually, this statement didn't work due to the auto_increment keyword.
Once I removed it, I got to the meat and potatoes of this question)

The version of SQLite I'm using on this ancient machine is 3.5.9.  I cannot
upgrade it, as the OS is long out of support, isn't used by anyone other
than our staff for a jump point to other servers, and there's so much
legacy crap on this box that upgrading would break anything that is running
on it.  I know SQLite3 is mostly backwards compatible, but I'm not going to
go playing with a production machine. ;)

Could there be an inch of movement for the future so that in this
particular example, SQLite would ignore everything between that final
closing bracket and the semi-colon?  Obviously a lost cause on this
particular host, but, thinking about the future and all...

(FWIW, I just tried on 3.20.0 and the ENGINE thing is still a "broken"
thing)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this rewrite of a query OK

[Stephen Chrzanowski]

I can see this query being useful as a part of an abstraction theory.  You
take away the responsibility of having a developer handle the math, so the
developer doesn't need to worry about what the two primary values are.
They can just pick what they need from the query.  Back in the Borland
Pascal days, when looking at drive space use and availability, I'd get
"frustrated" or slightly annoyed that I'd have to have a helper function or
code for the delta on storage.  IIRC, there is code to tell you the size of
the drive, and code to tell you the amount of used space, but, nothing
about what's free, or something along that line.  So a function, variable
or inline-math had to be written to give me the appropriate value.

BUT, I do also understand the theory that the DB engine (Of any flavor)
should be used to provide code the raw data, and the code should handle
presentation and manipulation.

Six in one basket, half dozen in the other, IMO.


On Fri, Jan 25, 2019 at 7:38 PM Jens Alfke  wrote:

>
> Couldn’t you do the subtraction in the program that’s running the query?
> (Or is this something you run directly from the shell?)
>
> —Jens
> ___
> 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] Variable Declaration

[Stephen Chrzanowski]

But then in BASH, for example, the temp table would only last for the life
of the initial connection, so you'd have to throw everything in one
execution.  This goes along the lines of just setting a variable.  A BIG
time question comes to scope of the life of the variable.  Does it live for
the entirety of the databases life?  Does it live for that connection?
Does it live for that transaction?  Too many ways to have that play out
that it just may not be worth while coding it into the SQLite base anyways.

I guess there are many different ways to skin this cat.  Temp tables, going
in and out of quotes, binding, etc.  I was just curious whether variables
were something to be, or are being considered, or, if it's something that's
been outright "Will not do".  I don't know of Postgres has variables or
not, but that's server side, and Keith mentions, SQLite works off of single
statements.

On Sat, Jan 19, 2019 at 2:12 PM Scott Robison 
wrote:

>
>
> 1> create temp table vars(name, value);
> 2> insert into vars values('count', 10);
> 3> select * from test where i <= (select value from vars where
> name='count');
>
> That's just one example, of course. Multiple variations on the theme are
> possible if one needs variables that live exclusively in SQL without using
> a host language and that also survive for more than a single statement.
> ___
> 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] Variable Declaration

[Stephen Chrzanowski]

I know about the bindings.  I don't know about all languages supporting it.

On Fri, Jan 18, 2019 at 11:26 PM Simon Slavin  wrote:

> On 19 Jan 2019, at 3:57am, Stephen Chrzanowski 
> wrote:
>
> > I was going to have three variable set with
> > three different strings I could just copy/paste to generate that
> > comparative list.
>
> You know SQLite does binding, right ?  You don't have to construct your
> command like
>
> command$ = "INSERT INTO MyTable VALUES ("+a$+","+b$+","+c$+")"
>
> You can do this:
>
> command$ = "INSERT INTO MyTable VALUES (?,?,?)"
>
> or
>
> command$ = "INSERT INTO MyTable VALUES ($name,$address,$phone)"
>
> and maintain the variables in whatever programming language you're using.
> You just have to remember to bind before you tell SQLite to execute
> command$.
> ___
> 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] Variable Declaration

[Stephen Chrzanowski]

More simple than that.  In MSSQL there's the DECLARE and SET commands (
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-2017
)

I was thinking that while I'm going through my list of "To make this, you
need that and the other thing", I was going to have three variable set with
three different strings I could just copy/paste to generate that
comparative list.  But then I got to thinking I could do the same kind of
thing with Excel, export to CSV, and just import it into SQLite.  Then I
got really lazy and decided to write an actual application to handle the
database instead, and use a GUI with Combo boxes and lists to show me what
I can make with what I have, etc.  Saves typing and keeps up accuracy, so
my request for this project is kind of moot.

But the principal for having this for some things would be nice. It'd
mostly fix the going in-and-out of quotes to create the SQL statements.  I
know binding is available in most places, but this just "sounds" more
easier.

On Fri, Jan 18, 2019 at 9:12 PM Keith Medcalf  wrote:

>
> SQLite3 does have variables.
>
> You set them by binding values to an SQL statement, and retrieve them by
> retrieving column values resulting from the execution of an SQL Statement.
> For example:
>
> sqlite3_prepare('select var1, var2 from table1 where val3=?;')
>
> You bind a value to the val3 variable using one of the bind functions,
> then when you sqlite3_step the statement, you can use sqlite3_column_xxx to
> retrieve the value of the var1 and var2 variables.
>
> Or did you have something else in mind?
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
> >Sent: Friday, 18 January, 2019 18:52
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Variable Declaration
> >
> >Tonight, I wanted to write up a small database to keep tabs on
> >certain
> >things I've done in a game, and show me new tasks I can do based on
> >what
> >I've completed.  I have a full list of items, and what pairs of items
> >I
> >need to poses to get the next thing, but, getting the data into the
> >DB is
> >tedious.   I'm still going to do it, but, the thought of using
> >variables
> >came to mind, and apparently this isn't something that's supported?
> >
> >I've seen long wind answers to how this can be completed on
> >stackoverflow,
> >and other sites, and for this one-time thing I'm doing, I'm not
> >really
> >interested in those proposals.
> >
> >So for now, trudging on without, but, has there been recent
> >discussions or
> >any plans on introducing variables to SQLite?  I know it's a bit of
> >fluff,
> >and the whole "Keeping it light" thing, but, it's kind of useful in
> >some
> >cases.
> >___
> >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

[sqlite] Variable Declaration

[Stephen Chrzanowski]

Tonight, I wanted to write up a small database to keep tabs on certain
things I've done in a game, and show me new tasks I can do based on what
I've completed.  I have a full list of items, and what pairs of items I
need to poses to get the next thing, but, getting the data into the DB is
tedious.   I'm still going to do it, but, the thought of using variables
came to mind, and apparently this isn't something that's supported?

I've seen long wind answers to how this can be completed on stackoverflow,
and other sites, and for this one-time thing I'm doing, I'm not really
interested in those proposals.

So for now, trudging on without, but, has there been recent discussions or
any plans on introducing variables to SQLite?  I know it's a bit of fluff,
and the whole "Keeping it light" thing, but, it's kind of useful in some
cases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL mode for in-memory databases?

[Stephen Chrzanowski]

That doesn't sound healthy at all.  If your application dies, what happens
to the database?  What if something rogue starts hitting it and just chews
up your memory?

IMO, Mem databases should be short lived and treated simply as an
intentional cache.  I get they're fast, but, long term life for a finite
resource isn't something I want kicking around when an application dies.

On Mon, Jan 14, 2019 at 1:43 PM Thomas Kurz  wrote:

> It would also be very helpful if more control about in-memory-databases
> was available. As far as I have understood, an in-memory database is
> deleted when the last connection closes. This requires me to always hold a
> connection to an in-memory database even if don't need it right now.
>
> Maybe one could introduce a pragma "persistent_inmemory" that allows to
> keep an in-memory-database until explicitely deleted. (DROP DATABASE IF
> EXISTS ...)
>
> ___
> 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] Syntax clarification

[Stephen Chrzanowski]

The .add indicates you're using some kind of dictionary type of class like
a tStringList and would append that string to the end of that dictionary.
The .Text would replace the contents of the dictionary with that string.


On Friday, December 28, 2018, Dave Delage  wrote:
> I've searched without success for this answer. I use SQLite3, Zeos and
Delphi so maybe this isn't a perfectly sqlite3 question but here goes:
>
>  What are the pros/cons of query.sql.text := 'some string'; versus
query.sql.add('some string');
>
> Dave
>
>
>
>
>
>
> ---
> This email has been checked for viruses by AVG.
> https://www.avg.com
>
> ___
> 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] Sample Employee database ported to SQLite from MySQL

[Stephen Chrzanowski]

Right up until procedures change and the identifier has changed from an
integer only data type to something that starts containing characters.
There's so many different types of UPCs out there, for example, that a
company can switch from an integer only type of UPC to something that
contains letters.

I'd err on the side that if there is even the hint that something in the
real world could change to what my schema isn't going to allow for, then,
make it as open-ended as possible, even going as far as changing the field
to a BLOB and have the softwares future functionality be able to store the
exact image that was scanned in.



On Thu, Dec 20, 2018 at 10:44 AM Chris Locke 
wrote:

> > Just because something doesn't have to be calculated, means that it has
> to be stored as text.
>
> Sorry - forgot a 'doesn't'.
> Just because something doesn't have to be calculated, doesn't mean that it
> has to be stored as text.
>
> On Thu, Dec 20, 2018 at 3:42 PM Chris Locke 
> wrote:
>
> > Just because something doesn't have to be calculated, means that it has
> to
> > be stored as text.
> > Its usually recommended to set the column affinity to the type of data
> > you're storing.  If you're storing a number (and a model number is a
> > numeric number) then it should be stored in a numeric field.  If your
> model
> > number has punctuation, then yes, a text field is required.
> > Its up to the application (although some would also argue the database)
> to
> > validate data input, ie, ensure numeric data was inputted into a numeric
> > field.
> >
> > On Thu, Dec 20, 2018 at 3:31 PM James K. Lowden <
> jklow...@schemamania.org>
> > wrote:
> >
> >> On Wed, 19 Dec 2018 10:55:11 +
> >> Chris Locke  wrote:
> >>
> >> > Fields with '_no' are read as 'number' and so should be a number.
> >> > OK, that doesn't always work for 'telephone_no' (they usually start
> >> > with a 0
> >>
> >> Lots of numbers are labels that aren't meant to be calculated on.  Item
> >> number, part number, model number, serial number, order number.
> >> Anything that needs to be distinguished and isn't worth naming.
> >>
> >> It's never a good idea to store such numbers as numerical types.
> >> There's always  a potential loss of information, be it the leading zero
> >> or embedded '-' or multiple '.' characters.  Unless the "number" is a
> >> quantity, for compuational purposes it's text.
> >>
> >> --jkl
> >>
> >> ___
> >> 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

[sqlite] Monitoring SQLite Web Site

[Stephen Chrzanowski]

This email is more to Dr Hipp, but, there are other members of this list
that might also benefit from this site I'm about to mention.  I know there
are many out there, but monitoring 50 sites for free for their up-time is
kind of nice.

Right up front, I have exactly ZERO affiliation with the product I'm about
to mention.  I have no financial backing from, or towards this product.
I've never spoken to anyone there, or no one from there has reached out to
me.  This is an opinion of my own.  It's a tool we're using here at work,
and I've just started to use to monitor my home servers as a method to
monitor when my ISP decides to shut me down randomly at night.

In the past, there have been reports about the SQLite sites being
unavailable.  It's unfortunate, but it happens to everyone.  Instead of
having someone from the user list report problems, maybe use
https://www.freshworks.com/website-monitoring/ to get an email report out
to the admins immediately?  Freshping checks the site you specify once per
minute, and throws a report when down.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Segmentation Fault When Using Window Function

[Stephen Chrzanowski]

I'm only seeing the one email, no duplicates.

On Fri, Nov 9, 2018 at 10:35 AM Dennis Clarke  wrote:

> On 11/08/2018 04:05 PM, Richard Hipp wrote:
> > In case you are not following the ticket at...
>
> 
>
> The list mail server sends this out about thirty times.  Not sure if
> anyone else sees abusive duplicates from the sqlite mail list server
> but I certainly do.
>
> Dennis
>
> ___
> 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] Database identified as zip

[Stephen Chrzanowski]

That said, I still didn't know the shell/CLI could do that.  But 14.5 and
14.6 are pretty far down https://sqlite.org/cli.html so, I probably gave up
after 6.1 or 6.2 {smirk}

Good nugget to keep rattling in my head.

On Tue, Oct 30, 2018 at 10:44 AM Richard Hipp  wrote:

>
> The core SQLite library understands SQLite database files only.
> Reading and writing of ZIP archives and other formats is handled by
> extensions.  Those extensions are built into the CLI.  The following
> discussion applies to the CLI, not to the core SQLite library.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database identified as zip

[Stephen Chrzanowski]

First off, I didn't know that SQLite could read from zip files directly.  I
don't like it, but, I'm sure there's a use case somewhere.

Second, shouldn't SQLite only consider reading a file if the file size is
zero or if the appropriate SQLite header is found, and completely ignore
the file extension?  If I create a normal SQLite file, then rename the file
to a .zip, the content isn't a ZIP, so things would break, I would think.


On Tue, Oct 30, 2018 at 9:49 AM Richard Hipp  wrote:

> On 10/30/18, Dingyuan Wang  wrote:
>
> > I look at the database file, there is a half zip file in the end. So
> > maybe the PK header confused the SQLite command line.
>
> That is the likely explanation.
>
> The code that deduces the database type (whether it is an ordinary
> SQLite database file, or a ZIP archive, or something else) is seen at
> (https://www.sqlite.org/src/artifact/ac4a731dac549746?ln=3666-3694).
> Line 3687 is the one that is causing problems for you, I am guessing.
>
> >
> > Is this considered a bug?
>
> I suppose it is.  I'm still trying to figure out exactly how to fix
> it, though.  Perhaps we should only look for the EOCD record at the
> end of the file if the file does not begin with the proper SQLite
> database prefix.
> --
> 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] Regarding CoC

[Stephen Chrzanowski]

I like this.

If I were to use this (And I don't expect I'll ever have to since I
typically write just for me) the only addition I'd make to this is about
the reporting.

From a users perspective, if the utility has something to report "home"
with (lets say crash reports), it'd have to be ABUNDANTLY clear and written
on the tin that the software has the capability to do so, and CLEARLY give
the option to view exactly what is being transmitted, and never in an
automated way without users consent. ... unlike...
https://www.extremetech.com/wp-content/uploads/2016/08/Windows10-BSOD-640x353.jpg


On Wed, Oct 24, 2018 at 1:28 PM dmp  wrote:

> Code Of Conduct, misplaced disposition on the individuals of
> an organization rather than the results of their work on
> intent.
>
> I have had a simple statement with my open source software
> downloads for years.
>
> "Dandy Made Productions would like to assure individuals that
>  any applications downloaded from this site are free from any
>  malicious code as so created. Great pride is taken in trying
>  to create ethical software that does not knowingly modify, or
>  change files or a system's configuration beyond the user's
>  request. In addition no software downloaded from this site
>  performs any type of monitoring or reporting on the user's
>  behavior in use of said application. Every reasonable attempt
>  is made to maintain the integrity of the downloaded software
>  packages at this site."
>
> Dana M. Proctor
>
> ___
> 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] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Stephen Chrzanowski]

I suppose you COULD get rid of the \n in the query  Its a Linux thing.
*sigh*

On Fri, Oct 19, 2018 at 10:50 AM Stephen Chrzanowski 
wrote:

> This would be something you'd need to write something quick for.  There's
> no application in the world that knows how to use your specific schema and
> react to it.  There may be apps that will read the values and automatically
> 'understand' that http:// and https:// are addresses, but, these probably
> are custom built applications that do something completely different to
> what you're doing. I've just checked with SQLite Expert, and it doesn't
> have any knowledge of how to treat a field as a URL/URI.
>
> That said, you may be able to get an ugly query going, and dump it to an
> HTML file you can bring up in your browser.
>
> My example table:
> CREATE TABLE [Test](  [TestID] INTEGER,   [Name] CHAR,   [Address] URL);
> INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(1, 'Google', '
> http://www.google.com');
> INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(2, 'YouTube', '
> http://www.youtube.com');
>
> Then execute SQLite3.exe like this:
>
> R:\>c:\myapps\sqlite3.exe
> SQLite version 3.20.0 2017-08-01 13:24:15
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open TestDB.db3
> sqlite> .output test.html
> sqlite> select ''||Name||'\n' from Test;
> sqlite> .quit
>
> R:\>type test.html
> http://www.google.com;>Google\n
> http://www.youtube.com;>YouTube\n
>
> (R: for me is a RamDRIVE)
>
>
>
> On Fri, Oct 19, 2018 at 10:03 AM Winfried  wrote:
>
>> Hello,
>>
>> I have a bunch of hyperlinks in an SQLite database, and need to read each
>> page for validation before deleting the record.
>> To make it easier, the DB manager should launch the default web browser
>> when
>> I double click on a column that contains a hyperlink.
>>
>> Before I build a GUI, is there a Windows SQLite database manager that can
>> do
>> this?
>>
>> I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none
>> seems
>> to support this feature.
>>
>> Thank you.
>>
>>
>>
>> --
>> Sent from: http://sqlite.1065341.n5.nabble.com/
>> ___
>> 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] Windows app to read SQLite DB, and launch web browser when dbl-clicking a record?

[Stephen Chrzanowski]

This would be something you'd need to write something quick for.  There's
no application in the world that knows how to use your specific schema and
react to it.  There may be apps that will read the values and automatically
'understand' that http:// and https:// are addresses, but, these probably
are custom built applications that do something completely different to
what you're doing. I've just checked with SQLite Expert, and it doesn't
have any knowledge of how to treat a field as a URL/URI.

That said, you may be able to get an ugly query going, and dump it to an
HTML file you can bring up in your browser.

My example table:
CREATE TABLE [Test](  [TestID] INTEGER,   [Name] CHAR,   [Address] URL);
INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(1, 'Google', '
http://www.google.com');
INSERT INTO [Test]([TestID], [Name], [Address]) VALUES(2, 'YouTube', '
http://www.youtube.com');

Then execute SQLite3.exe like this:

R:\>c:\myapps\sqlite3.exe
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open TestDB.db3
sqlite> .output test.html
sqlite> select ''||Name||'\n' from Test;
sqlite> .quit

R:\>type test.html
http://www.google.com;>Google\n
http://www.youtube.com;>YouTube\n

(R: for me is a RamDRIVE)



On Fri, Oct 19, 2018 at 10:03 AM Winfried  wrote:

> Hello,
>
> I have a bunch of hyperlinks in an SQLite database, and need to read each
> page for validation before deleting the record.
> To make it easier, the DB manager should launch the default web browser
> when
> I double click on a column that contains a hyperlink.
>
> Before I build a GUI, is there a Windows SQLite database manager that can
> do
> this?
>
> I tried DB Browser for SQLite, SQLitespeed, and SQLiteStudio, but none
> seems
> to support this feature.
>
> Thank you.
>
>
>
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> ___
> 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] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

FSCK is File System ChecK, so the equivalent in Windows Scandisk.

Trace through the code to find out when exactly the SQLite is reporting the
lock.  Is it at a query, on opening, is it multi-thread or
multi-application access?  Is the query taking too long?  If you're
debugging and everything is working through the transactions, I'd look at
how you're setting the timeout and maybe it isn't being passed through
correctly.  As mentioned, depending on the size of the DB, your AV may be
holding on and doing a scan of the file that is much longer than the 5
seconds.  Set breakpoints at every point where you do something with SQL,
then first step-over the break point to validate that specific function
isn't causing the lock, then resume until the next break point.  When/if
you do hit a break point that causes a lock, restart the app, then
step-into the function and see where the lock is happening.

Transactions may also cause some grief, but I defer to those with more
experience on that.  I know that there are blocks in play for writes, not
100% sure if you open a transaction if you'll get a locked message from
another call.

Another thing you could do is, again depending on the size of your database
and memory constraints, is "backup" the database to memory, if you're only
using a single-thread & single-application, this should be valid.

For a short bit, turn off all your AV software, or, get an AV free OS up
and running to test your application on.  Disconnect your Ethernet if
you're truly paranoid about getting a rogue application hitting your
machine while you've got AV off.  (Been there with Melissa Virus)
Another option is start up a new application and just have it do some data
massaging on a scrap database.  SQLite Expert I think has a demo DB you can
play with, or, just start from scratch with a single file, single table
database.  If your new application can do work without your AV having a
problem, then I'd be looking at what exactly your code is doing.  I'm sure
there's a SQLite3 Northwind database here:
https://github.com/jpwhite3/northwind-SQLite3

I use the free version of Berlin and Tokyo, and I've never had a lock issue
that couldn't be explained by another process taking too long with the
data.  I typically write single-threaded applications, but sometimes there
are multiple applications touching the database.  (IE: SQLite Expert;
sqlite3.exe, my applications, etc).  I've only ever run into database
locked issues is when my app is writing the data pretty quickly to the DB
and SQLite Expert runs a query.  I'll get a timeout somewhere, but its
easily explained.  Expanding the timeout on both applications usually fixes
the locked issue.

On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen  wrote:

> Thank you for your response. To answer your questions:
>


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

How about moving the databases elsewhere?  Sometimes applications are picky
when it comes where files are being written to (IE: Program Files).

On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen  wrote:

> Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the
> problem - unfortunately!
>
> -Oprindelig meddelelse-
> Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> På vegne af Stephen Chrzanowski
> Sendt: 12. oktober 2018 14:25
> Til: General Discussion of SQLite Database
> Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
> locked
>
> Change the timeout value on the connection to 5 seconds or so.  The DB will
> retry.  The AV has its grip on it, doing a scan, and won't let anything
> else touch it until its done.
>
> On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen 
> wrote:
>
> > Hello,
> >
> > I have made (in Delphi) a program that writes to a sqlite database. This
> is
> > a simple stand-alone solution. Now something is blocking for the programs
> > attempt to writing to the databse. There is a an error message:
> > [FireDAC][Phys][SQLite] Database is locked.
> >
> > A friend of mine has analysed the scenario and tells me that BitDefender
> is
> > the Problem.  In BitDefender "Application Access" I have given my program
> > permission to "Work".
> >
> > Any solution on how to "tell" BitDefender that it is OK to make changes
> in
> > my database? (I have tried BitDefender support - but no ticket answer!)
> >
> > Maybe also somebody knows an antivirus/anti-malware solution which are
> not
> > so "hard" as BitDefender? (and with a userfriendly GUI).
> >
> > Thank you so much for any help. I am rather "desperate" for some help!
> >
> > Best Regards
> >
> > Lars
> >
> >
> >
> >
> >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

Change the timeout value on the connection to 5 seconds or so.  The DB will
retry.  The AV has its grip on it, doing a scan, and won't let anything
else touch it until its done.

On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen  wrote:

> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>
>
>
>
> ___
> 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] DB To DB Transfer Time

[Stephen Chrzanowski]

You're kind of asking if a 4 cylinder engine can outrun an 8 cylinder
engine by just physically looking at the engines.  The answer is yes.  It
can.  Given the right circumstances.

Your transfer rates are going to be subjective based on your hardware, network
speeds at the source and destination (I know, LAN, but still relevant),
plus what kind of transactions you're doing per-insert on your SQLite
database, indexing on the database, and such.

If you DON'T wrap your insert/update/delete statements in a transaction,
then by SQLite will open a transaction, write your one row, then close the
transaction, and do that for every single row you're
adding/deleting/updating.

Just writing this particular email, I've been bouncing back and forth
between "Yeah, that's fine" and "Well, maybe not..." because there's a LOT
of things in the way that we're not clear on.  Especially the transactions
aspect.



On Tue, Sep 25, 2018 at 2:14 PM, dmp  wrote:

> Hello,
>
> I have created a database to database transfer plugin for
> my application. The plugin is functional and I have
> collected a log of testing results stored in a SQLite db.
> The links below are of the database and definition of
> parameters fields. The main exception, dbTypeMemory,
> meaning memory or file db.
>
> https://github.com/danap/db_to_filememorydb/blob/master/
> docs/dblog/db_To_db_Parameters-log.db
> https://github.com/danap/db_to_filememorydb/blob/master/lib/plugins/DB_To_
> FileMemoryDB/transfer-param.config
>
> Testing was done with a 50K entry MariaDB table on a low
> end local network machine. Four fields in the table, Integer,
> Double, Text, and Blob. Text and Blob fields of arbitrary
> length to 10K.
>
> Resulting file database sizes for all 50K rows were in the
> range of 390MB to 1.83GB.
>
> The application is Java based and uses JDBCs.
>
> The result for the 50K file db test of SQLite was 370.184
> seconds. Is this a reasonable transfer speed, given the
> conditions noted?
>
> danap.
>
> ___
> 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] Some queries with ORDER BY and LIMIT lock DB

[Stephen Chrzanowski]

I can reproduce this issue with the SQLite3.exe under Win7.  The first
sqlite3 was pulled from a sqlite3.exe I have somewhere in my path, I then
extracted the 3.24.0 version to the Ramdrive and executed from there.  On
the first run, r:\sqlite3.exe did not exist.

R:\>sqlite3 lock_issue.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path,
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from
meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;
B2A4025D165B97BF07B4489B30E4EC6F|/local/californication.S1.E4.mp4|1536215989.63828|778FF248-0A9E-44BE-BDAE-1B413A0C55A0|401460374.0

R:\>sqlite3.exe
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

R:\>sqlite3.exe lock_issue.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path,
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from
meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;
Error: interrupted <- Pressed CTRL-C after about 5 seconds.
sqlite>


On Fri, Sep 7, 2018 at 3:20 PM, Richard Hipp  wrote:

> On 9/7/18, Firecore  wrote:
> >
> > It's easily reproduced with the sqlite3 client and db file available here
> > (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):
>
> I cannot reproduce it.  It always runs instantly for me.  I also ran
> it under valgrind with no issues reported.
>
> --
> 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] Is there permanent link to the latest SQLite amalgamation source?

[Stephen Chrzanowski]

I don't do the compiling, but I run this daily:

#!/bin/bash
cd /mnt/drobo/Development/SQLite3
wget --no-check-certificate -q -O - https://sqlite.org/download.html | grep
"win32\|amalgamation" | grep ";" | cut -f4 -d\' > sqlite.list
wget --no-check-certificate --no-clobber -i sqlite.list -o download.log -B
https://sqlite.org

I've started doing this back at the end of 2013.  If I ever need to go back
to a version, I've got 1.93TB of storage on my Drobo to handle it. :]

This effectively grabs the amalgamation source, the Win32DLL and the
sqlite3.exe file.

To conserve drive space, Dr Hipp could potentially create a hard link at
the filesystem to put a "sqlite-amalgamation.zip" on the site, but then
IMO, its not worth it, as you just don't get to know if you've already
grabbed the file or not, short of doing the download and recompile ignoring
that fact.


On Wed, Sep 5, 2018 at 7:28 AM, John Found  wrote:

> On Wed, 5 Sep 2018 10:42:04 +0200
> "E.Pasma"  wrote:
>
> > John Found wrote:
> > > In order to write an autoupdater, I need to download the latest SQLite
> amalgamation.
> > > Is there a permanent link to the subject, or the only way is to parse
> the download page
> > > for links to "sqlite-amalgamation-*.zip" or to build it from the
> fossil checkout?
> >
> > The apsw installer, setup.py, can parse the download page. This has
> likely been tested very well:
> >
> > # work out the version
> > if self.version is None:
> > write("  Getting download page to work out current SQLite
> version")
> > page=self.download("https://sqlite.org/download.html;,
> text=True, checksum=False)
> > 
> > match=re.search(r'sqlite-amalgamation-3([0-9][0-9])([0-9][0-9])([0-9][0-9])\.zip',
> page)
> > if match:
> > self.version="3.%d.%d.%d" % tuple([int(match.group(n))
> for n in range(1,4)])
> > if self.version.endswith(".0"):
> > self.version=self.version[:-len(".0")]
> > else:
> > write("Unable to determine current SQLite version.  Use
> --version=VERSION", sys.stderr)
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> Well, IMHO every solution that parses the html page in order to extract
> the links is bad solution by definition.
> I would prefer another workaround - compiling from source - much more
> reliable IMHO:
>
> echo "Downloading latest SQLite source archive..."
> { wget -q -O - https://www.sqlite.org/cgi/
> src/tarball/sqlite3.tar.gz?r=release | tar -xz 2> /dev/null; } || { echo
> >&2 "Error: Can't download SQLite sources."; exit 2;}
>
> cd ./sqlite3
>
> echo "Building the amalgamation sqlite3.c ..."
> ./configure > /dev/null 2>&1 || { echo >&2 "Error: Can't configure
> SQLite sources"; exit 4; }
> make sqlite3.c > /dev/null 2>&1 || { echo >&2 "Error: Can't make
> sqlite3.c"; exit 4; }
>
> cd ..
> cp ./sqlite3/sqlite3.c ./
> rm -rf ./sqlite3/ &
>
> Unfortunately this process is pretty slow, especially on my low-end
> machine...
> That is why I asked about permanent link that needs much less CPU cycles
> and network bandwidth.
>
> Regards
>
> --
> John Found 
> ___
> 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] Safe sqlite over remote filesystem?

[Stephen Chrzanowski]

Have you read through this document:
https://www.sqlite.org/howtocorrupt.html


On Tue, Aug 14, 2018 at 9:46 AM, Wout Mertens 
wrote:

> In the meantime I found this discussion from 2011 about sqlite on CIFS:
> http://sqlite.1065341.n5.nabble.com/How-to-make-SQLite-
> run-safely-on-CIFS-mounted-file-system-tt37415.html#none
>
> Basically using any networked filesystem as a backing store for sqlite is
> madness? I imagine not much about that changed in the last 7 years.
>
> Using the per-host-file-messaging as a communication channel to a single
> master that also exports the NFS doesn't seem that outlandish any more.
>
> On Tue, Aug 14, 2018 at 3:07 PM Wout Mertens 
> wrote:
>
> > Idle musing again, I'm pretty bad at dropping thoughts that are not
> > immediately applicable to me, sorry.
> >
> > I know that multi-writer sqlite and NFS don't play well with each other.
> >
> > However, I wonder if some constraints could be added that would make this
> > situation safe.
> >
> > My problem space is that of a shared NixOS package store between VMs,
> > which holds metadata about the available packages:
> >
> >- many writers need access to the same db
> >- their only communication channel is the POSIX filesystem that holds
> >the db
> >- they only write "seldomly", every few seconds at the fastest
> >- they do read all the time
> >- it is ok if read data is a little bit stale (10s is acceptable)
> >- it is ok if write transactions fail and can be retried
> >- it is ok if writes are slow
> >- it is never ok for data to be corrupt
> >
> > Is there a way to use safely sqlite in this situation, perhaps by using
> > extra lock files or some other additional mechanism?
> >
> > One solution I can think of involves sending all writes through a single
> > master, via files describing changes and lots of polling, but that seems
> > really outlandish.
> >
> > Wout.
> >
> ___
> 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] "Cheating" at making common tables

[Stephen Chrzanowski]

Simon;

Interesting approach that I'd forgotten about.

The tables aren't "variable".  This is a "beginning of the project,
one-time execution" thing I was hoping to get at database initialization.
Meaning, 0-byte SQLite file size kind of initialization, with not a single
line of application code has been written.  I do want the tables to exist
and be static in the database file, and my application will reference those
exact table names.

I'm just looking for a shortcut that can trim down the time to do the
initial creation when I need to make 10 or so tables that have the exact
same structure, but different meaning for the content.  So basically
something I can whip out of a text file I have laying around somewhere, or
on a wiki I keep locally, paste it into my SQL editor of choice, change the
values in one place or on one line, press execute, poof, my tables are
created.  This would be executed after I've done the
pen-and-paper-proof-of-concept-schema design.

Obviously misunderstood by all, this whole post is more about using
different methodologies to get to the desired end result.  As Keith
mentions, a script can do this in a heartbeat, which is the true, but,
going CTE (Or other) routes may spark a new direction for me, or help
clarify something I may not quite understand right about CTEs.

That said, with your post below, you've reminded me that I'm actually using
this kind of methodology for an "Options" or "Preferences" database wrapper
for some of my applications that I share between machines.  This "Options"
database is a dedicated database file containing a single table with three
fields that have the machine name, the options keyword and the options
value as fields.  When the app looks for an 'option', the app does its look
up based on the machines name.  If the machines name doesn't exist, it'll
look for the same keyword substituting the hosts name as "DEFAULT".  If
that still doesn't find anything, then it goes and relies on the hard coded
default value.  It'll suck a tiny little bit when I run across a computer
with the name "DEFAULT" that actually uses this mechanism, but, the risk is
low, and even if it happens, the app still runs, pending hard coded
defaults blows something up I don't expect.

On Sat, Aug 4, 2018 at 10:52 PM, Simon Slavin  wrote:

>
> The name of a table should not be variable.  SQL has tables with fixed
> names and variable contents, and the entire support stack is designed to
> assist this.  So move the names of your tables, which are variable, into a
> table.
>
> In the case of the above schema, the fix would be this:
>
> TABLE InfoStore (
> infoType TEXT,
> ID INTEGER,
> name TEXT);
>
> CREATE UNIQUE INDEX IS_InfoType_ID ON InfoStore (infoType, ID);
>
> Assign an IDs for a new 'SolarSystem' row by calculating
>
> * 1 + max(SELECT ID FROM InfoStore WHERE infoType='SolarSystem')*


I'd rather a random 32-bit GUID and have the application die horribly due
to an almost impossible conflict, rather than offer a race condition like
this and successfully write wrong data.

The ID can be anything as it'll never be visible to the user.  It'll only
be visible to the code that needs to know how to update the data in the
database.  As I've spent a couple hours on this email alone (The rewrites..
ohhh the rewrites), I'm starting to fade with this thinking thing, but I'll
come up with something that works and isn't a possible race condition
probably on Monday (Out of town, out of internet service range, and I'm on
a week long vacation)


> Everything goes into one big table which has a fixed name.  Magically you
> no longer need weird things like CTEs, and all access can be done by
> binding column values.
>
> Simon.
>





On Sat, Aug 4, 2018 at 10:41 PM, Keith Medcalf  wrote:

> 1) Why are you using AUTOINCREMENT?

2) The datatype CHAR does not exist.  The correct name is TEXT
> 3) Why are you creating a separate UNIQUE constraint rather than just
> specifying the column as UNIQUE?
> 4) Are you sure the text string is case sensitive for comparisons rather
> than merely case-preserving (that is, did you forget COLLATE NOCASE)?
> 5) You should not be creating duplicate UNIQUE indexes.
>
>
1) 100% guaranteed uniqueness, its an identifier my UI uses in list boxes,
combo boxes, text fields, and anything else that represents a reference to
a row within the database.  I'm limited to 32-bit Windows applications due
to the choice of **not* *spending $3k on a language and a 64-bit IDE I'm
comfortable with.  (But I will one of these days when any one of my
applications make more than $1,000/year, I'll have to.  .. so far, I'm up
to a cup of coffee worth... from a coworker who appreciated the timer I
wrote for them)  The UI components can take any signed (32-bit)-1 integer
(-1 represents NULL or unassigned as an object type and seemingly makes my
programs perform bad life choices when I reference that type of object as a
number).  My application will not ever control or change 

Re: [sqlite] "Cheating" at making common tables

[Stephen Chrzanowski]

I was right.  I got the tables done before a response.  But still would
like to know if there's a SQLite method of doing so.

My method was to use a templating application that I wrote at work. I give
it this variable declaration:

Name=Resource

I then give it this text:

CREATE TABLE [%(Name)s]( [%(Name)ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[%(Name)Name] CHAR NOT NULL, UNIQUE([%(Name)Name]));
CREATE UNIQUE INDEX [u%(Name)Name] ON [%(Name)s]([%(Name)Name]);

It then gives me this result:

CREATE TABLE [Resources]( [ResourceID] INTEGER PRIMARY KEY AUTOINCREMENT,
[ResourceName] CHAR NOT NULL, UNIQUE([ResourceName]));
CREATE UNIQUE INDEX [uResourceName] ON [Resources]([ResourceName]);

Repeat for each simple table I want, and things were done in just a couple
of minutes.  Its a very basic template engine (Automatic Search & Replace
until no keyword strings exist), but it takes big chunks of time off when
we do upgrades to our 100+ servers around the world.

BUT, if I could have the SQL version be provided a list of names, it goes
and loops through repeating whatever processes I need based on that name
for that loop, and creates the structures I'd need later on in life. ;)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "Cheating" at making common tables

[Stephen Chrzanowski]

I'm making a small database for a game, and a bunch of the tables follow
the same kind of naming convention due to normalization, like

{Name_Of_Information}s
  {Name_Of_Information}ID as Integer
  {Name_Of_Information}Name as Char

So for example:

Resources
  ResourceID as Integer
  ResourceName as Integer

(Plural name on the table name, singulars on the field names)

Would there be a way within SQLite via CTE or whatever other magic there
is, to create tables based on this structure, and setup the PK?

One of the tables that would link to a series of tables looks like:

SolarSystems
  SolarSystemID as integer
  SolarSystemName as char
  LifeFormID as integer
  EconomyTypeID as integer
  EconomyRankID as integer
  ConflictID as integer

Probably by the time someone presents their magic (again!) I'll have all
these small tables created by hand, but, going forward, it'd be something
nice to have on the tool belt.

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

Re: [sqlite] Network share & disk I/O error

[Stephen Chrzanowski]

With Richards comment, the only additional thing I'd weigh in on is what
your remote file server is doing with the physical file.  Virus scanners
can interfere with the usual operation of your program, and can cause
certain things to happen.  Because the OS you're running your software on
is told that the file is written to successfully, it doesn't necessarily
mean that the remote server has actually performed the work.  There are a
lot of new assumptions that are thrown into the mix when dealing with
remote files.

Windows machines are notorious for not allowing things to happen to files
(IE: Delete/erase/rename/etc) while something external has hold of them,
which is a pain when doing file maintenance on larger systems with lots of
remote fingers on said file.  Linux is more liberal with how it handles
file access in that you can do more intentionally damaging things to files
(Such as delete/erase/rename/etc) but the problem with that approach is
that the client can never be sure its got the most recent data available
because the filesystem on the remote end has done something different with
the file compared to what your application is expecting, even with single
user single connection access.


On Thu, Jul 12, 2018 at 12:59 PM, Robert M. Münch <
robert.mue...@saphirion.com> wrote:

> Hi,
>
> Context: Users of our app can define a working directory where sqlite
> files are stored. This can be a network share. The files are only used by
> one user at the time.
>
> We now saw two crash reports with „Database disk I/O error“ when a
> transaction was closed with „END“. Is this a known problem when having
> sqlite files on a network share?
>
> I read about the locking quirks on network share but related this to cases
> where a file could be accessed by several users at the same time and an
> application trusts the operating system to handle the locking correct. I
> don’t understand it that way that using sqlite files from a network share
> is per se impossible.
>
> Any insights on this?
>
> Viele Grüsse.
>
> --
>
> Robert M. Münch, CEO
> M: +41 79 65 11 49 6
>
> Saphirion AG
> smarter | better | faster
>
> http://www.saphirion.com
> http://www.nlpp.ch
>
> ___
> 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] Minimum Delta Time

[Stephen Chrzanowski]

Paul;

I can't use a trigger without having to create it, do the update, then
destroy the trigger.  There are form elements that can change these times
(I submit at time start at 3pm, but I actually started at 2pm).  If the
time spent is less than 5 minutes, then

David;

I like that.  Didn't think of max.

On Wed, Jul 11, 2018 at 12:21 PM, Paul Sanderson <
sandersonforens...@gmail.com> wrote:

> How about just using a trigger to check if endtime is < starttime+10 and
> updating if it fires
>
> Paul
> www.sandersonforensics.com
> SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
>
> On 11 July 2018 at 17:09, David Raymond  wrote:
>
> > For a minimum of 10 minutes it'd be something like
> >
> > update TimeEvents
> > set EndTime = max(
> > current_timestamp,
> > datetime(StartTime, '+10 minutes')
> > )
> > where
> > EventID = ?
> > and (EndTime is null or EndTime = '');
> >
> >
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Stephen Chrzanowski
> > Sent: Wednesday, July 11, 2018 10:25 AM
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Minimum Delta Time
> >
> > I've got an application that I've written that keeps track of time spent
> on
> > particular tasks.  I do many things throughout the day, going between
> > different tasks, and tasks I've already worked on for today, so this tool
> > is incredibly helpful to help justify my warming a chair.
> >
> > I'd prefer the SQL code to handle this particular procedure I'm about to
> > describe.  Obviously, in the application, I can make an additional trip
> to
> > the database to yank out the start time, add the 10 minutes, then do the
> > update, but I'd rather the database deal with this particular business
> rule
> > since the handling of the actual values is done at the database level.
> >
> > At the bottom of this email is the Delphi code and the table schema I'm
> > interested in.
> >
> > What I have is a button on the UI that toggles start/stop work times on a
> > particular task.  The Delphi Code below shows the toggling methodology.
> My
> > interest is modifying the Stop part so that at a minimum, there is a 10
> > minute delta between the start and end time.  So if I start a timer at
> > 11:00, then stop at 11:01, I want the database to update the end time to
> > 11:10.
> >
> > I suspect a SQLite CASE statement may be of help, but I'm not sure how to
> > check for the 10 minute delta then update the row with the altered time
> or
> > the real time.
> >
> >
> >
> > *Delphi Code:*
> > tbl:=db.GetTable('select EndTime from TimeEvents where EventID=? order by
> > StartTime desc',[EventID]);
> > // If this task doesn't have a previous timer, or, this task has no
> > currently running timers, make a new timer
> > // otherwise, stop the currently running timer
> > if (tbl.RowCount=0)or(tbl.FieldByName['EndTime']<>'') then begin
> >   db.ExecSQL('insert into TimeEvents (EventID) values (?)',[EventID]);
> > end else begin
> >   db.ExecSQL('update TimeEvents set EndTime=current_timestamp where
> > EventID=? and (EndTime is null or EndTime="")',[EventID]);
> > end;
> >
> > *Table Schema*
> > CREATE TABLE [TimeEvents](
> >   [EventNumber] INTEGER PRIMARY KEY AUTOINCREMENT,
> >   [EventID] integer NOT NULL REFERENCES [tEvents]([EventID]) ON DELETE
> > CASCADE,
> >   [StartTime] DATETIME(10) NOT NULL DEFAULT CURRENT_TIMESTAMP,
> >   [EndTime] DATETIME);
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Minimum Delta Time

[Stephen Chrzanowski]

I've got an application that I've written that keeps track of time spent on
particular tasks.  I do many things throughout the day, going between
different tasks, and tasks I've already worked on for today, so this tool
is incredibly helpful to help justify my warming a chair.

I'd prefer the SQL code to handle this particular procedure I'm about to
describe.  Obviously, in the application, I can make an additional trip to
the database to yank out the start time, add the 10 minutes, then do the
update, but I'd rather the database deal with this particular business rule
since the handling of the actual values is done at the database level.

At the bottom of this email is the Delphi code and the table schema I'm
interested in.

What I have is a button on the UI that toggles start/stop work times on a
particular task.  The Delphi Code below shows the toggling methodology.  My
interest is modifying the Stop part so that at a minimum, there is a 10
minute delta between the start and end time.  So if I start a timer at
11:00, then stop at 11:01, I want the database to update the end time to
11:10.

I suspect a SQLite CASE statement may be of help, but I'm not sure how to
check for the 10 minute delta then update the row with the altered time or
the real time.



*Delphi Code:*
tbl:=db.GetTable('select EndTime from TimeEvents where EventID=? order by
StartTime desc',[EventID]);
// If this task doesn't have a previous timer, or, this task has no
currently running timers, make a new timer
// otherwise, stop the currently running timer
if (tbl.RowCount=0)or(tbl.FieldByName['EndTime']<>'') then begin
  db.ExecSQL('insert into TimeEvents (EventID) values (?)',[EventID]);
end else begin
  db.ExecSQL('update TimeEvents set EndTime=current_timestamp where
EventID=? and (EndTime is null or EndTime="")',[EventID]);
end;

*Table Schema*
CREATE TABLE [TimeEvents](
  [EventNumber] INTEGER PRIMARY KEY AUTOINCREMENT,
  [EventID] integer NOT NULL REFERENCES [tEvents]([EventID]) ON DELETE
CASCADE,
  [StartTime] DATETIME(10) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [EndTime] DATETIME);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Bind Question -- Is this correct?

[Stephen Chrzanowski]

Thanks for the replies gents.  This just caught me off guard a bit and
needed a check.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bind Question -- Is this correct?

[Stephen Chrzanowski]

I'm attempting to do a simple bind for an integer.

The partial query is:

V1: *datetime(date(current_timestamp,'localtime'),'+'||:StartTime||'
hours')))/60) MinutesSince9*
V2: *datetime(date(current_timestamp,'localtime'),'+:StartTime
hours')))/60) MinutesSince9 *

I'm getting a failure with V2, and I'm assuming that binding isn't
understood when the bind is within a string.  If this assumption is
correct, is V1 the correct and safe way to bind the values?

In THIS particular case, :StartTime is an ENUM type of integers 7, 8, or 9,
and programmatically set based on a configuration option set via a pull
down menu within the application, which is sanitized before performing the
SQL statement anyways.  My concern is geared towards the future when
handling strings.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Stephen Chrzanowski]

*sigh* I wish I had this kind of homework back in the day..

On Tue, Jun 19, 2018 at 3:22 PM, Simon Slavin  wrote:

> On 19 Jun 2018, at 8:15pm, Vicente minguez gabarda 
> wrote:
>
> > hello I need a simple example of relationship between table,
> > I would like to be able to visualize the relationship code between tables
> > and then reuse it.
>
> 
>
> One day, just for a laugh, we should actually do someone's homework for
> them.
>
> 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] Newbie help

[Stephen Chrzanowski]

SQLite is a library that is built into applications.  Other than
sqlite3.exe, its not a standalone 'thing', if you will.  If your
application can't be rebuilt to directly write to a SQLite database, then
the only alternatives I can think of are
- to keep doing what you're doing, or,
- look at your monitoring application to see if it has some kind of API
that can talk to an external application or service and write the
application or service to handle the requests, or
- If your application can pipe its findings to a text file, an application
that watches for these text files changes could be written to deal with the
output of your monitoring program.

On Fri, Jun 8, 2018 at 1:26 PM,  wrote:

>
>  Hello -
>
> I've been following for a while, a lot of very intelligent
> people here, and not a lot of fuss!!! Very nice, my compliments to
> all!!
>
> I'm just learning SQLite and using it to collect alarm values from a
> monitoring system. The monitoring system can't access the SQLite file
> directly, so I've been using sqlite3.exe as the intermediate.
>
> Since I'm
> making many access to the database the monitoring program is constantly
> launching sqlite3.exe with new commands or files of commands, very wasteful
> of resources. (Windows 7)
>
> Is there a platform/program that can be run as a
> SQLite "front end" that could be launched once and take commands from
> another program and pass back results, or is there a way to do that with
> sqlite3.exe? I've looked around the web and not found anything, so
> far.
>
> Any guidance is greatly appreciated, thanks for your time!!
>
> Leland
>
> ___
> 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] sqlite.org website is now HTTPS-only

[Stephen Chrzanowski]

On Thu, Jun 7, 2018 at 4:07 PM, Warren Young  wrote:

>
> That OS is over 9 years old now.
>

Trust me, its showing its age, and I'd really like to get rid of it, but
there's a bunch of things I really don't want to migrate as some of what is
doing relies on functionality in the underlying languages that no longer
exists in newer versions of that language.  When I get a long vacation and
I'm bored of gaming, I might dig into replacing the entire VM, but until
then, work-arounds it is.  (It doesn't make outbound calls much.  Its
mostly used for DHCP services now, and keeping tabs on my kids screen time
on their computers.)


> Do you really need something in the daily scrape that you wouldn’t get
> from a Fossil clone?  There are a few things on sqlite.org that aren’t in
> the Fossil repo, but perhaps not as many as you’d guess.
>

To that, I can honestly say, I don't know.  The thing I like about the
daily scrapes is that I can grab the zip files provided in whatever state
they were uploaded as, and I archive them so 'just in case' I can go back
to a particular DLL and see whats going on.  My intention was to have the
archives come down, have a machine take note, extract, compile, and check
the DLL into my code repo.  I'd then have my source code link to the
specific version of the DLL needed.  I got as far as the 3-line bash script
to download the files. heh.  I think I might go back to that tonight
instead of YouTube..

If Fossil can get me an amalgamation to a certain version, point me where
and I'll investigate.


>
> I ask because if you build a Fossil binary by hand, you can link it to an
> up-to-date version of OpenSSL, which may solve the certificate problem.
>

I remember downloading and running Fossil and playing in the browser, but I
didn't do much with it.  Also relying on going back like 3 or 4 years.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite.org website is now HTTPS-only

[Stephen Chrzanowski]

Probably, yes.  Its running Debian Lenny.  I'm trying to locate a resource
right now to see if I can get the appropriate files, and how to keep them
updated.

On Thu, Jun 7, 2018 at 3:43 PM, Simon Slavin  wrote:

> On 7 Jun 2018, at 8:35pm, Stephen Chrzanowski  wrote:
>
> > stephen@vmLamp:~$ wget -O - https://sqlite.org/download.html
> > --15:30:59--  https://sqlite.org/download.html
> >   => `-'
> > Resolving sqlite.org... 45.33.6.223
> > Connecting to sqlite.org|45.33.6.223|:443... connected.
> > ERROR: Certificate verification error for sqlite.org: unable to get
> local
> > issuer certificate
> > To connect to sqlite.org insecurely, use `--no-check-certificate'.
> > Unable to establish SSL connection.
> >
> > In my browser, however, the cert does show up as green
>
> Your copy of wget is using a different set of Certification Authority
> certificates to those used by your browser.  Since your browser was updated
> more recently than your OS (purely a guess on my part) I'm guessing that
> the certificates used by "wget" are slightly out of date.
>
> 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] sqlite.org website is now HTTPS-only

[Stephen Chrzanowski]

I've got a script that runs daily and scrapes the download page and grabs
everything new.  The last run was this morning at midnight eastern (-4UTC)
and it successfully grabbed the list of files that could be downloaded,
however, when I run it now, it doesn't seem to want to see anything.  After
making the script a bit more verbal, wget is reporting:

stephen@vmLamp:~$ wget -O - https://sqlite.org/download.html
--15:30:59--  https://sqlite.org/download.html
   => `-'
Resolving sqlite.org... 45.33.6.223
Connecting to sqlite.org|45.33.6.223|:443... connected.
ERROR: Certificate verification error for sqlite.org: unable to get local
issuer certificate
To connect to sqlite.org insecurely, use `--no-check-certificate'.
Unable to establish SSL connection.

In my browser, however, the cert does show up as green and verified by Lets
Encrypt.  I'll investigate why wget isn't validating Lets Encrypt (I use it
at home, so I'll test against my stuff first), then report back.  For now,
I'll just add the no cert check, and I should be fine.


On Thu, Jun 7, 2018 at 2:31 PM, Richard Hipp  wrote:

> As an experiment, I have reconfigured the sqlite.org website to
> redirect all HTTP requests over to HTTPS.
>
> Let me know if this causes anybody any unnecessary grief.  It is easy
> enough to undo the setting.
>
> --
> 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] Getting call backs on WRITEs?

[Stephen Chrzanowski]

Thanks Dan.  Initial glance, looks to be something that I can tie into.

On Tue, Jun 5, 2018 at 12:14 PM, Dan Kennedy  wrote:

>
> It might be worth looking at the "sessions" module:
>
>   https://www.sqlite.org/sessionintro.html
>
> Sessions uses this to get SQLite to make the required callbacks:
>
>   https://www.sqlite.org/c3ref/preupdate_count.html
>
> Dan.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting call backs on WRITEs?

[Stephen Chrzanowski]

The problem I have is that I was wondering what kind of hooks are available
to get my application to do what I need it to do 'in a moment', so my app
can hand off information to another service at a time that is convenient.
None of these applications describe a hook for my Delphi code, but packages
that my application would hook into through an external call ranging from a
socket (Needs to be online) or make a shell call to do work.  To top it
off, most of what was proposed does not have a 'native' Win32 interface but
relies on getting a completely different language to play in my game.

Of what was proposed, they don't fit my requirements.
- The machine I'm working on will need to be always online, connected to
the internet or LAN (Socket connections).  Sometimes, if I'm remote, no
cell signal, no Wifi, I'm skunked on this requirement.
- additional 'stuff' to be installed on the client machine to go forward,
and not something else I want to additionally maintain on my main gaming
rig, my Surface tablet, or my Notebook
- requires at least 3 machines to maintain a quorum, and there may be at
times where its just one online machine and the 'repository'.
- doesn't build into my Win32 application but either needs to make shell
calls, socket connections, or some other funky jazz.
- has reliance on languages other than the two languages I'm dealing with
(Pascal and SQL)
- way overboard and way over complicated for a very simple and small
exchange of data.  I know I'm going to run into data collision, and I'm
prepared to deal with it.
- requires a 3rd party application to sneak-a-peak at my data, which
introduces problems where that 3rd party app does something I don't want or
don't expect.

I don't think I'm reinventing the wheel.  Sure, conceptually what I'm doing
has already been done, but, I've got the desire to save on tire wear and go
down the path that gets me to where I want without having to dodge
roadblocks.

On Tue, Jun 5, 2018 at 11:58 AM, Warren Young  wrote:

> On Jun 5, 2018, at 9:46 AM, Stephen Chrzanowski 
> wrote:
> >
> > I'm *DREAMING* for a way to allow for an application level notification
> > system that when a write to the database is performed from anywhere
>
> It’s usually better to say what problem you’re trying to solve rather than
> specify the form the solution must be in.  Here, I think you should be
> asking if there is a way to have a distributed SQLite database with laggy
> synchronization.
>
> And indeed, that wheel has already been invented many times:
>
> http://bedrockdb.com/
> https://github.com/rqlite/rqlite
> https://github.com/CanonicalLtd/dqlite
>
> Hopefully you can just use one of these and not reinvent this wheel yet
> again.
> ___
> 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] Getting call backs on WRITEs?

[Stephen Chrzanowski]

Using the Amalgamation DLL, I'd like to know if there is a function that is
exposed when any WRITE function to the database happens on a per connection
basis, a call back to my program (Delphi - Tokyo and Berlin) will happen
and I can act on the results.  I don't think I'll be concerned about the
kind of write (Insert vs Delete for example) as I'll need to change the
database anyways to prepare for this kind of functionality.

I'm *DREAMING* for a way to allow for an application level notification
system that when a write to the database is performed from anywhere in my
code, the application will eventually perform a dump of differences, then
transmit those differences a server (If available).  When clients 'call
home' they get the differences and apply the changes locally.  The wrapper
I use has no knowledge of SELECT vs INSERT, it just passes on the command.

The goal I want to attain is I can take my application to an 'offline' mode
of sorts, or, use it 'online' and changes are reflected in clients that are
also 'online'.  When that 'offline' machine comes back, it pushes its
changes to a server, and the other 'online' clients catch up.

If there is no such function right now, no problem.  I can probably come up
with another route instead of a call back function.

- I'm not opposed to building my own DLL if there's a compile directive
required to turn this on, just prefer not to as then I'd have to build an
infrastructure to build the newest releases. (To be honest, been kind of
looking to do that anyways, that might be the push)
- I'm fully aware that I'll have to work through sync issues (IE: I delete
a record while online, and the 'offline' machine tries to write information
about that record, etc).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random rows

[Stephen Chrzanowski]

Here's my two cents.  Don't spend it all in one place...

CREATE TABLE [RandomTable](
  [PriID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [DataCol] CHAR);

INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(1, 'a');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(2, 'b');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(3, 'c');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(4, 'd');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(5, 'e');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(6, 'f');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(7, 'g');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(8, 'h');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(9, 'i');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(10, 'j');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(11, 'k');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(12, 'l');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(13, 'm');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(14, 'n');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(15, 'o');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(16, 'p');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(17, 'q');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(18, 'r');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(19, 's');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(20, 't');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(21, 'u');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(22, 'v');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(23, 'w');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(24, 'x');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(25, 'y');
INSERT INTO [RandomTable]([PriID], [DataCol]) VALUES(26, 'z');

select random() as R,* from RandomTable order by R limit 10;

Using 3.23.1


On Fri, Jun 1, 2018 at 10:22 AM, Don V Nielsen 
wrote:

> ??
>
> SELECT * FROM table WHERE id IN (SELECT id FROM table ORDER BY RANDOM()
> LIMIT x)
>
> Maybe. It is more memory efficient then trying to sort the entire lot of
> data.
>
>
> On Thu, May 31, 2018 at 7:13 PM Torsten Curdt  wrote:
>
> > I need to get some random rows from a large(ish) table.
> >
> > The following seems to be the most straight forward - but not the
> fastest.
> >
> >   SELECT * FROM table ORDER BY random() limit 200
> >
> > Is there a faster/better approach?
> >
> > cheers,
> > Torsten
> > ___
> > 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] ALTER TABLE

[Stephen Chrzanowski]

Thinking off the cuff, there'd be a seven step process for this;

Begin Transaction
Turn off the PK/FK relationship PRAGMA constraint checks
Rename old table to a temp table via whatever means are available
Create the new table
INSERT INTO the new table
Turn on the PK/FK relationship PRAGMA constraint check
End transaction.

I've never manually run the SQLite commands to perform a rename, as I use
SQLite expert, and "F2" allows me to rename if needed (So it does all the
legwork) so I don't know if its an alter table command or you have to go
the drop/create route.  On top of that, my databases typically only contain
about a meg or two of textual data, so, how quick my F2 would work on a
larger dataset, I don't know.

SQLite does not hold a unique permanent "link" from one table to another.
So the act of renaming, and then re-provisioning the table will not change
your code or how the other tables view the new table.  Its concern is that
at the time of the SQL execution, the tables and fields exist as required.
In other words, if you do rename a table, every other table in the database
is oblivious to the change, meaning its not aware.



On Tue, May 22, 2018 at 3:34 PM, Igor Korot  wrote:

> Hi, Charles,
>
> On Tue, May 22, 2018 at 2:30 PM, Charles Leifer 
> wrote:
> > As a workaround, you can always rename the existing table, create the new
> > table with desired attributes, and do a INSERT INTO ... SELECT FROM
> > old_table. Then you can safely drop the old table.
>
> But the table_name will be different.
> Also the data in the old table might be referencing some other table.
> So this process is not really very
> straightforward...
>
> Thank you.
>
> >
> > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz 
> wrote:
> >
> >> > ALTER TABLE ADD COLUMN has existed for a long time.
> >>
> >> Yes, sorry, I mixed things up.
> >>
> >> The order of importance is imho:
> >> 1. RENAME COLUMN (shouldn't be too hard)
> >> 2. DROP COLUMN (should be a bit more comlicated but feasible)
> >> 3. MODIFY COLUMN
> >>
> >> > What kind of MODIFY COLUMN changes do you have in mind?
> >>
> >> I understand this can be difficult as there are many possible operations
> >> that might be incompatible with the data already stored in that column.
> >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE
> CASCADE
> >> action of a foreign key column. Also adding/removing a foreign key
> would be
> >> useful.
> >>
> >> Kind regards,
> >> Thomas
> >>
> >> ___
> >> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this really the best way to do this?

[Stephen Chrzanowski]

Out of curiosity, where's the guarantee that the OK won't be displayed if
an email is found?  Do all unions show up in the return result based on
where in the query they're called?

On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch  wrote:

>
> SELECT action FROM blocked WHERE email = ?
> UNION ALL
> SELECT 'OK'
> LIMIT 1;
>
>
> 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

[sqlite] OR statement in LIKE

[Stephen Chrzanowski]

I can't remember if I asked this before.  Went to google and didn't find an
answer.

Could there be a plan put in place that would including an "OR" mask
character?

The reason I ask is that I've got a user input field that searches the
required field in a database using % as the "AND" statement, but, I'd like
to expand to include using "OR" as well, but, not have to rely on my
software to regenerate a SQL statement with multiple [Field LIKE
'%UserInput%"] statements.

I'd expect the LIKE to work the same as the % mask works now, in that each
OR statement is queried against the position of that mask.  So

select * from SomeComments where Comment like 'Beginning%|%End'

would return results where the SomeComments field begins with "Beginning"
or ends with "End".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] About storage of large amounts of image data

[Stephen Chrzanowski]

In my experience, store the blob information in a table to itself and do a
1:1 or 1:Many relationship between your main data and the blob data.

The reason for this is that SQLite will read the data of your request per
row if the query is asking for information past the field to which your
blob data lives.

So for example, off the cuff:

createTable Pikchurz(id:integer; Title:integer;Data:Blob;FileSize:integer);

If you do a [ select id,Title,FileSize from Pikchurz ], SQLite is going to
read Data.

The work around to this is keep Data as the last entry in your table
schema, or, use a different table that is directly related based on PK/FK
relationships.


On Tue, May 8, 2018 at 9:08 AM, Mike Clark  wrote:

>  Hi List!
>
> I'm developing a project that deals with image files and am considering
> storing all the images in the SQLite database itself, rather than (or in
> addition to) the file system.  Since the prospective users will probably be
> dealing with hundreds of gigabytes in their use of the project, I am
> wondering if this is an effective or efficient use of SQLite -- or safe,
> because of the risk of data corruption.
>
> I know the documentation says that SQLite can handle up to 140 TB (do we
> know of anyone who is doing this?), so hundreds of gigs is clearly doable.
>
> Is it advisable, however?
>
> ​(Sent this about 2 wks ago, but it never posted to the list and no
> moderator response either).​
>
> --
> Mike Clark
> Twitter: @Cyberherbalist
> Blog: Cyberherbalist's Blog 
> -
> "Free will, though it makes evil possible, is also the only thing that
> makes possible any love or goodness or joy worth having."
> *- C. S. Lewis*
> ___
> 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] The upcoming 3.23.0 release

[Stephen Chrzanowski]

If only all tech manuals were written like that, I'd be doing much MUCH
more entertaining things with my life.  Mind you, I love my job as is

On Fri, Mar 23, 2018 at 1:14 PM, Simon Slavin  wrote:

>
> You'll get mixed up, as you know
> You're not sure what you know
> So be sure when you test, test with care and great tact
> And remember that coding's a balancing act
> Work hard so your SQL is dextrous and deft
> And make sure that your comments and docs are the best
>
> All future SQLite documentation will be written by Dr Suess.
>
> 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] How many AUTOINCREMENT tables are in your schema?

[Stephen Chrzanowski]

Every table I create that is considered a primary resource, I use
auto-increment on exactly one field.  Any table I create that uses
auto-increment is used for internal use within the database, or, an
"object" to point an item in a tStringList to the database row.  This
integer is NEVER given outright to the user to manage.

On Fri, Mar 16, 2018 at 11:37 AM, Richard Hipp  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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] iOS Watchdog and database corruption

[Stephen Chrzanowski]

That number doesn't surprise me.  At my company, one of our products is
built around iPads.  Airlines give their pilots 16-32GB iPads to bring into
the cockpit to look at maps, charts, weather info, etc.  The iPads
essentially become EFB, or, Electronic Flight Bags.  Compressed, we push
two or three gig of PDFs, images, and proprietary information of different
format structures, probably some of which is SQLite, to the devices on
initial deployment, and then update packages data going forward.  Once
received, those packages are decompressed and put into place.

A single 5gig database?  Not a big deal if the device is being used for a
very specific purpose.

On Wed, Feb 21, 2018 at 10:22 AM, Simon Slavin  wrote:

>
> > SQLITE3 version is 3.20.1. Database size is around 5 GB.
>
> You have a 5 GB database on a device which may have a 16 GB capacity ?  I
> assume you know what you're doing.
>
> 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] Clarification on Storage

[Stephen Chrzanowski]

Thanks all.  I'll consider this thread done.  With Ryan and Bills comments,
I've decided that I'll truck on as is, and not worry about special string
considerations.

Thinking about how Delphi Berlin is handling strings at the compiler level,
and how the wrapper I use is handling the strings based on Delphi 5/7
innuendos, I've kind of got a my finger on its pulse to how long I'm going
to use it, and just start rolling my own or find a different, more recent,
and free library that isn't completely overkill.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Stephen Chrzanowski]

I'm absolutely clear that the SQLite3 developers have absolutely NOTHING to
do with SQLite Expert.  I apologize if there was any indication that I was
thinking that I was asking for the devs of SQLite3 would have a direct
answer to how that 3rd party tool works.

My hope was that someone who uses SQLite Expert (Or if Bogdan is watching)
could offer a hint to whether or not my software will require special
requirements to make absolute certain that the full amount of text I'm
entering is inserted properly.

I'm just trying to understand if there is ever going to be a potential
limitation on data entry using a standard insert/update command versus my
having to convert what I'm inserting into the "X" prefixed-hex-type.
Honestly, the GUI component I'm using I believe is limited to 65k worth of
text, but that was in the Delphi 5 days, and I'm unsure if Berlin has
changed that limitation.



On Tue, Feb 20, 2018 at 11:35 AM, Richard Hipp <d...@sqlite.org> wrote:

> One thing that it is important to be clear on is that SQLite Expert is
> a 3rd-party product that is not supported nor even understood by the
> official SQLite developers.  SQLite Expert, if I am not mistaken, does
> try to do some magic under the hood to make SQLite function more like
> how the SQLite-Expert authors think it should function, rather than
> how it actually functions.  So your theory of the difference in output
> being due to shenanigans perpetrated by SQLite Expert might be
> correct, for all I know.
>
> The sqlite3.exe program, on the other hand, is an officially supported
> product, developed by and for the core SQLite developers.  SQLite3.exe
> behaves as the SQLite core developers intend SQLite to behave.
>
> All that said, I'm not exactly sure what you are asking.
>
> On 2/20/18, Stephen Chrzanowski <pontia...@gmail.com> wrote:
> > That was NOT supposed to happen. :P
> >
> > CREATE TABLE [Test](
> > [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> > [T1] TEXT,
> > [T2] BLOB_TEXT);
> >
> > insert into Test (T1,T2) values ('Test 1','Test 2');
> >
> > In SQLite Expert, it hides "Test 2" as a (blob).  I'm fine with that.
> > In SQLITE3.exe, it shows "Test 2".  I'm also fine with that.
> >
> > What I'm not entirely sure of is whether SQLite Expert is understanding
> and
> > handling the BLOB_TEXT field and just not doing the actual query to get
> the
> > text, or, if it understand if there is some kind of special handling that
> > SQLite Expert is not doing and just shows (blob).
> >
> > The reason I'm asking is that I've got an application that handles bulk
> > text inserts, and I want to make sure that I don't run into some kind of
> > stupid (My code, not SQLite) limitation that'd chop text out, or what
> have
> > you.
> >
> > On Tue, Feb 20, 2018 at 11:17 AM, Stephen Chrzanowski <
> pontia...@gmail.com>
> > wrote:
> >
> >> I'm fully aware that any kind of data can be stored in any kind of
> field.
> >> Strings can be in integer declared fields, etc.
> >>
> >> What I'm interested in finding out, without changing my code at this
> time,
> >> is if SQLITE3.EXE is handling things differently than what SQLite Expert
> >> is.
> >>
> >> In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
> >> itself "hides" the information pending certain declaration types on the
> >> tables field.  SQLITE3.EXE does not.
> >>
> >> Case in point:
> >>
> >>
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Stephen Chrzanowski]

That was NOT supposed to happen. :P

CREATE TABLE [Test](
[ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[T1] TEXT,
[T2] BLOB_TEXT);

insert into Test (T1,T2) values ('Test 1','Test 2');

In SQLite Expert, it hides "Test 2" as a (blob).  I'm fine with that.
In SQLITE3.exe, it shows "Test 2".  I'm also fine with that.

What I'm not entirely sure of is whether SQLite Expert is understanding and
handling the BLOB_TEXT field and just not doing the actual query to get the
text, or, if it understand if there is some kind of special handling that
SQLite Expert is not doing and just shows (blob).

The reason I'm asking is that I've got an application that handles bulk
text inserts, and I want to make sure that I don't run into some kind of
stupid (My code, not SQLite) limitation that'd chop text out, or what have
you.

On Tue, Feb 20, 2018 at 11:17 AM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> I'm fully aware that any kind of data can be stored in any kind of field.
> Strings can be in integer declared fields, etc.
>
> What I'm interested in finding out, without changing my code at this time,
> is if SQLITE3.EXE is handling things differently than what SQLite Expert is.
>
> In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
> itself "hides" the information pending certain declaration types on the
> tables field.  SQLITE3.EXE does not.
>
> Case in point:
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Clarification on Storage

[Stephen Chrzanowski]

I'm fully aware that any kind of data can be stored in any kind of field.
Strings can be in integer declared fields, etc.

What I'm interested in finding out, without changing my code at this time,
is if SQLITE3.EXE is handling things differently than what SQLite Expert is.

In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
itself "hides" the information pending certain declaration types on the
tables field.  SQLITE3.EXE does not.

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

Re: [sqlite] Different kind of constraint

[Stephen Chrzanowski]

That is perfectly valid, and would resolve a bunch of other headaches.  I
think I'll go that route.  Thanks.

On Sun, Feb 11, 2018 at 8:47 AM, J Decker <d3c...@gmail.com> wrote:

> Just a thought; a thumbnail/cover image seems like somehting that is often
> gotten before lots of other data, and maybe storing that alone in a table
> of covers would keep that index shorter for the entry pages...
> And then you could enforce unique parent id on the cover and not on the
> content.
>
> On Sun, Feb 11, 2018 at 2:36 AM, Stephen Chrzanowski <pontia...@gmail.com>
> wrote:
>
> > Keith> Fixed text pushed by code, no user interaction to define what goes
> > in this field will exist.  The FileType handle will be used for different
> > file type descriptors that I'll later be grouping together (IE: JPG, PNG,
> > BMP all become "Images", while MP3 or WAV would be AUDIO).  I can set the
> > field to be insensitive with no repercussions, or I change the code to
> > force upper or lower case when setting this field.  When I dump the
> random
> > files in, the software is going to insert the type of files based on
> > extension, but when I SELECT, I'll be ordering/filtering based on
> FileType
> > then FileName.
> >
> > Dan> I'll try that on a scratch database.  I've been fighting with string
> > types and how this wrapper handles things.  I almost considered switching
> > to integers for this, and have a type set or a bunch of constants in code
> > to determine the different types, however, it came down to how string
> > conversions (again) and how it stored "Cover" as some type of weird
> string,
> > but everything I used to look at that string came back looking absolutely
> > normal.  I've managed to find a work around for it, and possibly an
> > adjustment in how I use this wrapper.  I've done plenty of inserts with
> > strings, I guess not as many with doing string compare.  Makes me wonder
> > what code I have written that inserts strings isn't what I expect.
> >
> > I still might rework this part of code/DB, I don't know yet.  Only been
> at
> > it for 5 hours.  But bed time now.
> >
> > Thanks for the input.
> >
> > On Sun, Feb 11, 2018 at 2:49 AM, Keith Medcalf <kmedc...@dessus.com>
> > wrote:
> >
> > >
> > > Is FileType case sensitive?
> > >
> > >
> > > ---
> > > The fact that there's a Highway to Hell but only a Stairway to Heaven
> > says
> > > a lot about anticipated traffic volume.
> > >
> > >
> > > >-Original Message-
> > > >From: sqlite-users [mailto:sqlite-users-
> > > >boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
> > > >Sent: Saturday, 10 February, 2018 22:35
> > > >To: General Discussion of SQLite Database
> > > >Subject: [sqlite] Different kind of constraint
> > > >
> > > >I've been mucking with this for the last half hour or so.
> > > >
> > > >I have a table which is kind of in a unique situation.
> > > >
> > > >In the app I was talking about earlier, I have a table that will
> > > >contain
> > > >BLOB that has FK data referencing a magazine table.  This blob-tables
> > > >responsibility is going to hold the actual contents of the files I'm
> > > >going
> > > >to give it, so, floppy disk images, or, pictures, or what have you.
> > > >Any
> > > >file can be attached to this one particular magazine, BUT, I only
> > > >want a
> > > >single COVER image stored in this table for that one magazine, and
> > > >I'd like
> > > >SQLite to handle ensuring that this happens.
> > > >
> > > >In reference to the magazine table itself, instead of putting a BLOB
> > > >field
> > > >on the actual Magazine table, or creating a table JUST for cover
> > > >images,
> > > >I'd like to have a unique constraint on the table in that I can have
> > > >the
> > > >same MagazineID and FileType fields, EXCEPT, I can only have one
> > > >MagazineID
> > > >with one FileType='Cover' entry.
> > > >
> > > >In other words, I can never have more than a single
> > > >MagazineID=1,FileType='Cover' entry, but, I can have as many
> > > >MagazineID=1,FileType='Image' and as many MagazineID=1,FileType='D64'
> > > >rows
> > > >with their relevant blob info.
>

Re: [sqlite] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Thanks Ryan.  The problem I had was with the string "myTable" when I was
doing a Delete.  My wrapper isn't 100% Unicode friendly apparently, and I
had to do a minor and easy work around.  Even SQLite Expert wasn't
returning what I wanted from a select, so I eventually determined my insert
statement was bad.

On Sun, Feb 11, 2018 at 3:20 AM, R Smith <ryansmit...@gmail.com> wrote:

> (Apologies if this is duplicate)
>
> On 2018/02/11 3:12 AM, Stephen Chrzanowski wrote:
>
> How does one reset the sqlite_sequence values?
>>
>
> How about:
> UPDATE sqlite_sequence SET seq = 1 WHERE name = 'myTable';
>
>
>
> ___
> 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] Different kind of constraint

[Stephen Chrzanowski]

Keith> Fixed text pushed by code, no user interaction to define what goes
in this field will exist.  The FileType handle will be used for different
file type descriptors that I'll later be grouping together (IE: JPG, PNG,
BMP all become "Images", while MP3 or WAV would be AUDIO).  I can set the
field to be insensitive with no repercussions, or I change the code to
force upper or lower case when setting this field.  When I dump the random
files in, the software is going to insert the type of files based on
extension, but when I SELECT, I'll be ordering/filtering based on FileType
then FileName.

Dan> I'll try that on a scratch database.  I've been fighting with string
types and how this wrapper handles things.  I almost considered switching
to integers for this, and have a type set or a bunch of constants in code
to determine the different types, however, it came down to how string
conversions (again) and how it stored "Cover" as some type of weird string,
but everything I used to look at that string came back looking absolutely
normal.  I've managed to find a work around for it, and possibly an
adjustment in how I use this wrapper.  I've done plenty of inserts with
strings, I guess not as many with doing string compare.  Makes me wonder
what code I have written that inserts strings isn't what I expect.

I still might rework this part of code/DB, I don't know yet.  Only been at
it for 5 hours.  But bed time now.

Thanks for the input.

On Sun, Feb 11, 2018 at 2:49 AM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Is FileType case sensitive?
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
> >Sent: Saturday, 10 February, 2018 22:35
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Different kind of constraint
> >
> >I've been mucking with this for the last half hour or so.
> >
> >I have a table which is kind of in a unique situation.
> >
> >In the app I was talking about earlier, I have a table that will
> >contain
> >BLOB that has FK data referencing a magazine table.  This blob-tables
> >responsibility is going to hold the actual contents of the files I'm
> >going
> >to give it, so, floppy disk images, or, pictures, or what have you.
> >Any
> >file can be attached to this one particular magazine, BUT, I only
> >want a
> >single COVER image stored in this table for that one magazine, and
> >I'd like
> >SQLite to handle ensuring that this happens.
> >
> >In reference to the magazine table itself, instead of putting a BLOB
> >field
> >on the actual Magazine table, or creating a table JUST for cover
> >images,
> >I'd like to have a unique constraint on the table in that I can have
> >the
> >same MagazineID and FileType fields, EXCEPT, I can only have one
> >MagazineID
> >with one FileType='Cover' entry.
> >
> >In other words, I can never have more than a single
> >MagazineID=1,FileType='Cover' entry, but, I can have as many
> >MagazineID=1,FileType='Image' and as many MagazineID=1,FileType='D64'
> >rows
> >with their relevant blob info.
> >
> >What I'm doing in code right now is deleting
> >MagazineID=1,FileType='Cover'
> >before doing an insert on the table, then update the blob field with
> >the
> >image, but, if I can set this up to be an Insert Or Replace function,
> >that'd be better on me.
> >
> >The schema for the table in question is pretty simple:
> >
> >CREATE TABLE [_MagazineContent](
> >  [MagazineID] INTEGER NOT NULL REFERENCES [Magazines]([MagazineID])
> >ON
> >DELETE CASCADE,
> >  [Description] CHAR,
> >  [FileType] CHAR NOT NULL,
> >  [Content] BLOB);
> >___
> >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

[sqlite] Different kind of constraint

[Stephen Chrzanowski]

I've been mucking with this for the last half hour or so.

I have a table which is kind of in a unique situation.

In the app I was talking about earlier, I have a table that will contain
BLOB that has FK data referencing a magazine table.  This blob-tables
responsibility is going to hold the actual contents of the files I'm going
to give it, so, floppy disk images, or, pictures, or what have you.  Any
file can be attached to this one particular magazine, BUT, I only want a
single COVER image stored in this table for that one magazine, and I'd like
SQLite to handle ensuring that this happens.

In reference to the magazine table itself, instead of putting a BLOB field
on the actual Magazine table, or creating a table JUST for cover images,
I'd like to have a unique constraint on the table in that I can have the
same MagazineID and FileType fields, EXCEPT, I can only have one MagazineID
with one FileType='Cover' entry.

In other words, I can never have more than a single
MagazineID=1,FileType='Cover' entry, but, I can have as many
MagazineID=1,FileType='Image' and as many MagazineID=1,FileType='D64' rows
with their relevant blob info.

What I'm doing in code right now is deleting MagazineID=1,FileType='Cover'
before doing an insert on the table, then update the blob field with the
image, but, if I can set this up to be an Insert Or Replace function,
that'd be better on me.

The schema for the table in question is pretty simple:

CREATE TABLE [_MagazineContent](
  [MagazineID] INTEGER NOT NULL REFERENCES [Magazines]([MagazineID]) ON
DELETE CASCADE,
  [Description] CHAR,
  [FileType] CHAR NOT NULL,
  [Content] BLOB);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Half thought... SQLite Wrapper from Tim Anderson at itwriting.com

On Sat, Feb 10, 2018 at 9:06 PM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> Delphi Berlin 10.1 using his SQLite Wrapper from I don't know how long
> ago.  Its served me well in Delphi 7, but with the move to Unicode
> materials, its got a few things to iron out that I've been able to work
> around in code.  Like this threads sample. :]
>
>
> On Sat, Feb 10, 2018 at 8:51 PM, Simon Slavin <slav...@bigfraud.org>
> wrote:
>
>>
>>
>> On 11 Feb 2018, at 1:39am, Stephen Chrzanowski <pontia...@gmail.com>
>> wrote:
>>
>> > What I had before was this:
>> > db.ExecSQL('delete from sqlite_sequence where
>> > name=?',[ansistring('Magazines')]);
>>
>> Oh dear.  Don't you just hate getting lost in multiple text
>> representations ?
>>
>> C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects
>> Unicode.  Anyway, glad you got it working.
>>
>> 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] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Delphi Berlin 10.1 using his SQLite Wrapper from I don't know how long
ago.  Its served me well in Delphi 7, but with the move to Unicode
materials, its got a few things to iron out that I've been able to work
around in code.  Like this threads sample. :]


On Sat, Feb 10, 2018 at 8:51 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
>
> On 11 Feb 2018, at 1:39am, Stephen Chrzanowski <pontia...@gmail.com>
> wrote:
>
> > What I had before was this:
> > db.ExecSQL('delete from sqlite_sequence where
> > name=?',[ansistring('Magazines')]);
>
> Oh dear.  Don't you just hate getting lost in multiple text
> representations ?
>
> C++ Builder ?  Delphi ?  Check the documentation.  SQLite expects
> Unicode.  Anyway, glad you got it working.
>
> 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] Reset value of sqlite_sequence

[Stephen Chrzanowski]

Did a bit more digging, and the code I wrote is doing what its supposed to
do.  There is (what I'm calling) a bug with the string handling in the
wrapper (That I didn't write) I'm using that doesn't know how to deal with
"strings" type, but it does work fine with ansistrings, and I think the
translation between strings and ansistring seems to be messing up the
actual bind.

What I had before was this:
db.ExecSQL('delete from sqlite_sequence where
name=?',[ansistring('Magazines')]);

However, when I changed the line to this:
db.ExecSQL('delete from sqlite_sequence where name=''Magazines''');

The code did what I needed it to do.

On Sat, Feb 10, 2018 at 8:19 PM, Simon Slavin <slav...@bigfraud.org> wrote:

> On 11 Feb 2018, at 1:12am, Stephen Chrzanowski <pontia...@gmail.com>
> wrote:
>
> > How does one reset the sqlite_sequence values?
>
> See section 2.6.2 of
>
> <https://sqlite.org/fileformat2.html#seqtab>
>
> When considering how to modify that table, pay attention to the last
> paragraph of that section.
>
> 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] Reset value of sqlite_sequence

[Stephen Chrzanowski]

How does one reset the sqlite_sequence values?

I'm trying with [ delete from sqlite_sequence where name='...' ] but my app
continuously goes and keeps adding up the number.  If I go into a 3rd party
tool and delete the table, the next time my app goes and populates data,
the counter starts at 1.  Is there a flush or something I need to run?

I essentially want to truncate the table, get rid of all data, reset the
insert counter.

This is a development action only while I'm preparing some data for the
database, and not to be used as a production activity.

I suppose what I could do is just forcibly set the ID at insert, but, was
rather curious to why this delete wasn't working on the sqlite_sequence
table.

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

Re: [sqlite] GMail blocking

[Stephen Chrzanowski]

I ran into this a couple of years ago.  To add to this, the only thing to
know about is that GMail (un)helpfully warns you that this was not sent to
junk because you marked it as not-junk.

Just marking mail as "Not Junk" doesn't help.

On Wed, Feb 7, 2018 at 6:18 AM, R Smith  wrote:

> This is an FYI to GMail users only - if you don't use GMail, skip this
> thread.
>
> I've had a problem since using GMail as my delivery address for sqlite,
> that near 30%  of all forum posts got channeled to spam and the Spam folder
> wasn't automatically synced. The fix turned out to be quite
> straight-forward, in case anyone else is affected by it:
>
> In GMail, click the gear icon --> Settings --> Filters and Blocked
> addresses.
>
> -> Create a new filter.
>
> In the Filter values box, put only the sqlite address (
> sqlite-users@mailinglists.sqlite.org) in the "From:" line.
>
> -> Create Filter with this search
>
> -> In the displayed list of filter options, tick the box next to:  [ ]
> Never send it to Spam
>
> -> Create the Filter.
>
>
> That's all there is to it.
> 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] Vetting SQLite

[Stephen Chrzanowski]

I was surprised to see that statement, so, checking my system, this isn't
true.  Win7Pro-x64.  Not with that filename anyways.  Searching my system
with the "Everything" tool, [ *sqlite3.exe ] comes up with DLLs that I've
touched only.  The DLL's I've dumped into the Windows directories exist in
c:\Windows\SysWOW64 only because c:\Windows\System32 is redirected there.
On my system, there is only one sqlite3.dll with a timestamp of Aug 11,
2016, and is version 3.14.1.0 according to the Details tab.


On Mon, Feb 5, 2018 at 6:02 PM, Richard Hipp  wrote:

> On 2/5/18, Drago, William @ CSG - NARDA-MITEQ 
> wrote:
> >
> > Most of the software we use here, Microsoft and other well-known and
> > paid-for products,
>
> You know that every copy of Windows comes with SQLite preinstalled,
> right?  C:\Windows\System32\winsqlite3.dll
> --
> 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] Atoi64 bug(s)

[Stephen Chrzanowski]

According to https://www.sqlite.org/datatype3.html










*2. Storage Classes and DatatypesEach value stored in an SQLite database
(or manipulated by the database engine) has one of the following storage
classes:NULL. The value is a NULL value.INTEGER. The value is a
signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the
magnitude of the value.REAL. The value is a floating point value,
stored as an 8-byte IEEE floating point number.TEXT. The value is a
text string, stored using the database encoding (UTF-8, UTF-16BE or
UTF-16LE).BLOB. The value is a blob of data, stored exactly as it was
input.*

*Each column in an SQLite 3 database is assigned one of the following type
affinities:*

   - *TEXT*
   - *NUMERIC*
   - *INTEGER*
   - *REAL*
   - *BLOB*

*...*

*A column with NUMERIC affinity may contain values using all five storage
classes. When text data is inserted into a NUMERIC column, the storage
class of the text is converted to INTEGER or REAL (in order of preference)
if such conversion is lossless and reversible.*

*...*

My understanding of this is that since you're using strings as integer
literals, its going to use integer calculations, and not go back to using
"REAL" calculations as you'll be losing information.  According to
http://en.cppreference.com/w/cpp/language/types if the engine was able to
predetermine that your string (Which looks like an integer, not a REAL)
it'd have more than a just over (1.7 * 10^308) but you're going to lose a
lot of accuracy as the IEEE specs state that there's 15 digit accuracy, and
you're asking for 23.  You can't just stuff that many numbers in a 64-bit
number and maintain accuracy, plus, you're looking at data loss since
you're going from a 23 digit number to a maximum of 15.  So according to
documentation, this is working as intended.  IMO, the query you gave should
FAULT as its an overrun and what you're asking it to do is impossible at a
64-bit integer level.

To test, I ran this:

C:\Users\schrzanowski.YKF>sqlite3
SQLite version 3.20.0 2017-08-01 13:24:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
4807115922877859019
sqlite> SELECT CAST('12345678901234567890123.0' AS INTEGER);
4807115922877859019
sqlite> SELECT CAST('12345678901234567890123.0' AS REAL);
1.23456789012346e+22
sqlite> SELECT CAST('12345678901234567890123' AS REAL);
1.23456789012346e+22


I'm not a developer of SQLite3 by any stretch of the imagination, so I
can't say for certain whether your patch is going to be accepted or not.
In my view, I've never had to deal with numbers that large, and I suspect
this is going to end up being looked at as an edge case.

On Thu, Jan 25, 2018 at 3:36 PM, Cezary H. Noweta 
wrote:

> Hello,
>
> On 2018-01-25 19:54, petern wrote:
>
>> CREATE TABLE IF NOT EXISTS a (a INTEGER);
>>
>
> INTEGER == NUMERIC in case of column declarations.
>
> -- Note however, the constant table expression works fine...
>>
>> SELECT CAST(column1 AS INTEGER) FROM (VALUES
>> ('901'),('901 '));
>> -- "CAST(column1 AS INTEGER)"
>> -- 901
>> -- 901
>>
>
> This is due a fact that ``CAST AS INTEGER'' blindly calls ``Atoi64()'' and
> returns its result.
>
> INTEGER/FLOAT handling/recognition is a bit more complicated in other
> places, what causes that '901 ' will become 9e18.
>
> For the same reason ``CAST ('901X' AS INTEGER)'' gives INT
> 901, while ``SELECT CAST ('901X' AS
> NUMERIC);'' gives FLOAT 9e18.
>
> Due to a bit disordered treatment of values, my own patch involves many
> changes. The ``concise'' patch, which I proposed in my original post,
> eliminates: (1) (mod 2^64) bug, and (2) an erratic treatment of INTs and
> FLOATs in some (not all) places. It changes only one line and adds one, and
> does not change affinity/type system at all. (As opposed to my ``long''
> version patch).
>
> 1. IMHO (mod 2^64) bug is serious. Speculative example: my app has a
> dangerous number ``1234'' and is checking input text against it; then
> ``295147905179352827090'' -- OK, go on -- says my app; then suddenly
> ``CAST('295147905179352827090' AS INTEGER)'' ==> BOOM: 1234.
>
> 2. STRING to be INT must be reacting to ``attention'' command immediately
> (even small, chaste space at the end will bother); to be FLOAT, it can
> carousel from dusk till next dusk all the time.
>
> There was no noticeable side effects (besides a performance) in old 32bit
> INT days, however now, 10 bits of each INT can be going to a vacuum.
>
>
> -- best regards
>
> Cezary H. Noweta
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Atoi64 bug(s)

[Stephen Chrzanowski]

RE the first question, the largest positive 64-bit number is
18446744073709551615, or, 18,446,744,073,709,551,615.  Your number is
overloading that value.
12,345,678,901,234,567,890,123
vs
18,446,744,073,709,551,615

AFAIK, SQLite uses max of 64-bit integer math, not 128.


On Thu, Jan 25, 2018 at 10:04 AM, Cezary H. Noweta 
wrote:

> Hello,
>
> About year age I reported some strange behavior:
>
> 1.
>
> https://www.sqlite.org/lang_expr.html#castexpr:
>
> INTEGER: ``When casting a TEXT value to INTEGER, the longest possible
> prefix of the value that can be interpreted as an integer number is
> extracted from the TEXT value and the remainder ignored. [...] If there is
> no prefix that can be interpreted as an integer number, the result of the
> conversion is 0.''
>
> sqlite> SELECT CAST('12345678901234567890123' AS INTEGER);
> 4807115922877859019
>
> What prefix of ``12345678901234567890123'' can be interpreted as
> ``4807115922877859019''?
>
> 2.
>
> Why trailing spaces are allowed in FLOATs and not in INTEGERs? It would
> not be a problem, if INTs were 32bit. Now, a value is lost:
>
> sqlite> CREATE TABLE a (a NUMERIC);
> sqlite> INSERT INTO a VALUES ('901'),('901
> ');
> sqlite> SELECT * FROM a;
> 901
> 900
>
> 2a.
>
> https://www.sqlite.org/lang_expr.html#castexpr:
>
> NUMERIC: ``Casting a TEXT or BLOB value into NUMERIC first does a forced
> conversion into REAL but then further converts the result into INTEGER if
> and only if the conversion from REAL to INTEGER is lossless and
> reversible.''
>
> Why a cast to universal AFFINITY (NUMERIC -- designed to represent both
> INTEGERs as well as FLOATs) could be skewed into FLOAT direction loosing a
> INTEGER's LSBits? INT is not 32bit wide. Hopefully above is not true:
>
> sqlite> SELECT CAST('901' AS NUMERIC);
> 901
>
> However due to pt 2.:
>
> sqlite> SELECT CAST('901 ' AS NUMERIC);
> 900
>
> The most concise patch (without, for example ``SELECT
> CAST('901X' AS NUMERIC);'') contains only two lines:
> ==
> --- sqlite-src-322/src/util.c   2018-01-23 01:57:26.0 +0100
> +++ sqlite-src-322/src/util.c   2018-01-25 14:22:18.428460300 +0100
> @@ -625,6 +625,7 @@
>  zNum += (enc&1);
>}
>while( zNum +  while( zNumif( zNum  if( *zNum=='-' ){
>neg = 1;
> @@ -638,7 +639,7 @@
>for(i=0; [i]='0' && c<='9'; i+=incr){
>  u = u*10 + c - '0';
>}
> -  if( u>LARGEST_INT64 ){
> +  if( 19 < i || u>LARGEST_INT64 ){
>  *pNum = neg ? SMALLEST_INT64 : LARGEST_INT64;
>}else if( neg ){
>  *pNum = -(i64)u;
> ==
>
> -- best regards
>
> Cezary H. Noweta
> ___
> 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] Efficient query to count number of leaves in a DAG.

[Stephen Chrzanowski]

Playing devils advocate, there may be a purpose to those aliases
(Application code requirement, which can't be changed), and/or, the single
letter aliases are documented and understood throughout the application.

But, you're absolutely correct.  The only time I personally use single
letter variables is when I'm doing tight loops.  Like "for x:=.." and "X"
doesn't go beyond what can be read in 10pt font on a 640x480 screen.  Its a
throw back to my C64 coding days. ;)

On Wed, Jan 3, 2018 at 9:55 AM, David Raymond 
wrote:

> A couple things...
>
> I recommend using longer names than 1 letter for your aliases, what you
> save in typing you lose a couple times over again when wondering what "r"
> is or why "t" has anything to do with "nodes"
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Move to Github!!?

[Stephen Chrzanowski]

As stated, SQLite is managed by Fossil, and Fossil is managed by SQLite.
(I think it was) Dr Hipp that stated that "Fossil is eating its own dog
food".

https://www.google.ca/search?q=eating+your+own+dog+food=utf-8=utf-8=firefox-b_rd=cr=0=SENBWvCGOs6fXo7ynMAJ

https://www.investopedia.com/terms/e/eatyourowndogfood.asp


On Mon, Dec 25, 2017 at 1:24 PM, J. King  wrote:

> SQLite source is managed in a Fossil (not Git)  repository, which is
> software itself designed by Dr. Hipp and based on SQLite. GitHub would be
> an entirely inappropriate venue.
>
> SQLite is also not open source software in the conventional sense. SQLite
> is written by a small team of people, and outside contributions are far
> less common than in most open source software.
>
> On December 25, 2017 1:19:18 PM EST, Shekhar Reddy <
> shekharreddy.k...@gmail.com> wrote:
> >Hi,
> >
> >Is there any particular reason that the source is not moved to GitHub?
> >I
> >think that would reach more number of people there.
> >
> >Regards
> >Shekhar
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> ___
> 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] Modify the sqlite database with DB Browser to update a new field

[Stephen Chrzanowski]

First, be careful with modifying a database you don't control.  This can
result in unexpected results, even if you're tailing onto the end of the
table.  Some calls are like [select * from ThisTable] and the code itself
is expecting exactly the number of fields to be returned.  You may also be
creating a field that might be used somewhere else in the code as an alias,
to which could cause ambiguous errors (Meaning, the code doesn't know which
date2 you're referencing).

Second, it'll depend on what the date1 field contains.  When you say "linux
time", as that phrase sits, I can think of two or three different methods
'Nix flavors display date and time values.

Date/Time functions can be found here:
https://sqlite.org/lang_datefunc.html

If you can show us a subset of records, and what the fields contain, we can
probably come up with a decent query to do a one-time update command (Or
periodic since the code doesn't know how to deal with your new field,
although, you could put in a trigger to automatically update after the
record was inserted)


On Sat, Dec 23, 2017 at 11:34 PM, Miel Loinaz  wrote:

> Hello,
> I want to modify a sqlite database of a weather station software
> (wewx.sdb). Date
> and time are in the form of echo or linux time (field date1). I have added
> a new field (date2) where I want to display human readable date and time.
>
> In Linux console I get it running:
>
> UPDATE table1 SET date2 = datetime (date1, 'unixepoch', 'localtime');
>
> But I do not know how to modify the sqlite database with DB Browser, I'm a
> newbie. Where should I enter this code?
>
> Thanks and Merry Christmas!
> ___
> 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] values ?

[Stephen Chrzanowski]

Nifty... but... With no option for "where" or "order by", where would this
come in useful?

On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner  wrote:

> Argh.  Yes, I was on 3.8.2.  Thanks!
>
> On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp  wrote:
>
> > On 12/12/17, Mark Wagner  wrote:
> > > My reading of https://sqlite.org/syntax/select-core.html makes me
> think
> > > that I should be able to issue something like values('foo'); and get a
> > row
> > > with a single column whose value is 'foo'.  But I get a syntax error.
> > >
> > > Probably obvious to the right people but what am I missing?
> >
> > It probably means you are using an older version of SQLite.  The
> > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > --
> > 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] Seasonal syntax

[Stephen Chrzanowski]

You had to do that just beclause

On Tue, Dec 12, 2017 at 12:24 PM, Simon Slavin  wrote:

> Some SQL terminology:
>
>   Selection Clause: WHERE 
>Sort Clause: ORDER BY 
> Sublist Clause: LIMIT  OFFSET 
> Subsort Clause: GROUP BY  HAVING 
>   Santa Clause: SELECT name,hobbies,address FROM people WHERE
> behaviour='nice’
>
> Season’s greetings and best wishes to all subscribers.
>
> 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] Emulate right-join

[Stephen Chrzanowski]

Thanks all for the input.  My knowledge from nearly 20 years ago is a bit
rusty when it came to material like this.  Just adding "LEFT JOIN" instead
of just "JOIN" has my query working as I need it to.  This type of result
set isn't something I regularly perform.


On Wed, Dec 6, 2017 at 4:41 AM, Simon Slavin  wrote:

> On 6 Dec 2017, at 9:36am, Jean-Luc Hainaut 
> wrote:
>
> > Actually, the left outer join is sufficient to execute all the outer
> join operators:
> >
> > - right outer join: just swap the "from" arguments
> >
> > - full outer joins: union of left and right outer joins
>
> I never realised that.  It’s clever.  And it’s also a cheap way to
> implement FULL JOINs.
>
> Not optimized, but it simple, and changes "can’t do" to "can do".  Thanks.
>
> 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] Emulate right-join

[Stephen Chrzanowski]

I'm working on a pretty simplified event tracking system (So I stay out of
trouble of not updating time spent throughout the day), and each item that
I need to track has an associated priority with it, which ranges for any
positive integer value.  This priority can be user defined as a positive
integer. (FWIW, zero is bottom of the pack, while 1 is top priority -- Zero
priority items are displayed at the bottom of the list, and I consider them
"Undefined")

Priorities 0 to 25 can have a color and title associated with it, and this
information is stored in its own table.  Not all of the 26 priorities have
to be defined.  The PriorityLevel is related to Priority (See below) but
has no direct requirement to exist.

So the question I have is how I can build the query to emulate a right-join?

The problem encountered is that if I have an event with a priority 99, and
with the priority text not being in the table, my query does not return
items with a priority 99.

The two tables goes something along the lines of:

Events
 - EventID
 - ContactInfo
 - TicketID
 - Resolved
 - Priority (Integer)
 - CreateDate

ColorScheme
 - PriortyLevel (Integer)
 - FGColor
 - BGColor
 - PriorityText

Notes:
There is no direct PK/FK relation between Priority and PriorityLevel.
The Priority is defaulted to zero when items are created.  No trigger
exists to enforce a positive value, but my code protects that data info.
If the the Priority of an Event item is defined with a value that does not
exist in ColorScheme, color wise, it defaults to whatever PriorityLevel 0
is defined as.
The existing ColorScheme table is loaded into memory to toy with on a new
UI form, then on submit, the real table is dumped and repopulated.
If the user changes the Priority in the Events table, and there is no
PriorityLevel in ColorScheme that matches, the color scheme is
defaulted/assumed to be 0 in the application, only for coloring purposes,
but, retains the priority level.
On the submission of the color schemes, priority zero is force-created,
meaning that if the user deletes priority 0, the software will add a
default values to the ColorScheme table.

The query I'm attempting to run is something along the lines of: (Untested)
Select EventID,ContactInfo,TicketID,Priority,CreateDate from Events order
by {SomeSoftwareDefinedOrder}

What I want to do is essentially:
select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate from
Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
Events.EventID order by {SomeSoftwareDefinedOrder}

What this did in MSSQL2000 days, if I remember correctly, give me all
results in the Events table even if the relevant info isn't in ColorScheme
table.  The PriorityLevel and PriorityText would be returned as NULL.

Does anyone have any working theories on how I can get ALL results in the
Events table regardless if the Events.Priority isn't in
ColorScheme.PriorityLevel?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users