Re: [sqlite] WITHOUT ROWID tables

2020-02-17 Thread sky5walk
n > Sent: Sunday, February 16, 2020 6:11:02 PM > To: SQLite mailing list > Subject: Re: [sqlite] WITHOUT ROWID tables > > On 16 Feb 2020, at 6:00pm, Bernardo Ramos wrote: > > > I've got no rows for both with and without rowid tables:

Re: [sqlite] WITHOUT ROWID tables

2020-02-16 Thread x
; for Windows 10 From: sqlite-users on behalf of Simon Slavin Sent: Sunday, February 16, 2020 6:11:02 PM To: SQLite mailing list Subject: Re: [sqlite] WITHOUT ROWID tables On 16 Feb 2020, at 6:00pm, Bernardo Ramos wrote: > I've got no rows for both with

Re: [sqlite] WITHOUT ROWID tables

2020-02-16 Thread Peter da Silva
tbl_name='?' AND type='table' > >contains "WITHOUT ROWID" > > > >be sufficient? > > > >Just being curious. > > > >- Original Message - > >From: sky5w...@gmail.com > >To: SQLite mailing list > >Sent: Satur

Re: [sqlite] WITHOUT ROWID tables

2020-02-16 Thread Simon Slavin
On 16 Feb 2020, at 6:00pm, Bernardo Ramos wrote: > I've got no rows for both with and without rowid tables: " as of SQLite version 3.30.0 on 2019-10-04 " ___ sqlite-users mailing list sqlite-users@mai

Re: [sqlite] WITHOUT ROWID tables

2020-02-16 Thread Bernardo Ramos
I've got no rows for both with and without rowid tables: SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> create table t1(name); sqlite> create table t2(name primary key, phone) without rowid; sqlite> pragma index_info(t1); sqlite> pragma index_info(t2); sqlite> p

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread J. King
-- 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 builde

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread Thomas Kurz
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:

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread 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/ ___ sql

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread 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

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread 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

Re: [sqlite] WITHOUT ROWID tables

2020-02-15 Thread 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"

Re: [sqlite] WITHOUT ROWID tables

2020-02-14 Thread Richard Hipp
On 2/14/20, Wayne Collins wrote: > My first posting so I hope I get the etiquette correct. > > 1st question How can I determine from a C-program if a table was > generated with the "WITHOUT ROWID" option? I have an application where > it is important to know if a table has a rowid or not. To dete

[sqlite] WITHOUT ROWID tables

2020-02-14 Thread Wayne Collins
My first posting so I hope I get the etiquette correct. 1st question How can I determine from a C-program if a table was generated with the "WITHOUT ROWID" option? I have an application where it is important to know if a table has a rowid or not. 2nd Question After a database is defined and t

Re: [sqlite] WITHOUT ROWID

2019-09-08 Thread Richard Hipp
On 9/8/19, t...@qvgps.com wrote: > > One option would be to create a new table with rowid, copy all records from > the old one, delete old one and rename new table. That is the quickest solution. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mai

[sqlite] WITHOUT ROWID

2019-09-08 Thread t...@qvgps.com
Hi sqlite-group, I accidentally created tables WITHOUT ROWID. These tables are filled with some 100MB of data and delivered to customers already. Now I'm looking for way to fix this bug. Is it possible to change (ALTER TABLE?) these tables to still include rowid? One option would be to create a

Re: [sqlite] WITHOUT ROWID tables with a ROWID

2019-02-22 Thread Tom Bassel
Ah I see now. Sorry I should have read the docs more carefully -- it is working according to spec in all cases. Great answers. Thanks guys! Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mai

Re: [sqlite] WITHOUT ROWID tables with a ROWID

2019-02-22 Thread tom-sqlite
Ah I see now. Sorry I should have read the docs more carefully -- it is working according to spec in all cases. Great answers. Thanks guys! Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/

Re: [sqlite] WITHOUT ROWID tables with a ROWID

2019-02-21 Thread David Raymond
t; affinity. Aka "prefer to store it as an integer but don't have to" sort. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tom Bassel Sent: Thursday, February 21, 2019 3:02 PM To: sqlite-users@mailinglists.sqlite.org

