Re: [sqlite] New SQLite Forum requires Javascript?

[J Decker]

On Thu, Mar 12, 2020 at 10:32 PM Wout Mertens 
wrote:

> Hi,
>
> I normally wouldn't do this on the internet, but this mailing list
> goes deep. Not sure how that will go now that we have to use the
> forum, but here goes.
>
> JavaScript makes websites much nicer to work with, by a wide margin,
> especially those with lots of interaction like forums. Furthermore, it
> is quite hard and expensive to make sites that work well with and
> without JS.
>
> Nowadays even microcontrollers can run JS - there are no browsers that
> can't run JS, even the text based ones.
>
> So IMHO, you are asking "please use time/money to achieve this state
> that I prefer but that doesn't make a difference for the vast majority
> of people".
>
> Or am I missing something?
>

Lynx doesn't support JS...  (it's the only one I know)

 https://stackoverflow.com/questions/9353519/lynx-with-javascript

http://elinks.or.cz/  is another text based browser   (from that)
a little more searching there's https://www.brow.sh/


> Wout.
>
> On Fri, Mar 13, 2020 at 4:19 AM J.B. Nicholson 
> wrote:
> >
> > Richard Hipp wrote:
> > > 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.
> >
> > Is there a way to use this without running the Javascript?
> > ___
> > 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] Write complete pages to journal in VFS?

[J Decker]

I've recently changed the underlaying file system driver such that it
allocates one of 2 sizes of blocks (4096 or 256), based on how much is
written to the file at a time; so a write < 2048 will allocate 256 byte
blocks instead; it's not a HUGE performance hit, but I am trying to keep it
more on the efficient side (I could maybe provide an IOCTL sort of op that
forces files to only use large blocks)

I was wondering if there was a way I could make SQLite always write full
pages to the VFS (actually I found the atomic4k option after this).  It has
(xSectorSize)

int xSectorSize(sqlite3_file*file)
{
return 4096;
}

And I found  SQLITE_IOCAP_ATOMIC4K just now,and updated to use that...

int xDeviceCharacteristics(sqlite3_file*file)
{
SQLITE_IOCAP_ATOMIC4K|SQLITE_IOCAP_SAFE_APPEND|SQLITE_IOCAP_UNDELETABLE_WHEN_OPEN|SQLITE_IOCAP_POWERSAFE_OVERWRITE;
}


But, the -journal file doesn't respect that setting...


Write keyMaster.db-journal 512 at 0
Write keyMaster.db   4096 at 0
Write keyMaster.db   4096 at 4096
Write keyMaster.db-journal 512 at 0
Write keyMaster.db-journal 4 at 512
Write keyMaster.db-journal 4096 at 516
Write keyMaster.db-journal 4 at 4612
Write keyMaster.db   4096 at 0
Write keyMaster.db   4096 at 8192
Write keyMaster.db-journal 512 at 0
Write keyMaster.db-journal 4 at 512
Write keyMaster.db-journal 4096 at 516
Write keyMaster.db-journal 4 at 4612
Write keyMaster.db   4096 at 0
Write keyMaster.db   4096 at 12288
Write keyMaster.db-journal 512 at 0
Write keyMaster.db-journal 4 at 512
Write keyMaster.db-journal 4096 at 516
Write keyMaster.db-journal 4 at 4612
Write keyMaster.db   4096 at 0
Write keyMaster.db   4096 at 16384
Write keyMaster.db   4096 at 20480
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] more efficient JSON encoding: idle musing

[J Decker]

On Fri, Feb 21, 2020 at 6:03 AM Richard Hipp  wrote:

> On 2/21/20, Wout Mertens  wrote:
> > The idea is that upon storing the JSON
> > data, the JSON1 extension parses it, extracts the layouts recursively,
> > stores them when they are not known yet, and then only stores the
> > values in the binary format with the layout identifiers.
>
> I experimented with a number of similar ideas for storing JSON when I
> was first designing the JSON components for SQLite.  I was never able
> to find anything that was as fast or as compact as just storing the
> original JSON text.  But I could have overlooked something.  If you
> have example code for a mechanism that is more space efficient and/or
> faster, please share it with us.
>

text is as long as text is, and numbers, for small ranges, are also
compressed to 2 bytes (one for a separator, or opener, and 1 for the value)
gets you 0-9 (0-64 if you base64 encode it)... looking at just the data
part of JSON.  You end up with a lot of overhead from the repeated field
name definition.

I created a format
https://github.com/d3x0r/jsox#jsox--javascript-object-exchange-format that
is compatible with existing JSON, but adds the ability to specify 'class'
definitions.  There's a specification of the grammar in bnf format, and
pictures... It tracks the current parsing state, 0, initial being called
'unknown'.  If a string is found in an unknown state, followed by an
object, then that defines a class type... 'record{id,title,author,data}'
then after than, a second occurrence in the unknown state, or within an
array or object context, '[record{1342,"book","person",1}]'  would use the
existing list of names in order with the values, and build an object that
was { id:1342,title:"book",author:"person",data:1 }.   'record' could be
shortened to any single unicode character, otherwise the saving isn't so
great.
The definition of 'string' is sort of loose in JSOX, as long as there isn't
a format control character ( whitespace, ':', '{', '}', '[', ']' ) you
don't need quotes around a sequence of characters to make a string;
excepting of course starting with characters that look like a number,
and/or match a keyword...
The triggering of the mode is '{' after a string, or while collecting a
string

I also extended the number format of JSON to allow specifying ISO-8601
times as numbers (just have to special case in addition to '.'; ':' 'T'
'Z' '-' (inline and not just at start)).

other than that; if space is really a concern, maybe a zip layer?

J


> --
> 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] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[J Decker]

On Fri, Feb 14, 2020 at 7:36 AM Simon Slavin  wrote:

> On 14 Feb 2020, at 2:59pm, Andy KU7T  wrote:
>
> > Do you suggest me keeping storing TEXT (and declaring the column as TEXT
> instead of GUID) or storing and declaring BLOB (and remove HEX)?
>
> TEXT.  Makes sorting and searching easier.
>

memcmp is harder than strcmp?
and with blob I suppose you don't have 'if UTF8 or UTF16 do
different things' so should compare slightly faster?


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

[J Decker]

Standalone (library / database provider )
native

header-only (almost)

there's an entry on 'standalone programs' in wikipedia, and there's lots of
other libraries that have standalone versions, but it's not a very well
defined word.

in-process is probably closest  (in-process database (interface/provider))

does it need to be a single word? or maybe just a catchy acronym, SIS
(standalone in-process service)  [probably not that one]

On Mon, Jan 27, 2020 at 4:03 PM Peter da Silva  wrote:

> Server-free sounds good. Standalone too. Integrated maybe?
>
> On Mon, Jan 27, 2020, 17:54 Donald Shepherd 
> wrote:
>
> > On Tue, 28 Jan 2020 at 10:19 am, Richard Hipp  wrote:
> >
> > > daemon-less?
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> >
> >
> > In-process? Same concept but defining it by what it is rather than what
> it
> > isn't.
> >
> > Regards,
> > Donald Shepherd.
> >
> > > 
> > ___
> > 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] Implementing xLock/xUnlock VFS methods...

[J Decker]

On Wed, Jan 8, 2020 at 7:10 AM Dan Kennedy  wrote:

>
> On 8/1/63 20:29, J Decker wrote:
> > The documentation isn't very clear on what the intent of an xUnlock(
> > SQLITE_LOCK_NONE ) is intended to do.  Is it unlock everything? Is it the
> > same as remove a shared lock?
>
> That's right. xUnlock(fd, SQLITE_LOCK_NONE) should completely unlock the
> file. xUnlock(fd, SQLITE_LOCK_SHARED) should fall back to a SHARED lock
> from RESERVED/PENDING/EXCLUSIVE.
>
> Thank you... the second thing there confuses me...
xLock(SHARED)  xLock(SHARED) xLock(PENDING)
xUnlock( SHARED) - clear pending, and add a shared for total of 3?  or is
xUnlock always remove (this file handle's) locks?
I haven't finished implementing the first part to get a 'real' sequence...
Also; I suppose I should return SQLITE_BUSY instead of waiting on the lock
myself?  expected return values aren't covered well either.

Dan.
>
>
>
> >
> > The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by
> > xUnlock(SQLITE_LOCK_NONE)...
> >
> > sqlite.h.in
> > https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L627
> > where the constants are defined... says 'one of these are used...'
> >
> > and describing locking levels...
> > https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L708-L720
> >
> >
> > https://www.sqlite.org/c3ref/io_methods.html
> > xLock() increases the lock. xUnlock() decreases the lock. The
> > xCheckReservedLock() method checks whether any database connection,
> either
> > in this process or in some other process, is holding a RESERVED, PENDING,
> > or EXCLUSIVE lock on the file. It returns true if such a lock exists and
> > false otherwise.
> >
> > https://www.sqlite.org/lockingv3.html
> > has description of what locking states are intended to be... there's
> > 'UNLOCKED' but not LOCK_NONE
> >
> > I did find another page that had lock state transitions, and information
> > about when the busy callback would be called... but that also didn't say
> > anything about the transition to unlock states...
> >
> > There was a note around the .C code in the pager unlocking using
> LOCK_NONE,
> > but I cna't find that, it just says, see note above... and that didn't
> > really say what the intent was.
> >
> > I searched the nabble mailing list archive for 'SQLITE_LOCK_NONE' and
> found
> > 0 hits.
> > ___
> > 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] Implementing xLock/xUnlock VFS methods...

[J Decker]

The documentation isn't very clear on what the intent of an xUnlock(
SQLITE_LOCK_NONE ) is intended to do.  Is it unlock everything? Is it the
same as remove a shared lock?

The first few operations are xLock( SQLITE_LOCK_SHARED ) followed by
xUnlock(SQLITE_LOCK_NONE)...

sqlite.h.in
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L627
where the constants are defined... says 'one of these are used...'

and describing locking levels...
https://github.com/mackyle/sqlite/blob/master/src/sqlite.h.in#L708-L720


https://www.sqlite.org/c3ref/io_methods.html
xLock() increases the lock. xUnlock() decreases the lock. The
xCheckReservedLock() method checks whether any database connection, either
in this process or in some other process, is holding a RESERVED, PENDING,
or EXCLUSIVE lock on the file. It returns true if such a lock exists and
false otherwise.

https://www.sqlite.org/lockingv3.html
has description of what locking states are intended to be... there's
'UNLOCKED' but not LOCK_NONE

I did find another page that had lock state transitions, and information
about when the busy callback would be called... but that also didn't say
anything about the transition to unlock states...

There was a note around the .C code in the pager unlocking using LOCK_NONE,
but I cna't find that, it just says, see note above... and that didn't
really say what the intent was.

I searched the nabble mailing list archive for 'SQLITE_LOCK_NONE' and found
0 hits.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

[J Decker]

On Sat, Jan 4, 2020 at 2:59 AM Howard Chu  wrote:

> Keith Medcalf wrote:
> >
> > Indeed turning off memstatus leads to a 500% (from ~3s to ~0.5s)
> performance increase.
> > Changing the threading mode or the indirection level of the mutexes
> calls seems to have no significant effect.
> >
> Goes to show - publishing benchmark results without investigating why they
> are what
> they are is mostly pointless. When you suspect mutex contention is a
> significant
> factor, you should use something like mutrace to confirm your suspicion
> first.
>
> Fundamentally there ought to be no performance difference between running
> a 64-threaded
> server on a 64-threaded CPU vs 64 single-threaded processes. In practice,
> the single
> process with 64 threads ought to be slightly faster, due to less context
> switch overhead
> between threads, but if nothing else in the system is contending for CPU
> then context
> switching shouldn't even be an issue.
>

but, if the mutex lock always does a 'sched_yield' when the locking fails,
then a failure to lock isn't just a stutter in the thread, but can result
in at least 2 more context switches every time.  Probably something simpler
like a simple InterlockedExchange() call in a loop could maintain locking,
and affect performance much less; since it sounds like the actual lock
duration is really only a few clocks anyway.  Could wish there was a way to
pause execution without giving up execution context...


>
> --
>   -- Howard Chu
>   CTO, Symas Corp.   http://www.symas.com
>   Director, Highland Sun http://highlandsun.com/hyc/
>   Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> 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 to determine the column type? – virtual table?

[J Decker]

I just don't see the trouble

https://github.com/d3x0r/SACK/blob/master/src/SQLlib/sqlstub.c#L3613-L3680
for each row for each column
= sqlite3_column_type( collection->stmt, idx - 1 )
and then get the data according to the type... sqlite3_column_double  (for
instance)
 It's not very expensive to get the data type; sqlite3 will have already
prepared its internal variant structure...
if you don't know the type, then you don't know what type go get (yes, you
can, get everything as a string, but then why do you care about the type
anyway? :)  )



On Sat, Dec 14, 2019 at 10:55 AM František Kučera 
wrote:

> Dne 14. 12. 19 v 18:20 Richard Damon napsal(a):
> > What he wants is different. He takes a basically arbitrary database
> > (user provided) and an arbitrary SQL statement (also user provided) and
> > he wants to determine what type a given column will present.
>
> Yes, I am looking for something that is usually called ResultSetMetaData.
>
> Currently I have solved it by adding a new command line option, so if the
> user wants integer in e.g. "size" column, he must say it explicitly by:
> --type-cast "size" integer.
>
> I can imagine a module, that will introduce optional static typing to
> SQLite. It could provide a virtual table that will parse the query and
> return expected result set metadata. So before executing the query, I could
> do SELECT order, column_name, column_type FROM result_set_metadata WHERE
> sql = 'SELECT mount_point, pass+1000 AS pass FROM fstab'; and it would
> return:
>
> result_set_metadata:
>  ╭─┬──┬──╮
>  │ order (integer) │ column_name (string) │ column_type (string) │
>  ├─┼──┼──┤
>  │   1 │ mount_point  │ string   │
>  │   2 │ pass │ integer  │
>  ╰─┴──┴──╯
> Record count: 2
>
> to do this, it would have to:
>
>  - parse the SQL (I am not sure whether internal SQLite parser could be
> used for it or if I had to do it myself)
>
>  - check whether requested tables and columns exist and check the declared
> types
>
>  - analyze the operations done in the SELECT clause (operators, function
> calls or CASE) and derive resulting type
>
>  - return more generic type if types varies e.g. CASE that returns integer
> or decimal numbers will result in a decimal type
>
>  - return an error if the CASE mixes e.g. integers and strings which does
> not make much sense and is probably a mistake
>
> expectations and limitations:
>
>  - the table contains only values of declared types
>
>  - null is not perceived as a type itself but rather as a value of another
> type (missing integer, missing string etc.)
>
> This is IMHO feasible, but would mean probably a lot of work. However it
> might be beneficial for those who come from other relational databases and
> prefer static typing rather than dynamic.
>
> i.e. something like optional type hints and checks in dynamic programming
> languages.
>
> Franta
>
> ___
> 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] https://www.sqlite.org/draft/gencol.html Typo

[J Decker]

On Tue, Oct 29, 2019 at 5:08 PM Keith Medcalf  wrote:

>
> On Tuesday, 29 October, 2019 16:17, Warren Young 
> wrote:
>
> >On Oct 29, 2019, at 7:20 AM, Simon Slavin  wrote:
>
> >One question I had after reading the draft doc is whether an application-
> >defined SQLITE_DETERMINISTIC function can be used to compute a generated
> >column.  My immediate use case for this feature would require logic I’d
> >struggle to define in SQL, but which we already have logic for in C++.
> >Indeed, I’d be using this feature to cache the results of that C++ code
> >in the DB table, so it only needs to run when the source DB column
> >changes.
>
> But of course.
>
> sqlite> create table sintab(x real not null, sin as (sin(radians(x)))
> stored);
> sqlite> insert into sintab select value from generate_series where start=0
> and stop=90 and step=5;
> sqlite> select * from sintab;
> 0.0|0.0
> 5.0|0.0871557427476582
> 10.0|0.17364817766693
> 15.0|0.258819045102521
> 20.0|0.342020143325669
> 25.0|0.422618261740699
> 30.0|0.5
> 35.0|0.573576436351046
> 40.0|0.642787609686539
> 45.0|0.707106781186548
> 50.0|0.76603118978
> 55.0|0.819152044288992
> 60.0|0.866025403784439
> 65.0|0.90630778703665
> 70.0|0.939692620785908
> 75.0|0.965925826289068
> 80.0|0.984807753012208
> 85.0|0.996194698091746
> 90.0|1.0
>
> >> ALTER TABLE ADD COLUMN cannot be used to add GENERATED ALWAYS …
> >> STORED columns
>
> > The doc should explain why this restriction exists, given that SQLite
> > does otherwise allow ALTER TABLE ADD COLUMN.
>
> For the same reason that you cannot add a column that does not have a
> default.  Adding a column merely adds the definition of the column, not the
> data for that column to each existing record of the database.  In order to
> be able to add a stored column, you must compute the value of that column
> for each record and update every record.
>
> You can add a virtual column, however, since it is the same thing as
> adding a regular column with a default -- the default is merely the
> computation expression and the result is never stored.
>
> However, I suppose it would theoretically be possible to add a generated
> stored column and have the value of the stored column computed on retrieval
> just like for a regular column that is added where the default is computed
> at retrieval time if the stored value does not exist -- and that the value
> would only become "stored" once the record were updated (just like how a
> regular added column is treated).
>
I like the sounds of that... but I would consider going one step further
and using that in an INDEX for the table with a deterministic function...
it would be nice if the data was only stored in the index.

The application of this I was thinking of is, I have user_id's.  Each
service gets a computed value of that user_id that makes it different
between every service, and being able to reverse lookup from the computed
user_id to the real user.row would be nice.

But, I suppose indexes are updated on every insert, and a full scan of the
table when created?  So it wouldn't really be sparse... like not all users
use all services, so the computed IDs would exist for lots of relations
that can't happen.



>
> > My first use of this feature will likely have me adding a STORED column
> > to an existing table, so unless this restriction is lifted before I get
> > around to using the new feature, I’ll be doing the old table migration
> > dance.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> 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] Limit on number of columns in SQLite table

[J Decker]

On Wed, Oct 16, 2019 at 11:03 AM Mitar  wrote:

> Hi!
>
> On Wed, Oct 16, 2019 at 3:16 PM Hick Gunter  wrote:
> > 100k distinct column names? Or is that 1 repeats of 10 attributes?
>
> 100k distinct names. Like each column a different gene expression.
>
To me that sounds more like a table distinction; such that you;'d have 100k
tables with 2 columns each (their row, and their value of that gene)...
it's too bad table names cant be bound parameters.


>
>
> Mitar
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
> ___
> 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] Date time input

[J Decker]

On Wed, Oct 9, 2019 at 9:05 PM Keith Medcalf  wrote:

>
> On Tuesday, 8 October, 2019 12:40, James K. Lowden <
> jklow...@schemamania.org> wrote:
>
> >OK, but it needs a better name.  What better place than here to debate
> >that?  ;-)
>
> >What the opposite of "Lite"?
>
> It's 'ite' as in 'stalagmite' or 'meteorite' as in rock solid...

https://changelog.com/podcast/201

"
RICHARD HIPP 

How do I pronounce the name of the product? I say S-Q-L-ite, like a mineral.

So probably Sqlism (as in organism) :)


> I believe the PC euphemism is Big and Tall.
>
> SQLBAT3?
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> 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] Request to get alias of table

[J Decker]

On Sun, Aug 18, 2019 at 6:51 PM Keith Medcalf  wrote:

>
> And what would you expect the column, alias, and table alias, and origin
> table to be for the following query:
>
> select * from (select * from ( select * from (select 1, 2, 3) ta) as tb)
> tc;
>
>
Wrong function... it's SQLColAttribute...
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolattribute-function?view=sql-server-2017

'1' '2' '3' no column alias
'ta' is where the values came from but probably  'tc'

amusing though.

---
Added some logging to break it out, because there isn't any reason to have
any depth to that

SQL_DESC_TABLE_NAME(TN)
SQL_DESC_BASE_TABLE_NAME   (BN)
SQL_DESC_NAME (CN)

select 1,2,3   =  CN=['1','2','3'] TN=blank BN=blank
select * from( select 1,2,3 ) ta  TN='ta'  BN=blank
select * from (select * from( select 1,2,3 ) ta )tb   TN='tb'  BN='tb';
... etc for tc, td, ...

For MySQL ODBC.

but most importantly;  'select * from someTable ta';   table_name returns
'ta' not 'someTable'   ... base name does return 'someTable'.

in current Sqlite API there is NO way to get 'ta' from the last query.






