Re: [sqlite] Extension Loading

[Clemens Ladisch]

Green Fields wrote:
> conn.EnableExtensions(true);
> conn.LoadExtension("mod_spatialite");
>
> works fine, but I need to use the SQL function
>
> conn.EnableExtensions(true);
> cmd.CommandText = @"SELECT load_extension('mod_spatialite');");
> int i = cmd.ExecuteNonQuery();
>
> raises
>
> System.Data.SQLite.SQLiteException : SQL logic error or missing database
> not authorized

The documentation for sqlite3_enable_load_extension() says:
> This interface enables or disables both the C-API
> sqlite3_load_extension() and the SQL function load_extension(). Use
> sqlite3_db_config(db,SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION,..) to
> enable or disable only the C-API.


EnableExtensions() call sqlite3_db_config() and not
sqlite3_enable_load_extension().  Apparently, forbidding loading
extensions from SQL is done on purpose.


You could create a user-defined function to call LoadExtension(), but
why do you need to do this from SQL?


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

[sqlite] Extension Loading

[Green Fields]

Hi
I'm using System.Data.SQLite Nuget package and attempting to load an
extension.
All depenent libraries are in the bin\debug output directory.
Extension is 32 bit and the project is set to compile for 32 bit


Using

conn.EnableExtensions(true);(I think this is enabled by default)
conn.LoadExtension("mod_spatialite");

works fine, but I need to use the SQL function
 and

conn.EnableExtensions(true);
SQLiteCommand cmd = *conn.Create*Command();
cmd.CommandText = @"SELECT load_extension('mod_spatialite');");
int i = cmd.ExecuteNonQuery();

raises

System.Data.SQLite.SQLiteException : SQL logic error or missing database
not authorized

but I am able to successfully call the function in the SQLite CLI

Could someone point out where I'm going wrong with this syntax please

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

Re: [sqlite] Inserts silently failing

[Keith Medcalf]

On Sunday, 12 February, 2017 07:28, Brian Macy  said:

> Recently I removed a bunch of latency from my application.  Suddenly I’m
> getting odd errors where a record that was just inserted can’t be read by
> the same process.  

SQLite does not do "per process" locking/transactions -- it does 
locking/transactions/isolation per connection (handle returned by one of the 
sqlite_open* calls).  You are not "spawning" new processes using the same 
connection handle, are you?

> In fact, even waiting till completion of the run, the
> record never exists.  I confirmed the insert is getting called with the
> correct parameters, I logged everything coming back on the error log
> callback, and I verified that the DB is always in auto commit.

Again, autocommit is not per database (nor per process) but is per connection.  
Does the database connection close properly (return SQLITE_OK)?




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

Re: [sqlite] Inserts silently failing

[Simon Slavin]

On 12 Feb 2017, at 2:27pm, Brian Macy  wrote:

> Recently I removed a bunch of latency from my application.  Suddenly I’m 
> getting odd errors where a record that was just inserted can’t be read by the 
> same process. In fact, even waiting till completion of the run, the record 
> never exists.  I confirmed the insert is getting called with the correct 
> parameters, I logged everything coming back on the error log callback, and I 
> verified that the DB is always in auto commit.

Are you intentionally using transactions by executing BEGIN and COMMIT ?  Or is 
each row of each table entirely independent so it doesn’t matter ?

> What can I do to debug better?

Are you checking the values returned from each of the SQLite function calls to 
make sure they’re SQLITE_OK ?

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

[sqlite] Inserts silently failing

[Brian Macy]

I have an application where multiple engines perform heavy write OLTP 
transactions on a repository.  The engine supports many data stores and I 
recently added SQLite support.

WAL is great, for small single node stuff SQLite is very fast and simple.  I 
had turned of synchronous (=0), as for the use case, having to start over due 
to a corrupted database is not a big deal, but I have turned it back to normal 
mode which lessens the problem but doesn’t go away.  Due to the heavy writes I 
have a lot of contention (my busy handler works well) and I have to force 
checkpoints to keep the log under control.

Recently I removed a bunch of latency from my application.  Suddenly I’m 
getting odd errors where a record that was just inserted can’t be read by the 
same process.  In fact, even waiting till completion of the run, the record 
never exists.  I confirmed the insert is getting called with the correct 
parameters, I logged everything coming back on the error log callback, and I 
verified that the DB is always in auto commit.

What can I do to debug better?  Can checkpoint RESTART/TRUNCATE cause data loss?

Brian Macy

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

Re: [sqlite] pragma_table_info column name is interpreted as reserved work?

[James K. Lowden]

On Sun, 12 Feb 2017 12:40:27 -0700
"Keith Medcalf"  wrote:

> I am aware that it works if you quote the apparently reserved word
> "notnull".  

That's the queer part.  I can imagine "not" and "null" as keywords, but
not "nonnull".  

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

[sqlite] Minor bugs in documentation

[Aaron Zeng]

Hi,

I found the following bugs in the online documentation:


   - In http://sqlite.org/testing.html#test_harnesses, the link to "fuzz
   tests" has an incorrect href attribute, '#fuzztests' instead of #fuzztests
   (superfluous quotes, causes the link to go to the wrong place (404)).
   - In http://sqlite.org/c3ref/create_collation.html, the callback arg is
   referred to as xCallback in the prose but xCompare in the function
   prototypes.

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

Re: [sqlite] pragma_table_info column name is interpreted as reserved work?

[Keith Medcalf]

On Sunday, 12 February, 2017 11:03, Domingo Alvarez Duarte  
wrote:

> Hello Keith !
 
> You can try this that works:
 
> select m.name   as TableName,
> i.cidas ColumnID,
> i.name   as ColumnName,
> i.type   as Affinity,
> i."notnull"as CanBeNull,
> i.dflt_value as DefaultValue,
> i.pk as PrimaryKeySeq
>from sqlite_master as m,
> pragma_table_info(m.name) as i
>   where m.type='table';

Yes, I am aware that it works if you quote the apparently reserved word 
"notnull".  That is however ugly beyond belief and quoted identifiers make my 
eyes bleed (just like Perl :) ).  It would be nice if the default column name 
were not a reserved word ...
 
> On 12/02/17 15:24, Keith Medcalf wrote:
> > select m.name   as TableName,
> > i.cidas ColumnID,
> > i.name   as ColumnName,
> > i.type   as Affinity,
> > i.notnullas CanBeNull,
> > i.dflt_value as DefaultValue,
> > i.pk as PrimaryKeySeq
> >from sqlite_master as m,
> > pragma_table_info(m.name) as i
> >   where m.type='table';
> 
> ___
> 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_table_info column name is interpreted as reserved work?

[Domingo Alvarez Duarte]

Hello Keith !

You can try this that works:

select m.name   as TableName,
   i.cidas ColumnID,
   i.name   as ColumnName,
   i.type   as Affinity,
   i."notnull"as CanBeNull,
   i.dflt_value as DefaultValue,
   i.pk as PrimaryKeySeq
  from sqlite_master as m,
   pragma_table_info(m.name) as i
 where m.type='table';


On 12/02/17 15:24, Keith Medcalf wrote:

select m.name   as TableName,
i.cidas ColumnID,
i.name   as ColumnName,
i.type   as Affinity,
i.notnullas CanBeNull,
i.dflt_value as DefaultValue,
i.pk as PrimaryKeySeq
   from sqlite_master as m,
pragma_table_info(m.name) as i
  where m.type='table';


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

[sqlite] pragma_table_info column name is interpreted as reserved work?

[Keith Medcalf]

select m.name   as TableName, 
   i.cidas ColumnID, 
   i.name   as ColumnName, 
   i.type   as Affinity, 
   i.notnullas CanBeNull, 
   i.dflt_value as DefaultValue, 
   i.pk as PrimaryKeySeq 
  from sqlite_master as m, 
   pragma_table_info(m.name) as i 
 where m.type='table';

returns Error: near "notnull": syntax error

however, quoting "notnull" works:

select m.name   as TableName, 
   i.cidas ColumnID, 
   i.name   as ColumnName, 
   i.type   as Affinity, 
   i."notnull"  as CanBeNull, 
   i.dflt_value as DefaultValue, 
   i.pk as PrimaryKeySeq 
  from sqlite_master as m, 
   pragma_table_info(m.name) as i 
 where m.type='table';

returns:

advisory|0|advisory|integer|0||1
advisory|1|advisoryname|text|1|'Unknown'|0
advlink|0|program|text|1||1
advlink|1|advisory|integer|1||2
crew|0|crew|integer|0||1
crew|1|surname|text|1|''|0
crew|2|givenname|text|1|''|0
crewlink|0|program|text|1||1
crewlink|1|crew|integer|1||3
crewlink|2|role|integer|1||2
...

Is there a way to make the "column names" returned by a pragma (eg, 
pragma_table_info) table not be interpreted as a reserved word?  While it is 
not a bug per-se, it is annoying to have to quote (what one would hope are) 
properly composed column names (even though there are many folks who habitually 
use column name quoting as a matter-of-course because many ill-conceived tools 
generate badly-formed column-names or quote wildly).  It one typically only 
used well-formed column names, then having to quote is downright ugly ...