Re: [sqlite] WITHOUT ROWID tables with a ROWID

2019-02-21 Thread J. King
On 2019-02-21 15:02:23, "Tom Bassel" wrote: Are tables t3, t4, and t5 below working as designed? Yes. See : > The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column na

[sqlite] WITHOUT ROWID tables with a ROWID

2019-02-21 Thread Tom Bassel
Hi, this is my first post to this group. I am not trying to be difficult. I work on an app that uses SQLite as the underlying database engine. At one time years ago I remember assuming that the ROWID column in a table in SQLite would always be an integer. However, I can see a user who is not fam

Re: [sqlite] without rowid and secondary indexes

2018-04-10 Thread Olivier Mascia
> Le 10 avr. 2018 à 18:25, Clemens Ladisch a écrit : > > Olivier Mascia wrote: >> Would you mean: >> >> select NAME,count(NAME) from SHEETS where SHEET>? group by NAME having NAME >> is not NULL; >> >> is more logically appropriate? > > Probably. The first query can give quite random result

Re: [sqlite] without rowid and secondary indexes

2018-04-10 Thread Clemens Ladisch
Olivier Mascia wrote: >> Le 10 avr. 2018 à 13:20, Clemens Ladisch a écrit : >>> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME >>> is not NULL; >> >> The SHEET value is from some random row in the group, and the NAME value is >> the same for all rows in the group. Are

Re: [sqlite] without rowid and secondary indexes

2018-04-10 Thread Olivier Mascia
> Le 10 avr. 2018 à 13:20, Clemens Ladisch a écrit : > >> select NAME,count(NAME) from SHEETS group by NAME having SHEET>? and NAME is >> not NULL; > > The SHEET value is from some random row in the group, and the NAME value is > the same for all rows in the group. Are you sure you do not want

Re: [sqlite] without rowid and secondary indexes

2018-04-10 Thread Clemens Ladisch
Olivier Mascia wrote: > [...] > So I should probably even better go with: > > create index ... where NAME is not NULL; > > as my only queries involving NAME have a 'where NAME is not NULL' restriction. To be sure, check with EXPLAIN QUERY PLAN whether the index is actually used. > select NAME,c

Re: [sqlite] without rowid and secondary indexes

2018-04-10 Thread Olivier Mascia
Thanks! > In your example below, your PRIMARY KEY consists of two integer > instead of just a single rowid integer. So it will take up slightly > more space. Not a lot, but some. > > Will that make a difference in your application? I do not know. Then, I will indeed run some more tests, but

Re: [sqlite] without rowid and secondary indexes

2018-04-10 Thread Richard Hipp
Every index is composed of the columns being indexed followed by the primary key of the table. In a regular ROWID table, the "primary key" is the rowid - an integer. In a WITHOUT ROWID table, the primary key is whatever you declare the PRIMARY KEY to be. So whether or not there is more overhead

[sqlite] without rowid and secondary indexes

2018-04-10 Thread Olivier Mascia
Dear all, With https://www.sqlite.org/withoutrowid.html in mind, assuming something simple as: create table if not exists SHEETS( NODE integer not null, SHEET integer not null, NAME text, -- often NULL, sometimes short text value (keyword) ..., -- 2 or

[sqlite] WITHOUT ROWID

2016-05-19 Thread R Smith
On 2016/05/19 2:50 PM, Richard Hipp wrote: > On 5/19/16, Ertan K???ko?lu wrote: >> Hello, >> >> I wonder if there are any drawbacks/things to be aware of, etc. for tables >> created "WITHOUT ROWID". I am considering using such tables in another >> project. > Experiment. Run your application wit

[sqlite] WITHOUT ROWID

2016-05-19 Thread Ertan Küçükoğlu
Hello, I wonder if there are any drawbacks/things to be aware of, etc. for tables created "WITHOUT ROWID". I am considering using such tables in another project. Thanks. Regards, Ertan K???ko?lu

[sqlite] WITHOUT ROWID

