Re: [sqlite] How do I combine these to 2 views ...

On 26 Nov 2017, at 8:02am, Shane Dev wrote: > Any ideas to achieve this? Use the UNION keyword to combine the results of the two SELECT commands: Simon. ___ sqlite-users mailing

[sqlite] Checkpointing (PASSIVE) at every close() - Was: Get wal page count out of context of wal_hook?

There is some other way to look at my original question. Or to cast some light on it. SQLite does a full checkpoint on the last attachment close to a database file, actually checkpointing whatever left in the wal file and finally removing the wal/shm files. I have implemented a PASSIVE

Re: [sqlite] Simple read-only program very slow

On 26 Nov 2017, at 3:13am, Kevin O'Gorman wrote: > > I've got a database of some 100 million records, and a file of just over > 300 thousand that I want represented in it. I wanted to check how much > difference it was going to make, so I wrote a super > simple

Re: [sqlite] "BIGINT" doesn't act like INTEGER on table creation [Bug]

The one thing that saddens me is that the clock is not the full first part of the UUID, so it's not a proxy for sorting by creation date. I often wonder why they did that, they must have done it on purpose. On 11/25/17, Peter Da Silva wrote: >>> What about time

[sqlite] How do I combine these to 2 views ...

Hello, I am try to combine the following 2 views - vtag and vparent_closetag sqlite> select id, level, line from vtag; id|lev|line id level line 1 0 2 1 3 1 4 2 5 1 6 2 7 3 8 2

Re: [sqlite] Simple read-only program very slow

Kevin O'Gorman wrote: > I wrote a super simple program to the read the file and count how many > records are already there. I got impatient waiting for it so I killed > the process and added an output of one dot (".") per 1000 records. It > went very fast for what I estimate was around 200 dots

Re: [sqlite] How do I combine these to 2 views ...

Simon Slavin wrote: > On 26 Nov 2017, at 8:02am, Shane Dev wrote: >> Any ideas to achieve this? > > Use the UNION keyword to combine the results of the two SELECT commands That would not order the close tags correctly. >> Any ideas to achieve this? Would it be possible to

Re: [sqlite] How do I combine these to 2 views ...

Shane Dev wrote: > Any ideas to achieve this? Use another CTE to bring all rows into the correct order. Then a standard tree walk: WITH RECURSIVE data AS ( SELECT id, line, par, 0 AS close FROM vtag UNION ALL SELECT id, line, par, 1 FROM vparent_closetag ORDER BY id, close --

Re: [sqlite] How do I combine these to 2 views ...

On 11/26/2017 3:02 AM, Shane Dev wrote: Hello, I am try to combine the following 2 views - vtag and vparent_closetag sqlite> select id, level, line from vtag; id|lev|line id level line 1 0 2 1 3 1 4 2 5 1 6 2

Re: [sqlite] Getting an advance list of RowIDs for a query result set

Thanks Keith. This one Update the omit-table-from-left-join optimization so that it can omit tables from the middle of the join as well as the end. Deals with the case I mentioned in my last post but this one is also interesting Fix a problem preventing the planner from identifying scans that

Re: [sqlite] How do I combine these to 2 views ...

Yes, the parent ID column (par) is available in both views - sqlite> select id, par, line from vtag; id par lev line 1 0 2 1 1 3 1 1 4 3 2 5 1 1 6 5 2 7 6

Re: [sqlite] Getting an advance list of RowIDs for a query result set

> If it’s joined to the BaseTbl by an integer primary key or FULLY joined by a > unique index then the table is redundant. I’m talking there. If it’s an inner join SQLite needs to check the record exists in the joined table. Sorry about that, back to left joins.

[sqlite] How to use sqlite3_table_column_metadata?

Having some difficulty using sqlite3_table_column_metadata, used from VB6. I keep getting a zero for the first output argument (should be pointer to declared data type string) and the return value of the function keeps giving 1. I read in Jay Kreibich's book: db_name A logical database name,

Re: [sqlite] Getting an advance list of RowIDs for a query result set

x wrote: I proceed as follows 1. Omit a table join from the SQL and try preparing it. 2. If it prepares OK then the table isn’t involved in the WHERE or ORDER BY. 3. If it’s joined to the BaseTbl by an integer primary key or FULLY joined by a unique index then the table is

Re: [sqlite] How to use sqlite3_table_column_metadata?

Not an answer to what is wrong with your call to get the metadata, however, the information is all available from SQL. Data Catalog Views: drop view SysIndexColumns; drop view SysIndexes; drop view SysColumns; drop view SysObjects; create view if not exists SysObjects as select type as

Re: [sqlite] How to use sqlite3_table_column_metadata?

Thanks, will try that out. I don't think I actually need the sqlite3_table_column_metadata, but just wanted to test it. RBS On Sun, Nov 26, 2017 at 9:41 PM, Keith Medcalf wrote: > Not an answer to what is wrong with your call to get the metadata, > however, the information

Re: [sqlite] How to use sqlite3_table_column_metadata?

All sorted now. This was indeed a coding error and nil to do with SQLite. RBS On Sun, Nov 26, 2017 at 7:56 PM, Clemens Ladisch wrote: > Bart Smissaert wrote: > > Having some difficulty using sqlite3_table_column_metadata, used from > VB6. > > > > I keep getting a zero for

Re: [sqlite] How do I combine these to 2 views ...

So simple in hindsight, just add a second sort column 'close' to the union and then traverse the tree - thanks. On 26 November 2017 at 15:44, Clemens Ladisch wrote: > Shane Dev wrote: > > Any ideas to achieve this? > > Use another CTE to bring all rows into the correct

Re: [sqlite] How to use sqlite3_table_column_metadata?

Bart Smissaert wrote: > Having some difficulty using sqlite3_table_column_metadata, used from VB6. > > I keep getting a zero for the first output argument (should be pointer to > declared data type string) > and the return value of the function keeps giving 1. The documentation

Re: [sqlite] How to use sqlite3_table_column_metadata?

I am passing a pointer to UTF encoded strings and the passed table and column do exist. There must be something wrong in my code. RBS On Sun, Nov 26, 2017 at 7:56 PM, Clemens Ladisch wrote: > Bart Smissaert wrote: > > Having some difficulty using

Re: [sqlite] How to use sqlite3_table_column_metadata?

I pasted that SQL into a SQLite shell and tried it out. First off, Affinity (aka pragma table_info.type) column is case collated, so the LIKE operator should be used: select O.* from SysColumns as O where ObjectType == 'table' and IsPrimaryKey == 1 and Affinity LIKE 'INTEGER' and

Re: [sqlite] How to use sqlite3_table_column_metadata?

On Sunday, 26 November, 2017 18:00, petern wrote: >First off, Affinity (aka pragma table_info.type) column is case >collated, so the LIKE operator should be used: Yes, but it is always lowercase. Just as the ObjectType (sqlite_master.type) is always lowercase.

[sqlite] sqlite3_get_auxdata() defect

Consider the simplest possible pair of argument metadata test functions as follows. -- /* Further to https://sqlite.org/c3ref/get_auxdata.html UDF auxint(X,I) - stores I plus the function argument X's current metadata INT and returns the new value. UDF auxint(X) - returns the current value