Re: [sqlite] Row count of a partial index ?

[Richard Hipp]

On 5/5/18, Deon Brewis  wrote:
> Is there a direct way to query how many rows are in a partial index?
>
> I know I can repeat the WHERE clause expression in the query, but this is
> for a dynamically constructed index and knowing what the current "where"
> clause is on the index has some race conditions attached. Solvable... but if
> there is a direct way, I'd prefer that. Otherwise no big deal.

There is some unsupported trickery involving SQLITE_TESTCTRL_IMPOSTER,
but extracting the WHERE clause from the sqlite_master table seems
safer and easier.
-- 
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] Row count of a partial index ?

[Deon Brewis]

Is there a direct way to query how many rows are in a partial index?

e.g.

select count(*) from table indexed by partial_index

(this does not work - no query solution).


I know I can repeat the WHERE clause expression in the query, but this is for a 
dynamically constructed index and knowing what the current "where" clause is on 
the index has some race conditions attached. Solvable... but if there is a 
direct way, I'd prefer that. Otherwise no big deal.

(I know I can also ANALYZE and check sqlite_stat1 after, but that's not really 
a solution...)

- Deon

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

Re: [sqlite] Always call a value-quoting routine

[José María Mateos]

On Sat, May 05, 2018 at 11:57:22PM +0100, Simon Slavin wrote:
> This is a genuine company registered under the UK Companies Act:
> 
> 
> 
> The name of company is
> 
> ; DROP TABLE "COMPANIES";-- LTD

Obligatory: https://xkcd.com/327/

Cheers, 

-- 
José María (Chema) Mateos
https://rinzewind.org/blog-es || https://rinzewind.org/blog-en
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Always call a value-quoting routine

[Scott Robison]

Thanks for sharing that. It will undoubtedly be useful to me in a computer
security class I'm taking this semester.

On Sat, May 5, 2018, 4:57 PM Simon Slavin  wrote:

> This is a genuine company registered under the UK Companies Act:
>
> 
>
> The name of company is
>
> ; DROP TABLE "COMPANIES";-- LTD
>
> (Note: For legal reasons a UK company name must end in 'LTD' or 'plc',
> depending on the type of company it is.)
>
> 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] Always call a value-quoting routine

[Simon Slavin]

This is a genuine company registered under the UK Companies Act:



The name of company is

; DROP TABLE "COMPANIES";-- LTD

(Note: For legal reasons a UK company name must end in 'LTD' or 'plc', 
depending on the type of company it is.)

Simon.
___
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  wrote:

> On Sat, May 5, 2018 at 1:19 PM, J Decker  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

[Max Vlasov]

On Sat, May 5, 2018 at 1:19 PM, J Decker  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.


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

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

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

[Max Vlasov]

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