> --
> 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 J Decker
> >Sent: Sunday, 18 August, 2019 18:14
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >And sorry for the Spam.  But, having had cake, I really do like cake,
> >and i
> >do beleive I have deliberatly forced a query to result with an object
> >of
> >group:{} and users: {} information in the group.
> >
> >But, having worked motstly with MySQL/MSSQL through ODBC, that's why
> >I
> >would expect
> >
> >select count(*) count from table alias
> >
> >to have a origin column name of 'count(*)' (not null), an alias of
> >'count'
> >, a table name of 'table' and a table alias of 'alias'.
> >
> >Although the ODBC stuff doesn't actually give you the choice to get
> >the
> >origin names of things.
> >
> >
> >
> >
> >On Sun, Aug 18, 2019 at 5:01 PM J Decker  wrote:
> >
> >> Okay let's start with, I originally had an API compatible with
> >ODBC, which
> >> the third information parameter is 'The name of the table, view,
> >alias,
> >> or synonym.'
> >>
> >>
> >>
> >https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src
> >/tpc/db2z_fncolumns.html
> >>
> >>
> >>
> >> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value
> >is
> >> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
> >> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
> >> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view,
> >alias, or
> >> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
> >> identifier. The name of the column of the specified table, view,
> >alias, or
> >> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of
> >the
> >> column that is identified by COLUMN_NAME. The DATA_TYPE is one of
> >the
> >> values in the Symbolic SQL Data Type column in the table of
> >symbolic and
> >> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not
> >NULL)A
> >> character string that represents the name of the data type that
> >corresponds
> >> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
> >>
> >>
> >___
> >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] Request to get alias of table

[J Decker]

And sorry for the Spam.  But, having had cake, I really do like cake, and i
do beleive I have deliberatly forced a query to result with an object of
group:{} and users: {} information in the group.

But, having worked motstly with MySQL/MSSQL through ODBC, that's why I
would expect

select count(*) count from table alias

to have a origin column name of 'count(*)' (not null), an alias of 'count'
, a table name of 'table' and a table alias of 'alias'.

Although the ODBC stuff doesn't actually give you the choice to get the
origin names of things.




On Sun, Aug 18, 2019 at 5:01 PM J Decker  wrote:

> Okay let's start with, I originally had an API compatible with ODBC, which
> the third information parameter is 'The name of the table, view, alias,
> or synonym.'
>
>
> https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fncolumns.html
>
>
>
> Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value is
> NULL if this table does not have catalogs.Column 2 TABLE_SCHEM
> (VARCHAR(128))The name of the schema containing TABLE_NAME.Column 3
> TABLE_NAME (VARCHAR(128) not NULL)The name of the table, view, alias, or
> synonym.Column 4 COLUMN_NAME (VARCHAR(128) not NULL)The column
> identifier. The name of the column of the specified table, view, alias, or
> synonym.Column 5 DATA_TYPE (SMALLINT not NULL)The SQL data type of the
> column that is identified by COLUMN_NAME. The DATA_TYPE is one of the
> values in the Symbolic SQL Data Type column in the table of symbolic and
> default data types for CLI.Column 6 TYPE_NAME (VARCHAR(128) not NULL)A
> character string that represents the name of the data type that corresponds
> to DATA_TYPE.Column 7 COLUMN_SIZE (INTEGER)
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Request to get alias of table

[J Decker]

Okay let's start with, I originally had an API compatible with ODBC, which
the third information parameter is 'The name of the table, view, alias, or
synonym.'

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/odbc/src/tpc/db2z_fncolumns.html



Column 1 TABLE_CAT (VARCHAR(128))The name of the catalog. The value is NULL
if this table does not have catalogs.Column 2 TABLE_SCHEM (VARCHAR(128))The
name of the schema containing TABLE_NAME.Column 3 TABLE_NAME (VARCHAR(128)
not NULL)The name of the table, view, alias, or synonym.Column 4
COLUMN_NAME (VARCHAR(128) not NULL)The column identifier. The name of the
column of the specified table, view, alias, or synonym.Column 5 DATA_TYPE
(SMALLINT not NULL)The SQL data type of the column that is identified by
COLUMN_NAME. The DATA_TYPE is one of the values in the Symbolic SQL Data
Type column in the table of symbolic and default data types for CLI.Column
6 TYPE_NAME (VARCHAR(128) not NULL)A character string that represents the
name of the data type that corresponds to DATA_TYPE.Column 7 COLUMN_SIZE
(INTEGER)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Request to get alias of table

[J Decker]

On Thu, Aug 15, 2019 at 7:13 PM Keith Medcalf  wrote:

>
> With long names turned on the columns are named differently when returned
> though the aliased subquery.  Since a dot (.) is the separator between the
> schema.table or table.column and the column name contains an embedded dot,
> you have to quote the name ... This is probably why long_column_names was
> deprecated.
>
> select count(*)count,* from ( select count(*)count,* from addresses join
> users on users.address_id=addresses.id ) address join pets pet on
> pet.user_id=address.'users.id';
>
> Row(count=1, address_count=1, address_addresses_id=1,
> address_addresses_name='there', address_users_id=1,
> address_users_address_id=1, address_users_name='bob', pet_id=1,
> pet_user_id=1, pet_name='odif')
>
> select count(*)count,* from ( select count(*)count,* from addresses join
> users on users.address_id=addresses.id ) address join pets pet on
> pet.user_id=address.[users.id];
>
> Row(count=1, address_count=1, address_addresses_id=1,
> address_addresses_name='there', address_users_id=1,
> address_users_address_id=1, address_users_name='bob', pet_id=1,
> pet_user_id=1, pet_name='odif')
>
> select count(*)count,* from ( select count(*)count,* from addresses join
> users on users.address_id=addresses.id ) address join pets pet on
> pet.user_id=address."users.id";
>
> Row(count=1, address_count=1, address_addresses_id=1,
> address_addresses_name='there', address_users_id=1,
> address_users_address_id=1, address_users_name='bob', pet_id=1,
> pet_user_id=1, pet_name='odif')
>
>
None of this removes the desire to get the alias of tables specified in
queries.
Even though queries eventually end up flat and column-aliased in the end,
during development, it's still nice to get structured records from the
database; and pragma long_column_names(whatever) changes the query
behavior, so that's not an option to use without breaking old stuff.


> --
> 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 J Decker
> >Sent: Thursday, 15 August, 2019 19:19
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf 
> >wrote:
> >
> >>
> >> The query does not work because it is defective.  The column name
> >pragma's
> >> only affect the column names that your program sees, not the names
> >used
> >> internally in the SQL statement.  It does not affect the identifier
> >use
> >> internally.  If your internal identifier use is defective, then it
> >is
> >> defective and only fixing the defect will fix it.  In this
> >particular case
> >> it can ONLY be fixed by specifying appropriate column names using
> >AS.
> >>
> >> pragma full_column_names=1;
> >pragma short_column_names=0;
> >does change how SQL deals with it.
> >
> >Without this query works
> >select count(*)count,* from ( select count(*)count,* from addresses
> >join
> >users on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=address.'id:1';
> >
> >and with, it doesn't seem to be able to be referenced... you're
> >right, I
> >did end up making it a string isntead of a colum name, quoting with
> >``
> >didn't work either.
> >
> >
> >
> >> In other words DO NOT use * unless you are absolutely certain that
> >the
> >> column names are unique.  This is not a "personal peeve", this is
> >how SQL
> >> works.  Do not use *.  Period.  End of story.  This is almost
> >always one of
> >> the very first things one learns when using SQL.  This is because
> >if
> >> something gets changed then the meaning of * gets changed.  If the
> >query
> >> optimizer decides to do things differently (in a different order),
> >the
> >> results will be different.
> >>
> >> Using * means that you are relying on happenstance rather than
> >actually
> >> writing what you mean.
> >>
> >> select * from ( select count(*)count,* from addresses join users
> >> on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=
> >> address.users.id;
> >>
> >> The subquery "select count(*) count,* from addresses join users on
> >> users.address_id=addresses.id" has duplicated column names, and
> >there is
> &

Re: [sqlite] Request to get alias of table

[J Decker]

On Thu, Aug 15, 2019 at 4:52 PM Keith Medcalf  wrote:

>
> The query does not work because it is defective.  The column name pragma's
> only affect the column names that your program sees, not the names used
> internally in the SQL statement.  It does not affect the identifier use
> internally.  If your internal identifier use is defective, then it is
> defective and only fixing the defect will fix it.  In this particular case
> it can ONLY be fixed by specifying appropriate column names using AS.
>
> pragma full_column_names=1;
pragma short_column_names=0;
does change how SQL deals with it.

Without this query works
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';

and with, it doesn't seem to be able to be referenced... you're right, I
did end up making it a string isntead of a colum name, quoting with ``
didn't work either.



> In other words DO NOT use * unless you are absolutely certain that the
> column names are unique.  This is not a "personal peeve", this is how SQL
> works.  Do not use *.  Period.  End of story.  This is almost always one of
> the very first things one learns when using SQL.  This is because if
> something gets changed then the meaning of * gets changed.  If the query
> optimizer decides to do things differently (in a different order), the
> results will be different.
>
> Using * means that you are relying on happenstance rather than actually
> writing what you mean.
>
> select * from ( select count(*)count,* from addresses join users
> on users.address_id=addresses.id ) address join pets pet on pet.user_id=
> address.users.id;
>
> The subquery "select count(*) count,* from addresses join users on
> users.address_id=addresses.id" has duplicated column names, and there is
> nothing that you can do about this except use AS to give specific distinct
> names to the columns.  You then alias this as a tableform entity called
> address.  There are two id columns in address, and there is no way that you
> can specify in the outer query which one you want other than by giving them
> unique names in the inner query by listing explicit columns and giving them
> unique aliases using AS.
>
> Like I said, if you care about column names then give the columns names,
> and if you need to distinguish between two columns with the same name, give
> them different aliases.
>

they already have different aliases, based on where they come from.  For
instance the long name option knows that count is not duplicated.
The query has lots of structure that keeps duplicated values separate...

  {
count: 1,
'address.count': 1,
'address.addresses.id': 1,
'address.addresses.name': 'there',
'address.users.id': 1,
'address.users.address_id': 1,
'address.users.name': 'bob'
  }



>
> --
> 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 J Decker
> >Sent: Thursday, 15 August, 2019 14:37
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Request to get alias of table
> >
> >>
> >>
> >>
> >> sqlite> select * from ( select count(*)count,* from addresses join
> >users
> >> on users.address_id=addresses.id ) address join pets pet on
> >pet.user_id=
> >> address.users.id;
> >> Error: no such column: address.users.id
> >>
> >>
> >>> console.log( db.do( "select count(*)count,* from ( select
> >count(*)count,*
> >from addresses join users on users.address_id=addresses.id ) address
> >join
> >pets pet on pet.user_id='address.user.id';" ) );
> >
> >
> >13:34:27.325|21281160~sql_module.cc(571):Do
> >Command[04F1ECF4:test.db]:
> >select count(*)count,* from ( select count(*)count,* from addresses
> >join
> >users on users.address_id=addres
> >ses.id ) address join pets pet on pet.user_id='address.user.id';
> >13:34:27.326|21281160~sack.cc(97503):Sqlite3 Err: (284) automatic
> >index
> >on users(address_id)
> >13:34:27.326|21281160~sack.cc(104891):0
> >13:34:27.326|21281160~sack.cc(101458):Column 0 :
> >table_name:(null)
> >origin_name:(null)   table_alias:(null)
> >13:34:27.327|21281160~sack.cc(101458):Column 1 :
> >table_name:(null)
> >origin_name:(null)   table_alias:(null)
> >13:34:27.327|21281160~sack.cc(101458):Column 2 :
> >table_name:addresses
> >origin_name:id   table_alias:addresses
> >13:34:27.327|2

Re: [sqlite] Request to get alias of table

[J Decker]

On Thu, Aug 15, 2019 at 2:53 PM Keith Medcalf  wrote:

>
> Using AS to name columns means NOT using *.  One only uses * when one does
> not care about column names (or the ordering of the result columns).
>

Sounds like a personal gospel.

This is closer to the test case
(yes it doesn't make a lot of sense without the additional wheres and other
context, but the structure is closer to what I'd have)

 select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id='address.user.id';

from a test driven development viewpoint, I want to see what's available
Then I can later refine the query.

Second though, Providing the ability to get table alias I can make queries
that reference different tables in reality but appear to have the same
results as other queries.

The query itself does a pretty good job of defining the structure of the
data itself even with *.





>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
> ___
> 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] Request to get alias of table

[J Decker]

>
>
>
> sqlite> select * from ( select count(*)count,* from addresses join users
> on users.address_id=addresses.id ) address join pets pet on pet.user_id=
> address.users.id;
> Error: no such column: address.users.id
>
>
>> console.log( db.do( "select count(*)count,* from ( select count(*)count,*
from addresses join users on users.address_id=addresses.id ) address join
pets pet on pet.user_id='address.user.id';" ) );


13:34:27.325|21281160~sql_module.cc(571):Do Command[04F1ECF4:test.db]:
select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addres
ses.id ) address join pets pet on pet.user_id='address.user.id';
13:34:27.326|21281160~sack.cc(97503):Sqlite3 Err: (284) automatic index
on users(address_id)
13:34:27.326|21281160~sack.cc(104891):0
13:34:27.326|21281160~sack.cc(101458):Column 0 : table_name:(null)
origin_name:(null)   table_alias:(null)
13:34:27.327|21281160~sack.cc(101458):Column 1 : table_name:(null)
origin_name:(null)   table_alias:(null)
13:34:27.327|21281160~sack.cc(101458):Column 2 : table_name:addresses
origin_name:id   table_alias:addresses
13:34:27.327|21281160~sack.cc(101458):Column 3 : table_name:addresses
origin_name:name   table_alias:addresses
13:34:27.327|21281160~sack.cc(101458):Column 4 : table_name:users
origin_name:id   table_alias:users
13:34:27.327|21281160~sack.cc(101458):Column 5 : table_name:users
origin_name:address_id   table_alias:users
13:34:27.328|21281160~sack.cc(101458):Column 6 : table_name:users
origin_name:name   table_alias:users
13:34:27.328|21281160~sack.cc(101458):Column 7 : table_name:pets
origin_name:id   table_alias:pet
13:34:27.328|21281160~sack.cc(101458):Column 8 : table_name:pets
origin_name:user_id   table_alias:pet
13:34:27.328|21281160~sack.cc(101458):Column 9 : table_name:pets
origin_name:name   table_alias:pet
13:34:27.328|21281160~sack.cc(104418):no data
[
  {
count: 0,
'address.count': null,
'address.addresses.id': null,
'address.addresses.name': null,
'address.users.id': null,
'address.users.address_id': null,
'address.users.name': null,
'pet.id': null,
'pet.user_id': null,
'pet.name': null
  }
]

Hmm... I don't get any values that way, I wonder what happens?

I suppose parsing that might be an option; not sure I can guarantee
databases will always pragma longname
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Request to get alias of table

[J Decker]

On Thu, Aug 15, 2019 at 12:33 PM J Decker  wrote:

>
>
> On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf 
> wrote:
>
>> After fixing the errors in the script (strings are quoted with single
>> quotes, not double quotes):
>>
> .headers on
> pragma full_column_names=1;
> pragma short_column_names=0;
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, 'there' );
> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
>> id|address_id|name|id|name|id|user_id|name
>> 1|1|bob|1|there|1|1|odif
>> sqlite> pragma full_column_names=1;
>> sqlite> pragma short_column_names=0;
>> sqlite> select * from users user join addresses address on address.id
>> =user.address_id
>>...> join pets pet on pet.user_id=user.id;
>> user.id|user.address_id|user.name|address.id|address.name|pet.id
>> |pet.user_id|pet.name
>> 1|1|bob|1|there|1|1|odif
>> sqlite>
>>
>> Note that the short_column_names and full_column_names pragma's are
>> deprecated even though highly useful.  You have to turn off
>> short_column_names (the default) in order for full_column_names to have any
>> effect.
>>
>> Note that you SHOULD be using AS to name your columns, if you care about
>> column names, and not relying on implementation details.
>>
>> Even if I do use AS, queries could be composed of expressions from other
queries, and eventually what someone makde 'unique' will be the same like
'count(*) AS count' , count, count, count



select count(*)count,* from ( select count(*)count,* from addresses join
users on users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';
1,1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null)   origin_name:(null)   table_alias:(null)
-- I would have expected 'table_name' to be (select
count(*)count,*,fromaddresses.) and alias to be 'address'
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)

Column 1 : table_name:(null)   origin_name:(null)   table_alias:(null)
-- I would have expected 'table_name' to be addresses and alias to be
'addresses' /* no alias, I'm not re-testing */
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)

Column 2 : table_name:addresses   origin_name:id   table_alias:addresses
Column 3 : table_name:addresses   origin_name:name   table_alias:addresses
Column 4 : table_name:users   origin_name:id   table_alias:users
Column 5 : table_name:users   origin_name:address_id   table_alias:users
Column 6 : table_name:users   origin_name:name   table_alias:users
Column 7 : table_name:pets   origin_name:id   table_alias:pet
Column 8 : table_name:pets   origin_name:user_id   table_alias:pet
Column 9 : table_name:pets   origin_name:name   table_alias:pet

[
  {
addresses: { id: 1, name: 'there' },
users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
pet: { id: 1, user_id: 1, name: 'odif' },
/* I Have an error apparently (or feature), that if there isn't a
table/alias name, I don't split it...
  I should at least promote it to an array... */
count: 1,
id: [ 1, 1, addresses: 1, pet: 1 ],
name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
  }
]


>
>
> --
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>>
>>
>> ___
>> 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] Request to get alias of table

[J Decker]

On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf  wrote:

> After fixing the errors in the script (strings are quoted with single
> quotes, not double quotes):
>
sqlite> .headers on
> sqlite> create table addresses( id, name );
> sqlite> create table users( id, address_id, name );
> sqlite> create table pets(id, user_id, name );
> sqlite>
> sqlite> insert into addresses(id,name) values ( 1, 'there' );
> sqlite> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> sqlite> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
> sqlite>
> sqlite> select * from users user join addresses address on address.id
> =user.address_id
>...> join pets pet on pet.user_id=user.id;
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
> sqlite> pragma full_column_names=1;
> sqlite> pragma short_column_names=0;
> sqlite> select * from users user join addresses address on address.id
> =user.address_id
>...> join pets pet on pet.user_id=user.id;
> user.id|user.address_id|user.name|address.id|address.name|pet.id
> |pet.user_id|pet.name
> 1|1|bob|1|there|1|1|odif
> sqlite>
>
> Note that the short_column_names and full_column_names pragma's are
> deprecated even though highly useful.  You have to turn off
> short_column_names (the default) in order for full_column_names to have any
> effect.
>
> Note that you SHOULD be using AS to name your columns, if you care about
> column names, and not relying on implementation details.
>
>

sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address ;
address.count|address.addresses.id|address.addresses.name|address.users.id
|address.users.address_id|address.users.name
1|1|there|1|1|bob

sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on pet.user_id=
address.users.id;
Error: no such column: address.users.id

How do I fix that query to work then?


Hmm, I don't really like this as the output though... I'd rather have the
individual names without parsing...

select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;
[
  {
'user.id': 1,
'user.address_id': 1,
'user.name': 'bob',
'address.id': 1,
'address.name': 'there',
'pet.id': 1,
'pet.user_id': 1,
'pet.name': 'odif'
  }
]


select * from ( select * from addresses join users on users.address_id=
addresses.id ) address
[
  {
'address.addresses.id': 1,
'address.addresses.name': 'there',
'address.users.id': 1,
'address.users.address_id': 1,
'address.users.name': 'bob'
  }
]



Intersting, origin_name is still the short name

Column 0 : table_name:users   origin_name:id   table_alias:user
Column 1 : table_name:users   origin_name:address_id   table_alias:user
Column 2 : table_name:users   origin_name:name   table_alias:user
Column 3 : table_name:addresses   origin_name:id   table_alias:address
Column 4 : table_name:addresses   origin_name:name   table_alias:address
Column 5 : table_name:pets   origin_name:id   table_alias:pet
Column 6 : table_name:pets   origin_name:user_id   table_alias:pet
Column 7 : table_name:pets   origin_name:name   table_alias:pet





-- 
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] Request to get alias of table

[J Decker]

I was trying a more complex query where the table name isn't a simple table
name...

select * from ( select * from addresses join user on user.address_id=
addresses.id ) address;

but that gives like really strange column names...

