[sqlite] Use journal after program crashed
Until now I did not need it, but better inform before I do. ;-) When a program crashes it is possible that you have a journal file with things that are not committed. Is there a way to find out what those are? (And selectively commit?) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use journal after program crashed
2016-05-25 3:49 GMT+02:00 Richard Hipp <d...@sqlite.org>: > On 5/24/16, Cecil Westerhof <cldwester...@gmail.com> wrote: > > Until now I did not need it, but better inform before I do. ;-) > > > > When a program crashes it is possible that you have a journal file with > > things that are not committed. Is there a way to find out what those are? > > (And selectively commit?) > > Are you talking about a rollback journal or a write-ahead log (WAL) file? > ​Probably both. ;-) (If that is possible.) As said: at the moment I do not need it. But I just want to be prepared when I do. I have had a few times when playing with SQLite that my program crashed. In those instances it was not a big problem that I lost my data. But it made me think about what to do if it would be a problem. Fast response by the way. :-) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Setting locale for ORDER BY
I want that ? comes after e, not after z. When using Python I get the right
ordering of ORDER BY with the following statement:
locale.setlocale(locale.LC_ALL, 'en_GB.UTF-8')
but I am now using Java. I am using here:
Locale.setDefault(new Locale("en_GB.UTF-8"));
But that does change the ordering of ORDER BY (? comes after z). What is
the correct way to get the same result in Java?
--
Cecil Westerhof
[sqlite] Selecting total and not used in one query
?I have the following query: SELECT (SELECT COUNT(*) FROM proverbs)AS Total , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used But I want something like: ?SELECT (SELECT COUNT(*) FROM proverbs)AS Total , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used , (Total - Used) AS Free ?? ?But then I get: ?no such column: Total It can be done, because I have it done in the past, but obviously a little different. :'-( ?Anybody knows how it can be done?? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 15:24 GMT+01:00 R Smith : > > > On 2015/12/12 4:18 PM, Cecil Westerhof wrote: > >> ?I have the following query: >> SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> >> But I want something like: >> ?SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> , (Total - Used) AS Free >> > > SELECT > (SELECT COUNT(*) FROM proverbs) AS Total > , (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used > , (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free > ?That is what I am using now, but it is inefficient. This uses three scans instead of two.? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 16:06 GMT+01:00 Luuk : > On 12-12-15 15:18, Cecil Westerhof wrote: > >> ?I have the following query: >>> SELECT >>> (SELECT COUNT(*) FROM proverbs) AS Total >>> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >>> >>> But I want something like: >>> ?SELECT >>> (SELECT COUNT(*) FROM proverbs) AS Total >>> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >>> , (Total - Used) AS Free >>> ?? >>> ?But then I get: >>> ?no such column: Total >>> >>> It can be done, because I have it done in the past, but obviously a >>> little >>> different. :'-( >>> >>> ?Anybody knows how it can be done?? >>> >>> >> >> SELECT Total, Used, Total-Used as Free >> FROM (SELECT (SELECT COUNT(*) >> FROM proverbs) AS Total , >> (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used) >> > > or: > select Total, Used, Total-Used as Free from (select count(*) as Total, > count(used) as Used from proverbs) > ?That is what I needed. I prefer the second one. Is probably a little less efficient, but much clearer.? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 16:23 GMT+01:00 Igor Tandetnik : > On 12/12/2015 9:18 AM, Cecil Westerhof wrote: > >> But I want something like: >> ?SELECT >> (SELECT COUNT(*) FROM proverbs)AS Total >> , (SELECT COUNT(*) FROM proverbs WHERE NOT used IS NULL) AS Used >> , (Total - Used) AS Free >> > > select count(*) Total, >sum(used is not null) Used, >sum(used is null) Free, >sum(any_boolean_condition) CountSatisfyingCondition > from proverbs; ?I like this one especially. Thanks. -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 19:00 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 2:42pm, Cecil Westerhof wrote: > > >> SELECT > >>(SELECT COUNT(*) FROM proverbs) AS Total > >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NOT NULL) AS Used > >>, (SELECT COUNT(*) FROM proverbs WHERE used IS NULL) AS Free > >> > > > > ?That is what I am using now, but it is inefficient. This uses three > scans > > instead of two.? > > Do you have an index on the column 'used' ? ?Not at the moment. But maybe that is a good idea.? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 19:17 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 6:14pm, Cecil Westerhof wrote: > > > ?Not at the moment. But maybe that is a good idea.? > > With clauses like > > >>> WHERE used IS NOT NULL) AS Used > > >>> WHERE used IS NULL > > It should transform the work done. > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said that you should not use an index on columns that use a high number of NULL values. At the moment that is true. So I should not use an Index?? -- Cecil Westerhof
[sqlite] Selecting total and not used in one query
2015-12-12 21:10 GMT+01:00 Simon Slavin : > > On 12 Dec 2015, at 7:52pm, Cecil Westerhof wrote: > > > ?Here http://www.tutorialspoint.com/sqlite/sqlite_indexes.htm it is said > > that you should not use an index on columns that use a high number of > NULL > > values. At the moment that is true. So I should not use an Index?? > > The tutorial thinks that you would use the index to pick out specific > values in the column. Your own use is to differentiate between columns > which have values and those which don't. > > Try it. Create the index. I promise it won't corrupt your database. > Check to see whether it improves your times for the SELECT I quoted. If it > turns out not to improve things then you can delete the index with no harm > done. > ?At the moment the select takes most of the time one millisecond. ?So I am afraid I will not see an improvement. But in the future I probably also want to select on date (what will be in used when it is not NULL), so for then it would be handy to have an index. Well lets just do it. ;-) It is a bit strange: after creating the index the database is still 176 KB. (It is a small one: I just started.) I would have expected the index to make the file bigger. Also now: SELECT Total , Used , Total - Used as Free FROM ( SELECT COUNT(*) AS Total , COUNT(used) AS Used FROM proverbs ) is faster as: SELECT COUNT(*) AS Total , SUM(used IS NOT NULL) AS Used , SUM(used IS NULL) AS Free FROM proverbs While before the index it was the other way around. It looks like the first is speeded up and the second slowed down. But it is nothing to really worry about I think. -- Cecil Westerhof
[sqlite] Putting an index on a boolean
I am playing with SQLite. I am thinking about writing an application for projects. At the moment I have the following: CREATE TABLE `projects` ( `projectID` TEXTPRIMARY KEY, `groupID` TEXT, `isPersonal`INTEGER NOT NULL CHECK(isPersonal in (0, 1)), `name` TEXT, `description` TEXT, `outcome` TEXT ); CREATE INDEX projects_groupID_idx ON projects(groupID); CREATE INDEX projects_isPersonal_idx ON projects(isPersonal); ?I like to differentiate between personal and non personal projects. Is it a good idea to put a index on isPersonal? By the way: I am thinking about using UUID for projectID and groupID, but I heard somewhere that it was a bad idea to use UUID for an indexed field. Is this true?? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
2015-12-12 21:45 GMT+01:00 Richard Hipp : > On 12/12/15, Cecil Westerhof wrote: > > I am playing with SQLite. I am thinking about writing an application for > > projects. At the moment I have the following: > > > > CREATE TABLE `projects` ( > > `projectID` TEXTPRIMARY KEY, > > `groupID` TEXT, > > `isPersonal`INTEGER NOT NULL CHECK(isPersonal in (0, 1)), > > `name` TEXT, > > `description` TEXT, > > `outcome` TEXT > > ); > > CREATE INDEX projects_groupID_idx > > ON projects(groupID); > > CREATE INDEX projects_isPersonal_idx > > ON projects(isPersonal); > > > > ?I like to differentiate between personal and non personal projects. Is > it > > a good idea to put a index on isPersonal? > > No, usually not. An exception would be if the boolean is almost > always true or almost always false. Then using a partial index on > (the infrequent value of) that boolean might make sense. > ?I do not think that will be the case. It is even possible that one time it is mostly true and another time mostly false. I will remove the index. > > By the way: I am thinking about using UUID for projectID and groupID, > but I > > heard somewhere that it was a bad idea to use UUID for an indexed field. > Is > > this true?? > > > > I think you might have misunderstood. UUID is almost always a good > field to index. > ?I was told because of the nature of random UUID (what I will be using) it is hard to create a good index. The article said that data that is really random cannot be indexed very efficient. But I do not have to worry about it then. :-) It has been a few years back, so it is also possible that the problem is solved nowadays. ? > One other point: The use of grave accents to quote column names is a > mysql-ism. SQLite also supports that for compatibility. But you > still shouldn't do it. The proper SQL-standard way is double-quote. > ?That is funny: I did not use them at first (or double). But I am using 'DB Browser for SQLite' and this shows those, so I thought that ? ?I should use them.? -- Cecil Westerhof
[sqlite] Integrity check with a SQL command
I have the following tables: CREATE TABLE "projects" ( "projectID" TEXT PRIMARY KEY, "groupID" TEXT, "isPersonal"INTEGER NOT NULL CHECK(ispersonal in (0, 1)), "name" TEXT, "description" TEXT, "outcome" TEXT ); CREATE INDEX projects_groupID_idx ON projects(groupID); CREATE TABLE "subprojects" ( "subprojectID" TEXT PRIMARY KEY, "projectID" TEXT, "parentID" TEXT, "name" TEXT, "description" TEXT, "outcome" TEXT ); CREATE INDEX subprojects_projectID_idx ON projects(projectID); CREATE INDEX subprojects_parentID_idx ON subprojects(parentID); ?The idea is that a project can have several subprojects. And a subproject can also have several subprojects. Is there a way to use a SQL statement to verify that the data is not corrupt? (Everything should be a tree.) Also is there a SQL command to verify the debt is not more as for example five?? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
2015-12-12 22:07 GMT+01:00 Darren Duncan : > On 2015-12-12 12:56 PM, Cecil Westerhof wrote: > >> By the way: I am thinking about using UUID for projectID and groupID, >>>> >>> but I >>> >>>> heard somewhere that it was a bad idea to use UUID for an indexed field. >>>> >>> Is >>> >>>> this true?? >>>> >>> >>> I think you might have misunderstood. UUID is almost always a good >>> field to index. >>> >> >> ?I was told because of the nature of random UUID (what I will be using) it >> is hard to create a good index. The article said that data that is really >> random cannot be indexed very efficient. But I do not have to worry about >> it then. :-) It has been a few years back, so it is also possible that the >> problem is solved nowadays. >> > > Cecil, it isn't about randomness, it is about uniqueness or cardinality. > The fields that index the best are ones with many different values, in > particular key fields where every record has a different value from every > other record. UUIDs have this quality in spades. It is even more important > to index such fields if you will either be searching/filtering on them or > if they are the parent in a foreign key constraint. This has always been > the case, its not a new thing. > ?That was what that (old) article said: because the data was completely random it was hard to create a balanced tree for the index. I did find it a little strange, but I am not an expert on creating balanced trees for an index. But again: I am happy that it is not a point. -- Cecil Westerhof
[sqlite] Putting an index on a boolean
2015-12-12 22:12 GMT+01:00 Mark Hamburg : > Though to the extent that speed is proportional to data size, it would be > good to use something other than hexadecimal to store UUIDs. Binary blobs > would be the most compact, but ASCII85 encoding would work well if you need > strings. > > Also, if these values are reused repeatedly as I suspect projectID and > groupID might be, then it may be useful to intern them into a table and use > integer keys. We got a noticeable performance improvement when I made that > sort of change recently in our project. (I also implemented a > string-to-integer-to-string cache that sits ahead of hitting the database.) > ?That was something I was wondering about. I was saving this question for when I got further ahead. But it does not hurt to have info about this. :-D
[sqlite] Integrity check with a SQL command
2015-12-12 22:13 GMT+01:00 Richard Hipp : > On 12/12/15, Cecil Westerhof wrote: > > I have the following tables: > > CREATE TABLE "projects" ( > > "projectID" TEXT PRIMARY KEY, > > ); > ?? > > CREATE INDEX subprojects_projectID_idx > > ON projects(projectID); > > Don't create indexes on primary keys. Doing so still gives a correct > answer, but it wastes space and CPU cycles. > ?Stupid error. :'-( Should be: ?CREATE INDEX subprojects_projectID_idx ON subprojects(projectID);? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
2015-12-12 22:44 GMT+01:00 Keith Medcalf : > > The first question(s) I would ask are: > Are all the fields case sensitive? (according to your definition they are) > ?Do you mean the name or the contents? ? > Are any of them, other than the primary key, unique? (according to your > definition they are not) > ?Only the primary key is unique. Maybe I should make name unique to, but it could be possible to have to projects with the same name in different groups I think. ? > Other than the isPersonal column all of the columns permit a NULL entry. > Is this your intent (it is what is written) > ?When thinking more about it, that should only be groupID. I think the others should always be filled. ? > One would presume (based on the English meanings of the column names) > that the groupid groups multiple projects together. How do you intend to > insure the group name is correct since you are repeating it in every > record. Do you not think it may be more appropriate to have a table of > groups, and have the projects.groupid be a foreign key into the groups > table? > ?I have a groups table also. But did not show it, because I did not think it important. It is: CREATE TABLE "groups" ( "groupID" TEXT PRIMARY KEY, "name"TEXT ); And probably I should make name unique.? -- Cecil Westerhof
[sqlite] Putting an index on a boolean
2015-12-12 22:50 GMT+01:00 Keith Medcalf : > > > One other point: The use of grave accents to quote column names is a > > > mysql-ism. SQLite also supports that for compatibility. But you > > > still shouldn't do it. The proper SQL-standard way is double-quote. > > > ?That is funny: I did not use them at first (or double). But I am using > 'DB > > Browser for SQLite' and this shows those, so I thought that > > ?I should use them.? > > Like the Windows Veneer of Long File Names and permitting embedded > "special characters" in filenames, you only need to use quotes identifying > field names if you are making the atrocious error of using restricted > symbols or words as column names. If you do not do that, then you do not > need them. > ?I can go back to not using them. :-D -- Cecil Westerhof
[sqlite] Set update time, but not always
I have a table where I would most of the time update a field lastChecked to current_date when I update the record. But it is possible that I sometimes want to update a record without updating lastChecked. Is this possible, or should I update it (almost) always manually? -- Cecil Westerhof
[sqlite] Checking Foreign Keys
I am continuing with my exploration of SQLite. :-) At the moment I am working with Foreign Keys. They need to be enabled. When you do not do this it is possible to enter records that break the Foreign Key rules. Is there a way to check for this. For example in a session where Foreign Keys where not enabled I did the first INSERT from: https://www.sqlite.org/foreignkeys.html When opening the database in a session with Foreign Keys enabled, is there a method to find this record that breaks the rules? -- Cecil Westerhof
[sqlite] Set update time, but not always
2015-12-13 13:18 GMT+01:00 R Smith :
>
> On 2015/12/13 1:31 PM, Cecil Westerhof wrote:
>
>> I have a table where I would most of the time update a field lastChecked
>> to
>> current_date when I update the record. But it is possible that I sometimes
>> want to update a record without updating lastChecked. Is this possible, or
>> should I update it (almost) always manually?
>>
>
> Not sure what you mean by this... You can either have a field update
> automatically, or manually. There is no "sometimes" update automatically.
>
?That was what I thought, but it never hurts to verify.?
> If you can define another field or table or some way of specifying whether
> the date updating should happen, you could use an ON-UPDATE trigger to
> update the row's lastChecked when some other queryable value is TRUE - but
> in my experience it is much easier in this case to simply have your program
> code decide and then add the date update bit to the update query when
> needed.
>
?That was what I was thinking: in all the statements where it is required I
add:
lastChecked = CURRENT_DATE?
?and the one situation it is not needed I do not add it.
Also - SQLite doesn't have a MySQL-esque "on_update_current_datetime"
> specification for a column - it has to be a trigger, though I have used
> DEFAULT values that set current date/time with success - like this:
>
> CREATE TABLE t (
> a INT,
> b NUMERIC DEFAULT (datetime('now','localtime'))
> );
>
?I use something like that already, but sligth?ly different, because I only
need the date:
entered TEXTDEFAULT CURRENT_DATE
--
Cecil Westerhof
[sqlite] Checking Foreign Keys
2015-12-13 13:52 GMT+01:00 R Smith : > > > On 2015/12/13 2:17 PM, Cecil Westerhof wrote: > >> I am continuing with my exploration of SQLite. :-) >> >> At the moment I am working with Foreign Keys. They need to be enabled. >> When >> you do not do this it is possible to enter records that break the Foreign >> Key rules. Is there a way to check for this. >> >> For example in a session where Foreign Keys where not enabled I did the >> first INSERT from: >> https://www.sqlite.org/foreignkeys.html >> >> When opening the database in a session with Foreign Keys enabled, is there >> a method to find this record that breaks the rules? >> > > No. > > Foreign Key checks only happen on data changes. I don't think there would > be a point of turning off FK checks, adding non-relation data to the > tables, then turning it back on, and simply get a permanent error condition. > ?I agree, but you never know what someone else is doing. ? -- Cecil Westerhof
[sqlite] Checking Foreign Keys
2015-12-13 13:53 GMT+01:00 Dominique Devienne : > On Sun, Dec 13, 2015 at 1:17 PM, Cecil Westerhof > wrote: > > > At the moment I am working with Foreign Keys. They need to be enabled. > When > > you do not do this it is possible to enter records that break the Foreign > > Key rules. Is there a way to check for this. > > > > For example in a session where Foreign Keys where not enabled I did the > > first INSERT from: > > https://www.sqlite.org/foreignkeys.html > > > > When opening the database in a session with Foreign Keys enabled, is > there > > a method to find this record that breaks the rules? > > > https://www.sqlite.org/pragma.html#pragma_foreign_key_check > ?That is very interesting information. Thanks. -- Cecil Westerhof
[sqlite] Locked database
I have a crontab job which uses a SQLite database. Sometimes this database is locked because I used SQLite DB Browser, but did not Write or Revert Changes. It looks like that when a database is locked there is a file with the database name with -journal appended to it. Can I count on this? Then I could write a script to warn me about the lock. -- Cecil Westerhof
[sqlite] Locked database
2015-12-14 12:40 GMT+01:00 Clemens Ladisch :
> Cecil Westerhof wrote:
> > I have a crontab job which uses a SQLite database. Sometimes this
> database
> > is locked because I used SQLite DB Browser, but did not Write or Revert
> > Changes. It looks like that when a database is locked there is a file
> with
> > the database name with -journal appended to it. Can I count on this?
>
> The -journal file exists when there is some changed data that might
> need to be rolled back. It is possible for the DB to be locked before
> some changed data is actually written.
>
> In WAL mode, there is no journal. (And if your job is read only, it
> then would not be blocked by concurrent writes.)
>
?No, I need to write also.?
> > Then I could write a script to warn me about the lock.
>
> You could use SQLite to check whether the DB is locked:
>
> if ! sqlite3 my.db "begin immediate"; then
> echo "is locked"
> fi
>
?Thanks, I improved a little on it:
sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
errorCode="${?}"
if [[ "${errorCode}" -eq 5 ]] ; then
printf "${DATABASE} is locked\n"
elif [[ "${errorCode}" -ne 0 ]] ; then
printf "Error ${errorCode} while accessing ${DATABASE}\n"
else
printf "${DATABASE} is free\n"
fi
I saw that when it is locked I get back a 5. Is this always the case? In
this way I can see the difference between a lock and another error.
?If this code is correct I rewrite it for crontab. ;-)?
--
Cecil Westerhof
[sqlite] Geting the errorcode in Java
I have the following code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
public class CheckProverbsLocked {
private CheckProverbsLocked() {
}
public static void main(String [] args) throws Exception {
Connection conn;
Statementstmt;
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:proverbs.sqlite");
stmt = conn.createStatement();
try {
stmt.executeUpdate("begin immediate");
} catch (SQLException e) {
System.out.println(e.getErrorCode());
System.out.println(e.getMessage());
System.out.println(e.getSQLState());
}
stmt.close();
conn.close();
}
}
?I get the following output when the database is locked:
0
database is locked
null
?I would expect the first one to be 5?. What am I doing wrong?
--
Cecil Westerhof
[sqlite] Locked database
2015-12-14 15:14 GMT+01:00 Clemens Ladisch :
> Cecil Westerhof wrote:
> > sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
> > errorCode="${?}"
> > if [[ "${errorCode}" -eq 5 ]] ; then
> > printf "${DATABASE} is locked\n"
> >
> > I saw that when it is locked I get back a 5. Is this always the case?
>
> sqlite3 returns the SQLite error code, and 5 indeed is SQLITE_BUSY.
> (Non-SQL errors result in 1, which would be the same as SQLITE_ERROR.)
>
?And here are the other codes; ;-)
https://sqlite.org/c3ref/c_abort.html?
--
Cecil Westerhof
[sqlite] Geting the errorcode in Java
2015-12-15 18:51 GMT+01:00 gwenn : > Your code looks good to me. > You should report an issue here: https://github.com/xerial/sqlite-jdbc > ?Done.? -- Cecil Westerhof
[sqlite] Locked database
2015-12-27 18:11 GMT+01:00 Yuriy M. Kaminskiy :
> (I know, I'm a bit late for discussion, but...)
>
> Cecil Westerhof
> writes:
>
> > 2015-12-14 12:40 GMT+01:00 Clemens Ladisch :
> >
> >> Cecil Westerhof wrote:
> >> > I have a crontab job which uses a SQLite database. Sometimes this
> database
> >> > is locked because I used SQLite DB Browser, but did not Write or
> Revert
> >> > Changes. It looks like that when a database is locked there is a file
> with
> >> > the database name with -journal appended to it. Can I count on this?
> >>
> >> The -journal file exists when there is some changed data that might
> >> need to be rolled back. It is possible for the DB to be locked before
> >> some changed data is actually written.
> >>
> >> In WAL mode, there is no journal. (And if your job is read only, it
> >> then would not be blocked by concurrent writes.)
> >>
> >
> > ?No, I need to write also.?
>
> Still, if read-only concurrent transactions are more likely, it will get
> rid of (some) waiting on locks.
>
?I know and in a program where I am only reading there is no problem.
?
> >> > Then I could write a script to warn me about the lock.
> >>
> >> You could use SQLite to check whether the DB is locked:
> >>
> >> if ! sqlite3 my.db "begin immediate"; then
> >> echo "is locked"
> >> fi
> >>
> >
> > ?Thanks, I improved a little on it:
> > sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
>
> ***[1]
>
> > errorCode="${?}"
> > if [[ "${errorCode}" -eq 5 ]] ; then
> > printf "${DATABASE} is locked\n"
> > elif [[ "${errorCode}" -ne 0 ]] ; then
> > printf "Error ${errorCode} while accessing ${DATABASE}\n"
> > else
> > printf "${DATABASE} is free\n"
> > fi
>
> ***[2]
>
> This check is racy and must not be used (What will happen if you will
> start concurrent queries in DB Browser somewhere between [1] and [2]?
> Right,
> this check won't detect database is locked, but your later (actual) code
> *will* fail on attempt to lock).
>
?You did not read the problem correctly. The program is only needed to
check if a database is inadvertently locked. In this way I can be notified,
so that the database is not locked when the cron job is going to send
proverbs and need to change the database.
?
> 1) You should set reasonable sqlite3_busy_timeout (e.g. with `sqlite3
> -cmd '.timeout 12' ...` [for 2 minutes timeout]). By default, it is 0,
> so even if database is only shortly locked, it will still fail, even though
> waiting a bit would help.
>
?My experience is that there is a wait of around two seconds. At least in
Bash, Python and Java.
?
> 2b) If you cannot express your DB change as static SQL statement(s)
> (e.g., if some statements use results obtained in previous SELECT
> queries, so you cannot feed your whole SQL code into single sqlite3
> invocation), you should use SQLite binding to some real program language
> (perl, tcl, python,...) instead of sh and sqlite3.
?Well, for just checking if a database is locked I think it is good enough.
But I also wrote a Java program for it:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
public class CheckProverbsLocked {
// We use this class as a program,
// so we make sure the class cannot be initiated
private CheckProverbsLocked() {
}
// Check if the database is locked.
// If it is give message and return 1
// On other error give message about the error and return 2
// Otherwise no message and return 0
public static void main(String [] args) throws Exception {
Connection conn;
Statementstmt;
Class.forName("org.sqlite.JDBC");
conn =
DriverManager.getConnection("jdbc:sqlite:proverbs.sqlite");
stmt = conn.createStatement();
try {
stmt.executeUpdate("BEGIN IMMEDIATE");
} catch (SQLException e) {
// In a stand-alone program the next two statements are not
necessary,
// but I prefer well written code, so I use them
stmt.close();
conn.close();
if (e.getMessage().equals("database is locked")) {
System.out.println("The database is locked.");
System.exit(1);
} else {
System.err.printf("Unexpected error: %s\n",
e.getMessage());
System.exit(2);
}
}
// In a stand-alone program these statements are not necessary,
// but I prefer well written code, so I use them
stmt.executeUpdate("ROLLBACK");
stmt.close();
conn.close();
}
}
?
?The reason I use getMessage instead of getErrorCode is that getErrorCode
return 0 instead of 5.?
I should generalise it, so it can be used for any SQLite database.
--
Cecil Westerhof
[sqlite] Locked database
2015-12-27 21:51 GMT+01:00 Cecil Westerhof : > invocation), you should use SQLite binding to some real program language > >> (perl, tcl, python,...) instead of sh and sqlite3. > > > ?Well, for just checking if a database is locked I think it is good > enough. But I also wrote a Java program for it: > ??? > I should generalise it, so it can be used for any SQLite database. > ?Generalised version: ? https://github.com/CecilWesterhof/JavaExamples/blob/master/CheckSQLiteLocked.java > -- > Cecil Westerhof > -- Cecil Westerhof
[sqlite] Using colation in Java
When working in Python I can use:
con.create_collation("mycollation", collate)
To change the sort order. How should I do this in Java?
--
Cecil Westerhof
[sqlite] Using colation in Java
2015-12-29 1:35 GMT+01:00 Rowan Worth :
> On 29 December 2015 at 08:23, Cecil Westerhof
> wrote:
>
> > When working in Python I can use:
> > con.create_collation("mycollation", collate)
> >
> > To change the sort order. How should I do this in Java?
> >
>
> Note there are multiple ways to use sqlite from java, so it would help to
> specify which bindings you are using. eg. sqlite4java doesn't provide a
> create_collation call as far as I can tell.
>
> Based on your previous posts I assume you are accessing sqlite via JDBC; I
> can be no help there.
>
?I am using:
https://github.com/xerial/sqlite-jdbc
For the moment being I read everything in an ArrayList and I sort that with
a Collation. Not the best solution, but the output is at the moment only 51
KB, so not a problem now.
Instead of using (as I should):
bw= new BufferedWriter(new FileWriter(outputfile));
stmt = conn.createStatement();
rs= stmt.executeQuery(selectProverbs);
while (rs.next()) {
bw.write(rs.getString("proverb") + "\n");
}
rs.close();
stmt.close();
bw.close();
I am using at the moment:
stmt = conn.createStatement();
rs= stmt.executeQuery(selectProverbs);
while (rs.next()) {
lineList.add(rs.getString("proverb"));
}
rs.close();
stmt.close();
Collections.sort(lineList, collator);
out = new PrintWriter(new FileWriter(outputfile));
for (String outputLine : lineList) {
out.println(outputLine);
}
out.flush();
out.close();
--
Cecil Westerhof
[sqlite] Date as integer
I first had the following table:
CREATE TABLE simpleLog (
datetimeTEXT NOT NULL PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
description TEXT NOT NULL
)
?But datetime then takes 19 bytes. I understood you can also use an Integer
or Real and that this should be more efficient. At the moment I have the
following (I do not expect more as one record in a second):
CREATE TABLE simpleLog (
datetimeINT NOT NULL PRIMARY KEY DEFAULT (strftime('%s')),
description TEXT NOT NULL
)
And a select is then done by (in my select minute is precision enough):
SELECT strftime('%Y-%m-%d %H:%M', datetime, 'unixepoch', 'localtime') as
datetime
,description
FROM simpleLog
ORDER BY datetime DESC
Is this a good way to go, or is there a better way?
--
Cecil Westerhof
[sqlite] Date as integer
??
2015-12-30 2:56 GMT+01:00 Richard Hipp :
> On 12/29/15, Cecil Westerhof wrote:
> > I first had the following table:
> > CREATE TABLE simpleLog (
> >datetimeTEXT NOT NULL PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
> >description TEXT NOT NULL
> > )
> >
> > ?But datetime then takes 19 bytes. I understood you can also use an
> Integer
> > or Real and that this should be more efficient. At the moment I have the
> > following (I do not expect more as one record in a second):
> > CREATE TABLE simpleLog (
> >datetimeINT NOT NULL PRIMARY KEY DEFAULT (strftime('%s')),
> >description TEXT NOT NULL
> > )
> >
> > And a select is then done by (in my select minute is precision enough):
> > SELECT strftime('%Y-%m-%d %H:%M', datetime, 'unixepoch', 'localtime')
> as
> > datetime
> > ,description
> > FROM simpleLog
> > ORDER BY datetime DESC
> >
> > Is this a good way to go, or is there a better way?
>
> What you have should work well.
>
> If you store the date/times as a floating-point Julian Day Number, you
> can omit the 'unixepoch' on query. Use julianday('now') instead of
> strftime('%s','now') on the DEFAULT. That seems a little simpler to
> me, and you get millisecond resolution on the date/times instead of
> just second resolution. But the unix-time format is more familar to
> many programmers, and can be stored in 4 bytes instead of 8.
>
?A resolution of one second is more as enough in this case and Integer is
more efficient as Real. So that is why I choose this solution. If I need a
finer resolution I always can redefine?
?the table. Thanks for the feedback.
--
Cecil Westerhof
[sqlite] Problem with create table and strftime
In a create table I have:
measureDateDATE UNIQUE DEFAULT
(strftime('%Y-%m-%d', 'now')),
but when I look with .schema, I get:
measureDateDATE DEFAULT (Datetime('now')),
The UNIQUE constraint is disappeared and the DEFAULT is changed.
I am using SQLite version 3.7.5.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in .indices
The help says that .indices shows all indices. But it shows at least not the PRIMARY KEY indices. When using: .indices I get nothing. When using: -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug in .indices
Something went wrong, so again. The help says that .indices shows all indices. But it shows at least not the PRIMARY KEY indices. When using: .indices I get nothing. When using: .indices weights I get: sqlite_autoindex_weights_1 -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with create table and strftime
2011/6/6 Igor Tandetnik <itandet...@mvps.org>:
>> In a create table I have:
>>    measureDate       DATE      UNIQUE    DEFAULT
>> (strftime('%Y-%m-%d', 'now')),
>> but when I look with .schema, I get:
>>    measureDate       DATE      DEFAULT (Datetime('now')),
>>
>> The UNIQUE constraint is disappeared and the DEFAULT is changed.
>
> This sounds extremely unlikely. If I had to guess, I'd suspect you already
> have the table in the database, with the old schema, and the new CREATE TABLE
> statement fails.
You are right. I made a mistake. My excuses for the noise.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in .indices
2011/6/6 Igor Tandetnik <itandet...@mvps.org>: > Cecil Westerhof <cldwester...@gmail.com> wrote: >> The help says that .indices shows all indices. But it shows at least >> not the PRIMARY KEY indices. >> When using: >> Â Â .indices >> I get nothing. > > Works for me: > > sqlite> create table t(x text primary key); > sqlite> .indices t > sqlite_autoindex_t_1 > > If you are talking about INTEGER PRIMARY KEY column, then no index is shown > for it because none is created. See also > http://sqlite.org/lang_createtable.html#rowid That is what I am talking about. Something went wrong with my previous e-mail. The strange thing is that the INTEGER PRIMARY key is not shown when using .indices without an argument, but is shown when I give a table. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in .indices
2011/6/6 Igor Tandetnik <itandet...@mvps.org>:
>>> If you are talking about INTEGER PRIMARY KEY column, then no index is shown
>>> for it because none is created. See also
>>> http://sqlite.org/lang_createtable.html#rowid
>>
>> That is what I am talking about. Something went wrong with my previous
>> e-mail.
>>
>> The strange thing is that the INTEGER PRIMARY key is not shown when
>> using .indices without an argument, but is shown when I give a table.
>
> It shows nothing for me either way:
>
> sqlite> create table t(x integer primary key);
> sqlite> .indices
> sqlite> .indices t
> sqlite>
>
> Again - no index is created for an INTEGER PRIMARY KEY column, so none is
> shown.
I was again wrong, but also right. It was not a PRIMARY index, but an
UNIQUE index:
sqlite> .schema weights
CREATE TABLE weights (
id INTEGERPRIMARY KEY,
categoryID INTEGERNOT NULL,
measureDateDATE DEFAULT (DATE('now')),
weight FLOAT NOT NULL,
fatFLOAT,
water FLOAT,
muscle FLOAT,
UNIQUE (
categoryID,
measureDate
)
);
sqlite> .indices
sqlite> .indices weights
sqlite_autoindex_weights_1
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Speed comparison
I saw that there is the need for a speed comparison. I have MySQL (5.1.53) installed (and when necessary I could install PostgreSQL). Would it be interesting if I made those tests? If yes, what is the correct way to do it? I am not using the most recent version (3.7.5), but I suppose that this would not be a big problem. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Speed comparison
There is no need for new speed comparisons? 2011/6/6 Cecil Westerhof <cldwester...@gmail.com>: > I saw that there is the need for a speed comparison. I have MySQL > (5.1.53) installed (and when necessary I could install PostgreSQL). > Would it be interesting if I made those tests? If yes, what is the > correct way to do it? > > I am not using the most recent version (3.7.5), but I suppose that > this would not be a big problem. > > -- > Cecil Westerhof > -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to register on the wiki
I like to make speed comparisons for sqlite. Asking on the list for the used code did not give a result, so I like to try it on the wiki. But you have to login and I do not see a way to register. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to register on the wiki
2011/6/10 Jos Groot Lipman <donts...@home.nl>: > Cecil, > > I think nobody 'gave' you the code because it does not exists. There is no > simple standard set of statements to execute for a speed comparison. > If it were that simple somebody would have retested it long ago. > > Doing a fair comparion also involves all sort of configuraton comparions. > For example: with SQLite you can do a > Â Â Â Â PRAGMA synchronous=OFF > Which will incread the speed at the expense of a slightly greater risk of > corruption. Is it fair to do this setting while comparing? How does another > DBMS do this? I was referring to speed.html. The condensed code shown there should be somewhere uncondensed (I hope). Then I could use this as a starting point. Still it would not be wrong to register on the wiki I think. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The last records of a query
With LIMIT you can get the first N records of a SELECT. Is it also possible to get the last N records? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The last records of a query
2011/6/25 Cecil Westerhof <cldwester...@gmail.com>
> With LIMIT you can get the first N records of a SELECT. Is it also possible
> to get the last N records?
>
Thanks for the answers. I had thought about both options, but was wondering
if I had missed something better. I original opted for the LIMIT/OFFSET
option (because the order is important), but after following the link I
found that this will be an ineffective way in the long run. That is way I
choose the DESC way in combination with tac. (I am using a bash script.):
selectStr="
SELECT categories.desc
,weights.measureDate
,weights.weight
,weights.fat
,weights.water
,weights.muscle
FROM weights
,categories
WHEREcategories.desc = 'Cecil'
AND weights.categoryID = categories.id
ORDER BY weights.measureDate DESC
LIMIT${SHOW_NUMBER}
;
"
for record in $(echo "${selectStr}" | sqlite3 ${DATABASE} | tac) ; do
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The last records of a query
2011/6/25 Kees Nuyt <k.n...@zonnet.nl>
> >selectStr="
> >SELECT categories.desc
> >,weights.measureDate
> >,weights.weight
> >,weights.fat
> >,weights.water
> >,weights.muscle
> >FROM weights
> >,categories
> >WHEREcategories.desc = 'Cecil'
> > AND weights.categoryID = categories.id
> >ORDER BY weights.measureDate DESC
> >LIMIT${SHOW_NUMBER}
> >;
> >"
> >for record in $(echo "${selectStr}" | sqlite3 ${DATABASE} | tac) ; do
>
> If you like, you can tac in SQL:
>
> selectStr="
> SELECT * FROM (
> SELECT categories.desc
>,weights.measureDate
>,weights.weight
>,weights.fat
>,weights.water
>,weights.muscle
>FROM weights
>,categories
>WHEREcategories.desc = 'Cecil'
> AND weights.categoryID = categories.id
>ORDER BY weights.measureDate DESC
>LIMIT${SHOW_NUMBER}
> ) ORDER BY weights.measureDate
> ;
> "
>
Is a lot neater and also a little more efficient. Thanks again.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The last records of a query
2011/6/25 Max Vlasov <max.vla...@gmail.com>
> Use ORDER BY ... DESC. Sure, if you have a large dataset, it'd better be
> indexed.
>
At the moment it is not a large dataset, but it is better to be prepared. I
have the following definition:
CREATE TABLE IF NOT EXISTS weights (
id INTEGERPRIMARY KEY,
categoryID INTEGERNOT NULL,
measureDateDATE DEFAULT (DATE('now')),
weight FLOAT NOT NULL,
fatFLOAT,
water FLOAT,
muscle FLOAT,
UNIQUE (
categoryID,
measureDate
)
);
The select is on the categoryID and measureDate. The UNIQUE constraint is
good enough, or is there needed more?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Again bug in .indices
I have the following (beside some others) table defined:
CREATE TABLE IF NOT EXISTS weights (
id INTEGERPRIMARY KEY,
categoryID INTEGERNOT NULL,
measureDateDATE DEFAULT (DATE('now')),
weight FLOAT NOT NULL,
fatFLOAT,
water FLOAT,
muscle FLOAT,
UNIQUE (
categoryID,
measureDate
)
);
When using
.indices
but when using:
.indices weights
I get:
sqlite_autoindex_weights_1
Should it not be shown in the first instance also?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to check foreign keys
Because SQLite is not a server, it is possible that someone removes a record that should not be removed because of a foreign key constraint. How to check if a database is still correct? There is no check after: PRAGMA FOREIGN_KEYS = ON; I checked. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Again bug in .indices
2011/6/26 Kees Nuyt <k.n...@zonnet.nl> > >When using > > .indices > >but when using: > >.indices weights > >I get: > >sqlite_autoindex_weights_1 > >Should it not be shown in the first instance also? > > It's a matter of taste if it should. > >.indices > will show indices which are created explicitly with a CREATE INDEX > statement. > >.indices tablename > will also show the automatically generated indices, needed to > implement UNIQUE constraints. > > I would call this both a feature and a documentation bug ;) > Well I think it should at least be documented. I caught me by surprise. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check foreign keys
2011/6/26 Kees Nuyt <k.n...@zonnet.nl> > I think Cecil was referring to the fact that some program can > forget to switch foreign_keys to on, and delete rows that are > referenced, or change primary keys. > Yes, that is what I mend. So, his question is not about locking, but about verifying all > references are still pointing to existing rows. > > To answer that question, suppose the following schema: > > CREATE TABLE T1 ( >id1 INTEGER PRIMARY KEY NOT NULL, >contents TEXT > ); > > CREATE TABLE T2 ( >id2 INTEGER PRIMARY KEY NOT NULL, >id1 INTEGER REFERENCES T1(id1) >ON UPDATE CASCADE ON DELETE CASCADE > ); > > Then one could detect missing keys in T1 with: > > SELECT DISTINCT T2.id1 >FROM T2 >LEFT OUTER JOIN T1 ON T2.id1 == T1.id1 >WHERE T1.id1 IS NULL > ORDER BY T2.id1 > ); > Okay, so it can only be done manually? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to check foreign keys
2011/6/27 Simon Slavin <slav...@bigfraud.org> > > I think Cecil was referring to the fact that some program can > > forget to switch foreign_keys to on, and delete rows that are > > referenced, or change primary keys. > > Ah. Okay. Yes, that can present a problem. There's an argument that > switching foreign_keys on should set a bit in the database so it will no > longer work with foreign_keys off. > That would be an improvement. Of-course, it should then also not work with a SQLite version who does not set this bit. Otherwise you keep having this problem. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Meaning of values in sqlite_stat1
When giving: select * from sqlite_stat1; I get: tbl|idx|stat utilities|sqlite_autoindex_utilities_2|67 1 utilities|sqlite_autoindex_utilities_1|67 1 categories|sqlite_autoindex_categories_1|4 1 weights|sqlite_autoindex_weights_1|228 57 1 I am curious about the stat field. The first value seems the number of records (but could also be the last distributed key), the last could be the lowest key in the table. Is this correct? But when there are three values –weights–, what is the meaning of the middle value? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compute percentage?
2011/6/29 Roger Andersson <r...@telia.com> > SELECT round(cast(COUNT(rowid)*100 as real)/(SELECT COUNT(*) FROM > people),2) FROM people WHERE zip="12345"; > Would it not be better to do the CAST on the second SELECT? Then there is only one CAST needed. In this case it does not matter much, but in the general case it could. Or maybe even better instead of doing * 100 in the first select, do * .01 in the second. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Continuous exclusive lock
I am writing a desktop application in which I want to have exclusive rights. In this way I do not need to check if the data has changed when the user of my program wants to change records. Is this possible? -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/6/30 Richard Hipp <d...@sqlite.org> > > > I am writing a desktop application in which I want to have exclusive > > rights. > > > In this way I do not need to check if the data has changed when the > user > > of > > > my program wants to change records. Is this possible? > > > > You can open a transaction as > > > > BEGIN EXCLUSIVE > > > > <http://www.sqlite.org/lang_transaction.html> > > > > "After a BEGIN EXCLUSIVE, no other database connection except for > > read_uncommitted connections will be able to read the database and no > other > > connection without exception will be able to write the database until the > > transaction is complete." > > > > This is how you do that sort of thing. So what you do is … > > > > 1) BEGIN EXCLUSIVE > > 2) Check the result code from step 1 and make sure you got the lock. > > 3) Do SELECTs to check the conditions for your data change and decide > what > > to do. > > 4) If the results of step suggest changes, make them. > > 5) END > > > > It is extremely common to see programs where step 3 of the above is done > > before step 1. Obviously, from your question, you understand this. > > > > Note to SQLite experts: it's not clear to me at what level the exclusive > > lock is maintained on the file. If an app crashes in while an EXCLUSIVE > > lock is held, is the lock released ? Does this vary by platform ? > > > > The BEGIN EXCLUSIVE...COMMIT technique shown above only gives you exclusive > access in rollback mode. If you select WAL mode, then BEGIN EXCLUSIVE > makes > you the exclusive writer, but other processes can continue to read. To get > exclusive access in WAL mode, use PRAGMA locking_mode=EXCLUSIVE. > Is good enough for me. My only problem is that between reading the data and writing the changes, I want to be sure that no one has changed the data. For me that is enough. But it is good to know for when I want really exclusive access. Thanks. I am going to try it. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/6/30 Jean-Christophe Deschamps <j...@antichoc.net>
> >Is good enough for me. My only problem is that between reading the
> >data and
> >writing the changes, I want to be sure that no one has changed the data.
> >For me that is enough.
>
> For this, a simple "Begin immediate;" ... "Commit;" embrassing you
> read-modify-write block will do. Doesn't block readers and works in
> std and WAL journaling modes.
>
Do I understand it correctly that after a commit the database is writeable
again for others? In that case it is maybe better to do a:
*PRAGMA locking_mode = EXCLUSIVE;
*followed by an update of the database. As long as the application is
running, I want to be sure that nobody writes to the database.*
*
I am now using:
stat.execute("" +
"PRAGMA locking_mode = EXCLUSIVE;\n" +
"UPDATE authors\n" +
"SETname = name\n" +
"WHERE id = (\n" +
"SELECT MIN(id)\n"+
"FROM authors\n" +
");" +
"COMMIT;");
The COMMIT is to be sure that this will not unlock the database. It does
not. But I can read the database. Not a problem for me, but I understood it
should not be possible.
I just tried it with "BEGIN IMMEDIATE". Gives exactly the same results. So
properly I should stick to the "BEGIN IMMEDIATE"?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/6/30 Simon Slavin <slav...@bigfraud.org> > > As long as the application is > > running, I want to be sure that nobody writes to the database. > > This may be sensible if the application never waits for any input. Some > sort of bulk-update application, for example, or an overnight batch run. > But if the application pauses for input, aren't you giving a user power > over the database ? They could just start up the application then leave it > in the background while they browse the web. > It is a single user application and database. So I do not think it is a problem. With a multi-user application I would not do this, and check that the data has not changed before doing an update. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/6/30 Simon Slavin <slav...@bigfraud.org> > > On 30 Jun 2011, at 6:34pm, Cecil Westerhof wrote: > > > It is a single user application and database. > > Sorry about that, Cecil. I was remembering some of the bonehead manoeuvres > some of my former clients have pulled, then complained about. > No problem. Better a warning to much as to less. :-D -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/6/30 Jean-Christophe Deschamps <j...@antichoc.net> > If you _need_ exclusiveaccess all along, then start app, "begin > exclusive", do your stuf, "commit" and exit. > The 'problem' is that the application can run for the whole day. > What I don't get is you later say it's a single-user, single-app > use. You don't have to take that much care in this case. In principal could the user use another program to change the database. But maybe I am paranoid. ;-} At the moment I am not even writing to the database. But I like to be prepared. To often things are not important, but when they become, it is to late. (When it is raining, you can not repair your roof. When it is not raining, it is not necessary.) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/7/1 Jean-Christophe Deschamps <j...@antichoc.net> > > > > If you _need_ exclusiveaccess all along, then start app, "begin > > > exclusive", do your stuf, "commit" and exit. > > > > > > >The 'problem' is that the application can run for the whole day. > > Granted. And the 'problem' is ??? > That there is not a clear R-M-W cycle. The data is read when starting up on eight o'clock, but maybe only at three in the afternoon something is changed. But the current solution is good enough. When starting the program for a second time (because you forgot it was already open), the second one is terminated with a message that the database is locked. When I make sure I do a COMMIT after a change and immediately a BEGIN EXCLUSIVE, I do not have to worry about anything. -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Continuous exclusive lock
2011/7/1 Simon Slavin <slav...@bigfraud.org> > >> If you _need_ exclusiveaccess all along, then start app, "begin > >> exclusive", do your stuf, "commit" and exit. > > > > The 'problem' is that the application can run for the whole day. > > There's no problem with this. You can maintain an EXCLUSIVE lock on the > database for the whole day. You can even maintain it while you're waiting > an unknown amount of time for user input. SQLite will happily keep the > database locked the whole time. > That is exactly what I mend to say. Thanks for the clarification. > Some may argue that this would be bad use of resources but that's a > different matter. > Depends on the situation, but in this case I think permissible. There is only one user. Saves me a lot of headache and the user also. Started editing something. Is interrupted. Forgot that he was working on it and starts the program again. In this case the program stops with the message that the table is locked and he can continue where he left of. ;-} -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Statement is not executing
I am not sure if it is a SQLite problem, or a Java problem.
While filling a table I get an error "Statement is not executing". It
happens in the following code:
System.out.printf("%d, %s, %s\n", line, citation, author);
prep.setString(1, citation);
System.out.print("After citation\n");
I see the first output, but not the second. After this there is a -journal
file. What could be happening here?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Statement is not executing
2011/7/3 Simon Slavin <slav...@bigfraud.org>
>
> On 3 Jul 2011, at 12:19am, Cecil Westerhof wrote:
>
> > I am not sure if it is a SQLite problem, or a Java problem.
> >
> > While filling a table I get an error "Statement is not executing". It
> > happens in the following code:
> >System.out.printf("%d, %s, %s\n", line, citation, author);
> >prep.setString(1, citation);
> >System.out.print("After citation\n");
> >
> > I see the first output, but not the second. After this there is a
> -journal
> > file. What could be happening here?
>
> The -journal file just means that some program with a SQLite connection
> quit without closing it. It's a result, rather than a cause, of the program
> crashing. Do not delete/rename/move the -journal file, since when the
> application is relaunched SQLite will use it to figure out if the database
> is corrupt and needs fixing.
>
> The error text "Statement is not executing" is not anything I've ever seen
> from SQLite, so I suspect you're right and that your problem isn't anything
> to do with SQLite. But I don't know what 'prep' is in your app.
>
It is java code. prep is of type preparedStatement ("INSERT INTO
storeCitations (citation, name) VALUES (?, ?);") and I am trying to set the
value for citation. The 2005 times before it goes good, but here it goes
wrong. I also put it on a java list. Maybe they can help me.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Statement is not executing
In case people are wondering. :-D
2011/7/3 Cecil Westerhof <cldwester...@gmail.com>
> I am not sure if it is a SQLite problem, or a Java problem.
>
> While filling a table I get an error "Statement is not executing". It
> happens in the following code:
> System.out.printf("%d, %s, %s\n", line, citation, author);
> prep.setString(1, citation);
> System.out.print("After citation\n");
>
> I see the first output, but not the second. After this there is a -journal
> file. What could be happening here?
>
The problem was a Java problem. I use a preparedStatement and for one reason
or another this went wrong after a little more as 2.000 inserts. I now do a
new preparedStatement every 1.000 inserts.
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Order with another sequence
When I use ORDER BY an ? comes after a z. Is it possible to make an ? come after a z? If it is important I am using SQLite 3.8.6 and Python 3.4.1. -- Cecil Westerhof
[sqlite] Using SQLite for storing photo's
I am thinking about storing my photo's in SQLite. This has to be done in a blob of-course, but I was wondering if there are any caveats, or if anyone has helpful tips. One thing I was wondering: what is the best way to make a backup? I would think a normal dump is not very handy. It would probably better to store the pictures them-self in one file pro picture and only store the (meta-)data in SQL. What would be a good way? -- Cecil Westerhof
[sqlite] Working with booleans
When working with booleans at the moment I use: isActive INTEGER NOT NULL CHECK(isActive in (0, 1)) Is this a good way, or would be using a CHAR with a check op 'T', or 'F' be better? -- Cecil Westerhof
[sqlite] Journal 4.6 KB, after commit database 6 or 7 KB bigger
I have seen several times that the journal was 4.6 KB, but that fter committing the database had grown with 6 or 7 KB. No big problem, but I find it strange. What could be happening here? I would expect it not togrow more as 5 KB. -- Cecil Westerhof
[sqlite] Exclude NULL from index
If I would have a table where a lot of values are NULL. Would it be possible to exclude those records from the index? I would only search for a certain value not for NULL. Or is it the default that they are excluded? -- Cecil Westerhof
[sqlite] Working with booleans
2016-04-14 19:49 GMT+02:00 Jay Kreibich : > > On Apr 14, 2016, at 12:42 PM, Cecil Westerhof > wrote: > > > When working with booleans at the moment I use: > >isActive INTEGER NOT NULL CHECK(isActive in (0, 1)) > > > > Is this a good way, or would be using a CHAR with a check op 'T', or 'F' > be > > better? > > > > Integer 1 and 0 will be the most compact representation in the file format. > ?OK, I will stick with it then. -- Cecil Westerhof
[sqlite] Journal 4.6 KB, after commit database 6 or 7 KB bigger
2016-04-14 20:00 GMT+02:00 Richard Hipp : > On 4/14/16, Cecil Westerhof wrote: > > I have seen several times that the journal was 4.6 KB, but that fter > > committing the database had grown with 6 or 7 KB. No big problem, but I > > find it strange. What could be happening here? I would expect it not > togrow > > more as 5 KB. > > > > The rollback journal records the original content of pages that > already existed in the database at the start of the transaction. New > pages added to the end of the database can be rolled back simply by > truncating the database file, so those pages are not stored in the > rollback journal. > > In WAL mode, the WAL file is a "roll-forward journal" and so the size > of the WAL file is proportional to the size of the transaction, as you > expected. > ?But how is it possible that ?it is smaller? Not really important, but I just like to know ?everything?. ;-) -- Cecil Westerhof
[sqlite] Exclude NULL from index
2016-04-14 19:49 GMT+02:00 Dominique Devienne : > On Thu, Apr 14, 2016 at 7:48 PM, Cecil Westerhof > wrote: > > > If I would have a table where a lot of values are NULL. Would it be > > possible to exclude those records from the index? I would only search > for a > > certain value not for NULL. Or is it the default that they are excluded? > > > https://www.sqlite.org/partialindex.html ?OK, I have to exclude it myself. (Is the sane option I think.) I have to study the page: it has the solution of another problem I was contemplating. From active records the description should be UNIQUE, but it is not necessary for inactive records. I saw that this page has the solution. :-) -- Cecil Westerhof
[sqlite] Using SQLite for storing photo's
2016-04-14 19:41 GMT+02:00 Simon Slavin : > > On 14 Apr 2016, at 6:37pm, Cecil Westerhof wrote: > > > I am thinking about storing my photo's in SQLite. > > Doesn't answer your question, but you should definitely read > > <https://www.sqlite.org/intern-v-extern-blob.html> > ?I would like to have everything in one file. Performance is not very high on my list, because it will be a single user application. But it is something to think about?. -- Cecil Westerhof
[sqlite] Checks with dates
I have a table where I have two fields: toStart and finishBefore. They are both dates and when filled the format should be %Y-%m-%d. How can this be checked? 2016-04-31 should not be accepted. The second part is that when both are filled, then finishBefore should be after toStart. Is that possible to check? -- Cecil Westerhof
[sqlite] Working with UUID
I want to work with UUID's. (Version 4.) What is the smartest way to put a check on this? Also: when using text to save them, you need 36 bytes instead of four bytes. When using a lot, it is better to use blob. I have to post the peculiarities I found with that another time. (For example when writing a lot and crashing the database is changed instead off rolled back.) I am leaning to using blob's (or at least for join tables). Are there reasons to go for one or the another, or is it just individual taste and efficiency when needed? -- Cecil Westerhof
[sqlite] Primary key values can be NULL
I was bitten by the fact that values of a primary key can be NULL. I understood that this is for historical reasons. But there has been a lot of changes. For example a database with partial tables can not be read with a SQLite before 3.8.0. So why is SQLite not changed so primary keys can not have the value NULL? -- Cecil Westerhof
[sqlite] Checks with dates
2016-04-14 21:26 GMT+02:00 Richard Hipp : > On 4/14/16, Cecil Westerhof wrote: > > I have a table where I have two fields: toStart and finishBefore. They > are > > both dates and when filled the format should be %Y-%m-%d. How can this be > > checked? 2016-04-31 should not be accepted. > > > > The second part is that when both are filled, then finishBefore should be > > after toStart. Is that possible to check? > > Untested: > > CHECK( toStart=date(toStart) > AND finishBefore=date(finishBefore) > AND finishBefore>toStart ) > ?Does not work, but I have something to play with. ;-) -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-14 21:46 GMT+02:00 Richard Hipp : > On 4/14/16, Cecil Westerhof wrote: > > For example a database with partial tables can not be read with a > > SQLite before 3.8.0. So why is SQLite not changed so primary keys can not > > have the value NULL? > > You fail to distinguish between "forwards compatible" and "backwards > compatible". > > Upgrading your program from version X to version Y (with Y>X) should > never cause your historical data to become unreadable. That's what > "backwards compatible" means. Upgrading does not break stuff. SQLite > is backwards compatible. > ?Yes that makes sense. But could not a type of PRAGMA be used? So if the PRAGMA is not defined the old functionality and your historical data is save. And if the PRAGMA is defined the new functionality. -- Cecil Westerhof
[sqlite] Using SQLite for storing photo's
2016-04-14 21:37 GMT+02:00 Simon Slavin : > > On 14 Apr 2016, at 8:10pm, Cecil Westerhof wrote: > > > ?I would like to have everything in one file. > > From previous discussions on this list, what you want to do will work > fine. Of course you should make sure your operating system is able to deal > with a very big file, and that your backup protocol works. > ?Well, I will try. I have a ?few? applications that I want to write using SQLite, so I will not get bored for some time. :-D -- Cecil Westerhof
[sqlite] Using SQLite for storing photo's
2016-04-14 22:07 GMT+02:00 Clemens Ladisch : > Cecil Westerhof wrote: > > what is the best way to make a backup? > > With the backup API: <http://www.sqlite.org/backup.html>. > (Also available as .backup in the shell.) > ?I should be more precise in my communication. :'-( I prefer to make my backup with .dump. Then I have just a text based backup. This works fine for the regular data, but not for photos: they can be 13 MB big. (And I am not even using raw. When I am going to do that, they will become much bigger.) So I would like to have my photos split out when making the dump. But maybe it will be better to use external blobs. Something to think about. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-14 22:10 GMT+02:00 Richard Hipp : > On 4/14/16, Cecil Westerhof wrote: > > > > ?Yes that makes sense. But could not a type of PRAGMA be used? So if the > > PRAGMA is not defined the old functionality and your historical data is > > save. And if the PRAGMA is defined the new functionality. > > > > Easier: Just declare the columns in question as NOT NULL. Or use a > WITHOUT ROWID table which *does* enforce NOT NULL. > ?The problem is that you do not expect that values in the primary key can be NULL, so you do not use NOT NULL. I now know that I need to do this and I do it now, but the unaware will be bitten, just as I was. It was not a big problem, because I just started playing with SQLite, but when you have a very big database which gets a lot of data every day and a year later you find out you are bitten by this ? -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 1:19 GMT+02:00 J Decker : > I would total expect any column I created without NOT NULL (double > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied > additionallywhat database does otherwise? MSSQL? > > On Thu, Apr 14, 2016 at 2:56 PM, R Smith wrote: > > > > > > On 2016/04/14 10:23 PM, Cecil Westerhof wrote: > >> > >> 2016-04-14 22:10 GMT+02:00 Richard Hipp : > >> > >>> On 4/14/16, Cecil Westerhof wrote: > >>>> > >>>> Yes that makes sense. But could not a type of PRAGMA be used? So if > the > >>>> PRAGMA is not defined the old functionality and your historical data > is > >>>> save. And if the PRAGMA is defined the new functionality. > >>>> > >>> Easier: Just declare the columns in question as NOT NULL. Or use a > >>> WITHOUT ROWID table which *does* enforce NOT NULL. > >>> > >> The problem is that you do not expect that values in the primary key can > >> be NULL, so you do not use NOT NULL. I now know that I need to do this > and > >> I do it now, but the unaware will be bitten, just as I was. It was not a > >> big problem, because I just started playing with SQLite, but when you > have > >> a very big database which gets a lot of data every day and a year later > >> you > >> find out you are bitten by this ? > > > > > > What makes you "expect" this? Nothing in the SQLite documentation I > hope. > > Is it perhaps habit based on how some other DBs do it? > > > > How will "those who don't know about NOT NULL" be better serviced by a > > pragma which they also don't know about? > > The documentation is very clear on the matter, even a cursory glance > would > > educate "the unaware". > > > > Making more things with more documentation to read, in order to help > people > > with other documentation they didn't read - not a salient course of > action I > > think. > ?Maybe you should read the documentation of SQLite? ;-) If you go to: https://www.sqlite.org/lang_createtable.html You will find: According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. And I am not the only one who has fallen for this problem. I found out because I noticed people complaining about it. Then I checked and found out that it did not work as expected. If a car company creates a car where the brakes does not work when you make a left turn, but they put this in the documentation of the car. Do you think it is the customers fault if the find out this when they want to brake while making a left turn? ?I know that I am new here, but I do not think I should be mocked. You make my suggestion sound needlessly complicated. SQLite could be made in such a way that when it creates a new database, this is with the pragma. Then everything works perfect. The old database does not brake, because it does not have the pragma. And when creating a new database it confirms to the SQL standard and those not create nasty surprises for the unaware. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 1:19 GMT+02:00 J Decker : > I would total expect any column I created without NOT NULL (double > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is applied > additionallywhat database does otherwise? MSSQL? > ?Every database that is not SQLite, because it is the standard.? -- Cecil Westerhof
[sqlite] Checks with dates
2016-04-15 2:29 GMT+02:00 Keith Medcalf :
>
> create table x
> (
> ds text not null check(date(ds) is not null),
> de text not null check(date(de) is not null),
> check(ds < de)
> );
> ??
> ??
> insert into x values ('2016-04-15', '2016-04-13');
> insert into x values ('2016-04-15', '2016-04-17');
> insert into x values ('2016-04-15', '2016-04-32');
>
> The constraint on the column values is that date() is not null (ie, a
> valid date)
>
?Almost, but the dates are allowed to be NULL. But this led me to:
CREATE TABLE x (
ds TEXT CHECK(ds IS NULL OR COALESCE(DATE(ds) == ds, 0)),
de TEXT CHECK(de IS NULL OR COALESCE(DATE(de) == de, 0)),
CHECK(ds IS NULL OR de IS NULL OR ds < de)
)
And this does what I want.
Thanks.
--
Cecil Westerhof
[sqlite] Working with UUID
2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof
> wrote:
>
> > I want to work with UUID's. (Version 4.)
>
>
> Honestly, your post is a little vague. But maybe the following will help.
>
?I am not always very good in asking questions. :'-(
?
> > What is the smartest way to put a check on this?
> >
>
> check what? CHECK constraint? SQLite is dynamically typed, and to enforce a
> column's
> type you can use check constraints.
>
> A blob UUID is 16 bytes, not 4.
?Eeeh. :-*
?
> So 36 vs 16 is a matter of choice.
> I prefer blob guids myself. See below for uid-related insert/select SQL.
>
?I myself also, but I am sometimes told I want to optimise to much. And
when using blobs the checks become much easier.
?
> PS: Note that these are random blobs, so the type-bits don't follow the
> UUID spec. I don't care personally. YMMV.
>
?I think I do and it looks like I got it working.
?
> sqlite> create table t (uid blob primary key CHECK(typeof(uid)='blob' and
> length(uid)=16));
>
?I will go for this. With checks it becomes:
?CREATE TABLE t (
UUID BLOB PRIMARY KEY CHECK(TYPEOF(UUID) = 'blob' AND LENGTH(UUID) = 16
AND substr(HEX(UUID), 13, 1) == '4' AND substr(HEX(UUID), 17, 1) IN ('8',
'9', 'A', 'B'))
)
?
> sqlite> select substr(u,1,8)||'-'||substr(u,9,4)||'-4'||substr(u,13,3)||
>...> '-'||substr(u,17,3)||'-'||substr(u,21,12) from (
>...> select hex(uid) as u from t
>...> );
>
?And this is the way to display them.
?Is there a way to convert a hex string to a blob? I did not find it yet.?
Most are rejected with:
insert into t values (randomblob(16));
Accepted are:
8E51A309-BC11-47CD-88C6-3F428D559B89
6C66572C-3FA1-4BEF-90D1-97678C30CB1D
3A713997-4035-4EDD-8E93-F7E3F579EF1D
Thus it looks like it works.
--
Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 16:43 GMT+02:00 R Smith : > > > On 2016/04/15 2:09 PM, Cecil Westerhof wrote: > >> If you go to: >> https://www.sqlite.org/lang_createtable.html >> >> You will find: >> According to the SQL standard, PRIMARY KEY should always >> imply NOT NULL. Unfortunately, due to a bug in some early >> versions, this is not the case in SQLite. Unless the column >> is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID >> table or the column is declared NOT NULL, SQLite allows NULL >> values in a PRIMARY KEY column. SQLite could be fixed to >> conform to the standard, but doing so might break legacy >> applications. Hence, it has been decided to merely document >> the fact that SQLite allowing NULLs in most PRIMARY KEY >> columns. >> > > I'm sorry, I think we are talking past each other - the above is exactly > my point made. The documentation clearly explains how it works in SQLite, > even if different to the other DBs (and yes, sadly the standard[1] too in > this case) there is no reason to "expect" behaviour different to what is > documented. My point was that your "expectation" was based on your beliefs > and not based on what you've read in SQLite documentation, which can be the > only real incitement of expectation from any "SQLite" system. There does > not exist a single DB engine which implements the SQLite99 (or other) > standard to the letter - PostGres and SQLite probably being some of the > closest adherents. > ?Do you want me to tell you that you always read all the documentation of all the software before you use it? I surely do not. And I think most people do not. To be honest I think you do also not: it is nearly impossible. When reading what the documentation says, I interpret it as: we really would like to comply to the standard. But we cannot. I (think I) gave a way to give the maintainers what they want. If there is a reason that what I suggest will not work, I do not mind to hear it. But just saying (in this case) you should have read the documentation, I find not helpful and to be honest a little cheap. Especially because the fix is mostly for new users. I know what to do now. I just want to make sure that SQLite gives the least surprise. https://en.wikipedia.org/wiki/Principle_of_least_astonishment > And I am not the only one who has fallen for this problem. I found out > >> because I noticed people complaining about it. Then I checked and found >> out >> that it did not work as expected. >> >> If a car company creates a car where the brakes does not work when you >> make >> a left turn, but they put this in the documentation of the car. Do you >> think it is the customers fault if the find out this when they want to >> brake while making a left turn? >> > > This example is a stick-man argument, brakes are legally required to be in > the position they are, but your argument could work if moved sideways > somewhat to, for instance, removing the headlight switch and making it > automated. This would certainly be unexpected and probably not fall inside > any standard, however, if it is documented correctly and fully (perhaps > marked in red) and it is working as documented, nobody would have a real > cause for dismay. This kind of thing does happen - and yes, I have seen > many complaints as you noted, but 10 or even 20 complaints by people who > are all not reading the documentation is still hard to hold up as > validation for breaking backward compatibility to millions of systems. (And > yes, this *is* what you are asking for still). ?I do not think it is. When you add something to the database to signify that a primary key is not allowed to be NULL, then this is not in an old database, ergo in the old database NULLs are allowed. Where does backward compatibility get broken? As I see it, it is as with partial indexes. That is a big change (I think), but it did not break backward compatibility. ?I know that I am new here, but I do not think I should be mocked. You make >> my suggestion sound needlessly complicated. SQLite could be made in such a >> way that when it creates a new database, this is with the pragma. Then >> everything works perfect. The old database does not brake, because it does >> not have the pragma. And when creating a new database it confirms to the >> SQL standard and those not create nasty surprises for the unaware. >> > > Firstly, I am terribly sorry if my response came across as mocking, this > was never my intent and probably only sounds that way cause you are new and > have not seen many of my posts. Please allow me to assure you that I would > never mock you and in no way
[sqlite] Working with UUID
2016-04-15 19:47 GMT+02:00 Dominique Devienne :
> On Fri, Apr 15, 2016 at 3:56 PM, Cecil Westerhof
> wrote:
>
> > 2016-04-15 8:45 GMT+02:00 Dominique Devienne :
> > > On Thu, Apr 14, 2016 at 9:30 PM, Cecil Westerhof <
> cldwesterhof at gmail.com>>
> > wrote:
> >
> > ?Is there a way to convert a hex string to a blob? I did not find it
> yet.?
> >
>
> I didn't find one either (see below), beside formatting a SQL statement
> with a blob literal,
> kinda like sprintf(..., "x'%s'", hex_string), which is hardly an option.
> I'm surprised there's no built-in function or SQL way to do it.
> I suspect there is, and I'm missing something. Hopefully someone will chime
> in.
>
> Perhaps a CTE could do it.
?I have to delve in that also. You are taking my sleep away. ;-)
?
> I'd reach for C/C++ and custom functions for
> such things.
>
?I am probably going to work with Java, but when exploring I am using Bash
and SQLite Browser.
In a program I have been testing with UUID, I had:
ps.setBytes(1, getRandomUUIDBlob());
and the function itself:
private static byte[] getRandomUUIDBlob() {
ByteBuffer bb;
byte[] bytes = new byte[16];
UUIDuuid = UUID.randomUUID();
bb = ByteBuffer.wrap(bytes);
bb.putLong(uuid.getMostSignificantBits());
bb.putLong(uuid.getLeastSignificantBits());
return bytes;
}
This worked. I have to clean it up to show some strange things I noticed.
(In my eyes.)
?
> (I'd use a UDF for the printing part as well, I suspect it's faster that
> way, but didn't measure it).
>
?And something else to keep me awake. ;-)?
> C:\Users\DDevienne>sqlite3
> SQLite version 3.10.2 2016-01-20 15:27:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite>
> sqlite> select typeof(x'ab');
> blob
> sqlite> select typeof('ab');
> text
> sqlite> select typeof(cast('ab' as blob));
> blob
> sqlite> select hex(x'ab');
> AB
> sqlite> select hex(cast('ab' as blob));
> 6162
> sqlite>
>
?Here I get a blob:
sqlite> WITH UUIDTable AS (
...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
...> )
...> SELECT TYPEOF(CAST(SUBSTR(UUIDStr, 1, 8) ||
...>SUBSTR(UUIDStr, 10, 4) ||
...>SUBSTR(UUIDStr, 15, 4) ||
...>SUBSTR(UUIDStr, 20, 4) ||
...>SUBSTR(UUIDStr, 25, 12) AS blob))
...> FROM UUIDTable
...> ;
blob
But when I do not use TYPEOF I get a string instead of a blob:
sqlite> WITH UUIDTable AS (
...> SELECT '3DBA81DE-7AA7-412E-954F-5B2DA8D4AB6C' AS UUIDStr
...> )
...> SELECT CAST(SUBSTR(UUIDStr, 1, 8) ||
...> SUBSTR(UUIDStr, 10, 4) ||
...> SUBSTR(UUIDStr, 15, 4) ||
...> SUBSTR(UUIDStr, 20, 4) ||
...> SUBSTR(UUIDStr, 25, 12) AS blob)
...> FROM UUIDTable
...> ;
3DBA81DE7AA7412E954F5B2DA8D4AB6C
What am I doing wrong?
--
Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 20:10 GMT+02:00 John McKown : > On Fri, Apr 15, 2016 at 1:00 PM, Cecil Westerhof > wrote: > ?? > > > > > > ?I do not think it is. When you add something to the database to signify > > that a primary key is not allowed to be NULL, then this is not in an old > > database, ergo in the old database NULLs are allowed. Where does backward > > compatibility get broken? > > > > ?I am somewhat hesitant to ?join in to this, however briefly. What occurs > to me on the breaking of backward in compatibility is an old application, > which is dependent on NULLs in a primary key, creating a _new_ database. > Perhaps because it has a "unload" and "reload" or "import" capability. Or > even one which depends on the user using the sqlite3 command to do backups. > If a PRAGMA were to be established as you have suggested, then it needs to > default to the _old_ way of doing things simply because the aforementioned > old application will not know of it and thus not use it. > ?That would make sense. It looks strange to me to depend on NULLs in a primary key, but you never know. When the default is the old way, then the pragma would not be very beneficial in my opinion. So I think we can take the pragma from the table. On the other hand it could be handy that by using the pragma there is no need for ?unnecessary? NOT NULLs, but that becomes a lot less interesting as what I first thought. The next best option is then to make the problem very clear, so people will not be bitten. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 22:04 GMT+02:00 R Smith : ?I do not think it is. When you add something to the database to signify >> that a primary key is not allowed to be NULL, then this is not in an old >> database, ergo in the old database NULLs are allowed. Where does backward >> compatibility get broken? As I see it, it is as with partial indexes. > > > > ? >> >>> B. Your suggestion would break backward compatibility, no matter how >>> "light" you coat it. >>> >>> ?I really do not see this. Could you expand on that? >>> >> > Imagine a program written 2 years ago, for instance works on all Apple > computers, or perhaps Android phones. In fact, imagine several programs > where the programmers used, either through conscious decision to employ a > "feature" of SQLite, or perhaps simply out of ignorance, the NULL values > allowed in PK situation. > Some time later, SQLite gets updated with your requested new default. > These programs get re-compiled with defaults, or even just use the packaged > SQLite that are now updated inside OSX or Android, etc. Suddenly, their > programs do no longer work, Keys fail to get inserted... Users have devices > crashing everywhere. Apple perhaps quickly rolls back to a previous version > that was not so damaged, but every compiled-in version of the SQLite code > is out in the wild causing problems. SQLite runs on billions of devices and > systems. > ?It is clear as daylight now. Thank you for the explanation. I hope that I was not to pesky.? > This is what backwards-compatible means, that a system and data will still > work as it always worked, even after you upgrade the engine. To get to your > example of Partial indices - if a DB did not use them before, then it still > doesn't use them, all is well. Only new DB's could use them. > ?Yeah, I did not think it through enough. Luckily the maintainers think better about consequences as I did.? > So if you opt for a pragma that lets you avoid NULLs once you activate > it... sure, but who will that really help? People will need to read the > documentation to even know that pragma exists (which you pointed out they > don't usually do in the first place), and simply /knowing/ the reason for > that pragma, will obviate the need for it. > ?It does not add much then no. The only thing is that people could keep database definitions ?the same? for different databases.? ?The only change I would like is in the documentation. It should be ?impossible? to start using SQLite without knowing this pitfall. Not for me, I know it now, but for future users. ? > I am hoping that is as clear as possible with no hint of mocking - I > honestly mean it well. ?Yes, I now understand it. Next time I should curb my enthusiasm. ;-) C. The suggested work-around would introduce more complication than it is >>> solving. >>> >>> ?I do not see that either. Could you enlighten me? >> > > I trust this point was made above too. > ?Certainly.? ? > ?I like SQLite very much also. I even gave a presentation about it on >> T-DOSE. As you can see from the plethora of questions I ask(ed) I want to >> get serious with it. I do not use MySQL anymore and plan to migrate what I >> still have in H2 to SQLite also. :-) I do not say there is never a reason >> for another database, but I think that in my case there is not (at this >> moment of time). >> > > That's great news :) > Let me just note that we do not really shun the likes of Postgress, MSSQL, > MySQL etc. - those systems answer a different need. ?Me neither, but when SQLite is enough why add the complications of the other type of database? At my work they use DB2: I do not think SQLite would be a good replacement there. :-D ? ?I use it for logging. It is much easier to find something, or delete the parts you do not need anymore. Thanks for the patience. -- Cecil Westerhof
[sqlite] Primary key values can be NULL
2016-04-15 22:36 GMT+02:00 Cecil Westerhof : > ?I use it for logging. It is much easier to find something, or delete the > parts you do not need anymore. > An example: https://www.linkedin.com/pulse/use-bash-store-disc-info-sqlite-cecil-westerhof If I could do things better: let me know. -- Cecil Westerhof
[sqlite] Caveat entry
I was ?bitten? by the fact that in SQLite primary keys can contain NULL values. As I understood it, I was certainly not the first person to who this happened. It would not surprise me if there are more deviations that could spell problems for users of SQLite. That is why I think it would be a good idea to add a caveat entry at: www.sqlite.org/about.html Somewhere in the beginning of the menu and with a font and colour that will spring out, so it would be reasonably certain that people fall for differences between regular databases and SQLite. -- Cecil Westerhof
[sqlite] Is it possible that dropping a big table takes very long
I am playing a bit with SQLite. I first had a table with 1E8 elements. When
trying to drop this it looked like SQLite got hung. I tried it from DB
Browser and a Java program.
I just tried it with a table of 1E7 elements. That was dropped in about 13
seconds.
I will try it again with 1E8 elements, but it takes 4? hours to generated.
Is it possible that SQLite has trouble dropping very large tables? It was
5.2 GB. With 1E7 elements the table is 512 MB.
The definition of the table:
CREATE TABLE testUniqueUUID (
UUIDblob,
PRIMARY KEY(UUID)
CHECK(TYPEOF(UUID) = 'blob' AND
LENGTH(UUID) = 16 AND
SUBSTR(HEX(UUID), 13, 1) == '4' AND
SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
)
);
--
Cecil Westerhof
[sqlite] Is it possible that dropping a big table takes very long
2016-04-16 14:51 GMT+02:00 Simon Slavin : > > On 16 Apr 2016, at 10:59am, Cecil Westerhof > wrote: > > > I first had a table with 1E8 elements. When > > trying to drop this it looked like SQLite got hung. > > Please tell us which version of SQLite and which journal mode you're using. > ?I work with Java. With: SELECT SQLITE_VERSION() I get: 3.8.11? ?How can I get the journal mode in Jav?? With DB Browser I get Delete. But when I in sqlite3 give: PRAGMA schema.journal_mode; I get: Error: unknown database schema -- Cecil Westerhof
[sqlite] Is it possible that dropping a big table takes very long
2016-04-16 14:52 GMT+02:00 R Smith :
>
>
> On 2016/04/16 11:59 AM, Cecil Westerhof wrote:
>
>> I am playing a bit with SQLite. I first had a table with 1E8 elements.
>> When
>> trying to drop this it looked like SQLite got hung. I tried it from DB
>> Browser and a Java program.
>> I just tried it with a table of 1E7 elements. That was dropped in about 13
>> seconds.
>> I will try it again with 1E8 elements, but it takes 4? hours to generated.
>> Is it possible that SQLite has trouble dropping very large tables? It was
>> 5.2 GB. With 1E7 elements the table is 512 MB.
>>
>> The definition of the table:
>> CREATE TABLE testUniqueUUID (
>> UUIDblob,
>>
>> PRIMARY KEY(UUID)
>> CHECK(TYPEOF(UUID) = 'blob' AND
>>LENGTH(UUID) = 16 AND
>>SUBSTR(HEX(UUID), 13, 1) == '4' AND
>>SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
>> )
>> );
>>
>>
> I just duplicated the experiment (though I did not include the CHECK
> constraint) on SQLitespeed using standard SQLite library, and here is the
> results for the 10 mil rows (1E+7):
>
>
> -- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed version
> 2.0.2.4.
>
> -- Script Items: 4 Parameter Count: 0
> -- 2016-04-16 14:42:43.333 | [Info] Script Initialized, Started
> executing...
> --
>
>
> CREATE TABLE testUniqueUUID (
> UUID BLOB PRIMARY KEY
> );
>
> --Item Stats: Item No: 1 Query Size (Chars):
> 62
> -- VM Work Steps: 43Rows Modified:
> 0
> -- Full Query Time: 0d 00h 00m and 00.001s
> -- Query Result: Success.
> --
>
>
> WITH RndGen(i,RndBlob) AS (
> SELECT 0, (randomblob(16))
> UNION ALL
> SELECT i+1, (randomblob(16)) FROM RndGen WHERE i<1000
> )
> INSERT INTO testUniqueUUID (UUID) SELECT RndBlob FROM RndGen;
>
>
> --Item Stats: Item No: 2 Query Size (Chars):
> 199
> -- Result Columns:0 Result Rows: 0
> -- VM Work Steps: 29050 Rows Modified:
> 1001
> -- Full Query Time: 0d 00h 02m and 10.878s
> -- Query Result: Success.
> --
>
>
?For me this took about 7 minutes. But I do also more,?
> SELECT UUID FROM testUniqueUUID LIMIT 10;
>
>
> -- UUID
> --
> -- 0xA3044750B1A8567E7FD9DACD5C0C64CF
> -- 0xC6C6AAFAE6179E7B28867D5FB6AED7A6
> -- 0x2267D5856D5D7601FA9E0D8A1E6A66BC
> -- 0x63BEB2ECC58EA6D02D30ED27A3A50971
> -- 0x18477B93BD35C7A2ED83010619CA3887
> -- 0x47D7F3284B094CBE3BF6D77DC974F147
> -- 0x77736E93FAFE0436199CE84760A1072A
> -- 0x015E14BEA6D3C889958329CAF9C11F5C
> -- 0x1805A44908518BE6D6DE6BA63B5A9B71
> -- 0xE21DA4DFD367286DE89343FB02B9F8EF
>
> --Item Stats: Item No: 3 Query Size (Chars):
> 43
> -- Result Columns:1 Result Rows: 10
> -- VM Work Steps: 48Rows Modified:
> 0
> -- Full Query Time: 0d 00h 00m and 00.001s
> -- Query Result: Success.
> --
>
>
> DROP TABLE testUniqueUUID;
>
> --Item Stats: Item No: 4 Query Size (Chars):
> 28
> -- VM Work Steps: 149 Rows Modified:
> 0
> -- Full Query Time: 0d 00h 00m and 00.721s
> -- Query Result: Success.
> --
>
>
> -- Script Stats: Total Script Execution Time: 0d 00h 02m and
> 11.733s
> -- Total Script Query Time: 0d 00h 02m and
> 11.601s
> -- Total Database Rows Changed: 1001
> -- Total Virtual-Machine Steps: 29290
> -- Last executed Item Index:4
> -- Last Script Error:
> --
>
>
> -- 2016-04-16 14:44:55.054 | [Success]S
[sqlite] Can autocommit create smaller files
When filling a table with 10.000 records the file is 501 KB without auto commit and 500 KB with auto commit. Not a big difference, but I am intrigued: can auto commit result in smaller SQLite files? Is with Java and SQLite 3.8.11. -- Cecil Westerhof
[sqlite] Is it possible that dropping a big table takes very long
2016-04-16 15:41 GMT+02:00 Simon Slavin : > > > ?How can I get the journal mode in Jav?? > > With DB Browser I get Delete. > > But when I in sqlite3 give: > >PRAGMA schema.journal_mode; > > I get: > >Error: unknown database schema > > That is not well explained. Try just > > PRAGMA journal_mode; > ?That gives delete. -- Cecil Westerhof
[sqlite] Can autocommit create smaller files
2016-04-16 15:44 GMT+02:00 Cecil Westerhof : > When filling a table with 10.000 records the file is 501 KB without auto > commit and 500 KB with auto commit. Not a big difference, but I am > intrigued: can auto commit result in smaller SQLite files? > > Is with Java and SQLite 3.8.11. > ?It is not auto commit. I tried the same with the data changed from blob to text. It first looked that this gave different results also. But when I ran the version without auto commit (takes less as a second instead of 25 minutes), I saw I got the same differences. Can it be that because the data is random sometimes the indexes need more room? -- Cecil Westerhof
[sqlite] Is it possible that dropping a big table takes very long
2016-04-16 16:00 GMT+02:00 R Smith : > > > On 2016/04/16 3:39 PM, Cecil Westerhof wrote: > >> 2016-04-16 14:52 GMT+02:00 R Smith : >> >>-- 2016-04-16 14:44:55.054 | [Success]Script Success. >>> >>> As you can see, the INSERT obviously takes some time (even more-so if the >>> CHECK constraint is added), but the DROP Table takes almost no time >>> here... >>> >>> ?The drop is a very big difference: .7 seconds or 13. Is almost 20 times >> as >> long. Could I be doing something wrong?? >> > > The big one is still running, so we'll see - but I do think already there > is something very different. Simon might be on to something with the cache. > The journal mode should also make a difference, but not that big I think. > Once this run finishes, I will try the 10-Mil one with different journal > modes. I have never noticed a large time taken for dropping tables though, > but then I do not often drop very large tables. > ?I am known for doing strange things. ;-)? > If useful, I could share the program I am using. >> > > As long as the version is known and the modes used, the rest of the > software differences should be small - it's usually down to hardware. > ?One strange thing the commandline and DB Browser are using ?3.8.10.2 while Java is using 3.8.11. -- Cecil Westerhof