2016-05-19 Thread Richard Hipp
On 5/19/16, Ertan K???ko?lu wrote: > Hello, > > I wonder if there are any drawbacks/things to be aware of, etc. for tables > created "WITHOUT ROWID". I am considering using such tables in another > project. Experiment. Run your application with WITHOUT ROWID and measure performance (where "perfo

Re: [sqlite] Without ROWID and Autoincrement question

2014-12-27 Thread Mohit Sindhwani
On 27/12/2014 12:13 AM, Teg wrote: Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a col

[sqlite] Without ROWID and Autoincrement question

2014-12-26 Thread Teg
Hello General, I was going to say I thought the docs were ambiguous but, on my 3rd reading I see they aren't. Still, I think this statement could be re-written to reduce confusion (at least mine). https://www.sqlite.org/autoinc.html >>Because AUTOINCREMENT keyword changes the behavior o

Re: [sqlite] WITHOUT ROWID option

2014-05-09 Thread Simon Slavin
On 9 May 2014, at 11:17am, Sky Meena wrote: > i need to transfer sqlite.db from server to client in udp socket Please start a new thread about this. It has nothing to do with 'WITHOUT ROWID option'. Simon. ___ sqlite-users mailing list sqlite-users

Re: [sqlite] WITHOUT ROWID option

2014-05-09 Thread Hick Gunter
Vlasov [mailto:max.vla...@gmail.com] Gesendet: Freitag, 09. Mai 2014 12:10 An: General Discussion of SQLite Database Betreff: Re: [sqlite] WITHOUT ROWID option On Wed, May 7, 2014 at 6:31 PM, Richard Hipp wrote: > On Wed, May 7, 2014 at 9:00 AM, Marco Bambini wrote: > >> What is the best

Re: [sqlite] WITHOUT ROWID option

2014-05-09 Thread Sky Meena
i need to transfer sqlite.db from server to client in udp socket.. working in c language.. .. i dont know how to transmit db. in client side it should create copy of db. if client ask name of db to server . server should transmit.. On Fri, May 9, 2014 at 3:09 PM, Stephan Beal wrote: > On Fr

Re: [sqlite] WITHOUT ROWID option

2014-05-09 Thread Max Vlasov
On Wed, May 7, 2014 at 6:31 PM, Richard Hipp wrote: > On Wed, May 7, 2014 at 9:00 AM, Marco Bambini wrote: > >> What is the best way to know if a table has been created with the WITHOUT >> ROWID option? >> > > > (1) You could send "SELECT rowid FROM table" > > (2) Run both "PRAGMA index_list

Re: [sqlite] WITHOUT ROWID option

2014-05-09 Thread Stephan Beal
On Fri, May 9, 2014 at 11:38 AM, Stephan Beal wrote: > i don't think a WHERE is necessary to improve the efficiency. The > statement only gets prepared, not stepped, and i would not expect any > analysis of results until the first step() (but maybe i'm assuming too > much). > In fact, a WHERE, i

Re: [sqlite] WITHOUT ROWID option

2014-05-09 Thread Stephan Beal
On Fri, May 9, 2014 at 8:36 AM, Andy Goth wrote: > On 5/8/2014 10:11 AM, Jim Morris wrote: > >> To improve efficiency you could add "where 1=2" to avoid returning any >> rows. Should just check validity. >> > > This being SQLite, as previously discussed, you could say "where 0" :^) i don't thi

Re: [sqlite] WITHOUT ROWID option

2014-05-08 Thread Andy Goth
On 5/8/2014 10:11 AM, Jim Morris wrote: To improve efficiency you could add "where 1=2" to avoid returning any rows. Should just check validity. This being SQLite, as previously discussed, you could say "where 0" :^) -- Andy Goth | ___ sqlite-users

Re: [sqlite] WITHOUT ROWID option