# sqlite3 output
id|name|id:1|address_id|name:1
1|there|1|1|bob
# my output
[ { id: 1, name: 'there', 'id:1': 1, address_id: 1, 'name:1': 'bob' } ]


so if I were to try an do like   select * from ( select * from addresses
join user on user.address_id=addresses.id ) address join pet on pet.user_id=
address.id:1;

is a syntax error ok...


select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on
pet.user_id=address.'id:1';

-- I guess that doesn't really make a 'table' to pull from

1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null)   origin_name:(null)   table_alias:(null)
Column 1 : table_name:addresses   origin_name:id   table_alias:addresses
Column 2 : table_name:addresses   origin_name:name   table_alias:addresses
Column 3 : table_name:users   origin_name:id   table_alias:users
Column 4 : table_name:users   origin_name:address_id   table_alias:users
Column 5 : table_name:users   origin_name:name   table_alias:users
Column 6 : table_name:pets   origin_name:id   table_alias:pet
Column 7 : table_name:pets   origin_name:user_id   table_alias:pet
Column 8 : table_name:pets   origin_name:name   table_alias:pet

[
  {
addresses: { id: 1, name: 'there' },
users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' },
pet: { id: 1, user_id: 1, name: 'odif' },
count: 1,
id: [ 1, 1, addresses: 1, pet: 1 ],
name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
  }
]

On Thu, Aug 15, 2019 at 11:51 AM J Decker  wrote:

> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
> So here's a script...
>
> --
> .headers on
>
> create table addresses( id, name );
> create table users( id, address_id, name );
> create table pets(id, user_id, name );
>
> insert into addresses(id,name) values ( 1, "there" );
> insert into users(id,address_id,name) values ( 1, 1, "bob" );
> insert into pets(id,user_id,name) values ( 1,1, "odif" );
>
> select * from users user join addresses address on address.id=user.address_id
> join pets pet on pet.user_id=user.id;
>
> --
> This is the output, which you can see in the one case for suer multiple
> values come back that are 'id'
>
> id|address_id|name|id|name|id|user_id|name
> 1|1|bob|1|there|1|1|odif
>
> Sqlite command line tool doesn't really provide all of the information
> available though...
>
> ---
> This is the returns of
> sqlite3_column_table_name
> sqlite3_column_origin_name
> , and the function that doesn't exist yet : sqlite3_column_table_alias
>
> data : 1,1,bob,1,there,1,1,odif
> Column 0 : table_name:users   origin_name:id   table_alias:user
> Column 1 : table_name:users   origin_name:address_id   table_alias:user
> Column 2 : table_name:users   origin_name:name   table_alias:user
> Column 3 : table_name:addresses   origin_name:id   table_alias:address
> Column 4 : table_name:addresses   origin_name:name   table_alias:address
> Column 5 : table_name:pets   origin_name:id   table_alias:pet
> Column 6 : table_name:pets   origin_name:user_id   table_alias:pet
> Column 7 : table_name:pets   origin_name:name   table_alias:pet
>
>
> Desired output:
> [
>   {
> user: { id: 1, address_id: 1, name: 'bob' },
> address: { id: 1, name: 'there' },
> pet: { id: 1, user_id: 1, name: 'odif' },
> id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
> name: [
>   'bob',
>   'there',
>   'odif',
>   user: 'bob',
>   address: 'there',
>   pet: 'odif'
> ]
>   }
> ]
>
> // in the resulting object returned to JS, I keep column names that are
> duplicated as both an array...
> id[0], id[1], id[2]  but also related to the table (alias) they come
> from.  id.name, id.pets, id.address... but conversely by table (alias)
> name... name.id, address.id, pets.id
>
> ---
>
> I would really like to get the table alias specified in the query, with an
> unpatched version of Sqlite.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Request to get alias of table

[J Decker]

I would really like to get the table alias specified in the query, with an
unpatched version of Sqlite.


So here's a script...

--
.headers on

create table addresses( id, name );
create table users( id, address_id, name );
create table pets(id, user_id, name );

insert into addresses(id,name) values ( 1, "there" );
insert into users(id,address_id,name) values ( 1, 1, "bob" );
insert into pets(id,user_id,name) values ( 1,1, "odif" );

select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;

--
This is the output, which you can see in the one case for suer multiple
values come back that are 'id'

id|address_id|name|id|name|id|user_id|name
1|1|bob|1|there|1|1|odif

Sqlite command line tool doesn't really provide all of the information
available though...

---
This is the returns of
sqlite3_column_table_name
sqlite3_column_origin_name
, and the function that doesn't exist yet : sqlite3_column_table_alias

data : 1,1,bob,1,there,1,1,odif
Column 0 : table_name:users   origin_name:id   table_alias:user
Column 1 : table_name:users   origin_name:address_id   table_alias:user
Column 2 : table_name:users   origin_name:name   table_alias:user
Column 3 : table_name:addresses   origin_name:id   table_alias:address
Column 4 : table_name:addresses   origin_name:name   table_alias:address
Column 5 : table_name:pets   origin_name:id   table_alias:pet
Column 6 : table_name:pets   origin_name:user_id   table_alias:pet
Column 7 : table_name:pets   origin_name:name   table_alias:pet


Desired output:
[
  {
user: { id: 1, address_id: 1, name: 'bob' },
address: { id: 1, name: 'there' },
pet: { id: 1, user_id: 1, name: 'odif' },
id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ],
name: [
  'bob',
  'there',
  'odif',
  user: 'bob',
  address: 'there',
  pet: 'odif'
]
  }
]

// in the resulting object returned to JS, I keep column names that are
duplicated as both an array...
id[0], id[1], id[2]  but also related to the table (alias) they come from.
id.name, id.pets, id.address... but conversely by table (alias) name...
name.id, address.id, pets.id

---

I would really like to get the table alias specified in the query, with an
unpatched version of Sqlite.
___
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)

[J Decker]

On Mon, Aug 12, 2019 at 6:54 AM Tim Streater  wrote:

> On 12 Aug 2019, at 14:30, J Decker  wrote:
>
> > On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin 
> wrote:
> >
> >> On 12 Aug 2019, at 1:27pm, Tim Streater  wrote:
> >>
> >> > I don't expect to do that with SQL. My "seconds since the epoch" is
> >> based on converting any particular time to GMT and storing that. That
> >> number is then converted to a date/time with TZ info for display.
>
> > If the timezone is stored, then the time is all UTC and easily sortable.
> >  A sub-order of timeone within a sepcific time sequence ends up happening
> > *shrug*
>
> 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.




> > But then, I'm assuming the time would just be ISO8601; since SQLite
> > datetime functions take that as an input already.
>
> I'm a user; I don't want my times displayed as ISO8601. That's why we have
> date/time control panels so the user gets to choose how those are displayed.
>
> Then strftime it.
I don't want to see PK/FK identifiers either.


>
> --
> Cheers  --  Tim
> ___
> 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)

[J Decker]

On Mon, Aug 12, 2019 at 5:42 AM Simon Slavin  wrote:

> On 12 Aug 2019, at 1:27pm, Tim Streater  wrote:
>
> > I don't expect to do that with SQL. My "seconds since the epoch" is
> based on converting any particular time to GMT and storing that. That
> number is then converted to a date/time with TZ info for display.
>
>
If the timezone is stored, then the time is all UTC and easily sortable.
 A sub-order of timeone within a sepcific time sequence ends up happening
*shrug*

But then, I'm assuming the time would just be ISO8601; since SQLite
datetime functions take that as an input already.



> I'm with Tim.  Storing the time zone with the timestamp is a different
> matter.  It leads to problems with sorting and searching.  We can discuss
> it, but it doesn't belong in this thread.
> ___
> 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] Hidden data

[J Decker]

On Sun, Aug 4, 2019 at 9:40 AM Simon Slavin  wrote:

> On 4 Aug 2019, at 5:33pm, bitwyse  wrote:
>
> > Could it be hidden in a Blob?
> > What program (command?) would diplay it and allow deleting it?
> > (VACUUMing doesn't remove it.)
>
> There's only one program guaranteed to get at every element of a SQLite
> database file: the sqlite3 shell tool written and supported by the team
> which developed SQLite.  You can download it from the SQLite web site
> ("precompiled binaries") and see documentation here:
>
Except the end of those that have a \0 in them.


>
> 
>
> Have a poke around in the database file using it.  I'd probably start with
> the '.schema' command and work from there.
>
> Once you've found what you're looking for you might be able to switch to
> some other program for everyday access.
> ___
> 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] readfile() enhancement request

[J Decker]

On Fri, May 17, 2019 at 9:34 AM Tony Papadimitriou  wrote:

> It’s quite often (for me, at least) the case I need to do something like
> this from the command line:
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text
> copied from some other app’)
>
> type con | sqlite3 my.db
insert into t values(‘simple field’,’multi-line text copied from some other
app’)
 ctrl-z

cat | sqlite3 my.db
insert into t values(‘simple field’,’multi-line text copied from some other
app’)
ctrl-D



> The problem is the multi-line text cannot be copy-pasted directly into the
> command line as the first newline will terminate the command.  So, I’ve
> been using readline() like so:
>
> First, save the copied text into some arbitrary file (e.g., xxx), and then
> do
>
> >sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘xxx’))
>
> What would be much easier is for readfile to accept standard input when
> provided with no empty name,
> i.e., readfile(‘’).
>
> Then, it would read standard input (i.e., wait for me to type/paste my
> text) until CTRL-Z/D.
>
> ---
> >sqlite3.exe my.db “insert into t values(‘simple field’,readfile(‘’))
> My text typed/pasted here
> ...
> ...
> CTRL-Z/D
> ---
>
> This could also be used to capture directly into the db the output of some
> other program without first having to save it to a file.
>
> I’m not sure how multiple readfile(‘’) on the same command should be
> handled, either not allowed, or they all get a copy of the same input.
>
> 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] Help with sqlite3_value_text

[J Decker]

On Sun, Apr 14, 2019 at 5:40 AM x  wrote:

> On second thoughts JD, can’t use strlen or sqlite3_value_bytes in case
> values(1) contains more than a single unicode character. This looks OK.
>
> Bytes are what you need though; it doesn't matter how big the buffer is,
as long as you have all of it.

As long as you use _value_bytes after _text you're fine... so if any
conversion did take place the value will be right of the last returned
string type.


> # define CHARLEN(x) !(x & 128) ? 1 : (x & 16 ? 4 : (x & 32 ? 3 : 2))
>
> char *c = (char *)sqlite3_value_text(values[0]);
> char *Sep = (char *)sqlite3_value_text(values[1]);
> int Count=0, Len, SepLen = CHARLEN(*Sep);
>
> while (*c)
> {
>if ((Len = CHARLEN(*c)) == SepLen && memcmp(c, Sep, Len)==0)
> Count++; // at start of Sep
>c += Len;
> }
> sqlite3_result_int(ctx, Count);
>
> ___
> 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 sqlite3_value_text

[J Decker]

On Sat, Apr 13, 2019 at 12:04 PM x  wrote:

> This seems to work OK as a sqlite function.
>
>
>
> // assume values[0] & [1] are supplied and not null
>
> // find Count of values[1] in values[0]
>
>
>
> char *c = (char *)sqlite3_value_text(values[0]);
>
> char *Sep = (char *)sqlite3_value_text(values[1]);
>
> int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);
>
you could use sqlite3_value_bytes(values[1]); instead.

>
>
>
> while (*c)
>
> {
>
>
>
>Byte1 = (*c) >> 4;
>
>if ((Byte1 & 8) == 0) NrBytes = 1;
>
>else if (Byte1 & 1) NrBytes = 4;
>
>else if (Byte1 & 2) NrBytes = 3;
>
>else NrBytes = 2; // (Byte1 & 4) == 4
>
>
>
>if (NrBytes == NrSepBytes && memcmp(c, Sep, NrBytes) == 0) Count++;
> // at first byte of Sep
>
>c += NrBytes;
>
> }
>
> sqlite3_result_int(ctx, Count);
>
>
>
> 
> From: sqlite-users  on
> behalf of Scott Robison 
> Sent: Friday, April 12, 2019 8:40:19 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Help with sqlite3_value_text
>
> On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:
>
> >
> > Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> > variable-length encoding.  An actual "unicode character" is (at this
> > present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
> >
>
> That is some impressive compression! :)
>
> Regardless, even if you use UCS-4, you still have the issue of combining
> characters. Unicode is complex as had been observed.
> ___
> 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] Help with sqlite3_value_text

[J Decker]

http://utf8everywhere.org/


On Fri, Apr 12, 2019 at 7:51 AM x  wrote:

> I’m still confused by utf strings. For simplicity, suppose I set up an
> sqlite function that takes a single string parameter and I want to scan the
> string to count the number of occurrences of a certain character . If I
> knew the string was made up entirely of ascii chars I’d do this
>
> char *c = _value_text(0)[0];
> int count=0;
> while (*c) if (*c++ == SearchChar) count++;
>
> How do I do the same thing if the string param is a utf-8 or utf-16 string
> and the SearchChar is a Unicode character?
>

You'd decide on the common operating mode of your system for 1... there are
of course middleware libraries that need to cater to both and consume
databases instead of just writing it.


>
> I’m confused by the fact that Unicode characters are not a fixed number of
> bytes so if I do this e.g.
>
> wchar_t *c = (wchar_t*) sqlite3_value_text(0);
>
>
https://www.sqlite.org/c3ref/value_blob.html

const void *sqlite3_value_text16(

returns wchar data, converting if nessecary from utf8 if it was stored as
utf8.

value_text only returns utf8 (char* really, but suffices for storage and
retreival of utf8 phrases)

for utf8 it would just be matter of matching 1,2,3, or 4 bytes (depending
on the codepoint you were matching).



does this mean a complete temporary copy of the value of
> sqlite3_value_text(0) has to be constructed by the compiler such that all
> characters of the newly constructed string are fixed width? If so, I’m just
> wanting to check if there’s a way  of avoiding this overhead.
>
>
If it is converted, a temporary value is allocated by the library (at
runtime, well beyond the compiler), ...


> ___
> 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] ISO8601 vs Numeric Timestamp for Date Storage

[J Decker]

From a JS point of view new Date( ISOString )and .toISOString() are quick
and available
ISO format parsing is NOT that hard it's just a minor varient of
parsing floats.  (maybe the conversion from parts into numeric?)
Haven't bothered to benchmark it.
Date Diffs easily avaialble.

On Wed, Feb 6, 2019 at 1:59 PM Jens Alfke  wrote:

>
>
> > On Feb 6, 2019, at 11:55 AM, Ben Asher  wrote:
> >
> > Hi there! We're having a debate at my company about date storage in
> SQLite.
> > SQLite has builtin support for ISO8601 in its date functions, so some
> folks
> > have started storing dates as ISO8601 SQLite-compatible date strings. Are
> > there pitfalls to storing dates this way compared to a unix timestamp?
>
> Date-string parsing can be surprisingly expensive. I’ve seen it as a
> hot-spot when profiling a program of mine that analyzed some XML-based data
> sets, and I’ve also seen it slow down CouchDB map functions.
>
> Date strings are many times larger than the equivalent numeric timestamps.
>
> On the plus side, they’re much more readable if someone has to look at the
> raw data in the database.
>
> —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] Displaying hierarchical structure

[J Decker]

Probably just the order by just do 'order by parent_id,name' to group
folders together and then alphabetcal?


(was expanding it... I would also start with where parent_id=0 )
with recursive foo (id, parent_id, name) as (
   select * from folders where parent_id = 0
   union all
   select folders.id, folders.parent_id, foo.name || '\' || folders.name from
folders inner join foo on folders.parent_id = foo.id
)

select * from foo order by parent_id,name;

On Tue, Jan 29, 2019 at 10:43 AM David Raymond 
wrote:

> Yup, you're right. Will have to think some more on that then.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Bart Smissaert
> Sent: Tuesday, January 29, 2019 1:06 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Displaying hierarchical structure
>
> Had another look at your solution and not sure now it is quite OK.
> The output comes out fine, but that seems to be due to the lucky fact that
> it just sort OK by folder. If I change the folder names then the output is
> not OK.
>
> RBS
>
> On Tue, Jan 29, 2019 at 5:09 PM David Raymond 
> wrote:
>
> > I may be missing something, but with recursive CTE's, why do you need the
> > second table? I'm assuming it's just to speed things up once the counts
> get
> > large?
> >
> >
> > sqlite> create table folders (id integer primary key, parent_id int
> > references folders, name text not null collate nocase, check (not
> > (parent_id is null and id != 1)));
> > QUERY PLAN
> > `--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)
> >
> >
> > sqlite> insert into folders values (1, null, 'Folder1'), (2, 1,
> > 'Folder2'), (3, 1, 'Folder3'), (4, 1, 'Folder4'), (5, 2, 'Folder5'), (6,
> 2,
> > 'Folder6');
> > QUERY PLAN
> > |--SCAN 6 CONSTANT ROWS
> > `--SCAN TABLE folders
> >
> >
> > Gonna use backslashes here rather than spaces. So admitidly not exactly
> > what you were looking for. But some playing around with string functions
> > should be able to get there.
> >
> >
> > sqlite> with recursive foo (id, parent_id, name) as (select * from
> folders
> > where id = 1 union all select folders.id, folders.parent_id, foo.name ||
> > '\' || folders.name from folders inner join foo on folders.parent_id =
> > foo.id) select * from foo order by name;
> > QUERY PLAN
> > |--CO-ROUTINE 2
> > |  |--SETUP
> > |  |  `--SEARCH TABLE folders USING INTEGER PRIMARY KEY (rowid=?)
> > |  `--RECURSIVE STEP
> > | |--SCAN TABLE foo
> > | `--SEARCH TABLE folders USING AUTOMATIC COVERING INDEX
> (parent_id=?)
> > |--SCAN SUBQUERY 2
> > `--USE TEMP B-TREE FOR ORDER BY
> > id|parent_id|name
> > 1||Folder1
> > 2|1|Folder1\Folder2
> > 5|2|Folder1\Folder2\Folder5
> > 6|2|Folder1\Folder2\Folder6
> > 3|1|Folder1\Folder3
> > 4|1|Folder1\Folder4
> >
> >
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Bart Smissaert
> > Sent: Tuesday, January 29, 2019 10:52 AM
> > To: General Discussion of SQLite Database
> > Subject: [sqlite] Displaying hierarchical structure
> >
> > Working on an Android app and part of that is storing SQL in a virtual
> > folder system in SQLite. For this I want to use a so-called closure table
> > as explained nicely here:
> >
> > http://technobytz.com/closure_table_store_hierarchical_data.html
> >
> > I have a table holder the folder details:
> >
> > ID PARENT_ID Folder
> >
> > -
> > 1  0   Folder1
> > 2  1   Folder2
> > 3  1   Folder3
> > 4  1   Folder4
> > 5  2   Folder5
> > 6  2   Folder6
> >
> >
> > And then the closure table:
> >
> > PARENT_ID CHILD_ID DEPTH
> >
> > ---
> > 1   10
> > 2   20
> > 3   30
> > 4   40
> > 5   50
> > 6   60
> > 1   21
> > 1   31
> > 1   41
> > 2   51
> > 1   52
> > 2   61
> > 1   62
> >
> > What should the SQL be to display the folders like this:
> >
> > FolderPARENT_ID
> > Folder1   0
> > Folder2   1
> > Folder5   2
> > Folder6   2
> > Folder3   1
> > Folder4   1
> >
> >
> > RBS
> > ___
> > 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 

Re: [sqlite] Feat Req Get Aliased Table Name.

[J Decker]

https://github.com/mackyle/sqlite/compare/master...d3x0r:TableAliasColumnInfo#diff-ba6bba0411f943cb54c02ee6f03e9a72R1666

Someone at smoe point mentioned "I know know... that's like grabbing memory
from some place untraced right and "

All the other META INFO comes from the same thing.

pTab = pTabList->a[j].pTab;

zOrigCol = pTab->aCol[iCol].zName;
zOrigTab = pTab->zName;

(added; and in the same routine)
zOrigTabAlias =
pTabList->a[j].zAlias ? pTabList->a[j].zAlias : pTabList->a[j].zName;


https://github.com/d3x0r/sqlite/blob/TableAliasColumnInfo/src/select.c#L1723

So basically all that meta info is tied to the lifetime of the preparated
query... and if any of what was already implemented are valid, the new one
is valid, and if they are invalid, the new is similarly invalid; but it'll
never be a case that part of that works and the other doesn't.

Can of course generate a patch; but the first link is the patch, visually
highlighted.
It's not a lot to hand implement, it's really just saving that info

