Re: [sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Keith Medcalf]

On Saturday, 15 February, 2020 19:27, Peng Yu  wrote:

>I am trying to see what tables are available in sqlite_master from
>firefox cookies sqlite3 fire.

>~/Library/Application
>Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite

>But the error message says "Error: database is locked".

>I see a cookies.sqlite-wal file in the same directory.

>Is there a way to inspect this db file without closing firefox?

Does it work when you close Firefox?  If it works when Firefox is closed but 
not when Firefox is open, then the answer is probably no.  If you still get an 
error message when Firefox is closed, then you probably have too old a version 
of whatever version of SQLite3 you are using to look at the database.

I can, but then I am on Windows and using the latest mostest up-to-date version 
of the SQLite3 command line tool.  

There are rumours on the various message boards that Firefox opens the 
databases with exclusive access to prevent fiddling.  Not having looked at the 
relevant Firefox source code, I have no clue whether or not this is accurate.  
However the rumoured exclusive access seems to "go away" when an up-to-date 
version of SQLite3 is used to look at the databases.  Plus, of course, if the 
access is supposed to be exclusive then it is broken on Windows.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

[sqlite] Is it possible to dump a sqlite db that has an associated -wal file?

[Peng Yu]

Hi,

I am trying to see what tables are available in sqlite_master from
firefox cookies sqlite3 fire.

~/Library/Application
Support/Firefox/Profiles/jaseom4q.default-1480119569722/cookies.sqlite

But the error message says "Error: database is locked".

I see a cookies.sqlite-wal file in the same directory.

Is there a way to inspect this db file without closing firefox?

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

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Keith Medcalf]

While that is nice, it is likely completely irrelevant.  The issue appears to 
be the flamboyant conversion of data being performed by dotSnot (.NET).

The fact that data can be stored in several different formats inside the 
database, and then converted to what dotSnot calls a "GUID" is all very nice 
and interesting, but not useful in the least (at least not if you want the 
database engine to do any work, rather than doing all joining and filtering and 
ordering in the dotSnot application).

What you need to know is how the thing that dotSnot calls a GUID is presented 
to the database.  If dotSnot sends a GUID to the database as a 16-byte binary 
blob, then you better store your GUIDs in the database as 16-byte binary blobs 
if you expect to be able to find them.  Similarly, if dotSnot sends a GUID to 
the database as a text string formatted with brockets and dashes and lower case 
hex digits, then you better store your GUIDs in the database in that format if 
you ever expect to be able to find them.

In other words, it is irrelevant that the conversion from database to dotSnot 
will interpret 400 different types of things as a dotSnot GUID.  What is 
important is how dotSnot sends what it thinks calls a GUID to the database, and 
that it can do that reversibly.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of Simon Slavin
>Sent: Saturday, 15 February, 2020 13:56
>To: SQLite mailing list 
>Subject: Re: [sqlite] Searching by Guid index in table (.NET)? I can
>write and read, but not search by the Guid
>
>On 15 Feb 2020, at 8:12pm, J Decker  wrote:
>
>> memcmp is harder than strcmp?
>> and with blob I suppose you don't have 'if UTF8 or UTF16 do
>> different things' so should compare slightly faster?
>
>It's not cut-and-dried, but you can take things into consideration.
>
>Once data is in a database it usually just sits there.  You're mostly
>interested in input and output.
>
>Think of the number of times you're going to do conversions, and what
>formats you're converting from and to.  How much processing is involved
>in each conversion.
>___
>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] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Simon Slavin]

On 15 Feb 2020, at 8:12pm, J Decker  wrote:

> memcmp is harder than strcmp?
> and with blob I suppose you don't have 'if UTF8 or UTF16 do
> different things' so should compare slightly faster?

It's not cut-and-dried, but you can take things into consideration.

Once data is in a database it usually just sits there.  You're mostly 
interested in input and output.

Think of the number of times you're going to do conversions, and what formats 
you're converting from and to.  How much processing is involved in each 
conversion.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[J Decker]

On Fri, Feb 14, 2020 at 7:36 AM Simon Slavin  wrote:

> On 14 Feb 2020, at 2:59pm, Andy KU7T  wrote:
>
> > Do you suggest me keeping storing TEXT (and declaring the column as TEXT
> instead of GUID) or storing and declaring BLOB (and remove HEX)?
>
> TEXT.  Makes sorting and searching easier.
>

memcmp is harder than strcmp?
and with blob I suppose you don't have 'if UTF8 or UTF16 do
different things' so should compare slightly faster?


