Re: [sqlite] System.Data.SQLite for Pocket PC & RTREE

[Joe Mistachkin]

Duncan Hall wrote:
>
> The db was created by Spatialite and has a fairly lengthy schema. I'm not
> sure if I should leave anything out so I'll attach a text file.
> 

Thanks.  I've run the schema and query here with the desktop version of
SpatiaLite and it does not crash.  I suspect the underlying issue may be
an alignment fault (or stack overflow) due to bad interaction between the
ARM processor architecture and the SpatiaLite extension.

Out of curiousity, do you know where to find the loadable module binaries
for Windows CE (for SpatiaLite)?

--
Joe Mistachkin

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

Re: [sqlite] System.Data.SQLite for Pocket PC & RTREE

[Duncan Hall]

Joe Mistachkin-3 wrote
> Are you able to share the schema for your database?  It may be helpful.


The db was created by Spatialite and has a fairly lengthy schema. I'm not
sure if I should leave anything out so I'll attach a text file.


schema.txt   

Thanks




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Re-System-Data-SQLite-for-Pocket-PC-RTREE-tp80344p80411.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Repost: Having problems with Entity Framework code first db creation

[Joe Mistachkin]

Walter Williams wrote:
>
> I noticed in the configuration of App.config after the packages
> are installed, the defaultConnectionFactory refers to
> System.Data.Entity.Infrastructure.SqlConnectionFactory, and there is a
> provider System.Data.Entity.SqlServer.SqlProviderServices in addition to
the
> SQLite provider.  I am not experienced in this area.  Does the default
> connection factory perhaps need to change?
>

Yeah, I noticed that yesterday as well.  I'm not sure if that is new to EF
6.1.1
and I'm also not sure if it's causing issues or not.  Yesterday, while
trying to
manually re-test various scenarios with Entity Framework 6 integration using
Visual Studio 2013, I was getting some very strange results.  I'm still
trying
to determine the root cause(s) of the issues people are seeing with the
latest
batch of Visual Studio and Entity Framework 6 updates.  Currently, the
1.0.95.0
release is blocked until these issues are resolved (in some way).

Meanwhile, it might be useful to remove the non-SQLite related connection
stuff
from the configuration file and try again.  By the way, I've also discovered
that rebuilding the solution may be necessary for the configuration file
changes
to be effective.

--
Joe Mistachkin

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

Re: [sqlite] System.Data.SQLite for Pocket PC & RTREE

[Joe Mistachkin]

Duncan Hall wrote:
>
> 'cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table'"
> cmd.CommandText = "SELECT * FROM idx_Locale_Geometry "
>

Are you able to share the schema for your database?  It may be helpful.

--
Joe Mistachkin

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

Re: [sqlite] Possible to get table size (in bytes)?

[Rael Bauer]

Thanks to the reference to the sqlite_analyzer. That is very 
interesting. (And the manual counting of bytes would also help me for my 
question..)


The main question I have is how expensive will adding an fts4aux table be?

I noticed the report for a FTS table e.g. NOTES_FTS

only shows results for:
NOTES_FTS_SEGMENTS
NOTES_FTS_CONTENT
NOTES_FTS_SEGDIR

Why not for NOTES_FTS - is that just a view?

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

Re: [sqlite] Repost: Having problems with Entity Framework code first db creation

[Walter Williams]

I tried it again with the 1.0.95 NuGet packages (even with a new project),
and I get the exact same error.  You said EF looks like it is trying to use
SQL Server.  I noticed in the configuration of App.config after the packages
are installed, the defaultConnectionFactory refers to
System.Data.Entity.Infrastructure.SqlConnectionFactory, and there is a
provider System.Data.Entity.SqlServer.SqlProviderServices in addition to the
SQLite provider.  I am not experienced in this area.  Does the default
connection factory perhaps need to change?






  


  
  

  
  

  
  
  
  

  
  


  
  

  



Walter Williams
Senior Software Engineer
Sawtooth Software, Inc.


"Do, or do not.  There is no try."


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin
Sent: Wednesday, February 04, 2015 9:22 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Repost: Having problems with Entity Framework code
first db creation


Walter Williams wrote:
>
> I received the same result.
>

