Re: [sqlite] Custom aggregate functions in Tcl

I read some more about window functions and now see more clearly that they are an extension to aggregate functions. Now I understand why it makes sense to have a method name for both aggregate and window functions. I'll also go ahead and put window function support in my code next chance I get,

Re: [sqlite] "Pickling" an in-memory SQLite database.

If sqlite3_serialize and sqlite3_deserialize are not exposed in your bindings, I suppose you can do something like with NamedTemporaryFile(mode='rb', prefix=prefix) as tmp: c = sqlite3.connect(tmp.name) # Do stuff. c.close() serialized = tmp.file.read() where "prefix" is a RAM

Re: [sqlite] "Pickling" an in-memory SQLite database.

sqlite3_serialize() and deserialize would work: https://www.sqlite.org/c3ref/serialize.html On Thu, Jan 31, 2019, 4:47 PM Randall Smith Hi, guys. > > I have an application that allows me to embed a (potentially large) block > of data into its application file at close, and read the block back on

[sqlite] "Pickling" an in-memory SQLite database.

Hi, guys. I have an application that allows me to embed a (potentially large) block of data into its application file at close, and read the block back on open. It would be convenient and attractive for me, for a plugin I am writing for this application, to be able to use an in-memory SQLite

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

On 31 Jan 2019, at 6:21pm, Scott wrote: > Figured it out! I had set the column Deleted to "CHAR" but all the fields > without 'X' were null. If I replaced null with a valid character it worked. > Thanks for your time. Ah, JOINing on NULL. Well done. For future reference, SQLite doesn't have

Re: [sqlite] Displaying hierarchical structure

On 31/01/2019 17:59, Bart Smissaert wrote: Thanks, will try that. order by PATH So, where is this path coming from? Simple, from a discrepancy between the script I have tested and the contents of this mail! Here is the complete (tested) script: create table CLOSURE(PARENT_ID integer,ID

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

Figured it out! I had set the column Deleted to "CHAR" but all the fields without 'X' were null. If I replaced null with a valid character it worked. Thanks for your time. Scott ValleryEcclesiastes 4:9-10 On Thursday, January 31, 2019, 12:46:34 PM EST, Scott wrote: I can return

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

On 31 Jan 2019, at 5:46pm, Scott wrote: > I can return results successfully from the t.Topic and n.Deleted columns > separately, but when I try to use AND I receive no results. There was an optimization bug that looked like your example in some previous version of SQLite. Are you running an

[sqlite] How to do I get an 'AND' condition to work in an SQLite query?

I can return results successfully from the t.Topic and n.Deleted columns separately, but when I try to use AND I receive no results. I'm not sure what I may be doing wrong. This is my first exhaustive work with a database project, so I've had to learn some syntax along the way, but has me

Re: [sqlite] Displaying hierarchical structure

Thanks, will try that. > order by PATH So, where is this path coming from? RBS On Thu, Jan 31, 2019 at 4:08 PM Jean-Luc Hainaut wrote: > Recursive CTEs are the most obvious technique to solve this kind of > problems. > However, a less known technique can do the job: recursive triggers. > Here

Re: [sqlite] Displaying hierarchical structure

Recursive CTEs are the most obvious technique to solve this kind of problems. However, a less known technique can do the job: recursive triggers. Here is how the closure of FOLDERS can be computed. It will be stored in table CLOSURE: create table CLOSURE(PARENT_ID integer, ID integer,

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

On Mon, Jan 28, 2019 at 9:26 AM Vladimir Barbu < vladimir.ba...@schneider-electric-dms.com> wrote: > This vulnerability has been addressed in SQLite 3.26.0. When could we > expect new version (official) of System.Data.SQLite which uses 3.26.0? > That would also make it much easier to use new

Re: [sqlite] Displaying hierarchical structure

Using the transitive_closure virtual table extension (closure.c) on your original question (my sqlite3 has everything built-in already, so no need to load the extension): Note though that the AVL tree generated by the closure extension is generated on the fly upon request and does not have a

Re: [sqlite] Displaying hierarchical structure

Thanks, second link regarding the extension looks interesting. RBS On Thu, Jan 31, 2019 at 8:32 AM Peter Johnson wrote: > some relevant links: > > http://dwhoman.com/blog/sql-transitive-closure.html > > >

Re: [sqlite] Displaying hierarchical structure

some relevant links: http://dwhoman.com/blog/sql-transitive-closure.html http://charlesleifer.com/blog/querying-tree-structures-in-sqlite-using-python-and-the-transitive-closure-extension/ On Wed, 30 Jan 2019, 4:52 AM Bart Smissaert Working on an Android app and part of that is storing SQL in

Re: [sqlite] Displaying hierarchical structure

This looks a nice and simple way to display the tree in the right order without recursive SQL: https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql Will do some testing on large numbers to see how the 2 methods compare speed-wise. RBS On Tue, Jan 29, 2019 at 8:33 PM Keith