> ___
> 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] WITHOUT ROWID tables

[J. King]

On February 15, 2020 2:14:30 p.m. EST, Thomas Kurz  
wrote:
>Wouldn't be something like
>
>SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
>contains "WITHOUT ROWID"
>
>be sufficient?
>
>Just being curious.
>
>- Original Message - 
>From: sky5w...@gmail.com 
>To: SQLite mailing list 
>Sent: Saturday, February 15, 2020, 18:06:47
>Subject: [sqlite] WITHOUT ROWID tables
>
>Ok, not ideal. Still confusing, but I see the difference.
>For my code, I know the schemas. I guess a SQL builder could offer up
>query
>options to the user browsing new databases.
>
>On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin 
>wrote:
>
>> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
>
>> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
>> >> index_info('XYZ');".  If you get back one or more rows, then XYZ
>is a
>> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
>> >> table.
>> >>
>> > Confused...What if I made an index on a ROWID table?
>> > CREATE INDEX "Z" ON "DOC" ("n_id");
>
>> The parameter in index_info() is normally the name of an index.  So
>if you
>> create an index "Z" and ask for index_info("Z") you will get
>information on
>> that index.
>
>> If you create a WITHOUT ROWID table with name 'Y", and ask for
>> index_info("Y") you will get information on the primary key of that
>table.
>
>> If both exist, you get information about the index.
>
>> 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-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

CREATE TABLE t(
c TEXT DEFAULT 'WITHOUT ROWID'
-- this comment mentions something about WITHOUT ROWID
);


Contrived, no question, but possible. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[Thomas Kurz]

Wouldn't be something like

SELECT sql FROM sqlite_master WHERE tbl_name='?' AND type='table'
contains "WITHOUT ROWID"

be sufficient?

Just being curious.

- Original Message - 
From: sky5w...@gmail.com 
To: SQLite mailing list 
Sent: Saturday, February 15, 2020, 18:06:47
Subject: [sqlite] WITHOUT ROWID tables

Ok, not ideal. Still confusing, but I see the difference.
For my code, I know the schemas. I guess a SQL builder could offer up query
options to the user browsing new databases.

On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin  wrote:

> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:

> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> table.
> >>
> > Confused...What if I made an index on a ROWID table?
> > CREATE INDEX "Z" ON "DOC" ("n_id");

> The parameter in index_info() is normally the name of an index.  So if you
> create an index "Z" and ask for index_info("Z") you will get information on
> that index.

> If you create a WITHOUT ROWID table with name 'Y", and ask for
> index_info("Y") you will get information on the primary key of that table.

> If both exist, you get information about the index.

> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

[Barry Smith]

Regardless of whether you decide to store GUIDs as text or binary, things will 
be easier if you set your connection string appropriately. BinaryGUID is the 
parameter you want to change. 

See https://www.connectionstrings.com/sqlite-net-provider/store-guid-as-text/

For performance binary is better than text representation. The text UID 
requires twice as many bytes to store, twice as much in cache. If the UID is a 
significant portion of your row, this could be significant. If you’re running 
the latest version of SQLite you can make a computed column to get the text 
representation for manual/CLI access if you need it.

Also ‘randomblob(16)’ will not give you a real GUID. It’d work for most 
purposes but why not call it something else, at least that’d give you a red 
flag to perhaps expect problems when you pass it to a library that expects a 
real GUID and might check the format bits.