Is that the same error message you posted before (i.e. "Unable to complete
operation. The supplied SqlConnection does not specify an initial catalog or
AttachDBFileName.")?  That particular error message leads me to believe that
the Entity Framework is trying to use the ADO.NET provider for SQL Server
instead of SQLite.

Also, could you try updating to the latest NuGet packages as well?  They
have not been pushed and are only [now] available for download from the
site.

--
Joe Mistachkin

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

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

Re: [sqlite] why does the recursive example sort alphabetically

[RSmith]

On 2015/02/04 15:45, Mark Summerfield wrote:

Hi,

In the documentation on page http://www.sqlite.org/lang_with.html
there is an example of a recursive query if you scroll down to the heading
"Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER
BY".

The second example under that heading shows how to get a depth-first
search. Here's the query:

WITH RECURSIVE
   under_alice(name,level) AS (
 VALUES('Alice',0)
 UNION ALL
 SELECT org.name, under_alice.level+1
   FROM org JOIN under_alice ON org.boss=under_alice.name
  ORDER BY 2 *DESC*
   )
SELECT substr('..',1,level*3) || name FROM under_alice;


It turns out that this query not only provides a correctly indented output
of the tree, but it also sorts every branch alphabetically by name.

What I don't understand is *why* it sorts alphabetically by name.

I would have expected to need to change the query to have ORDER BY 2 DESC,
org.name
for it to work, but it works anyway.


It doesn't actually "work anyway", the result is not ordered at all - it just happens to be the order in which the rows are fetched 
which only happens to be in that order because of the default sort direction of the primary key (which is ascending).  If you do 
PRAGMA reverse_unordered_selects=1;  and then run the query again, it should pop out the opposite order.


(I haven't actually tested this, but trust it to the point of not feeling the need to test it, hoever, let us know if you test it 
and get a different result).




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

Re: [sqlite] why does the recursive example sort alphabetically

[Richard Hipp]

On 2/4/15, Mark Summerfield  wrote:
> Hi,
>
> In the documentation on page http://www.sqlite.org/lang_with.html
> there is an example of a recursive query if you scroll down to the heading
> "Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER
> BY".
>
> The second example under that heading shows how to get a depth-first
> search. Here's the query:
>
> WITH RECURSIVE
>   under_alice(name,level) AS (
> VALUES('Alice',0)
> UNION ALL
> SELECT org.name, under_alice.level+1
>   FROM org JOIN under_alice ON org.boss=under_alice.name
>  ORDER BY 2 *DESC*
>   )
> SELECT substr('..',1,level*3) || name FROM under_alice;
>
>
> It turns out that this query not only provides a correctly indented output
> of the tree, but it also sorts every branch alphabetically by name.
>
> What I don't understand is *why* it sorts alphabetically by name.e


The ORDER BY clause within the recursive CTE determines the order of
recursion (depth-first or breadth-first), not the order of final
output.

The final output order, in this case, is the same as the PRIMARY KEY
order on the table.  (SQLite does not guarantee this - it just happens
to fall out because of the particular algorithm that SQLite chose in
this particular case.  The result might change at any time.)

If you change the table schema to say:

 name TEXT PRIMARY KEY DESC,

(Adding "DESC" after PRIMARY KEY) then you will observe that the
output is in the opposite order.

Emphasis:  There is ORDER BY clause on the final SELECT statement, and
so SQLite is free to output the results in any order it wants.  The
fact that is happens to come out in some particular order in the
current implementation is just a happy accident and is not something
you should rely upon.

>
> I would have expected to need to change the query to have ORDER BY 2 DESC,
> org.name
> for it to work, but it works anyway.
>
> Thanks!
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] why does the recursive example sort alphabetically

[Mark Summerfield]

Hi,

In the documentation on page http://www.sqlite.org/lang_with.html
there is an example of a recursive query if you scroll down to the heading
"Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER 
BY".

The second example under that heading shows how to get a depth-first 
search. Here's the query:

WITH RECURSIVE
  under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
  FROM org JOIN under_alice ON org.boss=under_alice.name
 ORDER BY 2 *DESC*
  )
SELECT substr('..',1,level*3) || name FROM under_alice;


It turns out that this query not only provides a correctly indented output 
of the tree, but it also sorts every branch alphabetically by name.

What I don't understand is *why* it sorts alphabetically by name.

I would have expected to need to change the query to have ORDER BY 2 DESC, 
org.name
for it to work, but it works anyway.

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

Re: [sqlite] System.Data.SQLite for Pocket PC & RTREE

[Duncan Hall]

Thanks for the response

I'm developing for a Mobile 5 or 6 device in Visual Studio 2008.
I'm porting a project that works fine on the desktop in VS 2012.
I have tried the most recent release 1.094 and also 1.095 pre release. I
also tried with an old version 1.082 which is working in another app, but it
could not open the database with the existing code.
Querying my database is fine for standard queries, but any query to the
rtree index causes the app to crash. The code used is below (VB I'm afraid),
and when the first CommandText is run a list of tables includes the
idx_Locale_Geometry, but when the second is run (test query, I have tried
others) the app crashes with a message 'The remote connection to the device
has been lost. Please verify the device connection and restart debugging'


Dim dbDataReader As SQLiteDataReader = Nothing
Dim cmd As SQLiteCommand = m_Connection.CreateCommand()
'cmd.CommandText = "SELECT name FROM sqlite_master WHERE
type='table'"
cmd.CommandText = "SELECT * FROM idx_Locale_Geometry "
Try
 dbDataReader = cmd.ExecuteReader()
Catch ex as Exception
End Try
If dbDataReader.HasRows() Then
While dbDataReader.Read()
   'Get data

End While
Else
Throw New Exception("No data")
End If


The crash is at ExecuteReader() and does not throw an exception and the call
stack clears. A break point at the catch clause is never triggered so I'm
not sure how to find the error. I might have to catch it on the device but
I'm not sure how to do that.


Thanks for your help

Duncan 




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Re-System-Data-SQLite-for-Pocket-PC-RTREE-tp80344p80403.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [ANN] schemadoc 1.0 - auto-generate your SQLite database schema docu for tables, columns, etc.; incl. themes

[Gerald Bauer]

Hello,

   I've put together a command line tool, that is, schemadoc [1] that
lets you auto-generate your SQLite database schema docu for tables,
columns, etc.

  The schemadoc tool connects to your SQLite database and writes out
the schema info in database.json and also builds an a-z symbol index
in symbols.json. Drop the json files in the _data/ folder for your
static site theme (template pack) and let Jekyll (or GitHub Pages) do
the rest.

  See the football.db [2] or beer.db [3] examples. All code and
templates public domain.  Cheers.

[1] https://github.com/rubylibs/schemadoc
[2] http://openfootball.github.io/schema
[3] http://openbeer.github.io/schema

PS: Thanks for your suggestions and helping w/ the design of the tool
and templates.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users