Most of the changes are behind SQLITE_ENABLE_COLUMN_METADATA which controls
the other metadata functions in the group.
That group of functions has an array of entries indexed by

https://github.com/mackyle/sqlite/compare/master...d3x0r:TableAliasColumnInfo?expand=1#diff-6f14e51175412ede90bf483cfca13d6dR143
#define COLNAME_NAME 0
#define COLNAME_DECLTYPE 1 #define COLNAME_DECLTYPE 1
#define COLNAME_DATABASE 2 #define COLNAME_DATABASE 2
#define COLNAME_TABLE 3 #define COLNAME_TABLE 3
#define COLNAME_COLUMN 4 #define COLNAME_COLUMN 4
#define COLNAME_TABLE_ALIAS 5
#ifdef SQLITE_ENABLE_COLUMN_METADATA #ifdef SQLITE_ENABLE_COLUMN_METADATA
# define COLNAME_N 5 /* Number of COLNAME_xxx symbols */ # define COLNAME_N
6 /* Number of COLNAME_xxx symbols */

Which "COLANME_N define automatically extends the array to have the
appropriate number of entires, then there's a couple generic accessors to
store/get from that using the other defined values.

On Sun, Mar 18, 2018 at 9:07 PM J Decker  wrote:

> https://sqlite.org/c3ref/column_database_name.html
>
> Source Of Data In A Query Result
> The names returned are the original un-aliased names of the database,
> table, and column.
>
>
> Okay.  and there's...
>
> https://sqlite.org/c3ref/column_name.html
>
> These routines return the name assigned to a particular column in the
> result set of a SELECT <https://sqlite.org/lang_select.html> statement.
>
> Which is aliased column names (if aliased)
>
> But there's no way to get the source table aliased.  And I don't think
> database ever gets a chance to be aliased?
>
> 
> SELECT a, sum(b), max(c) FROM t1 m GROUP BY a
>
> There's no C API to get 'm' from the above.
>
> Updated my patch to add the above through
>
>
> +const char *sqlite3_column_table_alias_name(sqlite3_stmt*,int);
> +const void *sqlite3_column_table_alias_name16(sqlite3_stmt*,int);
>
> which is added with the top mentionend function groups behind
> SQLITE_ENABLE_COLUMN_METADATA
>
> table_alias_info.fossil.patch
> https://drive.google.com/open?id=1c24qvtvS57ASJF5RfZxLJSsgI2FhOVk1
>
> Visual diff.
> https://github.com/d3x0r/sqlite3/compare/AddTableAliasForColumn?expand=1
>
>
> It's not very large.  It just has to allocate one more element in the
> array of column info allocated by METADATA flag and set it when it sets
> the rest, and expose it with an API call.
>
>
> Even if it's just taken as inspiration, please add something to expose the
> alias.
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building SQLite DLL with Visual Studio 2015

[J Decker]

On Mon, Jan 21, 2019 at 2:16 AM John Smith  wrote:

> Hi,
>
>
> I need to build a data-layer DLL for a large project.
>
> My project is 64-bit built with Visual-Studio 2015.
>
> I want to ask about  what would be the preferred way to build SQLite:
>
> 1. Build SQLite code as a separate DLL and use it from my data-layer DLL,
>
> 2. Use the ready-built binary of 64-bit SQLite DLL for Windows
> (sqlite-dll-win64-x64-326.zip),
>
> 3. Or, build my data-layer code with SQLite code as a single DLL.
>

4. Statically linked to and compiled with your datalayer code.


> Thanks in advanced,
>
> John
>
>
> ___
> 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] A few warnings compiling sqlite (-Wextra)

[J Decker]

gcc version 8.2.1 20181127 (GCC)

sqlite-amalgamation-326

gcc -Wextra -c sqlite3.c  (-Wno-fallthrough)

the last one ( sqlite3_trace ) is actually a pretty bad conversion,
changing a pointer to a u32 and vice versa; but it is only an internal
function so maybe it doesn't matter.

The rest are because SQLITE_TRANSIENT : SQLITE_DYNAMIC aren't just simple
values, but are function pointers with different signatures...



--
sqlite3.c: In function ‘strftimeFunc’:
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:21921:52: note: in expansion of macro ‘SQLITE_DYNAMIC’
   z==zBuf ? SQLITE_TRANSIENT : SQLITE_DYNAMIC);
^~
sqlite3.c: In function ‘sqlite3ErrorWithMsg’:
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:30178:54: note: in expansion of macro ‘SQLITE_DYNAMIC’
 sqlite3ValueSetStr(db->pErr, -1, z, SQLITE_UTF8, SQLITE_DYNAMIC);
  ^~
sqlite3.c: In function ‘sqlite3VdbeMemSetStr’:
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:75014:19: note: in expansion of macro ‘SQLITE_DYNAMIC’
   }else if( xDel==SQLITE_DYNAMIC ){
   ^~
sqlite3.c: In function ‘valueFromExpr’:
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:75419:55: note: in expansion of macro ‘SQLITE_DYNAMIC’
   sqlite3ValueSetStr(pVal, -1, zVal, SQLITE_UTF8, SQLITE_DYNAMIC);
   ^~
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:75462:29: note: in expansion of macro ‘SQLITE_DYNAMIC’
  0, SQLITE_DYNAMIC);
 ^~
sqlite3.c: In function ‘sqlite3VdbeExec’:
sqlite3.c:90356:38: warning: cast between incompatible function types from
‘int (*)(u32,  void *, void *, void *)’ {aka ‘int (*)(unsigned int,  void
*, void *, void *)’} to ‘void (*)(void *, const char *)’
[-Wcast-function-type]
   void (*x)(void*,const char*) = (void(*)(void*,const
char*))db->xTrace;
  ^
sqlite3.c: In function ‘printfFunc’:
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:112292:25: note: in expansion of macro ‘SQLITE_DYNAMIC’
 SQLITE_DYNAMIC);
 ^~
sqlite3.c: In function ‘generateColumnNames’:
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:125795:58: note: in expansion of macro ‘SQLITE_DYNAMIC’
 sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName, SQLITE_DYNAMIC);
  ^~
sqlite3.c:14080:27: warning: cast between incompatible function types from
‘int (*)(void *)’ to ‘void (*)(void *)’ [-Wcast-function-type]
 #define SQLITE_DYNAMIC   ((sqlite3_destructor_type)sqlite3MallocSize)
   ^
sqlite3.c:125802:52: note: in expansion of macro ‘SQLITE_DYNAMIC’
   sqlite3VdbeSetColName(v, i, COLNAME_NAME, z, SQLITE_DYNAMIC);
^~
sqlite3.c: In function ‘sqlite3InvokeBusyHandler’:
sqlite3.c:154304:12: warning: cast between incompatible function types from
‘int (*)(void *, int)’ to ‘int (*)(void *, int,  sqlite3_file *)’ {aka ‘int
(*)(void *, int,  struct sqlite3_file *)’} [-Wcast-function-type]
 xTra = (int(*)(void*,int,sqlite3_file*))p->xBusyHandler;
^
sqlite3.c: In function ‘sqlite3_busy_timeout’:
sqlite3.c:154382:30: warning: cast between incompatible function types from
‘int (*)(void *, int,  sqlite3_file *)’ {aka ‘int (*)(void *, int,  

Re: [sqlite] Broken Foreign key

[J Decker]

I see.  That version of the database is missing primary key option4_name...

Why did it suddenly start failing without the primary key index when it was
working for a very long time ...
it's almost like something removed the primary key

table|option4_name|CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL
PRIMARY KEY,`name` varchar(255) NOT NULL default '' UNIQUE)
index|sqlite_autoindex_option4_name_1|option4_name|3|
index|sqlite_autoindex_option4_name_2|option4_name|4|
index|name|option4_name|5|CREATE UNIQUE INDEX 'name' ON
'option4_name'('name')



On Sun, Nov 11, 2018 at 8:15 PM Keith Medcalf  wrote:

>
> parent keys MUST have unique indexes.  Add the missing indexes.
> child keys SHOULD be indexed:  UNIQUE indexes for 1:1 relationships,
> non-unique indexes for 1:N relationships
>
> put unique indexes on your parents and all will work just fine.
>
> ---
> 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 J Decker
> >Sent: Sunday, 11 November, 2018 20:25
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Broken Foreign key
> >
> >https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)
> >
> >this has a .option.db in it and a test.sh.
> >It turns on foreign_keys, selects from the name_id that it says is a
> >forieng key violation, and inserts into option4_map.
> >
> >option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
> >option4_name has name_id(PK), name
> >option4_values has option_id(FK),string
> >
> >the insert says the name isn't there; in the real scenario the ID of
> >the
> >name was JUST selected; as it is here... so it sort of had to be
> >there;
> >although technically the insert happens on a new connection (after
> >pragma
> >foreign_keys=on init runs)
> >
> >
> >sqlite3 --echo .option.db << EOF
> >pragma foreign_keys=on;
> >select * from option4_name where
> >name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
> >.print Insert into option4_map(option_id,parent_option_id,name_id)
> >values
> >('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
> >0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
> >Insert into option4_map(option_id,parent_option_id,name_id) values
> >('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-
> >0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
> >EOF
> >
> >
> >I tested with sqlite3   3.25.2(different linux system)
> >3.24.2(actual
> >code)3.7.17(actual linux system)
> >___
> >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] Broken Foreign key

[J Decker]

https://d3x0r.org:444/temporary/broken_fk.tgz  (30k)

this has a .option.db in it and a test.sh.
It turns on foreign_keys, selects from the name_id that it says is a
forieng key violation, and inserts into option4_map.

option4_map has option_id(PK),parent_id(FK option_id),name_id(FK)
option4_name has name_id(PK), name
option4_values has option_id(FK),string

the insert says the name isn't there; in the real scenario the ID of the
name was JUST selected; as it is here... so it sort of had to be there;
although technically the insert happens on a new connection (after pragma
foreign_keys=on init runs)


sqlite3 --echo .option.db << EOF
pragma foreign_keys=on;
select * from option4_name where
name_id='5e49af20-e621-11e8-9a4c-0cc47a7f9351';
.print Insert into option4_map(option_id,parent_option_id,name_id) values
('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
Insert into option4_map(option_id,parent_option_id,name_id) values
('366a676a-e626-11e8-a7de-0cc47a7f9351','b2b9feae-315d-11e7-b998-0cc47a7f9350','5e49af20-e621-11e8-9a4c-0cc47a7f9351');
EOF


I tested with sqlite3   3.25.2(different linux system)   3.24.2(actual
code)3.7.17(actual linux system)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding CoC

[J Decker]

On Mon, Oct 22, 2018 at 12:22 PM R Smith  wrote:

>
> On 2018/10/22 7:09 PM, Simon Slavin wrote:
> >
> > If you're not going to accept those rules, in exactly the translation
> used, you might as well pick something entirely different.
> >
> > Simon.
>
> Indeed. Further to this, as I understand a CoC, it's basically the core
> entity informing whomever be so interested, how he/she/they aim to
> conduct themselves in the fulfillment of duties or business.
>
> I do not think it proscribes or prescribes to anyone else.
>
> For me, speaking as the avid Atheist I am, the specific rule-set in
> question is weird, but it also provides a great picture into the core
> devs' feelings of what's right, and that they will do the "right" thing
> in general, by the average understanding of the word "right" among most
> current philosophies - and I imagine that's really all they wished to say.
>
> To add to that, if there is one thing we (as free thinkers) hold in
> highest regard, it's not being forced to do anything; not being
> commanded; not having to bend to another will or doctrine. So I say keep
> it.
>

59.  Fulfill not the desires of the flesh; hate your own will.
:)

>
>
>
> PS: I could never comply with rule 63.  :)
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

On Mon, Oct 22, 2018 at 12:22 PM R Smith  wrote:

>
> On 2018/10/22 7:09 PM, Simon Slavin wrote:
> >
> > If you're not going to accept those rules, in exactly the translation
> used, you might as well pick something entirely different.
> >
> > Simon.
>
> Indeed. Further to this, as I understand a CoC, it's basically the core
> entity informing whomever be so interested, how he/she/they aim to
> conduct themselves in the fulfillment of duties or business.
>
> I do not think it proscribes or prescribes to anyone else.
>
> For me, speaking as the avid Atheist I am, the specific rule-set in
> question is weird, but it also provides a great picture into the core
> devs' feelings of what's right, and that they will do the "right" thing
> in general, by the average understanding of the word "right" among most
> current philosophies - and I imagine that's really all they wished to say.
>
> To add to that, if there is one thing we (as free thinkers) hold in
> highest regard, it's not being forced to do anything; not being
> commanded; not having to bend to another will or doctrine. So I say keep
> it.
>
>
>
> PS: I could never comply with rule 63.  :)
>
>
> ___
> 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] Fill empty space with random

[J Decker]

(sorry for the math err s/16/32/g and s/512/256/   - I double and halfed
the wrong directions.)

On Sun, Oct 14, 2018 at 7:57 AM J Decker  wrote:

>
>
> On Sun, Oct 14, 2018 at 7:24 AM Luuk  wrote:
>
>> On 14-10-2018 16:17, Simon Slavin wrote:
>> > On 14 Oct 2018, at 12:56pm, J Decker  wrote:
>> >
>> >> Is there maybe a compile option for sqlite to fill empty space in a db
>> with random data rather than 0 ?
>> > There is not.  But
>> >
>> > (A) It may be an easy change to the source code
>> > (B) Your operating system may have a setting to do this automatically
>> to freed blocks on a storage device.
>> > (C) Your device driver may have a setting to do this automatically to
>> freed blocks on the device.
>> >
>> > That type of security is normally done at OS or device level, not by
>> each individual app.
>> >
>> > Simon.
>> >
>> Can you give any hints on why it would be a security issue to fill
>> 'empty space' with 0, and why 'random data' should be used?
>>
>> ?
>>
> I hesitate to describe the real scenario; and want to instead manufacture
> one; but in either case I feel there will be more comments about the
> underlaying system than on Sqlite itself.
>
> In the simple case, the VFS that the sqlite Db is mounted in is encrypted
> with a long key.  The key has cycles at 4096(A) and 16(B1-Bn) bytes
> (4096/16 = 256 cycles of Bn); such that each sector is masked with
> A^B1(256x), A^B2(256x), ... all together there is no repetition because the
> change from Bn to B(n+1) at the 4096 boundary makes the stream overall
> appear continuously random.
> Only data that is written is actually masked...
>
> Sqlite likes to write 0's in large splotches (in my usage); which leaks
> key information; (only slightly more than the data stored in tables
> typically, which is a lot of the same bytes (0, 1 for instance and A-Z, a-z
> less-so; but all of that has upper bit(s) that are 0... )
>
> And even is a specific sector (or several) is 'cracked' it doesn't do any
> good for any other page... but if LOTS of pages are found, it becomes
> easier to find what the overall A key is, which makes finding sector keys
> that you only need a few 32-64 bytes of 0's to reveal the sector specific
> key (for later use?)
>
> The keys are a procedurally generated with a PRNG sha2 bit streams based;
> so 512 bits (16 bytes) at a time; and sha algorithms generates VERY good PR
> numbers. which can be consumed as end-to-end bit streams.
>
> I might look into it; there are certainly a great test suite available to
> reveal issues; but I expect Sqlite 'expects' memory to be 0 initialized
> (even when filled from disk) and that it will be a HUGE can of worms.
>
>
>>
>> ___
>> 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] Fill empty space with random

[J Decker]

On Sun, Oct 14, 2018 at 7:24 AM Luuk  wrote:

> On 14-10-2018 16:17, Simon Slavin wrote:
> > On 14 Oct 2018, at 12:56pm, J Decker  wrote:
> >
> >> Is there maybe a compile option for sqlite to fill empty space in a db
> with random data rather than 0 ?
> > There is not.  But
> >
> > (A) It may be an easy change to the source code
> > (B) Your operating system may have a setting to do this automatically to
> freed blocks on a storage device.
> > (C) Your device driver may have a setting to do this automatically to
> freed blocks on the device.
> >
> > That type of security is normally done at OS or device level, not by
> each individual app.
> >
> > Simon.
> >
> Can you give any hints on why it would be a security issue to fill
> 'empty space' with 0, and why 'random data' should be used?
>
> ?
>
I hesitate to describe the real scenario; and want to instead manufacture
one; but in either case I feel there will be more comments about the
underlaying system than on Sqlite itself.

In the simple case, the VFS that the sqlite Db is mounted in is encrypted
with a long key.  The key has cycles at 4096(A) and 16(B1-Bn) bytes
(4096/16 = 256 cycles of Bn); such that each sector is masked with
A^B1(256x), A^B2(256x), ... all together there is no repetition because the
change from Bn to B(n+1) at the 4096 boundary makes the stream overall
appear continuously random.
Only data that is written is actually masked...

Sqlite likes to write 0's in large splotches (in my usage); which leaks key
information; (only slightly more than the data stored in tables typically,
which is a lot of the same bytes (0, 1 for instance and A-Z, a-z less-so;
but all of that has upper bit(s) that are 0... )

And even is a specific sector (or several) is 'cracked' it doesn't do any
good for any other page... but if LOTS of pages are found, it becomes
easier to find what the overall A key is, which makes finding sector keys
that you only need a few 32-64 bytes of 0's to reveal the sector specific
key (for later use?)

The keys are a procedurally generated with a PRNG sha2 bit streams based;
so 512 bits (16 bytes) at a time; and sha algorithms generates VERY good PR
numbers. which can be consumed as end-to-end bit streams.

I might look into it; there are certainly a great test suite available to
reveal issues; but I expect Sqlite 'expects' memory to be 0 initialized
(even when filled from disk) and that it will be a HUGE can of worms.


>
> ___
> 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] Fill empty space with random

[J Decker]

On Sun, Oct 14, 2018 at 7:17 AM Simon Slavin  wrote:

> On 14 Oct 2018, at 12:56pm, J Decker  wrote:
>
> > Is there maybe a compile option for sqlite to fill empty space in a db
> with random data rather than 0 ?
>
> There is not.  But
>
> (A) It may be an easy change to the source code
>
Sure; figured I'd toss out the question to see if there was at least a
springboard


> (B) Your operating system may have a setting to do this automatically to
> freed blocks on a storage device.
> (C) Your device driver may have a setting to do this automatically to
> freed blocks on the device.
>
> B and C would apply if there was a vacuum also; adding data, and
deleteting data, the db ends up with lots of zeros
Also between non-integral pages; messages that are say 700 bytes; so 4096%
700 is 596; which is all filled with zeros...
if I end up with messages that are say 2200 bytes; nearly half the page is
blank for a while.


> That type of security is normally done at OS or device level, not by each
> individual app.
>
> 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] Fill empty space with random

[J Decker]

On Sun, Oct 14, 2018 at 5:48 AM Bob Gailer  wrote:

> On Oct 14, 2018 7:57 AM, "J Decker"  wrote:
> >
> > Is there maybe a compile option for sqlite to fill empty space in a db
> with
> > random data rather than 0 ?
>
> I don't know about compile options.
>
> You could do a 1 time update to set all 0 columns to random(1,99), and
> create a trigger to do the same on insert.
>
> This, of course is on a table-by-table basis.
>
Doesn't work for all the empty space outside of the table; between tables;
not unused pages from deleted records, 

> ___
> 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] Fill empty space with random

[J Decker]

Is there maybe a compile option for sqlite to fill empty space in a db with
random data rather than 0 ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite Doc Error ?

[J Decker]

https://www.sqlite.org/limits.html
*Maximum Length Of An SQL Statement*
 The maximum number of bytes in the text of an SQL statement is limited to
SQLITE_MAX_SQL_LENGTH which defaults to 100. You can redefine this
limit to be as large as the smaller of SQLITE_MAX_LENGTH and 1073741824.


but from the sources (3.24)
#ifndef SQLITE_MAX_SQL_LENGTH
# define SQLITE_MAX_SQL_LENGTH 10
#endif
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_* with error handling

[J Decker]

On Tue, Sep 4, 2018 at 2:11 PM Keith Medcalf  wrote:

>
> On Tuesday, 4 September, 2018 14:00, Clemens Ladisch 
> wrote:
>
> >Keith Medcalf wrote:
> >> 6)  If the column type is SQLITE_BLOB
> >> a)  Retrieve the column value pointer using column_blob
> >> b)  If the returned pointer is NULL, then an error has occurred
>
> >"The return value from sqlite3_column_blob() for a zero-length BLOB
> >is a NULL pointer."
>
> Hmmm.  Yes.
>
> " The values returned by sqlite3_column_bytes() and
> sqlite3_column_bytes16() do not include the zero terminators at the end of
> the string. For clarity: the values returned by sqlite3_column_bytes() and
> sqlite3_column_bytes16() are the number of bytes in the string, not the
> number of characters.
>