> On 14 Feb 2020, at 6:09 am, Andy KU7T  wrote:
> 
> Hi,
> 
> I am having trouble searching for a Guid. I think some conversion is missing. 
>  I wonder if someone can point me to the error…
> 
> Column:
> [ID] GUID(16)
> Index:
> CREATE UNIQUE INDEX [ID_INDEX] ON [DXLOG]([ID] COLLATE [BINARY] ASC);
> Trigger:
> CREATE TRIGGER [AUTOGENERATE_ID] AFTER INSERT ON [DXLOG] FOR EACH ROW WHEN 
> ([NEW].[ID] IS NULL)
> BEGIN
>  UPDATE
>[DXLOG]
>  SET
>[ID] = HEX (RANDOMBLOB (16))
>  WHERE
>[NEW].[ID] IS NULL AND [DXLOG].[ROWID] = [NEW].[ROWID];
> END;
> 
> Inserting a row simply will create a new guid for me. Good. Reading it like 
> this:
> 
> SELECT ID FROM [Dxlog] WHERE RowId = @RowID
> 
> Where @RowId is the LastInsertedRowId.
> And getting it in code:
> 
> Dim rdr2 As SQLiteDataReader = Nothing
> rdr2 = theDB.ExecuteQuery("SELECT ID FROM [Dxlog] WHERE RowId = @RowID", 
> sqlParameters)
> rdr2.Read()
> Me.mvarId = rdr2.Item("ID")
> 
> The returned ID is indeed a Guid. In SQLite it looks like this:
> 
> 40FD6722384053ED3BA45CD1C5FDB30D
> And in .NET: {40fd6722-3840-53ed-3ba4-5cd1c5fdb30d}
> 
> Now, when I am trying to search for it like this, I get nothing:
> 
>sqlParameters = New List(Of SQLiteParameter)()
>AddSqlParam(sqlParameters, "@ID", DbType.Guid, Me.Id)
> 
>rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID 
> = @ID", sqlParameters)
>rdr2.Read()
> 
> 
> Do I have to converte the Guid to binary somehow?
> 
> Thanks
> Andy
> 
> Sent from Mail for Windows 10
> 
> ___
> 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] WITHOUT ROWID tables

[curmudgeon]

Does
sqlite3_table_column_metadata(db,dbName,tblName,"rowid",0,0,0,0,0)==SQLITE_OK
return false if table tblName is a without rowid table?

https://sqlite.org/c3ref/table_column_metadata.html





--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[sky5walk]

Ok, not ideal. Still confusing, but I see the difference.
For my code, I know the schemas. I guess a SQL builder could offer up query
options to the user browsing new databases.

On Sat, Feb 15, 2020 at 11:26 AM Simon Slavin  wrote:

> On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:
>
> >> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> >> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> >> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> >> table.
> >>
> > Confused...What if I made an index on a ROWID table?
> > CREATE INDEX "Z" ON "DOC" ("n_id");
>
> The parameter in index_info() is normally the name of an index.  So if you
> create an index "Z" and ask for index_info("Z") you will get information on
> that index.
>
> If you create a WITHOUT ROWID table with name 'Y", and ask for
> index_info("Y") you will get information on the primary key of that table.
>
> If both exist, you get information about the index.
>
> 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

Re: [sqlite] WITHOUT ROWID tables

[Simon Slavin]

On 15 Feb 2020, at 3:14pm, sky5w...@gmail.com wrote:

>> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
>> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
>> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
>> table.
>> 
> Confused...What if I made an index on a ROWID table?
> CREATE INDEX "Z" ON "DOC" ("n_id");

The parameter in index_info() is normally the name of an index.  So if you 
create an index "Z" and ask for index_info("Z") you will get information on 
that index.

If you create a WITHOUT ROWID table with name 'Y", and ask for index_info("Y") 
you will get information on the primary key of that table.

If both exist, you get information about the index.

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

Re: [sqlite] system.data.sqlite example code?

[rajatgaikwadzh]

I think this is an informative post and knowledgeable. I would like to thank
you for the efforts you have made in writing this article
Packers and Movers in Delhi   



-
[url=https://www.aryawartapackers.com]packers and movers in delhi[/url]
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WITHOUT ROWID tables

[sky5walk]

>
> To determine if table XYZ is a WITHOUT ROWID table, run "PRAGMA
> index_info('XYZ');".  If you get back one or more rows, then XYZ is a
> WITHOUT ROWID table.  If you get back no rows, then XYZ is a rowid
> table.
>
Confused...What if I made an index on a ROWID table?
CREATE INDEX "Z" ON "DOC" ("n_id");
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I search all tables and columns of SQLite database for a specific text string?

[John G]

You can do it in Tcl (reusing Jose's example tables) like this:

package require sqlite3
sqlite3 dbcmd ~/tmp/grbg.db

 dbcmd eval "create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c,
t0d)"
 dbcmd eval "insert into table0 (t0a, t0b, t0c, t0d) values ('text in
here', 'Shelby 2002', '2 plus 2 equals 4', 'I am going home soon')"
 dbcmd eval "create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12)"
 dbcmd eval "insert into table1 (t10,t11,t12) values ('p001', 'Shelby
2002', '1 plus 1 equals 2')"
 dbcmd eval "create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c,
t2d)"
 dbcmd eval "insert into table2 (t2a, t2b, t2c, t2d) values ('in here',
