Re: SQLite data storage
More complex and faster searches and joins are usually a good reason for a 'real' database, as well as the fact that a database has all the inbuilt methods for adding, and deleting records and other management tasks. The distinction is more relevant when getting into bigger data sets. However, in the flat data world, a datagrid in conjunction with arrays can meet the needs of 90% of most database applications. Trevor Devor's Datagrid is a one of the most amazing and useful features of LIvecode, and makes it a snap to integrate a manipulatable database and display into an application. On Fri, Mar 22, 2013 at 11:45 AM, Chris Sheffield cmsheffi...@icloud.comwrote: Hi Peter, For us, main reason to use a database rather than just a text file is simply to make the data a little more secure. I mean, it doesn't have to be way secure. But we do want to make it a little more difficult for someone with prying eyes to get hold of it. I also liked the idea of using the power of SQL to search and filter results for me. With a text file, to do it the easy way, you have to read the entire file into memory every time you access it. I didn't want to have to do that, being a mobile app and all. But to answer your question, yes, I could probably just use a tab-delimited text file and call it good. I just finished my import of the data and there are just over 8100 records. So nothing too huge. But like I said, I wanted to be able to run SQL queries on the data, and that can't be done with a text file, obviously. Thanks, Chris On Mar 22, 2013, at 11:43 AM, Peter Alcibiades palcibiades-fi...@yahoo.co.uk wrote: Can I ask what is probably a really silly question? Could you just use a tab separated text file to hold this amount of data, as long as there is only one table? I am a complete amateur of course so I probably did it all wrong. I had to accumulate 15,000+ records, adding to them as time went on, and periodically go through and extract reports. Not wishing to struggle with 'proper databases' I just stuck the records into a tab delimited file, and had at it. Making backups as seemed prudent. Nothing ever went wrong. It ran reasonably fast on a very old and obsolete spare machine. Also the nice thing was, the customer could just take a copy of the file and read it back into Excel and do any sort of analysis or custom reports they wanted. Probably there is some reason for using sqlite that I'm not aware of? Peter -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/SQLite-data-storage-tp4662380p4662429.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode -- Stephen Barncard San Francisco Ca. USA more about sqb http://www.google.com/profiles/sbarncar ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Thanks everyone for the feedback. Sounds like the general consensus is to use a single table to store all the data, which is the direction I was leaning anyway. I just wanted to make sure performance wouldn't take a hit, but it sounds like that shouldn't be a problem. Now I get to work on pulling the data from the spreadsheet into SQLite. Should be fun. :-) Thanks, Chris On Mar 21, 2013, at 11:50 AM, Peter Haworth p...@lcsql.com wrote: Hi Chris, Difficult to answer without a little more information about your app. In general, I wouldn't worry about the number of records - thousands won't be an issue for SQLite. I'd say that if the columns are the same on each sheet, one table will probably be the way to go - it's not usually a good idea to have tables with exactly the same table layout and I'd guess there's some piece of data you can use as a key when selecting data to differentiate between what used to be on each individual sheet, if that's a requirement. After that, it just becomes an issue of standard database design based on the data elements and the application requirements. If you'd like to send me the spreadsheet offline, I'd be happy to take a look and give you some thoughts. Pete lcSQL Software http://www.lcsql.com On Thu, Mar 21, 2013 at 9:14 AM, Chris Sheffield cmsheffi...@icloud.comwrote: I hope nobody minds that I ask this here. While it's not specifically about LiveCode, the database I'm asking about will be used for a LiveCode app. :-) I need some advice/pointers on how best to store some static read-only data in a SQLite database. We're talking potentially thousands of records. I've been given an Excel spreadsheet with 24 sheets containing data to import. There are about 12 fields/columns. The data is separated into 24 sheets, but it could potentially all reside in one table in the database (fields are the same on each sheet). The question is, should I do that? Will SQLite bog down after a while? This new app we're working on will need constant access to this database, probably via several open record sets at once. I'm just trying to figure out if it would be best to store everything in one large table, or to split each sheet of data into its own table. Would that be more efficient? Or would it be even better to have each sheet in its own file? Also, are there specific settings/properties I should set on the db to help keep performance as optimal as possible? Just brainstorming here. Would love to hear opinions, especially if someone out there is a SQLite guru. :-) Thanks, Chris -- Chris Sheffield Read Naturally, Inc. www.readnaturally.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Hi Chris, Is the import a one-time task or an ongoing need? If the former and you can export the spreadsheet data in csv format, there a number of tools that will do the import for you. On Fri, Mar 22, 2013 at 7:10 AM, Chris Sheffield cmsheffi...@icloud.comwrote: Now I get to work on pulling the data from the spreadsheet into SQLite. Should be fun. :-) Pete lcSQL Software http://www.lcsql.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Hi Pete, Just a one-time task. I'll export the sheets as you say, and then import them using a tool that I've used for quite a while now. Time-consuming, but not too bad. Thanks, Chris On Mar 22, 2013, at 10:28 AM, Peter Haworth p...@lcsql.com wrote: Hi Chris, Is the import a one-time task or an ongoing need? If the former and you can export the spreadsheet data in csv format, there a number of tools that will do the import for you. On Fri, Mar 22, 2013 at 7:10 AM, Chris Sheffield cmsheffi...@icloud.comwrote: Now I get to work on pulling the data from the spreadsheet into SQLite. Should be fun. :-) Pete lcSQL Software http://www.lcsql.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Can I ask what is probably a really silly question? Could you just use a tab separated text file to hold this amount of data, as long as there is only one table? I am a complete amateur of course so I probably did it all wrong. I had to accumulate 15,000+ records, adding to them as time went on, and periodically go through and extract reports. Not wishing to struggle with 'proper databases' I just stuck the records into a tab delimited file, and had at it. Making backups as seemed prudent. Nothing ever went wrong. It ran reasonably fast on a very old and obsolete spare machine. Also the nice thing was, the customer could just take a copy of the file and read it back into Excel and do any sort of analysis or custom reports they wanted. Probably there is some reason for using sqlite that I'm not aware of? Peter -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/SQLite-data-storage-tp4662380p4662429.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Hi Peter, For us, main reason to use a database rather than just a text file is simply to make the data a little more secure. I mean, it doesn't have to be way secure. But we do want to make it a little more difficult for someone with prying eyes to get hold of it. I also liked the idea of using the power of SQL to search and filter results for me. With a text file, to do it the easy way, you have to read the entire file into memory every time you access it. I didn't want to have to do that, being a mobile app and all. But to answer your question, yes, I could probably just use a tab-delimited text file and call it good. I just finished my import of the data and there are just over 8100 records. So nothing too huge. But like I said, I wanted to be able to run SQL queries on the data, and that can't be done with a text file, obviously. Thanks, Chris On Mar 22, 2013, at 11:43 AM, Peter Alcibiades palcibiades-fi...@yahoo.co.uk wrote: Can I ask what is probably a really silly question? Could you just use a tab separated text file to hold this amount of data, as long as there is only one table? I am a complete amateur of course so I probably did it all wrong. I had to accumulate 15,000+ records, adding to them as time went on, and periodically go through and extract reports. Not wishing to struggle with 'proper databases' I just stuck the records into a tab delimited file, and had at it. Making backups as seemed prudent. Nothing ever went wrong. It ran reasonably fast on a very old and obsolete spare machine. Also the nice thing was, the customer could just take a copy of the file and read it back into Excel and do any sort of analysis or custom reports they wanted. Probably there is some reason for using sqlite that I'm not aware of? Peter -- View this message in context: http://runtime-revolution.278305.n4.nabble.com/SQLite-data-storage-tp4662380p4662429.html Sent from the Revolution - User mailing list archive at Nabble.com. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
SQLite data storage
I hope nobody minds that I ask this here. While it's not specifically about LiveCode, the database I'm asking about will be used for a LiveCode app. :-) I need some advice/pointers on how best to store some static read-only data in a SQLite database. We're talking potentially thousands of records. I've been given an Excel spreadsheet with 24 sheets containing data to import. There are about 12 fields/columns. The data is separated into 24 sheets, but it could potentially all reside in one table in the database (fields are the same on each sheet). The question is, should I do that? Will SQLite bog down after a while? This new app we're working on will need constant access to this database, probably via several open record sets at once. I'm just trying to figure out if it would be best to store everything in one large table, or to split each sheet of data into its own table. Would that be more efficient? Or would it be even better to have each sheet in its own file? Also, are there specific settings/properties I should set on the db to help keep performance as optimal as possible? Just brainstorming here. Would love to hear opinions, especially if someone out there is a SQLite guru. :-) Thanks, Chris -- Chris Sheffield Read Naturally, Inc. www.readnaturally.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Chris- Caveat: I know enough about databases to be dangerous, so if someone else chimes in with conflicting information, ignore this. I don't think you'll run into any problems with SQLite performance for what you've got in mind. And anything beats Excel. Is there a reason the data is separated into 24 sheets? Since the data is all of a common format then it would be better normalized if you put it all in the same table. If the sharding makes it easier to retrieve data then you'll get slightly better performance if you keep it as 24 tables, but otherwise I'd just throw it all together - it will make your queries easier, especially if you have to combine data from the different sheets. -- Mark Wieder mwie...@ahsoftware.net ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Hi Chris, Difficult to answer without a little more information about your app. In general, I wouldn't worry about the number of records - thousands won't be an issue for SQLite. I'd say that if the columns are the same on each sheet, one table will probably be the way to go - it's not usually a good idea to have tables with exactly the same table layout and I'd guess there's some piece of data you can use as a key when selecting data to differentiate between what used to be on each individual sheet, if that's a requirement. After that, it just becomes an issue of standard database design based on the data elements and the application requirements. If you'd like to send me the spreadsheet offline, I'd be happy to take a look and give you some thoughts. Pete lcSQL Software http://www.lcsql.com On Thu, Mar 21, 2013 at 9:14 AM, Chris Sheffield cmsheffi...@icloud.comwrote: I hope nobody minds that I ask this here. While it's not specifically about LiveCode, the database I'm asking about will be used for a LiveCode app. :-) I need some advice/pointers on how best to store some static read-only data in a SQLite database. We're talking potentially thousands of records. I've been given an Excel spreadsheet with 24 sheets containing data to import. There are about 12 fields/columns. The data is separated into 24 sheets, but it could potentially all reside in one table in the database (fields are the same on each sheet). The question is, should I do that? Will SQLite bog down after a while? This new app we're working on will need constant access to this database, probably via several open record sets at once. I'm just trying to figure out if it would be best to store everything in one large table, or to split each sheet of data into its own table. Would that be more efficient? Or would it be even better to have each sheet in its own file? Also, are there specific settings/properties I should set on the db to help keep performance as optimal as possible? Just brainstorming here. Would love to hear opinions, especially if someone out there is a SQLite guru. :-) Thanks, Chris -- Chris Sheffield Read Naturally, Inc. www.readnaturally.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Hi Chris While not a SQLite guru, I have used it on and off at various times and would probably recommend just having it all in 1 table. Any read-only queries will just return you a record set which you can put in a container and do anything with. SQLite is quick, you can add indices and custom views. Think it'd keep your management much more straightforward than multiple tables. But just my 2c... ;-) cheers Alan I need some advice/pointers on how best to store some static read-only data in a SQLite database. We're talking potentially thousands of records. I've been given an Excel spreadsheet with 24 sheets containing data to import. There are about 12 fields/columns. The data is separated into 24 sheets, but it could potentially all reside in one table in the database (fields are the same on each sheet). The question is, should I do that? Will SQLite bog down after a while? This new app we're working on will need constant access to this database, probably via several open record sets at once. I'm just trying to figure out if it would be best to store everything in one large table, or to split each sheet of data into its own table. Would that be more efficient? Or would it be even better to have each sheet in its own file? Also, are there specific settings/properties I should set on the db to help keep performance as optimal as possible? Just brainstorming here. Would love to hear opinions, especially if someone out there is a SQLite guru. :-) Thanks, Chris -- Alan Stenhouse alanstenho...@hotmail.com Check out our apps on the App Store: BeatSpeak - the multilingual talking metronome; EV-Point - Find your nearest Electric Vehicle Recharge Station. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Chris Sheffield wrote: I need some advice/pointers on how best to store some static read-only data in a SQLite database. We're talking potentially thousands of records. Thousands isn't very much. Is that the total for all the sheets? If so, while SQLite is a good solution, sometimes keeping things simple with just chunk expressions isn't a bad option with relatively small data sets. -- Richard Gaskin Fourth World LiveCode training and consulting: http://www.fourthworld.com Webzine for LiveCode developers: http://www.LiveCodeJournal.com Follow me on Twitter: http://twitter.com/FourthWorldSys ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Thousands is like zero in terms of a performance hit. I have an app on iOS that has tens-of-thousands of records in it, and to say it's instantaneous would be an injustice. There are lots of things you can do to enhance performance (either normalizing or denormalizing the data, adding indexes, etc.), if that winds up being an issue, but I have tried both mySQL and SQLite and have yet to figure out why I need one over the other. Now when you get into multi-concurrent users with lots of records, then there start to be things to think about, but that is not the case here, and you could probably also store the data in a field in a card and not really notice. There have been numerous studies done that compare various DBMS's, especially the free ones. The differences seem to be small for the most part. On Thu, Mar 21, 2013 at 12:14 PM, Chris Sheffield cmsheffi...@icloud.comwrote: I hope nobody minds that I ask this here. While it's not specifically about LiveCode, the database I'm asking about will be used for a LiveCode app. :-) I need some advice/pointers on how best to store some static read-only data in a SQLite database. We're talking potentially thousands of records. I've been given an Excel spreadsheet with 24 sheets containing data to import. There are about 12 fields/columns. The data is separated into 24 sheets, but it could potentially all reside in one table in the database (fields are the same on each sheet). The question is, should I do that? Will SQLite bog down after a while? This new app we're working on will need constant access to this database, probably via several open record sets at once. I'm just trying to figure out if it would be best to store everything in one large table, or to split each sheet of data into its own table. Would that be more efficient? Or would it be even better to have each sheet in its own file? Also, are there specific settings/properties I should set on the db to help keep performance as optimal as possible? Just brainstorming here. Would love to hear opinions, especially if someone out there is a SQLite guru. :-) Thanks, Chris -- Chris Sheffield Read Naturally, Inc. www.readnaturally.com ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode -- On the first day, God created the heavens and the Earth On the second day, God created the oceans. On the third day, God put the animals on hold for a few hours, and did a little diving. And God said, This is good. ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: SQLite data storage
Especially when the data is conveniently within data-grid size On Thu, Mar 21, 2013 at 11:13 AM, Richard Gaskin ambassa...@fourthworld.com wrote: Thousands isn't very much. Is that the total for all the sheets? If so, while SQLite is a good solution, sometimes keeping things simple with just chunk expressions isn't a bad option with relatively small data sets. -- Richard Gaskin -- Stephen Barncard San Francisco Ca. USA more about sqb http://www.google.com/profiles/sbarncar ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
