Re: [sqlite] Is there equivalent to MySQL IF() function?

SELECT CASE WHEN (AGE<3) THEN 'Baby' WHEN (AGE BETWEEN 4 AND 18) THEN 'Child' ELSE 'Adult' END On 2014/10/07 11:15, Tony Papadimitriou wrote: Hi all, Is there any an equivalent function to the MySQL IF(condition,true_expr,false_expr) function? For example, SELECT AGE,IF(AGE <

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 12:42, Tony Papadimitriou wrote: You're right, ... but in that page it says: The only difference between the following two CASE expressions is that the x expression is evaluated exactly once in the first example but might be evaluated multiple times in the second: CASE x

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 12:13, Tony Papadimitriou wrote: Thanks. It seems quite a bit more verbose than the IF() function, but it works, so I can't complain. As an aside - It's not only a little more verbose, it also happens to be the way prescribed by the SQL standard and to my knowledge MySQL,

Re: [sqlite] Is there equivalent to MySQL IF() function?

On 2014/10/07 13:20, Tony Papadimitriou wrote: Well, it is exactly because I understand the difference between a boolean expression and a non-boolean expression, along with a bit misleading documentation, that I got confused. It is usually those who are used to only the C-like treatment of a

Re: [sqlite] Single large table vs. several smaller tables

There is no point to grouping similar fields in different tables, though I would advise grouping them together in the main table as a simple case for clarity, but it has no other benefit. As long as the data is in 1NF, one table is fine. The main reason (and I think only motivation) to have data

Re: [sqlite] Stored Procedures

On 2014/10/09 19:04, Omprakash Kolluri wrote: Hi, I am new to SQLite. I am working on an app that I am developing and plan to use SQLite as an embedded database. My Question - Does SQLite support stored procedures similar to those in MS SQL Server etc. Any suggestions OR pointers to

Re: [sqlite] decoding a bitmask

On 2014/10/13 13:52, Paul Sanderson wrote: I have a table with an integer value which is a bitmask. one or more of the bits can be set and each bit has a corresponding meaning. so using the windows file attribute as an example we have 0c01 readonly 0x02 hidden 0x04 system 0x10 directory 0x20

Re: [sqlite] decoding a bitmask

On 2014/10/13 16:51, Mark Lawrence wrote: On Mon Oct 13, 2014 at 02:39:40PM +0100, Paul Sanderson wrote: The query is on a visits table from a google chrome history database. The query seems to work OK if a single bit is set, but fails (a blank string is returned) when multiple bits are set.

Re: [sqlite] decoding a bitmask

On 2014/10/13 15:39, Paul Sanderson wrote: Thanks all Clemens - I went initially for your solution as it fitsbetter with some other work i have done My actual code is as folows (CASE visits.transition & 0xFF00 WHEN 0x0080 THEN 'Blocked' ELSE '' END || CASE

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

On 2014/10/13 23:21, pihu...@free.fr wrote: Hello! I'm trying to find a way to reduce the length of the following query using SQLite: select * from (select GroupName, JobName, Start, End, Status, (strftime('%s', End) - strftime('%s', Start)) as Length from ReportJobs where PlanDate =

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

ReportJobLengths R WHERE GroupName like 'GRP12%' LIMIT 10 UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP15%' LIMIT 10 UNION ALL SELECT * FROM ReportJobLengths R WHERE GroupName like 'GRP20%' LIMIT 10; etc. On 2014/10/14 09:22, RSmith wrote: On 2014/10/13 23:21, pihu...@free.fr

Re: [sqlite] line break, or similar, in query results

On 2014/10/14 13:09, Clemens Ladisch wrote: Paul Sanderson wrote: SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * FROM (SELECT ...); This solution from Clemens will work perfectly, and depending on the kind of OS you use and output method it might even work to add

Re: [sqlite] Make a database read-only?

On 2014/10/14 13:48, Ross Altman wrote: Hi Martin, Thank you, I'll definitely look into that. It's unfortunate that there isn't a simpler way to do this... oh well. Let me bud in here since I encounter this question a lot in other matters. There typically are three reasons one would like to

Re: [sqlite] Search query alternatives.

On 2014/10/16 00:05, Michael Falconer wrote: Hi all, first off I must start with an apology. I know I'm sort of doing the wrong//... No need to apologise, this flies quite close to the central theme. Whether you are using SQLite or any other SQL RDBMS, this is horrible DB design and it is

Re: [sqlite] x64 vs x32 DLL

On 2014/11/04 21:34, jose isaias cabrera wrote: Greetings! I have an application that is written for x32 machines. However, we now have a few machines that are x64 and all is well when we are using the precompiled x32 DLLs provided by http://www.sqlite.org/download.html. Changing the x32

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 2014/11/05 14:13, vita...@yourcmc.ru wrote: Hi! After playing a little with SQLite as a DBMS for Bugzilla, I've discovered that LEFT/INNER join affects query plan in a bad way even for semantically equal queries: SELECT * FROM bugs b INNER JOIN profiles p ON p.userid=b.assigned_to WHERE

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

On 2014/11/05 15:26, David Woodhouse wrote: On Wed, 2014-11-05 at 15:13 +0200, RSmith wrote: I don't think it's anything to do with the table data being special, is it? Isn't it generically true that for any LEFT JOIN of a,b WHERE b.anything IS NOT NULL, the results are going to be equal

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

On 2014/11/08 14:21, Clemens Ladisch wrote: Hi, the following query fails to parse, although it should be valid: sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x Will call the above Version A. I don't see how that

Re: [sqlite] What is the best alternative to this RIGHT OUTER JOIN ?

On 2014/11/09 14:11, Tristan Van Berkom wrote: A good SQL rule of thumb: if you can think of a way, so can the DBMS. "... no opportunity to make a good guess" is not true. In some sense, SQLite has had 10 years to make a good guess, and often does. A nested select need not be materialized as a

Re: [sqlite] Locking errors on network

On 2014/11/10 20:22, Mike McWhinney wrote: So SQLite shouldn't be used at all on a network? Aren't there any other provisions to handled the locking errors if/when they occur? It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 2014/11/13 15:01, Dinesh Navsupe wrote: Hi, My need is 23 decimal digits of precision. We work on complex payout calculation engine where in formula outputs are quite large numbers and clients do not want to round off. I do not think that re-stating your need suffices as a good enough

Re: [sqlite] [SQLite] Support for 23,10 Precision number format

On 2014/11/13 19:06, Simon Slavin wrote: On Thu, Nov 13, 2014 at 3:38 PM, Simon Slavin wrote: 100,000,000,000,000,000,000 But he's using the field to store an amount of money in. So why ask for anything with ten places after the decimal point ? No genuine currency

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 2014/11/14 16:32, RP McMurphy wrote: On Tue, 11/11/14, RP McMurphy wrote: > If you can provide any examples where ANALYZE makes a query slower, I suspect the developer team would like > to see them. > After we

[sqlite] Verifying a file is valid SQLite3

Could someone kindly tell me the byte-pattern, offset and length into an SQLite3 file that might suffice to verify that it is indeed an SQLite3 file. I am not very concerned with false positives (as nobody can control all the files in the World), but rather interested in absolute negatives,

Re: [sqlite] Verifying a file is valid SQLite3

Nvm - found it - thanks. It seems the SQlite search engine is better than the Google one - Page ref (in case anyone else is interested): https://www.sqlite.org/fileformat2.html#database_header Specifically point 1.2.1 On 2014/11/16 15:03, RSmith wrote: Could someone kindly tell me the byte

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 2014/11/17 14:48, RP McMurphy wrote: PS: Some administravia; Does anyone know of a way to reduce the posting delay for this list? Is it always like this? Or is it some problem with the gmane site in general? Join the mailing list directly (you can unsubscribe once you got what you were

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 2014/11/17 14:48, RP McMurphy wrote: Upon further analysis it appears that the data "shape" is different in different periods within the table. That is, some sections have the inverse shape to other sections. So it looked like query times would change over time but actually they are changing

Re: [sqlite] Insert Hindi Language

On 2014/11/18 15:12, Richard Hipp wrote: CREATE TABLE example(x TEXT); INSERT INTO example(x) VALUES('हैलो, विश्व'); SELECT * FROM example; The point being that Hindi isn't special, the presented characters are Unicode just like all the others - as long as your DB text encoding is set to

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

On 2014/11/21 08:09, Thane Michael wrote: Many of the answers I came across online implied that it wouldn't be as straightforward as serializing ints and strings. It would be equally difficult in any RDBMS, the difficulty does not lie with the Database, it lies with your objects. There are

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

Hi Darko, Firstly, kindly keep this to the sqlite-users forum and not on the dev forum (the devs read this too, the difference being simply that this one exists to help you, the other one is to discuss development stuff, not to help anyone). Secondly, you are confusing two things. You are

Re: [sqlite] [sqlite-dev] Implementing per-value custom types

On 2014/11/26 15:58, Darko Volaric wrote: I'm not looking for confirmation of ideas, on the contrary, people seem to want to push their own ideas about a database should be used and how I'm not using it correctly, when that is irrlevent to the issue I'm discussing. Maybe more focus on the

Re: [sqlite] "database is locked" for SQLITE_BUSY

On 2014/12/03 13:00, Jonathan Moules wrote: Hi, Just a quick request/suggestion. Currently SQLITE_BUSY events return an error of "Database is locked". Is it possible to change this to "Database is busy" or something similar? I ask because when someone then goes googling for "SQLite database

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 11:55, Gwendal Roué wrote: Hi, Unique indexes make some valid update queries fail. Please find below the SQL queries that lead to the unexpected error: -- The `books` and `pages` tables implement a book with several pages. -- Page ordering is implemented via the `position`

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a

Re: [sqlite] Artificially slow VACUUM by injecting a sleep() somewhere?

On 2014/12/09 03:36, David Barrett wrote: Hi all, great questions: *Re: Why VACUUM.* We vacuum weekly. This particular database is a "rolling journal" -- we are constantly adding new rows to the end of the table, and every week we truncate off the head of the journal to only keep 3M rows at

Re: [sqlite] seeking advice

On 2014/12/09 22:41, Rene Zaumseil wrote: Hi there, I have to store and retrieve up to 2000 parameters. The parameters can have real and integer values. The max. change rate is 100ms and the max. duration is up to some hours. The simple solution would be to use plain binary files. It's fast

Re: [sqlite] replace many rows with one

On 2014/12/10 13:39, Simon Slavin wrote: Dear folks, A little SQL question for you. The database file concerned is purely for data manipulation at the moment. I can do anything I like to it, even at the schema level, without inconveniencing anyone. I have a TABLE with about 300 million

Re: [sqlite] How to speed up database open

On 2014/12/11 13:51, Paul wrote: In my specific case I need to open database as fast as possible. Usual working cycle: open -> select small data set -> close. It is irrelevant how much time it takes to open database when data is being added or updated, since it happens not too often. /Snipped

Re: [sqlite] Counting rows

On 2014/12/11 17:19, Simon Slavin wrote: In my table which had about 300 million (sic.) rows I did this SELECT count(*) FROM myTable; to count the number of rows. After half an hour it was still processing and I had to kill it. I know that the internal structure of a table means that this

Re: [sqlite] How to speed up database open

On 2014/12/11 17:58, Paul wrote: On 2014/12/11 13:51, Paul wrote: I have yet to try and test if dropping stat tables worth the effort. Some databases in fact can grow pretty big, up to few hundred of megabytes// In that case maybe keep the Stat1 tables and there is also the option of

[sqlite] Feature Request - RowCount

To the SQLite devs: After recent discussion about the row-count issue w.r.t. Nulls in primary keys etc. I have been somewhat wrestling with how to improve this from a user perspective. To explain: Most DB Admin tools out there displays the number of rows in a table when you select it or open

Re: [sqlite] Feature Request - RowCount

On 2014/12/13 14:38, Richard Hipp wrote: The "SELECT count(*) FROM table" query already has a special optimization in the b-tree layer to make it go faster. You can see this by comparing the times of these queries: SELECT count(*) FROM table; SELECT count(*) FROM table WHERE 1;

Re: [sqlite] Feature Request - RowCount

On 2014/12/13 21:46, James K. Lowden wrote: So the number of tools with feature X is no measure of the value of X. (Notable example: the tool should keep every query and result in a time-sequenced transcript log, so that prior results can be re-examined and prior queries modified. Most tools

Re: [sqlite] Abnormal memory usage when removing rows within nested transaction from a table, referenced by another table via FK,

On 2014/12/18 12:03, Dan Kennedy wrote: On 12/18/2014 04:16 PM, Paul wrote: I understand. I guess, I'll have to stick to UPDATE <-> INSERT. Thank you for taking your time. Just out of curiosity, I want to ask one more question. How can FK constraint fail if I am removing (replacing) row from

Re: [sqlite] Parentheses in column name

On 2014/12/22 22:19, Federico Jurio wrote: Hi guys, i'm trying to make a simple query using sqlite through gdal library ( http://www.gdal.org/ogr_sql.html ) I have a simple table with two columns: ID and (asd*) (both columns have integer values) I want the minimun value of this columns My

Re: [sqlite] COMMIT nested in SELECT returns unexpected results

On 2014/12/24 12:50, Jim Carroll wrote: I understand that performing a SELECT and nested COMMIT on the same table is not supported in sqlite, but I would have expected a COMMIT on a separate table would not be a problem. Some test code in python however reveals that performing the COMMIT

Re: [sqlite] VACUUM requires 6.7 times space ?

On 2015/01/05 13:32, Dan Kennedy wrote: On 01/05/2015 06:22 PM, Simon Slavin wrote: I have a database file which is 120GB in size. It consists of two huge tables and an index. //... Probably running out of space wherever temp files are created. I haven't done this, but I seem to remember

Re: [sqlite] Can I user sqlite on wp8 native project?

On 2015/01/06 11:24, Bite Forest wrote: I’m developing game with cocos2d-x. But compile sqlite.c in vs, there’r lots of error. Which version of sqlite can I compile through vs in c++ code? SQLite source is in C so it won't compile in C++, but you can just statically link the .obj for it (in

Re: [sqlite] Sqlite problem possible bug

On 2015/01/07 12:13, The Responsa Project wrote: To Whom it amy concern I am trying to use SQLITE and the like statement with wildcards and hebrew when I put in an english string it works correctly, such as Select * from dbname where colname like '%123%' I will get all the entries from

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

On 2015/01/09 01:12, MikeSnow wrote: I was wondering if anyone could let me know where I am going wrong. I am getting the error... "Error while executing query: no such column: t1.*B.Switch-Tower-Sector" but, the column, t1.[*B.Switch-Tower-Sector], does exist. I get results when I do

Re: [sqlite] SQLite 3.8.8 expected in January

The pre-compiled and supplied DLL (sqlite3.dll) seem to be missing an entry point for "sqlite3_enable_load_extension" - I do not see any mention in the update text about altering or removing this feature so I am assuming this might be a compile-time omission? On 2015/01/09 19:23, Richard Hipp

Re: [sqlite] SQLite 3.8.8 expected in January

On 2015/01/10 15:50, Richard Hipp wrote: Yes, it was a compile-time omission. I have uploaded a new DLL that includes the loadable extension interface. Thank you - it works perfectly for all entries. All other tests worked well too, so no new problems to report from this side.

Re: [sqlite] Inserting/removing large number of rows with index

On 2015/01/15 23:18, Baruch Burstein wrote: Hi, If I have a table with an index, and INSERT or DELETE a large number of rows in one statement, does sqlite stop to update the index for each record, or is it smart enough to update the index just once for all the changed records? In a B-Tree

Re: [sqlite] Huge WAL log

On 2015/01/16 11:33, Jan Slodicka wrote: The code schema is as follows: foreach table { BEGIN INSERT INTO table VALUES() INSERT INTO table VALUES() ... COMMIT } Large column values are supplied as parameters, the rest (vast majority) is passed through SQL

Re: [sqlite] Shell tool improvement request

On 2015/01/16 18:33, Simon Slavin wrote: (following description simplified) I have a text file I wanted to .import into a table. The text file has two columns separated by a tab: a word and a number. It starts off like this: ! 32874624 " 239874242 # 98235252 $ 438743824

Re: [sqlite] Malformatted output by sqlite3

On 2015/01/19 12:52, Stephan Buchert wrote: (Prompt erased for easier paste and copy): CREATE TABLE satpos(msec INTEGER, latitude REAL, longitude REAL); INSERT INTO satpos VALUES (86386217,-0.0318895369716216,-167.689719869132); INSERT INTO satpos VALUES

Re: [sqlite] ORDER BY and LIMIT regression

Might this not be a "reverse_unordered_selects" pragma or compile option going wrong, or at least the code making it work getting somehow hooked in the new versions for this query? I have seen similar things when using that pragma (but of course that was intended). Just a thought... On

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

On 2015/01/23 16:51, Walter Williams wrote: I'm trying to use a code first model ///... (snipped) then in the signature... "Do, or do not. There is no try." Thank you for the chuckle. As to the actual question, when you say "when I try

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

On 2015/01/25 15:16, Marcus Bergner wrote: Hi, Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the following inconsistent behaviour: $ ./sqlite3 sqlite> .headers on sqlite> pragma short_column_names; 1 sqlite> pragma full_column_names; 0 sqlite> create table tbl1 (id1

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

On 2015/01/26 04:04, James K. Lowden wrote: On Sun, 25 Jan 2015 23:18:05 +0200 RSmith <rsm...@rsweb.co.za> wrote: There is no documentation in either SQLite or the SQL standard that would lead anyone to believe that behavior is expected - in fact it is very clear about the returned

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

On 2015/01/26 14:00, Tim Streater wrote: On 26 Jan 2015 at 07:33, Hick Gunter wrote: It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause.

Re: [sqlite] "database disk image is malformed" error occurs more

On 2015/01/28 20:06, Mario M. Westphal wrote: 1. I don’t have the damaged databases here so I cannot run the diagnosis myself. The databases are usually too large to upload or transfer. 2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation and Visual Studio 2012).

Re: [sqlite] Truncation of floating point numbers in SQLite?

On 2015/01/29 01:00, Donald Shepherd wrote: I can say there's no string round-trips with fairly high confidence and I am using _bind_double and _column_double. I can also confirm NaNs are a special case, as I've had to write code to store those as a BLOB. So you have a case where you have a

Re: [sqlite] Truncation of floating point numbers in SQLite?

On 2015/01/29 05:05, James K. Lowden wrote: There's no reason to think, if the data are provided in binary form, that they won't be returned in the identical form absent an explicit conversion. If that's not so, I'd sure like to know why. I'm faintly surprised NaNs can't be stored, too. Why

Re: [sqlite] Truncation of floating point numbers in SQLite?

On 2015/01/30 05:49, Donald Shepherd wrote: Trying to retrieve a stored qNaN or sNaN returns a column type of NULL and a value of 0. Thank you for letting us know. Well I suppose that's SQLite's method to answer with errors of the sort, returning NULL (as is the case with div0 for

Re: [sqlite] "database disk image is malformed" error occurs more

On 2015/01/30 14:45, Mario M. Westphal wrote: - The databases in question are stored on a location hard disk or SSD. - If a user stores his database on a NAS box or Windows server, it is accessed directly, via standard Windows file system routines. - From what I can tell, network-based

Re: [sqlite] Encoding question

On 2015/02/02 01:12, Peter Haworth wrote: I'm new to the unicode world so this question may not make sense. The "PRAGMA encoding" statement tells me the encoding of a database. Can I rely on all data in the database having that encoding? For example, if the encoding is UTF8 and a row is

Re: [sqlite] Encoding question

On 2015/02/02 19:37, Peter Haworth wrote: On Mon, Feb 2, 2015 at 9:00 AM, <sqlite-users-requ...@sqlite.org> wrote: From: RSmith <rsm...@rsweb.co.za> To: sqlite-users@sqlite.org Subject: Re: [sqlite] Encoding question Message-ID: <54cebb71.8060...@rsweb.co.za> Content-Type: t

Re: [sqlite] Looking for SQLite schema doc generator (in HTML) for tables, fields, etc

On 2015/02/03 17:34, Gerald Bauer wrote: Hello, I'm looking for a little tool that reads in an SQLite schema (e.g. beer.db, football.db, etc.) and outputs (generates) documentation for tables, fields etc. as a single HTML page or as HTML pages.Any insight appreciated? Actually I

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

On 2015/02/04 20:26, Rael Bauer wrote: Hi, Is it possible to get the information of how many bytes a table is taking up in the database? Not with API calls, but it is possible by running the SQLiteAnalyzer utility afvailable from the same download pages as SQLite3 CLI etc.

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

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

Re: [sqlite] Bug in SQLite FLOAT values

On 2015/02/07 15:47, Abdul Aziz wrote: Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? Please elaborate, my query to create DB is: mSQLiteDatabase.execSQL("CREATE TABLE " + tableName + " ( " + EVENT_TIME + " INTEGER, " + SYSTEM_TIME + "

[sqlite] Performance measurement

Hi all, I would like to gain more knowledge on the performance measurments and (more specifically) their possible implications and maybe some Database theory to boot. The figures of interest are the statement quantitative values which return simple counters such as Table scans, Virtual

Re: [sqlite] Dropping Trigger from Table

You seem to have a habit of inventing bogus SQL and then, when it doesn't work, ask for the correct form on here. If I was the forum police I would suggest reading some freely available on-line documents, but since I'm not - here's an attempt at answering the question: SQL is not a

Re: [sqlite] Dropping Trigger from Table

On 2013/11/01 14:41, techi eth wrote: I think so checking possible cases of any sqlite operation should not be called as bogus. Where ever I am not sure I will ask expert help & I have got all the time right answers. My point here is (and was) that the SQL you said that you have tried, is

Re: [sqlite] Dropping Trigger from Table

Thanks for all comments & suggestion. My Point : If we have trigger creation on per table basis then why can't we have dropping also on table basis. It is not good to have feature where each table owner are free to allow the way he want to create trigger. I can't quite decipher what you

[sqlite] Bug Report - Analyzer app

Hi there, I use the sqlite3_analyzer.exe app get some data about tables (It's very useful by the way - thanks.) The newest version downloaded some days ago gave me import errors for the produced file, so I tried loading it manually, and then got the SQL which follows for one of my small DB

Re: [sqlite] Bug Report - Analyzer app

be an easy fix, but may be very wrong - either way, kindly let us know if a fix is on the table or not. Thanks! Ryan On 2013/11/02 09:53, RSmith wrote: Hi there, I use the sqlite3_analyzer.exe app get some data about tables (It's very useful by the way - thanks.) The newest version

Re: [sqlite] Bug Report - Analyzer app

Thank you kindly and forgive me for not seeing this earlier... It works perfectly. On 2013/11/06 22:53, Richard Hipp wrote: http://www.sqlite.org/src/info/42a11e7464 ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Need suggestion for database scheama

On 2013/11/08 05:47, James K. Lowden wrote: Not that you asked, but I also suggest you consider dropping the "Tbl" from the table names. Noting that at table is a table in its name is like calling every file "data". It makes it harder to read and conveys no information. I myself prefer plurals

Re: [sqlite] Row Level Locking as in InnoDB

On 2013/11/11 08:40, Raheel Gupta wrote: I guess a Row level locking could be difficult but a Page Level locking could be not that difficult. ATM "db level locking" : If DB locked throw busy error In not locked lock db, let the writer do its thing For Page level locking (I think you could

Re: [sqlite] Simple string question

Suppose I have a table with one text column. I insert like this: INSERT INTO my_table (col1) VALUES ('arbitrary UTF-8 string'); * Isn't it true that the string must indeed be surrounded by single quotes as I do above? * Isn't it true that I have to replace all occurrences of ' in the

Re: [sqlite] Intended use case for 'without rowid'?

It can already be downloaded and is quite exciting. Yes there would be a space-saving, but it is rather minimal. The real advantage is removing one complete lookup reference cycle from a Query where the Primary key is anything other than an INTEGER-typed primary key. When you have an INTEGER

Re: [sqlite] Intended use case for 'without rowid'?

On Fri, Nov 15, 2013 at 7:33 AM, RSmith <rsm...@rsweb.co.za <mailto:rsm...@rsweb.co.za>> wrote: Yes there would be a space-saving, but it is rather minimal. The real advantage is removing one complete lookup reference cycle from a Query... That was my origina

Re: [sqlite] Intended use case for 'without rowid'?

Oh and of course the space saving for simple reference tables (basic Value-for-ref-lookups) would be great. To be sure, this does not just affect Text Keys, but all non-INTEGER primary keys, right? ___ sqlite-users mailing list

Re: [sqlite] Intended use case for 'without rowid'?

I'm ALWAYS looking for a faster query (Who isn't? -- Except those edge cases where management thinks the software is broken because the query is TOO fast and doesn't trust the results) but the loss of some common use functionality kind of has me wondering "Why?" Well yes but... Firstly, using

Re: [sqlite] Intended use case for 'without rowid'?

Pepijn & Peter - I'm not sure how this will be an issue for the sort of existing systems you describe? You will need to actually physically change your current schemas to produce the mentioned problems, which if you don't, you have nothing to worry about. The only people I think should plan

Re: [sqlite] Intended use case for 'without rowid'?

Here's a thought: What does your hypothetical function return for a table defined as follows: CREATE TABLE strange(rowid TEXT, _rowid_ TEXT, oid TEXT); That table has a rowid, but it is completely inaccessible to the application. Does your function return TRUE or FALSE? My point:

Re: [sqlite] Intended use case for 'without rowid'?

Perhaps we should make the allowed DDL subset a part of the spec. That way we make explicit what is allowed and anything outside of that is forbidden. Pepijn Perhaps. It would involve a rather large document though, one which an average user is sure to skip over but at least it provides

Re: [sqlite] help writing DELETE with JOIN

On 2013/11/16 20:02, David M. Cotter wrote: okay i realize my requirements were wrong, here's a better summary: the plID (playlist ID) in the song table is different (the OLD id 33), the plID in the playlist table is the new ID 35, so i have to test them separately. the song ID's must match

Re: [sqlite] SQLite server/file-locking scenario

Thanks so much for the reply. Sorry for the ignorance, but wouldn't only the sectors (page cache) that are being written need to be cached? And I was trying to read up on how sqlite does atomic writes, but doesn't the way sqlite handles atomic writes guarentee that the file is *always* in a

Re: [sqlite] query optimization

I might be missing something extraordinarily obvious... but I cannot understand the use case for this logic you have. My first response was to just use "delete from emp where key=123" and be done with it, who cares what the name is, right? But then it dawned on me that you may for some reason

Re: [sqlite] query optimization

= '' ) OR ( name = ?2 )); I think this is closer to the intended - thanks, Ryan On 2013/11/18 12:56, RSmith wrote: I might be missing something extraordinarily obvious... but I cannot understand the use case for this logic you have. My first response was to just use "delete from emp where ke

Re: [sqlite] query optimization

re which but between your and my solutions both are covered though, so I hope the OP gets sorted out - if not, let us know... On 2013/11/18 13:55, Kees Nuyt wrote: On Mon, 18 Nov 2013 13:04:31 +0200, RSmith <rsm...@rsweb.co.za> wrote: Oops, misprint... name won't be null of course, the

Re: [sqlite] query optimization

sert ( sOriginStr(n, "'"); } return "'" + sOriginStr + "'"; } where "'" is a single quote enclosed in double-quotes (incase that was not obvious) On 2013/11/18 14:24, d b wrote: Hi RSmith, Thanks. Still, I could not delete with single query. cr

Re: [sqlite] query optimization

Thanks RSmith. It works. But, I am looking for single query for prepared statements. That's the actual struggle for me. Ok, but you give code examples that has nothing to do with prepared statements. Giving this one last push, I iwll try to ignore all you have said and simply show the best

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

On 2013/11/19 00:45, Nico Williams wrote: Thinking about how SQLite3 would implement WITHOUT ROWID, it seems logical that a set of columns to be used as the primary indexed is required, but that they be unique shouldn't be. ..and... The implied UNIQUE constraint is just an unfortunate

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

On 2013/11/19 08:37, Nico Williams wrote: More generally however, it's clear that a unique constraint is not necessary to make rowid-less tables work (see the MySQL example), SQL doesn't require unique constraints, and it's not clear that just because you (or I) lack imagination that unique

Re: [sqlite] SQlite3 Query Format for Export

What will be the query format to export database table data into different export format (CSV,HTML,XML,SQL) ? This is not an SQL function. Do you need it exported to some format once, or do you need this often (such as providing it as a standard functionality to users?) If you need it

Re: [sqlite] Why must WITHOUT ROWID tables have PRIMARY KEYs?

Hi Nico, thanks for the discussion - although this thread is dangerously close to becoming a stale debate rather than a helpful forum discussion. In this light, I will try to be brief and then fight the urge to reply again. You still seem to try and sell the usability of non-unique tables to

  1   2   3   4   >