'going home', '2020-02-11','Once upon a time...')"

 set searchstr "plus"
 set SQL ""
 set u ""
 set tables [dbcmd eval "select name from sqlite_master where type='table'"]
 foreach t $tables {
   dbcmd eval "pragma table_info($t)" {
# cid name type notnull dflt_value pk
if {[regexp -nocase -- {char|text} $type] || $type == ""} {
  append SQL "$u select '$t' as tn, '$name' as cn, $name as val from $t
\
 where $name like '%$searchstr%' "
   set u "union"
}
if {[string length $SQL] > 100} {
error "too many fields"
}
   }
 }
 dbcmd eval $SQL {
   puts [format "Field %-15s on %-15s has the string %-15s: %s"  $cn $tn
$searchstr $val]
 }
Field t0c on table0  has the string plus   : 2
plus 2 equals 4
Field t12 on table1  has the string plus   : 1
plus 1 equals 2

John G


On Thu, 13 Feb 2020 at 15:24, Jose Isaias Cabrera 
wrote:

>
> Scott, on Thursday, February 13, 2020 09:01 AM, wrote...
> >
> > Can I search all tables and columns of SQLite database for a specific
> > text string? I'm sure this question has been asked many times, but I'm
> > having trouble finding a solid answer.
> > My problem: My clients SQLite database has 11 tables and multiple columns
> > (some designated and others not) and they want to be able to search the
> > entire database for a specific text or phrase.
> > What I have done: I've been searching a couple days and found the Full
> > Text search on SQLite home based upon using a virtual table, but I don't
> > think that will work. It appears that I may be able to search the
> > sqlite_master but it seems it may only contain table and column
> information
> > only minus the data.
> > What I'm working in: This is an Android app written in Java using the
> > SQLite
> > What I hope to do: Find a simple query statement or combination of
> > statements that may help to efficiently query for the string across
> tables
> > and columns before I resort to multiple queries and methods for all 11
> > tables.
> > I'm looking for any experienced thoughts or suggestions anyone may have
> > encountered resolving this kind of issue if available. I'm not expecting
> > anyone to solve it for me -- just some guidance would be helpful.
>
> This is a very wide open question.  It is a lot of work to create the
> query.
> I actually have to do this for some tables and some fields, but I know
> these
> tables and these fields. Here are some questions:
>
> 1. What are you going to do when you find a string match in a table field?
>
> 2. Do you need to know that table?  Do you need to know the field?
>
> 3. Do you need the whole content of that field if matched?
>
> There are just too many questions to help, but it is possible if you know
> what do you want to do. Here are some ideas:
> a. The command prompt has a .table option that will provide all the tables
> available on a DB
> b. The .schema [tablename] will give you the table's fields
>
> Imagine these three tables:
> create table table0 (id INTEGER PRIMARY KEY, t0a, t0b, t0c, t0d);
> insert into table0 (t0a, t0b, t0c, t0d) values ('text in here', 'Shelby
> 2002', '2 plus 2 equals 4', 'I am going home soon');
> create table table1 (id INTEGER PRIMARY KEY, t10,t11,t12);
> insert into table1 (t10,t11,t12) values ('p001', 'Shelby 2002', '1 plus 1
> equals 2');
> create table table2 (id INTEGER PRIMARY KEY, t2a, t2b, t2c, t2d);
> insert into table2 (t2a, t2b, t2c, t2d) values ('in here', 'going home',
> '2020-02-11','Once upon a time...');
>
> SELECT
> 'field t0a on table0 has the string [plus]: ', t0a from table0
> WHERE t0a LIKE '%plus%'
> UNION
> SELECT
> 'field t0b on table0 has the string [plus]: ', t0b from table0
> WHERE t0b LIKE '%plus%'
> UNION
> SELECT
> 'field t0c on table0 has the string [plus]: ', t0c from table0
> WHERE t0c LIKE '%plus%'
> UNION
> SELECT
> 'field t0d on table0 has the string [plus]: ', t0d from table0
> WHERE t0d LIKE '%plus%'
> UNION
> SELECT
> 'field t10 on table1 has the string [plus]: ', t10 from table1
> WHERE t10 LIKE '%plus%'
> UNION
> SELECT
> 'field t11 on table1 has the string [plus]: ', t11 from table1
> WHERE t11 LIKE '%plus%'
> UNION
> SELECT
> 'field t12 on table1 has the string [plus]: ', t12 from table1
> WHERE t12 LIKE '%plus%'
> UNION
> SELECT
> 'field t2a on tab