2014-05-08 Thread Jim Morris
To improve efficiency you could add "where 1=2" to avoid returning any rows. Should just check validity. On 5/7/2014 8:19 AM, Stephan Beal wrote: On Wed, May 7, 2014 at 4:57 PM, Simon Slavin wrote: somehow ? Perhaps the ROWID field of a table might have its own particular indication, and i

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread Stephan Beal
On Wed, May 7, 2014 at 4:57 PM, Simon Slavin wrote: > somehow ? Perhaps the ROWID field of a table might have its own > particular indication, and if you don't see any rows marked like that you > could deduce that the table had no ROWID column. I'm sure there are better > ways the > This isn't

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread Simon Slavin
On 7 May 2014, at 3:31pm, Richard Hipp wrote: > (2) Run both "PRAGMA index_list(table)" and "SELECT name FROM sqlite_master > WHERE tbl_name='table'". If the PRAGMA mentions an > "sqlite_autoindex_table_1" which is not mentioned by the SELECT, then you > have a WITHOUT ROWID table. This approa

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread Marco Bambini
Thanks a lot Richard, I really appreciate. -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 16:31, Richard Hipp wrote: > On Wed, May 7, 2014 at 9:00 AM, Marco Bambini wrote: > >> What is the best way to know if a table has been

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread Richard Hipp
On Wed, May 7, 2014 at 9:00 AM, Marco Bambini wrote: > What is the best way to know if a table has been created with the WITHOUT > ROWID option? > (1) You could send "SELECT rowid FROM table" to sqlite3_prepare() and see if it returns an error. This might fail on a table like "CREATE TABLE xyz

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread Marco Bambini
So, is there an official recommended way? or that check should require a manual sql parsing? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs On 07 May 2014, at 15:51, Igor Tandetnik wrote: > On 5/7/2014 9:40 AM, RSmith wrote: >> SELECT instr(upper(

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread RSmith
...or indeed this malicious-but-valid table-schema design would prove problematic. Other that might cause similar headaches are: CREATE TABLE t(x text // )WITHOUT ROWID; ); or CREATE TABLE t(x text); // )WITHOUT ROWID; etc. It would require a rather convoluted check to be very sure

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread RSmith
Apologies, my answer reads more like a "How to" than a "What is the best way" type answer, so to just elaborate on the brevity - SQLite stores no special pointer or memory or setting or even file value anywhere that can give you any clue apart from the actual words "Without RowID" which are found

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread Igor Tandetnik
On 5/7/2014 9:40 AM, RSmith wrote: SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. CREATE TABLE t(x text default 'WITHOUT ROWID'); -- Igor Tandetnik

Re: [sqlite] WITHOUT ROWID option

2014-05-07 Thread RSmith
SELECT instr(upper(sql),'WITHOUT ROWID')>1 FROM sqlite_master WHERE type='table' AND tbl_name='YourTableName' Returns 1 for tables made without rowid, 0 for the rest. On 2014/05/07 15:00, Marco Bambini wrote: What is the best way to know if a table has been created with the WITHOUT ROWID opt

[sqlite] WITHOUT ROWID option

2014-05-07 Thread Marco Bambini
What is the best way to know if a table has been created with the WITHOUT ROWID option? -- Marco Bambini http://www.sqlabs.com http://twitter.com/sqlabs http://instagram.com/sqlabs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.or

Re: [sqlite] WITHOUT ROWID problem and large number of row updating

2014-04-16 Thread James K. Lowden
On Wed, 16 Apr 2014 10:24:51 -0700 Joseph Yesselman wrote: > sql = "CREATE TABLE two_way(" \ > "id VARCHAR(50) NOT NULL," \ > "names VARCHAR(2000)," \ > "rs VARCHAR(3000)," \ > "ds VARCHAR(2000)," \ > "sugs VARCHAR(2000), " \ > "PRIMARY KEY (id));"; ... > if names is currentl

Re: [sqlite] WITHOUT ROWID problem and large number of row updating

2014-04-16 Thread Simon Slavin
On 16 Apr 2014, at 6:24pm, Joseph Yesselman wrote: > I would like to use the > WITHOUT ROWID statement as my primary key is a string in the format of 6 > coordinates. > > example: > "0.0 1.0 3.0 1.6 2.4 0.0" Why not use six REAL columns in a table, with an index consisting of all six columns

[sqlite] WITHOUT ROWID problem and large number of row updating

2014-04-16 Thread Joseph Yesselman
Hi All, I have two problems, the first one is probably very simple but I could not find anything online telling me how to fix it. I would like to use the WITHOUT ROWID statement as my primary key is a string in the format of 6 coordinates. example: "0.0 1.0 3.0 1.6 2.4 0.0" I am using c++, but n