(for text)
column_bytes() returns the number of bytes specified for the string, and
has nothing to do with NUL unless you end up with a tranlsation from utf8
to utf16 or vice versa.  So it can/will return the number of bytes
including nulls in the text.


>
> Strings returned by sqlite3_column_text() and sqlite3_column_text16(),
> even empty strings, are always zero-terminated. The return value from
> sqlite3_column_blob() for a zero-length BLOB is a NULL pointer. "
>
if there is no transform between utf16 or utf8, text returns a pointer
which may contain nuls; I do forget, but it may be the buffer is always
allocated +1 and include an extra nul... (probably does)
Such nuls can be isnerted with the escape '||char(0)||'


>
> So, an empty string returns a valid pointer but the bytes count is zero.
>
> For an empty blob, the returned pointer is NULL and the bytes count is
> zero.  So, for column_text the test for a NULL pointer will indicate an
> error.
>
> In the case of a BLOB if a NULL pointer is returned the error code must be
> retrieved and then if and only if the bytes counter is greater than 0 is
> the error valid.
>
> Does this mean that if you are retrieving the value of a blob via the
> colmn_blob interface you should ask for the column_bytes first and only
> expect a NULL pointer if the bytes count is zero?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
>
>
> ___
> 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] Strategies to reduce page-loads?

[J Decker]

On Thu, Aug 30, 2018 at 4:48 AM Richard Hipp  wrote:

> On 8/30/18, t...@qvgps.com  wrote:
> >
> > Structure is  simple:
> > CREATE TABLE Lines(Id LONG PRIMARY KEY, Label VARCHAR(50), Coordinates
> > BLOB, Flags INT, StyleId INT);
> > And an rtree-index:
> > CREATE VIRTUAL TABLE Lines_bb USING rtree (Id LONG PRIMARY KEY, X0
> > FLOAT, X1 FLOAT, Y0 FLOAT, Y1 FLOAT, Z0 FLOAT, Z1 FLOAT);
>
> Three points that might help, either separately or in combination:
>
> (1) Use exactly "INTEGER PRIMARY KEY".  "LONG PRIMARY KEY" and "INT
> PRIMARY KEY" are not the same thing and do not work as well.
>
> (2) In the very latest versions of SQLite, 3.24,0 and the beta for
> 3.25.0, you can put the "Lines" information directly in the RTree:
>
>CREATE VIRTUAL TABLE Lines_bb USING rtree(id, x0, x1, y0, y1, z0,
> z1, +Label, +Coordinates, +Flags, +StyleId);
>
> The extra columns in r-tree are prefaced by a "+" character so that
> the r-tree module knows that they are auxiliary columns and not extra
> coordinates.
>
> (3) Consider deliberating choosing INTEGER PRIMARY KEY values based on
> a "Morton code" or "Z-Order curve" of the coordinates.
>

Morton code and zorder curves look, initially, to be good for keeping near
things near, but the average distance for two elements is much greater than
the row skip in a similar linear col + n*rows type flat array; and more
often will span +4 distinct pages instead of being just 1 or 2 for here
and +/- 1 page for rows above/below

On the 2^n boundaries, the skips become very significant, and grabbing
random queries is guaranteed to cover more space.  so like at 15,15,15 to
16,16,16 there is a huge jump in space ( 4096) instead of a constant offset.

There was a voxel engine that was claiming they were going to move to a
morton encoding; and I was working with a different engine, so I built a
simulator to test averge lookup distances; it was far more efficient to
keep sectors of voxels (32x32x32) in flat indexing, which made the maximum
distance 1025 ( but along that row is +1023, 1024, 1025, which are all in
the same page, where morton would be like +512, +1024, +2048, which makes
it much more likely to overflow to yet another page.  (since the cells
arent' just bytes, all indexes should be mulitplied by cell structure size)

(https://en.wikipedia.org/wiki/Z-order_curve)  That will cause
> features that are close together geographically to tend to be close
> together within the file.  There is are two extension functions in the
> https://www.sqlite.org/src/artifact/b0ff58fa643afa1d file of the
> SQLite source tree that might help you with this.  Or you can do the
> same using your own functions.
> --
> 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] keys

[J Decker]

On Fri, Aug 24, 2018 at 4:31 PM  wrote:

> "The parent key of a foreign key constraint is not allowed to use the
> rowid. The parent key must used named columns only."
>
> Why is this?
> Which would be more efficient?
> 1) WITHOUT ROWID and column of INTEGER PRIMARY KEY
> or
> 2) an aliased rowid.
>
I expect because rowid is subject to change
insert 3 records, delete the first, and 2, 3 become 1 and 2 ... which would
have to cascade update ALL other FK refs...


>
> Background: The data is sparse, incomplete, and fuzzy. Two records are
> known to have a relationship
> even though the content that identifies this relation is not known.
>
>
> Lyle Ward
> Genealogist
>
> ___
> 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] Invalid Blob Length Error Message

[J Decker]

On Fri, Aug 17, 2018 at 5:14 AM Tim Streater  wrote:

> On 17 Aug 2018, at 01:24, Casey Rodarmor  wrote:
>
> > I wrote an erroneous update statement:
> >
> > ```
> > UPDATE foo SET bar = x'01234';
> > ```
> >
> > The error message was:
> >
> > ```
> > unrecognized token: "x'01234'
> > ```
> >
> > *I know now *that the problem was that the blob's length was not a
> > multiple of two. However, the error message didn't give me an
> > indication of that. Would it be possible to make the error message
> > more explicit, to help users debug this error?
>
> I expect the error message is correct, and that what you meant to write
> was:
>
> while 'correct' it's not meaningful is what the OP meant, I beleive.
something like

bad encoding: "x'01234'

would be much more meaningful(?)


> update foo set bar = 0x1234;
>
>
> See:
>
> 
>
> and scroll down to the part about Literal Values (Constants).
>
>
>
>
> --
> Cheers  --  Tim
> ___
> 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] PRAGMA case_sensitive_like

[J Decker]

https://www.sqlite.org/pragma.html#pragma_pragma_list

A pragma can take either zero or one argument. The argument is may be
either in parentheses or it may be separated from the pragma name by an
equal sign. The two syntaxes yield identical results. In many pragmas, the
argument is a boolean. The boolean can be one of:

*1 yes true on0 no false off*

Keyword arguments can optionally appear in quotes. (Example: 'yes' [FALSE].)
Some pragmas takes a string literal as their argument. When pragma takes a
keyword argument, it will usually also take a numeric equivalent as well.
For example, "0" and "no" mean the same thing, as does "1" and "yes". When
querying the value of a setting, many pragmas return the number rather than
the keyword.
looks like , from the docs, `  pragma *case_sensitive_like` should rteturn
the value; a pragma without a value.
there's also a pragma pragma_list which (if compiled in) will return all
available pragmas

On Thu, Aug 16, 2018 at 6:16 AM Chris Locke  wrote:

> While the pragma *case_sensitive_like *can be set, there doesn't seem to be
> a way to read it.
> Is there a reason for it being write only?
> A hacky workaround is to use the SQL, "select 'x' like 'X'  ", but is there
> a better 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] "Cheating" at making common tables

[J Decker]

I did this sort of thing in Xperdex (
https://sourceforge.net/projects/xperdex/ ) which is a C# thing, and
enabled easy creation of DataTables similarly auto creating ID and Name by
stripping pluralization from the name.
Was working on a similar thing for JS; but keep getting distracted making
it more of a schema layer for graph databases instead.  (
https://github.com/d3x0r/rdb-dataset )  (singularlize
https://github.com/d3x0r/rdb-dataset/blob/master/rdb-dataset.js#L53  (for
english))

But when I presented the utility of the methods; noone in the group I was
working with could concur on the automated methods; claiming I shouldn't
strip 's' off of 'games' and it should be 'games_id' and 'games_name' ..
and like 'sessions_game_groups_games_id'  *shrug* I just mention this,
because I doubt you'll ever get such a generic utility from sqlite... (or
any other database) but will be a layer you'll have to maintain in your own
libraries...

On Sat, Aug 4, 2018 at 7:00 PM Stephen Chrzanowski 
wrote:

> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key error

[J Decker]

I thought this was that index  index,sqlite_autoindex_
option4_name_1,option4_name,3,
but maybe that's the unique on name.

I see; I guess it is missing; indexes get created now more properly.  I
deleted it and recreated it, which created the indexes more properly.
thanx, and sorry for the noise.



On Mon, Jul 30, 2018 at 11:13 PM Clemens Ladisch  wrote:

> J Decker wrote:
> >  CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name`
> > varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE)
>
> > FOREIGN KEY (`name_id`) REFERENCES `option4_name`(`name_id`)
>
> > foreign key mismatch - "option4_map" referencing "option4_name"
>
> name_id must be the primary key, or at least have a unique
> constraint/index.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key error

[J Decker]

test.db
https://drive.google.com/open?id=1gX4QDLy3rA1YVFXZnhj_vlAClVmrU4Cz

SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
sqlite> pragma foreign_keys=on;
sqlite> Insert into option4_map(`option_id`,`parent_option_id`,`name_id`)
values
('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f');
Error: foreign key mismatch - "option4_map" referencing "option4_name"
sqlite> select * from option4_name where name like 'system settings';
cc47f95a-e79a-11e7-872c-2e6fc90d301f|System Settings

I have kinda an old command line tool - but it does the same thing.

On Mon, Jul 30, 2018 at 8:11 PM J Decker  wrote:

>
>
> On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf  wrote:
>
>>
>> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
>> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
>> >"SQLITE_DEFAULT_FOREIGN_KEYS=1"
>>
>> >Is there something about the combination of options I've used?
>>
>> Do you get different results when using different options?  (of course,
>> if you turn off foreign key enforcement then you will not get errors when
>> you violate declared foreign key constraints), so other than that obviously?
>>
>> It seems to be complaining about a foreign-key reference not existing.
>> The only option which would affect that is having foreign-keys turned on.
>> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
>> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
>> enforced -- the default state is ON rather than OFF)
>>
>> no, removing all options (except the one for metadata) and enabling
> pragma foreign_keys=on the error still happens (I had enabled it on the
> reader but not the writer, on the writer I added the keys=on and
> integrity_check ) and
>
> PRAGMA foreign_key_check
> PRAGMA foreign_key_check(option4_map)
>
> There doesn't appear to be existing errors.
>
> ```
> 
>
> pragma integrity_check
> pragma foreign_keys=on
> select * from option4_name where name = 'system Settings'
> no data
> PRAGMA foreign_key_check
> PRAGMA foreign_key_check(option4_map)
> PRAGMA foreign_key_list(option4_map)
> 0,0,option4_name,name_id,name_id,CASCADE,CASCADE,NONE
> 1,0,option4_map,parent_option_id,option_id,CASCADE,CASCADE,NONE
>
> Do Command[01888DA2FC6C:*/../option.db]: select * from sqlite_master
>  CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name`
> varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE)
>  index,sqlite_autoindex_option4_name_1,option4_name,3,
>  table,option4_map,option4_map,4,CREATE TABLE `option4_map` (`option_id`
> char(36) NOT NULL,`parent_option_id` char(36) NOT NULL default
> '0',`name_id` char(36) NOT NULL default '0',`description`
> tinytext,CONSTRAINT `parent_key2` UNIQUE (`parent_option_id`,`name_id`) ON
> CONFLICT REPLACE, FOREIGN KEY  (`parent_option_id`) REFERENCES
> `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY
> (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE CASCADE ON DELETE
> CASCADE)
>  index,sqlite_autoindex_option4_map_1,option4_map,5,
>  CREATE TABLE `option4_values` (`option_id` char(36) default '0',`string`
> varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
> UNIQUE (`option_id`,`segment`) ON CONFLICT REPLACE, FOREIGN KEY
> (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
> DELETE CASCADE)
>  index,sqlite_autoindex_option4_values_1,option4_values,7,
>  CREATE TABLE `option4_blobs` (`option_id` char(36) default '0' CONSTRAINT
> `value_id` UNIQUE,`binary` blob, FOREIGN KEY  (`option_id`) REFERENCES
> `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
>  index,sqlite_autoindex_option4_blobs_1,option4_blobs,9,
>  CREATE TABLE `option4_exception` (`option_exception_id` char(36) NOT NULL
> CONSTRAINT `option_exception_idkey` UNIQUE,`apply_from` datetime default
> '-00-00 00:00:00',`apply_until` datetime default '-00-00
> 00:00:00',`system_id` int(11) NOT NULL default '0',`override_value_id`
> char(36) NOT NULL default '0',`option_id` char(36) NOT NULL default
> '0',CONSTRAINT `FK_map_exception` FOREIGN KEY (`option_id`) REFERENCES
> `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
>  index,sqlite_autoindex_option4_exception_1,option4_exception,61,
>
>
> BEGIN TRANSACTION
> Insert into option4_map(`option_id`,`parent_option_id`,`name_id`) values
> ('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')
> Error inserting option: Result of p

Re: [sqlite] Foreign Key error

[J Decker]

On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf  wrote:

>
> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
> >"SQLITE_DEFAULT_FOREIGN_KEYS=1"
>
> >Is there something about the combination of options I've used?
>
> Do you get different results when using different options?  (of course, if
> you turn off foreign key enforcement then you will not get errors when you
> violate declared foreign key constraints), so other than that obviously?
>
> It seems to be complaining about a foreign-key reference not existing.
> The only option which would affect that is having foreign-keys turned on.
> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
> enforced -- the default state is ON rather than OFF)
>
> no, removing all options (except the one for metadata) and enabling pragma
foreign_keys=on the error still happens (I had enabled it on the reader but
not the writer, on the writer I added the keys=on and integrity_check ) and

PRAGMA foreign_key_check
PRAGMA foreign_key_check(option4_map)

There doesn't appear to be existing errors.

```


pragma integrity_check
pragma foreign_keys=on
select * from option4_name where name = 'system Settings'
no data
PRAGMA foreign_key_check
PRAGMA foreign_key_check(option4_map)
PRAGMA foreign_key_list(option4_map)
0,0,option4_name,name_id,name_id,CASCADE,CASCADE,NONE
1,0,option4_map,parent_option_id,option_id,CASCADE,CASCADE,NONE

Do Command[01888DA2FC6C:*/../option.db]: select * from sqlite_master
 CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE)
 index,sqlite_autoindex_option4_name_1,option4_name,3,
 table,option4_map,option4_map,4,CREATE TABLE `option4_map` (`option_id`
char(36) NOT NULL,`parent_option_id` char(36) NOT NULL default
'0',`name_id` char(36) NOT NULL default '0',`description`
tinytext,CONSTRAINT `parent_key2` UNIQUE (`parent_option_id`,`name_id`) ON
CONFLICT REPLACE, FOREIGN KEY  (`parent_option_id`) REFERENCES
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY
(`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE CASCADE ON DELETE
CASCADE)
 index,sqlite_autoindex_option4_map_1,option4_map,5,
 CREATE TABLE `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`) ON CONFLICT REPLACE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
 index,sqlite_autoindex_option4_values_1,option4_values,7,
 CREATE TABLE `option4_blobs` (`option_id` char(36) default '0' CONSTRAINT
`value_id` UNIQUE,`binary` blob, FOREIGN KEY  (`option_id`) REFERENCES
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
 index,sqlite_autoindex_option4_blobs_1,option4_blobs,9,
 CREATE TABLE `option4_exception` (`option_exception_id` char(36) NOT NULL
CONSTRAINT `option_exception_idkey` UNIQUE,`apply_from` datetime default
'-00-00 00:00:00',`apply_until` datetime default '-00-00
00:00:00',`system_id` int(11) NOT NULL default '0',`override_value_id`
char(36) NOT NULL default '0',`option_id` char(36) NOT NULL default
'0',CONSTRAINT `FK_map_exception` FOREIGN KEY (`option_id`) REFERENCES
`option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
 index,sqlite_autoindex_option4_exception_1,option4_exception,61,


BEGIN TRANSACTION
Insert into option4_map(`option_id`,`parent_option_id`,`name_id`) values
('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')
Error inserting option: Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 191[] in [Insert into
option4_map(`option_id`,`parent_option_id`,`name_id`) values
('5e16eb63-946e-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')]


```


> To understand what foreign-key constraint you are violating one would need
> to see the database schema -- however my initial guess would be that that a
> option4_name is being checked when you provided an name_id -- that you have
> your constraints crossed (or collation for text fields set incorrectly,
> perhaps)
>
> > ... select name_id from
> >option4_name
> >where name like 'System Settings'
>
> Why are you using "like" instead of "==" ... there is no wildcard in the
> string ...
>

turns out it's for case insensitivity...
select * from option4_name where name like 'system Settings'
cc47f95a-e79a-11e7-872c-2e6fc90d301f,System Settings

but
select * from option4_name where name = 'system Settings'
-no data-






> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users 

Re: [sqlite] (no subject)

[J Decker]

On Mon, Jul 30, 2018 at 7:09 PM J Decker  wrote:

>
>
> On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf  wrote:
>
>>
>> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
>> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
>> >"SQLITE_DEFAULT_FOREIGN_KEYS=1"
>>
>> >Is there something about the combination of options I've used?
>>
>> Do you get different results when using different options?  (of course,
>> if you turn off foreign key enforcement then you will not get errors when
>> you violate declared foreign key constraints), so other than that obviously?
>>
>> It seems to be complaining about a foreign-key reference not existing.
>> The only option which would affect that is having foreign-keys turned on.
>> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
>> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
>> enforced -- the default state is ON rather than OFF)
>>
>> I removed all ( added pragma foreign_keys=on when opening the connection
> ), and it worked... readded options one at a time until it failed.
> OMIT_UTF16 causes it to fail.
>
> "SQLITE_OMIT_UTF16"
>
I think I lied there (and really sorry this was under (no subject))

>
>
>
>> To understand what foreign-key constraint you are violating one would
>> need to see the database schema -- however my initial guess would be that
>> that a option4_name is being checked when you provided an name_id -- that
>> you have your constraints crossed (or collation for text fields set
>> incorrectly, perhaps)
>>
>> > ... select name_id from
>> >option4_name
>> >where name like 'System Settings'
>>
>> Why are you using "like" instead of "==" ... there is no wildcard in the
>> string ...
>>
>
> Uhmm good question?  I think it can also load names by wildcard.
>
>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>>
>>
>> ___
>> 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] (no subject)

[J Decker]

On Mon, Jul 30, 2018 at 2:11 PM Keith Medcalf  wrote:

>
> >"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0",
> >"SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
> >"SQLITE_DEFAULT_FOREIGN_KEYS=1"
>
> >Is there something about the combination of options I've used?
>
> Do you get different results when using different options?  (of course, if
> you turn off foreign key enforcement then you will not get errors when you
> violate declared foreign key constraints), so other than that obviously?
>
> It seems to be complaining about a foreign-key reference not existing.
> The only option which would affect that is having foreign-keys turned on.
> (SQLITE_DEFAULT_FOREIGN_KEYS=1 simply means that you do not need to execute
> PRAGMA FOREIGN_KEYS=ON on each connection in order to have foreign keys
> enforced -- the default state is ON rather than OFF)
>
> I removed all ( added pragma foreign_keys=on when opening the connection
), and it worked... readded options one at a time until it failed.
OMIT_UTF16 causes it to fail.

"SQLITE_OMIT_UTF16"



> To understand what foreign-key constraint you are violating one would need
> to see the database schema -- however my initial guess would be that that a
> option4_name is being checked when you provided an name_id -- that you have
> your constraints crossed (or collation for text fields set incorrectly,
> perhaps)
>
> > ... select name_id from
> >option4_name
> >where name like 'System Settings'
>
> Why are you using "like" instead of "==" ... there is no wildcard in the
> string ...
>

Uhmm good question?  I think it can also load names by wildcard.


>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> 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] (no subject)

[J Decker]

I have these options ... version 3.23.0.

"SQLITE_ENABLE_LOCKING_STYLE=0","SQLITE_THREADSAFE=0","SQLITE_OMIT_UTF16","SQLITE_ENABLE_COLUMN_METADATA=1",
"SQLITE_DEFAULT_FOREIGN_KEYS=1"

This is the end of operations...  It's starting to get an option...

13:31:12.541|88A08724~n...@sack.cc(91500):Getting option
{DEFAULT}[System Settings]Enable System Mapping=0

It selects from option4_name the name_id that = "system settings"