---
Life should not be a journey to the grave with the intention of arriving safely 
in a pretty and well preserved body, but rather to skid in broadside in a cloud 
of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! 
What a Ride!"
 -- Hunter S. Thompson




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

[sqlite] INDEX list

[Rob van der sloot]

Thanks very much Niall, the SELECT DISTINCT column FROM table is doing
exactly what I was looking for.

The ORDER BY clause gives the whole list and that's not what I wanted.

Regards,
Rob

-- 


This email and any attachments to it may be confidential and are intended
solely for the use of the individual to whom it is addressed. Any views or
opinions expressed are solely those of the author and do not necessarily
represent those of the sender of this email.

If you are not the intended recipient of this email, you must neither take
any action based upon its contents, nor copy or show it to anyone.

Please contact the sender if you believe you have received this email in
error.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pragma table-valued functions used in views in an attached database.

[Simon Slavin]

On 12 Feb 2017, at 11:10am, Constantine Yannakopoulos  
wrote:

> On Sun, Feb 12, 2017 at 12:53 PM, Simon Slavin  wrote:
>> You might have missed
>> 
>> 
>> 
>> which, despite its name, also works with views.
> 
> No, this returns the columns of a single table. Suggested pragma
> table_list/view_list would return all tables/views in a database.
> 
> I know I can get that from sqlite_master, but having to prefix it with
> the database name as part of the query syntax:
> 
>  select * from attached_db.sqlite_master;
> 
> makes it impossible to join with pragma database_list:
> 
>  select * from pragma_database_list as dblist, dblist.name.sqlite_master;

Okay, I see what you mean.  I don’t know of a way to do this in one command if 
you may have attached databases.

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

Re: [sqlite] Pragma table-valued functions used in views in an attached database.

[Constantine Yannakopoulos]

On Sun, Feb 12, 2017 at 12:53 PM, Simon Slavin  wrote:
> You might have missed
>
> 
>
> which, despite its name, also works with views.

No, this returns the columns of a single table. Suggested pragma
table_list/view_list would return all tables/views in a database.

I know I can get that from sqlite_master, but having to prefix it with
the database name as part of the query syntax:

  select * from attached_db.sqlite_master;

makes it impossible to join with pragma database_list:

  select * from pragma_database_list as dblist, dblist.name.sqlite_master;

cannot work while

  select * from pragma_database_list as dblist,
pragma_table_list(dblist.name) as tbllist;

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

Re: [sqlite] Pragma table-valued functions used in views in an attached database.

[Simon Slavin]

On 12 Feb 2017, at 10:39am, Constantine Yannakopoulos  
wrote:

> perhaps the addition of two more pragmas:
> 
>  pragma table_list(database-name);
>  pragma view_list(database-name);
> 
> would help.

You might have missed



which, despite its name, also works with views.

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

[sqlite] Pragma table-valued functions used in views in an attached database.

[Constantine Yannakopoulos]

Hi,

Trying to create an attached information schema using the instructions
in the additional notes of the "Pragma functions" section of
https://www.sqlite.org/pragma.html I have come across an issue. I
execute the following statements in any sqlite database:

attach :memory: as info;

drop view if exists info.table_columns;

create view info.table_columns as
select
  tbl_name as table_name,
  c.cid as column_id,
  c.name as column_name,
  c."type" as "type",
  c."notnull" as "notnull",
  c.dflt_value,
  c.pk
from sqlite_master m, pragma_table_info(m.tbl_name) c
where m.type = 'table';

Then, when I execute:

select * from table_columns;
or
select * from info.table_columns;

I get the error 'no such table: info.pragma_table_info'. But when I
execute the view's query directly I get the expected results. I get a
similar error if I try to use the example provided in the document:

CREATE VIEW info.indexed_columns AS
SELECT DISTINCT m.name || '.' || ii.name AS 'indexed-columns'
FROM sqlite_master AS m,
pragma_index_list(m.name) AS il,
pragma_index_info(il.name) AS ii
WHERE m.type='table'
ORDER BY 1;

If I create the view in the main database it works as expected but I
would prefer to be able to create it in an attached memory database so
that it does not persist in the database file.

Am I doing something wrong/not supported?

One more note: I believe it is impossible to construct a result set
that contains all databases (main and attached) and all objects (e.g.
tables) within each database with a single SQL statement that works
without "knowledge" of the attached databases (else it is easy with a
union). If someone knows a way please let me know. If there isn't,
perhaps the addition of two more pragmas:

  pragma table_list(database-name);
  pragma view_list(database-name);

would help.

PS: I know all this is possible by creating eponymous virtual tables
but the difficulty level for this is much higher.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users