[sqlite] Documentation Bug

The documentation at https://sqlite.org/quirks.html#no_separate_datetime_datatype refers to ISO 8610. I should be ISO 8601. Best Regards, Mark -- Mark Simon Manngo Net Pty Ltd mobile:0411 246 672 email:m...@manngo.net web:http://www.manngo.net

Re: [sqlite] Limit on number of columns in SQLite table

""Hi! On Thu, Oct 17, 2019 at 5:38 PM Jens Alfke wrote: > Why should SQLite make changes, which would introduce performance problems if > used, just to save your particular application the trouble of concatenating > some vectors into single columns, when it uses SQLite for an edge use-case >

Re: [sqlite] Limit on number of columns in SQLite table

—Jens > On Oct 17, 2019, at 1:56 AM, Mitar wrote: > > So why not increase the limit to 2 billion Why should SQLite make changes, which would introduce performance problems if used, just to save your particular application the trouble of concatenating some vectors into single columns, when

Re: [sqlite] Limit on number of columns in SQLite table

Hi! Oh, or we could just split CSV into separate lines, and then just store one line per SQLite row, into one column. Then we do not have to use JSON or something. That would work for CSV files. For other types of inputs we might be able to find some other similar approach. So generally the

Re: [sqlite] Limit on number of columns in SQLite table

So if character-separated values (CSV-ish) were originally your preferred import format, would using that format for the blob's work for you? E.g., Suppose you need to index the first two fields only, and so can use a blob column for the bulk of the record. If the records were supplied as:

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

Hi! Thanks for this input. So you are saying that sqlite3_column 100k times per row is slow, but retrieving 100k rows to construct one "original" row will be faster? So not sure if I understand why reading and decoding cells in over multiple columns is so much slower than reading and decoding

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

I have the impression that you still do not grasp the folly of a 100k column schema. See the example below, which only has 6 fields. As you can see, each field requires a Column opcode and arguments (about 10 bytes) and a "register" to hold the value (48 bytes), which for 100k columns uses

Re: [sqlite] Limit on number of columns in SQLite table

Hi! On Thu, Oct 17, 2019 at 3:04 PM Eric Grange wrote: > my suggestion would be to store them as JSON in a blob, and use the JSON > functions of SQLite to extract the data JSON has some crazy limitations like by standard it does not support full floating point spec, so NaN and infinity cannot

Re: [sqlite] Limit on number of columns in SQLite table

> I wrote earlier that for us use case where we are reading whole rows is the most common one. > [...] > we are looking for ways to store this in a stable format which will be supported for next 50 years, without modifying to original data too much. If you do not need access to individual columns

Re: [sqlite] Limit on number of columns in SQLite table

Hi! This is getting a bit off topic. On Thu, Oct 17, 2019 at 12:07 PM Simon Slavin wrote: > 1) Almost no piece of software can handle a grid 2 billion cells wide. Excel > maxes out at 16,384 columns. Matlab can store and retrieve a cell of data > directly from a file, but it has a max array

Re: [sqlite] Network file system that support sqlite3 well

On Oct 17, 2019, at 4:33 AM, Warren Young wrote: > > $ echo 'select count(*) from MyTable where rowid > 1234' | > nc dbserver Clarification: that echo line should begin with “Query: “ and end in a semicolon, which is a short form method of querying a Bedrock DB over the network. See

Re: [sqlite] Network file system that support sqlite3 well

On Oct 16, 2019, at 11:42 PM, Peng Yu wrote: > > Suppose A is a sqlite3 db file, B is some other file which is > generated based on the content in A. When A is not changed, there is > no need to change B, otherwise, B will need to be regenerated. In > other words, B depends on A, and can be

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Tested all on the Windows app and works perfectly fine indeed. Thanks for that. RBS On Thu, Oct 17, 2019 at 12:56 AM Olaf Schmidt wrote: > Am 12.10.2019 um 16:47 schrieb Bart Smissaert: > > Sorry, I forgot to tell that. It is date column with an integer number. > > > > ID xValue

Re: [sqlite] Excel Pivot Table

On 17 Oct 2019, at 12:11am, Harris, Dennis wrote: > I sure this has been asked a 100 times but what is the best practice to get > data from SQLITE to Excel? Use CSV file format as an interchange format. Save from SQLite as as a .csv file, and import that file into Excel. If you don't want

Re: [sqlite] Standard deviation last x entries

Ignore the mentioned problem, must have been an e-mail artefact and view creates fine. Will test all later. RBS On Thu, Oct 17, 2019 at 11:02 AM Bart Smissaert wrote: > Hi Olaf, > > Thanks, will try that out. > I have a slight problem with your first create view on Android (fine in my >

Re: [sqlite] Limit on number of columns in SQLite table

On 17 Oct 2019, at 9:56am, Mitar wrote: > I can understand how supporting a large number of columns might be > inappropriate when you want to run complicated SQL queries on data, > but to just store data and then extract all rows to do some data > processing, Or as the most complicated query it

Re: [sqlite] Standard deviation last x entries

Hi Olaf, Thanks, will try that out. I have a slight problem with your first create view on Android (fine in my Windows desktop app), objecting to: T.ID, highlighting the dot. I tried with giving an alias T2 to the table in the subselect, but that made no difference. Will try later. RBS On Thu,

Re: [sqlite] Opposite of SQLite

Someone asked: What the opposite of "Lite”? It has to be SQFullCream! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

Hi! In that case we would have to define a standard BLOB storage format, slightly defeating the idea of using SQLite to define such standard future-proof format. :-) Mitar On Thu, Oct 17, 2019 at 11:19 AM Hick Gunter wrote: > > Since your data is at least mostly opaque in the sense that

Re: [sqlite] [EXTERNAL] Re: Limit on number of columns in SQLite table

Since your data is at least mostly opaque in the sense that SQLite is not expected to interpret the contents, why not split your data into "stuff you want to query ins SQLite" and "stuff you want to just store"? The former means individual columns, whereas the latter could be stored in a single

Re: [sqlite] Network file system that support sqlite3 well

On 17/10/2019 16:42, Peng Yu wrote: I will need to use the actual files to test for dependency (just as the dependency that can be used by GNU make) I don’t understand what that means. You want to use a makefile that checks the mod date of the database? Suppose A is a sqlite3 db file, B is

[sqlite] Excel Pivot Table

I sure this has been asked a 100 times but what is the best practice to get data from SQLITE to Excel? I would like to have a pivot table that updates upon open. Several people will use the file so I will save in a shared location. Thanks in advance for any advice you can give me Dennis .

Re: [sqlite] Limit on number of columns in SQLite table

Hi! I can see how this is a reasonable limit when SQLite is used for querying power it provides. In our case we are really focusing on it as a standard long-term storage format. So in the "Appropriate Uses For SQLite" document [1] you have a section called "File archive and/or data container" and

Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

That would be falling off the other side of the horse. Neither column nor table names are supposed to be derived from data items, but rather from abstract categories. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von J

Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

The I suggest normalizing the schema. Table genes (gene_id integer primary key, name char) Table variant (variant_id integer primary key, gene_id integer, name char) Table sample (sample_id integer primary key, name char, ...); Table sample_gene_variant (sample_id integer, gene_id integer,

Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

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