13:31:12.543|88A08724~n...@sack.cc(91500):Do
Command[01EA9BCE24EC:*/../option.db]: select name_id from option4_name
where name like 'System Settings'

it returns a UUID of that name

13:31:12.544|88A08724~n...@sack.cc
(85945):cc47f95a-e79a-11e7-872c-2e6fc90d301f

this checks to see if the option already exists...

13:31:12.546|88A08724~n...@sack.cc(91500):Do
Command[01EA9BCE24EC:*/../option.db]: select option_id from option4_map
where parent_option_id='69c9627f-e285-11e7-8728-2c6fc90d3020' and
name_id='cc47f95a-e79a-11e7-872c-2e6fc90d301f'

And it does not.

13:31:12.547|88A08724~n...@sack.cc(85661):no data

This opens a new connection to the database ( * B6E7EC instead of * CE24EC
); and starts a transation for the inserts.

13:31:23.409|88A08724~n...@sack.cc(84179):Do
Command[01EA9DB6E7EC:*/../option.db]: BEGIN TRANSACTION

This does an insert of the above queried node with the UUID of the name
returned from option4_name

13:31:23.410|88A08724~n...@sack.cc(92559):Do
Command[01EA9DB6E7EC:*/../option.db]: Insert into
option4_map(`option_id`,`parent_option_id`,`name_id`) values
('7f597a7b-9437-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')

And the operation fails.

13:31:23.418|88A08724~n...@sack.cc(92567):Error inserting option:
Result of prepare failed? foreign key mismatch - "option4_map" referencing
"option4_name" at char 191[] in [Insert into
option4_map(`option_id`,`parent_option_id`,`name_id`) values
('7f597a7b-9437-11e8-8745-2c6fc90d3020','69c9627f-e285-11e7-8728-2c6fc90d3020','cc47f95a-e79a-11e7-872c-2e6fc90d301f')]


Is there something about the combination of options I've used?

There will only be 1 thread ever in sqlite library code at a time. (the
number after the logging timestamp is the thread ID   88A08724 )  So
not only is it one thread, but is the same thread always.

there shouldn't be any outstanding operations correction there aren't
any other outstanding operations on the database.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to know which database is corrupted

[J Decker]

On Tue, Jul 24, 2018 at 5:11 PM Simon Slavin  wrote:

> On 24 Jul 2018, at 11:34pm, J Decker  wrote:
>
> > If the system rebooted; did a screen size change, and terminated the
> > program, it's possible it coild cause corruption.
>
> Step 1: use the command-line tool to fix your existing corruption.
>
I don't have the vfs driver as a sqlite3 loadable module; if I did, I don't
know the keys I could know but that's a lot of work.

It could have hapepened because the program was at a breakpoint when the
system ended.

>
> Step 2: prevent more corruption.
>
ya; turns out it'll be a bunch of code that never runs...

Quick question though - do I hae to attempt to keep afinity for values in
an insert?

Insert into * values ( '1234', 'asdf', "0.3422" );
/* where * is a list built from querying select * fields */
Other than haveing to escape anything with ' in it?  (or \0 )



>
> Ignoring the possibbility of bad programming (overwriting SQLite's memory
> or files) my guess is that your corruption is caused by not closing the
> database properly before the computer reboots.
>
> Could you close the database if no changes are made to it for one minute ?
> Reopen it when it's needed again.  Seems like a lot of window position
> changes would be done for a short time, then no more for a long time.
>
> 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 to know which database is corrupted

[J Decker]

On Tue, Jul 24, 2018 at 1:47 PM Simon Slavin  wrote:

> On 24 Jul 2018, at 8:43pm, J Decker  wrote:
>
> > I have a database that got corrupted; was working on implementing
> automatic
> > recovery
>
> It would be a million times better to figure out how the corruption occurs
> and prevent it.
>

Well...I've been considering that.   that database is written to when the
window moves (so I restore the position); and during startup.  It's just a
database for program options, which are loaded at the start, created if
they don't exist, given defaults, and can be updated during startup with a
script that gets loaded.

If the system rebooted; did a screen size change, and terminated the
program, it's possible it coild cause corruption.

On tree page 41 cell 37: Rowid 45783 out of order
... 37 to 0
On tree page 41 cell 0: Rowid 45787 out of order
On tree page 6 cell 166: Rowid 45787 out of order
On tree page 42 cell 40: Rowid 45787 out of order
... 40-0
On tree page 42 cell 0: Rowid 45787 out of order
On tree page 6 cell 165: Rowid 45787 out





>
> 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] how to know which database is corrupted

[J Decker]

I have a database that got corrupted; was working on implementing automatic
recovery

I have a callback configured on SQLITE_CONFIG_LOG
sqlite3_config( SQLITE_CONFIG_LOG, errorLogCallback, 0);

Which is global, and does not identify the instance.  I figured, I could
just easily register the same callback on the db connection object

sqlite3_db_config( odbc->db, SQLITE_CONFIG_LOG, dbErrorLogCallback, odbc );

But that's not supported per connection.
So how do I know which connection is executing a statement that failed?  (I
could potentially have more than one query active at the same time in
different databases)

Also; should I post that error as an async error?  I mean... in the
callback for the log, a query is outstanding on a statement in the db; so I
suppose I should let that unwind before doing further operations
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue using SEE

[J Decker]

On Fri, Jul 20, 2018 at 11:05 AM Keith Medcalf  wrote:

>
> Firstly, the SQLITE_DLL define does not exist (is this a bug in the docs>?
> Secondly you did not define SQLITE_HAS_CODEC as required to integrate the
> SEE codec/
> Thirdly a .dll file cannot be created with ar.  ar is for creating
> libraries, not DLLs. (.a files)
>
> To statically link you should just add the see_sqlite3.c file to your
> project making sure you define SQLITE_HAS_CODEC
>
> Or do you really want a dynamically linked DLL?
>
It would be a dll... but you missed the more mportant point

This generates “see-sqlite3.dll” but for some reason when I am trying to
add this dll as a reference its throwing error "see-sqlite3.dll is not a
valid .Net assembly ".

that it needs to be built as system.data.sqlite is not just a dll.


>
> ---
> 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 sudeep singh
> >Sent: Friday, 20 July, 2018 11:52
> >To: sqlite-users@mailinglists.sqlite.org
> >Subject: [sqlite] Issue using SEE
> >
> >Hi Team,
> >
> >We have recently purchased SEE subscription to encrypt our data in
> >mobile.
> >Our application supports iOS, android and windows(UWP).  We used
> >Xamarin
> >forms(.net standard2) approach to build this application which
> >supports c#
> >language. We are following this link
> >https://www.sqlite.org/see/doc/trunk/www/readme.wiki where they have
> >guided
> >how to compile and use SEE.
> >
> >Basically we are trying to use this SQLITE as a dll and to do so we
> >ran
> >below command in mac
> >
> >cat sqlite3.c see.c >see-sqlite3.c
> >
> >gcc -c -DSQLITE_DLL=1 see-sqlite3.c
> >
> >ar a see-sqlite3.dll see-sqlite3.o
> >
> >
> >
> >
> >
> >This generates “see-sqlite3.dll” but for some reason when I am trying
> >to
> >add this dll as a reference its throwing error "see-sqlite3.dll is
> >not a
> >valid .Net assembly ".
> >
> >Any help will be really appreciated.
> >___
> >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] Round-tripping SQLite back and forth between text representation.

[J Decker]

does it have to be text?  There was serialization added to sqlite
https://www.sqlite.org/c3ref/serialize.html

On Mon, Jul 9, 2018 at 5:52 PM Randall Smith 
wrote:

> I'm curious if there is some standard or normal way to convert a SQLite DB
> to a text representation, and then recreate the DB content from the text.
> Naively, this seems hard or impossible as a general problem, but perhaps I
> am missing something.
>
> Thanks in advance for any advice or suggestions.
>
> Randall.
> ___
> 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 with Visual Studio

[J Decker]

there's also nuget package for sqlite which handles updates pretty well.
(IMO)

On Sun, Jul 8, 2018 at 11:13 PM Mike King  wrote:

> Same here. Again, I download it from the main system.data site. No problems
> at all.
>
> Cheers
>
> On Mon, 9 Jul 2018 at 06:57, Chris Locke  wrote:
>
> > I use system.data.sqlite.dll (taken from here:
> > https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
> > with no problems in both VS 2017 Professional and VS 2017 Community.
> >
> > Thanks,
> > Chris
> >
> >
> > On Mon, Jul 9, 2018 at 2:47 AM Roger Schlueter  wrote:
> >
> > > I am considering using the .net version of SQLite but have two
> questions:
> > >
> > >  1. The documentation lists the versions of Visual Studio that are
> > > supported.  VS2017 is NOT listed.  Is VS2017 supported.
> > >  2. The documentation states "Due to Visual Studio licensing
> > > restrictions, the Express Editions can no longer be supported."
> > > (Yes, in red).  However, Microsoft no longer uses the phrase
> > > "Express Edition" but rather calls the freebie version "Community".
> > > Is this just semantics or does the red warning still apply to
> > > Community?  Also, I am unaware of any "licensing restrictions" on
> > > the Community editions that would preclude the use of SQLite.  Are
> > > there such restrictions and, if so, what are they?
> > >
> > > Roger
> > >
> > > ___
> > > 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] Mailing list shutting down...

[J Decker]

On Wed, Jun 13, 2018 at 5:17 PM Simon Slavin  wrote:

> 13 Jun 2018, at 11:52pm, Bob Friesenhahn 
> wrote:
>
> > The problem is knowing what "one" means.  The subscription request is
> likely submitted via http/https into the web form and using a bogus email
> subscription address (of the "victim").  A botnet is able to submit these
> requests from hundreds of IP addresses.
>
>
And Hooray for TOR
https://www.dan.me.uk/tornodes


> First you accept only one request per IP address for every twentyfour
> hours.  You might as well just wipe your address list at midnight rather
> than do the tricky programming to implement a rolling 12 hour window.
>
> Second you have the form page generate a random number every time it shows
> the form.  The submission has to include the number sent to that IP
> address, and it has to be done at least five seconds after the number was
> generated.  This ties up that bot (though not the whole botnet) for five
> seconds.  One assumes that humans take more than 5 seconds to type their
> password twice and hit 'submit' so they won't even notice the difference.
> People who copy-and-paste their email address into the 'verify' field
> deserve what they get.
>
> Third you accept only one request per email address per week.
>
> The second of the above defeats a lot of bots.  They submit the request
> without ever downloading the form in the first place.
>
> For all the above you need two tables of data and some python
> programming.  Unfortunately I don't know Python.
>
> 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] [fossil-users] Mailing list shutting down...

[J Decker]

could use Gitter ( https://gitter.in ) is nice; emails on directed messges;
retains discussion histories; integrates with various other platforms...
git in particular such a referecning issues and pull requests with just
#
supports markdown formatting; which makes talking about `code fragments`
nice.

On Wed, Jun 13, 2018 at 1:22 PM Richard Hipp  wrote:

> On 6/13/18, Svyatoslav Mishyn  wrote:
> >
> > Another alternative would be nimforum:
> > https://github.com/nim-lang/nimforum
> >
>
> It does not appear to have email notification.  Unless I overlooked
> something.
> --
> 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] Idea: defining table-valued functions directly in SQL

[J Decker]

On Sun, Jun 10, 2018 at 7:48 AM Simon Slavin  wrote:

> On 10 Jun 2018, at 2:40pm, J Decker  wrote:
>
> > forget contributing code ... but funding?  how much can I pay to get
> better
> > NUL support?
>
> Can you tell us what's wrong with NUL support ?
>

I have, repeatedly.


> The best changes come free.  Because they're bug-fixes, or obvious
> improvements worth the increase in code size and processing time.
>

decrease in processing time.


>
> 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] Idea: defining table-valued functions directly in SQL

[J Decker]

On Sat, Jun 9, 2018 at 7:37 PM Andy Goth  wrote:

>
> This is probably not going to be added
> until SQLite's primary developers themselves decide they need it, or
> until someone else decides it's important enough to them to contribute
> code and/or funding.
>

forget contributing code ... but funding?  how much can I pay to get better
NUL support?  and get the table alias name for a column from a prepared
statement?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Usage of temporary files of SQLite3 on Android / SQLite commands fail

[J Decker]

On Sat, Jun 9, 2018 at 10:00 AM skywind mailing lists <
mailingli...@skywind.eu> wrote:

> Hi,
>
> what is your experience with temporary files requested by SQLite3 on
> Android? I am using SQLite3 natively on Android - I compiled SQLite3 by
> myself - and get an error when SQLite3 tries to create temporary files
> because:
>
> - the directories
> /var/tmp
> /usr/tmp
> /tmp
> .
>
Do you really NEED it to be a file or could you just open it as :memory: ?


>   are not accessible by a standard user (and therefore the app);
> - getenv is not guaranteed to be supported (or?);
> - sqlite3_temp_directory should not be used because it is deprecated.
>
> What are you doing?
>
> Regards,
> Hartwig
>
> ___
> 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] SQLite3 Error: Unable to open database file when file path has accents (e.g. é or è)

[J Decker]

On Tue, May 8, 2018 at 2:11 PM, Richard Hipp  wrote:

> On 5/8/18, Francis Kus  wrote:
> > I am able to open my database file if the path contains no accents.
> However,
> > if I change even one letter to an accented letter (e.g. replace 'e' with
> > 'é'), the database does not open and I get the error message "unable to
> open
> > database file".
>
> The argument to sqlite3_open() need to be UTF-8.  What encoding are you
> using?
>
and what system?


>
> --
> 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] Stored Procedures

[J Decker]

maybe Virtual tables?
can register functions of various types...
mostly since the typical usage of sqlite is as a tightly coupled library, a
function in your application is a 'stored procedure'.

http://www.sqlite.org/c3ref/update_hook.html  There are hooks which would
trigger callbacks like triggers...

to fake an exec you'd have to use like 'select sp_myProc(...)' instead of
'execute'


On Tue, May 8, 2018 at 1:58 PM, Igor Tandetnik  wrote:

> On 5/8/2018 4:02 PM, Mike Clark wrote:
>
>> Has there been any thought of implementing some kind of stored procedure
>> feature for SQLite?
>>
>> Or does this feature exist in some other form, with another name?
>>
>
> Triggers are kind of like stored procedures. You can create a dedicated
> view and put INSTEAD OF trigger on it. To "call" the trigger, insert a row
> into the view - the trigger could use column values, available via
> new.columnName, as its parameters.
> --
> Igor Tandetnik
>
>
>
> ___
> 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 statement column data types on Android

[J Decker]

https://www.sqlite.org/c3ref/column_blob.html

int sqlite3_column_type(sqlite3_stmt*, int iCol);

?

On Mon, May 7, 2018 at 4:28 PM, Bart Smissaert 
wrote:

> Yes, thanks, that might be the best way, but it can get a bit complicated
> with complex SQL.
>
> RBS
>
>
>
> On Tue, May 8, 2018 at 12:05 AM, Simon Slavin 
> wrote:
>
> > On 7 May 2018, at 10:49pm, Bart Smissaert 
> > wrote:
> >
> > > Using B4A for a SQLite database app on an Android phone.
> > > B4A doesn't have functions like sqlite3_column_decltype and
> > > sqlite3_column_type
> > > and this is causing some difficulty getting the column datatypes of a
> > row producing statement.
> >
> > See page 30 of the Basic4android manual, downloadable from
> >
> > 
> >
> > The general idea is to execute a query like
> >
> > SELECT ID, Name, Age, Height, typeof(Name), typeof(Age),
> > typeof(Height) FROM Table1
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Map a field type to a best suited sqlite3_result_* call

[J Decker]

On Sat, May 5, 2018 at 4:08 AM, Max Vlasov <max.vla...@gmail.com> wrote:

> On Sat, May 5, 2018 at 1:19 PM, J Decker <d3c...@gmail.com> wrote:
>
> > https://www.sqlite.org/c3ref/column_blob.html
> >
> > The sqlite3_column_type() routine returns the datatype code
> >
>
>
> Column api is not suitable for me since it relies on the actual data
> provided, but I'm the one who calls sqlite3_result_* to provide this.
>

https://www.sqlite.org/c3ref/value.html

sqlite_result_value the valuer type itself has the type.  This is the type
that would be returned by sqlite_column_types().

If you're serving the data, then you know what the data type is.  if you
don't you need to store it so you do; and this is outside of sqlite.



>
>
> >
> > PRAGMA table_info(table1);
> >
>
> The pragma just returns the type part as it was provided by the virtual
> table module with schema data. No conversion or mapping is made. But it
> will help be to extract the type part correctly if the provided list
> comments for example .
>   [FieldA] /*This is fieldA */ TEXT, [FieldB] FLOAT
>
>
> Something also came up as a solution during the reading of this post. The
> query
>   Select typeof(cast('' as ))
> doesn't require any table so I just can replace  with a provided
> column type and get the best affinity.
> But for any unrecognized it will return "integer", but I'd rather have
> "text".
>
> Max
> ___
> 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] Map a field type to a best suited sqlite3_result_* call

[J Decker]

https://www.sqlite.org/c3ref/column_blob.html

The sqlite3_column_type() routine returns the datatype code for the initial
data type of the result column. The returned value is one of
SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL. The
return value of sqlite3_column_type() can be used to decide which of the
first six interface should be used to extract the column value. The value
returned by sqlite3_column_type() is only meaningful if no automatic type
conversions have occurred for the value in question. After a type
conversion, the result of calling sqlite3_column_type() is undefined,
though harmless. Future versions of SQLite may change the behavior of
sqlite3_column_type() following a type conversion.

-
First search...
https://stackoverflow.com/questions/11753871/getting-the-type-of-a-column-in-sqlite

PRAGMA table_info(table1);

will dump the table information, e.g.

cid|name |type|notnull |dflt_value |pk0
|id_fields_starring   |INTEGER |0   |   |11
|fields_descriptor_id |INTEGER |1   |   |02  |starring_id
|INTEGER |1   |   |03  |form_mandatory
|INTEGER |1   |1  |04  |form_visible |INTEGER |1
|1  |0

http://www.sqlite.org/datatype3.html

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.



On Sat, May 5, 2018 at 2:59 AM, Max Vlasov  wrote:

> Hi,
>
> What is the best way to map a field type as it is provided in create table
> statement to sqlite3_result_* function call?
>
> More specifically, I have a virtual table implementation where a raw table
> data provided and the corresponding field definition part ([FieldA] TEXT,
> [FieldB] FLOAT). When asked by sqlite to fetch the column data I'd like
> to convert the text representation of a column to the best "type" and make
> the best sqlite3_result_* call. So I need probably the same machinery as
> sqlite itself when it handles type affinity.
>
> I found a function in the sources, sqlite3AffinityType, that probably
> serves the goal, but it's private. Also, a suggestion to implement a
> similar functionality was made by Jay A. Kreibich is in the mail list
> (mentioning  sqlite3AffinityType)
>
> On Thu, Jul 8, 2010 at 12:23 PM, Jay A. Kreibich  wrote:
> >> Other suggestions?
> >
> >  This has nothing to do with WAL, but it might be nice to expose
> >  the logic that does SQL-type => SQLite-affinity mappings
> >  (i.e. sqlite3AffinityType()):
> >
> >int sqlite3_get_affinity( const char *type );
> >
> >  Takes a string that contains an SQL type.  Returns one of:
> >
> >  SQLITE_AFF_TEXT, SQLITE_AFF_NONE, SQLITE_AFF_NUMERIC,
> >  SQLITE_AFF_INTEGER, SQLITE_AFF_REAL.
>
> But I suppose sqlite3_get_affinity or similar function was never
> implemented.
> I know that I probably might mimic the sqlite machinery knowing it's not
> that complex, but it is always better to rely on the approved logic of
> sqlite itself.
>
> Thanks,
>
> Max
> ___
> 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] Yes - Getting SPAM from Using Mailing List

[J Decker]

https://productforums.google.com/forum/?utm_medium=email_source=footer#!msg/gmail/vaG8BpMPov0/JqRR4wk2CQAJ

On Sat, Apr 28, 2018 at 3:05 AM, J Decker <d3c...@gmail.com> wrote:

>
>
> On Sat, Apr 28, 2018 at 3:01 AM, Luuk <luu...@gmail.com> wrote:
>
>>
>> On 28-4-2018 11:26, J Decker wrote:
>> > On Sat, Apr 28, 2018 at 2:08 AM, Luuk <luu...@gmail.com> wrote:
>> >
>> >> On 28-4-2018 07:36, Luuk wrote:
>> >>> On 27-4-2018 20:57, Denis Burke wrote:
>> >>>> I know steps were taken to reduce it, but just confirming it is still
>> >> going
>> >>>> on today.
>> >>>> I do not see spam in this list.
>> >>>>
>> >>>> I think you are infected with a SPAM-generator ;(
>> >>>>
>> >>>> now i'm receiving SPAM too, but this has noting to do with this
>> mailling
>> >>>> list,, an example below (note the' Form:'!):
>> >>>>
>> > Right.  (I want some too)
>> >
>> >
>>
>> The easiest way to (remporarily?) solve this is to hit the 'Report spam'
>> button in de web-interface of gmail
>> (only works if you are reading via @gmail.com)
>>
>
> Yes, but then that spams the whole thread, and counts against sqlite, not
> the individual message.
>
>> ___
>> 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] Yes - Getting SPAM from Using Mailing List

[J Decker]

On Sat, Apr 28, 2018 at 3:01 AM, Luuk <luu...@gmail.com> wrote:

>
> On 28-4-2018 11:26, J Decker wrote:
> > On Sat, Apr 28, 2018 at 2:08 AM, Luuk <luu...@gmail.com> wrote:
> >
> >> On 28-4-2018 07:36, Luuk wrote:
> >>> On 27-4-2018 20:57, Denis Burke wrote:
> >>>> I know steps were taken to reduce it, but just confirming it is still
> >> going
> >>>> on today.
> >>>> I do not see spam in this list.
> >>>>
> >>>> I think you are infected with a SPAM-generator ;(
> >>>>
> >>>> now i'm receiving SPAM too, but this has noting to do with this
> mailling
> >>>> list,, an example below (note the' Form:'!):
> >>>>
> > Right.  (I want some too)
> >
> >
>
> The easiest way to (remporarily?) solve this is to hit the 'Report spam'
> button in de web-interface of gmail
> (only works if you are reading via @gmail.com)
>

Yes, but then that spams the whole thread, and counts against sqlite, not
the individual message.

> ___
> 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] Yes - Getting SPAM from Using Mailing List

[J Decker]

On Sat, Apr 28, 2018 at 2:08 AM, Luuk  wrote:

> On 28-4-2018 07:36, Luuk wrote:
> > On 27-4-2018 20:57, Denis Burke wrote:
> >> I know steps were taken to reduce it, but just confirming it is still
> going
> >> on today.
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > I do not see spam in this list.
> >
> > The main reason is, i only read messages that contain the text
> > '[sqlite]' in the subject.
> > It's one of the features of gmail to filter on such a thing.
> >
> I think you are infected with a SPAM-generator ;(
>
> now i'm receiving SPAM too, but this has noting to do with this mailling
> list,, an example below (note the' Form:'!):
>

Right.  (I want some too)


>
> --
>  Forwarded Message 
> Subject:Re: [sqlite] Yes - Getting SPAM from Using Mailing List
> Date:   Sat, 28 Apr 2018 02:40:08 -0600
> From:   Natosha Jacobs 
> Reply-To:   awareessi...@safenmeet.xyz
> To: luu...@gmail.com
>
>
>
> Cool  Luuk i'm 100 sure you and me we can have a good time together.
> Just trust me okey.. Am not an escort ok so plz dont think me like that.
>
> I just need you to prove you are not minor and safe.
>
> My friends also here with me while am writing you. If you can come now
> you can ride us both. Just give me a call. Here’s Verify to See my
> profile 
>
> Again I did attach pic of mine also. How is that?  Come and treat us in
> real plz. To prove to you that im real, your Name is Luuk Caver it  ,i
> hope now you believe that I’m real. 5+7 =12 am i right ??
>
>
>
> On Fri, 27 Apr 2018 at 11:36 PM, Luuk  > wrote:
> ___
> 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] Window functions

[J Decker]

What are 'window functions'?

If you mean GUI; that's really outside the scope of Sqlite; and whatever
environment you're in can provide your GUI;
https://www.npmjs.com/package/sack-gui for instance for Javascript(Node.js).

On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer  wrote:

> Hi,
>
> I'm sure this has been asked before, but are window functions on the
> roadmap? Is it the authors' experience that the implementation would
> significantly complicate sqlite? Just curious. Thanks so much for a
> fantastic library.
>
> Charlie
> ___
> 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] "Tagging" SQLite tables with attributes for introspection.

[J Decker]

You could make a extra table that has a foreign key on sqlite_master and
add extra attributes...
You could add comments  ( -- ) in the create statemtents; and parse those
back out ...


On Fri, Apr 20, 2018 at 3:52 PM, Randall Smith 
wrote:

> I'm writing some code to do processing of some (but not quite all) of the
> tables in my SQLite database.  I could make this a lot easier and more
> general purpose if I could somehow attach an attribute to the various
> tables that indicated whether that table should be processed.
>
> Other than, say, keying off something in the table name or something which
> is a pain because all the table names are already set in stone, is there
> any way good of doing this?  I was hoping there was something in, say, the
> sqlite_master table I could intentionally affect and then use later.  But
> nothing jumps out.  Maybe define some kind of bogus index for the table I
> could check for?  But I'm hoping there's something with more grace and
> beauty.
>
> Thanks for any ideas or suggestions.
>
> Randall.
>
> ___
> 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] SQLite3 - Search on text field with \0 binary data

[J Decker]

It's not that it's impossible.
https://github.com/mackyle/sqlite/compare/master...d3x0r:AllowInlineNulChars

Improves handling in TCL, sqlite shell, and passing complete composed SQL
strings to prepare...

Which is why I know if you don't use the broken C-flavored(tainted) SQL
String functions, storing and retrieiving STRINGS with NUL characters is
easily handled which is why I started with my first response of 'what
tool are you actually using?'

In the storage, both the length and data are stored. (have been since 3.0
or before)

On Thu, Apr 19, 2018 at 4:11 PM, J Decker <d3c...@gmail.com> wrote:

>
>
> On Thu, Apr 19, 2018 at 4:07 PM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
>
>>
>> And what makes you think a "javascript string" is a "C string"?  While
>> the "string" part may be the same, "javascript" certainly does not equal
>> "C".  Just like you do not have issues with embedded zero-bytes in "pascal
>> strings".  Note that "pascal" != "C" even though "string" == "string".
>>
>>
> by the same reasoning that you apply saying SQL strings are C strings.
>
>
>> Note that the sqlite3_value_text returns the data (including embedded
>> zero bytes), but not the length.
>
>
> sqlite3_value_bytes() returns the number of bytes.
>
> 
>
>> If you pass the data returned thereby to a function expecting a C string
>> (zero terminated), it will terminate at the first zero byte encountered.
>> If you retrieve the length and the data separately and construct
>> pascal-style strings and pass them to functions expecting "pascal" style
>> strings, then the embedded zero is just "string data" (NB:  pascal is used
>> only as an example -- many X strings contain an embedded length for any
>> given value of X -- C strings do not).
>>
>> 
>
> Obviously "javascript" strings contain a length indicator and are not
>> zero-terminated.
>>
>> ---
>> 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 J Decker
>> >Sent: Thursday, 19 April, 2018 16:41
>> >To: SQLite mailing list
>> >Subject: Re: [sqlite] SQLite3 - Search on text field with \0 binary
>> >data
>> >
>> >On Thu, Apr 19, 2018 at 3:37 PM, J Decker <d3c...@gmail.com> wrote:
>> >
>> >>
>> >>
>> >> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf
>> ><kmedc...@dessus.com>
>> >> wrote:
>> >>
>> >>>
>> >>> Actually, nothing in the C or C++ world will "go past" the NULL
>> >byte
>> >>> since the very definition of a C string is a "bunch-o-bytes that
>> >are
>> >>> non-zero followed by one that is".
>> >>>
>> >>> And sory for the double response; but if C/C++ couldn't handle a
>> >NUL
>> >character (the character is 1 L) then spidermonkey/chakra/V8 would
>> >have
>> >problems with NUL characters in javascript strings.  But it doesn't.
>> >Why
>> >is that?
>> >
>> >
>> >> that doesnt' mean you can use a custom token structure that
>> >contains both
>> >> the pointer and length of the data. (which it already has)
>> >> sure, using standard C api - strlen, etc sure... but sqlite uses a
>> >custom
>> >> function internally sqlite3stlren30  which can easily be extended
>> >to take
>> >> the length of the string; but wait, if it was saved, it wouldn't
>> >need to be
>> >> called, and a overall performance gain is created.
>> >>
>> >> the biggest problem is really the internal function
>> >'(something)printf'
>> >> which returns a char *, and has no space to return the length, like
>> >> snprintf would.
>> >>
>> >> and I can easily put nuls into a string
>> >>
>> >> char buf[256];
>> >> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0
>> >);
>> >> and the length returned would be 27.
>> >>
>> >>
>> >>> If you want to embed non UTF8 text you should be using a BLOB not
>> >TEXT.
>> >>> Text means "an array of non-zero characters termina

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 4:07 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> And what makes you think a "javascript string" is a "C string"?  While the
> "string" part may be the same, "javascript" certainly does not equal "C".
> Just like you do not have issues with embedded zero-bytes in "pascal
> strings".  Note that "pascal" != "C" even though "string" == "string".
>
>
by the same reasoning that you apply saying SQL strings are C strings.


> Note that the sqlite3_value_text returns the data (including embedded zero
> bytes), but not the length.


sqlite3_value_bytes() returns the number of bytes.



> If you pass the data returned thereby to a function expecting a C string
> (zero terminated), it will terminate at the first zero byte encountered.
> If you retrieve the length and the data separately and construct
> pascal-style strings and pass them to functions expecting "pascal" style
> strings, then the embedded zero is just "string data" (NB:  pascal is used
> only as an example -- many X strings contain an embedded length for any
> given value of X -- C strings do not).
>
> 

Obviously "javascript" strings contain a length indicator and are not
> zero-terminated.
>
> ---
> 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 J Decker
> >Sent: Thursday, 19 April, 2018 16:41
> >To: SQLite mailing list
> >Subject: Re: [sqlite] SQLite3 - Search on text field with \0 binary
> >data
> >
> >On Thu, Apr 19, 2018 at 3:37 PM, J Decker <d3c...@gmail.com> wrote:
> >
> >>
> >>
> >> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf
> ><kmedc...@dessus.com>
> >> wrote:
> >>
> >>>
> >>> Actually, nothing in the C or C++ world will "go past" the NULL
> >byte
> >>> since the very definition of a C string is a "bunch-o-bytes that
> >are
> >>> non-zero followed by one that is".
> >>>
> >>> And sory for the double response; but if C/C++ couldn't handle a
> >NUL
> >character (the character is 1 L) then spidermonkey/chakra/V8 would
> >have
> >problems with NUL characters in javascript strings.  But it doesn't.
> >Why
> >is that?
> >
> >
> >> that doesnt' mean you can use a custom token structure that
> >contains both
> >> the pointer and length of the data. (which it already has)
> >> sure, using standard C api - strlen, etc sure... but sqlite uses a
> >custom
> >> function internally sqlite3stlren30  which can easily be extended
> >to take
> >> the length of the string; but wait, if it was saved, it wouldn't
> >need to be
> >> called, and a overall performance gain is created.
> >>
> >> the biggest problem is really the internal function
> >'(something)printf'
> >> which returns a char *, and has no space to return the length, like
> >> snprintf would.
> >>
> >> and I can easily put nuls into a string
> >>
> >> char buf[256];
> >> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0
> >);
> >> and the length returned would be 27.
> >>
> >>
> >>> If you want to embed non UTF8 text you should be using a BLOB not
> >TEXT.
> >>> Text means "an array of non-zero characters terminated by a zero
> >byte" and
> >>> a BLOB means a "bag-o-bytes" of a specific size.
> >>>
> >>> Blob means binary; havihng to deal with a binary structure to
> >convert to
> >> a string and back is ridiculous when the interface already supports
> >storing
> >> and getting strings with \0 in them.
> >>
> >>
> >>> Things meants to work on C "strings" should always stop at the
> >zero
> >>> terminator.  Failure to do so can lead to AHBL.
> >>>
> >>>
> >> So don't use the standard library.  That was one of the first
> >htings I
> >> created for my MUD client; a smart text string class.  (I say class
> >in the
> >> generic term, not the literal, since it was written in C)
> >>
> >>
> >>> (Note, this applies to "wide" (as in word) and "fat" (as in double
> >word)
> >

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 3:37 PM, J Decker <d3c...@gmail.com> wrote:

>
>
> On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
>
>>
>> Actually, nothing in the C or C++ world will "go past" the NULL byte
>> since the very definition of a C string is a "bunch-o-bytes that are
>> non-zero followed by one that is".
>>
>> And sory for the double response; but if C/C++ couldn't handle a NUL
character (the character is 1 L) then spidermonkey/chakra/V8 would have
problems with NUL characters in javascript strings.  But it doesn't.  Why
is that?


> that doesnt' mean you can use a custom token structure that contains both
> the pointer and length of the data. (which it already has)
> sure, using standard C api - strlen, etc sure... but sqlite uses a custom
> function internally sqlite3stlren30  which can easily be extended to take
> the length of the string; but wait, if it was saved, it wouldn't need to be
> called, and a overall performance gain is created.
>
> the biggest problem is really the internal function '(something)printf'
> which returns a char *, and has no space to return the length, like
> snprintf would.
>
> and I can easily put nuls into a string
>
> char buf[256];
> int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0 );
> and the length returned would be 27.
>
>
>> If you want to embed non UTF8 text you should be using a BLOB not TEXT.
>> Text means "an array of non-zero characters terminated by a zero byte" and
>> a BLOB means a "bag-o-bytes" of a specific size.
>>
>> Blob means binary; havihng to deal with a binary structure to convert to
> a string and back is ridiculous when the interface already supports storing
> and getting strings with \0 in them.
>
>
>> Things meants to work on C "strings" should always stop at the zero
>> terminator.  Failure to do so can lead to AHBL.
>>
>>
> So don't use the standard library.  That was one of the first htings I
> created for my MUD client; a smart text string class.  (I say class in the
> generic term, not the literal, since it was written in C)
>
>
>> (Note, this applies to "wide" (as in word) and "fat" (as in double word)
>> and obese (as in quad word) strings as well.  They are a sequence of
>> words/double-words/quad-words/ten-words (whatever) that are non-zero
>> followed by one that is zero -- and the narrow/wide/fat/obese string ends
>> at the zeo value).
>>
>>
> utf8everywhere.org
> No reason to use wide char.
>
>
> get good, son. (sorry if that's overly offensive)
>
> ---
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 3:22 PM, Keith Medcalf  wrote:

>
> Actually, nothing in the C or C++ world will "go past" the NULL byte since
> the very definition of a C string is a "bunch-o-bytes that are non-zero
> followed by one that is".
>
> that doesnt' mean you can use a custom token structure that contains both
the pointer and length of the data. (which it already has)
sure, using standard C api - strlen, etc sure... but sqlite uses a custom
function internally sqlite3stlren30  which can easily be extended to take
the length of the string; but wait, if it was saved, it wouldn't need to be
called, and a overall performance gain is created.

the biggest problem is really the internal function '(something)printf'
which returns a char *, and has no space to return the length, like
snprintf would.

and I can easily put nuls into a string

char buf[256];
int len = snprintf( buf, 256, "put a nul %c here and here %c", 0, 0 );
and the length returned would be 27.


> If you want to embed non UTF8 text you should be using a BLOB not TEXT.
> Text means "an array of non-zero characters terminated by a zero byte" and
> a BLOB means a "bag-o-bytes" of a specific size.
>
> Blob means binary; havihng to deal with a binary structure to convert to a
string and back is ridiculous when the interface already supports storing
and getting strings with \0 in them.


> Things meants to work on C "strings" should always stop at the zero
> terminator.  Failure to do so can lead to AHBL.
>
>
So don't use the standard library.  That was one of the first htings I
created for my MUD client; a smart text string class.  (I say class in the
generic term, not the literal, since it was written in C)


> (Note, this applies to "wide" (as in word) and "fat" (as in double word)
> and obese (as in quad word) strings as well.  They are a sequence of
> words/double-words/quad-words/ten-words (whatever) that are non-zero
> followed by one that is zero -- and the narrow/wide/fat/obese string ends
> at the zeo value).
>
>
utf8everywhere.org
No reason to use wide char.


get good, son. (sorry if that's overly offensive)

---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 - Search on text field with \0 binary data

[J Decker]

On Thu, Apr 19, 2018 at 1:49 PM, David Raymond 
wrote:

> After a little testing, of the core functions:
>
> Affected by \x00:
> substr
> like
> length
> quote
> replace when you're trying to replace the \x00
>
> Not affected by \x00:
> plain ol select
> instr
> lower
> upper
> trim
> ltrim
> rtrim
> replace when you're not replacing the \x00
> (works and replaces bits after the \x00 as well)
> ||
>
> At the moment I can't find anything in the documentation that covers this.
>
>
length has a note about terminating at nul.

I do know it's not an issue with the SQL standard; only implementations of
the standard.

The SQL standard is quite clear...

As mentioned here...
http://sqlite.1065341.n5.nabble.com/sqlite-command-line-tool-fails-to-dump-data-tp100196p100218.html




http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

spaces are used to separate syntactic elements. Multiple spaces and
 line breaks are treated as a single space. Apart from those symbols

 to which special functions were given above,
*other characters and  character strings in a formula stand for
themselves. In *addition,
 if the symbols to the right of the definition operator in a produc-

 tion consist entirely of BNF symbols, then those symbols stand for
 themselves and do not take on their special meaning.


For every portion of the string enclosed in square brackets,
either delete the brackets and their contents or change the
brackets to braces.


(from SQL 92) By this, I shouldn't also be able to use ~, `, Γειά σου Κόσμ,
Привет мир, or any other UNICODE character. (that is if you say things not
listed are " cannot contain embedded ;"


There is no specification that \0 means anything (in SQL).


So instead of like maybe you could use instr() instead?
> And it looks like messing with cast might do it to make substr work
>
> select cast(substr(cast(Data as blob), 11, 5) as text) from Record;
> select Data from Record where instr(Data, 'binar');
>
> Seems a little odd, as even when I declare the Data field as blob to begin
> with substr still doesn't work, but calling on the cast value does.
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of MARCHAND Loïc
> Sent: Thursday, April 19, 2018 8:27 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] SQLite3 - Search on text field with \0 binary data
>
> I index a file in a SQLite DB.
> I create my table with this:
> CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))
>
> I read a file, and for each line I add a row on the table. Each line can
> have binary data at end. It's not a problem for many chars, but \0 char
> make a problem.
> If I have a line like this : "My data \0with binary".
> When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5)
> FROM Record return an empty string or SELECT substr(Data, 4, 10) FROM
> Record return data)
>
> When I try to search a data (SELECT Data FROM Record WHERE Data LIKE
> '%binar%') return 0 rows returned.
>
> How can I solve this problem ? I try to replace \0 by an other char
> sequence, but it's not a good idea because can I have this sequence in my
> file.
> Thank you
> Loïc
> ___
> 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] SQLite3 - Search on text field with \0 binary data

[J Decker]

are you testing this in the sqlite3 shell or in your own program?
You should have no issue getting thisdata,

sqlite3_column_text followed by sqlite3_column_bytes  (the byte count is
set when the data is fetched in some format).

without modification, you can either excape \0 with'||char(0)||'...

"My data \0with binary".
"My data "||char(0)||" with binary".

or use a parameter binding to get the value stored in the database.

The row with a nul should be stored in the database; but the command line
shell will not return the right values for selects; it will truncate that
returned values at the \0.


On Thu, Apr 19, 2018 at 5:27 AM, MARCHAND Loïc 
wrote:

> I index a file in a SQLite DB.
> I create my table with this:
> CREATE TABLE Record (RecordID INTEGER,Data TEXT,PRIMARY KEY (RecordID))
>
> I read a file, and for each line I add a row on the table. Each line can
> have binary data at end. It's not a problem for many chars, but \0 char
> make a problem.
> If I have a line like this : "My data \0with binary".
> When I try to get data after the \0 not worked (SELECT substr(Data, 11, 5)
> FROM Record return an empty string or SELECT substr(Data, 4, 10) FROM
> Record return data)
>
> When I try to search a data (SELECT Data FROM Record WHERE Data LIKE
> '%binar%') return 0 rows returned.
>
> How can I solve this problem ? I try to replace \0 by an other char
> sequence, but it's not a good idea because can I have this sequence in my
> file.
> Thank you
> Loïc
> ___
> 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] Are you getting spam when you post to sqlite-users?

[J Decker]

On Tue, Apr 17, 2018 at 3:11 PM, Rich Shepard 
wrote:

> On Tue, 17 Apr 2018, Simon Slavin wrote:
>
> Okay, that's enough. Thanks for the help, everyone.
>>
>
> Simon,
>
>   FWIW, I haven't seen any spam from this mail list. I do run my own MTA
> which aggressively rejects known spam. Over 20 years I doubt there have
> been
> more than a handfull from all mail lists to which I have been subscribed.
>
>
Just really wanted to get some huh? :)


> Rich
>
> ___
> 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] Are you getting spam when you post to sqlite-users ?

[J Decker]

having replied in this thread I got one.
(can the real simon slavin please stand up?)

On Tue, Apr 17, 2018 at 3:00 PM, Warren Young  wrote:

> On Apr 17, 2018, at 3:39 PM, Simon Slavin  wrote:
> >
> > Are you getting a new dating-spam each time you post to this list ?  If
> you are, please post a brief follow-up to this message.
>
> Yes.  My recent reply to the header corruption thread triggered one.
>
> This is not new: some spammer subscribes to the mailing list and responds
> for each new post.  It’s difficult to cope with because the messages
> usually go out with a different source email than the one subscribed to the
> mailing list.
>
> ___
> 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] Are you getting spam when you post to sqlite-users ?

[J Decker]

march 26, apr 7 I found one each day in 'spam'

On Tue, Apr 17, 2018 at 2:39 PM, Simon Slavin  wrote:

> Dear list-posters,
>
> Are you getting a new dating-spam each time you post to this list ?  If
> you are, please post a brief follow-up to this message.  Please do /not/
> include any details about the spam, its headers, or the person it's
> apparently from.  Just a "me too" until I say I have seen enough responses.
>
> I'm trying to work out if I personally am being targeted or whether it's
> being sent to everyone who posts here.
>
> 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] Constraints must be defined last?

[J Decker]

Is there something about SQL that requires constraints to follow all column
definitions?


sqlite> create table `option4_blobs` (`option_id` char(36) default '0',
CONSTRAINT `value_id` UNIQUE (`option_id`),`binary` blob, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
   ...> ;
Error: near "`binary`": syntax error

sqlite> create table `option4_blobs` (`option_id` char(36) default
'0',`binary` blob, CONSTRAINT `value_id` UNIQUE (`option_id`), FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
   ...> ;
-- works
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert with foreign keys enabled prevented in transaction

[J Decker]

 I was trying to debug this set of commands, which is actually run on
two
connections within the same process... but that detail apparently
doesn't matter.



PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
`value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where
type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

--SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map

(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Help using 'attach database' or Why is this an error?

[J Decker]

On Wed, Apr 11, 2018 at 7:54 AM, Peter Da Silva <
peter.dasi...@flightaware.com> wrote:

> Try this:
>
> attach database 'test.db' as con2;
>
ahh that makes sense.
I'll split this into a separte thread for the real issue...


>
> On 4/11/18, 9:51 AM, "sqlite-users on behalf of J Decker" <
> sqlite-users-boun...@mailinglists.sqlite.org on behalf of d3c...@gmail.com>
> wrote:
>
> When I attempt to use attach database I get an error about 'no such
> column'
>
>
> M:\sqlite3\sqlite3\build>sqlite3 test.db
>
> SQLite version 3.23.0 2018-04-02 11:04:16es
> Enter ".help" for usage hints.
> sqlite> attach database test.db as con2;
> Error: no such column: test.db
> sqlite>
>
>
> I was trying to debug this set of commands, which is actually run on
> two
> connections within the same process... (oh I had misspelled foreign as
> foriegn)
>
>
>
>
> PRAGMA foreign_keys=on;
>
> create table `option4_name` (`name_id` char(36) NOT NULL,`name`
> varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);
>
> create table `option4_map` (`option_id` char(36) NOT
> NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id`
> char(36)
> NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2`
> UNIQUE
> (`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
> REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE
> CASCADE,
> FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
> CASCADE ON DELETE CASCADE);
>
> create table `option4_values` (`option_id` char(36) default
> '0',`string`
> varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT
> `value_id`
> UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
> (`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
> DELETE CASCADE);select tbl_name,sql from sqlite_master where
> type='table'
> and
>
> BEGIN TRANSACTION;
>
> insert into option4_name (name_id,name) values(
> '82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );
>
> insert into option4_map
> (option_id,parent_option_id,name_id)values('--
> --','----
> ','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
> );
>
> #SQLITE ERROR:Result of prepare failed? foreign key mismatch -
> "option4_map" referencing "option4_name" at char 185[] in [insert into
> option4_map
> (option_id,parent_option_id,name_id)values('--
> --','----
> ','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
> )]
> ___
> 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] Help using 'attach database' or Why is this an error?

[J Decker]

When I attempt to use attach database I get an error about 'no such column'


M:\sqlite3\sqlite3\build>sqlite3 test.db

SQLite version 3.23.0 2018-04-02 11:04:16es
Enter ".help" for usage hints.
sqlite> attach database test.db as con2;
Error: no such column: test.db
sqlite>


I was trying to debug this set of commands, which is actually run on two
connections within the same process... (oh I had misspelled foreign as
foriegn)




PRAGMA foreign_keys=on;

create table `option4_name` (`name_id` char(36) NOT NULL,`name`
varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE);

create table `option4_map` (`option_id` char(36) NOT
NULL,`parent_option_id` char(36) NOT NULL default '0',`name_id` char(36)
NOT NULL default '0',`description` tinytext,CONSTRAINT `parent_key2` UNIQUE
(`parent_option_id`,`name_id`), FOREIGN KEY  (`parent_option_id`)
REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY  (`name_id`) REFERENCES `option4_name`(`name_id`)ON UPDATE
CASCADE ON DELETE CASCADE);

create table `option4_values` (`option_id` char(36) default '0',`string`
varchar(100) default NULL,`segment` int(11) default 0,CONSTRAINT `value_id`
UNIQUE (`option_id`,`segment`)ON CONFLICT IGNORE, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE);select tbl_name,sql from sqlite_master where type='table'
and

BEGIN TRANSACTION;

insert into option4_name (name_id,name) values(
'82093fa2-3d93-11e8-98aa-6e01a5d0577f','.' );

insert into option4_map
(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
);

#SQLITE ERROR:Result of prepare failed? foreign key mismatch -
"option4_map" referencing "option4_name" at char 185[] in [insert into
option4_map
(option_id,parent_option_id,name_id)values('----','----','82093fa2-3d93-11e8-98aa-6e01a5d0577f'
)]
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fossil Feature Request

[J Decker]

On Fri, Mar 30, 2018 at 12:46 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 3/30/18, J Decker <d3c...@gmail.com> wrote:
> >  Sqlite's Fossile browser can't link line numbers...
> >
> >
> > Add ability to link to lines of source...
> >
> >
> > was trying to share this as another reference for getting UTF8 characters
> > from strings
> >
> > #define READ_UTF8(zIn, zTerm, c)
> > https://www.sqlite.org/src/artifact/810fbfebe12359f1
>
> Just add the "ln=155-165" query parameter.  Like this:
>
>   https://www.sqlite.org/src/artifact/810fbfebe12359f1?ln=155-165
>
>
Good to know for future reference - but there's no line numbers shown in
the first place


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

[sqlite] Fossil Feature Request

[J Decker]

 Sqlite's Fossile browser can't link line numbers...


Add ability to link to lines of source...


was trying to share this as another reference for getting UTF8 characters
from strings

#define READ_UTF8(zIn, zTerm, c)
https://www.sqlite.org/src/artifact/810fbfebe12359f1

which is like line 155 and kinda hard to find... when sharing refernces
from github I can select a line to share directly.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] json question

[J Decker]

On Tue, Mar 27, 2018 at 8:22 PM, Ian Zimmerman  wrote:

> On 2018-03-27 19:26, Keith Medcalf wrote:
>
> > Actually, it is very simple and straightforward to implement
> > hierarchical structures using Relational Database Models.
>
> But not recursive structures (or to be more precise, recursive queries)
> which are the next very natural step.
>
> You can have a "parent" database and you can use it to answer queries
> like "all X, Y where X is a parent of Y", "all X, Y where X is a
> grandparent of Y", "all X, Y where X is a great-grandparent of Y" etc.
> All that with a single table.  But no amount of SQL mastery will allow
> you to answer "all X, Y where X is an ancestor of Y".
>


that's where CTE queries come in... something like

create table jsonMap(node_id,parent_id,fieldName, value );

WITH temp as(
SELECT * FROM jsonMap x
WHERE parent_id=X

UNION ALL

SELECT om.* FROM jsonMap om
INNER JOIN temp x ON om.parent_id = x.node_id
)
SELECT * FROM temp


> --
> Please don't Cc: me privately on mailing lists and Usenet,
> if you also post the followup to the list or newsgroup.
> To reply privately _only_ on Usenet and on broken lists
> which rewrite From, fetch the TXT record for no-use.mooo.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

[sqlite] Feat Req Get Aliased Table Name.

[J Decker]

https://sqlite.org/c3ref/column_database_name.html

Source Of Data In A Query Result
The names returned are the original un-aliased names of the database,
table, and column.


Okay.  and there's...

https://sqlite.org/c3ref/column_name.html

These routines return the name assigned to a particular column in the
result set of a SELECT  statement.

Which is aliased column names (if aliased)

But there's no way to get the source table aliased.  And I don't think
database ever gets a chance to be aliased?


SELECT a, sum(b), max(c) FROM t1 m GROUP BY a

There's no C API to get 'm' from the above.

Updated my patch to add the above through


+const char *sqlite3_column_table_alias_name(sqlite3_stmt*,int);
+const void *sqlite3_column_table_alias_name16(sqlite3_stmt*,int);

which is added with the top mentionend function groups behind
SQLITE_ENABLE_COLUMN_METADATA

table_alias_info.fossil.patch
https://drive.google.com/open?id=1c24qvtvS57ASJF5RfZxLJSsgI2FhOVk1

Visual diff.
https://github.com/d3x0r/sqlite3/compare/AddTableAliasForColumn?expand=1


It's not very large.  It just has to allocate one more element in the array
of column info allocated by METADATA flag and set it when it sets the
rest, and expose it with an API call.


Even if it's just taken as inspiration, please add something to expose the
alias.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding 3.23.0 (pending) and TRUE/FALSE

[J Decker]

On Fri, Mar 2, 2018 at 4:19 AM, Richard Hipp  wrote:

> On 3/2/18, Olivier Mascia  wrote:
> >
> > What values will be considered FALSE, and hence will TRUE be NOT FALSE or
> > equality to some other specific value?
>
> I have a note to provide additional documentation on this before the
> release.
>
> In short, a value is FALSE is, when converted into a floating point
> number via CAST(x AS REAL) it has a value of 0.0.
>
> It used to be (https://www.sqlite.org/src/info/36fae083b450e3af) that
> sometimes FALSE was determined by CAST(x AS INTEGER).  This was
> inconsistent.  Beginning with 3.23.0, FALSE is determined by CAST(x AS
> REAL) in all cases.
>
> So in other words, it used to be that 0.5 was sometimes considered
> TRUE and other times considered FALSE, depending on context.  It is
> now always considered TRUE.
>
> Strings are TRUE or FALSE depending on whether or not they evaluate to
> non-zero when converted.  '123xyz' is TRUE, but '0abc', 'abc', and ''
> are all FALSE.
>
>
> > Will insert into T values(FALSE) actually store integer 0 no matter
> column
> > affinity or will it follow affinity?
>
> No.  FALSE is merely an alias for 0.  Affinity still applies.  If the
> column is of type TEXT, then it will store '0', not 0.
>

is NULL false?


>
> --
> 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] sqlite problem - field names missing in select

[J Decker]

https://sqlite.org/c3ref/column_name.html
You can get the column names.

On Fri, Feb 23, 2018 at 4:46 AM, M  wrote:

> sqlite has an integral problem, field names cannot be selected each one
> inside a program, there is way to do it, but it is not straight and
> complicates the software programs, and makes the program complicated and
> not readble.
>
> when you try to do something with the selected fields/columns, a lot of
> code can be reduced, if only there where field names - the ability to work
> with them directly.
>
>
> ---
> This email has been checked for viruses by Avast antivirus software.
> https://www.avast.com/antivirus
>
> ___
> 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] printf() problem padding multi-byte UTF-8 code points

[J Decker]

On Mon, Feb 19, 2018 at 7:49 PM, petern  wrote:

> There are other uses for padding strings besides user reports.  Consider
> scalar representations of computations for example. Also:
>
> 1.There was no mention of user display formatting in Ralf's original
> report.  It was a bug report about missing inverse functionality for
> padding/trimming strings.
> 2.The proposed functions fully exist in the PostgreSQL archetype.  Is
> PostgreSQL wrong?
> 3. Why can't SQLite have the expected common static SQL functions for
> getting rapid development done without external tools?
> Is the goal to reduce SQL portability and increase development effort just
> to see some representative output results?
>
> I don't think anybody is trying to create production grade displays within
> SQL but being able to produce representative output and having the expected
> nucleus of built-in SQL functions (including canonical inverses) is still a
> sensible goal.
>
>
I agree completely; however, what ARE standard SQL functions?  I went
searching and found 4 different answers.  Wriiting common SQL is pretty
hard so I have always avoided any internal functions and forced the
application to do it

LTRIM( "", 4-LENGTH(whategver)) ||whatever would seem to do
the padding?

Could wish there was a public SQL standard readable... while I understand
they need to make money too; makes it hard to find reference material to
state well... according to 'The Standard' Sqlite is missing XXX.

Unfortunately earlier 3 databases with 3 different functions to do the same
thing, (SUBSTR is another?) it doesn't appear there is a desire for
concensus either


> On Mon, Feb 19, 2018 at 6:06 PM, Simon Slavin 
> wrote:
>
> > On 20 Feb 2018, at 1:38am, petern  wrote:
> >
> > > Yet even so, as Ralf pointed out, the PostgreSQL lpad() and rpad() fill
> > > with arbitrary string functionality would still be missing despite the
> > > checked in printf() being more directly equivalent to the PostgreSQL
> > > format() function.  First things first I suppose...
> > >
> > > PostgreSQL lpad() and rpad() documentation is here:
> > > https://www.postgresql.org/docs/9.5/static/functions-string.html
> >
> > The problem with string length and padding was pointed out upthread.
> > Padding strings to a length was useful in the days of fixed-width fonts.
> > We don't do that much these days.  And even if you could equip SQLite
> with
> > functions which did those things, to do it properly you'd need routines
> > which understood Unicode characters, combinations, accents and the sort
> of
> > diacritics used for Hebrew and Arabic vowels.  Without that, you fancy
> new
> > feature is just going to trigger hundreds of bug reports.
> >
> > String width functions now days take two parameters, the string (in some
> > flavour of Unicode) and a font descriptor (font, size, emphasis and other
> > options) and return the width of the string in points, taking into
> account
> > not only Unicode features but font features like kern hinting and
> > ligatures.  And you will find these features in your operating system.
> >
> > So please, folks, don't try to do this in a purposely tiny DBMS.  Do it
> > using OS calls, as the people who designed your OS intended.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

[J Decker]

On Mon, Feb 19, 2018 at 5:38 PM, petern  wrote:

> FYI.  See http://www.sqlite.org/src/timeline for the equivalent DRH
> checkins:  http://www.sqlite.org/src/info/c883c4d33f4cd722
> Hopefully that branch will make a forthcoming trunk merge.   [Printing
> explicit nul terminator by formatting an interesting twist.]
>
> @DRH
printf( "whatever%ctest", 0 ); should result with that character in the
string
int length = snprintf( buf, 256, "whatever%ctest", 0 );

length == 13 while yes, applying strlen to the same buffer will result in
only 8 as the length.


> Yet even so, as Ralf pointed out, the PostgreSQL lpad() and rpad() fill
> with arbitrary string functionality would still be missing despite the
> checked in printf() being more directly equivalent to the PostgreSQL
> format() function.  First things first I suppose...
>
> PostgreSQL lpad() and rpad() documentation is here:
> https://www.postgresql.org/docs/9.5/static/functions-string.html
>
> Peter
>
> On Mon, Feb 19, 2018 at 4:38 PM, Cezary H. Noweta 
> wrote:
>
> > Hello,
> >
> > On 2018-02-17 18:39, Ralf Junker wrote:
> >
> >> Example SQL:
> >>
> >> select
> >>length(printf ('%4s', 'abc')),
> >>length(printf ('%4s', 'äöü')),
> >>length(printf ('%-4s', 'abc')),
> >>length(printf ('%-4s', 'äöü'))
> >>
> >> Output is 4, 3, 4, 3. Padding seems to take into account UTF-8 bytes
> >> instead of UTF-8 code points.
> >>
> >> Should padding not work on code points and output 4 in all cases as
> >> requested?
> >>
> >
> > If you are interested in a patch extending a functionality of
> ``printf()''
> > then http://sqlite.chncc.eu/utf8printf/. Adding ``l'' length modifier
> > makes width/precision specifications being treated as numbers of UTF-8
> > chars -- not bytes. ``SELECT length(printf ('%4ls', 'äöü'));'' will give
> 4.
> >
> > -- 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

[J Decker]

On Mon, Feb 19, 2018 at 2:54 AM, Ralf Junker  wrote:

> On 19.02.2018 09:50, Rowan Worth wrote:
>
> What is your expected answer for:
>>
>> select length(printf ('%4s', 'です'))
>>
>
> 'です' are 2 codepoints according to
>
>   http://www.fontspace.com/unicode/analyzer/?q=%E3%81%A7%E3%81%99
>
> The requested overall width is 4, so I would expect expect two added
> spaces and a total length of 4.
>
> Ralf
>
> PS: SQLite3 returns 2, which is less than the requested width.


Okay; but the functions in other databases weren't printf.  Because it is a
mimic of the C function of the same name, I would expect the count to be
bytes...
(v)(s)(n)printf, sscanf unfortunatly don't know rune like Go.
Although fprintf, I might expect to understand locale and UTF8 or other
wide encodings when writing to a fopen( ..., 't' ) type file... (probably
not even then though, since I think fprintf is vsnprintf to a buffer which
is then passed to fwrite or fputs which then it's probably bytes.

Changing the function is bound to break things, and it wouldn't be a small
task to reimplement a C library as utf8.

the SQL functions (that are not C emulations) do work in codepoints and not
bytes (for the most part; they break unnecessarily on NUL characters, which
is non SQL compliant ).

Could make a function to do the same job, but correctly; but even so; you'd
have to find a utf8 printf;
https://stackoverflow.com/questions/9325487/looking-for-utf8-aware-formatting-functions-like-printf-etc
not a lot of help; but maybe worth mentioning
" Just a warning, counting "characters" in Unicode data is quite a
complicated business. Besides the fact that each code point in UTF-8 is
composed of several bytes, each glyph (or "grapheme") can be composed of
several code points, and for that reason fwprintf is inadequate for
truncating Unicode data anyway -- for example you could cut off an accent
without cutting off the character it applies to. So whatever you end up
using, make sure that the meaning of the length you specify is clear to you.
 – Steve Jessop  Feb 17
'12 at 9:20

 "
"
"
"

I'm not finding anything; everyone recommends using different ways to do it
( use a unicode library, which doesn't have a printf) or do it in another
language - use String type or something









>
> ___
> 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] printf() problem padding multi-byte UTF-8 code points

[J Decker]

On Mon, Feb 19, 2018 at 3:21 AM, Cezary H. Noweta 
wrote:

> Hello,
>
> On 2018-02-18 00:36, Richard Hipp wrote:
>
>> The current behavior of the printf() function in SQLite, goofy though
>> it may be, exactly mirrors the behavior of the printf() C function in
>> the standard library in this regard.
>>
>
> So I'm not sure whether or not this is something that ought to be "fixed".
>>
>
> For the sake of sanity, such exception would be considered. I.e.
> ``length'' specification could mean number of ``multibyte characters'' --
> not ``characters''. A C programmer has a chance to put all his buffer,
> especially that there are no special provisions on multibyte characters in
> the buffer (i.e. it must not begin nor end with an initial shift state):
> for ( i = 0; len > i; i += 5 ) printf("%-5.5s", [i]); -- a bit non-sense
> but illustrates the problem.
>
> On the other hand, SQLite's SQL has no access to memory buffers. In such
> case, the C standard handles the situation (look at the end of ``s''
> conversion specifier together with ``l'' flag): ``In no case is a partial
> multibyte character written.''.
>
> Is there somebody who things about a byte content of buffers, when he is
> writing a software at a SQL level?

everyone dealing with padding/precision using printf() ?


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